//----------------------------------------------------------------------------
void ctkPluginStorageSQL::open()
{
createDatabaseDirectory();
QSqlDatabase database = getConnection();
//Check if the sqlite version supports foreign key constraints
QSqlQuery query(database);
if (!query.exec("PRAGMA foreign_keys"))
{
close();
throw ctkPluginDatabaseException(QString("Check for foreign key support failed."),
ctkPluginDatabaseException::DB_SQL_ERROR);
}
if (!query.next())
{
close();
throw ctkPluginDatabaseException(QString("SQLite db does not support foreign keys. It is either older than 3.6.19 or was compiled with SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER"),
ctkPluginDatabaseException::DB_SQL_ERROR);
}
query.finish();
query.clear();
//Enable foreign key support
if (!query.exec("PRAGMA foreign_keys = ON"))
{
close();
throw ctkPluginDatabaseException(QString("Enabling foreign key support failed."),
ctkPluginDatabaseException::DB_SQL_ERROR);
}
query.finish();
//Check database structure (tables) and recreate tables if neccessary
//If one of the tables is missing remove all tables and recreate them
//This operation is required in order to avoid data coruption
if (!checkTables())
{
if (dropTables())
{
createTables();
}
else
{
//dropTables() should've handled error message
//and warning
close();
}
}
// silently remove any plugin marked as uninstalled
cleanupDB();
//Update database based on the recorded timestamps
updateDB();
initNextFreeIds();
}
/* ****************************************************************************
* Menu Manager Session
* appPath:
* useDb:
* lang: FIXME: default|lang1|lang2|lang3
* connectionPool:
*/
MenuManSession::MenuManSession(const std::string& appPath, const std::string& useDb, const std::string& lang, Wt::Dbo::SqlConnectionPool& connectionPool) : appPath_(appPath), useDb_(useDb), lang_(lang), connectionPool_(connectionPool)
{
if (useDb == "1")
{
setConnectionPool(connectionPool_);
mapClass<MenuMan>("menuman"); // table name menuman
Wt::WApplication* app = Wt::WApplication::instance();
std::string path = app->internalPath(); // /lang/menuman/
bool doXmlUpdate = false;
// FIXME add security for logon or certificate
// hard code /admin/menuman/updatexml for admin work
if (path.find("/admin/menuman/updatexml") != std::string::npos) { doXmlUpdate = true; }
//Wt::log("start") << " *** MenuManSession::MenuManSession() useDb | path = " << path << " | doXmlUpdate = " << doXmlUpdate << " *** ";
if (CrystalBall::InitDb || doXmlUpdate)
{
try
{
Wt::Dbo::Transaction t(*this);
// Note: you must drop table to do update
if (doXmlUpdate)
{
Wt::log("warning") << "MenuManSession::MenuManSession() SQL Drop Table menuman";
dropTables();
}
createTables();
Wt::log("warning") << "Created database: menuman ";
if (!ImportXML())
{
Wt::log("error") << " *** MenuManSession::MenuManSession() ImportXML failed! *** ";
return;
}
t.commit();
}
catch (std::exception& e)
{
Wt::log("warning") << " *** MenuManSession::MenuManSession() Using existing menuman database = " << e.what();
}
}
} // end if (useDb == "1")
//Wt::log("end") << " *** MenuManSession::MenuManSession() *** ";
} // end MenuManSession
QTB_BEGIN_NAMESPACE
#define SQLITE_DRIVER_NAME "QSQLITE"
#define VERSION_UNAVAILABLE -1
#define INFO_TABLE_CREATE "CREATE TABLE IF NOT EXISTS _db_helper_info_ ( version INTEGER NOT NULL, creation_date INTEGER DEFAULT (datetime('now')) )"
#define TABLE_DROP "DROP TABLE IF EXISTS %1"
#define INFO_TABLE_INSERT_VERSION "INSERT INTO _db_helper_info_ (version) VALUES (?)"
#define INFO_TABLE_SELECT_VERSION "SELECT version FROM _db_helper_info_"
SqliteDbHelper::SqliteDbHelper(const QString &filePath, const int requestedVersion, const QString &defaultConnName) :
m_databaseFilepath(filePath),
m_currVersion(VERSION_UNAVAILABLE),
m_created(true)
{
if ( QSqlDatabase::contains(defaultConnName) ) {
m_database = QSqlDatabase::database(defaultConnName);
} else {
m_database = QSqlDatabase::addDatabase(SQLITE_DRIVER_NAME, defaultConnName);
}
m_database.setDatabaseName(m_databaseFilepath);
if ( m_database.isValid() && m_database.open() ) {
if ( version() == VERSION_UNAVAILABLE ) {
DEBUG("Creating DB: " << m_database.databaseName());
setVersion(requestedVersion); //< Set the DB version
m_created = false;
} else if ( version() != requestedVersion ) {
DEBUG("Updating DB: " << m_database.databaseName() << " - From version " << version() << " to version " << requestedVersion);
dropTables(); //< Drop all the old-version Tables
setVersion(requestedVersion); //< Set the DB version
m_created = false;
}
DEBUG("DB: " << m_database.databaseName() << " is now Ready!");
} else {
WARNING("Couldn't open the Database: " << m_database.databaseName() << " - Error: " << m_database.lastError().text());
}
logDriverFeatures(); //< Describe the driver being used
}
void Tietokanta::buildSQLite()
{
QSqlQuery query;
QStringList tables;
dropTables();
tables
<<
"CREATE TABLE tulospalvelu ("
" id INTEGER NOT NULL,"
" versio TEXT NOT NULL,"
" PRIMARY KEY (id)"
")"
<<
"CREATE TABLE tapahtuma ("
" id INTEGER NOT NULL,"
" nimi TEXT NOT NULL,"
" PRIMARY KEY (id)"
")"
<<
"CREATE TABLE kilpailija ("
" id INTEGER NOT NULL,"
" nimi TEXT NOT NULL,"
" PRIMARY KEY (id)"
")"
<<
"CREATE TABLE emit ("
" id VARCHAR(8) NOT NULL,"
" vuosi INTEGER(2) NOT NULL,"
" kuukausi INTEGER(2) NOT NULL,"
" laina BOOLEAN NOT NULL DEFAULT 0,"
" kilpailija INTEGER,"
" FOREIGN KEY (kilpailija) REFERENCES kilpailija(id)"
" ON UPDATE CASCADE"
" ON DELETE SET NULL,"
" PRIMARY KEY (id)"
")"
<<
"CREATE TABLE sarja ("
" id INTEGER NOT NULL,"
" nimi TEXT NOT NULL,"
" tapahtuma INTEGER NOT NULL,"
" sakkoaika INTEGER NOT NULL DEFAULT -1,"
" yhteislahto DATETIME,"
" FOREIGN KEY (tapahtuma) REFERENCES tapahtuma(id)"
" ON UPDATE CASCADE"
" ON DELETE CASCADE,"
" PRIMARY KEY (id)"
")"
<<
"CREATE TABLE tulos_tila ("
" id INTEGER NOT NULL,"
" nimi TEXT NOT NULL,"
" PRIMARY KEY (id)"
")"
<<
"CREATE TABLE tulos ("
" id INTEGER NOT NULL,"
" tapahtuma INTEGER NOT NULL,"
" emit VARCHAR(8) NOT NULL,"
" kilpailija INTEGER NOT NULL,"
" sarja INTEGER NOT NULL,"
" tila INTEGER NOT NULL,"
" aika TIME NOT NULL,"
" maaliaika DATETIME NOT NULL,"
" poistettu INTEGER NOT NULL DEFAULT 0,"
" FOREIGN KEY (tapahtuma) REFERENCES tapahtuma(id)"
" ON UPDATE CASCADE"
" ON DELETE CASCADE,"
" FOREIGN KEY (emit) REFERENCES emit(id)"
" ON UPDATE CASCADE"
" ON DELETE CASCADE,"
" FOREIGN KEY (kilpailija) REFERENCES kilpailija(id)"
" ON UPDATE CASCADE"
" ON DELETE CASCADE,"
" FOREIGN KEY (sarja) REFERENCES sarja(id)"
" ON UPDATE CASCADE"
" ON DELETE CASCADE,"
" FOREIGN KEY (tila) REFERENCES tulos_tila(id)"
" ON UPDATE CASCADE"
" ON DELETE RESTRICT,"
" PRIMARY KEY (id)"
")"
<<
"CREATE TABLE valiaika ("
" id INTEGER NOT NULL,"
" tulos INTEGER NOT NULL,"
" numero INTEGER NOT NULL,"
" koodi INTEGER NOT NULL,"
" aika TIME NOT NULL,"
" FOREIGN KEY (tulos) REFERENCES tulos(id)"
" ON UPDATE CASCADE"
" ON DELETE CASCADE,"
" PRIMARY KEY (id)"
")"
<<
"CREATE UNIQUE INDEX valiaika_index ON valiaika (tulos, numero, koodi, aika)"
<<
"CREATE TABLE rasti ("
" id INTEGER NOT NULL,"
" sarja INTEGER NOT NULL,"
" numero INTEGER NOT NULL,"
" koodi INTEGER NOT NULL,"
" FOREIGN KEY (sarja) REFERENCES sarja(id)"
" ON UPDATE CASCADE"
" ON DELETE CASCADE,"
" PRIMARY KEY (id)"
")"
<<
"CREATE TABLE luettu_emit ("
" id INTEGER NOT NULL,"
" tapahtuma INTEGER NOT NULL,"
" emit VARCHAR(8) NOT NULL,"
" luettu DATETIME NOT NULL,"
" tulos INTEGER,"
" FOREIGN KEY (tapahtuma) REFERENCES tapahtuma(id)"
" ON UPDATE CASCADE"
" ON DELETE CASCADE,"
" FOREIGN KEY (emit) REFERENCES emit(id)"
" ON UPDATE CASCADE"
" ON DELETE CASCADE,"
" FOREIGN KEY (tulos) REFERENCES tulos(id)"
" ON UPDATE CASCADE"
" ON DELETE CASCADE,"
" PRIMARY KEY (id)"
")"
<<
"CREATE TABLE luettu_emit_rasti ("
" id INTEGER NOT NULL,"
" luettu_emit INTEGER NOT NULL,"
" numero INTEGER NOT NULL,"
" koodi INTEGER NOT NULL,"
" aika INTEGER NOT NULL,"
" FOREIGN KEY (luettu_emit) REFERENCES luettu_emit(id)"
" ON UPDATE CASCADE"
" ON DELETE CASCADE,"
" PRIMARY KEY (id)"
")"
;
foreach (QString table, tables) {
query.prepare(table);
SQL_EXEC(query,);
}
/* throws Exception */
void SubqueriesRegressionTest::tearDown()
{
dropTables();
super::tearDown();
}
//----------------------------------------------------------------------------
void ctkPluginStorageSQL::open()
{
if (m_isDatabaseOpen)
return;
QString path;
//Create full path to database
if(m_databasePath.isEmpty ())
m_databasePath = getDatabasePath();
path = m_databasePath;
QFileInfo dbFileInfo(path);
if (!dbFileInfo.dir().exists())
{
if(!QDir::root().mkpath(dbFileInfo.path()))
{
close();
QString errorText("Could not create database directory: %1");
throw ctkPluginDatabaseException(errorText.arg(dbFileInfo.path()), ctkPluginDatabaseException::DB_CREATE_DIR_ERROR);
}
}
m_connectionName = dbFileInfo.completeBaseName();
QSqlDatabase database;
if (QSqlDatabase::contains(m_connectionName))
{
database = QSqlDatabase::database(m_connectionName);
}
else
{
database = QSqlDatabase::addDatabase("QSQLITE", m_connectionName);
database.setDatabaseName(path);
}
if (!database.isValid())
{
close();
throw ctkPluginDatabaseException(QString("Invalid database connection: %1").arg(m_connectionName),
ctkPluginDatabaseException::DB_CONNECTION_INVALID);
}
//Create or open database
if (!database.isOpen())
{
if (!database.open())
{
close();
throw ctkPluginDatabaseException(QString("Could not open database. ") + database.lastError().text(),
ctkPluginDatabaseException::DB_SQL_ERROR);
}
}
m_isDatabaseOpen = true;
//Check if the sqlite version supports foreign key constraints
QSqlQuery query(database);
if (!query.exec("PRAGMA foreign_keys"))
{
close();
throw ctkPluginDatabaseException(QString("Check for foreign key support failed."),
ctkPluginDatabaseException::DB_SQL_ERROR);
}
if (!query.next())
{
close();
throw ctkPluginDatabaseException(QString("SQLite db does not support foreign keys. It is either older than 3.6.19 or was compiled with SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER"),
ctkPluginDatabaseException::DB_SQL_ERROR);
}
query.finish();
query.clear();
//Enable foreign key support
if (!query.exec("PRAGMA foreign_keys = ON"))
{
close();
throw ctkPluginDatabaseException(QString("Enabling foreign key support failed."),
ctkPluginDatabaseException::DB_SQL_ERROR);
}
query.finish();
//Check database structure (tables) and recreate tables if neccessary
//If one of the tables is missing remove all tables and recreate them
//This operation is required in order to avoid data coruption
if (!checkTables())
{
if (dropTables())
{
createTables();
}
else
{
//dropTables() should've handled error message
//and warning
close();
}
}
// silently remove any plugin marked as uninstalled
cleanupDB();
//Update database based on the recorded timestamps
updateDB();
initNextFreeIds();
}
