X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=e91c8b2f6934421e46c875f7cd5ce4c31749d86c;hb=8357a70c4bc7a104373be2ddde6e8d918ea5721b;hp=3c2feb59f1c8c5ef25bc414382c38ce312404085;hpb=bee04f86ee716aa9271a262ad6b8ceed26aa4a6e;p=friendica.git diff --git a/database.sql b/database.sql index 3c2feb59f1..e91c8b2f69 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ --- Friendica 2021.09-dev (Siberian Iris) --- DB_UPDATE_VERSION 1432 +-- Friendica 2021.12-dev (Siberian Iris) +-- DB_UPDATE_VERSION 1436 -- ------------------------------------------ @@ -114,29 +114,16 @@ CREATE TABLE IF NOT EXISTS `contact` ( `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 'XMPP address', `matrix` varchar(255) NOT NULL DEFAULT '' COMMENT 'Matrix address', - `attag` varchar(255) NOT NULL DEFAULT '' COMMENT '', `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', @@ -145,54 +132,67 @@ CREATE TABLE IF NOT EXISTS `contact` ( `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`), @@ -332,6 +332,8 @@ CREATE TABLE IF NOT EXISTS `apcontact` ( `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 '', @@ -563,7 +565,6 @@ CREATE TABLE IF NOT EXISTS `event` ( `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', @@ -1470,6 +1471,30 @@ CREATE TABLE IF NOT EXISTS `storage` ( 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 -- @@ -1486,13 +1511,28 @@ CREATE TABLE IF NOT EXISTS `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'; -- @@ -1658,7 +1698,6 @@ CREATE VIEW `post-user-view` AS SELECT `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`, @@ -1819,7 +1858,6 @@ CREATE VIEW `post-thread-user-view` AS SELECT `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`, @@ -2199,7 +2237,6 @@ CREATE VIEW `owner-view` AS SELECT `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`, @@ -2221,7 +2258,6 @@ CREATE VIEW `owner-view` AS SELECT `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`, @@ -2250,9 +2286,7 @@ CREATE VIEW `owner-view` AS SELECT `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`, @@ -2426,7 +2460,7 @@ CREATE VIEW `account-user-view` AS SELECT `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`, @@ -2442,7 +2476,7 @@ CREATE VIEW `account-user-view` AS SELECT `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`,