← Back to branch summary

~ubuntu-branches/ubuntu/saucy/digikam/saucy

« back to all changes in this revision

Viewing changes to libs/database/schemaupdater.cpp

  • Committer: Bazaar Package Importer
  • Author(s): Scott Kitterman
  • Date: 2010-12-21 23:19:11 UTC
  • mfrom: (1.2.33 upstream) (3.1.7 experimental)
  • Revision ID: james.westby@ubuntu.com-20101221231911-z9jip7s5aht1jqn9
Tags: 2:1.7.0-1ubuntu1
* Merge from Debian Experimental. Remaining Ubuntu changes:
  - Export .pot name and copy to plugins in debian/rules
  - Version build-depends on kipi-plugins-dev to ensure build is against the
    same version on all archs
* Drop debian/patches/kubuntu_01_linker.diff, incoporated upstream
* Remove patches directory and unused patches

Show diffs side-by-side

added added

removed removed

Lines of Context:
libs/database/schemaupdater.cpp
72
72
    return m_LastErrorMessage;
73
73
}
74
74
 
75
 
void SchemaUpdater::setDatabaseAccess(DatabaseAccess *access)
 
75
void SchemaUpdater::setDatabaseAccess(DatabaseAccess* access)
76
76
{
77
77
    m_access=access;
78
78
}
79
79
 
80
 
SchemaUpdater::SchemaUpdater(AlbumDB *albumDB, DatabaseBackend *backend, DatabaseParameters parameters)
 
80
SchemaUpdater::SchemaUpdater(AlbumDB* albumDB, DatabaseBackend* backend, DatabaseParameters parameters)
81
81
{
82
82
    m_Backend         = backend;
83
83
    m_AlbumDB         = albumDB;
91
91
{
92
92
    kDebug() << "SchemaUpdater update";
93
93
    bool success = startUpdates();
 
94
 
94
95
    // cancelled?
95
96
    if (m_observer && !m_observer->continueQuery())
 
97
    {
96
98
        return false;
 
99
    }
 
100
 
97
101
    // even on failure, try to set current version - it may have incremented
98
102
    m_AlbumDB->setSetting("DBVersion",QString::number(m_currentVersion));
 
103
 
99
104
    if (!success)
 
105
    {
100
106
        return false;
 
107
    }
 
108
 
101
109
    updateFilterSettings();
102
110
 
103
111
    if (m_observer)
 
112
    {
104
113
        m_observer->finishedSchemaUpdate(InitializationObserver::UpdateSuccess);
 
114
    }
105
115
 
106
116
    return success;
107
117
}
108
118
 
109
 
void SchemaUpdater::setObserver(InitializationObserver *observer)
 
119
void SchemaUpdater::setObserver(InitializationObserver* observer)
110
120
{
111
121
    m_observer = observer;
112
122
}
129
139
            // Something is damaged. Give up.
130
140
            kError() << "DBVersion not available! Giving up schema upgrading.";
131
141
            QString errorMsg = i18n(
132
 
                    "The database is not valid: "
133
 
                    "the \"DBVersion\" setting does not exist. "
134
 
                    "The current database schema version cannot be verified. "
135
 
                    "Try to start with an empty database. "
136
 
                                   );
 
142
                                   "The database is not valid: "
 
143
                                   "the \"DBVersion\" setting does not exist. "
 
144
                                   "The current database schema version cannot be verified. "
 
145
                                   "Try to start with an empty database. "
 
146
                               );
137
147
            m_LastErrorMessage=errorMsg;
 
148
 
138
149
            if (m_observer)
139
150
            {
140
151
                m_observer->error(errorMsg);
141
152
                m_observer->finishedSchemaUpdate(InitializationObserver::UpdateErrorMustAbort);
142
153
            }
 
154
 
143
155
            return false;
144
156
        }
145
157
 
158
170
            else
159
171
            {
160
172
                QString errorMsg = i18n(
161
 
                            "The database has been used with a more recent version of digiKam "
162
 
                            "and has been updated to a database schema which cannot be used with this version. "
163
 
                            "(This means this digiKam version is too old, or the database format is to recent) "
164
 
                            "Please use the more recent version of digikam that you used before. "
165
 
                                       );
 
173
                                       "The database has been used with a more recent version of digiKam "
 
174
                                       "and has been updated to a database schema which cannot be used with this version. "
 
175
                                       "(This means this digiKam version is too old, or the database format is to recent) "
 
176
                                       "Please use the more recent version of digikam that you used before. "
 
177
                                   );
166
178
                m_LastErrorMessage=errorMsg;
 
179
 
167
180
                if (m_observer)
168
181
                {
169
182
                    m_observer->error(errorMsg);
170
183
                    m_observer->finishedSchemaUpdate(InitializationObserver::UpdateErrorMustAbort);
171
184
                }
 
185
 
172
186
                return false;
173
187
            }
174
188
        }
175
189
        else
 
190
        {
176
191
            return makeUpdates();
 
192
        }
177
193
    }
178
194
    else
179
195
    {
202
218
            if (digikam3DB.exists())
203
219
            {
204
220
                if (!copyV3toV4(digikam3DB.filePath(), currentDBFile.filePath()))
 
221
                {
205
222
                    return false;
 
223
                }
206
224
 
207
225
                // m_currentVersion is now 4;
208
226
                return makeUpdates();
210
228
            else if (digikamDB.exists())
211
229
            {
212
230
                if (!updateV2toV4(digikamDB.path()))
 
231
                {
213
232
                    return false;
 
233
                }
214
234
 
215
235
                // m_currentVersion is now 4;
216
236
                return makeUpdates();
217
237
            }
 
238
 
218
239
            // no else, fall through!
219
240
        }
220
241
 
222
243
        if (!createDatabase() || !createFilterSettings())
223
244
        {
224
245
            QString errorMsg = i18n("Failed to create tables in database.\n ")
225
 
                                    + m_Backend->lastError();
 
246
                               + m_Backend->lastError();
226
247
            m_LastErrorMessage=errorMsg;
 
248
 
227
249
            if (m_observer)
228
250
            {
229
251
                m_observer->error(errorMsg);
230
252
                m_observer->finishedSchemaUpdate(InitializationObserver::UpdateErrorMustAbort);
231
253
            }
 
254
 
232
255
            return false;
233
256
        }
 
257
 
234
258
        return true;
235
259
    }
236
260
}
238
262
bool SchemaUpdater::makeUpdates()
239
263
{
240
264
    kDebug() << "makeUpdates " << m_currentVersion << " to " << schemaVersion();
 
265
 
241
266
    //DatabaseTransaction transaction(m_access);
242
267
    if (m_currentVersion < schemaVersion())
243
268
    {
255
280
                m_observer->error(errorMsg);
256
281
                m_observer->finishedSchemaUpdate(InitializationObserver::UpdateErrorMustAbort);
257
282
            }
 
283
 
258
284
            if (!updateV4toV5())
259
285
            {
260
286
                m_Backend->rollbackTransaction();
 
287
 
261
288
                if (m_observer)
262
289
                {
263
290
                    // error or cancelled?
280
307
                        m_observer->finishedSchemaUpdate(InitializationObserver::UpdateErrorMustAbort);
281
308
                    }
282
309
                }
 
310
 
283
311
                return false;
284
312
            }
 
