path: root/digikam/digikam/upgradedb_sqlite2tosqlite3.cpp

/* ============================================================
 *
 * This file is a part of digiKam project
 * http://www.digikam.org
 *
 * Date        : 2005-06-05
 * Description : SQlite 2 to SQlite 3 interface.
 *
 * Copyright (C) 2005 by Renchi Raju <renchi@pooh.tam.uiuc.edu>
 *
 * This program is free software; you can redistribute it
 * and/or modify it under the terms of the GNU General
 * Public License as published by the Free Software Foundation;
 * either version 2, or (at your option)
 * any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * ============================================================ */

// TQt includes.

#include <tqmap.h>
#include <tqpair.h>
#include <tqdir.h>
#include <tqstringlist.h>
#include <tqfileinfo.h>

// KDE includes.

#include <kstandarddirs.h>
#include <kio/global.h>
#include <iostream>

// Local includes.

#include "ddebug.h"
#include "albumdb.h"
#include "albumdb_sqlite2.h"
#include "upgradedb_sqlite2tosqlite3.h"

namespace Digikam
{

struct _Album
{
    int     id;
    TQString url;
    TQString date;
    TQString caption;
    TQString collection;
    TQString icon;
};

struct _Tag
{
    int     id;
    int     pid;
    TQString name;
    TQString icon;
};

static TQString escapeString(TQString str)
{
    str.replace( "'", "''" );
    return str;
}

TQ_LLONG findOrAddImage(AlbumDB* db, int dirid, const TQString& name,
               const TQString& caption)
{
    TQStringList values;
    
    db->execSql(TQString("SELECT id FROM Images WHERE dirid=%1 AND name='%2'")
        .tqarg(dirid)
        .tqarg(escapeString(name)), &values);

    if (!values.isEmpty())
    {
    return values.first().toLongLong();
    }
    
    db->execSql(TQString("INSERT INTO Images (dirid, name, caption) \n "
            "VALUES(%1, '%2', '%3');")
        .tqarg(dirid)
        .tqarg(escapeString(name))
        .tqarg(escapeString(caption)), &values);

    return db->lastInsertedRow();
}


bool upgradeDB_Sqlite2ToSqlite3(const TQString& _libraryPath)
{
    TQString libraryPath = TQDir::cleanDirPath(_libraryPath);

    TQString newDB= libraryPath + "/digikam3.db";

#ifdef NFS_HACK
    newDB = locateLocal("appdata", KIO::encodeFileName(TQDir::cleanDirPath(newDB)));
    DDebug() << "NFS: " << newDB << endl;
#endif
    
    AlbumDB db3;
    db3.setDBPath(newDB);
    if (!db3.isValid())
    {
    DWarning() << "Failed to open new Album Database" << endl;
    DWarning() << "The directory <" << libraryPath << "> may not exist or is write-protected" << endl;
    return false;
    }

    if (db3.getSetting("UpgradedFromSqlite2") == "yes")
    return true;

    TQString dbPath = libraryPath + "/digikam.db";

#ifdef NFS_HACK
    dbPath = locateLocal("appdata", KIO::encodeFileName(TQDir::cleanDirPath(dbPath)));
    DDebug() << "From NFS: " << dbPath << endl;
#endif
        
    TQFileInfo fi(dbPath);

    if (!fi.exists())
    {
    DDebug() << "No old database present. Not upgrading" << endl;
    db3.setSetting("UpgradedFromSqlite2", "yes");
    return true;
    }

    AlbumDB_Sqlite2 db2;
    db2.setDBPath( dbPath );
    if (!db2.isValid())
    {
    DDebug() << "Failed to initialize Old Album Database" << endl;
    return false;
    }

    // delete entries from sqlite3 database
    db3.execSql("DELETE FROM Albums;");
    db3.execSql("DELETE FROM Tags;");
    db3.execSql("DELETE FROM TagsTree;");
    db3.execSql("DELETE FROM Images;");
    db3.execSql("DELETE FROM ImageTags;");
    db3.execSql("DELETE FROM ImageProperties;");
    
    TQStringList values;

    // update albums -------------------------------------------------
    
    values.clear();
    db2.execSql("SELECT id, url, date, caption, collection, icon FROM Albums;",
        &values);

    typedef TQValueList<_Album> AlbumList;
    AlbumList albumList;

    typedef TQMap<TQString, int> AlbumMap;
    AlbumMap albumMap;

    db3.beginTransaction();
    for (TQStringList::iterator it=values.begin(); it!=values.end();)
    {
    _Album album;

    album.id     = (*it).toInt();
    ++it;
    album.url    = (*it);
    ++it;
    album.date   = (*it);
    ++it;
    album.caption    = (*it);
    ++it;
    album.collection = (*it);
    ++it;
    album.icon   = (*it);
    ++it;

    albumList.append(album);
    albumMap.insert(album.url, album.id);

    db3.execSql(TQString("INSERT INTO Albums (id, url, date, caption, collection) "
                "VALUES(%1, '%2', '%3', '%4', '%5');")
            .tqarg(album.id)
            .tqarg(escapeString(album.url))
            .tqarg(escapeString(album.date))
            .tqarg(escapeString(album.caption))
            .tqarg(escapeString(album.collection)));
    }
    db3.commitTransaction();

    // update tags -------------------------------------------------
    
    values.clear();
    db2.execSql("SELECT id, pid, name, icon FROM Tags;",
        &values);

    typedef TQValueList<_Tag> TagList;
    TagList tagList;

    db3.beginTransaction();
    for (TQStringList::iterator it=values.begin(); it!=values.end();)
    {
    _Tag tag;

    tag.id   = (*it).toInt();
    ++it;
    tag.pid  = (*it).toInt();
    ++it;
    tag.name = (*it);
    ++it;
    tag.icon = (*it);
    ++it;

    tagList.append(tag);

    db3.execSql(TQString("INSERT INTO Tags (id, pid, name) "
                "VALUES(%1, %2, '%3');")
            .tqarg(tag.id)
            .tqarg(tag.pid)
            .tqarg(escapeString(tag.name)));
    }
    db3.commitTransaction();

    // update images -------------------------------------------------
    
    values.clear();
    db2.execSql("SELECT dirid, name, caption FROM Images;",
        &values);

    db3.beginTransaction();
    for (TQStringList::iterator it=values.begin(); it!=values.end();)
    {
    int dirid   = (*it).toInt();
    ++it;
    TQString name    = (*it);
    ++it;
    TQString caption = (*it);
    ++it;

    findOrAddImage(&db3, dirid, name, caption);
    }
    db3.commitTransaction();

    // update imagetags -----------------------------------------------
    
    values.clear();
    db2.execSql("SELECT dirid, name, tagid FROM ImageTags;",
        &values);
    db3.beginTransaction();
    for (TQStringList::iterator it=values.begin(); it!=values.end();)
    {
    int dirid = (*it).toInt();
    ++it;

    TQString name = (*it);
    ++it;

    int tagid = (*it).toInt();
    ++it;

    TQ_LLONG imageid = findOrAddImage(&db3, dirid, name, TQString());
    
    db3.execSql(TQString("INSERT INTO ImageTags VALUES( %1, %2 )")
            .tqarg(imageid).tqarg(tagid));
    }
    db3.commitTransaction();

    // update album icons -------------------------------------------------

    db3.beginTransaction();
    for (AlbumList::iterator it = albumList.begin(); it != albumList.end();
     ++it)
    {
    _Album album = *it;

    if (album.icon.isEmpty())
        continue;

    TQ_LLONG imageid = findOrAddImage(&db3, album.id, album.icon, TQString());

    db3.execSql(TQString("UPDATE Albums SET icon=%1 WHERE id=%2")
            .tqarg(imageid)
            .tqarg(album.id));
    }
    db3.commitTransaction();

    // -- update tag icons ---------------------------------------------------

    db3.beginTransaction();
    for (TagList::iterator it = tagList.begin(); it != tagList.end(); ++it)
    {
    _Tag tag = *it;

    if (tag.icon.isEmpty())
        continue;

    TQFileInfo fi(tag.icon);
    if (fi.isRelative())
    {
        db3.execSql(TQString("UPDATE Tags SET iconkde='%1' WHERE id=%2")
            .tqarg(escapeString(tag.icon))
            .tqarg(tag.id));
        continue;
    }

    tag.icon = TQDir::cleanDirPath(tag.icon);
    fi.setFile(tag.icon.remove(libraryPath));

    TQString url  = fi.dirPath(true);
    TQString name = fi.fileName();

    AlbumMap::iterator it1 = albumMap.find(url);
    if (it1 == albumMap.end())
    {
        DDebug() << "Could not find album with url: " << url << endl;
        DDebug() << "Most likely an external directory. Rejecting." << endl;
        continue;
    }

    int dirid = it1.data();

    TQ_LLONG imageid = findOrAddImage(&db3, dirid, name, TQString());;

    db3.execSql(TQString("UPDATE Tags SET icon=%1 WHERE id=%2")
            .tqarg(imageid)
            .tqarg(tag.id));

    }
    db3.commitTransaction();

    // -- Remove invalid entries ----------------------------------------
    db3.execSql("DELETE FROM Images WHERE dirid=-1");

    // -- update setting entry ------------------------------------------
    db3.setSetting("UpgradedFromSqlite2", "yes");

    DDebug() << "Successfully upgraded database to sqlite3 " << endl;

    // -- Check for db consistency ----------------------------------------

    std::cout << "Checking database consistency" << std::endl;

    
    std::cout << "Checking Albums..................";
    values.clear();
    db2.execSql("SELECT id, url, date, caption, collection FROM Albums;", &values);
    for (TQStringList::iterator it = values.begin(); it != values.end();)
    {
    _Album album;
    album.id     = (*it).toInt();
    ++it;
    album.url    = (*it);
    ++it;
    album.date   = (*it);
    ++it;
    album.caption    = (*it);
    ++it;
    album.collection = (*it);
    ++it;

    TQStringList list;
    db3.execSql(TQString("SELECT id FROM Albums WHERE \n"
                "    id=%1 AND \n"
                "    url='%2' AND \n"
                "    date='%3' AND \n"
                "    caption='%4' AND \n"
                "    collection='%5';")
            .tqarg(album.id)
            .tqarg(escapeString(album.url))
            .tqarg(escapeString(album.date))
            .tqarg(escapeString(album.caption))
            .tqarg(escapeString(album.collection)), &list, false);
    if (list.size() != 1)
    {
        std::cerr << "Failed" << std::endl;
        DWarning() << "" << endl;
        DWarning() << "Consistency check failed for Album: "
              << album.url << endl;
        return false;
    }
    }
    std::cout << "(" << values.count()/5 << " Albums) "  << "OK" << std::endl;

    
    std::cout << "Checking Tags....................";
    values.clear();
    db2.execSql("SELECT id, pid, name FROM Tags;", &values);
    for (TQStringList::iterator it = values.begin(); it != values.end();)
    {
    int id       = (*it).toInt();
    ++it;
    int pid      = (*it).toInt();
    ++it;
    TQString name = (*it);
    ++it;

    TQStringList list;
    db3.execSql(TQString("SELECT id FROM Tags WHERE \n"
                "    id=%1 AND \n"
                "    pid=%2 AND \n"
                "    name='%3';")
            .tqarg(id)
            .tqarg(pid)
            .tqarg(escapeString(name)),
            &list, false);
    if (list.size() != 1)
    {
        std::cerr << "Failed" << std::endl;
        DWarning() << "" << endl;
        DWarning() << "Consistency check failed for Tag: "
              << name << endl;
        return false;
    }
    }
    std::cout << "(" << values.count()/3 << " Tags) "  << "OK" << std::endl;

    
    std::cout << "Checking Images..................";
    values.clear();
    db2.execSql("SELECT Albums.url, Images.name, Images.caption "
        "FROM Images, Albums WHERE Albums.id=Images.dirid;", &values);
    for (TQStringList::iterator it = values.begin(); it != values.end();)
    {
    TQString url  = (*it);
    ++it;
    TQString name = (*it);
    ++it;
    TQString caption = (*it);
    ++it;

    TQStringList list;
    db3.execSql(TQString("SELECT Images.id FROM Images, Albums WHERE \n "
                "Albums.url = '%1' AND \n "
                "Images.dirid = Albums.id AND \n "
                "Images.name = '%2' AND \n "
                "Images.caption = '%3';")
            .tqarg(escapeString(url))
            .tqarg(escapeString(name))
            .tqarg(escapeString(caption)),
            &list, false);
    if (list.size() != 1)
    {
        std::cerr << "Failed" << std::endl;
        DWarning() << "" << endl;
        DWarning() << "Consistency check failed for Image: "
              << url << ", " << name << ", " << caption  << endl;
        return false;
    }
    }
    std::cout << "(" << values.count()/3 << " Images) " << "OK" << std::endl;

    
    std::cout << "Checking ImageTags...............";
    values.clear();
    db2.execSql("SELECT Albums.url, ImageTags.name, ImageTags.tagid "
        "FROM ImageTags, Albums WHERE \n "
        "   Albums.id=ImageTags.dirid;", &values);
    for (TQStringList::iterator it = values.begin(); it != values.end();)
    {
    TQString url   = (*it);
    ++it;
    TQString name  = (*it);
    ++it;
    int tagid = (*it).toInt();
    ++it;

    TQStringList list;
    db3.execSql(TQString("SELECT Images.id FROM Albums, Images, ImageTags WHERE \n "
                "Albums.url = '%1' AND \n "
                "Images.dirid = Albums.id AND \n "
                "Images.name = '%2' AND \n "
                "ImageTags.imageid = Images.id AND \n "
                "ImageTags.tagid = %3;")
            .tqarg(escapeString(url))
            .tqarg(escapeString(name))
            .tqarg(tagid),
            &list, false);
    if (list.size() != 1)
    {
        std::cerr << "Failed" << std::endl;
        DWarning() << "" << endl;
        DWarning() << "Consistency check failed for ImageTag: "
              << url << ", " << name << ", " << tagid << endl;
        return false;
    }
    }
    std::cout << "(" << values.count()/3 << " ImageTags) " << "OK" << std::endl;

    std::cout << "Checking Album icons ...............";
    values.clear();
    db2.execSql("SELECT url, icon FROM Albums;", &values);
    for (TQStringList::iterator it = values.begin(); it != values.end();)
    {
    TQString url    = (*it);
    ++it;
    TQString icon   = (*it);
    ++it;

    if (icon.isEmpty())
        continue;
    
    TQStringList list;
    db3.execSql(TQString("SELECT Images.id FROM Images, Albums WHERE \n "
                "Albums.url = '%1' AND \n "
                "Images.id = Albums.icon AND \n "
                "Images.name = '%2';")
            .tqarg(escapeString(url))
            .tqarg(escapeString(icon)), &list);

    if (list.size() != 1)
    {
        std::cerr << "Failed" << std::endl;
        DWarning() << "" << endl;
        DWarning() << "Consistency check failed for Album Icon: "
              << url << ", " << icon << endl;

        return false;
    }
    }
    std::cout << "(" << values.count()/2 << " Album Icons) " << "OK" << std::endl;

    
    std::cout << "Checking Tag icons ...............";
    values.clear();
    db2.execSql("SELECT id, icon FROM Tags;", &values);
    for (TQStringList::iterator it = values.begin(); it != values.end();)
    {
    int id       = (*it).toInt();
    ++it;
    TQString icon = (*it);
    ++it;

    if (icon.isEmpty())
        continue;

    if (!icon.startsWith("/"))
    {
        TQStringList list;
        db3.execSql(TQString("SELECT id FROM Tags WHERE \n "
                "id = %1 AND \n "
                "iconkde = '%2';")
            .tqarg(id)
            .tqarg(escapeString(icon)), &list);

        if (list.size() != 1)
        {
        std::cerr << "Failed" << std::endl;
        DWarning() << "" << endl;
        DWarning() << "Consistency check failed for Tag Icon: "
              << id << ", " << icon << endl;

        return false;
        }
    }
    else
    {
        icon = TQDir::cleanDirPath(icon);
        TQFileInfo fi(icon.remove(libraryPath));

        TQString url  = fi.dirPath(true);
        TQString name = fi.fileName();

        TQStringList list;

        list.clear();
        db3.execSql(TQString("SELECT id FROM Albums WHERE url='%1'")
            .tqarg(escapeString(url)), &list);
        if (list.isEmpty())
        {
        DWarning() << "Tag icon not in Album Library Path, Rejecting " << endl;
        DWarning() << "(" << icon << ")" << endl;
        continue;
        }

        list.clear();
        db3.execSql(TQString("SELECT Images.id FROM Images, Tags WHERE \n "
                " Images.dirid=(SELECT id FROM Albums WHERE url='%1') AND \n "
                " Images.name='%2' AND \n "
                " Tags.id=%3 AND \n "
                " Tags.icon=Images.id")
            .tqarg(escapeString(url))
            .tqarg(escapeString(name))
            .tqarg(id), &list);
        if (list.size() != 1)
        {
        std::cerr << "Failed." << std::endl;
        DWarning() << "" << endl;
        DWarning() << "Consistency check failed for Tag Icon: "
              << id << ", " << icon << endl;

        return false;
        }
        
    }
    }
    std::cout << "(" << values.count()/2 << " Tag Icons) " << "OK" << std::endl;

    std::cout << "" << std::endl;
    std::cout << "All Tests: A-OK" << std::endl;
    
    return true;
}

}  // namespace Digikam