X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=1e439213d4b46c27b6dfdd63954e22a8dc20239e;hb=bd89b8262f11b24b90c156359759f185b3844d68;hp=cc90509bccbf8657c88e274239045a38515194ae;hpb=9113b7e393acf0f74b42104b687f1ad5c8711b07;p=friendica.git diff --git a/database.sql b/database.sql index cc90509bcc..1e439213d4 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ --- Friendica 2020.09-dev (Red Hot Poker) --- DB_UPDATE_VERSION 1364 +-- Friendica 2020.12-dev (Red Hot Poker) +-- DB_UPDATE_VERSION 1370 -- ------------------------------------------ @@ -98,6 +98,7 @@ CREATE TABLE IF NOT EXISTS `contact` ( `forum` boolean NOT NULL DEFAULT '0' COMMENT 'contact is a forum', `prv` boolean NOT NULL DEFAULT '0' COMMENT 'contact is a private group', `contact-type` tinyint NOT NULL DEFAULT 0 COMMENT '', + `manually-approve` boolean COMMENT '', `hidden` boolean NOT NULL DEFAULT '0' COMMENT '', `archive` boolean NOT NULL DEFAULT '0' COMMENT '', `pending` boolean NOT NULL DEFAULT '1' COMMENT '', @@ -584,6 +585,16 @@ CREATE TABLE IF NOT EXISTS `hook` ( UNIQUE INDEX `hook_file_function` (`hook`,`file`,`function`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='addon hook registry'; +-- +-- TABLE host +-- +CREATE TABLE IF NOT EXISTS `host` ( + `id` tinyint unsigned NOT NULL auto_increment COMMENT 'sequential ID', + `name` varchar(128) NOT NULL DEFAULT '' COMMENT 'The hostname', + PRIMARY KEY(`id`), + UNIQUE INDEX `name` (`name`) +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Hostname'; + -- -- TABLE inbox-status -- @@ -616,6 +627,7 @@ CREATE TABLE IF NOT EXISTS `intro` ( PRIMARY KEY(`id`), INDEX `contact-id` (`contact-id`), INDEX `uid` (`uid`), + FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (`contact-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT=''; @@ -642,6 +654,7 @@ CREATE TABLE IF NOT EXISTS `item` ( `network` char(4) NOT NULL DEFAULT '' COMMENT 'Network from where the item comes from', `owner-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'Link to the contact table with uid=0 of the owner of this item', `author-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'Link to the contact table with uid=0 of the author of this item', + `causer-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'Link to the contact table with uid=0 of the contact that caused the item creation', `icid` int unsigned COMMENT 'Id of the item-content table entry that contains the whole item content', `iaid` int unsigned COMMENT 'Id of the item-activity table entry that contains the activity data', `vid` smallint unsigned COMMENT 'Id of the verb table entry that contains the activity verbs', @@ -727,9 +740,11 @@ CREATE TABLE IF NOT EXISTS `item` ( INDEX `uri-id` (`uri-id`), INDEX `parent-uri-id` (`parent-uri-id`), INDEX `thr-parent-id` (`thr-parent-id`), + INDEX `causer-id` (`causer-id`), FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (`parent-uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (`thr-parent-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`causer-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT, FOREIGN KEY (`psid`) REFERENCES `permissionset` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Structure for all posts'; @@ -774,6 +789,7 @@ CREATE TABLE IF NOT EXISTS `item-content` ( `verb` varchar(100) NOT NULL DEFAULT '' COMMENT 'ActivityStreams verb', PRIMARY KEY(`id`), UNIQUE INDEX `uri-plink-hash` (`uri-plink-hash`), + FULLTEXT INDEX `title-content-warning-body` (`title`,`content-warning`,`body`), INDEX `uri` (`uri`(191)), INDEX `plink` (`plink`(191)), INDEX `uri-id` (`uri-id`), @@ -901,6 +917,8 @@ CREATE TABLE IF NOT EXISTS `notify-threads` ( PRIMARY KEY(`id`), INDEX `master-parent-uri-id` (`master-parent-uri-id`), INDEX `receiver-uid` (`receiver-uid`), + INDEX `notify-id` (`notify-id`), + FOREIGN KEY (`notify-id`) REFERENCES `notify` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (`receiver-uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT=''; @@ -921,14 +939,13 @@ CREATE TABLE IF NOT EXISTS `oembed` ( -- CREATE TABLE IF NOT EXISTS `openwebauth-token` ( `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', - `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id', + `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id - currently unused', `type` varchar(32) NOT NULL DEFAULT '' COMMENT 'Verify type', `token` varchar(255) NOT NULL DEFAULT '' COMMENT 'A generated token', `meta` varchar(255) NOT NULL DEFAULT '' COMMENT '', `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'datetime of creation', PRIMARY KEY(`id`), - INDEX `uid` (`uid`), - FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE + INDEX `uid` (`uid`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Store OpenWebAuth token to verify contacts'; -- @@ -1276,6 +1293,7 @@ CREATE TABLE IF NOT EXISTS `thread` ( INDEX `uid_wall_received` (`uid`,`wall`,`received`), INDEX `private_wall_origin_commented` (`private`,`wall`,`origin`,`commented`), INDEX `uri-id` (`uri-id`), + FOREIGN KEY (`iid`) REFERENCES `item` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Thread related data'; @@ -1431,13 +1449,15 @@ CREATE VIEW `network-item-view` AS SELECT `item`.`network` AS `network`, `item`.`unseen` AS `unseen`, `item`.`gravity` AS `gravity`, - `item`.`contact-id` AS `contact-id` + `item`.`contact-id` AS `contact-id`, + `ownercontact`.`contact-type` AS `contact-type` FROM `item` INNER JOIN `thread` ON `thread`.`iid` = `item`.`parent` STRAIGHT_JOIN `contact` ON `contact`.`id` = `thread`.`contact-id` LEFT JOIN `user-item` ON `user-item`.`iid` = `item`.`id` AND `user-item`.`uid` = `thread`.`uid` LEFT JOIN `user-contact` AS `author` ON `author`.`uid` = `thread`.`uid` AND `author`.`cid` = `thread`.`author-id` LEFT JOIN `user-contact` AS `owner` ON `owner`.`uid` = `thread`.`uid` AND `owner`.`cid` = `thread`.`owner-id` + LEFT JOIN `contact` AS `ownercontact` ON `ownercontact`.`id` = `thread`.`owner-id` WHERE `thread`.`visible` AND NOT `thread`.`deleted` AND NOT `thread`.`moderated` AND (NOT `contact`.`readonly` AND NOT `contact`.`blocked` AND NOT `contact`.`pending`) AND (`user-item`.`hidden` IS NULL OR NOT `user-item`.`hidden`) @@ -1460,13 +1480,15 @@ CREATE VIEW `network-thread-view` AS SELECT `thread`.`starred` AS `starred`, `thread`.`mention` AS `mention`, `thread`.`network` AS `network`, - `thread`.`contact-id` AS `contact-id` + `thread`.`contact-id` AS `contact-id`, + `ownercontact`.`contact-type` AS `contact-type` FROM `thread` STRAIGHT_JOIN `contact` ON `contact`.`id` = `thread`.`contact-id` STRAIGHT_JOIN `item` ON `item`.`id` = `thread`.`iid` LEFT JOIN `user-item` ON `user-item`.`iid` = `item`.`id` AND `user-item`.`uid` = `thread`.`uid` LEFT JOIN `user-contact` AS `author` ON `author`.`uid` = `thread`.`uid` AND `author`.`cid` = `thread`.`author-id` LEFT JOIN `user-contact` AS `owner` ON `owner`.`uid` = `thread`.`uid` AND `owner`.`cid` = `thread`.`owner-id` + LEFT JOIN `contact` AS `ownercontact` ON `ownercontact`.`id` = `thread`.`owner-id` WHERE `thread`.`visible` AND NOT `thread`.`deleted` AND NOT `thread`.`moderated` AND (NOT `contact`.`readonly` AND NOT `contact`.`blocked` AND NOT `contact`.`pending`) AND (`user-item`.`hidden` IS NULL OR NOT `user-item`.`hidden`) @@ -1537,6 +1559,7 @@ CREATE VIEW `owner-view` AS SELECT `contact`.`forum` AS `forum`, `contact`.`prv` AS `prv`, `contact`.`contact-type` AS `contact-type`, + `contact`.`manually-approve` AS `manually-approve`, `contact`.`hidden` AS `hidden`, `contact`.`archive` AS `archive`, `contact`.`pending` AS `pending`,