void ClvDataAccessor::initialize2(QString table, QString dropQuery, QString createQuery){
if(AppInfo::DEBUG_CLV_DATA_ACCESSOR) qDebug() << "initialize table: " << table;
QSqlQuery query;
query.prepare(dropQuery);
execSql(query);
query.prepare(createQuery);
execSql(query);
}
void ConstraintsDialog::doRollback(QString message)
{
if (execSql("ROLLBACK TO CONSTRAINTS;", message))
{
// rollback does not cancel the savepoint
if (execSql("RELEASE CONSTRAINTS;", QString("")))
{
return;
}
}
ui.resultEdit->append(tr("Database may be left with a pending savepoint."));
}
/*
** Execute zSql on database db.
**
** If zSql returns rows, then each row will have exactly one
** column. (This will only happen if zSql begins with "SELECT".)
** Take each row of result and call execSql() again recursively.
**
** The execSqlF() routine does the same thing, except it accepts
** a format string as its third argument
*/
static int execSql(sqlite3 *db, char **pzErrMsg, const char *zSql){
sqlite3_stmt *pStmt;
int rc;
/* printf("SQL: [%s]\n", zSql); fflush(stdout); */
rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
if( rc!=SQLITE_OK ) return rc;
while( SQLITE_ROW==(rc = sqlite3_step(pStmt)) ){
const char *zSubSql = (const char*)sqlite3_column_text(pStmt,0);
assert( sqlite3_strnicmp(zSql,"SELECT",6)==0 );
/* The secondary SQL must be one of CREATE TABLE, CREATE INDEX,
** or INSERT. Historically there have been attacks that first
** corrupt the sqlite_master.sql field with other kinds of statements
** then run VACUUM to get those statements to execute at inappropriate
** times. */
if( zSubSql
&& (strncmp(zSubSql,"CRE",3)==0 || strncmp(zSubSql,"INS",3)==0)
){
rc = execSql(db, pzErrMsg, zSubSql);
if( rc!=SQLITE_OK ) break;
}
}
assert( rc!=SQLITE_ROW );
if( rc==SQLITE_DONE ) rc = SQLITE_OK;
if( rc ){
sqlite3SetString(pzErrMsg, db, sqlite3_errmsg(db));
}
(void)sqlite3_finalize(pStmt);
return rc;
}
void MySqlDataProvider::beginTransaction()
throw (std::runtime_error)
{
if (!mIsConnected)
{
const std::string error = "Trying to begin a transaction while not "
"connected to the database!";
LOG_ERROR(error);
throw std::runtime_error(error);
}
if (inTransaction())
{
const std::string error = "Trying to begin a transaction while another "
"one is still open!";
LOG_ERROR(error);
throw std::runtime_error(error);
}
if (mysql_autocommit(mDb, AUTOCOMMIT_OFF))
{
const std::string error = "Error while trying to disable autocommit";
LOG_ERROR(error);
throw std::runtime_error(error);
}
mInTransaction = true;
execSql("BEGIN");
LOG_DEBUG("SQL: started transaction");
}
void ClvDataAccessor::updateCommand(QString layer, QString group, QList<int> idList){
foreach(int id, idList){
QString sql = QString(COMMAND_UPDATE_GROUP_RAYER).arg(layer, group, QString::number(id));
QSqlQuery query;
query.prepare(sql);
execSql(query);
}
int CDB::selectIntList(const char* sql, CList<int>& result)
{
int re = execSql(sql);
if(re != 1)
{
UniERROR("EXEC SQL: '%s' failed, %s", sql, getErrMsg());
return 0;
}
TSelectResult* selectResult = getSelectResult();
if(selectResult != NULL && selectResult->fieldNum > 0)
{
TRow *row = m_pSelectResult->pRows;
while(row!=NULL)
{
struct Field* field = &(row->arrayField[0]);
int value;
int re = getFieldInt(field, value);
if(!re)
{
UniERROR("EXEC SQL: '%s' result type is %d, not int", sql, field->type);
return 0;
}
result.insert(value);
row = row->next;
}
}
return 1;
}
int CDB::selectStrList(const char* sql, CList<CStr>& result)
{
int re = execSql(sql);
if(re != 1)
{
UniERROR("EXEC SQL: '%s' failed, %s", sql, getErrMsg());
return 0;
}
TSelectResult* selectResult = getSelectResult();
if(selectResult != NULL && selectResult->fieldNum > 0)
{
TRow *row = m_pSelectResult->pRows;
while(row!=NULL)
{
struct Field* field = &(row->arrayField[0]);
if(field->type != T_STRING)
{
UniERROR("EXEC SQL: '%s' result type is %d, not string", sql, field->type);
return 0;
}
CStr value = field->value.stringValue;
result.insert(value);
row = row->next;
}
}
return 1;
}
void SqLiteDataProvider::beginTransaction()
throw (std::runtime_error)
{
if (!mIsConnected)
{
const std::string error = "Trying to begin a transaction while not "
"connected to the database!";
LOG_ERROR(error);
throw std::runtime_error(error);
}
if (inTransaction())
{
const std::string error = "Trying to begin a transaction while anoter "
"one is still open!";
LOG_ERROR(error);
throw std::runtime_error(error);
}
// trying to open a transaction
try
{
execSql("BEGIN TRANSACTION;");
LOG_DEBUG("SQL: started transaction");
}
catch (const DbSqlQueryExecFailure &e)
{
std::ostringstream error;
error << "SQL ERROR while trying to start a transaction: " << e.what();
LOG_ERROR(error);
throw std::runtime_error(error.str());
}
}
void SqLiteDataProvider::rollbackTransaction()
throw (std::runtime_error)
{
if (!mIsConnected)
{
const std::string error = "Trying to rollback a transaction while not "
"connected to the database!";
LOG_ERROR(error);
throw std::runtime_error(error);
}
if (!inTransaction())
{
const std::string error = "Trying to rollback a transaction while no "
"one is open!";
LOG_ERROR(error);
throw std::runtime_error(error);
}
// trying to rollback a transaction
try
{
execSql("ROLLBACK TRANSACTION;");
LOG_DEBUG("SQL: transaction rolled back");
}
catch (const DbSqlQueryExecFailure &e)
{
std::ostringstream error;
error << "SQL ERROR while trying to rollback a transaction: " << e.what();
LOG_ERROR(error);
throw std::runtime_error(error.str());
}
}
void deleteRecord(PGconn *connection){
char query[512];
int id;
printf("Wpisz id rekordu ktory usunac");
scanf("%d",&id);
sprintf(query,"DELETE FROM pracownik WHERE pracownik_id=%d",id);
execSql(connection,query);
}
bool SqliteDB::deleteTable(const string tblname, const string key, const string val)
{
string sqlcmd = "DELETE FROM ? WHERE ? == '?'";
DBUtil::addParam(sqlcmd, tblname);
DBUtil::addParam(sqlcmd, key);
DBUtil::addParam(sqlcmd, val);
return execSql(sqlcmd);
}
void ClvDataAccessor::writeProjectInfo(QString product){
QSqlQuery query;
query.prepare(PROJECT_INSERT_TO_TABLE);
query.bindValue(BIND_MAJOR_VERSION, AppInfo::MAJOR_VERSION);
query.bindValue(BIND_MINOR_VERSION, AppInfo::MINOR_VERSION);
query.bindValue(BIND_PRODUCT, product);
query.bindValue(BIND_CREATE_DATE, getCurrentDateString());
query.bindValue(BIND_UPDATE_DATE, getCurrentDateString());
execSql(query);
}
QVariant Application::eventInfo()
{
static QVariantMap info;
if(info.isEmpty()) {
QSqlQuery q = execSql("SELECT ckey, value FROM config");
while(q.next()) info[q.value(0).toString()] = q.value(1);
info["profile"] = profile();
}
return info;
}
enum ePrivilegeLevel cUser::getRights(QSqlQuery& q)
{
if (isNullId()) {
if (isNullName()) EXCEPTION(EDATA, 0, trUtf8("Nincs megadva a felhasználói ID, vagy név"));
setByName(q);
}
QString sql = "SELECT MAX(group_rights) FROM group_users JOIN groups USING(group_id) WHERE user_id = ?";
if (!execSql(q, sql, get(idIndex()))) EXCEPTION(EDATA, 0, trUtf8("Nem azonosÃtható a felhasználói ID, vagy név"));
_privilegeLevel = (enum ePrivilegeLevel)::privilegeLevel(q.value(0).toString(), EX_IGNORE);
return (ePrivilegeLevel)_privilegeLevel;
}
void ClvDataAccessor::writeCommand(QString name, QString decoratedName, QString scope, QString tag, QString file, QByteArray data){
QSqlQuery query;
query.prepare(COMMAND_INSERT_TO_TABLE);
query.bindValue(BIND_NAME, name);
query.bindValue(BIND_DECORATED_NAME, decoratedName);
query.bindValue(BIND_SCOPE, scope);
query.bindValue(BIND_TAG, tag);
query.bindValue(BIND_FILE, file);
query.bindValue(BIND_DATA, data);
execSql(query);
}
static int execSqlF(sqlite3 *db, char **pzErrMsg, const char *zSql, ...){
char *z;
va_list ap;
int rc;
va_start(ap, zSql);
z = sqlite3VMPrintf(db, zSql, ap);
va_end(ap);
if( z==0 ) return SQLITE_NOMEM;
rc = execSql(db, pzErrMsg, z);
sqlite3DbFree(db, z);
return rc;
}
void searchByBirthDate(PGconn *connection){
char date_f[10],date_t[10],query[512];
printf("\tZnajdz pracownika po dacie\n\n");
printf("data od: ");
scanf("%s",date_f);
printf("\ndata do: ");
scanf("%s",date_t);
sprintf(query,"SELECT * FROM pracownik WHERE data_u between \'%s\' and \'%s\' ORDER BY pracownik_id",date_f,date_t);
execSql(connection,query);
}
void searchByPosAndSalary(PGconn *connection){
char position[50],salary[35],query[512];
printf("\tZnajdz pracownika po Stanowisku i pensji\n\n");
printf("Stanowisko: ");
scanf("%s",position);
printf("\nNazwisko: ");
scanf("%s",salary);
sprintf(query,"SELECT * FROM pracownik WHERE stanowisko = \'%s\' and nazwisko = \'%s\' ORDER BY pracownik_id",position,salary);
execSql(connection,query);
}
bool ClvDataAccessor::checkTableSyntax2(QString tableString, QString checkQuery, QString createQuery){
bool isOk = false;
QSqlQuery query;
query.prepare(checkQuery);
execSql(query);
if(query.next()){
if(AppInfo::DEBUG_CLV_DATA_ACCESSOR) qDebug() << QString("Table '%1' exists.").arg(tableString);
QString pastCreateQuery = query.value(0).toString();
isOk = (createQuery == pastCreateQuery);
}
return isOk;
}
void addRecord(PGconn *connection){
char imie[45],nazwisko[50],stanowisko[50],data[10],query[512];
double pensja;
int nr;
printf(" wpisz Imie\n"); scanf("%s",imie);
printf(" wpisz Nazwisko\n"); scanf("%s",nazwisko);
printf(" wpisz Pensje\n"); scanf("%lf",&pensja);
printf(" wpisz date urodzenia\n"); scanf("%s",data);
printf(" wpisz stanowisko\n"); scanf("%s",stanowisko);
printf(" wpisz nr pracownika\n"); scanf("%d",&nr);
printf("%lf",pensja);
sprintf(query,"INSERT INTO pracownik(imie,nazwisko,pensja,data_u,stanowisko,nr) VALUES(\'%s\',\'%s\',%6.2lf,\'%s\',\'%s\',%d)",imie,nazwisko,pensja,data,stanowisko,nr);
execSql(connection,query);
}
void editRecord(PGconn *connection){
char column[50],query[512];
int id;
showAllRecords(connection);
printf("nazwa edytowanej kolumny:\n (imie,nazwisko,pensja,data_u,stanowisko,nr)");
scanf("%s",column);
printf("id edytowanego rekordu:\n (imie,nazwisko,pensja,data_u,stanowisko,nr)");
scanf("%d",&id);
printf("\nPodaj nowa wartosc dla kolumny %s\n",column);
if (strcmp(column,"imie")==0 || strcmp(column,"nazwisko")==0 || strcmp(column,"stanowisko")==0 || strcmp(column,"data")==0){
char val[50];
scanf("%s",val);
sprintf(query,"UPDATE pracownik SET %s = \'%s\' WHERE pracownik_id = %d ",column,val,id);
execSql(connection,query);
}
else{
if(strcmp(column,"pensja")==0){
double val;
scanf("%lf",&val);
sprintf(query,"UPDATE pracownik SET %s = %6.2lf WHERE pracownik_id = %d ",column,val,id);
execSql(connection,query);
}
else{
if(strcmp(column,"nr")==0){
int val;
scanf("%d",&val);
sprintf(query,"UPDATE pracownik SET %s = %d WHERE pracownik_id = %d ",column,val,id);
execSql(connection,query);
}else{
printf("\nbrak kolumny o nazwie %s\n",column);
}
}
}
}
bool SqliteDB::updateTable(const string tblname, const string colname[], const string colval[], const int length, string cond)
{
// UPDATE tbl SET col1 = val1, cal2 = val2 WHERE key == xxx"
string sqlcmd = "UPDATE " + tblname + " SET ";
for (int i = 0; i < length; i++) {
string val = (colval[i]);
DBUtil::escapeCmd(val);
sqlcmd += colname[i] + " = " + val;
if (i != length -1) sqlcmd += ", ";
}
sqlcmd += " ";
sqlcmd += cond;
return execSql(sqlcmd);
}
void Database::createStructure()
{
QStringList queries;
queries << "CREATE TABLE IF NOT EXISTS categories (id INTEGER PRIMARY KEY, name TEXT)"
<< "CREATE TABLE IF NOT EXISTS characters (id INTEGER PRIMARY KEY, uuid TEXT, block NUMERIC, bm NUMERIC, bs NUMERIC, "
"category_id NUMERIC, dodge NUMERIC, dr_arms NUMERIC, dr_face NUMERIC, dr_foots NUMERIC, "
"dr_hands NUMERIC, dr_legs NUMERIC, dr_neck NUMERIC, dr_skull NUMERIC, dr_torso NUMERIC, dx NUMERIC, "
"fp NUMERIC, hp NUMERIC, ht NUMERIC, iq NUMERIC, name TEXT, notes TEXT, "
"parry NUMERIC, per NUMERIC, st NUMERIC, will NUMERIC)"
<< "CREATE TABLE IF NOT EXISTS manevrous (id INTEGER PRIMARY KEY, name TEXT, tooltip TEXT)"
<< "CREATE TABLE IF NOT EXISTS postures (id INTEGER PRIMARY KEY, name TEXT, tooltip TEXT)"
<< "CREATE TABLE IF NOT EXISTS manual_effects (id INTEGER PRIMARY KEY, name TEXT)";
foreach (QString query, queries) {
execSql(query);
}
qlonglong PopulatorDialog::tableRowCount()
{
QString sql = QString("select count(1) from ")
+ Utils::quote(m_schema)
+ "."
+ Utils::quote(m_table)
+ ";";
QSqlQuery query(sql, QSqlDatabase::database(SESSION_NAME));
if (!execSql(sql, tr("Cannot get statistics for table")))
{
return -1;
}
while(query.next())
return query.value(0).toLongLong();
return -1;
}
bool SqliteDB::createTable(const string tblname, const string colname[], const string coltype[], const int length)
{
char *zErrMsg = 0;
// CREATE TABLE tb_name(col1 TEXT PRIMARY KEY, col2 TEXT)
string sqlcmd = "CREATE TABLE " + tblname;
sqlcmd += "(";
for (int i = 0; i < length; i++) {
sqlcmd += colname[i] + " " + coltype[i];
if (i != length -1) sqlcmd += ", ";
}
sqlcmd += ")";
return execSql(sqlcmd);
}
void SchemaBrowser::setPragmaButton_clicked()
{
int row = pragmaTable->currentRow();
QTableWidgetItem * item = pragmaTable->item(row, 0);
QString text(item->text().toLower());
QString value(pragmaTable->item(row, 1)->text());
// TODO: create a better way to get new value.
// TODO: Check sane values etc. It will need a some description of pragmas (XML?... now I wisth it could be written in python dicts...
bool ok;
QString newValue = QInputDialog::getText(this, "Set Pragma", text, QLineEdit::Normal, value, &ok);
if (ok && !text.isEmpty())
{
execSql(QString("PRAGMA main.%1 = %2;").arg(text).arg(newValue));
buildPragmasTree();
}
}
/*
** Execute zSql on database db. The statement returns exactly
** one column. Execute this as SQL on the same database.
*/
static int execExecSql(sqlite3 *db, const char *zSql){
sqlite3_stmt *pStmt;
int rc;
rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0);
if( rc!=SQLITE_OK ) return rc;
while( SQLITE_ROW==sqlite3_step(pStmt) ){
rc = execSql(db, (char*)sqlite3_column_text(pStmt, 0));
if( rc!=SQLITE_OK ){
sqlite3_finalize(pStmt);
return rc;
}
}
return sqlite3_finalize(pStmt);
}
bool ColumnPropertiesHandler::handleURI(URI& uri)
{
bool addField = uri.action == "add_field";
bool editField = uri.action == "edit_field";
if (!addField && !editField)
return false;
wxWindow* w = getParentWindow(uri);
MetadataItem* mo = extractMetadataItemFromURI<MetadataItem>(uri);
if (!mo || !w)
return true;
Column* c = 0;
Table* t;
if (addField)
t = (Table*)mo;
else
{
c = (Column*)mo;
t = c->getTable();
}
wxString statements, title;
{ // we want FPD to go out of scope and get destroyed since the action in
// ESF can destroy the field, and take down the FPD. Accessing FPD in turn
// leads to mysterious crash
FieldPropertiesDialog fpd(w, t, c);
// NOTE: this has been moved here from OnOkButtonClick() to make frame
// activation work properly. Basically activation of another
// frame has to happen outside wxDialog::ShowModal(), because it
// does at the end re-focus the last focused control, raising
// the parent frame over the newly created sql execution frame
if (fpd.ShowModal() == wxID_OK)
{
statements = fpd.getStatementsToExecute();
title = fpd.getStatementTitle();
}
}
if (!statements.IsEmpty())
execSql(w, title, t->getDatabase(), statements, true);
return true;
}
bool ReorderFieldsHandler::handleURI(URI& uri)
{
if (uri.action != "reorder_fields")
return false;
Table* t = extractMetadataItemFromURI<Table>(uri);
wxWindow* w = getParentWindow(uri);
if (!t || !w)
return true;
ReorderFieldsDialog rfd(w, t);
// NOTE: this has been moved here from OnOkButtonClick() to make frame
// activation work properly. Basically activation of another
// frame has to happen outside wxDialog::ShowModal(), because it
// does at the end re-focus the last focused control, raising
// the parent frame over the newly created sql execution frame
if (rfd.ShowModal() == wxID_OK)
{
execSql(w, rfd.GetTitle(), t->getDatabase(),
rfd.getStatementsToExecute(), true);
}
return true;
}
Field* CDB::selectOneField(const char* sql)
{
int re = execSql(sql);
if(re != 1)
{
UniERROR("EXEC SQL: '%s' failed, %s", sql, getErrMsg());
return NULL;
}
TSelectResult* selectResult = getSelectResult();
if(selectResult == NULL)
{
UniERROR("EXEC SQL: '%s' result is NULL", sql);
return NULL;
}
if(selectResult->fieldNum < 1 || selectResult->pRows == NULL)
{
return NULL;
}
if(selectResult->pRows->arrayField[0].isNull)
{
return NULL;
}
return &(selectResult->pRows->arrayField[0]);
}
