Same blanks removed

[friendica.git] / database.sql

diff --git a/database.sql b/database.sql
index 0067cc8e8d76e1641498ea3dd86a809b2d9571dd..602fe08b2dce6d30b0146dbb48aa7669a62722ff 100644 (file)
--- a/database.sql
+++ b/database.sql
@@ -88,6 +88,7 @@ CREATE TABLE IF NOT EXISTS `user` (
         PRIMARY KEY(`uid`),
         INDEX `nickname` (`nickname`(32)),
         INDEX `parent-uid` (`parent-uid`),
+        INDEX `guid` (`guid`),
        FOREIGN KEY (`parent-uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE
 ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='The local users';
 
@@ -257,7 +258,8 @@ CREATE TABLE IF NOT EXISTS `permissionset` (
 CREATE TABLE IF NOT EXISTS `verb` (
        `id` smallint unsigned NOT NULL auto_increment,
        `name` varchar(100) NOT NULL DEFAULT '' COMMENT '',
-        PRIMARY KEY(`id`)
+        PRIMARY KEY(`id`),
+        INDEX `name` (`name`)
 ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Activity Verbs';
 
 --
@@ -313,6 +315,7 @@ CREATE TABLE IF NOT EXISTS `addon` (
        `timestamp` int unsigned NOT NULL DEFAULT 0 COMMENT 'file timestamp to check for reloads',
        `plugin_admin` boolean NOT NULL DEFAULT '0' COMMENT '1 = has admin config, 0 = has no admin config',
         PRIMARY KEY(`id`),
+        INDEX `installed_name` (`installed`,`name`),
         UNIQUE INDEX `name` (`name`)
 ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='registered addons';
 
@@ -414,7 +417,8 @@ CREATE TABLE IF NOT EXISTS `challenge` (
        `expire` int unsigned NOT NULL DEFAULT 0 COMMENT '',
        `type` varchar(255) NOT NULL DEFAULT '' COMMENT '',
        `last_update` varchar(255) NOT NULL DEFAULT '' COMMENT '',
-        PRIMARY KEY(`id`)
+        PRIMARY KEY(`id`),
+        INDEX `expire` (`expire`)
 ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='';
 
 --
@@ -627,6 +631,7 @@ CREATE TABLE IF NOT EXISTS `hook` (
        `function` varbinary(200) NOT NULL DEFAULT '' COMMENT 'function name of hook handler',
        `priority` smallint unsigned NOT NULL DEFAULT 0 COMMENT 'not yet implemented - can be used to sort conflicts in hook handling by calling handlers in priority order',
         PRIMARY KEY(`id`),
+        INDEX `priority` (`priority`),
         UNIQUE INDEX `hook_file_function` (`hook`,`file`,`function`)
 ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='addon hook registry';
 
@@ -985,6 +990,7 @@ CREATE TABLE IF NOT EXISTS `post-content` (
        `plink` varchar(255) NOT NULL DEFAULT '' COMMENT 'permalink or URL to a displayable copy of the message at its source',
         PRIMARY KEY(`uri-id`),
         INDEX `plink` (`plink`(191)),
+        INDEX `resource-id` (`resource-id`),
         FULLTEXT INDEX `title-content-warning-body` (`title`,`content-warning`,`body`),
        FOREIGN KEY (`uri-id`) REFERENCES `item-uri` (`id`) ON UPDATE RESTRICT ON DELETE CASCADE
 ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='Content for all posts';
@@ -1056,6 +1062,7 @@ CREATE TABLE IF NOT EXISTS `post-thread` (
        `network` char(4) NOT NULL DEFAULT '' COMMENT '',
        `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '',
        `received` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '',
+       `changed` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT 'Date that something in the conversation changed, indicating clients should fetch the conversation again',
        `commented` datetime NOT NULL DEFAULT '0001-01-01 00:00:00' COMMENT '',
         PRIMARY KEY(`uri-id`),
         INDEX `owner-id` (`owner-id`),
@@ -1348,7 +1355,8 @@ CREATE TABLE IF NOT EXISTS `search` (
        `uid` mediumint unsigned NOT NULL DEFAULT 0 COMMENT 'User id',
        `term` varchar(255) NOT NULL DEFAULT '' COMMENT '',
         PRIMARY KEY(`id`),
-        INDEX `uid` (`uid`),
+        INDEX `uid_term` (`uid`,`term`(64)),
+        INDEX `term` (`term`(64)),
        FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON UPDATE RESTRICT ON DELETE CASCADE
 ) DEFAULT COLLATE utf8mb4_general_ci COMMENT='';
 
@@ -1591,9 +1599,9 @@ CREATE VIEW `post-view` AS SELECT
        `parent-post-author`.`network` AS `parent-author-network`
        FROM `post-user`
                        STRAIGHT_JOIN `post-thread-user` ON `post-thread-user`.`uri-id` = `post-user`.`parent-uri-id` AND `post-thread-user`.`uid` = `post-user`.`uid`
-                       LEFT JOIN `contact` ON `contact`.`id` = `post-user`.`contact-id`
-                       LEFT JOIN `contact` AS `author` ON `author`.`id` = `post-user`.`author-id`
-                       LEFT JOIN `contact` AS `owner` ON `owner`.`id` = `post-user`.`owner-id`
+                       STRAIGHT_JOIN `contact` ON `contact`.`id` = `post-user`.`contact-id`
+                       STRAIGHT_JOIN `contact` AS `author` ON `author`.`id` = `post-user`.`author-id`
+                       STRAIGHT_JOIN `contact` AS `owner` ON `owner`.`id` = `post-user`.`owner-id`
                        LEFT JOIN `contact` AS `causer` ON `causer`.`id` = `post-user`.`causer-id`
                        LEFT JOIN `item-uri` ON `item-uri`.`id` = `post-user`.`uri-id`
                        LEFT JOIN `item-uri` AS `thr-parent-item-uri` ON `thr-parent-item-uri`.`id` = `post-user`.`thr-parent-id`
@@ -1749,9 +1757,9 @@ CREATE VIEW `post-thread-view` AS SELECT
        `parent-post-author`.`network` AS `parent-author-network`
        FROM `post-thread-user`
                        INNER JOIN `post-user` ON `post-user`.`id` = `post-thread-user`.`post-user-id`
-                       LEFT JOIN `contact` ON `contact`.`id` = `post-thread-user`.`contact-id`
-                       LEFT JOIN `contact` AS `author` ON `author`.`id` = `post-thread-user`.`author-id`
-                       LEFT JOIN `contact` AS `owner` ON `owner`.`id` = `post-thread-user`.`owner-id`
+                       STRAIGHT_JOIN `contact` ON `contact`.`id` = `post-thread-user`.`contact-id`
+                       STRAIGHT_JOIN `contact` AS `author` ON `author`.`id` = `post-thread-user`.`author-id`
+                       STRAIGHT_JOIN `contact` AS `owner` ON `owner`.`id` = `post-thread-user`.`owner-id`
                        LEFT JOIN `contact` AS `causer` ON `causer`.`id` = `post-thread-user`.`causer-id`
                        LEFT JOIN `item-uri` ON `item-uri`.`id` = `post-thread-user`.`uri-id`
                        LEFT JOIN `item-uri` AS `thr-parent-item-uri` ON `thr-parent-item-uri`.`id` = `post-user`.`thr-parent-id`