313
 
285
314
            kDebug() << "Success updating to v5";
286
315
            m_Backend->commitTransaction();
287
316
            // REMOVE BEFORE FINAL VERSION
294
323
            m_AlbumDB->setSetting("beta010Update2", "true");
295
324
            // END REMOVE
296
325
        }
 
326
 
297
327
        // add future updates here
298
328
    }
299
329
    else
308
338
        beta010Update2();
309
339
        // END REMOVE
310
340
    }
 
341
 
311
342
    return true;
312
343
}
313
344
 
314
345
void SchemaUpdater::defaultFilterSettings(QStringList& defaultImageFilter,
315
 
                                          QStringList &defaultVideoFilter,
316
 
                                          QStringList &defaultAudioFilter)
 
346
        QStringList& defaultVideoFilter,
 
347
        QStringList& defaultAudioFilter)
317
348
{
318
349
    //NOTE for updating:
319
350
    //When changing anything here, just increment filterSettingsVersion() so that the changes take effect
351
382
    QString dcrawFilterVersion = m_AlbumDB->getSetting("DcrawFilterSettingsVersion");
352
383
 
353
384
    if (
354
 
         filterVersion.toInt() < filterSettingsVersion() ||
355
 
         dcrawFilterVersion.toInt() < KDcrawIface::KDcraw::rawFilesVersion()
356
 
       )
 
385
        filterVersion.toInt() < filterSettingsVersion() ||
 
386
        dcrawFilterVersion.toInt() < KDcrawIface::KDcraw::rawFilesVersion()
 
387
    )
357
388
    {
358
389
        createFilterSettings();
359
390
    }
 
391
 
360
392
    return true;
361
393
}
362
394
 
379
411
        return true;
380
412
    }
381
413
    else
 
414
    {
382
415
        return false;
 
416
    }
383
417
}
384
418
 
385
419
bool SchemaUpdater::createTablesV5()
388
422
    {
389
423
        return false;
390
424
    }
 
425
 
391
426
    return true;
392
427
}
393
428
 
411
446
bool SchemaUpdater::copyV3toV4(const QString& digikam3DBPath, const QString& currentDBPath)
412
447
{
413
448
    if (m_observer)
 
449
    {
414
450
        m_observer->moreSchemaUpdateSteps(2);
 
451
    }
415
452
 
416
453
    m_Backend->close();
417
454
 
420
457
    QFile newFile(currentDBPath);
421
458
    // QFile won't override. Remove the empty db file created when a non-existent file is opened
422
459
    newFile.remove();
 
460
 
423
461
    if (!oldFile.copy(currentDBPath))
424
462
    {
425
463
        QString errorMsg = i18n("Failed to copy the old database file (\"%1\") "
429
467
                                "or delete it.",
430
468
                                digikam3DBPath, currentDBPath, oldFile.errorString());
431
469
        m_LastErrorMessage=errorMsg;
 
470
 
432
471
        if (m_observer)
433
472
        {
434
473
            m_observer->error(errorMsg);
435
474
            m_observer->finishedSchemaUpdate(InitializationObserver::UpdateErrorMustAbort);
436
475
        }
 
476
 
437
477
        return false;
438
478
    }
 
479
 
439
480
    if (m_observer)
 
481
    {
440
482
        m_observer->schemaUpdateProgress(i18n("Copied database file"));
 
483
    }
441
484
 
442
485
    if (!m_Backend->open(m_Parameters))
443
486
    {
448
491
                                digikam3DBPath, currentDBPath);
449
492
 
450
493
        m_LastErrorMessage=errorMsg;
 
494
 
451
495
        if (m_observer)
452
496
        {
453
497
            m_observer->error(errorMsg);
454
498
            m_observer->finishedSchemaUpdate(InitializationObserver::UpdateErrorMustAbort);
455
499
        }
 
500
 
456
501
        return false;
457
502
    }
 
503
 
458
504
    if (m_observer)
 
505
    {
459
506
        m_observer->schemaUpdateProgress(i18n("Opened new database file"));
 
507
    }
460
508
 
461
509
    m_currentVersion = 4;
462
510
    return true;
465
513
bool SchemaUpdater::updateV2toV4(const QString& sqlite2DBPath)
466
514
{
467
515
    if (m_observer)
 
516
    {
468
517
        m_observer->moreSchemaUpdateSteps(1);
 
518
    }
469
519
 
470
520
    if (upgradeDB_Sqlite2ToSqlite3(m_AlbumDB, m_Backend, sqlite2DBPath))
471
521
    {
478
528
                                "Please delete this file and try again, "
479
529
                                "starting with an empty database. ", sqlite2DBPath);
480
530
        m_LastErrorMessage=errorMsg;
 
531
 
481
532
        if (m_observer)
482
533
        {
483
534
            m_observer->error(errorMsg);
484
535
            m_observer->finishedSchemaUpdate(InitializationObserver::UpdateErrorMustAbort);
485
536
        }
 
537
 
486
538
        return false;
487
539
    }
 
540
 
488
541
    if (m_observer)
 
542
    {
489
543
        m_observer->schemaUpdateProgress(i18n("Updated from 0.7 database"));
 
544
    }
490
545
}
491
546
 
492
 
static QStringList cleanUserFilterString(const QString &filterString)
 
