dbd/apr_dbd_pgsql.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_PGSQL
  20
  21 #include "apu_config.h"
  22
  23 #include <ctype.h>
  24 #include <stdlib.h>
  25
  26 #ifdef HAVE_LIBPQ_FE_H
  27 #include <libpq-fe.h>
  28 #elif defined(HAVE_POSTGRESQL_LIBPQ_FE_H)
  29 #include <postgresql/libpq-fe.h>
  30 #endif
  31
  32 #include "apr_strings.h"
  33 #include "apr_time.h"
  34
  35 #include "apr_dbd_internal.h"
  36
  37 #define QUERY_MAX_ARGS 40
  38
  39 struct apr_dbd_transaction_t {
  40     int errnum;
  41     apr_dbd_t *handle;
  42 };
  43
  44 struct apr_dbd_t {
  45     PGconn *conn;
  46     apr_dbd_transaction_t *trans;
  47 };
  48
  49 struct apr_dbd_results_t {
  50     int random;
  51     PGconn *handle;
  52     PGresult *res;
  53     size_t ntuples;
  54     size_t sz;
  55     size_t index;
  56 };
  57
  58 struct apr_dbd_row_t {
  59     int n;
  60     apr_dbd_results_t *res;
  61 };
  62
  63 struct apr_dbd_prepared_t {
  64     const char *name;
  65     int prepared;
  66     int nargs;
  67 };
  68
  69 #define dbd_pgsql_is_success(x) (((x) == PGRES_EMPTY_QUERY) \
  70                                  || ((x) == PGRES_COMMAND_OK) \
  71                                  || ((x) == PGRES_TUPLES_OK))
  72
  73 static apr_status_t clear_result(void *data)
  74 {
  75     PQclear(data);
  76     return APR_SUCCESS;
  77 }
  78
  79 static int dbd_pgsql_select(apr_pool_t *pool, apr_dbd_t *sql,
  80                             apr_dbd_results_t **results,
  81                             const char *query, int seek)
  82 {
  83     PGresult *res;
  84     int ret;
  85     if ( sql->trans && sql->trans->errnum ) {
  86         return sql->trans->errnum;
  87     }
  88     if (seek) { /* synchronous query */
  89         res = PQexec(sql->conn, query);
  90         if (res) {
  91             ret = PQresultStatus(res);
  92             if (dbd_pgsql_is_success(ret)) {
  93                 ret = 0;
  94             } else {
  95                 PQclear(res);
  96             }
  97         } else {
  98             ret = PGRES_FATAL_ERROR;
  99         }
 100         if (ret != 0) {
 101             if (sql->trans) {
 102                 sql->trans->errnum = ret;
 103             }
 104             return ret;
 105         }
 106         if (!*results) {
 107             *results = apr_pcalloc(pool, sizeof(apr_dbd_results_t));
 108         }
 109         (*results)->res = res;
 110         (*results)->ntuples = PQntuples(res);
 111         (*results)->sz = PQnfields(res);
 112         (*results)->random = seek;
 113         apr_pool_cleanup_register(pool, res, clear_result,
 114                                   apr_pool_cleanup_null);
 115     }
 116     else {
 117         if (PQsendQuery(sql->conn, query) == 0) {
 118             if (sql->trans) {
 119                 sql->trans->errnum = 1;
 120             }
 121             return 1;
 122         }
 123         if (*results == NULL) {
 124             *results = apr_pcalloc(pool, sizeof(apr_dbd_results_t));
 125         }
 126         (*results)->random = seek;
 127         (*results)->handle = sql->conn;
 128     }
 129     return 0;
 130 }
 131
 132 static const char *dbd_pgsql_get_name(const apr_dbd_results_t *res, int n)
 133 {
 134     return (res->res ? PQfname(res->res, n) : NULL);
 135 }
 136
 137 static int dbd_pgsql_get_row(apr_pool_t *pool, apr_dbd_results_t *res,
 138                              apr_dbd_row_t **rowp, int rownum)
 139 {
 140     apr_dbd_row_t *row = *rowp;
 141     int sequential = ((rownum >= 0) && res->random) ? 0 : 1;
 142
 143     if (row == NULL) {
 144         row = apr_palloc(pool, sizeof(apr_dbd_row_t));
 145         *rowp = row;
 146         row->res = res;
 147         row->n = sequential ? 0 : rownum;
 148     }
 149     else {
 150         if ( sequential ) {
 151             ++row->n;
 152         }
 153         else {
 154             row->n = rownum;
 155         }
 156     }
 157
 158     if (res->random) {
 159         if (row->n >= res->ntuples) {
 160             *rowp = NULL;
 161             apr_pool_cleanup_run(pool, res->res, clear_result);
 162             res->res = NULL;
 163             return -1;
 164         }
 165     }
 166     else {
 167         if (row->n >= res->ntuples) {
 168             /* no data; we have to fetch some */
 169             row->n -= res->ntuples;
 170             if (res->res != NULL) {
 171                 PQclear(res->res);
 172             }
 173             res->res = PQgetResult(res->handle);
 174             if (res->res) {
 175                 res->ntuples = PQntuples(res->res);
 176                 while (res->ntuples == 0) {
 177                     /* if we got an empty result, clear it, wait a mo, try
 178                      * again */
 179                     PQclear(res->res);
 180                     apr_sleep(100000);        /* 0.1 secs */
 181                     res->res = PQgetResult(res->handle);
 182                     if (res->res) {
 183                         res->ntuples = PQntuples(res->res);
 184                     }
 185                     else {
 186                         return -1;
 187                     }
 188                 }
 189                 if (res->sz == 0) {
 190                     res->sz = PQnfields(res->res);
 191                 }
 192             }
 193             else {
 194                 return -1;
 195             }
 196         }
 197     }
 198     return 0;
 199 }
 200
 201 static const char *dbd_pgsql_get_entry(const apr_dbd_row_t *row, int n)
 202 {
 203     return PQgetvalue(row->res->res, row->n, n);
 204 }
 205
 206 static const char *dbd_pgsql_error(apr_dbd_t *sql, int n)
 207 {
 208     return PQerrorMessage(sql->conn);
 209 }
 210
 211 static int dbd_pgsql_query(apr_dbd_t *sql, int *nrows, const char *query)
 212 {
 213     PGresult *res;
 214     int ret;
 215     if (sql->trans && sql->trans->errnum) {
 216         return sql->trans->errnum;
 217     }
 218     res = PQexec(sql->conn, query);
 219     if (res) {
 220         ret = PQresultStatus(res);
 221         if (dbd_pgsql_is_success(ret)) {
 222             /* ugh, making 0 return-success doesn't fit */
 223             ret = 0;
 224         }
 225         *nrows = atoi(PQcmdTuples(res));
 226         PQclear(res);
 227     }
 228     else {
 229         ret = PGRES_FATAL_ERROR;
 230     }
 231     if (sql->trans) {
 232         sql->trans->errnum = ret;
 233     }
 234     return ret;
 235 }
 236
 237 static const char *dbd_pgsql_escape(apr_pool_t *pool, const char *arg,
 238                                     apr_dbd_t *sql)
 239 {
 240     size_t len = strlen(arg);
 241     char *ret = apr_palloc(pool, 2*len + 2);
 242     PQescapeString(ret, arg, len);
 243     return ret;
 244 }
 245
 246 static int dbd_pgsql_prepare(apr_pool_t *pool, apr_dbd_t *sql,
 247                              const char *query, const char *label,
 248                              apr_dbd_prepared_t **statement)
 249 {
 250     char *sqlcmd;
 251     char *sqlptr;
 252     size_t length;
 253     size_t i = 0;
 254     const char *args[QUERY_MAX_ARGS];
 255     size_t alen;
 256     int ret;
 257     PGresult *res;
 258     char *pgquery;
 259     char *pgptr;
 260
 261     if (!*statement) {
 262         *statement = apr_palloc(pool, sizeof(apr_dbd_prepared_t));
 263     }
 264     (*statement)->nargs = 0;
 265     /* Translate from apr_dbd to native query format */
 266     for (sqlptr = (char*)query; *sqlptr; ++sqlptr) {
 267         if (sqlptr[0] == '%') {
 268             if (isalpha(sqlptr[1])) {
 269                 ++(*statement)->nargs;
 270             }
 271             else if (sqlptr[1] == '%') {
 272                 ++sqlptr;
 273             }
 274         }
 275     }
 276     length = strlen(query) + 1;
 277     if ((*statement)->nargs > 8) {
 278         length += (*statement)->nargs - 8;
 279     }
 280     pgptr = pgquery = apr_palloc(pool, length) ;
 281
 282     for (sqlptr = (char*)query; *sqlptr; ++sqlptr) {
 283         if ((sqlptr[0] == '%') && isalpha(sqlptr[1])) {
 284             *pgptr++ = '$';
 285             if (i < 9) {
 286                 *pgptr++ = '1' + i;
 287             }
 288             else {
 289                 *pgptr++ = '0' + ((i+1)/10);
 290                 *pgptr++ = '0' + ((i+1)%10);
 291             }
 292             switch (*++sqlptr) {
 293             case 'd':
 294                 args[i] = "integer";
 295                 break;
 296             case 's':
 297                 args[i] = "varchar";
 298                 break;
 299             default:
 300                 args[i] = "varchar";
 301                 break;
 302             }
 303             length += 1 + strlen(args[i]);
 304             ++i;
 305         }
 306         else if ((sqlptr[0] == '%') && (sqlptr[1] == '%')) {
 307             /* reduce %% to % */
 308             *pgptr++ = *sqlptr++;
 309         }
 310         else {
 311             *pgptr++ = *sqlptr;
 312         }
 313     }
 314     *pgptr = 0;
 315
 316     if (!label) {
 317         /* don't really prepare; use in execParams instead */
 318         (*statement)->prepared = 0;
 319         (*statement)->name = apr_pstrdup(pool, pgquery);
 320         return 0;
 321     }
 322     (*statement)->name = apr_pstrdup(pool, label);
 323
 324     /* length of SQL query that prepares this statement */
 325     length = 8 + strlen(label) + 2 + 4 + length + 1;
 326     sqlcmd = apr_palloc(pool, length);
 327     sqlptr = sqlcmd;
 328     memcpy(sqlptr, "PREPARE ", 8);
 329     sqlptr += 8;
 330     length = strlen(label);
 331     memcpy(sqlptr, label, length);
 332     sqlptr += length;
 333     if ((*statement)->nargs > 0) {
 334         memcpy(sqlptr, " (",2);
 335         sqlptr += 2;
 336         for (i=0; i < (*statement)->nargs; ++i) {
 337             alen = strlen(args[i]);
 338             memcpy(sqlptr, args[i], alen);
 339             sqlptr += alen;
 340             *sqlptr++ = ',';
 341         }
 342         sqlptr[-1] =  ')';
 343     }
 344     memcpy(sqlptr, " AS ", 4);
 345     sqlptr += 4;
 346     memcpy(sqlptr, pgquery, strlen(pgquery));
 347     sqlptr += strlen(pgquery);
 348     *sqlptr = 0;
 349
 350     res = PQexec(sql->conn, sqlcmd);
 351     if ( res ) {
 352         ret = PQresultStatus(res);
 353         if (dbd_pgsql_is_success(ret)) {
 354             ret = 0;
 355         }
 356         /* Hmmm, do we do this here or register it on the pool? */
 357         PQclear(res);
 358     }
 359     else {
 360         ret = PGRES_FATAL_ERROR;
 361     }
 362     (*statement)->prepared = 1;
 363
 364     return ret;
 365 }
 366
 367 static int dbd_pgsql_pquery(apr_pool_t *pool, apr_dbd_t *sql,
 368                             int *nrows, apr_dbd_prepared_t *statement,
 369                             int nargs, const char **values)
 370 {
 371     int ret;
 372     PGresult *res;
 373
 374     if (sql->trans && sql->trans->errnum) {
 375         return sql->trans->errnum;
 376     }
 377
 378     if (statement->prepared) {
 379         res = PQexecPrepared(sql->conn, statement->name, nargs, values, 0, 0,
 380                              0);
 381     }
 382     else {
 383         res = PQexecParams(sql->conn, statement->name, nargs, 0, values, 0, 0,
 384                            0);
 385     }
 386     if (res) {
 387         ret = PQresultStatus(res);
 388         if (dbd_pgsql_is_success(ret)) {
 389             ret = 0;
 390         }
 391         *nrows = atoi(PQcmdTuples(res));
 392         PQclear(res);
 393     }
 394     else {
 395         ret = PGRES_FATAL_ERROR;
 396     }
 397
 398     if (sql->trans) {
 399         sql->trans->errnum = ret;
 400     }
 401     return ret;
 402 }
 403
 404 static int dbd_pgsql_pvquery(apr_pool_t *pool, apr_dbd_t *sql,
 405                              int *nrows, apr_dbd_prepared_t *statement,
 406                              va_list args)
 407 {
 408     const char **values;
 409     int i;
 410
 411     if (sql->trans && sql->trans->errnum) {
 412         return sql->trans->errnum;
 413     }
 414
 415     values = apr_palloc(pool, sizeof(*values) * statement->nargs);
 416
 417     for (i = 0; i < statement->nargs; i++) {
 418         values[i] = apr_pstrdup(pool, va_arg(args, const char*));
 419     }
 420
 421     return dbd_pgsql_pquery(pool, sql, nrows, statement,
 422                             statement->nargs, values);
 423 }
 424
 425 static int dbd_pgsql_pselect(apr_pool_t *pool, apr_dbd_t *sql,
 426                              apr_dbd_results_t **results,
 427                              apr_dbd_prepared_t *statement,
 428                              int seek, int nargs, const char **values)
 429 {
 430     PGresult *res;
 431     int rv;
 432     int ret = 0;
 433
 434     if (sql->trans && sql->trans->errnum) {
 435         return sql->trans->errnum;
 436     }
 437
 438     if (seek) { /* synchronous query */
 439         if (statement->prepared) {
 440             res = PQexecPrepared(sql->conn, statement->name, nargs, values, 0,
 441                                  0, 0);
 442         }
 443         else {
 444             res = PQexecParams(sql->conn, statement->name, nargs, 0, values, 0,
 445                                0, 0);
 446         }
 447         if (res) {
 448             ret = PQresultStatus(res);
 449             if (dbd_pgsql_is_success(ret)) {
 450                 ret = 0;
 451             }
 452             else {
 453                 PQclear(res);
 454             }
 455         }
 456         else {
 457             ret = PGRES_FATAL_ERROR;
 458         }
 459         if (ret != 0) {
 460             if (sql->trans) {
 461                 sql->trans->errnum = ret;
 462             }
 463             return ret;
 464         }
 465         if (!*results) {
 466             *results = apr_pcalloc(pool, sizeof(apr_dbd_results_t));
 467         }
 468         (*results)->res = res;
 469         (*results)->ntuples = PQntuples(res);
 470         (*results)->sz = PQnfields(res);
 471         (*results)->random = seek;
 472         apr_pool_cleanup_register(pool, res, clear_result,
 473                                   apr_pool_cleanup_null);
 474     }
 475     else {
 476         if (statement->prepared) {
 477             rv = PQsendQueryPrepared(sql->conn, statement->name, nargs, values,
 478                                      0, 0, 0);
 479         }
 480         else {
 481             rv = PQsendQueryParams(sql->conn, statement->name, nargs, 0,
 482                                    values, 0, 0, 0);
 483         }
 484         if (rv == 0) {
 485             if (sql->trans) {
 486                 sql->trans->errnum = 1;
 487             }
 488             return 1;
 489         }
 490         if (!*results) {
 491             *results = apr_pcalloc(pool, sizeof(apr_dbd_results_t));
 492         }
 493         (*results)->random = seek;
 494         (*results)->handle = sql->conn;
 495     }
 496
 497     if (sql->trans) {
 498         sql->trans->errnum = ret;
 499     }
 500     return ret;
 501 }
 502
 503 static int dbd_pgsql_pvselect(apr_pool_t *pool, apr_dbd_t *sql,
 504                               apr_dbd_results_t **results,
 505                               apr_dbd_prepared_t *statement,
 506                               int seek, va_list args)
 507 {
 508     const char **values;
 509     int i;
 510
 511     if (sql->trans && sql->trans->errnum) {
 512         return sql->trans->errnum;
 513     }
 514
 515     values = apr_palloc(pool, sizeof(*values) * statement->nargs);
 516
 517     for (i = 0; i < statement->nargs; i++) {
 518         values[i] = apr_pstrdup(pool, va_arg(args, const char*));
 519     }
 520
 521     return dbd_pgsql_pselect(pool, sql, results, statement,
 522                              seek, statement->nargs, values) ;
 523 }
 524
 525 static int dbd_pgsql_start_transaction(apr_pool_t *pool, apr_dbd_t *handle,
 526                                        apr_dbd_transaction_t **trans)
 527 {
 528     int ret = 0;
 529     PGresult *res;
 530
 531     /* XXX handle recursive transactions here */
 532
 533     res = PQexec(handle->conn, "BEGIN TRANSACTION");
 534     if (res) {
 535         ret = PQresultStatus(res);
 536         if (dbd_pgsql_is_success(ret)) {
 537             ret = 0;
 538             if (!*trans) {
 539                 *trans = apr_pcalloc(pool, sizeof(apr_dbd_transaction_t));
 540             }
 541         }
 542         PQclear(res);
 543         (*trans)->handle = handle;
 544         handle->trans = *trans;
 545     }
 546     else {
 547         ret = PGRES_FATAL_ERROR;
 548     }
 549     return ret;
 550 }
 551
 552 static int dbd_pgsql_end_transaction(apr_dbd_transaction_t *trans)
 553 {
 554     PGresult *res;
 555     int ret = -1;                /* no transaction is an error cond */
 556     if (trans) {
 557         if (trans->errnum) {
 558             trans->errnum = 0;
 559             res = PQexec(trans->handle->conn, "ROLLBACK");
 560         }
 561         else {
 562             res = PQexec(trans->handle->conn, "COMMIT");
 563         }
 564         if (res) {
 565             ret = PQresultStatus(res);
 566             if (dbd_pgsql_is_success(ret)) {
 567                 ret = 0;
 568             }
 569             PQclear(res);
 570         }
 571         else {
 572             ret = PGRES_FATAL_ERROR;
 573         }
 574         trans->handle->trans = NULL;
 575     }
 576     return ret;
 577 }
 578
 579 static apr_dbd_t *dbd_pgsql_open(apr_pool_t *pool, const char *params)
 580 {
 581     apr_dbd_t *sql;
 582
 583     PGconn *conn = PQconnectdb(params);
 584
 585     /* if there's an error in the connect string or something we get
 586      * back a * bogus connection object, and things like PQreset are
 587      * liable to segfault, so just close it out now.  it would be nice
 588      * if we could give an indication of why we failed to connect... */
 589     if (PQstatus(conn) != CONNECTION_OK) {
 590         PQfinish(conn);
 591         return NULL;
 592     }
 593
 594     sql = apr_pcalloc (pool, sizeof (*sql));
 595
 596     sql->conn = conn;
 597
 598     return sql;
 599 }
 600
 601 static apr_status_t dbd_pgsql_close(apr_dbd_t *handle)
 602 {
 603     PQfinish(handle->conn);
 604     return APR_SUCCESS;
 605 }
 606
 607 static apr_status_t dbd_pgsql_check_conn(apr_pool_t *pool,
 608                                          apr_dbd_t *handle)
 609 {
 610     if (PQstatus(handle->conn) != CONNECTION_OK) {
 611         PQreset(handle->conn);
 612         if (PQstatus(handle->conn) != CONNECTION_OK) {
 613             return APR_EGENERAL;
 614         }
 615     }
 616     return APR_SUCCESS;
 617 }
 618
 619 static int dbd_pgsql_select_db(apr_pool_t *pool, apr_dbd_t *handle,
 620                                const char *name)
 621 {
 622     return APR_ENOTIMPL;
 623 }
 624
 625 static void *dbd_pgsql_native(apr_dbd_t *handle)
 626 {
 627     return handle->conn;
 628 }
 629
 630 static int dbd_pgsql_num_cols(apr_dbd_results_t* res)
 631 {
 632     return res->sz;
 633 }
 634
 635 static int dbd_pgsql_num_tuples(apr_dbd_results_t* res)
 636 {
 637     if (res->random) {
 638         return res->ntuples;
 639     }
 640     else {
 641         return -1;
 642     }
 643 }
 644
 645 APU_DECLARE_DATA const apr_dbd_driver_t apr_dbd_pgsql_driver = {
 646     "pgsql",
 647     NULL,
 648     dbd_pgsql_native,
 649     dbd_pgsql_open,
 650     dbd_pgsql_check_conn,
 651     dbd_pgsql_close,
 652     dbd_pgsql_select_db,
 653     dbd_pgsql_start_transaction,
 654     dbd_pgsql_end_transaction,
 655     dbd_pgsql_query,
 656     dbd_pgsql_select,
 657     dbd_pgsql_num_cols,
 658     dbd_pgsql_num_tuples,
 659     dbd_pgsql_get_row,
 660     dbd_pgsql_get_entry,
 661     dbd_pgsql_error,
 662     dbd_pgsql_escape,
 663     dbd_pgsql_prepare,
 664     dbd_pgsql_pvquery,
 665     dbd_pgsql_pvselect,
 666     dbd_pgsql_pquery,
 667     dbd_pgsql_pselect,
 668     dbd_pgsql_get_name
 669 };
 670 #endif