X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=35831813469b5397b64fb9a626f30cca2184a7da;hb=d5e005f90dd81ec4da8cab33c3473b8f74d5b1a8;hp=891b830a4391354c50940296c4f7a86a4bd1f512;hpb=6cb6841866d3111e1f7ec4222cd555dd4ac9ba28;p=friendica.git diff --git a/database.sql b/database.sql index 891b830a43..3583181346 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ -- Friendica 2022.12-dev (Giant Rhubarb) --- DB_UPDATE_VERSION 1487 +-- DB_UPDATE_VERSION 1498 -- ------------------------------------------ @@ -59,6 +59,7 @@ CREATE TABLE IF NOT EXISTS `user` ( `language` varchar(32) NOT NULL DEFAULT 'en' COMMENT 'default language', `register_date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'timestamp of registration', `login_date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'timestamp of last login', + `last-activity` date COMMENT 'Day of the last activity', `default-location` varchar(255) NOT NULL DEFAULT '' COMMENT 'Default for item.location', `allow_location` boolean NOT NULL DEFAULT '0' COMMENT '1 allows to display the location', `theme` varchar(255) NOT NULL DEFAULT '' COMMENT 'user theme preference', @@ -80,7 +81,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', @@ -309,6 +310,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-suggestion +-- +CREATE TABLE IF NOT EXISTS `account-suggestion` ( + `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', + `level` smallint unsigned COMMENT 'level of closeness', + `ignore` boolean NOT NULL DEFAULT '0' COMMENT 'If set, this account will not be suggested again', + PRIMARY KEY(`uid`,`uri-id`), + INDEX `uri-id_uid` (`uri-id`,`uid`), + 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='Account suggestion'; + -- -- TABLE account-user -- @@ -563,6 +578,40 @@ 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 diaspora-contact +-- +CREATE TABLE IF NOT EXISTS `diaspora-contact` ( + `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the contact URL', + `addr` varchar(255) COMMENT '', + `alias` varchar(255) COMMENT '', + `nick` varchar(255) COMMENT '', + `name` varchar(255) COMMENT '', + `given-name` varchar(255) COMMENT '', + `family-name` varchar(255) COMMENT '', + `photo` varchar(255) COMMENT '', + `photo-medium` varchar(255) COMMENT '', + `photo-small` varchar(255) COMMENT '', + `batch` varchar(255) COMMENT '', + `notify` varchar(255) COMMENT '', + `poll` varchar(255) COMMENT '', + `subscribe` varchar(255) COMMENT '', + `searchable` boolean COMMENT '', + `pubkey` text COMMENT '', + `gsid` int unsigned COMMENT 'Global Server ID', + `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(`uri-id`), + UNIQUE INDEX `addr` (`addr`), + INDEX `alias` (`alias`), + 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='Diaspora compatible contacts - used in the Diaspora implementation'; + -- -- TABLE diaspora-interaction -- @@ -1073,6 +1122,7 @@ CREATE TABLE IF NOT EXISTS `photo` ( `height` smallint unsigned NOT NULL DEFAULT 0 COMMENT '', `width` smallint unsigned NOT NULL DEFAULT 0 COMMENT '', `datasize` int unsigned NOT NULL DEFAULT 0 COMMENT '', + `blurhash` varbinary(255) COMMENT 'BlurHash representation of the photo', `data` mediumblob NOT NULL COMMENT '', `scale` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', `profile` boolean NOT NULL DEFAULT '0' COMMENT '', @@ -1298,6 +1348,7 @@ CREATE TABLE IF NOT EXISTS `post-media` ( `height` smallint unsigned COMMENT 'Height of the media', `width` smallint unsigned COMMENT 'Width of the media', `size` bigint unsigned COMMENT 'Media size', + `blurhash` varbinary(255) COMMENT 'BlurHash representation of the image', `preview` varbinary(512) COMMENT 'Preview URL', `preview-height` smallint unsigned COMMENT 'Height of the preview picture', `preview-width` smallint unsigned COMMENT 'Width of the preview picture', @@ -1646,6 +1697,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 +2035,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 +2212,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 +2356,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 +2490,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 +2552,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 -- @@ -2635,6 +2729,7 @@ CREATE VIEW `owner-view` AS SELECT `user`.`language` AS `language`, `user`.`register_date` AS `register_date`, `user`.`login_date` AS `login_date`, + `user`.`last-activity` AS `last-activity`, `user`.`default-location` AS `default-location`, `user`.`allow_location` AS `allow_location`, `user`.`theme` AS `theme`, @@ -2945,3 +3040,37 @@ CREATE VIEW `profile_field-view` AS SELECT `profile_field`.`edited` AS `edited` FROM `profile_field` INNER JOIN `permissionset` ON `permissionset`.`id` = `profile_field`.`psid`; + +-- +-- VIEW diaspora-contact-view +-- +DROP VIEW IF EXISTS `diaspora-contact-view`; +CREATE VIEW `diaspora-contact-view` AS SELECT + `diaspora-contact`.`uri-id` AS `uri-id`, + `item-uri`.`uri` AS `url`, + `item-uri`.`guid` AS `guid`, + `diaspora-contact`.`addr` AS `addr`, + `diaspora-contact`.`alias` AS `alias`, + `diaspora-contact`.`nick` AS `nick`, + `diaspora-contact`.`name` AS `name`, + `diaspora-contact`.`given-name` AS `given-name`, + `diaspora-contact`.`family-name` AS `family-name`, + `diaspora-contact`.`photo` AS `photo`, + `diaspora-contact`.`photo-medium` AS `photo-medium`, + `diaspora-contact`.`photo-small` AS `photo-small`, + `diaspora-contact`.`batch` AS `batch`, + `diaspora-contact`.`notify` AS `notify`, + `diaspora-contact`.`poll` AS `poll`, + `diaspora-contact`.`subscribe` AS `subscribe`, + `diaspora-contact`.`searchable` AS `searchable`, + `diaspora-contact`.`pubkey` AS `pubkey`, + `gserver`.`url` AS `baseurl`, + `diaspora-contact`.`gsid` AS `gsid`, + `diaspora-contact`.`created` AS `created`, + `diaspora-contact`.`updated` AS `updated`, + `diaspora-contact`.`interacting_count` AS `interacting_count`, + `diaspora-contact`.`interacted_count` AS `interacted_count`, + `diaspora-contact`.`post_count` AS `post_count` + FROM `diaspora-contact` + INNER JOIN `item-uri` ON `item-uri`.`id` = `diaspora-contact`.`uri-id` + LEFT JOIN `gserver` ON `gserver`.`id` = `diaspora-contact`.`gsid`;