547
static QStringList cleanUserFilterString(const QString& filterString)
493
548
{
494
549
    // splits by either ; or space, removes "*.", trims
495
550
    QStringList filterList;
499
554
 
500
555
    QChar sep(';');
501
556
    int i = filterString.indexOf( sep );
 
557
 
502
558
    if ( i == -1 && filterString.indexOf(' ') != -1 )
 
559
    {
503
560
        sep = QChar(' ');
 
561
    }
504
562
 
505
563
    QStringList sepList = filterString.split(sep, QString::SkipEmptyParts);
506
564
    foreach (const QString& f, sepList)
507
565
    {
508
566
        if (f.startsWith(wildcard))
 
567
        {
509
568
            filterList << f.mid(2).trimmed().toLower();
 
569
        }
510
570
        else
 
571
        {
511
572
            filterList << f.trimmed().toLower();
 
573
        }
512
574
    }
513
575
    return filterList;
514
576
}
516
578
bool SchemaUpdater::updateV4toV5()
517
579
{
518
580
    kDebug() << "updateV4toV5";
 
581
 
519
582
    if (m_observer)
520
583
    {
521
584
        if (!m_observer->continueQuery())
 
585
        {
522
586
            return false;
 
587
        }
 
588
 
523
589
        m_observer->moreSchemaUpdateSteps(11);
524
590
    }
525
591
 
528
594
 
529
595
    // --- Make space for new tables ---
530
596
    if (!m_Backend->execSql(QString("ALTER TABLE Albums RENAME TO AlbumsV3;")))
 
597
    {
531
598
        return false;
 
599
    }
532
600
 
533
601
    if (!m_Backend->execSql(QString("ALTER TABLE Images RENAME TO ImagesV3;")))
 
602
    {
534
603
        return false;
 
604
    }
535
605
 
536
606
    if (!m_Backend->execSql(QString("ALTER TABLE Searches RENAME TO SearchesV3;")))
 
607
    {
537
608
        return false;
 
609
    }
538
610
 
539
611
    kDebug() << "Moved tables";
540
612
    // --- Drop some triggers and indices ---
552
624
    if (m_observer)
553
625
    {
554
626
        if (!m_observer->continueQuery())
 
627
        {
555
628
            return false;
 
629
        }
 
630
 
556
631
        m_observer->schemaUpdateProgress(i18n("Prepared table creation"));
557
632
    }
 
633
 
558
634
    kDebug() << "Dropped triggers";
559
635
 
560
636
    // --- Create new tables ---
561
637
 
562
638
    if (!createTablesV5() || !createIndicesV5())
 
639
    {
563
640
        return false;
 
641
    }
564
642
 
565
643
    if (m_observer)
566
644
    {
567
645
        if (!m_observer->continueQuery())
 
646
        {
568
647
            return false;
 
648
        }
 
649
 
569
650
        m_observer->schemaUpdateProgress(i18n("Created tables"));
570
651
    }
571
652
 
584
665
                                "Please try with an empty database, or repair your configuration.");
585
666
        m_LastErrorMessage=errorMsg;
586
667
        m_setError = true;
 
668
 
587
669
        if (m_observer)
588
670
        {
589
671
            m_observer->error(errorMsg);
590
672
            m_observer->finishedSchemaUpdate(InitializationObserver::UpdateErrorMustAbort);
591
673
        }
 
674
 
592
675
        return false;
593
676
    }
594
677
 
595
678
    CollectionLocation location =
596
 
            CollectionManager::instance()->addLocation(KUrl::fromPath(albumLibraryPath));
 
679
        CollectionManager::instance()->addLocation(KUrl::fromPath(albumLibraryPath));
 
680
 
597
681
    if (location.isNull())
598
682
    {
599
683
        kError() << "Failure to create a collection location. Aborting update.";
607
691
                                albumLibraryPath);
608
692
        m_LastErrorMessage=errorMsg;
609
693
        m_setError = true;
 
694
 
610
695
        if (m_observer)
611
696
        {
612
697
            m_observer->error(errorMsg);
613
698
            m_observer->finishedSchemaUpdate(InitializationObserver::UpdateErrorMustAbort);
614
699
        }
 
700
 
615
701
        return false;
616
702
    }
617
703
 
618
704
    if (m_observer)
619
705
    {
620
706
        if (!m_observer->continueQuery())
 
707
        {
621
708
            return false;
 
709
        }
 
710
 
622
711
        m_observer->schemaUpdateProgress(i18n("Configured one album root"));
623
712
    }
 
713
 
624
714
    kDebug() << "Inserted album root";
625
715
 
626
716
    // --- With the album root, populate albums ---
627
717
 
628
718
    if (!m_Backend->execSql(QString(
629
 
                    "REPLACE INTO Albums "
630
 
                    " (id, albumRoot, relativePath, date, caption, collection, icon) "
631
 
                    "SELECT id, ?, url, date, caption, collection, icon "
632
 
                    " FROM AlbumsV3;"
633
 
                                             ),
634
 
                    location.id())
 
719
                                "REPLACE INTO Albums "
 
720
                                " (id, albumRoot, relativePath, date, caption, collection, icon) "
 
721
                                "SELECT id, ?, url, date, caption, collection, icon "
 
722
                                " FROM AlbumsV3;"
 
723
                            ),
 
724
                            location.id())
635
725
       )
 
726
    {
636
727
        return false;
 
728
    }
637
729
 
638
730
    if (m_observer)
639
731
    {
640
732
        if (!m_observer->continueQuery())
 
733
        {
641
734
            return false;
 
735
        }
 
736
 
642
737
        m_observer->schemaUpdateProgress(i18n("Imported albums"));
643
738
    }
 
739
 
644
740
    kDebug() << "Populated albums";
645
741
 
646
742
    // --- Add images ---
647
743
 
648
744
    if (!m_Backend->execSql(QString(
649
 
                    "REPLACE INTO Images "
650
 
                    " (id, album, name, status, category, modificationDate, fileSize, uniqueHash) "
651
 
                    "SELECT id, dirid, name, ?, ?, NULL, NULL, NULL"
652
 
                    " FROM ImagesV3;"
653
 
                                             ),
654
 
                    DatabaseItem::Visible, DatabaseItem::UndefinedCategory)
 
745
                                "REPLACE INTO Images "
 
746
                                " (id, album, name, status, category, modificationDate, fileSize, uniqueHash) "
 
747
                                "SELECT id, dirid, name, ?, ?, NULL, NULL, NULL"
 
748
                                " FROM ImagesV3;"
 
749
                            ),
 
750
                            DatabaseItem::Visible, DatabaseItem::UndefinedCategory)
655
751
       )
656
 
         return false;
 
752
    {
 
753
        return false;
 
754
    }
657
755
 
658
756
    if (!m_access->backend()->execSql(QString(
659
 
                    "REPLACE INTO ImageInformation (imageId) SELECT id FROM Images;"))
 
757
                                          "REPLACE INTO ImageInformation (imageId) SELECT id FROM Images;"))
660
758
       )
661
 
         return false;
 
759
    {
 
760
        return false;
 
761
    }
662
762
 
663
763
    // remove orphan images that would not be removed by CollectionScanner
664
764
    m_Backend->execSql(QString("DELETE FROM Images WHERE album NOT IN (SELECT id FROM Albums);"));
666
766
    if (m_observer)
667
767
    {
668
768
        if (!m_observer->continueQuery())
 
769
        {
669
770
            return false;
 
771
        }
 
772
 
670
773
        m_observer->schemaUpdateProgress(i18n("Imported images information"));
671
774
    }
672
775
 
675
778
    // --- Port searches ---
676
779
 
677
780
    if (!m_Backend->execSql(QString(
678
 
                    "REPLACE INTO Searches "
679
 
                    " (id, type, name, query) "
680
 
                    "SELECT id, ?, name, url"
681
 
                    " FROM SearchesV3;"),
682
 
                    DatabaseSearch::LegacyUrlSearch)
 
781
                                "REPLACE INTO Searches "
 
782
                                " (id, type, name, query) "
 
783
                                "SELECT id, ?, name, url"
 
784
                                " FROM SearchesV3;"),
 
785
                            DatabaseSearch::LegacyUrlSearch)
683
786
       )
684
 
         return false;
 
787
    {
 
788
        return false;
 
789
    }
685
790
 
686
791
    SearchInfo::List sList = m_AlbumDB->scanSearches();
687
792
 
