Introduction
Thought I would share some tricks I use for easy data CRUD’s in C. I’m using Sqlite here, but could apply to any database.
Not saying its the best approach at all (nor ryjen/db), this is just how it evolved for me.
Mapping
The core of my data access is the FieldMap structure, which determines how to save each field to the database.
/*
* this is the magical table used to communicate between memory and the database
*/
typedef struct FieldMap {
const char *name; /* name of the field */
void *value; /* a pointer to the value of the field */
int type; /* type of value */
const void *arg1; /* additional argument */
const void *arg2; /* additional argument */
int flags; /* usage flags */
} FieldMap;
/* callback for custom field types */
typedef int (*CustomField) (sqlite3_stmt *, int column, FieldMap *field);
typedef void (*DbCallback) (sqlite3_stmt *);
Basically you would have to:
Example 1 - Saving
This high level example shows building a quick table for each field we want to save in the database. In this case, an account object.
/* The account table name */
#define ACCOUNT_TABLE "account"
/* a structure representing the account fields */
FieldMap account_values[] = {
{"login", &acc->login, SQL_TEXT},
{"email", &acc->email, SQL_TEXT},
{"password", &acc->password, SQL_TEXT},
{"timezone", &acc->timezone, SQL_INT},
{0}
};
/* saves the fields to the database */
acc->id = db_save(account_values, ACCOUNT_TABLE, acc->id);
sqlite3_int64 db_save(FieldMap *table, const char *tableName, sqlite3_int64 *id)
{
if (id == 0) {
if (db_insert_query(table, tableName) != SQL_OK)
error("could not insert");
return db_last_insert_rowid();
} else {
if (db_update_query(table, tableName, *id) != SQL_OK)
error("could not update",);
return id;
}
}
Example 2 - Deeper into Saving
The first part of saving is inserting. The steps are to build a query, bind the values, and execute. In the case the values from the field map table for an account.
int sql_insert_query(FieldMap *table, const char *tablename)
{
sqlite3_stmt *stmt;
char buf[1024] = { 0 };
char columns[1024] = { 0 };
char params[1024] = { 0 };
int len;
/* create a csv list of columns and params */
db_build_columns(table, columns);
db_build_params(table, params);
len = sprintf(buf, "INSERT INTO %s (%s) VALUES(%s)", tablename, columns,
params);
if (sqlite3_query(buf, len, &stmt) != SQL_OK) {
return sql_finalize(stmt);
}
if (sqlite3_bind_values(stmt, table) != SQL_OK) {
return sql_finalize(stmt);
}
/* execute */
sqlite3_step(stmt);
return sqlite3_finalize(stmt);
}
Example 3 - Binding
Have to have a way to bind the FieldMap to the queries, so the following implementations take care of that.
int db_bind_values(sql_stmt *stmt, FieldMap *table)
{
for (int i = 0; table[i].name != 0; i++) {
int err = sql_bind_table_value(stmt, i+1, &table[i]);
if (err != SQL_OK)
return err;
}
return SQL_OK;
}
/*
* binds a single FieldMap value to a query
*/
int db_bind_table_value(sqlite3_stmt *stmt, int column, FieldMap *field)
{
if (field->value == 0) {
return sqlite3_bind_null(stmt, column);
}
switch (field->type) {
case SQL_INT:
return sqlite3_bind_int(stmt, column, *((int*) field->value));
case SQL_TEXT:
{
const char *str = *((const char *) field->value);
return sqlite3_bind_text(stmt, column, str, strlen(str), 0);
}
case SQL_DOUBLE:
return sqlite3_bind_double(stmt, column, *((double*) field->value));
case SQL_FLOAT:
return sqlite3_bind_float(stmt, column, *((float*) field->value));
case SQL_CUSTOM:
{
custom_sql func = (custom_sql) (field->arg1);
assert(func != 0);
return (*func) (stmt, column, field);
}
default:
error("unknown save type for field %s", field->name);
return SQL_NONTYPE;
}
}
Example 4 - Loading
This high level example shows loading an account by an id using the same table.
The the query results are assigned to the pointers in the table.
/* loads one account by id */
res = db_load_by_id(account_values, ACCOUNT_TABLE, acc->id);
int db_load_by_id(FieldMap *table, const char *tablename, sql_int64 id)
{
char buf[1024] = {0};
sprintf(buf, "where %s='%lld'", tablenameId(tablename), id);
if(db_select_query(0, table, tablename, 0, buf) != SQL_OK) {
error("could not load");
return 0;
}
return 1;
}
Load multiple accounts by using a callback method.
res = db_load_all(ACCOUNT_TABLE, load_account_callback);
int db_load_all(const char *tableName, DbCallback callback)
{
if (db_select_query(0, tableName, callback, 0) != SQL_OK) {
error("could not load");
return 0;
}
return 1;
}
Example 5 - Querying
Querying is not too complicated. Its the typical process of create and execute looping the results. For each row it will:
- loading columns into the field map
- issues the callback if provided
int sql_select_query(FieldMap *table, const char *tablename, DbCallback callback, const char *constraints)
{
sqlite3_stmt *stmt;
char buf[1024] = { 0 };
char columns[1024] = { 0 };
int column, len;
/* creates a csv list of columns */
db_build_columns(table, columns);
len = sprintf(buf, "SELECT %s,%s FROM %s %s",
tablenameId(tablename), columns,
tablename,
constraints ? constraints : "");
if (sqlite3_query(buf, len, &stmt) != SQL_OK) {
return sqlite3_finalize(stmt);
}
for (err = sqlite3_step(stmt); err != SQL_DONE; err = sqlite3_step(stmt)) {
/* check for a table to load */
for (column = 0; table && table[column].name != 0; column++) {
/* pass a pointer to the current position in the table */
db_load_column(stmt, column+1, &table[column]);
}
/* check for a callback */
if(callback) {
callback(stmt);
}
}
return sqlite3_finalize(stmt);
}
Example 6 - Deeper into Querying
You saw that querying will load each column in a row. This function shows how that is done.
void db_load_column(sqlite3_stmt *stmt, int column, FieldMap *field)
{
switch (field->type) {
case SQL_INT:
field_value(int, field) = sqlite3_column_int(stmt, column);
break;
case SQL_TEXT:
field_value(const char *, field) = str_dup(sqlite3_column_str(stmt, column));
break;
case SQL_FLOAT:
field_value(float, field) = sqlite3_column_float(stmt, column);
break;
case SQL_DOUBLE:
field_value(double, field) = sqlite3_column_double(stmt, column);
break;
case SQL_CUSTOM:
{
custom_sql *func = (custom_sql *) field->arg1;
assert(func != 0);
(*func) (stmt, column, field);
break;
}
}
}
The field_value macro turns the field map into something we can assign a result to.
Conclusion
C is already a very verbose language, but defining and using a table structure for database operations saves a lot of boiler plate code.