X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=database.sql;h=ffe020203319fba875772d6cae57ea771e6c060c;hb=1fae0123ec0f20d085b87b468a8a0bee32754ecf;hp=793aae3863a7d4c05e7936b4a22f43ba4e38635a;hpb=f1d3f60499a325557be866fedd31b46093d477ae;p=friendica.git diff --git a/database.sql b/database.sql index 793aae3863..ffe0202033 100644 --- a/database.sql +++ b/database.sql @@ -1,6 +1,6 @@ -- ------------------------------------------ --- Friendica 2021.12-dev (Siberian Iris) --- DB_UPDATE_VERSION 1439 +-- Friendica 2022.05-dev (Siberian Iris) +-- DB_UPDATE_VERSION 1453 -- ------------------------------------------ @@ -16,6 +16,11 @@ CREATE TABLE IF NOT EXISTS `gserver` ( `info` text COMMENT '', `register_policy` tinyint NOT NULL DEFAULT 0 COMMENT '', `registered-users` int unsigned NOT NULL DEFAULT 0 COMMENT 'Number of registered users', + `active-week-users` int unsigned COMMENT 'Number of active users in the last week', + `active-month-users` int unsigned COMMENT 'Number of active users in the last month', + `active-halfyear-users` int unsigned COMMENT 'Number of active users in the last six month', + `local-posts` int unsigned COMMENT 'Number of local posts', + `local-comments` int unsigned COMMENT 'Number of local comments', `directory-type` tinyint DEFAULT 0 COMMENT 'Type of directory service (Poco, Mastodon)', `poco` varchar(255) NOT NULL DEFAULT '' COMMENT '', `noscrape` varchar(255) NOT NULL DEFAULT '' COMMENT '', @@ -601,6 +606,9 @@ CREATE TABLE IF NOT EXISTS `fcontact` ( `alias` varchar(255) NOT NULL DEFAULT '' COMMENT '', `pubkey` text COMMENT '', `updated` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '', + `interacting_count` int unsigned DEFAULT 0 COMMENT 'Number of contacts this contact interactes with', + `interacted_count` int unsigned DEFAULT 0 COMMENT 'Number of contacts that interacted with this contact', + `post_count` int unsigned DEFAULT 0 COMMENT 'Number of posts and comments', PRIMARY KEY(`id`), INDEX `addr` (`addr`(32)), UNIQUE INDEX `url` (`url`(190)), @@ -636,10 +644,13 @@ CREATE TABLE IF NOT EXISTS `group` ( `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', PRIMARY KEY(`id`), INDEX `uid` (`uid`), - FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE + 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'; -- @@ -681,16 +692,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 -- @@ -715,14 +716,15 @@ CREATE TABLE IF NOT EXISTS `intro` ( `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, @@ -833,7 +835,8 @@ CREATE TABLE IF NOT EXISTS `notification` ( `target-uri-id` int unsigned COMMENT 'Item-uri id of the related post', `parent-uri-id` int unsigned COMMENT 'Item-uri id of the parent of the related post', `created` datetime COMMENT '', - `seen` boolean DEFAULT '0' COMMENT '', + `seen` boolean DEFAULT '0' COMMENT 'Seen on the desktop', + `dismissed` boolean DEFAULT '0' COMMENT 'Dismissed via the API', PRIMARY KEY(`id`), UNIQUE INDEX `uid_vid_type_actor-id_target-uri-id` (`uid`,`vid`,`type`,`actor-id`,`target-uri-id`), INDEX `vid` (`vid`), @@ -879,7 +882,7 @@ CREATE TABLE IF NOT EXISTS `notify` ( 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, FOREIGN KEY (`parent-uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE -) DEFAULT COLLATE utf8mb4_general_ci COMMENT='notifications'; +) DEFAULT COLLATE utf8mb4_general_ci COMMENT='[Deprecated] User notifications'; -- -- TABLE notify-threads @@ -1274,7 +1277,7 @@ CREATE TABLE IF NOT EXISTS `post-thread-user` ( `wall` boolean NOT NULL DEFAULT '0' COMMENT 'This item was posted to the wall of uid', `mention` boolean NOT NULL DEFAULT '0' COMMENT '', `pubmail` boolean NOT NULL DEFAULT '0' COMMENT '', - `forum_mode` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '', + `forum_mode` tinyint unsigned NOT NULL DEFAULT 0 COMMENT 'Deprecated', `contact-id` int unsigned NOT NULL DEFAULT 0 COMMENT 'contact.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', @@ -1324,10 +1327,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'; @@ -1519,6 +1523,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 '', @@ -1607,7 +1612,6 @@ CREATE VIEW `post-user-view` AS SELECT `post-user`.`deleted` AS `deleted`, `post-user`.`origin` AS `origin`, `post-thread-user`.`origin` AS `parent-origin`, - `post-thread-user`.`forum_mode` AS `forum_mode`, `post-thread-user`.`mention` AS `mention`, `post-user`.`global` AS `global`, `post-user`.`network` AS `network`, @@ -1617,7 +1621,7 @@ CREATE VIEW `post-user-view` AS SELECT `post-content`.`title` AS `title`, `post-content`.`content-warning` AS `content-warning`, `post-content`.`raw-body` AS `raw-body`, - `post-content`.`body` AS `body`, + IFNULL (`post-content`.`body`, '') AS `body`, `post-content`.`rendered-hash` AS `rendered-hash`, `post-content`.`rendered-html` AS `rendered-html`, `post-content`.`language` AS `language`, @@ -1709,6 +1713,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` @@ -1767,7 +1772,6 @@ CREATE VIEW `post-thread-user-view` AS SELECT `post-thread-user`.`unseen` AS `unseen`, `post-user`.`deleted` AS `deleted`, `post-thread-user`.`origin` AS `origin`, - `post-thread-user`.`forum_mode` AS `forum_mode`, `post-thread-user`.`mention` AS `mention`, `post-user`.`global` AS `global`, `post-thread-user`.`network` AS `network`, @@ -2408,10 +2412,8 @@ CREATE VIEW `account-view` AS SELECT `contact`.`archive` AS `archive`, `contact`.`deleted` AS `deleted`, `contact`.`blocked` AS `blocked`, - `contact`.`request` AS `dfrn-request`, `contact`.`notify` AS `dfrn-notify`, `contact`.`poll` AS `dfrn-poll`, - `contact`.`confirm` AS `dfrn-confirm`, `fcontact`.`guid` AS `diaspora-guid`, `fcontact`.`batch` AS `diaspora-batch`, `fcontact`.`notify` AS `diaspora-notify`, @@ -2427,11 +2429,15 @@ CREATE VIEW `account-view` AS SELECT `apcontact`.`generator` AS `ap-generator`, `apcontact`.`following_count` AS `ap-following_count`, `apcontact`.`followers_count` AS `ap-followers_count`, - `apcontact`.`statuses_count` AS `ap-statuses_count` + `apcontact`.`statuses_count` AS `ap-statuses_count`, + `gserver`.`site_name` AS `site_name`, + `gserver`.`platform` AS `platform`, + `gserver`.`version` AS `version` 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 `gserver` ON `gserver`.`id` = contact.`gsid` WHERE `contact`.`uid` = 0; -- @@ -2507,10 +2513,8 @@ CREATE VIEW `account-user-view` AS SELECT `ucontact`.`subhub` AS `subhub`, `ucontact`.`hub-verify` AS `hub-verify`, `ucontact`.`reason` AS `reason`, - `contact`.`request` AS `dfrn-request`, `contact`.`notify` AS `dfrn-notify`, `contact`.`poll` AS `dfrn-poll`, - `contact`.`confirm` AS `dfrn-confirm`, `fcontact`.`guid` AS `diaspora-guid`, `fcontact`.`batch` AS `diaspora-batch`, `fcontact`.`notify` AS `diaspora-notify`, @@ -2526,12 +2530,16 @@ CREATE VIEW `account-user-view` AS SELECT `apcontact`.`generator` AS `ap-generator`, `apcontact`.`following_count` AS `ap-following_count`, `apcontact`.`followers_count` AS `ap-followers_count`, - `apcontact`.`statuses_count` AS `ap-statuses_count` + `apcontact`.`statuses_count` AS `ap-statuses_count`, + `gserver`.`site_name` AS `site_name`, + `gserver`.`platform` AS `platform`, + `gserver`.`version` AS `version` 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 `fcontact` ON `fcontact`.`uri-id` = `ucontact`.`uri-id` AND `fcontact`.`network` = 'dspr' + LEFT JOIN `gserver` ON `gserver`.`id` = contact.`gsid`; -- -- VIEW pending-view @@ -2587,3 +2595,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`;