X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=855c913580d6781114a1f4729ff74c143f31febc;hb=fc1ef084f435edf26ea81a1fd43e8a49c2254d97;hp=51c377bad76847fe902594fd89270c338b3ff561;hpb=5c5ae00370b30e74d56cf37e27445c82db65e201;p=friendica.git diff --git a/database.sql b/database.sql index 51c377bad7..855c913580 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ --- Friendica 2022.09-dev (Giant Rhubarb) --- DB_UPDATE_VERSION 1480 +-- Friendica 2022.12-rc (Giant Rhubarb) +-- DB_UPDATE_VERSION 1500 -- ------------------------------------------ @@ -9,8 +9,8 @@ -- CREATE TABLE IF NOT EXISTS `gserver` ( `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', - `url` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `nurl` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `url` varbinary(383) NOT NULL DEFAULT '' COMMENT '', + `nurl` varbinary(383) NOT NULL DEFAULT '' COMMENT '', `version` varchar(255) NOT NULL DEFAULT '' COMMENT '', `site_name` varchar(255) NOT NULL DEFAULT '' COMMENT '', `info` text COMMENT '', @@ -22,8 +22,8 @@ CREATE TABLE IF NOT EXISTS `gserver` ( `local-posts` int unsigned COMMENT 'Number of local posts', `local-comments` int unsigned COMMENT 'Number of local comments', `directory-type` tinyint DEFAULT 0 COMMENT 'Type of directory service (Poco, Mastodon)', - `poco` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `noscrape` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `poco` varbinary(383) NOT NULL DEFAULT '' COMMENT '', + `noscrape` varbinary(383) NOT NULL DEFAULT '' COMMENT '', `network` char(4) NOT NULL DEFAULT '' COMMENT '', `protocol` tinyint unsigned COMMENT 'The protocol of the server', `platform` varchar(255) NOT NULL DEFAULT '' COMMENT '', @@ -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', @@ -104,7 +105,7 @@ CREATE TABLE IF NOT EXISTS `user` ( -- CREATE TABLE IF NOT EXISTS `item-uri` ( `id` int unsigned NOT NULL auto_increment, - `uri` varbinary(255) NOT NULL COMMENT 'URI of an item', + `uri` varbinary(383) NOT NULL COMMENT 'URI of an item', `guid` varbinary(255) COMMENT 'A unique identifier for an item', PRIMARY KEY(`id`), UNIQUE INDEX `uri` (`uri`), @@ -127,19 +128,19 @@ CREATE TABLE IF NOT EXISTS `contact` ( `keywords` text COMMENT 'public keywords (interests) of the contact', `xmpp` varchar(255) NOT NULL DEFAULT '' COMMENT 'XMPP address', `matrix` varchar(255) NOT NULL DEFAULT '' COMMENT 'Matrix address', - `avatar` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `header` varchar(255) COMMENT 'Header picture', - `url` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `nurl` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `avatar` varbinary(383) NOT NULL DEFAULT '' COMMENT '', + `header` varbinary(383) COMMENT 'Header picture', + `url` varbinary(383) NOT NULL DEFAULT '' COMMENT '', + `nurl` varbinary(383) NOT NULL DEFAULT '' COMMENT '', `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the contact url', `addr` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `alias` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `alias` varbinary(383) NOT NULL DEFAULT '' COMMENT '', `pubkey` text COMMENT 'RSA public key 4096 bit', `prvkey` text COMMENT 'RSA private key 4096 bit', - `batch` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `notify` varchar(255) COMMENT '', - `poll` varchar(255) COMMENT '', - `subscribe` varchar(255) COMMENT '', + `batch` varbinary(383) NOT NULL DEFAULT '' COMMENT '', + `notify` varbinary(383) COMMENT '', + `poll` varbinary(383) COMMENT '', + `subscribe` varbinary(383) COMMENT '', `last-update` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last try to update the contact info', `next-update` datetime COMMENT 'Next connection request', `success_update` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last successful contact update', @@ -157,7 +158,7 @@ CREATE TABLE IF NOT EXISTS `contact` ( `archive` boolean NOT NULL DEFAULT '0' COMMENT '', `unsearchable` boolean NOT NULL DEFAULT '0' COMMENT 'Contact prefers to not be searchable', `sensitive` boolean NOT NULL DEFAULT '0' COMMENT 'Contact posts sensitive content', - `baseurl` varchar(255) DEFAULT '' COMMENT 'baseurl of the contact', + `baseurl` varbinary(383) DEFAULT '' COMMENT 'baseurl of the contact', `gsid` int unsigned COMMENT 'Global Server ID', `bd` date NOT NULL DEFAULT '0001-01-01' COMMENT '', `reason` text COMMENT '', @@ -166,7 +167,7 @@ CREATE TABLE IF NOT EXISTS `contact` ( `rel` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'The kind of the relation between the user and the contact', `protocol` char(4) NOT NULL DEFAULT '' COMMENT 'Protocol of the contact', `subhub` boolean NOT NULL DEFAULT '0' COMMENT '', - `hub-verify` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `hub-verify` varbinary(383) NOT NULL DEFAULT '' COMMENT '', `rating` tinyint NOT NULL DEFAULT 0 COMMENT 'Automatically detected feed poll frequency', `priority` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'Feed poll priority', `attag` varchar(255) NOT NULL DEFAULT '' COMMENT '', @@ -177,15 +178,15 @@ CREATE TABLE IF NOT EXISTS `contact` ( `notify_new_posts` boolean NOT NULL DEFAULT '0' COMMENT '', `fetch_further_information` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', `ffi_keyword_denylist` text COMMENT '', - `photo` varchar(255) DEFAULT '' COMMENT 'Link to the profile photo of the contact', - `thumb` varchar(255) DEFAULT '' COMMENT 'Link to the profile photo (thumb size)', - `micro` varchar(255) DEFAULT '' COMMENT 'Link to the profile photo (micro size)', + `photo` varbinary(383) DEFAULT '' COMMENT 'Link to the profile photo of the contact', + `thumb` varbinary(383) DEFAULT '' COMMENT 'Link to the profile photo (thumb size)', + `micro` varbinary(383) DEFAULT '' COMMENT 'Link to the profile photo (micro size)', `name-date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', `uri-date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', `avatar-date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', - `request` varchar(255) COMMENT '', - `confirm` varchar(255) COMMENT '', - `poco` varchar(255) COMMENT '', + `request` varbinary(383) COMMENT '', + `confirm` varbinary(383) COMMENT '', + `poco` varbinary(383) COMMENT '', `writable` boolean NOT NULL DEFAULT '0' COMMENT '', `forum` boolean NOT NULL DEFAULT '0' COMMENT 'contact is a forum. Deprecated, use \'contact-type\' = \'community\' and \'manually-approve\' = false instead', `prv` boolean NOT NULL DEFAULT '0' COMMENT 'contact is a private group. Deprecated, use \'contact-type\' = \'community\' and \'manually-approve\' = true instead', @@ -193,8 +194,8 @@ CREATE TABLE IF NOT EXISTS `contact` ( `site-pubkey` text COMMENT 'Deprecated', `gender` varchar(32) NOT NULL DEFAULT '' COMMENT 'Deprecated', `duplex` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', - `issued-id` varchar(255) NOT NULL DEFAULT '' COMMENT 'Deprecated', - `dfrn-id` varchar(255) NOT NULL DEFAULT '' COMMENT 'Deprecated', + `issued-id` varbinary(383) NOT NULL DEFAULT '' COMMENT 'Deprecated', + `dfrn-id` varbinary(383) NOT NULL DEFAULT '' COMMENT 'Deprecated', `aes_allow` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', `ret-aes` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', `usehub` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated', @@ -222,7 +223,7 @@ CREATE TABLE IF NOT EXISTS `contact` ( INDEX `uid_contact-type` (`uid`,`contact-type`), INDEX `uid_self_contact-type` (`uid`,`self`,`contact-type`), INDEX `self_network_uid` (`self`,`network`,`uid`), - INDEX `gsid` (`gsid`), + INDEX `gsid_uid_failed` (`gsid`,`uid`,`failed`), INDEX `uri-id` (`uri-id`), FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, @@ -235,7 +236,7 @@ CREATE TABLE IF NOT EXISTS `contact` ( CREATE TABLE IF NOT EXISTS `tag` ( `id` int unsigned NOT NULL auto_increment COMMENT '', `name` varchar(96) NOT NULL DEFAULT '' COMMENT '', - `url` varbinary(255) NOT NULL DEFAULT '' COMMENT '', + `url` varbinary(383) NOT NULL DEFAULT '' COMMENT '', `type` tinyint unsigned COMMENT 'Type of the tag (Unknown, General Collection, Follower Collection or Account)', PRIMARY KEY(`id`), UNIQUE INDEX `type_name_url` (`name`,`url`), @@ -309,6 +310,34 @@ 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 +-- +CREATE TABLE IF NOT EXISTS `account-user` ( + `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', + `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', + PRIMARY KEY(`id`), + UNIQUE INDEX `uri-id_uid` (`uri-id`,`uid`), + INDEX `uid_uri-id` (`uid`,`uri-id`), + 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='Remote and local accounts'; + -- -- TABLE addon -- @@ -329,17 +358,17 @@ CREATE TABLE IF NOT EXISTS `addon` ( -- TABLE apcontact -- CREATE TABLE IF NOT EXISTS `apcontact` ( - `url` varbinary(255) NOT NULL COMMENT 'URL of the contact', + `url` varbinary(383) NOT NULL COMMENT 'URL of the contact', `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the apcontact url', - `uuid` varchar(255) COMMENT '', + `uuid` varbinary(255) COMMENT '', `type` varchar(20) NOT NULL COMMENT '', - `following` varchar(255) COMMENT '', - `followers` varchar(255) COMMENT '', - `inbox` varchar(255) NOT NULL COMMENT '', - `outbox` varchar(255) COMMENT '', - `sharedinbox` varchar(255) COMMENT '', - `featured` varchar(255) COMMENT 'Address for the collection of featured posts', - `featured-tags` varchar(255) COMMENT 'Address for the collection of featured tags', + `following` varbinary(383) COMMENT '', + `followers` varbinary(383) COMMENT '', + `inbox` varbinary(383) NOT NULL COMMENT '', + `outbox` varbinary(383) COMMENT '', + `sharedinbox` varbinary(383) COMMENT '', + `featured` varbinary(383) COMMENT 'Address for the collection of featured posts', + `featured-tags` varbinary(383) COMMENT 'Address for the collection of featured tags', `manually-approve` boolean COMMENT '', `discoverable` boolean COMMENT 'Mastodon extension: true if profile is published in their directory', `suspended` boolean COMMENT 'Mastodon extension: true if profile is suspended', @@ -348,13 +377,13 @@ CREATE TABLE IF NOT EXISTS `apcontact` ( `about` text COMMENT '', `xmpp` varchar(255) COMMENT 'XMPP address', `matrix` varchar(255) COMMENT 'Matrix address', - `photo` varchar(255) COMMENT '', - `header` varchar(255) COMMENT 'Header picture', + `photo` varbinary(383) COMMENT '', + `header` varbinary(383) COMMENT 'Header picture', `addr` varchar(255) COMMENT '', - `alias` varchar(255) COMMENT '', + `alias` varbinary(383) COMMENT '', `pubkey` text COMMENT '', - `subscribe` varchar(255) COMMENT '', - `baseurl` varchar(255) COMMENT 'baseurl of the ap contact', + `subscribe` varbinary(383) COMMENT '', + `baseurl` varbinary(383) COMMENT 'baseurl of the ap contact', `gsid` int unsigned COMMENT 'Global Server ID', `generator` varchar(255) COMMENT 'Name of the contact\'s system', `following_count` int unsigned DEFAULT 0 COMMENT 'Number of following contacts', @@ -381,8 +410,8 @@ CREATE TABLE IF NOT EXISTS `application` ( `client_id` varchar(64) NOT NULL COMMENT '', `client_secret` varchar(64) NOT NULL COMMENT '', `name` varchar(255) NOT NULL COMMENT '', - `redirect_uri` varchar(255) NOT NULL COMMENT '', - `website` varchar(255) COMMENT '', + `redirect_uri` varbinary(383) NOT NULL COMMENT '', + `website` varbinary(383) COMMENT '', `scopes` varchar(255) COMMENT '', `read` boolean COMMENT 'Read scope', `write` boolean COMMENT 'Write scope', @@ -399,7 +428,7 @@ CREATE TABLE IF NOT EXISTS `application-marker` ( `application-id` int unsigned NOT NULL COMMENT '', `uid` mediumint unsigned NOT NULL COMMENT 'Owner User id', `timeline` varchar(64) NOT NULL COMMENT 'Marker (home, notifications)', - `last_read_id` varchar(255) COMMENT 'Marker id for the timeline', + `last_read_id` varbinary(383) COMMENT 'Marker id for the timeline', `version` smallint unsigned COMMENT 'Version number', `updated_at` datetime COMMENT 'creation time', PRIMARY KEY(`application-id`,`uid`,`timeline`), @@ -496,7 +525,7 @@ CREATE TABLE IF NOT EXISTS `contact-relation` ( -- CREATE TABLE IF NOT EXISTS `conv` ( `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', - `guid` varchar(255) NOT NULL DEFAULT '' COMMENT 'A unique identifier for this conversation', + `guid` varbinary(255) NOT NULL DEFAULT '' COMMENT 'A unique identifier for this conversation', `recips` text COMMENT 'sender_handle;recipient_handle', `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner User id', `creator` varchar(255) NOT NULL DEFAULT '' COMMENT 'handle of creator', @@ -538,7 +567,7 @@ CREATE TABLE IF NOT EXISTS `workerqueue` ( -- CREATE TABLE IF NOT EXISTS `delayed-post` ( `id` int unsigned NOT NULL auto_increment, - `uri` varchar(255) COMMENT 'URI of the post that will be distributed later', + `uri` varbinary(383) COMMENT 'URI of the post that will be distributed later', `uid` mediumint unsigned COMMENT 'Owner User id', `delayed` datetime COMMENT 'delay time', `wid` int unsigned COMMENT 'Workerqueue id', @@ -549,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 -- @@ -563,7 +626,7 @@ CREATE TABLE IF NOT EXISTS `diaspora-interaction` ( -- TABLE endpoint -- CREATE TABLE IF NOT EXISTS `endpoint` ( - `url` varbinary(255) NOT NULL COMMENT 'URL of the contact', + `url` varbinary(383) NOT NULL COMMENT 'URL of the contact', `type` varchar(20) NOT NULL COMMENT '', `owner-uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the apcontact url', PRIMARY KEY(`url`), @@ -576,10 +639,10 @@ CREATE TABLE IF NOT EXISTS `endpoint` ( -- CREATE TABLE IF NOT EXISTS `event` ( `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', - `guid` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `guid` varbinary(255) NOT NULL DEFAULT '' COMMENT '', `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner User id', `cid` int unsigned NOT NULL DEFAULT 0 COMMENT 'contact_id (ID of the contact in contact table)', - `uri` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `uri` varbinary(383) NOT NULL DEFAULT '' COMMENT '', `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the event uri', `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'creation time', `edited` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'last edit time', @@ -604,44 +667,12 @@ 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` varchar(255) NOT NULL DEFAULT '' COMMENT 'unique id', - `url` varchar(255) 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` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `request` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `nick` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `addr` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `batch` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `notify` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `poll` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `confirm` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `priority` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', - `network` char(4) NOT NULL DEFAULT '' COMMENT '', - `alias` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `pubkey` text 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 -- CREATE TABLE IF NOT EXISTS `fetch-entry` ( `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', - `url` varbinary(255) COMMENT 'url that awaiting to be fetched', + `url` varbinary(383) COMMENT 'url that awaiting to be fetched', `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Creation date of the fetch request', `wid` int unsigned COMMENT 'Workerqueue id', PRIMARY KEY(`id`), @@ -659,9 +690,9 @@ CREATE TABLE IF NOT EXISTS `fsuggest` ( `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id', `cid` int unsigned NOT NULL DEFAULT 0 COMMENT '', `name` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `url` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `request` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `photo` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `url` varbinary(383) NOT NULL DEFAULT '' COMMENT '', + `request` varbinary(383) NOT NULL DEFAULT '' COMMENT '', + `photo` varbinary(383) NOT NULL DEFAULT '' COMMENT '', `note` text COMMENT '', `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', PRIMARY KEY(`id`), @@ -732,10 +763,10 @@ CREATE TABLE IF NOT EXISTS `hook` ( -- CREATE TABLE IF NOT EXISTS `inbox-entry` ( `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', - `activity-id` varbinary(255) COMMENT 'id of the incoming activity', - `object-id` varbinary(255) COMMENT '', - `in-reply-to-id` varbinary(255) COMMENT '', - `conversation` varbinary(255) COMMENT '', + `activity-id` varbinary(383) COMMENT 'id of the incoming activity', + `object-id` varbinary(383) COMMENT '', + `in-reply-to-id` varbinary(383) COMMENT '', + `conversation` varbinary(383) COMMENT '', `type` varchar(64) COMMENT 'Type of the activity', `object-type` varchar(64) COMMENT 'Type of the object activity', `object-object-type` varchar(64) COMMENT 'Type of the object\'s object activity', @@ -769,7 +800,7 @@ CREATE TABLE IF NOT EXISTS `inbox-entry-receiver` ( -- TABLE inbox-status -- CREATE TABLE IF NOT EXISTS `inbox-status` ( - `url` varbinary(255) NOT NULL COMMENT 'URL of the inbox', + `url` varbinary(383) NOT NULL COMMENT 'URL of the inbox', `uri-id` int unsigned COMMENT 'Item-uri id of inbox url', `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Creation date of this entry', `success` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last successful delivery', @@ -794,7 +825,7 @@ CREATE TABLE IF NOT EXISTS `intro` ( `knowyou` boolean NOT NULL DEFAULT '0' COMMENT '', `duplex` boolean NOT NULL DEFAULT '0' COMMENT 'deprecated', `note` text COMMENT '', - `hash` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `hash` varbinary(255) NOT NULL DEFAULT '' COMMENT '', `datetime` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', `blocked` boolean NOT NULL DEFAULT '0' COMMENT 'deprecated', `ignore` boolean NOT NULL DEFAULT '0' COMMENT '', @@ -826,11 +857,11 @@ CREATE TABLE IF NOT EXISTS `locks` ( CREATE TABLE IF NOT EXISTS `mail` ( `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner User id', - `guid` varchar(255) NOT NULL DEFAULT '' COMMENT 'A unique identifier for this private message', + `guid` varbinary(255) NOT NULL DEFAULT '' COMMENT 'A unique identifier for this private message', `from-name` varchar(255) NOT NULL DEFAULT '' COMMENT 'name of the sender', - `from-photo` varchar(255) NOT NULL DEFAULT '' COMMENT 'contact photo link of the sender', - `from-url` varchar(255) NOT NULL DEFAULT '' COMMENT 'profile linke of the sender', - `contact-id` varchar(255) COMMENT 'contact.id', + `from-photo` varbinary(383) NOT NULL DEFAULT '' COMMENT 'contact photo link of the sender', + `from-url` varbinary(383) NOT NULL DEFAULT '' COMMENT 'profile linke of the sender', + `contact-id` varbinary(255) COMMENT 'contact.id', `author-id` int unsigned COMMENT 'Link to the contact table with uid=0 of the author of the mail', `convid` int unsigned COMMENT 'conv.id', `title` varchar(255) NOT NULL DEFAULT '' COMMENT '', @@ -839,11 +870,11 @@ CREATE TABLE IF NOT EXISTS `mail` ( `reply` boolean NOT NULL DEFAULT '0' COMMENT '', `replied` boolean NOT NULL DEFAULT '0' COMMENT '', `unknown` boolean NOT NULL DEFAULT '0' COMMENT 'if sender not in the contact table this is 1', - `uri` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `uri` varbinary(383) NOT NULL DEFAULT '' COMMENT '', `uri-id` int unsigned COMMENT 'Item-uri id of the related mail', - `parent-uri` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `parent-uri` varbinary(383) NOT NULL DEFAULT '' COMMENT '', `parent-uri-id` int unsigned COMMENT 'Item-uri id of the parent of the related mail', - `thr-parent` varchar(255) COMMENT '', + `thr-parent` varbinary(383) COMMENT '', `thr-parent-id` int unsigned COMMENT 'Id of the item-uri table that contains the thread parent uri', `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'creation time of the private message', PRIMARY KEY(`id`), @@ -920,6 +951,7 @@ CREATE TABLE IF NOT EXISTS `notification` ( INDEX `target-uri-id` (`target-uri-id`), INDEX `parent-uri-id` (`parent-uri-id`), INDEX `seen_uid` (`seen`,`uid`), + INDEX `uid_type_parent-uri-id_actor-id` (`uid`,`type`,`parent-uri-id`,`actor-id`), FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, FOREIGN KEY (`vid`) REFERENCES `verb` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT, FOREIGN KEY (`actor-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, @@ -934,12 +966,12 @@ CREATE TABLE IF NOT EXISTS `notify` ( `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', `type` smallint unsigned NOT NULL DEFAULT 0 COMMENT '', `name` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `url` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `photo` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `url` varbinary(383) NOT NULL DEFAULT '' COMMENT '', + `photo` varbinary(383) NOT NULL DEFAULT '' COMMENT '', `date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', `msg` mediumtext COMMENT '', `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner User id', - `link` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `link` varbinary(383) NOT NULL DEFAULT '' COMMENT '', `iid` int unsigned COMMENT '', `parent` int unsigned COMMENT '', `uri-id` int unsigned COMMENT 'Item-uri id of the related post', @@ -983,7 +1015,7 @@ CREATE TABLE IF NOT EXISTS `notify-threads` ( -- TABLE oembed -- CREATE TABLE IF NOT EXISTS `oembed` ( - `url` varbinary(255) NOT NULL COMMENT 'page url', + `url` varbinary(383) NOT NULL COMMENT 'page url', `maxwidth` mediumint unsigned NOT NULL COMMENT 'Maximum width passed to Oembed', `content` mediumtext COMMENT 'OEmbed data of the page', `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'datetime of creation', @@ -1057,6 +1089,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 '', @@ -1153,9 +1186,12 @@ CREATE TABLE IF NOT EXISTS `post-category` ( CREATE TABLE IF NOT EXISTS `post-collection` ( `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', `type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '0 - Featured', + `author-id` int unsigned COMMENT 'Author of the featured post', PRIMARY KEY(`uri-id`,`type`), INDEX `type` (`type`), - FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE + INDEX `author-id` (`author-id`), + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`author-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Collection of posts'; -- @@ -1167,6 +1203,7 @@ CREATE TABLE IF NOT EXISTS `post-content` ( `content-warning` varchar(255) NOT NULL DEFAULT '' COMMENT '', `body` mediumtext COMMENT 'item body content', `raw-body` mediumtext COMMENT 'Body without embedded media links', + `quote-uri-id` int unsigned COMMENT 'Id of the item-uri table that contains the quoted uri', `location` varchar(255) NOT NULL DEFAULT '' COMMENT 'text location where this item originated', `coord` varchar(255) NOT NULL DEFAULT '' COMMENT 'longitude/latitude pair representing location where this item originated', `language` text COMMENT 'Language information about this post', @@ -1178,12 +1215,14 @@ CREATE TABLE IF NOT EXISTS `post-content` ( `target-type` varchar(100) NOT NULL DEFAULT '' COMMENT 'ActivityStreams target type if applicable (URI)', `target` text COMMENT 'JSON encoded target structure if used', `resource-id` varchar(32) NOT NULL DEFAULT '' COMMENT 'Used to link other tables to items, it identifies the linked resource (e.g. photo) and if set must also set resource_type', - `plink` varchar(255) NOT NULL DEFAULT '' COMMENT 'permalink or URL to a displayable copy of the message at its source', + `plink` varbinary(383) NOT NULL DEFAULT '' COMMENT 'permalink or URL to a displayable copy of the message at its source', PRIMARY KEY(`uri-id`), INDEX `plink` (`plink`(191)), INDEX `resource-id` (`resource-id`), FULLTEXT INDEX `title-content-warning-body` (`title`,`content-warning`,`body`), - FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE + INDEX `quote-uri-id` (`quote-uri-id`), + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`quote-uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Content for all posts'; -- @@ -1245,7 +1284,7 @@ CREATE TABLE IF NOT EXISTS `post-history` ( `target-type` varchar(100) NOT NULL DEFAULT '' COMMENT 'ActivityStreams target type if applicable (URI)', `target` text COMMENT 'JSON encoded target structure if used', `resource-id` varchar(32) NOT NULL DEFAULT '' COMMENT 'Used to link other tables to items, it identifies the linked resource (e.g. photo) and if set must also set resource_type', - `plink` varchar(255) NOT NULL DEFAULT '' COMMENT 'permalink or URL to a displayable copy of the message at its source', + `plink` varbinary(383) NOT NULL DEFAULT '' COMMENT 'permalink or URL to a displayable copy of the message at its source', PRIMARY KEY(`uri-id`,`edited`), FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Post history'; @@ -1270,26 +1309,30 @@ CREATE TABLE IF NOT EXISTS `post-media` ( `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri', `url` varbinary(1024) NOT NULL COMMENT 'Media URL', + `media-uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the activities uri-id', `type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'Media type', `mimetype` varchar(60) COMMENT '', `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', `description` text COMMENT '', `name` varchar(255) COMMENT 'Name of the media', - `author-url` varbinary(255) COMMENT 'URL of the author of the media', + `author-url` varbinary(383) COMMENT 'URL of the author of the media', `author-name` varchar(255) COMMENT 'Name of the author of the media', - `author-image` varbinary(255) COMMENT 'Image of the author of the media', - `publisher-url` varbinary(255) COMMENT 'URL of the publisher of the media', + `author-image` varbinary(383) COMMENT 'Image of the author of the media', + `publisher-url` varbinary(383) COMMENT 'URL of the publisher of the media', `publisher-name` varchar(255) COMMENT 'Name of the publisher of the media', - `publisher-image` varbinary(255) COMMENT 'Image of the publisher of the media', + `publisher-image` varbinary(383) COMMENT 'Image of the publisher of the media', PRIMARY KEY(`id`), UNIQUE INDEX `uri-id-url` (`uri-id`,`url`(512)), INDEX `uri-id-id` (`uri-id`,`id`), - FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE + INDEX `media-uri-id` (`media-uri-id`), + FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`media-uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Attached media'; -- @@ -1412,6 +1455,7 @@ CREATE TABLE IF NOT EXISTS `post-user` ( INDEX `author-id_uid` (`author-id`,`uid`), INDEX `author-id_received` (`author-id`,`received`), INDEX `parent-uri-id_uid` (`parent-uri-id`,`uid`), + INDEX `uid_wall_received` (`uid`,`wall`,`received`), INDEX `uid_contactid` (`uid`,`contact-id`), INDEX `uid_unseen_contactid` (`uid`,`unseen`,`contact-id`), INDEX `uid_unseen` (`uid`,`unseen`), @@ -1551,10 +1595,11 @@ 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` varchar(255) NOT NULL DEFAULT '' COMMENT '', - `thumb` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `photo` varbinary(383) NOT NULL DEFAULT '' COMMENT '', + `thumb` varbinary(383) NOT NULL DEFAULT '' COMMENT '', `publish` boolean NOT NULL DEFAULT '0' COMMENT 'publish default profile in local directory', `net-publish` boolean NOT NULL DEFAULT '0' COMMENT 'publish profile in global directory', PRIMARY KEY(`id`), @@ -1589,7 +1634,7 @@ CREATE TABLE IF NOT EXISTS `profile_field` ( CREATE TABLE IF NOT EXISTS `push_subscriber` ( `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id', - `callback_url` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `callback_url` varbinary(383) NOT NULL DEFAULT '' COMMENT '', `topic` varchar(255) NOT NULL DEFAULT '' COMMENT '', `nickname` varchar(255) NOT NULL DEFAULT '' COMMENT '', `push` tinyint NOT NULL DEFAULT 0 COMMENT 'Retrial counter', @@ -1608,7 +1653,7 @@ CREATE TABLE IF NOT EXISTS `push_subscriber` ( -- CREATE TABLE IF NOT EXISTS `register` ( `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', - `hash` varchar(255) NOT NULL DEFAULT '' COMMENT '', + `hash` varbinary(255) NOT NULL DEFAULT '' COMMENT '', `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id', `password` varchar(255) NOT NULL DEFAULT '' COMMENT '', @@ -1619,6 +1664,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 -- @@ -1708,7 +1784,7 @@ CREATE TABLE IF NOT EXISTS `user-contact` ( `fetch_further_information` tinyint unsigned COMMENT '', `ffi_keyword_denylist` text COMMENT '', `subhub` boolean COMMENT '', - `hub-verify` varchar(255) COMMENT '', + `hub-verify` varbinary(383) COMMENT '', `protocol` char(4) COMMENT 'Protocol of the contact', `rating` tinyint COMMENT 'Automatically detected feed poll frequency', `priority` tinyint unsigned COMMENT 'Feed poll priority', @@ -1724,7 +1800,7 @@ CREATE TABLE IF NOT EXISTS `user-contact` ( -- TABLE arrived-activity -- CREATE TABLE IF NOT EXISTS `arrived-activity` ( - `object-id` varbinary(255) NOT NULL COMMENT 'object id of the incoming activity', + `object-id` varbinary(383) NOT NULL COMMENT 'object id of the incoming activity', `received` datetime COMMENT 'Receiving date', PRIMARY KEY(`object-id`) ) ENGINE=MEMORY DEFAULT COLLATE utf8mb4_general_ci COMMENT='Id of arrived activities'; @@ -1733,7 +1809,7 @@ CREATE TABLE IF NOT EXISTS `arrived-activity` ( -- TABLE fetched-activity -- CREATE TABLE IF NOT EXISTS `fetched-activity` ( - `object-id` varbinary(255) NOT NULL COMMENT 'object id of fetched activity', + `object-id` varbinary(383) NOT NULL COMMENT 'object id of fetched activity', `received` datetime COMMENT 'Receiving date', PRIMARY KEY(`object-id`) ) ENGINE=MEMORY DEFAULT COLLATE utf8mb4_general_ci COMMENT='Id of fetched activities'; @@ -1787,6 +1863,8 @@ CREATE VIEW `post-user-view` AS SELECT `post-user`.`thr-parent-id` AS `thr-parent-id`, `conversation-item-uri`.`uri` AS `conversation`, `post-thread-user`.`conversation-id` AS `conversation-id`, + `quote-item-uri`.`uri` AS `quote-uri`, + `post-content`.`quote-uri-id` AS `quote-uri-id`, `item-uri`.`guid` AS `guid`, `post-user`.`wall` AS `wall`, `post-user`.`gravity` AS `gravity`, @@ -1924,9 +2002,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` @@ -1942,6 +2018,7 @@ CREATE VIEW `post-user-view` AS SELECT LEFT JOIN `event` ON `event`.`id` = `post-user`.`event-id` LEFT JOIN `diaspora-interaction` ON `diaspora-interaction`.`uri-id` = `post-user`.`uri-id` LEFT JOIN `post-content` ON `post-content`.`uri-id` = `post-user`.`uri-id` + LEFT JOIN `item-uri` AS `quote-item-uri` ON `quote-item-uri`.`id` = `post-content`.`quote-uri-id` LEFT JOIN `post-delivery-data` ON `post-delivery-data`.`uri-id` = `post-user`.`uri-id` AND `post-user`.`origin` LEFT JOIN `post-question` ON `post-question`.`uri-id` = `post-user`.`uri-id` LEFT JOIN `permissionset` ON `permissionset`.`id` = `post-user`.`psid` @@ -1965,6 +2042,8 @@ CREATE VIEW `post-thread-user-view` AS SELECT `post-user`.`thr-parent-id` AS `thr-parent-id`, `conversation-item-uri`.`uri` AS `conversation`, `post-thread-user`.`conversation-id` AS `conversation-id`, + `quote-item-uri`.`uri` AS `quote-uri`, + `post-content`.`quote-uri-id` AS `quote-uri-id`, `item-uri`.`guid` AS `guid`, `post-thread-user`.`wall` AS `wall`, `post-user`.`gravity` AS `gravity`, @@ -2100,9 +2179,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` @@ -2118,6 +2195,7 @@ CREATE VIEW `post-thread-user-view` AS SELECT LEFT JOIN `event` ON `event`.`id` = `post-user`.`event-id` LEFT JOIN `diaspora-interaction` ON `diaspora-interaction`.`uri-id` = `post-thread-user`.`uri-id` LEFT JOIN `post-content` ON `post-content`.`uri-id` = `post-thread-user`.`uri-id` + LEFT JOIN `item-uri` AS `quote-item-uri` ON `quote-item-uri`.`id` = `post-content`.`quote-uri-id` LEFT JOIN `post-delivery-data` ON `post-delivery-data`.`uri-id` = `post-thread-user`.`uri-id` AND `post-thread-user`.`origin` LEFT JOIN `post-question` ON `post-question`.`uri-id` = `post-thread-user`.`uri-id` LEFT JOIN `permissionset` ON `permissionset`.`id` = `post-thread-user`.`psid` @@ -2137,6 +2215,8 @@ CREATE VIEW `post-view` AS SELECT `post`.`thr-parent-id` AS `thr-parent-id`, `conversation-item-uri`.`uri` AS `conversation`, `post-thread`.`conversation-id` AS `conversation-id`, + `quote-item-uri`.`uri` AS `quote-uri`, + `post-content`.`quote-uri-id` AS `quote-uri-id`, `item-uri`.`guid` AS `guid`, `post`.`gravity` AS `gravity`, `external-item-uri`.`uri` AS `extid`, @@ -2243,9 +2323,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` @@ -2259,6 +2337,7 @@ CREATE VIEW `post-view` AS SELECT LEFT JOIN `verb` ON `verb`.`id` = `post`.`vid` LEFT JOIN `diaspora-interaction` ON `diaspora-interaction`.`uri-id` = `post`.`uri-id` LEFT JOIN `post-content` ON `post-content`.`uri-id` = `post`.`uri-id` + LEFT JOIN `item-uri` AS `quote-item-uri` ON `quote-item-uri`.`id` = `post-content`.`quote-uri-id` LEFT JOIN `post-question` ON `post-question`.`uri-id` = `post`.`uri-id` LEFT JOIN `post` AS `parent-post` ON `parent-post`.`uri-id` = `post`.`parent-uri-id` LEFT JOIN `contact` AS `parent-post-author` ON `parent-post-author`.`id` = `parent-post`.`author-id`; @@ -2276,6 +2355,8 @@ CREATE VIEW `post-thread-view` AS SELECT `post`.`thr-parent-id` AS `thr-parent-id`, `conversation-item-uri`.`uri` AS `conversation`, `post-thread`.`conversation-id` AS `conversation-id`, + `quote-item-uri`.`uri` AS `quote-uri`, + `post-content`.`quote-uri-id` AS `quote-uri-id`, `item-uri`.`guid` AS `guid`, `post`.`gravity` AS `gravity`, `external-item-uri`.`uri` AS `extid`, @@ -2376,15 +2457,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` @@ -2398,6 +2479,7 @@ CREATE VIEW `post-thread-view` AS SELECT LEFT JOIN `verb` ON `verb`.`id` = `post`.`vid` LEFT JOIN `diaspora-interaction` ON `diaspora-interaction`.`uri-id` = `post-thread`.`uri-id` LEFT JOIN `post-content` ON `post-content`.`uri-id` = `post-thread`.`uri-id` + LEFT JOIN `item-uri` AS `quote-item-uri` ON `quote-item-uri`.`id` = `post-content`.`quote-uri-id` LEFT JOIN `post-question` ON `post-question`.`uri-id` = `post-thread`.`uri-id` LEFT JOIN `post` AS `parent-post` ON `parent-post`.`uri-id` = `post`.`parent-uri-id` LEFT JOIN `contact` AS `parent-post-author` ON `parent-post-author`.`id` = `parent-post`.`author-id`; @@ -2423,7 +2505,7 @@ DROP VIEW IF EXISTS `collection-view`; CREATE VIEW `collection-view` AS SELECT `post-collection`.`uri-id` AS `uri-id`, `post-collection`.`type` AS `type`, - `post`.`author-id` AS `cid`, + `post-collection`.`author-id` AS `cid`, `post`.`received` AS `received`, `post`.`created` AS `created`, `post-thread`.`commented` AS `commented`, @@ -2431,12 +2513,30 @@ CREATE VIEW `collection-view` AS SELECT `post`.`visible` AS `visible`, `post`.`deleted` AS `deleted`, `post`.`thr-parent-id` AS `thr-parent-id`, - `post`.`author-id` AS `author-id`, + `post-collection`.`author-id` AS `author-id`, `post`.`gravity` AS `gravity` FROM `post-collection` 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 -- @@ -2596,6 +2696,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`, @@ -2636,6 +2737,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` @@ -2701,11 +2803,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`, @@ -2723,7 +2825,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; @@ -2802,14 +2904,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`, @@ -2828,7 +2930,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`; -- @@ -2905,3 +3007,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`;