00001 /* 00002 * $Id: database_access_mysql.c 2676 2010-01-11 15:31:31Z sion $ 00003 * 00004 * Copyright (c) 2008-2009 Nominet UK. All rights reserved. 00005 * 00006 * Redistribution and use in source and binary forms, with or without 00007 * modification, are permitted provided that the following conditions 00008 * are met: 00009 * 1. Redistributions of source code must retain the above copyright 00010 * notice, this list of conditions and the following disclaimer. 00011 * 2. Redistributions in binary form must reproduce the above copyright 00012 * notice, this list of conditions and the following disclaimer in the 00013 * documentation and/or other materials provided with the distribution. 00014 * 00015 * THIS SOFTWARE IS PROVIDED BY THE AUTHOR ``AS IS'' AND ANY EXPRESS OR 00016 * IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED 00017 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE 00018 * ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY 00019 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL 00020 * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE 00021 * GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS 00022 * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER 00023 * IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR 00024 * OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN 00025 * IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. 00026 * 00027 */ 00028 00029 /*+ 00030 * database_access - database Access Functions 00031 * 00032 * Description: 00033 * Holds miscellaneous utility functions associated with the MySql 00034 * database. 00035 * 00036 * This particular file holds encapsulations of the underlying access 00037 * functions - querying/modifying the database and retrieving results. 00038 -*/ 00039 00040 #include <stdarg.h> 00041 #include <string.h> 00042 #include <stdio.h> 00043 #include <time.h> 00044 00045 #include <mysql.h> 00046 00047 #include "ksm/dbsdef.h" 00048 #include "ksm/database.h" 00049 #include "ksm/debug.h" 00050 #include "ksm/memory.h" 00051 #include "ksm/message.h" 00052 #include "ksm/string_util.h" 00053 00054 #define MIN(x, y) ((x) < (y) ? (x) : (y)) 00055 #define MAX(x, y) ((x) > (y) ? (x) : (y)) 00056 00057 00058 /*+ 00059 * DbExecuteSqlStatement - Execute SQL Statement 00060 * 00061 * Description: 00062 * A wrapper round mysql_query that outputs the query being executed 00063 * if the appropriate debug flag is set. 00064 * 00065 * Arguments: 00066 * DB_HANDLE handle 00067 * Handle to the currently opened database. 00068 * 00069 * const char* stmt_str 00070 * SQL statement to execute. 00071 * 00072 * Returns: 00073 * int 00074 * Any return value from mysql_query. 00075 -*/ 00076 00077 static int DbExecuteSqlStatement(DB_HANDLE handle, const char* stmt_str) 00078 { 00079 DbgOutput(DBG_M_SQL, "%s\n", stmt_str); 00080 return mysql_query((MYSQL*) handle, stmt_str); 00081 } 00082 00083 00084 00085 /*+ 00086 * DbExecuteSql - Execute SQL Statement 00087 * 00088 * Description: 00089 * Executes the given SQL statement and returns the results (if any). 00090 * 00091 * Arguments: 00092 * DB_HANDLE handle 00093 * Handle to the currently opened database. 00094 * 00095 * const char* stmt_str 00096 * Statement to execute. 00097 * 00098 * DB_RESULT* result 00099 * Pointer to the result set is put here. It must be freed by 00100 * DbFreeResult(). This is NULL if no data is returned; on error, the 00101 * value is undefined. 00102 * 00103 * Returns: 00104 * int 00105 * 0 Success 00106 * Other Error code. A message will have been output. 00107 -*/ 00108 00109 int DbExecuteSql(DB_HANDLE handle, const char* stmt_str, DB_RESULT* result) 00110 { 00111 const char* errmsg = NULL; /* Error message from MySql on failure */ 00112 int status = 0; /* Status return */ 00113 00114 /* Argument check */ 00115 00116 if ((!handle) || (!stmt_str) || (*stmt_str == '\0') || (! result)) { 00117 status = MsgLog(DBS_INVARG, "DbExecuteSql"); 00118 return status; 00119 } 00120 00121 /* Allocate the result structure */ 00122 00123 *result = (DB_RESULT) MemCalloc(1, sizeof(struct db_result)); 00124 (*result)->magic = DB_RESULT_MAGIC; 00125 (*result)->handle = handle; 00126 00127 /* Execute statement */ 00128 00129 status = DbExecuteSqlStatement(handle, stmt_str); 00130 if (status == 0) { 00131 00132 /* Get the pointer to the result set */ 00133 00134 (*result)->data = mysql_store_result((MYSQL*) handle); 00135 if ((*result)->data == NULL) { 00136 00137 /* 00138 * No result set, so could be some error. See if this is the case 00139 * by checking if there is error text. If not, there are no results 00140 * from the SQL - it could have been a statement such as DELETE or 00141 * INSERT. 00142 */ 00143 00144 errmsg = DbErrmsg(handle); 00145 if (errmsg && *errmsg) { 00146 00147 /* Error text, so error occurred. Output message & tidy up */ 00148 00149 status = MsgLog(DBS_SQLFAIL, errmsg); 00150 } 00151 /* 00152 * else { 00153 * 00154 * No error, so we just don't have any results. 00155 * } 00156 */ 00157 00158 /* 00159 * Regardless of what heppened, there is no result set, so free up 00160 * allocated memory. 00161 */ 00162 00163 MemFree(*result); 00164 *result = NULL; 00165 } 00166 else { 00167 00168 /* 00169 * Success. "result" holds the result set. Store the number of 00170 * fields along with the length of each one for possible later use. 00171 */ 00172 00173 (*result)->count = mysql_field_count((MYSQL*) (*result)->handle); 00174 } 00175 } 00176 else { 00177 00178 /* Query failed. Log the error and free up the structure */ 00179 00180 status = MsgLog(DBS_SQLFAIL, DbErrmsg(handle)); 00181 MemFree(*result); 00182 *result = NULL; 00183 } 00184 00185 return status; 00186 } 00187 00188 /*+ 00189 * DbFreeResult - Free Result 00190 * 00191 * Description: 00192 * Frees up resources allocated for the result by DbExecuteSql. 00193 * 00194 * Arguments: 00195 * DB_RESULT result 00196 * Handle to the query result. May be NULL, in which case this 00197 * function is a no-op. 00198 * 00199 * If invalid, an error message will be output. 00200 -*/ 00201 00202 void DbFreeResult(DB_RESULT result) 00203 { 00204 if (result) { 00205 if (result->magic == DB_RESULT_MAGIC) { 00206 00207 /* Free up data */ 00208 00209 mysql_free_result((MYSQL_RES*) result->data); 00210 MemFree(result); 00211 } 00212 else { 00213 00214 /* Invalid structure - output a warning but do nothing */ 00215 00216 (void) MsgLog(DBS_INVARG, "DbFreeResult"); 00217 } 00218 } 00219 00220 return; 00221 } 00222 00223 00224 00225 /*+ 00226 * DbFetchRow - Fetch Row from Result 00227 * 00228 * Description: 00229 * Fetches the next row from the result set. The structure returned 00230 * *must* be freed by DbFreeRow() after use. 00231 * 00232 * Arguments: 00233 * DB_RESULT result 00234 * The result handle returned by the call to DbExecuteSql. 00235 * 00236 * DB_ROW* row 00237 * The row object is put here. It will be NULL end of file; on error, 00238 * it is undefined. 00239 * 00240 * Returns: 00241 * int 00242 * 0 Success, row information returned 00243 * -1 Success, no more rows for this result 00244 * Other Error code or error number from DbErrno(). 00245 -*/ 00246 00247 int DbFetchRow(DB_RESULT result, DB_ROW* row) 00248 { 00249 int status = 0; /* Status return */ 00250 MYSQL_ROW rowdata; /* Fetched row information */ 00251 00252 if (result && (result->magic == DB_RESULT_MAGIC) && row) { 00253 00254 /* There is a result structure (and row pointer), do something */ 00255 00256 rowdata = mysql_fetch_row(result->data); 00257 if (rowdata) { 00258 00259 /* Something returned, encapsulate the result in a structure */ 00260 00261 *row = (DB_ROW) MemCalloc(1, sizeof(struct db_row)); 00262 (*row)->magic = DB_ROW_MAGIC; 00263 (*row)->result = result; 00264 (*row)->data = rowdata; 00265 } 00266 else { 00267 00268 /* 00269 * End of file - in this implementation, only mysql_store_result is 00270 * used, so mysql_fetch_row returns NULL only on end of file. 00271 */ 00272 00273 /* leave freeing the row to the calling function */ 00274 /* *row = NULL; */ 00275 status = -1; 00276 } 00277 } 00278 else { 00279 status = MsgLog(DBS_INVARG, "DbFetchRow"); 00280 } 00281 00282 return status; 00283 } 00284 00285 00286 00287 /*+ 00288 * DbFreeRow - Free Row 00289 * 00290 * Description: 00291 * Frees up resources allocated for the row. 00292 * 00293 * Arguments: 00294 * DB_RESULT result 00295 * Handle to the query result. May be NULL, in which case this 00296 * function is a no-op. 00297 -*/ 00298 00299 void DbFreeRow(DB_ROW row) 00300 { 00301 if (row) { 00302 if (row->magic == DB_ROW_MAGIC) { 00303 MemFree(row); 00304 } 00305 else { 00306 00307 /* Output warning, but otherwise do nothing */ 00308 00309 (void) MsgLog(DBS_INVARG, "DbFreeRow"); 00310 } 00311 } 00312 00313 return; 00314 } 00315 00316 00317 00318 /*+ 00319 * DbString - Return String Value 00320 * 00321 * Description: 00322 * Returns string value from the current row. 00323 * 00324 * Arguments: 00325 * DB_ROW row 00326 * Pointer to the row object. 00327 * 00328 * int field_index 00329 * Index of the value required. 00330 * 00331 * char** result 00332 * Value of the field. It is up to the caller to free it with 00333 * a call to DbStringFree(). Note that this can be NULL if the 00334 * corresponding field is NULL. 00335 * 00336 * Returns: 00337 * int 00338 * 0 Success 00339 * Other Some error. A message will have been output 00340 -*/ 00341 00342 int DbString(DB_ROW row, int field_index, char** result) 00343 { 00344 int status = 0; /* Status return */ 00345 unsigned long *lengths; /* Lengths of columns in each row */ 00346 00347 /* Check arguments */ 00348 00349 if (row && (row->magic == DB_ROW_MAGIC) && result) { 00350 00351 /* Is the index requested valid? */ 00352 00353 if ((field_index >= 0) && (field_index < row->result->count)) { 00354 00355 /* Get the lengths of the fields in the row */ 00356 00357 lengths = mysql_fetch_lengths((MYSQL_RES*) row->result->data); 00358 00359 /* Get string into null-terminated form */ 00360 00361 if (row->data[field_index] != NULL) { 00362 /* TODO replece the below with strdup or StrStrdup ? */ 00363 *result = MemMalloc(lengths[field_index] + 1); 00364 memcpy(*result, row->data[field_index], lengths[field_index]); 00365 (*result)[lengths[field_index]] = 0; 00366 } 00367 else { 00368 *result = NULL; 00369 } 00370 } 00371 else { 00372 00373 /* Invalid field, tell the user */ 00374 00375 status = MsgLog(DBS_INVINDEX, field_index, row->result->count); 00376 } 00377 00378 } 00379 else { 00380 00381 /* Problem with the command arguments */ 00382 00383 status = MsgLog(DBS_INVARG, "DbString"); 00384 } 00385 00386 return status; 00387 00388 } 00389 00390 00391 /*+ 00392 * DbStringFree - Free String Returned by DbString 00393 * 00394 * Description: 00395 * Frees the pointer-to string. 00396 * 00397 * Arguments: 00398 * char* string 00399 * String allocated by DbString. On exit, this pointer is invalid. 00400 -*/ 00401 00402 void DbStringFree(char* string) 00403 { 00404 MemFree(string); 00405 } 00406 00407 /*+ 00408 * DbBeginTransaction - Start a (non-nested) transaction 00409 * 00410 * Description: 00411 * NB the following will not work if your tables are MyISAM 00412 * as transactions are not supported 00413 * 00414 * Arguments: 00415 * NONE 00416 -*/ 00417 00418 int DbBeginTransaction(void) 00419 { 00420 const char* sql = "start transaction"; 00421 return DbExecuteSqlNoResult(DbHandle(), sql); 00422 } 00423 00424 /*+ 00425 * DbCommit - End a (non-nested) transaction by commiting it 00426 * 00427 * Description: 00428 * 00429 * 00430 * Arguments: 00431 * NONE 00432 -*/ 00433 00434 int DbCommit(void) 00435 { 00436 const char* sql = "commit"; 00437 return DbExecuteSqlNoResult(DbHandle(), sql); 00438 } 00439 00440 /*+ 00441 * DbRollback - End a (non-nested) transaction by rolling it back 00442 * 00443 * Description: 00444 * 00445 * 00446 * Arguments: 00447 * NONE 00448 -*/ 00449 00450 int DbRollback(void) 00451 { 00452 const char* sql = "rollback"; 00453 return DbExecuteSqlNoResult(DbHandle(), sql); 00454 }