- postmodern-devel - mailman3.common-lisp.net

Fwd: How to connect cl+ssl with host ca verification?
by Tim Hawes 28 Mar '22

28 Mar '22

Sorry Sabra, I didn't intend to reply to you directly. ---------- Forwarded message --------- From: Tim Hawes <trhawes(a)gmail.com> Date: Mon, Mar 21, 2022 at 9:35 PM Subject: Re: How to connect cl+ssl with host ca verification? To: Sabra Crolleton <sabra.crolleton(a)gmail.com> That works, but it looks like the root ca has to be added to the system. If using psql I could specify where all the certs I want to use to connect with like so: $>psql "port=5432 host=localhost user=postgres sslcert=./test/client.crt sslkey=./test/client.key sslrootcert=./test/server.crt sslmode=verify-full" Is there an equivalent in Postmodern? On Mon, Mar 21, 2022 at 8:36 PM Sabra Crolleton <sabra.crolleton(a)gmail.com> wrote: > The cl-postgres (open-database ...) function has :use-ssl as a key > parameter where the parameter values: > > - :try means if the server supports it > - :require means use provided ssl certificate with no verification > - :yes means verify that the server cert is issued by a trusted CA, > but does not verify the server hostname > - :full means expect a CA-signed cert for the supplied hostname and > verify the server hostname > > This is mirrored in the postmodern (connect ...) function. > > If I understand your question correctly, you want to use :full as the > parameter passed to :use-ssl. E.g. > >> (connect "test_db" "test-user" "test-password" "192.168.5.223" >> :port 5434 :pooled-p t :use-ssl :full) >> >> But maybe I am not understanding your question correctly. > > Sabra > > On Mon, Mar 21, 2022 at 7:50 PM Tim Hawes <trhawes(a)gmail.com> wrote: > >> I am not finding any helpful information in how to connect to PostgreSQL >> using client key/client cert and a root ca for verifying the host with >> Postmodern. Can someone send me an example? >> >

2 5

How to connect cl+ssl with host ca verification?
by Tim Hawes 21 Mar '22

21 Mar '22

I am not finding any helpful information in how to connect to PostgreSQL using client key/client cert and a root ca for verifying the host with Postmodern. Can someone send me an example?

1 0

Postmodern Query Macro and binary parameters
by Sabra Crolleton 07 Jun '21

07 Jun '21

There has been a long standing issue about not having the ability to pass parameters to Postgresql in binary format. Currently all parameters are passed as text and Postgresql figures out what type they should be. Cyrus raised the question about whether passing parameters as binary rather than text should be set globally or on a query by query basis. He also raises the question of whether the QUERY macro could be more efficiently re-written. Your thoughts are requested. Sabra

1 0

loading problem
by Jans Aasman 30 Nov '20

30 Nov '20

Hi: this is my first time posting here so I have no idea whom to address this to. Quicklisp was my friend in downloading and installing postmodern in a our case sensitive ACL 10.1 SMP The only problem I ran into was that in the file normalize-backend.lisp there was one upper case DO and one upper case WHEN. After fixing that I could load the whole thing. Question: could this be fixed in your source somewhere? Cheers, Jans [package uax-15]..; While file-compiling #'compose-hangul in #P"/home/test/quicklisp/dists/quicklisp/software/uax-15-20200325-git/src/normalize-backend.lisp" ; starting at file character position 563: Error: DO is an unknown keyword in LOOP macro. Current LOOP context: DO (tagbody (when (<= 0 l-index (1- l-count)) (let ((v-index (- ch v-base))) (when (<= 0 v-index (1- v-count)) (setf last (+ s-base (* (+ (* l-index v-count) v-index) t-count))) (setf (car new-chars) last) (go :end)))) (when (and (<= 0 s-index (1- s-count)) (zerop (mod s-index t-count))) (let ((t-index (- ch t-base))) (when (< 0 t-index t-count) (setf last (+ last t-index)) (setf (car new-chars) last) (go :end)))) (push (setf last ch) new-chars) :end). [condition type: program-error] .; While file-compiling #'compose in #P"/home/test/quicklisp/dists/quicklisp/software/uax-15-20200325-git/src/normalize-backend.lisp" ; starting at file character position 3336: Error: WHEN is an unknown keyword in LOOP macro. Current LOOP context: WHEN ch-left. [condition type: program-error] Restart actions (select using :continue): 0: retry the compilation of /home/test/quicklisp/dists/quicklisp/software/uax-15-20200325-git/src/normalize-backend.lisp 1: continue compiling /home/test/quicklisp/dists/quicklisp/software/uax-15-20200325-git/src/normalize-backend.lisp but generate no output file 2: Retry compiling #<cl-source-file "uax-15" "src" "normalize-backend">. 3: Continue, treating compiling #<cl-source-file "uax-15" "src" "normalize-backend"> as having been successful. 4: Retry ASDF operation. 5: Retry ASDF operation after resetting the configuration. 6: Give up on "postmodern" 7: Return to Top Level (an "abort" restart). 8: Abort entirely from this (lisp) process. [1] uax-15(7): :cont 0 ............................... [package usocket]................................. [package cl-postgres].............................

