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 };
  67
  68 #define dbd_pgsql_is_success(x) (((x) == PGRES_EMPTY_QUERY) \
  69                                  || ((x) == PGRES_COMMAND_OK) \
  70                                  || ((x) == PGRES_TUPLES_OK))
  71
  72 static apr_status_t clear_result(void *data)
  73 {
  74     PQclear(data);
  75     return APR_SUCCESS;
  76 }
  77
  78 static int dbd_pgsql_select(apr_pool_t *pool, apr_dbd_t *sql,
  79                             apr_dbd_results_t **results,
  80                             const char *query, int seek)
  81 {
  82     PGresult *res;
  83     int ret;
  84     if ( sql->trans && sql->trans->errnum ) {
  85         return sql->trans->errnum;
  86     }
  87     if (seek) { /* synchronous query */
  88         res = PQexec(sql->conn, query);
  89         if (res) {
  90             ret = PQresultStatus(res);
  91             if (dbd_pgsql_is_success(ret)) {
  92                 ret = 0;
  93             } else {
  94                 PQclear(res);
  95             }
  96         } else {
  97             ret = PGRES_FATAL_ERROR;
  98         }
  99         if (ret != 0) {
 100             if (sql->trans) {
 101                 sql->trans->errnum = ret;
 102             }
 103             return ret;
 104         }
 105         if (!*results) {
 106             *results = apr_pcalloc(pool, sizeof(apr_dbd_results_t));
 107         }
 108         (*results)->res = res;
 109         (*results)->ntuples = PQntuples(res);
 110         (*results)->sz = PQnfields(res);
 111         (*results)->random = seek;
 112         apr_pool_cleanup_register(pool, res, clear_result,
 113                                   apr_pool_cleanup_null);
 114     }
 115     else {
 116         if (PQsendQuery(sql->conn, query) == 0) {
 117             if (sql->trans) {
 118                 sql->trans->errnum = 1;
 119             }
 120             return 1;
 121         }
 122         if (*results == NULL) {
 123             *results = apr_pcalloc(pool, sizeof(apr_dbd_results_t));
 124         }
 125         (*results)->random = seek;
 126         (*results)->handle = sql->conn;
 127     }
 128     return 0;
 129 }
 130
 131 static const char *dbd_pgsql_get_name(const apr_dbd_results_t *res, int n)
 132 {
 133     return (res->res ? PQfname(res->res, n) : NULL);
 134 }
 135
 136 static int dbd_pgsql_get_row(apr_pool_t *pool, apr_dbd_results_t *res,
 137                              apr_dbd_row_t **rowp, int rownum)
 138 {
 139     apr_dbd_row_t *row = *rowp;
 140     int sequential = ((rownum >= 0) && res->random) ? 0 : 1;
 141
 142     if (row == NULL) {
 143         row = apr_palloc(pool, sizeof(apr_dbd_row_t));
 144         *rowp = row;
 145         row->res = res;
 146         row->n = sequential ? 0 : rownum;
 147     }
 148     else {
 149         if ( sequential ) {
 150             ++row->n;
 151         }
 152         else {
 153             row->n = rownum;
 154         }
 155     }
 156
 157     if (res->random) {
 158         if (row->n >= res->ntuples) {
 159             *rowp = NULL;
 160             apr_pool_cleanup_kill(pool, res->res, (void*)PQclear);
 161             PQclear(res->res);
 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 nargs = 0;
 257     int ret;
 258     PGresult *res;
 259     char *pgquery;
 260     char *pgptr;
 261
 262     if (!*statement) {
 263         *statement = apr_palloc(pool, sizeof(apr_dbd_prepared_t));
 264     }
 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                 ++nargs;
 270             }
 271             else if (sqlptr[1] == '%') {
 272                 ++sqlptr;
 273             }
 274         }
 275     }
 276     length = strlen(query) + 1;
 277     if (nargs > 8) {
 278         length += 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 (nargs > 0) {
 334         memcpy(sqlptr, " (",2);
 335         sqlptr += 2;
 336         for (i=0; i<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 *arg;
 409     int nargs = 0;
 410     const char *values[QUERY_MAX_ARGS];
 411
 412     if (sql->trans && sql->trans->errnum) {
 413         return sql->trans->errnum;
 414     }
 415     while ( arg = va_arg(args, const char*), arg ) {
 416         if ( nargs >= QUERY_MAX_ARGS) {
 417             va_end(args);
 418             return -1;
 419         }
 420         values[nargs++] = apr_pstrdup(pool, arg);
 421     }
 422     values[nargs] = NULL;
 423     return dbd_pgsql_pquery(pool, sql, nrows, statement, nargs, values);
 424 }
 425
 426 static int dbd_pgsql_pselect(apr_pool_t *pool, apr_dbd_t *sql,
 427                              apr_dbd_results_t **results,
 428                              apr_dbd_prepared_t *statement,
 429                              int seek, int nargs, const char **values)
 430 {
 431     PGresult *res;
 432     int rv;
 433     int ret = 0;
 434
 435     if (sql->trans && sql->trans->errnum) {
 436         return sql->trans->errnum;
 437     }
 438
 439     if (seek) { /* synchronous query */
 440         if (statement->prepared) {
 441             res = PQexecPrepared(sql->conn, statement->name, nargs, values, 0,
 442                                  0, 0);
 443         }
 444         else {
 445             res = PQexecParams(sql->conn, statement->name, nargs, 0, values, 0,
 446                                0, 0);
 447         }
 448         if (res) {
 449             ret = PQresultStatus(res);
 450             if (dbd_pgsql_is_success(ret)) {
 451                 ret = 0;
 452             }
 453             else {
 454                 PQclear(res);
 455             }
 456         }
 457         else {
 458             ret = PGRES_FATAL_ERROR;
 459         }
 460         if (ret != 0) {
 461             if (sql->trans) {
 462                 sql->trans->errnum = ret;
 463             }
 464             return ret;
 465         }
 466         if (!*results) {
 467             *results = apr_pcalloc(pool, sizeof(apr_dbd_results_t));
 468         }
 469         (*results)->res = res;
 470         (*results)->ntuples = PQntuples(res);
 471         (*results)->sz = PQnfields(res);
 472         (*results)->random = seek;
 473         apr_pool_cleanup_register(pool, res, clear_result,
 474                                   apr_pool_cleanup_null);
 475     }
 476     else {
 477         if (statement->prepared) {
 478             rv = PQsendQueryPrepared(sql->conn, statement->name, nargs, values,
 479                                      0, 0, 0);
 480         }
 481         else {
 482             rv = PQsendQueryParams(sql->conn, statement->name, nargs, 0,
 483                                    values, 0, 0, 0);
 484         }
 485         if (rv == 0) {
 486             if (sql->trans) {
 487                 sql->trans->errnum = 1;
 488             }
 489             return 1;
 490         }
 491         if (!*results) {
 492             *results = apr_pcalloc(pool, sizeof(apr_dbd_results_t));
 493         }
 494         (*results)->random = seek;
 495         (*results)->handle = sql->conn;
 496     }
 497
 498     if (sql->trans) {
 499         sql->trans->errnum = ret;
 500     }
 501     return ret;
 502 }
 503
 504 static int dbd_pgsql_pvselect(apr_pool_t *pool, apr_dbd_t *sql,
 505                               apr_dbd_results_t **results,
 506                               apr_dbd_prepared_t *statement,
 507                               int seek, va_list args)
 508 {
 509     const char *arg;
 510     int nargs = 0;
 511     const char *values[QUERY_MAX_ARGS];
 512
 513     if (sql->trans && sql->trans->errnum) {
 514         return sql->trans->errnum;
 515     }
 516
 517     while (arg = va_arg(args, const char*), arg) {
 518         if ( nargs >= QUERY_MAX_ARGS) {
 519             va_end(args);
 520             return -1;
 521         }
 522         values[nargs++] = apr_pstrdup(pool, arg);
 523     }
 524     return dbd_pgsql_pselect(pool, sql, results, statement,
 525                              seek, nargs, values) ;
 526 }
 527
 528 static int dbd_pgsql_start_transaction(apr_pool_t *pool, apr_dbd_t *handle,
 529                                        apr_dbd_transaction_t **trans)
 530 {
 531     int ret = 0;
 532     PGresult *res;
 533
 534     /* XXX handle recursive transactions here */
 535
 536     res = PQexec(handle->conn, "BEGIN TRANSACTION");
 537     if (res) {
 538         ret = PQresultStatus(res);
 539         if (dbd_pgsql_is_success(ret)) {
 540             ret = 0;
 541             if (!*trans) {
 542                 *trans = apr_pcalloc(pool, sizeof(apr_dbd_transaction_t));
 543             }
 544         }
 545         PQclear(res);
 546         (*trans)->handle = handle;
 547         handle->trans = *trans;
 548     }
 549     else {
 550         ret = PGRES_FATAL_ERROR;
 551     }
 552     return ret;
 553 }
 554
 555 static int dbd_pgsql_end_transaction(apr_dbd_transaction_t *trans)
 556 {
 557     PGresult *res;
 558     int ret = -1;                /* no transaction is an error cond */
 559     if (trans) {
 560         if (trans->errnum) {
 561             trans->errnum = 0;
 562             res = PQexec(trans->handle->conn, "ROLLBACK");
 563         }
 564         else {
 565             res = PQexec(trans->handle->conn, "COMMIT");
 566         }
 567         if (res) {
 568             ret = PQresultStatus(res);
 569             if (dbd_pgsql_is_success(ret)) {
 570                 ret = 0;
 571             }
 572             PQclear(res);
 573         }
 574         else {
 575             ret = PGRES_FATAL_ERROR;
 576         }
 577         trans->handle->trans = NULL;
 578     }
 579     return ret;
 580 }
 581
 582 static apr_dbd_t *dbd_pgsql_open(apr_pool_t *pool, const char *params)
 583 {
 584     apr_dbd_t *sql;
 585
 586     PGconn *conn = PQconnectdb(params);
 587
 588     /* if there's an error in the connect string or something we get
 589      * back a * bogus connection object, and things like PQreset are
 590      * liable to segfault, so just close it out now.  it would be nice
 591      * if we could give an indication of why we failed to connect... */
 592     if (PQstatus(conn) != CONNECTION_OK) {
 593         PQfinish(conn);
 594         return NULL;
 595     }
 596
 597     sql = apr_pcalloc (pool, sizeof (*sql));
 598
 599     sql->conn = conn;
 600
 601     return sql;
 602 }
 603
 604 static apr_status_t dbd_pgsql_close(apr_dbd_t *handle)
 605 {
 606     PQfinish(handle->conn);
 607     return APR_SUCCESS;
 608 }
 609
 610 static apr_status_t dbd_pgsql_check_conn(apr_pool_t *pool,
 611                                          apr_dbd_t *handle)
 612 {
 613     if (PQstatus(handle->conn) != CONNECTION_OK) {
 614         PQreset(handle->conn);
 615         if (PQstatus(handle->conn) != CONNECTION_OK) {
 616             return APR_EGENERAL;
 617         }
 618     }
 619     return APR_SUCCESS;
 620 }
 621
 622 static int dbd_pgsql_select_db(apr_pool_t *pool, apr_dbd_t *handle,
 623                                const char *name)
 624 {
 625     return APR_ENOTIMPL;
 626 }
 627
 628 static void *dbd_pgsql_native(apr_dbd_t *handle)
 629 {
 630     return handle->conn;
 631 }
 632
 633 static int dbd_pgsql_num_cols(apr_dbd_results_t* res)
 634 {
 635     return res->sz;
 636 }
 637
 638 static int dbd_pgsql_num_tuples(apr_dbd_results_t* res)
 639 {
 640     if (res->random) {
 641         return res->ntuples;
 642     }
 643     else {
 644         return -1;
 645     }
 646 }
 647
 648 APU_DECLARE_DATA const apr_dbd_driver_t apr_dbd_pgsql_driver = {
 649     "pgsql",
 650     NULL,
 651     dbd_pgsql_native,
 652     dbd_pgsql_open,
 653     dbd_pgsql_check_conn,
 654     dbd_pgsql_close,
 655     dbd_pgsql_select_db,
 656     dbd_pgsql_start_transaction,
 657     dbd_pgsql_end_transaction,
 658     dbd_pgsql_query,
 659     dbd_pgsql_select,
 660     dbd_pgsql_num_cols,
 661     dbd_pgsql_num_tuples,
 662     dbd_pgsql_get_row,
 663     dbd_pgsql_get_entry,
 664     dbd_pgsql_error,
 665     dbd_pgsql_escape,
 666     dbd_pgsql_prepare,
 667     dbd_pgsql_pvquery,
 668     dbd_pgsql_pvselect,
 669     dbd_pgsql_pquery,
 670     dbd_pgsql_pselect,
 671     dbd_pgsql_get_name
 672 };
 673 #endif