I was inspired by the very capable DBA,
blindman to create a utility that will allow you to shadow data changes in a MySql database. The inspiration came after viewing and using a script created by him to shadow (or archive) data in a MS Sql Server database.
What do I mean by "Shadowing"?
This will probably help a little:
The shadow table is a duplicate of the real table except for two exceptions:
1) Constraints (Primary Keys/Foreign Keys/Data value constraints, etc) are relaxed.
2) A bit field is added to indicate whether or not the row was deleted.
So what the script does is generates the DDL for the shadow table, as well as create scripts for all the needed triggers. These triggers copy the data column for column into the archive table anytime the record is inserted, updated, or deleted. If the record is deleted then the deleted flag is turned on. Very useful functionality if you wanted to have a very strict audit of all data changes or if you wanted to have historical "snapshots" of data.
Getting this functionality in a MySql database
The app that I came up with is very simple. All I needed was:
1) A listing of all tables.
2) A listing of the columns for the tables, as well as data types, lengths, null/not null, and the default value for each.
3) The structure of a simple INSERT/UPDATE/DELETE trigger in MySql.
For #1, MySql provides a very simple "SHOW TABLES" command:
#2 I use the "SHOW COLUMNS FROM <XXX>"
We're half way there. Now all I needed was to learn the syntax for INSERT/UPDATE/DELETE triggers.
It's very simple:
DROP TABLE IF EXISTS sample_arc;
CREATE TABLE `sample_arc` ( `SampleID` int(11) NOT NULL, `SampleText` varchar(100) DEFAULT 'jlkj',
`MoreSampleText` varchar(20) DEFAULT NULL, `EvenMoreSampleText` varchar(255) DEFAULT NULL,
`Flag` bit(1) DEFAULT NULL, `DeletedFlag` bit(1) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TRIGGER IF EXISTS INSERT_sample;
CREATE TRIGGER INSERT_sample AFTER INSERT ON sample FOR EACH ROW INSERT INTO sample_arc
(SampleID,SampleText,MoreSampleText,EvenMoreSampleText,Flag,DeletedFlag)
VALUES (NEW.SampleID,NEW.SampleText,NEW.MoreSampleText,NEW.EvenMoreSampleText,NEW.Flag,0);
DROP TRIGGER IF EXISTS UPDATE_sample;
CREATE TRIGGER UPDATE_sample AFTER UPDATE ON sample FOR EACH ROW INSERT INTO sample_arc
(SampleID,SampleText,MoreSampleText,EvenMoreSampleText,Flag,DeletedFlag)
VALUES (NEW.SampleID,NEW.SampleText,NEW.MoreSampleText,NEW.EvenMoreSampleText,NEW.Flag,0);
DROP TRIGGER IF EXISTS DELETE_sample;
CREATE TRIGGER DELETE_sample AFTER DELETE ON sample FOR EACH ROW INSERT INTO sample_arc
(SampleID,SampleText,MoreSampleText,EvenMoreSampleText,Flag,DeletedFlag)
VALUES (OLD.SampleID,OLD.SampleText,OLD.MoreSampleText,OLD.EvenMoreSampleText,OLD.Flag,1);
The code
I wrote a little quick and dirty application in C# app to get the job done for me. It reads the tables and columns of a given database, and outputs a script to a file that can be run in MySql to create the needed tables and triggers.
For those interested, you can download the source here:
Link
If someone wanted to they could create a provider model for the app so that you could create the same functionality for different database platforms (Sql Server/PostgresSql/Oracle,etc). A user interface could also be built as this app runs in the console.
Edit 3/29/08
After playing around with SubSonic the last few days, I think it would be great to incorporate this functionality into SubStage (A GUI for much of the SubSonic code generation functionality). The provider model is already written for the major databases, and it would be trivial to tie into. There are plenty of other utility-type code generation use cases that could be implemented into this same environment.
