bool DBBrowserDB::open ( const QString & db) { bool ok=false; int err; if (isOpen()) close(); //try to verify the SQLite version 3 file header QFile dbfile(db); if ( dbfile.open( QIODevice::ReadOnly ) ) { char buffer[16+1]; dbfile.readLine(buffer, 16); QString contents = QString(buffer); dbfile.close(); if (!contents.startsWith("SQLite format 3")) { lastErrorMessage = QObject::tr("File is not a SQLite 3 database"); return false; } } else { lastErrorMessage = QObject::tr("File could not be read"); return false; } lastErrorMessage = QObject::tr("no error"); err = sqlite3_open_v2(db.toUtf8(), &_db, SQLITE_OPEN_READWRITE, NULL); if ( err ) { lastErrorMessage = QString::fromUtf8((const char*)sqlite3_errmsg(_db)); sqlite3_close(_db); _db = 0; return false; } if (_db){ // set preference defaults QSettings settings(QApplication::organizationName(), QApplication::organizationName()); settings.sync(); bool foreignkeys = settings.value( "/db/foreignkeys", false ).toBool(); setPragma("foreign_keys", foreignkeys ? "1" : "0"); if (SQLITE_OK==sqlite3_exec(_db,"PRAGMA empty_result_callbacks = ON;", NULL,NULL,NULL)){ if (SQLITE_OK==sqlite3_exec(_db,"PRAGMA show_datatypes = ON;", NULL,NULL,NULL)){ ok=true; } curDBFilename = db; } // Enable extension loading sqlite3_enable_load_extension(_db, 1); } return ok; }
bool DBBrowserDB::setPragma(const QString& pragma, const QString& value, QString& originalvalue) { if( originalvalue != value ) { if( setPragma(pragma, value)) { originalvalue = value; return true; } } return false; }
void Database::setVacuum() { { QSqlDatabase dbFile = QSqlDatabase::addDatabase("QSQLITE", "vacuum"); dbFile.setDatabaseName(mainApp->dbFileName()); dbFile.open(); setPragma(dbFile); dbFile.exec("VACUUM"); dbFile.close(); } QSqlDatabase::removeDatabase("vacuum"); }
bool DBBrowserDB::setPragma(const QString& pragma, int value, int& originalvalue) { if( originalvalue != value ) { QString val = QString::number(value); QString origval = QString::number(originalvalue); if( setPragma(pragma, val, origval)) { originalvalue = value; } } return false; }
QSqlDatabase Database::connection(const QString &connectionName) { QSqlDatabase db; if (mainApp->storeDBMemory()) { db = QSqlDatabase::database(); } else { db = QSqlDatabase::database(connectionName, true); if (!db.isValid()) { SQLiteDriver *driver = new SQLiteDriver(); db = QSqlDatabase::addDatabase(driver, connectionName); db.setDatabaseName(mainApp->dbFileName()); db.open(); setPragma(db); } } return db; }
void Database::initialization() { prepareDatabase(); SQLiteDriver *driver = new SQLiteDriver(); QSqlDatabase db = QSqlDatabase::addDatabase(driver); if (mainApp->storeDBMemory()) db.setDatabaseName(":memory:"); else db.setDatabaseName(mainApp->dbFileName()); if (db.open()) { setPragma(db); if (mainApp->storeDBMemory()) { sqliteDBMemFile(false); } } }
Database::Ptr Database::instance(Source source, OpenMode openMode) { Q_UNUSED(source) // for the time being std::lock_guard<std::mutex> lock(databases_mutex); // We are saving instances per thread and per read/write mode DatabaseInfo info; info.thread = QThread::currentThreadId(); info.openMode = openMode; // Do we have an instance matching the request? auto search = databases.find(info); if (search != databases.end()) { auto ptr = search->second.lock(); if (ptr) { return ptr; } } // Creating a new database instance auto ptr = std::make_shared<Database>(); ptr->d->database.reset(new QSqlDatabaseWrapper(info)); if (!ptr->d->database->isOpen()) { return Q_NULLPTR; } databases[info] = ptr; if (info.openMode == ReadOnly) { // From now on, only SELECT queries will work ptr->setPragma(QStringLiteral("query_only = 1")); // These should not make any difference ptr->setPragma(QStringLiteral("synchronous = 0")); } else { // Using the write-ahead log and sync = NORMAL for faster writes ptr->setPragma(QStringLiteral("synchronous = 1")); } // Maybe we should use the write-ahead log auto walResult = ptr->pragma(QStringLiteral("journal_mode = WAL")); if (walResult != "wal") { qFatal("KActivities: Database can not be opened in WAL mode. Check the " "SQLite version (required >3.7.0). And whether your filesystem " "supports shared memory"); } // We don't have a big database, lets flush the WAL when // it reaches 400k, not 4M as is default ptr->setPragma(QStringLiteral("wal_autocheckpoint = 100")); qDebug() << "KActivities: Database connection: " << ptr->d->database->connectionName() << "\n query_only: " << ptr->pragma(QStringLiteral("query_only")) << "\n journal_mode: " << ptr->pragma(QStringLiteral("journal_mode")) << "\n wal_autocheckpoint: " << ptr->pragma(QStringLiteral("wal_autocheckpoint")) << "\n synchronous: " << ptr->pragma(QStringLiteral("synchronous")) ; return ptr; }
void Database::prepareDatabase() { { QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "initialization"); db.setDatabaseName(mainApp->dbFileName()); if (!db.open()) { QString message = QString("Cannot open SQLite database! \n" "Error: %1").arg(db.lastError().text()); qCritical() << message; QMessageBox::critical(0, QObject::tr("Error"), message); } else { setPragma(db); QSqlQuery q(db); q.setForwardOnly(true); if (!mainApp->dbFileExists()) { qWarning() << "Creating database"; createTables(db); createLabels(db); q.prepare("INSERT INTO info(name, value) VALUES ('version', :version)"); q.bindValue(":version", version()); q.exec(); q.prepare("INSERT INTO info(name, value) VALUES('appVersion', :appVersion)"); q.bindValue(":appVersion", STRPRODUCTVER); q.exec(); } else { qWarning() << "Preparation database"; // Version DB > 0.12.1 Settings settings; int dbVersion = -1; q.exec("SELECT value FROM info WHERE name='version'"); if (q.first()) { dbVersion = q.value(0).toInt(); } QString appVersion = QString(); q.exec("SELECT value FROM info WHERE name='appVersion'"); if (q.first()) { appVersion = q.value(0).toString(); } // Create backups for DB and Settings if (appVersion != STRPRODUCTVER) { Common::createFileBackup(mainApp->dbFileName(), appVersion); Common::createFileBackup(settings.fileName(), appVersion); } if (dbVersion < 14) { q.exec("ALTER TABLE feeds ADD COLUMN showNotification integer default 0"); q.exec("ALTER TABLE feeds ADD COLUMN disableUpdate integer default 0"); q.exec("ALTER TABLE feeds ADD COLUMN javaScriptEnable integer default 1"); } if (dbVersion < 16) { q.exec("ALTER TABLE feeds ADD COLUMN layoutDirection integer default 0"); } // Update appVersion anyway if (appVersion.isEmpty()) { q.prepare("INSERT INTO info(name, value) VALUES('appVersion', :appVersion)"); q.bindValue(":appVersion", STRPRODUCTVER); q.exec(); } else if (appVersion != STRPRODUCTVER) { q.prepare("UPDATE info SET value=:appVersion WHERE name='appVersion'"); q.bindValue(":appVersion", STRPRODUCTVER); q.exec(); } if (dbVersion == -1) { q.prepare("INSERT INTO info(name, value) VALUES('version', :version)"); q.bindValue(":version", version()); q.exec(); } else if (dbVersion < version()) { q.prepare("UPDATE info SET value=:version WHERE name='version'"); q.bindValue(":version", version()); q.exec(); } settings.setValue("VersionDB", version()); } q.finish(); db.close(); } } QSqlDatabase::removeDatabase("initialization"); }