X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=0b91aedc06112d42ffc37024826bde562998d182;hb=15938c5b874434384a7dd4cdb245b43ac0a6dd6c;hp=35831813469b5397b64fb9a626f30cca2184a7da;hpb=d5e005f90dd81ec4da8cab33c3473b8f74d5b1a8;p=friendica.git diff --git a/database.sql b/database.sql index 3583181346..0b91aedc06 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ --- Friendica 2022.12-dev (Giant Rhubarb) --- DB_UPDATE_VERSION 1498 +-- Friendica 2023.03-dev (Giant Rhubarb) +-- DB_UPDATE_VERSION 1512 -- ------------------------------------------ @@ -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 '', @@ -493,18 +495,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 +568,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 -- @@ -667,39 +678,6 @@ CREATE TABLE IF NOT EXISTS `event` ( FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Events'; --- --- TABLE fcontact --- -CREATE TABLE IF NOT EXISTS `fcontact` ( - `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', - `guid` varbinary(255) NOT NULL DEFAULT '' COMMENT 'unique id', - `url` varbinary(383) NOT NULL DEFAULT '' COMMENT '', - `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the fcontact url', - `name` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `photo` varbinary(383) NOT NULL DEFAULT '' COMMENT '', - `request` varbinary(383) NOT NULL DEFAULT '' COMMENT '', - `nick` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `addr` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `batch` varbinary(383) NOT NULL DEFAULT '' COMMENT '', - `notify` varbinary(383) NOT NULL DEFAULT '' COMMENT '', - `poll` varbinary(383) NOT NULL DEFAULT '' COMMENT '', - `confirm` varbinary(383) NOT NULL DEFAULT '' COMMENT '', - `priority` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', - `network` char(4) NOT NULL DEFAULT '' COMMENT '', - `alias` varbinary(383) NOT NULL DEFAULT '' COMMENT '', - `pubkey` text COMMENT '', - `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', - `updated` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', - `interacting_count` int unsigned DEFAULT 0 COMMENT 'Number of contacts this contact interactes with', - `interacted_count` int unsigned DEFAULT 0 COMMENT 'Number of contacts that interacted with this contact', - `post_count` int unsigned DEFAULT 0 COMMENT 'Number of posts and comments', - PRIMARY KEY(`id`), - INDEX `addr` (`addr`(32)), - UNIQUE INDEX `url` (`url`(190)), - UNIQUE INDEX `uri-id` (`uri-id`), - FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Diaspora compatible contacts - used in the Diaspora implementation'; - -- -- TABLE fetch-entry -- @@ -835,6 +813,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', @@ -843,7 +822,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'; -- @@ -871,6 +852,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 -- @@ -1330,6 +1321,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 @@ -1468,7 +1462,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', @@ -1703,15 +1697,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=''; @@ -2836,11 +2835,11 @@ CREATE VIEW `account-view` AS SELECT `contact`.`blocked` AS `blocked`, `contact`.`notify` AS `dfrn-notify`, `contact`.`poll` AS `dfrn-poll`, - `fcontact`.`guid` AS `diaspora-guid`, - `fcontact`.`batch` AS `diaspora-batch`, - `fcontact`.`notify` AS `diaspora-notify`, - `fcontact`.`poll` AS `diaspora-poll`, - `fcontact`.`alias` AS `diaspora-alias`, + `item-uri`.`guid` AS `diaspora-guid`, + `diaspora-contact`.`batch` AS `diaspora-batch`, + `diaspora-contact`.`notify` AS `diaspora-notify`, + `diaspora-contact`.`poll` AS `diaspora-poll`, + `diaspora-contact`.`alias` AS `diaspora-alias`, `apcontact`.`uuid` AS `ap-uuid`, `apcontact`.`type` AS `ap-type`, `apcontact`.`following` AS `ap-following`, @@ -2858,7 +2857,7 @@ CREATE VIEW `account-view` AS SELECT FROM `contact` LEFT JOIN `item-uri` ON `item-uri`.`id` = `contact`.`uri-id` LEFT JOIN `apcontact` ON `apcontact`.`uri-id` = `contact`.`uri-id` - LEFT JOIN `fcontact` ON `fcontact`.`uri-id` = contact.`uri-id` + LEFT JOIN `diaspora-contact` ON `diaspora-contact`.`uri-id` = contact.`uri-id` LEFT JOIN `gserver` ON `gserver`.`id` = contact.`gsid` WHERE `contact`.`uid` = 0; @@ -2937,14 +2936,14 @@ CREATE VIEW `account-user-view` AS SELECT `ucontact`.`reason` AS `reason`, `contact`.`notify` AS `dfrn-notify`, `contact`.`poll` AS `dfrn-poll`, - `fcontact`.`guid` AS `diaspora-guid`, - `fcontact`.`batch` AS `diaspora-batch`, - `fcontact`.`notify` AS `diaspora-notify`, - `fcontact`.`poll` AS `diaspora-poll`, - `fcontact`.`alias` AS `diaspora-alias`, - `fcontact`.`interacting_count` AS `diaspora-interacting_count`, - `fcontact`.`interacted_count` AS `diaspora-interacted_count`, - `fcontact`.`post_count` AS `diaspora-post_count`, + `item-uri`.`guid` AS `diaspora-guid`, + `diaspora-contact`.`batch` AS `diaspora-batch`, + `diaspora-contact`.`notify` AS `diaspora-notify`, + `diaspora-contact`.`poll` AS `diaspora-poll`, + `diaspora-contact`.`alias` AS `diaspora-alias`, + `diaspora-contact`.`interacting_count` AS `diaspora-interacting_count`, + `diaspora-contact`.`interacted_count` AS `diaspora-interacted_count`, + `diaspora-contact`.`post_count` AS `diaspora-post_count`, `apcontact`.`uuid` AS `ap-uuid`, `apcontact`.`type` AS `ap-type`, `apcontact`.`following` AS `ap-following`, @@ -2963,7 +2962,7 @@ CREATE VIEW `account-user-view` AS SELECT INNER JOIN `contact` ON `contact`.`uri-id` = `ucontact`.`uri-id` AND `contact`.`uid` = 0 LEFT JOIN `item-uri` ON `item-uri`.`id` = `ucontact`.`uri-id` LEFT JOIN `apcontact` ON `apcontact`.`uri-id` = `ucontact`.`uri-id` - LEFT JOIN `fcontact` ON `fcontact`.`uri-id` = `ucontact`.`uri-id` AND `fcontact`.`network` = 'dspr' + LEFT JOIN `diaspora-contact` ON `diaspora-contact`.`uri-id` = `ucontact`.`uri-id` LEFT JOIN `gserver` ON `gserver`.`id` = contact.`gsid`; --