Этот модуль был написан мною в рамках одного проекта. Немного упрощает код запросов к базам данных SQLite 3.

Код:

dh.h

#ifndef _DB_H_
#define _DB_H_
/**************************************************************************/

#ifdef __cplusplus
extern "C" {
#endif

#define DB_QUERY_DELAY 3
#define DB_FIRST_ID 1

/**************************************************************************/

typedef sqlite3 DB_CONNECTION;

/**************************************************************************/

typedef enum {
DB_INT64 = SQLITE_INTEGER,
DB_DOUBLE = SQLITE_FLOAT,
DB_BLOB = SQLITE_BLOB,
DB_NULL = SQLITE_NULL,
DB_TEXT = SQLITE3_TEXT
} DB_VALUE_TYPE;

typedef struct {
int len;
unsigned char *text;
} DB_TXT;

typedef struct {
int sz;
void *blob;
} DB_BLB;

typedef struct {
DB_VALUE_TYPE type;
union {
long long as_int64;
double as_double;
DB_TXT as_text;
DB_BLB as_blob;
void *as_null;
} value;
} DB_VALUE;

struct _db_result_row {
struct _db_result_row *_next;
long long col_cnt;
DB_VALUE *values;
};
typedef struct _db_result_row DB_RESULT_ROW;

typedef struct {
long long row_cnt;
DB_RESULT_ROW *first_row;
DB_RESULT_ROW *last_row;
} DB_RESULT;

#define LAST_ROW_ID(db) sqlite3_last_insert_rowid((db))

#define DB_RESULT_INIT(res) {(res)->row_cnt = 0; (res)->first_row = (res)->last_row = NULL;}
#define DB_ROW_INIT(row) {(row)->_next = NULL; (row)->col_cnt = 0; (row)->values = NULL;}

#define DB_RESULT_ROW_COUNT(res) ((res).row_cnt)
#define DB_RESULT_ROW_FIRST(res) ((res).first_row)
#define DB_RESULT_ROW_NEXT(row) ((row)->_next)
#define DB_RESULT_COL_TYPE(row, n) ((row)->values[(n)].type)
#define DB_RESULT_AS_INT64(row, n) ((row)->values[(n)].value.as_int64)
#define DB_RESULT_AS_DOUBLE(row, n) ((row)->values[(n)].value.as_double)
#define DB_RESULT_AS_TEXT(row, n) ((row)->values[(n)].value.as_text.text)
#define DB_RESULT_TEXT_LEN(row, n) ((row)->values[(n)].value.as_text.len)
#define DB_RESULT_AS_BLOB(row, n) ((row)->values[(n)].value.as_blob.blob)
#define DB_RESULT_BLOB_SIZE(row, n) ((row)->values[(n)].value.as_blob.sz)

typedef int (*DB_ON_ROW_FUNC)(DB_RESULT_ROW *row, void *param);

/**************************************************************************/

int db_init(const wchar_t *db_file);
int db_open(DB_CONNECTION **db);
int db_close(DB_CONNECTION *db);

int db_query(DB_CONNECTION *db,
DB_RESULT *res,
const char *query,
const int len,
const DB_VALUE *binds,
const int b_cnt);
int db_query_ex(DB_CONNECTION *db,
DB_ON_ROW_FUNC on_row,
void *param,
const char *query,
const int len,
const DB_VALUE *binds,
const int b_cnt);
void db_row_free(DB_RESULT_ROW *row);
void db_result_free(DB_RESULT *db_res);

#ifdef __cplusplus
}
#endif

/**************************************************************************/
#endif /* _DB_H_ */


db.c

/*
Copyright 2005-2008 Vsevolod Yevgiyenko
All Rights Reserved
E-mail: inform@cipherwall.com
*/

/**************************************************************************/

#include <stdio.h>
#include <windows.h>
#include "sqlite3.h"
#include "db.h"

/**************************************************************************/

static wchar_t _db_file[MAX_PATH + 1];
static int _inited = 0;

/**************************************************************************/

static __inline DB_RESULT_ROW *_row_add(DB_RESULT *db_res)
{
register DB_RESULT_ROW *new_row = NULL;

if ((new_row = malloc(sizeof(DB_RESULT_ROW))) == NULL) {
return NULL;
}

DB_ROW_INIT(new_row);

if (db_res->row_cnt == 0) {
db_res->first_row = db_res->last_row = new_row;
} else {
db_res->last_row->_next = new_row;
db_res->last_row = new_row;
}

return new_row;
}