693
798
        QString query = builder.convertFromUrlToXml(url);
694
799
 
695
800
        QString name = (*it).name;
 
801
 
696
802
        if (name == i18n("Last Search"))
 
803
        {
697
804
            name = i18n("Last Search (0.9)");
 
805
        }
698
806
 
699
807
        if (url.queryItem("type") == QString("datesearch"))
700
808
        {
713
821
    // --- Create triggers ---
714
822
 
715
823
    if (!createTriggersV5())
 
824
    {
716
825
        return false;
 
826
    }
 
827
 
717
828
    kDebug() << "Created triggers";
718
829
 
719
830
    // --- Populate name filters ---
743
854
    if (m_observer)
744
855
    {
745
856
        if (!m_observer->continueQuery())
 
857
        {
746
858
            return false;
 
859
        }
 
860
 
747
861
        m_observer->schemaUpdateProgress(i18n("Initialized and imported file suffix filter"));
748
862
    }
749
863
 
750
864
    // --- do a full scan ---
751
865
 
752
866
    CollectionScanner scanner;
 
867
 
753
868
    if (m_observer)
754
869
    {
755
870
        m_observer->connectCollectionScanner(&scanner);
756
871
        scanner.setObserver(m_observer);
757
872
    }
 
873
 
758
874
    scanner.completeScan();
759
875
 
760
876
    if (m_observer)
761
877
    {
762
878
        if (!m_observer->continueQuery())
 
879
        {
763
880
            return false;
 
881
        }
 
882
 
764
883
        m_observer->schemaUpdateProgress(i18n("Did the initial full scan"));
765
884
    }
766
885
 
768
887
 
769
888
    // Port ImagesV3.date -> ImageInformation.creationDate
770
889
    if (!m_Backend->execSql(QString(
771
 
                    "UPDATE ImageInformation SET "
772
 
                    " creationDate=(SELECT datetime FROM ImagesV3 WHERE ImagesV3.id=ImageInformation.imageid) "
773
 
                    "WHERE imageid IN (SELECT id FROM ImagesV3);"
774
 
                                             )
775
 
                                     )
 
890
                                "UPDATE ImageInformation SET "
 
891
                                " creationDate=(SELECT datetime FROM ImagesV3 WHERE ImagesV3.id=ImageInformation.imageid) "
 
892
                                "WHERE imageid IN (SELECT id FROM ImagesV3);"
 
893
                            )
 
894
                           )
776
895
       )
777
 
         return false;
 
896
    {
 
897
        return false;
 
898
    }
778
899
 
779
900
    if (m_observer)
780
901
    {
781
902
        if (!m_observer->continueQuery())
 
903
        {
782
904
            return false;
 
905
        }
 
906
 
783
907
        m_observer->schemaUpdateProgress(i18n("Imported creation dates"));
784
908
    }
785
909
 
787
911
 
788
912
    // An author of NULL will inhibt the UNIQUE restriction to take effect (but #189080). Work around.
789
913
    m_Backend->execSql(QString(
790
 
                    "DELETE FROM ImageComments WHERE "
791
 
                    "type=? AND language=? AND author IS NULL "
792
 
                    "AND imageid IN ( SELECT id FROM ImagesV3 ); "),
793
 
                    (int)DatabaseComment::Comment, QString("x-default"));
 
914
                           "DELETE FROM ImageComments WHERE "
 
915
                           "type=? AND language=? AND author IS NULL "
 
916
                           "AND imageid IN ( SELECT id FROM ImagesV3 ); "),
 
917
                       (int)DatabaseComment::Comment, QString("x-default"));
794
918
 
795
919
    if (!m_Backend->execSql(QString(
796
 
                    "REPLACE INTO ImageComments "
797
 
                    " (imageid, type, language, comment) "
798
 
                    "SELECT id, ?, ?, caption FROM ImagesV3;"
799
 
                                             ),
800
 
                    (int)DatabaseComment::Comment, QString("x-default"))
 
920
                                "REPLACE INTO ImageComments "
 
921
                                " (imageid, type, language, comment) "
 
922
                                "SELECT id, ?, ?, caption FROM ImagesV3;"
 
923
                            ),
 
924
                            (int)DatabaseComment::Comment, QString("x-default"))
801
925
       )
802
 
         return false;
 
926
    {
 
927
        return false;
 
928
    }
803
929
 
804
930
    if (m_observer)
805
931
    {
806
932
        if (!m_observer->continueQuery())
 
933
        {
807
934
            return false;
 
935
        }
 
936
 
808
937
        m_observer->schemaUpdateProgress(i18n("Imported comments"));
809
938
    }
810
939
 
811
940
    // Port rating storage in ImageProperties to ImageInformation
812
941
    if (!m_Backend->execSql(QString(
813
 
                    "UPDATE ImageInformation SET "
814
 
                    " rating=(SELECT value FROM ImageProperties "
815
 
                    "         WHERE ImageInformation.imageid=ImageProperties.imageid AND ImageProperties.property=?) "
816
 
                    "WHERE imageid IN (SELECT imageid FROM ImageProperties WHERE property=?);"
817
 
                                             ),
818
 
                    QString("Rating"), QString("Rating"))
 
942
                                "UPDATE ImageInformation SET "
 
943
                                " rating=(SELECT value FROM ImageProperties "
 
944
                                "         WHERE ImageInformation.imageid=ImageProperties.imageid AND ImageProperties.property=?) "
 
945
                                "WHERE imageid IN (SELECT imageid FROM ImageProperties WHERE property=?);"
 
946
                            ),
 
947
                            QString("Rating"), QString("Rating"))
819
948
       )
820
 
         return false;
 
949
    {
 
950
        return false;
 
951
    }
821
952
 
822
953
    m_Backend->execSql(QString("DELETE FROM ImageProperties WHERE property=?;"), QString("Rating"));
823
954
    m_Backend->execSql(QString("UPDATE ImageInformation SET rating=0 WHERE rating<0;"));
825
956
    if (m_observer)
826
957
    {
827
958
        if (!m_observer->continueQuery())
 
959
        {
828
960
            return false;
 
961
        }
 
962
 
829
963
        m_observer->schemaUpdateProgress(i18n("Imported ratings"));
830
964
    }
831
965
 
836
970
    m_Backend->execSql(QString("DROP TABLE SearchesV3;"));
837
971
 
838
972
    if (m_observer)
 
973
    {
839
974
        m_observer->schemaUpdateProgress(i18n("Dropped v3 tables"));
 
975
    }
840
976
 
841
977
    m_currentVersion = 5;
842
978
    kDebug() << "Returning true from updating to 5";
