X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=891b830a4391354c50940296c4f7a86a4bd1f512;hb=6cb6841866d3111e1f7ec4222cd555dd4ac9ba28;hp=d50e0bbd063da19d19820be240e0d5f7021447fc;hpb=6eb9dff807b65729363ff094eba62746f4301c28;p=friendica.git diff --git a/database.sql b/database.sql index d50e0bbd06..891b830a43 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ --- Friendica 2022.09-rc (Giant Rhubarb) --- DB_UPDATE_VERSION 1483 +-- Friendica 2022.12-dev (Giant Rhubarb) +-- DB_UPDATE_VERSION 1487 -- ------------------------------------------ @@ -309,6 +309,20 @@ CREATE TABLE IF NOT EXISTS `2fa_trusted_browser` ( FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Two-factor authentication trusted browsers'; +-- +-- TABLE account-user +-- +CREATE TABLE IF NOT EXISTS `account-user` ( + `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', + `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the account url', + `uid` mediumint unsigned NOT NULL COMMENT 'User ID', + PRIMARY KEY(`id`), + UNIQUE INDEX `uri-id_uid` (`uri-id`,`uid`), + INDEX `uid_uri-id` (`uid`,`uri-id`), + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`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='Remote and local accounts'; + -- -- TABLE addon -- @@ -625,6 +639,7 @@ CREATE TABLE IF NOT EXISTS `fcontact` ( `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', @@ -1007,17 +1022,6 @@ CREATE TABLE IF NOT EXISTS `openwebauth-token` ( FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Store OpenWebAuth token to verify contacts'; --- --- TABLE pagecache --- -CREATE TABLE IF NOT EXISTS `pagecache` ( - `page` varbinary(255) NOT NULL COMMENT 'Page', - `content` mediumtext COMMENT 'Page content', - `fetched` datetime COMMENT 'date when the page had been fetched', - PRIMARY KEY(`page`), - INDEX `fetched` (`fetched`) -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Stores temporary data'; - -- -- TABLE parsed_url -- @@ -1182,6 +1186,7 @@ CREATE TABLE IF NOT EXISTS `post-content` ( `content-warning` varchar(255) NOT NULL DEFAULT '' COMMENT '', `body` mediumtext COMMENT 'item body content', `raw-body` mediumtext COMMENT 'Body without embedded media links', + `quote-uri-id` int unsigned COMMENT 'Id of the item-uri table that contains the quoted uri', `location` varchar(255) NOT NULL DEFAULT '' COMMENT 'text location where this item originated', `coord` varchar(255) NOT NULL DEFAULT '' COMMENT 'longitude/latitude pair representing location where this item originated', `language` text COMMENT 'Language information about this post', @@ -1198,7 +1203,9 @@ CREATE TABLE IF NOT EXISTS `post-content` ( INDEX `plink` (`plink`(191)), INDEX `resource-id` (`resource-id`), FULLTEXT INDEX `title-content-warning-body` (`title`,`content-warning`,`body`), - FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE + INDEX `quote-uri-id` (`quote-uri-id`), + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`quote-uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Content for all posts'; -- @@ -1285,6 +1292,7 @@ CREATE TABLE IF NOT EXISTS `post-media` ( `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', `url` varbinary(1024) NOT NULL COMMENT 'Media URL', + `media-uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the activities uri-id', `type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'Media type', `mimetype` varchar(60) COMMENT '', `height` smallint unsigned COMMENT 'Height of the media', @@ -1304,7 +1312,9 @@ CREATE TABLE IF NOT EXISTS `post-media` ( PRIMARY KEY(`id`), UNIQUE INDEX `uri-id-url` (`uri-id`,`url`(512)), INDEX `uri-id-id` (`uri-id`,`id`), - FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE + INDEX `media-uri-id` (`media-uri-id`), + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`media-uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Attached media'; -- @@ -1567,6 +1577,7 @@ CREATE TABLE IF NOT EXISTS `profile` ( `education` text COMMENT 'Deprecated', `contact` text COMMENT 'Deprecated', `homepage` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `homepage_verified` boolean NOT NULL DEFAULT '0' COMMENT 'was the homepage verified by a rel-me link back to the profile', `xmpp` varchar(255) NOT NULL DEFAULT '' COMMENT 'XMPP address', `matrix` varchar(255) NOT NULL DEFAULT '' COMMENT 'Matrix address', `photo` varbinary(383) NOT NULL DEFAULT '' COMMENT '', @@ -1803,6 +1814,8 @@ CREATE VIEW `post-user-view` AS SELECT `post-user`.`thr-parent-id` AS `thr-parent-id`, `conversation-item-uri`.`uri` AS `conversation`, `post-thread-user`.`conversation-id` AS `conversation-id`, + `quote-item-uri`.`uri` AS `quote-uri`, + `post-content`.`quote-uri-id` AS `quote-uri-id`, `item-uri`.`guid` AS `guid`, `post-user`.`wall` AS `wall`, `post-user`.`gravity` AS `gravity`, @@ -1958,6 +1971,7 @@ CREATE VIEW `post-user-view` AS SELECT LEFT JOIN `event` ON `event`.`id` = `post-user`.`event-id` LEFT JOIN `diaspora-interaction` ON `diaspora-interaction`.`uri-id` = `post-user`.`uri-id` LEFT JOIN `post-content` ON `post-content`.`uri-id` = `post-user`.`uri-id` + LEFT JOIN `item-uri` AS `quote-item-uri` ON `quote-item-uri`.`id` = `post-content`.`quote-uri-id` LEFT JOIN `post-delivery-data` ON `post-delivery-data`.`uri-id` = `post-user`.`uri-id` AND `post-user`.`origin` LEFT JOIN `post-question` ON `post-question`.`uri-id` = `post-user`.`uri-id` LEFT JOIN `permissionset` ON `permissionset`.`id` = `post-user`.`psid` @@ -1981,6 +1995,8 @@ CREATE VIEW `post-thread-user-view` AS SELECT `post-user`.`thr-parent-id` AS `thr-parent-id`, `conversation-item-uri`.`uri` AS `conversation`, `post-thread-user`.`conversation-id` AS `conversation-id`, + `quote-item-uri`.`uri` AS `quote-uri`, + `post-content`.`quote-uri-id` AS `quote-uri-id`, `item-uri`.`guid` AS `guid`, `post-thread-user`.`wall` AS `wall`, `post-user`.`gravity` AS `gravity`, @@ -2134,6 +2150,7 @@ CREATE VIEW `post-thread-user-view` AS SELECT LEFT JOIN `event` ON `event`.`id` = `post-user`.`event-id` LEFT JOIN `diaspora-interaction` ON `diaspora-interaction`.`uri-id` = `post-thread-user`.`uri-id` LEFT JOIN `post-content` ON `post-content`.`uri-id` = `post-thread-user`.`uri-id` + LEFT JOIN `item-uri` AS `quote-item-uri` ON `quote-item-uri`.`id` = `post-content`.`quote-uri-id` LEFT JOIN `post-delivery-data` ON `post-delivery-data`.`uri-id` = `post-thread-user`.`uri-id` AND `post-thread-user`.`origin` LEFT JOIN `post-question` ON `post-question`.`uri-id` = `post-thread-user`.`uri-id` LEFT JOIN `permissionset` ON `permissionset`.`id` = `post-thread-user`.`psid` @@ -2153,6 +2170,8 @@ CREATE VIEW `post-view` AS SELECT `post`.`thr-parent-id` AS `thr-parent-id`, `conversation-item-uri`.`uri` AS `conversation`, `post-thread`.`conversation-id` AS `conversation-id`, + `quote-item-uri`.`uri` AS `quote-uri`, + `post-content`.`quote-uri-id` AS `quote-uri-id`, `item-uri`.`guid` AS `guid`, `post`.`gravity` AS `gravity`, `external-item-uri`.`uri` AS `extid`, @@ -2275,6 +2294,7 @@ CREATE VIEW `post-view` AS SELECT LEFT JOIN `verb` ON `verb`.`id` = `post`.`vid` LEFT JOIN `diaspora-interaction` ON `diaspora-interaction`.`uri-id` = `post`.`uri-id` LEFT JOIN `post-content` ON `post-content`.`uri-id` = `post`.`uri-id` + LEFT JOIN `item-uri` AS `quote-item-uri` ON `quote-item-uri`.`id` = `post-content`.`quote-uri-id` LEFT JOIN `post-question` ON `post-question`.`uri-id` = `post`.`uri-id` LEFT JOIN `post` AS `parent-post` ON `parent-post`.`uri-id` = `post`.`parent-uri-id` LEFT JOIN `contact` AS `parent-post-author` ON `parent-post-author`.`id` = `parent-post`.`author-id`; @@ -2292,6 +2312,8 @@ CREATE VIEW `post-thread-view` AS SELECT `post`.`thr-parent-id` AS `thr-parent-id`, `conversation-item-uri`.`uri` AS `conversation`, `post-thread`.`conversation-id` AS `conversation-id`, + `quote-item-uri`.`uri` AS `quote-uri`, + `post-content`.`quote-uri-id` AS `quote-uri-id`, `item-uri`.`guid` AS `guid`, `post`.`gravity` AS `gravity`, `external-item-uri`.`uri` AS `extid`, @@ -2414,6 +2436,7 @@ CREATE VIEW `post-thread-view` AS SELECT LEFT JOIN `verb` ON `verb`.`id` = `post`.`vid` LEFT JOIN `diaspora-interaction` ON `diaspora-interaction`.`uri-id` = `post-thread`.`uri-id` LEFT JOIN `post-content` ON `post-content`.`uri-id` = `post-thread`.`uri-id` + LEFT JOIN `item-uri` AS `quote-item-uri` ON `quote-item-uri`.`id` = `post-content`.`quote-uri-id` LEFT JOIN `post-question` ON `post-question`.`uri-id` = `post-thread`.`uri-id` LEFT JOIN `post` AS `parent-post` ON `parent-post`.`uri-id` = `post`.`parent-uri-id` LEFT JOIN `contact` AS `parent-post-author` ON `parent-post-author`.`id` = `parent-post`.`author-id`; @@ -2652,6 +2675,7 @@ CREATE VIEW `owner-view` AS SELECT `profile`.`postal-code` AS `postal-code`, `profile`.`country-name` AS `country-name`, `profile`.`homepage` AS `homepage`, + `profile`.`homepage_verified` AS `homepage_verified`, `profile`.`dob` AS `dob` FROM `user` INNER JOIN `contact` ON `contact`.`uid` = `user`.`uid` AND `contact`.`self`