SQL-Queries are now stored in a resource. The bashscript
authorMarcus Eggenberger <egs@quassel-irc.org>
Mon, 7 Jan 2008 20:25:50 +0000 (20:25 +0000)
committerMarcus Eggenberger <egs@quassel-irc.org>
Mon, 7 Jan 2008 20:25:50 +0000 (20:25 +0000)
src/core/UPDATE_SQL_RESOURCES.sh adds all known sql query files to the
resource file. SqliteStorage derives from AbstractSqlStorage which
should make the integration of future sql engines way simpler. In
addition it features an automatic upgrade to newer schema versions.

48 files changed:
build/targets/core.pri
build/targets/core.pro
build/targets/monolithic.pro
dev-notes/ROADMAP
src/common/settings.cpp
src/core/SQL/SQLite/1/delete_backlog_by_uid.sql [new file with mode: 0644]
src/core/SQL/SQLite/1/delete_buffers_by_uid.sql [new file with mode: 0644]
src/core/SQL/SQLite/1/delete_networks_by_uid.sql [new file with mode: 0644]
src/core/SQL/SQLite/1/delete_quasseluser.sql [new file with mode: 0644]
src/core/SQL/SQLite/1/insert_buffer.sql [new file with mode: 0644]
src/core/SQL/SQLite/1/insert_message.sql [new file with mode: 0644]
src/core/SQL/SQLite/1/insert_network.sql [new file with mode: 0644]
src/core/SQL/SQLite/1/insert_quasseluser.sql [new file with mode: 0644]
src/core/SQL/SQLite/1/insert_sender.sql [new file with mode: 0644]
src/core/SQL/SQLite/1/select_authuser.sql [new file with mode: 0644]
src/core/SQL/SQLite/1/select_bufferByName.sql [new file with mode: 0644]
src/core/SQL/SQLite/1/select_buffers.sql [new file with mode: 0644]
src/core/SQL/SQLite/1/select_lastMessage.sql [new file with mode: 0644]
src/core/SQL/SQLite/1/select_messageRange.sql [new file with mode: 0644]
src/core/SQL/SQLite/1/select_messages.sql [new file with mode: 0644]
src/core/SQL/SQLite/1/select_messagesOffset.sql [new file with mode: 0644]
src/core/SQL/SQLite/1/select_messagesSince.sql [new file with mode: 0644]
src/core/SQL/SQLite/1/select_messagesSinceOffset.sql [new file with mode: 0644]
src/core/SQL/SQLite/1/select_userid.sql [new file with mode: 0644]
src/core/SQL/SQLite/1/setup_000_quasseluser.sql [new file with mode: 0644]
src/core/SQL/SQLite/1/setup_010_sender.sql [new file with mode: 0644]
src/core/SQL/SQLite/1/setup_020_network.sql [new file with mode: 0644]
src/core/SQL/SQLite/1/setup_040_buffer.sql [new file with mode: 0644]
src/core/SQL/SQLite/1/setup_050_buffer_idx.sql [new file with mode: 0644]
src/core/SQL/SQLite/1/setup_060_backlog.sql [new file with mode: 0644]
src/core/SQL/SQLite/1/setup_070_coreinfo.sql [new file with mode: 0644]
src/core/SQL/SQLite/1/setup_080_version.sql [new file with mode: 0644]
src/core/SQL/SQLite/1/update_username.sql [new file with mode: 0644]
src/core/SQL/SQLite/1/update_userpassword.sql [new file with mode: 0644]
src/core/SQL/SQLite/1/upgrade_000_drop_coreinfo.sql [new file with mode: 0644]
src/core/SQL/SQLite/1/upgrade_010_create_coreinfo.sql [new file with mode: 0644]
src/core/SQL/SQLite/1/upgrade_020_update_schemaversion.sql [new file with mode: 0644]
src/core/UPDATE_SQL_RESOURCE.sh [new file with mode: 0755]
src/core/abstractsqlstorage.cpp [new file with mode: 0644]
src/core/abstractsqlstorage.h [new file with mode: 0644]
src/core/core.cpp
src/core/core.h
src/core/core.pri
src/core/sql.qrc [new file with mode: 0644]
src/core/sqlitestorage.cpp
src/core/sqlitestorage.h
src/core/storage.cpp
src/core/storage.h

index 78d0811..2521b7d 100644 (file)
@@ -6,3 +6,4 @@ DEFINES = BUILD_CORE
 
 QT -= gui
 QT += network sql script
+
index 69d7840..973b94f 100644 (file)
@@ -1,2 +1,4 @@
 include(core.pri)
 include(target.pri)
+
+RESOURCES *= ../../src/core/sql.qrc
index 1b218a4..3c35668 100644 (file)
@@ -1,3 +1,5 @@
 include(monolithic.pri)
 include(target.pri)
+
 RESOURCES *= ../../src/icons/icons.qrc
+RESOURCES *= ../../src/core/sql.qrc
index 3267d7c..f7e4097 100644 (file)
@@ -5,6 +5,7 @@ Already Done:
       chatwidget state
       make signalproxy threadsafe
       utf-8 / encodings
+      externalize SQL-queries, provide migration path...
 
 Showstoppers:
 =============
@@ -18,7 +19,6 @@ Open: network settings in DB -> ?
       switch to network-IDs -> ?
       remove buffergroups (DB) -> ?
       insert buffertype field rein (DB) -> ?
-      externalize SQL-queries, provide migration path... -> EgS
       core-user admin, rights management (ACL) -> Sput?
 
 BUG:  multi-user join -> ?
index f35bdd7..d6ebb80 100644 (file)
 
 #include "settings.h"
 
