diff --git a/plugins/libkolab/SQL/mysql.initial.sql b/plugins/libkolab/SQL/mysql.initial.sql index 36b39903..e3e28b00 100644 --- a/plugins/libkolab/SQL/mysql.initial.sql +++ b/plugins/libkolab/SQL/mysql.initial.sql @@ -1,183 +1,182 @@ /** * libkolab database schema * - * @version 1.2 * @author Thomas Bruederli * @licence GNU AGPL */ /*!40014 SET FOREIGN_KEY_CHECKS=0 */; DROP TABLE IF EXISTS `kolab_folders`; CREATE TABLE `kolab_folders` ( `folder_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `resource` VARCHAR(255) BINARY NOT NULL, `type` VARCHAR(32) NOT NULL, `synclock` INT(10) NOT NULL DEFAULT '0', `ctag` VARCHAR(40) DEFAULT NULL, `changed` DATETIME DEFAULT NULL, `objectcount` BIGINT DEFAULT NULL, PRIMARY KEY(`folder_id`), INDEX `resource_type` (`resource`, `type`) -) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */; DROP TABLE IF EXISTS `kolab_cache`; DROP TABLE IF EXISTS `kolab_cache_contact`; CREATE TABLE `kolab_cache_contact` ( `folder_id` BIGINT UNSIGNED NOT NULL, `msguid` BIGINT UNSIGNED NOT NULL, `uid` VARCHAR(512) NOT NULL, `created` DATETIME DEFAULT NULL, `changed` DATETIME DEFAULT NULL, `data` LONGTEXT NOT NULL, `tags` TEXT NOT NULL, `words` TEXT NOT NULL, `type` VARCHAR(32) CHARACTER SET ascii NOT NULL, `name` VARCHAR(255) NOT NULL, `firstname` VARCHAR(255) NOT NULL, `surname` VARCHAR(255) NOT NULL, `email` VARCHAR(255) NOT NULL, CONSTRAINT `fk_kolab_cache_contact_folder` FOREIGN KEY (`folder_id`) REFERENCES `kolab_folders`(`folder_id`) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY(`folder_id`,`msguid`), INDEX `contact_type` (`folder_id`,`type`), INDEX `contact_uid2msguid` (`folder_id`,`uid`,`msguid`) -) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */; DROP TABLE IF EXISTS `kolab_cache_event`; CREATE TABLE `kolab_cache_event` ( `folder_id` BIGINT UNSIGNED NOT NULL, `msguid` BIGINT UNSIGNED NOT NULL, `uid` VARCHAR(512) NOT NULL, `created` DATETIME DEFAULT NULL, `changed` DATETIME DEFAULT NULL, `data` LONGTEXT NOT NULL, `tags` TEXT NOT NULL, `words` TEXT NOT NULL, `dtstart` DATETIME, `dtend` DATETIME, CONSTRAINT `fk_kolab_cache_event_folder` FOREIGN KEY (`folder_id`) REFERENCES `kolab_folders`(`folder_id`) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY(`folder_id`,`msguid`), INDEX `event_uid2msguid` (`folder_id`,`uid`,`msguid`) -) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */; DROP TABLE IF EXISTS `kolab_cache_task`; CREATE TABLE `kolab_cache_task` ( `folder_id` BIGINT UNSIGNED NOT NULL, `msguid` BIGINT UNSIGNED NOT NULL, `uid` VARCHAR(512) NOT NULL, `created` DATETIME DEFAULT NULL, `changed` DATETIME DEFAULT NULL, `data` LONGTEXT NOT NULL, `tags` TEXT NOT NULL, `words` TEXT NOT NULL, `dtstart` DATETIME, `dtend` DATETIME, CONSTRAINT `fk_kolab_cache_task_folder` FOREIGN KEY (`folder_id`) REFERENCES `kolab_folders`(`folder_id`) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY(`folder_id`,`msguid`), INDEX `task_uid2msguid` (`folder_id`,`uid`,`msguid`) -) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */; DROP TABLE IF EXISTS `kolab_cache_journal`; CREATE TABLE `kolab_cache_journal` ( `folder_id` BIGINT UNSIGNED NOT NULL, `msguid` BIGINT UNSIGNED NOT NULL, `uid` VARCHAR(512) NOT NULL, `created` DATETIME DEFAULT NULL, `changed` DATETIME DEFAULT NULL, `data` LONGTEXT NOT NULL, `tags` TEXT NOT NULL, `words` TEXT NOT NULL, `dtstart` DATETIME, `dtend` DATETIME, CONSTRAINT `fk_kolab_cache_journal_folder` FOREIGN KEY (`folder_id`) REFERENCES `kolab_folders`(`folder_id`) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY(`folder_id`,`msguid`), INDEX `journal_uid2msguid` (`folder_id`,`uid`,`msguid`) -) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */; DROP TABLE IF EXISTS `kolab_cache_note`; CREATE TABLE `kolab_cache_note` ( `folder_id` BIGINT UNSIGNED NOT NULL, `msguid` BIGINT UNSIGNED NOT NULL, `uid` VARCHAR(512) NOT NULL, `created` DATETIME DEFAULT NULL, `changed` DATETIME DEFAULT NULL, `data` LONGTEXT NOT NULL, `tags` TEXT NOT NULL, `words` TEXT NOT NULL, CONSTRAINT `fk_kolab_cache_note_folder` FOREIGN KEY (`folder_id`) REFERENCES `kolab_folders`(`folder_id`) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY(`folder_id`,`msguid`), INDEX `note_uid2msguid` (`folder_id`,`uid`,`msguid`) -) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */; DROP TABLE IF EXISTS `kolab_cache_file`; CREATE TABLE `kolab_cache_file` ( `folder_id` BIGINT UNSIGNED NOT NULL, `msguid` BIGINT UNSIGNED NOT NULL, `uid` VARCHAR(512) NOT NULL, `created` DATETIME DEFAULT NULL, `changed` DATETIME DEFAULT NULL, `data` LONGTEXT NOT NULL, `tags` TEXT NOT NULL, `words` TEXT NOT NULL, `filename` varchar(255) DEFAULT NULL, CONSTRAINT `fk_kolab_cache_file_folder` FOREIGN KEY (`folder_id`) REFERENCES `kolab_folders`(`folder_id`) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY(`folder_id`,`msguid`), INDEX `folder_filename` (`folder_id`, `filename`), INDEX `file_uid2msguid` (`folder_id`,`uid`,`msguid`) -) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */; DROP TABLE IF EXISTS `kolab_cache_configuration`; CREATE TABLE `kolab_cache_configuration` ( `folder_id` BIGINT UNSIGNED NOT NULL, `msguid` BIGINT UNSIGNED NOT NULL, `uid` VARCHAR(512) NOT NULL, `created` DATETIME DEFAULT NULL, `changed` DATETIME DEFAULT NULL, `data` LONGTEXT NOT NULL, `tags` TEXT NOT NULL, `words` TEXT NOT NULL, `type` VARCHAR(32) CHARACTER SET ascii NOT NULL, CONSTRAINT `fk_kolab_cache_configuration_folder` FOREIGN KEY (`folder_id`) REFERENCES `kolab_folders`(`folder_id`) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY(`folder_id`,`msguid`), INDEX `configuration_type` (`folder_id`,`type`), INDEX `configuration_uid2msguid` (`folder_id`,`uid`,`msguid`) -) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */; DROP TABLE IF EXISTS `kolab_cache_freebusy`; CREATE TABLE `kolab_cache_freebusy` ( `folder_id` BIGINT UNSIGNED NOT NULL, `msguid` BIGINT UNSIGNED NOT NULL, `uid` VARCHAR(512) NOT NULL, `created` DATETIME DEFAULT NULL, `changed` DATETIME DEFAULT NULL, `data` LONGTEXT NOT NULL, `tags` TEXT NOT NULL, `words` TEXT NOT NULL, `dtstart` DATETIME, `dtend` DATETIME, CONSTRAINT `fk_kolab_cache_freebusy_folder` FOREIGN KEY (`folder_id`) REFERENCES `kolab_folders`(`folder_id`) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY(`folder_id`,`msguid`), INDEX `freebusy_uid2msguid` (`folder_id`,`uid`,`msguid`) -) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */; +) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */; /*!40014 SET FOREIGN_KEY_CHECKS=1 */; -REPLACE INTO `system` (`name`, `value`) VALUES ('libkolab-version', '2019092900'); +REPLACE INTO `system` (`name`, `value`) VALUES ('libkolab-version', '2021101100'); diff --git a/plugins/libkolab/SQL/mysql/2021101100.sql b/plugins/libkolab/SQL/mysql/2021101100.sql new file mode 100644 index 00000000..4e46fb3a --- /dev/null +++ b/plugins/libkolab/SQL/mysql/2021101100.sql @@ -0,0 +1,46 @@ +-- changing table format and dropping foreign keys is needed for some versions of MySQL +ALTER TABLE `kolab_cache_contact` DROP FOREIGN KEY `fk_kolab_cache_contact_folder`; +ALTER TABLE `kolab_cache_event` DROP FOREIGN KEY`fk_kolab_cache_event_folder`; +ALTER TABLE `kolab_cache_task` DROP FOREIGN KEY`fk_kolab_cache_task_folder`; +ALTER TABLE `kolab_cache_journal` DROP FOREIGN KEY`fk_kolab_cache_journal_folder`; +ALTER TABLE `kolab_cache_note` DROP FOREIGN KEY`fk_kolab_cache_note_folder`; +ALTER TABLE `kolab_cache_file` DROP FOREIGN KEY`fk_kolab_cache_file_folder`; +ALTER TABLE `kolab_cache_configuration` DROP FOREIGN KEY`fk_kolab_cache_configuration_folder`; +ALTER TABLE `kolab_cache_freebusy` DROP FOREIGN KEY`fk_kolab_cache_freebusy_folder`; + +ALTER TABLE `kolab_folders` ROW_FORMAT=DYNAMIC; +ALTER TABLE `kolab_cache_contact` ROW_FORMAT=DYNAMIC; +ALTER TABLE `kolab_cache_event` ROW_FORMAT=DYNAMIC; +ALTER TABLE `kolab_cache_task` ROW_FORMAT=DYNAMIC; +ALTER TABLE `kolab_cache_journal` ROW_FORMAT=DYNAMIC; +ALTER TABLE `kolab_cache_note` ROW_FORMAT=DYNAMIC; +ALTER TABLE `kolab_cache_file` ROW_FORMAT=DYNAMIC; +ALTER TABLE `kolab_cache_configuration` ROW_FORMAT=DYNAMIC; +ALTER TABLE `kolab_cache_freebusy` ROW_FORMAT=DYNAMIC; + +ALTER TABLE `kolab_folders` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; +ALTER TABLE `kolab_cache_contact` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; +ALTER TABLE `kolab_cache_event` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; +ALTER TABLE `kolab_cache_task` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; +ALTER TABLE `kolab_cache_journal` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; +ALTER TABLE `kolab_cache_note` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; +ALTER TABLE `kolab_cache_file` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; +ALTER TABLE `kolab_cache_configuration` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; +ALTER TABLE `kolab_cache_freebusy` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; + +ALTER TABLE `kolab_cache_contact` ADD CONSTRAINT `fk_kolab_cache_contact_folder` FOREIGN KEY (`folder_id`) + REFERENCES `kolab_folders`(`folder_id`) ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE `kolab_cache_event` ADD CONSTRAINT `fk_kolab_cache_event_folder` FOREIGN KEY (`folder_id`) + REFERENCES `kolab_folders`(`folder_id`) ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE `kolab_cache_task` ADD CONSTRAINT `fk_kolab_cache_task_folder` FOREIGN KEY (`folder_id`) + REFERENCES `kolab_folders`(`folder_id`) ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE `kolab_cache_journal` ADD CONSTRAINT `fk_kolab_cache_journal_folder` FOREIGN KEY (`folder_id`) + REFERENCES `kolab_folders`(`folder_id`) ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE `kolab_cache_note` ADD CONSTRAINT `fk_kolab_cache_note_folder` FOREIGN KEY (`folder_id`) + REFERENCES `kolab_folders`(`folder_id`) ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE `kolab_cache_file` ADD CONSTRAINT `fk_kolab_cache_file_folder` FOREIGN KEY (`folder_id`) + REFERENCES `kolab_folders`(`folder_id`) ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE `kolab_cache_configuration` ADD CONSTRAINT `fk_kolab_cache_configuration_folder` FOREIGN KEY (`folder_id`) + REFERENCES `kolab_folders`(`folder_id`) ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE `kolab_cache_freebusy` ADD CONSTRAINT `fk_kolab_cache_freebusy_folder` FOREIGN KEY (`folder_id`) + REFERENCES `kolab_folders`(`folder_id`) ON DELETE CASCADE ON UPDATE CASCADE; diff --git a/plugins/libkolab/SQL/oracle.initial.sql b/plugins/libkolab/SQL/oracle.initial.sql index 5b9a07d1..8adf83cf 100644 --- a/plugins/libkolab/SQL/oracle.initial.sql +++ b/plugins/libkolab/SQL/oracle.initial.sql @@ -1,178 +1,176 @@ /** * libkolab database schema * - * @version 1.2 * @author Aleksander Machniak * @licence GNU AGPL - **/ - + */ CREATE TABLE "kolab_folders" ( "folder_id" number NOT NULL PRIMARY KEY, "resource" VARCHAR(255) NOT NULL, "type" VARCHAR(32) NOT NULL, "synclock" integer DEFAULT 0 NOT NULL, "ctag" VARCHAR(40) DEFAULT NULL, "changed" timestamp DEFAULT NULL, "objectcount" number DEFAULT NULL ); CREATE INDEX "kolab_folders_resource_idx" ON "kolab_folders" ("resource", "type"); CREATE SEQUENCE "kolab_folders_seq" START WITH 1 INCREMENT BY 1 NOMAXVALUE; CREATE TRIGGER "kolab_folders_seq_trig" BEFORE INSERT ON "kolab_folders" FOR EACH ROW BEGIN :NEW."folder_id" := "kolab_folders_seq".nextval; END; / CREATE TABLE "kolab_cache_contact" ( "folder_id" number NOT NULL REFERENCES "kolab_folders" ("folder_id") ON DELETE CASCADE, "msguid" number NOT NULL, "uid" varchar(512) NOT NULL, "created" timestamp DEFAULT NULL, "changed" timestamp DEFAULT NULL, "data" clob NOT NULL, "tags" clob DEFAULT NULL, "words" clob DEFAULT NULL, "type" varchar(32) NOT NULL, "name" varchar(255) DEFAULT NULL, "firstname" varchar(255) DEFAULT NULL, "surname" varchar(255) DEFAULT NULL, "email" varchar(255) DEFAULT NULL, PRIMARY KEY ("folder_id", "msguid") ); CREATE INDEX "kolab_cache_contact_type_idx" ON "kolab_cache_contact" ("folder_id", "type"); CREATE INDEX "kolab_cache_contact_uid2msguid" ON "kolab_cache_contact" ("folder_id", "uid", "msguid"); CREATE TABLE "kolab_cache_event" ( "folder_id" number NOT NULL REFERENCES "kolab_folders" ("folder_id") ON DELETE CASCADE, "msguid" number NOT NULL, "uid" varchar(512) NOT NULL, "created" timestamp DEFAULT NULL, "changed" timestamp DEFAULT NULL, "data" clob NOT NULL, "tags" clob DEFAULT NULL, "words" clob DEFAULT NULL, "dtstart" timestamp DEFAULT NULL, "dtend" timestamp DEFAULT NULL, PRIMARY KEY ("folder_id", "msguid") ); CREATE INDEX "kolab_cache_event_uid2msguid" ON "kolab_cache_event" ("folder_id", "uid", "msguid"); CREATE TABLE "kolab_cache_task" ( "folder_id" number NOT NULL REFERENCES "kolab_folders" ("folder_id") ON DELETE CASCADE, "msguid" number NOT NULL, "uid" varchar(512) NOT NULL, "created" timestamp DEFAULT NULL, "changed" timestamp DEFAULT NULL, "data" clob NOT NULL, "tags" clob DEFAULT NULL, "words" clob DEFAULT NULL, "dtstart" timestamp DEFAULT NULL, "dtend" timestamp DEFAULT NULL, PRIMARY KEY ("folder_id", "msguid") ); CREATE INDEX "kolab_cache_task_uid2msguid" ON "kolab_cache_task" ("folder_id", "uid", "msguid"); CREATE TABLE "kolab_cache_journal" ( "folder_id" number NOT NULL REFERENCES "kolab_folders" ("folder_id") ON DELETE CASCADE, "msguid" number NOT NULL, "uid" varchar(512) NOT NULL, "created" timestamp DEFAULT NULL, "changed" timestamp DEFAULT NULL, "data" clob NOT NULL, "tags" clob DEFAULT NULL, "words" clob DEFAULT NULL, "dtstart" timestamp DEFAULT NULL, "dtend" timestamp DEFAULT NULL, PRIMARY KEY ("folder_id", "msguid") ); CREATE INDEX "kolab_cache_journal_uid2msguid" ON "kolab_cache_journal" ("folder_id", "uid", "msguid"); CREATE TABLE "kolab_cache_note" ( "folder_id" number NOT NULL REFERENCES "kolab_folders" ("folder_id") ON DELETE CASCADE, "msguid" number NOT NULL, "uid" varchar(512) NOT NULL, "created" timestamp DEFAULT NULL, "changed" timestamp DEFAULT NULL, "data" clob NOT NULL, "tags" clob DEFAULT NULL, "words" clob DEFAULT NULL, PRIMARY KEY ("folder_id", "msguid") ); CREATE INDEX "kolab_cache_note_uid2msguid" ON "kolab_cache_note" ("folder_id", "uid", "msguid"); CREATE TABLE "kolab_cache_file" ( "folder_id" number NOT NULL REFERENCES "kolab_folders" ("folder_id") ON DELETE CASCADE, "msguid" number NOT NULL, "uid" varchar(512) NOT NULL, "created" timestamp DEFAULT NULL, "changed" timestamp DEFAULT NULL, "data" clob NOT NULL, "tags" clob DEFAULT NULL, "words" clob DEFAULT NULL, "filename" varchar(255) DEFAULT NULL, PRIMARY KEY ("folder_id", "msguid") ); CREATE INDEX "kolab_cache_file_filename" ON "kolab_cache_file" ("folder_id", "filename"); CREATE INDEX "kolab_cache_file_uid2msguid" ON "kolab_cache_file" ("folder_id", "uid", "msguid"); CREATE TABLE "kolab_cache_configuration" ( "folder_id" number NOT NULL REFERENCES "kolab_folders" ("folder_id") ON DELETE CASCADE, "msguid" number NOT NULL, "uid" varchar(512) NOT NULL, "created" timestamp DEFAULT NULL, "changed" timestamp DEFAULT NULL, "data" clob NOT NULL, "tags" clob DEFAULT NULL, "words" clob DEFAULT NULL, "type" varchar(32) NOT NULL, PRIMARY KEY ("folder_id", "msguid") ); CREATE INDEX "kolab_cache_config_type" ON "kolab_cache_configuration" ("folder_id", "type"); CREATE INDEX "kolab_cache_config_uid2msguid" ON "kolab_cache_configuration" ("folder_id", "uid", "msguid"); CREATE TABLE "kolab_cache_freebusy" ( "folder_id" number NOT NULL REFERENCES "kolab_folders" ("folder_id") ON DELETE CASCADE, "msguid" number NOT NULL, "uid" varchar(512) NOT NULL, "created" timestamp DEFAULT NULL, "changed" timestamp DEFAULT NULL, "data" clob NOT NULL, "tags" clob DEFAULT NULL, "words" clob DEFAULT NULL, "dtstart" timestamp DEFAULT NULL, "dtend" timestamp DEFAULT NULL, PRIMARY KEY("folder_id", "msguid") ); CREATE INDEX "kolab_cache_fb_uid2msguid" ON "kolab_cache_freebusy" ("folder_id", "uid", "msguid"); -INSERT INTO "system" ("name", "value") VALUES ('libkolab-version', '2019092900'); +INSERT INTO "system" ("name", "value") VALUES ('libkolab-version', '2021101100'); diff --git a/plugins/libkolab/SQL/oracle/2021101100.sql b/plugins/libkolab/SQL/oracle/2021101100.sql new file mode 100644 index 00000000..fe6741a0 --- /dev/null +++ b/plugins/libkolab/SQL/oracle/2021101100.sql @@ -0,0 +1 @@ +-- empty \ No newline at end of file diff --git a/plugins/libkolab/SQL/sqlite.initial.sql b/plugins/libkolab/SQL/sqlite.initial.sql index 745fdfdb..93cbc88d 100644 --- a/plugins/libkolab/SQL/sqlite.initial.sql +++ b/plugins/libkolab/SQL/sqlite.initial.sql @@ -1,151 +1,150 @@ /** * libkolab database schema * - * @version 1.2 * @author Thomas Bruederli * @licence GNU AGPL - **/ + */ CREATE TABLE kolab_folders ( folder_id INTEGER NOT NULL PRIMARY KEY, resource VARCHAR(255) NOT NULL, type VARCHAR(32) NOT NULL, synclock INTEGER NOT NULL DEFAULT '0', ctag VARCHAR(40) DEFAULT NULL, changed DATETIME DEFAULT NULL, objectcount INTEGER DEFAULT NULL ); CREATE INDEX ix_resource_type ON kolab_folders(resource, type); CREATE TABLE kolab_cache_contact ( folder_id INTEGER NOT NULL, msguid INTEGER NOT NULL, uid VARCHAR(512) NOT NULL, created DATETIME DEFAULT NULL, changed DATETIME DEFAULT NULL, data TEXT NOT NULL, tags TEXT NOT NULL, words TEXT NOT NULL, type VARCHAR(32) NOT NULL, name VARCHAR(255) NOT NULL, firstname VARCHAR(255) NOT NULL, surname VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, PRIMARY KEY(folder_id,msguid) ); CREATE INDEX ix_contact_type ON kolab_cache_contact(folder_id,type); CREATE INDEX ix_contact_uid2msguid ON kolab_cache_contact(folder_id,uid,msguid); CREATE TABLE kolab_cache_event ( folder_id INTEGER NOT NULL, msguid INTEGER NOT NULL, uid VARCHAR(512) NOT NULL, created DATETIME DEFAULT NULL, changed DATETIME DEFAULT NULL, data TEXT NOT NULL, tags TEXT NOT NULL, words TEXT NOT NULL, dtstart DATETIME, dtend DATETIME, PRIMARY KEY(folder_id,msguid) ); CREATE INDEX ix_event_uid2msguid ON kolab_cache_event(folder_id,uid,msguid); CREATE TABLE kolab_cache_task ( folder_id INTEGER NOT NULL, msguid INTEGER NOT NULL, uid VARCHAR(512) NOT NULL, created DATETIME DEFAULT NULL, changed DATETIME DEFAULT NULL, data TEXT NOT NULL, tags TEXT NOT NULL, words TEXT NOT NULL, dtstart DATETIME, dtend DATETIME, PRIMARY KEY(folder_id,msguid) ); CREATE INDEX ix_task_uid2msguid ON kolab_cache_task(folder_id,uid,msguid); CREATE TABLE kolab_cache_journal ( folder_id INTEGER NOT NULL, msguid INTEGER NOT NULL, uid VARCHAR(512) NOT NULL, created DATETIME DEFAULT NULL, changed DATETIME DEFAULT NULL, data TEXT NOT NULL, tags TEXT NOT NULL, words TEXT NOT NULL, dtstart DATETIME, dtend DATETIME, PRIMARY KEY(folder_id,msguid) ); CREATE INDEX ix_journal_uid2msguid ON kolab_cache_journal(folder_id,uid,msguid); CREATE TABLE kolab_cache_note ( folder_id INTEGER NOT NULL, msguid INTEGER NOT NULL, uid VARCHAR(512) NOT NULL, created DATETIME DEFAULT NULL, changed DATETIME DEFAULT NULL, data TEXT NOT NULL, tags TEXT NOT NULL, words TEXT NOT NULL, PRIMARY KEY(folder_id,msguid) ); CREATE INDEX ix_note_uid2msguid ON kolab_cache_note(folder_id,uid,msguid); CREATE TABLE kolab_cache_file ( folder_id INTEGER NOT NULL, msguid INTEGER NOT NULL, uid VARCHAR(512) NOT NULL, created DATETIME DEFAULT NULL, changed DATETIME DEFAULT NULL, data TEXT NOT NULL, tags TEXT NOT NULL, words TEXT NOT NULL, filename varchar(255) DEFAULT NULL, PRIMARY KEY(folder_id,msguid) ); CREATE INDEX ix_folder_filename ON kolab_cache_file(folder_id,filename); CREATE INDEX ix_file_uid2msguid ON kolab_cache_file(folder_id,uid,msguid); CREATE TABLE kolab_cache_configuration ( folder_id INTEGER NOT NULL, msguid INTEGER NOT NULL, uid VARCHAR(512) NOT NULL, created DATETIME DEFAULT NULL, changed DATETIME DEFAULT NULL, data TEXT NOT NULL, tags TEXT NOT NULL, words TEXT NOT NULL, type VARCHAR(32) NOT NULL, PRIMARY KEY(folder_id,msguid) ); CREATE INDEX ix_configuration_type ON kolab_cache_configuration(folder_id,type); CREATE INDEX ix_configuration_uid2msguid ON kolab_cache_configuration(folder_id,uid,msguid); CREATE TABLE kolab_cache_freebusy ( folder_id INTEGER NOT NULL, msguid INTEGER NOT NULL, uid VARCHAR(512) NOT NULL, created DATETIME DEFAULT NULL, changed DATETIME DEFAULT NULL, data TEXT NOT NULL, tags TEXT NOT NULL, words TEXT NOT NULL, dtstart DATETIME, dtend DATETIME, PRIMARY KEY(folder_id,msguid) ); CREATE INDEX ix_freebusy_uid2msguid ON kolab_cache_freebusy(folder_id,uid,msguid); -INSERT INTO system (name, value) VALUES ('libkolab-version', '2019092900'); +INSERT INTO system (name, value) VALUES ('libkolab-version', '2021101100'); diff --git a/plugins/libkolab/SQL/sqlite/2021101100.sql b/plugins/libkolab/SQL/sqlite/2021101100.sql new file mode 100644 index 00000000..fe6741a0 --- /dev/null +++ b/plugins/libkolab/SQL/sqlite/2021101100.sql @@ -0,0 +1 @@ +-- empty \ No newline at end of file