X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=e06454b1145d8927925efe21a53dfcf43c1a4883;hb=af2a38c5b3724d140700316f3a0251e82692de38;hp=855c913580d6781114a1f4729ff74c143f31febc;hpb=215c332a94ad31ac4c0c18ecf0cc4f8eafbd6b56;p=friendica.git diff --git a/database.sql b/database.sql index 855c913580..e06454b114 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ --- Friendica 2022.12-rc (Giant Rhubarb) --- DB_UPDATE_VERSION 1500 +-- Friendica 2023.03-dev (Giant Rhubarb) +-- DB_UPDATE_VERSION 1510 -- ------------------------------------------ @@ -129,6 +129,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 '', @@ -493,18 +494,6 @@ CREATE TABLE IF NOT EXISTS `cache` ( INDEX `k_expires` (`k`,`expires`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Stores temporary data'; --- --- TABLE config --- -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 '', - `v` mediumtext COMMENT '', - PRIMARY KEY(`id`), - UNIQUE INDEX `cat_k` (`cat`,`k`) -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='main configuration storage'; - -- -- TABLE contact-relation -- @@ -578,6 +567,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 +812,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 +821,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 +851,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 +1320,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 +1461,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 +1696,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='';