/******************************************************************************
****
**** Database package for the MudOS driver
****
**** History:
**** Sometime:
**** Descartes@Nightmare created and added mSQL support
****
**** Feb 1999:
**** Andrew@Nanvaent restructured to add MySQL support and a
**** framework for other databases to be added.
****
**** Jul 2000:
**** Andrew@Nanvaent's work included in the MudOS proper
****
**** Apr 2006:
**** Ajandurah@Demonslair added SQLite v3 support, defining
**** USE_SQLITE3 in local_options will enable it.
****
**** Feb 2009:
**** Ajandurah@Demonslair added SQLite v2 support, defining
**** USE_SQLITE2 in local_options will enable it.
****
**** Notes:
**** . This package has been restructured so that it can be compiled into
**** a driver without any database types defined so that you can write
**** stuff without necessarily having the database.
****
**** . No database type has been added that supports commit or rollback,
**** so these functions have not been fully implemented, particularly
**** with regard to error handling.
****
**** . Support for multiple database types is present, if obscure. When
**** you have multiple types you should have DEFAULT_DB defined to be
**** the default one, and USE_MYSQL/USE_MSQL should be defined to be
**** numbers in the local_options file or equivalent, e.g.:
**** #define USE_MSQL 1
**** #define USE_MYSQL 2
**** #define DEFAULT_DB USE_MSQL
****
**** The value that you defined it to will be that expected when you
**** make a call to db_connect( ... ) as the fourth argument. Without
**** the fourth argument, the value used will be that for DEFAULT_DB.
****
**** . Adding another database type should involve:
**** + picking your own define name
**** + editing db.h and adding an appropriate member to the dbconn_t
**** union
**** + adding a dbdefn_t definition for it in this file
**** + playing around with the code for deciding between databases in
**** f_db_connect()
**** + writing all the required interface functions as you've defined
**** for the dbdefn_t structure. Minimum requirements would be
**** connect, close, fetch and execute and cleanup if you need to
**** cleanup memory allocated between searches.
****
**** TODO:
**** . Decent Error Message reporting
**** . Function for showing the current connections (incomplete)
**** . Standardise on return values (only db_exec is nonstandard)
**** . Documentation
**** . Add more databases
****
******************************************************************************/
#include "../std.h"
#include "../md.h"
#include "../master.h"
#include "../lpc_incl.h"
#include "../mapping.h"
#include "../comm.h"
#include "../file_incl.h"
#include "../file.h"
#include "../object.h"
#include "../eoperators.h"
#include "../backend.h"
#include "db.h"
static int dbConnAlloc, dbConnUsed;
static db_t *dbConnList;
static db_t * find_db_conn (int);
static int create_db_conn (void);
static void free_db_conn (db_t *);
#ifdef USE_MSQL
static int msql_connect (dbconn_t *, const char *, const char *, const char *, const char *);
static int msql_close (dbconn_t *);
static int msql_execute (dbconn_t *, const char *);
static array_t *msql_fetch (dbconn_t *, int);
static void msql_cleanup (dbconn_t *);
static char * msql_errormsg (dbconn_t *);
static db_defn_t msql = {
"mSQL", msql_connect, msql_close, msql_execute, msql_fetch, NULL, NULL, msql_cleanup, NULL, msql_errormsg
};
#endif
#ifdef USE_MYSQL
static int MySQL_connect (dbconn_t *, const char *, const char *, const char *, const char *);
static int MySQL_close (dbconn_t *);
static int MySQL_execute (dbconn_t *, const char *);
static array_t *MySQL_fetch (dbconn_t *, int);
static void MySQL_cleanup (dbconn_t *);
static char * MySQL_errormsg (dbconn_t *);
static db_defn_t mysql = {
"MySQL", MySQL_connect, MySQL_close, MySQL_execute, MySQL_fetch, NULL, NULL, MySQL_cleanup, NULL, MySQL_errormsg
};
#endif
#ifdef USE_SQLITE2
static int SQLite2_connect (dbconn_t *, const char *, const char *, const char *, const char *);
static int SQLite2_close (dbconn_t *);
static int SQLite2_execute (dbconn_t *, const char *);
static array_t *SQLite2_fetch (dbconn_t *, int);
static void SQLite2_cleanup (dbconn_t *);
static char * SQLite2_errormsg (dbconn_t *);
static db_defn_t SQLite2 = {
"SQLite2", SQLite2_connect, SQLite2_close, SQLite2_execute, SQLite2_fetch, NULL, NULL, SQLite2_cleanup, NULL, SQLite2_errormsg
};
#endif
#ifdef USE_SQLITE3
static int SQLite3_connect (dbconn_t *, const char *, const char *, const char *, const char *);
static int SQLite3_close (dbconn_t *);
static int SQLite3_execute (dbconn_t *, const char *);
static array_t *SQLite3_fetch (dbconn_t *, int);
static void SQLite3_cleanup (dbconn_t *);
static char * SQLite3_errormsg (dbconn_t *);
static db_defn_t SQLite3 = {
"SQLite3", SQLite3_connect, SQLite3_close, SQLite3_execute, SQLite3_fetch, NULL, NULL, SQLite3_cleanup, NULL, SQLite3_errormsg
};
#endif
static db_defn_t no_db = {
"None", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
};
/* valid_database
*
* Calls APPLY_VALID_DATABASE in the master object to provide some
* security on which objects can tweak your database (we don't want
* people doing "DELETE * FROM *" or equivalent for us)
*/
static svalue_t *valid_database (const char * action, array_t * info)
{
svalue_t *ret;
/*
* Call valid_database(object ob, string action, mixed *info)
*
* Return: string - password for access
* int - 1 for no password, accept, 0 deny
*/
push_object(current_object);
push_constant_string(action);
push_refed_array(info);
ret = apply_master_ob(APPLY_VALID_DATABASE, 3);
if (ret && (ret == (svalue_t *)-1 || (ret->type == T_STRING || (ret->type == T_NUMBER && ret->u.number))))
return ret;
error("Database security violation attempted\n");
}
/* int db_close(int handle);
*
* Closes the connection to the database represented by the named handle
*
* Returns 1 on success, 0 on failure
*/
#ifdef F_DB_CLOSE
void f_db_close (void)
{
int ret = 0;
db_t *db;
valid_database("close", &the_null_array);
db = find_db_conn(sp->u.number);
if (!db) {
error("Attempt to close an invalid database handle\n");
}
/* Cleanup any memory structures left around */
if (db->type->cleanup) {
db->type->cleanup(&(db->c));
}
if (db->type->close) {
ret = db->type->close(&(db->c));
}
/* Remove the entry from the linked list */
free_db_conn(db);
sp->u.number = ret;
}
#endif
/* int db_commit(int handle);
*
* Commits the last set of transactions to the database
* NOTE: MSQL does not have transaction logic, so since
* MSQL is the only thing supported now, this does nothing
* I have put it in, however, so people can write properly
* portable LPC code
*
* Returns 1 on success, 0 on failure
*/
#ifdef F_DB_COMMIT
void f_db_commit (void)
{
int ret = 0;
db_t *db;
valid_database("commit", &the_null_array);
db = find_db_conn(sp->u.number);
if (!db) {
error("Attempt to commit an invalid database handle\n");
}
if (db->type->commit) {
ret = db->type->commit(&(db->c));
}
sp->u.number = ret;
}
#endif
/* int db_connect(string host, string database, string user, int type)
*
* Creates a database connection to the database named by the
* second argument found on the host named by the first argument.
* Note that this means you can connect to database servers running on
* machines other than the one on which the mud is running. It will
* connect based on settings established at compile time for the
* user id and password (if required).
*
* Returns a new database handle.
*/
#ifdef F_DB_CONNECT
void f_db_connect (void)
{
char *errormsg = 0;
const char *user = "", *database, *host;
db_t *db;
array_t *info;
svalue_t *mret;
int handle, ret = 0, args = 0, type;
#ifdef DEFAULT_DB
type = DEFAULT_DB;
#else
type = 0;
#endif
switch (st_num_arg) {
case 4: type = (sp - (args++))->u.number;
case 3: user = (sp - (args++))->u.string;
case 2: database = (sp - (args++))->u.string;
case 1: host = (sp - (args++))->u.string;
}
info = allocate_empty_array(3);
info->item[0].type = info->item[1].type = info->item[2].type = T_STRING;
info->item[0].subtype = info->item[1].subtype = info->item[2].subtype = STRING_MALLOC;
info->item[0].u.string = string_copy(database, "f_db_connect:1");
if (*host)
info->item[1].u.string = string_copy(host, "f_db_connect:2");
else
info->item[1] = const0;
info->item[2].u.string = string_copy(user, "f_db_connect:3");
mret = valid_database("connect", info);
handle = create_db_conn();
if (!handle) {
pop_n_elems(args);
push_number(0);
return;
}
db = find_db_conn(handle);
switch (type) {
default:
/* fallthrough */
#ifdef USE_MSQL
#if USE_MSQL - 0
case USE_MSQL:
#endif
db->type = &msql;
break;
#endif
#ifdef USE_MYSQL
#if USE_MYSQL - 0
case USE_MYSQL:
#endif
db->type = &mysql;
break;
#endif
#ifdef USE_SQLITE2
#if USE_SQLITE2 - 0
case USE_SQLITE2:
#endif
db->type = &SQLite2;
break;
#endif
#ifdef USE_SQLITE3
#if USE_SQLITE3 - 0
case USE_SQLITE3:
#endif
db->type = &SQLite3;
break;
#endif
}
if (db->type->connect) {
ret = db->type->connect(&(db->c), host, database, user,
(mret != (svalue_t *)-1 && mret->type == T_STRING ? mret->u.string : 0));
}
pop_n_elems(args);
if (!ret) {
if (db->type->error) {
errormsg = db->type->error(&(db->c));
push_malloced_string(errormsg);
} else {
push_number(0);
}
free_db_conn(db);
} else {
push_number(handle);
}
}
#endif
/* mixed db_exec(int handle, string sql)
*
* Executes the SQL statement passed for the named connection handle.
* If data needs to be retrieved from this execution, it should be done
* through db_fetch() after making the call to db_exec()
*
* Returns number of rows in result set on success, an error string on failure
* NOTE: the number of rows on INSERT, UPDATE, and DELETE statements will
* be zero since there is no result set.
*/
#ifdef F_DB_EXEC
void f_db_exec (void)
{
int ret = 0;
db_t *db;
array_t *info;
info = allocate_empty_array(1);
info->item[0].type = T_STRING;
info->item[0].subtype = STRING_MALLOC;
info->item[0].u.string = string_copy(sp->u.string, "f_db_exec");
valid_database("exec", info);
db = find_db_conn((sp-1)->u.number);
if (!db) {
error("Attempt to exec on an invalid database handle\n");
}
if (db->type->cleanup) {
db->type->cleanup(&(db->c));
}
if (db->type->execute) {
ret = db->type->execute(&(db->c), sp->u.string);
}
pop_stack();
if (ret == -1) {
if (db->type->error) {
char *errormsg;
errormsg = db->type->error(&(db->c));
put_malloced_string(errormsg);
} else {
put_constant_string("Unknown error");
}
} else {
sp->u.number = ret;
}
}
#endif
/* array db_fetch(int db_handle, int row);
*
* Returns the result set from the last database transaction
* performed through db_exec() on the db handle in question for the row
* named. For example, db_exec(10, "SELECT player_name from t_player") might
* have returned two rows. Typical code to extract that data might be:
* string *res;
* mixed rows;
* int dbconn, i;
*
* dbconn = db_connect("nightmare.imaginary.com", "db_mud");
* if( dbconn < 1 ) return 0;
* rows = db_exec(dbconn, "SELECT player_name from t_player");
* if( !rows ) write("No rows returned.");
* else if( stringp(rows) ) write(rows);
* else for(i=1; i<=rows; i++) {
* res = db_fetch(dbconn, i);
* write(res[0]);
* }
* db_close(dbconn);
* return 1;
*
* Returns an array of columns from the named row on success.
*/
#ifdef F_DB_FETCH
void f_db_fetch (void)
{
db_t *db;
array_t *ret;
valid_database("fetch", &the_null_array);
db = find_db_conn((sp-1)->u.number);
if (!db) {
error("Attempt to fetch from an invalid database handle\n");
}
if (db->type->fetch) {
ret = db->type->fetch(&(db->c), sp->u.number);
} else {
ret = &the_null_array;
}
pop_stack();
if (!ret) {
if (db->type->error) {
char *errormsg;
errormsg = db->type->error(&(db->c));
put_malloced_string(errormsg);
} else {
sp->u.number = 0;
}
} else {
put_array(ret);
}
}
#endif
/* int db_rollback(int handle)
*
* Rollsback all db_exec() calls back to the last db_commit() call for the
* named connection handle.
* NOTE: MSQL does not support rollbacks
*
* Returns 1 on success, 0 on failure
*/
#ifdef F_DB_ROLLBACK
void f_db_rollback (void)
{
int ret = 0;
db_t *db;
valid_database("rollback", &the_null_array);
db = find_db_conn(sp->u.number);
if (!db) {
error("Attempt to rollback an invalid database handle\n");
}
if (db->type->rollback) {
ret = db->type->rollback(&(db->c));
}
if (ret > 0) {
if (db->type->cleanup) {
db->type->cleanup(&(db->c));
}
}
sp->u.number = ret;
}
#endif
/* string db_status()
*
* Returns a string describing the database package's current status
*/
#ifdef F_DB_STATUS
void f_db_status (void)
{
int i;
outbuffer_t out;
outbuf_zero(&out);
for (i = 0; i < dbConnAlloc; i++) {
if (dbConnList[i].flags & DB_FLAG_EMPTY) {
continue;
}
outbuf_addv(&out, "Handle: %d (%s)\n", i + 1, dbConnList[i].type->name);
if (dbConnList[i].type->status != NULL) {
dbConnList[i].type->status(&(dbConnList[i].c), &out);
}
}
outbuf_push(&out);
}
#endif
void db_cleanup (void)
{
int i;
for (i = 0; i < dbConnAlloc; i++) {
if (!(dbConnList[i].flags & DB_FLAG_EMPTY)) {
if (dbConnList[i].type->cleanup) {
dbConnList[i].type->cleanup(&(dbConnList[i].c));
}
if (dbConnList[i].type->close) {
dbConnList[i].type->close(&(dbConnList[i].c));
}
dbConnList[i].flags = DB_FLAG_EMPTY;
dbConnUsed--;
}
}
}
int create_db_conn (void)
{
int i;
/* allocate more slots if we need them */
if (dbConnAlloc == dbConnUsed) {
i = dbConnAlloc;
dbConnAlloc += 10;
if (!dbConnList) {
dbConnList = CALLOCATE(dbConnAlloc, db_t, TAG_DB, "create_db_conn");
} else {
dbConnList = RESIZE(dbConnList, dbConnAlloc, db_t, TAG_DB, "create_db_conn");
}
while (i < dbConnAlloc) {
dbConnList[i++].flags = DB_FLAG_EMPTY;
}
}
for (i = 0; i < dbConnAlloc; i++) {
if (dbConnList[i].flags & DB_FLAG_EMPTY) {
dbConnList[i].flags = 0;
dbConnList[i].type = &no_db;
dbConnUsed++;
return i + 1;
}
}
fatal("dbConnAlloc != dbConnUsed, but no empty slots");
}
db_t *find_db_conn (int handle)
{
if (handle < 1 || handle > dbConnAlloc || dbConnList[handle - 1].flags & DB_FLAG_EMPTY)
return 0;
return &(dbConnList[handle - 1]);
}
void free_db_conn (db_t * db)
{
DEBUG_CHECK(db->flags & DB_FLAG_EMPTY, "Freeing DB connection that is already freed\n");
DEBUG_CHECK(!dbConnUsed, "Freeing DB connection when dbConnUsed == 0\n");
dbConnUsed--;
db->flags |= DB_FLAG_EMPTY;
}
/*
* MySQL support
*/
#ifdef USE_MYSQL
static void MySQL_cleanup (dbconn_t * c)
{
*(c->mysql.errormsg) = 0;
if (c->mysql.results) {
mysql_free_result(c->mysql.results);
c->mysql.results = 0;
}
}
static char *MySQL_errormsg (dbconn_t * c)
{
if (*(c->mysql.errormsg)) {
return string_copy(c->mysql.errormsg, "MySQL_errormsg:1");
}
return string_copy(mysql_error(c->mysql.handle), "MySQL_errormsg:2");
}
static int MySQL_close (dbconn_t * c)
{
mysql_close(c->mysql.handle);
FREE(c->mysql.handle);
c->mysql.handle = 0;
return 1;
}
static int MySQL_execute (dbconn_t * c, const char * s)
{
if (!mysql_query(c->mysql.handle, s)) {
c->mysql.results = mysql_store_result(c->mysql.handle);
if (c->mysql.results) {
return mysql_num_rows(c->mysql.results);
}
/* Queries returning no input can return a NULL handle */
if (!mysql_errno(c->mysql.handle)) {
return 0;
}
}
return -1;
}
static array_t *MySQL_fetch (dbconn_t * c, int row)
{
array_t *v;
MYSQL_ROW target_row;
unsigned int i, num_fields;
if (!c->mysql.results) {
return &the_null_array;
}
if (row < 0 || row > mysql_num_rows(c->mysql.results)) {
return &the_null_array;
}
num_fields = mysql_num_fields(c->mysql.results);
if (num_fields < 1) {
return &the_null_array;
}
if(row>0){
mysql_data_seek(c->mysql.results, row - 1);
target_row = mysql_fetch_row(c->mysql.results);
if (!target_row) {
return &the_null_array;
}
}
v = allocate_empty_array(num_fields);
for (i = 0; i < num_fields; i++) {
MYSQL_FIELD *field;
field = mysql_fetch_field(c->mysql.results);
if (row == 0) {
if (field == (MYSQL_FIELD *)NULL) {
v->item[i] = const0u;
} else {
v->item[i].type = T_STRING;
v->item[i].subtype = STRING_MALLOC;
v->item[i].u.string = string_copy(field->name, "f_db_fetch");
}
continue;
}
if (!field || !target_row[i]) {
v->item[i] = const0u;
} else {
switch (field->type) {
case FIELD_TYPE_TINY:
case FIELD_TYPE_SHORT:
case FIELD_TYPE_DECIMAL:
case FIELD_TYPE_LONG:
v->item[i].type = T_NUMBER;
v->item[i].u.number = atoi(target_row[i]);
break;
case FIELD_TYPE_FLOAT:
case FIELD_TYPE_DOUBLE:
v->item[i].type = T_REAL;
v->item[i].u.real = atof(target_row[i]);
break;
case FIELD_TYPE_TINY_BLOB:
case FIELD_TYPE_MEDIUM_BLOB:
case FIELD_TYPE_LONG_BLOB:
case FIELD_TYPE_BLOB:
case FIELD_TYPE_STRING:
case FIELD_TYPE_VAR_STRING:
if (field->flags & BINARY_FLAG) {
#ifndef NO_BUFFER_TYPE
v->item[i].type = T_BUFFER;
v->item[i].u.buf = allocate_buffer(field->max_length);
write_buffer(v->item[i].u.buf, 0, target_row[i], field->max_length);
#else
v->item[i] = const0u;
#endif
} else {
v->item[i].type = T_STRING;
if (target_row[i]) {
v->item[i].subtype = STRING_MALLOC;
v->item[i].u.string = string_copy(target_row[i], "MySQL_fetch");
} else {
v->item[i].subtype = STRING_CONSTANT;
v->item[i].u.string = "";
}
}
break;
default:
v->item[i] = const0u;
break;
}
}
}
mysql_field_seek(c->mysql.results, 0);
return v;
}
#ifndef MYSQL_SOCKET_ADDRESS
#define MYSQL_SOCKET_ADDRESS "/tmp/mysql.sock"
#endif
static int MySQL_connect (dbconn_t * c, const char * host, const char * database, const char * username, const char * password)
{
int ret;
MYSQL *tmp;
tmp = ALLOCATE(MYSQL, TAG_DB, "MySQL_connect");
tmp = mysql_init(tmp);
*(c->mysql.errormsg) = 0;
c->mysql.handle = mysql_real_connect(tmp, host, username, password, database, 0, MYSQL_SOCKET_ADDRESS, 0);
//c->mysql.handle = mysql_connect(tmp, host, username, password);
if (!c->mysql.handle) {
strncpy(c->mysql.errormsg, mysql_error(tmp), sizeof(c->mysql.errormsg));
c->mysql.errormsg[sizeof(c->mysql.errormsg) - 1] = 0;
FREE(tmp);
return 0;
}
ret = mysql_select_db(c->mysql.handle, database);
if (ret) {
strncpy(c->mysql.errormsg, mysql_error(c->mysql.handle), sizeof(c->mysql.errormsg));
c->mysql.errormsg[sizeof(c->mysql.errormsg) - 1] = 0;
mysql_close(c->mysql.handle);
c->mysql.handle = 0;
FREE(tmp);
return 0;
}
c->mysql.results = 0;
return 1;
}
#endif
/*
* mSQL support
*/
#ifdef USE_MSQL
static void msql_cleanup (dbconn_t * c)
{
if (c->msql.result_set) {
msqlFreeResult(c->msql.result_set);
c->msql.result_set = 0;
}
}
static int msql_close (dbconn_t * c)
{
msqlClose(c->msql.handle);
c->msql.handle = -1;
return 1;
}
static int msql_execute (dbconn_t * c, const char * s)
{
if (msqlQuery(c->msql.handle, s) != -1) {
c->msql.result_set = msqlStoreResult();
if (!c->msql.result_set) {
/* Query was an UPDATE or INSERT or DELETE */
return 0;
}
return msqlNumRows(c->msql.result_set);
}
return -1;
}
static array_t *msql_fetch (dbconn_t * c, int row)
{
int i, num_fields;
m_row this_row;
array_t *v;
if (!c->msql.result_set) {
return &the_null_array;
}
if (row < 1 || row > msqlNumRows(c->msql.result_set)) {
return &the_null_array;
}
num_fields = msqlNumFields(c->msql.result_set);
if (num_fields < 1) {
return &the_null_array;
}
msqlDataSeek(c->msql.result_set, row - 1);
this_row = msqlFetchRow(c->msql.result_set);
if (!this_row) {
return &the_null_array;
}
v = allocate_empty_array(num_fields);
for (i = 0; i < num_fields; i++) {
m_field *field;
field = msqlFetchField(c->msql.result_set);
if (!field || !this_row[i]) {
v->item[i] = const0u;
} else {
switch (field->type) {
case INT_TYPE:
case UINT_TYPE:
v->item[i].type = T_NUMBER;
v->item[i].u.number = atoi(this_row[i]);
break;
case REAL_TYPE:
case MONEY_TYPE:
v->item[i].type = T_REAL;
v->item[i].u.real = atof(this_row[i]);
break;
case CHAR_TYPE:
case TEXT_TYPE:
case DATE_TYPE:
case TIME_TYPE:
v->item[i].type = T_STRING;
v->item[i].subtype = STRING_MALLOC;
v->item[i].u.string = string_copy(this_row[i], "msql_fetch");
break;
default:
v->item[i] = const0u;
break;
}
}
}
msqlFieldSeek(c->msql.result_set, 0);
return v;
}
static int msql_connect (dbconn_t * c, char * host, char * database, char * username, char * password)
{
c->msql.handle = msqlConnect(host);
if (c->msql.handle < 1) {
return 0;
}
if (msqlSelectDB(c->msql.handle, database) == -1) {
msqlClose(c->msql.handle);
return 0;
}
c->msql.result_set = 0;
return 1;
}
static char *msql_errormsg (dbconn_t * c)
{
return string_copy(msqlErrMsg, "msql_errormsg");
}
#endif
/*
* SQLite v2 support
* ajandurah@demonslair (Mark Lyndoe)
*/
#ifdef USE_SQLITE2
static int SQLite2_connect (dbconn_t * c, const char * host, const char * database, const char * username, const char * password)
{
c->SQLite2.handle = sqlite_open(database, 0666, &c->SQLite2.errormsg);
if (!c->SQLite2.handle) {
sqlite_close(c->SQLite2.handle);
return 0;
}
c->SQLite2.nrows = 0;
c->SQLite2.ncolumns = 0;
c->SQLite2.last_row = 0;
c->SQLite2.step_res = 0;
c->SQLite2.values = NULL;
c->SQLite2.col_names = NULL;
c->SQLite2.vm = NULL;
return 1;
}
static int SQLite2_close (dbconn_t * c)
{
if (c->SQLite2.errormsg)
free(c->SQLite2.errormsg);
if (c->SQLite2.vm)
sqlite_finalize(c->SQLite2.vm, NULL);
sqlite_close(c->SQLite2.handle);
c->SQLite2.handle = 0;
c->SQLite2.errormsg = 0;
c->SQLite2.nrows = 0;
c->SQLite2.ncolumns = 0;
c->SQLite2.last_row = 0;
c->SQLite2.step_res =0;
c->SQLite2.vm = NULL;
c->SQLite2.values = NULL;
c->SQLite2.col_names = NULL;
return 1;
}
static void SQLite2_cleanup (dbconn_t * c)
{
if (c->SQLite2.errormsg) {
free(c->SQLite2.errormsg);
c->SQLite2.errormsg = 0;
}
if (c->SQLite2.vm) {
sqlite_finalize(c->SQLite2.vm, NULL);
c->SQLite2.vm = 0;
c->SQLite2.last_row = 0;
c->SQLite2.step_res = 0;
}
}
static int SQLite2_execute (dbconn_t * c, const char * s)
{
char **result;
const char *tail;
int ret;
/* Oddly enough a sqlite_get_table will execute sql that inserts and updates! */
if (sqlite_get_table(c->SQLite2.handle, s, &result, &c->SQLite2.nrows, &c->SQLite2.ncolumns, NULL) != SQLITE_OK) {
sqlite_free_table(result);
return 0;
}
else {
sqlite_free_table(result);
c->SQLite2.sql = string_copy(s, "SQLite2_execute");
c->SQLite2.last_row = 0;
c->SQLite2.step_res = 0;
return c->SQLite2.nrows;
}
return -1;
}
static array_t *SQLite2_fetch (dbconn_t * c, int row)
{
int last_row, length, i, l, r;
char *p_end;
const char *tail;
double d;
array_t *v;
if (!c->SQLite2.vm) {
/* We don't have a vm yet because the sql has not been compiled.
* This is down to db_exec using sqlite_get_table to execute the sql in the
* first instance. This is the reason we saved the sql into the SQLite
* structure, compile it now and create a vm. We return a null array only
* if the compile fails.
*/
r = sqlite_compile(c->SQLite2.handle, c->SQLite2.sql, NULL, &c->SQLite2.vm, &c->SQLite2.errormsg);
if (r != SQLITE_OK || !c->SQLite2.vm)
return &the_null_array;
}
if (c->SQLite2.step_res && c->SQLite2.step_res != SQLITE_ROW) {
return &the_null_array;
}
if (row < 0 || row > c->SQLite2.nrows) {
return &the_null_array;
}
if (c->SQLite2.ncolumns < 1) {
return &the_null_array;
}
/* If the fetch is for row 0 then we don't return a row containing data values
* instead we return the column names. This has proven quite useful in a number
* of circumstances when they are unknown ahead of the query. Unlike SQLite3 we
* have no means of obtaining them without stepping the virtual machine so we
* have no choice. We will have to check the last_row and step_rc later to make
* sure we use the values here before we step again.
*/
if (row == 0) {
c->SQLite2.step_res = sqlite_step(c->SQLite2.vm, NULL, &c->SQLite2.values, &c->SQLite2.col_names);
if (c->SQLite2.step_res == SQLITE_ROW || c->SQLite2.step_res == SQLITE_DONE) {
v = allocate_empty_array(c->SQLite2.ncolumns);
for (i = 0; i < c->SQLite2.ncolumns; i++) {
v->item[i].type = T_STRING;
v->item[i].subtype = STRING_MALLOC;
v->item[i].u.string = string_copy((char *)c->SQLite2.col_names[i], "SQLite2_fetch");
}
return v;
}
return &the_null_array;
}
/* There is no quick entry to a row in the prepared statement. Thus we have
* too loop through until we reach the desired row, but only if the last row
* that we fetched is not the previous row... confused? join the club.
*/
last_row = c->SQLite2.last_row;
/* If the requested row is before the last row that was accessed then we need
* to re-compile the sql and recreate the virtual machine. SQLite3 provides a
* facility to reset a vm however SQLite2 does not. This is a downfall of
* SQLite in general though, we need to restart everything and walk through
* all of the results again until we get to the row we want... sigh
*/
if (row < last_row) {
free(c->SQLite2.errormsg);
sqlite_finalize(c->SQLite2.vm, NULL);
if (sqlite_compile(c->SQLite2.handle, c->SQLite2.sql, &tail, &c->SQLite2.vm, &c->SQLite2.errormsg) != SQLITE_OK)
return 0;
c->SQLite2.last_row = 0;
c->SQLite2.step_res = 0;
last_row = 0;
}
/* If the requested row is the same as the last one, ie: it's been requested
* again! we do not need to step forward, so we miss the row location loop
* and get straight to the nitty gritty of building the result array. If not
* we loop through from the last_row requested to the one requested this time
* using sqlite_step(). As long as the result is SQLITE_ROW we move on, if
* not then either an error occured or there are no more rows so we return a
* null array. The result is stored in the SQLite structure for later checks
* so if fetch is called again on a completed or errornous statement we can
* fail out sooner saving time.
*/
if ((row != last_row) && (last_row < row)) {
for (i = last_row; i < row; i++) {
c->SQLite2.step_res = sqlite_step(c->SQLite2.vm, NULL, &c->SQLite2.values, &c->SQLite2.col_names);
if (c->SQLite2.step_res == SQLITE_ROW)
break;
else
return &the_null_array;
}
}
/* SQLite v2 does not provide any functions for obtaining the values based on
* their datatypes like v3 does. It is completely typeless and everything is
* returned as a (char *). Thus we need a way of determining if the value is
* numeric or a string. I do make some assumptions here, but all in all it
* does work for the vast majority of cases. There is no support for blobs
* to be returned as LPC buffers with v2. Support for binary data in v2 is
* suspect at best and is not recommended anyway, if you need that use v3.
*
* To determine the datatype, we do the following. Run the value through
* strtoul() if it fails then the value could not be converted to a number
* so we assume it's a string and return it as such. If it works but also
* has trailing data, then it might be a real number or a string. Both
* "12.34" and "12 bottles" will cause strtoul() to work returning 12 but
* both will also have trailing data. Thus we try converting it to a real
* number using strtod() if this fails then we assume its a string that
* starts with a number ie: "12 bottles" and return it as a string. If it
* works then we return it as a real number (float).
*
* It's by no means perfect, but it does catch pretty much everything I've
* thrown at it and is the best solution, bar walking the embedded datatype
* description, if one was set, and working it out from that.
*/
v = allocate_empty_array(c->SQLite2.ncolumns);
for (i = 0; i < c->SQLite2.ncolumns; i++) {
/* If we have a NULL value get out now or we'll segfault */
if (c->SQLite2.values[i] == NULL) {
v->item[i] = const0u;
continue;
}
errno = 0;
l = strtoul(c->SQLite2.values[i], &p_end, 10);
if (errno != 0 || c->SQLite2.values[i] == p_end) {
/* The conversion failed so assume it's a string */
v->item[i].type = T_STRING;
v->item[i].subtype = STRING_MALLOC;
v->item[i].u.string = string_copy((char *)c->SQLite2.values[i], "SQLite2_fetch");
}
else if (*p_end != 0) {
/* The conversion left trailing characters behind, see if its a float */
errno = 0;
d = strtod(c->SQLite2.values[i], &p_end);
if (errno != 0 || c->SQLite2.values[i] == p_end || *p_end != 0) {
/* The conversion to float failed so it must be a string */
v->item[i].type = T_STRING;
v->item[i].subtype = STRING_MALLOC;
v->item[i].u.string = string_copy((char *)c->SQLite2.values[i], "SQLite2_fetch");
}
else {
/* It was a floating point number */
v->item[i].type = T_REAL;
v->item[i].u.real = (float)d;
}
}
else if (errno == 0) {
/* It was an integer */
v->item[i].type = T_NUMBER;
v->item[i].u.number = (int)l;
}
else {
/* No idea what it was */
v->item[i] = const0u;
}
}
c->SQLite2.last_row = row;
return v;
}
static char *SQLite2_errormsg (dbconn_t * c)
{
return string_copy(c->SQLite2.errormsg, "SQLite2_errormsg");
}
#endif
/*
* SQLite v3 support
* ajandurah@demonslair (Mark Lyndoe)
*/
#ifdef USE_SQLITE3
static int SQLite3_connect (dbconn_t * c, const char * host, const char * database, const char * username, const char * password)
{
if (sqlite3_open(database, &c->SQLite3.handle)) {
strncpy(c->SQLite3.errormsg, sqlite3_errmsg(c->SQLite3.handle), sizeof(c->SQLite3.errormsg));
c->SQLite3.errormsg[sizeof(c->SQLite3.errormsg) - 1] = 0;
sqlite3_close(c->SQLite3.handle);
return 0;
}
c->SQLite3.results = 0;
c->SQLite3.nrows = 0;
c->SQLite3.last_row = 0;
c->SQLite3.step_res = 0;
return 1;
}
static int SQLite3_close (dbconn_t * c)
{
if (c->SQLite3.results) {
sqlite3_finalize(c->SQLite3.results);
}
sqlite3_close(c->SQLite3.handle);
c->SQLite3.handle = 0;
c->SQLite3.nrows = 0;
c->SQLite3.last_row = 0;
c->SQLite3.step_res = 0;
return 1;
}
static void SQLite3_cleanup (dbconn_t * c)
{
if (c->SQLite3.results) {
sqlite3_finalize(c->SQLite3.results);
c->SQLite3.results = 0;
c->SQLite3.last_row = 0;
c->SQLite3.step_res = 0;
}
}
static int SQLite3_execute (dbconn_t * c, const char * s)
{
char **result;
if (sqlite3_prepare(c->SQLite3.handle, s, -1, &c->SQLite3.results, 0) != SQLITE_OK) {
strncpy(c->SQLite3.errormsg, sqlite3_errmsg(c->SQLite3.handle), sizeof(c->SQLite3.errormsg));
c->SQLite3.errormsg[sizeof(c->SQLite3.errormsg) - 1] = 0;
return 0;
}
/* There has to be a better way of determining the number of rows in the result
* set. sqlite3_prepare() does not provide them, since we need to call sqlite3_step()
* to walk through them, which we dont want to do until db_fetch() is called. This
* hack means we actually have to execute the full SQL statement to get the row
* total.. expensive time wise unfortunately.
*/
if (c->SQLite3.results) {
if (sqlite3_get_table(c->SQLite3.handle, s, &result, &c->SQLite3.nrows, &c->SQLite3.ncolumns, NULL) != SQLITE_OK) {
sqlite3_free_table(result);
sqlite3_finalize(c->SQLite3.results);
return 0;
}
sqlite3_free_table(result);
c->SQLite3.last_row = 0;
c->SQLite3.step_res = 0;
return c->SQLite3.nrows;
}
return -1;
}
static array_t *SQLite3_fetch (dbconn_t * c, int row)
{
int cols, last_row, length, i, r;
array_t *v;
if (!c->SQLite3.results) {
return &the_null_array;
}
if (c->SQLite3.step_res && c->SQLite3.step_res != SQLITE_ROW) {
return &the_null_array;
}
if (row < 0 || row > c->SQLite3.nrows) {
return &the_null_array;
}
cols = sqlite3_column_count(c->SQLite3.results);
if (cols < 1) {
return &the_null_array;
}
/* If the fetch is for row 0 then we don't return a row from the prepared
* statement, instead we return an array containing the column names. This
* ability is useful in a number of circumstances when the column names are
* not known in advance for a query. We do not step the statement nor do we
* adjust the last_row index since sqlite provides a method to obtain the
* column names at any time.
*/
if (row == 0) {
v = allocate_empty_array(cols);
for (i = 0; i< cols; i++) {
v->item[i].type = T_STRING;
v->item[i].subtype = STRING_MALLOC;
v->item[i].u.string = string_copy((char *)sqlite3_column_name(c->SQLite3.results, i), "SQLite3_fetch");
}
return v;
}
/* There is no quick entry to a row in the prepared statement. Thus we have
* too loop through till we reach the desired row, but only if the last row
* that we fetched is not the previous row... confused? join the club.
*/
last_row = c->SQLite3.last_row;
/* If the requested row is before the last row that was accessed then we
* reset the compiled statement before continuing. This is because we can't
* just select a row at will using sqlite and we can't rewind a step either.
* We could just reset the result statement at the end of this function like
* the msql and mysql versions, but that gets expensive next time it's called
* if we need to walk through everything again... sigh
*/
if (row < last_row) {
sqlite3_reset(c->SQLite3.results);
c->SQLite3.last_row = 0;
c->SQLite3.step_res = 0;
last_row = 0;
}
/* If the requested row is the same as the last one, ie: it's been requested
* again! we do not need to step forward, so we miss the row location loop
* and get straight to the nitty gritty of building the result array. If not
* we loop through from the last_row requested to the one requested this time
* using sqlite3_step(). As long as the result is SQLITE_ROW we move on, if
* not then either an error occured or there are no more rows so we return a
* null array. The result is stored in the sqlite structure for later checks
* so if fetch is called again on a completed or errornous statement we can
* fail out sooner saving time.
*/
if ((row != last_row) && (last_row < row)) {
for (i = last_row; i < row; i++) {
c->SQLite3.step_res = sqlite3_step(c->SQLite3.results);
if (c->SQLite3.step_res == SQLITE_ROW)
break;
else
return &the_null_array;
}
}
v = allocate_empty_array(cols);
for (i = 0; i < cols; i++) {
switch (sqlite3_column_type(c->SQLite3.results, i)) {
case SQLITE_INTEGER:
v->item[i].type = T_NUMBER;
v->item[i].u.number = sqlite3_column_int(c->SQLite3.results, i);
break;
case SQLITE_FLOAT:
v->item[i].type = T_REAL;
v->item[i].u.real = (float)sqlite3_column_double(c->SQLite3.results, i);
break;
case SQLITE3_TEXT:
v->item[i].type = T_STRING;
v->item[i].subtype = STRING_MALLOC;
v->item[i].u.string = string_copy((char *)sqlite3_column_text(c->SQLite3.results, i), "SQLite3_fetch");
break;
case SQLITE_BLOB:
#ifndef NO_BUFFER_TYPE
length = sqlite3_column_bytes(c->SQLite3.results, i);
v->item[i].type = T_BUFFER;
v->item[i].u.buf = allocate_buffer(length);
write_buffer(v->item[i].u.buf, 0, (char *)sqlite3_column_blob(c->SQLite3.results, i), length);
#else
v->item[i] = const0u;
#endif
break;
default:
v->item[i] = const0u;
break;
}
}
c->SQLite3.last_row = row;
return v;
}
static char *SQLite3_errormsg (dbconn_t * c)
{
if (*(c->SQLite3.errormsg)) {
return string_copy((char *)c->SQLite3.errormsg, "SQLite3_errormsg:1");
}
return string_copy((char *)sqlite3_errmsg(c->SQLite3.handle), "SQLite3_errormsg:2");
}
#endif
