/kf6-qt6/pim/akonadi/autotests/server/dbtest_data/dbinit_mysql

0001 CREATE TABLE SchemaVersionTable (version INTEGER NOT NULL DEFAULT 0,
0002                                  generation INTEGER NOT NULL DEFAULT 0)
0003                                  COLLATE=utf8_general_ci DEFAULT CHARSET=utf8
0004
0005 INSERT INTO SchemaVersionTable (version) VALUES (36)
0006
0007 CREATE TABLE ResourceTable (id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
0008                             name VARBINARY(255) NOT NULL UNIQUE,
0009                             isVirtual BOOL DEFAULT false)
0010                             COLLATE=utf8_general_ci DEFAULT CHARSET=utf8
0011
0012 INSERT INTO ResourceTable (isVirtual,name) VALUES (true,'akonadi_search_resource')
0013
0014 CREATE TABLE CollectionTable (id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
0015                               remoteId VARBINARY(255),
0016                               remoteRevision VARBINARY(255),
0017                               name VARBINARY(255) NOT NULL,
0018                               parentId BIGINT,
0019                               resourceId BIGINT NOT NULL,
0020                               enabled BOOL NOT NULL DEFAULT true,
0021                               syncPref TINYINT DEFAULT 2,
0022                               displayPref TINYINT DEFAULT 2,
0023                               indexPref TINYINT DEFAULT 2,
0024                               referenced BOOL NOT NULL DEFAULT false,
0025                               cachePolicyInherit BOOL NOT NULL DEFAULT true,
0026                               cachePolicyCheckInterval INTEGER NOT NULL DEFAULT -1,
0027                               cachePolicyCacheTimeout INTEGER NOT NULL DEFAULT -1,
0028                               cachePolicySyncOnDemand BOOL NOT NULL DEFAULT false,
0029                               cachePolicyLocalParts VARBINARY(255),
0030                               queryString VARBINARY(32768),
0031                               queryAttributes VARBINARY(255),
0032                               queryCollections VARBINARY(255),
0033                               isVirtual BOOL DEFAULT false,
0034                               FOREIGN KEY (parentId) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE,
0035                               FOREIGN KEY (resourceId) REFERENCES ResourceTable(id) ON UPDATE CASCADE ON DELETE CASCADE)
0036                               COLLATE=utf8_general_ci DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
0037
0038 INSERT INTO CollectionTable (isVirtual,name,parentId,resourceId) VALUES (true,'Search',NULL,1)
0039
0040 CREATE TABLE MimeTypeTable (id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
0041                             name VARBINARY(255) NOT NULL UNIQUE)
0042                             COLLATE=utf8_general_ci DEFAULT CHARSET=utf8
0043
0044 CREATE TABLE PimItemTable (id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
0045                            rev INTEGER NOT NULL DEFAULT 0,
0046                            remoteId VARBINARY(255),
0047                            remoteRevision VARBINARY(255),
0048                            gid VARBINARY(255),
0049                            collectionId BIGINT,
0050                            mimeTypeId BIGINT,
0051                            datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
0052                            atime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
0053                            dirty BOOL,
0054                            size BIGINT NOT NULL DEFAULT 0,
0055                            FOREIGN KEY (collectionId) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE,
0056                            FOREIGN KEY (mimeTypeId) REFERENCES MimeTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT)
0057                            COLLATE=utf8_general_ci DEFAULT CHARSET=utf8
0058
0059 CREATE TABLE FlagTable (id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
0060                         name VARBINARY(255) NOT NULL UNIQUE)
0061                         COLLATE=utf8_general_ci DEFAULT CHARSET=utf8
0062
0063 CREATE TABLE PartTypeTable (id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
0064                             name VARBINARY(255) NOT NULL,
0065                             ns VARBINARY(255) NOT NULL)
0066                             COLLATE=utf8_general_ci DEFAULT CHARSET=utf8
0067
0068 CREATE TABLE PartTable (id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
0069                         pimItemId BIGINT NOT NULL,
0070                         partTypeId BIGINT NOT NULL,
0071                         data LONGBLOB,
0072                         datasize BIGINT NOT NULL,
0073                         version INTEGER DEFAULT 0,
0074                         storage TINYINT DEFAULT 0,
0075                         FOREIGN KEY (pimItemId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE,
0076                         FOREIGN KEY (partTypeId) REFERENCES PartTypeTable(id) ON UPDATE CASCADE ON DELETE CASCADE)
0077                         COLLATE=utf8_general_ci DEFAULT CHARSET=utf8
0078
0079 CREATE TABLE CollectionAttributeTable (id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
0080                                        collectionId BIGINT NOT NULL,
0081                                        type LONGBLOB NOT NULL,
0082                                        value LONGBLOB,
0083                                        FOREIGN KEY (collectionId) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE)
0084                                        COLLATE=utf8_general_ci DEFAULT CHARSET=utf8
0085
0086 CREATE TABLE TagTypeTable (id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
0087                            name VARBINARY(255) NOT NULL UNIQUE)
0088                            COLLATE=utf8_general_ci DEFAULT CHARSET=utf8
0089
0090 INSERT INTO TagTypeTable (name) VALUES ('PLAIN')
0091
0092 CREATE TABLE TagTable (id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
0093                        gid VARBINARY(255) NOT NULL,
0094                        parentId BIGINT,
0095                        typeId BIGINT DEFAULT 1,
0096                        FOREIGN KEY (parentId) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE,
0097                        FOREIGN KEY (typeId) REFERENCES TagTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT)
0098                        COLLATE=utf8_general_ci DEFAULT CHARSET=utf8
0099
0100 CREATE TABLE TagAttributeTable (id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
0101                                 tagId BIGINT NOT NULL,
0102                                 type LONGBLOB NOT NULL,
0103                                 value LONGBLOB,
0104                                 FOREIGN KEY (tagId) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE)
0105                                 COLLATE=utf8_general_ci DEFAULT CHARSET=utf8
0106
0107 CREATE TABLE TagRemoteIdResourceRelationTable (tagId BIGINT NOT NULL,
0108                                                resourceId BIGINT NOT NULL,
0109                                                remoteId VARBINARY(255) NOT NULL,
0110                                                FOREIGN KEY (tagId) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE,
0111                                                FOREIGN KEY (resourceId) REFERENCES ResourceTable(id) ON UPDATE CASCADE ON DELETE CASCADE)
0112                                                COLLATE=utf8_general_ci DEFAULT CHARSET=utf8
0113
0114 CREATE TABLE RelationTypeTable (id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
0115                                 name VARBINARY(255) NOT NULL UNIQUE)
0116                                 COLLATE=utf8_general_ci DEFAULT CHARSET=utf8
0117
0118 INSERT INTO RelationTypeTable (name) VALUES ('GENERIC')
0119
0120 CREATE TABLE RelationTable (leftId BIGINT NOT NULL,
0121                             rightId BIGINT NOT NULL,
0122                             typeId BIGINT DEFAULT 1,
0123                             remoteId VARBINARY(255),
0124                             FOREIGN KEY (leftId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE,
0125                             FOREIGN KEY (rightId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE,
0126                             FOREIGN KEY (typeId) REFERENCES RelationTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT)
0127                             COLLATE=utf8_general_ci DEFAULT CHARSET=utf8
0128
0129 CREATE TABLE PimItemFlagRelation (PimItem_id BIGINT NOT NULL,
0130                                   Flag_id BIGINT NOT NULL,
0131                                   PRIMARY KEY (PimItem_id, Flag_id),
0132                                   FOREIGN KEY (PimItem_id) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE,
0133                                   FOREIGN KEY (Flag_id) REFERENCES FlagTable(id) ON UPDATE CASCADE ON DELETE CASCADE) COLLATE=utf8_general_ci DEFAULT CHARSET=utf8
0134
0135 CREATE TABLE PimItemTagRelation (PimItem_id BIGINT NOT NULL,
0136                                  Tag_id BIGINT NOT NULL,
0137                                  PRIMARY KEY (PimItem_id, Tag_id),
0138                                  FOREIGN KEY (PimItem_id) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE,
0139                                  FOREIGN KEY (Tag_id) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE)
0140                                  COLLATE=utf8_general_ci DEFAULT CHARSET=utf8
0141
0142 CREATE TABLE CollectionMimeTypeRelation (Collection_id BIGINT NOT NULL,
0143                                          MimeType_id BIGINT NOT NULL,
0144                                          PRIMARY KEY (Collection_id, MimeType_id),
0145                                          FOREIGN KEY (Collection_id) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE,
0146                                          FOREIGN KEY (MimeType_id) REFERENCES MimeTypeTable(id) ON UPDATE CASCADE ON DELETE CASCADE) COLLATE=utf8_general_ci DEFAULT CHARSET=utf8
0147
0148 CREATE TABLE CollectionPimItemRelation (Collection_id BIGINT NOT NULL,
0149                                         PimItem_id BIGINT NOT NULL,
0150                                         PRIMARY KEY (Collection_id, PimItem_id),
0151                                         FOREIGN KEY (Collection_id) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE,
0152                                         FOREIGN KEY (PimItem_id) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE) COLLATE=utf8_general_ci DEFAULT CHARSET=utf8
0153
0154 CREATE UNIQUE INDEX CollectionTable_parentAndNameIndex ON CollectionTable (parentId,name)
0155
0156 CREATE INDEX CollectionTable_parentIndex ON CollectionTable (parentId)
0157
0158 CREATE INDEX CollectionTable_resourceIndex ON CollectionTable (resourceId)
0159
0160 CREATE INDEX CollectionTable_enabledIndex ON CollectionTable (enabled)
0161
0162 CREATE INDEX CollectionTable_syncPrefIndex ON CollectionTable (syncPref)
0163
0164 CREATE INDEX CollectionTable_displayPrefIndex ON CollectionTable (displayPref)
0165
0166 CREATE INDEX CollectionTable_indexPrefIndex ON CollectionTable (indexPref)
0167
0168 CREATE INDEX PimItemTable_collectionIndex ON PimItemTable (collectionId)
0169
0170 CREATE INDEX PimItemTable_mimeTypeIndex ON PimItemTable (mimeTypeId)
0171
0172 CREATE INDEX PimItemTable_gidIndex ON PimItemTable (gid)
0173
0174 CREATE INDEX PimItemTable_ridIndex ON PimItemTable (remoteId)
0175
0176 CREATE INDEX PimItemTable_idSortIndex ON PimItemTable (id DESC)
0177
0178 CREATE UNIQUE INDEX PartTypeTable_partTypeNameIndex ON PartTypeTable (ns,name)
0179
0180 CREATE UNIQUE INDEX PartTable_pimItemIdTypeIndex ON PartTable (pimItemId,partTypeId)
0181
0182 CREATE INDEX PartTable_pimItemIdSortIndex ON PartTable (pimItemId DESC)
0183
0184 CREATE INDEX PartTable_partTypeIndex ON PartTable (partTypeId)
0185
0186 CREATE INDEX CollectionAttributeTable_collectionIndex ON CollectionAttributeTable (collectionId)
0187
0188 CREATE INDEX TagTable_parentIndex ON TagTable (parentId)
0189
0190 CREATE INDEX TagTable_typeIndex ON TagTable (typeId)
0191
0192 CREATE INDEX TagAttributeTable_tagIndex ON TagAttributeTable (tagId)
0193
0194 CREATE UNIQUE INDEX TagRemoteIdResourceRelationTable_TagAndResourceIndex ON TagRemoteIdResourceRelationTable (tagId,resourceId)
0195
0196 CREATE INDEX TagRemoteIdResourceRelationTable_tagIndex ON TagRemoteIdResourceRelationTable (tagId)
0197
0198 CREATE INDEX TagRemoteIdResourceRelationTable_resourceIndex ON TagRemoteIdResourceRelationTable (resourceId)
0199
0200 CREATE UNIQUE INDEX RelationTable_RelationIndex ON RelationTable (leftId,rightId,typeId)
0201
0202 CREATE INDEX RelationTable_leftIndex ON RelationTable (leftId)
0203
0204 CREATE INDEX RelationTable_rightIndex ON RelationTable (rightId)
0205
0206 CREATE INDEX RelationTable_typeIndex ON RelationTable (typeId)
0207
0208 CREATE INDEX PimItemFlagRelation_PimItem_idIndex ON PimItemFlagRelation (PimItem_id)
0209
0210 CREATE INDEX PimItemFlagRelation_Flag_idIndex ON PimItemFlagRelation (Flag_id)
0211
0212 CREATE INDEX PimItemFlagRelation_pimItemIdSortIndex ON PimItemFlagRelation (pimitem_id DESC)
0213
0214 CREATE INDEX PimItemTagRelation_PimItem_idIndex ON PimItemTagRelation (PimItem_id)
0215
0216 CREATE INDEX PimItemTagRelation_Tag_idIndex ON PimItemTagRelation (Tag_id)
0217
0218 CREATE INDEX CollectionMimeTypeRelation_Collection_idIndex ON CollectionMimeTypeRelation (Collection_id)
0219
0220 CREATE INDEX CollectionMimeTypeRelation_MimeType_idIndex ON CollectionMimeTypeRelation (MimeType_id)
0221
0222 CREATE INDEX CollectionPimItemRelation_Collection_idIndex ON CollectionPimItemRelation (Collection_id)
0223
0224 CREATE INDEX CollectionPimItemRelation_PimItem_idIndex ON CollectionPimItemRelation (PimItem_id)
0225
0226 ALTER TABLE CollectionTable ADD FOREIGN KEY (parentId) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE
0227
0228 ALTER TABLE CollectionTable ADD FOREIGN KEY (resourceId) REFERENCES ResourceTable(id) ON UPDATE CASCADE ON DELETE CASCADE
0229
0230 ALTER TABLE PimItemTable ADD FOREIGN KEY (collectionId) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE
0231
0232 ALTER TABLE PimItemTable ADD FOREIGN KEY (mimeTypeId) REFERENCES MimeTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT
0233
0234 ALTER TABLE PartTable ADD FOREIGN KEY (pimItemId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE
0235
0236 ALTER TABLE PartTable ADD FOREIGN KEY (partTypeId) REFERENCES PartTypeTable(id) ON UPDATE CASCADE ON DELETE CASCADE
0237
0238 ALTER TABLE CollectionAttributeTable ADD FOREIGN KEY (collectionId) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE
0239
0240 ALTER TABLE TagTable ADD FOREIGN KEY (parentId) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE
0241
0242 ALTER TABLE TagTable ADD FOREIGN KEY (typeId) REFERENCES TagTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT
0243
0244 ALTER TABLE TagAttributeTable ADD FOREIGN KEY (tagId) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE
0245
0246 ALTER TABLE TagRemoteIdResourceRelationTable ADD FOREIGN KEY (tagId) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE
0247
0248 ALTER TABLE TagRemoteIdResourceRelationTable ADD FOREIGN KEY (resourceId) REFERENCES ResourceTable(id) ON UPDATE CASCADE ON DELETE CASCADE
0249
0250 ALTER TABLE RelationTable ADD FOREIGN KEY (leftId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE
0251
0252 ALTER TABLE RelationTable ADD FOREIGN KEY (rightId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE
0253
0254 ALTER TABLE RelationTable ADD FOREIGN KEY (typeId) REFERENCES RelationTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT
0255
0256 ALTER TABLE PimItemFlagRelation ADD FOREIGN KEY (PimItem_id) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE
0257
0258 ALTER TABLE PimItemFlagRelation ADD FOREIGN KEY (Flag_id) REFERENCES FlagTable(id) ON UPDATE CASCADE ON DELETE CASCADE
0259
0260 ALTER TABLE PimItemTagRelation ADD FOREIGN KEY (PimItem_id) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE
0261
0262 ALTER TABLE PimItemTagRelation ADD FOREIGN KEY (Tag_id) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE
0263
0264 ALTER TABLE CollectionMimeTypeRelation ADD FOREIGN KEY (Collection_id) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE
0265
0266 ALTER TABLE CollectionMimeTypeRelation ADD FOREIGN KEY (MimeType_id) REFERENCES MimeTypeTable(id) ON UPDATE CASCADE ON DELETE CASCADE
0267
0268 ALTER TABLE CollectionPimItemRelation ADD FOREIGN KEY (Collection_id) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE
0269
0270 ALTER TABLE CollectionPimItemRelation ADD FOREIGN KEY (PimItem_id) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE
0271