Improved vacuumlo command - Mailing list pgsql-patches

I've improved the contributed vacuumlo command, now it behaves like all other
postgres command line utilites e.g. supports -U, -p, -h, -?, -v, password
prompt and has a "test mode". In test mode, no large objects are removed,
just reported.


Here's the patch:
--- ../../../postgresql-7.2.1-orig/contrib/vacuumlo/vacuumlo.c    Mon Sep 17
04:30:54 2001
+++ vacuumlo.c    Tue Apr 16 13:44:05 2002
@@ -12,10 +12,16 @@
  *
  *-------------------------------------------------------------------------
  */
+
+#include <pg_config.h>
 #include <stdio.h>
 #include <stdlib.h>
 #include <string.h>

+#ifdef HAVE_TERMIOS_H
+#include <termios.h>
+#endif
+
 #include <sys/types.h>
 #include <sys/stat.h>
 #include <fcntl.h>
@@ -28,24 +34,163 @@

 #define BUFSIZE            1024

-int            vacuumlo(char *, int);
+extern char *optarg;
+extern int optind, opterr, optopt;
+
+struct _param {
+        char *pg_user;
+    int  pg_prompt;
+    char *pg_port;
+    char *pg_host;
+    int  verbose;
+    int  dry_run;
+};
+
+int    vacuumlo(char *, struct _param *);
+char    *simple_prompt(const char *prompt, int , int);
+void    usage(void);
+
+
+/*
+ * simple_prompt
+ *
+ * Generalized function especially intended for reading in usernames and
+ * password interactively. Reads from /dev/tty or stdin/stderr.
+ *
+ * prompt:        The prompt to print
+ * maxlen:        How many characters to accept
+ * echo:        Set to 0 if you want to hide what is entered (for passwords)
+ *
+ * Returns a malloc()'ed string with the input (w/o trailing newline).
+ */
+static int prompt_state = 0;
+
+char *
+simple_prompt(const char *prompt, int maxlen, int echo)
+{
+    int            length;
+    char       *destination;
+    FILE       *termin,
+               *termout;
+
+#ifdef HAVE_TERMIOS_H
+    struct termios t_orig,
+                t;
+#endif
+
+    destination = (char *) malloc(maxlen + 2);
+    if (!destination)
+        return NULL;
+
+    prompt_state = 1;        /* disable SIGINT */
+
+    /*
+     * Do not try to collapse these into one "w+" mode file. Doesn't work
+     * on some platforms (eg, HPUX 10.20).
+     */
+    termin = fopen("/dev/tty", "r");
+    termout = fopen("/dev/tty", "w");
+    if (!termin || !termout)
+    {
+        if (termin)
+            fclose(termin);
+        if (termout)
+            fclose(termout);
+        termin = stdin;
+        termout = stderr;
+    }
+
+#ifdef HAVE_TERMIOS_H
+    if (!echo)
+    {
+        tcgetattr(fileno(termin), &t);
+        t_orig = t;
+        t.c_lflag &= ~ECHO;
+        tcsetattr(fileno(termin), TCSAFLUSH, &t);
+    }
+#endif
+
+    if (prompt)
+    {
+        fputs(prompt, termout);
+        fflush(termout);
+    }
+
+    if (fgets(destination, maxlen, termin) == NULL)
+        destination[0] = '\0';
+
+    length = strlen(destination);
+    if (length > 0 && destination[length - 1] != '\n')
+    {
+        /* eat rest of the line */
+        char        buf[128];
+        int            buflen;
+
+        do
+        {
+            if (fgets(buf, sizeof(buf), termin) == NULL)
+                break;
+            buflen = strlen(buf);
+        } while (buflen > 0 && buf[buflen - 1] != '\n');
+    }
+
+    if (length > 0 && destination[length - 1] == '\n')
+        /* remove trailing newline */
+        destination[length - 1] = '\0';
+
+#ifdef HAVE_TERMIOS_H
+    if (!echo)
+    {
+        tcsetattr(fileno(termin), TCSAFLUSH, &t_orig);
+        fputs("\n", termout);
+        fflush(termout);
+    }
+#endif
+
+    if (termin != stdin)
+    {
+        fclose(termin);
+        fclose(termout);
+    }
+
+    prompt_state = 0;        /* SIGINT okay again */
+
+    return destination;
+}
+


 /*
  * This vacuums LOs of one database. It returns 0 on success, -1 on failure.
  */
 int
-vacuumlo(char *database, int verbose)
+vacuumlo(char *database, struct _param *param)
 {
     PGconn       *conn;
     PGresult   *res,
-               *res2;
+           *res2;
     char        buf[BUFSIZE];
-    int            matched;
-    int            deleted;
-    int            i;
+    int        matched;
+    int        deleted;
+    int        i;
+    char            *password = NULL;
+
+        if(param->pg_prompt) {
+                password = simple_prompt("Password: ", 32, 0);
+        if(!password) {
+                fprintf(stderr, "failed to get password\n");
+                exit(1);
+        }
+    }

-    conn = PQsetdb(NULL, NULL, NULL, NULL, database);
+    conn = PQsetdbLogin( param->pg_host,
+                         param->pg_port,
+                             NULL,
+                 NULL,
+                 database,
+                 param->pg_user,
+                 password
+                       );

     /* check to see that the backend connection was successfully made */
     if (PQstatus(conn) == CONNECTION_BAD)
@@ -56,8 +201,11 @@
         return -1;
     }

