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

[postmodern-devel] Submarine announcement
by Ryszard Szopa 04 Sep '07

04 Sep '07

Hello Marijn and Postmodern users, I was a bit dissatisfied with Postmodern's DAO system and lack of support for foreign keys. At first, I wrote just a patch that added support for foreign keys, but it turned the macro DEFTABLE into something that had more than 100 lines and was pretty unmaintainable. So, I left Postmodern's sources alone and wrote a library over it, something between Postmodern and an object persistency library. I've used it in some of my projects at work, and now I decided to make it open source. In a couple of days I'll start it a common-lisp.net project and announce it to c.l.l. In the meantime, I will be very grateful for any comments. I encourage you to at least take a look at the example file: http://bender.streamtech.nl/darcs/submarine/doc/example.lisp Here are the contents of my README file: Submarine is a Common Lisp library that's somewhere between a PostgreSQL library an an object persistency system. It uses Postmodern to communicate with the database. The basic idea is that you create your classes in the metaclass DB-CLASS and submarine cares about creating SQL tables or, if the tables already exist, checking if they conform to the provided specification. Moreover, Submarine supports an intuitive way of expressing both one-to-many and many-to-many relations. Getting submarine ================= At the moment there's only a darcs repository available: darcs pull http://bender.streamtech.nl/darcs/submarine/ You will also need my mop-utilities, which may be incorporated into submarine in the close future: darcs get http://bender.streamtech.nl/darcs/mop-utils Dependencies ============ Submarine depends on Postmodern and Iterate. It uses also my library of MOP utilities, MOP-UTILS, which may become a separate library in the future. On platforms other than SBCL, mop-utils needs Closer-mop. License ======= Submarine is released under a BSD-like license. Which approximately means you can use the code in whatever way you like, except for passing it off as your own or releasing a modified version without indication that it is not the original. Relation to Postmodern ====================== Submarine started as a patch that added, among others, foreign keys support to Postmodern. This meant hacking the DEFTABLE macro and TABLE and TABLEFIELD classes. After some time I realized that my modified Postmodern macros had become far too large and heavy to be easy maintainable. So, I decided to leave Postmodern's code as it was and write a separate library. Introduction for Postmodern users ================================= Submarine tries to keep as much as possible of Postmodern's original API. I use the same terminology, and functions with similar names will probably do very similar things. My purpose was twofold. First of all, I wanted to make porting programs using Postmodern to Submarine easy. Secondly, this would allow me to use some of Marijn Haverbeke's superb documentation nearly without any changes. Main differences between Postmodern and Submarine: * You don't have to create DAO (database access object) classes and SQL tables separately. You just create classes belonging to a certain metaclass, and the library cares of the rest. * If the table with an appropriate name exists in the database, Submarine will test if it has columns with the right names and types. If a column does not exist or has a wrong type, Submarine will offer the user a possibility to fix it. Submarine will warn, but do nothing about any additional columns in the table. * Each DB-CLASS class has its own connection specification. This means that you can just access your objects, without wrapping them in a WITH-CONNECTION macro, and they will care about setting the right connection. * DAO is a basis class for DB-CLASS classes, and methods that are supposed to work on an object of any DB-CLASS class use DAO as a dispatch type. Of course, you don't have to use DAO as a base class, but it makes a lot of things easier. * Submarine supports foreign keys. If you define a DB-CLASS class with a slot whose type is another DB-CLASS, it is treated as foreign key and an appropriate constraint is added to the database. * Submarine provides a function to retrieve all the elements of a given type standing in a many-to-one relation with some DAO object. * The DEF-MANY-TO-MANY macro defines a many-to-many relation between two classes and creates methods appropriate to their retrieval (it creates a link table in the database). * DB-CLASS slots have an additional attribute TRANSIENT. If you set it to a non-NIL value, it will behave just as a STANDARD-SLOT-DEFINITION and will be ignored in database related operations. * Submarine uses MOP rather than macros to achieve its purposes. This makes it easier to maintain and to extend. At the moment there's no real documentation apart from docstrings, but there's a poor man's tutorial in the file http://bender.streamtech.nl/darcs/submarine/doc/example.lisp (this should give you a pretty good overview of the possibilities of Submarine. Bests, -- Richard -- http://szopa.tasak.gda.pl/

3 9

[postmodern-devel] postmodern, pg, cl-rdbms
by Attila Lendvai 27 Aug '07

27 Aug '07

