dbd/apr_dbd_sqlite3.c

   1 /* Copyright 2000-2005 The Apache Software Foundation or its licensors, as
   2  * applicable.
   3  *
   4  * Licensed under the Apache License, Version 2.0 (the "License");
   5  * you may not use this file except in compliance with the License.
   6  * You may obtain a copy of the License at
   7  *
   8  *     http://www.apache.org/licenses/LICENSE-2.0
   9  *
  10  * Unless required by applicable law or agreed to in writing, software
  11  * distributed under the License is distributed on an "AS IS" BASIS,
  12  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  13  * See the License for the specific language governing permissions and
  14  * limitations under the License.
  15  */
  16
  17 #include "apu.h"
  18
  19 #if APU_HAVE_SQLITE3
  20
  21 #include <ctype.h>
  22 #include <stdlib.h>
  23
  24 #include <sqlite3.h>
  25
  26 #include "apr_strings.h"
  27 #include "apr_time.h"
  28
  29 #include "apr_dbd_internal.h"
  30
  31 #define MAX_RETRY_COUNT 15
  32 #define MAX_RETRY_SLEEP 100000
  33
  34 #define QUERY_MAX_ARGS 40
  35
  36 struct apr_dbd_transaction_t {
  37     int errnum;
  38     apr_dbd_t *handle;
  39 };
  40
  41 struct apr_dbd_t {
  42     sqlite3 *conn;
  43     apr_dbd_transaction_t *trans;
  44     apr_pool_t *pool;
  45     apr_dbd_prepared_t *prep;
  46 };
  47
  48 typedef struct {
  49     char *name;
  50     char *value;
  51     int size;
  52     int type;
  53 } apr_dbd_column_t;
  54
  55 struct apr_dbd_row_t {
  56     apr_dbd_results_t *res;
  57     apr_dbd_column_t **columns;
  58     apr_dbd_row_t *next_row;
  59     int columnCount;
  60     int rownum;
  61 };
  62
  63 struct apr_dbd_results_t {
  64     int random;
  65     sqlite3 *handle;
  66     sqlite3_stmt *stmt;
  67     apr_dbd_row_t *next_row;
  68     size_t sz;
  69     int tuples;
  70     char **col_names;
  71 };
  72
  73 struct apr_dbd_prepared_t {
  74     sqlite3_stmt *stmt;
  75     apr_dbd_prepared_t *next;
  76 };
  77
  78 #define dbd_sqlite3_is_success(x) (((x) == SQLITE_DONE ) \
  79                 || ((x) == SQLITE_OK ))
  80
  81 static int dbd_sqlite3_select(apr_pool_t * pool, apr_dbd_t * sql, apr_dbd_results_t ** results, const char *query, int seek)
  82 {
  83     sqlite3_stmt *stmt = NULL;
  84     const char *tail = NULL;
  85     int i, ret, retry_count = 0;
  86     size_t num_tuples = 0;
  87     int increment = 0;
  88     apr_dbd_row_t *row = NULL;
  89     apr_dbd_row_t *lastrow = NULL;
  90     apr_dbd_column_t *column;
  91     char *hold = NULL;
  92
  93     if (sql->trans && sql->trans->errnum) {
  94         return sql->trans->errnum;
  95     }
  96
  97     apr_dbd_mutex_lock();
  98
  99     ret = sqlite3_prepare(sql->conn, query, strlen(query), &stmt, &tail);
 100     if (!dbd_sqlite3_is_success(ret)) {
 101         apr_dbd_mutex_unlock();
 102         return ret;
 103     } else {
 104         int column_count;
 105         column_count = sqlite3_column_count(stmt);
 106         if (!*results) {
 107             *results = apr_pcalloc(pool, sizeof(apr_dbd_results_t));
 108         }
 109         (*results)->stmt = stmt;
 110         (*results)->sz = column_count;
 111         (*results)->random = seek;
 112         (*results)->next_row = 0;
 113         (*results)->tuples = 0;
 114         (*results)->col_names = apr_pcalloc(pool,
 115                                             column_count * sizeof(char *));
 116         do {
 117             ret = sqlite3_step(stmt);
 118             if (ret == SQLITE_BUSY) {
 119                 if (retry_count++ > MAX_RETRY_COUNT) {
 120                     ret = SQLITE_ERROR;
 121                 } else {
 122                     apr_dbd_mutex_unlock();
 123                     apr_sleep(MAX_RETRY_SLEEP);
 124                     apr_dbd_mutex_lock();
 125                 }
 126             } else if (ret == SQLITE_ROW) {
 127                 int length;
 128                 apr_dbd_column_t *col;
 129                 row = apr_palloc(pool, sizeof(apr_dbd_row_t));
 130                 row->res = *results;
 131                 increment = sizeof(apr_dbd_column_t *);
 132                 length = increment * (*results)->sz;
 133                 row->columns = apr_palloc(pool, length);
 134                 row->columnCount = column_count;
 135                 for (i = 0; i < (*results)->sz; i++) {
 136                     column = apr_palloc(pool, sizeof(apr_dbd_column_t));
 137                     row->columns[i] = column;
 138                     /* copy column name once only */
 139                     if ((*results)->col_names[i] == NULL) {
 140                       (*results)->col_names[i] =
 141                           apr_pstrdup(pool, sqlite3_column_name(stmt, i));
 142                     }
 143                     column->name = (*results)->col_names[i];
 144                     column->size = sqlite3_column_bytes(stmt, i);
 145                     column->type = sqlite3_column_type(stmt, i);
 146                     column->value = NULL;
 147                     switch (column->type) {
 148                     case SQLITE_FLOAT:
 149                     case SQLITE_INTEGER:
 150                     case SQLITE_TEXT:
 151                         hold = NULL;
 152                         hold = (char *) sqlite3_column_text(stmt, i);
 153                         if (hold) {
 154                             column->value = apr_palloc(pool, column->size + 1);
 155                             strncpy(column->value, hold, column->size + 1);
 156                         }
 157                         break;
 158                     case SQLITE_BLOB:
 159                         break;
 160                     case SQLITE_NULL:
 161                         break;
 162                     }
 163                     col = row->columns[i];
 164                 }
 165                 row->rownum = num_tuples++;
 166                 row->next_row = 0;
 167                 (*results)->tuples = num_tuples;
 168                 if ((*results)->next_row == 0) {
 169                     (*results)->next_row = row;
 170                 }
 171                 if (lastrow != 0) {
 172                     lastrow->next_row = row;
 173                 }
 174                 lastrow = row;
 175             } else if (ret == SQLITE_DONE) {
 176                 ret = SQLITE_OK;
 177             }
 178         } while (ret == SQLITE_ROW || ret == SQLITE_BUSY);
 179     }
 180     ret = sqlite3_finalize(stmt);
 181     apr_dbd_mutex_unlock();
 182
 183     if (sql->trans) {
 184         sql->trans->errnum = ret;
 185     }
 186     return ret;
 187 }
 188
 189 static const char *dbd_sqlite3_get_name(const apr_dbd_results_t *res, int n)
 190 {
 191     if ((n < 0) || (n >= res->sz)) {
 192         return NULL;
 193     }
 194
 195     return res->next_row->columns[n]->name;
 196 }
 197
 198 static int dbd_sqlite3_get_row(apr_pool_t *pool, apr_dbd_results_t *res,
 199                                apr_dbd_row_t **rowp, int rownum)
 200 {
 201     int i = 0;
 202
 203     if (rownum == -1) {
 204         *rowp = res->next_row;
 205         if (*rowp == 0)
 206             return -1;
 207         res->next_row = (*rowp)->next_row;
 208         return 0;
 209     }
 210     if (rownum > res->tuples) {
 211         return -1;
 212     }
 213     rownum--;
 214     *rowp = res->next_row;
 215     for (; *rowp != 0; i++, *rowp = (*rowp)->next_row) {
 216         if (i == rownum) {
 217             return 0;
 218         }
 219     }
 220
 221     return -1;
 222
 223 }
 224
 225 static const char *dbd_sqlite3_get_entry(const apr_dbd_row_t *row, int n)
 226 {
 227     apr_dbd_column_t *column;
 228     const char *value;
 229     if ((n < 0) || (n >= row->columnCount)) {
 230         return NULL;
 231     }
 232     column = row->columns[n];
 233     value = column->value;
 234     return value;
 235 }
 236
 237 static const char *dbd_sqlite3_error(apr_dbd_t *sql, int n)
 238 {
 239     return sqlite3_errmsg(sql->conn);
 240 }
 241
 242 static int dbd_sqlite3_query(apr_dbd_t *sql, int *nrows, const char *query)
 243 {
 244     sqlite3_stmt *stmt = NULL;
 245     const char *tail = NULL;
 246     int ret = -1, length = 0;
 247
 248     if (sql->trans && sql->trans->errnum) {
 249         return sql->trans->errnum;
 250     }
 251
 252     length = strlen(query);
 253     apr_dbd_mutex_lock();
 254
 255     do {
 256         int retry_count = 0;
 257
 258         ret = sqlite3_prepare(sql->conn, query, length, &stmt, &tail);
 259         if (ret != SQLITE_OK) {
 260             sqlite3_finalize(stmt);
 261             break;
 262         }
 263
 264         while(retry_count++ <= MAX_RETRY_COUNT) {
 265             ret = sqlite3_step(stmt);
 266             if (ret != SQLITE_BUSY)
 267                 break;
 268
 269             apr_dbd_mutex_unlock();
 270             apr_sleep(MAX_RETRY_SLEEP);
 271             apr_dbd_mutex_lock();
 272         }
 273
 274         *nrows = sqlite3_changes(sql->conn);
 275         sqlite3_finalize(stmt);
 276         length -= (tail - query);
 277         query = tail;
 278     } while (length > 0);
 279
 280     if (dbd_sqlite3_is_success(ret)) {
 281         ret = 0;
 282     }
 283     apr_dbd_mutex_unlock();
 284     if (sql->trans) {
 285         sql->trans->errnum = ret;
 286     }
 287     return ret;
 288 }
 289
 290 static apr_status_t free_mem(void *data)
 291 {
 292     sqlite3_free(data);
 293     return APR_SUCCESS;
 294 }
 295
 296 static const char *dbd_sqlite3_escape(apr_pool_t *pool, const char *arg,
 297                                       apr_dbd_t *sql)
 298 {
 299     char *ret = sqlite3_mprintf("%q", arg);
 300     apr_pool_cleanup_register(pool, ret, free_mem,
 301                               apr_pool_cleanup_null);
 302     return ret;
 303 }
 304
 305 static int dbd_sqlite3_prepare(apr_pool_t *pool, apr_dbd_t *sql,
 306                                const char *query, const char *label,
 307                                apr_dbd_prepared_t **statement)
 308 {
 309     sqlite3_stmt *stmt;
 310     char *p, *slquery = apr_pstrdup(pool, query);
 311     const char *tail = NULL, *q;
 312     int ret;
 313
 314     for (p = slquery, q = query; *q; ++q) {
 315         if (q[0] == '%') {
 316             if (isalpha(q[1])) {
 317                 *p++ = '?';
 318                 ++q;
 319             }
 320             else if (q[1] == '%') {
 321                 /* reduce %% to % */
 322                 *p++ = *q++;
 323             }
 324             else {
 325                 *p++ = *q;
 326             }
 327         }
 328         else {
 329             *p++ = *q;
 330         }
 331     }
 332     *p = 0;
 333
 334     apr_dbd_mutex_lock();
 335
 336     ret = sqlite3_prepare(sql->conn, slquery, strlen(query), &stmt, &tail);
 337     if (ret == SQLITE_OK) {
 338         apr_dbd_prepared_t *prep;
 339
 340         prep = apr_pcalloc(sql->pool, sizeof(*prep));
 341         prep->stmt = stmt;
 342         prep->next = sql->prep;
 343
 344         /* link new statement to the handle */
 345         sql->prep = prep;
 346
 347         *statement = prep;
 348     } else {
 349         sqlite3_finalize(stmt);
 350     }
 351
 352     apr_dbd_mutex_unlock();
 353
 354     return ret;
 355 }
 356
 357 static int dbd_sqlite3_pquery(apr_pool_t *pool, apr_dbd_t *sql,
 358                               int *nrows, apr_dbd_prepared_t *statement,
 359                               int nargs, const char **values)
 360 {
 361     sqlite3_stmt *stmt = statement->stmt;
 362     int ret = -1, retry_count = 0, i;
 363
 364     if (sql->trans && sql->trans->errnum) {
 365         return sql->trans->errnum;
 366     }
 367
 368     apr_dbd_mutex_lock();
 369
 370     ret = sqlite3_reset(stmt);
 371     if (ret == SQLITE_OK) {
 372         for (i=0; i < nargs; i++) {
 373             sqlite3_bind_text(stmt, i + 1, values[i], strlen(values[i]),
 374                               SQLITE_STATIC);
 375         }
 376
 377         while(retry_count++ <= MAX_RETRY_COUNT) {
 378             ret = sqlite3_step(stmt);
 379             if (ret != SQLITE_BUSY)
 380                 break;
 381
 382             apr_dbd_mutex_unlock();
 383             apr_sleep(MAX_RETRY_SLEEP);
 384             apr_dbd_mutex_lock();
 385         }
 386
 387         *nrows = sqlite3_changes(sql->conn);
 388
 389         sqlite3_reset(stmt);
 390     }
 391
 392     if (dbd_sqlite3_is_success(ret)) {
 393         ret = 0;
 394     }
 395     apr_dbd_mutex_unlock();
 396     if (sql->trans) {
 397         sql->trans->errnum = ret;
 398     }
 399
 400     return ret;
 401 }
 402
 403 static int dbd_sqlite3_pvquery(apr_pool_t *pool, apr_dbd_t *sql, int *nrows,
 404                                apr_dbd_prepared_t *statement, va_list args)
 405 {
 406     const char *arg, *values[QUERY_MAX_ARGS];
 407     int nargs = 0;
 408
 409     if (sql->trans && sql->trans->errnum) {
 410         return sql->trans->errnum;
 411     }
 412
 413     while (arg = va_arg(args, const char*), arg) {
 414         if (nargs >= QUERY_MAX_ARGS) {
 415             va_end(args);
 416             return -1;
 417         }
 418         values[nargs++] = apr_pstrdup(pool, arg);
 419     }
 420     values[nargs] = NULL;
 421
 422     return dbd_sqlite3_pquery(pool, sql, nrows, statement, nargs, values);
 423 }
 424
 425 static int dbd_sqlite3_pselect(apr_pool_t *pool, apr_dbd_t *sql,
 426                                apr_dbd_results_t **results,
 427                                apr_dbd_prepared_t *statement, int seek,
 428                                int nargs, const char **values)
 429 {
 430     sqlite3_stmt *stmt = statement->stmt;
 431     int i, ret, retry_count = 0;
 432     size_t num_tuples = 0;
 433     int increment = 0;
 434     apr_dbd_row_t *row = NULL;
 435     apr_dbd_row_t *lastrow = NULL;
 436     apr_dbd_column_t *column;
 437     char *hold = NULL;
 438
 439     if (sql->trans && sql->trans->errnum) {
 440         return sql->trans->errnum;
 441     }
 442
 443     apr_dbd_mutex_lock();
 444
 445     ret = sqlite3_reset(stmt);
 446     if (ret == SQLITE_OK) {
 447         int column_count;
 448
 449         for (i=0; i < nargs; i++) {
 450             sqlite3_bind_text(stmt, i + 1, values[i], strlen(values[i]),
 451                               SQLITE_STATIC);
 452         }
 453
 454         column_count = sqlite3_column_count(stmt);
 455         if (!*results) {
 456             *results = apr_pcalloc(pool, sizeof(apr_dbd_results_t));
 457         }
 458         (*results)->stmt = stmt;
 459         (*results)->sz = column_count;
 460         (*results)->random = seek;
 461         (*results)->next_row = 0;
 462         (*results)->tuples = 0;
 463         (*results)->col_names = apr_pcalloc(pool,
 464                                             column_count * sizeof(char *));
 465         do {
 466             ret = sqlite3_step(stmt);
 467             if (ret == SQLITE_BUSY) {
 468                 if (retry_count++ > MAX_RETRY_COUNT) {
 469                     ret = SQLITE_ERROR;
 470                 } else {
 471                     apr_dbd_mutex_unlock();
 472                     apr_sleep(MAX_RETRY_SLEEP);
 473                     apr_dbd_mutex_lock();
 474                 }
 475             } else if (ret == SQLITE_ROW) {
 476                 int length;
 477                 apr_dbd_column_t *col;
 478                 row = apr_palloc(pool, sizeof(apr_dbd_row_t));
 479                 row->res = *results;
 480                 increment = sizeof(apr_dbd_column_t *);
 481                 length = increment * (*results)->sz;
 482                 row->columns = apr_palloc(pool, length);
 483                 row->columnCount = column_count;
 484                 for (i = 0; i < (*results)->sz; i++) {
 485                     column = apr_palloc(pool, sizeof(apr_dbd_column_t));
 486                     row->columns[i] = column;
 487                     /* copy column name once only */
 488                     if ((*results)->col_names[i] == NULL) {
 489                       (*results)->col_names[i] =
 490                           apr_pstrdup(pool, sqlite3_column_name(stmt, i));
 491                     }
 492                     column->name = (*results)->col_names[i];
 493                     column->size = sqlite3_column_bytes(stmt, i);
 494                     column->type = sqlite3_column_type(stmt, i);
 495                     column->value = NULL;
 496                     switch (column->type) {
 497                     case SQLITE_FLOAT:
 498                     case SQLITE_INTEGER:
 499                     case SQLITE_TEXT:
 500                         hold = NULL;
 501                         hold = (char *) sqlite3_column_text(stmt, i);
 502                         if (hold) {
 503                             column->value = apr_palloc(pool, column->size + 1);
 504                             strncpy(column->value, hold, column->size + 1);
 505                         }
 506                         break;
 507                     case SQLITE_BLOB:
 508                         break;
 509                     case SQLITE_NULL:
 510                         break;
 511                     }
 512                     col = row->columns[i];
 513                 }
 514                 row->rownum = num_tuples++;
 515                 row->next_row = 0;
 516                 (*results)->tuples = num_tuples;
 517                 if ((*results)->next_row == 0) {
 518                     (*results)->next_row = row;
 519                 }
 520                 if (lastrow != 0) {
 521                     lastrow->next_row = row;
 522                 }
 523                 lastrow = row;
 524             } else if (ret == SQLITE_DONE) {
 525                 ret = SQLITE_OK;
 526             }
 527         } while (ret == SQLITE_ROW || ret == SQLITE_BUSY);
 528
 529         sqlite3_reset(stmt);
 530     }
 531     apr_dbd_mutex_unlock();
 532
 533     if (sql->trans) {
 534         sql->trans->errnum = ret;
 535     }
 536     return ret;
 537 }
 538
 539 static int dbd_sqlite3_pvselect(apr_pool_t *pool, apr_dbd_t *sql,
 540                                 apr_dbd_results_t **results,
 541                                 apr_dbd_prepared_t *statement, int seek,
 542                                 va_list args)
 543 {
 544     const char *arg, *values[QUERY_MAX_ARGS];
 545     int nargs = 0;
 546
 547     if (sql->trans && sql->trans->errnum) {
 548         return sql->trans->errnum;
 549     }
 550
 551     while (arg = va_arg(args, const char*), arg) {
 552         if (nargs >= QUERY_MAX_ARGS) {
 553             va_end(args);
 554             return -1;
 555         }
 556         values[nargs++] = apr_pstrdup(pool, arg);
 557     }
 558     values[nargs] = NULL;
 559
 560     return dbd_sqlite3_pselect(pool, sql, results, statement,
 561                                seek, nargs, values);
 562 }
 563
 564 static int dbd_sqlite3_start_transaction(apr_pool_t *pool,
 565                                          apr_dbd_t *handle,
 566                                          apr_dbd_transaction_t **trans)
 567 {
 568     int ret = 0;
 569     int nrows = 0;
 570
 571     ret = dbd_sqlite3_query(handle, &nrows, "BEGIN");
 572     if (!*trans) {
 573         *trans = apr_pcalloc(pool, sizeof(apr_dbd_transaction_t));
 574         (*trans)->handle = handle;
 575         handle->trans = *trans;
 576     }
 577
 578     return ret;
 579 }
 580
 581 static int dbd_sqlite3_end_transaction(apr_dbd_transaction_t *trans)
 582 {
 583     int ret = -1; /* ending transaction that was never started is an error */
 584     int nrows = 0;
 585
 586     if (trans) {
 587         if (trans->errnum) {
 588             trans->errnum = 0;
 589             ret = dbd_sqlite3_query(trans->handle, &nrows, "ROLLBACK");
 590         } else {
 591             ret = dbd_sqlite3_query(trans->handle, &nrows, "COMMIT");
 592         }
 593         trans->handle->trans = NULL;
 594     }
 595
 596     return ret;
 597 }
 598
 599 static apr_dbd_t *dbd_sqlite3_open(apr_pool_t *pool, const char *params)
 600 {
 601     apr_dbd_t *sql = NULL;
 602     sqlite3 *conn = NULL;
 603     int sqlres;
 604     if (!params)
 605         return NULL;
 606     sqlres = sqlite3_open(params, &conn);
 607     if (sqlres != SQLITE_OK) {
 608         sqlite3_close(conn);
 609         return NULL;
 610     }
 611     /* should we register rand or power functions to the sqlite VM? */
 612     sql = apr_pcalloc(pool, sizeof(*sql));
 613     sql->conn = conn;
 614     sql->pool = pool;
 615     sql->trans = NULL;
 616
 617     return sql;
 618 }
 619
 620 static apr_status_t dbd_sqlite3_close(apr_dbd_t *handle)
 621 {
 622     apr_dbd_prepared_t *prep = handle->prep;
 623
 624     /* finalize all prepared statements, or we'll get SQLITE_BUSY on close */
 625     while (prep) {
 626         sqlite3_finalize(prep->stmt);
 627         prep = prep->next;
 628     }
 629
 630     sqlite3_close(handle->conn);
 631     return APR_SUCCESS;
 632 }
 633
 634 static apr_status_t dbd_sqlite3_check_conn(apr_pool_t *pool,
 635                                            apr_dbd_t *handle)
 636 {
 637     return (handle->conn != NULL) ? APR_SUCCESS : APR_EGENERAL;
 638 }
 639
 640 static int dbd_sqlite3_select_db(apr_pool_t *pool, apr_dbd_t *handle,
 641                                  const char *name)
 642 {
 643     return APR_ENOTIMPL;
 644 }
 645
 646 static void *dbd_sqlite3_native(apr_dbd_t *handle)
 647 {
 648     return handle->conn;
 649 }
 650
 651 static int dbd_sqlite3_num_cols(apr_dbd_results_t *res)
 652 {
 653     return res->sz;
 654 }
 655
 656 static int dbd_sqlite3_num_tuples(apr_dbd_results_t *res)
 657 {
 658     return res->tuples;
 659 }
 660
 661 APU_DECLARE_DATA const apr_dbd_driver_t apr_dbd_sqlite3_driver = {
 662     "sqlite3",
 663     NULL,
 664     dbd_sqlite3_native,
 665     dbd_sqlite3_open,
 666     dbd_sqlite3_check_conn,
 667     dbd_sqlite3_close,
 668     dbd_sqlite3_select_db,
 669     dbd_sqlite3_start_transaction,
 670     dbd_sqlite3_end_transaction,
 671     dbd_sqlite3_query,
 672     dbd_sqlite3_select,
 673     dbd_sqlite3_num_cols,
 674     dbd_sqlite3_num_tuples,
 675     dbd_sqlite3_get_row,
 676     dbd_sqlite3_get_entry,
 677     dbd_sqlite3_error,
 678     dbd_sqlite3_escape,
 679     dbd_sqlite3_prepare,
 680     dbd_sqlite3_pvquery,
 681     dbd_sqlite3_pvselect,
 682     dbd_sqlite3_pquery,
 683     dbd_sqlite3_pselect,
 684     dbd_sqlite3_get_name
 685 };
 686 #endif