filename: qcons-notes.txt
time-stamp: <2007-04-11 12:14:17 ttn>

Copyright (C) 2005, 2007 Thien-Thi Nguyen

This file contains notes on effective use of module (database postgres-qcons),
distributed with Guile-PG[0].  The motivation is to document some findings
related to ETRACK[1] munging wrt DB schema maintenance, so that future munging
is easier for both myself and others.

Contents:

   1. Which columns are NOT NULL?
   2. Operator OVERLAPS.


1. Which columns are NOT NULL?
------------------------------

The immediate task at hand is to determine whether or not a table's columns
are declared "NOT NULL".  Recalling that gxrepl (from Guile-PG) has this
figured out already is the first step:

M-x gxrepl RET nr2 RET
(nr2) ,dt expenses
name    | type        |  bytes |  mod | etc
-------+++-----------+++------+++----+++------------------
i       | int4        |      4 |   -1 | NOT NULL, has defs
date    | timestamptz |      8 |   -1 | NULL ok, no defs
amount  | int4        |      4 |   -1 | NULL ok, no defs
attcode | text        | varies |   -1 | NULL ok, no defs
details | _text       | varies |   -1 | NULL ok, no defs
(5 rows)

We see that under "etc", there is "NOT NULL" and "NULL ok" information.  So
now we look at gxrepl source to find out how this was done.  The excerpt:

      (sqlsel '(("name"   . a.attname)
                ("type"   . t.typname)
                (" bytes" . (if (< 0 a.attlen)
                                (to_char a.attlen "99999")
                                "varies"))
                ("mod"    . (to_char a.atttypmod "999"))
                ("etc"    . (|| (if a.attnotnull
                                    "NOT NULL"
                                    "NULL ok")
                                ", "
                                (if a.atthasdef
                                    "has defs"
                                    "no defs"))))
              #:from
              '((c . pg_class) (a . pg_attribute) (t . pg_type))
              #:where
              `(and (= c.relname ,(symbol->string (car which)))
                    (> a.attnum 0)
                    (= a.attrelid c.oid)
                    (= a.atttypid t.oid))
              #:order-by
              '((< a.attnum)))

This is more info than we need, so we can prune the expression a bit.  We
concentrate on the output column specifications and don't touch the #:from,
#:where and #:order-by clauses.  The result is:

      (sqlsel '(("name"    . a.attname)
                ("notnull" . a.attnotnull))
              #:from
              '((c . pg_class) (a . pg_attribute) (t . pg_type))
              #:where
              `(and (= c.relname ,(symbol->string (car which)))
                    (> a.attnum 0)
                    (= a.attrelid c.oid)
                    (= a.atttypid t.oid))
              #:order-by
              '((< a.attnum)))

Actually for this particular munging, we really just want a list of columns
declared "NOT NULL", instead of the status of each column.  So we convert an
output column into a #:where subexpression.  The result is:

      (sqlsel '(("name" . a.attname))
              #:from
              '((c . pg_class) (a . pg_attribute) (t . pg_type))
              #:where
              `(and (= c.relname ,(symbol->string (car which)))
                    (> a.attnum 0)
                    (= a.attrelid c.oid)
                    (= a.atttypid t.oid)
                    a.attnotnull)
              #:order-by
              '((< a.attnum)))

Lastly, we specialize (a nice way to say "hard code" :-) the expression since
it is to be used only once.  The result is:

      (sqlsel '(("name" . a.attname))
              #:from
              '((c . pg_class) (a . pg_attribute) (t . pg_type))
              #:where
              '(and (= c.relname "expenses")
                    (> a.attnum 0)
                    (= a.attrelid c.oid)
                    (= a.atttypid t.oid)
                    a.attnotnull)
              #:order-by
              '((< a.attnum)))

Note that the `quasiquote' has changed to `quote'.  As for `sqlsel', here is
its definition excerpted from gxrepl source:

(define (sqlsel cols . rest)
  (sql-command<-trees (apply parse+make-SELECT-tree #t cols rest)))

We're cool (and resolved ;-) now because both `sql-command<-trees' and
`parse+make-SELECT-tree' are provided by (database postgresql-qcons).
Wrapping the expression in a thunk, we get:

(define (SQL/expenses-columns-NOT-NULL)
  (sqlsel '(("name" . a.attname))
          #:from
          '((c . pg_class) (a . pg_attribute) (t . pg_type))
          #:where
          '(and (= c.relname "expenses")
                (> a.attnum 0)
                (= a.attrelid c.oid)
                (= a.atttypid t.oid)
                a.attnotnull)
          #:order-by
          '((< a.attnum))))

guile> (write-line (SQL/expenses-columns-NOT-NULL))
SELECT a.attname AS "name"
FROM "pg_class" AS "c", "pg_attribute" AS "a", "pg_type" AS "t"
WHERE ((c.relname = 'expenses') AND (a.attnum > 0) AND (a.attrelid = c.oid) AND (a.atttypid = t.oid) AND a.attnotnull)
ORDER BY a.attnum ASC;


2. Operator OVERLAPS.
---------------------

The SQL OVERLAPS operator takes two "pairs", as described in the PostgreSQL
7.4.16 documentation, Section 9.8 "Date/Time Functions and Operators":

( start1, end1 ) OVERLAPS ( start2, end2 )

I was afraid I'd have to add special support in qcons, but it turns out this
can be expressed as a (normal) function, with four arguments.  For example,
here is a snippet from a gxrepl session:

,echo
,fix #:from expenses
,fsel #:where (overlaps (:: date "2006-01-01")
                        (:: interval "1 month")
                        date
                        (:: interval "1 day"))
SELECT *
FROM "expenses"
WHERE (overlaps (CAST ('2006-01-01' AS date), CAST ('1 month' AS interval), "date", CAST ('1 day' AS interval)));

So, no special kludging required.  Cool.


(THE END)



[0] http://www.gnuvola.org/software/guile-pg/
    http://www.gnuvola.org/software/guile-pg/doc/Query-Construction.html

[1] http://www.gnuvola.org/software/etrack/