-- ------------------------------------------
--- Friendica 2021.09-rc (Siberian Iris)
--- DB_UPDATE_VERSION 1435
+-- Friendica 2022.05-dev (Siberian Iris)
+-- DB_UPDATE_VERSION 1452
-- ------------------------------------------
`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 '',
`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',
`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)),
`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';
--
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
--
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='';
--
`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`),
`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';
`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',
-- 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';
`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 '',
`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`,
`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`,
`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`,
`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`
`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`,
`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`.`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`,
`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`,
`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';
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`;