[Digikam-users] MySQL DB migration

[Ignatius Reilly]

OK, I gave another try at migrating from SQLite to MySQL after upgrading
to Ubuntu 11.10 and the DK 2.2.0 version from the Philip Jonhson ppa

It works, sort of: the digikam DB is correctly migrated, but not the
thumbnails one

For the thumbnails DB I extracted and adapted for MySQL the DDL from
SQLite Manager, and inserted the necessary settings value. Here is the
result:

-- phpMyAdmin SQL Dump
-- version 3.3.8.1
-- http://www.phpmyadmin.net
--
-- Host: localhost:3306
-- Generation Time: Oct 30, 2011 at 06:58 PM
-- Server version: 5.1.58
-- PHP Version: 5.3.6-13ubuntu3.2

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `thumbnails_digikam21`
--

-- --------------------------------------------------------

--
-- Table structure for table `CustomIdentifiers`
--

CREATE TABLE IF NOT EXISTS `CustomIdentifiers` (
  `identifier` varchar(256) DEFAULT NULL,
  `thumbId` int(11) DEFAULT NULL,
  UNIQUE KEY `identifier` (`identifier`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `FilePaths`
--

CREATE TABLE IF NOT EXISTS `FilePaths` (
  `path` longtext CHARACTER SET utf8,
  `thumbId` int(11) DEFAULT NULL,
  UNIQUE KEY `path` (`path`(333)),
  KEY `id_filePaths` (`thumbId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `Settings`
--

CREATE TABLE IF NOT EXISTS `Settings` (
  `keyword` longtext CHARACTER SET utf8 NOT NULL,
  `value` longtext CHARACTER SET utf8,
  UNIQUE KEY `keyword` (`keyword`(333))
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `Thumbnails`
--

CREATE TABLE IF NOT EXISTS `Thumbnails` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` int(11) DEFAULT NULL,
  `modificationDate` datetime DEFAULT NULL,
  `orientationHint` int(11) DEFAULT NULL,
  `data` longblob,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=17999 ;

--
-- Triggers `Thumbnails`
--
DROP TRIGGER IF EXISTS `delete_thumbnails`;
DELIMITER //
CREATE TRIGGER `delete_thumbnails` AFTER DELETE ON `Thumbnails`
 FOR EACH ROW BEGIN
                                DELETE FROM UniqueHashes WHERE
UniqueHashes.thumbId = OLD.id;
                                DELETE FROM FilePaths WHERE
FilePaths.thumbId = OLD.id;
                                DELETE FROM CustomIdentifiers WHERE
CustomIdentifiers.thumbId = OLD.id;
                                END
//
DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `UniqueHashes`
--

CREATE TABLE IF NOT EXISTS `UniqueHashes` (
  `uniqueHash` varchar(32) DEFAULT NULL,
  `fileSize` int(11) DEFAULT NULL,
  `thumbId` int(11) DEFAULT NULL,
  UNIQUE KEY `uniqueHash` (`uniqueHash`,`fileSize`),
  KEY `id_uniqueHashes` (`thumbId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Then you can rebuild your thumbnails from DK

HTH
Ignatius