-Settings::Settings(QString g, QString applicationName) : QSettings(QCoreApplication::organizationName(), applicationName), group(g) {
+Settings::Settings(QString g, QString applicationName)
+
+#ifdef Q_WS_MAC
+  : QSettings(QCoreApplication::organizationDomain(), applicationName),
+#else
+  : QSettings(QCoreApplication::organizationName(), applicationName),
+#endif
+    group(g)
+{
 
 /* we need to call the constructor immediately in order to set the path...
 #ifndef Q_WS_QWS
diff --git a/src/core/SQL/SQLite/1/delete_backlog_by_uid.sql b/src/core/SQL/SQLite/1/delete_backlog_by_uid.sql
new file mode 100644 (file)
index 0000000..b5520ec
--- /dev/null
@@ -0,0 +1,2 @@
+DELETE FROM backlog
+WHERE bufferid IN (SELECT DISTINCT bufferid FROM buffer WHERE userid = :userid)
diff --git a/src/core/SQL/SQLite/1/delete_buffers_by_uid.sql b/src/core/SQL/SQLite/1/delete_buffers_by_uid.sql
new file mode 100644 (file)
index 0000000..14f56ac
--- /dev/null
@@ -0,0 +1,2 @@
+DELETE FROM buffer
+WHERE userid = :userid
diff --git a/src/core/SQL/SQLite/1/delete_networks_by_uid.sql b/src/core/SQL/SQLite/1/delete_networks_by_uid.sql
new file mode 100644 (file)
index 0000000..0542d85
--- /dev/null
@@ -0,0 +1,2 @@
+DELETE FROM network
+WHERE userid = :userid
diff --git a/src/core/SQL/SQLite/1/delete_quasseluser.sql b/src/core/SQL/SQLite/1/delete_quasseluser.sql
new file mode 100644 (file)
index 0000000..f9bc9ae
--- /dev/null
@@ -0,0 +1,2 @@
+DELETE FROM quasseluser
+WHERE userid = :userid
diff --git a/src/core/SQL/SQLite/1/insert_buffer.sql b/src/core/SQL/SQLite/1/insert_buffer.sql
new file mode 100644 (file)
index 0000000..84ac02a
--- /dev/null
@@ -0,0 +1,2 @@
+INSERT INTO buffer (userid, networkid, buffername)
+VALUES (:userid, (SELECT networkid FROM network WHERE networkname = :networkname AND userid = :userid2), :buffername)
diff --git a/src/core/SQL/SQLite/1/insert_message.sql b/src/core/SQL/SQLite/1/insert_message.sql
new file mode 100644 (file)
index 0000000..20facb8
--- /dev/null
@@ -0,0 +1,2 @@
+INSERT INTO backlog (time, bufferid, type, flags, senderid, message)
+VALUES (:time, :bufferid, :type, :flags, (SELECT senderid FROM sender WHERE sender = :sender), :message)
diff --git a/src/core/SQL/SQLite/1/insert_network.sql b/src/core/SQL/SQLite/1/insert_network.sql
new file mode 100644 (file)
index 0000000..935cb96
--- /dev/null
@@ -0,0 +1,2 @@
+INSERT INTO network (userid, networkname)
+VALUES (:userid, :networkname)
diff --git a/src/core/SQL/SQLite/1/insert_quasseluser.sql b/src/core/SQL/SQLite/1/insert_quasseluser.sql
new file mode 100644 (file)
index 0000000..3a9ff8b
--- /dev/null
@@ -0,0 +1,2 @@
+INSERT INTO quasseluser (username, password)
+VALUES (:username, :password)
\ No newline at end of file
diff --git a/src/core/SQL/SQLite/1/insert_sender.sql b/src/core/SQL/SQLite/1/insert_sender.sql
new file mode 100644 (file)
index 0000000..0e84f63
--- /dev/null
@@ -0,0 +1,2 @@
+INSERT INTO sender (sender)
+VALUES (:sender)
\ No newline at end of file
diff --git a/src/core/SQL/SQLite/1/select_authuser.sql b/src/core/SQL/SQLite/1/select_authuser.sql
new file mode 100644 (file)
index 0000000..f11bf18
--- /dev/null
@@ -0,0 +1,3 @@
+SELECT userid
+FROM quasseluser
+WHERE username = :username AND password = :password
diff --git a/src/core/SQL/SQLite/1/select_bufferByName.sql b/src/core/SQL/SQLite/1/select_bufferByName.sql
new file mode 100644 (file)
index 0000000..ef5e1a1
--- /dev/null
@@ -0,0 +1,4 @@
+SELECT bufferid
+FROM buffer
+JOIN network ON buffer.networkid = network.networkid
+WHERE network.networkname = :networkname AND network.userid = :userid AND buffer.userid = :userid2 AND lower(buffer.buffername) = lower(:buffername)
diff --git a/src/core/SQL/SQLite/1/select_buffers.sql b/src/core/SQL/SQLite/1/select_buffers.sql
new file mode 100644 (file)
index 0000000..11e21c5
--- /dev/null
@@ -0,0 +1,5 @@
+SELECT DISTINCT buffer.bufferid, buffername, network.networkid, networkname
+FROM buffer
+JOIN network ON buffer.networkid = network.networkid
+JOIN backlog ON buffer.bufferid = backlog.bufferid
+WHERE buffer.userid = :userid AND time >= :time
diff --git a/src/core/SQL/SQLite/1/select_lastMessage.sql b/src/core/SQL/SQLite/1/select_lastMessage.sql
new file mode 100644 (file)
index 0000000..bbd3b1d
--- /dev/null
@@ -0,0 +1,2 @@
+SELECT messageid FROM backlog
+WHERE time = :time AND bufferid = :bufferid AND type = :type AND senderid = (SELECT senderid FROM sender WHERE sender = :sender)
diff --git a/src/core/SQL/SQLite/1/select_messageRange.sql b/src/core/SQL/SQLite/1/select_messageRange.sql
new file mode 100644 (file)
index 0000000..55dd073
--- /dev/null
@@ -0,0 +1,7 @@
+SELECT messageid, time,  type, flags, sender, message, displayname
+FROM backlog
+JOIN buffer ON backlog.bufferid = buffer.bufferid
+JOIN sender ON backlog.senderid = sender.senderid
+LEFT JOIN buffergroup ON buffer.groupid = buffergroup.groupid
+WHERE (buffer.bufferid = :bufferid OR buffer.groupid = (SELECT groupid FROM buffer WHERE bufferid = :bufferid2)) AND backlog.messageid >= :firstmsg AND backlog.messageid <= :lastmsg
+ORDER BY messageid DESC
diff --git a/src/core/SQL/SQLite/1/select_messages.sql b/src/core/SQL/SQLite/1/select_messages.sql
new file mode 100644 (file)
index 0000000..b9596e7
--- /dev/null
@@ -0,0 +1,8 @@
+SELECT messageid, time,  type, flags, sender, message, displayname
+FROM backlog
+JOIN buffer ON backlog.bufferid = buffer.bufferid
+JOIN sender ON backlog.senderid = sender.senderid
+LEFT JOIN buffergroup ON buffer.groupid = buffergroup.groupid
+WHERE buffer.bufferid = :bufferid OR buffer.groupid = (SELECT groupid FROM buffer WHERE bufferid = :bufferid2)
+ORDER BY messageid DESC
+LIMIT :limit OFFSET :offset
diff --git a/src/core/SQL/SQLite/1/select_messagesOffset.sql b/src/core/SQL/SQLite/1/select_messagesOffset.sql
new file mode 100644 (file)
index 0000000..7c8795f
--- /dev/null
@@ -0,0 +1,3 @@
+SELECT count(*)
+FROM backlog
+WHERE bufferid = :bufferid AND messageid < :messageid
diff --git a/src/core/SQL/SQLite/1/select_messagesSince.sql b/src/core/SQL/SQLite/1/select_messagesSince.sql
new file mode 100644 (file)
index 0000000..9776b58
--- /dev/null
@@ -0,0 +1,8 @@
+SELECT messageid, time,  type, flags, sender, message, displayname
+FROM backlog
+JOIN buffer ON backlog.bufferid = buffer.bufferid
+JOIN sender ON backlog.senderid = sender.senderid
+LEFT JOIN buffergroup ON buffer.groupid = buffergroup.groupid
+WHERE (buffer.bufferid = :bufferid OR buffer.groupid = (SELECT groupid FROM buffer WHERE bufferid = :bufferid2)) AND backlog.time >= :since
+ORDER BY messageid DESC
+LIMIT -1 OFFSET :offset
diff --git a/src/core/SQL/SQLite/1/select_messagesSinceOffset.sql b/src/core/SQL/SQLite/1/select_messagesSinceOffset.sql
new file mode 100644 (file)
index 0000000..65f2cc2
--- /dev/null
@@ -0,0 +1,3 @@
+SELECT count(*)
+FROM backlog
+WHERE bufferid = :bufferid AND time >= :since
diff --git a/src/core/SQL/SQLite/1/select_userid.sql b/src/core/SQL/SQLite/1/select_userid.sql
new file mode 100644 (file)
index 0000000..181c1b3
--- /dev/null
@@ -0,0 +1,3 @@
+SELECT userid
+FROM quasseluser
+WHERE username = :username
diff --git a/src/core/SQL/SQLite/1/setup_000_quasseluser.sql b/src/core/SQL/SQLite/1/setup_000_quasseluser.sql
new file mode 100644 (file)
index 0000000..10dd533
--- /dev/null
@@ -0,0 +1,6 @@
+CREATE TABLE quasseluser (
+       userid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+       username TEXT UNIQUE NOT NULL,
+       password BLOB NOT NULL)
+
+         
diff --git a/src/core/SQL/SQLite/1/setup_010_sender.sql b/src/core/SQL/SQLite/1/setup_010_sender.sql
new file mode 100644 (file)
index 0000000..66d5755
--- /dev/null
@@ -0,0 +1,5 @@
+CREATE TABLE sender (
+       senderid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+       sender TEXT UNIQUE NOT NULL)
+
+         
diff --git a/src/core/SQL/SQLite/1/setup_020_network.sql b/src/core/SQL/SQLite/1/setup_020_network.sql
new file mode 100644 (file)
index 0000000..9a84689
--- /dev/null
@@ -0,0 +1,5 @@
+CREATE TABLE network (
+       networkid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+       userid INTEGER NOT NULL,
+       networkname TEXT NOT NULL,
+       UNIQUE (userid, networkname))
diff --git a/src/core/SQL/SQLite/1/setup_040_buffer.sql b/src/core/SQL/SQLite/1/setup_040_buffer.sql
new file mode 100644 (file)
index 0000000..a7473fa
--- /dev/null
@@ -0,0 +1,6 @@
+CREATE TABLE buffer (
+       bufferid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+       userid INTEGER NOT NULL,
+       groupid INTEGER,
+       networkid INTEGER NOT NULL,
+       buffername TEXT NOT NULL)
diff --git a/src/core/SQL/SQLite/1/setup_050_buffer_idx.sql b/src/core/SQL/SQLite/1/setup_050_buffer_idx.sql
new file mode 100644 (file)
index 0000000..042ce3a
--- /dev/null
@@ -0,0 +1,2 @@
+CREATE UNIQUE INDEX buffer_idx 
+       ON buffer(userid, networkid, buffername)
diff --git a/src/core/SQL/SQLite/1/setup_060_backlog.sql b/src/core/SQL/SQLite/1/setup_060_backlog.sql
new file mode 100644 (file)
index 0000000..d3e191d
--- /dev/null
@@ -0,0 +1,8 @@
+CREATE TABLE backlog (
+       messageid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+       time INTEGER NOT NULL,
+       bufferid INTEGER NOT NULL,
+       type INTEGER NOT NULL,
+       flags INTEGER NOT NULL,
+       senderid INTEGER NOT NULL,
+       message TEXT)
diff --git a/src/core/SQL/SQLite/1/setup_070_coreinfo.sql b/src/core/SQL/SQLite/1/setup_070_coreinfo.sql
new file mode 100644 (file)
index 0000000..6a6415d
--- /dev/null
@@ -0,0 +1,3 @@
+CREATE TABLE coreinfo (
+       key TEXT NOT NULL PRIMARY KEY,
+       value TEXT)
diff --git a/src/core/SQL/SQLite/1/setup_080_version.sql b/src/core/SQL/SQLite/1/setup_080_version.sql
new file mode 100644 (file)
index 0000000..323d569
--- /dev/null
@@ -0,0 +1 @@
+INSERT INTO coreinfo (key, value) VALUES ('schemaversion', '1')
diff --git a/src/core/SQL/SQLite/1/update_username.sql b/src/core/SQL/SQLite/1/update_username.sql
new file mode 100644 (file)
index 0000000..951e17a
--- /dev/null
@@ -0,0 +1,3 @@
+UPDATE quasseluser
+SET username = :username
+WHERE userid = :userid
diff --git a/src/core/SQL/SQLite/1/update_userpassword.sql b/src/core/SQL/SQLite/1/update_userpassword.sql
new file mode 100644 (file)
index 0000000..44a514a
--- /dev/null
@@ -0,0 +1,3 @@
+UPDATE quasseluser
+SET password = :password
+WHERE userid = :userid
diff --git a/src/core/SQL/SQLite/1/upgrade_000_drop_coreinfo.sql b/src/core/SQL/SQLite/1/upgrade_000_drop_coreinfo.sql
new file mode 100644 (file)
index 0000000..7581705
--- /dev/null
@@ -0,0 +1 @@
+DROP TABLE coreinfo
diff --git a/src/core/SQL/SQLite/1/upgrade_010_create_coreinfo.sql b/src/core/SQL/SQLite/1/upgrade_010_create_coreinfo.sql
new file mode 100644 (file)
index 0000000..6a6415d
--- /dev/null
@@ -0,0 +1,3 @@
+CREATE TABLE coreinfo (
+       key TEXT NOT NULL PRIMARY KEY,
+       value TEXT)
diff --git a/src/core/SQL/SQLite/1/upgrade_020_update_schemaversion.sql b/src/core/SQL/SQLite/1/upgrade_020_update_schemaversion.sql
new file mode 100644 (file)
index 0000000..323d569
--- /dev/null
@@ -0,0 +1 @@
+INSERT INTO coreinfo (key, value) VALUES ('schemaversion', '1')
diff --git a/src/core/UPDATE_SQL_RESOURCE.sh b/src/core/UPDATE_SQL_RESOURCE.sh
new file mode 100755 (executable)
index 0000000..17d10f3
--- /dev/null
@@ -0,0 +1,11 @@
+#!/bin/bash
+
+cat > sql.qrc <<EOF
+<!DOCTYPE RCC><RCC version="1.0">
+<qresource>
+EOF
+find . -name \*.sql -exec echo "    <file>{}</file>" \; >> sql.qrc
+cat >> sql.qrc <<EOF
+</qresource>
+</RCC>
+EOF
diff --git a/src/core/abstractsqlstorage.cpp b/src/core/abstractsqlstorage.cpp
new file mode 100644 (file)
index 0000000..b29c88c
--- /dev/null
@@ -0,0 +1,230 @@
+/***************************************************************************
+ *   Copyright (C) 2005-07 by the Quassel IRC Team                         *
+ *   devel@quassel-irc.org                                                 *
+ *                                                                         *
+ *   This program is free software; you can redistribute it and/or modify  *
+ *   it under the terms of the GNU General Public License as published by  *
+ *   the Free Software Foundation; either version 2 of the License, or     *
+ *   (at your option) version 3.                                           *
+ *                                                                         *
+ *   This program is distributed in the hope that it will be useful,       *
+ *   but WITHOUT ANY WARRANTY; without even the implied warranty of        *
+ *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the         *
+ *   GNU General Public License for more details.                          *
+ *                                                                         *
+ *   You should have received a copy of the GNU General Public License     *
+ *   along with this program; if not, write to the                         *
+ *   Free Software Foundation, Inc.,                                       *
+ *   59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.             *
+ ***************************************************************************/
+
+#include "abstractsqlstorage.h"
+
+#include <QSqlError>
+#include <QSqlQuery>
+
+AbstractSqlStorage::AbstractSqlStorage(QObject *parent)
+  : Storage(parent),
+    _schemaVersion(0)
+{
+}
+
+AbstractSqlStorage::~AbstractSqlStorage() {
+  {
+    QSqlDatabase db = QSqlDatabase::database("quassel_connection");
+    db.commit();
+    db.close();
+  }
+  QSqlDatabase::removeDatabase("quassel_connection");  
+}
+
+QSqlDatabase AbstractSqlStorage::logDb() {
+  QSqlDatabase db = QSqlDatabase::database("quassel_connection");
+  if(db.isValid() && db.isOpen())
+    return db;
+
+  if(!openDb()) {
+    qWarning() << "Unable to Open Database" << engineName();
+    qWarning() << " -" << db.lastError().text();
+  }
+
+  return QSqlDatabase::database("quassel_connection");
+}
+
+bool AbstractSqlStorage::openDb() {
+  QSqlDatabase db = QSqlDatabase::database("quassel_connection");
+  if(db.isValid() && !db.isOpen())
+    return db.open();
+
+  db = QSqlDatabase::addDatabase(driverName(), "quassel_connection");
+  db.setDatabaseName(databaseName());
+
+  if(!hostName().isEmpty())
+    db.setHostName(hostName());
+
+  if(!userName().isEmpty()) {
+    db.setUserName(userName());
+    db.setPassword(password());
+  }
+
+  return db.open();
+}
+
+bool AbstractSqlStorage::init(const QVariantMap &settings) {
+  Q_UNUSED(settings)
+  QSqlDatabase db = logDb();
+  if(!db.isValid() || !db.isOpen())
+    return false;
+
+  if(installedSchemaVersion() == -1) {
+    qDebug() << "Storage Schema is missing!";
+    return false;
+  }
+
+  if(installedSchemaVersion() > schemaVersion()) {
+    qWarning() << "Installed Schema is newer then any known Version.";
+    return false;
+  }
+  
+  if(installedSchemaVersion() < schemaVersion()) {
+    qWarning() << "Installed Schema is not up to date. Upgrading...";
+    if(!upgradeDb())
+      return false;
+  }
+  
+  qDebug() << "Storage Backend is ready. Quassel Schema Version:" << installedSchemaVersion();
+  return true;
+}
+
+QString AbstractSqlStorage::queryString(const QString &queryName, int version) {
+  if(version == 0)
+    version = schemaVersion();
+    
+  QFileInfo queryInfo(QString(":/SQL/%1/%2/%3.sql").arg(engineName()).arg(version).arg(queryName));
+  if(!queryInfo.exists() || !queryInfo.isFile() || !queryInfo.isReadable()) {
+    qWarning() << "Unable to read SQL-Query" << queryName << "for Engine" << engineName();
+    return QString();
+  }
+
+  QFile queryFile(queryInfo.filePath());
+  if(!queryFile.open(QIODevice::ReadOnly | QIODevice::Text))
+    return QString();
+  QString query = QTextStream(&queryFile).readAll();
+  queryFile.close();
+  
+  return query.trimmed();
+}
+
+QString AbstractSqlStorage::queryString(const QString &queryName) {
+  return queryString(queryName, 0);
+}
+
+QSqlQuery *AbstractSqlStorage::cachedQuery(const QString &queryName, int version) {
+  QPair<QString, int> queryId = qMakePair(queryName, version);
+  if(!_queryCache.contains(queryId)) {
+    QSqlQuery *query = new QSqlQuery(logDb());
+    query->prepare(queryString(queryName, version));
+    _queryCache[queryId] = query;
+  }
+
+  return _queryCache[queryId];
+}
+
+QSqlQuery *AbstractSqlStorage::cachedQuery(const QString &queryName) {
+  return cachedQuery(queryName, 0);
+}
+
+QStringList AbstractSqlStorage::setupQueries() {
+  QStringList queries;
+  QDir dir = QDir(QString(":/SQL/%1/%2/").arg(engineName()).arg(schemaVersion()));
+  foreach(QFileInfo fileInfo, dir.entryInfoList(QStringList() << "setup*", QDir::NoFilter, QDir::Name)) {
+    queries << queryString(fileInfo.baseName());
+  }
+  return queries;
+}
+
+bool AbstractSqlStorage::setup(const QVariantMap &settings) {
+  Q_UNUSED(settings)
+  QSqlDatabase db = logDb();
+  if(!db.isOpen()) {
+    qWarning() << "Unable to setup Logging Backend!";
+    return false;
+  }
+
+  foreach(QString queryString, setupQueries()) {
+    QSqlQuery query = db.exec(queryString);
+    if(!watchQuery(&query)) {
+      qWarning() << "Unable to setup Logging Backend!";
+      return false;
+    }
+  }
+  return true;
+}
+
+QStringList AbstractSqlStorage::upgradeQueries(int version) {
+  QStringList queries;
+  QDir dir = QDir(QString(":/SQL/%1/%2/").arg(engineName()).arg(version));
+  foreach(QFileInfo fileInfo, dir.entryInfoList(QStringList() << "upgrade*", QDir::NoFilter, QDir::Name)) {
+    qDebug() << queryString(fileInfo.baseName());
+    queries << queryString(fileInfo.baseName());
+  }
+  return queries;
+}
+
+bool AbstractSqlStorage::upgradeDb() {
+  if(schemaVersion() <= installedSchemaVersion())
+    return true;
+
+  QSqlDatabase db = logDb();
+
+  for(int ver = installedSchemaVersion() + 1; ver <= schemaVersion(); ver++) {
+    foreach(QString queryString, upgradeQueries(ver)) {
+      QSqlQuery query = db.exec(queryString);
+      if(!watchQuery(&query)) {
+       qWarning() << "Unable to upgrade Logging Backend!";
+       return false;
+      }
+    }
+  }
+  return true;
+}
+
+
+int AbstractSqlStorage::schemaVersion() {
+  // returns the newest Schema Version!
+  // not the currently used one! (though it can be the same)
+  if(_schemaVersion > 0)
+    return _schemaVersion;
+
+  int version;
+  bool ok;
+  QDir dir = QDir(":/SQL/" + engineName());
+  foreach(QFileInfo fileInfo, dir.entryInfoList()) {
+    if(!fileInfo.isDir())
+      continue;
+
+    version = fileInfo.fileName().toInt(&ok);
+    if(!ok)
+      continue;
+
+    if(version > _schemaVersion)
+      _schemaVersion = version;
+  }
+  return _schemaVersion;
+}
+
+bool AbstractSqlStorage::watchQuery(QSqlQuery *query) {
+  if(query->lastError().isValid()) {
+    qWarning() << "unhandled Error in QSqlQuery!";
+    qWarning() << "                  last Query:" << query->lastQuery();
+    qWarning() << "              executed Query:" << query->executedQuery();
+    qWarning() << "                bound Values:" << query->boundValues();
+    qWarning() << "                Error Number:" << query->lastError().number();
+    qWarning() << "               Error Message:" << query->lastError().text();
+    qWarning() << "              Driver Message:" << query->lastError().driverText();
+    qWarning() << "                  DB Message:" << query->lastError().databaseText();
+    
+    return false;
+  }
+  return true;
+}
diff --git a/src/core/abstractsqlstorage.h b/src/core/abstractsqlstorage.h
new file mode 100644 (file)
index 0000000..7002699
--- /dev/null
@@ -0,0 +1,79 @@
+/***************************************************************************
+ *   Copyright (C) 2005-07 by the Quassel IRC Team                         *
+ *   devel@quassel-irc.org                                                 *
+ *                                                                         *
+ *   This program is free software; you can redistribute it and/or modify  *
+ *   it under the terms of the GNU General Public License as published by  *
+ *   the Free Software Foundation; either version 2 of the License, or     *
+ *   (at your option) version 3.                                           *
+ *                                                                         *
+ *   This program is distributed in the hope that it will be useful,       *
+ *   but WITHOUT ANY WARRANTY; without even the implied warranty of        *
+ *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the         *
+ *   GNU General Public License for more details.                          *
+ *                                                                         *
+ *   You should have received a copy of the GNU General Public License     *
+ *   along with this program; if not, write to the                         *
+ *   Free Software Foundation, Inc.,                                       *
+ *   59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.             *
+ ***************************************************************************/
+
+#ifndef ABSTRACTSQLSTORAGE_H
+#define ABSTRACTSQLSTORAGE_H
+
+#include "storage.h"
+
+#include <QSqlDatabase>
+
+class QSqlQuery;
+
+class AbstractSqlStorage : public Storage {
+  Q_OBJECT
+
+public:
+  AbstractSqlStorage(QObject *parent = 0);
+  virtual ~AbstractSqlStorage();
+
+  //! Returns the name of the storage backend engine
+  /** \return A virtual equivalent of displayName() */
+  virtual QString engineName() { return ""; }
+  
+protected:
+  bool init(const QVariantMap &settings = QVariantMap());
+  
+  QSqlDatabase logDb();
+  
+  QString queryString(const QString &queryName, int version);
+  QString queryString(const QString &queryName);
+
+  QSqlQuery *cachedQuery(const QString &queryName, int version);
+  QSqlQuery *cachedQuery(const QString &queryName);
+
+  QStringList setupQueries();
+  bool setup(const QVariantMap &settings = QVariantMap());
+
+  QStringList upgradeQueries(int ver);
+  bool upgradeDb();
+
+  bool watchQuery(QSqlQuery *query);
+  
+  int schemaVersion();
+  virtual int installedSchemaVersion() { return -1; };
+
+  virtual QString driverName() = 0;
+  inline virtual QString hostName() { return QString(); }
+  virtual QString databaseName() = 0;
+  inline virtual QString userName() { return QString(); }
+  inline virtual QString password() { return QString(); }
+
+private:
+  bool openDb();
+
+  int _schemaVersion;
+
+  QHash<QPair<QString, int>, QSqlQuery *> _queryCache;
+
+};
+
+
+#endif
index 4f97c3a..0f007d3 100644 (file)
@@ -27,6 +27,8 @@
 #include <QMetaObject>
 #include <QMetaMethod>
 
+#include <QCoreApplication>
+
 Core *Core::instanceptr = 0;
 
 Core *Core::instance() {
@@ -41,25 +43,34 @@ void Core::destroy() {
   instanceptr = 0;
 }
 
-Core::Core() {
-  storage = 0;
+Core::Core()
+  : storage(0)
+{
 }
 
 void Core::init() {
-
   // TODO: Remove this again at some point
   // Check if old core settings need to be migrated in order to make the switch to the
   // new location less painful.
   CoreSettings cs;
   QVariant foo = cs.databaseSettings();
+  
   if(!foo.isValid()) {
     // ok, no settings stored yet. check for old ones.
+#ifdef Q_WS_MAC
+    QSettings os("quassel-irc.org", "Quassel IRC", this);
+#else
     QSettings os("Quassel IRC Development Team", "Quassel IRC");
+#endif
     QVariant bar = os.value("Core/DatabaseSettings");
     if(bar.isValid()) {
       // old settings available -- migrate!
       qWarning() << "\n\nOld settings detected. Will migrate core settings to the new location...\nNOTE: GUI style settings won't be migrated!\n";
+#ifdef Q_WS_MAC
+      QSettings ncs("quassel-irc.org", "Quassel Core");
+#else
       QSettings ncs("Quassel Project", "Quassel Core");
+#endif
       ncs.setValue("Core/CoreState", os.value("Core/CoreState"));
       ncs.setValue("Core/DatabaseSettings", os.value("Core/DatabaseSettings"));
       os.beginGroup("SessionData");
@@ -68,8 +79,11 @@ void Core::init() {
         ncs.setValue(QString("CoreUser/%1/SessionData/Networks").arg(group), os.value(QString("%1/Networks").arg(group)));
       }
       os.endGroup();
-
+#ifdef Q_WS_MAC
+      QSettings ngs("quassel-irc.org", "Quassel Client");
+#else
       QSettings ngs("Quassel Project", "Quassel Client");
+#endif
       os.beginGroup("Accounts");
       foreach(QString key, os.childKeys()) {
         ngs.setValue(QString("Accounts/%1").arg(key), os.value(key));
@@ -91,51 +105,48 @@ void Core::init() {
   }
   // END
 
-  CoreSettings s;
   configured = false;
 
-  QVariantMap dbSettings = s.databaseSettings().toMap();
-  QString hname = dbSettings["Type"].toString().toLower();
-  hname[0] = hname[0].toUpper();
-  hname = "initStorage" + hname;
-  if (!QMetaObject::invokeMethod(this, hname.toAscii(), Q_RETURN_ARG(bool, configured),  Q_ARG(QVariantMap, dbSettings), Q_ARG(bool, false))) {
-    qWarning("No database backend configured.");
-  }
-  
-  if (!configured) {
+  if(!(configured = initStorage(cs.databaseSettings().toMap()))) {
     qWarning("Core is currently not configured!");
   }
-    
+  
   connect(&server, SIGNAL(newConnection()), this, SLOT(incomingConnection()));
-  startListening(s.port());
+  startListening(cs.port());
   guiUser = 0;
 
 }
 
-bool Core::initStorageSqlite(QVariantMap dbSettings, bool setup) {
-  if (!SqliteStorage::isAvailable()) {
-    qFatal("Sqlite is currently required! Please make sure your Qt library has sqlite support enabled.");
-  }
-  if (storage) {
+bool Core::initStorage(QVariantMap dbSettings, bool setup) {
+  QString engine = dbSettings["Type"].toString().toLower();
+
+  if(storage) {
     qDebug() << "Deleting old storage object.";
-    delete storage;
-    storage = NULL;
+    storage->deleteLater();
+    storage = 0;
   }
-  
-  storage = new SqliteStorage();
-  if (setup && !storage->setup(dbSettings)) {
-    return false;
+
+  // FIXME register new storageProviders here
+  if(engine == "sqlite" && SqliteStorage::isAvailable()) {
+    storage = new SqliteStorage(this);
+  } else {
+    qWarning() << "Selected StorageBackend is not available:" << dbSettings["Type"].toString();
+    return configured = false;
   }
-  
-  return storage->init(dbSettings);
+
+  if(setup && !storage->setup(dbSettings)) {
+    return configured = false;
+  }
+
+  return configured = storage->init(dbSettings);
+}
+                                               
+bool Core::initStorage(QVariantMap dbSettings) {
+  return initStorage(dbSettings, false);
 }
 
 Core::~Core() {
   qDeleteAll(sessions);
-  if (storage) {
-    delete storage;
-    storage = NULL;
-  }
 }
 
 void Core::restoreState() {
@@ -243,7 +254,7 @@ void Core::clientHasData() {
       return;
     }
   }
-  blockSizes[socket] = bsize = 0;  // FIXME blockSizes aufrum0rn!
+  blockSizes[socket] = bsize = 0;  // FIXME blockSizes aufräum0rn!
 }
 
 // FIXME: no longer called, since connection handling is now in SignalProxy
@@ -294,13 +305,8 @@ void Core::processCoreSetup(QTcpSocket *socket, QVariantMap &msg) {
     msg.remove("User");
     msg.remove("Password");
     qDebug() << "Initializing storage provider" << msg["Type"].toString();
-    QString hname = msg["Type"].toString().toLower();
-    hname[0] = hname[0].toUpper();
-    hname = "initStorage" + hname;
-    if (!QMetaObject::invokeMethod(this, hname.toAscii(), Q_RETURN_ARG(bool, configured),  Q_ARG(QVariantMap, msg), Q_ARG(bool, true))) {
-      qWarning("No database backend configured.");
-    }
-    if (!configured) {
+
+    if(!initStorage(msg, true)) {
       // notify client to start wizard again
       qWarning("Core is currently not configured!");
       QVariantMap reply;
@@ -344,7 +350,7 @@ QStringList Core::availableStorageProviders() {
     storageProviders.append(SqliteStorage::displayName());
   }
   // TODO: temporary
-  storageProviders.append("MySQL");
+  // storageProviders.append("MySQL");
   
   return storageProviders;
 }
index e620389..ae39dc1 100644 (file)
@@ -56,7 +56,8 @@ class Core : public QObject {
     void clientHasData();
     void clientDisconnected();
 
-    bool initStorageSqlite(QVariantMap dbSettings, bool setup);
+    bool initStorage(QVariantMap dbSettings, bool setup);
+    bool initStorage(QVariantMap dbSettings);
 
   private:
     Core();
index 033d2b4..2de6475 100644 (file)
@@ -1,6 +1,6 @@
 DEPMOD = common
 QT_MOD = core network sql script
-SRCS = core.cpp coresession.cpp coresettings.cpp server.cpp sqlitestorage.cpp storage.cpp basichandler.cpp \
+SRCS = core.cpp coresession.cpp coresettings.cpp server.cpp sqlitestorage.cpp abstractsqlstorage.cpp storage.cpp basichandler.cpp \
        ircserverhandler.cpp userinputhandler.cpp ctcphandler.cpp coreusersettings.cpp
-HDRS = core.h coresession.h coresettings.h server.h sqlitestorage.h storage.h basichandler.h \
+HDRS = core.h coresession.h coresettings.h server.h sqlitestorage.h abstractsqlstorage.h storage.h basichandler.h \
        ircserverhandler.h userinputhandler.h ctcphandler.h coreusersettings.h
diff --git a/src/core/sql.qrc b/src/core/sql.qrc
new file mode 100644 (file)
index 0000000..f4da999
--- /dev/null
@@ -0,0 +1,36 @@
+<!DOCTYPE RCC><RCC version="1.0">
+<qresource>
+    <file>./SQL/SQLite/1/delete_backlog_by_uid.sql</file>
+    <file>./SQL/SQLite/1/delete_buffers_by_uid.sql</file>
+    <file>./SQL/SQLite/1/delete_networks_by_uid.sql</file>
+    <file>./SQL/SQLite/1/delete_quasseluser.sql</file>
+    <file>./SQL/SQLite/1/insert_buffer.sql</file>
+    <file>./SQL/SQLite/1/insert_message.sql</file>
+    <file>./SQL/SQLite/1/insert_network.sql</file>
+    <file>./SQL/SQLite/1/insert_quasseluser.sql</file>
+    <file>./SQL/SQLite/1/insert_sender.sql</file>
+    <file>./SQL/SQLite/1/select_authuser.sql</file>
+    <file>./SQL/SQLite/1/select_bufferByName.sql</file>
+    <file>./SQL/SQLite/1/select_buffers.sql</file>
+    <file>./SQL/SQLite/1/select_lastMessage.sql</file>
+    <file>./SQL/SQLite/1/select_messageRange.sql</file>
+    <file>./SQL/SQLite/1/select_messages.sql</file>
+    <file>./SQL/SQLite/1/select_messagesOffset.sql</file>
+    <file>./SQL/SQLite/1/select_messagesSince.sql</file>
+    <file>./SQL/SQLite/1/select_messagesSinceOffset.sql</file>
+    <file>./SQL/SQLite/1/select_userid.sql</file>
+    <file>./SQL/SQLite/1/setup_000_quasseluser.sql</file>
+    <file>./SQL/SQLite/1/setup_010_sender.sql</file>
+    <file>./SQL/SQLite/1/setup_020_network.sql</file>
+    <file>./SQL/SQLite/1/setup_040_buffer.sql</file>
+    <file>./SQL/SQLite/1/setup_050_buffer_idx.sql</file>
+    <file>./SQL/SQLite/1/setup_060_backlog.sql</file>
+    <file>./SQL/SQLite/1/setup_070_coreinfo.sql</file>
+    <file>./SQL/SQLite/1/setup_080_version.sql</file>
+    <file>./SQL/SQLite/1/update_username.sql</file>
+    <file>./SQL/SQLite/1/update_userpassword.sql</file>
+    <file>./SQL/SQLite/1/upgrade_000_drop_coreinfo.sql</file>
+    <file>./SQL/SQLite/1/upgrade_010_create_coreinfo.sql</file>
+    <file>./SQL/SQLite/1/upgrade_020_update_schemaversion.sql</file>
+</qresource>
+</RCC>
index d81deee..90a111e 100644 (file)
@@ -1,5 +1,5 @@
 /***************************************************************************
- *   Copyright (C) 2005-08 by the Quassel Project                          *
+ *   Copyright (C) 2005-07 by the Quassel IRC Team                         *
  *   devel@quassel-irc.org                                                 *
  *                                                                         *
  *   This program is free software; you can redistribute it and/or modify  *
 
 #include "sqlitestorage.h"
 
+#include <QCryptographicHash>
+
 #include <QtSql>
 
-SqliteStorage::SqliteStorage() {
-  logMessageQuery = NULL;
-  addSenderQuery = NULL;
-  getLastMessageIdQuery = NULL;
-  requestMsgsQuery = NULL;
-  requestMsgsOffsetQuery = NULL;
-  requestMsgsSinceQuery = NULL;
-  requestMsgsSinceOffsetQuery = NULL;
-  requestMsgRangeQuery = NULL;
-  createNetworkQuery = NULL;
-  createBufferQuery = NULL;
-  getBufferInfoQuery = NULL;
+SqliteStorage::SqliteStorage(QObject *parent)
+  : AbstractSqlStorage(parent)
+{
 }
 
 SqliteStorage::~SqliteStorage() {
-  if (logMessageQuery) delete logMessageQuery;
-  if (addSenderQuery) delete addSenderQuery;
-  if (getLastMessageIdQuery) delete getLastMessageIdQuery;
-  if (requestMsgsQuery) delete requestMsgsQuery;
-  if (requestMsgsOffsetQuery) delete requestMsgsOffsetQuery;
-  if (requestMsgsSinceQuery) delete requestMsgsSinceQuery;
-  if (requestMsgsSinceOffsetQuery) delete requestMsgsSinceOffsetQuery;
-  if (requestMsgRangeQuery) delete requestMsgRangeQuery;
-  if (createNetworkQuery) delete createNetworkQuery;
-  if (createBufferQuery) delete createBufferQuery;
-  if (getBufferInfoQuery) delete getBufferInfoQuery;
-  
-  logDb.close();
 }
 
 bool SqliteStorage::isAvailable() {
@@ -58,184 +38,32 @@ bool SqliteStorage::isAvailable() {
 }
 
 QString SqliteStorage::displayName() {
-  return QString("SQlite");
+  return QString("SQLite");
 }
 
-bool SqliteStorage::setup(const QVariantMap &settings) {
-  Q_UNUSED(settings);
-  bool ok;
-  // this extra scope is needed to be able to remove the database connection later
-  {
-    logDb = QSqlDatabase::addDatabase("QSQLITE", "quassel_setup");
-    logDb.setDatabaseName(SqliteStorage::backlogFile(true));
-    ok = logDb.open();
-    
-    if (!ok) {
-      qWarning(tr("Could not open backlog database: %1").arg(logDb.lastError().text()).toAscii());
-    } else {
-      logDb.exec("CREATE TABLE quasseluser ("
-                    "userid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
-                    "username TEXT UNIQUE NOT NULL,"
-                    "password BLOB NOT NULL)");
-         
-      logDb.exec("CREATE TABLE sender ("
-                    "senderid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
-                    "sender TEXT UNIQUE NOT NULL)");
-         
-      logDb.exec("CREATE TABLE network ("
-                    "networkid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
-                    "userid INTEGER NOT NULL,"
-                    "networkname TEXT NOT NULL,"
-                    "UNIQUE (userid, networkname))");
-         
-      logDb.exec("CREATE TABLE buffergroup ("
-                    "groupid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
-                    "userid INTEGER NOT NULL,"
-                    "displayname TEXT)");
-         
-      logDb.exec("CREATE TABLE buffer ("
-                    "bufferid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
-                    "userid INTEGER NOT NULL,"
-                    "groupid INTEGER,"
-                    "networkid INTEGER NOT NULL,"
-                    "buffername TEXT NOT NULL)");
-         
-      logDb.exec("CREATE UNIQUE INDEX buffer_idx "
-                    "ON buffer(userid, networkid, buffername)");
-           
-      logDb.exec("CREATE TABLE backlog ("
-                    "messageid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
-                    "time INTEGER NOT NULL,"
-                    "bufferid INTEGER NOT NULL,"
-                    "type INTEGER NOT NULL,"
-                    "flags INTEGER NOT NULL,"
-                    "senderid INTEGER NOT NULL,"
-                    "message TEXT)");
-         
-      logDb.exec("CREATE TABLE coreinfo ("
-                    "updateid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
-                    "version INTEGER NOT NULL)");
-         
-      logDb.exec("INSERT INTO coreinfo (version) VALUES (0)");
-      
-      // something fucked up -> no logging possible
-      // FIXME logDb.lastError is reset whenever exec is called
-      if(logDb.lastError().isValid()) { 
-        qWarning(tr("Could not create backlog table: %1").arg(logDb.lastError().text()).toAscii());
-        qWarning(tr("Disabling logging...").toAscii());
-        Q_ASSERT(false); // quassel does require logging
-        ok = false;
-      }
-    
-      logDb.close();
-    }
-  } 
-  
-  QSqlDatabase::removeDatabase("quassel_setup");
-  return ok;
+QString SqliteStorage::engineName() {
+  return SqliteStorage::displayName();
 }
 
-bool SqliteStorage::init(const QVariantMap &settings) {
-  Q_UNUSED(settings);
-  bool ok;
-  // i need the extra scope to be able to remove the database connection
-  {
-    logDb = QSqlDatabase::database("quassel_connection", false);
-    if (!logDb.isValid()) {
-      logDb = QSqlDatabase::addDatabase("QSQLITE", "quassel_connection");
-    }
-    logDb.setDatabaseName(SqliteStorage::backlogFile());
-    ok = logDb.open();
-    if (!ok) {
-      qWarning(tr("Could not open backlog database: %1").arg(logDb.lastError().text()).toAscii());
-    }
-  }
-
-  if (!ok) {
-    //QSqlDatabase::removeDatabase("quassel_connection");
-    return false;
-  }
+int SqliteStorage::installedSchemaVersion() {
+  QSqlQuery query = logDb().exec("SELECT value FROM coreinfo WHERE key = 'schemaversion'");
+  if(query.first())
+    return query.value(0).toInt();
 
-  // check if the db schema is up to date
-  QSqlQuery query = logDb.exec("SELECT MAX(version) FROM coreinfo");
-  if(query.first()) {
-    // TODO VersionCheck
-    //checkVersion(query.value(0));
-    qDebug() << "Sqlite is ready. Quassel Schema Version:" << query.value(0).toUInt();
-  } else {
-    qWarning("Sqlite is not ready!");
-    return false;
-  }
+  // maybe it's really old... (schema version 0)
+  query = logDb().exec("SELECT MAX(version) FROM coreinfo");
+  if(query.first())
+    return query.value(0).toInt();
 
-  // we will need those pretty often... so let's speed things up:
-  createBufferQuery = new QSqlQuery(logDb);
-  createBufferQuery->prepare("INSERT INTO buffer (userid, networkid, buffername) VALUES (:userid, (SELECT networkid FROM network WHERE networkname = :networkname AND userid = :userid2), :buffername)");
-
-  createNetworkQuery = new QSqlQuery(logDb);
-  createNetworkQuery->prepare("INSERT INTO network (userid, networkname) VALUES (:userid, :networkname)");
-
-  getBufferInfoQuery = new QSqlQuery(logDb);
-  getBufferInfoQuery->prepare("SELECT bufferid FROM buffer "
-                            "JOIN network ON buffer.networkid = network.networkid "
-                            "WHERE network.networkname = :networkname AND network.userid = :userid AND buffer.userid = :userid2 AND lower(buffer.buffername) = lower(:buffername)");
-
-  logMessageQuery = new QSqlQuery(logDb);
-  logMessageQuery->prepare("INSERT INTO backlog (time, bufferid, type, flags, senderid, message) "
-                           "VALUES (:time, :bufferid, :type, :flags, (SELECT senderid FROM sender WHERE sender = :sender), :message)");
-
-  addSenderQuery = new QSqlQuery(logDb);
-  addSenderQuery->prepare("INSERT INTO sender (sender) VALUES (:sender)");
-
-  getLastMessageIdQuery = new QSqlQuery(logDb);
-  getLastMessageIdQuery->prepare("SELECT messageid FROM backlog "
-                                 "WHERE time = :time AND bufferid = :bufferid AND type = :type AND senderid = (SELECT senderid FROM sender WHERE sender = :sender)");
-
-  requestMsgsOffsetQuery = new QSqlQuery(logDb);
-  requestMsgsOffsetQuery->prepare("SELECT count(*) FROM backlog WHERE bufferid = :bufferid AND messageid < :messageid");
-
-  requestMsgsQuery = new QSqlQuery(logDb);
-  requestMsgsQuery->prepare("SELECT messageid, time,  type, flags, sender, message, displayname "
-                            "FROM backlog "
-                            "JOIN buffer ON backlog.bufferid = buffer.bufferid "
-                            "JOIN sender ON backlog.senderid = sender.senderid "
-                            "LEFT JOIN buffergroup ON buffer.groupid = buffergroup.groupid "
-                            "WHERE buffer.bufferid = :bufferid OR buffer.groupid = (SELECT groupid FROM buffer WHERE bufferid = :bufferid2) "
-                            "ORDER BY messageid DESC "
-                            "LIMIT :limit OFFSET :offset");
-
-  requestMsgsSinceOffsetQuery = new QSqlQuery(logDb);
-  requestMsgsSinceOffsetQuery->prepare("SELECT count(*) FROM backlog WHERE bufferid = :bufferid AND time >= :since");
-
-  requestMsgsSinceQuery = new QSqlQuery(logDb);
-  requestMsgsSinceQuery->prepare("SELECT messageid, time,  type, flags, sender, message, displayname "
-                                 "FROM backlog "
-                                 "JOIN buffer ON backlog.bufferid = buffer.bufferid "
-                                 "JOIN sender ON backlog.senderid = sender.senderid "
-                                 "LEFT JOIN buffergroup ON buffer.groupid = buffergroup.groupid "
-                                 "WHERE (buffer.bufferid = :bufferid OR buffer.groupid = (SELECT groupid FROM buffer WHERE bufferid = :bufferid2)) AND "
-                                 "backlog.time >= :since "
-                                 "ORDER BY messageid DESC "
-                                 "LIMIT -1 OFFSET :offset");
-
-  requestMsgRangeQuery = new QSqlQuery(logDb);
-  requestMsgRangeQuery->prepare("SELECT messageid, time,  type, flags, sender, message, displayname "
-                                "FROM backlog "
-                                "JOIN buffer ON backlog.bufferid = buffer.bufferid "
-                                "JOIN sender ON backlog.senderid = sender.senderid "
-                                "LEFT JOIN buffergroup ON buffer.groupid = buffergroup.groupid "
-                                "WHERE (buffer.bufferid = :bufferid OR buffer.groupid = (SELECT groupid FROM buffer WHERE bufferid = :bufferid2)) AND "
-                                "backlog.messageid >= :firstmsg AND backlog.messageid <= :lastmsg "
-                                "ORDER BY messageid DESC ");
-  
-  return true;
+  return AbstractSqlStorage::installedSchemaVersion();
 }
 
 UserId SqliteStorage::addUser(const QString &user, const QString &password) {
   QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
   cryptopass = cryptopass.toHex();
 
-  QSqlQuery query(logDb);
-  query.prepare("INSERT INTO quasseluser (username, password) VALUES (:username, :password)");
+  QSqlQuery query(logDb());
+  query.prepare(queryString("insert_quasseluser"));
   query.bindValue(":username", user);
   query.bindValue(":password", cryptopass);
   query.exec();
@@ -243,7 +71,7 @@ UserId SqliteStorage::addUser(const QString &user, const QString &password) {
     return 0;
   }
 
-  query.prepare("SELECT userid FROM quasseluser WHERE username = :username");
+  query.prepare(queryString("select_userid"));
   query.bindValue(":username", user);
   query.exec();
   query.first();
@@ -256,16 +84,16 @@ void SqliteStorage::updateUser(UserId user, const QString &password) {
   QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
   cryptopass = cryptopass.toHex();
 
-  QSqlQuery query(logDb);
-  query.prepare("UPDATE quasseluser SET password = :password WHERE userid = :userid");
+  QSqlQuery query(logDb());
+  query.prepare(queryString("update_userpassword"));
   query.bindValue(":userid", user);
   query.bindValue(":password", cryptopass);
   query.exec();
 }
 
 void SqliteStorage::renameUser(UserId user, const QString &newName) {
-  QSqlQuery query(logDb);
-  query.prepare("UPDATE quasseluser SET username = :username WHERE userid = :userid");
+  QSqlQuery query(logDb());
+  query.prepare(queryString("update_username"));
   query.bindValue(":userid", user);
   query.bindValue(":username", newName);
   query.exec();
@@ -276,8 +104,8 @@ UserId SqliteStorage::validateUser(const QString &user, const QString &password)
   QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
   cryptopass = cryptopass.toHex();
 
-  QSqlQuery query(logDb);
-  query.prepare("SELECT userid FROM quasseluser WHERE username = :username AND password = :password");
+  QSqlQuery query(logDb());
+  query.prepare(queryString("select_authuser"));
   query.bindValue(":username", user);
   query.bindValue(":password", cryptopass);
   query.exec();
@@ -286,25 +114,25 @@ UserId SqliteStorage::validateUser(const QString &user, const QString &password)
     return query.value(0).toUInt();
   } else {
     throw AuthError();
-    //return 0;
+    return 0;
   }
 }
 
 void SqliteStorage::delUser(UserId user) {
-  QSqlQuery query(logDb);
-  query.prepare("DELETE FROM backlog WHERE bufferid IN (SELECT DISTINCT bufferid FROM buffer WHERE userid = :userid");
+  QSqlQuery query(logDb());
+  query.prepare(queryString("delete_backlog_by_uid"));
   query.bindValue(":userid", user);
   query.exec();
-  query.prepare("DELETE FROM buffer WHERE userid = :userid");
-  query.bindValue(":userid", user);
-  query.exec();
-  query.prepare("DELETE FROM buffergroup WHERE userid = :userid");
+  
+  query.prepare(queryString("delete_buffers_by_uid"));
   query.bindValue(":userid", user);
   query.exec();
-  query.prepare("DELETE FROM network WHERE userid = :userid");
+  
+  query.prepare(queryString("delete_networks_by_uid"));
   query.bindValue(":userid", user);
   query.exec();
-  query.prepare("DELETE FROM quasseluser WHERE userid = :userid");
+  
+  query.prepare(queryString("delete_quasseluser"));
   query.bindValue(":userid", user);
   query.exec();
   // I hate the lack of foreign keys and on delete cascade... :(
@@ -312,6 +140,7 @@ void SqliteStorage::delUser(UserId user) {
 }
 
 void SqliteStorage::createBuffer(UserId user, const QString &network, const QString &buffer) {
+  QSqlQuery *createBufferQuery = cachedQuery("insert_buffer");
   createBufferQuery->bindValue(":userid", user);
   createBufferQuery->bindValue(":userid2", user);  // Qt can't handle same placeholder twice (maybe sqlites fault)
   createBufferQuery->bindValue(":networkname", network);
@@ -319,7 +148,8 @@ void SqliteStorage::createBuffer(UserId user, const QString &network, const QStr
   createBufferQuery->exec();
 
   if(createBufferQuery->lastError().isValid()) {
-    if(createBufferQuery->lastError().number() == 19) { // Null Constraint violation 
+    if(createBufferQuery->lastError().number() == 19) { // Null Constraint violation
+      QSqlQuery *createNetworkQuery = cachedQuery("insert_network");
       createNetworkQuery->bindValue(":userid", user);
       createNetworkQuery->bindValue(":networkname", network);
       createNetworkQuery->exec();
@@ -335,7 +165,7 @@ void SqliteStorage::createBuffer(UserId user, const QString &network, const QStr
 }
 
 uint SqliteStorage::getNetworkId(UserId user, const QString &network) {
-  QSqlQuery query(logDb);
+  QSqlQuery query(logDb());
   query.prepare("SELECT networkid FROM network "
                "WHERE userid = :userid AND networkname = :networkname");
   query.bindValue(":userid", user);
@@ -360,6 +190,8 @@ BufferInfo SqliteStorage::getBufferInfo(UserId user, const QString &network, con
   BufferInfo bufferid;
   // TODO: get rid of this hackaround
   uint networkId = getNetworkId(user, network);
+
+  QSqlQuery *getBufferInfoQuery = cachedQuery("select_bufferByName");
   getBufferInfoQuery->bindValue(":networkname", network);
   getBufferInfoQuery->bindValue(":userid", user);
   getBufferInfoQuery->bindValue(":userid2", user); // Qt can't handle same placeholder twice... though I guess it's sqlites fault
@@ -383,28 +215,26 @@ BufferInfo SqliteStorage::getBufferInfo(UserId user, const QString &network, con
 }
 
 QList<BufferInfo> SqliteStorage::requestBuffers(UserId user, QDateTime since) {
+  uint time = 0;
+  if(since.isValid())
+    time = since.toTime_t();
+  
   QList<BufferInfo> bufferlist;
-  QSqlQuery query(logDb);
-  query.prepare("SELECT DISTINCT buffer.bufferid, networkname, buffername FROM buffer "
-                "JOIN network ON buffer.networkid = network.networkid "
-                "JOIN backlog ON buffer.bufferid = backlog.bufferid "
-                "WHERE buffer.userid = :userid AND time >= :time");
+  QSqlQuery query(logDb());
+  query.prepare(queryString("select_buffers"));
   query.bindValue(":userid", user);
-  if (since.isValid()) {
-    query.bindValue(":time", since.toTime_t());
-  } else {
-    query.bindValue(":time", 0);
-  }
+  query.bindValue(":time", time);
   
   query.exec();
-
+  watchQuery(&query);
   while(query.next()) {
-    bufferlist << BufferInfo(query.value(0).toUInt(), getNetworkId(user, query.value(1).toString()), 0, query.value(1).toString(), query.value(2).toString());
+    bufferlist << BufferInfo(query.value(0).toUInt(), query.value(2).toUInt(), 0, query.value(3).toString(), query.value(1).toString());
   }
   return bufferlist;
 }
 
 MsgId SqliteStorage::logMessage(Message msg) {
+  QSqlQuery *logMessageQuery = cachedQuery("insert_message");
   logMessageQuery->bindValue(":time", msg.timestamp().toTime_t());
   logMessageQuery->bindValue(":bufferid", msg.buffer().uid());
   logMessageQuery->bindValue(":type", msg.type());
@@ -415,7 +245,8 @@ MsgId SqliteStorage::logMessage(Message msg) {
   
   if(logMessageQuery->lastError().isValid()) {
     // constraint violation - must be NOT NULL constraint - probably the sender is missing...
-    if(logMessageQuery->lastError().number() == 19) { 
+    if(logMessageQuery->lastError().number() == 19) {
+      QSqlQuery *addSenderQuery = cachedQuery("insert_sender");
       addSenderQuery->bindValue(":sender", msg.sender());
       addSenderQuery->exec();
       watchQuery(addSenderQuery);
@@ -427,6 +258,7 @@ MsgId SqliteStorage::logMessage(Message msg) {
     }
   }
 
+  QSqlQuery *getLastMessageIdQuery = cachedQuery("select_lastMessage");
   getLastMessageIdQuery->bindValue(":time", msg.timestamp().toTime_t());
   getLastMessageIdQuery->bindValue(":bufferid", msg.buffer().uid());
   getLastMessageIdQuery->bindValue(":type", msg.type());
@@ -445,6 +277,7 @@ MsgId SqliteStorage::logMessage(Message msg) {
 QList<Message> SqliteStorage::requestMsgs(BufferInfo buffer, int lastmsgs, int offset) {
   QList<Message> messagelist;
   // we have to determine the real offset first
+  QSqlQuery *requestMsgsOffsetQuery = cachedQuery("select_messagesOffset");
   requestMsgsOffsetQuery->bindValue(":bufferid", buffer.uid());
   requestMsgsOffsetQuery->bindValue(":messageid", offset);
   requestMsgsOffsetQuery->exec();
@@ -452,6 +285,7 @@ QList<Message> SqliteStorage::requestMsgs(BufferInfo buffer, int lastmsgs, int o
   offset = requestMsgsOffsetQuery->value(0).toUInt();
 
   // now let's select the messages
+  QSqlQuery *requestMsgsQuery = cachedQuery("select_messages");
   requestMsgsQuery->bindValue(":bufferid", buffer.uid());
   requestMsgsQuery->bindValue(":bufferid2", buffer.uid());  // Qt can't handle the same placeholder used twice
   requestMsgsQuery->bindValue(":limit", lastmsgs);
@@ -474,6 +308,7 @@ QList<Message> SqliteStorage::requestMsgs(BufferInfo buffer, int lastmsgs, int o
 QList<Message> SqliteStorage::requestMsgs(BufferInfo buffer, QDateTime since, int offset) {
   QList<Message> messagelist;
   // we have to determine the real offset first
+  QSqlQuery *requestMsgsSinceOffsetQuery = cachedQuery("select_messagesSinceOffset");
   requestMsgsSinceOffsetQuery->bindValue(":bufferid", buffer.uid());
   requestMsgsSinceOffsetQuery->bindValue(":since", since.toTime_t());
   requestMsgsSinceOffsetQuery->exec();
@@ -481,6 +316,7 @@ QList<Message> SqliteStorage::requestMsgs(BufferInfo buffer, QDateTime since, in
   offset = requestMsgsSinceOffsetQuery->value(0).toUInt();  
 
   // now let's select the messages
+  QSqlQuery *requestMsgsSinceQuery = cachedQuery("select_messagesSince");
   requestMsgsSinceQuery->bindValue(":bufferid", buffer.uid());
   requestMsgsSinceQuery->bindValue(":bufferid2", buffer.uid());
   requestMsgsSinceQuery->bindValue(":since", since.toTime_t());
@@ -504,6 +340,7 @@ QList<Message> SqliteStorage::requestMsgs(BufferInfo buffer, QDateTime since, in
 
 QList<Message> SqliteStorage::requestMsgRange(BufferInfo buffer, int first, int last) {
   QList<Message> messagelist;
+  QSqlQuery *requestMsgRangeQuery = cachedQuery("select_messageRange");
   requestMsgRangeQuery->bindValue(":bufferid", buffer.uid());
   requestMsgRangeQuery->bindValue(":bufferid2", buffer.uid());
   requestMsgRangeQuery->bindValue(":firstmsg", first);
@@ -523,37 +360,18 @@ QList<Message> SqliteStorage::requestMsgRange(BufferInfo buffer, int first, int
   return messagelist;
 }
 
-QString SqliteStorage::backlogFile(bool createPath) {
+QString SqliteStorage::backlogFile() {
   // kinda ugly, but I currently see no other way to do that
 #ifdef Q_OS_WIN32
   QString quasselDir = QDir::homePath() + qgetenv("APPDATA") + "\\quassel\\";
 #else
   QString quasselDir = QDir::homePath() + "/.quassel/";
 #endif
-  
-  if (createPath) {
-    QDir *qDir = new QDir(quasselDir);
-    if (!qDir->exists(quasselDir)) {
-      qDir->mkpath(quasselDir);
-    }
-    delete qDir;
-  }
 
-  return quasselDir + "quassel-storage.sqlite";
+  QDir qDir(quasselDir);
+  if(!qDir.exists(quasselDir))
+    qDir.mkpath(quasselDir);
+  
+  return quasselDir + "quassel-storage.sqlite";  
 }
 
-bool SqliteStorage::watchQuery(QSqlQuery *query) {
-  if(query->lastError().isValid()) {
-    qWarning() << "unhandled Error in QSqlQuery!";
-    qWarning() << "                  last Query:" << query->lastQuery();
-    qWarning() << "              executed Query:" << query->executedQuery();
-    qWarning() << "                bound Values:" << query->boundValues();
-    qWarning() << "                Error Number:" << query->lastError().number();
-    qWarning() << "               Error Message:" << query->lastError().text();
-    qWarning() << "              Driver Message:" << query->lastError().driverText();
-    qWarning() << "                  DB Message:" << query->lastError().databaseText();
-    
-    return false;
-  }
-  return true;
-}
index b661aed..3b27205 100644 (file)
  *   59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.             *
  ***************************************************************************/
 
-#ifndef _SQLITESTORAGE_H_
-#define _SQLITESTORAGE_H_
+#ifndef SQLITESTORAGE_H
+#define SQLITESTORAGE_H
 
-#include <QCryptographicHash>
+#include "abstractsqlstorage.h"
 
-#include "storage.h"
+#include <QSqlDatabase>
 
 class QSqlQuery;
 
-class SqliteStorage : public Storage {
+class SqliteStorage : public AbstractSqlStorage {
   Q_OBJECT
 
-  public:
-    SqliteStorage();
-    virtual ~SqliteStorage();
-
-  public slots:
-    /* General */
-
-    static bool isAvailable();
-    static QString displayName();
-    virtual bool setup(const QVariantMap &settings = QVariantMap());
-    virtual bool init(const QVariantMap &settings = QVariantMap());
-
-    // TODO: Add functions for configuring the backlog handling, i.e. defining auto-cleanup settings etc
-
-    /* User handling */
-
-    virtual UserId addUser(const QString &user, const QString &password);
-    virtual void updateUser(UserId user, const QString &password);
-    virtual void renameUser(UserId user, const QString &newName);
-    virtual UserId validateUser(const QString &user, const QString &password);
-    virtual void delUser(UserId user);
-
-    /* Network handling */
-    virtual uint getNetworkId(UserId user, const QString &network);
-
-    /* Buffer handling */
-    virtual BufferInfo getBufferInfo(UserId user, const QString &network, const QString &buffer = "");
-    virtual QList<BufferInfo> requestBuffers(UserId user, QDateTime since = QDateTime());
-
-    /* Message handling */
-
-    virtual MsgId logMessage(Message msg);
-    virtual QList<Message> requestMsgs(BufferInfo buffer, int lastmsgs = -1, int offset = -1);
-    virtual QList<Message> requestMsgs(BufferInfo buffer, QDateTime since, int offset = -1);
-    virtual QList<Message> requestMsgRange(BufferInfo buffer, int first, int last);
-
-  signals:
-    void bufferInfoUpdated(BufferInfo);
-
-  protected:
-
-  private:
-    static QString backlogFile(bool createPath = false);
-    
-    void createBuffer(UserId user, const QString &network, const QString &buffer);
-    bool watchQuery(QSqlQuery *query);
-
-    QSqlDatabase logDb;
-
-    QSqlQuery *logMessageQuery;
-    QSqlQuery *addSenderQuery;
-    QSqlQuery *getLastMessageIdQuery;
-    QSqlQuery *requestMsgsQuery;
-    QSqlQuery *requestMsgsOffsetQuery;
-    QSqlQuery *requestMsgsSinceQuery;
-    QSqlQuery *requestMsgsSinceOffsetQuery;
-    QSqlQuery *requestMsgRangeQuery;
-    QSqlQuery *createNetworkQuery;
-    QSqlQuery *createBufferQuery;
-    QSqlQuery *getBufferInfoQuery;
+public:
+  SqliteStorage(QObject *parent = 0);
+  virtual ~SqliteStorage();
+                         
+public slots:
+  /* General */
+  
+  static bool isAvailable();
+  static QString displayName();
+  virtual QString engineName() ;
+  // TODO: Add functions for configuring the backlog handling, i.e. defining auto-cleanup settings etc
+  
+  /* User handling */
+  
+  virtual UserId addUser(const QString &user, const QString &password);
+  virtual void updateUser(UserId user, const QString &password);
+  virtual void renameUser(UserId user, const QString &newName);
+  virtual UserId validateUser(const QString &user, const QString &password);
+  virtual void delUser(UserId user);
+  
+  /* Network handling */
+  virtual uint getNetworkId(UserId user, const QString &network);
+  
+  /* Buffer handling */
+  virtual BufferInfo getBufferInfo(UserId user, const QString &network, const QString &buffer = "");
+  virtual QList<BufferInfo> requestBuffers(UserId user, QDateTime since = QDateTime());
+  
+  /* Message handling */
+  
+  virtual MsgId logMessage(Message msg);
+  virtual QList<Message> requestMsgs(BufferInfo buffer, int lastmsgs = -1, int offset = -1);
+  virtual QList<Message> requestMsgs(BufferInfo buffer, QDateTime since, int offset = -1);
+  virtual QList<Message> requestMsgRange(BufferInfo buffer, int first, int last);
+
+protected:
+  inline virtual QString driverName() { return "QSQLITE"; }
+  inline virtual QString databaseName() { return backlogFile(); }
+  virtual int installedSchemaVersion();
+  
+signals:
+  void bufferInfoUpdated(BufferInfo);
+  
+private:
+  static QString backlogFile();
+  void createBuffer(UserId user, const QString &network, const QString &buffer);
 };
 
 #endif
index 03990c8..26172c8 100644 (file)
@@ -19,3 +19,8 @@
  ***************************************************************************/
 
 #include "storage.h"
+
+Storage::Storage(QObject *parent)
+  : QObject(parent)
+{
+}
index 08a44f2..69a229e 100644 (file)
@@ -22,7 +22,6 @@
 #define _STORAGE_H_
 
 #include <QtCore>
-#include <QSqlDatabase>
 
 #include "message.h"
 
@@ -30,7 +29,7 @@ class Storage : public QObject {
   Q_OBJECT
 
   public:
-    Storage() {};
+    Storage(QObject *parent = 0);
     virtual ~Storage() {};
 
   public slots: