granicus.if.org Git - postgresql/blob - src/bin/pg_dump/pg_backup_db.c

   1 /*-------------------------------------------------------------------------
   2  *
   3  * pg_backup_db.c
   4  *
   5  *      Implements the basic DB functions used by the archiver.
   6  *
   7  * IDENTIFICATION
   8  *        $Header: /cvsroot/pgsql/src/bin/pg_dump/pg_backup_db.c,v 1.22 2001/08/03 19:43:05 tgl Exp $
   9  *
  10  * NOTES
  11  *
  12  * Modifications - 04-Jan-2001 - pjw@rhyme.com.au
  13  *
  14  *        - Check results of PQ routines more carefully.
  15  *
  16  * Modifications - 19-Mar-2001 - pjw@rhyme.com.au
  17  *
  18  *        - Avoid forcing table name to lower case in FixupBlobXrefs!
  19  *
  20  *-------------------------------------------------------------------------
  21  */
  22
  23 #include "pg_backup.h"
  24 #include "pg_backup_archiver.h"
  25 #include "pg_backup_db.h"
  26
  27 #include <unistd.h>                             /* for getopt() */
  28 #include <ctype.h>
  29
  30 #ifdef HAVE_TERMIOS_H
  31 #include <termios.h>
  32 #endif
  33
  34 #include "libpq-fe.h"
  35 #include "libpq/libpq-fs.h"
  36 #ifndef HAVE_STRDUP
  37 #include "strdup.h"
  38 #endif
  39
  40 static const char *modulename = gettext_noop("archiver (db)");
  41
  42 static void _check_database_version(ArchiveHandle *AH, bool ignoreVersion);
  43 static PGconn *_connectDB(ArchiveHandle *AH, const char *newdbname, char *newUser);
  44 static int      _executeSqlCommand(ArchiveHandle *AH, PGconn *conn, PQExpBuffer qry, char *desc);
  45
  46
  47 /*
  48  * simple_prompt
  49  *
  50  * Generalized function especially intended for reading in usernames and
  51  * password interactively. Reads from stdin.
  52  *
  53  * prompt:              The prompt to print
  54  * maxlen:              How many characters to accept
  55  * echo:                Set to false if you want to hide what is entered (for passwords)
  56  *
  57  * Returns a malloc()'ed string with the input (w/o trailing newline).
  58  */
  59 char *
  60 simple_prompt(const char *prompt, int maxlen, bool echo)
  61 {
  62         int                     length;
  63         char       *destination;
  64
  65 #ifdef HAVE_TERMIOS_H
  66         struct termios t_orig,
  67                                 t;
  68
  69 #endif
  70
  71         destination = (char *) malloc(maxlen + 2);
  72         if (!destination)
  73                 return NULL;
  74         if (prompt)
  75                 fputs(gettext(prompt), stderr);
  76
  77 #ifdef HAVE_TERMIOS_H
  78         if (!echo)
  79         {
  80                 tcgetattr(0, &t);
  81                 t_orig = t;
  82                 t.c_lflag &= ~ECHO;
  83                 tcsetattr(0, TCSADRAIN, &t);
  84         }
  85 #endif
  86
  87         if (fgets(destination, maxlen, stdin) == NULL)
  88                 destination[0] = '\0';
  89
  90 #ifdef HAVE_TERMIOS_H
  91         if (!echo)
  92         {
  93                 tcsetattr(0, TCSADRAIN, &t_orig);
  94                 fputs("\n", stderr);
  95         }
  96 #endif
  97
  98         length = strlen(destination);
  99         if (length > 0 && destination[length - 1] != '\n')
 100         {
 101                 /* eat rest of the line */
 102                 char            buf[128];
 103                 int                     buflen;
 104
 105                 do
 106                 {
 107                         if (fgets(buf, sizeof(buf), stdin) == NULL)
 108                                 break;
 109                         buflen = strlen(buf);
 110                 } while (buflen > 0 && buf[buflen - 1] != '\n');
 111         }
 112         if (length > 0 && destination[length - 1] == '\n')
 113                 /* remove trailing newline */
 114                 destination[length - 1] = '\0';
 115
 116         return destination;
 117 }
 118
 119
 120 static int
 121 _parse_version(ArchiveHandle *AH, const char* versionString)
 122 {
 123         int                     cnt;
 124         int                     vmaj, vmin, vrev;
 125
 126         cnt = sscanf(versionString, "%d.%d.%d", &vmaj, &vmin, &vrev);
 127
 128         if (cnt < 2)
 129         {
 130                 die_horribly(AH, modulename, "unable to parse version string \"%s\"\n", versionString);
 131         }
 132
 133         if (cnt == 2)
 134                 vrev = 0;
 135
 136         return (100 * vmaj + vmin) * 100 + vrev;
 137 }
 138
 139 static void
 140 _check_database_version(ArchiveHandle *AH, bool ignoreVersion)
 141 {
 142         PGresult   *res;
 143         int                     myversion;
 144         const char *remoteversion_str;
 145         int                     remoteversion;
 146         PGconn     *conn = AH->connection;
 147
 148         myversion = _parse_version(AH, PG_VERSION);
 149
 150         res = PQexec(conn, "SELECT version();");
 151         if (!res ||
 152                 PQresultStatus(res) != PGRES_TUPLES_OK ||
 153                 PQntuples(res) != 1)
 154
 155                 die_horribly(AH, modulename, "could not get version from server: %s", PQerrorMessage(conn));
 156
 157         remoteversion_str = PQgetvalue(res, 0, 0);
 158         remoteversion = _parse_version(AH, remoteversion_str + 11);
 159
 160         PQclear(res);
 161
 162         AH->public.remoteVersion = remoteversion;
 163
 164         if (myversion != remoteversion
 165                 && (remoteversion < AH->public.minRemoteVersion || remoteversion > AH->public.maxRemoteVersion) )
 166         {
 167                 write_msg(NULL, "server version: %s, %s version: %s\n",
 168                                   remoteversion_str, progname, PG_VERSION);
 169                 if (ignoreVersion)
 170                         write_msg(NULL, "proceeding despite version mismatch\n");
 171                 else
 172                         die_horribly(AH, NULL, "aborting because of version mismatch  (Use the -i option to proceed anyway.)\n");
 173         }
 174 }
 175
 176 /*
 177  * Check if a given user is a superuser.
 178  */
 179 int
 180 UserIsSuperuser(ArchiveHandle *AH, char *user)
 181 {
 182         PQExpBuffer qry = createPQExpBuffer();
 183         PGresult   *res;
 184         int                     i_usesuper;
 185         int                     ntups;
 186         int                     isSuper;
 187
 188         /* Get the superuser setting */
 189         appendPQExpBuffer(qry, "select usesuper from pg_user where usename = '%s'", user);
 190         res = PQexec(AH->connection, qry->data);
 191
 192         if (!res)
 193                 die_horribly(AH, modulename, "null result checking superuser status of %s\n", user);
 194
 195         if (PQresultStatus(res) != PGRES_TUPLES_OK)
 196                 die_horribly(AH, modulename, "could not check superuser status of %s: %s",
 197                                          user, PQerrorMessage(AH->connection));
 198
 199         ntups = PQntuples(res);
 200
 201         if (ntups == 0)
 202                 isSuper = 0;
 203         else
 204         {
 205                 i_usesuper = PQfnumber(res, "usesuper");
 206                 isSuper = (strcmp(PQgetvalue(res, 0, i_usesuper), "t") == 0);
 207         }
 208         PQclear(res);
 209
 210         destroyPQExpBuffer(qry);
 211
 212         return isSuper;
 213 }
 214
 215 int
 216 ConnectedUserIsSuperuser(ArchiveHandle *AH)
 217 {
 218         return UserIsSuperuser(AH, PQuser(AH->connection));
 219 }
 220
 221 char *
 222 ConnectedUser(ArchiveHandle *AH)
 223 {
 224         return PQuser(AH->connection);
 225 }
 226
 227 /*
 228  * Reconnect the DB associated with the archive handle
 229  */
 230 int
 231 ReconnectDatabase(ArchiveHandle *AH, const char *newdbname, char *newUser)
 232 {
 233         PGconn     *newConn;
 234         char       *dbname;
 235
 236         if (!newdbname || (strcmp(newdbname, "-") == 0))
 237                 dbname = PQdb(AH->connection);
 238         else
 239                 dbname = (char *) newdbname;
 240
 241         /* Let's see if the request is already satisfied */
 242         if (strcmp(PQuser(AH->connection), newUser) == 0 && strcmp(newdbname, PQdb(AH->connection)) == 0)
 243                 return 1;
 244
 245         newConn = _connectDB(AH, dbname, newUser);
 246
 247         PQfinish(AH->connection);
 248         AH->connection = newConn;
 249         free(AH->username);
 250         AH->username = strdup(newUser);
 251
 252         return 1;
 253 }
 254
 255 /*
 256  * Connect to the db again.
 257  */
 258 static PGconn *
 259 _connectDB(ArchiveHandle *AH, const char *reqdb, char *requser)
 260 {
 261         int                     need_pass;
 262         PGconn     *newConn;
 263         char       *password = NULL;
 264         int                     badPwd = 0;
 265         int                     noPwd = 0;
 266         char       *newdb;
 267         char       *newuser;
 268
 269         if (!reqdb || (strcmp(reqdb, "-") == 0))
 270                 newdb = PQdb(AH->connection);
 271         else
 272                 newdb = (char *) reqdb;
 273
 274         if (!requser || (strlen(requser) == 0))
 275                 newuser = PQuser(AH->connection);
 276         else
 277                 newuser = (char *) requser;
 278
 279         ahlog(AH, 1, "connecting to database %s as user %s\n", newdb, newuser);
 280
 281         if (AH->requirePassword)
 282         {
 283                 password = simple_prompt("Password: ", 100, false);
 284                 if (password == NULL)
 285                         die_horribly(AH, modulename, "out of memory\n");
 286         }
 287
 288         do
 289         {
 290                 need_pass = false;
 291                 newConn = PQsetdbLogin(PQhost(AH->connection), PQport(AH->connection),
 292                                                            NULL, NULL, newdb,
 293                                                            newuser, password);
 294                 if (!newConn)
 295                         die_horribly(AH, modulename, "failed to reconnect to database\n");
 296
 297                 if (PQstatus(newConn) == CONNECTION_BAD)
 298                 {
 299                         noPwd = (strcmp(PQerrorMessage(newConn),
 300                                                         "fe_sendauth: no password supplied\n") == 0);
 301                         badPwd = (strncmp(PQerrorMessage(newConn),
 302                                                           "Password authentication failed for user", 39) == 0);
 303
 304                         if (noPwd || badPwd)
 305                         {
 306
 307                                 if (badPwd)
 308                                         fprintf(stderr, "Password incorrect\n");
 309
 310                                 fprintf(stderr, "Connecting to %s as %s\n",
 311                                                 PQdb(AH->connection), newuser);
 312
 313                                 need_pass = true;
 314                                 if (password)
 315                                         free(password);
 316                                 password = simple_prompt("Password: ", 100, false);
 317                         }
 318                         else
 319                                 die_horribly(AH, modulename, "could not reconnect to database: %s",
 320                                                          PQerrorMessage(newConn));
 321                 }
 322
 323         } while (need_pass);
 324
 325         if (password)
 326                 free(password);
 327
 328         return newConn;
 329 }
 330
 331
 332 /*
 333  * Make a database connection with the given parameters.  The
 334  * connection handle is returned, the parameters are stored in AHX.
 335  * An interactive password prompt is automatically issued if required.
 336  */
 337 PGconn *
 338 ConnectDatabase(Archive *AHX,
 339                                 const char *dbname,
 340                                 const char *pghost,
 341                                 const char *pgport,
 342                                 const char *username,
 343                                 const int reqPwd,
 344                                 const int ignoreVersion)
 345 {
 346         ArchiveHandle *AH = (ArchiveHandle *) AHX;
 347         char       *password = NULL;
 348         bool            need_pass = false;
 349
 350         if (AH->connection)
 351                 die_horribly(AH, modulename, "already connected to a database\n");
 352
 353         if (!dbname && !(dbname = getenv("PGDATABASE")))
 354                 die_horribly(AH, modulename, "no database name specified\n");
 355
 356         AH->dbname = strdup(dbname);
 357
 358         if (pghost != NULL)
 359                 AH->pghost = strdup(pghost);
 360         else
 361                 AH->pghost = NULL;
 362
 363         if (pgport != NULL)
 364                 AH->pgport = strdup(pgport);
 365         else
 366                 AH->pgport = NULL;
 367
 368         if (username != NULL)
 369                 AH->username = strdup(username);
 370         else
 371                 AH->username = NULL;
 372
 373         if (reqPwd)
 374         {
 375                 password = simple_prompt("Password: ", 100, false);
 376                 if (password == NULL)
 377                         die_horribly(AH, modulename, "out of memory\n");
 378                 AH->requirePassword = true;
 379         }
 380         else
 381                 AH->requirePassword = false;
 382
 383         /*
 384          * Start the connection.  Loop until we have a password if
 385          * requested by backend.
 386          */
 387         do
 388         {
 389                 need_pass = false;
 390                 AH->connection = PQsetdbLogin(AH->pghost, AH->pgport, NULL, NULL,
 391                                                                           AH->dbname, AH->username, password);
 392
 393                 if (!AH->connection)
 394                         die_horribly(AH, modulename, "failed to connect to database\n");
 395
 396                 if (PQstatus(AH->connection) == CONNECTION_BAD &&
 397                         strcmp(PQerrorMessage(AH->connection), "fe_sendauth: no password supplied\n") == 0 &&
 398                         !feof(stdin))
 399                 {
 400                         PQfinish(AH->connection);
 401                         need_pass = true;
 402                         free(password);
 403                         password = NULL;
 404                         password = simple_prompt("Password: ", 100, false);
 405                 }
 406         } while (need_pass);
 407
 408         if (password)
 409                 free(password);
 410
 411         /* check to see that the backend connection was successfully made */
 412         if (PQstatus(AH->connection) == CONNECTION_BAD)
 413                 die_horribly(AH, modulename, "connection to database \"%s\" failed: %s",
 414                                          AH->dbname, PQerrorMessage(AH->connection));
 415
 416         /* check for version mismatch */
 417         _check_database_version(AH, ignoreVersion);
 418
 419         /*
 420          * AH->currUser = PQuser(AH->connection);
 421          *
 422          * Removed because it prevented an initial \connect when dumping to SQL
 423          * in pg_dump.
 424          */
 425
 426         return AH->connection;
 427 }
 428
 429 /* Public interface */
 430 /* Convenience function to send a query. Monitors result to handle COPY statements */
 431 int
 432 ExecuteSqlCommand(ArchiveHandle *AH, PQExpBuffer qry, char *desc, bool use_blob)
 433 {
 434         if (use_blob)
 435                 return _executeSqlCommand(AH, AH->blobConnection, qry, desc);
 436         else
 437                 return _executeSqlCommand(AH, AH->connection, qry, desc);
 438 }
 439
 440 /*
 441  * Handle command execution. This is used to execute a command on more than one connection,
 442  * but the 'pgCopyIn' setting assumes the COPY commands are ONLY executed on the primary
 443  * setting...an error will be raised otherwise.
 444  */
 445 static int
 446 _executeSqlCommand(ArchiveHandle *AH, PGconn *conn, PQExpBuffer qry, char *desc)
 447 {
 448         PGresult   *res;
 449
 450         /* fprintf(stderr, "Executing: '%s'\n\n", qry->data); */
 451         res = PQexec(conn, qry->data);
 452         if (!res)
 453                 die_horribly(AH, modulename, "%s: no result from server\n", desc);
 454
 455         if (PQresultStatus(res) != PGRES_COMMAND_OK && PQresultStatus(res) != PGRES_TUPLES_OK)
 456         {
 457                 if (PQresultStatus(res) == PGRES_COPY_IN)
 458                 {
 459                         if (conn != AH->connection)
 460                                 die_horribly(AH, modulename, "COPY command executed in non-primary connection\n");
 461
 462                         AH->pgCopyIn = 1;
 463                 }
 464                 else
 465                         die_horribly(AH, modulename, "%s: %s",
 466                                                  desc, PQerrorMessage(AH->connection));
 467         }
 468
 469         PQclear(res);
 470
 471         return strlen(qry->data);
 472 }
 473
 474 /* Convenience function to send one or more queries. Monitors result to handle COPY statements */
 475 int
 476 ExecuteSqlCommandBuf(ArchiveHandle *AH, void *qryv, int bufLen)
 477 {
 478         int                     loc;
 479         int                     pos = 0;
 480         int                     sPos = 0;
 481         char       *qry = (char *) qryv;
 482         int                     isEnd = 0;
 483         char       *eos = qry + bufLen;
 484
 485         /*
 486          * fprintf(stderr, "\n\n*****\n
 487          * Buffer:\n\n%s\n*******************\n\n", qry);
 488          */
 489
 490         /* If we're in COPY IN mode, then just break it into lines and send... */
 491         if (AH->pgCopyIn)
 492         {
 493                 for (;;)
 494                 {
 495
 496                         /* Find a lf */
 497                         loc = strcspn(&qry[pos], "\n") + pos;
 498                         pos = 0;
 499
 500                         /* If no match, then wait */
 501                         if (loc >= (eos - qry))         /* None found */
 502                         {
 503                                 appendBinaryPQExpBuffer(AH->pgCopyBuf, qry, (eos - qry));
 504                                 break;
 505                         };
 506
 507                         /*
 508                          * fprintf(stderr, "Found cr at %d, prev char was %c, next was
 509                          * %c\n", loc, qry[loc-1], qry[loc+1]);
 510                          */
 511
 512                         /* Count the number of preceding slashes */
 513                         sPos = loc;
 514                         while (sPos > 0 && qry[sPos - 1] == '\\')
 515                                 sPos--;
 516
 517                         sPos = loc - sPos;
 518
 519                         /*
 520                          * If an odd number of preceding slashes, then \n was escaped
 521                          * so set the next search pos, and restart (if any left).
 522                          */
 523                         if ((sPos & 1) == 1)
 524                         {
 525                                 /* fprintf(stderr, "cr was escaped\n"); */
 526                                 pos = loc + 1;
 527                                 if (pos >= (eos - qry))
 528                                 {
 529                                         appendBinaryPQExpBuffer(AH->pgCopyBuf, qry, (eos - qry));
 530                                         break;
 531                                 }
 532                         }
 533                         else
 534                         {
 535                                 /* We got a good cr */
 536                                 qry[loc] = '\0';
 537                                 appendPQExpBuffer(AH->pgCopyBuf, "%s\n", qry);
 538                                 qry += loc + 1;
 539                                 isEnd = (strcmp(AH->pgCopyBuf->data, "\\.\n") == 0);
 540
 541                                 /*---------
 542                                  * fprintf(stderr, "Sending '%s' via
 543                                  *              COPY (at end = %d)\n\n", AH->pgCopyBuf->data, isEnd);
 544                                  *---------
 545                                  */
 546
 547                                 if (PQputline(AH->connection, AH->pgCopyBuf->data) != 0)
 548                                         die_horribly(AH, modulename, "error returned by PQputline\n");
 549
 550                                 resetPQExpBuffer(AH->pgCopyBuf);
 551
 552                                 /*
 553                                  * fprintf(stderr, "Buffer is '%s'\n",
 554                                  * AH->pgCopyBuf->data);
 555                                  */
 556
 557                                 if (isEnd)
 558                                 {
 559                                         if (PQendcopy(AH->connection) != 0)
 560                                                 die_horribly(AH, modulename, "error returned by PQendcopy\n");
 561
 562                                         AH->pgCopyIn = 0;
 563                                         break;
 564                                 }
 565
 566                         }
 567
 568                         /* Make sure we're not past the original buffer end */
 569                         if (qry >= eos)
 570                                 break;
 571
 572                 }
 573         }
 574
 575         /* We may have finished Copy In, and have a non-empty buffer */
 576         if (!AH->pgCopyIn)
 577         {
 578
 579                 /*
 580                  * The following is a mini state machine to assess then of of an
 581                  * SQL statement. It really only needs to parse good SQL, or at
 582                  * least that's the theory... End-of-statement is assumed to be an
 583                  * unquoted, un commented semi-colon.
 584                  */
 585
 586                 /*
 587                  * fprintf(stderr, "Buffer at start is: '%s'\n\n",
 588                  * AH->sqlBuf->data);
 589                  */
 590
 591                 for (pos = 0; pos < (eos - qry); pos++)
 592                 {
 593                         appendPQExpBufferChar(AH->sqlBuf, qry[pos]);
 594                         /* fprintf(stderr, " %c",qry[pos]); */
 595
 596                         switch (AH->sqlparse.state)
 597                         {
 598
 599                                 case SQL_SCAN:  /* Default state == 0, set in _allocAH */
 600
 601                                         if (qry[pos] == ';' && AH->sqlparse.braceDepth == 0)
 602                                         {
 603                                                 /* Send It & reset the buffer */
 604
 605                                                 /*
 606                                                  * fprintf(stderr, "    sending: '%s'\n\n",
 607                                                  * AH->sqlBuf->data);
 608                                                  */
 609                                                 ExecuteSqlCommand(AH, AH->sqlBuf, "could not execute query", false);
 610                                                 resetPQExpBuffer(AH->sqlBuf);
 611                                                 AH->sqlparse.lastChar = '\0';
 612                                         }
 613                                         else
 614                                         {
 615                                                 if (qry[pos] == '"' || qry[pos] == '\'')
 616                                                 {
 617                                                         /* fprintf(stderr,"[startquote]\n"); */
 618                                                         AH->sqlparse.state = SQL_IN_QUOTE;
 619                                                         AH->sqlparse.quoteChar = qry[pos];
 620                                                         AH->sqlparse.backSlash = 0;
 621                                                 }
 622                                                 else if (qry[pos] == '-' && AH->sqlparse.lastChar == '-')
 623                                                         AH->sqlparse.state = SQL_IN_SQL_COMMENT;
 624                                                 else if (qry[pos] == '*' && AH->sqlparse.lastChar == '/')
 625                                                         AH->sqlparse.state = SQL_IN_EXT_COMMENT;
 626                                                 else if (qry[pos] == '(')
 627                                                         AH->sqlparse.braceDepth++;
 628                                                 else if (qry[pos] == ')')
 629                                                         AH->sqlparse.braceDepth--;
 630
 631                                                 AH->sqlparse.lastChar = qry[pos];
 632                                         }
 633
 634                                         break;
 635
 636                                 case SQL_IN_SQL_COMMENT:
 637
 638                                         if (qry[pos] == '\n')
 639                                                 AH->sqlparse.state = SQL_SCAN;
 640                                         break;
 641
 642                                 case SQL_IN_EXT_COMMENT:
 643
 644                                         if (AH->sqlparse.lastChar == '*' && qry[pos] == '/')
 645                                                 AH->sqlparse.state = SQL_SCAN;
 646                                         break;
 647
 648                                 case SQL_IN_QUOTE:
 649
 650                                         if (!AH->sqlparse.backSlash && AH->sqlparse.quoteChar == qry[pos])
 651                                         {
 652                                                 /* fprintf(stderr,"[endquote]\n"); */
 653                                                 AH->sqlparse.state = SQL_SCAN;
 654                                         }
 655                                         else
 656                                         {
 657
 658                                                 if (qry[pos] == '\\')
 659                                                 {
 660                                                         if (AH->sqlparse.lastChar == '\\')
 661                                                                 AH->sqlparse.backSlash = !AH->sqlparse.backSlash;
 662                                                         else
 663                                                                 AH->sqlparse.backSlash = 1;
 664                                                 }
 665                                                 else
 666                                                         AH->sqlparse.backSlash = 0;
 667                                         }
 668                                         break;
 669
 670                         }
 671                         AH->sqlparse.lastChar = qry[pos];
 672                         /* fprintf(stderr, "\n"); */
 673                 }
 674
 675         }
 676
 677         return 1;
 678 }
 679
 680 void
 681 FixupBlobRefs(ArchiveHandle *AH, char *tablename)
 682 {
 683         PQExpBuffer tblQry;
 684         PGresult   *res,
 685                            *uRes;
 686         int                     i,
 687                                 n;
 688         char       *attr;
 689
 690         if (strcmp(tablename, BLOB_XREF_TABLE) == 0)
 691                 return;
 692
 693         tblQry = createPQExpBuffer();
 694
 695         appendPQExpBuffer(tblQry, "SELECT a.attname FROM pg_class c, pg_attribute a, pg_type t "
 696          " WHERE a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid "
 697                           " AND t.typname = 'oid' AND c.relname = '%s';", tablename);
 698
 699         res = PQexec(AH->blobConnection, tblQry->data);
 700         if (!res)
 701                 die_horribly(AH, modulename, "could not find oid columns of table \"%s\": %s",
 702                                          tablename, PQerrorMessage(AH->connection));
 703
 704         if ((n = PQntuples(res)) == 0)
 705         {
 706                 /* nothing to do */
 707                 ahlog(AH, 1, "no OID type columns in table %s\n", tablename);
 708         }
 709
 710         for (i = 0; i < n; i++)
 711         {
 712                 attr = PQgetvalue(res, i, 0);
 713
 714                 ahlog(AH, 1, "fixing BLOB cross-references for %s.%s\n", tablename, attr);
 715
 716                 resetPQExpBuffer(tblQry);
 717
 718                 /*
 719                  * We should use coalesce here (rather than 'exists'), but it
 720                  * seems to be broken in 7.0.2 (weird optimizer strategy)
 721                  */
 722                 appendPQExpBuffer(tblQry, "UPDATE \"%s\" SET \"%s\" = ", tablename, attr);
 723                 appendPQExpBuffer(tblQry, " (SELECT x.newOid FROM \"%s\" x WHERE x.oldOid = \"%s\".\"%s\")",
 724                                                   BLOB_XREF_TABLE, tablename, attr);
 725                 appendPQExpBuffer(tblQry, " where exists"
 726                                   "(select * from %s x where x.oldOid = \"%s\".\"%s\");",
 727                                                   BLOB_XREF_TABLE, tablename, attr);
 728
 729                 ahlog(AH, 10, "SQL: %s\n", tblQry->data);
 730
 731                 uRes = PQexec(AH->blobConnection, tblQry->data);
 732                 if (!uRes)
 733                         die_horribly(AH, modulename,
 734                                                  "could not update column \"%s\" of table \"%s\": %s",
 735                                                  attr, tablename, PQerrorMessage(AH->blobConnection));
 736
 737                 if (PQresultStatus(uRes) != PGRES_COMMAND_OK)
 738                         die_horribly(AH, modulename,
 739                                                  "error while updating column \"%s\" of table \"%s\": %s",
 740                                                  attr, tablename, PQerrorMessage(AH->blobConnection));
 741
 742                 PQclear(uRes);
 743         }
 744
 745         PQclear(res);
 746         destroyPQExpBuffer(tblQry);
 747 }
 748
 749 /**********
 750  *      Convenient SQL calls
 751  **********/
 752 void
 753 CreateBlobXrefTable(ArchiveHandle *AH)
 754 {
 755         PQExpBuffer qry = createPQExpBuffer();
 756
 757         /* IF we don't have a BLOB connection, then create one */
 758         if (!AH->blobConnection)
 759                 AH->blobConnection = _connectDB(AH, NULL, NULL);
 760
 761         ahlog(AH, 1, "creating table for BLOB cross-references\n");
 762
 763         appendPQExpBuffer(qry, "Create Temporary Table %s(oldOid oid, newOid oid);", BLOB_XREF_TABLE);
 764
 765         ExecuteSqlCommand(AH, qry, "could not create BLOB cross reference table", true);
 766
 767         resetPQExpBuffer(qry);
 768
 769         appendPQExpBuffer(qry, "Create Unique Index %s_ix on %s(oldOid)", BLOB_XREF_TABLE, BLOB_XREF_TABLE);
 770         ExecuteSqlCommand(AH, qry, "could not create index on BLOB cross reference table", true);
 771
 772         destroyPQExpBuffer(qry);
 773 }
 774
 775 void
 776 InsertBlobXref(ArchiveHandle *AH, int old, int new)
 777 {
 778         PQExpBuffer qry = createPQExpBuffer();
 779
 780         appendPQExpBuffer(qry, "Insert Into %s(oldOid, newOid) Values (%d, %d);", BLOB_XREF_TABLE, old, new);
 781
 782         ExecuteSqlCommand(AH, qry, "could not create BLOB cross reference entry", true);
 783
 784         destroyPQExpBuffer(qry);
 785 }
 786
 787 void
 788 StartTransaction(ArchiveHandle *AH)
 789 {
 790         PQExpBuffer qry = createPQExpBuffer();
 791
 792         appendPQExpBuffer(qry, "Begin;");
 793
 794         ExecuteSqlCommand(AH, qry, "could not start database transaction", false);
 795         AH->txActive = true;
 796
 797         destroyPQExpBuffer(qry);
 798 }
 799
 800 void
 801 StartTransactionXref(ArchiveHandle *AH)
 802 {
 803         PQExpBuffer qry = createPQExpBuffer();
 804
 805         appendPQExpBuffer(qry, "Begin;");
 806
 807         ExecuteSqlCommand(AH, qry,
 808                                           "could not start transaction for BLOB cross references", true);
 809         AH->blobTxActive = true;
 810
 811         destroyPQExpBuffer(qry);
 812 }
 813
 814 void
 815 CommitTransaction(ArchiveHandle *AH)
 816 {
 817         PQExpBuffer qry = createPQExpBuffer();
 818
 819         appendPQExpBuffer(qry, "Commit;");
 820
 821         ExecuteSqlCommand(AH, qry, "could not commit database transaction", false);
 822         AH->txActive = false;
 823
 824         destroyPQExpBuffer(qry);
 825 }
 826
 827 void
 828 CommitTransactionXref(ArchiveHandle *AH)
 829 {
 830         PQExpBuffer qry = createPQExpBuffer();
 831
 832         appendPQExpBuffer(qry, "Commit;");
 833
 834         ExecuteSqlCommand(AH, qry, "could not commit transaction for BLOB cross references", true);
 835         AH->blobTxActive = false;
 836
 837         destroyPQExpBuffer(qry);
 838 }