[Digikam-devel] [Bug 283502] database upgrade v5 to v6 failed

[Francesco Riosa]

https://bugs.kde.org/show_bug.cgi?id=283502





--- Comment #3 from Francesco Riosa <francesco+kde pnpitalia it>  2011-10-07 18:27:37 ---
The problem should be that "images metadata" and "thumbnails" databases are not
very well isolated in digikam code relevant to mysql. Waiting for a fix (which
will take some time) you can either:

a) use the same schema for metadata and thumbnails
b) manually run the following queries (from dbconfig.xml) in the thumbnail
schema
I would suggest a)

CREATE TABLE IF NOT EXISTS Thumbnails
            (id INTEGER PRIMARY KEY AUTO_INCREMENT,
            type INTEGER,
            modificationDate DATETIME,
            orientationHint INTEGER,
            data LONGBLOB);

CREATE TABLE IF NOT EXISTS UniqueHashes
            (uniqueHash VARCHAR(128),
            fileSize INTEGER,
            thumbId INTEGER,
            UNIQUE(uniqueHash, fileSize));

CREATE TABLE IF NOT EXISTS FilePaths
            (path LONGTEXT CHARACTER SET utf8,
            thumbId INTEGER,
            UNIQUE(path(255)));

CREATE TABLE IF NOT EXISTS CustomIdentifiers
            (identifier LONGTEXT CHARACTER SET utf8,
            thumbId INTEGER,
            UNIQUE(identifier(255)));

CREATE TABLE IF NOT EXISTS Settings
            (keyword LONGTEXT CHARACTER SET utf8 NOT NULL,
            value LONGTEXT CHARACTER SET utf8,
            UNIQUE(keyword(255)));


            INSERT INTO Settings (keyword, value)
            VALUES('DBThumbnailsVersionRequired','1')
            ON DUPLICATE KEY
            UPDATE value = GREATEST(VALUES(value), 1);


            INSERT INTO Settings (keyword, value)
            VALUES('DBThumbnailsVersion','2')
            ON DUPLICATE KEY
            UPDATE value = GREATEST(VALUES(value), 2);


    DROP PROCEDURE IF EXISTS create_index_if_not_exists;

    DELIMITER ;;

    CREATE PROCEDURE create_index_if_not_exists(table_name_vc varchar(50),
index_name_vc varchar(50), field_list_vc varchar(1024))
    SQL SECURITY INVOKER
    BEGIN

    set @Index_cnt = (
        SELECT COUNT(1) cnt
        FROM INFORMATION_SCHEMA.STATISTICS
        WHERE CONVERT(DATABASE() USING latin1) = CONVERT(TABLE_SCHEMA USING
latin1)
        AND CONVERT(table_name USING latin1) = CONVERT(table_name_vc USING
latin1)
        AND CONVERT(index_name USING latin1) = CONVERT(index_name_vc USING
latin1)
    );

    IF IFNULL(@Index_cnt, 0) = 0 THEN
        set @index_sql = CONCAT( 
            CONVERT( 'ALTER TABLE ' USING latin1),
            CONVERT( table_name_vc USING latin1),
            CONVERT( ' ADD INDEX ' USING latin1),
            CONVERT( index_name_vc USING latin1),
            CONVERT( '(' USING latin1),
            CONVERT( field_list_vc USING latin1),
            CONVERT( ');' USING latin1)
        );
        PREPARE stmt FROM @index_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
    END;;

CALL create_index_if_not_exists('UniqueHashes','id_uniqueHashes','thumbId');
CALL create_index_if_not_exists('FilePaths','id_filePaths','thumbId');
CALL
create_index_if_not_exists('CustomIdentifiers','id_customIdentifiers','thumbId');

-- 
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.