X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=8fa20be52c464b295f517d337ad9d7805a8459cc;hb=01558d6dda2fa18cdfd1860e53f60e0ccef62905;hp=ab8c300a88ad30383cfd70e8f1ca384b65706825;hpb=b4661b35fdf431cfc5d71d554692cc61d9e80219;p=friendica.git diff --git a/database.sql b/database.sql index ab8c300a88..8fa20be52c 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ -- Friendica 2022.12-dev (Giant Rhubarb) --- DB_UPDATE_VERSION 1486 +-- DB_UPDATE_VERSION 1491 -- ------------------------------------------ @@ -80,7 +80,7 @@ CREATE TABLE IF NOT EXISTS `user` ( `pwdreset` varchar(255) COMMENT 'Password reset request token', `pwdreset_time` datetime COMMENT 'Timestamp of the last password reset request', `maxreq` int unsigned NOT NULL DEFAULT 10 COMMENT '', - `expire` int unsigned NOT NULL DEFAULT 0 COMMENT '', + `expire` int unsigned NOT NULL DEFAULT 0 COMMENT 'Delay in days before deleting user-related posts. Scope is controlled by pConfig.', `account_removed` boolean NOT NULL DEFAULT '0' COMMENT 'if 1 the account is removed', `account_expired` boolean NOT NULL DEFAULT '0' COMMENT '', `account_expires_on` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'timestamp when account expires and will be deleted', @@ -1577,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 '', @@ -1645,6 +1646,35 @@ CREATE TABLE IF NOT EXISTS `register` ( FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='registrations requiring admin approval'; +-- +-- TABLE report +-- +CREATE TABLE IF NOT EXISTS `report` ( + `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', + `uid` mediumint unsigned COMMENT 'Reporting user', + `cid` int unsigned NOT NULL COMMENT 'Reported contact', + `comment` text COMMENT 'Report', + `forward` boolean COMMENT 'Forward the report to the remote server', + `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', + PRIMARY KEY(`id`), + INDEX `uid` (`uid`), + INDEX `cid` (`cid`), + FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE +) DEFAULT COLLATE utf8mb4_general_ci COMMENT=''; + +-- +-- TABLE report-post +-- +CREATE TABLE IF NOT EXISTS `report-post` ( + `rid` int unsigned NOT NULL COMMENT 'Report id', + `uri-id` int unsigned NOT NULL COMMENT 'Uri-id of the reported post', + PRIMARY KEY(`rid`,`uri-id`), + INDEX `uri-id` (`uri-id`), + FOREIGN KEY (`rid`) REFERENCES `report` (`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=''; + -- -- TABLE search -- @@ -1952,9 +1982,7 @@ CREATE VIEW `post-user-view` AS SELECT `parent-post-author`.`url` AS `parent-author-link`, `parent-post-author`.`name` AS `parent-author-name`, `parent-post-author`.`nick` AS `parent-author-nick`, - `parent-post-author`.`network` AS `parent-author-network`, - `parent-post-author`.`blocked` AS `parent-author-blocked`, - `parent-post-author`.`hidden` AS `parent-author-hidden` + `parent-post-author`.`network` AS `parent-author-network` FROM `post-user` STRAIGHT_JOIN `post-thread-user` ON `post-thread-user`.`uri-id` = `post-user`.`parent-uri-id` AND `post-thread-user`.`uid` = `post-user`.`uid` STRAIGHT_JOIN `contact` ON `contact`.`id` = `post-user`.`contact-id` @@ -2131,9 +2159,7 @@ CREATE VIEW `post-thread-user-view` AS SELECT `parent-post`.`author-id` AS `parent-author-id`, `parent-post-author`.`url` AS `parent-author-link`, `parent-post-author`.`name` AS `parent-author-name`, - `parent-post-author`.`network` AS `parent-author-network`, - `parent-post-author`.`blocked` AS `parent-author-blocked`, - `parent-post-author`.`hidden` AS `parent-author-hidden` + `parent-post-author`.`network` AS `parent-author-network` FROM `post-thread-user` INNER JOIN `post-user` ON `post-user`.`id` = `post-thread-user`.`post-user-id` STRAIGHT_JOIN `contact` ON `contact`.`id` = `post-thread-user`.`contact-id` @@ -2277,9 +2303,7 @@ CREATE VIEW `post-view` AS SELECT `parent-post`.`author-id` AS `parent-author-id`, `parent-post-author`.`url` AS `parent-author-link`, `parent-post-author`.`name` AS `parent-author-name`, - `parent-post-author`.`network` AS `parent-author-network`, - `parent-post-author`.`blocked` AS `parent-author-blocked`, - `parent-post-author`.`hidden` AS `parent-author-hidden` + `parent-post-author`.`network` AS `parent-author-network` FROM `post` STRAIGHT_JOIN `post-thread` ON `post-thread`.`uri-id` = `post`.`parent-uri-id` STRAIGHT_JOIN `contact` AS `author` ON `author`.`id` = `post`.`author-id` @@ -2419,9 +2443,7 @@ CREATE VIEW `post-thread-view` AS SELECT `parent-post`.`author-id` AS `parent-author-id`, `parent-post-author`.`url` AS `parent-author-link`, `parent-post-author`.`name` AS `parent-author-name`, - `parent-post-author`.`network` AS `parent-author-network`, - `parent-post-author`.`blocked` AS `parent-author-blocked`, - `parent-post-author`.`hidden` AS `parent-author-hidden` + `parent-post-author`.`network` AS `parent-author-network` FROM `post-thread` INNER JOIN `post` ON `post`.`uri-id` = `post-thread`.`uri-id` STRAIGHT_JOIN `contact` AS `author` ON `author`.`id` = `post-thread`.`author-id` @@ -2674,6 +2696,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`