1CREATE TABLE SchemaVersionTable (version INTEGER NOT NULL DEFAULT 0,
2                                 generation INTEGER NOT NULL DEFAULT 0)
3
4INSERT INTO SchemaVersionTable (version) VALUES (36)
5
6CREATE TABLE ResourceTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
7                            name TEXT UNIQUE NOT NULL,
8                            isVirtual BOOL DEFAULT 0)
9
10INSERT INTO ResourceTable (isVirtual,name) VALUES (1,'akonadi_search_resource')
11
12CREATE TABLE CollectionTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
13                              remoteId TEXT,
14                              remoteRevision TEXT,
15                              name TEXT NOT NULL,
16                              parentId BIGINT,
17                              resourceId BIGINT NOT NULL,
18                              enabled BOOL NOT NULL DEFAULT 1,
19                              syncPref TINYINT DEFAULT 2,
20                              displayPref TINYINT DEFAULT 2,
21                              indexPref TINYINT DEFAULT 2,
22                              referenced BOOL NOT NULL DEFAULT 0,
23                              cachePolicyInherit BOOL NOT NULL DEFAULT 1,
24                              cachePolicyCheckInterval INTEGER NOT NULL DEFAULT -1,
25                              cachePolicyCacheTimeout INTEGER NOT NULL DEFAULT -1,
26                              cachePolicySyncOnDemand BOOL NOT NULL DEFAULT 0,
27                              cachePolicyLocalParts TEXT,
28                              queryString TEXT,
29                              queryAttributes TEXT,
30                              queryCollections TEXT,
31                              isVirtual BOOL DEFAULT 0,
32                              CONSTRAINT CollectionTableparentId_Collectionid_fk FOREIGN KEY (parentId) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
33                              CONSTRAINT CollectionTableresourceId_Resourceid_fk FOREIGN KEY (resourceId) REFERENCES ResourceTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED)
34
35INSERT INTO CollectionTable (isVirtual,name,parentId,resourceId) VALUES (1,'Search',NULL,1)
36
37CREATE TABLE MimeTypeTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
38                            name TEXT UNIQUE NOT NULL)
39
40CREATE TABLE PimItemTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
41                           rev INTEGER NOT NULL DEFAULT 0,
42                           remoteId TEXT,
43                           remoteRevision TEXT,
44                           gid TEXT,
45                           collectionId BIGINT,
46                           mimeTypeId BIGINT,
47                           datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
48                           atime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
49                           dirty BOOL,
50                           size BIGINT NOT NULL DEFAULT 0,
51                           CONSTRAINT PimItemTablecollectionId_Collectionid_fk FOREIGN KEY (collectionId) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
52                           CONSTRAINT PimItemTablemimeTypeId_MimeTypeid_fk FOREIGN KEY (mimeTypeId) REFERENCES MimeTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED)
53
54CREATE TABLE FlagTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
55                        name TEXT UNIQUE NOT NULL)
56
57CREATE TABLE PartTypeTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
58                            name TEXT NOT NULL,
59                            ns TEXT NOT NULL)
60
61CREATE TABLE PartTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
62                        pimItemId BIGINT NOT NULL,
63                        partTypeId BIGINT NOT NULL,
64                        data LONGBLOB,
65                        datasize BIGINT NOT NULL,
66                        version INTEGER DEFAULT 0,
67                        storage TINYINT DEFAULT 0,
68                        CONSTRAINT PartTablepimItemId_PimItemid_fk FOREIGN KEY (pimItemId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
69                        CONSTRAINT PartTablepartTypeId_PartTypeid_fk FOREIGN KEY (partTypeId) REFERENCES PartTypeTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED)
70
71
72CREATE TABLE CollectionAttributeTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
73                                       collectionId BIGINT NOT NULL,
74                                       type LONGBLOB NOT NULL,
75                                       value LONGBLOB,
76                                       CONSTRAINT CollectionAttributeTablecollectionId_Collectionid_fk FOREIGN KEY (collectionId) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED)
77
78CREATE TABLE TagTypeTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
79                           name TEXT UNIQUE NOT NULL)
80
81INSERT INTO TagTypeTable (name) VALUES ('PLAIN')
82
83CREATE TABLE TagTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
84                       gid TEXT NOT NULL,
85                       parentId BIGINT,
86                       typeId BIGINT DEFAULT 1,
87                       CONSTRAINT TagTableparentId_Tagid_fk FOREIGN KEY (parentId) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
88                       CONSTRAINT TagTabletypeId_TagTypeid_fk FOREIGN KEY (typeId) REFERENCES TagTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED)
89
90CREATE TABLE TagAttributeTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
91                                tagId BIGINT NOT NULL,
92                                type LONGBLOB NOT NULL,
93                                value LONGBLOB,
94                                CONSTRAINT TagAttributeTabletagId_Tagid_fk FOREIGN KEY (tagId) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED)
95
96CREATE TABLE TagRemoteIdResourceRelationTable (tagId BIGINT NOT NULL,
97                                               resourceId BIGINT NOT NULL,
98                                               remoteId TEXT NOT NULL,
99                                               CONSTRAINT TagRemoteIdResourceRelationTabletagId_Tagid_fk FOREIGN KEY (tagId) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
100                                               CONSTRAINT TagRemoteIdResourceRelationTableresourceId_Resourceid_fk FOREIGN KEY (resourceId) REFERENCES ResourceTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED)
101
102CREATE TABLE RelationTypeTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
103                                name TEXT UNIQUE NOT NULL)
104
105INSERT INTO RelationTypeTable (name) VALUES ('GENERIC')
106
107CREATE TABLE RelationTable (leftId BIGINT NOT NULL,
108                            rightId BIGINT NOT NULL,
109                            typeId BIGINT DEFAULT 1,
110                            remoteId TEXT,
111                            CONSTRAINT RelationTableleftId_PimItemid_fk FOREIGN KEY (leftId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
112                            CONSTRAINT RelationTablerightId_PimItemid_fk FOREIGN KEY (rightId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE
113                            DEFERRABLE INITIALLY DEFERRED,
114                            CONSTRAINT RelationTabletypeId_RelationTypeid_fk FOREIGN KEY (typeId) REFERENCES RelationTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED)
115
116CREATE TABLE PimItemFlagRelation (PimItem_id BIGINT NOT NULL,
117                                  Flag_id BIGINT NOT NULL,
118                                  PRIMARY KEY (PimItem_id, Flag_id),
119                                  CONSTRAINT PimItemFlagRelationPimItem_id_PimItemid_fk FOREIGN KEY (PimItem_id) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
120                                  CONSTRAINT PimItemFlagRelationFlag_id_Flagid_fk FOREIGN KEY (Flag_id) REFERENCES FlagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED)
121
122CREATE TABLE PimItemTagRelation (PimItem_id BIGINT NOT NULL,
123                                 Tag_id BIGINT NOT NULL,
124                                 PRIMARY KEY (PimItem_id, Tag_id),
125                                 CONSTRAINT PimItemTagRelationPimItem_id_PimItemid_fk FOREIGN KEY (PimItem_id) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
126                                 CONSTRAINT PimItemTagRelationTag_id_Tagid_fk FOREIGN KEY (Tag_id) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED)
127
128CREATE TABLE CollectionMimeTypeRelation (Collection_id BIGINT NOT NULL,
129                                         MimeType_id BIGINT NOT NULL,
130                                         PRIMARY KEY (Collection_id, MimeType_id),
131                                         CONSTRAINT CollectionMimeTypeRelationCollection_id_Collectionid_fk FOREIGN KEY (Collection_id) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
132                                         CONSTRAINT CollectionMimeTypeRelationMimeType_id_MimeTypeid_fk FOREIGN KEY (MimeType_id) REFERENCES MimeTypeTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED)
133
134CREATE TABLE CollectionPimItemRelation (Collection_id BIGINT NOT NULL,
135                                        PimItem_id BIGINT NOT NULL,
136                                        PRIMARY KEY (Collection_id, PimItem_id),
137                                        CONSTRAINT CollectionPimItemRelationCollection_id_Collectionid_fk FOREIGN KEY (Collection_id) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
138                                        CONSTRAINT CollectionPimItemRelationPimItem_id_PimItemid_fk FOREIGN KEY (PimItem_id) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED)
139
140CREATE UNIQUE INDEX CollectionTable_parentAndNameIndex ON CollectionTable (parentId,name)
141
142CREATE INDEX CollectionTable_parentIndex ON CollectionTable (parentId)
143
144CREATE INDEX CollectionTable_resourceIndex ON CollectionTable (resourceId)
145
146CREATE INDEX CollectionTable_enabledIndex ON CollectionTable (enabled)
147
148CREATE INDEX CollectionTable_syncPrefIndex ON CollectionTable (syncPref)
149
150CREATE INDEX CollectionTable_displayPrefIndex ON CollectionTable (displayPref)
151
152CREATE INDEX CollectionTable_indexPrefIndex ON CollectionTable (indexPref)
153
154CREATE INDEX PimItemTable_collectionIndex ON PimItemTable (collectionId)
155
156CREATE INDEX PimItemTable_mimeTypeIndex ON PimItemTable (mimeTypeId)
157
158CREATE INDEX PimItemTable_gidIndex ON PimItemTable (gid)
159
160CREATE INDEX PimItemTable_ridIndex ON PimItemTable (remoteId)
161
162CREATE INDEX PimItemTable_idSortIndex ON PimItemTable (id DESC)
163
164CREATE UNIQUE INDEX PartTypeTable_partTypeNameIndex ON PartTypeTable (ns,name)
165
166CREATE UNIQUE INDEX PartTable_pimItemIdTypeIndex ON PartTable (pimItemId,partTypeId)
167
168CREATE INDEX PartTable_pimItemIdSortIndex ON PartTable (pimItemId DESC)
169
170CREATE INDEX PartTable_partTypeIndex ON PartTable (partTypeId)
171
172CREATE INDEX CollectionAttributeTable_collectionIndex ON CollectionAttributeTable (collectionId)
173
174CREATE INDEX TagTable_parentIndex ON TagTable (parentId)
175
176CREATE INDEX TagTable_typeIndex ON TagTable (typeId)
177
178CREATE INDEX TagAttributeTable_tagIndex ON TagAttributeTable (tagId)
179
180CREATE UNIQUE INDEX TagRemoteIdResourceRelationTable_TagAndResourceIndex ON TagRemoteIdResourceRelationTable (tagId,resourceId)
181
182CREATE INDEX TagRemoteIdResourceRelationTable_tagIndex ON TagRemoteIdResourceRelationTable (tagId)
183
184CREATE INDEX TagRemoteIdResourceRelationTable_resourceIndex ON TagRemoteIdResourceRelationTable (resourceId)
185
186CREATE UNIQUE INDEX RelationTable_RelationIndex ON RelationTable (leftId,rightId,typeId)
187
188CREATE INDEX RelationTable_leftIndex ON RelationTable (leftId)
189
190CREATE INDEX RelationTable_rightIndex ON RelationTable (rightId)
191
192CREATE INDEX RelationTable_typeIndex ON RelationTable (typeId)
193
194CREATE INDEX PimItemFlagRelation_PimItem_idIndex ON PimItemFlagRelation (PimItem_id)
195
196CREATE INDEX PimItemFlagRelation_Flag_idIndex ON PimItemFlagRelation (Flag_id)
197
198CREATE INDEX PimItemFlagRelation_pimItemIdSortIndex ON PimItemFlagRelation (pimitem_id DESC)
199
200CREATE INDEX PimItemTagRelation_PimItem_idIndex ON PimItemTagRelation (PimItem_id)
201
202CREATE INDEX PimItemTagRelation_Tag_idIndex ON PimItemTagRelation (Tag_id)
203
204CREATE INDEX CollectionMimeTypeRelation_Collection_idIndex ON CollectionMimeTypeRelation (Collection_id)
205
206CREATE INDEX CollectionMimeTypeRelation_MimeType_idIndex ON CollectionMimeTypeRelation (MimeType_id)
207
208CREATE INDEX CollectionPimItemRelation_Collection_idIndex ON CollectionPimItemRelation (Collection_id)
209
210CREATE INDEX CollectionPimItemRelation_PimItem_idIndex ON CollectionPimItemRelation (PimItem_id)
211
212PRAGMA defer_foreign_keys=ON
213
214BEGIN TRANSACTION
215
216ALTER TABLE CollectionTable RENAME TO CollectionTable_old
217
218CREATE TABLE CollectionTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
219                              remoteId TEXT,
220                              remoteRevision TEXT,
221                              name TEXT NOT NULL,
222                              parentId BIGINT,
223                              resourceId BIGINT NOT NULL,
224                              enabled BOOL NOT NULL DEFAULT 1,
225                              syncPref TINYINT DEFAULT 2,
226                              displayPref TINYINT DEFAULT 2,
227                              indexPref TINYINT DEFAULT 2,
228                              referenced BOOL NOT NULL DEFAULT 0,
229                              cachePolicyInherit BOOL NOT NULL DEFAULT 1,
230                              cachePolicyCheckInterval INTEGER NOT NULL DEFAULT -1,
231                              cachePolicyCacheTimeout INTEGER NOT NULL DEFAULT -1,
232                              cachePolicySyncOnDemand BOOL NOT NULL DEFAULT 0,
233                              cachePolicyLocalParts TEXT,
234                              queryString TEXT,
235                              queryAttributes TEXT,
236                              queryCollections TEXT,
237                              isVirtual BOOL DEFAULT 0,
238                              CONSTRAINT CollectionTableparentId_Collectionid_fk FOREIGN KEY (parentId) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
239                              CONSTRAINT CollectionTableresourceId_Resourceid_fk FOREIGN KEY (resourceId) REFERENCES ResourceTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED)
240
241INSERT INTO CollectionTable SELECT * FROM CollectionTable_old
242
243DROP TABLE CollectionTable_old
244
245COMMIT
246
247PRAGMA defer_foreign_keys=OFF
248
249PRAGMA defer_foreign_keys=ON
250
251BEGIN TRANSACTION
252
253ALTER TABLE CollectionTable RENAME TO CollectionTable_old
254
255CREATE TABLE CollectionTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
256                              remoteId TEXT,
257                              remoteRevision TEXT,
258                              name TEXT NOT NULL,
259                              parentId BIGINT,
260                              resourceId BIGINT NOT NULL,
261                              enabled BOOL NOT NULL DEFAULT 1,
262                              syncPref TINYINT DEFAULT 2,
263                              displayPref TINYINT DEFAULT 2,
264                              indexPref TINYINT DEFAULT 2,
265                              referenced BOOL NOT NULL DEFAULT 0,
266                              cachePolicyInherit BOOL NOT NULL DEFAULT 1,
267                              cachePolicyCheckInterval INTEGER NOT NULL DEFAULT -1,
268                              cachePolicyCacheTimeout INTEGER NOT NULL DEFAULT -1,
269                              cachePolicySyncOnDemand BOOL NOT NULL DEFAULT 0,
270                              cachePolicyLocalParts TEXT,
271                              queryString TEXT,
272                              queryAttributes TEXT,
273                              queryCollections TEXT,
274                              isVirtual BOOL DEFAULT 0,
275                              CONSTRAINT CollectionTableparentId_Collectionid_fk FOREIGN KEY (parentId) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
276                              CONSTRAINT CollectionTableresourceId_Resourceid_fk FOREIGN KEY (resourceId) REFERENCES ResourceTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED)
277
278INSERT INTO CollectionTable SELECT * FROM CollectionTable_old
279
280DROP TABLE CollectionTable_old
281
282COMMIT
283
284PRAGMA defer_foreign_keys=OFF
285
286PRAGMA defer_foreign_keys=ON
287
288BEGIN TRANSACTION
289
290ALTER TABLE PimItemTable RENAME TO PimItemTable_old
291
292CREATE TABLE PimItemTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
293                           rev INTEGER NOT NULL DEFAULT 0,
294                           remoteId TEXT,
295                           remoteRevision TEXT,
296                           gid TEXT,
297                           collectionId BIGINT,
298                           mimeTypeId BIGINT,
299                           datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
300                           atime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
301                           dirty BOOL,
302                           size BIGINT NOT NULL DEFAULT 0,
303                           CONSTRAINT PimItemTablecollectionId_Collectionid_fk FOREIGN KEY (collectionId) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
304                           CONSTRAINT PimItemTablemimeTypeId_MimeTypeid_fk FOREIGN KEY (mimeTypeId) REFERENCES MimeTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED)
305
306INSERT INTO PimItemTable SELECT * FROM PimItemTable_old
307
308DROP TABLE PimItemTable_old
309
310COMMIT
311
312PRAGMA defer_foreign_keys=OFF
313
314PRAGMA defer_foreign_keys=ON
315
316BEGIN TRANSACTION
317
318ALTER TABLE PimItemTable RENAME TO PimItemTable_old
319
320CREATE TABLE PimItemTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
321                           rev INTEGER NOT NULL DEFAULT 0,
322                           remoteId TEXT,
323                           remoteRevision TEXT,
324                           gid TEXT,
325                           collectionId BIGINT,
326                           mimeTypeId BIGINT,
327                           datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
328                           atime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
329                           dirty BOOL,
330                           size BIGINT NOT NULL DEFAULT 0,
331                           CONSTRAINT PimItemTablecollectionId_Collectionid_fk FOREIGN KEY (collectionId) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
332                           CONSTRAINT PimItemTablemimeTypeId_MimeTypeid_fk FOREIGN KEY (mimeTypeId) REFERENCES MimeTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED)
333
334INSERT INTO PimItemTable SELECT * FROM PimItemTable_old
335
336DROP TABLE PimItemTable_old
337
338COMMIT
339
340PRAGMA defer_foreign_keys=OFF
341
342PRAGMA defer_foreign_keys=ON
343
344BEGIN TRANSACTION
345
346ALTER TABLE PartTable RENAME TO PartTable_old
347
348CREATE TABLE PartTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
349                        pimItemId BIGINT NOT NULL,
350                        partTypeId BIGINT NOT NULL,
351                        data LONGBLOB,
352                        datasize BIGINT NOT NULL,
353                        version INTEGER DEFAULT 0,
354                        storage TINYINT DEFAULT 0,
355                        CONSTRAINT PartTablepimItemId_PimItemid_fk FOREIGN KEY (pimItemId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
356                        CONSTRAINT PartTablepartTypeId_PartTypeid_fk FOREIGN KEY (partTypeId) REFERENCES PartTypeTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED)
357
358
359INSERT INTO PartTable SELECT * FROM PartTable_old
360
361DROP TABLE PartTable_old
362
363COMMIT
364
365PRAGMA defer_foreign_keys=OFF
366
367PRAGMA defer_foreign_keys=ON
368
369BEGIN TRANSACTION
370
371ALTER TABLE PartTable RENAME TO PartTable_old
372
373CREATE TABLE PartTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
374                        pimItemId BIGINT NOT NULL,
375                        partTypeId BIGINT NOT NULL,
376                        data LONGBLOB,
377                        datasize BIGINT NOT NULL,
378                        version INTEGER DEFAULT 0,
379                        storage TINYINT DEFAULT 0,
380                        CONSTRAINT PartTablepimItemId_PimItemid_fk FOREIGN KEY (pimItemId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
381                        CONSTRAINT PartTablepartTypeId_PartTypeid_fk FOREIGN KEY (partTypeId) REFERENCES PartTypeTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED)
382
383
384INSERT INTO PartTable SELECT * FROM PartTable_old
385
386DROP TABLE PartTable_old
387
388COMMIT
389
390PRAGMA defer_foreign_keys=OFF
391
392PRAGMA defer_foreign_keys=ON
393
394BEGIN TRANSACTION
395
396ALTER TABLE CollectionAttributeTable RENAME TO CollectionAttributeTable_old
397
398CREATE TABLE CollectionAttributeTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
399                                       collectionId BIGINT NOT NULL,
400                                       type LONGBLOB NOT NULL,
401                                       value LONGBLOB,
402                                       CONSTRAINT CollectionAttributeTablecollectionId_Collectionid_fk FOREIGN KEY (collectionId) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED)
403
404INSERT INTO CollectionAttributeTable SELECT * FROM CollectionAttributeTable_old
405
406DROP TABLE CollectionAttributeTable_old
407
408COMMIT
409
410PRAGMA defer_foreign_keys=OFF
411
412PRAGMA defer_foreign_keys=ON
413
414BEGIN TRANSACTION
415
416ALTER TABLE TagTable RENAME TO TagTable_old
417
418CREATE TABLE TagTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
419                       gid TEXT NOT NULL,
420                       parentId BIGINT,
421                       typeId BIGINT DEFAULT 1,
422                       CONSTRAINT TagTableparentId_Tagid_fk FOREIGN KEY (parentId) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
423                       CONSTRAINT TagTabletypeId_TagTypeid_fk FOREIGN KEY (typeId) REFERENCES TagTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED)
424
425INSERT INTO TagTable SELECT * FROM TagTable_old
426
427DROP TABLE TagTable_old
428
429COMMIT
430
431PRAGMA defer_foreign_keys=OFF
432
433PRAGMA defer_foreign_keys=ON
434
435BEGIN TRANSACTION
436
437ALTER TABLE TagTable RENAME TO TagTable_old
438
439CREATE TABLE TagTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
440                       gid TEXT NOT NULL,
441                       parentId BIGINT,
442                       typeId BIGINT DEFAULT 1,
443                       CONSTRAINT TagTableparentId_Tagid_fk FOREIGN KEY (parentId) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
444                       CONSTRAINT TagTabletypeId_TagTypeid_fk FOREIGN KEY (typeId) REFERENCES TagTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED)
445
446INSERT INTO TagTable SELECT * FROM TagTable_old
447
448DROP TABLE TagTable_old
449
450COMMIT
451
452PRAGMA defer_foreign_keys=OFF
453
454PRAGMA defer_foreign_keys=ON
455
456BEGIN TRANSACTION
457
458ALTER TABLE TagAttributeTable RENAME TO TagAttributeTable_old
459
460
461CREATE TABLE TagAttributeTable (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
462                                tagId BIGINT NOT NULL,
463                                type LONGBLOB NOT NULL,
464                                value LONGBLOB,
465                                CONSTRAINT TagAttributeTabletagId_Tagid_fk FOREIGN KEY (tagId) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED)
466
467INSERT INTO TagAttributeTable SELECT * FROM TagAttributeTable_old
468
469DROP TABLE TagAttributeTable_old
470
471COMMIT
472
473PRAGMA defer_foreign_keys=OFF
474
475PRAGMA defer_foreign_keys=ON
476
477BEGIN TRANSACTION
478
479ALTER TABLE TagRemoteIdResourceRelationTable RENAME TO TagRemoteIdResourceRelationTable_old
480
481CREATE TABLE TagRemoteIdResourceRelationTable (tagId BIGINT NOT NULL,
482                                               resourceId BIGINT NOT NULL,
483                                               remoteId TEXT NOT NULL,
484                                               CONSTRAINT TagRemoteIdResourceRelationTabletagId_Tagid_fk FOREIGN KEY (tagId) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
485                                               CONSTRAINT TagRemoteIdResourceRelationTableresourceId_Resourceid_fk FOREIGN KEY (resourceId) REFERENCES ResourceTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED)
486
487INSERT INTO TagRemoteIdResourceRelationTable SELECT * FROM TagRemoteIdResourceRelationTable_old
488
489DROP TABLE TagRemoteIdResourceRelationTable_old
490
491COMMIT
492
493PRAGMA defer_foreign_keys=OFF
494
495PRAGMA defer_foreign_keys=ON
496
497BEGIN TRANSACTION
498
499ALTER TABLE TagRemoteIdResourceRelationTable RENAME TO TagRemoteIdResourceRelationTable_old
500
501CREATE TABLE TagRemoteIdResourceRelationTable (tagId BIGINT NOT NULL,
502                                               resourceId BIGINT NOT NULL,
503                                               remoteId TEXT NOT NULL,
504                                               CONSTRAINT TagRemoteIdResourceRelationTabletagId_Tagid_fk FOREIGN KEY (tagId) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
505                                               CONSTRAINT TagRemoteIdResourceRelationTableresourceId_Resourceid_fk FOREIGN KEY (resourceId) REFERENCES ResourceTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED)
506
507INSERT INTO TagRemoteIdResourceRelationTable SELECT * FROM TagRemoteIdResourceRelationTable_old
508
509DROP TABLE TagRemoteIdResourceRelationTable_old
510
511COMMIT
512
513PRAGMA defer_foreign_keys=OFF
514
515PRAGMA defer_foreign_keys=ON
516
517BEGIN TRANSACTION
518
519ALTER TABLE RelationTable RENAME TO RelationTable_old
520
521CREATE TABLE RelationTable (leftId BIGINT NOT NULL,
522                            rightId BIGINT NOT NULL,
523                            typeId BIGINT DEFAULT 1,
524                            remoteId TEXT,
525                            CONSTRAINT RelationTableleftId_PimItemid_fk FOREIGN KEY (leftId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
526                            CONSTRAINT RelationTablerightId_PimItemid_fk FOREIGN KEY (rightId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE
527                            DEFERRABLE INITIALLY DEFERRED,
528                            CONSTRAINT RelationTabletypeId_RelationTypeid_fk FOREIGN KEY (typeId) REFERENCES RelationTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED)
529
530
531INSERT INTO RelationTable SELECT * FROM RelationTable_old
532
533DROP TABLE RelationTable_old
534
535COMMIT
536
537PRAGMA defer_foreign_keys=OFF
538
539PRAGMA defer_foreign_keys=ON
540
541BEGIN TRANSACTION
542
543ALTER TABLE RelationTable RENAME TO RelationTable_old
544
545CREATE TABLE RelationTable (leftId BIGINT NOT NULL,
546                            rightId BIGINT NOT NULL,
547                            typeId BIGINT DEFAULT 1,
548                            remoteId TEXT,
549                            CONSTRAINT RelationTableleftId_PimItemid_fk FOREIGN KEY (leftId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
550                            CONSTRAINT RelationTablerightId_PimItemid_fk FOREIGN KEY (rightId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE
551                            DEFERRABLE INITIALLY DEFERRED,
552                            CONSTRAINT RelationTabletypeId_RelationTypeid_fk FOREIGN KEY (typeId) REFERENCES RelationTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED)
553
554
555INSERT INTO RelationTable SELECT * FROM RelationTable_old
556
557DROP TABLE RelationTable_old
558
559COMMIT
560
561PRAGMA defer_foreign_keys=OFF
562
563PRAGMA defer_foreign_keys=ON
564
565BEGIN TRANSACTION
566
567ALTER TABLE RelationTable RENAME TO RelationTable_old
568
569CREATE TABLE RelationTable (leftId BIGINT NOT NULL,
570                            rightId BIGINT NOT NULL,
571                            typeId BIGINT DEFAULT 1,
572                            remoteId TEXT,
573                            CONSTRAINT RelationTableleftId_PimItemid_fk FOREIGN KEY (leftId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
574                            CONSTRAINT RelationTablerightId_PimItemid_fk FOREIGN KEY (rightId) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE
575                            DEFERRABLE INITIALLY DEFERRED,
576                            CONSTRAINT RelationTabletypeId_RelationTypeid_fk FOREIGN KEY (typeId) REFERENCES RelationTypeTable(id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED)
577
578
579INSERT INTO RelationTable SELECT * FROM RelationTable_old
580
581DROP TABLE RelationTable_old
582
583COMMIT
584
585PRAGMA defer_foreign_keys=OFF
586
587PRAGMA defer_foreign_keys=ON
588
589BEGIN TRANSACTION
590
591ALTER TABLE PimItemFlagRelation RENAME TO PimItemFlagRelation_old
592
593CREATE TABLE PimItemFlagRelation (PimItem_id BIGINT NOT NULL,
594                                  Flag_id BIGINT NOT NULL,
595                                  PRIMARY KEY (PimItem_id, Flag_id),
596                                  CONSTRAINT PimItemFlagRelationPimItem_id_PimItemid_fk FOREIGN KEY (PimItem_id) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
597                                  CONSTRAINT PimItemFlagRelationFlag_id_Flagid_fk FOREIGN KEY (Flag_id) REFERENCES FlagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED)
598
599
600INSERT INTO PimItemFlagRelation SELECT * FROM PimItemFlagRelation_old
601
602DROP TABLE PimItemFlagRelation_old
603
604COMMIT
605
606PRAGMA defer_foreign_keys=OFF
607
608
609PRAGMA defer_foreign_keys=ON
610
611BEGIN TRANSACTION
612
613ALTER TABLE PimItemFlagRelation RENAME TO PimItemFlagRelation_old
614
615CREATE TABLE PimItemFlagRelation (PimItem_id BIGINT NOT NULL,
616                                  Flag_id BIGINT NOT NULL,
617                                  PRIMARY KEY (PimItem_id, Flag_id),
618                                  CONSTRAINT PimItemFlagRelationPimItem_id_PimItemid_fk FOREIGN KEY (PimItem_id) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
619                                  CONSTRAINT PimItemFlagRelationFlag_id_Flagid_fk FOREIGN KEY (Flag_id) REFERENCES FlagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED)
620
621
622INSERT INTO PimItemFlagRelation SELECT * FROM PimItemFlagRelation_old
623
624DROP TABLE PimItemFlagRelation_old
625
626COMMIT
627
628PRAGMA defer_foreign_keys=OFF
629
630PRAGMA defer_foreign_keys=ON
631
632BEGIN TRANSACTION
633
634ALTER TABLE PimItemTagRelation RENAME TO PimItemTagRelation_old
635
636CREATE TABLE PimItemTagRelation (PimItem_id BIGINT NOT NULL,
637                                 Tag_id BIGINT NOT NULL,
638                                 PRIMARY KEY (PimItem_id, Tag_id),
639                                 CONSTRAINT PimItemTagRelationPimItem_id_PimItemid_fk FOREIGN KEY (PimItem_id) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
640                                 CONSTRAINT PimItemTagRelationTag_id_Tagid_fk FOREIGN KEY (Tag_id) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED)
641
642INSERT INTO PimItemTagRelation SELECT * FROM PimItemTagRelation_old
643
644DROP TABLE PimItemTagRelation_old
645
646COMMIT
647
648PRAGMA defer_foreign_keys=OFF
649
650PRAGMA defer_foreign_keys=ON
651
652BEGIN TRANSACTION
653
654ALTER TABLE PimItemTagRelation RENAME TO PimItemTagRelation_old
655
656CREATE TABLE PimItemTagRelation (PimItem_id BIGINT NOT NULL,
657                                 Tag_id BIGINT NOT NULL,
658                                 PRIMARY KEY (PimItem_id, Tag_id),
659                                 CONSTRAINT PimItemTagRelationPimItem_id_PimItemid_fk FOREIGN KEY (PimItem_id) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
660                                 CONSTRAINT PimItemTagRelationTag_id_Tagid_fk FOREIGN KEY (Tag_id) REFERENCES TagTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED)
661
662INSERT INTO PimItemTagRelation SELECT * FROM PimItemTagRelation_old
663
664DROP TABLE PimItemTagRelation_old
665
666COMMIT
667
668PRAGMA defer_foreign_keys=OFF
669
670PRAGMA defer_foreign_keys=ON
671
672BEGIN TRANSACTION
673
674ALTER TABLE CollectionMimeTypeRelation RENAME TO CollectionMimeTypeRelation_old
675
676CREATE TABLE CollectionMimeTypeRelation (Collection_id BIGINT NOT NULL,
677                                         MimeType_id BIGINT NOT NULL,
678                                         PRIMARY KEY (Collection_id, MimeType_id),
679                                         CONSTRAINT CollectionMimeTypeRelationCollection_id_Collectionid_fk FOREIGN KEY (Collection_id) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
680                                         CONSTRAINT CollectionMimeTypeRelationMimeType_id_MimeTypeid_fk FOREIGN KEY (MimeType_id) REFERENCES MimeTypeTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED)
681
682INSERT INTO CollectionMimeTypeRelation SELECT * FROM CollectionMimeTypeRelation_old
683
684DROP TABLE CollectionMimeTypeRelation_old
685
686COMMIT
687
688PRAGMA defer_foreign_keys=OFF
689
690PRAGMA defer_foreign_keys=ON
691
692BEGIN TRANSACTION
693
694ALTER TABLE CollectionMimeTypeRelation RENAME TO CollectionMimeTypeRelation_old
695
696CREATE TABLE CollectionMimeTypeRelation (Collection_id BIGINT NOT NULL,
697                                         MimeType_id BIGINT NOT NULL,
698                                         PRIMARY KEY (Collection_id, MimeType_id),
699                                         CONSTRAINT CollectionMimeTypeRelationCollection_id_Collectionid_fk FOREIGN KEY (Collection_id) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
700                                         CONSTRAINT CollectionMimeTypeRelationMimeType_id_MimeTypeid_fk FOREIGN KEY (MimeType_id) REFERENCES MimeTypeTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED)
701
702INSERT INTO CollectionMimeTypeRelation SELECT * FROM CollectionMimeTypeRelation_old
703
704DROP TABLE CollectionMimeTypeRelation_old
705
706COMMIT
707
708PRAGMA defer_foreign_keys=OFF
709
710PRAGMA defer_foreign_keys=ON
711
712BEGIN TRANSACTION
713
714ALTER TABLE CollectionPimItemRelation RENAME TO CollectionPimItemRelation_old
715
716CREATE TABLE CollectionPimItemRelation (Collection_id BIGINT NOT NULL,
717                                        PimItem_id BIGINT NOT NULL,
718                                        PRIMARY KEY (Collection_id, PimItem_id),
719                                        CONSTRAINT CollectionPimItemRelationCollection_id_Collectionid_fk FOREIGN KEY (Collection_id) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
720                                        CONSTRAINT CollectionPimItemRelationPimItem_id_PimItemid_fk FOREIGN KEY (PimItem_id) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED)
721
722INSERT INTO CollectionPimItemRelation SELECT * FROM CollectionPimItemRelation_old
723
724DROP TABLE CollectionPimItemRelation_old
725
726COMMIT
727
728PRAGMA defer_foreign_keys=OFF
729
730PRAGMA defer_foreign_keys=ON
731
732BEGIN TRANSACTION
733
734ALTER TABLE CollectionPimItemRelation RENAME TO CollectionPimItemRelation_old
735
736CREATE TABLE CollectionPimItemRelation (Collection_id BIGINT NOT NULL,
737                                        PimItem_id BIGINT NOT NULL,
738                                        PRIMARY KEY (Collection_id, PimItem_id),
739                                        CONSTRAINT CollectionPimItemRelationCollection_id_Collectionid_fk FOREIGN KEY (Collection_id) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
740                                        CONSTRAINT CollectionPimItemRelationPimItem_id_PimItemid_fk FOREIGN KEY (PimItem_id) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED)
741
742INSERT INTO CollectionPimItemRelation SELECT * FROM CollectionPimItemRelation_old
743
744DROP TABLE CollectionPimItemRelation_old
745
746COMMIT
747
748PRAGMA defer_foreign_keys=OFF
749