diff --git a/autotests/server/dbtest_data/dbinit_sqlite b/autotests/server/dbtest_data/dbinit_sqlite index 94e75a25e..95ba53781 100644 --- a/autotests/server/dbtest_data/dbinit_sqlite +++ b/autotests/server/dbtest_data/dbinit_sqlite @@ -1,748 +1,748 @@ CREATE TABLE SchemaVersionTable (version INTEGER NOT NULL DEFAULT 0, generation INTEGER NOT NULL DEFAULT 0) INSERT INTO SchemaVersionTable (version) VALUES (36) CREATE TABLE ResourceTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT UNIQUE NOT NULL, isVirtual BOOL DEFAULT 0) INSERT INTO ResourceTable (isVirtual,name) VALUES (1,'akonadi_search_resource') CREATE TABLE CollectionTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, remoteId TEXT, remoteRevision TEXT, name TEXT NOT NULL, parentId BIGINT, resourceId BIGINT NOT NULL, enabled BOOL NOT NULL DEFAULT 1, syncPref TINYINT DEFAULT 2, displayPref TINYINT DEFAULT 2, indexPref TINYINT DEFAULT 2, referenced BOOL NOT NULL DEFAULT 0, cachePolicyInherit BOOL NOT NULL DEFAULT 1, cachePolicyCheckInterval INTEGER NOT NULL DEFAULT -1, cachePolicyCacheTimeout INTEGER NOT NULL DEFAULT -1, cachePolicySyncOnDemand BOOL NOT NULL DEFAULT 0, cachePolicyLocalParts TEXT, queryString TEXT, queryAttributes TEXT, queryCollections TEXT, isVirtual BOOL DEFAULT 0, CONSTRAINT CollectionTableparentId_Collectionid_fk FOREIGN KEY (parentId) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT CollectionTableresourceId_Resourceid_fk FOREIGN KEY (resourceId) REFERENCES ResourceTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO CollectionTable (isVirtual,name,parentId,resourceId) VALUES (1,'Search',NULL,1) CREATE TABLE MimeTypeTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT UNIQUE NOT NULL) CREATE TABLE PimItemTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, rev INTEGER NOT NULL DEFAULT 0, remoteId TEXT, remoteRevision TEXT, gid TEXT, collectionId BIGINT, mimeTypeId BIGINT, datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, atime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, dirty BOOL, size BIGINT NOT NULL DEFAULT 0, CONSTRAINT PimItemTablecollectionId_Collectionid_fk FOREIGN KEY (collectionId) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT PimItemTablemimeTypeId_MimeTypeid_fk FOREIGN KEY (mimeTypeId) REFERENCES MimeTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED) CREATE TABLE FlagTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT UNIQUE NOT NULL) CREATE TABLE PartTypeTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT NOT NULL, ns TEXT NOT NULL) CREATE TABLE PartTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, pimItemId BIGINT NOT NULL, partTypeId BIGINT NOT NULL, data LONGBLOB, datasize BIGINT NOT NULL, version INTEGER DEFAULT 0, storage TINYINT DEFAULT 0, CONSTRAINT PartTablepimItemId_PimItemid_fk FOREIGN KEY (pimItemId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT PartTablepartTypeId_PartTypeid_fk FOREIGN KEY (partTypeId) REFERENCES PartTypeTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) CREATE TABLE CollectionAttributeTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, collectionId BIGINT NOT NULL, type LONGBLOB NOT NULL, value LONGBLOB, CONSTRAINT CollectionAttributeTablecollectionId_Collectionid_fk FOREIGN KEY (collectionId) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) CREATE TABLE TagTypeTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT UNIQUE NOT NULL) INSERT INTO TagTypeTable (name) VALUES ('PLAIN') CREATE TABLE TagTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, gid TEXT NOT NULL, parentId BIGINT, typeId BIGINT DEFAULT 1, CONSTRAINT TagTableparentId_Tagid_fk FOREIGN KEY (parentId) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT TagTabletypeId_TagTypeid_fk FOREIGN KEY (typeId) REFERENCES TagTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED) CREATE TABLE TagAttributeTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, tagId BIGINT NOT NULL, type LONGBLOB NOT NULL, value LONGBLOB, CONSTRAINT TagAttributeTabletagId_Tagid_fk FOREIGN KEY (tagId) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) CREATE TABLE TagRemoteIdResourceRelationTable (tagId BIGINT NOT NULL, resourceId BIGINT NOT NULL, remoteId TEXT NOT NULL, CONSTRAINT TagRemoteIdResourceRelationTabletagId_Tagid_fk FOREIGN KEY (tagId) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT TagRemoteIdResourceRelationTableresourceId_Resourceid_fk FOREIGN KEY (resourceId) REFERENCES ResourceTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) CREATE TABLE RelationTypeTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT UNIQUE NOT NULL) INSERT INTO RelationTypeTable (name) VALUES ('GENERIC') CREATE TABLE RelationTable (leftId BIGINT NOT NULL, rightId BIGINT NOT NULL, typeId BIGINT DEFAULT 1, remoteId TEXT, CONSTRAINT RelationTableleftId_PimItemid_fk FOREIGN KEY (leftId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT RelationTablerightId_PimItemid_fk FOREIGN KEY (rightId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT RelationTabletypeId_RelationTypeid_fk FOREIGN KEY (typeId) REFERENCES RelationTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED) CREATE TABLE PimItemFlagRelation (PimItem_id BIGINT NOT NULL, Flag_id BIGINT NOT NULL, PRIMARY KEY (PimItem_id, Flag_id), CONSTRAINT PimItemFlagRelationPimItem_id_PimItemid_fk FOREIGN KEY (PimItem_id) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT PimItemFlagRelationFlag_id_Flagid_fk FOREIGN KEY (Flag_id) REFERENCES FlagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) CREATE TABLE PimItemTagRelation (PimItem_id BIGINT NOT NULL, Tag_id BIGINT NOT NULL, PRIMARY KEY (PimItem_id, Tag_id), CONSTRAINT PimItemTagRelationPimItem_id_PimItemid_fk FOREIGN KEY (PimItem_id) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT PimItemTagRelationTag_id_Tagid_fk FOREIGN KEY (Tag_id) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) CREATE TABLE CollectionMimeTypeRelation (Collection_id BIGINT NOT NULL, MimeType_id BIGINT NOT NULL, PRIMARY KEY (Collection_id, MimeType_id), CONSTRAINT CollectionMimeTypeRelationCollection_id_Collectionid_fk FOREIGN KEY (Collection_id) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT CollectionMimeTypeRelationMimeType_id_MimeTypeid_fk FOREIGN KEY (MimeType_id) REFERENCES MimeTypeTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) CREATE TABLE CollectionPimItemRelation (Collection_id BIGINT NOT NULL, PimItem_id BIGINT NOT NULL, PRIMARY KEY (Collection_id, PimItem_id), CONSTRAINT CollectionPimItemRelationCollection_id_Collectionid_fk FOREIGN KEY (Collection_id) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT CollectionPimItemRelationPimItem_id_PimItemid_fk FOREIGN KEY (PimItem_id) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) CREATE UNIQUE INDEX CollectionTable_parentAndNameIndex ON CollectionTable (parentId,name) CREATE INDEX CollectionTable_parentIndex ON CollectionTable (parentId) CREATE INDEX CollectionTable_resourceIndex ON CollectionTable (resourceId) CREATE INDEX CollectionTable_enabledIndex ON CollectionTable (enabled) CREATE INDEX CollectionTable_syncPrefIndex ON CollectionTable (syncPref) CREATE INDEX CollectionTable_displayPrefIndex ON CollectionTable (displayPref) CREATE INDEX CollectionTable_indexPrefIndex ON CollectionTable (indexPref) CREATE INDEX PimItemTable_collectionIndex ON PimItemTable (collectionId) CREATE INDEX PimItemTable_mimeTypeIndex ON PimItemTable (mimeTypeId) CREATE INDEX PimItemTable_gidIndex ON PimItemTable (gid) CREATE INDEX PimItemTable_ridIndex ON PimItemTable (remoteId) CREATE INDEX PimItemTable_idSortIndex ON PimItemTable (id DESC) CREATE UNIQUE INDEX PartTypeTable_partTypeNameIndex ON PartTypeTable (ns,name) CREATE UNIQUE INDEX PartTable_pimItemIdTypeIndex ON PartTable (pimItemId,partTypeId) CREATE INDEX PartTable_pimItemIdSortIndex ON PartTable (pimItemId DESC) CREATE INDEX PartTable_partTypeIndex ON PartTable (partTypeId) CREATE INDEX CollectionAttributeTable_collectionIndex ON CollectionAttributeTable (collectionId) CREATE INDEX TagTable_parentIndex ON TagTable (parentId) CREATE INDEX TagTable_typeIndex ON TagTable (typeId) CREATE INDEX TagAttributeTable_tagIndex ON TagAttributeTable (tagId) CREATE UNIQUE INDEX TagRemoteIdResourceRelationTable_TagAndResourceIndex ON TagRemoteIdResourceRelationTable (tagId,resourceId) CREATE INDEX TagRemoteIdResourceRelationTable_tagIndex ON TagRemoteIdResourceRelationTable (tagId) CREATE INDEX TagRemoteIdResourceRelationTable_resourceIndex ON TagRemoteIdResourceRelationTable (resourceId) CREATE UNIQUE INDEX RelationTable_RelationIndex ON RelationTable (leftId,rightId,typeId) CREATE INDEX RelationTable_leftIndex ON RelationTable (leftId) CREATE INDEX RelationTable_rightIndex ON RelationTable (rightId) CREATE INDEX RelationTable_typeIndex ON RelationTable (typeId) CREATE INDEX PimItemFlagRelation_PimItem_idIndex ON PimItemFlagRelation (PimItem_id) CREATE INDEX PimItemFlagRelation_Flag_idIndex ON PimItemFlagRelation (Flag_id) CREATE INDEX PimItemFlagRelation_pimItemIdSortIndex ON PimItemFlagRelation (pimitem_id DESC) CREATE INDEX PimItemTagRelation_PimItem_idIndex ON PimItemTagRelation (PimItem_id) CREATE INDEX PimItemTagRelation_Tag_idIndex ON PimItemTagRelation (Tag_id) CREATE INDEX CollectionMimeTypeRelation_Collection_idIndex ON CollectionMimeTypeRelation (Collection_id) CREATE INDEX CollectionMimeTypeRelation_MimeType_idIndex ON CollectionMimeTypeRelation (MimeType_id) CREATE INDEX CollectionPimItemRelation_Collection_idIndex ON CollectionPimItemRelation (Collection_id) CREATE INDEX CollectionPimItemRelation_PimItem_idIndex ON CollectionPimItemRelation (PimItem_id) -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE CollectionTable RENAME TO CollectionTable_old CREATE TABLE CollectionTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, remoteId TEXT, remoteRevision TEXT, name TEXT NOT NULL, parentId BIGINT, resourceId BIGINT NOT NULL, enabled BOOL NOT NULL DEFAULT 1, syncPref TINYINT DEFAULT 2, displayPref TINYINT DEFAULT 2, indexPref TINYINT DEFAULT 2, referenced BOOL NOT NULL DEFAULT 0, cachePolicyInherit BOOL NOT NULL DEFAULT 1, cachePolicyCheckInterval INTEGER NOT NULL DEFAULT -1, cachePolicyCacheTimeout INTEGER NOT NULL DEFAULT -1, cachePolicySyncOnDemand BOOL NOT NULL DEFAULT 0, cachePolicyLocalParts TEXT, queryString TEXT, queryAttributes TEXT, queryCollections TEXT, isVirtual BOOL DEFAULT 0, CONSTRAINT CollectionTableparentId_Collectionid_fk FOREIGN KEY (parentId) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT CollectionTableresourceId_Resourceid_fk FOREIGN KEY (resourceId) REFERENCES ResourceTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO CollectionTable SELECT * FROM CollectionTable_old DROP TABLE CollectionTable_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE CollectionTable RENAME TO CollectionTable_old CREATE TABLE CollectionTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, remoteId TEXT, remoteRevision TEXT, name TEXT NOT NULL, parentId BIGINT, resourceId BIGINT NOT NULL, enabled BOOL NOT NULL DEFAULT 1, syncPref TINYINT DEFAULT 2, displayPref TINYINT DEFAULT 2, indexPref TINYINT DEFAULT 2, referenced BOOL NOT NULL DEFAULT 0, cachePolicyInherit BOOL NOT NULL DEFAULT 1, cachePolicyCheckInterval INTEGER NOT NULL DEFAULT -1, cachePolicyCacheTimeout INTEGER NOT NULL DEFAULT -1, cachePolicySyncOnDemand BOOL NOT NULL DEFAULT 0, cachePolicyLocalParts TEXT, queryString TEXT, queryAttributes TEXT, queryCollections TEXT, isVirtual BOOL DEFAULT 0, CONSTRAINT CollectionTableparentId_Collectionid_fk FOREIGN KEY (parentId) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT CollectionTableresourceId_Resourceid_fk FOREIGN KEY (resourceId) REFERENCES ResourceTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO CollectionTable SELECT * FROM CollectionTable_old DROP TABLE CollectionTable_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE PimItemTable RENAME TO PimItemTable_old CREATE TABLE PimItemTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, rev INTEGER NOT NULL DEFAULT 0, remoteId TEXT, remoteRevision TEXT, gid TEXT, collectionId BIGINT, mimeTypeId BIGINT, datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, atime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, dirty BOOL, size BIGINT NOT NULL DEFAULT 0, CONSTRAINT PimItemTablecollectionId_Collectionid_fk FOREIGN KEY (collectionId) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT PimItemTablemimeTypeId_MimeTypeid_fk FOREIGN KEY (mimeTypeId) REFERENCES MimeTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED) INSERT INTO PimItemTable SELECT * FROM PimItemTable_old DROP TABLE PimItemTable_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE PimItemTable RENAME TO PimItemTable_old CREATE TABLE PimItemTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, rev INTEGER NOT NULL DEFAULT 0, remoteId TEXT, remoteRevision TEXT, gid TEXT, collectionId BIGINT, mimeTypeId BIGINT, datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, atime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, dirty BOOL, size BIGINT NOT NULL DEFAULT 0, CONSTRAINT PimItemTablecollectionId_Collectionid_fk FOREIGN KEY (collectionId) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT PimItemTablemimeTypeId_MimeTypeid_fk FOREIGN KEY (mimeTypeId) REFERENCES MimeTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED) INSERT INTO PimItemTable SELECT * FROM PimItemTable_old DROP TABLE PimItemTable_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE PartTable RENAME TO PartTable_old CREATE TABLE PartTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, pimItemId BIGINT NOT NULL, partTypeId BIGINT NOT NULL, data LONGBLOB, datasize BIGINT NOT NULL, version INTEGER DEFAULT 0, storage TINYINT DEFAULT 0, CONSTRAINT PartTablepimItemId_PimItemid_fk FOREIGN KEY (pimItemId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT PartTablepartTypeId_PartTypeid_fk FOREIGN KEY (partTypeId) REFERENCES PartTypeTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO PartTable SELECT * FROM PartTable_old DROP TABLE PartTable_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE PartTable RENAME TO PartTable_old CREATE TABLE PartTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, pimItemId BIGINT NOT NULL, partTypeId BIGINT NOT NULL, data LONGBLOB, datasize BIGINT NOT NULL, version INTEGER DEFAULT 0, storage TINYINT DEFAULT 0, CONSTRAINT PartTablepimItemId_PimItemid_fk FOREIGN KEY (pimItemId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT PartTablepartTypeId_PartTypeid_fk FOREIGN KEY (partTypeId) REFERENCES PartTypeTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO PartTable SELECT * FROM PartTable_old DROP TABLE PartTable_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE CollectionAttributeTable RENAME TO CollectionAttributeTable_old CREATE TABLE CollectionAttributeTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, collectionId BIGINT NOT NULL, type LONGBLOB NOT NULL, value LONGBLOB, CONSTRAINT CollectionAttributeTablecollectionId_Collectionid_fk FOREIGN KEY (collectionId) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO CollectionAttributeTable SELECT * FROM CollectionAttributeTable_old DROP TABLE CollectionAttributeTable_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE TagTable RENAME TO TagTable_old CREATE TABLE TagTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, gid TEXT NOT NULL, parentId BIGINT, typeId BIGINT DEFAULT 1, CONSTRAINT TagTableparentId_Tagid_fk FOREIGN KEY (parentId) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT TagTabletypeId_TagTypeid_fk FOREIGN KEY (typeId) REFERENCES TagTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED) INSERT INTO TagTable SELECT * FROM TagTable_old DROP TABLE TagTable_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE TagTable RENAME TO TagTable_old CREATE TABLE TagTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, gid TEXT NOT NULL, parentId BIGINT, typeId BIGINT DEFAULT 1, CONSTRAINT TagTableparentId_Tagid_fk FOREIGN KEY (parentId) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT TagTabletypeId_TagTypeid_fk FOREIGN KEY (typeId) REFERENCES TagTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED) INSERT INTO TagTable SELECT * FROM TagTable_old DROP TABLE TagTable_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE TagAttributeTable RENAME TO TagAttributeTable_old CREATE TABLE TagAttributeTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, tagId BIGINT NOT NULL, type LONGBLOB NOT NULL, value LONGBLOB, CONSTRAINT TagAttributeTabletagId_Tagid_fk FOREIGN KEY (tagId) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO TagAttributeTable SELECT * FROM TagAttributeTable_old DROP TABLE TagAttributeTable_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE TagRemoteIdResourceRelationTable RENAME TO TagRemoteIdResourceRelationTable_old CREATE TABLE TagRemoteIdResourceRelationTable (tagId BIGINT NOT NULL, resourceId BIGINT NOT NULL, remoteId TEXT NOT NULL, CONSTRAINT TagRemoteIdResourceRelationTabletagId_Tagid_fk FOREIGN KEY (tagId) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT TagRemoteIdResourceRelationTableresourceId_Resourceid_fk FOREIGN KEY (resourceId) REFERENCES ResourceTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO TagRemoteIdResourceRelationTable SELECT * FROM TagRemoteIdResourceRelationTable_old DROP TABLE TagRemoteIdResourceRelationTable_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE TagRemoteIdResourceRelationTable RENAME TO TagRemoteIdResourceRelationTable_old CREATE TABLE TagRemoteIdResourceRelationTable (tagId BIGINT NOT NULL, resourceId BIGINT NOT NULL, remoteId TEXT NOT NULL, CONSTRAINT TagRemoteIdResourceRelationTabletagId_Tagid_fk FOREIGN KEY (tagId) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT TagRemoteIdResourceRelationTableresourceId_Resourceid_fk FOREIGN KEY (resourceId) REFERENCES ResourceTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO TagRemoteIdResourceRelationTable SELECT * FROM TagRemoteIdResourceRelationTable_old DROP TABLE TagRemoteIdResourceRelationTable_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE RelationTable RENAME TO RelationTable_old CREATE TABLE RelationTable (leftId BIGINT NOT NULL, rightId BIGINT NOT NULL, typeId BIGINT DEFAULT 1, remoteId TEXT, CONSTRAINT RelationTableleftId_PimItemid_fk FOREIGN KEY (leftId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT RelationTablerightId_PimItemid_fk FOREIGN KEY (rightId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT RelationTabletypeId_RelationTypeid_fk FOREIGN KEY (typeId) REFERENCES RelationTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED) INSERT INTO RelationTable SELECT * FROM RelationTable_old DROP TABLE RelationTable_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE RelationTable RENAME TO RelationTable_old CREATE TABLE RelationTable (leftId BIGINT NOT NULL, rightId BIGINT NOT NULL, typeId BIGINT DEFAULT 1, remoteId TEXT, CONSTRAINT RelationTableleftId_PimItemid_fk FOREIGN KEY (leftId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT RelationTablerightId_PimItemid_fk FOREIGN KEY (rightId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT RelationTabletypeId_RelationTypeid_fk FOREIGN KEY (typeId) REFERENCES RelationTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED) INSERT INTO RelationTable SELECT * FROM RelationTable_old DROP TABLE RelationTable_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE RelationTable RENAME TO RelationTable_old CREATE TABLE RelationTable (leftId BIGINT NOT NULL, rightId BIGINT NOT NULL, typeId BIGINT DEFAULT 1, remoteId TEXT, CONSTRAINT RelationTableleftId_PimItemid_fk FOREIGN KEY (leftId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT RelationTablerightId_PimItemid_fk FOREIGN KEY (rightId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT RelationTabletypeId_RelationTypeid_fk FOREIGN KEY (typeId) REFERENCES RelationTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED) INSERT INTO RelationTable SELECT * FROM RelationTable_old DROP TABLE RelationTable_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE PimItemFlagRelation RENAME TO PimItemFlagRelation_old CREATE TABLE PimItemFlagRelation (PimItem_id BIGINT NOT NULL, Flag_id BIGINT NOT NULL, PRIMARY KEY (PimItem_id, Flag_id), CONSTRAINT PimItemFlagRelationPimItem_id_PimItemid_fk FOREIGN KEY (PimItem_id) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT PimItemFlagRelationFlag_id_Flagid_fk FOREIGN KEY (Flag_id) REFERENCES FlagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO PimItemFlagRelation SELECT * FROM PimItemFlagRelation_old DROP TABLE PimItemFlagRelation_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE PimItemFlagRelation RENAME TO PimItemFlagRelation_old CREATE TABLE PimItemFlagRelation (PimItem_id BIGINT NOT NULL, Flag_id BIGINT NOT NULL, PRIMARY KEY (PimItem_id, Flag_id), CONSTRAINT PimItemFlagRelationPimItem_id_PimItemid_fk FOREIGN KEY (PimItem_id) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT PimItemFlagRelationFlag_id_Flagid_fk FOREIGN KEY (Flag_id) REFERENCES FlagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO PimItemFlagRelation SELECT * FROM PimItemFlagRelation_old DROP TABLE PimItemFlagRelation_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE PimItemTagRelation RENAME TO PimItemTagRelation_old CREATE TABLE PimItemTagRelation (PimItem_id BIGINT NOT NULL, Tag_id BIGINT NOT NULL, PRIMARY KEY (PimItem_id, Tag_id), CONSTRAINT PimItemTagRelationPimItem_id_PimItemid_fk FOREIGN KEY (PimItem_id) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT PimItemTagRelationTag_id_Tagid_fk FOREIGN KEY (Tag_id) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO PimItemTagRelation SELECT * FROM PimItemTagRelation_old DROP TABLE PimItemTagRelation_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE PimItemTagRelation RENAME TO PimItemTagRelation_old CREATE TABLE PimItemTagRelation (PimItem_id BIGINT NOT NULL, Tag_id BIGINT NOT NULL, PRIMARY KEY (PimItem_id, Tag_id), CONSTRAINT PimItemTagRelationPimItem_id_PimItemid_fk FOREIGN KEY (PimItem_id) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT PimItemTagRelationTag_id_Tagid_fk FOREIGN KEY (Tag_id) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO PimItemTagRelation SELECT * FROM PimItemTagRelation_old DROP TABLE PimItemTagRelation_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE CollectionMimeTypeRelation RENAME TO CollectionMimeTypeRelation_old CREATE TABLE CollectionMimeTypeRelation (Collection_id BIGINT NOT NULL, MimeType_id BIGINT NOT NULL, PRIMARY KEY (Collection_id, MimeType_id), CONSTRAINT CollectionMimeTypeRelationCollection_id_Collectionid_fk FOREIGN KEY (Collection_id) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT CollectionMimeTypeRelationMimeType_id_MimeTypeid_fk FOREIGN KEY (MimeType_id) REFERENCES MimeTypeTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO CollectionMimeTypeRelation SELECT * FROM CollectionMimeTypeRelation_old DROP TABLE CollectionMimeTypeRelation_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE CollectionMimeTypeRelation RENAME TO CollectionMimeTypeRelation_old CREATE TABLE CollectionMimeTypeRelation (Collection_id BIGINT NOT NULL, MimeType_id BIGINT NOT NULL, PRIMARY KEY (Collection_id, MimeType_id), CONSTRAINT CollectionMimeTypeRelationCollection_id_Collectionid_fk FOREIGN KEY (Collection_id) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT CollectionMimeTypeRelationMimeType_id_MimeTypeid_fk FOREIGN KEY (MimeType_id) REFERENCES MimeTypeTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO CollectionMimeTypeRelation SELECT * FROM CollectionMimeTypeRelation_old DROP TABLE CollectionMimeTypeRelation_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE CollectionPimItemRelation RENAME TO CollectionPimItemRelation_old CREATE TABLE CollectionPimItemRelation (Collection_id BIGINT NOT NULL, PimItem_id BIGINT NOT NULL, PRIMARY KEY (Collection_id, PimItem_id), CONSTRAINT CollectionPimItemRelationCollection_id_Collectionid_fk FOREIGN KEY (Collection_id) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT CollectionPimItemRelationPimItem_id_PimItemid_fk FOREIGN KEY (PimItem_id) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO CollectionPimItemRelation SELECT * FROM CollectionPimItemRelation_old DROP TABLE CollectionPimItemRelation_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE CollectionPimItemRelation RENAME TO CollectionPimItemRelation_old CREATE TABLE CollectionPimItemRelation (Collection_id BIGINT NOT NULL, PimItem_id BIGINT NOT NULL, PRIMARY KEY (Collection_id, PimItem_id), CONSTRAINT CollectionPimItemRelationCollection_id_Collectionid_fk FOREIGN KEY (Collection_id) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT CollectionPimItemRelationPimItem_id_PimItemid_fk FOREIGN KEY (PimItem_id) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO CollectionPimItemRelation SELECT * FROM CollectionPimItemRelation_old DROP TABLE CollectionPimItemRelation_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF diff --git a/autotests/server/dbtest_data/dbinit_sqlite_incremental b/autotests/server/dbtest_data/dbinit_sqlite_incremental index 7ea6182d7..24ef4b409 100644 --- a/autotests/server/dbtest_data/dbinit_sqlite_incremental +++ b/autotests/server/dbtest_data/dbinit_sqlite_incremental @@ -1,745 +1,745 @@ ALTER TABLE SchemaVersionTable ADD COLUMN version INTEGER NOT NULL DEFAULT 0 ALTER TABLE SchemaVersionTable ADD COLUMN generation INTEGER NOT NULL DEFAULT 0 ALTER TABLE ResourceTable ADD COLUMN id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ALTER TABLE ResourceTable ADD COLUMN name TEXT UNIQUE NOT NULL ALTER TABLE ResourceTable ADD COLUMN isVirtual BOOL DEFAULT 0 ALTER TABLE CollectionTable ADD COLUMN id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ALTER TABLE CollectionTable ADD COLUMN remoteId TEXT ALTER TABLE CollectionTable ADD COLUMN remoteRevision TEXT ALTER TABLE CollectionTable ADD COLUMN name TEXT NOT NULL ALTER TABLE CollectionTable ADD COLUMN parentId BIGINT ALTER TABLE CollectionTable ADD COLUMN resourceId BIGINT NOT NULL ALTER TABLE CollectionTable ADD COLUMN enabled BOOL NOT NULL DEFAULT 1 ALTER TABLE CollectionTable ADD COLUMN syncPref TINYINT DEFAULT 2 ALTER TABLE CollectionTable ADD COLUMN displayPref TINYINT DEFAULT 2 ALTER TABLE CollectionTable ADD COLUMN indexPref TINYINT DEFAULT 2 ALTER TABLE CollectionTable ADD COLUMN referenced BOOL NOT NULL DEFAULT 0 ALTER TABLE CollectionTable ADD COLUMN cachePolicyInherit BOOL NOT NULL DEFAULT 1 ALTER TABLE CollectionTable ADD COLUMN cachePolicyCheckInterval INTEGER NOT NULL DEFAULT -1 ALTER TABLE CollectionTable ADD COLUMN cachePolicyCacheTimeout INTEGER NOT NULL DEFAULT -1 ALTER TABLE CollectionTable ADD COLUMN cachePolicySyncOnDemand BOOL NOT NULL DEFAULT 0 ALTER TABLE CollectionTable ADD COLUMN cachePolicyLocalParts TEXT ALTER TABLE CollectionTable ADD COLUMN queryString TEXT ALTER TABLE CollectionTable ADD COLUMN queryAttributes TEXT ALTER TABLE CollectionTable ADD COLUMN queryCollections TEXT ALTER TABLE CollectionTable ADD COLUMN isVirtual BOOL DEFAULT 0 ALTER TABLE MimeTypeTable ADD COLUMN id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ALTER TABLE MimeTypeTable ADD COLUMN name TEXT UNIQUE NOT NULL ALTER TABLE PimItemTable ADD COLUMN id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ALTER TABLE PimItemTable ADD COLUMN rev INTEGER NOT NULL DEFAULT 0 ALTER TABLE PimItemTable ADD COLUMN remoteId TEXT ALTER TABLE PimItemTable ADD COLUMN remoteRevision TEXT ALTER TABLE PimItemTable ADD COLUMN gid TEXT ALTER TABLE PimItemTable ADD COLUMN collectionId BIGINT ALTER TABLE PimItemTable ADD COLUMN mimeTypeId BIGINT ALTER TABLE PimItemTable ADD COLUMN datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ALTER TABLE PimItemTable ADD COLUMN atime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ALTER TABLE PimItemTable ADD COLUMN dirty BOOL ALTER TABLE PimItemTable ADD COLUMN size BIGINT NOT NULL DEFAULT 0 ALTER TABLE FlagTable ADD COLUMN id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ALTER TABLE FlagTable ADD COLUMN name TEXT UNIQUE NOT NULL ALTER TABLE PartTypeTable ADD COLUMN id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ALTER TABLE PartTypeTable ADD COLUMN name TEXT NOT NULL ALTER TABLE PartTypeTable ADD COLUMN ns TEXT NOT NULL ALTER TABLE PartTable ADD COLUMN id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ALTER TABLE PartTable ADD COLUMN pimItemId BIGINT NOT NULL ALTER TABLE PartTable ADD COLUMN data LONGBLOB ALTER TABLE PartTable ADD COLUMN datasize BIGINT NOT NULL ALTER TABLE PartTable ADD COLUMN version INTEGER DEFAULT 0 ALTER TABLE PartTable ADD COLUMN storage TINYINT DEFAULT 0 ALTER TABLE CollectionAttributeTable ADD COLUMN id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ALTER TABLE CollectionAttributeTable ADD COLUMN collectionId BIGINT NOT NULL ALTER TABLE CollectionAttributeTable ADD COLUMN type LONGBLOB NOT NULL ALTER TABLE CollectionAttributeTable ADD COLUMN value LONGBLOB ALTER TABLE TagTypeTable ADD COLUMN id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ALTER TABLE TagTypeTable ADD COLUMN name TEXT UNIQUE NOT NULL ALTER TABLE TagTable ADD COLUMN id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ALTER TABLE TagTable ADD COLUMN gid TEXT NOT NULL ALTER TABLE TagTable ADD COLUMN parentId BIGINT ALTER TABLE TagTable ADD COLUMN typeId BIGINT DEFAULT 1 ALTER TABLE TagAttributeTable ADD COLUMN id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ALTER TABLE TagAttributeTable ADD COLUMN tagId BIGINT NOT NULL ALTER TABLE TagAttributeTable ADD COLUMN type LONGBLOB NOT NULL ALTER TABLE TagAttributeTable ADD COLUMN value LONGBLOB ALTER TABLE TagRemoteIdResourceRelationTable ADD COLUMN tagId BIGINT NOT NULL ALTER TABLE TagRemoteIdResourceRelationTable ADD COLUMN resourceId BIGINT NOT NULL ALTER TABLE TagRemoteIdResourceRelationTable ADD COLUMN remoteId TEXT NOT NULL ALTER TABLE RelationTypeTable ADD COLUMN id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ALTER TABLE RelationTypeTable ADD COLUMN name TEXT UNIQUE NOT NULL ALTER TABLE RelationTable ADD COLUMN leftId BIGINT NOT NULL ALTER TABLE RelationTable ADD COLUMN rightId BIGINT NOT NULL ALTER TABLE RelationTable ADD COLUMN typeId BIGINT DEFAULT 1 ALTER TABLE RelationTable ADD COLUMN remoteId TEXT ALTER TABLE PimItemFlagRelation ADD COLUMN PimItem_id BIGINT NOT NULL ALTER TABLE PimItemFlagRelation ADD COLUMN Flag_id BIGINT NOT NULL ALTER TABLE PimItemTagRelation ADD COLUMN PimItem_id BIGINT NOT NULL ALTER TABLE PimItemTagRelation ADD COLUMN Tag_id BIGINT NOT NULL ALTER TABLE CollectionMimeTypeRelation ADD COLUMN Collection_id BIGINT NOT NULL ALTER TABLE CollectionMimeTypeRelation ADD COLUMN MimeType_id BIGINT NOT NULL ALTER TABLE CollectionPimItemRelation ADD COLUMN Collection_id BIGINT NOT NULL ALTER TABLE CollectionPimItemRelation ADD COLUMN PimItem_id BIGINT NOT NULL -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE PimItemTable RENAME TO PimItemTable_old CREATE TABLE PimItemTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, rev INTEGER NOT NULL DEFAULT 0, remoteId TEXT, remoteRevision TEXT, gid TEXT, collectionId BIGINT, mimeTypeId BIGINT, datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, atime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, dirty BOOL, size BIGINT NOT NULL DEFAULT 0, CONSTRAINT PimItemTablecollectionId_Collectionid_fk FOREIGN KEY (collectionId) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT PimItemTablemimeTypeId_MimeTypeid_fk FOREIGN KEY (mimeTypeId) REFERENCES MimeTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED) INSERT INTO PimItemTable SELECT * FROM PimItemTable_old DROP TABLE PimItemTable_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE CollectionAttributeTable RENAME TO CollectionAttributeTable_old CREATE TABLE CollectionAttributeTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, collectionId BIGINT NOT NULL, type LONGBLOB NOT NULL, value LONGBLOB, CONSTRAINT CollectionAttributeTablecollectionId_Collectionid_fk FOREIGN KEY (collectionId) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO CollectionAttributeTable SELECT * FROM CollectionAttributeTable_old DROP TABLE CollectionAttributeTable_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE CollectionTable RENAME TO CollectionTable_old CREATE TABLE CollectionTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, remoteId TEXT, remoteRevision TEXT, name TEXT NOT NULL, parentId BIGINT, resourceId BIGINT NOT NULL, enabled BOOL NOT NULL DEFAULT 1, syncPref TINYINT DEFAULT 2, displayPref TINYINT DEFAULT 2, indexPref TINYINT DEFAULT 2, referenced BOOL NOT NULL DEFAULT 0, cachePolicyInherit BOOL NOT NULL DEFAULT 1, cachePolicyCheckInterval INTEGER NOT NULL DEFAULT -1, cachePolicyCacheTimeout INTEGER NOT NULL DEFAULT -1, cachePolicySyncOnDemand BOOL NOT NULL DEFAULT 0, cachePolicyLocalParts TEXT, queryString TEXT, queryAttributes TEXT, queryCollections TEXT, isVirtual BOOL DEFAULT 0, CONSTRAINT CollectionTableparentId_Collectionid_fk FOREIGN KEY (parentId) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT CollectionTableresourceId_Resourceid_fk FOREIGN KEY (resourceId) REFERENCES ResourceTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO CollectionTable SELECT * FROM CollectionTable_old DROP TABLE CollectionTable_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE CollectionTable RENAME TO CollectionTable_old CREATE TABLE CollectionTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, remoteId TEXT, remoteRevision TEXT, name TEXT NOT NULL, parentId BIGINT, resourceId BIGINT NOT NULL, enabled BOOL NOT NULL DEFAULT 1, syncPref TINYINT DEFAULT 2, displayPref TINYINT DEFAULT 2, indexPref TINYINT DEFAULT 2, referenced BOOL NOT NULL DEFAULT 0, cachePolicyInherit BOOL NOT NULL DEFAULT 1, cachePolicyCheckInterval INTEGER NOT NULL DEFAULT -1, cachePolicyCacheTimeout INTEGER NOT NULL DEFAULT -1, cachePolicySyncOnDemand BOOL NOT NULL DEFAULT 0, cachePolicyLocalParts TEXT, queryString TEXT, queryAttributes TEXT, queryCollections TEXT, isVirtual BOOL DEFAULT 0, CONSTRAINT CollectionTableparentId_Collectionid_fk FOREIGN KEY (parentId) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT CollectionTableresourceId_Resourceid_fk FOREIGN KEY (resourceId) REFERENCES ResourceTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO CollectionTable SELECT * FROM CollectionTable_old DROP TABLE CollectionTable_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE PimItemTable RENAME TO PimItemTable_old CREATE TABLE PimItemTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, rev INTEGER NOT NULL DEFAULT 0, remoteId TEXT, remoteRevision TEXT, gid TEXT, collectionId BIGINT, mimeTypeId BIGINT, datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, atime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, dirty BOOL, size BIGINT NOT NULL DEFAULT 0, CONSTRAINT PimItemTablecollectionId_Collectionid_fk FOREIGN KEY (collectionId) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT PimItemTablemimeTypeId_MimeTypeid_fk FOREIGN KEY (mimeTypeId) REFERENCES MimeTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED) INSERT INTO PimItemTable SELECT * FROM PimItemTable_old DROP TABLE PimItemTable_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE PimItemTable RENAME TO PimItemTable_old CREATE TABLE PimItemTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, rev INTEGER NOT NULL DEFAULT 0, remoteId TEXT, remoteRevision TEXT, gid TEXT, collectionId BIGINT, mimeTypeId BIGINT, datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, atime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, dirty BOOL, size BIGINT NOT NULL DEFAULT 0, CONSTRAINT PimItemTablecollectionId_Collectionid_fk FOREIGN KEY (collectionId) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT PimItemTablemimeTypeId_MimeTypeid_fk FOREIGN KEY (mimeTypeId) REFERENCES MimeTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED) INSERT INTO PimItemTable SELECT * FROM PimItemTable_old DROP TABLE PimItemTable_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE PartTable RENAME TO PartTable_old CREATE TABLE PartTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, pimItemId BIGINT NOT NULL, partTypeId BIGINT NOT NULL, data LONGBLOB, datasize BIGINT NOT NULL, version INTEGER DEFAULT 0, storage TINYINT DEFAULT 0, CONSTRAINT PartTablepimItemId_PimItemid_fk FOREIGN KEY (pimItemId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT PartTablepartTypeId_PartTypeid_fk FOREIGN KEY (partTypeId) REFERENCES PartTypeTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO PartTable SELECT * FROM PartTable_old DROP TABLE PartTable_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE PartTable RENAME TO PartTable_old CREATE TABLE PartTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, pimItemId BIGINT NOT NULL, partTypeId BIGINT NOT NULL, data LONGBLOB, datasize BIGINT NOT NULL, version INTEGER DEFAULT 0, storage TINYINT DEFAULT 0, CONSTRAINT PartTablepimItemId_PimItemid_fk FOREIGN KEY (pimItemId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT PartTablepartTypeId_PartTypeid_fk FOREIGN KEY (partTypeId) REFERENCES PartTypeTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO PartTable SELECT * FROM PartTable_old DROP TABLE PartTable_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE CollectionAttributeTable RENAME TO CollectionAttributeTable_old CREATE TABLE CollectionAttributeTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, collectionId BIGINT NOT NULL, type LONGBLOB NOT NULL, value LONGBLOB, CONSTRAINT CollectionAttributeTablecollectionId_Collectionid_fk FOREIGN KEY (collectionId) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO CollectionAttributeTable SELECT * FROM CollectionAttributeTable_old DROP TABLE CollectionAttributeTable_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE TagTable RENAME TO TagTable_old CREATE TABLE TagTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, gid TEXT NOT NULL, parentId BIGINT, typeId BIGINT DEFAULT 1, CONSTRAINT TagTableparentId_Tagid_fk FOREIGN KEY (parentId) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT TagTabletypeId_TagTypeid_fk FOREIGN KEY (typeId) REFERENCES TagTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED) INSERT INTO TagTable SELECT * FROM TagTable_old DROP TABLE TagTable_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE TagTable RENAME TO TagTable_old CREATE TABLE TagTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, gid TEXT NOT NULL, parentId BIGINT, typeId BIGINT DEFAULT 1, CONSTRAINT TagTableparentId_Tagid_fk FOREIGN KEY (parentId) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT TagTabletypeId_TagTypeid_fk FOREIGN KEY (typeId) REFERENCES TagTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED) INSERT INTO TagTable SELECT * FROM TagTable_old DROP TABLE TagTable_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE TagAttributeTable RENAME TO TagAttributeTable_old CREATE TABLE TagAttributeTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, tagId BIGINT NOT NULL, type LONGBLOB NOT NULL, value LONGBLOB, CONSTRAINT TagAttributeTabletagId_Tagid_fk FOREIGN KEY (tagId) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO TagAttributeTable SELECT * FROM TagAttributeTable_old DROP TABLE TagAttributeTable_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE TagRemoteIdResourceRelationTable RENAME TO TagRemoteIdResourceRelationTable_old CREATE TABLE TagRemoteIdResourceRelationTable (tagId BIGINT NOT NULL, resourceId BIGINT NOT NULL, remoteId TEXT NOT NULL, CONSTRAINT TagRemoteIdResourceRelationTabletagId_Tagid_fk FOREIGN KEY (tagId) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT TagRemoteIdResourceRelationTableresourceId_Resourceid_fk FOREIGN KEY (resourceId) REFERENCES ResourceTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO TagRemoteIdResourceRelationTable SELECT * FROM TagRemoteIdResourceRelationTable_old DROP TABLE TagRemoteIdResourceRelationTable_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE TagRemoteIdResourceRelationTable RENAME TO TagRemoteIdResourceRelationTable_old CREATE TABLE TagRemoteIdResourceRelationTable (tagId BIGINT NOT NULL, resourceId BIGINT NOT NULL, remoteId TEXT NOT NULL, CONSTRAINT TagRemoteIdResourceRelationTabletagId_Tagid_fk FOREIGN KEY (tagId) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT TagRemoteIdResourceRelationTableresourceId_Resourceid_fk FOREIGN KEY (resourceId) REFERENCES ResourceTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO TagRemoteIdResourceRelationTable SELECT * FROM TagRemoteIdResourceRelationTable_old DROP TABLE TagRemoteIdResourceRelationTable_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE RelationTable RENAME TO RelationTable_old CREATE TABLE RelationTable (leftId BIGINT NOT NULL, rightId BIGINT NOT NULL, typeId BIGINT DEFAULT 1, remoteId TEXT, CONSTRAINT RelationTableleftId_PimItemid_fk FOREIGN KEY (leftId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT RelationTablerightId_PimItemid_fk FOREIGN KEY (rightId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT RelationTabletypeId_RelationTypeid_fk FOREIGN KEY (typeId) REFERENCES RelationTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED) INSERT INTO RelationTable SELECT * FROM RelationTable_old DROP TABLE RelationTable_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE RelationTable RENAME TO RelationTable_old CREATE TABLE RelationTable (leftId BIGINT NOT NULL, rightId BIGINT NOT NULL, typeId BIGINT DEFAULT 1, remoteId TEXT, CONSTRAINT RelationTableleftId_PimItemid_fk FOREIGN KEY (leftId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT RelationTablerightId_PimItemid_fk FOREIGN KEY (rightId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT RelationTabletypeId_RelationTypeid_fk FOREIGN KEY (typeId) REFERENCES RelationTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED) INSERT INTO RelationTable SELECT * FROM RelationTable_old DROP TABLE RelationTable_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE RelationTable RENAME TO RelationTable_old CREATE TABLE RelationTable (leftId BIGINT NOT NULL, rightId BIGINT NOT NULL, typeId BIGINT DEFAULT 1, remoteId TEXT, CONSTRAINT RelationTableleftId_PimItemid_fk FOREIGN KEY (leftId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT RelationTablerightId_PimItemid_fk FOREIGN KEY (rightId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT RelationTabletypeId_RelationTypeid_fk FOREIGN KEY (typeId) REFERENCES RelationTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED) INSERT INTO RelationTable SELECT * FROM RelationTable_old DROP TABLE RelationTable_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE PimItemFlagRelation RENAME TO PimItemFlagRelation_old CREATE TABLE PimItemFlagRelation (PimItem_id BIGINT NOT NULL, Flag_id BIGINT NOT NULL, PRIMARY KEY (PimItem_id, Flag_id), CONSTRAINT PimItemFlagRelationPimItem_id_PimItemid_fk FOREIGN KEY (PimItem_id) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT PimItemFlagRelationFlag_id_Flagid_fk FOREIGN KEY (Flag_id) REFERENCES FlagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO PimItemFlagRelation SELECT * FROM PimItemFlagRelation_old DROP TABLE PimItemFlagRelation_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE PimItemFlagRelation RENAME TO PimItemFlagRelation_old CREATE TABLE PimItemFlagRelation (PimItem_id BIGINT NOT NULL, Flag_id BIGINT NOT NULL, PRIMARY KEY (PimItem_id, Flag_id), CONSTRAINT PimItemFlagRelationPimItem_id_PimItemid_fk FOREIGN KEY (PimItem_id) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT PimItemFlagRelationFlag_id_Flagid_fk FOREIGN KEY (Flag_id) REFERENCES FlagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO PimItemFlagRelation SELECT * FROM PimItemFlagRelation_old DROP TABLE PimItemFlagRelation_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE PimItemTagRelation RENAME TO PimItemTagRelation_old CREATE TABLE PimItemTagRelation (PimItem_id BIGINT NOT NULL, Tag_id BIGINT NOT NULL, PRIMARY KEY (PimItem_id, Tag_id), CONSTRAINT PimItemTagRelationPimItem_id_PimItemid_fk FOREIGN KEY (PimItem_id) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT PimItemTagRelationTag_id_Tagid_fk FOREIGN KEY (Tag_id) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO PimItemTagRelation SELECT * FROM PimItemTagRelation_old DROP TABLE PimItemTagRelation_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE PimItemTagRelation RENAME TO PimItemTagRelation_old CREATE TABLE PimItemTagRelation (PimItem_id BIGINT NOT NULL, Tag_id BIGINT NOT NULL, PRIMARY KEY (PimItem_id, Tag_id), CONSTRAINT PimItemTagRelationPimItem_id_PimItemid_fk FOREIGN KEY (PimItem_id) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT PimItemTagRelationTag_id_Tagid_fk FOREIGN KEY (Tag_id) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO PimItemTagRelation SELECT * FROM PimItemTagRelation_old DROP TABLE PimItemTagRelation_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE CollectionMimeTypeRelation RENAME TO CollectionMimeTypeRelation_old CREATE TABLE CollectionMimeTypeRelation (Collection_id BIGINT NOT NULL, MimeType_id BIGINT NOT NULL, PRIMARY KEY (Collection_id, MimeType_id), CONSTRAINT CollectionMimeTypeRelationCollection_id_Collectionid_fk FOREIGN KEY (Collection_id) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT CollectionMimeTypeRelationMimeType_id_MimeTypeid_fk FOREIGN KEY (MimeType_id) REFERENCES MimeTypeTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO CollectionMimeTypeRelation SELECT * FROM CollectionMimeTypeRelation_old DROP TABLE CollectionMimeTypeRelation_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE CollectionMimeTypeRelation RENAME TO CollectionMimeTypeRelation_old CREATE TABLE CollectionMimeTypeRelation (Collection_id BIGINT NOT NULL, MimeType_id BIGINT NOT NULL, PRIMARY KEY (Collection_id, MimeType_id), CONSTRAINT CollectionMimeTypeRelationCollection_id_Collectionid_fk FOREIGN KEY (Collection_id) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT CollectionMimeTypeRelationMimeType_id_MimeTypeid_fk FOREIGN KEY (MimeType_id) REFERENCES MimeTypeTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO CollectionMimeTypeRelation SELECT * FROM CollectionMimeTypeRelation_old DROP TABLE CollectionMimeTypeRelation_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE CollectionPimItemRelation RENAME TO CollectionPimItemRelation_old CREATE TABLE CollectionPimItemRelation (Collection_id BIGINT NOT NULL, PimItem_id BIGINT NOT NULL, PRIMARY KEY (Collection_id, PimItem_id), CONSTRAINT CollectionPimItemRelationCollection_id_Collectionid_fk FOREIGN KEY (Collection_id) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT CollectionPimItemRelationPimItem_id_PimItemid_fk FOREIGN KEY (PimItem_id) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO CollectionPimItemRelation SELECT * FROM CollectionPimItemRelation_old DROP TABLE CollectionPimItemRelation_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF -PRAGMA foreign_key_check=OFF +PRAGMA defer_foreign_keys=ON BEGIN TRANSACTION ALTER TABLE CollectionPimItemRelation RENAME TO CollectionPimItemRelation_old CREATE TABLE CollectionPimItemRelation (Collection_id BIGINT NOT NULL, PimItem_id BIGINT NOT NULL, PRIMARY KEY (Collection_id, PimItem_id), CONSTRAINT CollectionPimItemRelationCollection_id_Collectionid_fk FOREIGN KEY (Collection_id) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, CONSTRAINT CollectionPimItemRelationPimItem_id_PimItemid_fk FOREIGN KEY (PimItem_id) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED) INSERT INTO CollectionPimItemRelation SELECT * FROM CollectionPimItemRelation_old DROP TABLE CollectionPimItemRelation_old COMMIT -PRAGMA foreign_key_check=ON +PRAGMA defer_foreign_keys=OFF diff --git a/src/server/storage/dbinitializer_p.cpp b/src/server/storage/dbinitializer_p.cpp index 0c15553ad..18cdd4a51 100644 --- a/src/server/storage/dbinitializer_p.cpp +++ b/src/server/storage/dbinitializer_p.cpp @@ -1,370 +1,370 @@ /*************************************************************************** * Copyright (C) 2006 by Tobias Koenig * * Copyright (C) 2010 by Volker Krause * * * * This program is free software; you can redistribute it and/or modify * * it under the terms of the GNU Library General Public License as * * published by the Free Software Foundation; either version 2 of the * * License, or (at your option) any later version. * * * * This program is distributed in the hope that it will be useful, * * but WITHOUT ANY WARRANTY; without even the implied warranty of * * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * * GNU General Public License for more details. * * * * You should have received a copy of the GNU Library General Public * * License along with this program; if not, write to the * * Free Software Foundation, Inc., * * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. * ***************************************************************************/ #include "storage/dbinitializer_p.h" using namespace Akonadi::Server; //BEGIN MySQL DbInitializerMySql::DbInitializerMySql(const QSqlDatabase &database) : DbInitializer(database) { } bool DbInitializerMySql::hasForeignKeyConstraints() const { return true; } QString DbInitializerMySql::sqlType(const ColumnDescription &col, int size) const { if (col.type == QLatin1String("QString")) { return QLatin1Literal("VARBINARY(") + QString::number(size <= 0 ? 255 : size) + QLatin1Literal(")"); } else { return DbInitializer::sqlType(col, size); } } QString DbInitializerMySql::buildCreateTableStatement(const TableDescription &tableDescription) const { QStringList columns; QStringList references; Q_FOREACH (const ColumnDescription &columnDescription, tableDescription.columns) { columns.append(buildColumnStatement(columnDescription, tableDescription)); if (!columnDescription.refTable.isEmpty() && !columnDescription.refColumn.isEmpty()) { references << QStringLiteral("FOREIGN KEY (%1) REFERENCES %2Table(%3) ") .arg(columnDescription.name, columnDescription.refTable, columnDescription.refColumn) + buildReferentialAction(columnDescription.onUpdate, columnDescription.onDelete); } } if (tableDescription.primaryKeyColumnCount() > 1) { columns.push_back(buildPrimaryKeyStatement(tableDescription)); } columns << references; const QString tableProperties = QStringLiteral(" COLLATE=utf8_general_ci DEFAULT CHARSET=utf8"); return QStringLiteral("CREATE TABLE %1 (%2) %3").arg(tableDescription.name, columns.join(QStringLiteral(", ")), tableProperties); } QString DbInitializerMySql::buildColumnStatement(const ColumnDescription &columnDescription, const TableDescription &tableDescription) const { QString column = columnDescription.name; column += QLatin1Char(' ') + sqlType(columnDescription, columnDescription.size); if (!columnDescription.allowNull) { column += QLatin1String(" NOT NULL"); } if (columnDescription.isAutoIncrement) { column += QLatin1String(" AUTO_INCREMENT"); } if (columnDescription.isPrimaryKey && tableDescription.primaryKeyColumnCount() == 1) { column += QLatin1String(" PRIMARY KEY"); } if (columnDescription.isUnique) { column += QLatin1String(" UNIQUE"); } if (!columnDescription.defaultValue.isEmpty()) { const QString defaultValue = sqlValue(columnDescription, columnDescription.defaultValue); if (!defaultValue.isEmpty()) { column += QStringLiteral(" DEFAULT %1").arg(defaultValue); } } return column; } QString DbInitializerMySql::buildInsertValuesStatement(const TableDescription &tableDescription, const DataDescription &dataDescription) const { QMap data = dataDescription.data; QMutableMapIterator it(data); while (it.hasNext()) { it.next(); it.value().replace(QLatin1String("\\"), QLatin1String("\\\\")); } return QStringLiteral("INSERT INTO %1 (%2) VALUES (%3)") .arg(tableDescription.name, QStringList(data.keys()).join(QLatin1Char(',')), QStringList(data.values()).join(QLatin1Char(','))); } QStringList DbInitializerMySql::buildAddForeignKeyConstraintStatements(const TableDescription &table, const ColumnDescription &column) const { return { QStringLiteral("ALTER TABLE %1 ADD FOREIGN KEY (%2) REFERENCES %4Table(%5) %6") .arg(table.name, column.name, column.refTable, column.refColumn, buildReferentialAction(column.onUpdate, column.onDelete)) }; } QStringList DbInitializerMySql::buildRemoveForeignKeyConstraintStatements(const DbIntrospector::ForeignKey &fk, const TableDescription &table) const { return { QStringLiteral("ALTER TABLE %1 DROP FOREIGN KEY %2").arg(table.name, fk.name) }; } //END MySQL //BEGIN Sqlite DbInitializerSqlite::DbInitializerSqlite(const QSqlDatabase &database) : DbInitializer(database) { } bool DbInitializerSqlite::hasForeignKeyConstraints() const { return true; } QString DbInitializerSqlite::buildCreateTableStatement(const TableDescription &tableDescription) const { QStringList columns; columns.reserve(tableDescription.columns.count() + 1); for (const ColumnDescription &columnDescription : qAsConst(tableDescription.columns)) { columns.append(buildColumnStatement(columnDescription, tableDescription)); } if (tableDescription.primaryKeyColumnCount() > 1) { columns.push_back(buildPrimaryKeyStatement(tableDescription)); } QStringList references; for (const ColumnDescription &columnDescription : qAsConst(tableDescription.columns)) { if (!columnDescription.refTable.isEmpty() && !columnDescription.refColumn.isEmpty()) { const auto constraintName = QStringLiteral("%1%2_%3%4_fk").arg(tableDescription.name, columnDescription.name, columnDescription.refTable, columnDescription.refColumn); references << QStringLiteral("CONSTRAINT %1 FOREIGN KEY (%2) REFERENCES %3Table(%4) %5 DEFERRABLE INITIALLY DEFERRED") .arg(constraintName, columnDescription.name, columnDescription.refTable, columnDescription.refColumn, buildReferentialAction(columnDescription.onUpdate, columnDescription.onDelete)); } } columns << references; return QStringLiteral("CREATE TABLE %1 (%2)").arg(tableDescription.name, columns.join(QStringLiteral(", "))); } QString DbInitializerSqlite::buildColumnStatement(const ColumnDescription &columnDescription, const TableDescription &tableDescription) const { QString column = columnDescription.name + QLatin1Char(' '); if (columnDescription.isAutoIncrement) { column += QLatin1String("INTEGER"); } else { column += sqlType(columnDescription, columnDescription.size); } if (columnDescription.isPrimaryKey && tableDescription.primaryKeyColumnCount() == 1) { column += QLatin1String(" PRIMARY KEY"); } else if (columnDescription.isUnique) { column += QLatin1String(" UNIQUE"); } if (columnDescription.isAutoIncrement) { column += QLatin1String(" AUTOINCREMENT"); } if (!columnDescription.allowNull) { column += QLatin1String(" NOT NULL"); } if (!columnDescription.defaultValue.isEmpty()) { const QString defaultValue = sqlValue(columnDescription, columnDescription.defaultValue); if (!defaultValue.isEmpty()) { column += QStringLiteral(" DEFAULT %1").arg(defaultValue); } } return column; } QString DbInitializerSqlite::buildInsertValuesStatement(const TableDescription &tableDescription, const DataDescription &dataDescription) const { QMap data = dataDescription.data; QMutableMapIterator it(data); while (it.hasNext()) { it.next(); it.value().replace(QLatin1String("true"), QLatin1String("1")); it.value().replace(QLatin1String("false"), QLatin1String("0")); } return QStringLiteral("INSERT INTO %1 (%2) VALUES (%3)") .arg(tableDescription.name, QStringList(data.keys()).join(QLatin1Char(',')), QStringList(data.values()).join(QLatin1Char(','))); } QString DbInitializerSqlite::sqlValue(const ColumnDescription &col, const QString &value) const { if (col.type == QLatin1String("bool")) { if (value == QLatin1String("false")) { return QStringLiteral("0"); } else if (value == QLatin1String("true")) { return QStringLiteral("1"); } return value; } return Akonadi::Server::DbInitializer::sqlValue(col, value); } QStringList DbInitializerSqlite::buildAddForeignKeyConstraintStatements(const TableDescription &table, const ColumnDescription &) const { return buildUpdateForeignKeyConstraintsStatements(table); } QStringList DbInitializerSqlite::buildRemoveForeignKeyConstraintStatements(const DbIntrospector::ForeignKey &, const TableDescription &table) const { return buildUpdateForeignKeyConstraintsStatements(table); } QStringList DbInitializerSqlite::buildUpdateForeignKeyConstraintsStatements(const TableDescription &table) const { // Unforunately, SQLite does not support add or removing foreign keys through ALTER TABLE, // this is the only way how to do it. return { - QStringLiteral("PRAGMA foreign_key_check=OFF"), + QStringLiteral("PRAGMA defer_foreign_keys=ON"), QStringLiteral("BEGIN TRANSACTION"), QStringLiteral("ALTER TABLE %1 RENAME TO %1_old").arg(table.name), buildCreateTableStatement(table), QStringLiteral("INSERT INTO %1 SELECT * FROM %1_old").arg(table.name), QStringLiteral("DROP TABLE %1_old").arg(table.name), QStringLiteral("COMMIT"), - QStringLiteral("PRAGMA foreign_key_check=ON") + QStringLiteral("PRAGMA defer_foreign_keys=OFF") }; } //END Sqlite //BEGIN PostgreSQL DbInitializerPostgreSql::DbInitializerPostgreSql(const QSqlDatabase &database) : DbInitializer(database) { } bool DbInitializerPostgreSql::hasForeignKeyConstraints() const { return true; } QString DbInitializerPostgreSql::sqlType(const ColumnDescription &col, int size) const { if (col.type == QLatin1String("qint64")) { return QStringLiteral("int8"); } else if (col.type == QLatin1String("QByteArray")) { return QStringLiteral("BYTEA"); } else if (col.isEnum) { return QStringLiteral("SMALLINT"); } return DbInitializer::sqlType(col, size); } QString DbInitializerPostgreSql::buildCreateTableStatement(const TableDescription &tableDescription) const { QStringList columns; columns.reserve(tableDescription.columns.size() + 1); Q_FOREACH (const ColumnDescription &columnDescription, tableDescription.columns) { columns.append(buildColumnStatement(columnDescription, tableDescription)); } if (tableDescription.primaryKeyColumnCount() > 1) { columns.push_back(buildPrimaryKeyStatement(tableDescription)); } return QStringLiteral("CREATE TABLE %1 (%2)").arg(tableDescription.name, columns.join(QStringLiteral(", "))); } QString DbInitializerPostgreSql::buildColumnStatement(const ColumnDescription &columnDescription, const TableDescription &tableDescription) const { QString column = columnDescription.name + QLatin1Char(' '); if (columnDescription.isAutoIncrement) { column += QLatin1String("SERIAL"); } else { column += sqlType(columnDescription, columnDescription.size); } if (columnDescription.isPrimaryKey && tableDescription.primaryKeyColumnCount() == 1) { column += QLatin1String(" PRIMARY KEY"); } else if (columnDescription.isUnique) { column += QLatin1String(" UNIQUE"); } if (!columnDescription.allowNull && !(columnDescription.isPrimaryKey && tableDescription.primaryKeyColumnCount() == 1)) { column += QLatin1String(" NOT NULL"); } if (!columnDescription.defaultValue.isEmpty()) { const QString defaultValue = sqlValue(columnDescription, columnDescription.defaultValue); if (!defaultValue.isEmpty()) { column += QStringLiteral(" DEFAULT %1").arg(defaultValue); } } return column; } QString DbInitializerPostgreSql::buildInsertValuesStatement(const TableDescription &tableDescription, const DataDescription &dataDescription) const { QMap data = dataDescription.data; return QStringLiteral("INSERT INTO %1 (%2) VALUES (%3)") .arg(tableDescription.name, QStringList(data.keys()).join(QLatin1Char(',')), QStringList(data.values()).join(QLatin1Char(','))); } QStringList DbInitializerPostgreSql::buildAddForeignKeyConstraintStatements(const TableDescription &table, const ColumnDescription &column) const { // constraints must have name in PostgreSQL const QString constraintName = table.name + column.name + QLatin1Literal("_") + column.refTable + column.refColumn + QLatin1Literal("_fk"); return { QStringLiteral("ALTER TABLE %1 ADD CONSTRAINT %2 FOREIGN KEY (%3) REFERENCES %4Table(%5) %6 DEFERRABLE INITIALLY DEFERRED") .arg(table.name, constraintName, column.name, column.refTable, column.refColumn, buildReferentialAction(column.onUpdate, column.onDelete)) }; } QStringList DbInitializerPostgreSql::buildRemoveForeignKeyConstraintStatements(const DbIntrospector::ForeignKey &fk, const TableDescription &table) const { return { QStringLiteral("ALTER TABLE %1 DROP CONSTRAINT %2").arg(table.name, fk.name) }; } //END PostgreSQL