[okfn-help] vdm: mysql compatibility

[Martijn Faassen]

Hi there,

I just played with vdm using a MySQL database. I ran into some
compatibility issues which I'd like to discuss so we can fix them:

MySQL apparently cannot use UnicodeText() (text, I think, in SQL)
columns as primary keys. It requires you to have a text column where
the length is specified. This means that the 'revision' table cannot
be constructed.  If instead in make_revision_table 'Unicode' is used
for the id
column as opposed to UnicodeText, the table can be created. unicode()
however requires a maximum length.

What goes into this column is a uuid4. The question is how long can a
uuid4 become? I think, from a brief study of rfc4122, that this is
always 36 characters, so I think Unicode(36) should do the trick.

The same problem occurs in make_revisioned_table(), where a foreign
key is set up to point to revision.id. This should also be the same
Unicode(36).

The next problem is that I use InnoDB tables with MySQL, and
make_revision_table creates a non-InnoDB table. It doesn't appear
possible to create a foreign key reference from a InnoDB to a
non-InnoDB table, so that's a problem.

In SQLAlchemy, you set up InnoDB tables by passing
"mysql_engine='InnoDB'" into the Table constructor. I can't do that
with
this table created by vdm. There are potentially other such options,
so the simplest way to fix this is to modify make_revision_table to
take
variable keyword arguments, and to pass these along to Table:

def make_revision_table(metadata, **kw):
    revision_table = Table('revision', metadata,
            Column('id', Unicode(36), primary_key=True, default=make_uuid),
            Column('timestamp', DateTime, default=datetime.now),
            Column('author', String(200)),
            Column('message', UnicodeText),
            Column('state', UnicodeText, default=State.ACTIVE),
            **kw
            )
    return revision_table

After these modifications things appear to work somewhat. For more
about the "somewhat", I'll send another mail.

Regards,

Martijn