-- ------------------------------------------
--- Friendica 2022.12-dev (Giant Rhubarb)
--- DB_UPDATE_VERSION 1496
+-- Friendica 2023.03-dev (Giant Rhubarb)
+-- DB_UPDATE_VERSION 1514
-- ------------------------------------------
`last_poco_query` datetime DEFAULT '0001-01-01 00:00:00' COMMENT '',
`last_contact` datetime DEFAULT '0001-01-01 00:00:00' COMMENT 'Last successful connection request',
`last_failure` datetime DEFAULT '0001-01-01 00:00:00' COMMENT 'Last failed connection request',
+ `blocked` boolean COMMENT 'Server is blocked',
`failed` boolean COMMENT 'Connection failed',
`next_contact` datetime DEFAULT '0001-01-01 00:00:00' COMMENT 'Next connection request',
PRIMARY KEY(`id`),
`language` varchar(32) NOT NULL DEFAULT 'en' COMMENT 'default language',
`register_date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'timestamp of registration',
`login_date` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'timestamp of last login',
+ `last-activity` date COMMENT 'Day of the last activity',
`default-location` varchar(255) NOT NULL DEFAULT '' COMMENT 'Default for item.location',
`allow_location` boolean NOT NULL DEFAULT '0' COMMENT '1 allows to display the location',
`theme` varchar(255) NOT NULL DEFAULT '' COMMENT 'user theme preference',
`xmpp` varchar(255) NOT NULL DEFAULT '' COMMENT 'XMPP address',
`matrix` varchar(255) NOT NULL DEFAULT '' COMMENT 'Matrix address',
`avatar` varbinary(383) NOT NULL DEFAULT '' COMMENT '',
+ `blurhash` varbinary(255) COMMENT 'BlurHash representation of the avatar',
`header` varbinary(383) COMMENT 'Header picture',
`url` varbinary(383) NOT NULL DEFAULT '' COMMENT '',
`nurl` varbinary(383) NOT NULL DEFAULT '' COMMENT '',
--
CREATE TABLE IF NOT EXISTS `config` (
`id` int unsigned NOT NULL auto_increment COMMENT '',
- `cat` varbinary(50) NOT NULL DEFAULT '' COMMENT '',
- `k` varbinary(50) NOT NULL DEFAULT '' COMMENT '',
+ `cat` varbinary(50) NOT NULL DEFAULT '' COMMENT 'The category of the entry',
+ `k` varbinary(50) NOT NULL DEFAULT '' COMMENT 'The key of the entry',
`v` mediumtext COMMENT '',
PRIMARY KEY(`id`),
UNIQUE INDEX `cat_k` (`cat`,`k`)
FOREIGN KEY (`wid`) REFERENCES `workerqueue` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Posts that are about to be distributed at a later time';
+--
+-- TABLE delivery-queue
+--
+CREATE TABLE IF NOT EXISTS `delivery-queue` (
+ `gsid` int unsigned NOT NULL COMMENT 'Target server',
+ `uri-id` int unsigned NOT NULL COMMENT 'Delivered post',
+ `created` datetime COMMENT '',
+ `command` varbinary(32) COMMENT '',
+ `cid` int unsigned COMMENT 'Target contact',
+ `uid` mediumint unsigned COMMENT 'Delivering user',
+ `failed` tinyint DEFAULT 0 COMMENT 'Number of times the delivery has failed',
+ PRIMARY KEY(`uri-id`,`gsid`),
+ INDEX `gsid_created` (`gsid`,`created`),
+ INDEX `uid` (`uid`),
+ INDEX `cid` (`cid`),
+ FOREIGN KEY (`gsid`) REFERENCES `gserver` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT,
+ FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
+ FOREIGN KEY (`cid`) REFERENCES `contact` (`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='Delivery data for posts for the batch processing';
+
+--
+-- TABLE diaspora-contact
+--
+CREATE TABLE IF NOT EXISTS `diaspora-contact` (
+ `uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the contact URL',
+ `addr` varchar(255) COMMENT '',
+ `alias` varchar(255) COMMENT '',
+ `nick` varchar(255) COMMENT '',
+ `name` varchar(255) COMMENT '',
+ `given-name` varchar(255) COMMENT '',
+ `family-name` varchar(255) COMMENT '',
+ `photo` varchar(255) COMMENT '',
+ `photo-medium` varchar(255) COMMENT '',
+ `photo-small` varchar(255) COMMENT '',
+ `batch` varchar(255) COMMENT '',
+ `notify` varchar(255) COMMENT '',
+ `poll` varchar(255) COMMENT '',
+ `subscribe` varchar(255) COMMENT '',
+ `searchable` boolean COMMENT '',
+ `pubkey` text COMMENT '',
+ `gsid` int unsigned COMMENT 'Global Server ID',
+ `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' 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(`uri-id`),
+ UNIQUE INDEX `addr` (`addr`),
+ INDEX `alias` (`alias`),
+ INDEX `gsid` (`gsid`),
+ FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
+ FOREIGN KEY (`gsid`) REFERENCES `gserver` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT
+) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Diaspora compatible contacts - used in the Diaspora implementation';
+
--
-- TABLE diaspora-interaction
--
FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Events';
---
--- TABLE fcontact
---
-CREATE TABLE IF NOT EXISTS `fcontact` (
- `id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
- `guid` varbinary(255) NOT NULL DEFAULT '' COMMENT 'unique id',
- `url` varbinary(383) NOT NULL DEFAULT '' COMMENT '',
- `uri-id` int unsigned COMMENT 'Id of the item-uri table entry that contains the fcontact url',
- `name` varchar(255) NOT NULL DEFAULT '' COMMENT '',
- `photo` varbinary(383) NOT NULL DEFAULT '' COMMENT '',
- `request` varbinary(383) NOT NULL DEFAULT '' COMMENT '',
- `nick` varchar(255) NOT NULL DEFAULT '' COMMENT '',
- `addr` varchar(255) NOT NULL DEFAULT '' COMMENT '',
- `batch` varbinary(383) NOT NULL DEFAULT '' COMMENT '',
- `notify` varbinary(383) NOT NULL DEFAULT '' COMMENT '',
- `poll` varbinary(383) NOT NULL DEFAULT '' COMMENT '',
- `confirm` varbinary(383) NOT NULL DEFAULT '' COMMENT '',
- `priority` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '',
- `network` char(4) NOT NULL DEFAULT '' COMMENT '',
- `alias` varbinary(383) NOT NULL DEFAULT '' COMMENT '',
- `pubkey` text COMMENT '',
- `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' 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)),
- UNIQUE INDEX `uri-id` (`uri-id`),
- FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
-) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Diaspora compatible contacts - used in the Diaspora implementation';
-
--
-- TABLE fetch-entry
--
CREATE TABLE IF NOT EXISTS `inbox-status` (
`url` varbinary(383) NOT NULL COMMENT 'URL of the inbox',
`uri-id` int unsigned COMMENT 'Item-uri id of inbox url',
+ `gsid` int unsigned COMMENT 'ID of the related server',
`created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Creation date of this entry',
`success` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last successful delivery',
`failure` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date of the last failed delivery',
`shared` boolean NOT NULL DEFAULT '0' COMMENT 'Is it a shared inbox?',
PRIMARY KEY(`url`),
INDEX `uri-id` (`uri-id`),
- FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
+ INDEX `gsid` (`gsid`),
+ FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
+ FOREIGN KEY (`gsid`) REFERENCES `gserver` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT
) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Status of ActivityPub inboxes';
--
FOREIGN KEY (`suggest-cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
) DEFAULT COLLATE utf8mb4_general_ci COMMENT='';
+--
+-- TABLE key-value
+--
+CREATE TABLE IF NOT EXISTS `key-value` (
+ `k` varbinary(50) NOT NULL COMMENT '',
+ `v` mediumtext COMMENT '',
+ `updated_at` int unsigned NOT NULL COMMENT 'timestamp of the last update',
+ PRIMARY KEY(`k`)
+) DEFAULT COLLATE utf8mb4_general_ci COMMENT='A key value storage';
+
--
-- TABLE locks
--
`height` smallint unsigned NOT NULL DEFAULT 0 COMMENT '',
`width` smallint unsigned NOT NULL DEFAULT 0 COMMENT '',
`datasize` int unsigned NOT NULL DEFAULT 0 COMMENT '',
+ `blurhash` varbinary(255) COMMENT 'BlurHash representation of the photo',
`data` mediumblob NOT NULL COMMENT '',
`scale` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '',
`profile` boolean NOT NULL DEFAULT '0' COMMENT '',
`uri-id` int unsigned NOT NULL COMMENT 'Id of the item-uri table entry that contains the item uri',
`url` varbinary(511) NOT NULL COMMENT 'External URL',
`mimetype` varchar(60) COMMENT '',
+ `height` smallint unsigned COMMENT 'Height of the media',
+ `width` smallint unsigned COMMENT 'Width of the media',
+ `blurhash` varbinary(255) COMMENT 'BlurHash representation of the link',
PRIMARY KEY(`id`),
UNIQUE INDEX `uri-id-url` (`uri-id`,`url`),
FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
`height` smallint unsigned COMMENT 'Height of the media',
`width` smallint unsigned COMMENT 'Width of the media',
`size` bigint unsigned COMMENT 'Media size',
+ `blurhash` varbinary(255) COMMENT 'BlurHash representation of the image',
`preview` varbinary(512) COMMENT 'Preview URL',
`preview-height` smallint unsigned COMMENT 'Height of the preview picture',
`preview-width` smallint unsigned COMMENT 'Width of the preview picture',
`event-id` int unsigned COMMENT 'Used to link to the event.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',
- `notification-type` tinyint unsigned NOT NULL DEFAULT 0 COMMENT '',
+ `notification-type` smallint unsigned NOT NULL DEFAULT 0 COMMENT '',
`wall` boolean NOT NULL DEFAULT '0' COMMENT 'This item was posted to the wall of uid',
`origin` boolean NOT NULL DEFAULT '0' COMMENT 'item originated at this site',
`psid` int unsigned COMMENT 'ID of the permission set of this post',
CREATE TABLE IF NOT EXISTS `report` (
`id` int unsigned NOT NULL auto_increment COMMENT 'sequential ID',
`uid` mediumint unsigned COMMENT 'Reporting user',
+ `reporter-id` int unsigned COMMENT 'Reporting contact',
`cid` int unsigned NOT NULL COMMENT 'Reported contact',
`comment` text COMMENT 'Report',
+ `category` varchar(20) COMMENT 'Category of the report (spam, violation, other)',
+ `rules` text COMMENT 'Violated rules',
`forward` boolean COMMENT 'Forward the report to the remote server',
`created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '',
`status` tinyint unsigned COMMENT 'Status of the report',
PRIMARY KEY(`id`),
INDEX `uid` (`uid`),
INDEX `cid` (`cid`),
+ INDEX `reporter-id` (`reporter-id`),
FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE,
+ FOREIGN KEY (`reporter-id`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE,
FOREIGN KEY (`cid`) REFERENCES `contact` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
) DEFAULT COLLATE utf8mb4_general_ci COMMENT='';
`user`.`language` AS `language`,
`user`.`register_date` AS `register_date`,
`user`.`login_date` AS `login_date`,
+ `user`.`last-activity` AS `last-activity`,
`user`.`default-location` AS `default-location`,
`user`.`allow_location` AS `allow_location`,
`user`.`theme` AS `theme`,
`contact`.`blocked` AS `blocked`,
`contact`.`notify` AS `dfrn-notify`,
`contact`.`poll` AS `dfrn-poll`,
- `fcontact`.`guid` AS `diaspora-guid`,
- `fcontact`.`batch` AS `diaspora-batch`,
- `fcontact`.`notify` AS `diaspora-notify`,
- `fcontact`.`poll` AS `diaspora-poll`,
- `fcontact`.`alias` AS `diaspora-alias`,
+ `item-uri`.`guid` AS `diaspora-guid`,
+ `diaspora-contact`.`batch` AS `diaspora-batch`,
+ `diaspora-contact`.`notify` AS `diaspora-notify`,
+ `diaspora-contact`.`poll` AS `diaspora-poll`,
+ `diaspora-contact`.`alias` AS `diaspora-alias`,
`apcontact`.`uuid` AS `ap-uuid`,
`apcontact`.`type` AS `ap-type`,
`apcontact`.`following` AS `ap-following`,
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 `diaspora-contact` ON `diaspora-contact`.`uri-id` = contact.`uri-id`
LEFT JOIN `gserver` ON `gserver`.`id` = contact.`gsid`
WHERE `contact`.`uid` = 0;
`ucontact`.`reason` AS `reason`,
`contact`.`notify` AS `dfrn-notify`,
`contact`.`poll` AS `dfrn-poll`,
- `fcontact`.`guid` AS `diaspora-guid`,
- `fcontact`.`batch` AS `diaspora-batch`,
- `fcontact`.`notify` AS `diaspora-notify`,
- `fcontact`.`poll` AS `diaspora-poll`,
- `fcontact`.`alias` AS `diaspora-alias`,
- `fcontact`.`interacting_count` AS `diaspora-interacting_count`,
- `fcontact`.`interacted_count` AS `diaspora-interacted_count`,
- `fcontact`.`post_count` AS `diaspora-post_count`,
+ `item-uri`.`guid` AS `diaspora-guid`,
+ `diaspora-contact`.`batch` AS `diaspora-batch`,
+ `diaspora-contact`.`notify` AS `diaspora-notify`,
+ `diaspora-contact`.`poll` AS `diaspora-poll`,
+ `diaspora-contact`.`alias` AS `diaspora-alias`,
+ `diaspora-contact`.`interacting_count` AS `diaspora-interacting_count`,
+ `diaspora-contact`.`interacted_count` AS `diaspora-interacted_count`,
+ `diaspora-contact`.`post_count` AS `diaspora-post_count`,
`apcontact`.`uuid` AS `ap-uuid`,
`apcontact`.`type` AS `ap-type`,
`apcontact`.`following` AS `ap-following`,
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 `diaspora-contact` ON `diaspora-contact`.`uri-id` = `ucontact`.`uri-id`
LEFT JOIN `gserver` ON `gserver`.`id` = contact.`gsid`;
--
`profile_field`.`edited` AS `edited`
FROM `profile_field`
INNER JOIN `permissionset` ON `permissionset`.`id` = `profile_field`.`psid`;
+
+--
+-- VIEW diaspora-contact-view
+--
+DROP VIEW IF EXISTS `diaspora-contact-view`;
+CREATE VIEW `diaspora-contact-view` AS SELECT
+ `diaspora-contact`.`uri-id` AS `uri-id`,
+ `item-uri`.`uri` AS `url`,
+ `item-uri`.`guid` AS `guid`,
+ `diaspora-contact`.`addr` AS `addr`,
+ `diaspora-contact`.`alias` AS `alias`,
+ `diaspora-contact`.`nick` AS `nick`,
+ `diaspora-contact`.`name` AS `name`,
+ `diaspora-contact`.`given-name` AS `given-name`,
+ `diaspora-contact`.`family-name` AS `family-name`,
+ `diaspora-contact`.`photo` AS `photo`,
+ `diaspora-contact`.`photo-medium` AS `photo-medium`,
+ `diaspora-contact`.`photo-small` AS `photo-small`,
+ `diaspora-contact`.`batch` AS `batch`,
+ `diaspora-contact`.`notify` AS `notify`,
+ `diaspora-contact`.`poll` AS `poll`,
+ `diaspora-contact`.`subscribe` AS `subscribe`,
+ `diaspora-contact`.`searchable` AS `searchable`,
+ `diaspora-contact`.`pubkey` AS `pubkey`,
+ `gserver`.`url` AS `baseurl`,
+ `diaspora-contact`.`gsid` AS `gsid`,
+ `diaspora-contact`.`created` AS `created`,
+ `diaspora-contact`.`updated` AS `updated`,
+ `diaspora-contact`.`interacting_count` AS `interacting_count`,
+ `diaspora-contact`.`interacted_count` AS `interacted_count`,
+ `diaspora-contact`.`post_count` AS `post_count`
+ FROM `diaspora-contact`
+ INNER JOIN `item-uri` ON `item-uri`.`id` = `diaspora-contact`.`uri-id`
+ LEFT JOIN `gserver` ON `gserver`.`id` = `diaspora-contact`.`gsid`;
projects / friendica.git / blobdiff