postmodern-devel

Download

postmodern-devel@common-lisp.net

June 2011

4 participants
2 discussions

[postmodern-devel] VACUUM, ANALYZE
by John Morrison 22 Jun '11

22 Jun '11

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(a)gmail.com

2 2

[postmodern-devel] using dao-class on database with column names like "addr:city"
by John Morrison 14 Jun '11

14 Jun '11

Hi All; Sorry if this is a dumb newbie question. I have a PostGIS database with column names that are rather unfortunate (and created not by me but by/via many different bits of GIS software, and they sadly seem not to have mechanisms to specify rewriting of column names on the fly during the workflow). Some of the tables have column names which have colons in them. When I try and do the first Obvious Thing I can think of (name my dao-defclass slots e.g., |addr:city| in an attempt to match the column name), get-dao complains thusly: --- cut here --- No slot named addr:city in class CM-LOCATION. DAO out of sync with table, or incorrect query used. --- cut here A trip into the debugger leads me to believe that the slot name |addr:city| in the dao-class defclass gets rewritten as "addr_city", and then the get-dao from the table with column name "addr:city" (which does not seem to likewise get passed through to-sql-name, as it IS in fact an sql name) fails. (FWIW I have never had any trouble whatsoever with normally-named columns in almost all other databases I have processed.) Another Obvious Thing to try - setting *escape-sql-names-p* around the defclass (as suggested in another mailing to this list circa 2008) didn't seem to affect things either (but that could be Pilot Error). The final Obvious Thing to try was figuring out how to set the sql-name in the slot definition, but there seemed to be no initarg or other mechanisms I could spot. If some kind soul out there would point me in the right direction, I'd greatly appreciate it. Thanks in advance, -jm p.s., and then there are the database column names (e.g., "type") that are locked in various CL packages... -- --- John Morrison --- john.nmi.morrison(a)gmail.com

4 6