Logo Search packages:      
Sourcecode: kamefu version File versions

collectionaccess.cpp

/*
    collectionaccess.cpp - Access to the collection of ROMs

    Copyright (c) 2005      by Michaƫl Larouche       <michael.larouche@kdemail.net>

    *************************************************************************
    *                                                                       *
    * 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 of the License, or     *
    * (at your option) any later version.                                   *
    *                                                                       *
    *************************************************************************
*/
#include "collectionaccess.h"

// Qt include
#include <qsqldatabase.h>
#include <qsqldriver.h>
#include <qsqlquery.h>
#include <qsqlfield.h>
#include <qsqlcursor.h>
#include <qstringlist.h>

// KDE include
#include <kdebug.h>
#include <klocale.h>
#include <kstaticdeleter.h>

// Kamefu include
#include "databasepreferences.h"
#include "rommetainformation.h"

namespace Kamefu
{

static KStaticDeleter<CollectionAccess> collectionAccessDeleter;

CollectionAccess *CollectionAccess::s_self = 0L;
00040 CollectionAccess *CollectionAccess::self()
{
      if( !s_self )
      {
            collectionAccessDeleter.setObject(s_self, new CollectionAccess);
      }
      
      return s_self;
}

class CollectionAccess::Private
{
public:
      Private() 
       : connectionValid(false), transactionInProgress(false)
      {}

