Ejemplo n.º 1
0
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();
}
Ejemplo n.º 2
0
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();
}
Ejemplo n.º 3
0
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();
}
Ejemplo n.º 4
0
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();
}
Ejemplo n.º 5
0
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();
	}
Ejemplo n.º 6
0
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();
}
Ejemplo n.º 7
0
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());
}
Ejemplo n.º 8
0
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);
	}
}