hello list, i've recently found postmodern on cl.net, and i was wondering that there seems to be a massive amount of duplication of efforts among the following projects: - pg is a long-time postgresql socket based lib: http://common-lisp.net/project/pg/ - cl-rdbms (our fresh clsql replacement, http://common-lisp.net/project/cl-rdbms/ ) which aims to be a backend independent sql lib, just like clsql, with sexp sql syntax ( see http://common-lisp.net/cgi-bin/darcsweb/darcsweb.cgi?r=cl-rdbms-cl-rdbms;a=… for a quick impression ). it has a single backend for now, based on pg. - and postmodern which is something like a mixture of pg and cl-rdbms we should bring out the best of these... my first impression of postmodern is that it's more modern and better organized code then pg which is good. and to also write actual suggestions besides to this rant, i'd use http://common-lisp.net/project/local-time/ as the date/time abstraction data type. it supports timezones and has a string parser/printer. and fyi, trivial-sockets seems to be a dead project. i've tried to contact the author several times with patches and there wasn't any response; #lisp is on the same opinion. usocket at least welcomes patches, but it's not really important if t-s can already do everything needed. any toughts? -- - attila "- The truth is that I've been too considerate, and so became unintentionally cruel... - I understand. - No, you don't understand! We don't speak the same language!" (Ingmar Bergman - Smultronstället)

3 10

[postmodern-devel] conditional query building
by Jens Teich 16 Aug '07

16 Aug '07

> (postmodern:sql (:= 's 'tr)) => "(s = tr)" > (postmodern:sql (if t (:= 's 'tr))) => Error: Undefined operator := in form (:= (QUOTE S) (QUOTE TR)). How do I make this work? I want to build more complex where clauses using cl:if. Jens

2 3

[postmodern-devel] Join Slots
by Jens Teich 13 Aug '07

13 Aug '07

I'm considering to change from clsql to postmodern because I need unicode support. In clsql the following viewclass definition is possible with join slots: (clsql:def-view-class elements () ((guid :reader element-guid :db-kind :key :type (string 36)) (lang_id :db-kind :key :type (string 2)) ... more ... 'normal' slots) (produkt :reader product-element :db-kind :join :db-info (:join-class produkte :home-key (guid_objekt lang_id) :foreign-key (guid lang_id) :set nil))) (:base-table project-elements)) so that I can access the joined records easily via (product-element instance-of-element) How do I do this with postmodern? Thanks Jens

2 1

[postmodern-devel] Can't nest QUERY within prepared-statement QUERY
by J.P. Larocque 30 Jul '07

30 Jul '07

