/****************************************************************************** **** **** 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" #ifdef PACKAGE_ASYNC #include #endif static int dbConnAlloc, dbConnUsed; static db_t *dbConnList; 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_POSTGRES static int Postgres_connect (dbconn_t *, const char *, const char *, const char *, const char *); static int Postgres_close (dbconn_t *); static int Postgres_execute (dbconn_t *, const char *); static array_t *Postgres_fetch (dbconn_t *, int); static void Postgres_cleanup (dbconn_t *); static char * Postgres_errormsg (dbconn_t *); static db_defn_t postgres = { "Postgres", Postgres_connect, Postgres_close, Postgres_execute, Postgres_fetch, NULL, NULL, Postgres_cleanup, NULL, Postgres_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) */ 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 #ifdef USE_POSTGRES #if USE_POSTGRES - 0 case USE_POSTGRES: #endif db->type = &postgres; 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 PACKAGE_ASYNC extern pthread_mutex_t *db_mut; #endif #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"); } #ifdef PACKAGE_ASYNC if(!db_mut){ db_mut = (pthread_mutex_t *)malloc(sizeof(pthread_mutex_t)); pthread_mutex_init(db_mut, NULL); } pthread_mutex_lock(db_mut); #endif 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; } #ifdef PACKAGE_ASYNC pthread_mutex_unlock(db_mut); #endif } #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 { pthread_mutex_lock(db_mut); dbConnList = RESIZE(dbConnList, dbConnAlloc, db_t, TAG_DB, "create_db_conn"); pthread_mutex_unlock(db_mut); } 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: case FIELD_TYPE_INT24: case FIELD_TYPE_LONGLONG: v->item[i].type = T_NUMBER; v->item[i].subtype = 0; 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 /* * Postgres support */ #ifdef USE_POSTGRES static void Postgres_cleanup (dbconn_t * c) { c->postgres.res = 0; } static char *Postgres_errormsg (dbconn_t * c) { return string_copy(PQerrorMessage(c->postgres.conn), "postgresql_errormsg"); } static int Postgres_close (dbconn_t * c) { PQclear(c->postgres.res); PQfinish(c->postgres.conn); return 1; } static int Postgres_execute (dbconn_t * c, const char * s) { c->postgres.res = PQexec( c->postgres.conn, s ); if( (PQresultStatus( c->postgres.res )) == PGRES_TUPLES_OK ) { return PQntuples( c->postgres.res ); } if( PQresultStatus( c->postgres.res ) == PGRES_COMMAND_OK ) { return 0; } fprintf(stderr, "FT: Query failed: \"%s\"\n", PQresultErrorMessage(c->postgres.res)); return -1; } static int Postgres_connect (dbconn_t * c, const char * host, const char * database, const char * username, const char * password) { int buffsize; char *connstr = "host = '%s' dbname = '%s' user = '%s' password = '%s'"; buffsize = strlen(connstr) + strlen(host) + strlen(database) + strlen(username) + strlen(password); char *conninfo = malloc(buffsize); if( conninfo != NULL ) { sprintf(conninfo,connstr,host,database,username,password); } c->postgres.conn = PQconnectdb( conninfo ); free(conninfo); if( (PQstatus(c->postgres.conn) != CONNECTION_OK) ) { return 0; } return 1; } static array_t *Postgres_fetch (dbconn_t * c, int row) { array_t *v; char * field; unsigned int i, num_fields; if (!c->postgres.res) { return &the_null_array; } num_fields = PQnfields( c->postgres.res ); if (row < -1 || row > PQntuples( c->postgres.res )) { return &the_null_array; } if (num_fields < 1) { return &the_null_array; } if(row==-1) { v = allocate_empty_array(num_fields); for( i = 0;i < num_fields; i++ ) { v->item[i].type = T_STRING; v->item[i].subtype = STRING_MALLOC; v->item[i].u.string = string_copy(PQfname(c->postgres.res,i), "f_db_fetch"); } } if(row>=0){ v = allocate_empty_array(num_fields); for (i = 0;i < num_fields;i++) { if( PQgetisnull(c->postgres.res, row, i) ) { v->item[i] = const0u; } else { v->item[i].type = T_STRING; v->item[i].subtype = STRING_MALLOC; v->item[i].u.string = string_copy(PQgetvalue(c->postgres.res,row,i),"postgres_fetch"); } } } return v; } #endif