Warning, /graphics/digikam/core/data/database/dbconfig.xml.cmake.in is written in an unsupported language. File is not indexed.
0001 <?xml version="1.0" encoding="UTF-8"?>
0002 <!--
0003 * ============================================================
0004 *
0005 * This file is a part of digiKam project
0006 * https://www.digikam.org
0007 *
0008 * Date : 2009-07-28
0009 * Description : Databases SQL statements
0010 *
0011 * SPDX-FileCopyrightText: 2009 by Holger Foerster <hamsi2k at freenet dot de>
0012 * SPDX-FileCopyrightText: 2007-2013 by Marcel Wiesweg <marcel dot wiesweg at gmx dot de>
0013 * SPDX-FileCopyrightText: 2010-2024 by Gilles Caulier <caulier dot gilles at gmail dot com>
0014 * SPDX-FileCopyrightText: 2018 by Mario Frank <mario dot frank at uni minus potsdam dot de>
0015 *
0016 * SPDX-License-Identifier: GPL-2.0-or-later
0017 *
0018 * ============================================================
0019 -->
0020
0021 <databaseconfig>
0022
0023 <defaultDB>QSQLITE</defaultDB>
0024
0025 <!-- Increment this version number whenever you change this file.
0026 The DBCORECONFIG_XML_VERSION value is defined in the toplevel CMakeLists.txt file.
0027 On version mismatch, users will be warned.
0028 -->
0029 <version>${DBCORECONFIG_XML_VERSION}</version>
0030
0031 <!-- =============================================================================================================== -->
0032 <!-- ========================================== SQLITE DATABASE STATEMENTS ========================================= -->
0033 <!-- =============================================================================================================== -->
0034
0035
0036 <database name="QSQLITE">
0037
0038 <hostName>TestHost</hostName>
0039 <databaseName>DatabaseName</databaseName>
0040 <userName>UserName</userName>
0041 <password>Password</password>
0042 <port>Port</port>
0043 <connectoptions>ConnectOptions</connectoptions>
0044
0045 <dbactions>
0046
0047 <!-- SQlite check privileges rules -->
0048
0049 <dbaction name="CheckPriv_CREATE_TRIGGER">
0050 <statement mode="plain">CREATE TRIGGER privcheck_trigger DELETE ON PrivCheck
0051 BEGIN
0052 SELECT * FROM PrivCheck;
0053 END;
0054 </statement>
0055 </dbaction>
0056
0057 <dbaction name="CheckPriv_DROP_TRIGGER">
0058 <statement mode="plain">
0059 DROP TRIGGER privcheck_trigger;
0060 </statement>
0061 </dbaction>
0062
0063 <dbaction name="CheckPriv_CREATE_TABLE">
0064 <statement mode="plain">CREATE TABLE PrivCheck
0065 (id INT,
0066 name VARCHAR(35));
0067 </statement>
0068 </dbaction>
0069
0070 <dbaction name="CheckPriv_ALTER_TABLE">
0071 <statement mode="plain">
0072 ALTER TABLE PrivCheck ADD COLUMN addedColumn;
0073 </statement>
0074 </dbaction>
0075
0076 <dbaction name="CheckPriv_DROP_TABLE">
0077 <statement mode="plain">
0078 DROP TABLE PrivCheck;
0079 </statement>
0080 </dbaction>
0081
0082 <dbaction name="CheckPriv_Cleanup">
0083 <statement mode="plain">
0084 DROP TABLE IF EXISTS PrivCheck;
0085 </statement>
0086 </dbaction>
0087
0088 <!-- SQlite Core Schema Database -->
0089
0090 <dbaction name="CreateDB" mode="transaction">
0091 <statement mode="plain">CREATE TABLE AlbumRoots
0092 (id INTEGER PRIMARY KEY,
0093 label TEXT,
0094 status INTEGER NOT NULL,
0095 type INTEGER NOT NULL,
0096 identifier TEXT,
0097 specificPath TEXT,
0098 caseSensitivity INTEGER,
0099 UNIQUE(identifier, specificPath));
0100 </statement>
0101 <statement mode="plain">CREATE TABLE Albums
0102 (id INTEGER PRIMARY KEY,
0103 albumRoot INTEGER NOT NULL,
0104 relativePath TEXT NOT NULL,
0105 date DATE,
0106 caption TEXT,
0107 collection TEXT,
0108 icon INTEGER,
0109 modificationDate DATETIME,
0110 UNIQUE(albumRoot, relativePath));
0111 </statement>
0112 <statement mode="plain">CREATE TABLE Images
0113 (id INTEGER PRIMARY KEY,
0114 album INTEGER,
0115 name TEXT NOT NULL,
0116 status INTEGER NOT NULL,
0117 category INTEGER NOT NULL,
0118 modificationDate DATETIME,
0119 fileSize INTEGER,
0120 uniqueHash TEXT,
0121 manualOrder INTEGER,
0122 UNIQUE (album, name));
0123 </statement>
0124 <statement mode="plain">CREATE TABLE ImageInformation
0125 (imageid INTEGER PRIMARY KEY,
0126 rating INTEGER,
0127 creationDate DATETIME,
0128 digitizationDate DATETIME,
0129 orientation INTEGER,
0130 width INTEGER,
0131 height INTEGER,
0132 format TEXT,
0133 colorDepth INTEGER,
0134 colorModel INTEGER);
0135 </statement>
0136 <statement mode="plain">CREATE TABLE ImageMetadata
0137 (imageid INTEGER PRIMARY KEY,
0138 make TEXT,
0139 model TEXT,
0140 lens TEXT,
0141 aperture REAL,
0142 focalLength REAL,
0143 focalLength35 REAL,
0144 exposureTime REAL,
0145 exposureProgram INTEGER,
0146 exposureMode INTEGER,
0147 sensitivity INTEGER,
0148 flash INTEGER,
0149 whiteBalance INTEGER,
0150 whiteBalanceColorTemperature INTEGER,
0151 meteringMode INTEGER,
0152 subjectDistance REAL,
0153 subjectDistanceCategory INTEGER);
0154 </statement>
0155 <statement mode="plain">CREATE TABLE VideoMetadata
0156 (imageid INTEGER PRIMARY KEY,
0157 aspectRatio TEXT,
0158 audioBitRate TEXT,
0159 audioChannelType TEXT,
0160 audioCompressor TEXT,
0161 duration TEXT,
0162 frameRate TEXT,
0163 exposureProgram INTEGER,
0164 videoCodec TEXT);
0165 </statement>
0166 <statement mode="plain">CREATE TABLE ImagePositions
0167 (imageid INTEGER PRIMARY KEY,
0168 latitude TEXT,
0169 latitudeNumber REAL,
0170 longitude TEXT,
0171 longitudeNumber REAL,
0172 altitude REAL,
0173 orientation REAL,
0174 tilt REAL,
0175 roll REAL,
0176 accuracy REAL,
0177 description TEXT);
0178 </statement>
0179 <statement mode="plain">CREATE TABLE ImageComments
0180 (id INTEGER PRIMARY KEY,
0181 imageid INTEGER,
0182 type INTEGER,
0183 language TEXT,
0184 author TEXT,
0185 date DATETIME,
0186 comment TEXT,
0187 UNIQUE(imageid, type, language, author));
0188 </statement>
0189 <statement mode="plain">CREATE TABLE ImageCopyright
0190 (id INTEGER PRIMARY KEY,
0191 imageid INTEGER,
0192 property TEXT,
0193 value TEXT,
0194 extraValue TEXT,
0195 UNIQUE(imageid, property, value, extraValue));
0196 </statement>
0197 <statement mode="plain">CREATE TABLE IF NOT EXISTS Tags
0198 (id INTEGER PRIMARY KEY,
0199 pid INTEGER,
0200 name TEXT NOT NULL,
0201 icon INTEGER,
0202 iconkde TEXT,
0203 UNIQUE (name, pid));
0204 </statement>
0205 <statement mode="plain">CREATE TABLE IF NOT EXISTS TagsTree
0206 (id INTEGER NOT NULL,
0207 pid INTEGER NOT NULL,
0208 UNIQUE (id, pid));
0209 </statement>
0210 <statement mode="plain">CREATE TABLE IF NOT EXISTS ImageTags
0211 (imageid INTEGER NOT NULL,
0212 tagid INTEGER NOT NULL,
0213 UNIQUE (imageid, tagid));
0214 </statement>
0215 <statement mode="plain">CREATE TABLE IF NOT EXISTS ImageProperties
0216 (imageid INTEGER NOT NULL,
0217 property TEXT NOT NULL,
0218 value TEXT NOT NULL,
0219 UNIQUE (imageid, property));
0220 </statement>
0221 <statement mode="plain">CREATE TABLE IF NOT EXISTS Searches
0222 (id INTEGER PRIMARY KEY,
0223 type INTEGER,
0224 name TEXT NOT NULL,
0225 query TEXT NOT NULL);
0226 </statement>
0227 <statement mode="plain">CREATE TABLE DownloadHistory
0228 (id INTEGER PRIMARY KEY,
0229 identifier TEXT,
0230 filename TEXT,
0231 filesize INTEGER,
0232 filedate DATETIME,
0233 UNIQUE(identifier, filename, filesize, filedate));
0234 </statement>
0235 <statement mode="plain">CREATE TABLE IF NOT EXISTS Settings
0236 (keyword TEXT NOT NULL UNIQUE,
0237 value TEXT);
0238 </statement>
0239 <statement mode="plain">CREATE TABLE ImageHistory
0240 (imageid INTEGER PRIMARY KEY,
0241 uuid TEXT,
0242 history TEXT);
0243 </statement>
0244 <statement mode="plain">CREATE TABLE ImageRelations
0245 (subject INTEGER,
0246 object INTEGER,
0247 type INTEGER,
0248 UNIQUE(subject, object, type));
0249 </statement>
0250 <statement mode="plain">CREATE TABLE TagProperties
0251 (tagid INTEGER,
0252 property TEXT,
0253 value TEXT);
0254 </statement>
0255 <statement mode="plain">CREATE TABLE ImageTagProperties
0256 (imageid INTEGER,
0257 tagid INTEGER,
0258 property TEXT,
0259 value TEXT);
0260 </statement>
0261 </dbaction>
0262
0263 <!-- SQlite Core Indexes -->
0264
0265 <dbaction name="CreateIndices" mode="transaction">
0266 <statement mode="plain">CREATE INDEX dir_index ON Images (album);</statement>
0267 <statement mode="plain">CREATE INDEX hash_index ON Images (uniqueHash);</statement>
0268 <statement mode="plain">CREATE INDEX tag_index ON ImageTags (tagid);</statement>
0269 <statement mode="plain">CREATE INDEX tag_id_index ON ImageTags (imageid);</statement>
0270 <statement mode="plain">CREATE INDEX image_name_index ON Images (name);</statement>
0271 <statement mode="plain">CREATE INDEX creationdate_index ON ImageInformation (creationDate);</statement>
0272 <statement mode="plain">CREATE INDEX comments_imageid_index ON ImageComments (imageid);</statement>
0273 <statement mode="plain">CREATE INDEX copyright_imageid_index ON ImageCopyright (imageid);</statement>
0274 <statement mode="plain">CREATE INDEX uuid_index ON ImageHistory (uuid);</statement>
0275 <statement mode="plain">CREATE INDEX subject_relations_index ON ImageRelations (subject);</statement>
0276 <statement mode="plain">CREATE INDEX object_relations_index ON ImageRelations (object);</statement>
0277 <statement mode="plain">CREATE INDEX tagproperties_index ON TagProperties (tagid);</statement>
0278 <statement mode="plain">CREATE INDEX imagetagproperties_index ON ImageTagProperties (imageid, tagid);</statement>
0279 <statement mode="plain">CREATE INDEX imagetagproperties_imageid_index ON ImageTagProperties (imageid);</statement>
0280 <statement mode="plain">CREATE INDEX imagetagproperties_tagid_index ON ImageTagProperties (tagid);</statement>
0281 </dbaction>
0282
0283 <!-- SQlite Core Triggers -->
0284
0285 <dbaction name="CreateTriggers" mode="transaction">
0286 <statement mode="plain">CREATE TRIGGER delete_albumroot DELETE ON AlbumRoots
0287 BEGIN
0288 DELETE FROM Albums
0289 WHERE Albums.albumRoot = OLD.id;
0290 END;
0291 </statement>
0292 <statement mode="plain">CREATE TRIGGER delete_album DELETE ON Albums
0293 BEGIN
0294 DELETE FROM Images
0295 WHERE Images.album = OLD.id;
0296 END;
0297 </statement>
0298 <statement mode="plain">CREATE TRIGGER delete_image DELETE ON Images
0299 BEGIN
0300 DELETE FROM ImageTags WHERE imageid=OLD.id;
0301 DELETE From ImageInformation WHERE imageid=OLD.id;
0302 DELETE From ImageMetadata WHERE imageid=OLD.id;
0303 DELETE From VideoMetadata WHERE imageid=OLD.id;
0304 DELETE From ImagePositions WHERE imageid=OLD.id;
0305 DELETE From ImageComments WHERE imageid=OLD.id;
0306 DELETE From ImageCopyright WHERE imageid=OLD.id;
0307 DELETE From ImageProperties WHERE imageid=OLD.id;
0308 DELETE From ImageHistory WHERE imageid=OLD.id;
0309 DELETE FROM ImageRelations WHERE subject=OLD.id OR object=OLD.id;
0310 DELETE FROM ImageTagProperties WHERE imageid=OLD.id;
0311 UPDATE Albums SET icon=null WHERE icon=OLD.id;
0312 UPDATE Tags SET icon=null WHERE icon=OLD.id;
0313 END;
0314 </statement>
0315 <statement mode="plain">CREATE TRIGGER delete_tag DELETE ON Tags
0316 BEGIN
0317 DELETE FROM ImageTags WHERE tagid=OLD.id;
0318 DELETE FROM TagProperties WHERE tagid=OLD.id;
0319 DELETE FROM ImageTagProperties WHERE tagid=OLD.id;
0320 END;
0321 </statement>
0322 <statement mode="plain">CREATE TRIGGER insert_tagstree AFTER INSERT ON Tags
0323 BEGIN
0324 INSERT INTO TagsTree
0325 SELECT NEW.id, NEW.pid
0326 UNION
0327 SELECT NEW.id, pid FROM TagsTree WHERE id=NEW.pid;
0328 END;
0329 </statement>
0330 <statement mode="plain">CREATE TRIGGER delete_tagstree DELETE ON Tags
0331 BEGIN
0332 DELETE FROM Tags
0333 WHERE id IN (SELECT id FROM TagsTree WHERE pid=OLD.id);
0334 DELETE FROM TagsTree
0335 WHERE id IN (SELECT id FROM TagsTree WHERE pid=OLD.id);
0336 DELETE FROM TagsTree
0337 WHERE id=OLD.id;
0338 END;
0339 </statement>
0340 <statement mode="plain">CREATE TRIGGER move_tagstree UPDATE OF pid ON Tags
0341 BEGIN
0342 DELETE FROM TagsTree
0343 WHERE
0344 ((id = OLD.id)
0345 OR
0346 id IN (SELECT id FROM TagsTree WHERE pid=OLD.id))
0347 AND
0348 pid IN (SELECT pid FROM TagsTree WHERE id=OLD.id);
0349 INSERT INTO TagsTree
0350 SELECT NEW.id, NEW.pid
0351 UNION
0352 SELECT NEW.id, pid FROM TagsTree WHERE id=NEW.pid
0353 UNION
0354 SELECT id, NEW.pid FROM TagsTree WHERE pid=NEW.id
0355 UNION
0356 SELECT A.id, B.pid FROM TagsTree A, TagsTree B
0357 WHERE
0358 A.pid = NEW.id AND B.id = NEW.pid;
0359 END;
0360 </statement>
0361 </dbaction>
0362
0363 <dbaction name="getItemURLsInAlbumByItemName">
0364 <statement mode="query">SELECT Albums.relativePath, Images.name FROM Images INNER JOIN Albums ON Albums.id=Images.album WHERE Albums.id=:albumID ORDER BY Images.name COLLATE NOCASE;</statement>
0365 </dbaction>
0366
0367 <!-- NOTE: Don't collate on the path - this is to maintain the same behavior
0368 that happens when sort order is "By Path"
0369 -->
0370 <dbaction name="getItemURLsInAlbumByItemPath">
0371 <statement mode="query">SELECT Albums.relativePath, Images.name FROM Images INNER JOIN Albums ON Albums.id=Images.album WHERE Albums.id=:albumID ORDER BY Albums.relativePath,Images.name;</statement>
0372 </dbaction>
0373
0374 <dbaction name="getItemURLsInAlbumByItemDate">
0375 <statement mode="query">SELECT Albums.relativePath, Images.name FROM Images INNER JOIN Albums ON Albums.id=Images.album INNER JOIN ImageInformation ON ImageInformation.imageid=Images.id WHERE Albums.id=:albumID ORDER BY ImageInformation.creationDate;</statement>
0376 </dbaction>
0377
0378 <dbaction name="getItemURLsInAlbumByItemRating">
0379 <statement mode="query">SELECT Albums.relativePath, Images.name FROM Images INNER JOIN Albums ON Albums.id=Images.album INNER JOIN ImageInformation ON ImageInformation.imageid=Images.id WHERE Albums.id=:albumID ORDER BY ImageInformation.rating DESC;</statement>
0380 </dbaction>
0381
0382 <dbaction name="getItemURLsInAlbumNoItemSorting">
0383 <statement mode="query">SELECT Albums.relativePath, Images.name FROM Images INNER JOIN Albums ON Albums.id=Images.album WHERE Albums.id=:albumID;</statement>
0384 </dbaction>
0385
0386 <dbaction name="changeItemInformation" mode="transaction">
0387 <statement mode="query">INSERT OR IGNORE INTO ImageInformation ( imageid, :fieldList ) VALUES ( :id, :valueList );</statement>
0388 <statement mode="query">UPDATE ImageInformation SET :fieldValueList WHERE imageid=:id;</statement>
0389 </dbaction>
0390
0391 <dbaction name="changeImageHistory" mode="transaction">
0392 <statement mode="query">INSERT OR IGNORE INTO ImageHistory ( imageid, :fieldList ) VALUES ( :id, :valueList );</statement>
0393 <statement mode="query">UPDATE ImageHistory SET :fieldValueList WHERE imageid=:id;</statement>
0394 </dbaction>
0395
0396 <dbaction name="InsertTag">
0397 <statement mode="query">INSERT INTO Tags (pid, name) VALUES( :tagPID, :tagname);</statement>
0398 </dbaction>
0399
0400 <dbaction name="DeleteTag">
0401 <statement mode="query">DELETE FROM Tags WHERE id=:tagID;</statement>
0402 </dbaction>
0403
0404 <dbaction name="deleteAlbumRoot">
0405 <statement mode="query">DELETE FROM Albums WHERE albumRoot=:albumRoot;</statement>
0406 </dbaction>
0407
0408 <dbaction name="deleteAlbumRootPath">
0409 <statement mode="query">DELETE FROM Albums WHERE albumRoot=:albumRoot AND relativePath=:relativePath;</statement>
0410 </dbaction>
0411
0412 <dbaction name="deleteAlbumID">
0413 <statement mode="query">DELETE FROM Albums WHERE Albums.id=:albumId;</statement>
0414 </dbaction>
0415
0416 <dbaction name="GetItemURLsInTagRecursive">
0417 <statement mode="query">SELECT Albums.albumRoot, Albums.relativePath, Images.name
0418 FROM Images JOIN Albums ON Albums.id=Images.album
0419 WHERE Images.status=1 AND Images.id IN (SELECT imageid FROM ImageTags WHERE tagid=:tagID OR tagid IN (SELECT id FROM TagsTree WHERE pid=:tagID2) );
0420 </statement>
0421 </dbaction>
0422
0423 <dbaction name="GetItemURLsInTag">
0424 <statement mode="query">SELECT Albums.albumRoot, Albums.relativePath, Images.name
0425 FROM Images JOIN Albums ON Albums.id=Images.album
0426 WHERE Images.status=1 AND Images.id IN (SELECT imageid FROM ImageTags WHERE tagid=:tagID);
0427 </statement>
0428 </dbaction>
0429
0430 <dbaction name="getItemIDsInTagRecursive">
0431 <statement mode="query">SELECT imageid FROM ImageTags JOIN Images ON ImageTags.imageid=Images.id
0432 WHERE Images.status=1 AND
0433 ( tagid=:tagID
0434 OR tagid IN (SELECT id FROM TagsTree WHERE pid=:tagPID) );
0435 </statement>
0436 </dbaction>
0437
0438 <dbaction name="getItemIDsInTag">
0439 <statement mode="query">SELECT imageid FROM ImageTags JOIN Images ON ImageTags.imageid=Images.id
0440 WHERE Images.status=1 AND tagid=:tagID;
0441 </statement>
0442 </dbaction>
0443
0444 <dbaction name="listTagRecursive">
0445 <statement mode="query">SELECT DISTINCT Images.id, Images.name, Images.album,
0446 Albums.albumRoot,
0447 ImageInformation.rating, Images.category,
0448 ImageInformation.format, ImageInformation.creationDate,
0449 Images.modificationDate, Images.fileSize,
0450 ImageInformation.width, ImageInformation.height
0451 FROM Images
0452 INNER JOIN ImageInformation ON Images.id=ImageInformation.imageid
0453 INNER JOIN Albums ON Albums.id=Images.album
0454 WHERE Images.status=1 AND Images.id IN
0455 (SELECT imageid FROM ImageTags
0456 WHERE tagid=:tagID OR tagid IN (SELECT id FROM TagsTree WHERE pid=:tagPID));
0457 </statement>
0458 </dbaction>
0459
0460 <dbaction name="listTag">
0461 <statement mode="query">SELECT DISTINCT Images.id, Images.name, Images.album,
0462 Albums.albumRoot,
0463 ImageInformation.rating, Images.category,
0464 ImageInformation.format, ImageInformation.creationDate,
0465 Images.modificationDate, Images.fileSize,
0466 ImageInformation.width, ImageInformation.height
0467 FROM Images
0468 INNER JOIN ImageInformation ON Images.id=ImageInformation.imageid
0469 INNER JOIN Albums ON Albums.id=Images.album
0470 WHERE Images.status=1 AND Images.id IN
0471 (SELECT imageid FROM ImageTags
0472 WHERE tagid=:tagID );
0473 </statement>
0474 </dbaction>
0475
0476 <dbaction name="getDatabaseEncoding">
0477 <statement mode="query">PRAGMA encoding;</statement>
0478 </dbaction>
0479
0480 <!-- SQlite Thumbnails Schema Database -->
0481
0482 <dbaction name="CreateThumbnailsDB" mode="transaction">
0483 <statement mode="plain">CREATE TABLE Thumbnails
0484 (id INTEGER PRIMARY KEY,
0485 type INTEGER,
0486 modificationDate DATETIME,
0487 orientationHint INTEGER,
0488 data BLOB);
0489 </statement>
0490 <statement mode="plain">CREATE TABLE UniqueHashes
0491 (uniqueHash TEXT,
0492 fileSize INTEGER,
0493 thumbId INTEGER,
0494 UNIQUE(uniqueHash, fileSize));
0495 </statement>
0496 <statement mode="plain">CREATE TABLE FilePaths
0497 (path TEXT,
0498 thumbId INTEGER,
0499 UNIQUE(path));
0500 </statement>
0501 <statement mode="plain">CREATE TABLE CustomIdentifiers
0502 (identifier TEXT,
0503 thumbId INTEGER,
0504 UNIQUE(identifier));
0505 </statement>
0506 <statement mode="plain">CREATE TABLE IF NOT EXISTS Settings
0507 (keyword TEXT NOT NULL UNIQUE,
0508 value TEXT);
0509 </statement>
0510 </dbaction>
0511
0512 <!-- SQlite Thumbnails Indexes -->
0513
0514 <dbaction name="CreateThumbnailsDBIndices" mode="transaction">
0515 <statement mode="plain">CREATE INDEX id_uniqueHashes ON UniqueHashes (thumbId);</statement>
0516 <statement mode="plain">CREATE INDEX id_filePaths ON FilePaths (thumbId);</statement>
0517 <statement mode="plain">CREATE INDEX id_customIdentifiers ON CustomIdentifiers (thumbId);</statement>
0518 </dbaction>
0519
0520 <!-- SQlite Thumbnails Trigger -->
0521
0522 <dbaction name="CreateThumbnailsDBTrigger" mode="transaction">
0523 <statement mode="plain">CREATE TRIGGER delete_thumbnails DELETE ON Thumbnails
0524 BEGIN
0525 DELETE FROM UniqueHashes WHERE UniqueHashes.thumbId = OLD.id;
0526 DELETE FROM FilePaths WHERE FilePaths.thumbId = OLD.id;
0527 DELETE FROM CustomIdentifiers WHERE CustomIdentifiers.thumbId = OLD.id;
0528 END;
0529 </statement>
0530 </dbaction>
0531
0532 <!-- SQlite Thumbnails Queries -->
0533
0534 <dbaction name="SelectThumbnailSetting">
0535 <statement mode="query">SELECT value FROM Settings WHERE keyword=:keyword;</statement>
0536 </dbaction>
0537
0538 <dbaction name="SelectThumbnailLegacySetting">
0539 <statement mode="query">SELECT value FROM Settings WHERE keyword=:keyword;</statement>
0540 </dbaction>
0541
0542 <dbaction name="ReplaceThumbnailSetting">
0543 <statement mode="query">REPLACE INTO Settings VALUES (:keyword, :value);</statement>
0544 </dbaction>
0545
0546 <!-- SQlite Face Schema Database -->
0547
0548 <!--
0549 The face database file contains the tables listed below:
0550 Identities: contains a list of identities with type.
0551 IdentityAttributes: contains identity attributes as text name and UUID.
0552 FaceMatrices: contains face matrices data for each identity for the DNN recognition algorithm that was used when the training was done.
0553 Settings: includes database version rules.
0554 -->
0555
0556 <dbaction name="CreateFaceDB" mode="transaction">
0557 <statement mode="plain">CREATE TABLE IF NOT EXISTS Settings
0558 (keyword TEXT NOT NULL UNIQUE,
0559 value TEXT);
0560 </statement>
0561 <statement mode="plain">CREATE TABLE Identities
0562 (id INTEGER PRIMARY KEY,
0563 type INTEGER);
0564 </statement>
0565 <statement mode="plain">CREATE TABLE IdentityAttributes
0566 (id INTEGER,
0567 attribute TEXT,
0568 value TEXT);
0569 </statement>
0570 </dbaction>
0571
0572 <dbaction name="CreateFaceDBFaceMatrices" mode="transaction">
0573 <statement mode="plain">DROP TABLE IF EXISTS FaceMatrices;</statement>
0574 <statement mode="plain">CREATE TABLE FaceMatrices
0575 (id INTEGER PRIMARY KEY,
0576 identity INTEGER NOT NULL,
0577 context TEXT,
0578 embedding BLOB NOT NULL);
0579 </statement>
0580 </dbaction>
0581
0582 <dbaction name="CreateFaceDBKDTree" mode="transaction">
0583 <statement mode="plain">CREATE TABLE IF NOT EXISTS KDTree
0584 (id INTEGER PRIMARY KEY,
0585 split_axis INTEGER NOT NULL,
0586 position INTEGER NOT NULL,
0587 max_range BLOB NOT NULL,
0588 min_range BLOB NOT NULL,
0589 parent INTEGER,
0590 left INTEGER,
0591 right INTEGER);
0592 </statement>
0593 </dbaction>
0594
0595 <!-- SQlite Face Indexes -->
0596
0597 <dbaction name="CreateFaceIndices" mode="transaction">
0598 <statement mode="plain">CREATE INDEX attribute_index ON IdentityAttributes (id);</statement>
0599 </dbaction>
0600
0601 <!-- SQlite Face Triggers -->
0602
0603 <dbaction name="CreateFaceTriggers" mode="transaction">
0604 <statement mode="plain">
0605 CREATE TRIGGER delete_identities DELETE ON Identities
0606 BEGIN
0607 DELETE FROM IdentityAttributes
0608 WHERE IdentityAttributes.id = OLD.id;
0609 END;
0610 </statement>
0611 </dbaction>
0612
0613 <!-- SQlite Face Queries -->
0614
0615 <dbaction name="SelectFaceSetting">
0616 <statement mode="query">SELECT value FROM Settings WHERE keyword=:keyword;</statement>
0617 </dbaction>
0618
0619 <dbaction name="ReplaceFaceSetting">
0620 <statement mode="query">REPLACE INTO Settings VALUES (:keyword, :value);</statement>
0621 </dbaction>
0622
0623 <!-- SQlite Similarity Database -->
0624
0625 <dbaction name="CreateSimilarityDB" mode="transaction">
0626 <statement mode="plain">CREATE TABLE IF NOT EXISTS ImageSimilarity
0627 (imageid1 INTEGER NOT NULL,
0628 imageid2 INTEGER NOT NULL,
0629 algorithm INTEGER,
0630 value DOUBLE,
0631 CONSTRAINT Similar UNIQUE(imageid1, imageid2, algorithm));
0632 </statement>
0633 <statement mode="plain">CREATE TABLE IF NOT EXISTS ImageHaarMatrix
0634 (imageid INTEGER PRIMARY KEY,
0635 modificationDate DATETIME,
0636 uniqueHash TEXT,
0637 matrix BLOB);
0638 </statement>
0639 <statement mode="plain">CREATE TABLE IF NOT EXISTS SimilaritySettings
0640 (keyword TEXT NOT NULL UNIQUE,
0641 value TEXT);
0642 </statement>
0643 </dbaction>
0644
0645 <!-- SQlite Similarity Indexes -->
0646
0647 <dbaction name="CreateSimilarityDBIndices" mode="transaction">
0648 </dbaction>
0649
0650 <!-- SQlite Similarity Triggers -->
0651
0652 <dbaction name="CreateSimilarityDBTriggers" mode="transaction">
0653 <statement mode="plain">
0654 <!--
0655 If an entry of the ImageHaarMatrix is deleted,
0656 delete all entries from the ImageSimilarity table
0657 for the Haar algorithm (1) where either the first or the second image id is
0658 the deleted one.
0659 -->
0660 CREATE TRIGGER IF NOT EXISTS delete_similarities DELETE ON ImageHaarMatrix
0661 BEGIN
0662 DELETE FROM ImageSimilarity
0663 WHERE ( ImageSimilarity.imageid1=OLD.imageid OR ImageSimilarity.imageid2=OLD.imageid )
0664 AND ( ImageSimilarity.algorithm=1 );
0665 END;
0666 </statement>
0667 </dbaction>
0668
0669 <!-- SQlite Similarity Queries -->
0670
0671 <dbaction name="SelectSimilaritySetting">
0672 <statement mode="query">SELECT value FROM SimilaritySettings WHERE keyword=:keyword;</statement>
0673 </dbaction>
0674
0675 <dbaction name="SelectSimilarityLegacySetting">
0676 <statement mode="query">SELECT value FROM SimilaritySettings WHERE keyword=:keyword;</statement>
0677 </dbaction>
0678
0679 <dbaction name="ReplaceSimilaritySetting">
0680 <statement mode="query">REPLACE INTO SimilaritySettings VALUES (:keyword, :value);</statement>
0681 </dbaction>
0682
0683 <!-- SQlite Migration Statements -->
0684
0685 <!-- NOTE: Migrate_Cleanup_DB now it's done by the program except for cleanup prepare -->
0686 <dbaction name="Migrate_Cleanup_Prepare">
0687 </dbaction>
0688
0689 <dbaction name="Migrate_Read_AlbumRoots"><statement mode="query">
0690 SELECT id, label, status, type, identifier, specificPath, caseSensitivity FROM AlbumRoots;
0691 </statement></dbaction>
0692 <dbaction name="Migrate_Write_AlbumRoots"><statement mode="query">
0693 INSERT OR IGNORE INTO AlbumRoots (id, label, status, type, identifier, specificPath, caseSensitivity) VALUES (:id, :label, :status, :type, :identifier, :specificPath, :caseSensitivity);
0694 </statement></dbaction>
0695
0696 <!-- Note Albums with an icon set are setup after population of the Images table. -->
0697 <dbaction name="Migrate_Read_Albums"><statement mode="query">
0698 SELECT id, albumRoot, relativePath, date, caption, collection, modificationDate FROM Albums
0699 WHERE albumRoot IN (SELECT id FROM AlbumRoots);
0700 </statement></dbaction>
0701 <dbaction name="Migrate_Write_Albums"><statement mode="query">
0702 INSERT OR IGNORE INTO Albums (id, albumRoot, relativePath, date, caption, collection, icon, modificationDate) VALUES (:id, :albumRoot, :relativePath, :date, :caption, :collection, NULL, :modificationDate);
0703 </statement></dbaction>
0704
0705 <!-- Populate the Albums icon where set. -->
0706 <dbaction name="Migrate_Read_AlbumsExtra"><statement mode="query">
0707 SELECT id, icon FROM Albums WHERE icon IS NOT NULL AND icon != 0;
0708 </statement></dbaction>
0709 <dbaction name="Migrate_Write_AlbumsExtra"><statement mode="query">
0710 UPDATE OR IGNORE Albums SET icon = :icon WHERE id = :id;
0711 </statement></dbaction>
0712
0713 <dbaction name="Migrate_Read_Images"><statement mode="query">
0714 SELECT id, album, name, status, category, modificationDate, fileSize, uniqueHash, manualOrder FROM Images
0715 WHERE album IN (SELECT id FROM Albums);
0716 </statement></dbaction>
0717 <dbaction name="Migrate_Write_Images"><statement mode="query">
0718 INSERT OR IGNORE INTO Images (id, album, name, status, category, modificationDate, fileSize, uniqueHash, manualOrder) VALUES (:id, :album, :name, :status, :category, :modificationDate, :fileSize, :uniqueHash, :manualOrder);
0719 </statement></dbaction>
0720
0721 <dbaction name="Migrate_Read_ImageInformation"><statement mode="query">
0722 SELECT imageid, rating, creationDate, digitizationDate, orientation, width, height, format, colorDepth, colorModel FROM ImageInformation
0723 WHERE imageid IN (SELECT id FROM Images);
0724 </statement></dbaction>
0725 <dbaction name="Migrate_Write_ImageInformation"><statement mode="query">
0726 INSERT OR IGNORE INTO ImageInformation (imageid, rating, creationDate, digitizationDate, orientation, width, height, format, colorDepth, colorModel) VALUES (:imageid, :rating, :creationDate, :digitizationDate, :orientation, :width, :height, :format, :colorDepth, :colorModel);
0727 </statement></dbaction>
0728
0729 <dbaction name="Migrate_Read_ImageMetadata"><statement mode="query">
0730 SELECT imageid, make, model, lens, aperture, focalLength, focalLength35, exposureTime, exposureProgram, exposureMode, sensitivity, flash, whiteBalance, whiteBalanceColorTemperature, meteringMode, subjectDistance, subjectDistanceCategory FROM ImageMetadata
0731 WHERE imageid IN (SELECT id FROM Images);
0732 </statement></dbaction>
0733 <dbaction name="Migrate_Write_ImageMetadata"><statement mode="query">
0734 INSERT OR IGNORE INTO ImageMetadata (imageid, make, model, lens, aperture, focalLength, focalLength35, exposureTime, exposureProgram, exposureMode, sensitivity, flash, whiteBalance, whiteBalanceColorTemperature, meteringMode, subjectDistance, subjectDistanceCategory) VALUES (:imageid, :make, :model, :lens, :aperture, :focalLength, :focalLength35, :exposureTime, :exposureProgram, :exposureMode, :sensitivity, :flash, :whiteBalance, :whiteBalanceColorTemperature, :meteringMode, :subjectDistance, :subjectDistanceCategory);
0735 </statement></dbaction>
0736
0737 <dbaction name="Migrate_Read_VideoMetadata"><statement mode="query">
0738 SELECT imageid, aspectRatio, audioBitRate, audioChannelType, audioCompressor, duration, frameRate, videoCodec FROM VideoMetadata
0739 WHERE imageid IN (SELECT id FROM Images);
0740 </statement></dbaction>
0741 <dbaction name="Migrate_Write_VideoMetadata"><statement mode="query">
0742 INSERT OR IGNORE INTO VideoMetadata (imageid, aspectRatio, audioBitRate, audioChannelType, audioCompressor, duration, frameRate, videoCodec) VALUES (:imageid, :aspectRatio, :audioBitRate, :audioChannelType, :audioCompressor, :duration, :frameRate, :videoCodec);
0743 </statement></dbaction>
0744
0745 <dbaction name="Migrate_Read_ImageTagProperties"><statement mode="query">
0746 SELECT imageid, tagid, property, value FROM ImageTagProperties
0747 WHERE imageid IN (SELECT id FROM Images);
0748 </statement></dbaction>
0749 <dbaction name="Migrate_Write_ImageTagProperties"><statement mode="query">
0750 INSERT OR IGNORE INTO ImageTagProperties (imageid, tagid, property, value) VALUES (:imageid, :tagid, :property, :value);
0751 </statement></dbaction>
0752
0753 <dbaction name="Migrate_Read_TagProperties"><statement mode="query">
0754 SELECT tagid, property, value FROM TagProperties;
0755 </statement></dbaction>
0756 <dbaction name="Migrate_Write_TagProperties"><statement mode="query">
0757 INSERT OR IGNORE INTO TagProperties (tagid, property, value) VALUES (:tagid, :property, :value);
0758 </statement></dbaction>
0759
0760 <dbaction name="Migrate_Read_ImagePositions"><statement mode="query">
0761 SELECT imageid, latitude, latitudeNumber, longitude, longitudeNumber, altitude, orientation, tilt, roll, accuracy, description FROM ImagePositions
0762 WHERE imageid IN (SELECT id FROM Images);
0763 </statement></dbaction>
0764 <dbaction name="Migrate_Write_ImagePositions"><statement mode="query">
0765 INSERT OR IGNORE INTO ImagePositions (imageid, latitude, latitudeNumber, longitude, longitudeNumber, altitude, orientation, tilt, roll, accuracy, description) VALUES (:imageid, :latitude, :latitudeNumber, :longitude, :longitudeNumber, :altitude, :orientation, :tilt, :roll, :accuracy, :description);
0766 </statement></dbaction>
0767
0768 <dbaction name="Migrate_Read_ImageComments"><statement mode="query">
0769 SELECT id, imageid, type, language, author, date, comment FROM ImageComments
0770 WHERE imageid IN (SELECT id FROM Images);
0771 </statement></dbaction>
0772 <dbaction name="Migrate_Write_ImageComments"><statement mode="query">
0773 INSERT OR IGNORE INTO ImageComments (id, imageid, type, language, author, date, comment) VALUES (:id, :imageid, :type, :language, :author, :date, :comment);
0774 </statement></dbaction>
0775
0776 <dbaction name="Migrate_Read_ImageCopyright"><statement mode="query">
0777 SELECT id, imageid, property, value, extraValue FROM ImageCopyright
0778 WHERE imageid IN (SELECT id FROM Images);
0779 </statement></dbaction>
0780 <dbaction name="Migrate_Write_ImageCopyright"><statement mode="query">
0781 INSERT OR IGNORE INTO ImageCopyright (id, imageid, property, value, extraValue) VALUES (:id, :imageid, :property, :value, :extraValue);
0782 </statement></dbaction>
0783
0784 <dbaction name="Migrate_Read_Tags"><statement mode="query">
0785 SELECT id, pid, name, iconkde FROM Tags WHERE id != 0;
0786 </statement></dbaction>
0787 <dbaction name="Migrate_Write_Tags"><statement mode="query">
0788 INSERT OR REPLACE INTO Tags (id, pid, name, icon, iconkde) VALUES (:id, :pid, :name, NULL, :iconkde);
0789 </statement></dbaction>
0790
0791 <!-- Populate the Tags icon where set. -->
0792 <dbaction name="Migrate_Read_TagsExtra"><statement mode="query">
0793 SELECT id, icon FROM Tags WHERE icon IS NOT NULL AND icon != 0;
0794 </statement></dbaction>
0795 <dbaction name="Migrate_Write_TagsExtra"><statement mode="query">
0796 UPDATE OR IGNORE Tags SET icon = :icon WHERE id = :id;
0797 </statement></dbaction>
0798
0799 <dbaction name="Migrate_Read_TagsTree"></dbaction>
0800 <dbaction name="Migrate_Write_TagsTree"></dbaction>
0801
0802 <dbaction name="Migrate_Read_ImageTags"><statement mode="query">
0803 SELECT imageid, tagid FROM ImageTags
0804 WHERE imageid IN (SELECT id FROM Images);
0805 </statement></dbaction>
0806 <dbaction name="Migrate_Write_ImageTags"><statement mode="query">
0807 INSERT OR IGNORE INTO ImageTags (imageid, tagid) VALUES (:imageid, :tagid);
0808 </statement></dbaction>
0809
0810 <dbaction name="Migrate_Read_ImageProperties"><statement mode="query">
0811 SELECT imageid, property, value FROM ImageProperties
0812 WHERE imageid IN (SELECT id FROM Images);
0813 </statement></dbaction>
0814 <dbaction name="Migrate_Write_ImageProperties"><statement mode="query">
0815 INSERT OR IGNORE INTO ImageProperties (imageid, property, value) VALUES (:imageid, :property, :value);
0816 </statement></dbaction>
0817
0818 <dbaction name="Migrate_Read_ImageHistory"><statement mode="query">
0819 SELECT imageid, uuid, history FROM ImageHistory
0820 WHERE imageid IN (SELECT id FROM Images);
0821 </statement></dbaction>
0822 <dbaction name="Migrate_Write_ImageHistory"><statement mode="query">
0823 INSERT OR IGNORE INTO ImageHistory (imageid, uuid, history) VALUES (:imageid, :uuid, :history);
0824 </statement></dbaction>
0825
0826 <dbaction name="Migrate_Read_ImageRelations"><statement mode="query">
0827 SELECT subject, object, type FROM ImageRelations
0828 INNER JOIN Images ON subject = Images.id WHERE object IN (SELECT id FROM Images);
0829 </statement></dbaction>
0830 <dbaction name="Migrate_Write_ImageRelations"><statement mode="query">
0831 INSERT OR IGNORE INTO ImageRelations (subject, object, type) VALUES (:subject, :object, :type);
0832 </statement></dbaction>
0833
0834 <dbaction name="Migrate_Read_Searches"><statement mode="query">
0835 SELECT id, type, name, query FROM Searches;
0836 </statement></dbaction>
0837 <dbaction name="Migrate_Write_Searches"><statement mode="query">
0838 INSERT OR IGNORE INTO Searches (id, type, name, query) VALUES (:id, :type, :name, :query);
0839 </statement></dbaction>
0840
0841 <dbaction name="Migrate_Read_DownloadHistory"><statement mode="query">
0842 SELECT id, identifier, filename, filesize, filedate FROM DownloadHistory;
0843 </statement></dbaction>
0844 <dbaction name="Migrate_Write_DownloadHistory"><statement mode="query">
0845 INSERT OR IGNORE INTO DownloadHistory (id, identifier, filename, filesize, filedate) VALUES (:id, :identifier, :filename, :filesize, :filedate);
0846 </statement></dbaction>
0847
0848 <dbaction name="Migrate_Read_Settings"><statement mode="query">
0849 SELECT keyword, value FROM Settings WHERE keyword != 'Locale';
0850 </statement></dbaction>
0851 <dbaction name="Migrate_Write_Settings"><statement mode="query">
0852 INSERT OR IGNORE INTO Settings (keyword, value) VALUES (:keyword, :value);
0853 </statement></dbaction>
0854
0855 <dbaction name="Delete_Thumbnail_ByPath"><statement mode="query">
0856 DELETE FROM Thumbnails WHERE id IN (SELECT thumbId FROM FilePaths WHERE path=:path);
0857 </statement></dbaction>
0858
0859 <dbaction name="Delete_Thumbnail_ByUniqueHashId"><statement mode="query">
0860 DELETE FROM Thumbnails WHERE id IN (SELECT thumbId FROM UniqueHashes WHERE uniqueHash=:uniqueHash AND fileSize=:filesize);
0861 </statement></dbaction>
0862
0863 <dbaction name="Delete_Thumbnail_ByCustomIdentifier"><statement mode="query">
0864 DELETE FROM Thumbnails WHERE id IN (SELECT thumbId FROM CustomIdentifiers WHERE identifier=:identifier);
0865 </statement></dbaction>
0866
0867 <!-- SQlite Migration from Database Version 5 (0.10 - 1.4) to Version 6 (1.5-) -->
0868
0869 <dbaction name="UpdateSchemaFromV5ToV6" mode="transaction">
0870 <statement mode="plain">CREATE TABLE ImageHistory
0871 (imageid INTEGER PRIMARY KEY,
0872 uuid TEXT,
0873 history TEXT);
0874 </statement>
0875 <statement mode="plain">CREATE TABLE ImageRelations
0876 (subject INTEGER,
0877 object INTEGER,
0878 type INTEGER,
0879 UNIQUE(subject, object, type));
0880 </statement>
0881 <statement mode="plain">CREATE TABLE TagProperties
0882 (tagid INTEGER,
0883 property TEXT,
0884 value TEXT);
0885 </statement>
0886 <statement mode="plain">CREATE TABLE ImageTagProperties
0887 (imageid INTEGER,
0888 tagid INTEGER,
0889 property TEXT,
0890 value TEXT);
0891 </statement>
0892 <statement mode="plain">CREATE INDEX tag_id_index ON ImageTags (imageid);</statement>
0893 <statement mode="plain">CREATE INDEX image_name_index ON Images (name);</statement>
0894 <statement mode="plain">CREATE INDEX creationdate_index ON ImageInformation (creationDate);</statement>
0895 <statement mode="plain">CREATE INDEX comments_imageid_index ON ImageComments (imageid);</statement>
0896 <statement mode="plain">CREATE INDEX copyright_imageid_index ON ImageCopyright (imageid);</statement>
0897 <statement mode="plain">CREATE INDEX uuid_index ON ImageHistory (uuid);</statement>
0898 <statement mode="plain">CREATE INDEX subject_relations_index ON ImageRelations (subject);</statement>
0899 <statement mode="plain">CREATE INDEX object_relations_index ON ImageRelations (object);</statement>
0900 <statement mode="plain">CREATE INDEX tagproperties_index ON TagProperties (tagid);</statement>
0901 <statement mode="plain">CREATE INDEX imagetagproperties_index ON ImageTagProperties (imageid, tagid);</statement>
0902 <statement mode="plain">CREATE INDEX imagetagproperties_imageid_index ON ImageTagProperties (imageid);</statement>
0903 <statement mode="plain">CREATE INDEX imagetagproperties_tagid_index ON ImageTagProperties (tagid);</statement>
0904 <statement mode="plain">DROP TRIGGER delete_image;</statement>
0905 <statement mode="plain">CREATE TRIGGER delete_image DELETE ON Images
0906 BEGIN
0907 DELETE FROM ImageTags WHERE imageid=OLD.id;
0908 DELETE From ImageHaarMatrix WHERE imageid=OLD.id;
0909 DELETE From ImageInformation WHERE imageid=OLD.id;
0910 DELETE From ImageMetadata WHERE imageid=OLD.id;
0911 DELETE From VideoMetadata WHERE imageid=OLD.id;
0912 DELETE From ImagePositions WHERE imageid=OLD.id;
0913 DELETE From ImageComments WHERE imageid=OLD.id;
0914 DELETE From ImageCopyright WHERE imageid=OLD.id;
0915 DELETE From ImageProperties WHERE imageid=OLD.id;
0916 DELETE From ImageHistory WHERE imageid=OLD.id;
0917 DELETE FROM ImageRelations WHERE subject=OLD.id OR object=OLD.id;
0918 DELETE FROM ImageTagProperties WHERE imageid=OLD.id;
0919 UPDATE Albums SET icon=null WHERE icon=OLD.id;
0920 UPDATE Tags SET icon=null WHERE icon=OLD.id;
0921 END;
0922 </statement>
0923 <statement mode="plain">DROP TRIGGER delete_tag;</statement>
0924 <statement mode="plain">CREATE TRIGGER delete_tag DELETE ON Tags
0925 BEGIN
0926 DELETE FROM ImageTags WHERE tagid=OLD.id;
0927 DELETE FROM TagProperties WHERE tagid=OLD.id;
0928 DELETE FROM ImageTagProperties WHERE tagid=OLD.id;
0929 END;
0930 </statement>
0931 </dbaction>
0932
0933 <dbaction name="UpdateSchemaFromV6ToV7" mode="transaction">
0934 <statement mode="plain"> CREATE TABLE VideoMetadata
0935 (imageid INTEGER PRIMARY KEY,
0936 aspectRatio TEXT,
0937 audioBitRate TEXT,
0938 audioChannelType TEXT,
0939 audioCompressor TEXT,
0940 duration TEXT,
0941 frameRate TEXT,
0942 exposureProgram INTEGER,
0943 videoCodec TEXT);
0944 </statement>
0945 <statement mode="plain">DROP TRIGGER delete_image;</statement>
0946 <statement mode="plain">CREATE TRIGGER delete_image DELETE ON Images
0947 BEGIN
0948 DELETE FROM ImageTags WHERE imageid=OLD.id;
0949 DELETE From ImageHaarMatrix WHERE imageid=OLD.id;
0950 DELETE From ImageInformation WHERE imageid=OLD.id;
0951 DELETE From ImageMetadata WHERE imageid=OLD.id;
0952 DELETE From VideoMetadata WHERE imageid=OLD.id;
0953 DELETE From ImagePositions WHERE imageid=OLD.id;
0954 DELETE From ImageComments WHERE imageid=OLD.id;
0955 DELETE From ImageCopyright WHERE imageid=OLD.id;
0956 DELETE From ImageProperties WHERE imageid=OLD.id;
0957 DELETE From ImageHistory WHERE imageid=OLD.id;
0958 DELETE FROM ImageRelations WHERE subject=OLD.id OR object=OLD.id;
0959 DELETE FROM ImageTagProperties WHERE imageid=OLD.id;
0960 UPDATE Albums SET icon=null WHERE icon=OLD.id;
0961 UPDATE Tags SET icon=null WHERE icon=OLD.id;
0962 END;
0963 </statement>
0964 </dbaction>
0965
0966 <dbaction name="UpdateSchemaFromV7ToV9" mode="transaction">
0967 <!-- Nothing to do for SQLite -->
0968 </dbaction>
0969
0970 <dbaction name="UpdateSchemaFromV9ToV10" mode="transaction">
0971 <statement mode="plain">DROP TABLE IF EXISTS ImageHaarMatrix;</statement>
0972 <statement mode="plain">DROP TRIGGER delete_image;</statement>
0973 <statement mode="plain">CREATE TRIGGER delete_image DELETE ON Images
0974 BEGIN
0975 DELETE FROM ImageTags WHERE imageid=OLD.id;
0976 DELETE From ImageInformation WHERE imageid=OLD.id;
0977 DELETE From ImageMetadata WHERE imageid=OLD.id;
0978 DELETE From VideoMetadata WHERE imageid=OLD.id;
0979 DELETE From ImagePositions WHERE imageid=OLD.id;
0980 DELETE From ImageComments WHERE imageid=OLD.id;
0981 DELETE From ImageCopyright WHERE imageid=OLD.id;
0982 DELETE From ImageProperties WHERE imageid=OLD.id;
0983 DELETE From ImageHistory WHERE imageid=OLD.id;
0984 DELETE FROM ImageRelations WHERE subject=OLD.id OR object=OLD.id;
0985 DELETE FROM ImageTagProperties WHERE imageid=OLD.id;
0986 UPDATE Albums SET icon=null WHERE icon=OLD.id;
0987 UPDATE Tags SET icon=null WHERE icon=OLD.id;
0988 END;
0989 </statement>
0990 <statement mode="plain">ALTER TABLE Images ADD manualOrder INTEGER;</statement>
0991 </dbaction>
0992
0993 <dbaction name="UpdateSchemaFromV10ToV11" mode="transaction">
0994 <!-- Nothing to do for SQLite -->
0995 </dbaction>
0996
0997 <dbaction name="UpdateSchemaFromV11ToV12" mode="transaction">
0998 <!-- Nothing to do for SQLite -->
0999 </dbaction>
1000
1001 <dbaction name="UpdateSchemaFromV12ToV13" mode="transaction">
1002 <!-- Nothing to do for SQLite -->
1003 </dbaction>
1004
1005 <dbaction name="UpdateSchemaFromV13ToV14" mode="transaction">
1006 <statement mode="plain">ALTER TABLE Albums ADD modificationDate DATETIME;</statement>
1007 </dbaction>
1008
1009 <dbaction name="UpdateSchemaFromV14ToV15" mode="transaction">
1010 <!-- Nothing to do for SQLite -->
1011 </dbaction>
1012
1013 <dbaction name="UpdateSchemaFromV15ToV16" mode="transaction">
1014 <statement mode="plain">ALTER TABLE AlbumRoots ADD caseSensitivity INTEGER;</statement>
1015 <statement mode="plain">UPDATE AlbumRoots SET status=0, caseSensitivity=0;</statement>
1016 <statement mode="plain">DELETE FROM Settings WHERE keyword='Locale';</statement>
1017 </dbaction>
1018
1019 <dbaction name="UpdateThumbnailsDBSchemaFromV1ToV2" mode="transaction">
1020 <statement mode="plain">CREATE TABLE CustomIdentifiers
1021 (identifier TEXT,
1022 thumbId INTEGER,
1023 UNIQUE(identifier));
1024 </statement>
1025 <statement mode="plain">CREATE INDEX id_customIdentifiers ON CustomIdentifiers (thumbId);</statement>
1026 <statement mode="plain">DROP TRIGGER delete_thumbnails;</statement>
1027 <statement mode="plain">CREATE TRIGGER delete_thumbnails DELETE ON Thumbnails
1028 BEGIN
1029 DELETE FROM UniqueHashes WHERE UniqueHashes.thumbId = OLD.id;
1030 DELETE FROM FilePaths WHERE FilePaths.thumbId = OLD.id;
1031 DELETE FROM CustomIdentifiers WHERE CustomIdentifiers.thumbId = OLD.id;
1032 END;
1033 </statement>
1034 </dbaction>
1035
1036 <dbaction name="UpdateThumbnailsDBSchemaFromV2ToV3" mode="transaction">
1037 <!-- Nothing to do for SQLite -->
1038 </dbaction>
1039
1040 <!--
1041 statements for shrinking the databases. We need actions for each database since MySQL has only vacuum
1042 and integtrity check for tables. Thus, MySQL needs at least one action per table.
1043 Vacuum (SQLite) creates a temporary database that replaces the current one on successful vacuuming.
1044 -->
1045
1046 <dbaction name="vacuumCoreDB">
1047 <statement mode="query">VACUUM;</statement>
1048 </dbaction>
1049
1050 <dbaction name="vacuumThumbnailsDB">
1051 <statement mode="query">VACUUM;</statement>
1052 </dbaction>
1053
1054 <dbaction name="vacuumRecognitionDB">
1055 <statement mode="query">VACUUM;</statement>
1056 </dbaction>
1057
1058 <dbaction name="vacuumSimilarityDB">
1059 <statement mode="query">VACUUM;</statement>
1060 </dbaction>
1061
1062 <dbaction name="checkCoreDbIntegrity">
1063 <statement mode="query">pragma integrity_check;</statement>
1064 </dbaction>
1065
1066 <dbaction name="checkThumbnailsDbIntegrity">
1067 <statement mode="query">pragma integrity_check;</statement>
1068 </dbaction>
1069
1070 <dbaction name="checkRecognitionDbIntegrity">
1071 <statement mode="query">pragma integrity_check;</statement>
1072 </dbaction>
1073
1074 <dbaction name="checkSimilarityDbIntegrity">
1075 <statement mode="query">pragma integrity_check;</statement>
1076 </dbaction>
1077
1078 </dbactions>
1079
1080 </database>
1081
1082
1083
1084 <!-- =============================================================================================================== -->
1085 <!-- ========================================== MYSQL DATABASE STATEMENTS ========================================== -->
1086 <!-- =============================================================================================================== -->
1087
1088
1089
1090 <database name="QMYSQL">
1091
1092 <!-- NOTE: default settings for Mysql Internal server -->
1093 <hostName>$$DBHOSTNAME$$</hostName>
1094 <databaseName>digikam</databaseName>
1095 <userName>root</userName>
1096 <password></password>
1097 <port>$$DBPORT$$</port>
1098 <connectoptions>$$DBOPTIONS$$</connectoptions>
1099
1100 <dbactions>
1101
1102 <!-- Mysql check privileges rules -->
1103
1104 <dbaction name="CheckPriv_CREATE_TRIGGER">
1105 </dbaction>
1106
1107 <dbaction name="CheckPriv_DROP_TRIGGER">
1108 </dbaction>
1109
1110 <dbaction name="CheckPriv_CREATE_TABLE">
1111 <statement mode="plain">CREATE TABLE IF NOT EXISTS PrivCheck
1112 (id INT,
1113 name VARCHAR(35))
1114 ENGINE InnoDB;
1115 </statement>
1116 </dbaction>
1117
1118 <dbaction name="CheckPriv_ALTER_TABLE">
1119 <statement mode="plain">
1120 ALTER TABLE PrivCheck DROP COLUMN name;
1121 </statement>
1122 </dbaction>
1123
1124 <dbaction name="CheckPriv_DROP_TABLE">
1125 <statement mode="plain">
1126 DROP TABLE PrivCheck;
1127 </statement>
1128 </dbaction>
1129
1130 <dbaction name="CheckPriv_Cleanup">
1131 <statement mode="plain">
1132 DROP TABLE IF EXISTS PrivCheck;
1133 </statement>
1134 </dbaction>
1135
1136 <!-- Mysql Core Schema Database -->
1137
1138 <dbaction name="CreateDB" mode="transaction">
1139 <statement mode="plain">CREATE TABLE IF NOT EXISTS AlbumRoots
1140 (id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
1141 label LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1142 status INTEGER NOT NULL,
1143 type INTEGER NOT NULL,
1144 identifier LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1145 specificPath LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1146 caseSensitivity INTEGER,
1147 UNIQUE(identifier(127), specificPath(128)))
1148 ENGINE InnoDB;
1149 </statement>
1150 <statement mode="plain">CREATE TABLE IF NOT EXISTS Albums
1151 (id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
1152 albumRoot INTEGER NOT NULL,
1153 relativePath LONGTEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
1154 date DATE,
1155 caption LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1156 collection LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1157 icon BIGINT,
1158 modificationDate DATETIME,
1159 CONSTRAINT Albums_AlbumRoots FOREIGN KEY (albumRoot) REFERENCES AlbumRoots (id) ON DELETE CASCADE ON UPDATE CASCADE,
1160 UNIQUE(albumRoot, relativePath(255)))
1161 ENGINE InnoDB;
1162 </statement>
1163 <statement mode="plain">CREATE TABLE IF NOT EXISTS Images
1164 (id BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,
1165 album INTEGER,
1166 name LONGTEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
1167 status INTEGER NOT NULL,
1168 category INTEGER NOT NULL,
1169 modificationDate DATETIME,
1170 fileSize BIGINT,
1171 uniqueHash VARCHAR(128),
1172 manualOrder BIGINT,
1173 CONSTRAINT Images_Albums FOREIGN KEY (album) REFERENCES Albums (id) ON DELETE CASCADE ON UPDATE CASCADE,
1174 UNIQUE (album, name(255)))
1175 ENGINE InnoDB;
1176 </statement>
1177 <statement mode="plain">ALTER TABLE Albums
1178 ADD CONSTRAINT Albums_Images FOREIGN KEY (icon) REFERENCES Images (id) ON DELETE SET NULL ON UPDATE CASCADE;
1179 </statement>
1180 <statement mode="plain">CREATE TABLE IF NOT EXISTS ImageInformation
1181 (imageid BIGINT PRIMARY KEY,
1182 rating INTEGER,
1183 creationDate DATETIME,
1184 digitizationDate DATETIME,
1185 orientation INTEGER,
1186 width INTEGER,
1187 height INTEGER,
1188 format LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1189 colorDepth INTEGER,
1190 colorModel INTEGER,
1191 CONSTRAINT ImageInformation_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE)
1192 ENGINE InnoDB;
1193 </statement>
1194 <statement mode="plain">CREATE TABLE IF NOT EXISTS ImageMetadata
1195 (imageid BIGINT PRIMARY KEY,
1196 make LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1197 model LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1198 lens LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1199 aperture REAL,
1200 focalLength REAL,
1201 focalLength35 REAL,
1202 exposureTime REAL,
1203 exposureProgram INTEGER,
1204 exposureMode INTEGER,
1205 sensitivity INTEGER,
1206 flash INTEGER,
1207 whiteBalance INTEGER,
1208 whiteBalanceColorTemperature INTEGER,
1209 meteringMode INTEGER,
1210 subjectDistance REAL,
1211 subjectDistanceCategory INTEGER,
1212 CONSTRAINT ImageMetadata_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE)
1213 ENGINE InnoDB;
1214 </statement>
1215 <statement mode="plain">CREATE TABLE IF NOT EXISTS VideoMetadata
1216 (imageid BIGINT PRIMARY KEY,
1217 aspectRatio TEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1218 audioBitRate TEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1219 audioChannelType TEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1220 audioCompressor TEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1221 duration TEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1222 frameRate TEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1223 exposureProgram INTEGER,
1224 videoCodec TEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1225 CONSTRAINT VideoMetadata_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE)
1226 ENGINE InnoDB;
1227 </statement>
1228 <statement mode="plain">CREATE TABLE IF NOT EXISTS ImagePositions
1229 (imageid BIGINT PRIMARY KEY,
1230 latitude LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1231 latitudeNumber REAL,
1232 longitude LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1233 longitudeNumber REAL,
1234 altitude REAL,
1235 orientation REAL,
1236 tilt REAL,
1237 roll REAL,
1238 accuracy REAL,
1239 description LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1240 CONSTRAINT ImagePositions_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE)
1241 ENGINE InnoDB;
1242 </statement>
1243 <statement mode="plain">CREATE TABLE IF NOT EXISTS ImageComments
1244 (id BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,
1245 imageid BIGINT,
1246 type INTEGER,
1247 language VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_general_ci,
1248 author LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1249 date DATETIME,
1250 comment LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1251 CONSTRAINT ImageComments_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE,
1252 UNIQUE(imageid, type, language, author(202)))
1253 ENGINE InnoDB;</statement>
1254 <statement mode="plain">CREATE TABLE IF NOT EXISTS ImageCopyright
1255 (id BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,
1256 imageid BIGINT,
1257 property LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1258 value LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1259 extraValue LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1260 CONSTRAINT ImageCopyright_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE,
1261 UNIQUE(imageid, property(110), value(111), extraValue(111)))
1262 ENGINE InnoDB;
1263 </statement>
1264 <statement mode="plain">CREATE TABLE IF NOT EXISTS Tags
1265 (id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
1266 pid INTEGER,
1267 name LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
1268 icon BIGINT,
1269 iconkde LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1270 CONSTRAINT Tags_Images FOREIGN KEY (icon) REFERENCES Images (id) ON DELETE SET NULL ON UPDATE CASCADE,
1271 UNIQUE(pid, name(100)))
1272 ENGINE InnoDB;
1273 </statement>
1274 <statement mode="plain">CREATE TABLE IF NOT EXISTS TagsTree
1275 (id INTEGER NOT NULL,
1276 pid INTEGER NOT NULL,
1277 UNIQUE (id, pid))
1278 ENGINE InnoDB;
1279 </statement>
1280 <statement mode="plain">CREATE TABLE IF NOT EXISTS ImageTags
1281 (imageid BIGINT NOT NULL,
1282 tagid INTEGER NOT NULL,
1283 CONSTRAINT ImageTags_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE,
1284 CONSTRAINT ImageTags_Tags FOREIGN KEY (tagid) REFERENCES Tags (id) ON DELETE CASCADE ON UPDATE CASCADE,
1285 UNIQUE (imageid, tagid))
1286 ENGINE InnoDB;
1287 </statement>
1288 <statement mode="plain"> CREATE TABLE IF NOT EXISTS ImageProperties
1289 (imageid BIGINT NOT NULL,
1290 property LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
1291 value LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
1292 CONSTRAINT ImageProperties_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE,
1293 UNIQUE (imageid, property(255)))
1294 ENGINE InnoDB;
1295 </statement>
1296 <statement mode="plain">CREATE TABLE IF NOT EXISTS Searches
1297 (id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
1298 type INTEGER,
1299 name LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
1300 query LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL)
1301 ENGINE InnoDB;
1302 </statement>
1303 <statement mode="plain">CREATE TABLE IF NOT EXISTS DownloadHistory
1304 (id BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,
1305 identifier LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1306 filename LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1307 filesize BIGINT,
1308 filedate DATETIME,
1309 UNIQUE(identifier(164), filename(165), filesize, filedate))
1310 ENGINE InnoDB;
1311 </statement>
1312 <statement mode="plain">CREATE TABLE IF NOT EXISTS Settings
1313 (keyword LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
1314 value LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1315 UNIQUE(keyword(255)))
1316 ENGINE InnoDB;
1317 </statement>
1318 <statement mode="plain">CREATE TABLE IF NOT EXISTS ImageHistory
1319 (imageid BIGINT PRIMARY KEY,
1320 uuid VARCHAR(128),
1321 history LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1322 CONSTRAINT ImageHistory_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE)
1323 ENGINE InnoDB;
1324 </statement>
1325 <statement mode="plain">CREATE TABLE IF NOT EXISTS ImageRelations
1326 (subject BIGINT,
1327 object BIGINT,
1328 type INTEGER,
1329 CONSTRAINT ImageRelations_ImagesS FOREIGN KEY (subject) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE,
1330 CONSTRAINT ImageRelations_ImagesO FOREIGN KEY (object) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE,
1331 UNIQUE(subject, object, type))
1332 ENGINE InnoDB;
1333 </statement>
1334 <statement mode="plain">CREATE TABLE IF NOT EXISTS TagProperties
1335 (tagid INTEGER,
1336 property TEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1337 value LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1338 CONSTRAINT TagProperties_Tags FOREIGN KEY (tagid) REFERENCES Tags (id) ON DELETE CASCADE ON UPDATE CASCADE)
1339 ENGINE InnoDB;
1340 </statement>
1341 <statement mode="plain">CREATE TABLE IF NOT EXISTS ImageTagProperties
1342 (imageid BIGINT,
1343 tagid INTEGER,
1344 property TEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1345 value LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1346 CONSTRAINT ImageTagProperties_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE,
1347 CONSTRAINT ImageTagProperties_Tags FOREIGN KEY (tagid) REFERENCES Tags (id) ON DELETE CASCADE ON UPDATE CASCADE)
1348 ENGINE InnoDB;
1349 </statement>
1350 </dbaction>
1351
1352 <!-- Mysql Core Indexes -->
1353
1354 <dbaction name="CreateIndices" mode="transaction">
1355 <statement mode="plain">
1356 DROP PROCEDURE IF EXISTS create_index_if_not_exists;
1357 </statement>
1358 <statement mode="plain">
1359 CREATE PROCEDURE create_index_if_not_exists(table_name_vc varchar(50), index_name_vc varchar(50), field_list_vc varchar(1024))
1360 BEGIN
1361
1362 set @Index_cnt = (
1363 SELECT COUNT(1) cnt
1364 FROM INFORMATION_SCHEMA.STATISTICS
1365 WHERE CONVERT(DATABASE() USING latin1) = CONVERT(TABLE_SCHEMA USING latin1)
1366 AND CONVERT(table_name USING latin1) = CONVERT(table_name_vc USING latin1)
1367 AND CONVERT(index_name USING latin1) = CONVERT(index_name_vc USING latin1)
1368 );
1369
1370 IF IFNULL(@Index_cnt, 0) = 0 THEN
1371 set @index_sql = CONCAT(
1372 CONVERT( 'ALTER TABLE ' USING latin1),
1373 CONVERT( table_name_vc USING latin1),
1374 CONVERT( ' ADD INDEX ' USING latin1),
1375 CONVERT( index_name_vc USING latin1),
1376 CONVERT( '(' USING latin1),
1377 CONVERT( field_list_vc USING latin1),
1378 CONVERT( ');' USING latin1)
1379 );
1380 PREPARE stmt FROM @index_sql;
1381 EXECUTE stmt;
1382 DEALLOCATE PREPARE stmt;
1383 END IF;
1384 END;
1385 </statement>
1386 <statement mode="plain">CALL create_index_if_not_exists('Images','dir_index','album');</statement>
1387 <statement mode="plain">CALL create_index_if_not_exists('Images','hash_index','uniqueHash');</statement>
1388 <statement mode="plain">CALL create_index_if_not_exists('ImageTags','tag_index','tagid');</statement>
1389 <statement mode="plain">CALL create_index_if_not_exists('ImageTags','tag_id_index','imageid');</statement>
1390 <statement mode="plain">CALL create_index_if_not_exists('Images','image_name_index','name(255)');</statement>
1391 <statement mode="plain">CALL create_index_if_not_exists('ImageInformation','creationdate_index','creationDate');</statement>
1392 <statement mode="plain">CALL create_index_if_not_exists('ImageComments','comments_imageid_index','imageid');</statement>
1393 <statement mode="plain">CALL create_index_if_not_exists('ImageCopyright','copyright_imageid_index','imageid');</statement>
1394 <statement mode="plain">CALL create_index_if_not_exists('ImageHistory','uuid_index','uuid');</statement>
1395 <statement mode="plain">CALL create_index_if_not_exists('ImageRelations','subject_relations_index','subject');</statement>
1396 <statement mode="plain">CALL create_index_if_not_exists('ImageRelations','object_relations_index','object');</statement>
1397 <statement mode="plain">CALL create_index_if_not_exists('TagProperties','tagproperties_index','tagid');</statement>
1398 <statement mode="plain">CALL create_index_if_not_exists('ImageTagProperties','imagetagproperties_index','imageid, tagid');</statement>
1399 <statement mode="plain">CALL create_index_if_not_exists('ImageTagProperties','imagetagproperties_imageid_index','imageid');</statement>
1400 <statement mode="plain">CALL create_index_if_not_exists('ImageTagProperties','imagetagproperties_tagid_index','tagid');</statement>
1401 <statement mode="plain">CALL create_index_if_not_exists('TagsTree','tagstree_id_index','id');</statement>
1402 <statement mode="plain">CALL create_index_if_not_exists('TagsTree','tagstree_pid_index','pid');</statement>
1403 </dbaction>
1404
1405 <!-- Mysql Core Triggers -->
1406
1407 <dbaction name="CreateTriggers" mode="transaction">
1408 <statement mode="plain">SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';</statement>
1409 <statement mode="plain">
1410 REPLACE INTO Tags (id, pid, name, icon, iconkde) VALUES (0, -1, '_Digikam_root_tag_', NULL, NULL);
1411 </statement>
1412 <statement mode="plain">SET SQL_MODE=@OLD_SQL_MODE;</statement>
1413
1414 <statement mode="plain">CREATE TRIGGER insert_tagstree AFTER INSERT ON Tags FOR EACH ROW
1415 INSERT INTO TagsTree SELECT NEW.id, NEW.pid
1416 UNION SELECT NEW.id, pid FROM TagsTree WHERE id = NEW.pid;
1417 </statement>
1418
1419 <statement mode="plain">CREATE TRIGGER delete_tagstree AFTER DELETE ON Tags FOR EACH ROW
1420 BEGIN
1421 DELETE FROM TagsTree WHERE id IN (SELECT id FROM
1422 (SELECT id FROM TagsTree WHERE pid = OLD.id) AS tmpTree1);
1423 DELETE FROM TagsTree WHERE id = OLD.id;
1424 END;
1425 </statement>
1426
1427 <statement mode="plain">CREATE TRIGGER move_tagstree AFTER UPDATE ON Tags FOR EACH ROW
1428 BEGIN
1429 IF (NEW.pid != OLD.pid) THEN
1430 DELETE FROM TagsTree WHERE ((id = OLD.id) OR id IN (SELECT id FROM
1431 (SELECT id FROM TagsTree WHERE pid = OLD.id) AS tmpTree1))
1432 AND pid IN (SELECT pid FROM
1433 (SELECT pid FROM TagsTree WHERE id = OLD.id) AS tmpTree2);
1434 INSERT INTO TagsTree SELECT NEW.id, NEW.pid
1435 UNION SELECT NEW.id, pid FROM TagsTree
1436 WHERE id = NEW.pid
1437 UNION SELECT id, NEW.pid FROM TagsTree
1438 WHERE pid = NEW.id
1439 UNION SELECT A.id, B.pid FROM TagsTree A, TagsTree B
1440 WHERE A.pid = NEW.id AND B.id = NEW.pid;
1441 END IF;
1442 END;
1443 </statement>
1444 </dbaction>
1445
1446 <dbaction name="getItemURLsInAlbumByItemName">
1447 <statement mode="query">SELECT Albums.relativePath, Images.name FROM Images INNER JOIN Albums ON Albums.id=Images.album WHERE Albums.id=:albumID ORDER BY Images.name;</statement>
1448 </dbaction>
1449
1450 <!-- NOTE: Don't collate on the path - this is to maintain the same behavior
1451 that happens when sort order is "By Path"
1452 -->
1453 <dbaction name="getItemURLsInAlbumByItemPath">
1454 <statement mode="query">SELECT Albums.relativePath, Images.name FROM Images INNER JOIN Albums ON Albums.id=Images.album WHERE Albums.id=:albumID ORDER BY Albums.relativePath,Images.name;</statement>
1455 </dbaction>
1456
1457 <dbaction name="getItemURLsInAlbumByItemDate">
1458 <statement mode="query">SELECT Albums.relativePath, Images.name FROM Images INNER JOIN Albums ON Albums.id=Images.album INNER JOIN ImageInformation ON ImageInformation.imageid=Images.id WHERE Albums.id=:albumID ORDER BY ImageInformation.creationDate;</statement>
1459 </dbaction>
1460
1461 <dbaction name="getItemURLsInAlbumByItemRating">
1462 <statement mode="query">SELECT Albums.relativePath, Images.name FROM Images INNER JOIN Albums ON Albums.id=Images.album INNER JOIN ImageInformation ON ImageInformation.imageid=Images.id WHERE Albums.id=:albumID ORDER BY ImageInformation.rating DESC;</statement>
1463 </dbaction>
1464
1465 <dbaction name="getItemURLsInAlbumNoItemSorting">
1466 <statement mode="query">SELECT Albums.relativePath, Images.name FROM Images INNER JOIN Albums ON Albums.id=Images.album WHERE Albums.id=:albumID;</statement>
1467 </dbaction>
1468
1469 <dbaction name="changeItemInformation">
1470 <statement mode="query">INSERT INTO ImageInformation ( imageid, :fieldList ) VALUES ( :id, :valueList ) ON DUPLICATE KEY UPDATE :fieldValueList;</statement>
1471 </dbaction>
1472
1473 <dbaction name="changeImageHistory">
1474 <statement mode="query">INSERT INTO ImageHistory( imageid, :fieldList ) VALUES ( :id, :valueList ) ON DUPLICATE KEY UPDATE :fieldValueList;</statement>
1475 </dbaction>
1476
1477 <dbaction name="InsertTag" mode="transaction">
1478 <statement mode="query">INSERT IGNORE INTO Tags (pid, name) VALUES( :tagPID, :tagname);</statement>
1479 </dbaction>
1480
1481 <dbaction name="DeleteTag" mode="transaction">
1482 <statement mode="query">DELETE FROM Tags WHERE id=:tagID;</statement>
1483 </dbaction>
1484
1485 <dbaction name="deleteAlbumRoot" mode="transaction">
1486 <statement mode="query">SELECT @albumID:=id FROM Albums WHERE albumRoot=:albumRoot;</statement>
1487 <statement mode="query">DELETE FROM Albums WHERE albumRoot=:albumRoot;</statement>
1488 <statement mode="query">DELETE FROM Images WHERE Images.album=@albumID;</statement>
1489 </dbaction>
1490
1491 <dbaction name="deleteAlbumRootPath" mode="transaction">
1492 <statement mode="query">SELECT @albumID:=id FROM Albums WHERE albumRoot=:albumRoot AND BINARY relativePath=:relativePath;</statement>
1493 <statement mode="query">DELETE FROM Albums WHERE albumRoot=:albumRoot AND BINARY relativePath=:relativePath;</statement>
1494 <statement mode="query">DELETE FROM Images WHERE Images.album=@albumID;</statement>
1495 </dbaction>
1496
1497 <dbaction name="deleteAlbumID" mode="transaction">
1498 <statement mode="query">SELECT @albumID:=id FROM Albums WHERE Albums.id=:albumId;</statement>
1499 <statement mode="query">DELETE FROM Albums WHERE Albums.id=:albumId;</statement>
1500 <statement mode="query">DELETE FROM Images WHERE Images.album=@albumID;</statement>
1501 </dbaction>
1502
1503 <dbaction name="GetItemURLsInTagRecursive">
1504 <statement mode="query">SELECT Albums.albumRoot, Albums.relativePath, Images.name
1505 FROM Images JOIN Albums ON Albums.id=Images.album
1506 WHERE Images.status=1 AND Images.id IN (SELECT imageid FROM ImageTags WHERE tagid=:tagID OR tagid IN (SELECT id FROM TagsTree WHERE pid=:tagID2) );
1507 </statement>
1508 </dbaction>
1509
1510 <dbaction name="GetItemURLsInTag">
1511 <statement mode="query">SELECT Albums.albumRoot, Albums.relativePath, Images.name
1512 FROM Images JOIN Albums ON Albums.id=Images.album
1513 WHERE Images.status=1 AND Images.id IN (SELECT imageid FROM ImageTags WHERE tagid=:tagID);
1514 </statement>
1515 </dbaction>
1516
1517 <dbaction name="getItemIDsInTagRecursive">
1518 <statement mode="query">SELECT imageid FROM ImageTags JOIN Images ON ImageTags.imageid=Images.id
1519 WHERE Images.status=1 AND
1520 ( tagid=:tagID
1521 OR tagid IN (SELECT id FROM TagsTree WHERE pid=:tagPID) );
1522 </statement>
1523 </dbaction>
1524
1525 <dbaction name="getItemIDsInTag">
1526 <statement mode="query">SELECT imageid FROM ImageTags JOIN Images ON ImageTags.imageid=Images.id
1527 WHERE Images.status=1 AND tagid=:tagID;
1528 </statement>
1529 </dbaction>
1530
1531 <dbaction name="listTagRecursive">
1532 <statement mode="query">SELECT DISTINCT Images.id, Images.name, Images.album,
1533 Albums.albumRoot,
1534 ImageInformation.rating, Images.category,
1535 ImageInformation.format, ImageInformation.creationDate,
1536 Images.modificationDate, Images.fileSize,
1537 ImageInformation.width, ImageInformation.height
1538 FROM Images
1539 INNER JOIN ImageInformation ON Images.id=ImageInformation.imageid
1540 INNER JOIN Albums ON Albums.id=Images.album
1541 WHERE Images.status=1 AND Images.id IN
1542 (SELECT imageid FROM ImageTags
1543 WHERE tagid=:tagID OR tagid IN (SELECT id FROM TagsTree WHERE pid=:tagPID));
1544 </statement>
1545 </dbaction>
1546
1547 <dbaction name="listTag">
1548 <statement mode="query">SELECT DISTINCT Images.id, Images.name, Images.album,
1549 Albums.albumRoot,
1550 ImageInformation.rating, Images.category,
1551 ImageInformation.format, ImageInformation.creationDate,
1552 Images.modificationDate, Images.fileSize,
1553 ImageInformation.width, ImageInformation.height
1554 FROM Images
1555 INNER JOIN ImageInformation ON Images.id=ImageInformation.imageid
1556 INNER JOIN Albums ON Albums.id=Images.album
1557 WHERE Images.status=1 AND Images.id IN
1558 (SELECT imageid FROM ImageTags
1559 WHERE tagid=:tagID );
1560 </statement>
1561 </dbaction>
1562
1563 <dbaction name="getDatabaseEncoding">
1564 <statement mode="query">SELECT @@character_set_database;</statement>
1565 </dbaction>
1566
1567 <!-- Mysql Thumbnails Schema Database -->
1568
1569 <dbaction name="CreateThumbnailsDB" mode="transaction">
1570 <statement mode="plain">CREATE TABLE IF NOT EXISTS Thumbnails
1571 (id BIGINT PRIMARY KEY AUTO_INCREMENT,
1572 type INTEGER,
1573 modificationDate DATETIME,
1574 orientationHint INTEGER,
1575 data LONGBLOB)
1576 ENGINE InnoDB;
1577 </statement>
1578 <statement mode="plain">CREATE TABLE IF NOT EXISTS UniqueHashes
1579 (uniqueHash VARCHAR(128),
1580 fileSize BIGINT,
1581 thumbId BIGINT,
1582 CONSTRAINT UniqueHashes_Thumbnails FOREIGN KEY (thumbId) REFERENCES Thumbnails (id) ON DELETE CASCADE ON UPDATE CASCADE,
1583 UNIQUE(uniqueHash, fileSize))
1584 ENGINE InnoDB;
1585 </statement>
1586 <statement mode="plain">CREATE TABLE IF NOT EXISTS FilePaths
1587 (path LONGTEXT CHARACTER SET utf8 COLLATE utf8_bin,
1588 thumbId BIGINT,
1589 CONSTRAINT FilePaths_Thumbnails FOREIGN KEY (thumbId) REFERENCES Thumbnails (id) ON DELETE CASCADE ON UPDATE CASCADE,
1590 UNIQUE(path(255)))
1591 ENGINE InnoDB;
1592 </statement>
1593 <statement mode="plain">CREATE TABLE IF NOT EXISTS CustomIdentifiers
1594 (identifier LONGTEXT CHARACTER SET utf8 COLLATE utf8_bin,
1595 thumbId BIGINT,
1596 CONSTRAINT CustomIdentifiers_Thumbnails FOREIGN KEY (thumbId) REFERENCES Thumbnails (id) ON DELETE CASCADE ON UPDATE CASCADE,
1597 UNIQUE(identifier(255)))
1598 ENGINE InnoDB;
1599 </statement>
1600 <statement mode="plain">CREATE TABLE IF NOT EXISTS ThumbSettings
1601 (keyword LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
1602 value LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1603 UNIQUE(keyword(255)))
1604 ENGINE InnoDB;
1605 </statement>
1606 </dbaction>
1607
1608 <!-- Mysql Thumbnails Indexes -->
1609
1610 <dbaction name="CreateThumbnailsDBIndices" mode="transaction">
1611 <statement mode="plain">
1612 DROP PROCEDURE IF EXISTS create_index_if_not_exists;
1613 </statement>
1614 <statement mode="plain">
1615 CREATE PROCEDURE create_index_if_not_exists(table_name_vc varchar(50), index_name_vc varchar(50), field_list_vc varchar(1024))
1616 BEGIN
1617
1618 set @Index_cnt = (
1619 SELECT COUNT(1) cnt
1620 FROM INFORMATION_SCHEMA.STATISTICS
1621 WHERE CONVERT(DATABASE() USING latin1) = CONVERT(TABLE_SCHEMA USING latin1)
1622 AND CONVERT(table_name USING latin1) = CONVERT(table_name_vc USING latin1)
1623 AND CONVERT(index_name USING latin1) = CONVERT(index_name_vc USING latin1)
1624 );
1625
1626 IF IFNULL(@Index_cnt, 0) = 0 THEN
1627 set @index_sql = CONCAT(
1628 CONVERT( 'ALTER TABLE ' USING latin1),
1629 CONVERT( table_name_vc USING latin1),
1630 CONVERT( ' ADD INDEX ' USING latin1),
1631 CONVERT( index_name_vc USING latin1),
1632 CONVERT( '(' USING latin1),
1633 CONVERT( field_list_vc USING latin1),
1634 CONVERT( ');' USING latin1)
1635 );
1636 PREPARE stmt FROM @index_sql;
1637 EXECUTE stmt;
1638 DEALLOCATE PREPARE stmt;
1639 END IF;
1640 END;
1641 </statement>
1642 <statement mode="plain">CALL create_index_if_not_exists('UniqueHashes','id_uniqueHashes','thumbId');</statement>
1643 <statement mode="plain">CALL create_index_if_not_exists('FilePaths','id_filePaths','thumbId');</statement>
1644 <statement mode="plain">CALL create_index_if_not_exists('CustomIdentifiers','id_customIdentifiers','thumbId');</statement>
1645 </dbaction>
1646
1647 <!-- Mysql Thumbnails Trigger -->
1648
1649 <dbaction name="CreateThumbnailsDBTrigger" mode="transaction"></dbaction>
1650
1651 <!-- Mysql Thumbnails Queries -->
1652
1653 <dbaction name="SelectThumbnailSetting">
1654 <statement mode="query">SELECT value FROM ThumbSettings WHERE keyword=:keyword;</statement>
1655 </dbaction>
1656
1657 <dbaction name="SelectThumbnailLegacySetting">
1658 <statement mode="query">SELECT value FROM Settings WHERE keyword=:keyword;</statement>
1659 </dbaction>
1660
1661 <dbaction name="ReplaceThumbnailSetting">
1662 <statement mode="query">REPLACE INTO ThumbSettings VALUES (:keyword, :value);</statement>
1663 </dbaction>
1664
1665 <!-- Mysql Face Schema Database -->
1666 <!--
1667 The face database file contains the tables listed below:
1668 Identities: contains a list of identities with type.
1669 IdentityAttributes: contains identity attributes as text name and UUID.
1670 FaceMatrices: contains face matrices data for each identity for the DNN recognition algorithm that was used when the training was done.
1671 Settings: includes database version rules.
1672 -->
1673
1674 <dbaction name="CreateFaceDB" mode="transaction">
1675 <statement mode="plain">CREATE TABLE IF NOT EXISTS Identities
1676 (id INTEGER PRIMARY KEY AUTO_INCREMENT,
1677 `type` INTEGER)
1678 ENGINE InnoDB;
1679 </statement>
1680 <statement mode="plain">CREATE TABLE IF NOT EXISTS IdentityAttributes
1681 (id INTEGER,
1682 `type` INTEGER,
1683 attribute LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1684 `value` LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1685 CONSTRAINT IdentityAttributes_Identities FOREIGN KEY (id) REFERENCES Identities (id) ON DELETE CASCADE ON UPDATE CASCADE)
1686 ENGINE InnoDB;
1687 </statement>
1688 <statement mode="plain">CREATE TABLE IF NOT EXISTS FaceSettings
1689 (keyword LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
1690 `value` LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1691 UNIQUE(keyword(255)))
1692 ENGINE InnoDB;
1693 </statement>
1694 </dbaction>
1695
1696 <dbaction name="CreateFaceDBFaceMatrices" mode="transaction">
1697 <statement mode="plain">SET FOREIGN_KEY_CHECKS=0;</statement>
1698 <statement mode="plain">DROP TABLE IF EXISTS FaceMatrices;</statement>
1699 <statement mode="plain">SET FOREIGN_KEY_CHECKS=1;</statement>
1700 <statement mode="plain">CREATE TABLE FaceMatrices
1701 (id INTEGER PRIMARY KEY AUTO_INCREMENT,
1702 identity INTEGER NOT NULL,
1703 `context` LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1704 embedding BLOB NOT NULL,
1705 CONSTRAINT FaceEmbedding_Identities FOREIGN KEY (identity) REFERENCES Identities (id) ON DELETE CASCADE ON UPDATE CASCADE)
1706 ENGINE InnoDB;
1707 </statement>
1708 </dbaction>
1709
1710 <dbaction name="CreateFaceDBKDTree" mode="transaction">
1711 <statement mode="plain">CREATE TABLE IF NOT EXISTS KDTree
1712 (id INTEGER PRIMARY KEY AUTO_INCREMENT,
1713 split_axis INTEGER NOT NULL,
1714 position INTEGER NOT NULL,
1715 max_range BLOB NOT NULL,
1716 min_range BLOB NOT NULL,
1717 parent INTEGER,
1718 `left` INTEGER,
1719 `right` INTEGER,
1720 CONSTRAINT Node_position FOREIGN KEY (position) REFERENCES FaceMatrices (id) ON DELETE CASCADE ON UPDATE CASCADE,
1721 CONSTRAINT Parent_node FOREIGN KEY (parent) REFERENCES KDTree (id) ON DELETE CASCADE ON UPDATE CASCADE,
1722 CONSTRAINT Left_node FOREIGN KEY (`left`) REFERENCES KDTree (id) ON DELETE CASCADE ON UPDATE CASCADE,
1723 CONSTRAINT Right_node FOREIGN KEY (`right`) REFERENCES KDTree (id) ON DELETE CASCADE ON UPDATE CASCADE)
1724 ENGINE InnoDB;
1725 </statement>
1726 </dbaction>
1727
1728 <!-- Mysql face Indexes -->
1729
1730 <dbaction name="CreateFaceIndices" mode="transaction">
1731 <statement mode="plain">
1732 DROP PROCEDURE IF EXISTS create_index_if_not_exists;
1733 </statement>
1734 <statement mode="plain">
1735 CREATE PROCEDURE create_index_if_not_exists(table_name_vc varchar(50), index_name_vc varchar(50), field_list_vc varchar(1024))
1736 BEGIN
1737
1738 set @Index_cnt = (
1739 SELECT COUNT(1) cnt
1740 FROM INFORMATION_SCHEMA.STATISTICS
1741 WHERE CONVERT(DATABASE() USING latin1) = CONVERT(TABLE_SCHEMA USING latin1)
1742 AND CONVERT(table_name USING latin1) = CONVERT(table_name_vc USING latin1)
1743 AND CONVERT(index_name USING latin1) = CONVERT(index_name_vc USING latin1)
1744 );
1745
1746 IF IFNULL(@Index_cnt, 0) = 0 THEN
1747 set @index_sql = CONCAT(
1748 CONVERT( 'ALTER TABLE ' USING latin1),
1749 CONVERT( table_name_vc USING latin1),
1750 CONVERT( ' ADD INDEX ' USING latin1),
1751 CONVERT( index_name_vc USING latin1),
1752 CONVERT( '(' USING latin1),
1753 CONVERT( field_list_vc USING latin1),
1754 CONVERT( ');' USING latin1)
1755 );
1756 PREPARE stmt FROM @index_sql;
1757 EXECUTE stmt;
1758 DEALLOCATE PREPARE stmt;
1759 END IF;
1760 END;
1761 </statement>
1762 <statement mode="plain">CALL create_index_if_not_exists('IdentityAttributes','identityattributes_index','id');</statement>
1763 </dbaction>
1764
1765 <!-- Mysql Face Triggers -->
1766
1767 <dbaction name="CreateFaceTriggers" mode="transaction">
1768 </dbaction>
1769
1770 <!-- Mysql Face Queries -->
1771
1772 <dbaction name="SelectFaceSetting">
1773 <statement mode="query">SELECT value FROM FaceSettings WHERE keyword=:keyword;</statement>
1774 </dbaction>
1775
1776 <dbaction name="ReplaceFaceSetting">
1777 <statement mode="query">REPLACE INTO FaceSettings VALUES (:keyword, :value);</statement>
1778 </dbaction>
1779
1780
1781 <!-- Mysql Similarity Schema Database -->
1782
1783 <dbaction name="CreateSimilarityDB" mode="transaction">
1784 <statement mode="plain">CREATE TABLE IF NOT EXISTS ImageSimilarity
1785 (imageid1 BIGINT NOT NULL,
1786 imageid2 BIGINT NOT NULL,
1787 algorithm INTEGER,
1788 value DOUBLE,
1789 CONSTRAINT Similar UNIQUE(imageid1, imageid2, algorithm))
1790 ENGINE InnoDB;
1791 </statement>
1792 <statement mode="plain">CREATE TABLE IF NOT EXISTS ImageHaarMatrix
1793 (imageid BIGINT PRIMARY KEY,
1794 modificationDate DATETIME,
1795 uniqueHash LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1796 matrix LONGBLOB)
1797 ENGINE InnoDB;
1798 </statement>
1799 <statement mode="plain">CREATE TABLE IF NOT EXISTS SimilaritySettings
1800 (keyword LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
1801 `value` LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
1802 UNIQUE(keyword(255)))
1803 ENGINE InnoDB;
1804 </statement>
1805 </dbaction>
1806
1807 <!-- Mysql Similarity Indexes -->
1808 <dbaction name="CreateSimilarityDBIndices" mode="transaction">
1809 </dbaction>
1810
1811 <!-- Mysql Similarity Triggers -->
1812 <dbaction name="CreateSimilarityDBTriggers" mode="transaction">
1813 <!--
1814 <statement mode="plain">
1815
1816 If an entry of the ImageHaarMatrix is deleted,
1817 delete all entries from the ImageSimilarity table
1818 for the Haar algorithm (1) where either the first or the second image id is
1819 the deleted one.
1820
1821 CREATE TRIGGER IF NOT EXISTS delete_similarities AFTER DELETE ON ImageHaarMatrix
1822 FOR EACH ROW
1823 BEGIN
1824 DELETE FROM ImageSimilarity
1825 WHERE ( ImageSimilarity.imageid1=OLD.id OR ImageSimilarity.imageid2=OLD.id )
1826 AND ( ImageSimilarity.algorithm=1 );
1827 END;
1828
1829 </statement>
1830 -->
1831 </dbaction>
1832
1833 <!-- Mysql Similarity Queries -->
1834
1835 <dbaction name="SelectSimilaritySetting">
1836 <statement mode="query">SELECT value FROM SimilaritySettings WHERE keyword=:keyword;</statement>
1837 </dbaction>
1838
1839 <dbaction name="SelectSimilarityLegacySetting">
1840 <statement mode="query">SELECT value FROM SimilaritySettings WHERE keyword=:keyword;</statement>
1841 </dbaction>
1842
1843 <dbaction name="ReplaceSimilaritySetting">
1844 <statement mode="query">REPLACE INTO SimilaritySettings VALUES (:keyword, :value);</statement>
1845 </dbaction>
1846
1847 <!-- Mysql Migration Statements -->
1848
1849 <!-- NOTE: Migrate_Cleanup_DB now it's done by the program except for cleanup prepare -->
1850 <dbaction name="Migrate_Cleanup_Prepare" mode="transaction">
1851 <statement mode="plain">
1852 DROP PROCEDURE IF EXISTS drop_foreign_key;
1853 </statement>
1854 <statement mode="plain">
1855 CREATE PROCEDURE drop_foreign_key(IN tableName VARCHAR(64), IN constraintName VARCHAR(64))
1856 BEGIN
1857 IF EXISTS(
1858 SELECT * FROM information_schema.table_constraints
1859 WHERE
1860 table_schema = DATABASE() AND
1861 table_name = CONVERT(tableName USING latin1) AND
1862 constraint_name = CONVERT(constraintName USING latin1) AND
1863 constraint_type = 'FOREIGN KEY')
1864 THEN
1865 SET @query = CONCAT(
1866 CONVERT('ALTER TABLE ' USING latin1),
1867 CONVERT(tableName USING latin1),
1868 CONVERT(' DROP FOREIGN KEY ' USING latin1),
1869 CONVERT(constraintName USING latin1),
1870 CONVERT(';'USING latin1)
1871 );
1872 PREPARE stmt FROM @query;
1873 EXECUTE stmt;
1874 DEALLOCATE PREPARE stmt;
1875 END IF;
1876 END;
1877 </statement>
1878 <statement mode="plain">SET FOREIGN_KEY_CHECKS=0;</statement>
1879 <statement mode="plain">CALL drop_foreign_key('Albums', 'Albums_Images');</statement>
1880 <statement mode="plain">CALL drop_foreign_key('Albums', 'Images_Albums');</statement>
1881 <statement mode="plain">SET FOREIGN_KEY_CHECKS=1;</statement>
1882 </dbaction>
1883
1884 <dbaction name="Migrate_Read_AlbumRoots"><statement mode="query">
1885 SELECT id, label, status, type, identifier, specificPath, caseSensitivity FROM AlbumRoots;
1886 </statement></dbaction>
1887 <dbaction name="Migrate_Write_AlbumRoots" mode="transaction"><statement mode="query">
1888 INSERT IGNORE INTO AlbumRoots (id, label, status, type, identifier, specificPath, caseSensitivity) VALUES (:id, :label, :status, :type, :identifier, :specificPath, :caseSensitivity);
1889 </statement></dbaction>
1890
1891 <!-- Note Albums with an icon set are setup after population of the Images table. -->
1892 <dbaction name="Migrate_Read_Albums"><statement mode="query">
1893 SELECT id, albumRoot, relativePath COLLATE utf8_general_ci, date, caption, collection, modificationDate FROM Albums
1894 WHERE albumRoot IN (SELECT id FROM AlbumRoots);
1895 </statement></dbaction>
1896 <dbaction name="Migrate_Write_Albums" mode="transaction"><statement mode="query">
1897 INSERT IGNORE INTO Albums (id, albumRoot, relativePath, date, caption, collection, icon, modificationDate) VALUES (:id, :albumRoot, :relativePath, :date, :caption, :collection, NULL, :modificationDate);
1898 </statement></dbaction>
1899
1900 <!-- Populate the Albums icon where set. -->
1901 <dbaction name="Migrate_Read_AlbumsExtra"><statement mode="query">
1902 SELECT id, icon FROM Albums WHERE icon IS NOT NULL AND icon != 0;
1903 </statement></dbaction>
1904 <dbaction name="Migrate_Write_AlbumsExtra" mode="transaction"><statement mode="query">
1905 UPDATE IGNORE Albums SET icon = :icon WHERE id = :id;
1906 </statement></dbaction>
1907
1908 <dbaction name="Migrate_Read_Images"><statement mode="query">
1909 SELECT id, album, name COLLATE utf8_general_ci, status, category, modificationDate, fileSize, uniqueHash, manualOrder FROM Images
1910 WHERE album IN (SELECT id FROM Albums);
1911 </statement></dbaction>
1912 <dbaction name="Migrate_Write_Images" mode="transaction"><statement mode="query">
1913 INSERT IGNORE INTO Images (id, album, name, status, category, modificationDate, fileSize, uniqueHash, manualOrder) VALUES (:id, :album, :name, :status, :category, :modificationDate, :fileSize, :uniqueHash, :manualOrder);
1914 </statement></dbaction>
1915
1916 <dbaction name="Migrate_Read_ImageInformation"><statement mode="query">
1917 SELECT imageid, rating, creationDate, digitizationDate, orientation, width, height, format, colorDepth, colorModel FROM ImageInformation
1918 WHERE imageid IN (SELECT id FROM Images);
1919 </statement></dbaction>
1920 <dbaction name="Migrate_Write_ImageInformation" mode="transaction"><statement mode="query">
1921 INSERT IGNORE INTO ImageInformation (imageid, rating, creationDate, digitizationDate, orientation, width, height, format, colorDepth, colorModel) VALUES (:imageid, :rating, :creationDate, :digitizationDate, :orientation, :width, :height, :format, :colorDepth, :colorModel);
1922 </statement></dbaction>
1923
1924 <dbaction name="Migrate_Read_ImageMetadata"><statement mode="query">
1925 SELECT imageid, make, model, lens, aperture, focalLength, focalLength35, exposureTime, exposureProgram, exposureMode, sensitivity, flash, whiteBalance, whiteBalanceColorTemperature, meteringMode, subjectDistance, subjectDistanceCategory FROM ImageMetadata
1926 WHERE imageid IN (SELECT id FROM Images);
1927 </statement></dbaction>
1928 <dbaction name="Migrate_Write_ImageMetadata" mode="transaction"><statement mode="query">
1929 INSERT IGNORE INTO ImageMetadata (imageid, make, model, lens, aperture, focalLength, focalLength35, exposureTime, exposureProgram, exposureMode, sensitivity, flash, whiteBalance, whiteBalanceColorTemperature, meteringMode, subjectDistance, subjectDistanceCategory) VALUES (:imageid, :make, :model, :lens, :aperture, :focalLength, :focalLength35, :exposureTime, :exposureProgram, :exposureMode, :sensitivity, :flash, :whiteBalance, :whiteBalanceColorTemperature, :meteringMode, :subjectDistance, :subjectDistanceCategory);
1930 </statement></dbaction>
1931
1932 <dbaction name="Migrate_Read_VideoMetadata"><statement mode="query">
1933 SELECT imageid, aspectRatio, audioBitRate, audioChannelType, audioCompressor, duration, frameRate, videoCodec FROM VideoMetadata
1934 WHERE imageid IN (SELECT id FROM Images);
1935 </statement></dbaction>
1936 <dbaction name="Migrate_Write_VideoMetadata" mode="transaction"><statement mode="query">
1937 INSERT IGNORE INTO VideoMetadata (imageid, aspectRatio, audioBitRate, audioChannelType, audioCompressor, duration, frameRate, videoCodec) VALUES (:imageid, :aspectRatio, :audioBitRate, :audioChannelType, :audioCompressor, :duration, :frameRate, :videoCodec);
1938 </statement></dbaction>
1939
1940 <dbaction name="Migrate_Read_ImageTagProperties"><statement mode="query">
1941 SELECT imageid, tagid, property, value FROM ImageTagProperties
1942 WHERE imageid IN (SELECT id FROM Images);
1943 </statement></dbaction>
1944 <dbaction name="Migrate_Write_ImageTagProperties"><statement mode="query">
1945 INSERT IGNORE INTO ImageTagProperties (imageid, tagid, property, value) VALUES (:imageid, :tagid, :property, :value);
1946 </statement></dbaction>
1947
1948 <dbaction name="Migrate_Read_TagProperties"><statement mode="query">
1949 SELECT tagid, property, value FROM TagProperties;
1950 </statement></dbaction>
1951 <dbaction name="Migrate_Write_TagProperties"><statement mode="query">
1952 INSERT IGNORE INTO TagProperties (tagid, property, value) VALUES (:tagid, :property, :value);
1953 </statement></dbaction>
1954
1955 <dbaction name="Migrate_Read_ImagePositions"><statement mode="query">
1956 SELECT imageid, latitude, latitudeNumber, longitude, longitudeNumber, altitude, orientation, tilt, roll, accuracy, description FROM ImagePositions
1957 WHERE imageid IN (SELECT id FROM Images);
1958 </statement></dbaction>
1959 <dbaction name="Migrate_Write_ImagePositions" mode="transaction"><statement mode="query">
1960 INSERT IGNORE INTO ImagePositions (imageid, latitude, latitudeNumber, longitude, longitudeNumber, altitude, orientation, tilt, roll, accuracy, description) VALUES (:imageid, :latitude, :latitudeNumber, :longitude, :longitudeNumber, :altitude, :orientation, :tilt, :roll, :accuracy, :description);
1961 </statement></dbaction>
1962
1963 <dbaction name="Migrate_Read_ImageComments"><statement mode="query">
1964 SELECT id, imageid, type, language, author, date, comment FROM ImageComments
1965 WHERE imageid IN (SELECT id FROM Images);
1966 </statement></dbaction>
1967 <dbaction name="Migrate_Write_ImageComments" mode="transaction"><statement mode="query">
1968 INSERT IGNORE INTO ImageComments (id, imageid, type, language, author, date, comment) VALUES (:id, :imageid, :type, :language, :author, :date, :comment);
1969 </statement></dbaction>
1970
1971 <dbaction name="Migrate_Read_ImageCopyright"><statement mode="query">
1972 SELECT id, imageid, property, value, extraValue FROM ImageCopyright
1973 WHERE imageid IN (SELECT id FROM Images);
1974 </statement></dbaction>
1975 <dbaction name="Migrate_Write_ImageCopyright" mode="transaction"><statement mode="query">
1976 INSERT IGNORE INTO ImageCopyright (id, imageid, property, value, extraValue) VALUES (:id, :imageid, :property, :value, :extraValue);
1977 </statement></dbaction>
1978
1979 <dbaction name="Migrate_Read_Tags"><statement mode="query">
1980 SELECT id, pid, name, iconkde FROM Tags WHERE id != 0;
1981 </statement></dbaction>
1982 <dbaction name="Migrate_Write_Tags" mode="transaction">
1983 <statement mode="plain">SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';</statement>
1984 <statement mode="query">
1985 REPLACE INTO Tags (id, pid, name, icon, iconkde) VALUES (:id, :pid, :name, NULL, :iconkde);
1986 </statement>
1987 <statement mode="plain">SET SQL_MODE=@OLD_SQL_MODE;</statement>
1988 </dbaction>
1989
1990 <!-- Populate the Tags icon where set. -->
1991 <dbaction name="Migrate_Read_TagsExtra"><statement mode="query">
1992 SELECT id, icon FROM Tags WHERE icon IS NOT NULL AND icon != 0;
1993 </statement></dbaction>
1994 <dbaction name="Migrate_Write_TagsExtra"><statement mode="query">
1995 UPDATE IGNORE Tags SET icon = :icon WHERE id = :id;
1996 </statement></dbaction>
1997
1998 <dbaction name="Migrate_Read_TagsTree"></dbaction>
1999 <dbaction name="Migrate_Write_TagsTree"></dbaction>
2000
2001 <dbaction name="Migrate_Read_ImageTags"><statement mode="query">
2002 SELECT imageid, tagid FROM ImageTags
2003 WHERE imageid IN (SELECT id FROM Images);
2004 </statement></dbaction>
2005 <dbaction name="Migrate_Write_ImageTags" mode="transaction"><statement mode="query">
2006 INSERT IGNORE INTO ImageTags (imageid, tagid) VALUES (:imageid, :tagid);
2007 </statement></dbaction>
2008
2009 <dbaction name="Migrate_Read_ImageProperties"><statement mode="query">
2010 SELECT imageid, property, value FROM ImageProperties
2011 WHERE imageid IN (SELECT id FROM Images);
2012 </statement></dbaction>
2013 <dbaction name="Migrate_Write_ImageProperties" mode="transaction"><statement mode="query">
2014 INSERT IGNORE INTO ImageProperties (imageid, property, value) VALUES (:imageid, :property, :value);
2015 </statement></dbaction>
2016
2017 <dbaction name="Migrate_Read_ImageHistory"><statement mode="query">
2018 SELECT imageid, uuid, history FROM ImageHistory
2019 WHERE imageid IN (SELECT id FROM Images);
2020 </statement></dbaction>
2021 <dbaction name="Migrate_Write_ImageHistory"><statement mode="query">
2022 INSERT IGNORE INTO ImageHistory (imageid, uuid, history) VALUES (:imageid, :uuid, :history);
2023 </statement></dbaction>
2024
2025 <dbaction name="Migrate_Read_ImageRelations"><statement mode="query">
2026 SELECT subject, object, type FROM ImageRelations
2027 INNER JOIN Images ON subject = Images.id WHERE object IN (SELECT id FROM Images);
2028 </statement></dbaction>
2029 <dbaction name="Migrate_Write_ImageRelations"><statement mode="query">
2030 INSERT IGNORE INTO ImageRelations (subject, object, type) VALUES (:subject, :object, :type);
2031 </statement></dbaction>
2032
2033 <dbaction name="Migrate_Read_Searches"><statement mode="query">
2034 SELECT id, type, name, query FROM Searches;
2035 </statement></dbaction>
2036 <dbaction name="Migrate_Write_Searches" mode="transaction"><statement mode="query">
2037 INSERT IGNORE INTO Searches (id, type, name, query) VALUES (:id, :type, :name, :query);
2038 </statement></dbaction>
2039
2040 <dbaction name="Migrate_Read_DownloadHistory"><statement mode="query">
2041 SELECT id, identifier, filename, filesize, filedate FROM DownloadHistory;
2042 </statement></dbaction>
2043 <dbaction name="Migrate_Write_DownloadHistory" mode="transaction"><statement mode="query">
2044 INSERT IGNORE INTO DownloadHistory (id, identifier, filename, filesize, filedate) VALUES (:id, :identifier, :filename, :filesize, :filedate);
2045 </statement></dbaction>
2046
2047 <dbaction name="Migrate_Read_Settings"><statement mode="query">
2048 SELECT keyword, value FROM Settings WHERE keyword != 'Locale';
2049 </statement></dbaction>
2050 <dbaction name="Migrate_Write_Settings" mode="transaction"><statement mode="query">
2051 INSERT IGNORE INTO Settings (keyword, value) VALUES (:keyword, :value);
2052 </statement></dbaction>
2053
2054 <dbaction name="Delete_Thumbnail_ByPath" mode="query">
2055 <statement mode="query">
2056 SELECT @thumbsId := thumbId FROM FilePaths WHERE path=:path;
2057 </statement>
2058 <statement mode="query">
2059 DELETE FROM UniqueHashes WHERE UniqueHashes.thumbId = @thumbsId;
2060 </statement>
2061 <statement mode="query">
2062 DELETE FROM FilePaths WHERE FilePaths.thumbId = @thumbsId;
2063 </statement>
2064 <statement mode="query">
2065 DELETE FROM Thumbnails WHERE id = @thumbsId;
2066 </statement>
2067 </dbaction>
2068
2069 <dbaction name="Delete_Thumbnail_ByUniqueHashId" mode="query">
2070 <statement mode="query">
2071 SELECT @thumbsId := thumbId FROM UniqueHashes WHERE uniqueHash=:uniqueHash AND fileSize=:filesize;
2072 </statement>
2073 <statement mode="query">
2074 DELETE FROM UniqueHashes WHERE UniqueHashes.thumbId = @thumbsId;
2075 </statement>
2076 <statement mode="query">
2077 DELETE FROM FilePaths WHERE FilePaths.thumbId = @thumbsId;
2078 </statement>
2079 <statement mode="query">
2080 DELETE FROM Thumbnails WHERE id = @thumbsId;
2081 </statement>
2082 </dbaction>
2083
2084 <dbaction name="Delete_Thumbnail_ByCustomIdentifier" mode="query">
2085 <statement mode="query">
2086 SELECT @thumbsId := thumbId FROM CustomIdentifiers WHERE identifier=:identifier;
2087 </statement>
2088 <statement mode="query">
2089 DELETE FROM UniqueHashes WHERE UniqueHashes.thumbId = @thumbsId;
2090 </statement>
2091 <statement mode="query">
2092 DELETE FROM FilePaths WHERE FilePaths.thumbId = @thumbsId;
2093 </statement>
2094 <statement mode="query">
2095 DELETE FROM CustomIdentifiers WHERE CustomIdentifiers.thumbId = @thumbsId;
2096 </statement>
2097 <statement mode="query">
2098 DELETE FROM Thumbnails WHERE id = @thumbsId;
2099 </statement>
2100 </dbaction>
2101
2102 <!-- Mysql Migration from Database Version 5 (0.10 - 1.4) to Version 6 (1.5-) -->
2103
2104 <dbaction name="UpdateSchemaFromV5ToV6" mode="transaction">
2105 <statement mode="plain">CREATE TABLE IF NOT EXISTS ImageHistory
2106 (imageid INTEGER PRIMARY KEY,
2107 uuid VARCHAR(128),
2108 history LONGTEXT CHARACTER SET utf8);
2109 </statement>
2110 <statement mode="plain">CREATE TABLE IF NOT EXISTS ImageRelations
2111 (subject INTEGER,
2112 object INTEGER,
2113 type INTEGER,
2114 UNIQUE(subject, object, type));
2115 </statement>
2116 <statement mode="plain">CREATE TABLE IF NOT EXISTS TagProperties
2117 (tagid INTEGER,
2118 property TEXT CHARACTER SET utf8,
2119 value LONGTEXT CHARACTER SET utf8);
2120 </statement>
2121 <statement mode="plain">CREATE TABLE IF NOT EXISTS ImageTagProperties
2122 (imageid INTEGER,
2123 tagid INTEGER,
2124 property TEXT CHARACTER SET utf8,
2125 value LONGTEXT CHARACTER SET utf8);
2126 </statement>
2127 <statement mode="plain">CALL create_index_if_not_exists('ImageTags','tag_id_index','imageid');</statement>
2128 <statement mode="plain">CALL create_index_if_not_exists('Images','image_name_index','name(255)');</statement>
2129 <statement mode="plain">CALL create_index_if_not_exists('ImageInformation','creationdate_index','creationDate');</statement>
2130 <statement mode="plain">CALL create_index_if_not_exists('ImageComments','comments_imageid_index','imageid');</statement>
2131 <statement mode="plain">CALL create_index_if_not_exists('ImageCopyright','copyright_imageid_index','imageid');</statement>
2132 <statement mode="plain">CALL create_index_if_not_exists('ImageHistory','uuid_index','uuid');</statement>
2133 <statement mode="plain">CALL create_index_if_not_exists('ImageRelations','subject_relations_index','subject');</statement>
2134 <statement mode="plain">CALL create_index_if_not_exists('ImageRelations','object_relations_index','object');</statement>
2135 <statement mode="plain">CALL create_index_if_not_exists('TagProperties','tagproperties_index','tagid');</statement>
2136 <statement mode="plain">CALL create_index_if_not_exists('ImageTagProperties','imagetagproperties_index','imageid,tagid');</statement>
2137 <statement mode="plain">CALL create_index_if_not_exists('ImageTagProperties','imagetagproperties_imageid_index','imageid');</statement>
2138 <statement mode="plain">CALL create_index_if_not_exists('ImageTagProperties','imagetagproperties_tagid_index','tagid');</statement>
2139 <statement mode="plain">ALTER TABLE Images CHANGE uniqueHash uniqueHash VARCHAR(128);</statement>
2140 <statement mode="plain">DROP TRIGGER IF EXISTS delete_image;</statement>
2141 <statement mode="plain">CREATE TRIGGER delete_image AFTER DELETE ON Images
2142 FOR EACH ROW BEGIN
2143 DELETE FROM ImageTags WHERE imageid=OLD.id;
2144 DELETE From ImageHaarMatrix WHERE imageid=OLD.id;
2145 DELETE From ImageInformation WHERE imageid=OLD.id;
2146 DELETE From ImageMetadata WHERE imageid=OLD.id;
2147 DELETE From VideoMetadata WHERE imageid=OLD.id;
2148 DELETE From ImagePositions WHERE imageid=OLD.id;
2149 DELETE From ImageComments WHERE imageid=OLD.id;
2150 DELETE From ImageCopyright WHERE imageid=OLD.id;
2151 DELETE From ImageProperties WHERE imageid=OLD.id;
2152 DELETE From ImageHistory WHERE imageid=OLD.id;
2153 DELETE FROM ImageRelations WHERE subject=OLD.id OR object=OLD.id;
2154 DELETE FROM ImageTagProperties WHERE imageid=OLD.id;
2155 UPDATE Albums SET icon=null WHERE icon=OLD.id;
2156 UPDATE Tags SET icon=null WHERE icon=OLD.id;
2157 END;
2158 </statement>
2159 <statement mode="plain">DROP TRIGGER IF EXISTS delete_tag;</statement>
2160 <statement mode="plain">CREATE TRIGGER delete_tag AFTER DELETE ON Tags
2161 FOR EACH ROW BEGIN
2162 DELETE FROM ImageTags WHERE tagid=OLD.id;
2163 DELETE FROM TagProperties WHERE tagid=OLD.id;
2164 DELETE FROM ImageTagProperties WHERE tagid=OLD.id;
2165 END;
2166 </statement>
2167 </dbaction>
2168
2169 <dbaction name="UpdateSchemaFromV6ToV7" mode="transaction">
2170 <statement mode="plain">CREATE TABLE IF NOT EXISTS VideoMetadata
2171 (imageid INTEGER PRIMARY KEY,
2172 aspectRatio TEXT,
2173 audioBitRate TEXT,
2174 audioChannelType TEXT,
2175 audioCompressor TEXT,
2176 duration TEXT,
2177 frameRate TEXT,
2178 exposureProgram INTEGER,
2179 videoCodec TEXT);
2180 </statement>
2181 <statement mode="plain">DROP TRIGGER IF EXISTS delete_image;</statement>
2182 <statement mode="plain">CREATE TRIGGER delete_image AFTER DELETE ON Images
2183 FOR EACH ROW BEGIN
2184 DELETE FROM ImageTags WHERE imageid=OLD.id;
2185 DELETE From ImageHaarMatrix WHERE imageid=OLD.id;
2186 DELETE From ImageInformation WHERE imageid=OLD.id;
2187 DELETE From ImageMetadata WHERE imageid=OLD.id;
2188 DELETE From VideoMetadata WHERE imageid=OLD.id;
2189 DELETE From ImagePositions WHERE imageid=OLD.id;
2190 DELETE From ImageComments WHERE imageid=OLD.id;
2191 DELETE From ImageCopyright WHERE imageid=OLD.id;
2192 DELETE From ImageProperties WHERE imageid=OLD.id;
2193 DELETE From ImageHistory WHERE imageid=OLD.id;
2194 DELETE FROM ImageRelations WHERE subject=OLD.id OR object=OLD.id;
2195 DELETE FROM ImageTagProperties WHERE imageid=OLD.id;
2196 UPDATE Albums SET icon=null WHERE icon=OLD.id;
2197 UPDATE Tags SET icon=null WHERE icon=OLD.id;
2198 END;
2199 </statement>
2200 </dbaction>
2201
2202 <dbaction name="UpdateSchemaFromV7ToV9" mode="transaction">
2203 <statement mode="plain">
2204 DROP PROCEDURE IF EXISTS drop_foreign_key;
2205 </statement>
2206 <statement mode="plain">
2207 CREATE PROCEDURE drop_foreign_key(IN tableName VARCHAR(64), IN constraintName VARCHAR(64))
2208 BEGIN
2209 IF EXISTS(
2210 SELECT * FROM information_schema.table_constraints
2211 WHERE
2212 table_schema = DATABASE() AND
2213 table_name = CONVERT(tableName USING latin1) AND
2214 constraint_name = CONVERT(constraintName USING latin1) AND
2215 constraint_type = 'FOREIGN KEY')
2216 THEN
2217 SET @query = CONCAT(
2218 CONVERT('ALTER TABLE ' USING latin1),
2219 CONVERT(tableName USING latin1),
2220 CONVERT(' DROP FOREIGN KEY ' USING latin1),
2221 CONVERT(constraintName USING latin1),
2222 CONVERT(';'USING latin1)
2223 );
2224 PREPARE stmt FROM @query;
2225 EXECUTE stmt;
2226 DEALLOCATE PREPARE stmt;
2227 END IF;
2228 END;
2229 </statement>
2230 <statement mode="plain">
2231 DROP PROCEDURE IF EXISTS drop_index_if_exists;
2232 </statement>
2233 <statement mode="plain">
2234 CREATE PROCEDURE drop_index_if_exists(in tableName varchar(64), in indexName varchar(64))
2235 BEGIN
2236 IF((SELECT COUNT(*) AS index_exists FROM information_schema.statistics
2237 WHERE TABLE_SCHEMA = DATABASE() AND table_name = CONVERT(tableName USING latin1) AND index_name = CONVERT(indexName USING latin1)) > 0)
2238 THEN
2239 SET @query = CONCAT(
2240 CONVERT('DROP INDEX ' USING latin1),
2241 CONVERT(indexName USING latin1),
2242 CONVERT(' ON ' USING latin1),
2243 CONVERT(tableName USING latin1)
2244 );
2245 PREPARE stmt FROM @query;
2246 EXECUTE stmt;
2247 END IF;
2248 END;
2249 </statement>
2250 <statement mode="plain">DROP TRIGGER IF EXISTS delete_image;</statement>
2251 <statement mode="plain">DROP TRIGGER IF EXISTS delete_tag;</statement>
2252 <statement mode="plain">DROP TRIGGER IF EXISTS move_tagstree;</statement>
2253 <statement mode="plain">CALL drop_index_if_exists('AlbumRoots', 'identifier');</statement>
2254 <statement mode="plain">CALL drop_foreign_key('Albums', 'Albums_AlbumRoots');</statement>
2255 <statement mode="plain">CALL drop_index_if_exists('Albums', 'albumRoot');</statement>
2256 <statement mode="plain">CALL drop_foreign_key('Images', 'Images_Albums');</statement>
2257 <statement mode="plain">CALL drop_index_if_exists('Images', 'album');</statement>
2258 <statement mode="plain">CALL drop_index_if_exists('Images', 'album_2');</statement>
2259 <statement mode="plain">CALL drop_index_if_exists('Images', 'album_3');</statement>
2260 <statement mode="plain">CALL drop_foreign_key('Albums', 'Albums_Images');</statement>
2261 <statement mode="plain">CALL drop_foreign_key('ImageHaarMatrix', 'ImageHaarMatrix_Images');</statement>
2262 <statement mode="plain">CALL drop_foreign_key('ImageInformation', 'ImageInformation_Images');</statement>
2263 <statement mode="plain">CALL drop_foreign_key('ImageMetadata', 'ImageMetadata_Images');</statement>
2264 <statement mode="plain">CALL drop_foreign_key('VideoMetadata', 'VideoMetadata_Images');</statement>
2265 <statement mode="plain">CALL drop_foreign_key('ImagePositions', 'ImagePositions_Images');</statement>
2266 <statement mode="plain">CALL drop_foreign_key('ImageComments', 'ImageComments_Images');</statement>
2267 <statement mode="plain">CALL drop_index_if_exists('ImageComments', 'imageid');</statement>
2268 <statement mode="plain">CALL drop_foreign_key('ImageCopyright', 'ImageCopyright_Images');</statement>
2269 <statement mode="plain">CALL drop_index_if_exists('ImageCopyright', 'imageid');</statement>
2270 <statement mode="plain">CALL drop_foreign_key('Tags', 'Tags_Images');</statement>
2271 <statement mode="plain">CALL drop_index_if_exists('Tags', 'pid');</statement>
2272 <statement mode="plain">CALL drop_foreign_key('ImageTags', 'ImageTags_Images');</statement>
2273 <statement mode="plain">CALL drop_foreign_key('ImageTags', 'ImageTags_Tags');</statement>
2274 <statement mode="plain">CALL drop_foreign_key('ImageProperties', 'ImageProperties_Images');</statement>
2275 <statement mode="plain">CALL drop_index_if_exists('ImageProperties', 'imageid');</statement>
2276 <statement mode="plain">CALL drop_index_if_exists('DownloadHistory', 'identifier');</statement>
2277 <statement mode="plain">CALL drop_index_if_exists('Settings', 'keyword');</statement>
2278 <statement mode="plain">CALL drop_foreign_key('ImageHistory', 'ImageHistory_Images');</statement>
2279 <statement mode="plain">CALL drop_foreign_key('ImageRelations', 'ImageRelations_ImagesS');</statement>
2280 <statement mode="plain">CALL drop_foreign_key('ImageRelations', 'ImageRelations_ImagesO');</statement>
2281 <statement mode="plain">CALL drop_foreign_key('TagProperties', 'TagProperties_Tags');</statement>
2282 <statement mode="plain">CALL drop_foreign_key('ImageTagProperties', 'ImageTagProperties_Images');</statement>
2283 <statement mode="plain">CALL drop_foreign_key('ImageTagProperties', 'ImageTagProperties_Tags');</statement>
2284
2285 <statement mode="plain">ALTER TABLE AlbumRoots MODIFY COLUMN label LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci;</statement>
2286 <statement mode="plain">ALTER TABLE AlbumRoots MODIFY COLUMN identifier LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci;</statement>
2287 <statement mode="plain">ALTER TABLE AlbumRoots MODIFY COLUMN specificPath LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci;</statement>
2288 <statement mode="plain">ALTER TABLE Albums MODIFY COLUMN relativePath LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;</statement>
2289 <statement mode="plain">ALTER TABLE Albums MODIFY COLUMN caption LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci;</statement>
2290 <statement mode="plain">ALTER TABLE Albums MODIFY COLUMN collection LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci;</statement>
2291 <statement mode="plain">ALTER TABLE Images MODIFY COLUMN name LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;</statement>
2292 <statement mode="plain">ALTER TABLE Images MODIFY COLUMN fileSize BIGINT;</statement>
2293 <statement mode="plain">ALTER TABLE ImageHaarMatrix MODIFY COLUMN uniqueHash LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci;</statement>
2294 <statement mode="plain">ALTER TABLE ImageInformation MODIFY COLUMN format LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci;</statement>
2295 <statement mode="plain">ALTER TABLE ImageMetadata MODIFY COLUMN make LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci;</statement>
2296 <statement mode="plain">ALTER TABLE ImageMetadata MODIFY COLUMN model LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci;</statement>
2297 <statement mode="plain">ALTER TABLE ImageMetadata MODIFY COLUMN lens LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci;</statement>
2298 <statement mode="plain">ALTER TABLE ImagePositions MODIFY COLUMN latitude LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci;</statement>
2299 <statement mode="plain">ALTER TABLE ImagePositions MODIFY COLUMN longitude LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci;</statement>
2300 <statement mode="plain">ALTER TABLE ImagePositions MODIFY COLUMN description LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci;</statement>
2301 <statement mode="plain">ALTER TABLE ImageComments MODIFY COLUMN language VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_general_ci;</statement>
2302 <statement mode="plain">ALTER TABLE ImageComments MODIFY COLUMN author LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci;</statement>
2303 <statement mode="plain">ALTER TABLE ImageComments MODIFY COLUMN comment LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci;</statement>
2304 <statement mode="plain">ALTER TABLE ImageCopyright MODIFY COLUMN property LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci;</statement>
2305 <statement mode="plain">ALTER TABLE ImageCopyright MODIFY COLUMN value LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci;</statement>
2306 <statement mode="plain">ALTER TABLE ImageCopyright MODIFY COLUMN extraValue LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci;</statement>
2307 <statement mode="plain">ALTER TABLE Tags MODIFY COLUMN name LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;</statement>
2308 <statement mode="plain">ALTER TABLE Tags MODIFY COLUMN iconkde LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci;</statement>
2309 <statement mode="plain">ALTER TABLE ImageProperties MODIFY COLUMN property LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;</statement>
2310 <statement mode="plain">ALTER TABLE ImageProperties MODIFY COLUMN value LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;</statement>
2311 <statement mode="plain">ALTER TABLE Searches MODIFY COLUMN name LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;</statement>
2312 <statement mode="plain">ALTER TABLE Searches MODIFY COLUMN query LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;</statement>
2313 <statement mode="plain">ALTER TABLE DownloadHistory MODIFY COLUMN identifier LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci;</statement>
2314 <statement mode="plain">ALTER TABLE DownloadHistory MODIFY COLUMN filename LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci;</statement>
2315 <statement mode="plain">ALTER TABLE DownloadHistory MODIFY COLUMN filesize BIGINT;</statement>
2316 <statement mode="plain">ALTER TABLE Settings MODIFY COLUMN keyword LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;</statement>
2317 <statement mode="plain">ALTER TABLE Settings MODIFY COLUMN value LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci;</statement>
2318 <statement mode="plain">ALTER TABLE ImageHistory MODIFY COLUMN history LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci;</statement>
2319 <statement mode="plain">ALTER TABLE TagProperties MODIFY COLUMN property TEXT CHARACTER SET utf8 COLLATE utf8_general_ci;</statement>
2320 <statement mode="plain">ALTER TABLE TagProperties MODIFY COLUMN value LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci;</statement>
2321 <statement mode="plain">ALTER TABLE ImageTagProperties MODIFY COLUMN property TEXT CHARACTER SET utf8 COLLATE utf8_general_ci;</statement>
2322 <statement mode="plain">ALTER TABLE ImageTagProperties MODIFY COLUMN value LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci;</statement>
2323
2324 <statement mode="plain">DROP TABLE IF EXISTS ImageTagProperties_old;</statement>
2325 <statement mode="plain">DROP TABLE IF EXISTS TagProperties_old;</statement>
2326 <statement mode="plain">DROP TABLE IF EXISTS ImageRelations_old;</statement>
2327 <statement mode="plain">DROP TABLE IF EXISTS ImageHistory_old;</statement>
2328 <statement mode="plain">DROP TABLE IF EXISTS Settings_old;</statement>
2329 <statement mode="plain">DROP TABLE IF EXISTS DownloadHistory_old;</statement>
2330 <statement mode="plain">DROP TABLE IF EXISTS ImageProperties_old;</statement>
2331 <statement mode="plain">DROP TABLE IF EXISTS ImageTags_old;</statement>
2332 <statement mode="plain">DROP TABLE IF EXISTS Tags_old;</statement>
2333 <statement mode="plain">DROP TABLE IF EXISTS ImageCopyright_old;</statement>
2334 <statement mode="plain">DROP TABLE IF EXISTS ImageComments_old;</statement>
2335 <statement mode="plain">DROP TABLE IF EXISTS ImagePositions_old;</statement>
2336 <statement mode="plain">DROP TABLE IF EXISTS VideoMetadata_old;</statement>
2337 <statement mode="plain">DROP TABLE IF EXISTS ImageMetadata_old;</statement>
2338 <statement mode="plain">DROP TABLE IF EXISTS ImageInformation_old;</statement>
2339 <statement mode="plain">DROP TABLE IF EXISTS ImageHaarMatrix_old;</statement>
2340 <statement mode="plain">DROP TABLE IF EXISTS Images_old;</statement>
2341 <statement mode="plain">DROP TABLE IF EXISTS Albums_old;</statement>
2342 <statement mode="plain">DROP TABLE IF EXISTS AlbumRoots_old;</statement>
2343
2344 <statement mode="plain">RENAME TABLE AlbumRoots TO AlbumRoots_old;</statement>
2345 <statement mode="plain">CREATE TABLE AlbumRoots LIKE AlbumRoots_old;</statement>
2346 <statement mode="plain">ALTER TABLE AlbumRoots
2347 ADD UNIQUE (identifier(127), specificPath(128)),
2348 ENGINE InnoDB;
2349 </statement>
2350 <statement mode="plain">INSERT IGNORE INTO AlbumRoots SELECT * FROM AlbumRoots_old;</statement>
2351
2352 <statement mode="plain">RENAME TABLE Albums TO Albums_old;</statement>
2353 <statement mode="plain">CREATE TABLE Albums LIKE Albums_old;</statement>
2354 <statement mode="plain">ALTER TABLE Albums
2355 ADD CONSTRAINT Albums_AlbumRoots FOREIGN KEY (albumRoot) REFERENCES AlbumRoots (id) ON DELETE CASCADE ON UPDATE CASCADE,
2356 ADD UNIQUE (albumRoot, relativePath(255)),
2357 ENGINE InnoDB;
2358 </statement>
2359 <statement mode="plain">INSERT IGNORE INTO Albums SELECT * FROM Albums_old;</statement>
2360 <statement mode="plain">UPDATE Albums SET icon = NULL WHERE icon = 0;</statement>
2361
2362 <statement mode="plain">RENAME TABLE Images TO Images_old;</statement>
2363 <statement mode="plain">CREATE TABLE Images LIKE Images_old;</statement>
2364 <statement mode="plain">ALTER TABLE Images
2365 ADD CONSTRAINT Images_Albums FOREIGN KEY (album) REFERENCES Albums (id) ON DELETE CASCADE ON UPDATE CASCADE,
2366 ADD UNIQUE (album, name(255)),
2367 ENGINE InnoDB;
2368 </statement>
2369 <statement mode="plain">INSERT IGNORE INTO Images SELECT * FROM Images_old;</statement>
2370
2371 <statement mode="plain">ALTER TABLE Albums
2372 ADD CONSTRAINT Albums_Images FOREIGN KEY (icon) REFERENCES Images (id) ON DELETE SET NULL ON UPDATE CASCADE;
2373 </statement>
2374
2375 <statement mode="plain">RENAME TABLE ImageHaarMatrix TO ImageHaarMatrix_old;</statement>
2376 <statement mode="plain">CREATE TABLE ImageHaarMatrix LIKE ImageHaarMatrix_old;</statement>
2377 <statement mode="plain">ALTER TABLE ImageHaarMatrix
2378 ADD CONSTRAINT ImageHaarMatrix_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE,
2379 ENGINE InnoDB;
2380 </statement>
2381 <statement mode="plain">INSERT IGNORE INTO ImageHaarMatrix SELECT * FROM ImageHaarMatrix_old;</statement>
2382
2383 <statement mode="plain">RENAME TABLE ImageInformation TO ImageInformation_old;</statement>
2384 <statement mode="plain">CREATE TABLE ImageInformation LIKE ImageInformation_old;</statement>
2385 <statement mode="plain">ALTER TABLE ImageInformation
2386 ADD CONSTRAINT ImageInformation_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE,
2387 ENGINE InnoDB;
2388 </statement>
2389 <statement mode="plain">INSERT IGNORE INTO ImageInformation SELECT * FROM ImageInformation_old;</statement>
2390
2391 <statement mode="plain">RENAME TABLE ImageMetadata TO ImageMetadata_old;</statement>
2392 <statement mode="plain">CREATE TABLE ImageMetadata LIKE ImageMetadata_old;</statement>
2393 <statement mode="plain">ALTER TABLE ImageMetadata
2394 ADD CONSTRAINT ImageMetadata_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE,
2395 ENGINE InnoDB;
2396 </statement>
2397 <statement mode="plain">INSERT IGNORE INTO ImageMetadata SELECT * FROM ImageMetadata_old;</statement>
2398
2399 <statement mode="plain">RENAME TABLE VideoMetadata TO VideoMetadata_old;</statement>
2400 <statement mode="plain">CREATE TABLE VideoMetadata LIKE VideoMetadata_old;</statement>
2401 <statement mode="plain">ALTER TABLE VideoMetadata
2402 ADD CONSTRAINT VideoMetadata_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE,
2403 ENGINE InnoDB;
2404 </statement>
2405 <statement mode="plain">INSERT IGNORE INTO VideoMetadata SELECT * FROM VideoMetadata_old;</statement>
2406
2407 <statement mode="plain">RENAME TABLE ImagePositions TO ImagePositions_old;</statement>
2408 <statement mode="plain">CREATE TABLE ImagePositions LIKE ImagePositions_old;</statement>
2409 <statement mode="plain">ALTER TABLE ImagePositions
2410 ADD CONSTRAINT ImagePositions_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE,
2411 ENGINE InnoDB;
2412 </statement>
2413 <statement mode="plain">INSERT IGNORE INTO ImagePositions SELECT * FROM ImagePositions_old;</statement>
2414
2415 <statement mode="plain">RENAME TABLE ImageComments TO ImageComments_old;</statement>
2416 <statement mode="plain">CREATE TABLE ImageComments LIKE ImageComments_old;</statement>
2417 <statement mode="plain">ALTER TABLE ImageComments
2418 ADD CONSTRAINT ImageComments_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE,
2419 ADD UNIQUE(imageid, type, language, author(202)),
2420 ENGINE InnoDB;
2421 </statement>
2422 <statement mode="plain">INSERT IGNORE INTO ImageComments SELECT * FROM ImageComments_old;</statement>
2423
2424 <statement mode="plain">RENAME TABLE ImageCopyright TO ImageCopyright_old;</statement>
2425 <statement mode="plain">CREATE TABLE ImageCopyright LIKE ImageCopyright_old;</statement>
2426 <statement mode="plain">ALTER TABLE ImageCopyright
2427 ADD CONSTRAINT ImageCopyright_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE,
2428 ADD UNIQUE(imageid, property(110), value(111), extraValue(111)),
2429 ENGINE InnoDB;
2430 </statement>
2431 <statement mode="plain">INSERT IGNORE INTO ImageCopyright SELECT * FROM ImageCopyright_old;</statement>
2432
2433 <statement mode="plain">SET FOREIGN_KEY_CHECKS=0;</statement>
2434 <statement mode="plain">RENAME TABLE Tags TO Tags_old;</statement>
2435 <statement mode="plain">CREATE TABLE Tags LIKE Tags_old;</statement>
2436 <statement mode="plain">ALTER TABLE Tags
2437 ADD CONSTRAINT Tags_Images FOREIGN KEY (icon) REFERENCES Images (id) ON DELETE SET NULL ON UPDATE CASCADE,
2438 ADD UNIQUE(pid, name(100)),
2439 ENGINE InnoDB;
2440 </statement>
2441 <statement mode="plain">REPLACE INTO Tags SELECT * FROM Tags_old;</statement>
2442 <statement mode="plain">SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';</statement>
2443 <statement mode="plain">REPLACE INTO Tags
2444 (id, pid, name, icon, iconkde, lft, rgt) VALUES (0, -1, '_Digikam_root_tag_', NULL, NULL,
2445 (SELECT MIN(tl.lft) FROM Tags AS tl),
2446 (SELECT MAX(tr.rgt) FROM Tags AS tr));
2447 </statement>
2448 <statement mode="plain">SET SQL_MODE=@OLD_SQL_MODE;</statement>
2449 <statement mode="plain">UPDATE Tags SET icon = NULL WHERE icon = 0;</statement>
2450 <statement mode="plain">SET FOREIGN_KEY_CHECKS=1;</statement>
2451
2452 <statement mode="plain">RENAME TABLE ImageTags TO ImageTags_old;</statement>
2453 <statement mode="plain">CREATE TABLE ImageTags LIKE ImageTags_old;</statement>
2454 <statement mode="plain">ALTER TABLE ImageTags
2455 ADD CONSTRAINT ImageTags_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE,
2456 ADD CONSTRAINT ImageTags_Tags FOREIGN KEY (tagid) REFERENCES Tags (id) ON DELETE CASCADE ON UPDATE CASCADE,
2457 ENGINE InnoDB;
2458 </statement>
2459 <statement mode="plain">INSERT IGNORE INTO ImageTags SELECT * FROM ImageTags_old;</statement>
2460
2461 <statement mode="plain">RENAME TABLE ImageProperties TO ImageProperties_old;</statement>
2462 <statement mode="plain">CREATE TABLE ImageProperties LIKE ImageProperties_old;</statement>
2463 <statement mode="plain">ALTER TABLE ImageProperties
2464 ADD CONSTRAINT ImageProperties_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE,
2465 ADD UNIQUE (imageid, property(255)),
2466 ENGINE InnoDB;
2467 </statement>
2468 <statement mode="plain">INSERT IGNORE INTO ImageProperties SELECT * FROM ImageProperties_old;</statement>
2469
2470 <statement mode="plain">ALTER TABLE Searches ENGINE InnoDB;</statement>
2471
2472 <statement mode="plain">RENAME TABLE DownloadHistory TO DownloadHistory_old;</statement>
2473 <statement mode="plain">CREATE TABLE DownloadHistory LIKE DownloadHistory_old;</statement>
2474 <statement mode="plain">ALTER TABLE DownloadHistory
2475 ADD UNIQUE(identifier(164), filename(165), filesize, filedate),
2476 ENGINE InnoDB;
2477 </statement>
2478 <statement mode="plain">INSERT IGNORE INTO DownloadHistory SELECT * FROM DownloadHistory_old;</statement>
2479
2480 <statement mode="plain">RENAME TABLE Settings TO Settings_old;</statement>
2481 <statement mode="plain">CREATE TABLE Settings LIKE Settings_old;</statement>
2482 <statement mode="plain">ALTER TABLE Settings
2483 ADD UNIQUE(keyword(255)),
2484 ENGINE InnoDB;
2485 </statement>
2486 <statement mode="plain">INSERT IGNORE INTO Settings SELECT * FROM Settings_old;</statement>
2487
2488 <statement mode="plain">RENAME TABLE ImageHistory TO ImageHistory_old;</statement>
2489 <statement mode="plain">CREATE TABLE ImageHistory LIKE ImageHistory_old;</statement>
2490 <statement mode="plain">ALTER TABLE ImageHistory
2491 ADD CONSTRAINT ImageHistory_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE,
2492 ENGINE InnoDB;
2493 </statement>
2494 <statement mode="plain">INSERT IGNORE INTO ImageHistory SELECT * FROM ImageHistory_old;</statement>
2495
2496 <statement mode="plain">RENAME TABLE ImageRelations TO ImageRelations_old;</statement>
2497 <statement mode="plain">CREATE TABLE ImageRelations LIKE ImageRelations_old;</statement>
2498 <statement mode="plain">ALTER TABLE ImageRelations
2499 ADD CONSTRAINT ImageRelations_ImagesS FOREIGN KEY (subject) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE,
2500 ADD CONSTRAINT ImageRelations_ImagesO FOREIGN KEY (object) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE,
2501 ENGINE InnoDB;
2502 </statement>
2503 <statement mode="plain">INSERT IGNORE INTO ImageRelations SELECT * FROM ImageRelations_old;</statement>
2504
2505 <statement mode="plain">RENAME TABLE TagProperties TO TagProperties_old;</statement>
2506 <statement mode="plain">CREATE TABLE TagProperties LIKE TagProperties_old;</statement>
2507 <statement mode="plain">ALTER TABLE TagProperties
2508 ADD CONSTRAINT TagProperties_Tags FOREIGN KEY (tagid) REFERENCES Tags (id) ON DELETE CASCADE ON UPDATE CASCADE,
2509 ENGINE InnoDB;
2510 </statement>
2511 <statement mode="plain">INSERT IGNORE INTO TagProperties SELECT * FROM TagProperties_old;</statement>
2512
2513 <statement mode="plain">RENAME TABLE ImageTagProperties TO ImageTagProperties_old;</statement>
2514 <statement mode="plain">CREATE TABLE ImageTagProperties LIKE ImageTagProperties_old;</statement>
2515 <statement mode="plain">ALTER TABLE ImageTagProperties
2516 ADD CONSTRAINT ImageTagProperties_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE,
2517 ADD CONSTRAINT ImageTagProperties_Tags FOREIGN KEY (tagid) REFERENCES Tags (id) ON DELETE CASCADE ON UPDATE CASCADE,
2518 ENGINE InnoDB;
2519 </statement>
2520 <statement mode="plain">INSERT IGNORE INTO ImageTagProperties SELECT * FROM ImageTagProperties_old;</statement>
2521
2522 <statement mode="plain">DROP TABLE ImageTagProperties_old;</statement>
2523 <statement mode="plain">DROP TABLE TagProperties_old;</statement>
2524 <statement mode="plain">DROP TABLE ImageRelations_old;</statement>
2525 <statement mode="plain">DROP TABLE ImageHistory_old;</statement>
2526 <statement mode="plain">DROP TABLE Settings_old;</statement>
2527 <statement mode="plain">DROP TABLE DownloadHistory_old;</statement>
2528 <statement mode="plain">DROP TABLE ImageProperties_old;</statement>
2529 <statement mode="plain">DROP TABLE ImageTags_old;</statement>
2530 <statement mode="plain">DROP TABLE Tags_old;</statement>
2531 <statement mode="plain">DROP TABLE ImageCopyright_old;</statement>
2532 <statement mode="plain">DROP TABLE ImageComments_old;</statement>
2533 <statement mode="plain">DROP TABLE ImagePositions_old;</statement>
2534 <statement mode="plain">DROP TABLE VideoMetadata_old;</statement>
2535 <statement mode="plain">DROP TABLE ImageMetadata_old;</statement>
2536 <statement mode="plain">DROP TABLE ImageInformation_old;</statement>
2537 <statement mode="plain">DROP TABLE ImageHaarMatrix_old;</statement>
2538 <statement mode="plain">DROP TABLE Images_old;</statement>
2539 <statement mode="plain">DROP TABLE Albums_old;</statement>
2540 <statement mode="plain">DROP TABLE AlbumRoots_old;</statement>
2541 </dbaction>
2542
2543 <dbaction name="UpdateSchemaFromV9ToV10" mode="transaction">
2544 <statement mode="plain">DROP TABLE IF EXISTS ImageHaarMatrix;</statement>
2545 <statement mode="plain">ALTER TABLE Images ADD manualOrder INTEGER;</statement>
2546 </dbaction>
2547
2548 <dbaction name="UpdateSchemaFromV10ToV11" mode="transaction">
2549 <statement mode="plain">DROP TRIGGER IF EXISTS temp_insert_tagstree;</statement>
2550 <statement mode="plain">DROP TRIGGER IF EXISTS insert_tagstree;</statement>
2551 <statement mode="plain">DROP TRIGGER IF EXISTS delete_tagstree;</statement>
2552 <statement mode="plain">DROP TRIGGER IF EXISTS move_tagstree;</statement>
2553 <statement mode="plain">DROP TABLE IF EXISTS temp_Tags;</statement>
2554 <statement mode="plain">DROP TABLE IF EXISTS TagsTree;</statement>
2555 <statement mode="plain">DROP VIEW IF EXISTS TagsTree;</statement>
2556
2557 <statement mode="plain">CREATE TABLE IF NOT EXISTS temp_Tags
2558 (id INTEGER,
2559 pid INTEGER,
2560 name LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
2561 icon BIGINT,
2562 iconkde LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
2563 lft INTEGER,
2564 rgt INTEGER)
2565 ENGINE InnoDB;
2566 </statement>
2567
2568 <statement mode="plain">CREATE TABLE IF NOT EXISTS TagsTree
2569 (id INTEGER NOT NULL,
2570 pid INTEGER NOT NULL,
2571 UNIQUE (id, pid))
2572 ENGINE InnoDB;
2573 </statement>
2574
2575 <statement mode="plain">CREATE TRIGGER temp_insert_tagstree AFTER INSERT ON temp_Tags
2576 FOR EACH ROW INSERT INTO TagsTree SELECT NEW.id, NEW.pid
2577 UNION SELECT NEW.id, pid FROM TagsTree WHERE id = NEW.pid;
2578 </statement>
2579
2580 <statement mode="plain">REPLACE INTO temp_Tags SELECT * FROM Tags WHERE id != 0;</statement>
2581 <statement mode="plain">DROP TRIGGER IF EXISTS temp_insert_tagstree;</statement>
2582 <statement mode="plain">DROP TABLE IF EXISTS temp_Tags;</statement>
2583
2584 <statement mode="plain">CREATE TRIGGER insert_tagstree AFTER INSERT ON Tags FOR EACH ROW
2585 INSERT INTO TagsTree SELECT NEW.id, NEW.pid
2586 UNION SELECT NEW.id, pid FROM TagsTree WHERE id = NEW.pid;
2587 </statement>
2588
2589 <statement mode="plain">CREATE TRIGGER delete_tagstree AFTER DELETE ON Tags FOR EACH ROW
2590 BEGIN
2591 DELETE FROM TagsTree WHERE id IN (SELECT id FROM
2592 (SELECT id FROM TagsTree WHERE pid = OLD.id) AS tmpTree1);
2593 DELETE FROM TagsTree WHERE id = OLD.id;
2594 END;
2595 </statement>
2596
2597 <statement mode="plain">CREATE TRIGGER move_tagstree AFTER UPDATE ON Tags FOR EACH ROW
2598 BEGIN
2599 IF (NEW.pid != OLD.pid) THEN
2600 DELETE FROM TagsTree WHERE ((id = OLD.id) OR id IN (SELECT id FROM
2601 (SELECT id FROM TagsTree WHERE pid = OLD.id) AS tmpTree1))
2602 AND pid IN (SELECT pid FROM
2603 (SELECT pid FROM TagsTree WHERE id = OLD.id) AS tmpTree2);
2604 INSERT INTO TagsTree SELECT NEW.id, NEW.pid
2605 UNION SELECT NEW.id, pid FROM TagsTree
2606 WHERE id = NEW.pid
2607 UNION SELECT id, NEW.pid FROM TagsTree
2608 WHERE pid = NEW.id
2609 UNION SELECT A.id, B.pid FROM TagsTree A, TagsTree B
2610 WHERE A.pid = NEW.id AND B.id = NEW.pid;
2611 END IF;
2612 END;
2613 </statement>
2614
2615 <statement mode="plain">ALTER TABLE Tags DROP COLUMN lft;</statement>
2616 <statement mode="plain">ALTER TABLE Tags DROP COLUMN rgt;</statement>
2617 </dbaction>
2618
2619 <dbaction name="UpdateSchemaFromV11ToV12" mode="transaction">
2620 <statement mode="plain">DROP TRIGGER IF EXISTS insert_tagstree;</statement>
2621 <statement mode="plain">DROP TRIGGER IF EXISTS delete_tagstree;</statement>
2622 <statement mode="plain">DROP TRIGGER IF EXISTS move_tagstree;</statement>
2623
2624 <statement mode="plain">CREATE TRIGGER insert_tagstree AFTER INSERT ON Tags FOR EACH ROW
2625 INSERT INTO TagsTree SELECT NEW.id, NEW.pid
2626 UNION SELECT NEW.id, pid FROM TagsTree WHERE id = NEW.pid;
2627 </statement>
2628
2629 <statement mode="plain">CREATE TRIGGER delete_tagstree AFTER DELETE ON Tags FOR EACH ROW
2630 BEGIN
2631 DELETE FROM TagsTree WHERE id IN (SELECT id FROM
2632 (SELECT id FROM TagsTree WHERE pid = OLD.id) AS tmpTree1);
2633 DELETE FROM TagsTree WHERE id = OLD.id;
2634 END;
2635 </statement>
2636
2637 <statement mode="plain">CREATE TRIGGER move_tagstree AFTER UPDATE ON Tags FOR EACH ROW
2638 BEGIN
2639 IF (NEW.pid != OLD.pid) THEN
2640 DELETE FROM TagsTree WHERE ((id = OLD.id) OR id IN (SELECT id FROM
2641 (SELECT id FROM TagsTree WHERE pid = OLD.id) AS tmpTree1))
2642 AND pid IN (SELECT pid FROM
2643 (SELECT pid FROM TagsTree WHERE id = OLD.id) AS tmpTree2);
2644 INSERT INTO TagsTree SELECT NEW.id, NEW.pid
2645 UNION SELECT NEW.id, pid FROM TagsTree
2646 WHERE id = NEW.pid
2647 UNION SELECT id, NEW.pid FROM TagsTree
2648 WHERE pid = NEW.id
2649 UNION SELECT A.id, B.pid FROM TagsTree A, TagsTree B
2650 WHERE A.pid = NEW.id AND B.id = NEW.pid;
2651 END IF;
2652 END;
2653 </statement>
2654 </dbaction>
2655
2656 <dbaction name="UpdateSchemaFromV12ToV13" mode="transaction">
2657 <statement mode="plain">
2658 DROP PROCEDURE IF EXISTS create_index_if_not_exists;
2659 </statement>
2660 <statement mode="plain">
2661 CREATE PROCEDURE create_index_if_not_exists(table_name_vc varchar(50), index_name_vc varchar(50), field_list_vc varchar(1024))
2662 BEGIN
2663
2664 set @Index_cnt = (
2665 SELECT COUNT(1) cnt
2666 FROM INFORMATION_SCHEMA.STATISTICS
2667 WHERE CONVERT(DATABASE() USING latin1) = CONVERT(TABLE_SCHEMA USING latin1)
2668 AND CONVERT(table_name USING latin1) = CONVERT(table_name_vc USING latin1)
2669 AND CONVERT(index_name USING latin1) = CONVERT(index_name_vc USING latin1)
2670 );
2671
2672 IF IFNULL(@Index_cnt, 0) = 0 THEN
2673 set @index_sql = CONCAT(
2674 CONVERT( 'ALTER TABLE ' USING latin1),
2675 CONVERT( table_name_vc USING latin1),
2676 CONVERT( ' ADD INDEX ' USING latin1),
2677 CONVERT( index_name_vc USING latin1),
2678 CONVERT( '(' USING latin1),
2679 CONVERT( field_list_vc USING latin1),
2680 CONVERT( ');' USING latin1)
2681 );
2682 PREPARE stmt FROM @index_sql;
2683 EXECUTE stmt;
2684 DEALLOCATE PREPARE stmt;
2685 END IF;
2686 END;
2687 </statement>
2688 <statement mode="plain">CALL create_index_if_not_exists('TagsTree','tagstree_id_index','id');</statement>
2689 <statement mode="plain">CALL create_index_if_not_exists('TagsTree','tagstree_pid_index','pid');</statement>
2690 </dbaction>
2691
2692 <dbaction name="UpdateSchemaFromV13ToV14" mode="transaction">
2693 <statement mode="plain">ALTER TABLE Albums ADD modificationDate DATETIME;</statement>
2694 </dbaction>
2695
2696 <dbaction name="UpdateSchemaFromV14ToV15" mode="transaction">
2697 <statement mode="plain">ALTER TABLE Albums MODIFY COLUMN relativePath LONGTEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL;</statement>
2698 <statement mode="plain">ALTER TABLE Images MODIFY COLUMN name LONGTEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL;</statement>
2699 </dbaction>
2700
2701 <dbaction name="UpdateSchemaFromV15ToV16" mode="transaction">
2702 <statement mode="plain">ALTER TABLE AlbumRoots ADD caseSensitivity INTEGER;</statement>
2703 <statement mode="plain">UPDATE AlbumRoots SET status=0, caseSensitivity=0;</statement>
2704 <statement mode="plain">DELETE FROM Settings WHERE keyword='Locale';</statement>
2705 </dbaction>
2706
2707 <dbaction name="UpdateThumbnailsDBSchemaFromV1ToV2" mode="transaction">
2708 <statement mode="plain">ALTER TABLE UniqueHashes CHANGE uniqueHash uniqueHash VARCHAR(128);</statement>
2709 <statement mode="plain">CREATE TABLE IF NOT EXISTS CustomIdentifiers
2710 (identifier LONGTEXT CHARACTER SET utf8,
2711 thumbId INTEGER,
2712 UNIQUE(identifier(333)));
2713 </statement>
2714 <statement mode="plain">CALL create_index_if_not_exists('CustomIdentifiers','id_customIdentifiers','thumbId');</statement>
2715 </dbaction>
2716
2717 <dbaction name="UpdateThumbnailsDBSchemaFromV2ToV3" mode="transaction">
2718 <statement mode="plain">ALTER TABLE Settings RENAME ThumbSettings;</statement>
2719 <statement mode="plain">ALTER TABLE ThumbSettings
2720 MODIFY COLUMN keyword VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci,
2721 MODIFY COLUMN value LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci;
2722 </statement>
2723 <statement mode="plain">ALTER TABLE ThumbSettings ENGINE InnoDB;</statement>
2724 <statement mode="plain">ALTER TABLE Thumbnails ENGINE InnoDB;</statement>
2725 <statement mode="plain">ALTER TABLE UniqueHashes ENGINE InnoDB;</statement>
2726 <statement mode="plain">ALTER TABLE FilePaths MODIFY COLUMN path VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci;</statement>
2727 <statement mode="plain">ALTER TABLE FilePaths ENGINE InnoDB;</statement>
2728 <statement mode="plain">ALTER TABLE CustomIdentifiers MODIFY COLUMN identifier VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci;</statement>
2729 <statement mode="plain">ALTER TABLE CustomIdentifiers ENGINE InnoDB;</statement>
2730 <statement mode="plain">ALTER TABLE UniqueHashes
2731 ADD CONSTRAINT UniqueHashes_Thumbnails FOREIGN KEY (thumbId) REFERENCES Thumbnails (id) ON DELETE CASCADE ON UPDATE CASCADE;
2732 </statement>
2733 <statement mode="plain">ALTER TABLE FilePaths
2734 ADD CONSTRAINT FilePaths_Thumbnails FOREIGN KEY (thumbId) REFERENCES Thumbnails (id) ON DELETE CASCADE ON UPDATE CASCADE;
2735 </statement>
2736 <statement mode="plain">ALTER TABLE CustomIdentifiers
2737 ADD CONSTRAINT CustomIdentifiers_Thumbnails FOREIGN KEY (thumbId) REFERENCES Thumbnails (id) ON DELETE CASCADE ON UPDATE CASCADE;
2738 </statement>
2739 </dbaction>
2740
2741 <!-- statements for shrinking the databases -->
2742
2743 <dbaction name="vacuumCoreDB">
2744 <statement mode="query">OPTIMIZE TABLE Albums, Images, ImageInformation, ImageMetadata, VideoMetadata, ImagePositions, ImageComments, ImageCopyright, ImageProperties, ImageHistory, ImageRelations, Tags, ImageTags, ImageTagProperties;</statement>
2745 </dbaction>
2746
2747 <dbaction name="vacuumThumbnailsDB">
2748 <statement mode="query">OPTIMIZE TABLE Thumbnails, UniqueHashes, FilePaths, CustomIdentifiers;</statement>
2749 </dbaction>
2750
2751 <dbaction name="vacuumRecognitionDB">
2752 <statement mode="query">OPTIMIZE TABLE Identities, IdentityAttributes;</statement>
2753 </dbaction>
2754
2755 <dbaction name="vacuumSimilarityDB">
2756 <statement mode="query">OPTIMIZE TABLE ImageSimilarity, ImageHaarMatrix, SimilaritySettings;</statement>
2757 </dbaction>
2758
2759 <dbaction name="checkCoreDbIntegrity">
2760 <statement mode="unprepared">CHECK TABLE Albums, Images, ImageInformation, ImageMetadata, VideoMetadata, ImagePositions, ImageComments, ImageCopyright, ImageProperties, ImageHistory, ImageRelations, Tags, ImageTags, ImageTagProperties;</statement>
2761 </dbaction>
2762
2763 <dbaction name="checkThumbnailsDbIntegrity">
2764 <statement mode="unprepared">CHECK TABLE Thumbnails, UniqueHashes, FilePaths, CustomIdentifiers;</statement>
2765 </dbaction>
2766
2767 <dbaction name="checkRecognitionDbIntegrity">
2768 <statement mode="unprepared">CHECK TABLE Identities, IdentityAttributes;</statement>
2769 </dbaction>
2770
2771 <dbaction name="checkSimilarityDbIntegrity">
2772 <statement mode="unprepared">CHECK TABLE ImageSimilarity, ImageHaarMatrix, SimilaritySettings;</statement>
2773 </dbaction>
2774
2775 </dbactions>
2776
2777 </database>
2778
2779 </databaseconfig>