-    if (verbose)
+    if (param->verbose) {
         fprintf(stdout, "Connected to %s\n", database);
+                if(param->dry_run)
+                fprintf(stdout, "Test run: no large objects will be removed!\n");
+    }

     /*
      * First we create and populate the LO temp table
@@ -132,7 +280,7 @@
         table = PQgetvalue(res, i, 0);
         field = PQgetvalue(res, i, 1);

-        if (verbose)
+        if (param->verbose)
             fprintf(stdout, "Checking %s in %s\n", field, table);

         /*
@@ -188,19 +336,22 @@
     {
         Oid            lo = atooid(PQgetvalue(res, i, 0));

-        if (verbose)
+        if (param->verbose)
         {
             fprintf(stdout, "\rRemoving lo %6u   ", lo);
             fflush(stdout);
         }

-        if (lo_unlink(conn, lo) < 0)
-        {
-            fprintf(stderr, "\nFailed to remove lo %u: ", lo);
-            fprintf(stderr, "%s", PQerrorMessage(conn));
-        }
-        else
-            deleted++;
+        if(param->dry_run == 0) {
+                if (lo_unlink(conn, lo) < 0)
+                {
+                    fprintf(stderr, "\nFailed to remove lo %u: ", lo);
+                    fprintf(stderr, "%s", PQerrorMessage(conn));
+                }
+                else
+                    deleted++;
+        } else
+                deleted++;
     }
     PQclear(res);

@@ -212,33 +363,95 @@

     PQfinish(conn);

-    if (verbose)
-        fprintf(stdout, "\rRemoved %d large objects from %s.\n",
-                deleted, database);
+    if (param->verbose)
+        fprintf(stdout, "\r%s %d large objects from %s.\n",
+                (param->dry_run?"Would remove":"Removed"), deleted, database);

     return 0;
 }

+void
+usage(void) {
+        fprintf(stdout, "vacuumlo removes unreferenced large objects from
databases\n\n");
+        fprintf(stdout, "Usage:\n  vacuumlo [options] dbname
[dbnames...]\n\n");
+        fprintf(stdout, "Options:\n");
+    fprintf(stdout, "  -v\t\tWrite a lot of output\n");
+    fprintf(stdout, "  -n\t\tDon't remove any large object, just show what would
be done\n");
+        fprintf(stdout, "  -U username\tUsername to connect as\n");
+        fprintf(stdout, "  -W\t\tPrompt for password\n");
+        fprintf(stdout, "  -h hostname\tDatabase server host\n");
+    fprintf(stdout, "  -p port\tDatabase server port\n");
+        fprintf(stdout, "  -p port\tDatabase server port\n\n");
+}
+
+
 int
 main(int argc, char **argv)
 {
-    int            verbose = 0;
-    int            arg;
     int            rc = 0;
-
-    if (argc < 2)
-    {
-        fprintf(stderr, "Usage: %s [-v] database_name [db2 ... dbn]\n",
-                argv[0]);
+    struct _param           param;
+    int                     c;
+    int                     port;
+
+    /* Parameter handling */
+        param.pg_user = NULL;
+    param.pg_prompt = 0;
+    param.pg_host = NULL;
+    param.pg_port = 0;
+        param.verbose = 0;
+    param.dry_run = 0;
+
+        while( 1 ) {
+      c = getopt(argc, argv, "?h:U:p:vnW");
+      if(c == -1)
+        break;
+
+      switch(c) {
+            case '?':
+          if(optopt == '?') {
+            usage();
+        exit(0);
+          }
+          exit(1);
+        case ':':
+          exit(1);
+            case 'v':
+          param.verbose = 1;
+          break;
+        case 'n':
+          param.dry_run = 1;
+          param.verbose = 1;
+          break;
+        case 'U':
+          param.pg_user = strdup(optarg);
+          break;
+        case 'W':
+          param.pg_prompt = 1;
+          break;
+        case 'p':
+              port = strtol(optarg, NULL, 10);
+          if( (port < 1) || (port > 65535)) {
+            fprintf(stderr, "[%s]: invalid port number '%s'\n", argv[0],
optarg);
         exit(1);
+          }
+          param.pg_port = strdup(optarg);
+          break;
+        case 'h':
+          param.pg_host = strdup(optarg);
+          break;
+      }
+    }
+
+    /* No database given? Show usage */
+    if(optind >= argc-1) {
+      fprintf(stderr, "vacuumlo: missing required argument: database name\n");
+      fprintf(stderr, "Try 'vacuumlo -?' for help.\n");
+      exit(1);
     }

-    for (arg = 1; arg < argc; arg++)
-    {
-        if (strcmp("-v", argv[arg]) == 0)
-            verbose = !verbose;
-        else
-            rc += (vacuumlo(argv[arg], verbose) != 0);
+        for(c = optind; c < argc; c++) {
+      /* Work on selected database */
+          rc += (vacuumlo(argv[c], ¶m) != 0);
     }

     return rc;