846
982
void SchemaUpdater::preAlpha010Update1()
847
983
{
848
984
    QString hasUpdate = m_AlbumDB->getSetting("preAlpha010Update1");
 
985
 
849
986
    if (!hasUpdate.isNull())
 
987
    {
850
988
        return;
 
989
    }
851
990
 
852
991
    if (!m_Backend->execSql(QString("ALTER TABLE Searches RENAME TO SearchesV3;")))
 
992
    {
853
993
        return;
 
994
    }
854
995
 
855
996
    if ( !m_Backend->execSql(
856
 
                   QString( "CREATE TABLE IF NOT EXISTS Searches  \n"
857
 
                            " (id INTEGER PRIMARY KEY, \n"
858
 
                            "  type INTEGER, \n"
859
 
                            "  name TEXT NOT NULL, \n"
860
 
                            "  query TEXT NOT NULL);" ) ))
 
997
             QString( "CREATE TABLE IF NOT EXISTS Searches  \n"
 
998
                      " (id INTEGER PRIMARY KEY, \n"
 
999
                      "  type INTEGER, \n"
 
1000
                      "  name TEXT NOT NULL, \n"
 
1001
                      "  query TEXT NOT NULL);" ) ))
 
1002
    {
861
1003
        return;
 
1004
    }
862
1005
 
863
1006
    if (!m_Backend->execSql(QString(
864
 
                    "REPLACE INTO Searches "
865
 
                    " (id, type, name, query) "
866
 
                    "SELECT id, ?, name, url"
867
 
                    " FROM SearchesV3;"),
868
 
                    DatabaseSearch::LegacyUrlSearch)
 
1007
                                "REPLACE INTO Searches "
 
1008
                                " (id, type, name, query) "
 
1009
                                "SELECT id, ?, name, url"
 
1010
                                " FROM SearchesV3;"),
 
1011
                            DatabaseSearch::LegacyUrlSearch)
869
1012
       )
870
 
         return;
 
1013
    {
 
1014
        return;
 
1015
    }
871
1016
 
872
1017
    SearchInfo::List sList = m_AlbumDB->scanSearches();
873
1018
 
900
1045
void SchemaUpdater::preAlpha010Update2()
901
1046
{
902
1047
    QString hasUpdate = m_AlbumDB->getSetting("preAlpha010Update2");
 
1048
 
903
1049
    if (!hasUpdate.isNull())
 
1050
    {
904
1051
        return;
 
1052
    }
905
1053
 
906
1054
    if (!m_Backend->execSql(QString("ALTER TABLE ImagePositions RENAME TO ImagePositionsTemp;")))
 
1055
    {
907
1056
        return;
 
1057
    }
 
1058
 
908
1059
    if (!m_Backend->execSql(QString("ALTER TABLE ImageMetadata RENAME TO ImageMetadataTemp;")))
 
1060
    {
909
1061
        return;
 
1062
    }
910
1063
 
911
1064
    m_Backend->execSql(
912
 
                    QString("CREATE TABLE ImagePositions\n"
913
 
                            " (imageid INTEGER PRIMARY KEY,\n"
914
 
                            "  latitude TEXT,\n"
915
 
                            "  latitudeNumber REAL,\n"
916
 
                            "  longitude TEXT,\n"
917
 
                            "  longitudeNumber REAL,\n"
918
 
                            "  altitude REAL,\n"
919
 
                            "  orientation REAL,\n"
920
 
                            "  tilt REAL,\n"
921
 
                            "  roll REAL,\n"
922
 
                            "  accuracy REAL,\n"
923
 
                            "  description TEXT);") );
 
1065
        QString("CREATE TABLE ImagePositions\n"
 
1066
                " (imageid INTEGER PRIMARY KEY,\n"
 
1067
                "  latitude TEXT,\n"
 
1068
                "  latitudeNumber REAL,\n"
 
1069
                "  longitude TEXT,\n"
 
1070
                "  longitudeNumber REAL,\n"
 
1071
                "  altitude REAL,\n"
 
1072
                "  orientation REAL,\n"
 
1073
                "  tilt REAL,\n"
 
1074
                "  roll REAL,\n"
 
1075
                "  accuracy REAL,\n"
 
1076
                "  description TEXT);") );
924
1077
 
925
1078
    m_Backend->execSql(QString(
926
 
                    "REPLACE INTO ImagePositions "
927
 
                    " (imageid, latitude, latitudeNumber, longitude, longitudeNumber, "
928
 
                    "  altitude, orientation, tilt, roll, accuracy, description) "
929
 
                    "SELECT imageid, latitude, latitudeNumber, longitude, longitudeNumber, "
930
 
                    "  altitude, orientation, tilt, roll, 0, description "
931
 
                    " FROM ImagePositionsTemp;"));
 
1079
                           "REPLACE INTO ImagePositions "
 
1080
                           " (imageid, latitude, latitudeNumber, longitude, longitudeNumber, "
 
1081
                           "  altitude, orientation, tilt, roll, accuracy, description) "
 
1082
                           "SELECT imageid, latitude, latitudeNumber, longitude, longitudeNumber, "
 
1083
                           "  altitude, orientation, tilt, roll, 0, description "
 
1084
                           " FROM ImagePositionsTemp;"));
932
1085
 
933
1086
    m_Backend->execSql(
934
 
                    QString("CREATE TABLE ImageMetadata\n"
935
 
                            " (imageid INTEGER PRIMARY KEY,\n"
936
 
                            "  make TEXT,\n"
937
 
                            "  model TEXT,\n"
938
 
                            "  lens TEXT,\n"
939
 
                            "  aperture REAL,\n"
940
 
                            "  focalLength REAL,\n"
941
 
                            "  focalLength35 REAL,\n"
942
 
                            "  exposureTime REAL,\n"
943
 
                            "  exposureProgram INTEGER,\n"
944
 
                            "  exposureMode INTEGER,\n"
945
 
                            "  sensitivity INTEGER,\n"
946
 
                            "  flash INTEGER,\n"
947
 
                            "  whiteBalance INTEGER,\n"
948
 
                            "  whiteBalanceColorTemperature INTEGER,\n"
949
 
                            "  meteringMode INTEGER,\n"
950
 
                            "  subjectDistance REAL,\n"
951
 
                            "  subjectDistanceCategory INTEGER);") );
 
1087
        QString("CREATE TABLE ImageMetadata\n"
 
1088
                " (imageid INTEGER PRIMARY KEY,\n"
 
1089
                "  make TEXT,\n"
 
1090
                "  model TEXT,\n"
 
1091
                "  lens TEXT,\n"
 
1092
                "  aperture REAL,\n"
 
1093
                "  focalLength REAL,\n"
 
1094
                "  focalLength35 REAL,\n"
 
1095
                "  exposureTime REAL,\n"
 
1096
                "  exposureProgram INTEGER,\n"
 
1097
                "  exposureMode INTEGER,\n"
 
1098
                "  sensitivity INTEGER,\n"
 
1099
                "  flash INTEGER,\n"
 
1100
                "  whiteBalance INTEGER,\n"
 
1101
                "  whiteBalanceColorTemperature INTEGER,\n"
 
1102
                "  meteringMode INTEGER,\n"
 
1103
                "  subjectDistance REAL,\n"
 
1104
                "  subjectDistanceCategory INTEGER);") );
952
1105
 
