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