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