QSqlQuery
QcOfflineCacheDatabase::insert(const QString & table, const KeyValuePair & kwargs, bool commit_request)
{
QSqlQuery query = new_query();
QStringList fields = kwargs.keys();
QString sql_query =
QStringLiteral("INSERT INTO ") + table +
QStringLiteral(" (") + fields.join(',') + QStringLiteral(") VALUES (");
for (int i = 0; i < fields.size(); i++) {
if (i > 0)
sql_query += ',';
sql_query += '?';
}
sql_query += ')';
qInfo() << sql_query << kwargs;
query.prepare(sql_query);
for (const auto & value : kwargs.values())
query.addBindValue(value);
if (!query.exec())
qWarning() << query.lastError().text();
if (commit_request)
commit();
return query;
}
//=========================================================================
int Database::simple_query_num(const std::string& query)
{
std::auto_ptr<DbQuery> q(new_query(query));
if (!q->exec(0)) return -1;
if (!q->next_result()) return -1;
ScriptRef* row_ref = q->result_list();
ScriptList* row = dynamic_cast<ScriptList*>(row_ref->object());
ScriptRef* a_num = row->get(0);
int result = a_num ? a_num->object()->get_int() : -1;
delete row_ref;
return result;
}
void
QcOfflineCacheDatabase::create_tables()
{
// # https://www.sqlite.org/autoinc.html
// # Fixme: how to handle auto-increment overflow ?
QList<QString> schemas;
const QString metadata_schema =
"CREATE TABLE metadata ("
"version INTEGER"
")";
schemas << metadata_schema;
const QString provider_schema =
"CREATE TABLE provider ("
"provider_id INTEGER PRIMARY KEY AUTOINCREMENT,"
"name TEXT NOT NULL"
")";
schemas << provider_schema;
const QString map_level_schema =
"CREATE TABLE map_level ("
"map_level_id INTEGER PRIMARY KEY AUTOINCREMENT,"
"provider_id INTEGER, "
"map_id INTEGER, "
"level INTEGER, "
"FOREIGN KEY(provider_id) REFERENCES provider_id(provider_id)"
")";
schemas << map_level_schema;
// PRIMARY KEY (map_level_id, row, column),
const QString tile_schema =
"CREATE TABLE tile ("
"map_level_id INTEGER, "
"row INTEGER, "
"column INTEGER, "
"offline_count INTEGER, "
"FOREIGN KEY(map_level_id) REFERENCES map_level(map_level_id)"
")";
schemas << tile_schema;
QSqlQuery query = new_query();
for (const auto & sql_query : schemas)
if (!query.exec(sql_query))
qWarning() << query.lastError().text();
init_version();
commit();
}
QSqlQuery
QcOfflineCacheDatabase::update(const QString & table, const KeyValuePair & kwargs, const QString & where)
{
QSqlQuery query = new_query();
QString sql_query = QStringLiteral("UPDATE ") + table + QStringLiteral(" SET ") + format_kwarg(kwargs);
if (!where.isEmpty())
sql_query += QStringLiteral(" WHERE ") + where;
qInfo() << sql_query << kwargs;
if (!query.exec(sql_query))
qWarning() << query.lastError().text();
return query;
}
QSqlQuery
QcOfflineCacheDatabase::delete_row(const QString & table, const QString & where)
{
QSqlQuery query = new_query();
QString sql_query = QStringLiteral("DELETE FROM ") + table;
if (!where.isEmpty())
sql_query += QStringLiteral(" WHERE ") + where;
qInfo() << sql_query;
if (!query.exec(sql_query))
qWarning() << query.lastError().text();
return query;
}
QSqlQuery
QcOfflineCacheDatabase::select(const QString & table, const QStringList & fields, const QString & where)
{
QSqlQuery query = new_query();
QString sql_query = QStringLiteral("SELECT ") + fields.join(',') + QStringLiteral(" FROM ") + table;
if (!where.isEmpty())
sql_query += QStringLiteral(" WHERE ") + where;
qInfo() << sql_query << fields;
if (!query.exec(sql_query))
qWarning() << query.lastError().text();
return query;
}
bool db_query_widget::init(const db_connection *cnn, const QString &SQL)
{
pv_cnn = cnn;
pv_SQL = SQL;
pv_vlayout = new QVBoxLayout(this);
lb_title = new QLabel(tr("SQL query widget - Connection: " ) + pv_cnn->get_cnn_name());
pv_vlayout->addWidget(lb_title);
pv_table = new QTableWidget(this);
pv_vlayout->addWidget(pv_table);
le_sql = new db_mem_lineedit;
pv_vlayout->addWidget(le_sql);
pv_hlayout = new QHBoxLayout(this);
pv_vlayout->addLayout(pv_hlayout);
pb_submit = new QPushButton(tr("&Submit"));
pv_hlayout->addWidget(pb_submit);
pb_html = new QPushButton(tr("&Export HTML"));
pv_hlayout->addWidget(pb_html);
pb_pdf = new QPushButton(tr("Export PDF"));
pv_hlayout->addWidget(pb_pdf);
lb_status = new QLabel(tr("Ready"));
pv_vlayout->addWidget(lb_status);
//pv_printer = new QPrinter;
//pv_painter = new QPainter;
pv_text_doc = new QTextDocument(this);
te_text = new QTextEdit;
//te_text->show();
pv_table->setEditTriggers(QAbstractItemView::NoEditTriggers);
pv_query = new QSqlQuery(pv_cnn->get_db());
if(!pv_SQL.isEmpty()){
new_query(pv_SQL);
}
//pv_label.init(cnn);
connect(pb_submit, SIGNAL(clicked()),this, SLOT(submit_query()));
connect(pb_html, SIGNAL(clicked()),this, SLOT(export_html()));
connect(pb_pdf, SIGNAL(clicked()),this, SLOT(export_pdf()));
return true;
}
//=========================================================================
bool Database::simple_query(const std::string& query)
{
std::auto_ptr<DbQuery> q(new_query(query));
return q->exec(0);
}
bool db_query_widget::submit_query()
{
return new_query(le_sql->text());
}
void Querytextedit::make_query(QString query)
{
emit new_query(query);
}
