contrib/oid2name/oid2name.c

   1 /*
   2  * oid2name, a PostgreSQL app to map OIDs on the filesystem
   3  * to table and database names.
   4  *
   5  * Originally by
   6  * B. Palmer, bpalmer@crimelabs.net 1-17-2001
   7  *
   8  * contrib/oid2name/oid2name.c
   9  */
  10 #include "postgres_fe.h"
  11
  12 #include "catalog/pg_class_d.h"
  13 #include "common/connect.h"
  14 #include "common/logging.h"
  15 #include "common/string.h"
  16 #include "getopt_long.h"
  17 #include "libpq-fe.h"
  18 #include "pg_getopt.h"
  19
  20 /* an extensible array to keep track of elements to show */
  21 typedef struct
  22 {
  23         char      **array;
  24         int                     num;
  25         int                     alloc;
  26 } eary;
  27
  28 /* these are the opts structures for command line params */
  29 struct options
  30 {
  31         eary       *tables;
  32         eary       *oids;
  33         eary       *filenumbers;
  34
  35         bool            quiet;
  36         bool            systables;
  37         bool            indexes;
  38         bool            nodb;
  39         bool            extended;
  40         bool            tablespaces;
  41
  42         char       *dbname;
  43         char       *hostname;
  44         char       *port;
  45         char       *username;
  46         const char *progname;
  47 };
  48
  49 /* function prototypes */
  50 static void help(const char *progname);
  51 void            get_opts(int argc, char **argv, struct options *my_opts);
  52 void            add_one_elt(char *eltname, eary *eary);
  53 char       *get_comma_elts(eary *eary);
  54 PGconn     *sql_conn(struct options *my_opts);
  55 int                     sql_exec(PGconn *conn, const char *todo, bool quiet);
  56 void            sql_exec_dumpalldbs(PGconn *conn, struct options *opts);
  57 void            sql_exec_dumpalltables(PGconn *conn, struct options *opts);
  58 void            sql_exec_searchtables(PGconn *conn, struct options *opts);
  59 void            sql_exec_dumpalltbspc(PGconn *conn, struct options *opts);
  60
  61 /* function to parse command line options and check for some usage errors. */
  62 void
  63 get_opts(int argc, char **argv, struct options *my_opts)
  64 {
  65         static struct option long_options[] = {
  66                 {"dbname", required_argument, NULL, 'd'},
  67                 {"host", required_argument, NULL, 'h'},
  68                 {"host", required_argument, NULL, 'H'}, /* deprecated */
  69                 {"filenode", required_argument, NULL, 'f'},
  70                 {"indexes", no_argument, NULL, 'i'},
  71                 {"oid", required_argument, NULL, 'o'},
  72                 {"port", required_argument, NULL, 'p'},
  73                 {"quiet", no_argument, NULL, 'q'},
  74                 {"tablespaces", no_argument, NULL, 's'},
  75                 {"system-objects", no_argument, NULL, 'S'},
  76                 {"table", required_argument, NULL, 't'},
  77                 {"username", required_argument, NULL, 'U'},
  78                 {"version", no_argument, NULL, 'V'},
  79                 {"extended", no_argument, NULL, 'x'},
  80                 {"help", no_argument, NULL, '?'},
  81                 {NULL, 0, NULL, 0}
  82         };
  83
  84         int                     c;
  85         const char *progname;
  86         int                     optindex;
  87
  88         pg_logging_init(argv[0]);
  89         progname = get_progname(argv[0]);
  90
  91         /* set the defaults */
  92         my_opts->quiet = false;
  93         my_opts->systables = false;
  94         my_opts->indexes = false;
  95         my_opts->nodb = false;
  96         my_opts->extended = false;
  97         my_opts->tablespaces = false;
  98         my_opts->dbname = NULL;
  99         my_opts->hostname = NULL;
 100         my_opts->port = NULL;
 101         my_opts->username = NULL;
 102         my_opts->progname = progname;
 103
 104         if (argc > 1)
 105         {
 106                 if (strcmp(argv[1], "--help") == 0 || strcmp(argv[1], "-?") == 0)
 107                 {
 108                         help(progname);
 109                         exit(0);
 110                 }
 111                 if (strcmp(argv[1], "--version") == 0 || strcmp(argv[1], "-V") == 0)
 112                 {
 113                         puts("oid2name (PostgreSQL) " PG_VERSION);
 114                         exit(0);
 115                 }
 116         }
 117
 118         /* get opts */
 119         while ((c = getopt_long(argc, argv, "d:f:h:H:io:p:qsSt:U:x", long_options, &optindex)) != -1)
 120         {
 121                 switch (c)
 122                 {
 123                                 /* specify the database */
 124                         case 'd':
 125                                 my_opts->dbname = pg_strdup(optarg);
 126                                 break;
 127
 128                                 /* specify one filenumber to show */
 129                         case 'f':
 130                                 add_one_elt(optarg, my_opts->filenumbers);
 131                                 break;
 132
 133                                 /* host to connect to */
 134                         case 'H':                       /* deprecated */
 135                         case 'h':
 136                                 my_opts->hostname = pg_strdup(optarg);
 137                                 break;
 138
 139                                 /* also display indexes */
 140                         case 'i':
 141                                 my_opts->indexes = true;
 142                                 break;
 143
 144                                 /* specify one Oid to show */
 145                         case 'o':
 146                                 add_one_elt(optarg, my_opts->oids);
 147                                 break;
 148
 149                                 /* port to connect to on remote host */
 150                         case 'p':
 151                                 my_opts->port = pg_strdup(optarg);
 152                                 break;
 153
 154                                 /* don't show headers */
 155                         case 'q':
 156                                 my_opts->quiet = true;
 157                                 break;
 158
 159                                 /* dump tablespaces only */
 160                         case 's':
 161                                 my_opts->tablespaces = true;
 162                                 break;
 163
 164                                 /* display system tables */
 165                         case 'S':
 166                                 my_opts->systables = true;
 167                                 break;
 168
 169                                 /* specify one tablename to show */
 170                         case 't':
 171                                 add_one_elt(optarg, my_opts->tables);
 172                                 break;
 173
 174                                 /* username */
 175                         case 'U':
 176                                 my_opts->username = pg_strdup(optarg);
 177                                 break;
 178
 179                                 /* display extra columns */
 180                         case 'x':
 181                                 my_opts->extended = true;
 182                                 break;
 183
 184                         default:
 185                                 /* getopt_long already emitted a complaint */
 186                                 pg_log_error_hint("Try \"%s --help\" for more information.", progname);
 187                                 exit(1);
 188                 }
 189         }
 190
 191         if (optind < argc)
 192         {
 193                 pg_log_error("too many command-line arguments (first is \"%s\")",
 194                                          argv[optind]);
 195                 pg_log_error_hint("Try \"%s --help\" for more information.", progname);
 196                 exit(1);
 197         }
 198 }
 199
 200 static void
 201 help(const char *progname)
 202 {
 203         printf("%s helps examining the file structure used by PostgreSQL.\n\n"
 204                    "Usage:\n"
 205                    "  %s [OPTION]...\n"
 206                    "\nOptions:\n"
 207                    "  -f, --filenode=FILENODE    show info for table with given file node\n"
 208                    "  -i, --indexes              show indexes and sequences too\n"
 209                    "  -o, --oid=OID              show info for table with given OID\n"
 210                    "  -q, --quiet                quiet (don't show headers)\n"
 211                    "  -s, --tablespaces          show all tablespaces\n"
 212                    "  -S, --system-objects       show system objects too\n"
 213                    "  -t, --table=TABLE          show info for named table\n"
 214                    "  -V, --version              output version information, then exit\n"
 215                    "  -x, --extended             extended (show additional columns)\n"
 216                    "  -?, --help                 show this help, then exit\n"
 217                    "\nConnection options:\n"
 218                    "  -d, --dbname=DBNAME        database to connect to\n"
 219                    "  -h, --host=HOSTNAME        database server host or socket directory\n"
 220                    "  -H                         same as -h, deprecated option\n"
 221                    "  -p, --port=PORT            database server port number\n"
 222                    "  -U, --username=USERNAME    connect as specified database user\n"
 223                    "\nThe default action is to show all database OIDs.\n\n"
 224                    "Report bugs to <%s>.\n"
 225                    "%s home page: <%s>\n",
 226                    progname, progname, PACKAGE_BUGREPORT, PACKAGE_NAME, PACKAGE_URL);
 227 }
 228
 229 /*
 230  * add_one_elt
 231  *
 232  * Add one element to a (possibly empty) eary struct.
 233  */
 234 void
 235 add_one_elt(char *eltname, eary *eary)
 236 {
 237         if (eary->alloc == 0)
 238         {
 239                 eary      ->alloc = 8;
 240                 eary      ->array = (char **) pg_malloc(8 * sizeof(char *));
 241         }
 242         else if (eary->num >= eary->alloc)
 243         {
 244                 eary      ->alloc *= 2;
 245                 eary      ->array = (char **) pg_realloc(eary->array,
 246                                                                                                  eary->alloc * sizeof(char *));
 247         }
 248
 249         eary      ->array[eary->num] = pg_strdup(eltname);
 250         eary      ->num++;
 251 }
 252
 253 /*
 254  * get_comma_elts
 255  *
 256  * Return the elements of an eary as a (freshly allocated) single string, in
 257  * single quotes, separated by commas and properly escaped for insertion in an
 258  * SQL statement.
 259  */
 260 char *
 261 get_comma_elts(eary *eary)
 262 {
 263         char       *ret,
 264                            *ptr;
 265         int                     i,
 266                                 length = 0;
 267
 268         if (eary->num == 0)
 269                 return pg_strdup("");
 270
 271         /*
 272          * PQescapeString wants 2 * length + 1 bytes of breath space.  Add two
 273          * chars per element for the single quotes and one for the comma.
 274          */
 275         for (i = 0; i < eary->num; i++)
 276                 length += strlen(eary->array[i]);
 277
 278         ret = (char *) pg_malloc(length * 2 + 4 * eary->num);
 279         ptr = ret;
 280
 281         for (i = 0; i < eary->num; i++)
 282         {
 283                 if (i != 0)
 284                         sprintf(ptr++, ",");
 285                 sprintf(ptr++, "'");
 286                 ptr += PQescapeString(ptr, eary->array[i], strlen(eary->array[i]));
 287                 sprintf(ptr++, "'");
 288         }
 289
 290         return ret;
 291 }
 292
 293 /* establish connection with database. */
 294 PGconn *
 295 sql_conn(struct options *my_opts)
 296 {
 297         PGconn     *conn;
 298         char       *password = NULL;
 299         bool            new_pass;
 300         PGresult   *res;
 301
 302         /*
 303          * Start the connection.  Loop until we have a password if requested by
 304          * backend.
 305          */
 306         do
 307         {
 308 #define PARAMS_ARRAY_SIZE       7
 309
 310                 const char *keywords[PARAMS_ARRAY_SIZE];
 311                 const char *values[PARAMS_ARRAY_SIZE];
 312
 313                 keywords[0] = "host";
 314                 values[0] = my_opts->hostname;
 315                 keywords[1] = "port";
 316                 values[1] = my_opts->port;
 317                 keywords[2] = "user";
 318                 values[2] = my_opts->username;
 319                 keywords[3] = "password";
 320                 values[3] = password;
 321                 keywords[4] = "dbname";
 322                 values[4] = my_opts->dbname;
 323                 keywords[5] = "fallback_application_name";
 324                 values[5] = my_opts->progname;
 325                 keywords[6] = NULL;
 326                 values[6] = NULL;
 327
 328                 new_pass = false;
 329                 conn = PQconnectdbParams(keywords, values, true);
 330
 331                 if (!conn)
 332                         pg_fatal("could not connect to database %s",
 333                                          my_opts->dbname);
 334
 335                 if (PQstatus(conn) == CONNECTION_BAD &&
 336                         PQconnectionNeedsPassword(conn) &&
 337                         !password)
 338                 {
 339                         PQfinish(conn);
 340                         password = simple_prompt("Password: ", false);
 341                         new_pass = true;
 342                 }
 343         } while (new_pass);
 344
 345         /* check to see that the backend connection was successfully made */
 346         if (PQstatus(conn) == CONNECTION_BAD)
 347         {
 348                 pg_log_error("%s", PQerrorMessage(conn));
 349                 PQfinish(conn);
 350                 exit(1);
 351         }
 352
 353         res = PQexec(conn, ALWAYS_SECURE_SEARCH_PATH_SQL);
 354         if (PQresultStatus(res) != PGRES_TUPLES_OK)
 355         {
 356                 pg_log_error("could not clear search_path: %s",
 357                                          PQerrorMessage(conn));
 358                 PQclear(res);
 359                 PQfinish(conn);
 360                 exit(1);
 361         }
 362         PQclear(res);
 363
 364         /* return the conn if good */
 365         return conn;
 366 }
 367
 368 /*
 369  * Actual code to make call to the database and print the output data.
 370  */
 371 int
 372 sql_exec(PGconn *conn, const char *todo, bool quiet)
 373 {
 374         PGresult   *res;
 375
 376         int                     nfields;
 377         int                     nrows;
 378         int                     i,
 379                                 j,
 380                                 l;
 381         int                *length;
 382         char       *pad;
 383
 384         /* make the call */
 385         res = PQexec(conn, todo);
 386
 387         /* check and deal with errors */
 388         if (!res || PQresultStatus(res) > 2)
 389         {
 390                 pg_log_error("query failed: %s", PQerrorMessage(conn));
 391                 pg_log_error_detail("Query was: %s", todo);
 392
 393                 PQclear(res);
 394                 PQfinish(conn);
 395                 exit(1);
 396         }
 397
 398         /* get the number of fields */
 399         nrows = PQntuples(res);
 400         nfields = PQnfields(res);
 401
 402         /* for each field, get the needed width */
 403         length = (int *) pg_malloc(sizeof(int) * nfields);
 404         for (j = 0; j < nfields; j++)
 405                 length[j] = strlen(PQfname(res, j));
 406
 407         for (i = 0; i < nrows; i++)
 408         {
 409                 for (j = 0; j < nfields; j++)
 410                 {
 411                         l = strlen(PQgetvalue(res, i, j));
 412                         if (l > length[j])
 413                                 length[j] = strlen(PQgetvalue(res, i, j));
 414                 }
 415         }
 416
 417         /* print a header */
 418         if (!quiet)
 419         {
 420                 for (j = 0, l = 0; j < nfields; j++)
 421                 {
 422                         fprintf(stdout, "%*s", length[j] + 2, PQfname(res, j));
 423                         l += length[j] + 2;
 424                 }
 425                 fprintf(stdout, "\n");
 426                 pad = (char *) pg_malloc(l + 1);
 427                 memset(pad, '-', l);
 428                 pad[l] = '\0';
 429                 fprintf(stdout, "%s\n", pad);
 430                 free(pad);
 431         }
 432
 433         /* for each row, dump the information */
 434         for (i = 0; i < nrows; i++)
 435         {
 436                 for (j = 0; j < nfields; j++)
 437                         fprintf(stdout, "%*s", length[j] + 2, PQgetvalue(res, i, j));
 438                 fprintf(stdout, "\n");
 439         }
 440
 441         /* cleanup */
 442         PQclear(res);
 443         free(length);
 444
 445         return 0;
 446 }
 447
 448 /*
 449  * Dump all databases.  There are no system objects to worry about.
 450  */
 451 void
 452 sql_exec_dumpalldbs(PGconn *conn, struct options *opts)
 453 {
 454         char            todo[1024];
 455
 456         /* get the oid and database name from the system pg_database table */
 457         snprintf(todo, sizeof(todo),
 458                          "SELECT d.oid AS \"Oid\", datname AS \"Database Name\", "
 459                          "spcname AS \"Tablespace\" FROM pg_catalog.pg_database d JOIN pg_catalog.pg_tablespace t ON "
 460                          "(dattablespace = t.oid) ORDER BY 2");
 461
 462         sql_exec(conn, todo, opts->quiet);
 463 }
 464
 465 /*
 466  * Dump all tables, indexes and sequences in the current database.
 467  */
 468 void
 469 sql_exec_dumpalltables(PGconn *conn, struct options *opts)
 470 {
 471         char            todo[1024];
 472         char       *addfields = ",c.oid AS \"Oid\", nspname AS \"Schema\", spcname as \"Tablespace\" ";
 473
 474         snprintf(todo, sizeof(todo),
 475                          "SELECT pg_catalog.pg_relation_filenode(c.oid) as \"Filenode\", relname as \"Table Name\" %s "
 476                          "FROM pg_catalog.pg_class c "
 477                          "      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace "
 478                          "      LEFT JOIN pg_catalog.pg_database d ON d.datname = pg_catalog.current_database(),"
 479                          "      pg_catalog.pg_tablespace t "
 480                          "WHERE relkind IN (" CppAsString2(RELKIND_RELATION) ","
 481                          CppAsString2(RELKIND_MATVIEW) "%s%s) AND "
 482                          "      %s"
 483                          "              t.oid = CASE"
 484                          "                      WHEN reltablespace <> 0 THEN reltablespace"
 485                          "                      ELSE dattablespace"
 486                          "              END "
 487                          "ORDER BY relname",
 488                          opts->extended ? addfields : "",
 489                          opts->indexes ? "," CppAsString2(RELKIND_INDEX) "," CppAsString2(RELKIND_SEQUENCE) : "",
 490                          opts->systables ? "," CppAsString2(RELKIND_TOASTVALUE) : "",
 491                          opts->systables ? "" : "n.nspname NOT IN ('pg_catalog', 'information_schema') AND n.nspname !~ '^pg_toast' AND");
 492
 493         sql_exec(conn, todo, opts->quiet);
 494 }
 495
 496 /*
 497  * Show oid, filenumber, name, schema and tablespace for each of the
 498  * given objects in the current database.
 499  */
 500 void
 501 sql_exec_searchtables(PGconn *conn, struct options *opts)
 502 {
 503         char       *todo;
 504         char       *qualifiers,
 505                            *ptr;
 506         char       *comma_oids,
 507                            *comma_filenumbers,
 508                            *comma_tables;
 509         bool            written = false;
 510         char       *addfields = ",c.oid AS \"Oid\", nspname AS \"Schema\", spcname as \"Tablespace\" ";
 511
 512         /* get tables qualifiers, whether names, filenumbers, or OIDs */
 513         comma_oids = get_comma_elts(opts->oids);
 514         comma_tables = get_comma_elts(opts->tables);
 515         comma_filenumbers = get_comma_elts(opts->filenumbers);
 516
 517         /* 80 extra chars for SQL expression */
 518         qualifiers = (char *) pg_malloc(strlen(comma_oids) + strlen(comma_tables) +
 519                                                                         strlen(comma_filenumbers) + 80);
 520         ptr = qualifiers;
 521
 522         if (opts->oids->num > 0)
 523         {
 524                 ptr += sprintf(ptr, "c.oid IN (%s)", comma_oids);
 525                 written = true;
 526         }
 527         if (opts->filenumbers->num > 0)
 528         {
 529                 if (written)
 530                         ptr += sprintf(ptr, " OR ");
 531                 ptr += sprintf(ptr, "pg_catalog.pg_relation_filenode(c.oid) IN (%s)",
 532                                            comma_filenumbers);
 533                 written = true;
 534         }
 535         if (opts->tables->num > 0)
 536         {
 537                 if (written)
 538                         ptr += sprintf(ptr, " OR ");
 539                 sprintf(ptr, "c.relname ~~ ANY (ARRAY[%s])", comma_tables);
 540         }
 541         free(comma_oids);
 542         free(comma_tables);
 543         free(comma_filenumbers);
 544
 545         /* now build the query */
 546         todo = psprintf("SELECT pg_catalog.pg_relation_filenode(c.oid) as \"Filenode\", relname as \"Table Name\" %s\n"
 547                                         "FROM pg_catalog.pg_class c\n"
 548                                         "       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
 549                                         "       LEFT JOIN pg_catalog.pg_database d ON d.datname = pg_catalog.current_database(),\n"
 550                                         "       pg_catalog.pg_tablespace t\n"
 551                                         "WHERE relkind IN (" CppAsString2(RELKIND_RELATION) ","
 552                                         CppAsString2(RELKIND_MATVIEW) ","
 553                                         CppAsString2(RELKIND_INDEX) ","
 554                                         CppAsString2(RELKIND_SEQUENCE) ","
 555                                         CppAsString2(RELKIND_TOASTVALUE) ") AND\n"
 556                                         "               t.oid = CASE\n"
 557                                         "                       WHEN reltablespace <> 0 THEN reltablespace\n"
 558                                         "                       ELSE dattablespace\n"
 559                                         "               END AND\n"
 560                                         "  (%s)\n"
 561                                         "ORDER BY relname\n",
 562                                         opts->extended ? addfields : "",
 563                                         qualifiers);
 564
 565         free(qualifiers);
 566
 567         sql_exec(conn, todo, opts->quiet);
 568 }
 569
 570 void
 571 sql_exec_dumpalltbspc(PGconn *conn, struct options *opts)
 572 {
 573         char            todo[1024];
 574
 575         snprintf(todo, sizeof(todo),
 576                          "SELECT oid AS \"Oid\", spcname as \"Tablespace Name\"\n"
 577                          "FROM pg_catalog.pg_tablespace");
 578
 579         sql_exec(conn, todo, opts->quiet);
 580 }
 581
 582 int
 583 main(int argc, char **argv)
 584 {
 585         struct options *my_opts;
 586         PGconn     *pgconn;
 587
 588         my_opts = (struct options *) pg_malloc(sizeof(struct options));
 589
 590         my_opts->oids = (eary *) pg_malloc(sizeof(eary));
 591         my_opts->tables = (eary *) pg_malloc(sizeof(eary));
 592         my_opts->filenumbers = (eary *) pg_malloc(sizeof(eary));
 593
 594         my_opts->oids->num = my_opts->oids->alloc = 0;
 595         my_opts->tables->num = my_opts->tables->alloc = 0;
 596         my_opts->filenumbers->num = my_opts->filenumbers->alloc = 0;
 597
 598         /* parse the opts */
 599         get_opts(argc, argv, my_opts);
 600
 601         if (my_opts->dbname == NULL)
 602         {
 603                 my_opts->dbname = "postgres";
 604                 my_opts->nodb = true;
 605         }
 606         pgconn = sql_conn(my_opts);
 607
 608         /* display only tablespaces */
 609         if (my_opts->tablespaces)
 610         {
 611                 if (!my_opts->quiet)
 612                         printf("All tablespaces:\n");
 613                 sql_exec_dumpalltbspc(pgconn, my_opts);
 614
 615                 PQfinish(pgconn);
 616                 exit(0);
 617         }
 618
 619         /* display the given elements in the database */
 620         if (my_opts->oids->num > 0 ||
 621                 my_opts->tables->num > 0 ||
 622                 my_opts->filenumbers->num > 0)
 623         {
 624                 if (!my_opts->quiet)
 625                         printf("From database \"%s\":\n", my_opts->dbname);
 626                 sql_exec_searchtables(pgconn, my_opts);
 627
 628                 PQfinish(pgconn);
 629                 exit(0);
 630         }
 631
 632         /* no elements given; dump the given database */
 633         if (my_opts->dbname && !my_opts->nodb)
 634         {
 635                 if (!my_opts->quiet)
 636                         printf("From database \"%s\":\n", my_opts->dbname);
 637                 sql_exec_dumpalltables(pgconn, my_opts);
 638
 639                 PQfinish(pgconn);
 640                 exit(0);
 641         }
 642
 643         /* no database either; dump all databases */
 644         if (!my_opts->quiet)
 645                 printf("All databases:\n");
 646         sql_exec_dumpalldbs(pgconn, my_opts);
 647
 648         PQfinish(pgconn);
 649         return 0;
 650 }