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");
}
