Designing SQL Schemas in Lisp

I have, as a task, designing a fairly rich DBMS schema for some future systems.  I’m not keen on doing a whole lot of by-hand reconciliation, so am taking a somewhat different approach.  I am analytically describing the relationships between tables, and then generating SQL DDL from the results.

I am doing some of the intermediary work in Lisp; probably a little bit dubious, but the translation is making sure that everything that I do have is  pretty strictly defined.  I created Lisp “primitive” functions such as “make-table”, “add-column” which do a little bit of data validation, and do any SQL generation in one spot.  The more interesting part is then to characterize the creation of various sorts of tables:

  • first-class-object indicates a table that sits on its own.
  • make-subobject indicates a table that depends on one of the “first class” objects.
  • make-association-table indicates a table that links two “first class” objects together.
  • make-transaction-stream indicates a table that contains transactions/activities
  • make-queue sets up a table that will contain work that is to be processed (and, presumably, cleared out upon completion)

The more structure that may be characterized in a fashion that makes it amenable to automation, the better 🙂

Schema day…

One of those “clean” moments; we’re looking to redo a ‘contact database,’ and make sure it’s open to adding quite a bit of further extra functionality. I have been spending a considerable portion of the day simply writing DDL.

Now, for one regret: it looks like something in PostgreSQL Autodoc”, and in the most regrettable place.

Every time it tries evaluating a foreign key, it hits the following complaint:
DBD::Pg::st execute failed: ERROR: invalid input syntax for integer: "ARRAY(0x84a5584)" has broken.

I think I need to bug Mr. Taylor…

Slony-I DDL problem

Yay, I figured out the nature of the problem with the running of per-node DDL scripts on v2.0.

It’s fairly deep; there is something about the semantics of “local” execution that’s wrong.   Gonna need to pass this upstream to Jan for more input…


There is an ASN.1 encoder/decoder for Common LISP as part of a “SYSMAN” system, an SNMP implementation.  It doesn’t directly support CLISP :-(.  But the bits that seem troublesome are evidently pretty directed to SNMP MIBs…

I got something working via the combination of:

(dolist (file '("asn1-package" "dependent" "mib" "asn1-defs" "ber-defs" "ber-decode" "ber-encode"))
  (compile-file file)
  (load file))

Along with a few minor changes to some of the component files.  It looks like it works, but I haven’t really exercised it yet…

Slony-I 2.0 fixes…

Apparently, it’s a good day to CVS COMMIT…

I was able to get fixes in to

  • fix testpartition
    The partitioning functions were still modelled on the “old” way that Slony-I 1.2 and earlier handled triggers on functions,  where they had to get dropped/re-added each time you run DDL.  Need to use New Scheme.
  • fix multipaths
    This code exercised code paths that showed off that there were a number of references left in the code to storenode_int(int,text,boolean), where, in 2.0, we’re dropping the boolean flag that was intended to indicate that the node was to be used as a log shipping source.

There are several tests that are not working, at present, in the 2.0 branch, notably (but possibly not “only”), that will be the targets of further work:

  • testddl
  • testlogship

Slony-I 2.0 Testing

The “CVS HEAD” branch of Slony-I has been open for rather a while, and has rather a lot of changes in it.  ‘Tis time, now, to validate that it is generally working, so that we can, with some confidence, let it out and allow others to start criticizing any remaining issues that would prevent a release.

Today’s extra entertainment: I have added a bit of code to my test script that publishes to Twitter each time I run a test…

Slony-I Cancel Subscription

Well, I have a preliminary spec out, now, for CANCEL SUBSCRIPTION, as part of

Doubtless it will merit some discussion before leaping into implementation.

My Next Slony-I trick…

I’m working on a “CANCEL SUBSCRIPTION” feature that has been talked about for rather a long time.  (It’s on the bug list:

The notion is that if you have a subscription that keeps failing for some reason, we should have a way of dropping the subscription request without being forced to throw away the entire cluster configuration.  The special thing about CANCEL SUBSCRIPTION is that it needs to be able to come in and take action against events EARLIER in the event stream than itself.

In analyzing the environment of data available to CANCEL SUBSCRIPTION, it looks like it’s pretty highly constrained.  There are quite a number of cases where it should fail to do anything.