X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=d1c73cc048f3f606932d629760f3f8145188cd47;hb=903d829f4988592c102495eab82d523d7d093300;hp=02b01e25c69ec8f07a3756468a83eaea2f852038;hpb=5abc1543fdc01887d44943138d33501ea39bdfea;p=friendica.git diff --git a/database.sql b/database.sql index 02b01e25c6..d1c73cc048 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ --- Friendica 2021.09-rc (Siberian Iris) --- DB_UPDATE_VERSION 1435 +-- Friendica 2021.12-rc (Siberian Iris) +-- DB_UPDATE_VERSION 1446 -- ------------------------------------------ @@ -565,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', @@ -682,16 +681,6 @@ CREATE TABLE IF NOT EXISTS `hook` ( UNIQUE INDEX `hook_file_function` (`hook`,`file`,`function`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='addon hook registry'; --- --- TABLE host --- -CREATE TABLE IF NOT EXISTS `host` ( - `id` tinyint unsigned NOT NULL auto_increment COMMENT 'sequential ID', - `name` varchar(128) NOT NULL DEFAULT '' COMMENT 'The hostname', - PRIMARY KEY(`id`), - UNIQUE INDEX `name` (`name`) -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Hostname'; - -- -- TABLE inbox-status -- @@ -712,20 +701,23 @@ CREATE TABLE IF NOT EXISTS `inbox-status` ( CREATE TABLE IF NOT EXISTS `intro` ( `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID', `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id', - `fid` int unsigned COMMENT '', + `fid` int unsigned COMMENT 'deprecated', `contact-id` int unsigned NOT NULL DEFAULT 0 COMMENT '', + `suggest-cid` int unsigned COMMENT 'Suggested contact', `knowyou` boolean NOT NULL DEFAULT '0' COMMENT '', - `duplex` boolean NOT NULL DEFAULT '0' COMMENT '', + `duplex` boolean NOT NULL DEFAULT '0' COMMENT 'deprecated', `note` text COMMENT '', `hash` varchar(255) NOT NULL DEFAULT '' COMMENT '', `datetime` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', - `blocked` boolean NOT NULL DEFAULT '1' COMMENT '', + `blocked` boolean NOT NULL DEFAULT '0' COMMENT 'deprecated', `ignore` boolean NOT NULL DEFAULT '0' COMMENT '', PRIMARY KEY(`id`), INDEX `contact-id` (`contact-id`), + INDEX `suggest-cid` (`suggest-cid`), INDEX `uid` (`uid`), FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE, - FOREIGN KEY (`contact-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE + FOREIGN KEY (`contact-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE, + FOREIGN KEY (`suggest-cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE ) DEFAULT COLLATE utf8mb4_general_ci COMMENT=''; -- @@ -971,6 +963,7 @@ CREATE TABLE IF NOT EXISTS `photo` ( `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 '', @@ -994,6 +987,7 @@ CREATE TABLE IF NOT EXISTS `photo` ( 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'; @@ -1321,10 +1315,11 @@ CREATE TABLE IF NOT EXISTS `post-user-notification` ( -- TABLE process -- CREATE TABLE IF NOT EXISTS `process` ( - `pid` int unsigned NOT NULL COMMENT '', + `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', `command` varbinary(32) NOT NULL DEFAULT '' COMMENT '', `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', - PRIMARY KEY(`pid`), + PRIMARY KEY(`pid`,`hostname`), INDEX `command` (`command`) ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Currently running system processes'; @@ -1516,6 +1511,7 @@ CREATE TABLE IF NOT EXISTS `user-contact` ( `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', + `hidden` boolean COMMENT 'This contact is hidden from the others', `pending` boolean COMMENT '', `rel` tinyint unsigned COMMENT 'The kind of the relation between the user and the contact', `info` mediumtext COMMENT '', @@ -1526,6 +1522,8 @@ CREATE TABLE IF NOT EXISTS `user-contact` ( `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`), @@ -1697,7 +1695,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`, @@ -1705,6 +1702,7 @@ CREATE VIEW `post-user-view` AS SELECT `parent-post`.`author-id` AS `parent-author-id`, `parent-post-author`.`url` AS `parent-author-link`, `parent-post-author`.`name` AS `parent-author-name`, + `parent-post-author`.`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` @@ -1858,7 +1856,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`, @@ -2356,6 +2353,7 @@ CREATE VIEW `account-view` AS SELECT `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`, @@ -2425,6 +2423,7 @@ CREATE VIEW `account-view` AS SELECT `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; @@ -2440,6 +2439,7 @@ CREATE VIEW `account-user-view` AS SELECT `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`, @@ -2523,6 +2523,7 @@ CREATE VIEW `account-user-view` AS SELECT `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'; @@ -2580,3 +2581,23 @@ CREATE VIEW `workerqueue-view` AS SELECT FROM `process` INNER JOIN `workerqueue` ON `workerqueue`.`pid` = `process`.`pid` WHERE NOT `workerqueue`.`done`; + +-- +-- VIEW profile_field-view +-- +DROP VIEW IF EXISTS `profile_field-view`; +CREATE VIEW `profile_field-view` AS SELECT + `profile_field`.`id` AS `id`, + `profile_field`.`uid` AS `uid`, + `profile_field`.`label` AS `label`, + `profile_field`.`value` AS `value`, + `profile_field`.`order` AS `order`, + `profile_field`.`psid` AS `psid`, + `permissionset`.`allow_cid` AS `allow_cid`, + `permissionset`.`allow_gid` AS `allow_gid`, + `permissionset`.`deny_cid` AS `deny_cid`, + `permissionset`.`deny_gid` AS `deny_gid`, + `profile_field`.`created` AS `created`, + `profile_field`.`edited` AS `edited` + FROM `profile_field` + INNER JOIN `permissionset` ON `permissionset`.`id` = `profile_field`.`psid`;