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

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 20 Mar '14

20 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 28 Feb '14

28 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