Hi.
On Tue 2002-07-23 at 16:19:43 +0100, [EMAIL PROTECTED] wrote:
> Hi.
>
> Thought I'd try sending this again as no takers for my earlier post!
>
> Has anyone got any good ideas or examples about doing Version Control on
> a MySQL table? I'd like all changes to fields to be recoverable back to
> any date-time in the past, without changing the current state of the
> database (therefore, not involving any actual restore of db backups).
>
> My current idea is to maintain a change-log table, something like {
> TABLE_NAME, FIELD_NAME, OLD_VALUE, TIMESTAMP, WHO_BY }, to be updated
> each time a field is changed. This ought to allow a script to rollback
> all changes to a table or a field, but it feels a bit simplistic and
> kludgy - does anyone have any more cunning ideas?
Your suggestion sounds reasonable, depending on your needs.
> OTOH, maybe the database's own logs contain enough info to extract the
> 'old' state of the table? Is this something anyone else has done?
You can activate logs which would be good enough to restore earlier
state, but it would be a lot of work to use them this way, as the
changes are not necessarily row-centric (i.e. UPDATE on all rows).
> All bright ideas gratefully received.
An alternative - not necessarily better - approach could be to never
replace any row, but insert new rows and only use the rows with the
newest date. Depending on your queries this could make everything
easier or much more complicated.
A variant of this, which is more flexible is to make two table, an
archive one and a current one. If you update the current one, move
(INSERT INTO ... SELECT; DELETE) the old row to the archive table,
insert the new row. This makes the current table fast and
uncomplicated as in your suggestion.
Define a MERGE table over the archive and current table and you can
retrieve any version with a simple SELECT.
As I said, which approach is best for you, depends on your needs. My
suggestion performs bad (especially disk-usage-wise), if you have a
lot of small changes. It's main advantage is that you have really fast
access to old version and need no explicit mechanism to retrieve old
version.
If you main need is some kind of back-up, or as with CVS mainly the
most recent version is of interest, your suggestion will fit better,
because it will save a lot of space. Additionally it's easier with
your suggestion to track changes on particular fields.
I do not think that there is an really "elegant" solution.
Greetings,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
