Versioning

[sudsy1970]

Hi all,

 

just a quick query, i have created a very simple blog, where messages can be added, amended or deleted and are stored in a DB called messages.  i now have to implement versioning where a history is kept, versions can be compared and also rolled back to earlier versions.

 

Just wanted some info or ideas on how to do it really, i am guessing that i would have another DB that gets updated when the original DB does, does that sound right  ?

 

cheers for any info

 

sudsy

[The Little Guy]

I would store the post in a text column, then when it gets changed, copy that text with the changes to a new row. Then every time a new entry is added use a timestamp to mark the change. Also give each row a unique id. I would also have a column that marks what version you are using (0 = non-current copy; 1 = current copy)

 

now you just have to do a query.

 

To get the most current

 

SELECT * FROM table WHERE current_copy = 1 [AND ...]

[fenway]

This is a very serious concern -- and in general, a true "rollback" is all but impossible.

[sudsy1970]

Thanks for the info, i have been told that an easy way to do this would be to use a transaction or stored procedure:

 

a) is this true

 

b) how would this look (roughly)

 

C) Can i just save the code as a .sql file as technically it doesn't interact with my code, only when some happens to the database ?

[jdavidbakr]

A transaction is probably not what you're looking for, assuming you're wanting to do something along the lines of Wikipedia where you can revert to a previous version.  Transaction just gives you the opportunity to roll back to where you started, akin to "Revert to saved."  Once saved, "Revert to Saved" no longer gets you further back than the previous saved state.  If you're using a web-based interface to update the database this pretty much hoses any use of a transaction unless you do something to persist the database connection between http requests.

 

Also not sure what you would do with a stored procedure to accomplish this.  Maybe triggers, but you probably are going to be better off just adding the logic to your application code just because triggers can be kind of hairy if you aren't familiar with them.

 

The problem you are presenting is not exactly trivial.  The more power you want the rollbacks to have, the more complicated the solution would be.  If you want to be able to roll back just sections of the text, you're going to have to have a more complex data structure than if you just need to roll back to a date/time for a blog.

 

If the latter is sufficient, here is what I would probably do:

 

You have your blog table.  In the blog table is the primary key, we'll call it blog_id.  Have a column in this table that contains the date and time of the most recent update.  You have another table called blog_versions.  Its primary key could be the blog_id and datetime (or timestamp).  Whenever you update the blog table, first copy the existing row into the blog_versions, including the previous update time.  Then update the blog table, making sure that you update the timestamp column.  Now, you can present the user a list of previous versions and give them the opportunity to choose one (showing the publish time) to override the current version.

 

It actually wouldn't be too difficult to populate the blog_versions with a trigger whenever the blog table is updated.  Look up the mysql docs on creating triggers if you feel up to the task.

[sudsy1970]

Thanks for the reply, i have a very simple table, with an id number, title, message and date added entries, so i am guessing that just getting

another table called versions would be a n easier option. i shall attempt that as i only really need to demo that it can be done, will post the code when i get stuck lol

[sudsy1970]

 

have tried for ages and i am stuck.

 

The code to enter in the info into my blog table is:

 

INSERT INTO messages(title,message,date_added) values(?,?,?) 

 

if i ever get the amend to work it will find it via $id

 SELECT id,title,message FROM messages WHERE id = ?   

 

so if i select the message , insert it to a backup table and also update the original table will i get :

 

 SELECT id,title,message FROM messages WHERE id = ? INSERT INTO messages_audit(title,message,date_added) values(?,?,?)  AND UPDATE messages(title,message,date_added) values(?,?,?)          

 

If this is right can i use a simialr approach for delete and add ?

 

[ignace]

CREATE TABLE Posts (
  post_id ..
);

CREATE TABLE PostsRevisions (
  post_id ..
  version .. AUTO_INCREMENT,
  ..
  PRIMARY KEY (post_id, version)
);

CREATE TRIGGER CreatePostRevision BEFORE UPDATE ON Posts
FOR EACH ROW BEGIN
  INSERT INTO PostsRevision (..) VALUES (OLD.<column-name>, ..)
END;

 

This will keep the current revision in the Posts table and available revisions in the PostsRevisions table.

[ignace]

It should be noted that it's up to you to implement that only one given person is allowed to edit a specific post at any given time (to avoid 2 people overwrite each others work and both are given a different revision or you could complicate it more and implement merging and conflict resolution).

[sudsy1970]

ok have had a go and have got the following does that look right ?
[code
]CREATE TRIGGER CreatePostRevision BEFORE UPDATE ON messages
FOR EACH ROW BEGIN
INSERT INTO messages_audit (title,message,date_added) values(?,?,?) VALUES (OLD.<title>,OLD.<message>,OLD.<date_added>)
END;

 

Now can i just write another trigger for a delete ?

where does the code sit, do i just make a sql folder and leave it in there as i am thinking that this will always trigger whenever the update/delete is executed ?

 

Thanks for your patience

 

[fenway]

In the general case, these simple implementations are nowhere near sufficient -- this assumes that nothing every references the blog table.

