void CSqlServer::CreateTables()
{
if (!Connect())
return;
try
{
char aBuf[1024];
// create tables
str_format(aBuf, sizeof(aBuf), "CREATE TABLE IF NOT EXISTS %s_race (Map VARCHAR(128) BINARY NOT NULL, Name VARCHAR(%d) BINARY NOT NULL, Timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , Time FLOAT DEFAULT 0, Server CHAR(4), cp1 FLOAT DEFAULT 0, cp2 FLOAT DEFAULT 0, cp3 FLOAT DEFAULT 0, cp4 FLOAT DEFAULT 0, cp5 FLOAT DEFAULT 0, cp6 FLOAT DEFAULT 0, cp7 FLOAT DEFAULT 0, cp8 FLOAT DEFAULT 0, cp9 FLOAT DEFAULT 0, cp10 FLOAT DEFAULT 0, cp11 FLOAT DEFAULT 0, cp12 FLOAT DEFAULT 0, cp13 FLOAT DEFAULT 0, cp14 FLOAT DEFAULT 0, cp15 FLOAT DEFAULT 0, cp16 FLOAT DEFAULT 0, cp17 FLOAT DEFAULT 0, cp18 FLOAT DEFAULT 0, cp19 FLOAT DEFAULT 0, cp20 FLOAT DEFAULT 0, cp21 FLOAT DEFAULT 0, cp22 FLOAT DEFAULT 0, cp23 FLOAT DEFAULT 0, cp24 FLOAT DEFAULT 0, cp25 FLOAT DEFAULT 0, GameID VARCHAR(64), KEY (Map, Name)) CHARACTER SET utf8mb4;", m_aPrefix, MAX_NAME_LENGTH);
executeSql(aBuf);
str_format(aBuf, sizeof(aBuf), "CREATE TABLE IF NOT EXISTS %s_teamrace (Map VARCHAR(128) BINARY NOT NULL, Name VARCHAR(%d) BINARY NOT NULL, Timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, Time FLOAT DEFAULT 0, ID VARBINARY(16) NOT NULL, GameID VARCHAR(64), KEY Map (Map)) CHARACTER SET utf8mb4;", m_aPrefix, MAX_NAME_LENGTH);
executeSql(aBuf);
str_format(aBuf, sizeof(aBuf), "CREATE TABLE IF NOT EXISTS %s_maps (Map VARCHAR(128) BINARY NOT NULL, Server VARCHAR(32) BINARY NOT NULL, Mapper VARCHAR(128) BINARY NOT NULL, Points INT DEFAULT 0, Stars INT DEFAULT 0, Timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY Map (Map)) CHARACTER SET utf8mb4;", m_aPrefix);
executeSql(aBuf);
str_format(aBuf, sizeof(aBuf), "CREATE TABLE IF NOT EXISTS %s_saves (Savegame TEXT CHARACTER SET utf8mb4 BINARY NOT NULL, Map VARCHAR(128) BINARY NOT NULL, Code VARCHAR(128) BINARY NOT NULL, Timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, Server CHAR(4), UNIQUE KEY (Map, Code)) CHARACTER SET utf8mb4;", m_aPrefix);
executeSql(aBuf);
str_format(aBuf, sizeof(aBuf), "CREATE TABLE IF NOT EXISTS %s_points (Name VARCHAR(%d) BINARY NOT NULL, Points INT DEFAULT 0, UNIQUE KEY Name (Name)) CHARACTER SET utf8mb4;", m_aPrefix, MAX_NAME_LENGTH);
executeSql(aBuf);
dbg_msg("sql", "Tables were created successfully");
}
catch (sql::SQLException &e)
{
dbg_msg("sql", "MySQL Error: %s", e.what());
}
Disconnect();
}
bool QgsPostgresTransaction::beginTransaction( QString &error , int statementTimeout )
{
mConn = QgsPostgresConn::connectDb( mConnString, false /*readonly*/, false /*shared*/, true /*transaction*/ );
return executeSql( QString( "SET statement_timeout = %1" ).arg( statementTimeout * 1000 ), error )
&& executeSql( "BEGIN TRANSACTION", error );
}
void setUpDbForTest(){
db = openDb();
executeSql("create table config (key, value)", NULL);
populateConfigTable();
executeSql("create table data (ts,dr,ad,dl,ul,hs)", NULL);
executeSql("create table alert (id, name, active, bound, direction, amount);", NULL);
executeSql("create table interval (id, yr, mn, dy, wk, hr);", NULL);
executeSql("create table alert_interval (alert_id, interval_id);", NULL);
}
void initDatabase(const char* filename, sqlite3 **db)
{
int rc = sqlite3_open(filename, db);
if( rc ){
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(*db));
sqlite3_close(*db);
exit(1);
}
printf("opened database %s\n",filename);
executeSql(*db, "create table packets (id integer primary key autoincrement, timestamp datetime, direction integer, opcode integer, data blob);");
executeSql(*db, "BEGIN;");
executeSql(*db, "create table header (`key` string primary key, value string);");
}
void addConfigRow(char* key, char* value){
rmConfigRow(key);
char sql[200];
sprintf(sql, "INSERT INTO config values ('%s', '%s')", key, value);
executeSql(sql, NULL);
}
void DBManager::initialize(){
if(s != nullptr){
std::cerr << "DBManager::initialize called multiple times!\n";
exit(EXIT_FAILURE);
return;
}
std::string dbPath = DirectoryManager::getSingleton()->getRootDirectory() + "db";
int ret = sqlite3_open(dbPath.c_str(), &s);
if(ret){
std::cerr << "DB Open Failure" << sqlite3_errmsg(s) << "\n";
exit(EXIT_FAILURE);
return;
}
// build db structure
executeSql("CREATE TABLE IF NOT EXISTS fract (" \
"ID INTEGER NOT NULL," \
"UID INTEGER NOT NULL," \
"NAME TEXT NOT NULL," \
"AUTHOR TEXT NOT NULL," \
"STATUS INTEGER NOT NULL )");
sqlite3_stmt *stmt;
std::string query = "SELECT ID FROM fract ORDER BY ID DESC LIMIT 1";
ret = sqlite3_prepare(s, query.c_str(), query.length(), &stmt, NULL);
dbVerify(ret);
ret = sqlite3_step(stmt);
dbVerify(ret);
if(ret != SQLITE_ROW){
lastID = 0;
}else{
lastID = sqlite3_column_int(stmt, 0);
}
ret = sqlite3_finalize(stmt);
dbVerify(ret);
// these were prematurely canceled with either crash or force quit
executeSql(concat("UPDATE fract SET STATUS=",FDBS_ERR)+concat(" WHERE STATUS=",FDBS_RENDERING)+
concat(" OR STATUS=",FDBS_QUEUED));
}
void SQLDialog::sqlButtonPressed()
{
QTextCursor cursor = m_textEdit->textCursor();
if (cursor.hasComplexSelection())
{
// TODO: Error? Iterate over selections
//ScrollMessageBox::information(this, "Simple Selection", cursor.selectedText());
ScrollMessageBox::information(this, "Complex Selection", "??");
}
else if (cursor.hasSelection())
{
executeSql(cursor.selectedText());
}
else
{
executeSql(cursor.block().text());
}
}
void addDbRow(time_t ts, int dr, char* ad, int dl, int ul, char* hs){
char sql[200];
if (hs != NULL){
sprintf(sql, "INSERT INTO data (ts, dr, ad, dl, ul, hs) values (%d, %d, '%s', %d, %d, '%s')", (int)ts, dr, ad, dl, ul, hs);
} else {
sprintf(sql, "INSERT INTO data (ts, dr, ad, dl, ul) values (%d, %d, '%s', %d, %d)", (int)ts, dr, ad, dl, ul);
}
executeSql(sql, NULL);
}
bool QgsPostgresTransaction::commitTransaction( QString &error )
{
if ( executeSql( "COMMIT TRANSACTION", error ) )
{
mConn->unref();
mConn = 0;
return true;
}
return false;
}
bool QgsPostgresTransaction::rollbackTransaction( QString &error )
{
if ( executeSql( "ROLLBACK TRANSACTION", error ) )
{
mConn->unref();
mConn = 0;
return true;
}
return false;
}
DatabaseResult* Database::executeSynchSql(const char* sql, ...) {
// format our sql string
va_list args;
va_start(args, sql);
char localSql[8192];
vsnprintf(localSql, sizeof(localSql), sql, args);
va_end(args);
return executeSql(localSql);
}
void DataBase::category_delete(int id)
{
std::vector<int> categories;
recurse_category(id, &categories);
categories.push_back(id);
std::vector<int>::iterator it;
std::string cids = "(";
for ( it = categories.begin(); it < categories.end(); it++ ) {
if ( it != categories.begin() ) {
cids += ",";
}
cids += IntToStdString(*it);
}
cids += ")";
SqlResult qr = question_select_where("category IN " + cids);
std::string qids = "(";
SqlResult::iterator rit;
for ( rit = qr.begin(); rit < qr.end(); rit++ ) {
SqlRow q = *rit;
if ( rit != qr.begin() ) {
qids += ",";
}
qids += q["id"];
}
qids += ")";
std::string
query = "DELETE FROM categories WHERE id IN " + cids;
executeSql( query.c_str() );
query = "DELETE FROM answers WHERE question IN " + qids;
executeSql( query.c_str() );
query = "DELETE FROM questions WHERE id IN " + qids;
executeSql( query.c_str() );
}
SqlResult DataBase::category_select_where(std::string where_clause)
{
std::string query = "SELECT * FROM categories";
if ( !where_clause.empty() ) {
query += " WHERE ";
query += where_clause;
}
return executeSql(query);
}
void insertClientBuild(uint8_t *data, uint32_t data_len, sqlite3 *db)
{
if(data_len <4)
{
printf("FATAL: got a packet with opcode = 493 = CMSG_AUTH_SESSION but payload len=%u < 4\n", data_len);
exit(1);
}
uint32_t *clientBuild = (uint32_t*)data;
const char* insertFormat = "insert into header values ('clientBuild', %u)";
uint32_t allocSize = strlen(insertFormat)+10 /*uint32*/;
char* buffer = malloc(allocSize);
if(!buffer)
{
printf("Failed to allocate %u bytes for insert query\n", allocSize);
exit(1);
}
sprintf(buffer, insertFormat, *clientBuild);
executeSql(db, buffer);
free(buffer);
//FIXME make sniffitzt happy
char sql[512];
sprintf(sql, "insert into header values ('clientLang', '%s')", "");
executeSql(db, sql);
sprintf(sql, "insert into header values ('accountName', '%s')", (char*)(data+8));//FIXME
executeSql(db, sql);
sprintf(sql, "insert into header values ('realmName', '%s')", "");
executeSql(db, sql);
sprintf(sql, "insert into header values ('realmServer', '%s')", "");
executeSql(db, sql);
sprintf(sql, "insert into header values ('snifferVersion', '%s')", "");
executeSql(db, sql);
}
SqlRow DataBase::category_select(int id)
{
std::string query = "SELECT * FROM categories WHERE id=";
query += IntToStdString(id);
SqlResult r = executeSql(query);
if ( r.empty() ) {
return SqlRow();
}
return r[0];
}
bool LogBook::processSqlFile(QString &fileName)
{
bool result = true;
QFile file(fileName);
if (!file.open(QIODevice::ReadOnly)) {
qWarning() << "LogBook: could not process SQL file " << fileName;
result = false;
} else {
QTextStream inStream(&file);
while (!inStream.atEnd()) {
QString line = inStream.readLine();
if (!line.startsWith("--") && !line.trimmed().size() == 0) {
if (!executeSql(line)) {
result = false;
}
}
}
}
qDebug() << "LogBook: finished processing SQL file " << fileName;
return result;
}
void insertClientBuild(uint8_t *data, uint32_t data_len, sqlite3 *db)
{
if(data_len <4)
{
printf("FATAL: got a packet with opcode = 493 = CMSG_AUTH_SESSION but payload len=%u < 4\n", data_len);
exit(1);
}
uint32_t *clientBuild = (uint32_t*)data;
const char* insertFormat = "insert into header values ('clientBuild', %u)";
uint32_t allocSize = strlen(insertFormat)+10 /*uint32*/;
char* buffer = malloc(allocSize);
if(!buffer)
{
printf("Failed to allocate %u bytes for insert query\n", allocSize);
exit(1);
}
sprintf(buffer, insertFormat, *clientBuild);
executeSql(db, buffer);
free(buffer);
}
void insertPacket(uint8_t s2c, uint64_t time, uint16_t opcode, uint8_t *data, uint32_t data_len, void* arg)
{
sqlite3 *db = (sqlite3*) arg;
const char* insertFormat = "insert into packets (timestamp, direction, opcode, data) "
"values (datetime(%u,'unixepoch'), %u, %u, X'%s');";
uint32_t allocSize = 3/*s2c*/+10 /*time*/+5/*opcode*/+data_len*2+strlen(insertFormat);
char* queryBuffer = malloc(allocSize);
if(!queryBuffer)
{
printf("Failed to allocate %u bytes for insert query\n", allocSize);
exit(1);
}
sprintf(queryBuffer, insertFormat, (uint32_t)(time/1000000), s2c, opcode, hexEncode(data, data_len));
executeSql(db, queryBuffer);
free(queryBuffer);
if(opcode == 0x3000) //CMSG_AUTH_SESSION //493)
insertClientBuild(data, data_len, db);
}
void freeDatabase(sqlite3 **db)
{
executeSql(*db, "COMMIT;");
sqlite3_close(*db);
}
void populateConfigTable(){
executeSql("insert into config (key,value) values ('cap.compress_interval', 3600)", NULL);
executeSql("insert into config (key,value) values ('cap.keep_sec_limit', 3600)", NULL);
executeSql("insert into config (key,value) values ('cap.keep_min_limit', 86400)", NULL);
executeSql("insert into config (key,value) values ('cap.busy_wait_interval', 60000)", NULL);
}
void rmConfigRow(char* key){
char sql[200];
sprintf(sql, "DELETE FROM config WHERE key='%s'", key);
executeSql(sql, NULL);
}
void emptyDb(){
executeSql("delete from data", NULL);
executeSql("delete from alert;", NULL);
executeSql("delete from alert_interval;", NULL);
executeSql("delete from interval;", NULL);
}
