[okfn-help] vdm: more mysql compatibility

[Rufus Pollock]

On 25 August 2010 12:25, Martijn Faassen <faassen at startifact.com> wrote:
> 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?

We'd prefer not to. Alternative is to add an extra 'number' field to
revision that auto-increments. This would be better I think.

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

Ah.

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

that's a problem and why I moved to timestamp. Only other alternative
is for revisions to specify an explicit 'parent' but then you need to
walk this tree to get everything in 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.

Hmm. This may be the best way to go here.

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

Not at the moment but I'll reflect :)

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

I think the best option would be just to document this very clearly
for mysql users rather than introduce an explicit dependency.

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

Easiest way would to introduce a simple test.ini file in base
directory for connection info and then we can just run tests with
different ini files.

Rufus