[okfn-help] vdm: strange behavior

[Rufus Pollock]

On 23 August 2010 17:31, Martijn Faassen <faassen at startifact.com> wrote:
> Hi there,
>
> On Mon, Aug 23, 2010 at 4:01 PM, Martijn Faassen <faassen at startifact.com> wrote:
>>> One way to resolve this would be to introduce a revision 'number'
>>> attribute which is an auto-incrementing integer. In fact up until v0.6
>>> revision id was an auto-incrementing number so you had this for free.
>>> However we switched to uuids to better support shares revisions
>>> between different repositories. However we could reintroduce this and
>>> it would resolve any ambiguity issues in the time stamps.
>>
>> Alternatively for MySQL we can probably store the timestamp in some
>> other column type altogether.
[...]

> It might be that the exact table structure we create depends on the
> database system in use; PostgreSQL could use DateTime and MySQL could
> use a float. This might lead to hard to understand code however.
> Alternatively we could translate the float column back to DateTime
> objects on the fly, with a read-only property. But it would still make
> direct queries of the revision table more difficult.

You could implement this at a lower level in sqlalchemy, i.e. by
creating a custom type. In many ways if we are going to support mysql
(despite it's poor timestamp behaviour) I would like to special case
it where possible and not disturb the existing db structure (for
psql/sqlite etc) -- even if this is a bit ugly!

> The alternative approach would be to also add an extra 'order' column
> that automatically increments for each session created. This is
> difficult to do in a database-independent manner though - MySQL for
> instance doesn't support a Sequence construction so this needs to be
> emulated. I also wonder whether we can actually guarantee that
> revisions are always inserted into the database in temporal order.

so that's hopeless :)

> If this guarantee can be made, introducing an autoincrement column
> would be the simplest to gain compatibility between MySQL and
> Postgresql. You mention though that adding uuids had a reason, is this
> working out for you or are you really willing to switch back?

uuids are now being used in several places and switching back would be
quite a significant change.

I note it appears better timestamp precision is planned for mysql.

Rufus