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

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