953
1106
    m_Backend->execSql( QString("INSERT INTO ImageMetadata "
954
 
                            " (imageid, make, model, lens, aperture, focalLength, focalLength35, "
955
 
                            "  exposureTime, exposureProgram, exposureMode, sensitivity, flash, whiteBalance, "
956
 
                            "  whiteBalanceColorTemperature, meteringMode, subjectDistance, subjectDistanceCategory) "
957
 
                            "SELECT imageid, make, model, NULL, aperture, focalLength, focalLength35, "
958
 
                            "  exposureTime, exposureProgram, exposureMode, sensitivity, flash, whiteBalance, "
959
 
                            "  whiteBalanceColorTemperature, meteringMode, subjectDistance, subjectDistanceCategory "
960
 
                            "FROM ImageMetadataTemp;"));
 
1107
                                " (imageid, make, model, lens, aperture, focalLength, focalLength35, "
 
1108
                                "  exposureTime, exposureProgram, exposureMode, sensitivity, flash, whiteBalance, "
 
1109
                                "  whiteBalanceColorTemperature, meteringMode, subjectDistance, subjectDistanceCategory) "
 
1110
                                "SELECT imageid, make, model, NULL, aperture, focalLength, focalLength35, "
 
1111
                                "  exposureTime, exposureProgram, exposureMode, sensitivity, flash, whiteBalance, "
 
1112
                                "  whiteBalanceColorTemperature, meteringMode, subjectDistance, subjectDistanceCategory "
 
1113
                                "FROM ImageMetadataTemp;"));
961
1114
 
962
1115
    m_Backend->execSql(QString("DROP TABLE ImagePositionsTemp;"));
963
1116
    m_Backend->execSql(QString("DROP TABLE ImageMetadataTemp;"));
968
1121
void SchemaUpdater::preAlpha010Update3()
969
1122
{
970
1123
    QString hasUpdate = m_AlbumDB->getSetting("preAlpha010Update3");
 
1124
 
971
1125
    if (!hasUpdate.isNull())
 
1126
    {
972
1127
        return;
 
1128
    }
973
1129
 
974
1130
    m_Backend->execSql(QString("DROP TABLE ImageCopyright;"));
975
1131
    m_Backend->execSql(
976
 
                    QString("CREATE TABLE ImageCopyright\n"
977
 
                            " (imageid INTEGER,\n"
978
 
                            "  property TEXT,\n"
979
 
                            "  value TEXT,\n"
980
 
                            "  extraValue TEXT,\n"
981
 
                            "  UNIQUE(imageid, property, value, extraValue));")
982
 
                                );
 
1132
        QString("CREATE TABLE ImageCopyright\n"
 
1133
                " (imageid INTEGER,\n"
 
1134
                "  property TEXT,\n"
 
1135
                "  value TEXT,\n"
 
1136
                "  extraValue TEXT,\n"
 
1137
                "  UNIQUE(imageid, property, value, extraValue));")
 
1138
    );
983
1139
 
984
1140
    m_AlbumDB->setSetting("preAlpha010Update3", "true");
985
1141
}
987
1143
void SchemaUpdater::beta010Update1()
988
1144
{
989
1145
    QString hasUpdate = m_AlbumDB->getSetting("beta010Update1");
 
1146
 
990
1147
    if (!hasUpdate.isNull())
 
1148
    {
991
1149
        return;
 
1150
    }
992
1151
 
993
1152
    // if Image has been deleted
994
1153
    m_Backend->execSql("DROP TRIGGER delete_image;");
995
1154
    m_Backend->execSql(
996
 
            "CREATE TRIGGER delete_image DELETE ON Images\n"
997
 
            "BEGIN\n"
998
 
            "  DELETE FROM ImageTags\n"
999
 
            "    WHERE imageid=OLD.id;\n"
1000
 
            "  DELETE From ImageHaarMatrix\n "
1001
 
            "    WHERE imageid=OLD.id;\n"
1002
 
            "  DELETE From ImageInformation\n "
1003
 
            "    WHERE imageid=OLD.id;\n"
1004
 
            "  DELETE From ImageMetadata\n "
1005
 
            "    WHERE imageid=OLD.id;\n"
1006
 
            "  DELETE From ImagePositions\n "
1007
 
            "    WHERE imageid=OLD.id;\n"
1008
 
            "  DELETE From ImageComments\n "
1009
 
            "    WHERE imageid=OLD.id;\n"
1010
 
            "  DELETE From ImageCopyright\n "
1011
 
            "    WHERE imageid=OLD.id;\n"
1012
 
            "  DELETE From ImageProperties\n "
1013
 
            "    WHERE imageid=OLD.id;\n"
1014
 
            "  UPDATE Albums SET icon=null \n "
1015
 
            "    WHERE icon=OLD.id;\n"
1016
 
            "  UPDATE Tags SET icon=null \n "
1017
 
            "    WHERE icon=OLD.id;\n"
1018
 
            "END;");
 
1155
        "CREATE TRIGGER delete_image DELETE ON Images\n"
 
1156
        "BEGIN\n"
 
1157
        "  DELETE FROM ImageTags\n"
 
1158
        "    WHERE imageid=OLD.id;\n"
 
1159
        "  DELETE From ImageHaarMatrix\n "
 
1160
        "    WHERE imageid=OLD.id;\n"
 
1161
        "  DELETE From ImageInformation\n "
 
1162
        "    WHERE imageid=OLD.id;\n"
 
1163
        "  DELETE From ImageMetadata\n "
 
1164
        "    WHERE imageid=OLD.id;\n"
 
1165
        "  DELETE From ImagePositions\n "
 
1166
        "    WHERE imageid=OLD.id;\n"
 
1167
        "  DELETE From ImageComments\n "
 
1168
        "    WHERE imageid=OLD.id;\n"
 
1169
        "  DELETE From ImageCopyright\n "
 
1170
        "    WHERE imageid=OLD.id;\n"
 
1171
        "  DELETE From ImageProperties\n "
 
1172
        "    WHERE imageid=OLD.id;\n"
 
1173
        "  UPDATE Albums SET icon=null \n "
 
1174
        "    WHERE icon=OLD.id;\n"
 
1175
        "  UPDATE Tags SET icon=null \n "
 
1176
        "    WHERE icon=OLD.id;\n"
 
1177
        "END;");
1019
1178
 
1020
1179
 
1021
1180
    m_AlbumDB->setSetting("beta010Update1", "true");