static __inline int _fill_current_row(DB_RESULT_ROW *row, sqlite3_stmt *stmt)
{
register int i = 0;

if ((row->col_cnt = sqlite3_data_count(stmt)) == 0) {
return 1;
}

if ((row->values = malloc(sizeof(DB_VALUE) * row->col_cnt)) == NULL) {
return 0;
}

for (; i < row->col_cnt; i++) {
switch (row->values[i].type = sqlite3_column_type(stmt, i)) {
case SQLITE_INTEGER:
row->values[i].value.as_int64 = sqlite3_column_int64(stmt, i);
break;
case SQLITE_FLOAT:
row->values[i].value.as_double = sqlite3_column_double(stmt, i);
break;
case SQLITE_BLOB:
if ((row->values[i].value.as_blob.sz = sqlite3_column_bytes(stmt, i)) > 0) {
if ((row->values[i].value.as_blob.blob = malloc(row->values[i].value.as_blob.sz)) == NULL) {
return 0;
}
memcpy(row->values[i].value.as_blob.blob, sqlite3_column_blob(stmt, i), row->values[i].value.as_blob.sz);
}
break;
case SQLITE3_TEXT:
if ((row->values[i].value.as_text.len = sqlite3_column_bytes(stmt, i)) > 0) {
if ((row->values[i].value.as_text.text = malloc((sizeof(unsigned char) * row->values[i].value.as_text.len) + sizeof(unsigned char))) == NULL) {
return 0;
}
if (strcpy(row->values[i].value.as_text.text, sqlite3_column_text(stmt, i)) == NULL) {
return 0;
}
}
break;
case SQLITE_NULL:
row->values[i].value.as_null = NULL;
break;
default:
return 0;
break;
}
}

return 1;
}

/**************************************************************************/

int db_init(const wchar_t *db_file)
{
if ( ! _inited) {
if (wcsncpy(_db_file, db_file, MAX_PATH + 1) == NULL) {
return 0;
}
if (sqlite3_enable_shared_cache(1) != SQLITE_OK) {
return 0;
}
_inited = 1;
}

return 1;
}

int db_open(DB_CONNECTION **db)
{
if (_inited) {
if (sqlite3_open16(_db_file, db) != SQLITE_OK) {
sqlite3_close(*db);
return 0;
}
}

return 1;
}

int db_close(DB_CONNECTION *db)
{
if (db != NULL) {
sqlite3_interrupt(db);
if (sqlite3_close(db) != SQLITE_OK) {
return 0;
}
}

return 1;
}

int db_final(void)
{
_inited = 0;
}

int db_query(DB_CONNECTION *db,
DB_RESULT *res,
const char *query,
const int len,
const DB_VALUE *binds,
const int b_cnt)
{
sqlite3_stmt *stmt;
DB_RESULT_ROW *row;
register int trying = 1;
register int i = 0, sq_res;
int err = 1;

if (res != NULL) {
DB_RESULT_INIT(res);
}

if (sqlite3_prepare_v2(db, query, len, &stmt, NULL) != SQLITE_OK) {
sqlite3_finalize(stmt);
return 0;
}

for (; i < b_cnt; i++) {
switch (binds[i].type) {
case SQLITE_INTEGER:
sq_res = sqlite3_bind_int64(stmt, i+1, binds[i].value.as_int64);
break;
case SQLITE_FLOAT:
sq_res = sqlite3_bind_double(stmt, i+1, binds[i].value.as_double);
break;
case SQLITE_BLOB:
sq_res = sqlite3_bind_blob(stmt, i+1, binds[i].value.as_blob.blob, binds[i].value.as_blob.sz, SQLITE_STATIC);
break;
case SQLITE3_TEXT:
sq_res = sqlite3_bind_text(stmt, i+1, binds[i].value.as_text.text, binds[i].value.as_text.len, SQLITE_STATIC);
break;
case SQLITE_NULL:
sq_res = sqlite3_bind_null(stmt, i+1);
break;
default:
sqlite3_finalize(stmt);
return 0;
break;
}
}
if ((b_cnt > 0) && (sq_res != SQLITE_OK)) {
sqlite3_finalize(stmt);
return 0;
}

while (trying) {
switch (sqlite3_step(stmt)) {
case SQLITE_ROW:
if (res != NULL) {
if ((row = _row_add(res)) == NULL) {
sqlite3_finalize(stmt);
return 0;
}
if ((err = _fill_current_row(row, stmt)) != 1) {
sqlite3_finalize(stmt);
return err;
}
++(res->row_cnt);
}
break;
case SQLITE_BUSY:
Sleep(DB_QUERY_DELAY);
break;
case SQLITE_DONE:
trying = 0;
break;
default:
sqlite3_finalize(stmt);
return 0;
break;
}
}
if (sqlite3_finalize(stmt) != SQLITE_OK) {
return 0;
}

return 1;
}

