bool marky::Backend_SQLite::update_snippets_impl(const State& state,
scorer_t scorer, snippet_ptr_set_t& snippets) {
bool ok = true;
for (snippet_ptr_set_t::const_iterator iter = snippets.begin();
iter != snippets.end(); ++iter) {
Snippet& snippet = **iter;
/* update existing scores; use increment() since these snippets were not just created */
if (!bind_int64(stmt_update_snippet, 1, snippet.score(scorer, state)) ||
!bind_int64(stmt_update_snippet, 2, state.time) ||
!bind_int64(stmt_update_snippet, 3, state.count) ||
!bind_words(stmt_update_snippet, 4, snippet.words)) {
ok = false;
}
int update_step = sqlite3_step(stmt_update_snippet);
if (update_step != SQLITE_DONE) {
ERROR("Error when parsing response to '%s': %d/%s",
QUERY_UPDATE_SNIPPET, update_step, sqlite3_errmsg(db));
}
sqlite3_clear_bindings(stmt_update_snippet);
sqlite3_reset(stmt_update_snippet);
if (!ok) {
break;
}
}
return ok;
}
int bind_params(sqlite3_stmt* stmt, const mxArray *params, int column)
{
TYPECHECK(params, mxSTRUCT_CLASS);
int i, n = mxGetNumberOfFields(params);
for (i = 0; i < n; i++) {
mxArray *array = mxGetFieldByNumber(params, column, i);
mxClassID cls = mxGetClassID(array);
int res;
switch (cls) {
case mxFUNCTION_CLASS:
break;
case mxCHAR_CLASS:
res = bind_string(stmt, i + 1, array);
break;
case mxSINGLE_CLASS:
case mxDOUBLE_CLASS:
res = bind_double(stmt, i + 1, array);
break;
default: /* anything else is an integer */
res = bind_int64(stmt, i + 1, array);
}
if (res != SQLITE_OK) {
return res;
}
}
return SQLITE_OK;
}
void DB::addMessage(Message &msg)
{
int res;
SQLOK(bind_int64(stmt_add_message, 1, msg.time),
"binding message time");
SQLOK(bind_int (stmt_add_message, 2, (int) msg.type),
"binding message type");
SQLOK(bind_int (stmt_add_message, 3, msg.bufferid),
"binding message bufferid");
SQLOK(bind_int (stmt_add_message, 4, msg.senderid),
"binding message senderid");
SQLOK(bind_text (stmt_add_message, 5, msg.text.data(), msg.text.size(),
NULL), "binding message text");
SQLRES(step(stmt_add_message), SQLITE_DONE,
"running message insertion statement");
SQLOK(reset(stmt_add_message),
"reseting message insertion statement");
}
int main ()
{
sqlite3 * db;
char * sql;
sqlite3_stmt * stmt;
int i;
CALL_SQLITE (open ("db/test.sqlite", & db));
sql = "DROP TABLE \"employees\"";
CALL_SQLITE(exec(db,sql,0,0,0));
sql = "CREATE TABLE \"employees\" (\"token\" integer(8), \"name\" varchar(20) NOT NULL)";
CALL_SQLITE(exec(db,sql,0,0,0));
sql = "INSERT INTO Employees(name, token) VALUES('employee-1', ?)";
CALL_SQLITE (prepare_v2 (db, sql, strlen (sql) + 1, & stmt, NULL));
CALL_SQLITE (bind_int64 (stmt, 1, 4907021672125087744 + 100));
CALL_SQLITE_EXPECT (step (stmt), DONE);
return 0;
}
bool marky::Backend_SQLite::insert_snippets_impl(const snippet_ptr_set_t& snippets,
bool allow_updates) {
bool ok = true;
sqlite3_stmt* snippet_update_stmt = (allow_updates) ? stmt_upsert_snippet : stmt_insert_snippet;
for (snippet_ptr_set_t::const_iterator iter = snippets.begin();
iter != snippets.end(); ++iter) {
const Snippet& snippet = **iter;
//ERROR("INSERT: %s", snippet.str().c_str());
/* snippets table */
if (!bind_words(snippet_update_stmt, 1, snippet.words) ||
!bind_int64(snippet_update_stmt, 2, snippet.cur_score()) ||
!bind_int64(snippet_update_stmt, 3, snippet.cur_state().time) ||
!bind_int64(snippet_update_stmt, 4, snippet.cur_state().count)) {
ok = false;
} else {
int insert_step = sqlite3_step(snippet_update_stmt);
if (insert_step != SQLITE_DONE) {
ok = false;
ERROR("Error when flushing entry with '%s': %d/%s [%s]",
QUERY_INSERT_SNIPPET, insert_step, sqlite3_errmsg(db),
snippet.str().c_str());
}
}
sqlite3_clear_bindings(snippet_update_stmt);
sqlite3_reset(snippet_update_stmt);
sqlite3_int64 snippet_id = sqlite3_last_insert_rowid(db);
/* nexts table */
words_t words_subset = snippet.words;
words_subset.pop_back();// all except back
if (!bind_words(stmt_insert_next, 1, words_subset) ||
!bind_int64(stmt_insert_next, 2, snippet_id)) {
ok = false;
} else {
int insert_step = sqlite3_step(stmt_insert_next);
if (insert_step != SQLITE_DONE) {
ok = false;
ERROR("Error when parsing response to '%s': %d/%s",
QUERY_INSERT_NEXT, insert_step, sqlite3_errmsg(db));
}
}
sqlite3_clear_bindings(stmt_insert_next);
sqlite3_reset(stmt_insert_next);
/* prevs table */
words_subset.push_back(snippet.words.back());
words_subset.pop_front();// all except front (from all except back)
if (!bind_words(stmt_insert_prev, 1, words_subset) ||
!bind_int64(stmt_insert_prev, 2, snippet_id)) {
ok = false;
} else {
int insert_step = sqlite3_step(stmt_insert_prev);
if (insert_step != SQLITE_DONE) {
ok = false;
ERROR("Error when parsing response to '%s': %d/%s",
QUERY_INSERT_PREV, insert_step, sqlite3_errmsg(db));
}
}
sqlite3_clear_bindings(stmt_insert_prev);
sqlite3_reset(stmt_insert_prev);
if (!ok) {
break;
}
}
return ok;
}
static int add_log(const char *table,
const opal_value_t *kvs, int nkvs)
{
int i, rc;
char *sql, **cmd = NULL, *tmp;
sqlite3_stmt *stmt;
opal_output_verbose(2, opal_db_base_framework.framework_output,
"Logging data for table %s", table);
/* setup the insert statement */
for (i=0; i < nkvs; i++) {
opal_argv_append_nosize(&cmd, "?");
}
tmp = opal_argv_join(cmd, ',');
asprintf(&sql, "INSERT INTO %s VALUES (%s)", table, tmp);
free(tmp);
opal_argv_free(cmd);
/* use the next worker thread */
OPAL_SQLITE_CMD(prepare_v2(dbhandles[active], sql, strlen(sql)+1, &stmt, NULL), dbhandles[active], &rc);
if (SQLITE_OK != rc) {
return OPAL_ERROR;
}
/* cycle through the provided values and construct
* an insert command for them - note that the values
* MUST be in column-order for the database!
*/
for (i=0; i < nkvs; i++) {
switch (kvs[i].type) {
case OPAL_STRING:
OPAL_SQLITE_CMD(bind_text(stmt, i, kvs[i].data.string, strlen(kvs[i].data.string), NULL),
dbhandles[active], &rc);
break;
case OPAL_INT32:
OPAL_SQLITE_CMD(bind_int(stmt, i, kvs[i].data.int32), dbhandles[active], &rc);
break;
case OPAL_INT16:
OPAL_SQLITE_CMD(bind_int(stmt, i, kvs[i].data.int16), dbhandles[active], &rc);
break;
case OPAL_PID:
OPAL_SQLITE_CMD(bind_int64(stmt, i, kvs[i].data.pid), dbhandles[active], &rc);
break;
case OPAL_INT64:
OPAL_SQLITE_CMD(bind_int64(stmt, i, kvs[i].data.int64), dbhandles[active], &rc);
break;
case OPAL_FLOAT:
OPAL_SQLITE_CMD(bind_double(stmt, i, kvs[i].data.fval), dbhandles[active], &rc);
break;
case OPAL_TIMEVAL:
asprintf(&tmp, "%d.%06d", (int)kvs[i].data.tv.tv_sec, (int)kvs[i].data.tv.tv_usec);
OPAL_SQLITE_CMD(bind_text(stmt, i, tmp, strlen(tmp), NULL),
dbhandles[active], &rc);
free(tmp);
break;
}
if (SQLITE_OK != rc) {
return OPAL_ERROR;
}
}
OPAL_SQLITE_OP(step(stmt), DONE, dbhandles[active], &rc);
if (SQLITE_OK != rc) {
return OPAL_ERROR;
}
opal_output_verbose(2, opal_db_base_framework.framework_output,
"INSERTED ROW %d", (int)sqlite3_last_insert_rowid(dbhandles[active]));
/* cycle to the next worker thread */
active++;
if (nthreads < active) {
active = 0;
}
return OPAL_SUCCESS;
}
static int store(struct orcm_db_base_module_t *imod,
const char *primary_key,
opal_list_t *kvs)
{
int i, rc;
char *sql, **cmd = NULL, *tmp;
sqlite3_stmt *stmt;
opal_value_t *kv;
mca_db_sqlite_module_t *mod = (mca_db_sqlite_module_t*)imod;
/* setup the insert statement */
for (i=0; i < (int)opal_list_get_size(kvs); i++) {
opal_argv_append_nosize(&cmd, "?");
}
tmp = opal_argv_join(cmd, ',');
asprintf(&sql, "INSERT INTO %s VALUES (%s)", mod->dbfile, tmp);
free(tmp);
opal_argv_free(cmd);
/* use the next worker thread */
ORCM_SQLITE_CMD(prepare_v2(mod->dbhandles[mod->active], sql, strlen(sql)+1, &stmt, NULL), mod->dbhandles[mod->active], &rc);
if (SQLITE_OK != rc) {
return ORCM_ERROR;
}
/* cycle through the provided values and construct
* an insert command for them - note that the values
* MUST be in column-order for the database!
*/
OPAL_LIST_FOREACH(kv, kvs, opal_value_t) {
switch (kv->type) {
case OPAL_STRING:
ORCM_SQLITE_CMD(bind_text(stmt, i, kv->data.string, strlen(kv->data.string), NULL),
mod->dbhandles[mod->active], &rc);
break;
case OPAL_INT32:
ORCM_SQLITE_CMD(bind_int(stmt, i, kv->data.int32), mod->dbhandles[mod->active], &rc);
break;
case OPAL_INT16:
ORCM_SQLITE_CMD(bind_int(stmt, i, kv->data.int16), mod->dbhandles[mod->active], &rc);
break;
case OPAL_PID:
ORCM_SQLITE_CMD(bind_int64(stmt, i, kv->data.pid), mod->dbhandles[mod->active], &rc);
break;
case OPAL_INT64:
ORCM_SQLITE_CMD(bind_int64(stmt, i, kv->data.int64), mod->dbhandles[mod->active], &rc);
break;
case OPAL_FLOAT:
ORCM_SQLITE_CMD(bind_double(stmt, i, kv->data.fval), mod->dbhandles[mod->active], &rc);
break;
case OPAL_TIMEVAL:
asprintf(&tmp, "%d.%06d", (int)kv->data.tv.tv_sec, (int)kv->data.tv.tv_usec);
ORCM_SQLITE_CMD(bind_text(stmt, i, tmp, strlen(tmp), NULL),
mod->dbhandles[mod->active], &rc);
free(tmp);
break;
}
if (SQLITE_OK != rc) {
return ORCM_ERROR;
}
}
ORCM_SQLITE_OP(step(stmt), DONE, mod->dbhandles[mod->active], &rc);
if (SQLITE_OK != rc) {
return ORCM_ERROR;
}
opal_output_verbose(2, orcm_db_base_framework.framework_output,
"INSERTED ROW %d", (int)sqlite3_last_insert_rowid(mod->dbhandles[mod->active]));
/* cycle to the next worker thread */
mod->active++;
if (mod->nthreads < mod->active) {
mod->active = 0;
}
return ORCM_SUCCESS;
}