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
On 8/28/07, Ryszard Szopa ryszard.szopa@gmail.com wrote:
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...
Hi,
Is there any way to separate DAO definition and db connection spec? Keeping connection spec in one place (global variable) makes deployment easier.
BTW, nice password ;-)
On 8/28/07, Rafał Strzaliński nablaone@gmail.com wrote:
Is there any way to separate DAO definition and db connection spec? Keeping connection spec in one place (global variable) makes deployment easier.
Well, the idea behind each class having its own connection spec is that I would like to allow each class to be connecting another database. I don't know if this is the best approach (after all, this is not the most common a situation), and maybe I will rethink it.
Personally, I usually wrap my dao defnitions into a macro with the right connection spec, like the following:
(defmacro defdao-example (name supers &body body) "Wrapper macro for defining a class inheriting from DAO with the metaclass set to HTML and the right connection-spec." `(defdao ,name ,supers ,@body (:connection-spec "submarine-test" "richard" "dupa" "localhost")))
BTW, nice password ;-)
Well, I guess it is one of the glorious Polish traditions (-;
Bests,
-- Richard
Hey Ryszard,
Great work! I've only checked it out globally, but it looks very nice. I'm always a big fan of keeping stuff separate, rather than integrating things into big blobs of code, so I'm glad you managed to make this a separate library. Have you found a way around the issues postgres has with creation and deletion of tables that have foreign keys referring to each other? (i.e. it will not let you put your database in an incorrect state)
Cheers, Marijn
On 8/29/07, Marijn Haverbeke marijnh@gmail.com wrote:
Hey Ryszard,
Great work! I've only checked it out globally, but it looks very nice.
Thanks!
I'm always a big fan of keeping stuff separate, rather than integrating things into big blobs of code, so I'm glad you managed to make this a separate library. Have you found a way around the issues postgres has with creation and deletion of tables that have foreign keys referring to each other? (i.e. it will not let you put your database in an incorrect state)
Not really. I also have a problem if a class refers to a not-yet-existing class/table in its foreign key: Postgres will stubbornly refuse to create it.
At the moment I am working on a workaround for it. I want to separate the foreign key stuff from the table creation, i.e. I want to first create the table, and then add constraints to it (this would also allow me to check if the constraints are right when the table already exists). I plan to put the code for setting foreign key constraints into a lambda, and wrap its funcall by handler-case. If everything goes right, this is the end of the story for that lambda. If the handler-case catches a PostgreSQL error, then the lambda is stored somewhere. Then, when a class of db-class is created, it tries to run all the canned constraints available.
There are two things about this approach I am not so happy about. First, this means adding an additional attribute to DB-CLASS-SLOT-DEFINITION. Secondly, I am not really sure what is the best way of dealing with classes that have delayed constraints that hadn't been fired (like when, for example, the user had forgotten to write the definition of the class referred to).
Do you feel this approach is sensible? Or maybe anybody has a better idea?
Bests,
-- Richard
Here is one way in which you could approach it: On the Lisp side, you can just let people define tables in any order, since non-existant foreign targets aren't a problem there. When creating a whole 'database schema' at once in the database, just create the tables first, and then the keys, and let any errors generated by non-existant tables go through. When creating or resetting a single table, if it has any foreign keys into tables that do not exist, check whether such a table is defined on the Lisp side -- if it is, raise an exception with a 'create this table' restart, otherwise, raise an unrecoverable exception.
(This might, of course, have some problems that I haven't thought of yet, but I think it would be workable.)
Will submarine also have possibilities to define sequences, stored procs, and views? It would be cool to have a single place to define whole db schemas in Lisp, and then have convenient ways to create them all at once in an actual database. -- And of course, the hypothetical 'automatically detect differences between the schema defined in Lisp and the one existing in the database and interactively update the database' functionality might also be something you can think about -- if Jaap allows you to waste another two months on this, hah.
Cheers, Marijn
On 9/1/07, Marijn Haverbeke marijnh@gmail.com wrote:
Here is one way in which you could approach it: On the Lisp side, you can just let people define tables in any order, since non-existant foreign targets aren't a problem there. When creating a whole 'database schema' at once in the database, just create the tables first, and then the keys, and let any errors generated by non-existant tables go through. When creating or resetting a single table, if it has any foreign keys into tables that do not exist, check whether such a table is defined on the Lisp side -- if it is, raise an exception with a 'create this table' restart, otherwise, raise an unrecoverable exception.
(This might, of course, have some problems that I haven't thought of yet, but I think it would be workable.)
I was thinking about something similar, but I was afraid of one situation. Imagine the user actually forgets to define a class he references to. Swallowing all the db errors would mean that he has something that seems to work, and only in some specific situations will start behaving strangely. This may lead to errors very hard to debug.
Also, the creation of an SQL table is a shared-initialize :after method of db-class. This means that there's no need of an additional command to creating a table or all the tables -- it's all done at definition time. The drawback of this approach is that I cannot compute the dependencies between tables and reorder them so that Postgres is happy.
On Friday I've implemented nearly exactly what I have written in my letter from Thursday (when there's a non-existing referenced table problem, the constraint adding function is stored; after the initialization of each new db-class, all delayed constraints are tried). And, apparently, it seems to work. When the user tries to make an instance of a class that has unresolved dependencies, submarine raises an error.
Will submarine also have possibilities to define sequences, stored procs, and views? It would be cool to have a single place to define whole db schemas in Lisp, and then have convenient ways to create them all at once in an actual database. -- And of course, the hypothetical 'automatically detect differences between the schema defined in Lisp and the one existing in the database and interactively update the database' functionality might also be something you can think about -- if Jaap allows you to waste another two months on this, hah.
I think Jaap won't mind, as I am going back to Poland next Friday. (Uh-oh... That also means he'll stop paying me... oh, rats... ;))
The "checking if the table in the database complies to the specification of the db-class" of submarine is nearly finished (checking constraints is the only thing left to be written).
As for my plans for the future: I will be thinking about inheritance (fortunately Postgres supports multiple inheritance), schemata (I think they should be somehow related CL packages, because they seem to be doing a very similar job). As for stored procs: I am not really sure what could I do with them (it's a pity Postgres doesn't support writing procedures in CL, like it does for Scheme).
Also, I would find it very fortunate if S-SQL supported ALTER, constraints and stuff like that (I am using just quick hacks to generate the appropriate SQL code right now, and I can't say to be very happy about it). As you are on vacation, it probably means I should do it myself. :P
BTW: how did it take for you to get a project at c-l.net? Because I sent them an e-mail as they wanted and I still haven't received any answer.
Bests, -- Richard
On 9/3/07, Ryszard Szopa ryszard.szopa@gmail.com wrote:
I was thinking about something similar, but I was afraid of one situation. Imagine the user actually forgets to define a class he references to. Swallowing all the db errors would mean that he has something that seems to work, and only in some specific situations will start behaving strangely. This may lead to errors very hard to debug.
Note that I didn't talk about swallowing any db errors -- my idea was to just fail to create the table entirely when some dependencies were missing.
Also, the creation of an SQL table is a shared-initialize :after method of db-class. This means that there's no need of an additional command to creating a table or all the tables -- it's all done at definition time. The drawback of this approach is that I cannot compute the dependencies between tables and reorder them so that Postgres is happy.
I think this is an awful idea -- keeping the definition and the actual creation of tables separately allows a flexibility which can save you a ton of headaches.
Also, I would find it very fortunate if S-SQL supported ALTER, constraints and stuff like that (I am using just quick hacks to generate the appropriate SQL code right now, and I can't say to be very happy about it). As you are on vacation, it probably means I should do it myself. :P
It is something I didn't need often enough to bother about (I tend to write alter table commands directly at an SQL prompt). Also, alter is quite a complicated statement. But if you write support for it I will, of course, be happy to add it to the repo.
BTW: how did it take for you to get a project at c-l.net? Because I sent them an e-mail as they wanted and I still haven't received any answer.
Took a while before I received a response as well -- if I remember correctly the owner was on some kind of trip at the time. But once I got a response, everything was handled smoothly and quickly.
Cheers, Marijn
On 9/2/07, Marijn Haverbeke marijnh@gmail.com wrote:
Also, the creation of an SQL table is a shared-initialize :after method of db-class. This means that there's no need of an additional command to creating a table or all the tables -- it's all done at definition time. The drawback of this approach is that I cannot compute the dependencies between tables and reorder them so that Postgres is happy.
I think this is an awful idea -- keeping the definition and the actual creation of tables separately allows a flexibility which can save you a ton of headaches.
Well, for me it works fine this way. Also, this is only the default behavior. If you don't provide a connection spec to defdao (macro creating a subclass of dao with the metaclass set do db-class), then no SQL queries will be executed (I use this feature for making fixtures and base classes on the Lisp side that shouldn't have any effect on the Postgres side).
Do you think this is enough for saving that ton of headaches? :) If it is not, please don't hesitate to give examples of problems that may arise, and I will try to do something about them.
Cheers,
-- Richard
Do you think this is enough for saving that ton of headaches? :) If it is not, please don't hesitate to give examples of problems that may arise, and I will try to do something about them.
Will Submarine allow DAO-style classes to be defined for the created tables? These classes will have to defined every time the program is compiled/used, while the database tables only have to be created when a new DB is initialized. Also, the problem with the foreign keys to non-existant tables seems a lot more manageable to me if definition and creation are two different commands. But I haven't thought deeply about any of this, so just do what seems workable to you.
Marijn
postmodern-devel@common-lisp.net
-
Marijn Haverbeke -
Rafał Strzaliński -
Ryszard Szopa