Merge pull request #11289 from tobiasd/20220302-hu

[friendica.git] / database.sql

diff --git a/database.sql b/database.sql
index 9567b7ac1b8b8517a4a1e11052f0ad65e34c1bac..a36adad01588cfe0f7dec8eb30f43d7ec3208e68 100644 (file)
--- a/database.sql
+++ b/database.sql
@@ -1,6 +1,6 @@
 -- ------------------------------------------
--- Friendica 2021.12-dev (Siberian Iris)
--- DB_UPDATE_VERSION 1444
+-- Friendica 2022.05-dev (Siberian Iris)
+-- DB_UPDATE_VERSION 1452
 -- ------------------------------------------
 
 
@@ -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';
 
 --
@@ -824,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`),
@@ -1265,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',
@@ -1600,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`,
@@ -1610,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`,
@@ -1702,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`
@@ -1760,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`,