[okfn-help] vdm: mysql compatibility

[Rufus Pollock]

On 19 August 2010 20:08, Martijn Faassen <faassen at startifact.com> wrote:
> 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).

Very good point and this is clearly a 'bug' that should be immediately
fixed. If you want to send a patch, or, even better, fork the codebase
on bitbucket or the like we'll apply the patch ...

> 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:

Again, very sensible ...

> 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.

If you use mercurial then forking onto a public bitbucket repo would
definitely be the best way to go for these kind of patches. Otherwise
please do submit patches (try and keep them specific to each item --
e.g. unicode column separate from make_revision_table) and we'll apply
them.

Rufus