A bit OT: handling multiple users in DB

[Klaus Major]

Hi Andre,

> Klaus,
>
> if you just want to track changes, like, "hey, my cursor is outdated,
> need to fetch again!", use a table for revisions, this table has at
> least three fields, user who made the change, SQL used for the change,
> hash or unique id for the revision. When anyone is trying to write,
> read, or navigate a previous cursor, check their revision against the
> last one in the database. If they differ, then there's something out
> dated.
>
> If you wanted to be really neat with this, use an extra field for
> storing the SQL command that undo the change just made, so if you're
> creating a record like
>
> user_id: 111
> revision_hash: some-big-integer
> SQL_command: insert into favoriteColors(user_id, color) values  
> ('111','blue');
>
> You also store something like:
>
> SQL_undo_command: remove from favoriteColors where user_id='111' and
> color='blue';
>
> Or better, you pick the id of the record created by the SQL command
> and use a remove from with the specific id, this way, you'll be able
> to rollback to any place you want in time or see a detailed picture of
> what changed since your last revision. For example if you use a
> revision id as an auto increment integer, and your cursor is like
> revision 1 and your software noticed that all of a sudden you're on
> revision 5, you know that there were four SQL commands in between, you
> can inspect the revision table to see what changed, roll them back or
> simply refetch the cursor.
>
> It's easier than it appears...

Very interesting thoughts, will surely think about them.

Thanks!

> andre


Best

Klaus Major
klaus at major-k.de
http://www.major-k.de