1024
1183
void SchemaUpdater::beta010Update2()
1025
1184
{
1026
1185
    QString hasUpdate = m_AlbumDB->getSetting("beta010Update2");
 
1186
 
1027
1187
    if (!hasUpdate.isNull())
 
1188
    {
1028
1189
        return;
 
1190
    }
1029
1191
 
1030
1192
    // force rescan and creation of ImageInformation entry for videos and audio
1031
1193
    m_Backend->execSql("DELETE FROM Images WHERE category=2 OR category=3;");
1039
1201
bool SchemaUpdater::createTablesV3()
1040
1202
{
1041
1203
    if (!m_Backend->execSql( QString("CREATE TABLE Albums\n"
1042
 
                            " (id INTEGER PRIMARY KEY,\n"
1043
 
                            "  url TEXT NOT NULL UNIQUE,\n"
1044
 
                            "  date DATE NOT NULL,\n"
1045
 
                            "  caption TEXT,\n"
1046
 
                            "  collection TEXT,\n"
1047
 
                            "  icon INTEGER);") ))
 
1204
                                     " (id INTEGER PRIMARY KEY,\n"
 
1205
                                     "  url TEXT NOT NULL UNIQUE,\n"
 
1206
                                     "  date DATE NOT NULL,\n"
 
1207
                                     "  caption TEXT,\n"
 
1208
                                     "  collection TEXT,\n"
 
1209
                                     "  icon INTEGER);") ))
1048
1210
    {
1049
1211
        return false;
1050
1212
    }
1051
1213
 
1052
1214
    if (!m_Backend->execSql( QString("CREATE TABLE Tags\n"
1053
 
                            " (id INTEGER PRIMARY KEY,\n"
1054
 
                            "  pid INTEGER,\n"
1055
 
                            "  name TEXT NOT NULL,\n"
1056
 
                            "  icon INTEGER,\n"
1057
 
                            "  iconkde TEXT,\n"
1058
 
                            "  UNIQUE (name, pid));") ))
 
1215
                                     " (id INTEGER PRIMARY KEY,\n"
 
1216
                                     "  pid INTEGER,\n"
 
1217
                                     "  name TEXT NOT NULL,\n"
 
1218
                                     "  icon INTEGER,\n"
 
1219
                                     "  iconkde TEXT,\n"
 
1220
                                     "  UNIQUE (name, pid));") ))
1059
1221
    {
1060
1222
        return false;
1061
1223
    }
1062
1224
 
1063
1225
    if (!m_Backend->execSql( QString("CREATE TABLE TagsTree\n"
1064
 
                            " (id INTEGER NOT NULL,\n"
1065
 
                            "  pid INTEGER NOT NULL,\n"
1066
 
                            "  UNIQUE (id, pid));") ))
 
1226
                                     " (id INTEGER NOT NULL,\n"
 
1227
                                     "  pid INTEGER NOT NULL,\n"
 
1228
                                     "  UNIQUE (id, pid));") ))
1067
1229
    {
1068
1230
        return false;
1069
1231
    }
1070
1232
 
1071
1233
    if (!m_Backend->execSql( QString("CREATE TABLE Images\n"
1072
 
                            " (id INTEGER PRIMARY KEY,\n"
1073
 
                            "  name TEXT NOT NULL,\n"
1074
 
                            "  dirid INTEGER NOT NULL,\n"
1075
 
                            "  caption TEXT,\n"
1076
 
                            "  datetime DATETIME,\n"
1077
 
                            "  UNIQUE (name, dirid));") ))
 
1234
                                     " (id INTEGER PRIMARY KEY,\n"
 
1235
                                     "  name TEXT NOT NULL,\n"
 
1236
                                     "  dirid INTEGER NOT NULL,\n"
 
1237
                                     "  caption TEXT,\n"
 
1238
                                     "  datetime DATETIME,\n"
 
1239
                                     "  UNIQUE (name, dirid));") ))
1078
1240
    {
1079
1241
        return false;
1080
1242
    }
1081
1243
 
1082
1244
 
1083
1245
    if (!m_Backend->execSql( QString("CREATE TABLE ImageTags\n"
1084
 
                            " (imageid INTEGER NOT NULL,\n"
1085
 
                            "  tagid INTEGER NOT NULL,\n"
1086
 
                            "  UNIQUE (imageid, tagid));") ))
 
1246
                                     " (imageid INTEGER NOT NULL,\n"
 
1247
                                     "  tagid INTEGER NOT NULL,\n"
 
1248
                                     "  UNIQUE (imageid, tagid));") ))
1087
1249
    {
1088
1250
        return false;
1089
1251
    }
1090
1252
 
1091
1253
    if (!m_Backend->execSql( QString("CREATE TABLE ImageProperties\n"
1092
 
                            " (imageid  INTEGER NOT NULL,\n"
1093
 
                            "  property TEXT    NOT NULL,\n"
1094
 
                            "  value    TEXT    NOT NULL,\n"
1095
 
                            "  UNIQUE (imageid, property));") ))
 
1254
                                     " (imageid  INTEGER NOT NULL,\n"
 
1255
                                     "  property TEXT    NOT NULL,\n"
 
1256
                                     "  value    TEXT    NOT NULL,\n"
 
1257
                                     "  UNIQUE (imageid, property));") ))
1096
1258
    {
1097
1259
        return false;
1098
1260
    }
1099
1261
 
1100
1262
    if ( !m_Backend->execSql( QString( "CREATE TABLE Searches  \n"
1101
 
                            " (id INTEGER PRIMARY KEY, \n"
1102
 
                            "  name TEXT NOT NULL UNIQUE, \n"
1103
 
                            "  url  TEXT NOT NULL);" ) ) )
 
1263
                                       " (id INTEGER PRIMARY KEY, \n"
 
1264
                                       "  name TEXT NOT NULL UNIQUE, \n"
 
1265
                                       "  url  TEXT NOT NULL);" ) ) )
1104
1266
    {
1105
1267
        return false;
1106
1268
    }
1107
1269
 
1108
1270
    if (!m_Backend->execSql( QString("CREATE TABLE Settings         \n"
1109
 
                            "(keyword TEXT NOT NULL UNIQUE,\n"
1110
 
                            " value TEXT);") ))
 
1271
                                     "(keyword TEXT NOT NULL UNIQUE,\n"
 
1272
                                     " value TEXT);") ))
1111
1273
    {
1112
1274
        return false;
1113
1275
    }
1122
1284
    // trigger: delete from Images/ImageTags/ImageProperties
1123
1285
    // if Album has been deleted
1124
1286
    m_Backend->execSql("CREATE TRIGGER delete_album DELETE ON Albums\n"
1125
 
            "BEGIN\n"
1126
 
            " DELETE FROM ImageTags\n"
1127
 
            "   WHERE imageid IN (SELECT id FROM Images WHERE dirid=OLD.id);\n"
1128
 
            " DELETE From ImageProperties\n"
1129
 
            "   WHERE imageid IN (SELECT id FROM Images WHERE dirid=OLD.id);\n"
1130
 
            " DELETE FROM Images\n"
1131
 
            "   WHERE dirid = OLD.id;\n"
1132
 
            "END;");
 
1287
                       "BEGIN\n"
 
1288
                       " DELETE FROM ImageTags\n"
 
1289
                       "   WHERE imageid IN (SELECT id FROM Images WHERE dirid=OLD.id);\n"
 
1290
                       " DELETE From ImageProperties\n"
 
1291
                       "   WHERE imageid IN (SELECT id FROM Images WHERE dirid=OLD.id);\n"
 
