From 5893fde69518d70904d2b96e13800c7432b4e4c4 Mon Sep 17 00:00:00 2001 From: Maik Qualmann Date: Mon, 15 Jan 2018 20:24:41 +0100 Subject: [PATCH 04/32] fix schema update from V7/8 to V9 with temporary tables BUGS: 388977 BUGS: 388867 BUGS: 388824 FIXED-IN: 5.9.0 --- NEWS | 6 +- data/database/dbconfig.xml.cmake.in | 154 +++++++++++++++++++++++++++++------- 2 files changed, 128 insertions(+), 32 deletions(-) diff --git a/data/database/dbconfig.xml.cmake.in b/data/database/dbconfig.xml.cmake.in index a315361218..2c5008fe38 100644 --- a/data/database/dbconfig.xml.cmake.in +++ b/data/database/dbconfig.xml.cmake.in @@ -1992,8 +1992,6 @@ END IF; END; - SET SESSION OLD_ALTER_TABLE=1; - SET SESSION FOREIGN_KEY_CHECKS=0; DROP TRIGGER IF EXISTS delete_image; DROP TRIGGER IF EXISTS delete_tag; DROP TRIGGER IF EXISTS move_tagstree; @@ -2064,85 +2062,181 @@ ALTER TABLE TagProperties MODIFY COLUMN value LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE ImageTagProperties MODIFY COLUMN property TEXT CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE ImageTagProperties MODIFY COLUMN value LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci; - ALTER IGNORE TABLE AlbumRoots + + RENAME TABLE AlbumRoots TO AlbumRoots_old; + CREATE TABLE AlbumRoots LIKE AlbumRoots_old; + ALTER TABLE AlbumRoots ADD UNIQUE (identifier(127), specificPath(128)), ENGINE InnoDB; - ALTER IGNORE TABLE Albums + INSERT IGNORE INTO AlbumRoots SELECT * FROM AlbumRoots_old; + + RENAME TABLE Albums TO Albums_old; + CREATE TABLE Albums LIKE Albums_old; + ALTER TABLE Albums ADD CONSTRAINT Albums_AlbumRoots FOREIGN KEY (albumRoot) REFERENCES AlbumRoots (id) ON DELETE CASCADE ON UPDATE CASCADE, ADD UNIQUE (albumRoot, relativePath(255)), ENGINE InnoDB; - ALTER IGNORE TABLE Images + INSERT IGNORE INTO Albums SELECT * FROM Albums_old; + UPDATE Albums SET icon = NULL WHERE icon = 0; + + RENAME TABLE Images TO Images_old; + CREATE TABLE Images LIKE Images_old; + ALTER TABLE Images ADD CONSTRAINT Images_Albums FOREIGN KEY (album) REFERENCES Albums (id) ON DELETE CASCADE ON UPDATE CASCADE, ADD UNIQUE (album, name(255)), ENGINE InnoDB; - UPDATE Albums SET icon = NULL WHERE icon = 0; - ALTER IGNORE TABLE Albums + INSERT IGNORE INTO Images SELECT * FROM Images_old; + + ALTER TABLE Albums ADD CONSTRAINT Albums_Images FOREIGN KEY (icon) REFERENCES Images (id) ON DELETE SET NULL ON UPDATE CASCADE; - ALTER IGNORE TABLE ImageHaarMatrix + + RENAME TABLE ImageHaarMatrix TO ImageHaarMatrix_old; + CREATE TABLE ImageHaarMatrix LIKE ImageHaarMatrix_old; + ALTER TABLE ImageHaarMatrix ADD CONSTRAINT ImageHaarMatrix_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE, ENGINE InnoDB; - ALTER IGNORE TABLE ImageInformation + INSERT IGNORE INTO ImageHaarMatrix SELECT * FROM ImageHaarMatrix_old; + + RENAME TABLE ImageInformation TO ImageInformation_old; + CREATE TABLE ImageInformation LIKE ImageInformation_old; + ALTER TABLE ImageInformation ADD CONSTRAINT ImageInformation_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE, ENGINE InnoDB; - ALTER IGNORE TABLE ImageMetadata + INSERT IGNORE INTO ImageInformation SELECT * FROM ImageInformation_old; + + RENAME TABLE ImageMetadata TO ImageMetadata_old; + CREATE TABLE ImageMetadata LIKE ImageMetadata_old; + ALTER TABLE ImageMetadata ADD CONSTRAINT ImageMetadata_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE, ENGINE InnoDB; - ALTER IGNORE TABLE VideoMetadata + INSERT IGNORE INTO ImageMetadata SELECT * FROM ImageMetadata_old; + + RENAME TABLE VideoMetadata TO VideoMetadata_old; + CREATE TABLE VideoMetadata LIKE VideoMetadata_old; + ALTER TABLE VideoMetadata ADD CONSTRAINT VideoMetadata_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE, ENGINE InnoDB; - ALTER IGNORE TABLE ImagePositions + INSERT IGNORE INTO VideoMetadata SELECT * FROM VideoMetadata_old; + + RENAME TABLE ImagePositions TO ImagePositions_old; + CREATE TABLE ImagePositions LIKE ImagePositions_old; + ALTER TABLE ImagePositions ADD CONSTRAINT ImagePositions_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE, ENGINE InnoDB; - ALTER IGNORE TABLE ImageComments + INSERT IGNORE INTO ImagePositions SELECT * FROM ImagePositions_old; + + RENAME TABLE ImageComments TO ImageComments_old; + CREATE TABLE ImageComments LIKE ImageComments_old; + ALTER TABLE ImageComments ADD CONSTRAINT ImageComments_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE, ADD UNIQUE(imageid, type, language, author(202)), ENGINE InnoDB; - ALTER IGNORE TABLE ImageCopyright + INSERT IGNORE INTO ImageComments SELECT * FROM ImageComments_old; + + RENAME TABLE ImageCopyright TO ImageCopyright_old; + CREATE TABLE ImageCopyright LIKE ImageCopyright_old; + ALTER TABLE ImageCopyright ADD CONSTRAINT ImageCopyright_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE, ADD UNIQUE(imageid, property(110), value(111), extraValue(111)), ENGINE InnoDB; + INSERT IGNORE INTO ImageCopyright SELECT * FROM ImageCopyright_old; + + RENAME TABLE Tags TO Tags_old; + CREATE TABLE Tags LIKE Tags_old; + ALTER TABLE Tags + ADD CONSTRAINT Tags_Images FOREIGN KEY (icon) REFERENCES Images (id) ON DELETE SET NULL ON UPDATE CASCADE, + ADD UNIQUE(pid, name(100)), + ENGINE InnoDB; + INSERT IGNORE INTO Tags SELECT * FROM Tags_old; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'; REPLACE INTO Tags (id, pid, name, icon, iconkde, lft, rgt) VALUES (0, -1, '_Digikam_root_tag_', NULL, NULL, (SELECT MIN(tl.lft) FROM Tags AS tl), (SELECT MAX(tr.rgt) FROM Tags AS tr)); SET SQL_MODE=@OLD_SQL_MODE; - UPDATE Tags set icon = NULL WHERE icon = 0; - ALTER IGNORE TABLE Tags - ADD CONSTRAINT Tags_Images FOREIGN KEY (icon) REFERENCES Images (id) ON DELETE SET NULL ON UPDATE CASCADE, - ADD UNIQUE(pid, name(100)), - ENGINE InnoDB; - ALTER IGNORE TABLE ImageTags + UPDATE Tags SET icon = NULL WHERE icon = 0; + + RENAME TABLE ImageTags TO ImageTags_old; + CREATE TABLE ImageTags LIKE ImageTags_old; + ALTER TABLE ImageTags ADD CONSTRAINT ImageTags_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT ImageTags_Tags FOREIGN KEY (tagid) REFERENCES Tags (id) ON DELETE CASCADE ON UPDATE CASCADE, ENGINE InnoDB; - ALTER IGNORE TABLE ImageProperties + INSERT IGNORE INTO ImageTags SELECT * FROM ImageTags_old; + + RENAME TABLE ImageProperties TO ImageProperties_old; + CREATE TABLE ImageProperties LIKE ImageProperties_old; + ALTER TABLE ImageProperties ADD CONSTRAINT ImageProperties_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE, ADD UNIQUE (imageid, property(255)), ENGINE InnoDB; - ALTER IGNORE TABLE Searches + INSERT IGNORE INTO ImageProperties SELECT * FROM ImageProperties_old; + + ALTER TABLE Searches ENGINE InnoDB; - ALTER IGNORE TABLE DownloadHistory + + RENAME TABLE DownloadHistory TO DownloadHistory_old; + CREATE TABLE DownloadHistory LIKE DownloadHistory_old; + ALTER TABLE DownloadHistory ADD UNIQUE(identifier(164), filename(165), filesize, filedate), ENGINE InnoDB; - ALTER IGNORE TABLE Settings + INSERT IGNORE INTO DownloadHistory SELECT * FROM DownloadHistory_old; + + RENAME TABLE Settings TO Settings_old; + CREATE TABLE Settings LIKE Settings_old; + ALTER TABLE Settings ADD UNIQUE(keyword(255)), ENGINE InnoDB; - ALTER IGNORE TABLE ImageHistory + INSERT IGNORE INTO Settings SELECT * FROM Settings_old; + + RENAME TABLE ImageHistory TO ImageHistory_old; + CREATE TABLE ImageHistory LIKE ImageHistory_old; + ALTER TABLE ImageHistory ADD CONSTRAINT ImageHistory_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE, ENGINE InnoDB; - ALTER IGNORE TABLE ImageRelations + INSERT IGNORE INTO ImageHistory SELECT * FROM ImageHistory_old; + + RENAME TABLE ImageRelations TO ImageRelations_old; + CREATE TABLE ImageRelations LIKE ImageRelations_old; + ALTER TABLE ImageRelations ADD CONSTRAINT ImageRelations_ImagesS FOREIGN KEY (subject) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT ImageRelations_ImagesO FOREIGN KEY (object) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE, ENGINE InnoDB; - ALTER IGNORE TABLE TagProperties + INSERT IGNORE INTO ImageRelations SELECT * FROM ImageRelations_old; + + RENAME TABLE TagProperties TO TagProperties_old; + CREATE TABLE TagProperties LIKE TagProperties_old; + ALTER TABLE TagProperties ADD CONSTRAINT TagProperties_Tags FOREIGN KEY (tagid) REFERENCES Tags (id) ON DELETE CASCADE ON UPDATE CASCADE, ENGINE InnoDB; - ALTER IGNORE TABLE ImageTagProperties + INSERT IGNORE INTO TagProperties SELECT * FROM TagProperties_old; + + RENAME TABLE ImageTagProperties TO ImageTagProperties_old; + CREATE TABLE ImageTagProperties LIKE ImageTagProperties_old; + ALTER TABLE ImageTagProperties ADD CONSTRAINT ImageTagProperties_Images FOREIGN KEY (imageid) REFERENCES Images (id) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT ImageTagProperties_Tags FOREIGN KEY (tagid) REFERENCES Tags (id) ON DELETE CASCADE ON UPDATE CASCADE, ENGINE InnoDB; - SET SESSION FOREIGN_KEY_CHECKS=1; - SET SESSION OLD_ALTER_TABLE=0; + INSERT IGNORE INTO ImageTagProperties SELECT * FROM ImageTagProperties_old; + + DROP TABLE ImageTagProperties_old; + DROP TABLE TagProperties_old; + DROP TABLE ImageRelations_old; + DROP TABLE ImageHistory_old; + DROP TABLE Settings_old; + DROP TABLE DownloadHistory_old; + DROP TABLE ImageProperties_old; + DROP TABLE ImageTags_old; + DROP TABLE Tags_old; + DROP TABLE ImageCopyright_old; + DROP TABLE ImageComments_old; + DROP TABLE ImagePositions_old; + DROP TABLE VideoMetadata_old; + DROP TABLE ImageMetadata_old; + DROP TABLE ImageInformation_old; + DROP TABLE ImageHaarMatrix_old; + DROP TABLE Images_old; + DROP TABLE Albums_old; + DROP TABLE AlbumRoots_old; -- 2.14.3