static void db_tag_blob(int bid, const char **tags, size_t ntags) {
int tid;
size_t i;
sqlite3_stmt *stmt;
const char *sql =
"INSERT INTO fk_blobs_tags (blob_id, tag_id) "
"VALUES (?, ?)";
err_assert(bid > 0);
if(ntags < 1)
return;
DB_STMT_PREP(sql, &stmt);
DB_BIND_INT(stmt, 1, bid);
for(i = 0; i < ntags; i++) {
if(i > 0)
DB_STMT_RESET(stmt);
tid = db_find_or_create_tag(tags[i]);
DB_BIND_INT(stmt, 2, tid);
if(db_stmt_step(stmt) == 0)
err_panic(0, "expected SQLITE_DONE from %s: ", sql);
}
DB_STMT_FINALIZE(stmt);
}
/* this function should be run within a transaction */
static int db_find_or_create_blob(const void *data, size_t bytes, int raw) {
sqlite3_stmt *stmt;
int bid;
if( (bid = db_blob_id(data, bytes)) > 0) {
return bid;
}
DB_STMT_PREP("INSERT INTO blobs (value, raw) VALUES (?, ?)", &stmt);
DB_BIND_BLOB(stmt, 1, data, bytes);
DB_BIND_INT(stmt, 2, raw);
DB_STMT_EXEC(stmt);
bid = sqlite3_last_insert_rowid(g.handle);
return bid;
}
void db_update_chosen_at(int id) {
sqlite3_stmt *stmt;
DB_BEGIN();
DB_STMT_PREP(
"UPDATE blobs "
"SET chosen_at = strftime('%s','now') "
"WHERE id = ?",
&stmt
);
DB_BIND_INT(stmt, 1, id);
if(db_stmt_step(stmt) != -1)
err_panic(0, "didnt expect statement to return rows");
DB_STMT_FINALIZE(stmt);
DB_COMMIT();
}
void db_trash(int bid) {
sqlite3_stmt *stmt;
DB_BEGIN();
DB_STMT_PREP(
"UPDATE blobs "
"SET trash = 1 "
"WHERE id = ?",
&stmt
);
DB_BIND_INT(stmt, 1, bid);
if(db_stmt_step(stmt) != -1)
err_panic(0, "didnt expect statement to return rows");
DB_STMT_FINALIZE(stmt);
DB_COMMIT();
}
void db_query_prepare(struct db_query *query, unsigned int offset, const uint8_t **queries,
size_t nqueries, const uint8_t **tags, size_t ntags) {
char *sql;
size_t i;
int offset_idx;
if(nqueries < 1 && ntags < 1) {
sql =
"SELECT DISTINCT "
DB_QUERY_COLUMNS ", 0 AS score "
"FROM blobs b "
"WHERE " DB_NON_TRASH_BLOB " "
"ORDER BY b.chosen_at DESC "
DB_QUERY_LIMIT ;
}
else
db_query_fmt(nqueries, ntags, &sql);
DB_STMT_PREP(sql, &query->stmt);
aug_log("db: prepare sql (%p) %s\n", query->stmt, sql);
#define DB_QP_BIND(_idx, _ptr) \
DB_BIND_BUF(text, query->stmt, _idx, _ptr, -1, SQLITE_TRANSIENT)
for(i = 0; i < nqueries; i++) {
/*aug_log("bind %s to ?(%d)\n", queries[i], i+1);*/
DB_QP_BIND(i+1, (const char *) queries[i]);
}
for(i = 0; i < ntags; i++) {
/*aug_log("bind %s to ?(%d)\n", tags[i], nqueries+i+1);*/
DB_QP_BIND(nqueries+i+1, (const char *) tags[i]);
}
#undef DB_QP_BIND
DB_BIND_PRM_IDX(query->stmt, "@offset", &offset_idx);
DB_BIND_INT(query->stmt, offset_idx, offset);
if(!(nqueries < 1 && ntags < 1))
talloc_free(sql);
}
static int beatmap_insert(osux_beatmap_db *db, osux_beatmap *bm)
{
int ret;
if (!db->insert_prepared) {
ret = osux_database_prepare_query(
&db->base, "INSERT INTO beatmap "
"(osu_beatmap_id, game_mode, audio_filename, diff_name,"
"md5_hash, osu_filename, file_path,"
"circles, sliders, spinners, last_modification, last_checked,"
"approach_rate, circle_size, hp_drain, overall_diff,"
"slider_velocity, stack_leniency, drain_time, total_time,"
"preview_time, bpm_avg, bpm_max, bpm_min, local_offset,"
"online_offset, already_played, last_played, ignore_hitsound,"
"ignore_skin, disable_sb, disable_video, visual_override,"
"mania_scroll_speed)"
" VALUES"
"(:osu_beatmap_id, :game_mode, :audio_filename, :diff_name,"
":md5_hash, :osu_filename, :file_path,"
":circles, :sliders, :spinners, :last_modification, :last_checked,"
":approach_rate, :circle_size, :hp_drain, :overall_diff,"
":slider_velocity, :stack_leniency, :drain_time, :total_time,"
":preview_time, :bpm_avg, :bpm_max, :bpm_min, :local_offset,"
":online_offset, :already_played, :last_played, :ignore_hitsound,"
":ignore_skin, :disable_sb, :disable_video, :visual_override,"
":mania_scroll_speed)");
if (ret < 0)
return ret;
db->insert_prepared = true;
}
DB_BIND_INT(db, "osu_beatmap_id", bm->BeatmapID);
DB_BIND_INT(db, "game_mode", bm->Mode);
DB_BIND_TEXT(db, "audio_filename", bm->AudioFilename);
DB_BIND_TEXT(db, "md5_hash", bm->md5_hash);
DB_BIND_TEXT(db, "osu_filename", bm->osu_filename);
DB_BIND_TEXT(db, "file_path", bm->file_path);
DB_BIND_TEXT(db, "diff_name", bm->Version);
DB_BIND_INT(db, "circles", bm->circles);
DB_BIND_INT(db, "sliders", bm->sliders);
DB_BIND_INT(db, "spinners", bm->spinners);
DB_BIND_INT(db, "last_modification", bm->last_modification);
DB_BIND_INT(db, "last_checked", bm->last_checked);
DB_BIND_DOUBLE(db, "approach_rate", bm->ApproachRate);
DB_BIND_DOUBLE(db, "circle_size", bm->CircleSize);
DB_BIND_DOUBLE(db, "hp_drain", bm->HPDrainRate);
DB_BIND_DOUBLE(db, "overall_diff", bm->OverallDifficulty);
DB_BIND_DOUBLE(db, "slider_velocity", bm->SliderMultiplier);
DB_BIND_DOUBLE(db, "stack_leniency", bm->StackLeniency);
DB_BIND_INT(db, "drain_time", bm->drain_time);
DB_BIND_INT(db, "total_time", bm->total_time);
DB_BIND_INT(db, "preview_time", bm->PreviewTime);
DB_BIND_INT(db, "bpm_avg", bm->bpm_avg);
DB_BIND_INT(db, "bpm_min", bm->bpm_min);
DB_BIND_INT(db, "bpm_max", bm->bpm_max);
DB_BIND_INT(db, "local_offset", bm->local_offset);
DB_BIND_INT(db, "online_offset", bm->online_offset);
DB_BIND_INT(db, "already_played", bm->already_played);
DB_BIND_INT(db, "last_played", bm->last_played);
DB_BIND_INT(db, "ignore_hitsound", bm->ignore_hitsound);
DB_BIND_INT(db, "ignore_skin", bm->ignore_skin);
DB_BIND_INT(db, "disable_sb", bm->disable_sb);
DB_BIND_INT(db, "disable_video", bm->disable_video);
DB_BIND_INT(db, "visual_override", bm->visual_override);
DB_BIND_INT(db, "mania_scroll_speed", bm->mania_scroll_speed);
return osux_database_exec_prepared_query(&db->base, NULL);
}