QSqlError pki_x509super::lookupKey()
{
XSqlQuery q;
unsigned hash = pubHash();
SQL_PREPARE(q, "SELECT item FROM public_keys WHERE hash=?");
q.bindValue(0, hash);
q.exec();
if (q.lastError().isValid())
return q.lastError();
while (q.next()) {
pki_key *x = db_base::lookupPki<pki_key>(q.value(0));
if (!x) {
qDebug("Public key with id %d not found",
q.value(0).toInt());
continue;
}
x->resetUcount();
if (compareRefKey(x)) {
setRefKey(x);
break;
}
}
return q.lastError();
}
QSqlError pki_x509super::deleteSqlData()
{
XSqlQuery q;
if (privkey)
privkey->resetUcount();
SQL_PREPARE(q, "DELETE FROM x509super WHERE item=?");
q.bindValue(0, sqlItemId);
q.exec();
return q.lastError();
}
QSqlError pki_x509req::deleteSqlData()
{
XSqlQuery q;
QSqlError e = pki_x509super::deleteSqlData();
if (e.isValid())
return e;
SQL_PREPARE(q, "DELETE FROM requests WHERE item=?");
q.bindValue(0, sqlItemId);
q.exec();
return q.lastError();
}
QSqlError pki_x509req::insertSqlData()
{
XSqlQuery q;
QSqlError e = pki_x509super::insertSqlData();
if (e.isValid())
return e;
SQL_PREPARE(q, "INSERT INTO requests (item, hash, signed, request) "
"VALUES (?, ?, 0, ?)");
q.bindValue(0, sqlItemId);
q.bindValue(1, hash());
q.bindValue(2, i2d_b64());
q.exec();
return q.lastError();
}
bool DbTransaction::finish(const char *oper, const char *file, int line)
{
if (!has_begun)
return true;
if (mutex > 0)
mutex--;
else
qCritical() << "Unbalanced DB Transaction in " << oper;
debug(oper, file, line);
has_begun = false;
if (mutex > 0)
return true;
QSqlDatabase db = QSqlDatabase::database();
if (error) {
error = 0;
items.clear();
return db.rollback();
}
mutex++;
XSqlQuery q;
SQL_PREPARE(q, "SELECT MAX(stamp) +1 from items");
q.exec();
if (q.first())
DatabaseStamp = q.value(0).toULongLong();
SQL_PREPARE(q, "UPDATE items SET stamp=? WHERE stamp=0");
q.bindValue(0, DatabaseStamp);
q.exec();
SQL_PREPARE(q, "UPDATE items SET stamp=? WHERE id=?");
q.bindValue(0, DatabaseStamp);
foreach(quint64 id, DbTransaction::items) {
q.bindValue(1, id);
q.exec();
}
QSqlError pki_x509super::insertSqlData()
{
QSqlError e = lookupKey();
if (e.isValid())
return e;
XSqlQuery q;
SQL_PREPARE(q, "INSERT INTO x509super (item, subj_hash, pkey, key_hash) "
"VALUES (?, ?, ?, ?)");
q.bindValue(0, sqlItemId);
q.bindValue(1, (uint)getSubject().hashNum());
q.bindValue(2, privkey ? privkey->getSqlItemId() : QVariant());
q.bindValue(3, pubHash());
q.exec();
return q.lastError();
}
void db_key::loadContainer()
{
XSqlQuery q;
db_base::loadContainer();
FOR_ALL_pki(key, pki_key)
key->setUcount(0);
SQL_PREPARE(q, "SELECT pkey, COUNT(*) FROM x509super WHERE pkey IS NOT NULL GROUP by pkey");
q.exec();
while (q.next()) {
pki_key *key = lookupPki<pki_key>(q.value(0));
if (!key) {
qDebug() << "Unknown key" << q.value(0).toULongLong();
continue;
}
key->setUcount(q.value(1).toInt());
}
MainWindow::dbSqlError(q.lastError());
}
void StorageAccess::checkDatabaseStructure()
{
QStringList lTables = mStorageDatabase.tables();
QSqlQuery q;
if (!lTables.contains("profile"))
{
SQL_PREPARE(q,
"create table profile ("
"id INTEGER PRIMARY KEY AUTOINCREMENT,"
"service VARCHAR(16),"
"name VARCHAR(32),"
"level INTEGER,"
"token VARCHAR(64),"
"last_access INTEGER,"
"UNIQUE (service,name)"
")"
);
SQL_EXEC(q);
}
if (!lTables.contains("sessions"))
{
SQL_PREPARE(q,
"create table sessions ("
"id INTEGER PRIMARY KEY AUTOINCREMENT,"
"profile_id INTEGER REFERENCES profile (id) ON DELETE CASCADE ON UPDATE CASCADE,"
"started INTEGER,"
"finished INTEGER,"
"stream_title VARCHAR(200),"
"UNIQUE (profile_id, started)"
")"
);
SQL_EXEC(q);
}
if (!lTables.contains("statistics"))
{
SQL_PREPARE(q,
"create table statistics ("
"session_id INTEGER REFERENCES sessions (id) ON DELETE CASCADE ON UPDATE CASCADE,"
"curr_time INTEGER,"
"total_subscribers INTEGER,"
"total_followers INTEGER,"
"total_viewers INTEGER,"
"total_chatters INTEGER,"
"num_lines_chatted INTEGER,"
"num_words_chatted INTEGER,"
"PRIMARY KEY (session_id, curr_time)"
")"
);
SQL_EXEC(q);
}
if (!lTables.contains("followers"))
{
SQL_PREPARE(q,
"create table followers ("
"profile_id INTEGER REFERENCES profile (id) ON DELETE CASCADE ON UPDATE CASCADE,"
"created INTEGER,"
"name VARCHAR(32)"
")"
);
SQL_EXEC(q);
SQL_PREPARE(q, "create index followers_idx1 on followers (profile_id, created)");
SQL_EXEC(q);
SQL_PREPARE(q, "create index followers_idx2 on followers (profile_id, name, created)");
SQL_EXEC(q);
}
if (!lTables.contains("subscribers"))
{
SQL_PREPARE(q,
"create table subscribers ("
"profile_id INTEGER REFERENCES profile (id) ON DELETE CASCADE ON UPDATE CASCADE,"
"created INTEGER,"
"name VARCHAR(32)"
")"
);
SQL_EXEC(q);
SQL_PREPARE(q, "create index subscribers_idx1 on subscribers (profile_id, created)");
SQL_EXEC(q);
SQL_PREPARE(q, "create index subscribers_idx2 on subscribers (profile_id, name, created)");
SQL_EXEC(q);
}
if (!lTables.contains("chatters"))
{
SQL_PREPARE(q,
"create table chatters ("
"session_id INTEGER REFERENCES sessions (id) ON DELETE CASCADE ON UPDATE CASCADE,"
"name VARCHAR(32),"
"joined INTEGER,"
"parted INTEGER,"
"num_lines INTEGER,"
"num_highlights INTEGER"
")"
);
SQL_EXEC(q);
SQL_PREPARE(q, "create index chatters_idx1 on chatters (session_id, name, joined)");
SQL_EXEC(q);
}
}
