Parent Directory
| 
Revision Log
Revision 17 - (view) (download)
| 1 : | ian | 1 | /** |
| 2 : | * SQLX - Newer version of SQL stuff | ||
| 3 : | */ | ||
| 4 : | |||
| 5 : | #if defined _sqlx_included | ||
| 6 : | #endinput | ||
| 7 : | #endif | ||
| 8 : | #define _sqlx_included | ||
| 9 : | |||
| 10 : | //eh.. | ||
| 11 : | #define SQL_NumRows SQL_NumResults | ||
| 12 : | |||
| 13 : | #if AMXX_VERSION_NUM >= 175 | ||
| 14 : | #pragma reqclass sqlx | ||
| 15 : | #if !defined AMXMODX_NOAUTOLOAD | ||
| 16 : | #pragma defclasslib sqlx mysql | ||
| 17 : | #endif //!defined AMXMODX_NOAUTOLOAD | ||
| 18 : | #endif //AMXX_VERSION_NUM | ||
| 19 : | |||
| 20 : | enum Handle | ||
| 21 : | { | ||
| 22 : | Empty_Handle | ||
| 23 : | }; | ||
| 24 : | |||
| 25 : | /** | ||
| 26 : | * Creates a connection information tuple. | ||
| 27 : | * This tuple must be passed into connection routines. | ||
| 28 : | * Freeing the tuple is not necessary, but is a good idea if you | ||
| 29 : | * create many of them. You can cache these handles globally. | ||
| 30 : | * !!NOTE!! I have seen most people think that this connects to the DB. | ||
| 31 : | * Nowhere does it say this, and in fact it does not. It only caches | ||
| 32 : | * the connection information, the host/user/pass/etc. | ||
| 33 : | * | ||
| 34 : | * The optional timeout parameter specifies how long connections should wait before | ||
| 35 : | * giving up. If 0, the default (which is undefined) is used. | ||
| 36 : | * | ||
| 37 : | */ | ||
| 38 : | native Handle:SQL_MakeDbTuple(const host[], const user[], const pass[], const db[], timeout=0); | ||
| 39 : | |||
| 40 : | |||
| 41 : | /** | ||
| 42 : | * Frees an SQL handle. | ||
| 43 : | * The handle can be to anything (tuple, connection, query, results, etc). | ||
| 44 : | * If you free a database connection, it closes the connection as well. | ||
| 45 : | */ | ||
| 46 : | native SQL_FreeHandle(Handle:h); | ||
| 47 : | |||
| 48 : | |||
| 49 : | /** | ||
| 50 : | * Opens a database connection. | ||
| 51 : | * Returns an SQL handle, which must be freed. | ||
| 52 : | * Returns Empty_Handle on failure. | ||
| 53 : | */ | ||
| 54 : | native Handle:SQL_Connect(Handle:cn_tuple, &errcode, error[], maxlength); | ||
| 55 : | |||
| 56 : | |||
| 57 : | /** | ||
| 58 : | * Prepares a query. | ||
| 59 : | * The query must always be freed. | ||
| 60 : | * This does not actually do the query! | ||
| 61 : | */ | ||
| 62 : | ian | 17 | native Handle:SQL_PrepareQuery(Handle:db, const fmt[], any:...); |
| 63 : | ian | 1 | |
| 64 : | ian | 17 | |
| 65 : | /** | ||
| 66 : | * Back-quotes characters in a string for database querying. | ||
| 67 : | * Note: The buffer's maximum size should be 2*strlen(string) to catch | ||
| 68 : | * all scenarios. | ||
| 69 : | * | ||
| 70 : | * @param db Database handle, for localization. | ||
| 71 : | * @param buffer Buffer to copy to. | ||
| 72 : | * @param buflen Maximum size of the buffer. | ||
| 73 : | * @param string String to backquote (should not overlap buffer). | ||
| 74 : | * @return Length of new string, or -1 on failure. | ||
| 75 : | */ | ||
| 76 : | native SQL_QuoteString(Handle:db, buffer[], buflen, const string[]); | ||
| 77 : | |||
| 78 : | /** | ||
| 79 : | * Back-quotes characters in a string for database querying. | ||
| 80 : | * Note: The buffer's maximum size should be 2*strlen(string) to catch | ||
| 81 : | * all scenarios. | ||
| 82 : | * | ||
| 83 : | * @param db Database handle, for localization. | ||
| 84 : | * @param buffer Buffer to copy to. | ||
| 85 : | * @param buflen Maximum size of the buffer. | ||
| 86 : | * @param fmt Format of string to backquote (should not overlap buffer). | ||
| 87 : | * @param ... Format arguments. | ||
| 88 : | * @return Length of new string, or -1 on failure. | ||
| 89 : | */ | ||
| 90 : | native SQL_QuoteStringFmt(Handle:db, buffer[], buflen, const fmt[], any:...); | ||
| 91 : | |||
| 92 : | |||
| 93 : | ian | 1 | #define TQUERY_CONNECT_FAILED -2 |
| 94 : | #define TQUERY_QUERY_FAILED -1 | ||
| 95 : | #define TQUERY_SUCCESS 0 | ||
| 96 : | /** | ||
| 97 : | * Prepares and executes a threaded query. | ||
| 98 : | * This will not interrupt gameplay in the event of a poor/lossed | ||
| 99 : | * connection, however, the interface is more complicated and | ||
| 100 : | * asynchronous. Furthermore, a new connection/disconnection is | ||
| 101 : | * made for each query to simplify driver support. | ||
| 102 : | * The handler should look like: | ||
| 103 : | * | ||
| 104 : | * @param failstate - One of the three TQUERY_ defines. | ||
| 105 : | * @param query - Handle to the query, do not free it. | ||
| 106 : | * @param error - An error message, if any. | ||
| 107 : | * @param errnum - An error code, if any. | ||
| 108 : | * @param data - Data array you passed in. | ||
| 109 : | * @param size - Size of the data array you passed in. | ||
| 110 : | * @param queuetime - Amount of gametime that passed while the query was resolving. | ||
| 111 : | * | ||
| 112 : | * public QueryHandler(failstate, Handle:query, error[], errnum, data[], size, Float:queuetime) | ||
| 113 : | * | ||
| 114 : | * Note! The handle you pass in is a DB Tuple, NOT an active connection! | ||
| 115 : | * Note! The handle does not need to be freed. | ||
| 116 : | * Also note: This function is not guaranteed to be in another thread | ||
| 117 : | * (in fact - it's not). You're seeing data "after the fact", | ||
| 118 : | * and as such to execute another query you should run | ||
| 119 : | * SQL_ThreadQuery again with new data. | ||
| 120 : | */ | ||
| 121 : | native SQL_ThreadQuery(Handle:db_tuple, const handler[], const query[], const data[]="", dataSize=0); | ||
| 122 : | |||
| 123 : | |||
| 124 : | /** | ||
| 125 : | * Executes a query. | ||
| 126 : | * Returns 1 if the query succeeded. | ||
| 127 : | * Returns 0 if the query failed. | ||
| 128 : | * NOTE: You can call this multiple times as long as its parent | ||
| 129 : | * connection is kept open. Each time the result set will be freed | ||
| 130 : | * from the previous call. | ||
| 131 : | */ | ||
| 132 : | native SQL_Execute(Handle:query); | ||
| 133 : | |||
| 134 : | /** | ||
| 135 : | * Gets information about a failed query error. | ||
| 136 : | * Returns the errorcode. | ||
| 137 : | */ | ||
| 138 : | native SQL_QueryError(Handle:query, error[], maxlength); | ||
| 139 : | |||
| 140 : | |||
| 141 : | /** | ||
| 142 : | * Returns 1 if there are more results to be read, | ||
| 143 : | * 0 otherwise. | ||
| 144 : | */ | ||
| 145 : | native SQL_MoreResults(Handle:query); | ||
| 146 : | |||
| 147 : | |||
| 148 : | /** | ||
| 149 : | * Tells whether a specific column in the current row | ||
| 150 : | * is NULL or not. | ||
| 151 : | */ | ||
| 152 : | native SQL_IsNull(Handle:query, column); | ||
| 153 : | |||
| 154 : | /** | ||
| 155 : | * Retrieves the current result. | ||
| 156 : | * A successful query starts at the first result, | ||
| 157 : | * so you should not call SQL_NextRow() first. | ||
| 158 : | * Passing no extra params - return int | ||
| 159 : | * Passing one extra param - return float in 1st extra arg | ||
| 160 : | * Passing two extra params - return string in 1st arg, max length in 2nd | ||
| 161 : | * Example: | ||
| 162 : | * new num = SQL_ReadResult(query, 0) | ||
| 163 : | * new Float:num2 | ||
| 164 : | * new str[32] | ||
| 165 : | * SQL_ReadResult(query, 1, num2) | ||
| 166 : | * SQL_ReadResult(query, 2, str, 31) | ||
| 167 : | */ | ||
| 168 : | native SQL_ReadResult(Handle:query, column, {Float,_}:...); | ||
| 169 : | |||
| 170 : | |||
| 171 : | /** | ||
| 172 : | * Advances to the next result (return value should be ignored). | ||
| 173 : | */ | ||
| 174 : | native SQL_NextRow(Handle:query); | ||
| 175 : | |||
| 176 : | |||
| 177 : | /** | ||
| 178 : | * Returns the number of affected rows. | ||
| 179 : | */ | ||
| 180 : | native SQL_AffectedRows(Handle:query); | ||
| 181 : | |||
| 182 : | |||
| 183 : | /** | ||
| 184 : | * Returns the number of rows total. | ||
| 185 : | */ | ||
| 186 : | native SQL_NumResults(Handle:query); | ||
| 187 : | |||
| 188 : | |||
| 189 : | /** | ||
| 190 : | * Returns the number of columns total. | ||
| 191 : | */ | ||
| 192 : | native SQL_NumColumns(Handle:query); | ||
| 193 : | |||
| 194 : | |||
| 195 : | /** | ||
| 196 : | * Returns the name of a column. | ||
| 197 : | * Errors on a bad field number. | ||
| 198 : | */ | ||
| 199 : | native SQL_FieldNumToName(Handle:query, num, name[], maxlength); | ||
| 200 : | |||
| 201 : | |||
| 202 : | /** | ||
| 203 : | * Returns the number of a named column, or -1 if not found. | ||
| 204 : | */ | ||
| 205 : | native SQL_FieldNameToNum(Handle:query, const name[]); | ||
| 206 : | |||
| 207 : | |||
| 208 : | /** | ||
| 209 : | ian | 17 | * Rewinds a result set to the first row. |
| 210 : | */ | ||
| 211 : | native SQL_Rewind(Handle:query); | ||
| 212 : | |||
| 213 : | |||
| 214 : | /** | ||
| 215 : | ian | 1 | * Returns the insert id of the last INSERT query. |
| 216 : | * Returns 0 otherwise. | ||
| 217 : | */ | ||
| 218 : | native SQL_GetInsertId(Handle:query); | ||
| 219 : | |||
| 220 : | |||
| 221 : | /** | ||
| 222 : | * Returns which driver this plugin is currently bound to. | ||
| 223 : | */ | ||
| 224 : | native SQL_GetAffinity(driver[], maxlen); | ||
| 225 : | |||
| 226 : | /** | ||
| 227 : | * Sets driver affinity. You can use this to force a particular | ||
| 228 : | * driver implementation. This will automatically change all SQL | ||
| 229 : | * natives in your plugin to be "bound" to the module in question. | ||
| 230 : | * If no such module is found, it will return 0. This isn't necessarily bad - | ||
| 231 : | * the user might have typed the wrong driver. Unless your plugin is built | ||
| 232 : | * to handle different driver types at once, you should let this error pass. | ||
| 233 : | * Note, that using this while you have open handles to another database | ||
| 234 : | * type will cause problems. I.e., you cannot open a handle, switch | ||
| 235 : | * affinity, then close the handle with a different driver. | ||
| 236 : | * Switching affinity is an O(n*m) operation, where n is the number of | ||
| 237 : | * SQL natives and m is the number of used natives in total. | ||
| 238 : | * Intuitive programmers will note that this causes problems for threaded queries. | ||
| 239 : | * You will have to either force your script to work under one affinity, or to | ||
| 240 : | * pack the affinity type into the query data, check it against the current, then | ||
| 241 : | * set the new affinity if necessary. Then, restore the old for safety. | ||
| 242 : | */ | ||
| 243 : | native SQL_SetAffinity(const driver[]); | ||
| 244 : | |||
| 245 : | /** | ||
| 246 : | * Returns the original query string that a query handle used. | ||
| 247 : | */ | ||
| 248 : | native SQL_GetQueryString(Handle:query, buffer[], maxlength); | ||
| 249 : | |||
| 250 : | /** | ||
| 251 : | ian | 17 | * For queries which return multiple result sets, this advances to the next |
| 252 : | * result set if one is available. Otherwise, the current result set is | ||
| 253 : | * destroyed and will no longer be accessible. | ||
| 254 : | * | ||
| 255 : | * This function will always return false on SQLite, and when using threaded | ||
| 256 : | * queries in MySQL. Nonetheless, it has the same effect of removing the last | ||
| 257 : | * result set. | ||
| 258 : | * | ||
| 259 : | * @param query Query Handle. | ||
| 260 : | * @return True on success, false on failure. | ||
| 261 : | */ | ||
| 262 : | native bool:SQL_NextResultSet(Handle:query); | ||
| 263 : | |||
| 264 : | /** | ||
| 265 : | ian | 1 | * This function can be used to find out if a table in a Sqlite database exists. |
| 266 : | * (updated for newer API) | ||
| 267 : | */ | ||
| 268 : | stock bool:sqlite_TableExists(Handle:db, const table[]) | ||
| 269 : | { | ||
| 270 : | new Handle:query = SQL_PrepareQuery( | ||
| 271 : | db, | ||
| 272 : | "SELECT name FROM sqlite_master WHERE type='table' AND name='%s' LIMIT 1;", | ||
| 273 : | table); | ||
| 274 : | |||
| 275 : | if (!SQL_Execute(query) || !SQL_NumResults(query)) | ||
| 276 : | { | ||
| 277 : | SQL_FreeHandle(query); | ||
| 278 : | return false; | ||
| 279 : | } | ||
| 280 : | |||
| 281 : | SQL_FreeHandle(query); | ||
| 282 : | |||
| 283 : | return true; | ||
| 284 : | } | ||
| 285 : | |||
| 286 : | /** | ||
| 287 : | * Use this for executing a query where you don't care about the result. | ||
| 288 : | * Returns 0 on failure, 1 on success | ||
| 289 : | */ | ||
| 290 : | stock SQL_SimpleQuery(Handle:db, const query[], error[]="", maxlength=0, &rows=0) | ||
| 291 : | { | ||
| 292 : | new Handle:hQuery = SQL_PrepareQuery(db, "%s", query); | ||
| 293 : | |||
| 294 : | if (!SQL_Execute(hQuery)) | ||
| 295 : | { | ||
| 296 : | SQL_QueryError(hQuery, error, maxlength); | ||
| 297 : | SQL_FreeHandle(hQuery); | ||
| 298 : | return 0; | ||
| 299 : | } | ||
| 300 : | |||
| 301 : | rows = SQL_NumResults(hQuery); | ||
| 302 : | |||
| 303 : | SQL_FreeHandle(hQuery); | ||
| 304 : | |||
| 305 : | return 1; | ||
| 306 : | } | ||
| 307 : | |||
| 308 : | /** | ||
| 309 : | * Use this for executing a query where you don't care about the result. | ||
| 310 : | * Returns 0 on failure, 1 on success | ||
| 311 : | */ | ||
| 312 : | ian | 17 | stock SQL_SimpleQueryFmt(Handle:db, error[]="", maxlength=0, &rows=0, const fmt[], any:...) |
| 313 : | ian | 1 | { |
| 314 : | static query_buf[2048]; | ||
| 315 : | vformat(query_buf, 2047, fmt, 6); | ||
| 316 : | |||
| 317 : | new Handle:hQuery = SQL_PrepareQuery(db, "%s", query_buf); | ||
| 318 : | |||
| 319 : | if (!SQL_Execute(hQuery)) | ||
| 320 : | { | ||
| 321 : | SQL_QueryError(hQuery, error, maxlength); | ||
| 322 : | SQL_FreeHandle(hQuery); | ||
| 323 : | return 0; | ||
| 324 : | } | ||
| 325 : | |||
| 326 : | rows = SQL_NumResults(hQuery); | ||
| 327 : | |||
| 328 : | SQL_FreeHandle(hQuery); | ||
| 329 : | |||
| 330 : | return 1; | ||
| 331 : | } | ||
| 332 : | |||
| 333 : | /** | ||
| 334 : | * Use this for executing a query and not caring about the error. | ||
| 335 : | * Returns -1 on error, >=0 on success (with number of affected rows) | ||
| 336 : | */ | ||
| 337 : | ian | 17 | stock SQL_QueryAndIgnore(Handle:db, const queryfmt[], any:...) |
| 338 : | ian | 1 | { |
| 339 : | static query[4096]; | ||
| 340 : | new Handle:hQuery; | ||
| 341 : | new ret; | ||
| 342 : | |||
| 343 : | vformat(query, sizeof(query)-1, queryfmt, 3); | ||
| 344 : | |||
| 345 : | hQuery = SQL_PrepareQuery(db, "%s", query); | ||
| 346 : | |||
| 347 : | if (SQL_Execute(hQuery)) | ||
| 348 : | { | ||
| 349 : | ret = SQL_AffectedRows(hQuery); | ||
| 350 : | } else { | ||
| 351 : | ret = -1; | ||
| 352 : | } | ||
| 353 : | |||
| 354 : | SQL_FreeHandle(hQuery); | ||
| 355 : | |||
| 356 : | return ret; | ||
| 357 : | } | ||
| 358 : | |||
| 359 : | ian | 17 | stock Handle:SQL_MakeStdTuple(timeout = 0) |
| 360 : | ian | 1 | { |
| 361 : | static host[64], user[32], pass[32], db[128]; | ||
| 362 : | static get_type[12], set_type[12]; | ||
| 363 : | |||
| 364 : | get_cvar_string("amx_sql_host", host, 63); | ||
| 365 : | get_cvar_string("amx_sql_user", user, 31); | ||
| 366 : | get_cvar_string("amx_sql_pass", pass, 31); | ||
| 367 : | get_cvar_string("amx_sql_type", set_type, 11); | ||
| 368 : | get_cvar_string("amx_sql_db", db, 127); | ||
| 369 : | |||
| 370 : | SQL_GetAffinity(get_type, 12); | ||
| 371 : | |||
| 372 : | if (!equali(get_type, set_type)) | ||
| 373 : | { | ||
| 374 : | if (!SQL_SetAffinity(set_type)) | ||
| 375 : | { | ||
| 376 : | log_amx("Failed to set affinity from %s to %s.", get_type, set_type); | ||
| 377 : | } | ||
| 378 : | } | ||
| 379 : | |||
| 380 : | ian | 17 | return SQL_MakeDbTuple(host, user, pass, db, timeout); |
| 381 : | ian | 1 | } |
| Contact | ViewVC Help |
| Powered by ViewVC 1.0.4 |