X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=7eb756cf2a3df73aa5140d9cba7c98e18251c7b8;hb=954b95b039ccd481550438a98bc592ce8f08fe96;hp=cf07937725e165010d8d86d6899fda9c108be941;hpb=f31e617f5d7a0908e82e8f62bf415635a02c57b1;p=friendica.git diff --git a/database.sql b/database.sql index cf07937725..7eb756cf2a 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ --- Friendica 2022.12-dev (Giant Rhubarb) --- DB_UPDATE_VERSION 1496 +-- Friendica 2023.03-dev (Giant Rhubarb) +-- DB_UPDATE_VERSION 1504 -- ------------------------------------------ @@ -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', @@ -128,6 +129,7 @@ CREATE TABLE IF NOT EXISTS `contact` ( `xmpp` varchar(255) NOT NULL DEFAULT '' COMMENT 'XMPP address', `matrix` varchar(255) NOT NULL DEFAULT '' COMMENT 'Matrix address', `avatar` varbinary(383) NOT NULL DEFAULT '' COMMENT '', + `blurhash` varbinary(255) COMMENT 'BlurHash representation of the avatar', `header` varbinary(383) COMMENT 'Header picture', `url` varbinary(383) NOT NULL DEFAULT '' COMMENT '', `nurl` varbinary(383) NOT NULL DEFAULT '' COMMENT '', @@ -577,6 +579,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 -- @@ -632,39 +668,6 @@ CREATE TABLE IF NOT EXISTS `event` ( FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Events'; --- --- TABLE fcontact --- -CREATE TABLE IF NOT EXISTS `fcontact` ( - `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', - `guid` varbinary(255) NOT NULL DEFAULT '' COMMENT 'unique id', - `url` varbinary(383) NOT NULL DEFAULT '' COMMENT '', - `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the fcontact url', - `name` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `photo` varbinary(383) NOT NULL DEFAULT '' COMMENT '', - `request` varbinary(383) NOT NULL DEFAULT '' COMMENT '', - `nick` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `addr` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `batch` varbinary(383) NOT NULL DEFAULT '' COMMENT '', - `notify` varbinary(383) NOT NULL DEFAULT '' COMMENT '', - `poll` varbinary(383) NOT NULL DEFAULT '' COMMENT '', - `confirm` varbinary(383) NOT NULL DEFAULT '' COMMENT '', - `priority` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', - `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', - `post_count` int unsigned DEFAULT 0 COMMENT 'Number of posts and comments', - PRIMARY KEY(`id`), - INDEX `addr` (`addr`(32)), - UNIQUE INDEX `url` (`url`(190)), - UNIQUE INDEX `uri-id` (`uri-id`), - FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Diaspora compatible contacts - used in the Diaspora implementation'; - -- -- TABLE fetch-entry -- @@ -1087,6 +1090,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 '', @@ -1294,6 +1298,9 @@ CREATE TABLE IF NOT EXISTS `post-link` ( `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', `url` varbinary(511) NOT NULL COMMENT 'External URL', `mimetype` varchar(60) COMMENT '', + `height` smallint unsigned COMMENT 'Height of the media', + `width` smallint unsigned COMMENT 'Width of the media', + `blurhash` varbinary(255) COMMENT 'BlurHash representation of the link', PRIMARY KEY(`id`), UNIQUE INDEX `uri-id-url` (`uri-id`,`url`), FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE @@ -1312,6 +1319,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', @@ -1431,7 +1439,7 @@ CREATE TABLE IF NOT EXISTS `post-user` ( `event-id` int unsigned COMMENT 'Used to link to the event.id', `unseen` boolean NOT NULL DEFAULT '1' COMMENT 'post has not been seen', `hidden` boolean NOT NULL DEFAULT '0' COMMENT 'Marker to hide the post from the user', - `notification-type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', + `notification-type` smallint unsigned NOT NULL DEFAULT 0 COMMENT '', `wall` boolean NOT NULL DEFAULT '0' COMMENT 'This item was posted to the wall of uid', `origin` boolean NOT NULL DEFAULT '0' COMMENT 'item originated at this site', `psid` int unsigned COMMENT 'ID of the permission set of this post', @@ -1666,15 +1674,20 @@ CREATE TABLE IF NOT EXISTS `register` ( CREATE TABLE IF NOT EXISTS `report` ( `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', `uid` mediumint unsigned COMMENT 'Reporting user', + `reporter-id` int unsigned COMMENT 'Reporting contact', `cid` int unsigned NOT NULL COMMENT 'Reported contact', `comment` text COMMENT 'Report', + `category` varchar(20) COMMENT 'Category of the report (spam, violation, other)', + `rules` text COMMENT 'Violated rules', `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`), + INDEX `reporter-id` (`reporter-id`), FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`reporter-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (`cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT=''; @@ -2692,6 +2705,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`, @@ -2798,11 +2812,11 @@ CREATE VIEW `account-view` AS SELECT `contact`.`blocked` AS `blocked`, `contact`.`notify` AS `dfrn-notify`, `contact`.`poll` AS `dfrn-poll`, - `fcontact`.`guid` AS `diaspora-guid`, - `fcontact`.`batch` AS `diaspora-batch`, - `fcontact`.`notify` AS `diaspora-notify`, - `fcontact`.`poll` AS `diaspora-poll`, - `fcontact`.`alias` AS `diaspora-alias`, + `item-uri`.`guid` AS `diaspora-guid`, + `diaspora-contact`.`batch` AS `diaspora-batch`, + `diaspora-contact`.`notify` AS `diaspora-notify`, + `diaspora-contact`.`poll` AS `diaspora-poll`, + `diaspora-contact`.`alias` AS `diaspora-alias`, `apcontact`.`uuid` AS `ap-uuid`, `apcontact`.`type` AS `ap-type`, `apcontact`.`following` AS `ap-following`, @@ -2820,7 +2834,7 @@ CREATE VIEW `account-view` AS SELECT FROM `contact` LEFT JOIN `item-uri` ON `item-uri`.`id` = `contact`.`uri-id` LEFT JOIN `apcontact` ON `apcontact`.`uri-id` = `contact`.`uri-id` - LEFT JOIN `fcontact` ON `fcontact`.`uri-id` = contact.`uri-id` + LEFT JOIN `diaspora-contact` ON `diaspora-contact`.`uri-id` = contact.`uri-id` LEFT JOIN `gserver` ON `gserver`.`id` = contact.`gsid` WHERE `contact`.`uid` = 0; @@ -2899,14 +2913,14 @@ CREATE VIEW `account-user-view` AS SELECT `ucontact`.`reason` AS `reason`, `contact`.`notify` AS `dfrn-notify`, `contact`.`poll` AS `dfrn-poll`, - `fcontact`.`guid` AS `diaspora-guid`, - `fcontact`.`batch` AS `diaspora-batch`, - `fcontact`.`notify` AS `diaspora-notify`, - `fcontact`.`poll` AS `diaspora-poll`, - `fcontact`.`alias` AS `diaspora-alias`, - `fcontact`.`interacting_count` AS `diaspora-interacting_count`, - `fcontact`.`interacted_count` AS `diaspora-interacted_count`, - `fcontact`.`post_count` AS `diaspora-post_count`, + `item-uri`.`guid` AS `diaspora-guid`, + `diaspora-contact`.`batch` AS `diaspora-batch`, + `diaspora-contact`.`notify` AS `diaspora-notify`, + `diaspora-contact`.`poll` AS `diaspora-poll`, + `diaspora-contact`.`alias` AS `diaspora-alias`, + `diaspora-contact`.`interacting_count` AS `diaspora-interacting_count`, + `diaspora-contact`.`interacted_count` AS `diaspora-interacted_count`, + `diaspora-contact`.`post_count` AS `diaspora-post_count`, `apcontact`.`uuid` AS `ap-uuid`, `apcontact`.`type` AS `ap-type`, `apcontact`.`following` AS `ap-following`, @@ -2925,7 +2939,7 @@ CREATE VIEW `account-user-view` AS SELECT INNER JOIN `contact` ON `contact`.`uri-id` = `ucontact`.`uri-id` AND `contact`.`uid` = 0 LEFT JOIN `item-uri` ON `item-uri`.`id` = `ucontact`.`uri-id` LEFT JOIN `apcontact` ON `apcontact`.`uri-id` = `ucontact`.`uri-id` - LEFT JOIN `fcontact` ON `fcontact`.`uri-id` = `ucontact`.`uri-id` AND `fcontact`.`network` = 'dspr' + LEFT JOIN `diaspora-contact` ON `diaspora-contact`.`uri-id` = `ucontact`.`uri-id` LEFT JOIN `gserver` ON `gserver`.`id` = contact.`gsid`; -- @@ -3002,3 +3016,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`;