SqliteStorage::SqliteStorage() {
// TODO I don't think that this path is failsafe for windows users :)
- QString backlogFile = Global::quasselDir + "/quassel-backlog-newstyle.sqlite";
+ QString backlogFile = Global::quasselDir + "/quassel-storage.sqlite";
logDb = QSqlDatabase::addDatabase("QSQLITE");
logDb.setDatabaseName(backlogFile);
bool ok = logDb.open();
Q_ASSERT(ok);
return;
}
-
+
+ /*
+ NO, we should not start a transaction to check if transactions are supported :-)
if(!logDb.transaction()) {
qWarning(tr("Database driver does not support transactions. This might lead to a corrupt database!").toAscii());
}
-
+ */
+
// check if the db schema is up to date
QSqlQuery query = logDb.exec("SELECT MAX(version) FROM coreinfo");
if(query.first()) {
- // TOTO VersionCheck
+ // TODO VersionCheck
//checkVersion(query.value(0));
qDebug() << "Sqlite is ready. Quassel Schema Version:" << query.value(0).toUInt();
} else {
initDb();
}
-
+
// we will need those pretty often... so let's speed things up:
- createBufferQuery = new QSqlQuery();
+ createBufferQuery = new QSqlQuery(logDb);
createBufferQuery->prepare("INSERT INTO buffer (userid, networkid, buffername) VALUES (:userid, (SELECT networkid FROM network WHERE networkname = :networkname), :buffername)");
-
- createNetworkQuery = new QSqlQuery();
+
+ createNetworkQuery = new QSqlQuery(logDb);
createNetworkQuery->prepare("INSERT INTO network (userid, networkname) VALUES (:userid, :networkname)");
-
- getBufferIdQuery = new QSqlQuery();
+
+ getBufferIdQuery = new QSqlQuery(logDb);
getBufferIdQuery->prepare("SELECT bufferid FROM buffer "
"JOIN network ON buffer.networkid = network.networkid "
- "WHERE network.networkname = :networkname AND buffer.userid = :userid AND buffer.buffername = :buffername");
+ "WHERE network.networkname = :networkname AND buffer.userid = :userid AND buffer.buffername = :buffername "
+ "LIMIT 1");
-
- logMessageQuery = new QSqlQuery();
+
+ 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();
+
+ addSenderQuery = new QSqlQuery(logDb);
addSenderQuery->prepare("INSERT INTO sender (sender) VALUES (:sender)");
- getLastMessageIdQuery = new QSqlQuery();
+ 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();
+ requestMsgsOffsetQuery = new QSqlQuery(logDb);
requestMsgsOffsetQuery->prepare("SELECT count(*) FROM backlog WHERE bufferid = :bufferid AND messageid < :messageid");
-
- requestMsgsQuery = new QSqlQuery();
+
+ 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 = :bufferid) "
+ "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();
+
+ requestMsgsSinceOffsetQuery = new QSqlQuery(logDb);
requestMsgsSinceOffsetQuery->prepare("SELECT count(*) FROM backlog WHERE bufferid = :bufferid AND time >= :since");
-
- requestMsgsSinceQuery = new QSqlQuery();
+
+ 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 = :bufferid)) AND "
+ "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();
+
+ 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 = :bufferid)) AND "
+ "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 ");
-
+
}
SqliteStorage::~SqliteStorage() {
delete createBufferQuery;
delete getBufferIdQuery;
logDb.close();
+ //qDebug() << logDb.lastError().text();
}
void SqliteStorage::initDb() {
+ //logDb.transaction();
logDb.exec("CREATE TABLE quasseluser ("
"userid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"
"username TEXT UNIQUE NOT NULL,"
}
bool SqliteStorage::isAvailable() {
- // oh yes we're available... at least I do hope so :)
+ if(!QSqlDatabase::isDriverAvailable("QSQLITE")) return false;
return true;
}
QString SqliteStorage::displayName() {
- // I think the class name is a got start here
+ // I think the class name is a good start here
return QString("SqliteStorage");
}
UserId SqliteStorage::addUser(QString user, QString password) {
QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
cryptopass = cryptopass.toHex();
-
+
+ //logDb.transaction();
QSqlQuery query(logDb);
query.prepare("INSERT INTO quasseluser (username, password) VALUES (:username, :password)");
query.bindValue(":username", user);
query.bindValue(":password", cryptopass);
query.exec();
if(query.lastError().isValid() && query.lastError().number() == 19) { // user already exists - sadly 19 seems to be the general constraint violation error...
+ logDb.rollback();
return 0;
}
logDb.commit();
-
+
query.prepare("SELECT userid FROM quasseluser WHERE username = :username");
query.bindValue(":username", user);
query.exec();
void SqliteStorage::updateUser(UserId user, QString password) {
QByteArray cryptopass = QCryptographicHash::hash(password.toUtf8(), QCryptographicHash::Sha1);
cryptopass = cryptopass.toHex();
-
+
+ //logDb.transaction();
QSqlQuery query(logDb);
query.prepare("UPDATE quasseluser SET password = :password WHERE userid = :userid");
query.bindValue(":userid", user);
UserId SqliteStorage::validateUser(QString user, 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");
query.bindValue(":username", user);
} else {
return 0;
}
-
}
void SqliteStorage::delUser(UserId user) {
+ //logDb.transaction();
QSqlQuery query(logDb);
+ // FIXME: backlog has no userid, it's in bufferid
query.prepare("DELETE FROM backlog WHERE userid = :userid");
query.bindValue(":userid", user);
query.exec();
}
void SqliteStorage::createBuffer(UserId user, QString network, QString buffer) {
- qDebug() << "creating buffer:" << user << network << buffer;
+ //qDebug() << "creating buffer:" << user << network << buffer;
+ //logDb.transaction();
createBufferQuery->bindValue(":userid", user);
createBufferQuery->bindValue(":networkname", network);
createBufferQuery->bindValue(":buffername", buffer);
createBufferQuery->exec();
-
+
if(createBufferQuery->lastError().isValid()) {
if(createBufferQuery->lastError().number() == 19) { // Null Constraint violation
createNetworkQuery->bindValue(":userid", user);
createNetworkQuery->bindValue(":networkname", network);
createNetworkQuery->exec();
createBufferQuery->exec();
+ //qDebug() << "net" << createNetworkQuery->lastError().text();
+ //qDebug() << "buf" << createBufferQuery->lastError().text();
Q_ASSERT(!createNetworkQuery->lastError().isValid());
Q_ASSERT(!createBufferQuery->lastError().isValid());
} else {
// do panic!
qDebug() << "failed to create Buffer: ErrNo:" << createBufferQuery->lastError().number() << "ErrMsg:" << createBufferQuery->lastError().text();
+ Q_ASSERT(false);
}
}
logDb.commit();
}
BufferId SqliteStorage::getBufferId(UserId user, QString network, QString buffer) {
+ if(buffer == "") buffer = "$$$"; // FIXME
+
+ QSqlQuery *getBufferIdQuery = new QSqlQuery(logDb);
+ getBufferIdQuery->prepare("SELECT bufferid FROM buffer "
+ "JOIN network ON buffer.networkid = network.networkid "
+ "WHERE network.networkname = :networkname AND buffer.userid = :userid AND buffer.buffername = :buffername "
+ "LIMIT 1");
+
getBufferIdQuery->bindValue(":networkname", network);
getBufferIdQuery->bindValue(":userid", user);
getBufferIdQuery->bindValue(":buffername", buffer);
getBufferIdQuery->exec();
+ bool flg = false;
if(!getBufferIdQuery->first()) {
createBuffer(user, network, buffer);
getBufferIdQuery->exec();
- Q_ASSERT(getBufferIdQuery->first());
+ flg = getBufferIdQuery->first();
+ Q_ASSERT(flg);
}
-
- return BufferId(getBufferIdQuery->value(0).toUInt(), network, buffer);
+ if(buffer == "$$$") buffer = "";
+ BufferId result = BufferId(getBufferIdQuery->value(0).toUInt(), network, buffer);
+ //getBufferIdQuery->clear(); // this is active for some reason, which wrecks havoc with later transactions
+ getBufferIdQuery->last();
+ //qDebug() << "active" << getBufferIdQuery->isActive();
+ if(flg) emit bufferIdUpdated(result);
+ delete getBufferIdQuery;
+ return result;
}
QList<BufferId> SqliteStorage::requestBuffers(UserId user, QDateTime since) {
QList<BufferId> bufferlist;
QSqlQuery query(logDb);
- query.prepare("SELECT bufferid, networkname, buffername FROM buffer "
+ // FIXME: fix query (and make it run in sane time)
+ query.prepare("SELECT buffer.bufferid, networkname, buffername FROM buffer "
+ //"JOIN buffer ON buffer.bufferid = backlog.bufferid "
"JOIN network ON buffer.networkid = network.networkid "
- "JOIN backlog ON buffer.bufferid = backlog.bufferid "
- "WHERE buffer.userid = :userid AND time >= :time");
- query.bindValue(":userid", user);
- query.bindValue(":time", since.toTime_t());
+ //"JOIN backlog ON buffer.bufferid = backlog.bufferid "
+ "WHERE buffer.userid = 1");// AND time >= 0");
+ //query.bindValue(":userid", user);
+ //if(since.isValid()) query.bindValue(":time", since.toTime_t());
+ //else query.bindValue(":time",0);
+ //qDebug() << query.boundValues();
query.exec();
+ //qDebug() << query.lastError().text();
while(query.next()) {
- bufferlist << BufferId(query.value(0).toUInt(), query.value(1).toString(), query.value(2).toString());
+ QString buf = query.value(2).toString();
+ if(buf == "$$$") buf = "";
+ bufferlist << BufferId(query.value(0).toUInt(), query.value(1).toString(), buf);
}
return bufferlist;
}
MsgId SqliteStorage::logMessage(Message msg) {
+ if(msg.sender == "") msg.sender = "$$$"; // FIXME handle empty sender strings in a sane way
+ //logDb.transaction();
logMessageQuery->bindValue(":time", msg.timeStamp.toTime_t());
logMessageQuery->bindValue(":bufferid", msg.buffer.uid());
logMessageQuery->bindValue(":type", msg.type);
logMessageQuery->bindValue(":sender", msg.sender);
logMessageQuery->bindValue(":message", msg.text);
logMessageQuery->exec();
-
- // constraint violation - must be NOT NULL constraint - probably the sender is missing...
+ // constraint violation - must be NOT NULL constraint - probably the sender is missing...
if(logMessageQuery->lastError().isValid()) {
if(logMessageQuery->lastError().number() == 19) {
addSenderQuery->bindValue(":sender", msg.sender);
}
logDb.commit();
-
+
getLastMessageIdQuery->bindValue(":time", msg.timeStamp.toTime_t());
getLastMessageIdQuery->bindValue(":bufferid", msg.buffer.uid());
getLastMessageIdQuery->bindValue(":type", msg.type);
getLastMessageIdQuery->bindValue(":sender", msg.sender);
getLastMessageIdQuery->exec();
-
+
if(getLastMessageIdQuery->first()) {
return getLastMessageIdQuery->value(0).toUInt();
} else { // somethin went wrong... :(
+ qDebug() << getLastMessageIdQuery->lastQuery();
+ Q_ASSERT(false);
return 0;
}
}
requestMsgsOffsetQuery->exec();
requestMsgsOffsetQuery->first();
offset = requestMsgsOffsetQuery->value(0).toUInt();
-
+
// now let's select the messages
requestMsgsQuery->bindValue(":bufferid", buffer.uid());
+ requestMsgsQuery->bindValue(":bufferid2", buffer.uid()); // Qt can't handle the same placeholder used twice
requestMsgsQuery->bindValue(":limit", lastmsgs);
requestMsgsQuery->bindValue(":offset", offset);
requestMsgsQuery->exec();
-
while(requestMsgsQuery->next()) {
Message msg(QDateTime::fromTime_t(requestMsgsQuery->value(1).toInt()),
buffer,
requestMsgsQuery->value(4).toString(),
requestMsgsQuery->value(3).toUInt());
msg.msgId = requestMsgsQuery->value(0).toUInt();
+ if(msg.sender == "$$$") msg.sender = ""; // FIXME
messagelist << msg;
}
-
+
return messagelist;
}
requestMsgsSinceOffsetQuery->exec();
requestMsgsSinceOffsetQuery->first();
offset = requestMsgsSinceOffsetQuery->value(0).toUInt();
-
+
// now let's select the messages
requestMsgsSinceQuery->bindValue(":bufferid", buffer.uid());
+ requestMsgsSinceQuery->bindValue(":bufferid2", buffer.uid());
requestMsgsSinceQuery->bindValue(":since", since.toTime_t());
requestMsgsSinceQuery->bindValue(":offset", offset);
requestMsgsSinceQuery->exec();
-
+
while(requestMsgsSinceQuery->next()) {
Message msg(QDateTime::fromTime_t(requestMsgsSinceQuery->value(1).toInt()),
buffer,
requestMsgsSinceQuery->value(4).toString(),
requestMsgsSinceQuery->value(3).toUInt());
msg.msgId = requestMsgsSinceQuery->value(0).toUInt();
+ if(msg.sender == "$$$") msg.sender = ""; // FIXME
messagelist << msg;
}
-
-
+
+
return messagelist;
}
QList<Message> SqliteStorage::requestMsgRange(BufferId buffer, int first, int last) {
QList<Message> messagelist;
requestMsgRangeQuery->bindValue(":bufferid", buffer.uid());
+ requestMsgRangeQuery->bindValue(":bufferid2", buffer.uid());
requestMsgRangeQuery->bindValue(":firstmsg", first);
requestMsgRangeQuery->bindValue(":lastmsg", last);
-
+
while(requestMsgRangeQuery->next()) {
Message msg(QDateTime::fromTime_t(requestMsgRangeQuery->value(1).toInt()),
buffer,
requestMsgRangeQuery->value(4).toString(),
requestMsgRangeQuery->value(3).toUInt());
msg.msgId = requestMsgRangeQuery->value(0).toUInt();
+ if(msg.sender == "$$$") msg.sender = ""; // FIXME
messagelist << msg;
}
-
+
return messagelist;
}
} else {
user = query.value(0).toUInt();
}
+ // FIXME: backlog does not have userid, we have to select bufferids first
query.prepare("DELETE FROM backlog WHERE userid = :userid");
query.bindValue(":userid", user);
query.exec();
projects / quassel.git / blobdiff