X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=053f0c7279b6ad1a8116517728d569cd7b95af0d;hb=0dbee1c4d2e3e47fae5d40b9677851b44969843c;hp=3169bac03ac75bce1cbcb94878d4eea1e487eff0;hpb=733543505be2345b1a763b4924413569193773cc;p=friendica.git diff --git a/database.sql b/database.sql index 3169bac03a..053f0c7279 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ --- Friendica 2022.12-dev (Giant Rhubarb) --- DB_UPDATE_VERSION 1500 +-- Friendica 2023.03-dev (Giant Rhubarb) +-- DB_UPDATE_VERSION 1515 -- ------------------------------------------ @@ -34,6 +34,7 @@ CREATE TABLE IF NOT EXISTS `gserver` ( `last_poco_query` datetime DEFAULT '0001-01-01 00:00:00' COMMENT '', `last_contact` datetime DEFAULT '0001-01-01 00:00:00' COMMENT 'Last successful connection request', `last_failure` datetime DEFAULT '0001-01-01 00:00:00' COMMENT 'Last failed connection request', + `blocked` boolean COMMENT 'Server is blocked', `failed` boolean COMMENT 'Connection failed', `next_contact` datetime DEFAULT '0001-01-01 00:00:00' COMMENT 'Next connection request', PRIMARY KEY(`id`), @@ -129,6 +130,7 @@ CREATE TABLE IF NOT EXISTS `contact` ( `xmpp` varchar(255) NOT NULL DEFAULT '' COMMENT 'XMPP address', `matrix` varchar(255) NOT NULL DEFAULT '' COMMENT 'Matrix address', `avatar` varbinary(383) NOT NULL DEFAULT '' COMMENT '', + `blurhash` varbinary(255) COMMENT 'BlurHash representation of the avatar', `header` varbinary(383) COMMENT 'Header picture', `url` varbinary(383) NOT NULL DEFAULT '' COMMENT '', `nurl` varbinary(383) NOT NULL DEFAULT '' COMMENT '', @@ -498,8 +500,8 @@ CREATE TABLE IF NOT EXISTS `cache` ( -- CREATE TABLE IF NOT EXISTS `config` ( `id` int unsigned NOT NULL auto_increment COMMENT '', - `cat` varbinary(50) NOT NULL DEFAULT '' COMMENT '', - `k` varbinary(50) NOT NULL DEFAULT '' COMMENT '', + `cat` varbinary(50) NOT NULL DEFAULT '' COMMENT 'The category of the entry', + `k` varbinary(50) NOT NULL DEFAULT '' COMMENT 'The key of the entry', `v` mediumtext COMMENT '', PRIMARY KEY(`id`), UNIQUE INDEX `cat_k` (`cat`,`k`) @@ -578,6 +580,27 @@ CREATE TABLE IF NOT EXISTS `delayed-post` ( FOREIGN KEY (`wid`) REFERENCES `workerqueue` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Posts that are about to be distributed at a later time'; +-- +-- TABLE delivery-queue +-- +CREATE TABLE IF NOT EXISTS `delivery-queue` ( + `gsid` int unsigned NOT NULL COMMENT 'Target server', + `uri-id` int unsigned NOT NULL COMMENT 'Delivered post', + `created` datetime COMMENT '', + `command` varbinary(32) COMMENT '', + `cid` int unsigned COMMENT 'Target contact', + `uid` mediumint unsigned COMMENT 'Delivering user', + `failed` tinyint DEFAULT 0 COMMENT 'Number of times the delivery has failed', + PRIMARY KEY(`uri-id`,`gsid`), + INDEX `gsid_created` (`gsid`,`created`), + INDEX `uid` (`uid`), + INDEX `cid` (`cid`), + FOREIGN KEY (`gsid`) REFERENCES `gserver` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT, + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Delivery data for posts for the batch processing'; + -- -- TABLE diaspora-contact -- @@ -802,6 +825,7 @@ CREATE TABLE IF NOT EXISTS `inbox-entry-receiver` ( CREATE TABLE IF NOT EXISTS `inbox-status` ( `url` varbinary(383) NOT NULL COMMENT 'URL of the inbox', `uri-id` int unsigned COMMENT 'Item-uri id of inbox url', + `gsid` int unsigned COMMENT 'ID of the related server', `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Creation date of this entry', `success` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last successful delivery', `failure` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last failed delivery', @@ -810,7 +834,9 @@ CREATE TABLE IF NOT EXISTS `inbox-status` ( `shared` boolean NOT NULL DEFAULT '0' COMMENT 'Is it a shared inbox?', PRIMARY KEY(`url`), INDEX `uri-id` (`uri-id`), - FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE + INDEX `gsid` (`gsid`), + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`gsid`) REFERENCES `gserver` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Status of ActivityPub inboxes'; -- @@ -838,6 +864,16 @@ CREATE TABLE IF NOT EXISTS `intro` ( FOREIGN KEY (`suggest-cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT=''; +-- +-- TABLE key-value +-- +CREATE TABLE IF NOT EXISTS `key-value` ( + `k` varbinary(50) NOT NULL COMMENT '', + `v` mediumtext COMMENT '', + `updated_at` int unsigned NOT NULL COMMENT 'timestamp of the last update', + PRIMARY KEY(`k`) +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='A key value storage'; + -- -- TABLE locks -- @@ -1297,6 +1333,9 @@ CREATE TABLE IF NOT EXISTS `post-link` ( `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', `url` varbinary(511) NOT NULL COMMENT 'External URL', `mimetype` varchar(60) COMMENT '', + `height` smallint unsigned COMMENT 'Height of the media', + `width` smallint unsigned COMMENT 'Width of the media', + `blurhash` varbinary(255) COMMENT 'BlurHash representation of the link', PRIMARY KEY(`id`), UNIQUE INDEX `uri-id-url` (`uri-id`,`url`), FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE @@ -1435,7 +1474,7 @@ CREATE TABLE IF NOT EXISTS `post-user` ( `event-id` int unsigned COMMENT 'Used to link to the event.id', `unseen` boolean NOT NULL DEFAULT '1' COMMENT 'post has not been seen', `hidden` boolean NOT NULL DEFAULT '0' COMMENT 'Marker to hide the post from the user', - `notification-type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', + `notification-type` smallint unsigned NOT NULL DEFAULT 0 COMMENT '', `wall` boolean NOT NULL DEFAULT '0' COMMENT 'This item was posted to the wall of uid', `origin` boolean NOT NULL DEFAULT '0' COMMENT 'item originated at this site', `psid` int unsigned COMMENT 'ID of the permission set of this post', @@ -1670,15 +1709,20 @@ CREATE TABLE IF NOT EXISTS `register` ( CREATE TABLE IF NOT EXISTS `report` ( `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', `uid` mediumint unsigned COMMENT 'Reporting user', + `reporter-id` int unsigned COMMENT 'Reporting contact', `cid` int unsigned NOT NULL COMMENT 'Reported contact', `comment` text COMMENT 'Report', + `category` varchar(20) COMMENT 'Category of the report (spam, violation, other)', + `rules` text COMMENT 'Violated rules', `forward` boolean COMMENT 'Forward the report to the remote server', `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', `status` tinyint unsigned COMMENT 'Status of the report', PRIMARY KEY(`id`), INDEX `uid` (`uid`), INDEX `cid` (`cid`), + INDEX `reporter-id` (`reporter-id`), FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`reporter-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (`cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='';