A bit OT: handling multiple users in DB

[Andre Garzia]

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

andre

On Mon, Sep 22, 2008 at 11:52 AM, Klaus Major <klaus at major-k.de> wrote:
> Hi Luis,
>
>> Hiya,
>>
>> It depends on what your concern is: Do you want to allow them both write
>> access?
>
> Yep.
>
>> If they both have the ability to write, then the last to write will be the
>> one visible - This is the standard behaviour.
>> If you want to ensure only one is able to update a field then you lock
>> down their access.
>> If you plan on the need for a rollback, for whatever reason, then you
>> might as well implement a tracking option to list all the changes (I do this
>> for accountability, not for data restore).
>
> Sure, I know this, I was just hoping for an easier solution :-)
> But there obviously isn't one :-/
>
>> Cheers,
>>
>> Luis.
>
> Best
>
> Klaus Major
> klaus at major-k.de
> http://www.major-k.de
>
>
> _______________________________________________
> use-revolution mailing list
> use-revolution at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription
> preferences:
> http://lists.runrev.com/mailman/listinfo/use-revolution
>



-- 
http://www.andregarzia.com All We Do Is Code.