[okfn-help] vdm: more mysql compatibility

[Martijn Faassen]

Hi there,

I've done some more work on MySQL compatibility for vdm.

As you may recall from the last discussion, the main issue left (I
think) is that MySQL doesn't support subsecond granularity for
datetime fields. As a result vdm can easily get the wrong revision if
they have been made in the same second, and this isn't an uncommon
situation I think.

Rufus proposed adding an auto-incrementing order field (perhaps the
id) to vdm to resolve the ambiguity. Some issues with that:

* you guys switched to a uuid for the primary key for a reason, is it
okay to switch back?

* doing an extra 'order' column that is autoincrementing is a bit
tricky to implement with mysql, as it doesn't have Serial support.

* can we really guarantee the records are inserted in historical
order? Could it be that two records are inserted in reverse order?

Yesterday I've explored another direction: a SQLAlchemy DateTime
column for MySQL that actually stores the timestamp in a decimal
column. datetime objects are converted to and from unix timestamp
(with floating point component for subsecond granularity), and those
are stored in the column. For the SQLAlchemy user however the column
behaves like a datetime column during insertion and queries and the
works. This isn't perfect; it's going to be harder to construct raw
SQL queries, but from the SQLAlchemy side it is actually quite nice.

The code that does that is here:

http://bitbucket.org/faassen/sa_mysql_dt/

I experimented yesterday with hooking this into vdm and running the
vdm tests against a MySQL database; I had one test failure only:

ERROR: vdm.sqlalchemy.test_demo.Test_05_RevertAndPurge.test_purge_revision

  File "/home/faassen/ve_vdm/lib/python2.6/site-packages/SQLAlchemy-0.5.8-py2.6.egg/sqlalchemy/orm/mapper.py",
line 1549, in _delete_obj
    "number of objects deleted %d" % (c.rowcount, len(del_objects)))
ConcurrentModificationError: Deleted rowcount 0 does not match number
of objects deleted 1

This still needs to be debugged (any clues what this is about?), but
otherwise it looks good.

Any ideas for how we can integrate this into vdm? I could copy the
code into vdm, but the whole sa_mysql_dt is actually a python package
that could be useful outside of vdm, and I can easily publish it to
pypi. I could then add it as a dependency to vdm in its setup.py. This
would have my preference, but vdm at this time declares no
dependencies, so perhaps this isn't desired?

As Rufus already suggested, we need to come up with test
infrastructure that can run the tests against multiple database
servers (at least PostgreSQL and MySQL to start with).

Regards,

Martijn