X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=4e940b3197f9d3dc9f48abc91f84fbef337a6d3b;hb=7d86cb1a1e4ef775b41216a1374a93d892c264d8;hp=891b830a4391354c50940296c4f7a86a4bd1f512;hpb=a98d3213767392dcce399188f65b5f976caf98e6;p=friendica.git diff --git a/database.sql b/database.sql index 891b830a43..4e940b3197 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ -- Friendica 2022.12-dev (Giant Rhubarb) --- DB_UPDATE_VERSION 1487 +-- DB_UPDATE_VERSION 1495 -- ------------------------------------------ @@ -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', @@ -1646,6 +1646,37 @@ 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 '', + `status` tinyint unsigned COMMENT 'Status of the report', + 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', + `status` tinyint unsigned COMMENT 'Status 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 -- @@ -1953,9 +1984,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` @@ -2132,9 +2161,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` @@ -2278,9 +2305,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` @@ -2414,15 +2439,15 @@ CREATE VIEW `post-thread-view` AS SELECT `post-question`.`end-time` AS `question-end-time`, 0 AS `has-categories`, EXISTS(SELECT `id` FROM `post-media` WHERE `post-media`.`uri-id` = `post-thread`.`uri-id`) AS `has-media`, + (SELECT COUNT(*) FROM `post` WHERE `parent-uri-id` = `post-thread`.`uri-id` AND `gravity` = 6) AS `total-comments`, + (SELECT COUNT(DISTINCT(`author-id`)) FROM `post` WHERE `parent-uri-id` = `post-thread`.`uri-id` AND `gravity` = 6) AS `total-actors`, `diaspora-interaction`.`interaction` AS `signed_text`, `parent-item-uri`.`guid` AS `parent-guid`, `parent-post`.`network` AS `parent-network`, `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` @@ -2476,6 +2501,24 @@ CREATE VIEW `collection-view` AS SELECT INNER JOIN `post` ON `post-collection`.`uri-id` = `post`.`uri-id` INNER JOIN `post-thread` ON `post-thread`.`uri-id` = `post`.`parent-uri-id`; +-- +-- VIEW media-view +-- +DROP VIEW IF EXISTS `media-view`; +CREATE VIEW `media-view` AS SELECT + `post-media`.`uri-id` AS `uri-id`, + `post-media`.`type` AS `type`, + `post`.`received` AS `received`, + `post`.`created` AS `created`, + `post`.`private` AS `private`, + `post`.`visible` AS `visible`, + `post`.`deleted` AS `deleted`, + `post`.`thr-parent-id` AS `thr-parent-id`, + `post`.`author-id` AS `author-id`, + `post`.`gravity` AS `gravity` + FROM `post-media` + INNER JOIN `post` ON `post-media`.`uri-id` = `post`.`uri-id`; + -- -- VIEW tag-view --