[sudsy1970]

Well it didn't work anyway and this is only to show it can be done for a project, if i can get it working it could provide extra points.

 

i have a php function that calls the sql code so might just see if i can add to that to back up the record i am deleting.

[ignace]

Well it didn't work anyway

 

Copy-pasting won't get you anywhere. If you would have taken the time to look up triggers you would have found that it was missing a ; after the insert statement and you need to specify a different delimiter then the default one.

 

In the general case, these simple implementations are nowhere near sufficient -- this assumes that nothing every references the blog table.

 

True, but this was the most simplest design I could come up with at the time. Another approach would be the one Wordpress employs each revision is a descendant of the current version as discussed on Wordpress Codex: Revision Management.

 

Are there any SQL patterns for this particular problem? Or better solutions available then the one presented by Wordpress?

[ignace]

It seems their is "Audit Trails" as found on MySQL forums: http://forums.mysql.com/read.php?20,271696,271699#msg-271699

[fenway]

Ultimately, the most robust way is to effectively duplicate the row with every change -- how you decide to manage the duplication is up to you.

 

Of course, as I mentioned earlier, maintaining a relational DB with versioning is a very Hard problem, and working around it requires a variety of assumptions and limitations, based on your particular scenario

[ignace]

Ultimately, the most robust way is to effectively duplicate the row with every change -- how you decide to manage the duplication is up to you.

 

Too bad you can't create a MySQL partition and tell MySQL to store all revisions on the other partitions

[fenway]

In general, the storage engines of MySQL are designed for just that -- storage -- and anything even remotely useful requires black magic.

[sudsy1970]

Copy-pasting won't get you anywhere. If you would have taken the time to look up triggers you would have found that it was missing a ; after the insert statement and you need to specify a different delimiter then the default one.

 

 

I did look up triggers and found it very confusing which is why i asked for help in the first place.

 

Perhaps the scorn passed from you is not worth the extra mark i could receive !

 

[sudsy1970]

Ok so i have tried again, basically what needs to happen is

 

Select * from messages where ID = ?

THEN

Insert into messages_audit (info from above

THEN

 delete from messages where ID = ?

 

Have tried to create a trigger to make this happen which goes like:

mysql> Delimiter //
mysql> CREATE TRIGGER messages_bd
-> BEFORE DELETE ON messages
->FOR EACH ROW
->BEGIN
->INSERT INTO messages_audit (id,title,message,date_added) VALUES (SELECT * FROMmessages WHERE ID = ?);
->END//

 

A) does this seem to be ok

 

B) i get the error code

]#1064 you have an error in you SQL syntax, check the manual for right syntax to use near 'mysql>CREATE TRIGGER messages_bd -> BEFORE DELETE ON messages->FOR' near line 1

 

Anyone got any ideas please ?

[fenway]

Why the ?

[sudsy1970]

Because of :

 

Advanced Web Technologies

Assessment 1 – Website marking sheet

 

Student name and number:

Marker:

 

Mark and comments

Basic functionalities

- Articles can be added and viewed 1 point

- Articles can be amended and deleted 1 point

- You can search for articles 1 point

(3 points)

OO Programming and Patterns

- You have used OO programming techniques (inheritance, static methods, abstract classes etc.) when appropriate. 1 point

- Your website is based on a suitable Architectural Pattern (1 point for the example featured on the Wiki) 3 points

(4 points)

Versioning

- A history of versions is kept 1 point

- Versions can be compared 1 point

- Articles can be rolled back to older versions 1 point

(3 points)RSS

- The website is consuming (parsing and appropriately displaying) an existing feed. 1 point

- The website is dynamically producing its own compliant web feed. 1 point

(2 points)

API

- The website makes use of an API to offer useful functionality (e.g. search, spelling, social networking, ecommerce, etc.))

 

Note: Reusing the examples from the Wiki will NOT grant you any points! 1 point for simple use (e.g. widget)

2 points for complex REST/SOAP request

(2 points)

Ajax

- The user experience is improved by appropriate use of Ajax technology (live search, form filling assistance…)

1 point per Ajax feature, up to a max. of 2 points

(2 points)

Total ________ out of 16

 

 

[fenway]

No, I meant why the question mark in your code samples.

 

But if it's just for a course, do the bare minimum, you don't need a real-world, live-data solution.

[jdavidbakr]

B) i get the error code

]#1064 you have an error in you SQL syntax, check the manual for right syntax to use near 'mysql>CREATE TRIGGER messages_bd -> BEFORE DELETE ON messages->FOR' near line 1

 

Anyone got any ideas please ?

 

Are you typing in the prompts?

[sudsy1970]

No, I meant why the question mark in your code samples.

  that is because when the message amend is selected the id is passed via php so the table knows which entry to get. 

 

I will check on whether i have entered the code correctly, not at pc at present.

 

Can i just check i have a folder called assignment with all my files for the blog, if i just create the triggers i need and save them in the same folder, will they automatically 'trigger' or do i need to call them, if so how ?

[fenway]

Sorry, I don't follow.