Vagueness and patched strings

Yeah, I ended up being a little vague after writing a really long one trying not to cloud it. Erg.

Doing multiple records/rows and then using the most recent did occur to me but I figured there might be a lower memory way or A Way It Is Done(tm). My conception of how I would do it if I could get it was:

create table changes (
     id  char(10),  # DB's other tables all have uniquely prefixed ids
     diff mediumtext,
     timestamp datestamp
);
[download]

And then stringify + concat all the new values of the cols, diff it against the old... It would need to do diffs and patches on strings. Text::Diff looks helpful for the first part at least. Patching (reverting) doesn't seem as obvious.

Now that this problem is broken down against the simplicity of the problem (as DaveH and Corion mention) I think repeated records would be easier to implement less and prone to problems over all. Thanks!

Comment on Vagueness and patched strings Download Code

Replies are listed 'Best First'.
Re: Vagueness and patched strings by ryantate (Friar) on Jul 21, 2004 at 18:20 UTC
If you do ever decide to try and implement a diff system (I have thought about this problem a lot myself), remember to think about how your system will handle two simultaneous updates to a piece of content. There could be a race condition in the system you've outlined where, as you are doing the SELECTing and diffing required to generate the new change entry, a new row is entered in `changes`, for example from an update begun just before yours, for the same id. Now your diff is totally wrong. Also, I don't know about the latest versions of MySQL, but in my 3.x datestamp only is granular to the second level, and it is not inconceivable for a central piece of content on a busy site like a Wiki to get two changes entered within the same one-second window, so there would be no way to select which change to apply first because they both have the same timestamp. (Although this eventuality could be mitigated by designating PRIMARY KEY(id, timestamp) which should throw an error on simultaneous update)	[reply] [d/l]
Re^2: Vagueness and patched strings by Your Mother (Archbishop) on Jul 22, 2004 at 09:10 UTC
Good thoughts, and from others above. Thanks, as usual to all. I'm on MySQL 4.whatever but trying to keep things pretty generic just in case. The sad part is, the last place I worked did just what I want (within a Berkeley DB) and I wasn't interested in checking out the code at the time so I have no idea how it worked.	[reply]
Re^3: Vagueness and patched strings by jimrobertsiii (Scribe) on Jul 24, 2004 at 04:33 UTC
Not sure if it will work, but you could try diff'ing the backups of your Mysql database. Since a Mysql database backup is simply the DML (read SQL commands) to recreate the database, this may work. Now this idea assumes that Mysql will create the DML in the same order every time, but it might be worth a shot. -Jim	[reply]
Re: Vagueness and patched strings by jimrobertsiii (Scribe) on Jul 21, 2004 at 04:11 UTC
There doesn't seem to be any mention of which RDBMS you're using. If you are using MS SQL Server I can suggest that you check out Red Gate's offering. They have tools that will compare both data and schema and there is also a programmer's toolkit for .NET if you care to depart from our beloved Perl :-) -Jim	[reply]