bool DatabaseService::setupTables() {
QSqlDatabase dbDisk = QSqlDatabase::database("disk");
QSqlQuery queryDisk(dbDisk);
queryDisk.exec("CREATE TABLE appData ("
"name VARCHAR(255) PRIMARY KEY, "
"value VARCHAR(255));");
int version = getAppData("database_version").toInt();
qDebug() << __func__ << " - 'database_version': " << version;
if (version < 1) {
queryDisk.exec("CREATE TABLE logFileSource ("
"id INTEGER PRIMARY KEY,"
"type INTEGER,"
"name VARCHAR(255),"
"local_path VARCHAR(255),"
"ezp_server_url VARCHAR(255),"
"ezp_username VARCHAR(255),"
"ezp_password VARCHAR(255),"
"priority INTEGER DEFAULT 0)");
version = 1;
}
if (version < 2) {
queryDisk.exec("ALTER TABLE logFileSource ADD "
"add_downloaded_file_prefix BOOLEAN;");
version = 2;
}
setAppData("database_version", QString::number(version));
return true;
}
/**
* Creates or updates the note folder tables
*/
bool DatabaseService::setupNoteFolderTables() {
QSqlDatabase dbDisk = QSqlDatabase::database("note_folder");
QSqlQuery queryDisk(dbDisk);
queryDisk.exec("CREATE TABLE IF NOT EXISTS appData ("
"name VARCHAR(255) PRIMARY KEY, "
"value VARCHAR(255));");
int version = getAppData("database_version", "note_folder").toInt();
int oldVersion = version;
qDebug() << __func__ << " - 'database version': " << version;
if (version < 1) {
queryDisk.exec("CREATE TABLE IF NOT EXISTS tag ("
"id INTEGER PRIMARY KEY,"
"name VARCHAR(255),"
"priority INTEGER DEFAULT 0,"
"created DATETIME DEFAULT current_timestamp)");
queryDisk.exec("CREATE UNIQUE INDEX IF NOT EXISTS idxUniqueTag ON "
"tag (name);");
queryDisk.exec("CREATE TABLE IF NOT EXISTS noteTagLink ("
"id INTEGER PRIMARY KEY,"
"tag_id INTEGER,"
"note_file_name VARCHAR(255),"
"created DATETIME DEFAULT current_timestamp)");
queryDisk.exec("CREATE UNIQUE INDEX IF NOT EXISTS idxUniqueTagNoteLink"
" ON noteTagLink (tag_id, note_file_name);");
version = 1;
}
if (version < 2) {
queryDisk.exec("ALTER TABLE tag ADD parent_id INTEGER DEFAULT 0;");
queryDisk.exec("CREATE INDEX IF NOT EXISTS idxTagParent "
"ON tag( parent_id );");
version = 2;
}
if (version < 3) {
queryDisk.exec("DROP INDEX IF EXISTS idxUniqueTag;");
queryDisk.exec("CREATE UNIQUE INDEX IF NOT EXISTS idxUniqueTag ON "
"tag (name, parent_id);");
version = 3;
}
if (version != oldVersion) {
setAppData("database_version",
QString::number(version), "note_folder");
}
return true;
}
bool DatabaseService::setupTables() {
QSqlDatabase dbDisk = QSqlDatabase::database("disk");
QSqlQuery queryDisk(dbDisk);
queryDisk.exec("CREATE TABLE IF NOT EXISTS appData ("
"name VARCHAR(255) PRIMARY KEY, "
"value VARCHAR(255))");
int version = getAppData("database_version").toInt();
int oldVersion = version;
qDebug() << __func__ << " - 'database_version': " << version;
QSqlDatabase dbMemory = QSqlDatabase::database("memory");
QSqlQuery queryMemory(dbMemory);
queryMemory.exec("CREATE TABLE IF NOT EXISTS note ("
"id INTEGER PRIMARY KEY,"
"name VARCHAR(255),"
"file_name VARCHAR(255),"
"note_sub_folder_id int,"
"note_text TEXT,"
"decrypted_note_text TEXT,"
"has_dirty_data INTEGER DEFAULT 0,"
"file_last_modified DATETIME,"
"file_created DATETIME,"
"crypto_key INT64 DEFAULT 0,"
"crypto_password VARCHAR(255),"
"created DATETIME default current_timestamp,"
"modified DATETIME default current_timestamp)");
queryMemory.exec("CREATE TABLE IF NOT EXISTS noteSubFolder ("
"id INTEGER PRIMARY KEY,"
"name VARCHAR(255),"
"parent_id int,"
"file_last_modified DATETIME,"
"created DATETIME default current_timestamp,"
"modified DATETIME default current_timestamp)");
if (version < 1) {
queryDisk.exec("CREATE TABLE IF NOT EXISTS calendarItem ("
"id INTEGER PRIMARY KEY,"
"summary VARCHAR(255),"
"url VARCHAR(255),"
"description TEXT,"
"has_dirty_data INTEGER DEFAULT 0,"
"completed INTEGER DEFAULT 0,"
"priority INTEGER,"
"calendar VARCHAR(255),"
"uid VARCHAR(255),"
"ics_data TEXT,"
"alarm_date DATETIME,"
"etag VARCHAR(255),"
"last_modified_string VARCHAR(255),"
"created DATETIME DEFAULT current_timestamp,"
"modified DATETIME DEFAULT current_timestamp)");
queryDisk.exec("CREATE UNIQUE INDEX IF NOT EXISTS idxUrl "
"ON calendarItem( url )");
queryDisk.exec("ALTER TABLE calendarItem ADD completed_date DATETIME");
queryDisk.exec("ALTER TABLE calendarItem "
"ADD sort_priority INTEGER DEFAULT 0");
version = 1;
}
if (version < 2) {
CalendarItem::updateAllSortPriorities();
version = 2;
}
if (version < 3) {
queryDisk.exec("CREATE TABLE IF NOT EXISTS noteFolder ("
"id INTEGER PRIMARY KEY,"
"name VARCHAR(255),"
"local_path VARCHAR(255),"
"remote_path VARCHAR(255),"
"owncloud_server_id INTEGER DEFAULT 0,"
"priority INTEGER DEFAULT 0 )");
version = 3;
}
// we need to remove the main splitter sizes for version 4 and 5
if (version < 5) {
QSettings settings;
// remove the main splitter sizes for the tags pane
settings.remove("mainSplitterSizes");
version = 5;
}
if (version < 6) {
QSettings settings;
// remove the obsolete activeTagId setting
settings.remove("activeTagId");
version = 6;
}
if (version < 7) {
queryDisk.exec("ALTER TABLE noteFolder ADD active_tag_id INTEGER");
version = 7;
}
if (version < 8) {
queryDisk.exec("CREATE TABLE IF NOT EXISTS script ("
"id INTEGER PRIMARY KEY,"
"name VARCHAR(255),"
"script_path TEXT,"
"enabled BOOLEAN DEFAULT 1,"
"priority INTEGER DEFAULT 0 )");
version = 8;
}
if (version < 9) {
queryDisk.exec("ALTER TABLE noteFolder ADD show_subfolders BOOLEAN "
"DEFAULT 0");
version = 9;
}
if (version < 10) {
queryDisk.exec("ALTER TABLE noteFolder "
"ADD active_note_sub_folder_data TEXT");
version = 10;
}
if (version != oldVersion) {
setAppData("database_version", QString::number(version));
}
return true;
}
/**
* Creates or updates the note folder tables
*/
bool DatabaseService::setupNoteFolderTables() {
QSqlDatabase dbDisk = QSqlDatabase::database("note_folder");
QSqlQuery queryDisk(dbDisk);
queryDisk.exec("CREATE TABLE IF NOT EXISTS appData ("
"name VARCHAR(255) PRIMARY KEY, "
"value VARCHAR(255))");
int version = getAppData("database_version", "note_folder").toInt();
int oldVersion = version;
qDebug() << __func__ << " - 'database version': " << version;
if (version < 1) {
queryDisk.exec("CREATE TABLE IF NOT EXISTS tag ("
"id INTEGER PRIMARY KEY,"
"name VARCHAR(255),"
"priority INTEGER DEFAULT 0,"
"created DATETIME DEFAULT current_timestamp)");
queryDisk.exec("CREATE UNIQUE INDEX IF NOT EXISTS idxUniqueTag ON "
"tag (name)");
queryDisk.exec("CREATE TABLE IF NOT EXISTS noteTagLink ("
"id INTEGER PRIMARY KEY,"
"tag_id INTEGER,"
"note_file_name VARCHAR(255),"
"created DATETIME DEFAULT current_timestamp)");
queryDisk.exec("CREATE UNIQUE INDEX IF NOT EXISTS idxUniqueTagNoteLink"
" ON noteTagLink (tag_id, note_file_name)");
version = 1;
}
if (version < 2) {
queryDisk.exec("ALTER TABLE tag ADD parent_id INTEGER DEFAULT 0");
queryDisk.exec("CREATE INDEX IF NOT EXISTS idxTagParent "
"ON tag( parent_id )");
version = 2;
}
if (version < 3) {
queryDisk.exec("DROP INDEX IF EXISTS idxUniqueTag");
queryDisk.exec("CREATE UNIQUE INDEX IF NOT EXISTS idxUniqueTag ON "
"tag (name, parent_id)");
version = 3;
}
if (version < 4) {
queryDisk.exec("ALTER TABLE noteTagLink ADD note_sub_folder_path TEXT");
version = 4;
}
if (version < 5) {
queryDisk.exec("DROP INDEX IF EXISTS idxUniqueTagNoteLink");
queryDisk.exec("CREATE UNIQUE INDEX IF NOT EXISTS idxUniqueTagNoteLink "
"ON noteTagLink (tag_id, note_file_name, "
"note_sub_folder_path)");
version = 5;
}
if (version < 6) {
// we need to add a `DEFAULT ''` to column note_sub_folder_path
queryDisk.exec("ALTER TABLE noteTagLink RENAME TO _noteTagLink");
queryDisk.exec("CREATE TABLE IF NOT EXISTS noteTagLink ("
"id INTEGER PRIMARY KEY,"
"tag_id INTEGER,"
"note_file_name VARCHAR(255) DEFAULT '',"
"note_sub_folder_path TEXT DEFAULT '',"
"created DATETIME DEFAULT current_timestamp)");
queryDisk.exec("INSERT INTO noteTagLink (tag_id, note_file_name, "
"note_sub_folder_path, created) "
"SELECT tag_id, note_file_name, "
"note_sub_folder_path, created "
"FROM _noteTagLink ORDER BY id");
queryDisk.exec("DROP INDEX IF EXISTS idxUniqueTagNoteLink");
queryDisk.exec("CREATE UNIQUE INDEX IF NOT EXISTS idxUniqueTagNoteLink "
"ON noteTagLink (tag_id, note_file_name, "
"note_sub_folder_path)");
queryDisk.exec("DROP TABLE _noteTagLink");
queryDisk.exec("UPDATE noteTagLink SET note_sub_folder_path = '' "
"WHERE note_sub_folder_path IS NULL");
version = 6;
}
if (version != oldVersion) {
setAppData("database_version",
QString::number(version), "note_folder");
}
return true;
}
/**
* \brief Get the associated data.
* \return The associated data.
*/
template <typename T> T* getAppData()
{
return reinterpret_cast<T*>(getAppData());
}
bool DatabaseService::setupTables() {
QSqlDatabase dbDisk = QSqlDatabase::database("disk");
QSqlQuery queryDisk(dbDisk);
queryDisk.exec("CREATE TABLE appData ("
"name VARCHAR(255) PRIMARY KEY, "
"value VARCHAR(255));");
int version = getAppData("database_version").toInt();
qDebug() << __func__ << " - 'database_version': " << version;
QSqlDatabase dbMemory = QSqlDatabase::database("memory");
QSqlQuery queryMemory(dbMemory);
queryMemory.exec("CREATE TABLE note ("
"id INTEGER PRIMARY KEY,"
"name VARCHAR(255),"
"file_name VARCHAR(255),"
"note_text TEXT,"
"decrypted_note_text TEXT,"
"has_dirty_data INTEGER DEFAULT 0,"
"file_last_modified DATETIME,"
"file_created DATETIME,"
"crypto_key INT64 DEFAULT 0,"
"crypto_password VARCHAR(255),"
"created DATETIME default current_timestamp,"
"modified DATETIME default current_timestamp)");
if (version < 1) {
queryDisk.exec("CREATE TABLE calendarItem ("
"id INTEGER PRIMARY KEY,"
"summary VARCHAR(255),"
"url VARCHAR(255),"
"description TEXT,"
"has_dirty_data INTEGER DEFAULT 0,"
"completed INTEGER DEFAULT 0,"
"priority INTEGER,"
"calendar VARCHAR(255),"
"uid VARCHAR(255),"
"ics_data TEXT,"
"alarm_date DATETIME,"
"etag VARCHAR(255),"
"last_modified_string VARCHAR(255),"
"created DATETIME DEFAULT current_timestamp,"
"modified DATETIME DEFAULT current_timestamp)");
queryDisk.exec("CREATE UNIQUE INDEX idxUrl ON calendarItem( url );");
queryDisk.exec("ALTER TABLE calendarItem ADD completed_date DATETIME;");
queryDisk.exec("ALTER TABLE calendarItem "
"ADD sort_priority INTEGER DEFAULT 0;");
version = 1;
}
if (version < 2) {
CalendarItem::updateAllSortPriorities();
version = 2;
}
setAppData("database_version", QString::number(version));
return true;
}