[Digikam-devel] [PATCH 4/5] Remove trigger dependency for MySQL.
Richard Mortimer
richm at oldelvet.org.uk
Tue Nov 24 09:42:40 GMT 2015
Add full referential integrity to AlbumRoots, Albums, Images and Tags
tables entries.
Provide the equivalent behaviour to the triggers using ON DELETE and
ON UPDATE in FOREIGN KEY references.
---
data/database/dbconfig.xml.cmake.in | 82 ++++++++++++++++++-------------------
1 file changed, 40 insertions(+), 42 deletions(-)
diff --git a/data/database/dbconfig.xml.cmake.in b/data/database/dbconfig.xml.cmake.in
index 42a6947..f2a951e 100644
--- a/data/database/dbconfig.xml.cmake.in
+++ b/data/database/dbconfig.xml.cmake.in
@@ -859,14 +859,12 @@
<!-- Mysql check privileges actions -->
<dbaction name="CheckPriv_CREATE_TRIGGER"><statement mode="plain">
- CREATE TRIGGER privcheck_trigger AFTER DELETE ON PrivCheck
- FOR EACH ROW BEGIN
- END;
+ SELECT 1;
</statement>
</dbaction>
<dbaction name="CheckPriv_DROP_TRIGGER"><statement mode="plain">
- DROP TRIGGER privcheck_trigger;
+ SELECT 1;
</statement>
</dbaction>
@@ -913,6 +911,7 @@
caption LONGTEXT CHARACTER SET utf8,
collection LONGTEXT CHARACTER SET utf8,
icon INTEGER,
+ CONSTRAINT Albums_AlbumRoots FOREIGN KEY (albumRoot) REFERENCES AlbumRoots (id) ON DELETE CASCADE ON UPDATE CASCADE,
UNIQUE(albumRoot, relativePath(255)));</statement>
<statement mode="plain">CREATE TABLE IF NOT EXISTS Images
(id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
@@ -923,12 +922,16 @@
modificationDate DATETIME,
fileSize INTEGER,
uniqueHash VARCHAR(128),
+ CONSTRAINT Images_Albums FOREIGN KEY (album) REFERENCES Albums (id) ON DELETE CASCADE ON UPDATE CASCADE,
UNIQUE (album, name(255)));</statement>
+ <statement mode="plain">ALTER TABLE Albums
+ ADD CONSTRAINT Albums_Images FOREIGN KEY (icon) REFERENCES Images (id) ON DELETE SET NULL ON UPDATE CASCADE;</statement>
<statement mode="plain">CREATE TABLE IF NOT EXISTS ImageHaarMatrix
(imageid INTEGER PRIMARY KEY,
modificationDate DATETIME,
uniqueHash LONGTEXT CHARACTER SET utf8,
- matrix LONGBLOB);</statement>
+ matrix LONGBLOB,
+ CONSTRAINT ImageHaarMatrix_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE);</statement>
<statement mode="plain">CREATE TABLE IF NOT EXISTS ImageInformation
(imageid INTEGER PRIMARY KEY,
rating INTEGER,
@@ -939,7 +942,8 @@
height INTEGER,
format LONGTEXT CHARACTER SET utf8,
colorDepth INTEGER,
- colorModel INTEGER);</statement>
+ colorModel INTEGER,
+ CONSTRAINT ImageInformation_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE);</statement>
<statement mode="plain">CREATE TABLE IF NOT EXISTS ImageMetadata
(imageid INTEGER PRIMARY KEY,
make LONGTEXT CHARACTER SET utf8,
@@ -957,7 +961,8 @@
whiteBalanceColorTemperature INTEGER,
meteringMode INTEGER,
subjectDistance REAL,
- subjectDistanceCategory INTEGER);</statement>
+ subjectDistanceCategory INTEGER,
+ CONSTRAINT ImageMetadata_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE);</statement>
<statement mode="plain">CREATE TABLE IF NOT EXISTS VideoMetadata
(imageid INTEGER PRIMARY KEY,
aspectRatio TEXT,
@@ -967,7 +972,8 @@
duration TEXT,
frameRate TEXT,
exposureProgram INTEGER,
- videoCodec TEXT);</statement>
+ videoCodec TEXT,
+ CONSTRAINT VideoMetadata_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE);</statement>
<statement mode="plain">CREATE TABLE IF NOT EXISTS ImagePositions
(imageid INTEGER PRIMARY KEY,
latitude LONGTEXT CHARACTER SET utf8,
@@ -979,7 +985,8 @@
tilt REAL,
roll REAL,
accuracy REAL,
- description LONGTEXT CHARACTER SET utf8);</statement>
+ description LONGTEXT CHARACTER SET utf8,
+ CONSTRAINT ImagePositions_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE);</statement>
<statement mode="plain">CREATE TABLE IF NOT EXISTS ImageComments
(id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
imageid INTEGER,
@@ -988,6 +995,7 @@
author LONGTEXT CHARACTER SET utf8,
date DATETIME,
comment LONGTEXT CHARACTER SET utf8,
+ CONSTRAINT ImageComments_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE,
UNIQUE(imageid, type, language, author(202)));</statement>
<statement mode="plain">CREATE TABLE IF NOT EXISTS ImageCopyright
(id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
@@ -995,6 +1003,7 @@
property LONGTEXT CHARACTER SET utf8,
value LONGTEXT CHARACTER SET utf8,
extraValue LONGTEXT CHARACTER SET utf8,
+ CONSTRAINT ImageCopyright_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE,
UNIQUE(imageid, property(110), value(111), extraValue(111)));</statement>
<statement mode="plain">CREATE TABLE IF NOT EXISTS Tags
(id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
@@ -1003,16 +1012,20 @@
icon INTEGER,
iconkde LONGTEXT CHARACTER SET utf8,
lft INT NOT NULL,
- rgt INT NOT NULL
+ rgt INT NOT NULL,
+ CONSTRAINT Tags_Images FOREIGN KEY (icon) REFERENCES Images (id) ON DELETE SET NULL ON UPDATE CASCADE
);</statement>
<statement mode="plain">CREATE TABLE IF NOT EXISTS ImageTags
(imageid INTEGER NOT NULL,
tagid INTEGER NOT NULL,
+ CONSTRAINT ImageTags_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT ImageTags_Tags FOREIGN KEY (tagid) REFERENCES Tags (id) ON DELETE CASCADE ON UPDATE CASCADE,
UNIQUE (imageid, tagid));</statement>
<statement mode="plain"> CREATE TABLE IF NOT EXISTS ImageProperties
(imageid INTEGER NOT NULL,
property LONGTEXT CHARACTER SET utf8 NOT NULL,
value LONGTEXT CHARACTER SET utf8 NOT NULL,
+ CONSTRAINT ImageProperties_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE,
UNIQUE (imageid, property(255)));</statement>
<statement mode="plain">CREATE TABLE IF NOT EXISTS Searches
(id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
@@ -1033,21 +1046,27 @@
<statement mode="plain">CREATE TABLE IF NOT EXISTS ImageHistory
(imageid INTEGER PRIMARY KEY,
uuid VARCHAR(128),
- history LONGTEXT CHARACTER SET utf8);</statement>
+ history LONGTEXT CHARACTER SET utf8,
+ CONSTRAINT ImageHistory_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE);</statement>
<statement mode="plain">CREATE TABLE IF NOT EXISTS ImageRelations
(subject INTEGER,
object INTEGER,
type INTEGER,
+ CONSTRAINT ImageRelations_ImagesS FOREIGN KEY (subject) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT ImageRelations_ImagesO FOREIGN KEY (object) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE,
UNIQUE(subject, object, type));</statement>
<statement mode="plain">CREATE TABLE IF NOT EXISTS TagProperties
(tagid INTEGER,
property TEXT CHARACTER SET utf8,
- value LONGTEXT CHARACTER SET utf8);</statement>
+ value LONGTEXT CHARACTER SET utf8,
+ CONSTRAINT TagProperties_Tags FOREIGN KEY (tagid) REFERENCES Tags (id) ON DELETE CASCADE ON UPDATE CASCADE);</statement>
<statement mode="plain">CREATE TABLE IF NOT EXISTS ImageTagProperties
(imageid INTEGER,
tagid INTEGER,
property TEXT CHARACTER SET utf8,
- value LONGTEXT CHARACTER SET utf8);</statement>
+ value LONGTEXT CHARACTER SET utf8,
+ CONSTRAINT ImageTagProperties_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT ImageTagProperties_Tags FOREIGN KEY (tagid) REFERENCES Tags (id) ON DELETE CASCADE ON UPDATE CASCADE);</statement>
<statement mode="plain">
CREATE OR REPLACE VIEW TagsTree
AS
@@ -1109,34 +1128,9 @@
<!-- Mysql Core Triggers -->
<dbaction name="CreateTriggers" mode="transaction">
- <statement mode="plain">DROP TRIGGER IF EXISTS delete_image;</statement>
- <statement mode="plain">CREATE TRIGGER delete_image AFTER DELETE ON Images
- FOR EACH ROW BEGIN
- DELETE FROM ImageTags WHERE imageid=OLD.id;
- DELETE From ImageHaarMatrix WHERE imageid=OLD.id;
- DELETE From ImageInformation WHERE imageid=OLD.id;
- DELETE From ImageMetadata WHERE imageid=OLD.id;
- DELETE From VideoMetadata WHERE imageid=OLD.id;
- DELETE From ImagePositions WHERE imageid=OLD.id;
- DELETE From ImageComments WHERE imageid=OLD.id;
- DELETE From ImageCopyright WHERE imageid=OLD.id;
- DELETE From ImageProperties WHERE imageid=OLD.id;
- DELETE From ImageHistory WHERE imageid=OLD.id;
- DELETE FROM ImageRelations WHERE subject=OLD.id OR object=OLD.id;
- DELETE FROM ImageTagProperties WHERE imageid=OLD.id;
- UPDATE Albums SET icon=null WHERE icon=OLD.id;
- UPDATE Tags SET icon=null WHERE icon=OLD.id;
- END;
- </statement>
- <statement mode="plain">DROP TRIGGER IF EXISTS delete_tag;</statement>
- <statement mode="plain">CREATE TRIGGER delete_tag AFTER DELETE ON Tags
- FOR EACH ROW BEGIN
- DELETE FROM ImageTags WHERE tagid=OLD.id;
- DELETE FROM TagProperties WHERE tagid=OLD.id;
- DELETE FROM ImageTagProperties WHERE tagid=OLD.id;
- END;
- </statement>
+<!-- Note move_tagstree does not exist for mysql
<statement mode="plain">DROP TRIGGER IF EXISTS move_tagstree;</statement>
+-->
<statement mode="plain">SELECT
@minLeft := IF(ISNULL(MIN(lft)), 1, MIN(lft)-1),
@@ -1148,7 +1142,7 @@
<statement mode="plain">REPLACE INTO Tags
(id, pid, name, icon, iconkde, lft, rgt)
VALUES
- (0, -1, '_Digikam_root_tag_', 0, NULL, @minLeft, @maxRight )
+ (0, -1, '_Digikam_root_tag_', NULL, NULL, @minLeft, @maxRight )
</statement>
<statement mode="plain">SET SQL_MODE=@OLD_SQL_MODE;</statement>
</dbaction>
@@ -1441,7 +1435,8 @@ ORDER BY inf.rating DESC, img.name ASC
(id INTEGER,
type INTEGER,
attribute TEXT,
- value TEXT);
+ value TEXT,
+ CONSTRAINT IdentityAttributes_Identities FOREIGN KEY (id) REFERENCES Identities (id) ON DELETE CASCADE ON UPDATE CASCADE);
</statement>
<statement mode="plain">CREATE TABLE IF NOT EXISTS Settings
(keyword LONGTEXT CHARACTER SET utf8 NOT NULL,
@@ -1523,12 +1518,14 @@ ORDER BY inf.rating DESC, img.name ASC
<!-- Mysql Face Triggers -->
<dbaction name="CreateFaceTriggers" mode="transaction">
+<!--
<statement mode="plain">DROP TRIGGER IF EXISTS delete_identities;</statement>
<statement mode="plain">CREATE TRIGGER delete_identities AFTER DELETE ON Identities
FOR EACH ROW BEGIN
DELETE FROM IdentityAttributes WHERE id=OLD.id;
END;
</statement>
+-->
</dbaction>
<!-- Mysql Migration Statements -->
@@ -1544,6 +1541,7 @@ ORDER BY inf.rating DESC, img.name ASC
<dbaction name="Migrate_Read_Albums"><statement mode="query">
SELECT id, albumRoot, relativePath, date, caption, collection, icon FROM Albums;
</statement></dbaction>
+ <!-- Note Albums with an icon set will probably fail to migrate due to referential integrity checks. The icons should probably be set after images are migrated. -->
<dbaction name="Migrate_Write_Albums" mode="transaction"><statement mode="query">
INSERT INTO Albums (id, albumRoot, relativePath, date, caption, collection, icon) VALUES (:id, :albumRoot, :relativePath, :date, :caption, :collection, :icon);
</statement></dbaction>
--
2.5.0
More information about the Digikam-devel
mailing list