Hi again, Thanks for the fix and the new release, Marijn. I think I found another problem. When I try to query with a prepared statement with the QUERY macro, and one of the arguments to the statement is the result of another QUERY macro (whether directly or from the result of a function), strange things crop up. In one instance, the inner QUERY was returning nil where it would return the expected integer otherwise. Here's an isolated case: (postmodern:query "select cast ($1 as int)" (let ((foo (postmodern:query "select 12" :single))) (format t "result: ~S~%" foo) foo)) This gives me the expected message "result: 12", but also signals a condition, "Database error: Incorrect number of parameters given for prepared statement ." Below is the complete macroexpansion of the above form (without LET and FORMAT). (PROGN (CL-POSTGRES:PREPARE-QUERY POSTMODERN:*DATABASE* "" "select cast ($1 as int)") (CL-POSTGRES:EXEC-PREPARED POSTMODERN:*DATABASE* "" (MAPCAR 'S-SQL:SQL-IZE (LIST (CAR (CL-POSTGRES:EXEC-QUERY POSTMODERN:*DATABASE* "select 12" 'POSTMODERN::COLUMN-ROW-READER)))) 'CL-POSTGRES:LIST-ROW-READER)) The sequence of evaluation is PREPARE-QUERY, then EXEC-QUERY (for the inner "select 12"), then EXEC-PREPARED. I guessed that doing things with the database in-between PREPARE-QUERY and EXEC-PREPARED might be the problem, so I modified QUERY to evaluate prepared-statement arguments before PREPARE-QUERY. This allows the inner query to complete before the outer one actually starts. diff -urNx '*~' postmodern-1.02/postmodern/query.lisp postmodern-1.02-modified/postmodern/query.lisp --- postmodern-1.02/postmodern/query.lisp 2007-07-25 04:10:42.000000000 -0700 +++ postmodern-1.02-modified/postmodern/query.lisp 2007-07-27 23:03:16.000000000 -0700 @@ -55,9 +55,10 @@ :else :collect arg))) (destructuring-bind (reader single-row) (cdr (assoc format *result-styles*)) (let ((base (if args - `(progn - (prepare-query *database* "" ,(real-query query)) - (exec-prepared *database* "" (mapcar 'sql-ize (list ,@args)) ',reader)) + (let ((arg-values (gensym))) + `(let ((,arg-values (mapcar 'sql-ize (list ,@args)))) + (prepare-query *database* "" ,(real-query query)) + (exec-prepared *database* "" ,arg-values ',reader))) `(exec-query *database* ,(real-query query) ',reader)))) (if single-row `(multiple-value-call 'car-of-first-value ,base) Now my first example works, as does the result of one prepared-statement QUERY's being indirectly nested in another prepared-statement QUERY. -- J.P. Larocque: <piranha(a)thoughtcrime.us>, <piranha(a)ely.ath.cx>

2 4

[postmodern-devel] Mainainer absence
by Marijn Haverbeke 28 Jul '07

28 Jul '07

Hello Postmodern-users, Starting somewhere at the end of next week, I will be travelling. How long this is going to take is not clear yet, but it will mean that I will be less actively maintaining the library. I will read my e-mail now and then, and applying a patch sent to me should be possible whenever I come across an internet cafe that lets me use SSH, but I certainly won't be writing any patches or properly testing things sent to me. If anyone feels up to it, I wouldn't mind giving someone else commit rights to the repository. (I will, of course, still be hanging over your shoulder trying to enforce my extremist ideas about clean interfaces and up-to-date documentation.) Another option is that somebody sets up a 'secondary' repository, advertises it on this list, and keeps it up to date with whatever bug-fixes and features people come up with. Cheers, Marijn

1 0

[postmodern-devel] bug in query (or deeper)
by Ryszard Szopa 28 Jul '07

28 Jul '07

If you do something like this: > (postmodern::query '(:select 1)) The value (:SELECT 1) is not of type STRING. [Condition of type TYPE-ERROR] Restarts: 0: [ABORT] Return to SLIME's top level. 1: [TERMINATE-THREAD] Terminate this thread (#<THREAD "repl-thread" {AF37921}>) 0 the slime-repl buffer starts waiting for something (probably some response from the database). When you hit C-c, and select abort, it stops. Afterwards, however, the database connection is lost: > (postmodern::query (:select 1)) Database error: Connection to database server lost. [Condition of type DATABASE-CONNECTION-LOST] Restarts: 0: [RECONNECT] Try to reconnect. 1: [ABORT] Return to SLIME's top level. 2: [TERMINATE-THREAD] Terminate this thread (#<THREAD "repl-thread" {AF37921}>) The problem lies probably somewhere deeper than in query. If I have some time, I'll try to investigate it. Cheers, -- Richard -- http://szopa.tasak.gda.pl/

2 1

[postmodern-devel] Can't prepare query with :NULL
by J.P. Larocque 25 Jul '07

25 Jul '07

Hi, I can't use the value :NULL in a Postmodern prepared query: > (postmodern:query "select cast ($1 as int)" :null :single) debugger invoked on a CL-POSTGRES:DATABASE-ERROR in thread #<THREAD "initial thread" {10025599C1}>: Database error 08P01: bind message supplies 0 parameters, but prepared statement "" requires 1 Query: select cast ($1 as int) (The cast was given so that the type of the expected value would be known.) If I understand PostgreSQL and Postmodern correctly, this should return the Lisp value NIL. I have no similar problem with SQL and SQL-COMPILE, since these apparently don't technically do query preparation. I believe there is an error in the QUERY macro which is causing this. The loop which determines ARGS sees :NULL as a keyword symbol and assumes this means it is an output format. Instead of checking each member of ARGS/FORMAT to see if it is a keyword symbol, I would check for its membership in a list of possible valid format symbols. Alternatively, I'd take the idea of the DB-NULL type to an extreme, writing Lisp type definitions for each supported SQL type, and also write a type (e.g. DB-VALUE) that is the OR of all of these types. (It looks something similar was already done.) In the QUERY macro, only if an argument isn't of the type DB-VALUE would it be considered an output format. -- J.P. Larocque: <piranha(a)thoughtcrime.us>, <piranha(a)ely.ath.cx>

3 4

[postmodern-devel] Query generation
by Osei Poku 27 Jun '07

27 Jun '07

Hi, I want to generate the following query: "UPDATE table1 SET col1 = NULL, col2 = NULL" from the following list: ("col1" "col2") my attempt is: (sql (append '(:update :table1 :set) (loop for a in '("col1" "col2") collect a collect :NULL))) but that apparently doesnt work. this can probably be attributed to my newness to lisp. Any ideas? thanks -- Osei Poku PhD Student, ECE Carnegie Mellon University

3 3

[postmodern-devel] Defer creation of sequence ids?
by Vamsee Kanakala 22 Jun '07

22 Jun '07

Hi, I think, create-instance should defer the creation of an id (from sequence-next), because, there are some times when an object is created, but is not saved. For example, in a web application, I would need to create an object so that I can fill in the slots from the form values, but I might not save it, as it might have some validation errors. It would save some sequence ids (a lot actually, as there might be multiple rounds of validation errors) if I don't have to assign an id unless it is being saved to the database. I think we could have insert-dao/save-dao to retrieve a new sequence id if the column "id" is not filled/nil/zero, and use an existing id if it's value is greater than zero. If this sounds ok, I can create a patch. Regards, Vamsee.

2 6