      bool connectionValid;
      bool transactionInProgress;
};

00061 CollectionAccess::CollectionAccess(QObject *parent, const char *name)
 : QObject(parent, name), d(new Private())
{}


00066 CollectionAccess::~CollectionAccess()
{
      kdDebug() << k_funcinfo << endl;
      delete d;
}

00072 bool CollectionAccess::init()
{
      kdDebug() << k_funcinfo << "Init the database connection." << endl;
      // Create database connection, 
      // do not recreate the connection.
      if(!d->connectionValid && createConnection())
      {
            // Check if tables exists.
            QSqlQuery query;
            // No entries were returned, so the database is empty, rom is the 
            if( !query.exec("SELECT * FROM rom") )
            {
                  kdDebug() << k_funcinfo << "Database was empty, so we are creating tables." << endl;
                  createTables(TablePermanent); // Not temp
            }
            d->connectionValid = true;
            return true;
      }
      return false;
      
}

00094 void CollectionAccess::beginRomTransaction()
{
      // Restrict acces to database to only transaction-related commands.
      d->transactionInProgress = true;

      createTables(TableTemporary);
}

00102 void CollectionAccess::endRomTransaction(bool /*commit*/)
{
      cleanTables(TablePermanent);

      // Do the actual commit, move information from temp files to permanent table.
      executeQuery( QString::fromUtf8("INSERT INTO rom SELECT * FROM rom_temp") );
      executeQuery( QString::fromUtf8("INSERT INTO system SELECT * FROM system_temp") );
      executeQuery( QString::fromUtf8("INSERT INTO editor SELECT * FROM editor_temp") );
      executeQuery( QString::fromUtf8("INSERT INTO country SELECT * FROM country_temp") );
      executeQuery( QString::fromUtf8("INSERT INTO genre SELECT * FROM genre_temp") );

      dropTables(TableTemporary);

      d->transactionInProgress = false;
}

00118 bool CollectionAccess::isTransactionInProgress() const
{
      return d->transactionInProgress;
}

00123 void CollectionAccess::insertNewRom(const Kamefu::RomMetaInformation &newRom)
{
      // Do nothing if they are no current transaction.
      if( !d->transactionInProgress )
            return;

      bool dummy;

      QSqlCursor newRomCursor("rom_temp");
      QSqlRecord *buffer = newRomCursor.primeInsert();
      buffer->setValue("rom_id", createNewIndex("rom_id", "rom_temp") );
      buffer->setValue("system_id", getForeignId("system_id", "system_temp", "system_name", newRom.system()) );
      buffer->setValue("editor_id", getForeignId("editor_id", "editor_temp", "editor_name", newRom.editor()) );
      buffer->setValue("country_id", getForeignId("country_id", "country_temp", "country_name", newRom.country()) );
      buffer->setValue("genre_id", getForeignId("genre_id", "genre_temp", "genre_name", newRom.genre()) );
      buffer->setValue("rom_name", newRom.romName());
      buffer->setValue("rom_url", newRom.url());
      buffer->setValue("rom_md5Hash", newRom.md5Hash());
      buffer->setValue("rom_year", newRom.year().toInt(&dummy, 10));
      buffer->setValue("rom_titlescreenpath", newRom.titlescreenPath() );
      buffer->setValue("rom_screenshotpath", newRom.screenshotPath() );
      newRomCursor.insert();

      kdDebug() << k_funcinfo << endl;
}

00149 void CollectionAccess::modifyExistingRom(const Kamefu::RomMetaInformation &existingRom)
{
      // Do nothing if a transaction is in progress.
      if(d->transactionInProgress)
            return;
      
      // Make sure we're dealing with something in the database.
      if( !isRomUrlInDatabase(existingRom.url()) )
      {
            kdDebug() << k_funcinfo << "Given ROM is not in the database!" << endl;
            return;
      }
      
      // Modify the entry
      // note that getForeignId() adds the id if not found in the database,
      // so this will handle new text just fine :)
      
      // Format the rom url according to SQL database format.
      QSqlField *theField = new QSqlField("rom_url", QVariant::String);
      theField->setValue(existingRom.url());
      QString formattedName = QSqlDatabase::database()->driver()->formatValue(theField);
      delete theField;
      
      QSqlCursor updateRomCursor("rom");
      
      QString path(QString("rom_url = %1").arg(formattedName));
      updateRomCursor.select(path);
      
      if (updateRomCursor.next()) 
      {
            QSqlRecord *buffer = updateRomCursor.primeUpdate();
            
            buffer->setValue("editor_id", getForeignId("editor_id", "editor", "editor_name", existingRom.editor()));
            buffer->setValue("country_id", getForeignId("country_id", "country", "country_name", existingRom.country()));
            buffer->setValue("genre_id", getForeignId("genre_id", "genre", "genre_name", existingRom.genre()));
            buffer->setValue("rom_name", existingRom.romName());
            buffer->setValue("rom_year", existingRom.year().toInt());
            buffer->setValue("rom_titlescreenpath", existingRom.titlescreenPath());
            buffer->setValue("rom_screenshotpath", existingRom.screenshotPath());
            updateRomCursor.update();
      } 
      else 
      {
            kdDebug() << k_funcinfo << "no update! " << path << "\n"
                  << updateRomCursor.lastError().text() << "\n"
                  << updateRomCursor.lastQuery() << endl;
      }
}

00198 void CollectionAccess::modifyRoms(const Kamefu::RomMetaInformation &commonInformation, const KURL::List &romUrlList)
{
      // Do nothing if a transaction is in progress.
      if(d->transactionInProgress)
            return;

      QStringList formattedRomList;
      // Format the rom url according to SQL database format.
      QSqlField *theField = new QSqlField("rom_url", QVariant::String);
      QString formattedName;

      // Format each rom url according to SQL database format.
      KURL::List::ConstIterator it, itEnd = romUrlList.constEnd();
      for( it = romUrlList.constBegin(); it != itEnd; ++it )
      {
            theField->setValue( static_cast<KURL>(*it).path() );
            formattedName = QSqlDatabase::database()->driver()->formatValue(theField);

            formattedRomList.append( formattedName );
      }
      delete theField;

      // Common information are: editor(developer), year, country, genre
      QStringList updateInformation;
      if( !commonInformation.editor().isEmpty() )
            updateInformation.append( QString::fromUtf8("editor_id = %1").arg( getForeignId("editor_id", "editor", "editor_name", commonInformation.editor()) ) );
      if( !commonInformation.country().isEmpty() )
            updateInformation.append( QString::fromUtf8("country_id = %1").arg( getForeignId("country_id", "country", "country_name", commonInformation.country()) ) );
      if( !commonInformation.genre().isEmpty() )
            updateInformation.append( QString::fromUtf8("genre_id = %1").arg( getForeignId("genre_id", "genre", "genre_name", commonInformation.genre()) ) );
      if( !commonInformation.year().isEmpty() && commonInformation.year() != "0" )
            updateInformation.append( QString::fromUtf8("rom_year = %1").arg( commonInformation.year() ) );

      QString updateQuery = QString::fromUtf8("UPDATE rom SET %1 WHERE rom_url IN(%2);").arg( updateInformation.join(",") ).arg( formattedRomList.join(",") );
      
      kdDebug() << k_funcinfo << updateQuery << endl;
      executeQuery(updateQuery, i18n("An error occured while trying to update a batch of ROMs"));
}

00237 void CollectionAccess::retrieveAllRoms()
{
      // Do nothing if a transaction is in progress.
      if(d->transactionInProgress)
            return;

      QSqlQuery selectQuery = executeQuery( 
            QString::fromUtf8(
                  "SELECT rom.rom_name,"
                  "rom.rom_url,"
                  "rom.rom_md5Hash,"
                  "rom.rom_year,"
                  "rom.rom_titlescreenpath,"
                  "rom.rom_screenshotpath,"
                  "system.system_name," 
                  "editor.editor_name, "
                  "country.country_name,"
                  "genre.genre_name "
                  "FROM rom, system, editor, country, genre "
                  "WHERE system.system_id = rom.system_id AND "
                  "editor.editor_id = rom.editor_id AND "
                  "country.country_id = rom.country_id AND "
                  "genre.genre_id = rom.genre_id "
                  "ORDER BY rom.rom_name ASC"
            ), i18n("Error while retriving all ROMs from database") );
      while( selectQuery.next() )
      {
            RomMetaInformation newRom;

            newRom.setRomName(selectQuery.value(0).toString());
            newRom.setUrl(selectQuery.value(1).toString());
            newRom.setMd5Hash(selectQuery.value(2).toString());
            newRom.setYear(selectQuery.value(3).toString());
            newRom.setTitlescreenPath(selectQuery.value(4).toString());
            newRom.setScreenshotPath(selectQuery.value(5).toString());
            newRom.setSystem(selectQuery.value(6).toString());
            newRom.setEditor(selectQuery.value(7).toString());
            newRom.setCountry(selectQuery.value(8).toString());
            newRom.setGenre(selectQuery.value(9).toString());

            emit gettingRomMetaInformation(newRom);
      }

      emit romListingFinished();
}

00283 void CollectionAccess::retrieveRoms(const QString &query)
{
      // Do nothing if a transaction is in progress.
      if(d->transactionInProgress)
            return;

      QSqlQuery selectQuery = executeQuery( query, i18n("Error while retrieving ROMs from database") );
      while( selectQuery.next() )
      {
            RomMetaInformation newRom;

            newRom.setRomName(selectQuery.value(0).toString());
            newRom.setUrl(selectQuery.value(1).toString());
            newRom.setMd5Hash(selectQuery.value(2).toString());
            newRom.setYear(selectQuery.value(3).toString());
            newRom.setTitlescreenPath(selectQuery.value(4).toString());
            newRom.setScreenshotPath(selectQuery.value(5).toString());
            newRom.setSystem(selectQuery.value(6).toString());
            newRom.setEditor(selectQuery.value(7).toString());
            newRom.setCountry(selectQuery.value(8).toString());
            newRom.setGenre(selectQuery.value(9).toString());

            emit gettingRomMetaInformation(newRom);
      }

      emit romListingFinished();
}

00311 QValueList<Kamefu::RomMetaInformation> CollectionAccess::getAllRomsList()
{
      QValueList<Kamefu::RomMetaInformation> romList;

      // Do nothing if a transaction is in progress.
      if(d->transactionInProgress)
            return romList;

      QSqlQuery selectQuery = executeQuery( 
            QString::fromUtf8(
                  "SELECT rom.rom_name,"
                  "rom.rom_url,"
                  "rom.rom_md5Hash,"
                  "rom.rom_year,"
                  "rom.rom_titlescreenpath,"
                  "rom.rom_screenshotpath,"
                  "system.system_name," 
                  "editor.editor_name, "
                  "country.country_name,"
                  "genre.genre_name "
                  "FROM rom, system, editor, country, genre "
                  "WHERE system.system_id = rom.system_id AND "
                  "editor.editor_id = rom.editor_id AND "
                  "country.country_id = rom.country_id AND "
                  "genre.genre_id = rom.genre_id "
                  "ORDER BY rom.rom_name ASC"
            ), i18n("Error while retriving all ROMs from database") );
      while( selectQuery.next() )
      {
            RomMetaInformation newRom;

            newRom.setRomName(selectQuery.value(0).toString());
            newRom.setUrl(selectQuery.value(1).toString());
            newRom.setMd5Hash(selectQuery.value(2).toString());
            newRom.setYear(selectQuery.value(3).toString());
            newRom.setTitlescreenPath(selectQuery.value(4).toString());
            newRom.setScreenshotPath(selectQuery.value(5).toString());
            newRom.setSystem(selectQuery.value(6).toString());
            newRom.setEditor(selectQuery.value(7).toString());
            newRom.setCountry(selectQuery.value(8).toString());
            newRom.setGenre(selectQuery.value(9).toString());

            romList.append(newRom);
      }
      
      return romList;
}

00359 QValueList<Kamefu::RomMetaInformation> CollectionAccess::getRomList(const KURL::List &romUrlList)
{
      QValueList<Kamefu::RomMetaInformation> romList;

      // Do nothing if a transaction is in progress.
      if(d->transactionInProgress)
            return romList;

      QStringList formattedRomList;
      // Format the rom url according to SQL database format.
      QSqlField *theField = new QSqlField("rom_url", QVariant::String);
      QString formattedName;

      // Format each rom url according to SQL database format.
      KURL::List::ConstIterator it, itEnd = romUrlList.constEnd();
      for( it = romUrlList.constBegin(); it != itEnd; ++it )
      {
            theField->setValue( static_cast<KURL>(*it).path() );
            formattedName = QSqlDatabase::database()->driver()->formatValue(theField);

            formattedRomList.append( formattedName );
      }
      delete theField;

      QSqlQuery selectQuery = executeQuery( 
            QString::fromUtf8(
                  "SELECT rom.rom_name,"
                  "rom.rom_url,"
                  "rom.rom_md5Hash,"
                  "rom.rom_year,"
                  "rom.rom_titlescreenpath,"
                  "rom.rom_screenshotpath,"
                  "system.system_name," 
                  "editor.editor_name, "
                  "country.country_name,"
                  "genre.genre_name "
                  "FROM rom, system, editor, country, genre "
                  "WHERE system.system_id = rom.system_id AND "
                  "editor.editor_id = rom.editor_id AND "
                  "country.country_id = rom.country_id AND "
                  "genre.genre_id = rom.genre_id AND "
                  "rom.rom_url IN(%1) "
                  "ORDER BY rom.rom_name ASC"
            ).arg( formattedRomList.join(",") ),
            i18n("Error while retriving all ROMs from database") );
      while( selectQuery.next() )
      {
            RomMetaInformation newRom;

            newRom.setRomName(selectQuery.value(0).toString());
            newRom.setUrl(selectQuery.value(1).toString());
            newRom.setMd5Hash(selectQuery.value(2).toString());
            newRom.setYear(selectQuery.value(3).toString());
            newRom.setTitlescreenPath(selectQuery.value(4).toString());
            newRom.setScreenshotPath(selectQuery.value(5).toString());
            newRom.setSystem(selectQuery.value(6).toString());
            newRom.setEditor(selectQuery.value(7).toString());
            newRom.setCountry(selectQuery.value(8).toString());
            newRom.setGenre(selectQuery.value(9).toString());

            romList.append(newRom);
      }
      
      return romList;
}

00425 QStringList CollectionAccess::retrieveTableEntries(const QString &table)
{
      QStringList tableEntries;
      // Do nothing if a transaction is in progress.
      if(d->transactionInProgress)
            return QStringList();

      QSqlQuery selectQuery = executeQuery( 
      QString::fromUtf8("SELECT %1_name FROM %2").arg(table).arg(table), 
      i18n("Error while retriving entries from %1 table.").arg(table) );

      while( selectQuery.next() )
      {
            tableEntries.append( selectQuery.value(0).toString() );
      }

      return tableEntries;
}

00444 QStringList CollectionAccess::retrieveTableEntriesFiltered(const QString &table, const QString &filterValue)
{
      QStringList tableEntries;
      // Do nothing if a transaction is in progress.
      if(d->transactionInProgress)
            return QStringList();
      
      QSqlQuery selectQuery = executeQuery( 
                  QString::fromUtf8("SELECT %1_name FROM %2 WHERE %3").arg(table).arg(table).arg(filterValue),
      i18n("Error while retriving entries from %1 table.").arg(table) );

      while( selectQuery.next() )
      {
            tableEntries.append( selectQuery.value(0).toString() );
      }

      return tableEntries;
}

00463 void CollectionAccess::removeRomFromDatabase(const QString &romUrl)
{
      // Do nothing if a transaction is in progress.
      if(d->transactionInProgress)
            return;

      QSqlField *theField = new QSqlField("rom_url", QVariant::String);
      theField->setValue(romUrl);
      QString formattedField = QSqlDatabase::database()->driver()->formatValue(theField);

      executeQuery( QString::fromUtf8("DELETE FROM rom WHERE rom_url = %1").arg(formattedField) );
}

00476 QString CollectionAccess::getRomUrlFromRomName(const QString &romName)
{
      kdDebug() << k_funcinfo << "Rom name: " << romName << endl;
      // Format the rom name according to SQL database format.
      QSqlField *theField = new QSqlField("rom_name", QVariant::String);
      theField->setValue(romName);
      QString formattedName = QSqlDatabase::database()->driver()->formatValue(theField);

      QString url;
      QSqlQuery urlQuery = executeQuery( 
                  QString::fromUtf8("SELECT rom_url FROM rom WHERE rom_name LIKE (%1)").arg(formattedName), i18n("Error while retrieving rom URL!"));
      
      if( urlQuery.next() )
      {
            url = urlQuery.value(0).toString();
      }

      return url;
}

00496 Kamefu::RomMetaInformation CollectionAccess::getRomMetadataFromRomUrl(const QString &romUrl)
{
      // Do nothing if a transaction is in progress.
      if(d->transactionInProgress)
            return RomMetaInformation();
      
      // Format the rom url according to SQL database format.
      QSqlField *theField = new QSqlField("rom_url", QVariant::String);
      theField->setValue(romUrl);
      QString formattedUrl = QSqlDatabase::database()->driver()->formatValue(theField);
      
      QSqlQuery selectQuery = executeQuery(
            (QString::fromUtf8(
                  "SELECT rom.rom_name,"
                  "rom.rom_url,"
                  "rom.rom_md5Hash,"
                  "rom.rom_year,"
                  "rom.rom_titlescreenpath,"
                  "rom.rom_screenshotpath,"
                  "system.system_name,"
                  "editor.editor_name,"
                  "country.country_name,"
                  "genre.genre_name "
                  "FROM rom, system, editor, country, genre "
                  "WHERE rom_url LIKE (%1) AND "
                  "system.system_id = rom.system_id AND "
                  "editor.editor_id = rom.editor_id AND "
                  "country.country_id = rom.country_id AND "
                  "genre.genre_id = rom.genre_id").arg(formattedUrl)
            ), i18n("Error while retrieving ROM metadata!"));
      
      if( !selectQuery.first() )
      {
            return RomMetaInformation();
      }
      else 
      {
            RomMetaInformation newRom;
            
            newRom.setRomName(selectQuery.value(0).toString());
            newRom.setUrl(selectQuery.value(1).toString());
            newRom.setMd5Hash(selectQuery.value(2).toString());
            newRom.setYear(selectQuery.value(3).toString());
            newRom.setTitlescreenPath(selectQuery.value(4).toString());
            newRom.setScreenshotPath(selectQuery.value(5).toString());
            newRom.setSystem(selectQuery.value(6).toString());
            newRom.setEditor(selectQuery.value(7).toString());
            newRom.setCountry(selectQuery.value(8).toString());
            newRom.setGenre(selectQuery.value(9).toString());
            
            return newRom;
      }
}

00550 Kamefu::RomMetaInformation CollectionAccess::getRomMetadataFromRomName(const QString &romName)
{
      // Do nothing if a transaction is in progress.
      if(d->transactionInProgress)
            return RomMetaInformation();
      
      kdDebug() << k_funcinfo << "Rom name: " << romName << endl;
      // Format the rom name according to SQL database format.
      QSqlField *theField = new QSqlField("rom_name", QVariant::String);
      theField->setValue(romName);
      QString formattedName = QSqlDatabase::database()->driver()->formatValue(theField);
      
      QSqlQuery selectQuery = executeQuery(
            (QString::fromUtf8(
                  "SELECT rom.rom_name,"
                  "rom.rom_url,"
                  "rom.rom_md5Hash,"
                  "rom.rom_year,"
                  "rom.rom_titlescreenpath,"
                  "rom.rom_screenshotpath,"
                  "system.system_name,"
                  "editor.editor_name,"
                  "country.country_name,"
                  "genre.genre_name "
                  "FROM rom, system, editor, country, genre "
                  "WHERE rom_name LIKE (%1) AND "
                  "system.system_id = rom.system_id AND "
                  "editor.editor_id = rom.editor_id AND "
                  "country.country_id = rom.country_id AND "
                  "genre.genre_id = rom.genre_id").arg(formattedName)
            ), i18n("Error while retrieving ROM metadata!"));
      
      // TODO handle if we get multiple results from the query
      if (!selectQuery.first()) 
      {
            return RomMetaInformation();
      } 
      else
      {
            RomMetaInformation newRom;
            
            newRom.setRomName(selectQuery.value(0).toString());
            newRom.setUrl(selectQuery.value(1).toString());
            newRom.setMd5Hash(selectQuery.value(2).toString());
            newRom.setYear(selectQuery.value(3).toString());
            newRom.setTitlescreenPath(selectQuery.value(4).toString());
            newRom.setScreenshotPath(selectQuery.value(5).toString());
            newRom.setSystem(selectQuery.value(6).toString());
            newRom.setEditor(selectQuery.value(7).toString());
            newRom.setCountry(selectQuery.value(8).toString());
            newRom.setGenre(selectQuery.value(9).toString());
            
            return newRom;
      }
}

00606 QString CollectionAccess::getSystemNameFromRomUrl(const QString &romUrl)
{
      // Format the rom url according to SQL database format.
      QSqlField *theField = new QSqlField("rom_url", QVariant::String);
      theField->setValue(romUrl);
      QString formattedName = QSqlDatabase::database()->driver()->formatValue(theField);

      QString systemName;
      QSqlQuery systemQuery = executeQuery(
                  QString::fromUtf8("SELECT system_name FROM system WHERE system_id = (SELECT system_id FROM rom WHERE rom_url LIKE(%1))").arg(formattedName),
                  i18n("Error while retrieving system name from database based on ROM url."));

      if( systemQuery.next() )
      {
            systemName = systemQuery.value(0).toString();
      }

      return systemName;
}

00626 bool CollectionAccess::isRomUrlInDatabase(const QString &romUrl)
{
      // Format the rom url according to SQL database format.
      QSqlField *theField = new QSqlField("rom_url", QVariant::String);
      theField->setValue(romUrl);
      QString formattedName = QSqlDatabase::database()->driver()->formatValue(theField);

      delete theField;

      QSqlQuery checkQuery;
      // If a transaction is in progress, get all information from the ROM and put it in rom_temp.
      if(d->transactionInProgress)
      {
            checkQuery = executeQuery( 
            QString::fromUtf8(
                  "SELECT rom.rom_name,"
                  "rom.rom_url,"
                  "rom.rom_md5Hash,"
                  "rom.rom_year,"
                  "rom.rom_titlescreenpath,"
                  "rom.rom_screenshotpath,"
                  "system.system_name," 
                  "editor.editor_name, "
                  "country.country_name,"
                  "genre.genre_name "
                  "FROM rom, system, editor, country, genre "
                  "WHERE system.system_id = rom.system_id AND "
                  "editor.editor_id = rom.editor_id AND "
                  "country.country_id = rom.country_id AND "
                  "genre.genre_id = rom.genre_id AND "
                  "rom_url LIKE(%1)"
            ).arg(formattedName), i18n("Error while retriving all ROMs from database") );

            if( checkQuery.next() )
            {
                  RomMetaInformation newRom;

                  newRom.setRomName(checkQuery.value(0).toString());
                  newRom.setUrl(checkQuery.value(1).toString());
                  newRom.setMd5Hash(checkQuery.value(2).toString());
                  newRom.setYear(checkQuery.value(3).toString());
                  newRom.setTitlescreenPath(checkQuery.value(4).toString());
                  newRom.setScreenshotPath(checkQuery.value(5).toString());
                  newRom.setSystem(checkQuery.value(6).toString());
                  newRom.setEditor(checkQuery.value(7).toString());
                  newRom.setCountry(checkQuery.value(8).toString());
                  newRom.setGenre(checkQuery.value(9).toString());

                  kdDebug() << k_funcinfo << "Found rom, inserting into rom_temp..." << endl;
                  insertNewRom(newRom);
                  return true;
            }
      }
      else
      {
            checkQuery = executeQuery(
                  QString::fromUtf8("SELECT rom_id FROM rom WHERE rom_url LIKE(%1)").arg(formattedName),
                  i18n("Error while checking if a ROM url is in database."));

            if( checkQuery.next() )
            {
                  return true;
            }
      }     
      
      return false;
}

00694 int CollectionAccess::getForeignId(const QString &key, const QString &table, const QString &researchFieldName, const QString &researchValue)
{
      // If the value is empty, use "Unknown" as default.
      // FIXME: Find a better way to do that.
      QString searchValue = researchValue.isEmpty() ? "Unknown" : researchValue;

      QSqlField *theField = new QSqlField(researchFieldName, QVariant::String);
      theField->setValue(searchValue);
      QString formattedName = QSqlDatabase::database()->driver()->formatValue(theField);

      QSqlQuery getIdQuery = executeQuery( QString::fromUtf8("SELECT %1 FROM %2 WHERE %3 LIKE (%4)").arg(key).arg(table).arg(researchFieldName).arg(formattedName) );
      getIdQuery.exec();
      if( getIdQuery.next() )
      {
            kdDebug() << k_funcinfo << "Found id: " << getIdQuery.value(0).toInt() << " for " << searchValue << endl;
            // A item with the same name was found.
            delete theField;
            return getIdQuery.value(0).toInt();
      }
      else
      {
            // Insert a new id.
            QSqlCursor newId(table);
            QSqlRecord *buffer = newId.primeInsert();
            int newIndex = createNewIndex(key, table);
            kdDebug() << k_funcinfo << "Creating a new entry for " << table << ". Index: " << newIndex << endl;
            buffer->setValue(key, newIndex);
            buffer->setValue(researchFieldName, searchValue);
            newId.insert();
            delete theField;
            return newIndex;
      }
}

00728 void CollectionAccess::createTables(int tableMode)
{
      bool temp = tableMode & TableTemporary;

      // Create the rom Table.
      executeQuery( QString::fromUtf8(
                  "CREATE %1 TABLE rom%2 ("
                  "rom_id INTEGER PRIMARY KEY,"
                  "system_id INTEGER,"
                  "editor_id INTEGER,"
                  "country_id INTEGER,"
                  "genre_id INTEGER,"
                  "rom_name TEXT,"
                  "rom_url TEXT,"
                  "rom_md5hash TEXT,"
                  "rom_year INTEGER(4),"
                  "rom_titlescreenpath TEXT,"
                  "rom_screenshotpath TEXT);"
                  ).arg(temp ? "TEMPORARY" : "").arg(temp ? "_temp" : "")
                  ,i18n("Error creating rom table !")
                  );

      // Create system Table.
      executeQuery( QString::fromUtf8(
                  "CREATE %1 TABLE system%2 ("
                  "system_id INTEGER PRIMARY KEY,"
                  "system_name TEXT);"
                  ).arg(temp ? "TEMPORARY" : "").arg(temp ? "_temp" : ""), i18n("Error while creating system table !") );
      
      // Create editor Table.
      executeQuery( QString::fromUtf8(
                  "CREATE %1 TABLE editor%2 ("
                  "editor_id INTEGER PRIMARY KEY,"
                  "editor_name TEXT);"
                  ).arg(temp ? "TEMPORARY" : "").arg(temp ? "_temp" : ""), i18n("Error while creating editor table !") );

      // Create country Table.
      executeQuery( QString::fromUtf8(
                  "CREATE %1 TABLE country%2 ("
                  "country_id INTEGER PRIMARY KEY,"
                  "country_name TEXT);"
                  ).arg(temp ? "TEMPORARY" : "").arg(temp ? "_temp" : ""), i18n("Error while creating country table !") );
      
      // Create genre Table.
      executeQuery( QString::fromUtf8(
                  "CREATE %1 TABLE genre%2 ("
                  "genre_id INTEGER PRIMARY KEY,"
                  "genre_name TEXT);"
                  ).arg(temp ? "TEMPORARY" : "").arg(temp ? "_temp" : ""), i18n("Error while creating genre table!") );
      
      createIndex( QString::fromUtf8("rom_url_idx%1").arg(temp ? "_temp" : ""), QString::fromUtf8("rom%1").arg(temp ? "_temp" : ""), QString::fromUtf8("rom_url"), 10 );
      createIndex( QString::fromUtf8("system_name_idx%1").arg(temp ? "_temp" : ""), QString::fromUtf8("system%1").arg(temp ? "_temp" : ""), QString::fromUtf8("system_name"), 10 );
      createIndex( QString::fromUtf8("editor_name_idx%1").arg(temp ? "_temp" : ""), QString::fromUtf8("editor%1").arg(temp ? "_temp" : ""), QString::fromUtf8("editor_name"), 10 );
      createIndex( QString::fromUtf8("country_name_idx%1").arg(temp ? "_temp" : ""), QString::fromUtf8("country%1").arg(temp ? "_temp" : ""), QString::fromUtf8("country_name"), 10);
      createIndex( QString::fromUtf8("genre_name_idx%1").arg(temp ? "_temp" : ""), QString::fromUtf8("genre%1").arg(temp ? "_temp" : ""), QString::fromUtf8("genre_name"), 10);
      
      if( !temp )
      {
            // Create indexes for rom table.
            createIndex( QString::fromUtf8("rom_name_idx"), QString::fromUtf8("rom"), QString::fromUtf8("rom_name"), 10 );
            createIndex( QString::fromUtf8("rom_year_idx"), QString::fromUtf8("rom"), QString::fromUtf8("rom_year"));
            createIndex( QString::fromUtf8("rom_md5hash_idx"), QString::fromUtf8("rom"), QString::fromUtf8("rom_md5hash"), 16);
      }
}

00793 void CollectionAccess::cleanTables(int tableMode)
{
      bool temp = tableMode & TableTemporary;

      executeQuery( QString::fromUtf8("DELETE FROM rom%1").arg(temp ? "_temp" : "") );
      executeQuery( QString::fromUtf8("DELETE FROM system%1").arg(temp ? "_temp" : "") );
      executeQuery( QString::fromUtf8("DELETE FROM editor%1").arg(temp ? "_temp" : "") );
      executeQuery( QString::fromUtf8("DELETE FROM country%1").arg(temp ? "_temp" : "") );
      executeQuery( QString::fromUtf8("DELETE FROM genre%1").arg(temp ? "_temp" : "") );
}

00804 void CollectionAccess::dropTables(int tableMode)
{
      bool temp = tableMode & TableTemporary;

      executeQuery( QString::fromUtf8("DROP TABLE rom%1").arg(temp ? "_temp" : "") );
      executeQuery( QString::fromUtf8("DROP TABLE system%1").arg(temp ? "_temp" : "") );
      executeQuery( QString::fromUtf8("DROP TABLE editor%1").arg(temp ? "_temp" : "") );
      executeQuery( QString::fromUtf8("DROP TABLE country%1").arg(temp ? "_temp" : "") );
      executeQuery( QString::fromUtf8("DROP TABLE genre%1").arg(temp ? "_temp" : "") );
}

00815 QSqlQuery CollectionAccess::executeQuery(const QString &query, const QString &errorMessage)
{
      QSqlQuery sqlQuery;
      
      // Execute query.
      if( !sqlQuery.exec(query) )
      {
            kdDebug() << k_funcinfo << "SQL Query(" << query << "): " << QSqlDatabase::database()->lastError().text() << endl;
            if(!errorMessage.isEmpty())
                  emit databaseError(errorMessage);
      }

      return sqlQuery;
}

00830 void CollectionAccess::createIndex(const QString &indexName, const QString &tableName, const QString &fieldName, int indexLength)
{
      QString textQuery = QString("CREATE INDEX %1 ON %2( %3%4 );").arg(indexName).arg(tableName).arg(fieldName).arg(
                  (QSqlDatabase::database()->driverName() == QString::fromUtf8("QMYSQL3") && indexLength != 0) ? QString("(%1)").arg(indexLength) : QString("") );

      executeQuery(textQuery, i18n("Cannot create %1 index.").arg(indexName));
}

00838 int CollectionAccess::createNewIndex(const QString &fieldName, const QString &tableName)
{
      QSqlQuery sqlQuery( QString::fromUtf8("SELECT MAX(%1) FROM %2").arg(fieldName).arg(tableName) );
      sqlQuery.exec();
      if( sqlQuery.next() && !sqlQuery.isNull(0) )
      {
            return sqlQuery.value(0).toInt() + 1;
      }
      else
      {
            return 1;
      }
}

00852 bool CollectionAccess::createConnection()
{
      kdDebug() << k_funcinfo << "Connecting to database." << endl;

      QSqlDatabase *db = QSqlDatabase::addDatabase(DatabasePreferences::driver());
      db->setHostName(DatabasePreferences::hostname());
      db->setDatabaseName(DatabasePreferences::database());
      db->setUserName(DatabasePreferences::username());
      db->setPassword(DatabasePreferences::password());
      if( !db->open() )
      {
            emit databaseError(db->lastError().text());
            return false;
      }

      return true;
}

}

#include "collectionaccess.moc"

Generated by  Doxygen 1.6.0   Back to index