X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=35831813469b5397b64fb9a626f30cca2184a7da;hb=d5e005f90dd81ec4da8cab33c3473b8f74d5b1a8;hp=cf07937725e165010d8d86d6899fda9c108be941;hpb=f31e617f5d7a0908e82e8f62bf415635a02c57b1;p=friendica.git diff --git a/database.sql b/database.sql index cf07937725..3583181346 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ -- Friendica 2022.12-dev (Giant Rhubarb) --- DB_UPDATE_VERSION 1496 +-- 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', @@ -577,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 -- @@ -1087,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 '', @@ -1312,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', @@ -2692,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`, @@ -3002,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`;