-- ------------------------------------------
--- Friendica 2022.12-dev (Giant Rhubarb)
--- DB_UPDATE_VERSION 1493
+-- Friendica 2023.09-dev (Giant Rhubarb)
+-- DB_UPDATE_VERSION 1529
-- ------------------------------------------
`last_poco_query` datetime DEFAULT '0001-01-01 00:00:00' COMMENT '',
`last_contact` datetime DEFAULT '0001-01-01 00:00:00' COMMENT 'Last successful connection request',
`last_failure` datetime DEFAULT '0001-01-01 00:00:00' COMMENT 'Last failed connection request',
+ `blocked` boolean COMMENT 'Server is blocked',
`failed` boolean COMMENT 'Connection failed',
`next_contact` datetime DEFAULT '0001-01-01 00:00:00' COMMENT 'Next connection request',
PRIMARY KEY(`id`),
`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',
`verified` boolean NOT NULL DEFAULT '0' COMMENT 'user is verified through email',
`blocked` boolean NOT NULL DEFAULT '0' COMMENT '1 for user is blocked',
`blockwall` boolean NOT NULL DEFAULT '0' COMMENT 'Prohibit contacts to post to the profile page of the user',
- `hidewall` boolean NOT NULL DEFAULT '0' COMMENT 'Hide profile details from unkown viewers',
+ `hidewall` boolean NOT NULL DEFAULT '0' COMMENT 'Hide profile details from unknown viewers',
`blocktags` boolean NOT NULL DEFAULT '0' COMMENT 'Prohibit contacts to tag the post of this user',
`unkmail` boolean NOT NULL DEFAULT '0' COMMENT 'Permit unknown people to send private mails to this user',
`cntunkmail` int unsigned NOT NULL DEFAULT 10 COMMENT '',
FOREIGN KEY (`parent-uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE
) DEFAULT COLLATE utf8mb4_general_ci COMMENT='The local users';
+--
+-- TABLE user-gserver
+--
+CREATE TABLE IF NOT EXISTS `user-gserver` (
+ `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner User id',
+ `gsid` int unsigned NOT NULL DEFAULT 0 COMMENT 'Gserver id',
+ `ignored` boolean NOT NULL DEFAULT '0' COMMENT 'server accounts are ignored for the user',
+ PRIMARY KEY(`uid`,`gsid`),
+ INDEX `gsid` (`gsid`),
+ FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE,
+ FOREIGN KEY (`gsid`) REFERENCES `gserver` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
+) DEFAULT COLLATE utf8mb4_general_ci COMMENT='User settings about remote servers';
+
--
-- TABLE item-uri
--
`xmpp` varchar(255) NOT NULL DEFAULT '' COMMENT 'XMPP address',
`matrix` varchar(255) NOT NULL DEFAULT '' COMMENT 'Matrix address',
`avatar` varbinary(383) NOT NULL DEFAULT '' COMMENT '',
+ `blurhash` varbinary(255) COMMENT 'BlurHash representation of the avatar',
`header` varbinary(383) COMMENT 'Header picture',
`url` varbinary(383) NOT NULL DEFAULT '' COMMENT '',
`nurl` varbinary(383) NOT NULL DEFAULT '' COMMENT '',
`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` varbinary(383) DEFAULT '' COMMENT 'baseurl of the contact',
- `gsid` int unsigned COMMENT 'Global Server ID',
+ `baseurl` varbinary(383) DEFAULT '' COMMENT 'baseurl of the contact from the gserver record, can be missing',
+ `gsid` int unsigned COMMENT 'Global Server ID, can be missing',
`bd` date NOT NULL DEFAULT '0001-01-01' COMMENT '',
`reason` text COMMENT '',
`self` boolean NOT NULL DEFAULT '0' COMMENT '1 if the contact is the user him/her self',
`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',
+ `forum` boolean NOT NULL DEFAULT '0' COMMENT 'contact is a group. 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',
`bdyear` varchar(4) NOT NULL DEFAULT '' COMMENT '',
`site-pubkey` text COMMENT 'Deprecated',
`id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
`uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner id of this permission set',
`allow_cid` mediumtext COMMENT 'Access Control - list of allowed contact.id \'<19><78>\'',
- `allow_gid` mediumtext COMMENT 'Access Control - list of allowed groups',
+ `allow_gid` mediumtext COMMENT 'Access Control - list of allowed circles',
`deny_cid` mediumtext COMMENT 'Access Control - list of denied contact.id',
- `deny_gid` mediumtext COMMENT 'Access Control - list of denied groups',
+ `deny_gid` mediumtext COMMENT 'Access Control - list of denied circles',
PRIMARY KEY(`id`),
INDEX `uid_allow_cid_allow_gid_deny_cid_deny_gid` (`uid`,`allow_cid`(50),`allow_gid`(30),`deny_cid`(50),`deny_gid`(30)),
FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE
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
--
FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE
) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Remote and local accounts';
---
--- TABLE addon
---
-CREATE TABLE IF NOT EXISTS `addon` (
- `id` int unsigned NOT NULL auto_increment COMMENT '',
- `name` varchar(50) NOT NULL DEFAULT '' COMMENT 'addon base (file)name',
- `version` varchar(50) NOT NULL DEFAULT '' COMMENT 'currently unused',
- `installed` boolean NOT NULL DEFAULT '0' COMMENT 'currently always 1',
- `hidden` boolean NOT NULL DEFAULT '0' COMMENT 'currently unused',
- `timestamp` int unsigned NOT NULL DEFAULT 0 COMMENT 'file timestamp to check for reloads',
- `plugin_admin` boolean NOT NULL DEFAULT '0' COMMENT '1 = has admin config, 0 = has no admin config',
- PRIMARY KEY(`id`),
- INDEX `installed_name` (`installed`,`name`),
- UNIQUE INDEX `name` (`name`)
-) DEFAULT COLLATE utf8mb4_general_ci COMMENT='registered addons';
-
--
-- TABLE apcontact
--
`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',
`allow_cid` mediumtext COMMENT 'Access Control - list of allowed contact.id \'<19><78>',
- `allow_gid` mediumtext COMMENT 'Access Control - list of allowed groups',
+ `allow_gid` mediumtext COMMENT 'Access Control - list of allowed circles',
`deny_cid` mediumtext COMMENT 'Access Control - list of denied contact.id',
- `deny_gid` mediumtext COMMENT 'Access Control - list of denied groups',
+ `deny_gid` mediumtext COMMENT 'Access Control - list of denied circles',
`backend-class` tinytext COMMENT 'Storage backend class',
`backend-ref` text COMMENT 'Storage backend data reference',
PRIMARY KEY(`id`),
--
CREATE TABLE IF NOT EXISTS `config` (
`id` int unsigned NOT NULL auto_increment COMMENT '',
- `cat` varbinary(50) NOT NULL DEFAULT '' COMMENT '',
- `k` varbinary(50) NOT NULL DEFAULT '' COMMENT '',
+ `cat` varbinary(50) NOT NULL DEFAULT '' COMMENT 'The category of the entry',
+ `k` varbinary(50) NOT NULL DEFAULT '' COMMENT 'The key of the entry',
`v` mediumtext COMMENT '',
PRIMARY KEY(`id`),
UNIQUE INDEX `cat_k` (`cat`,`k`)
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 delivery-queue
+--
+CREATE TABLE IF NOT EXISTS `delivery-queue` (
+ `gsid` int unsigned NOT NULL COMMENT 'Target server',
+ `uri-id` int unsigned NOT NULL COMMENT 'Delivered post',
+ `created` datetime COMMENT '',
+ `command` varbinary(32) COMMENT '',
+ `cid` int unsigned COMMENT 'Target contact',
+ `uid` mediumint unsigned COMMENT 'Delivering user',
+ `failed` tinyint DEFAULT 0 COMMENT 'Number of times the delivery has failed',
+ PRIMARY KEY(`uri-id`,`gsid`),
+ INDEX `gsid_created` (`gsid`,`created`),
+ INDEX `uid` (`uid`),
+ INDEX `cid` (`cid`),
+ FOREIGN KEY (`gsid`) REFERENCES `gserver` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT,
+ FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
+ FOREIGN KEY (`cid`) REFERENCES `contact` (`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 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 interacts 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
--
`nofinish` boolean NOT NULL DEFAULT '0' COMMENT 'if event does have no end this is 1',
`ignore` boolean NOT NULL DEFAULT '0' COMMENT '0 or 1',
`allow_cid` mediumtext COMMENT 'Access Control - list of allowed contact.id \'<19><78>\'',
- `allow_gid` mediumtext COMMENT 'Access Control - list of allowed groups',
+ `allow_gid` mediumtext COMMENT 'Access Control - list of allowed circles',
`deny_cid` mediumtext COMMENT 'Access Control - list of denied contact.id',
- `deny_gid` mediumtext COMMENT 'Access Control - list of denied groups',
+ `deny_gid` mediumtext COMMENT 'Access Control - list of denied circles',
PRIMARY KEY(`id`),
INDEX `uid_start` (`uid`,`start`),
INDEX `cid` (`cid`),
FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Events';
---
--- TABLE fcontact
---
-CREATE TABLE IF NOT EXISTS `fcontact` (
- `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
- `guid` varbinary(255) NOT NULL DEFAULT '' COMMENT 'unique id',
- `url` varbinary(383) NOT NULL DEFAULT '' COMMENT '',
- `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the fcontact url',
- `name` varchar(255) NOT NULL DEFAULT '' COMMENT '',
- `photo` varbinary(383) NOT NULL DEFAULT '' COMMENT '',
- `request` varbinary(383) NOT NULL DEFAULT '' COMMENT '',
- `nick` varchar(255) NOT NULL DEFAULT '' COMMENT '',
- `addr` varchar(255) NOT NULL DEFAULT '' COMMENT '',
- `batch` varbinary(383) NOT NULL DEFAULT '' COMMENT '',
- `notify` varbinary(383) NOT NULL DEFAULT '' COMMENT '',
- `poll` varbinary(383) NOT NULL DEFAULT '' COMMENT '',
- `confirm` varbinary(383) NOT NULL DEFAULT '' COMMENT '',
- `priority` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '',
- `network` char(4) NOT NULL DEFAULT '' COMMENT '',
- `alias` varbinary(383) NOT NULL DEFAULT '' COMMENT '',
- `pubkey` text COMMENT '',
- `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '',
- `updated` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '',
- `interacting_count` int unsigned DEFAULT 0 COMMENT 'Number of contacts this contact interactes with',
- `interacted_count` int unsigned DEFAULT 0 COMMENT 'Number of contacts that interacted with this contact',
- `post_count` int unsigned DEFAULT 0 COMMENT 'Number of posts and comments',
- PRIMARY KEY(`id`),
- INDEX `addr` (`addr`(32)),
- UNIQUE INDEX `url` (`url`(190)),
- UNIQUE INDEX `uri-id` (`uri-id`),
- FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
-) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Diaspora compatible contacts - used in the Diaspora implementation';
-
--
-- TABLE fetch-entry
--
`id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
`uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner User id',
`visible` boolean NOT NULL DEFAULT '0' COMMENT '1 indicates the member list is not private',
- `deleted` boolean NOT NULL DEFAULT '0' COMMENT '1 indicates the group has been deleted',
- `cid` int unsigned COMMENT 'Contact id of forum. When this field is filled then the members are synced automatically.',
- `name` varchar(255) NOT NULL DEFAULT '' COMMENT 'human readable name of group',
+ `deleted` boolean NOT NULL DEFAULT '0' COMMENT '1 indicates the circle has been deleted',
+ `cid` int unsigned COMMENT 'Contact id of group. When this field is filled then the members are synced automatically.',
+ `name` varchar(255) NOT NULL DEFAULT '' COMMENT 'human readable name of circle',
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='privacy groups, group info';
+) DEFAULT COLLATE utf8mb4_general_ci COMMENT='privacy circles, circle info';
--
-- TABLE group_member
--
CREATE TABLE IF NOT EXISTS `group_member` (
`id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
- `gid` int unsigned NOT NULL DEFAULT 0 COMMENT 'groups.id of the associated group',
- `contact-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'contact.id of the member assigned to the associated group',
+ `gid` int unsigned NOT NULL DEFAULT 0 COMMENT 'group.id of the associated circle',
+ `contact-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'contact.id of the member assigned to the associated circle',
PRIMARY KEY(`id`),
INDEX `contactid` (`contact-id`),
UNIQUE INDEX `gid_contactid` (`gid`,`contact-id`),
FOREIGN KEY (`gid`) REFERENCES `group` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
FOREIGN KEY (`contact-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
-) DEFAULT COLLATE utf8mb4_general_ci COMMENT='privacy groups, member info';
+) DEFAULT COLLATE utf8mb4_general_ci COMMENT='privacy circles, member info';
--
-- TABLE gserver-tag
CREATE TABLE IF NOT EXISTS `inbox-status` (
`url` varbinary(383) NOT NULL COMMENT 'URL of the inbox',
`uri-id` int unsigned COMMENT 'Item-uri id of inbox url',
+ `gsid` int unsigned COMMENT 'ID of the related server',
`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',
`shared` boolean NOT NULL DEFAULT '0' COMMENT 'Is it a shared inbox?',
PRIMARY KEY(`url`),
INDEX `uri-id` (`uri-id`),
- FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
+ 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='Status of ActivityPub inboxes';
--
FOREIGN KEY (`suggest-cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
) DEFAULT COLLATE utf8mb4_general_ci COMMENT='';
+--
+-- TABLE key-value
+--
+CREATE TABLE IF NOT EXISTS `key-value` (
+ `k` varbinary(50) NOT NULL COMMENT '',
+ `v` mediumtext COMMENT '',
+ `updated_at` int unsigned NOT NULL COMMENT 'timestamp of the last update',
+ PRIMARY KEY(`k`)
+) DEFAULT COLLATE utf8mb4_general_ci COMMENT='A key value storage';
+
--
-- TABLE locks
--
`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` 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',
+ `from-url` varbinary(383) NOT NULL DEFAULT '' COMMENT 'profile link 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',
`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 '',
`allow_cid` mediumtext COMMENT 'Access Control - list of allowed contact.id \'<19><78>\'',
- `allow_gid` mediumtext COMMENT 'Access Control - list of allowed groups',
+ `allow_gid` mediumtext COMMENT 'Access Control - list of allowed circles',
`deny_cid` mediumtext COMMENT 'Access Control - list of denied contact.id',
- `deny_gid` mediumtext COMMENT 'Access Control - list of denied groups',
+ `deny_gid` mediumtext COMMENT 'Access Control - list of denied circles',
`accessible` boolean NOT NULL DEFAULT '0' COMMENT 'Make photo publicly accessible, ignoring permissions',
`backend-class` tinytext COMMENT 'Storage backend class',
`backend-ref` text COMMENT 'Storage backend data reference',
`uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri',
`url` varbinary(511) NOT NULL COMMENT 'External URL',
`mimetype` varchar(60) COMMENT '',
+ `height` smallint unsigned COMMENT 'Height of the media',
+ `width` smallint unsigned COMMENT 'Width of the media',
+ `blurhash` varbinary(255) COMMENT 'BlurHash representation of the link',
PRIMARY KEY(`id`),
UNIQUE INDEX `uri-id-url` (`uri-id`,`url`),
FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
`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',
`event-id` int unsigned COMMENT 'Used to link to the event.id',
`unseen` boolean NOT NULL DEFAULT '1' COMMENT 'post has not been seen',
`hidden` boolean NOT NULL DEFAULT '0' COMMENT 'Marker to hide the post from the user',
- `notification-type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '',
+ `notification-type` smallint unsigned NOT NULL DEFAULT 0 COMMENT '',
`wall` boolean NOT NULL DEFAULT '0' COMMENT 'This item was posted to the wall of uid',
`origin` boolean NOT NULL DEFAULT '0' COMMENT 'item originated at this site',
`psid` int unsigned COMMENT 'ID of the permission set of this post',
--
CREATE TABLE IF NOT EXISTS `process` (
`pid` int unsigned NOT NULL COMMENT 'The ID of the process',
- `hostname` varchar(32) NOT NULL COMMENT 'The name of the host the process is ran on',
+ `hostname` varchar(255) NOT NULL COMMENT 'The name of the host the process is ran on',
`command` varbinary(32) NOT NULL DEFAULT '' COMMENT '',
`created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '',
PRIMARY KEY(`pid`,`hostname`),
`profile-name` varchar(255) COMMENT 'Deprecated',
`is-default` boolean COMMENT 'Deprecated',
`hide-friends` boolean NOT NULL DEFAULT '0' COMMENT 'Hide friend list from viewers of this profile',
- `name` varchar(255) NOT NULL DEFAULT '' COMMENT '',
+ `name` varchar(255) NOT NULL DEFAULT '' COMMENT 'Unused in favor of user.username',
`pdesc` varchar(255) COMMENT 'Deprecated',
`dob` varchar(32) NOT NULL DEFAULT '0000-00-00' COMMENT 'Day of birth',
`address` varchar(255) NOT NULL DEFAULT '' COMMENT '',
CREATE TABLE IF NOT EXISTS `report` (
`id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
`uid` mediumint unsigned COMMENT 'Reporting user',
+ `reporter-id` int unsigned COMMENT 'Reporting contact',
`cid` int unsigned NOT NULL COMMENT 'Reported contact',
+ `gsid` int unsigned COMMENT 'Reported contact server',
`comment` text COMMENT 'Report',
+ `category-id` int unsigned NOT NULL DEFAULT 1 COMMENT 'Report category, one of Entity\Report::CATEGORY_*',
`forward` boolean COMMENT 'Forward the report to the remote server',
- `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '',
+ `public-remarks` text COMMENT 'Remarks shared with the reporter',
+ `private-remarks` text COMMENT 'Remarks shared with the moderation team',
+ `last-editor-uid` mediumint unsigned COMMENT 'Last editor user',
+ `assigned-uid` mediumint unsigned COMMENT 'Assigned moderator user',
+ `status` tinyint unsigned NOT NULL COMMENT 'Status of the report, one of Entity\Report::STATUS_*',
+ `resolution` tinyint unsigned COMMENT 'Resolution of the report, one of Entity\Report::RESOLUTION_*',
+ `created` datetime(6) NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '',
+ `edited` datetime(6) COMMENT 'Last time the report has been edited',
PRIMARY KEY(`id`),
INDEX `uid` (`uid`),
INDEX `cid` (`cid`),
+ INDEX `reporter-id` (`reporter-id`),
+ INDEX `gsid` (`gsid`),
+ INDEX `last-editor-uid` (`last-editor-uid`),
+ INDEX `assigned-uid` (`assigned-uid`),
+ INDEX `status-resolution` (`status`,`resolution`),
+ INDEX `created` (`created`),
+ INDEX `edited` (`edited`),
FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE,
- FOREIGN KEY (`cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
+ FOREIGN KEY (`reporter-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
+ FOREIGN KEY (`cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
+ FOREIGN KEY (`gsid`) REFERENCES `gserver` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
+ FOREIGN KEY (`last-editor-uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE,
+ FOREIGN KEY (`assigned-uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE
) DEFAULT COLLATE utf8mb4_general_ci COMMENT='';
--
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='';
+) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Individual posts attached to a moderation report';
+
+--
+-- TABLE report-rule
+--
+CREATE TABLE IF NOT EXISTS `report-rule` (
+ `rid` int unsigned NOT NULL COMMENT 'Report id',
+ `line-id` int unsigned NOT NULL COMMENT 'Terms of service rule line number, may become invalid after a TOS change.',
+ `text` text NOT NULL COMMENT 'Terms of service rule text recorded at the time of the report',
+ PRIMARY KEY(`rid`,`line-id`),
+ FOREIGN KEY (`rid`) REFERENCES `report` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
+) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Terms of service rule lines relevant to a moderation report';
--
-- TABLE search
`rel` tinyint unsigned COMMENT 'The kind of the relation between the user and the contact',
`info` mediumtext COMMENT '',
`notify_new_posts` boolean COMMENT '',
- `remote_self` boolean COMMENT '',
- `fetch_further_information` tinyint unsigned COMMENT '',
+ `remote_self` tinyint unsigned COMMENT '0 => No mirroring, 1-2 => Mirror as own post, 3 => Mirror as reshare',
+ `fetch_further_information` tinyint unsigned COMMENT '0 => None, 1 => Fetch information, 3 => Fetch keywords, 2 => Fetch both',
`ffi_keyword_denylist` text COMMENT '',
`subhub` boolean COMMENT '',
`hub-verify` varbinary(383) COMMENT '',
FROM `application-token`
INNER JOIN `application` ON `application-token`.`application-id` = `application`.`id`;
+--
+-- VIEW circle-member-view
+--
+DROP VIEW IF EXISTS `circle-member-view`;
+CREATE VIEW `circle-member-view` AS SELECT
+ `group_member`.`id` AS `id`,
+ `group`.`uid` AS `uid`,
+ `group_member`.`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`.`nick` AS `contact-nick`,
+ `contact`.`thumb` AS `contact-avatar`,
+ `contact`.`network` AS `contact-network`,
+ `contact`.`blocked` AS `contact-blocked`,
+ `contact`.`hidden` AS `contact-hidden`,
+ `contact`.`readonly` AS `contact-readonly`,
+ `contact`.`archive` AS `contact-archive`,
+ `contact`.`pending` AS `contact-pending`,
+ `contact`.`self` AS `contact-self`,
+ `contact`.`rel` AS `contact-rel`,
+ `contact`.`contact-type` AS `contact-contact-type`,
+ `group_member`.`gid` AS `circle-id`,
+ `group`.`visible` AS `circle-visible`,
+ `group`.`deleted` AS `circle-deleted`,
+ `group`.`name` AS `circle-name`
+ FROM `group_member`
+ INNER JOIN `contact` ON `group_member`.`contact-id` = `contact`.`id`
+ INNER JOIN `group` ON `group_member`.`gid` = `group`.`id`;
+
--
-- VIEW post-user-view
--
`post-user`.`id` AS `id`,
`post-user`.`id` AS `post-user-id`,
`post-user`.`uid` AS `uid`,
- `parent-post`.`id` AS `parent`,
+ `post-thread-user`.`post-user-id` AS `parent`,
`item-uri`.`uri` AS `uri`,
`post-user`.`uri-id` AS `uri-id`,
`parent-item-uri`.`uri` AS `parent-uri`,
`author`.`addr` AS `author-addr`,
IF (`contact`.`url` = `author`.`url` AND `contact`.`name` != '', `contact`.`name`, `author`.`name`) AS `author-name`,
`author`.`nick` AS `author-nick`,
+ `author`.`alias` AS `author-alias`,
IF (`contact`.`url` = `author`.`url` AND `contact`.`thumb` != '', `contact`.`thumb`, `author`.`thumb`) AS `author-avatar`,
`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`,
+ `author`.`baseurl` AS `author-baseurl`,
`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`.`nick` AS `owner-nick`,
+ `owner`.`alias` AS `owner-alias`,
IF (`contact`.`url` = `owner`.`url` AND `contact`.`thumb` != '', `contact`.`thumb`, `owner`.`thumb`) AS `owner-avatar`,
`owner`.`network` AS `owner-network`,
`owner`.`blocked` AS `owner-blocked`,
`owner`.`hidden` AS `owner-hidden`,
`owner`.`updated` AS `owner-updated`,
+ `owner`.`gsid` AS `owner-gsid`,
`owner`.`contact-type` AS `owner-contact-type`,
`post-user`.`causer-id` AS `causer-id`,
`causer`.`uri-id` AS `causer-uri-id`,
`causer`.`addr` AS `causer-addr`,
`causer`.`name` AS `causer-name`,
`causer`.`nick` AS `causer-nick`,
+ `causer`.`alias` AS `causer-alias`,
`causer`.`thumb` AS `causer-avatar`,
`causer`.`network` AS `causer-network`,
`causer`.`blocked` AS `causer-blocked`,
`causer`.`hidden` AS `causer-hidden`,
+ `causer`.`gsid` AS `causer-gsid`,
`causer`.`contact-type` AS `causer-contact-type`,
`post-delivery-data`.`postopts` AS `postopts`,
`post-delivery-data`.`inform` AS `inform`,
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-id` AS `parent-author-id`,
+ `post-thread-user`.`network` AS `parent-network`,
+ `post-thread-user`.`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`.`nick` AS `parent-author-nick`,
`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`
+ INNER 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`
STRAIGHT_JOIN `contact` AS `author` ON `author`.`id` = `post-user`.`author-id`
STRAIGHT_JOIN `contact` AS `owner` ON `owner`.`id` = `post-user`.`owner-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`
- LEFT JOIN `post-user` AS `parent-post` ON `parent-post`.`uri-id` = `post-user`.`parent-uri-id` AND `parent-post`.`uid` = `post-user`.`uid`
- LEFT JOIN `contact` AS `parent-post-author` ON `parent-post-author`.`id` = `parent-post`.`author-id`;
+ LEFT JOIN `contact` AS `parent-post-author` ON `parent-post-author`.`id` = `post-thread-user`.`author-id`;
--
-- VIEW post-thread-user-view
`post-user`.`id` AS `id`,
`post-user`.`id` AS `post-user-id`,
`post-thread-user`.`uid` AS `uid`,
- `parent-post`.`id` AS `parent`,
+ `post-thread-user`.`post-user-id` AS `parent`,
`item-uri`.`uri` AS `uri`,
`post-thread-user`.`uri-id` AS `uri-id`,
`parent-item-uri`.`uri` AS `parent-uri`,
`contact`.`pending` AS `contact-pending`,
`contact`.`rel` AS `contact-rel`,
`contact`.`uid` AS `contact-uid`,
+ `contact`.`gsid` AS `contact-gsid`,
`contact`.`contact-type` AS `contact-contact-type`,
IF (`post-user`.`network` IN ('apub', 'dfrn', 'dspr', 'stat'), true, `contact`.`writable`) AS `writable`,
`contact`.`self` AS `self`,
`author`.`addr` AS `author-addr`,
IF (`contact`.`url` = `author`.`url` AND `contact`.`name` != '', `contact`.`name`, `author`.`name`) AS `author-name`,
`author`.`nick` AS `author-nick`,
+ `author`.`alias` AS `author-alias`,
IF (`contact`.`url` = `author`.`url` AND `contact`.`thumb` != '', `contact`.`thumb`, `author`.`thumb`) AS `author-avatar`,
`author`.`network` AS `author-network`,
`author`.`blocked` AS `author-blocked`,
`owner`.`addr` AS `owner-addr`,
IF (`contact`.`url` = `owner`.`url` AND `contact`.`name` != '', `contact`.`name`, `owner`.`name`) AS `owner-name`,
`owner`.`nick` AS `owner-nick`,
+ `owner`.`alias` AS `owner-alias`,
IF (`contact`.`url` = `owner`.`url` AND `contact`.`thumb` != '', `contact`.`thumb`, `owner`.`thumb`) AS `owner-avatar`,
`owner`.`network` AS `owner-network`,
`owner`.`blocked` AS `owner-blocked`,
`owner`.`hidden` AS `owner-hidden`,
`owner`.`updated` AS `owner-updated`,
+ `owner`.`gsid` AS `owner-gsid`,
`owner`.`contact-type` AS `owner-contact-type`,
`post-thread-user`.`causer-id` AS `causer-id`,
`causer`.`uri-id` AS `causer-uri-id`,
`causer`.`addr` AS `causer-addr`,
`causer`.`name` AS `causer-name`,
`causer`.`nick` AS `causer-nick`,
+ `causer`.`alias` AS `causer-alias`,
`causer`.`thumb` AS `causer-avatar`,
`causer`.`network` AS `causer-network`,
`causer`.`blocked` AS `causer-blocked`,
`causer`.`hidden` AS `causer-hidden`,
+ `causer`.`gsid` AS `causer-gsid`,
`causer`.`contact-type` AS `causer-contact-type`,
`post-delivery-data`.`postopts` AS `postopts`,
`post-delivery-data`.`inform` AS `inform`,
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`
+ `post-thread-user`.`network` AS `parent-network`,
+ `post-thread-user`.`author-id` AS `parent-author-id`,
+ `author`.`url` AS `parent-author-link`,
+ `author`.`name` AS `parent-author-name`,
+ `author`.`nick` AS `parent-author-nick`,
+ `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` 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`
- LEFT JOIN `post-user` AS `parent-post` ON `parent-post`.`uri-id` = `post-user`.`parent-uri-id` AND `parent-post`.`uid` = `post-thread-user`.`uid`
- LEFT JOIN `contact` AS `parent-post-author` ON `parent-post-author`.`id` = `parent-post`.`author-id`;
+ LEFT JOIN `permissionset` ON `permissionset`.`id` = `post-thread-user`.`psid`;
--
-- VIEW post-view
`author`.`addr` AS `author-addr`,
`author`.`name` AS `author-name`,
`author`.`nick` AS `author-nick`,
+ `author`.`alias` AS `author-alias`,
`author`.`thumb` AS `author-avatar`,
`author`.`network` AS `author-network`,
`author`.`blocked` AS `author-blocked`,
`owner`.`addr` AS `owner-addr`,
`owner`.`name` AS `owner-name`,
`owner`.`nick` AS `owner-nick`,
+ `owner`.`alias` AS `owner-alias`,
`owner`.`thumb` AS `owner-avatar`,
`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`,
+ `owner`.`gsid` AS `owner-gsid`,
`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`,
`causer`.`nick` AS `causer-nick`,
+ `causer`.`alias` AS `causer-alias`,
`causer`.`thumb` AS `causer-avatar`,
`causer`.`network` AS `causer-network`,
`causer`.`blocked` AS `causer-blocked`,
`causer`.`hidden` AS `causer-hidden`,
`causer`.`contact-type` AS `causer-contact-type`,
+ `causer`.`gsid` AS `causer-gsid`,
`post-question`.`id` AS `question-id`,
`post-question`.`multiple` AS `question-multiple`,
`post-question`.`voters` AS `question-voters`,
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`,
+ `post-thread`.`network` AS `parent-network`,
+ `post-thread`.`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`.`nick` AS `parent-author-nick`,
`parent-post-author`.`network` AS `parent-author-network`
FROM `post`
STRAIGHT_JOIN `post-thread` ON `post-thread`.`uri-id` = `post`.`parent-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`;
+ LEFT JOIN `contact` AS `parent-post-author` ON `parent-post-author`.`id` = `post-thread`.`author-id`;
--
-- VIEW post-thread-view
`author`.`addr` AS `author-addr`,
`author`.`name` AS `author-name`,
`author`.`nick` AS `author-nick`,
+ `author`.`alias` AS `author-alias`,
`author`.`thumb` AS `author-avatar`,
`author`.`network` AS `author-network`,
`author`.`blocked` AS `author-blocked`,
`owner`.`addr` AS `owner-addr`,
`owner`.`name` AS `owner-name`,
`owner`.`nick` AS `owner-nick`,
+ `owner`.`alias` AS `owner-alias`,
`owner`.`thumb` AS `owner-avatar`,
`owner`.`network` AS `owner-network`,
`owner`.`blocked` AS `owner-blocked`,
`owner`.`hidden` AS `owner-hidden`,
`owner`.`updated` AS `owner-updated`,
+ `owner`.`gsid` AS `owner-gsid`,
`owner`.`contact-type` AS `owner-contact-type`,
`post-thread`.`causer-id` AS `causer-id`,
`causer`.`uri-id` AS `causer-uri-id`,
`causer`.`addr` AS `causer-addr`,
`causer`.`name` AS `causer-name`,
`causer`.`nick` AS `causer-nick`,
+ `causer`.`alias` AS `causer-alias`,
`causer`.`thumb` AS `causer-avatar`,
`causer`.`network` AS `causer-network`,
`causer`.`blocked` AS `causer-blocked`,
`causer`.`hidden` AS `causer-hidden`,
+ `causer`.`gsid` AS `causer-gsid`,
`causer`.`contact-type` AS `causer-contact-type`,
`post-question`.`id` AS `question-id`,
`post-question`.`multiple` AS `question-multiple`,
(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`
+ `post-thread`.`network` AS `parent-network`,
+ `post-thread`.`author-id` AS `parent-author-id`,
+ `author`.`url` AS `parent-author-link`,
+ `author`.`name` AS `parent-author-name`,
+ `author`.`nick` AS `parent-author-nick`,
+ `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 `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`;
+ LEFT JOIN `post-question` ON `post-question`.`uri-id` = `post-thread`.`uri-id`;
--
-- VIEW category-view
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
--
DROP VIEW IF EXISTS `network-item-view`;
CREATE VIEW `network-item-view` AS SELECT
`post-user`.`uri-id` AS `uri-id`,
- `parent-post`.`id` AS `parent`,
+ `post-thread-user`.`post-user-id` AS `parent`,
`post-user`.`received` AS `received`,
`post-thread-user`.`commented` AS `commented`,
`post-user`.`created` AS `created`,
`post-user`.`contact-id` AS `contact-id`,
`ownercontact`.`contact-type` AS `contact-type`
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`
- INNER JOIN `contact` ON `contact`.`id` = `post-thread-user`.`contact-id`
- LEFT JOIN `user-contact` AS `author` ON `author`.`uid` = `post-thread-user`.`uid` AND `author`.`cid` = `post-thread-user`.`author-id`
- LEFT JOIN `user-contact` AS `owner` ON `owner`.`uid` = `post-thread-user`.`uid` AND `owner`.`cid` = `post-thread-user`.`owner-id`
- INNER JOIN `contact` AS `ownercontact` ON `ownercontact`.`id` = `post-thread-user`.`owner-id`
- LEFT JOIN `post-user` AS `parent-post` ON `parent-post`.`uri-id` = `post-user`.`parent-uri-id` AND `parent-post`.`uid` = `post-user`.`uid`
+ INNER 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-thread-user`.`contact-id`
+ STRAIGHT_JOIN `contact` AS `authorcontact` ON `authorcontact`.`id` = `post-thread-user`.`author-id`
+ STRAIGHT_JOIN `contact` AS `ownercontact` ON `ownercontact`.`id` = `post-thread-user`.`owner-id`
WHERE `post-user`.`visible` AND NOT `post-user`.`deleted`
AND (NOT `contact`.`readonly` AND NOT `contact`.`blocked` AND NOT `contact`.`pending`)
AND (`post-user`.`hidden` IS NULL OR NOT `post-user`.`hidden`)
- AND (`author`.`blocked` IS NULL OR NOT `author`.`blocked`)
- AND (`owner`.`blocked` IS NULL OR NOT `owner`.`blocked`);
+ AND NOT `authorcontact`.`blocked` AND NOT `ownercontact`.`blocked`
+ AND NOT EXISTS(SELECT `cid` FROM `user-contact` WHERE `uid` = `post-thread-user`.`uid` AND `cid` IN (`authorcontact`.`id`, `ownercontact`.`id`) AND (`blocked` OR `ignored`))
+ AND NOT EXISTS(SELECT `gsid` FROM `user-gserver` WHERE `uid` = `post-thread-user`.`uid` AND `gsid` IN (`authorcontact`.`gsid`, `ownercontact`.`gsid`) AND `ignored`);
--
-- VIEW network-thread-view
DROP VIEW IF EXISTS `network-thread-view`;
CREATE VIEW `network-thread-view` AS SELECT
`post-thread-user`.`uri-id` AS `uri-id`,
- `parent-post`.`id` AS `parent`,
+ `post-thread-user`.`post-user-id` AS `parent`,
`post-thread-user`.`received` AS `received`,
`post-thread-user`.`commented` AS `commented`,
`post-thread-user`.`created` AS `created`,
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 `user-contact` AS `author` ON `author`.`uid` = `post-thread-user`.`uid` AND `author`.`cid` = `post-thread-user`.`author-id`
- LEFT JOIN `user-contact` AS `owner` ON `owner`.`uid` = `post-thread-user`.`uid` AND `owner`.`cid` = `post-thread-user`.`owner-id`
- LEFT JOIN `contact` AS `ownercontact` ON `ownercontact`.`id` = `post-thread-user`.`owner-id`
- LEFT JOIN `post-user` AS `parent-post` ON `parent-post`.`uri-id` = `post-user`.`parent-uri-id` AND `parent-post`.`uid` = `post-user`.`uid`
+ STRAIGHT_JOIN `contact` AS `authorcontact` ON `authorcontact`.`id` = `post-thread-user`.`author-id`
+ STRAIGHT_JOIN `contact` AS `ownercontact` ON `ownercontact`.`id` = `post-thread-user`.`owner-id`
WHERE `post-user`.`visible` AND NOT `post-user`.`deleted`
AND (NOT `contact`.`readonly` AND NOT `contact`.`blocked` AND NOT `contact`.`pending`)
AND (`post-thread-user`.`hidden` IS NULL OR NOT `post-thread-user`.`hidden`)
- AND (`author`.`blocked` IS NULL OR NOT `author`.`blocked`)
- AND (`owner`.`blocked` IS NULL OR NOT `owner`.`blocked`);
+ AND NOT `authorcontact`.`blocked` AND NOT `ownercontact`.`blocked`
+ AND NOT EXISTS(SELECT `cid` FROM `user-contact` WHERE `uid` = `post-thread-user`.`uid` AND `cid` IN (`authorcontact`.`id`, `ownercontact`.`id`) AND (`blocked` OR `ignored`))
+ AND NOT EXISTS(SELECT `gsid` FROM `user-gserver` WHERE `uid` = `post-thread-user`.`uid` AND `gsid` IN (`authorcontact`.`gsid`, `ownercontact`.`gsid`) AND `ignored`);
--
-- VIEW owner-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`,
`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`,
`apcontact`.`statuses_count` AS `ap-statuses_count`,
`gserver`.`site_name` AS `site_name`,
`gserver`.`platform` AS `platform`,
- `gserver`.`version` AS `version`
+ `gserver`.`version` AS `version`,
+ `gserver`.`blocked` AS `server-blocked`,
+ `gserver`.`failed` AS `server-failed`
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`,
- `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`,
`apcontact`.`statuses_count` AS `ap-statuses_count`,
`gserver`.`site_name` AS `site_name`,
`gserver`.`platform` AS `platform`,
- `gserver`.`version` AS `version`
+ `gserver`.`version` AS `version`,
+ `gserver`.`blocked` AS `server-blocked`,
+ `gserver`.`failed` AS `server-failed`
FROM `contact` AS `ucontact`
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`;
projects / friendica.git / blobdiff