1292
                       " DELETE FROM Images\n"
 
1293
                       "   WHERE dirid = OLD.id;\n"
 
1294
                       "END;");
1133
1295
 
1134
1296
    // trigger: delete from ImageTags/ImageProperties
1135
1297
    // if Image has been deleted
1136
1298
    m_Backend->execSql("CREATE TRIGGER delete_image DELETE ON Images\n"
1137
 
            "BEGIN\n"
1138
 
            "  DELETE FROM ImageTags\n"
1139
 
            "    WHERE imageid=OLD.id;\n"
1140
 
            "  DELETE From ImageProperties\n "
1141
 
            "    WHERE imageid=OLD.id;\n"
1142
 
            "  UPDATE Albums SET icon=null \n "
1143
 
            "    WHERE icon=OLD.id;\n"
1144
 
            "  UPDATE Tags SET icon=null \n "
1145
 
            "    WHERE icon=OLD.id;\n"
1146
 
            "END;");
 
1299
                       "BEGIN\n"
 
1300
                       "  DELETE FROM ImageTags\n"
 
1301
                       "    WHERE imageid=OLD.id;\n"
 
1302
                       "  DELETE From ImageProperties\n "
 
1303
                       "    WHERE imageid=OLD.id;\n"
 
1304
                       "  UPDATE Albums SET icon=null \n "
 
1305
                       "    WHERE icon=OLD.id;\n"
 
1306
                       "  UPDATE Tags SET icon=null \n "
 
1307
                       "    WHERE icon=OLD.id;\n"
 
1308
                       "END;");
1147
1309
 
1148
1310
    // trigger: delete from ImageTags if Tag has been deleted
1149
1311
    m_Backend->execSql("CREATE TRIGGER delete_tag DELETE ON Tags\n"
1150
 
            "BEGIN\n"
1151
 
            "  DELETE FROM ImageTags WHERE tagid=OLD.id;\n"
1152
 
            "END;");
 
1312
                       "BEGIN\n"
 
1313
                       "  DELETE FROM ImageTags WHERE tagid=OLD.id;\n"
 
1314
                       "END;");
1153
1315
 
1154
1316
    // trigger: insert into TagsTree if Tag has been added
1155
1317
    m_Backend->execSql("CREATE TRIGGER insert_tagstree AFTER INSERT ON Tags\n"
1156
 
            "BEGIN\n"
1157
 
            "  INSERT INTO TagsTree\n"
1158
 
            "    SELECT NEW.id, NEW.pid\n"
1159
 
            "    UNION\n"
1160
 
            "    SELECT NEW.id, pid FROM TagsTree WHERE id=NEW.pid;\n"
1161
 
            "END;");
 
1318
                       "BEGIN\n"
 
1319
                       "  INSERT INTO TagsTree\n"
 
1320
                       "    SELECT NEW.id, NEW.pid\n"
 
1321
                       "    UNION\n"
 
1322
                       "    SELECT NEW.id, pid FROM TagsTree WHERE id=NEW.pid;\n"
 
1323
                       "END;");
1162
1324
 
1163
1325
    // trigger: delete from TagsTree if Tag has been deleted
1164
1326
    m_Backend->execSql("CREATE TRIGGER delete_tagstree DELETE ON Tags\n"
1165
 
            "BEGIN\n"
1166
 
            " DELETE FROM Tags\n"
1167
 
            "   WHERE id  IN (SELECT id FROM TagsTree WHERE pid=OLD.id);\n"
1168
 
            " DELETE FROM TagsTree\n"
1169
 
            "   WHERE id IN (SELECT id FROM TagsTree WHERE pid=OLD.id);\n"
1170
 
            " DELETE FROM TagsTree\n"
1171
 
            "    WHERE id=OLD.id;\n"
1172
 
            "END;");
 
1327
                       "BEGIN\n"
 
1328
                       " DELETE FROM Tags\n"
 
1329
                       "   WHERE id  IN (SELECT id FROM TagsTree WHERE pid=OLD.id);\n"
 
1330
                       " DELETE FROM TagsTree\n"
 
1331
                       "   WHERE id IN (SELECT id FROM TagsTree WHERE pid=OLD.id);\n"
 
1332
                       " DELETE FROM TagsTree\n"
 
1333
                       "    WHERE id=OLD.id;\n"
 
1334
                       "END;");
1173
1335
 
1174
1336
    // trigger: delete from TagsTree if Tag has been deleted
1175
1337
    m_Backend->execSql("CREATE TRIGGER move_tagstree UPDATE OF pid ON Tags\n"
1176
 
            "BEGIN\n"
1177
 
            "  DELETE FROM TagsTree\n"
1178
 
            "    WHERE\n"
1179
 
            "      ((id = OLD.id)\n"
1180
 
            "        OR\n"
1181
 
            "        id IN (SELECT id FROM TagsTree WHERE pid=OLD.id))\n"
1182
 
            "      AND\n"
1183
 
            "      pid IN (SELECT pid FROM TagsTree WHERE id=OLD.id);\n"
1184
 
            "  INSERT INTO TagsTree\n"
1185
 
            "     SELECT NEW.id, NEW.pid\n"
1186
 
            "     UNION\n"
1187
 
            "     SELECT NEW.id, pid FROM TagsTree WHERE id=NEW.pid\n"
1188
 
            "     UNION\n"
1189
 
            "     SELECT id, NEW.pid FROM TagsTree WHERE pid=NEW.id\n"
1190
 
            "     UNION\n"
1191
 
            "     SELECT A.id, B.pid FROM TagsTree A, TagsTree B\n"
1192
 
            "        WHERE\n"
1193
 
            "        A.pid = NEW.id AND B.id = NEW.pid;\n"
1194
 
            "END;");
 
1338
                       "BEGIN\n"
 
1339
                       "  DELETE FROM TagsTree\n"
 
1340
                       "    WHERE\n"
 
1341
                       "      ((id = OLD.id)\n"
 
1342
                       "        OR\n"
 
1343
                       "        id IN (SELECT id FROM TagsTree WHERE pid=OLD.id))\n"
 
1344
                       "      AND\n"
 
1345
                       "      pid IN (SELECT pid FROM TagsTree WHERE id=OLD.id);\n"
 
1346
                       "  INSERT INTO TagsTree\n"
 
1347
                       "     SELECT NEW.id, NEW.pid\n"
 
1348
                       "     UNION\n"
 
1349
                       "     SELECT NEW.id, pid FROM TagsTree WHERE id=NEW.pid\n"
 
1350
                       "     UNION\n"
 
1351
                       "     SELECT id, NEW.pid FROM TagsTree WHERE pid=NEW.id\n"
 
1352
                       "     UNION\n"
 
1353
                       "     SELECT A.id, B.pid FROM TagsTree A, TagsTree B\n"
 
1354
                       "        WHERE\n"
 
1355
                       "        A.pid = NEW.id AND B.id = NEW.pid;\n"
 
1356
                       "END;");
1195
1357
 
1196
1358
    return true;
1197
1359
}