-- ------------------------------------------
--- Friendica 2022.05-rc (Siberian Iris)
--- DB_UPDATE_VERSION 1460
+-- Friendica 2022.12-rc (Giant Rhubarb)
+-- DB_UPDATE_VERSION 1500
-- ------------------------------------------
--
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 '',
`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 '',
`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',
`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',
--
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`),
`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',
`failure_update` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last failed update',
`failed` boolean COMMENT 'Connection failed',
`term-date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '',
`last-item` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'date of the last post',
`last-discovery` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'date of the last follower discovery',
+ `local-data` boolean COMMENT 'Is true when there are posts with this contact on the system',
`blocked` boolean NOT NULL DEFAULT '1' COMMENT 'Node-wide block status',
`block_reason` text COMMENT 'Node-wide block reason',
`readonly` boolean NOT NULL DEFAULT '0' COMMENT 'posts of the contact are readonly',
`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 '',
`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 '',
`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',
`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',
INDEX `attag_uid` (`attag`(96),`uid`),
INDEX `network_uid_lastupdate` (`network`,`uid`,`last-update`),
INDEX `uid_network_self_lastupdate` (`uid`,`network`,`self`,`last-update`),
+ INDEX `next-update` (`next-update`),
+ INDEX `local-data-next-update` (`local-data`,`next-update`),
INDEX `uid_lastitem` (`uid`,`last-item`),
INDEX `baseurl` (`baseurl`(64)),
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,
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`),
`cookie_hash` varchar(80) NOT NULL COMMENT 'Trusted cookie hash',
`uid` mediumint unsigned NOT NULL COMMENT 'User ID',
`user_agent` text COMMENT 'User agent string',
+ `trusted` boolean NOT NULL DEFAULT '1' COMMENT 'Whenever this browser should be trusted or not',
`created` datetime NOT NULL COMMENT 'Datetime the trusted browser was recorded',
`last_used` datetime COMMENT 'Datetime the trusted browser was last used',
PRIMARY KEY(`cookie_hash`),
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
--
-- 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',
`nick` varchar(255) NOT NULL DEFAULT '' COMMENT '',
`name` varchar(255) COMMENT '',
`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',
`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',
UNIQUE INDEX `client_id` (`client_id`)
) DEFAULT COLLATE utf8mb4_general_ci COMMENT='OAuth application';
+--
+-- TABLE application-marker
+--
+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` 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`),
+ INDEX `uid_id` (`uid`),
+ FOREIGN KEY (`application-id`) REFERENCES `application` (`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='Timeline marker';
+
--
-- TABLE application-token
--
--
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',
FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE
) DEFAULT COLLATE utf8mb4_general_ci COMMENT='private messages';
---
--- TABLE conversation
---
-CREATE TABLE IF NOT EXISTS `conversation` (
- `item-uri` varbinary(255) NOT NULL COMMENT 'Original URI of the item - unrelated to the table with the same name',
- `reply-to-uri` varbinary(255) NOT NULL DEFAULT '' COMMENT 'URI to which this item is a reply',
- `conversation-uri` varbinary(255) NOT NULL DEFAULT '' COMMENT 'GNU Social conversation URI',
- `conversation-href` varbinary(255) NOT NULL DEFAULT '' COMMENT 'GNU Social conversation link',
- `protocol` tinyint unsigned NOT NULL DEFAULT 255 COMMENT 'The protocol of the item',
- `direction` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'How the message arrived here: 1=push, 2=pull',
- `source` mediumtext COMMENT 'Original source',
- `received` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Receiving date',
- PRIMARY KEY(`item-uri`),
- INDEX `conversation-uri` (`conversation-uri`),
- INDEX `received` (`received`)
-) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Raw data and structure information for messages';
-
--
-- TABLE 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',
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
--
-- 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`),
--
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',
) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Events';
--
--- TABLE fcontact
+-- TABLE fetch-entry
--
-CREATE TABLE IF NOT EXISTS `fcontact` (
+CREATE TABLE IF NOT EXISTS `fetch-entry` (
`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',
+ `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`),
- 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';
+ UNIQUE INDEX `url` (`url`),
+ INDEX `created` (`created`),
+ INDEX `wid` (`wid`),
+ FOREIGN KEY (`wid`) REFERENCES `workerqueue` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
+) DEFAULT COLLATE utf8mb4_general_ci COMMENT='';
--
-- TABLE 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`),
UNIQUE INDEX `hook_file_function` (`hook`,`file`,`function`)
) DEFAULT COLLATE utf8mb4_general_ci COMMENT='addon hook registry';
+--
+-- TABLE inbox-entry
+--
+CREATE TABLE IF NOT EXISTS `inbox-entry` (
+ `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
+ `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',
+ `received` datetime COMMENT 'Receiving date',
+ `activity` mediumtext COMMENT 'The JSON activity',
+ `signer` varchar(255) COMMENT '',
+ `push` boolean COMMENT 'Is the entry pushed or have pulled it?',
+ `trust` boolean COMMENT 'Do we trust this entry?',
+ `wid` int unsigned COMMENT 'Workerqueue id',
+ PRIMARY KEY(`id`),
+ UNIQUE INDEX `activity-id` (`activity-id`),
+ INDEX `object-id` (`object-id`),
+ INDEX `received` (`received`),
+ INDEX `wid` (`wid`),
+ FOREIGN KEY (`wid`) REFERENCES `workerqueue` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
+) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Incoming activity';
+
+--
+-- TABLE inbox-entry-receiver
+--
+CREATE TABLE IF NOT EXISTS `inbox-entry-receiver` (
+ `queue-id` int unsigned NOT NULL COMMENT '',
+ `uid` mediumint unsigned NOT NULL COMMENT 'User id',
+ PRIMARY KEY(`queue-id`,`uid`),
+ INDEX `uid` (`uid`),
+ FOREIGN KEY (`queue-id`) REFERENCES `inbox-entry` (`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='Receiver for the incoming activity';
+
--
-- 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',
`failure` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last failed delivery',
`previous` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Previous delivery date',
`archive` boolean NOT NULL DEFAULT '0' COMMENT 'Is the inbox archived?',
`shared` boolean NOT NULL DEFAULT '0' COMMENT 'Is it a shared inbox?',
- PRIMARY KEY(`url`)
+ PRIMARY KEY(`url`),
+ 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='Status of ActivityPub inboxes';
--
`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 '',
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 '',
`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`),
`id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
`uid` mediumint unsigned COMMENT 'Owner User id',
`vid` smallint unsigned COMMENT 'Id of the verb table entry that contains the activity verbs',
- `type` tinyint unsigned COMMENT '',
+ `type` smallint unsigned COMMENT '',
`actor-id` int unsigned COMMENT 'Link to the contact table with uid=0 of the actor that caused the notification',
`target-uri-id` int unsigned COMMENT 'Item-uri id of the related post',
`parent-uri-id` int unsigned COMMENT 'Item-uri id of the parent of the related post',
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,
`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',
-- 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',
`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 '',
FOREIGN KEY (`vid`) REFERENCES `verb` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT
) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Structure for all posts';
+--
+-- TABLE post-activity
+--
+CREATE TABLE IF NOT EXISTS `post-activity` (
+ `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri',
+ `activity` mediumtext COMMENT 'Original activity',
+ `received` datetime COMMENT '',
+ PRIMARY KEY(`uri-id`),
+ FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
+) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Original remote activity';
+
--
-- TABLE post-category
--
`type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '',
`tid` int unsigned NOT NULL DEFAULT 0 COMMENT '',
PRIMARY KEY(`uri-id`,`uid`,`type`,`tid`),
- INDEX `uri-id` (`tid`),
- INDEX `uid` (`uid`),
+ INDEX `tid` (`tid`),
+ 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,
FOREIGN KEY (`tid`) REFERENCES `tag` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT
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';
--
`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',
`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';
+--
+-- TABLE post-delivery
+--
+CREATE TABLE IF NOT EXISTS `post-delivery` (
+ `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri',
+ `inbox-id` int unsigned NOT NULL COMMENT 'Item-uri id of inbox url',
+ `uid` mediumint unsigned COMMENT 'Delivering user',
+ `created` datetime DEFAULT '0001-01-01 00:00:00' COMMENT '',
+ `command` varbinary(32) COMMENT '',
+ `failed` tinyint DEFAULT 0 COMMENT 'Number of times the delivery has failed',
+ `receivers` mediumtext COMMENT 'JSON encoded array with the receiving contacts',
+ PRIMARY KEY(`uri-id`,`inbox-id`),
+ INDEX `inbox-id_created` (`inbox-id`,`created`),
+ INDEX `uid` (`uid`),
+ FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
+ FOREIGN KEY (`inbox-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='Delivery data for posts for the batch processing';
+
--
-- TABLE post-delivery-data
--
FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Delivery data for items';
+--
+-- TABLE post-history
+--
+CREATE TABLE IF NOT EXISTS `post-history` (
+ `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri',
+ `edited` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of edit',
+ `title` varchar(255) NOT NULL DEFAULT '' COMMENT 'item title',
+ `content-warning` varchar(255) NOT NULL DEFAULT '' COMMENT '',
+ `body` mediumtext COMMENT 'item body content',
+ `raw-body` mediumtext COMMENT 'Body without embedded media links',
+ `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',
+ `app` varchar(255) NOT NULL DEFAULT '' COMMENT 'application which generated this item',
+ `rendered-hash` varchar(32) NOT NULL DEFAULT '' COMMENT '',
+ `rendered-html` mediumtext COMMENT 'item.body converted to html',
+ `object-type` varchar(100) NOT NULL DEFAULT '' COMMENT 'ActivityStreams object type',
+ `object` text COMMENT 'JSON encoded object structure unless it is an implied object (normal post)',
+ `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` 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';
+
--
-- TABLE post-link
--
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(511) NOT NULL COMMENT 'Media URL',
+ `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` int unsigned COMMENT 'Media size',
- `preview` varbinary(255) COMMENT 'Preview URL',
+ `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`),
- FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
+ UNIQUE INDEX `uri-id-url` (`uri-id`,`url`(512)),
+ INDEX `uri-id-id` (`uri-id`,`id`),
+ 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';
--
--
CREATE TABLE IF NOT EXISTS `post-thread` (
`uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri',
+ `conversation-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the conversation uri',
`owner-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'Item owner',
`author-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'Item author',
`causer-id` int unsigned COMMENT 'Link to the contact table with uid=0 of the contact that caused the item creation',
`changed` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date that something in the conversation changed, indicating clients should fetch the conversation again',
`commented` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '',
PRIMARY KEY(`uri-id`),
+ INDEX `conversation-id` (`conversation-id`),
INDEX `owner-id` (`owner-id`),
INDEX `author-id` (`author-id`),
INDEX `causer-id` (`causer-id`),
INDEX `received` (`received`),
INDEX `commented` (`commented`),
FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
+ FOREIGN KEY (`conversation-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
FOREIGN KEY (`owner-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT,
FOREIGN KEY (`author-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT,
FOREIGN KEY (`causer-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT
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`),
--
CREATE TABLE IF NOT EXISTS `post-thread-user` (
`uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri',
+ `conversation-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the conversation uri',
`owner-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'Item owner',
`author-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'Item author',
`causer-id` int unsigned COMMENT 'Link to the contact table with uid=0 of the contact that caused the item creation',
`post-user-id` int unsigned COMMENT 'Id of the post-user table',
PRIMARY KEY(`uid`,`uri-id`),
INDEX `uri-id` (`uri-id`),
+ INDEX `conversation-id` (`conversation-id`),
INDEX `owner-id` (`owner-id`),
INDEX `author-id` (`author-id`),
INDEX `causer-id` (`causer-id`),
INDEX `uid_starred` (`uid`,`starred`),
INDEX `uid_mention` (`uid`,`mention`),
FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
+ FOREIGN KEY (`conversation-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
FOREIGN KEY (`owner-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT,
FOREIGN KEY (`author-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT,
FOREIGN KEY (`causer-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT,
CREATE TABLE IF NOT EXISTS `post-user-notification` (
`uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri',
`uid` mediumint unsigned NOT NULL COMMENT 'Owner id which owns this copy of the item',
- `notification-type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '',
+ `notification-type` smallint unsigned NOT NULL DEFAULT 0 COMMENT '',
PRIMARY KEY(`uid`,`uri-id`),
INDEX `uri-id` (`uri-id`),
FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
`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`),
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',
--
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 '',
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
--
`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',
FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
) DEFAULT COLLATE utf8mb4_general_ci COMMENT='User specific public contact data';
+--
+-- TABLE arrived-activity
+--
+CREATE TABLE IF NOT EXISTS `arrived-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';
+
+--
+-- TABLE fetched-activity
+--
+CREATE TABLE IF NOT EXISTS `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';
+
--
-- TABLE worker-ipc
--
`post-user`.`parent-uri-id` AS `parent-uri-id`,
`thr-parent-item-uri`.`uri` AS `thr-parent`,
`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`,
`post-user`.`global` AS `global`,
EXISTS(SELECT `type` FROM `post-collection` WHERE `type` = 0 AND `uri-id` = `post-user`.`uri-id`) AS `featured`,
`post-user`.`network` AS `network`,
+ `post-user`.`protocol` AS `protocol`,
`post-user`.`vid` AS `vid`,
`post-user`.`psid` AS `psid`,
IF (`post-user`.`vid` IS NULL, '', `verb`.`name`) AS `verb`,
`post-content`.`target` AS `target`,
`post-content`.`resource-id` AS `resource-id`,
`post-user`.`contact-id` AS `contact-id`,
+ `contact`.`uri-id` AS `contact-uri-id`,
`contact`.`url` AS `contact-link`,
`contact`.`addr` AS `contact-addr`,
`contact`.`name` AS `contact-name`,
`contact`.`avatar-date` AS `avatar-date`,
`contact`.`thumb` AS `thumb`,
`post-user`.`author-id` AS `author-id`,
+ `author`.`uri-id` AS `author-uri-id`,
`author`.`url` AS `author-link`,
`author`.`addr` AS `author-addr`,
IF (`contact`.`url` = `author`.`url` AND `contact`.`name` != '', `contact`.`name`, `author`.`name`) AS `author-name`,
`author`.`network` AS `author-network`,
`author`.`blocked` AS `author-blocked`,
`author`.`hidden` AS `author-hidden`,
+ `author`.`updated` AS `author-updated`,
+ `author`.`gsid` AS `author-gsid`,
`post-user`.`owner-id` AS `owner-id`,
+ `owner`.`uri-id` AS `owner-uri-id`,
`owner`.`url` AS `owner-link`,
`owner`.`addr` AS `owner-addr`,
IF (`contact`.`url` = `owner`.`url` AND `contact`.`name` != '', `contact`.`name`, `owner`.`name`) AS `owner-name`,
`owner`.`network` AS `owner-network`,
`owner`.`blocked` AS `owner-blocked`,
`owner`.`hidden` AS `owner-hidden`,
+ `owner`.`updated` AS `owner-updated`,
`owner`.`contact-type` AS `owner-contact-type`,
`post-user`.`causer-id` AS `causer-id`,
+ `causer`.`uri-id` AS `causer-uri-id`,
`causer`.`url` AS `causer-link`,
`causer`.`addr` AS `causer-addr`,
`causer`.`name` AS `causer-name`,
`post-question`.`multiple` AS `question-multiple`,
`post-question`.`voters` AS `question-voters`,
`post-question`.`end-time` AS `question-end-time`,
+ EXISTS(SELECT `uri-id` FROM `post-category` WHERE `post-category`.`uri-id` = `post-user`.`uri-id` AND `post-category`.`uid` = `post-user`.`uid`) AS `has-categories`,
+ EXISTS(SELECT `id` FROM `post-media` WHERE `post-media`.`uri-id` = `post-user`.`uri-id`) AS `has-media`,
`diaspora-interaction`.`interaction` AS `signed_text`,
`parent-item-uri`.`guid` AS `parent-guid`,
`parent-post`.`network` AS `parent-network`,
`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`
LEFT JOIN `item-uri` ON `item-uri`.`id` = `post-user`.`uri-id`
LEFT JOIN `item-uri` AS `thr-parent-item-uri` ON `thr-parent-item-uri`.`id` = `post-user`.`thr-parent-id`
LEFT JOIN `item-uri` AS `parent-item-uri` ON `parent-item-uri`.`id` = `post-user`.`parent-uri-id`
+ LEFT JOIN `item-uri` AS `conversation-item-uri` ON `conversation-item-uri`.`id` = `post-thread-user`.`conversation-id`
LEFT JOIN `item-uri` AS `external-item-uri` ON `external-item-uri`.`id` = `post-user`.`external-id`
LEFT JOIN `verb` ON `verb`.`id` = `post-user`.`vid`
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`
`post-user`.`parent-uri-id` AS `parent-uri-id`,
`thr-parent-item-uri`.`uri` AS `thr-parent`,
`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`,
`post-content`.`target` AS `target`,
`post-content`.`resource-id` AS `resource-id`,
`post-thread-user`.`contact-id` AS `contact-id`,
+ `contact`.`uri-id` AS `contact-uri-id`,
`contact`.`url` AS `contact-link`,
`contact`.`addr` AS `contact-addr`,
`contact`.`name` AS `contact-name`,
`contact`.`avatar-date` AS `avatar-date`,
`contact`.`thumb` AS `thumb`,
`post-thread-user`.`author-id` AS `author-id`,
+ `author`.`uri-id` AS `author-uri-id`,
`author`.`url` AS `author-link`,
`author`.`addr` AS `author-addr`,
IF (`contact`.`url` = `author`.`url` AND `contact`.`name` != '', `contact`.`name`, `author`.`name`) AS `author-name`,
`author`.`network` AS `author-network`,
`author`.`blocked` AS `author-blocked`,
`author`.`hidden` AS `author-hidden`,
+ `author`.`updated` AS `author-updated`,
+ `author`.`gsid` AS `author-gsid`,
`post-thread-user`.`owner-id` AS `owner-id`,
+ `owner`.`uri-id` AS `owner-uri-id`,
`owner`.`url` AS `owner-link`,
`owner`.`addr` AS `owner-addr`,
IF (`contact`.`url` = `owner`.`url` AND `contact`.`name` != '', `contact`.`name`, `owner`.`name`) AS `owner-name`,
`owner`.`network` AS `owner-network`,
`owner`.`blocked` AS `owner-blocked`,
`owner`.`hidden` AS `owner-hidden`,
+ `owner`.`updated` AS `owner-updated`,
`owner`.`contact-type` AS `owner-contact-type`,
`post-thread-user`.`causer-id` AS `causer-id`,
+ `causer`.`uri-id` AS `causer-uri-id`,
`causer`.`url` AS `causer-link`,
`causer`.`addr` AS `causer-addr`,
`causer`.`name` AS `causer-name`,
`post-question`.`multiple` AS `question-multiple`,
`post-question`.`voters` AS `question-voters`,
`post-question`.`end-time` AS `question-end-time`,
+ EXISTS(SELECT `uri-id` FROM `post-category` WHERE `post-category`.`uri-id` = `post-thread-user`.`uri-id` AND `post-category`.`uid` = `post-thread-user`.`uid`) AS `has-categories`,
+ EXISTS(SELECT `id` FROM `post-media` WHERE `post-media`.`uri-id` = `post-thread-user`.`uri-id`) AS `has-media`,
`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-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`
LEFT JOIN `item-uri` ON `item-uri`.`id` = `post-thread-user`.`uri-id`
LEFT JOIN `item-uri` AS `thr-parent-item-uri` ON `thr-parent-item-uri`.`id` = `post-user`.`thr-parent-id`
LEFT JOIN `item-uri` AS `parent-item-uri` ON `parent-item-uri`.`id` = `post-user`.`parent-uri-id`
+ LEFT JOIN `item-uri` AS `conversation-item-uri` ON `conversation-item-uri`.`id` = `post-thread-user`.`conversation-id`
LEFT JOIN `item-uri` AS `external-item-uri` ON `external-item-uri`.`id` = `post-user`.`external-id`
LEFT JOIN `verb` ON `verb`.`id` = `post-user`.`vid`
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`
`post`.`parent-uri-id` AS `parent-uri-id`,
`thr-parent-item-uri`.`uri` AS `thr-parent`,
`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`,
`post-content`.`target` AS `target`,
`post-content`.`resource-id` AS `resource-id`,
`post`.`author-id` AS `contact-id`,
+ `author`.`uri-id` AS `contact-uri-id`,
`author`.`url` AS `contact-link`,
`author`.`addr` AS `contact-addr`,
`author`.`name` AS `contact-name`,
`author`.`avatar-date` AS `avatar-date`,
`author`.`thumb` AS `thumb`,
`post`.`author-id` AS `author-id`,
+ `author`.`uri-id` AS `author-uri-id`,
`author`.`url` AS `author-link`,
`author`.`addr` AS `author-addr`,
`author`.`name` AS `author-name`,
`author`.`network` AS `author-network`,
`author`.`blocked` AS `author-blocked`,
`author`.`hidden` AS `author-hidden`,
+ `author`.`updated` AS `author-updated`,
+ `author`.`gsid` AS `author-gsid`,
`post`.`owner-id` AS `owner-id`,
+ `owner`.`uri-id` AS `owner-uri-id`,
`owner`.`url` AS `owner-link`,
`owner`.`addr` AS `owner-addr`,
`owner`.`name` AS `owner-name`,
`owner`.`network` AS `owner-network`,
`owner`.`blocked` AS `owner-blocked`,
`owner`.`hidden` AS `owner-hidden`,
+ `owner`.`updated` AS `owner-updated`,
`owner`.`contact-type` AS `owner-contact-type`,
`post`.`causer-id` AS `causer-id`,
+ `causer`.`uri-id` AS `causer-uri-id`,
`causer`.`url` AS `causer-link`,
`causer`.`addr` AS `causer-addr`,
`causer`.`name` AS `causer-name`,
`post-question`.`multiple` AS `question-multiple`,
`post-question`.`voters` AS `question-voters`,
`post-question`.`end-time` AS `question-end-time`,
+ 0 AS `has-categories`,
+ EXISTS(SELECT `id` FROM `post-media` WHERE `post-media`.`uri-id` = `post`.`uri-id`) AS `has-media`,
`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`
STRAIGHT_JOIN `post-thread` ON `post-thread`.`uri-id` = `post`.`parent-uri-id`
STRAIGHT_JOIN `contact` AS `author` ON `author`.`id` = `post`.`author-id`
LEFT JOIN `item-uri` ON `item-uri`.`id` = `post`.`uri-id`
LEFT JOIN `item-uri` AS `thr-parent-item-uri` ON `thr-parent-item-uri`.`id` = `post`.`thr-parent-id`
LEFT JOIN `item-uri` AS `parent-item-uri` ON `parent-item-uri`.`id` = `post`.`parent-uri-id`
+ LEFT JOIN `item-uri` AS `conversation-item-uri` ON `conversation-item-uri`.`id` = `post-thread`.`conversation-id`
LEFT JOIN `item-uri` AS `external-item-uri` ON `external-item-uri`.`id` = `post`.`external-id`
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`;
`post`.`parent-uri-id` AS `parent-uri-id`,
`thr-parent-item-uri`.`uri` AS `thr-parent`,
`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`,
`post-content`.`target` AS `target`,
`post-content`.`resource-id` AS `resource-id`,
`post-thread`.`author-id` AS `contact-id`,
+ `author`.`uri-id` AS `contact-uri-id`,
`author`.`url` AS `contact-link`,
`author`.`addr` AS `contact-addr`,
`author`.`name` AS `contact-name`,
`author`.`avatar-date` AS `avatar-date`,
`author`.`thumb` AS `thumb`,
`post-thread`.`author-id` AS `author-id`,
+ `author`.`uri-id` AS `author-uri-id`,
`author`.`url` AS `author-link`,
`author`.`addr` AS `author-addr`,
`author`.`name` AS `author-name`,
`author`.`network` AS `author-network`,
`author`.`blocked` AS `author-blocked`,
`author`.`hidden` AS `author-hidden`,
+ `author`.`updated` AS `author-updated`,
+ `author`.`gsid` AS `author-gsid`,
`post-thread`.`owner-id` AS `owner-id`,
+ `owner`.`uri-id` AS `owner-uri-id`,
`owner`.`url` AS `owner-link`,
`owner`.`addr` AS `owner-addr`,
`owner`.`name` AS `owner-name`,
`owner`.`network` AS `owner-network`,
`owner`.`blocked` AS `owner-blocked`,
`owner`.`hidden` AS `owner-hidden`,
+ `owner`.`updated` AS `owner-updated`,
`owner`.`contact-type` AS `owner-contact-type`,
`post-thread`.`causer-id` AS `causer-id`,
+ `causer`.`uri-id` AS `causer-uri-id`,
`causer`.`url` AS `causer-link`,
`causer`.`addr` AS `causer-addr`,
`causer`.`name` AS `causer-name`,
`post-question`.`multiple` AS `question-multiple`,
`post-question`.`voters` AS `question-voters`,
`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`
LEFT JOIN `item-uri` ON `item-uri`.`id` = `post-thread`.`uri-id`
LEFT JOIN `item-uri` AS `thr-parent-item-uri` ON `thr-parent-item-uri`.`id` = `post`.`thr-parent-id`
LEFT JOIN `item-uri` AS `parent-item-uri` ON `parent-item-uri`.`id` = `post`.`parent-uri-id`
+ LEFT JOIN `item-uri` AS `conversation-item-uri` ON `conversation-item-uri`.`id` = `post-thread`.`conversation-id`
LEFT JOIN `item-uri` AS `external-item-uri` ON `external-item-uri`.`id` = `post`.`external-id`
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`;
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`.`created` AS `created`,
+ `post-thread`.`commented` AS `commented`,
+ `post`.`private` AS `private`,
+ `post`.`visible` AS `visible`,
+ `post`.`deleted` AS `deleted`,
+ `post`.`thr-parent-id` AS `thr-parent-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` 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
`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`,
`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`
`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`,
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;
`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`,
+ `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`,
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`;
--
`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`;