[postmodern-devel] VACUUM, ANALYZE

[John Morrison]

Hi All;

I am using PostModern for PostGIS databases, and need to periodically
VACUUM and/or ANALYZE the database.  I ended up writing SQL ops for
VACUUM and ANALYZE.  There are some caveats which I mention in case
their behavior should be changed and/or they should be rewritten to be
more elegant.  I am torn between embarrassment at the attached code
and my desire to contribute to PostModern (which I find so useful).
Obviously, I chose to brave embarrassment.  In any event, advice,
suggestions, and feedback are welcomed (in decreasing order).

(1) The syntax I use does not follow the SQL spec.  Instead of:
    VACUUM FREEZE
    VACUUM FULL FREEZE VERBOSE table0 column0 column1
I respectively do:
    (:vacuum :freeze)
    (:vacuum 'table0 'column0 'column1 :full :freeze :verbose)

Please advise if this is the Wrong Thing to do, and if so, which
arg-processing macros/helpers I should be using instead of the one I
have perhaps, er, misused.

(2) The split-on-keywords mechanism seems to enforce order of optional
SQL arguments (i.e., FREEZE must precede VERBOSE).  I am not sure that
is either necessary or acceptable, or I am missing something.

(3) The argument-sniffing at the end of the split-on-keywords is ugly.
It sniffs the args to see if there are (non-keyword)table/column
optional SQL arguments (implying a list of table, and optionally
columns), and only sticks in a (:vars ...)  if there are any.

-jm

-- 
--- John Morrison
--- john.nmi.morrison at gmail.com
-------------- next part --------------
(in-package :s-sql)

(def-sql-op :vacuum (&rest args)
  (split-on-keywords ((vars * ?)
                      (full - ?) 
                      (freeze - ?) 
                      (verbose - ?)
                      (analyze - ?)) (cond ((null args) nil)
					   ((and (listp args) (keywordp (first args))) args)
					   (t (cons :vars args)))
    `("VACUUM "
      ,@(if full '("FULL "))
      ,@(if freeze '("FREEZE "))
      ,@(if verbose '("VERBOSE "))
      ,@(if analyze '("ANALYZE "))
      ,@(sql-expand-list vars))))

#|
(sql (:vacuum))
(sql (:vacuum :full))
(sql (:vacuum :analyze))
(sql (:vacuum 't0 :full))
(sql (:vacuum 't0 'c0 'c1))
(sql (:vacuum 't0 :analyze))
(sql (:vacuum 't0 'c0 :analyze))
(sql (:vacuum :full :freeze :verbose :analyze))
(sql (:vacuum 't0 'c0 'c1 :full :freeze :verbose :analyze))
|#

(def-sql-op :analyze (&rest args)
  (split-on-keywords ((vars * ?)
                      (verbose - ?)) (cond ((null args) nil)
					   ((and (listp args) (keywordp (first args))) args)
					   (t (cons :vars args)))
    `("ANALYZE  "
      ,@(if verbose '("VERBOSE "))
      ,@(sql-expand-list vars))))

#|
(sql (:analyze))
(sql (:analyze 't0))
(sql (:analyze 't0 'c0))
(sql (:analyze 't0 'c0 'c1))
(sql (:analyze :verbose))
(sql (:analyze 't0 :verbose))
(sql (:analyze 't0 'c0 :verbose))
(sql (:analyze 't0 'c0 'c1 :verbose))
|#