Library::Library(const QString &dbname, QObject *parent)
: QObject(parent)
{
db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(dbname);
if(!db.open())
{
qDebug() << "FAILED TO OPEN DB";
throw; // TODO
}
query = QSqlQuery(db);
query.exec("SELECT v FROM settings WHERE k='schema_version'");
if(query.next())
{
int v = query.value(0).toInt();
qDebug() << "Current schema is " << v;
if(v != CURRENT_SCHEMA_VERSION)
{
qDebug() << "Schema version too old: " << v << ". Current version is: " << CURRENT_SCHEMA_VERSION;
updateSchema(v);
}
} else {
updateSchema(0);
}
}
bool DBBrowserDB::addColumn(const QString& tablename, const sqlb::FieldPtr& field)
{
QString sql = QString("ALTER TABLE `%1` ADD COLUMN %2").arg(tablename).arg(field->toString());
// Execute it and update the schema
bool result = executeSQL(sql);
updateSchema();
return result;
}
bool DBBrowserDB::createTable(const QString& name, const sqlb::FieldVector& structure)
{
// Build SQL statement
sqlb::Table table(name);
for(int i=0;i<structure.size();i++)
table.addField(structure.at(i));
// Execute it and update the schema
bool result = executeSQL(table.sql());
updateSchema();
return result;
}
bool DBBrowserDB::renameTable(const QString& from_table, const QString& to_table)
{
QString sql = QString("ALTER TABLE `%1` RENAME TO `%2`").arg(from_table, to_table);
if(!executeSQL(sql))
{
QString error = QObject::tr("Error renaming table '%1' to '%2'."
"Message from database engine:\n%3").arg(from_table).arg(to_table).arg(lastErrorMessage);
lastErrorMessage = error;
qWarning() << lastErrorMessage;
return false;
} else {
updateSchema();
return true;
}
}
void Database::setupDatabase() {
LOG4CPLUS_INFO(logger, "Setting up database " << dbName);
int ret = sqlite3_open(dbName, &db);
exec(const_cast<char *>("PRAGMA page_size = 4096;"));
exec(const_cast<char *>("PRAGMA cache_size=10000;"));
exec(const_cast<char *>("PRAGMA locking_mode=EXCLUSIVE;"));
exec(const_cast<char *>("PRAGMA synchronous=NORMAL;"));
exec(const_cast<char *>("PRAGMA temp_store = MEMORY;"));
exec(const_cast<char *>("PRAGMA journal_mode=MEMORY;"));
exec(const_cast<char *>("CREATE TABLE IF NOT EXISTS `imagerecord` (`path` VARCHAR NOT NULL , `pHash` BIGINT NOT NULL , PRIMARY KEY (`path`) );"));
exec(const_cast<char *>("CREATE TABLE IF NOT EXISTS `filterrecord` (`pHash` BIGINT NOT NULL , `reason` VARCHAR NOT NULL , PRIMARY KEY (`pHash`) );"));
exec(const_cast<char *>("CREATE TABLE IF NOT EXISTS `badfilerecord` (`path` VARCHAR NOT NULL , PRIMARY KEY (`path`) );"));
updateSchema();
if(ret) {
LOG4CPLUS_ERROR(logger, "Database setup failed");
throw "Database setup failed";
}
}
DatabaseImpl::DatabaseImpl( const QString& dbname, Database* parent )
: QObject( (QObject*) parent )
, m_lastartid( 0 )
, m_lastalbid( 0 )
, m_lasttrkid( 0 )
{
db = QSqlDatabase::addDatabase( "QSQLITE", "tomahawk" );
db.setDatabaseName( dbname );
if ( !db.open() )
{
qDebug() << "FAILED TO OPEN DB";
throw "failed to open db"; // TODO
}
QSqlQuery qry = QSqlQuery( db );
bool schemaUpdated = false;
qry.exec( "SELECT v FROM settings WHERE k='schema_version'" );
if ( qry.next() )
{
int v = qry.value( 0 ).toInt();
qDebug() << "Current schema is" << v << this->thread();
if ( v != CURRENT_SCHEMA_VERSION )
{
QString newname = QString("%1.v%2").arg(dbname).arg(v);
qDebug() << endl << "****************************" << endl;
qDebug() << "Schema version too old: " << v << ". Current version is:" << CURRENT_SCHEMA_VERSION;
qDebug() << "Moving" << dbname << newname;
qDebug() << endl << "****************************" << endl;
qry.clear();
qry.finish();
db.close();
db.removeDatabase( "tomahawk" );
if( QFile::rename( dbname, newname ) )
{
db = QSqlDatabase::addDatabase( "QSQLITE", "tomahawk" );
db.setDatabaseName( dbname );
if( !db.open() )
throw "db moving failed";
TomahawkSqlQuery query = newquery();
query.exec( "PRAGMA auto_vacuum = FULL" );
schemaUpdated = updateSchema( v );
}
else
{
Q_ASSERT( false );
QTimer::singleShot( 0, qApp, SLOT( quit() ) );
return;
}
}
}
else
{
schemaUpdated = updateSchema( 0 );
}
TomahawkSqlQuery query = newquery();
query.exec( "SELECT v FROM settings WHERE k='dbid'" );
if( query.next() )
{
m_dbid = query.value( 0 ).toString();
}
else
{
m_dbid = uuid();
query.exec( QString( "INSERT INTO settings(k,v) VALUES('dbid','%1')" ).arg( m_dbid ) );
}
qDebug() << "Database ID:" << m_dbid;
// make sqlite behave how we want:
query.exec( "PRAGMA synchronous = ON" );
query.exec( "PRAGMA foreign_keys = ON" );
//query.exec( "PRAGMA temp_store = MEMORY" );
// in case of unclean shutdown last time:
query.exec( "UPDATE source SET isonline = 'false'" );
m_fuzzyIndex = new FuzzyIndex( *this, schemaUpdated );
}
bool DBBrowserDB::renameColumn(const QString& tablename, const QString& name, sqlb::FieldPtr to, int move)
{
// NOTE: This function is working around the incomplete ALTER TABLE command in SQLite.
// If SQLite should fully support this command one day, this entire
// function can be changed to executing something like this:
//QString sql;
//if(to.isNull())
// sql = QString("ALTER TABLE `%1` DROP COLUMN `%2`;").arg(table).arg(column);
//else
// sql = QString("ALTER TABLE `%1` MODIFY `%2` %3").arg(tablename).arg(to).arg(type); // This is wrong...
//return executeSQL(sql);
// Collect information on the current DB layout
QString tableSql = getObjectByName(tablename).getsql();
if(tableSql.isEmpty())
{
lastErrorMessage = QObject::tr("renameColumn: cannot find table %1.").arg(tablename);
qWarning() << lastErrorMessage;
return false;
}
// Create table schema
sqlb::Table oldSchema = sqlb::Table::parseSQL(tableSql).first;
// Check if field actually exists
if(oldSchema.findField(name) == -1)
{
lastErrorMessage = QObject::tr("renameColumn: cannot find column %1.").arg(name);
qWarning() << lastErrorMessage;
return false;
}
// Create savepoint to be able to go back to it in case of any error
if(!executeSQL("SAVEPOINT sqlitebrowser_rename_column"))
{
lastErrorMessage = QObject::tr("renameColumn: creating savepoint failed. DB says: %1").arg(lastErrorMessage);
qWarning() << lastErrorMessage;
return false;
}
// Create a new table with a name that hopefully doesn't exist yet.
// Its layout is exactly the same as the one of the table to change - except for the column to change
// of course
sqlb::Table newSchema = oldSchema;
newSchema.setName("sqlitebrowser_rename_column_new_table");
QString select_cols;
if(to.isNull())
{
// We want drop the column - so just remove the field
newSchema.removeField(name);
for(int i=0;i<newSchema.fields().count();++i)
select_cols.append(QString("`%1`,").arg(newSchema.fields().at(i)->name()));
select_cols.chop(1); // remove last comma
} else {
// We want to modify it
// Move field
int index = newSchema.findField(name);
sqlb::FieldPtr temp = newSchema.fields().at(index);
newSchema.setField(index, newSchema.fields().at(index + move));
newSchema.setField(index + move, temp);
// Get names of fields to select from old table now - after the field has been moved and before it might be renamed
for(int i=0;i<newSchema.fields().count();++i)
select_cols.append(QString("`%1`,").arg(newSchema.fields().at(i)->name()));
select_cols.chop(1); // remove last comma
// Modify field
newSchema.setField(index + move, to);
}
// Create the new table
if(!executeSQL(newSchema.sql()))
{
lastErrorMessage = QObject::tr("renameColumn: creating new table failed. DB says: %1").arg(lastErrorMessage);
qWarning() << lastErrorMessage;
executeSQL("ROLLBACK TO SAVEPOINT sqlitebrowser_rename_column;");
return false;
}
// Copy the data from the old table to the new one
if(!executeSQL(QString("INSERT INTO sqlitebrowser_rename_column_new_table SELECT %1 FROM `%2`;").arg(select_cols).arg(tablename)))
{
lastErrorMessage = QObject::tr("renameColumn: copying data to new table failed. DB says:\n"
"%1").arg(lastErrorMessage);
qWarning() << lastErrorMessage;
executeSQL("ROLLBACK TO SAVEPOINT sqlitebrowser_rename_column;");
return false;
}
// Save all indices, triggers and views associated with this table because SQLite deletes them when we drop the table in the next step
QString otherObjectsSql;
for(objectMap::ConstIterator it=objMap.begin();it!=objMap.end();++it)
{
// If this object references the table and it's not the table itself save it's SQL string
if((*it).getTableName() == tablename && (*it).gettype() != "table")
otherObjectsSql += (*it).getsql() + "\n";
}
// Delete the old table
if(!executeSQL(QString("DROP TABLE `%1`;").arg(tablename)))
{
lastErrorMessage = QObject::tr("renameColumn: deleting old table failed. DB says: %1").arg(lastErrorMessage);
qWarning() << lastErrorMessage;
executeSQL("ROLLBACK TO SAVEPOINT sqlitebrowser_rename_column;");
return false;
}
// Rename the temporary table
if(!renameTable("sqlitebrowser_rename_column_new_table", tablename))
{
executeSQL("ROLLBACK TO SAVEPOINT sqlitebrowser_rename_column;");
return false;
}
// Restore the saved triggers, views and indices
if(!executeMultiSQL(otherObjectsSql, true, true))
{
QMessageBox::information(0, qApp->applicationName(), QObject::tr("Restoring some of the objects associated with this table failed. "
"This is most likely because some column names changed. "
"Here's the SQL statement which you might want to fix and execute manually:\n\n")
+ otherObjectsSql);
}
// Release the savepoint - everything went fine
if(!executeSQL("RELEASE SAVEPOINT sqlitebrowser_rename_column;"))
{
lastErrorMessage = QObject::tr("renameColumn: releasing savepoint failed. DB says: %1").arg(lastErrorMessage);
qWarning() << lastErrorMessage;
return false;
}
// Success, update the DB schema before returning
updateSchema();
return true;
}
DatabaseImpl::DatabaseImpl( const QString& dbname, Database* parent )
: QObject( (QObject*) parent )
, m_lastartid( 0 )
, m_lastalbid( 0 )
, m_lasttrkid( 0 )
{
bool schemaUpdated = false;
int version = getDatabaseVersion( dbname );
if ( version > 0 && version != CURRENT_SCHEMA_VERSION )
{
QString newname = QString( "%1.v%2" ).arg( dbname ).arg( version );
tLog() << endl << "****************************" << endl;
tLog() << "Schema version too old: " << version << ". Current version is:" << CURRENT_SCHEMA_VERSION;
tLog() << "Moving" << dbname << newname;
tLog() << "If the migration fails, you can recover your DB by copying" << newname << "back to" << dbname;
tLog() << endl << "****************************" << endl;
QFile::copy( dbname, newname );
{
db = QSqlDatabase::addDatabase( "QSQLITE", "tomahawk" );
db.setDatabaseName( dbname );
if( !db.open() )
throw "db moving failed";
TomahawkSqlQuery query = newquery();
query.exec( "PRAGMA auto_vacuum = FULL" );
schemaUpdated = updateSchema( version );
if ( !schemaUpdated )
{
Q_ASSERT( false );
QTimer::singleShot( 0, qApp, SLOT( quit() ) );
}
}
}
else
{
db = QSqlDatabase::addDatabase( "QSQLITE", "tomahawk" );
db.setDatabaseName( dbname );
if ( !db.open() )
{
tLog() << "Failed to open database" << dbname;
throw "failed to open db"; // TODO
}
if ( version < 0 )
schemaUpdated = updateSchema( 0 );
}
TomahawkSqlQuery query = newquery();
query.exec( "SELECT v FROM settings WHERE k='dbid'" );
if( query.next() )
{
m_dbid = query.value( 0 ).toString();
}
else
{
m_dbid = uuid();
query.exec( QString( "INSERT INTO settings(k,v) VALUES('dbid','%1')" ).arg( m_dbid ) );
}
tLog() << "Database ID:" << m_dbid;
// make sqlite behave how we want:
query.exec( "PRAGMA synchronous = ON" );
query.exec( "PRAGMA foreign_keys = ON" );
//query.exec( "PRAGMA temp_store = MEMORY" );
// in case of unclean shutdown last time:
query.exec( "UPDATE source SET isonline = 'false'" );
m_fuzzyIndex = new FuzzyIndex( *this, schemaUpdated );
}