void db_row_free(DB_RESULT_ROW *row)
{
register int i = 0;

for (; i < row->col_cnt; i++) {
if (row->values[i].type == DB_BLOB) {
if (row->values[i].value.as_blob.sz > 0) {
free(row->values[i].value.as_blob.blob);
}
}
if (row->values[i].type == DB_TEXT) {
if (row->values[i].value.as_text.len > 0) {
free(row->values[i].value.as_text.text);
}
}
}
if (row->col_cnt > 0) {
free(row->values);
}
}

int db_query_ex(DB_CONNECTION *db,
DB_ON_ROW_FUNC on_row,
void *param,
const char *query,
const int len,
const DB_VALUE *binds,
const int b_cnt)
{
sqlite3_stmt *stmt;
DB_RESULT_ROW c_row;
register int trying = 1;
register int i = 0, sq_res;
int err = 1;

if (sqlite3_prepare_v2(db, query, len, &stmt, NULL) != SQLITE_OK) {
sqlite3_finalize(stmt);
return 0;
}

for (; i < b_cnt; i++) {
switch (binds[i].type) {
case SQLITE_INTEGER:
sq_res = sqlite3_bind_int64(stmt, i+1, binds[i].value.as_int64);
break;
case SQLITE_FLOAT:
sq_res = sqlite3_bind_double(stmt, i+1, binds[i].value.as_double);
break;
case SQLITE_BLOB:
sq_res = sqlite3_bind_blob(stmt, i+1, binds[i].value.as_blob.blob, binds[i].value.as_blob.sz, SQLITE_STATIC);
break;
case SQLITE3_TEXT:
sq_res = sqlite3_bind_text(stmt, i+1, binds[i].value.as_text.text, binds[i].value.as_text.len, SQLITE_STATIC);
break;
case SQLITE_NULL:
sq_res = sqlite3_bind_null(stmt, i+1);
break;
default:
sqlite3_finalize(stmt);
return 0;
break;
}
}
if ((b_cnt > 0) && (sq_res != SQLITE_OK)) {
sqlite3_finalize(stmt);
return 0;
}

while (trying) {
switch (sqlite3_step(stmt)) {
case SQLITE_ROW:
if (on_row != NULL) {
if ((err = _fill_current_row(&c_row, stmt)) != 1) {
sqlite3_finalize(stmt);
return err;
}
if ((err = on_row(&c_row, param)) != 1) {
sqlite3_finalize(stmt);
return err;
}
db_row_free(&c_row);
}
break;
case SQLITE_BUSY:
Sleep(DB_QUERY_DELAY);
break;
case SQLITE_DONE:
trying = 0;
break;
default:
sqlite3_finalize(stmt);
return 0;
break;
}
}
if (sqlite3_finalize(stmt) != SQLITE_OK) {
return 0;
}

return 1;
}

void db_result_free(DB_RESULT *db_res)
{
register DB_RESULT_ROW *c_row = db_res->first_row, *n_row;
register int i = 0;

while ((db_res->row_cnt--) && (c_row != NULL)) {
for (; i < c_row->col_cnt; i++) {
if (c_row->values[i].type == DB_BLOB) {
if (c_row->values[i].value.as_blob.sz > 0) {
free(c_row->values[i].value.as_blob.blob);
}
}
if (c_row->values[i].type == DB_TEXT) {
if (c_row->values[i].value.as_text.len > 0) {
free(c_row->values[i].value.as_text.text);
}
}
}
n_row = c_row->_next;
if (c_row->col_cnt > 0) {
free(c_row->values);
}
free(c_row);
c_row = n_row;
}
}


Пример использования модуля:

void ex1(DB_CONNECTION *db, int x, int y)
{
static const char query[] = "SELECT COUNT(string) FROM table WHERE x=? AND y=?";
DB_RESULT res;
DB_RESULT_ROW *row;
DB_VALUE binds[2] = {{DB_INT64, x}, {DB_INT64, y}};
long long value;

if ( ! db_query(db, &res, query, strlen(query), binds, 2)) {
db_result_free(&res);
return;
}
if (DB_RESULT_ROW_COUNT(res) != 1) {
db_result_free(&res);
return;
}

row = DB_RESULT_ROW_FIRST(res);
value = DB_RESULT_AS_INT64(row, 0);

db_result_free(&res);
}