1 0

How to create dao class with identity column as primary key?
by Timo Myyrä 10 Jun '20

10 Jun '20

Hi, I'm learning to use postmodern as part of my hobby project but I've hit a small bump in the process. I can have the identity column defined for table without dao with something like: (s-sql:sql (:create-table (:if-not-exists 'user) ((id :type int :identity-always t) (username :type text) (:primary-key id))) But I intent to use dao classes so it would be nice to have identity column specified as part to defclass slot options. Is there some way to create dao class with identity column? Br, Timo M

2 5

New date/time integration for cl-postgres
by Chaitanya Gupta 19 Feb '19

19 Feb '19

Hi, I created a new date/time integration for cl-postgres. https://github.com/chaitanyagupta/cl-postgres-datetime There's nothing fancy -- it just uses local-time to parse timestamptz and simple-date for other types (which don't use a timezone). Why? Because neither local-time nor simple-date alone work well with all the date/time types that Postgres supports. local-time's TIMESTAMP is a natural fit for timestamptz, and while non-timezone types like timestamp and date could be parsed into it, the user has to remember to always use +UTC-ZONE+ when decoding such a timestamp (the values of decoded components may mismatch with the original if the default time zone were different from UTC). simple-date has no concept of time zones, so while it works well for every type that doesn't need a timezone, it fails badly when it comes to timestamptz. Hope someone finds it useful. Chaitanya --- lisper.in deftask.com

2 5

Does anyone use the hstore operators in s-sql?
by Sabra Crolleton 23 Oct '18

23 Oct '18

I am looking at the hstore operators in s-sql. Does anyone use them? If so, could you send examples of how you are using them? I want to make sure that if I touch those operators, I do not accidentally break anyone's code. Sabra

2 2

:create-table extension
by Steven Nunez 12 Feb '18

12 Feb '18

Greetings all, I am updating the :create-table macro to work with Greenplum's CREATE TABLE syntax. For the most part, this is identical except for two important table attributes: DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY These tell Greenplum how to distribute the table across the cluster, and getting these right is critical for good performance. Here is a SQL example using the FAA dataset: create table faa.d_airports ( AirportID integer, Name text, City text, Country text, airport_code text, ICOA_code text, Latitude float8, Longitude float8, Altitude float8, TimeZoneOffset float, DST_Flag text , TZ text ) distributed by (airport_code); The challenge is in emitting the 'distributed by (...)' or 'distributed randomly' after the closing parenthesis in the SQL. The S-SQL macro, with the correct location is: (def-sql-op :create-table (name (&rest columns) &rest options) (when (null columns) (sql-error "No columns defined for table ~A." name)) `("CREATE TABLE " ,(to-sql-name name) " (" ,@(loop :for ((column-name . args) . rest) :on columns :append (expand-table-column column-name args) :if rest :collect ", ") ,@(loop :for ((option . args)) :on options :collect ", " :append (expand-table-constraint option args)) ")" ; DISTRIBUTED BY needs to go here )) Initially I thought I could modify expand-table-constraints in a way that would do the trick, but I could not find a way to do this without either hardcoding these two cases or modifying the function signature. Modifying the signature of the :create-table macro seems the best choice, and then add another invocation of expand-table-constraint after the closing parentheses, but that is a breaking change. Alternatively we could have a :create-gp-table, but I would rather not diverge. So, I am here to ask: am I missing something obvious? If not, what is the consensus for moving forward? I supposed I would be most in favor of modifying the signature for :create-table. Regards, Steve P.S. There is also an open issue for create table that might have a need for something similar. I do not know, but it is worth considering both of them whilst making the change.

1 0

test for the mailing list
by Sabra Crolleton 11 Feb '18

11 Feb '18

This is just a test.

1 0

Supporting ~/.pgpass?
by Johann 'Myrkraverk' Oskarsson 02 Sep '15

02 Sep '15

Hi all, I've previously blogged one naive parser for ~/.pgpass, at http://www.myrkraverk.com/blog/2015/03/postgresqls-pgpass-parser-in-common-… Is it possible to incorporate that, or a better parser, into Postmodern? And thank you, those who answered my question about (:as 'tbl 't), -- Johann I'm not from the internet, I just work there.

2 3

S-SQL table aliases?
by Johann 'Myrkraverk' Oskarsson 21 Aug '15

21 Aug '15

Hi, Does the S-SQL syntax allow table aliases? I haven't come across examples, neither in the official docs nor the example list on sabraonthehill. This is what I mean: SELECT t.col FROM tbl t; -- Johann I'm not from the internet, I just work there.

3 2

PostgreSQL date type as bigint in Postmodern
by Rel 20 Jul '15

20 Jul '15

Hello, regarding the type date from PostgreSQL, when I query th DB with Postmodern like (query "select * from es"), a bigint is returned for date-columns (e.g. 3586982400 for "2013-09-01"). I assumed that this is a unix timestamp, but when I convert 3586982400 with a tool back to a normal date format it says "09/01/2083 @ 12:00am (UTC)". Can somebody bring some light to this? Thank you!

2 2

Impossible to specify different port number in open-database with CCL1.10
by Renzo Orsini 21 May '15

21 May '15

Using CCL on a Mac (Version 1.10-r16196 (DarwinX8664)), when I try to open a connection specifying a port: (cl-postgres:open-database “database” "user” "pwd" "localhost" :port 5434) the following error is raised: value :PORT is not an integer from 1 to 65535 ((INTEGER 1 65535)) But even if I try to coerce it explicitly, with something like: (cl-postgres:open-database "database" "user" "pwd" "localhost" :port (coerce 5434 '(integer 1 65535))) the same error is raised. Maybe this depends on CCL, since when I write to the REPL: (type-of (coerce 5434 '(integer 1 65535))) the answer is: (INTEGER 0 1152921504606846975) So, may I suggest to change the parameter check to perform an explicit test on the limits? Thank you very much for your attention Renzo Orsini -- "Nota automatica aggiunta dal sistema di posta. Destina il 5 per mille per sostenere con borse di studio gli studenti meritevoli di Ca' Foscari. E' un atto volontario, non costa nulla e non sostituisce l'8 per mille. Scegli Ca' Foscari: codice fiscale 80007720271 Please note that the above message is addressed only to individuals filing Italian income tax returns."

2 2

Beginner's question on enumerated types
by Gitsis Christos 19 Mar '14

19 Mar '14

Hello, I have recently started using postmodern in a project. I need to store enum values (of many different types) in a class. I have declared a LISP type (deftype season-type () '(member winter spring summer autumn)) and I have functions that convert the above symbols to the numbers 0..3 and vice-versa, if needed. -- I have tried to emulate enum types with (defclass my-type () ((id :initarg :id :col-type bigint) (season :initarg :season :col-type smallint)) (:metaclass dao-class) (:keys id)) CREATE TABLE valid_seasons ( id SERIAL PRIMARY KEY NOT NULL, season TEXT ); INSERT INTO valid_seasons (seasons) VALUES ("WINTER"), ("SPRING"), ("SUMMER"), ("AUTUMN"); and then like (deftable season (!dao-def) (!foreign 'valid-seasons 'id 'season)) I would like the class my-type to have the symbols of the seasons (winter, spring, etc) stored in its slot when I am in the land of lisp and not to be forced to store integer numbers, which should only be used in the land of the database. What is the normal way to achieve that in postmodern? -- PostgreSQL offers an enum type, so I tried using it in postmodern (:create-enum 'seasondbtype (list "WINTER" "SPRING" "SUMMER" "AUTUMN")) (defclass my-type () ((id :initarg :id :col-type bigint) (season :initarg :season :col-type seasondbtype)) (:metaclass dao-class) (:keys id)) But then again I have to store strings inside my-type.season, not symbols. I have added something like (defmethod initialize-instance :after ((object my-type) &key) (dolist (slot-name '(season)) (setf (slot-value object slot-name) (symbol-name (slot-value object slot-name))))), so that I can call make-instance with a symbol of season-type as an argument. But this is only for storing objects to the database, I have not gotten around to retrieving yet, and I think initialize-instance will be called there as well, so I will have to fix it. -- CL-SQL offers two slot options called :db-reader :db-writer where you can specify functions which "take one argument, the value from the database, and return the value that should be put into the slot" and "take one argument, the value of the slot, and return the value that should be put into the database" respectively. Is there something similar in postmodern? Does the macro with-column-writers or the parameter *custom-column-writers* have anything to do with that? If yes, can I have an example of usage? I think that might solve my problem. In short, in what way do postmodern's users deal with enumerated types, and in particular how do they manage to store symbols in CLOS instances and the corresponding strings (or even numbers) in PostgreSQL? Regards, Christos Gitsis

1 0

S-SQL and nested joins -- correct version
by Eli Naeher 28 Feb '14

28 Feb '14

Hello, My apologies, I sent my previous email by mistake before I'd finished composing it. Here is the completed email: Is there an S-SQL expression which will produce nested join expressions like this one: select * from table_1 inner join table_2 inner join table_3 on table_2.c = table_3.d on table_1.a = test_2.b; (This form is described in section 7.2.1.1 of the Postgres documentation, which says, "Joins of all types can be chained together or nested: either or both T1 and T2 can be joined tables.") I know I can do this: (:select '* :from 'table-1 :inner-join 'table-2 :on (:= 'table-1.a 'table-2.b) :inner-join 'table-3 :on (:= 'table-2.c 'table-3.d)) which produces a chained join expression: (SELECT * FROM table_1 INNER JOIN table_2 ON (table_1.a = table_2.b) INNER JOIN table_3 ON (table_2.c = table_3.d)) However, these chained join expressions behave differently than nested ones when mixing different types of joins (inner with outer, etc.), and I need the behavior of the nested expression. I tried this: (:select '* :from 'table-1 :inner-join 'table-2 :inner-join 'table-3 :on (:= 'table-2.c 'table-3.d) :on (:= 'table-1.a 'table-2.b)) But it produces the error "Incorrect join form in select." Am I missing the correct syntax, or is this unsupported right now? Thank you, -Eli

2 1

S-SQL and nested joins
by Eli Naeher 27 Feb '14

27 Feb '14

Hello, Is there an S-SQL expression which will produce nested join expressions like this one: select * from table_1 inner join table_2 inner join table_3 on table_2.c = table_3.d on table_1.a = test_2.b; I know I can do this: (:select '* :from 'table-1 :inner-join 'table-2 :on (:= 'table-1.a 'table-2.b) :inner-join 'table-3 :on (:= 'table-2.c 'table-3.d)) which produces a chained join expression: (SELECT * FROM table_1 INNER JOIN table_2 ON (table_1.a = table_2.b) INNER JOIN table_3 ON (table_2.c = table_3.d)) However, these chained join expressions behave differently than nested ones when mixing different types of joins, and I need the behavior of the nested expression. I tried this:

1 0

on removing build-dao-methods
by Marco Baringer 06 Feb '14

06 Feb '14

i'd like to replace build-dao-methods with a set of slots on the dao-class instance, a common root in the class hierarchy (which would be dao-object) and a set of methods specialized on dao-object. before i do the work i want to make sure the patch won't be rejected on principle. my main problem with build-dao-methods is that it generates a new, distinct, method on the generic functions for every dao-class instance. when you have a lot of dao-class instances it becomes difficult to figure out which methods are auto generated and which are 'real' and may do something different than the standard methods (and implementing these non standard methods requires rewriting a lot of the code in build-dao-methods since call-next-method doesn't actually do anything). another problem, though i haven't actually had this problem in practice, is that build-dao-methods defines shared-initialize :after method on the given class to call fetch-defaults; this makes it impossible for me to define my own shared-initialize :after method to perform whatever extra initialization i may have. i can imagine a work around to this issue by defining the dao-class and then subclassing it to get the actual functionality i need, or call fetch-defaults myself, but being able to just define an :after method and have things Just Work(TM) seems simpler. there are a few things the build-dao-methods does that, i think, i can maintain using just the plain old mop: 1. only defining update-dao and upsert-dao when the class has value fields. by inspecting the class at compute-class-precedence-list time we can inject either dao-value-object or dao-key-only-object and then define the update/upsert-dao methods on dao-value-object and not dao-key-only-object. fwiw i think these methods should be defined for all dao objects; just because all the slots of the object are its primary key doesn't mean that changing one of those slots doesn't make sense (this can happen in practice when working with relation tables whose columns are all foreign keys and to preserve uniqueness of the relation the primary key consists of all (usually 2) columns). 2. the methods defined by build-dao-methods close over a set of precomputed values (column names and sql templates). by storing these values in the class instance directly we still avoid recomputing them every time the method is called and gain the ability to, easily, inspect their values at run time for debugging purposes. the one thing build-dao-methods does that i can't maintain is the lack of a common root class for all dao-class instances (in order for the various update/get/upsert/insert dao methods to get called there will need to be a dao-object in the hierarchy somewhere). the dao-object class can be injected in dao-class instance's precedence-list via compute-class-precedence-list, so there wouldn't be any changes required to user code; i don't really see this as an issue. i would also need to add in a dependency on closer-mop. i could imagine just cut 'n pasting the, relatively limited, parts of closer-mop we'd actually be using, and that would keep postmodern's dependencies to what they are today, but that seems like a bad thing to do. since this is a substantial change to how the dao methods are created i'd like to make sure that there aren't any objects to it (philosophical or technical) before actually making the change and submitting the pull request. thanks, -- -marco

2 1

"Auto-sync" questions
by Eli Naeher 28 Jan '14

28 Jan '14

Hello, I'm in the process of converting an existing application from CLSQL to Postmodern. This application makes heavy use of CLSQL's *db-auto-sync* feature, whereby new instances of view (DAO) classes are immediately inserted into the DB table, and changes to view class instance slots result in immediate updates. While I like the explicit approach of save-dao/update-dao/etc., there is just too much code in this application that relies on the auto-sync behavior to adopt that approach in the immediate term. So, a few questions: - would a *db-auto-sync*-like feature be accepted if I were to submit a pull request, or are there philosophical objections to having this feature in mainline Postmodern? - if this feature is not desired, would you be willing to accept a few patches that would make it easier for me to build this feature as a separate third-party library? Specifically, I'm thinking of something like CLSQL's *db-deserializing* flag, which would indicate to my code when it is being called by a dao-row-reader (and thus should not propagate slot value changes to the database) vs when it is being called by user code. Thank you, -Eli

2 3

Suddenly having trouble with update-dao
by Sabra Crolleton 23 Jan '14

23 Jan '14

After a recent quicklisp update, suddenly update-dao throws syntax errors. Simplest example: (update-dao (get-dao 'topic 182)) throws the following error: Database error 42601: syntax error at or near "Border" QUERY: UPDATE topics SET name = Cross Border Transactions, comments = NULL, usergroup_id = 1, permission_id = 1, parenttopic_id = 0 WHERE (id = 182) [Condition of type CL-POSTGRES-ERROR:SYNTAX-ERROR-OR-ACCESS-VIOLATION] I haven't updated anything and having a hard time trying to figure out what is triggering the problem. Anyone else seeing this or just me? Any pointers would be appreciated. Sabra

5 9

Simple-date vs local-time
by Eli Naeher 02 Jan '14

02 Jan '14

Hello, I'm working on moving an existing application to Postmodern, and I'm trying to decide whether to go with simple-date or local-time. Currently all dates are stored as "timestamp without time zone," but we are hoping to move toward a timezone-aware model at some point down the road (although not immediately--it might end up being a couple years). For this reason I'm considering using local-time instead of simple-date. Times and dates are a big part of the application so I'd like to get this right. I notice that the local-time cl-postgres integration has not had any commits in a few years. I'm hoping this is because it is stable and complete. Is anyone using it? Thank you, -Eli

3 3

doquery for DAOs
by Eli Naeher 26 Dec '13

26 Dec '13

Hello, I am working on implementing something like doquery that operates on DAOs--that is, something that will allow me to iterate over all the DAOs matching a certain test without consing up all the instances at once. Before I spend too much time on this, I thought I'd see if anyone else has done something like this already, or if there is a simple way to do this with the existing doquery that I have overlooked. Thank you, -Eli

2 1

Parsing for double precision not working yet
by Daniel Brunner 28 Nov '13

28 Nov '13

[NB: Marjin, I already posted this issue on Github] Hello! There is a problem with columns with double precision. Test case: --8<-- (pomo:query "create table ftest (value double precision)") (pomo:query "insert into ftest values ($1)" (coerce 100.10 'double-float)) --8<-- This fails with: --8<-- Database error 22P02: invalid input syntax for type double precision: "100.0999984741211D0" [Condition of type CL-POSTGRES-ERROR:DATA-EXCEPTION] --8<-- A short patch in cl-postgres/sql-string.lisp could solve that problem: --8<-- diff --git a/cl-postgres/sql-string.lisp b/cl-postgres/sql-string.lisp index 3157d30..4dff4be 100644 --- a/cl-postgres/sql-string.lisp +++ b/cl-postgres/sql-string.lisp @@ -84,6 +84,8 @@ whether the string should be escaped before being put into a query.") (princ-to-string arg)) (:method ((arg float)) (format nil "~f" arg)) + (:method ((arg double-float)) + (format nil "~,,,,,,'EE" arg)) (:method ((arg ratio)) ;; Possible optimization: we could probably build up the same binary structure postgres --8<-- Best wishes, Daniel

2 1

[postmodern-devel] A question about connection pooling, and the many ways to connect.
by Jim Barrows 12 Nov '13

12 Nov '13

I'm a java developer, and I think I'm expecting something to happen that's not. In Java I setup a pooled connection, and just use it. The connections pool themselves. I had thought that by doing: (setf *database* (connect "PomBanDone" "pombandone" "pombandone" "localhost" :pooled-p T))) that I would get connection pooling. However now that I'm getting a better understanding of Lisp and I think Postmodern, that connections aren't getting pooled. I think I have to use (with-connection (spec &body body) in order to make the connection pooling work. Is that the case? If not, why would I want to use with-connection? Thanks! -- James A Barrows

3 2

Possible additional sql-ops
by Sabra Crolleton 26 Oct '13

26 Oct '13

I have been writing queries requiring window functions and recursive with functions. I finally decided to try to write some sql-ops so that I can use s-sql. They work for me but would appreciate comments. I did include a :parens op because I find that sometimes I just need an additional set of parens for an sql query. Below are the functions, the amendments to :select and :order-by and usage examples. (def-sql-op :over (form &rest args) (if args `("(" ,@(sql-expand form) " OVER " ,@(sql-expand-list args) ")") `("(" ,@(sql-expand form) " OVER ()) "))) (def-sql-op :partition-by (&rest args) `("(PARTITION BY " ,@(sql-expand-list args) ")")) (def-sql-op :partition-by-order-by (form &rest fields) `("(PARTITION BY " ,@(sql-expand form) " ORDER BY " ,@(sql-expand-list fields) ") ")) (def-sql-op :parens (op) `(" (" ,@(sql-expand op) ") ")) (def-sql-op :with (&rest args) (let ((x (butlast args)) (y (last args))) `("WITH " ,@(sql-expand-list x) ,@(sql-expand (car y))))) (def-sql-op :with-recursive1 (form1 form2) `("WITH RECURSIVE " ,@(sql-expand form1) ,@(sql-expand form2))) (def-sql-op :window (form) `("WINDOW " ,@(sql-expand form))) Amendment to :select (to pick up the new "window" arg (def-sql-op :select (&rest args) (split-on-keywords ((vars *) (distinct - ?) (distinct-on * ?) (from * ?) (window ?) (where ?) (group-by * ?) (having ?)) (cons :vars args) `("(SELECT " ,@(if distinct '("DISTINCT ")) ,@(if distinct-on `("DISTINCT ON (" ,@(sql-expand-list distinct-on) ") ")) ,@(sql-expand-list vars) ,@(if from (cons " FROM " (expand-joins from))) ,@(if window (cons " WINDOW " (sql-expand-list window))) ,@(if where (cons " WHERE " (sql-expand (car where)))) ,@(if group-by (cons " GROUP BY " (sql-expand-list group-by))) ,@(if having (cons " HAVING " (sql-expand (car having)))) ")"))) Amendment to :order-by to pick up the situation where the only arg is the form (def-sql-op :order-by (form &rest fields) (if fields `("(" ,@(sql-expand form) " ORDER BY " ,@(sql-expand-list fields) ")") `("( ORDER BY " ,@(sql-expand form) ")"))) Usage Examples: Over Examples generally following http://www.postgresql.org/docs/9.3/static/tutorial-window.html (query (:select 'salary (:over (:sum 'salary)) :from 'empsalary)) (query (:select 'depname 'empno 'salary (:over (:avg 'salary) (:partition-by 'depname)) :from 'empsalary)) (query (:select 'depname 'empno 'salary (:over (:rank) (:partition-by-order-by 'depname (:desc 'salary))) :from 'empsalary)) (query (:select (:over (:sum 'salary) 'w) (:over (:avg 'salary) 'w) :from 'empsalary :window (:as 'w (:partition-by-order-by 'depname (:desc 'salary))))) With Examples (query (:with (:as 'upd (:parens (:update 'employees :set 'sales-count (:+ 'sales-count 1) :where (:= 'id (:select 'sales-person :from 'accounts :where (:= 'name "Acme Corporation"))) :returning '*))) (:insert-into 'employees-log (:select '* 'current-timestamp :from 'upd)))) With-Recursive Examples following http://www.postgresql.org/docs/current/static/queries-with.html (query (:with-recursive (:as (:t1 'n) (:union-all (:values 1) (:select (:+ 'n 1) :from 't1 :where (:< 'n 100)))) (:select (:sum 'n) :from 't1))) (query (:with-recursive (:as (:included_parts 'sub-part 'part 'quantity) (:union-all (:select 'sub-part 'part 'quantity :from 'parts :where (:= 'part "our-product")) (:select 'p.sub-part 'p.part 'p.quantity :from (:as 'included-parts 'pr) (:as 'parts 'p) :where (:= 'p.part 'pr.sub-part) ))) (:select 'sub-part (:as (:sum 'quantity) 'total-quantity) :from 'included-parts :group-by 'sub-part))) (query (:with-recursive (:as (:search-graph 'id 'link 'data 'depth) (:union-all (:select 'g.id 'g.link 'g.data 1 :from (:as 'graph 'g)) (:select 'g.id 'g.link 'g.data (:+ 'sg.depth 1) :from (:as 'graph 'g) (:as 'search-graph 'sg) :where (:= 'g.id 'sg.link)))) (:select '* :from 'search-graph))) (query (:with-recursive (:as (:search-graph 'id 'link 'data'depth 'path 'cycle) (:union-all (:select 'g.id 'g.link 'g.data 1 (:[] 'g.f1 'g.f2) nil :from (:as 'graph 'g)) (:select 'g.id 'g.link 'g.data (:+ 'sg.depth 1) (:|| 'path (:row 'g.f1 'g.f2)) (:= (:row 'g.f1 'g.f2) (:any* 'path)) :from (:as 'graph 'g) (:as 'search-graph 'sg) :where (:and (:= 'g.id 'sg.link) (:not 'cycle))))) (:select '* :from 'search-graph)))

2 1

What is the easiest way to get the col-type of a slot?
by Sabra Crolleton 08 Sep '13

08 Sep '13

Feeling a bit brain dead at the moment. I am trying to do a sanity check on user-input before the data gets put in a dao-class. That requires simply checking the col-type for the slot, comparing it to the type for the data and, if they do not match, rejecting the data. I can do it looping through postmodern:dao-column-slots using postmodern::column-type, but is there a way of doing it with exported functions? Sabra

2 1