-- ------------------------------------------
--- Friendica 2021.09-dev (Siberian Iris)
--- DB_UPDATE_VERSION 1431
+-- Friendica 2021.12-dev (Siberian Iris)
+-- DB_UPDATE_VERSION 1438
-- ------------------------------------------
`uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'Owner User id',
`created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '',
`updated` datetime DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of last contact update',
- `self` boolean NOT NULL DEFAULT '0' COMMENT '1 if the contact is the user him/her self',
- `remote_self` boolean NOT NULL DEFAULT '0' COMMENT '',
- `rel` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'The kind of the relation between the user and the contact',
- `duplex` boolean NOT NULL DEFAULT '0' COMMENT 'Deprecated',
`network` char(4) NOT NULL DEFAULT '' COMMENT 'Network of the contact',
- `protocol` char(4) NOT NULL DEFAULT '' COMMENT 'Protocol of the contact',
`name` varchar(255) NOT NULL DEFAULT '' COMMENT 'Name that this contact is known by',
`nick` varchar(255) NOT NULL DEFAULT '' COMMENT 'Nick- and user name of the contact',
`location` varchar(255) DEFAULT '' COMMENT '',
`about` text COMMENT '',
`keywords` text COMMENT 'public keywords (interests) of the contact',
- `gender` varchar(32) NOT NULL DEFAULT '' COMMENT 'Deprecated',
- `xmpp` varchar(255) NOT NULL DEFAULT '' COMMENT '',
- `attag` varchar(255) NOT NULL DEFAULT '' COMMENT '',
+ `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 '',
- `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)',
`header` varchar(255) COMMENT 'Header picture',
- `site-pubkey` text COMMENT 'Deprecated',
- `issued-id` varchar(255) NOT NULL DEFAULT '' COMMENT 'Deprecated',
- `dfrn-id` varchar(255) NOT NULL DEFAULT '' COMMENT 'Deprecated',
`url` varchar(255) NOT NULL DEFAULT '' COMMENT '',
`nurl` varchar(255) NOT NULL DEFAULT '' COMMENT '',
`uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the contact url',
`pubkey` text COMMENT 'RSA public key 4096 bit',
`prvkey` text COMMENT 'RSA private key 4096 bit',
`batch` varchar(255) NOT NULL DEFAULT '' COMMENT '',
- `request` varchar(255) COMMENT '',
`notify` varchar(255) COMMENT '',
`poll` varchar(255) COMMENT '',
- `confirm` varchar(255) COMMENT '',
`subscribe` varchar(255) COMMENT '',
- `poco` varchar(255) COMMENT '',
- `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 '',
- `subhub` boolean NOT NULL DEFAULT '0' COMMENT '',
- `hub-verify` varchar(255) NOT NULL DEFAULT '' COMMENT '',
`last-update` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last try to update the contact info',
`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',
- `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 '',
`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',
- `priority` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '',
`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',
- `writable` boolean NOT NULL DEFAULT '0' COMMENT '',
- `forum` boolean NOT NULL DEFAULT '0' COMMENT 'contact is a forum',
- `prv` boolean NOT NULL DEFAULT '0' COMMENT 'contact is a private group',
- `contact-type` tinyint NOT NULL DEFAULT 0 COMMENT '',
- `manually-approve` boolean COMMENT '',
- `hidden` boolean NOT NULL DEFAULT '0' COMMENT '',
+ `contact-type` tinyint NOT NULL DEFAULT 0 COMMENT 'Person, organisation, news, community, relay',
+ `manually-approve` boolean COMMENT 'Contact requests have to be approved manually',
`archive` boolean NOT NULL DEFAULT '0' COMMENT '',
- `pending` boolean NOT NULL DEFAULT '1' COMMENT '',
- `deleted` boolean NOT NULL DEFAULT '0' COMMENT 'Contact has been deleted',
- `rating` tinyint 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',
`gsid` int unsigned COMMENT 'Global Server ID',
+ `bd` date NOT NULL DEFAULT '0001-01-01' COMMENT '',
`reason` text COMMENT '',
- `closeness` tinyint unsigned NOT NULL DEFAULT 99 COMMENT '',
+ `self` boolean NOT NULL DEFAULT '0' COMMENT '1 if the contact is the user him/her self',
+ `remote_self` boolean NOT NULL DEFAULT '0' 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 '',
+ `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 '',
+ `hidden` boolean NOT NULL DEFAULT '0' COMMENT '',
+ `pending` boolean NOT NULL DEFAULT '1' COMMENT 'Contact request is pending',
+ `deleted` boolean NOT NULL DEFAULT '0' COMMENT 'Contact has been deleted',
`info` mediumtext COMMENT '',
- `profile-id` int unsigned COMMENT 'Deprecated',
- `bdyear` varchar(4) NOT NULL DEFAULT '' COMMENT '',
- `bd` date NOT NULL DEFAULT '0001-01-01' 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)',
+ `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 '',
+ `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',
+ `bdyear` varchar(4) NOT NULL DEFAULT '' COMMENT '',
+ `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',
+ `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',
+ `closeness` tinyint unsigned NOT NULL DEFAULT 99 COMMENT 'Deprecated',
+ `profile-id` int unsigned COMMENT 'Deprecated',
PRIMARY KEY(`id`),
INDEX `uid_name` (`uid`,`name`(190)),
INDEX `self_uid` (`self`,`uid`),
`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',
`addr` varchar(255) COMMENT '',
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',
- `title` varchar(255) COMMENT 'post title',
- `body` mediumtext COMMENT 'post body content',
- `private` tinyint unsigned COMMENT '0=public, 1=private, 2=unlisted',
- `wid` int unsigned COMMENT 'Workerqueue id',
`uid` mediumint unsigned COMMENT 'Owner User id',
`delayed` datetime COMMENT 'delay time',
+ `wid` int unsigned COMMENT 'Workerqueue id',
PRIMARY KEY(`id`),
UNIQUE INDEX `uid_uri` (`uid`,`uri`(190)),
INDEX `wid` (`wid`),
- FOREIGN KEY (`wid`) REFERENCES `workerqueue` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
- FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE
+ FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE,
+ 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';
--
`location` text COMMENT 'event location',
`type` varchar(20) NOT NULL DEFAULT '' COMMENT 'event or birthday',
`nofinish` boolean NOT NULL DEFAULT '0' COMMENT 'if event does have no end this is 1',
- `adjust` boolean NOT NULL DEFAULT '1' COMMENT 'adjust to timezone of the recipient (0 or 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',
`title` varchar(255) NOT NULL DEFAULT '' COMMENT '',
`desc` text COMMENT '',
`album` varchar(255) NOT NULL DEFAULT '' COMMENT 'The name of the album to which the photo belongs',
+ `photo-type` tinyint unsigned COMMENT 'User avatar, user banner, contact avatar, contact banner or default',
`filename` varchar(255) NOT NULL DEFAULT '' COMMENT '',
`type` varchar(30) NOT NULL DEFAULT 'image/jpeg',
`height` smallint unsigned NOT NULL DEFAULT 0 COMMENT '',
INDEX `uid_album_scale_created` (`uid`,`album`(32),`scale`,`created`),
INDEX `uid_album_resource-id_created` (`uid`,`album`(32),`resource-id`,`created`),
INDEX `resource-id` (`resource-id`),
+ INDEX `uid_photo-type` (`uid`,`photo-type`),
FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE RESTRICT,
FOREIGN KEY (`contact-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT
) DEFAULT COLLATE utf8mb4_general_ci COMMENT='photo storage';
`education` text COMMENT 'Deprecated',
`contact` text COMMENT 'Deprecated',
`homepage` varchar(255) NOT NULL DEFAULT '' COMMENT '',
- `xmpp` varchar(255) NOT NULL DEFAULT '' COMMENT '',
+ `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 '',
`publish` boolean NOT NULL DEFAULT '0' COMMENT 'publish default profile in local directory',
PRIMARY KEY(`id`)
) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Data stored by Database storage backend';
+--
+-- TABLE subscription
+--
+CREATE TABLE IF NOT EXISTS `subscription` (
+ `id` int unsigned NOT NULL auto_increment COMMENT 'Auto incremented image data id',
+ `application-id` int unsigned NOT NULL COMMENT '',
+ `uid` mediumint unsigned NOT NULL COMMENT 'Owner User id',
+ `endpoint` varchar(511) COMMENT 'Endpoint URL',
+ `pubkey` varchar(127) COMMENT 'User agent public key',
+ `secret` varchar(32) COMMENT 'Auth secret',
+ `follow` boolean COMMENT '',
+ `favourite` boolean COMMENT '',
+ `reblog` boolean COMMENT '',
+ `mention` boolean COMMENT '',
+ `poll` boolean COMMENT '',
+ `follow_request` boolean COMMENT '',
+ `status` boolean COMMENT '',
+ PRIMARY KEY(`id`),
+ UNIQUE INDEX `application-id_uid` (`application-id`,`uid`),
+ INDEX `uid_application-id` (`uid`,`application-id`),
+ 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='Push Subscription for the API';
+
--
-- TABLE userd
--
CREATE TABLE IF NOT EXISTS `user-contact` (
`cid` int unsigned NOT NULL DEFAULT 0 COMMENT 'Contact id of the linked public contact',
`uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id',
+ `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the contact url',
`blocked` boolean COMMENT 'Contact is completely blocked for this user',
`ignored` boolean COMMENT 'Posts from this contact are ignored',
`collapsed` boolean COMMENT 'Posts from this contact are collapsed',
+ `pending` boolean COMMENT '',
+ `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 '',
+ `ffi_keyword_denylist` text COMMENT '',
+ `subhub` boolean COMMENT '',
+ `hub-verify` varchar(255) COMMENT '',
+ `protocol` char(4) COMMENT 'Protocol of the contact',
+ `rating` tinyint COMMENT 'Automatically detected feed poll frequency',
+ `priority` tinyint unsigned COMMENT 'Feed poll priority',
PRIMARY KEY(`uid`,`cid`),
INDEX `cid` (`cid`),
+ UNIQUE INDEX `uri-id_uid` (`uri-id`,`uid`),
FOREIGN KEY (`cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
- FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE
+ 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
) DEFAULT COLLATE utf8mb4_general_ci COMMENT='User specific public contact data';
--
`event`.`location` AS `event-location`,
`event`.`type` AS `event-type`,
`event`.`nofinish` AS `event-nofinish`,
- `event`.`adjust` AS `event-adjust`,
`event`.`ignore` AS `event-ignore`,
`diaspora-interaction`.`interaction` AS `signed_text`,
`parent-item-uri`.`guid` AS `parent-guid`,
`event`.`location` AS `event-location`,
`event`.`type` AS `event-type`,
`event`.`nofinish` AS `event-nofinish`,
- `event`.`adjust` AS `event-adjust`,
`event`.`ignore` AS `event-ignore`,
`diaspora-interaction`.`interaction` AS `signed_text`,
`parent-item-uri`.`guid` AS `parent-guid`,
`contact`.`location` AS `location`,
`contact`.`about` AS `about`,
`contact`.`keywords` AS `keywords`,
- `contact`.`gender` AS `gender`,
`contact`.`xmpp` AS `xmpp`,
+ `contact`.`matrix` AS `matrix`,
`contact`.`attag` AS `attag`,
`contact`.`avatar` AS `avatar`,
`contact`.`photo` AS `photo`,
`contact`.`poll` AS `poll`,
`contact`.`confirm` AS `confirm`,
`contact`.`poco` AS `poco`,
- `contact`.`usehub` AS `usehub`,
`contact`.`subhub` AS `subhub`,
`contact`.`hub-verify` AS `hub-verify`,
`contact`.`last-update` AS `last-update`,
`contact`.`sensitive` AS `sensitive`,
`contact`.`baseurl` AS `baseurl`,
`contact`.`reason` AS `reason`,
- `contact`.`closeness` AS `closeness`,
`contact`.`info` AS `info`,
- `contact`.`profile-id` AS `profile-id`,
`contact`.`bdyear` AS `bdyear`,
`contact`.`bd` AS `bd`,
`contact`.`notify_new_posts` AS `notify_new_posts`,
`contact`.`url` AS `url`,
`contact`.`nurl` AS `nurl`,
`contact`.`uri-id` AS `uri-id`,
+ `item-uri`.`guid` AS `guid`,
`contact`.`addr` AS `addr`,
`contact`.`alias` AS `alias`,
`contact`.`name` AS `name`,
`contact`.`about` AS `about`,
`contact`.`keywords` AS `keywords`,
`contact`.`xmpp` AS `xmpp`,
+ `contact`.`matrix` AS `matrix`,
`contact`.`avatar` AS `avatar`,
`contact`.`photo` AS `photo`,
`contact`.`thumb` AS `thumb`,
`apcontact`.`followers_count` AS `ap-followers_count`,
`apcontact`.`statuses_count` AS `ap-statuses_count`
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`
WHERE `contact`.`uid` = 0;
`contact`.`url` AS `url`,
`contact`.`nurl` AS `nurl`,
`contact`.`uri-id` AS `uri-id`,
+ `item-uri`.`guid` AS `guid`,
`contact`.`addr` AS `addr`,
`contact`.`alias` AS `alias`,
`contact`.`name` AS `name`,
`contact`.`about` AS `about`,
`contact`.`keywords` AS `keywords`,
`contact`.`xmpp` AS `xmpp`,
+ `contact`.`matrix` AS `matrix`,
`contact`.`avatar` AS `avatar`,
`contact`.`photo` AS `photo`,
`contact`.`thumb` AS `thumb`,
`contact`.`network` AS `network`,
`ucontact`.`protocol` AS `protocol`,
`contact`.`location` AS `location`,
- `contact`.`attag` AS `attag`,
+ `ucontact`.`attag` AS `attag`,
`contact`.`pubkey` AS `pubkey`,
`contact`.`prvkey` AS `prvkey`,
`contact`.`subscribe` AS `subscribe`,
`contact`.`sensitive` AS `sensitive`,
`contact`.`baseurl` AS `baseurl`,
`contact`.`gsid` AS `gsid`,
- `contact`.`info` AS `info`,
+ `ucontact`.`info` AS `info`,
`contact`.`bdyear` AS `bdyear`,
`contact`.`bd` AS `bd`,
`contact`.`poco` AS `poco`,
`apcontact`.`statuses_count` AS `ap-statuses_count`
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';