History table using Delegate Class almost there?

[Aoirthoir]

It took a while but I finally understand the delegate class.

I am attempting to set up a History table using a delegate class. Eventually this will be implemented with the stored procedures in MySQL. But the Dataface Delegate class looks like it can handle it immediately if I figure out a couple of things.

So to accomplish this I have a regular Data table. I duplicate that table into a History table and add two fields: H_ID and H_DATETIME. The H_ID is the true autoincrement for the History table. The H_DATETIME will be a timestamp of a change. So whenever a record is added or changed, an exact duplicate gets saved to the H_ table. If a record is Deleted I will probably have to note that also. And of course add fields for USERS..so we can note who did the add/change/delete. But for now I am just trying to get the basic Delegate Class/Trigger code prepared.

Thus I have two tables:

CREATE TABLE `D_TASK` (
`ID` mediumint(8) unsigned zerofill NOT NULL auto_increment,
`TYPE` enum('Task','Project') NOT NULL default 'Task',
`DESCRIPTION` varchar(80) default NULL,
`PRIORITY` enum('URGENT','High','Medium','Low','Whenever','Just a Brain Storm','Nevermind') default NULL,
`STATUS` enum('Assigned','Under Consideration','Started','Halfway','Almost Done','Completed','On Going','Repeating','Delayed','Requires Outside Help','Postponed','Cancelled') default NULL,
`ID_USER_CREATED` mediumint(8) unsigned zerofill default NULL,
`ID_USER_ASSIGNED` mediumint(8) unsigned zerofill default NULL,
`DATE_PROJECTED_START` date default NULL,
`DATE_PROJECTED_FINISH` date default NULL,
`DATE_ACTUAL_START` date default NULL,
`DATE_ACTUAL_FINISH` date default NULL,
`COMMENT` text,
PRIMARY KEY (`ID`)\n) ENGINE=MyISAM DEFAULT CHARSET=latin1



CREATE TABLE `H_TASK` (
`H_ID` int(10) unsigned zerofill NOT NULL auto_increment,
`H_DATETIME` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
`ID` mediumint(8) unsigned zerofill NOT NULL auto_increment,
`TYPE` enum('Task','Project') NOT NULL default 'Task',
`DESCRIPTION` varchar(80) default NULL,
`PRIORITY` enum('URGENT','High','Medium','Low','Whenever','Just a Brain Storm','Nevermind') default NULL,
`STATUS` enum('Assigned','Under Consideration','Started','Halfway','Almost Done','Completed','On Going','Repeating','Delayed','Requires Outside Help','Postponed','Cancelled') default NULL,
`ID_USER_CREATED` mediumint(8) unsigned zerofill default NULL,
`ID_USER_ASSIGNED` mediumint(8) unsigned zerofill default NULL,
`DATE_PROJECTED_START` date default NULL,
`DATE_PROJECTED_FINISH` date default NULL,
`DATE_ACTUAL_START` date default NULL,
`DATE_ACTUAL_FINISH` date default NULL,
`COMMENT` text,
PRIMARY KEY (`ID`)\n) ENGINE=MyISAM DEFAULT CHARSET=latin1


So in planning/tables/D_TASK/D_TASK.php I wish to set up the triggers. Thus far I have the following code:

<---?
class tables_D_TASK {

function block__html_title(){
echo "YO TASKS!";

}

function afterInsert(&$record){

}

function afterSave(&$record){

}

function afterDelete(&$record){

}

}

?--->


So the basic triggers are prepared. Now I just need to add line for the proper SQL to Insert/Select. Or insert using the php variables of the just added record. I would probably move that SQL code to a function of itsown, and have each of the above triggers simply call that function. It would be nice of course to push the entire code base off onto its own class, so that, as Dataface can read all the fields from a table, so too could this class. Then from those fields build the appropriate history insert/select statement on its own. For now though, my SQL looks something like this:


INSERT INTO H_TASK (
`H_DATETIME`,
`ID`,
`TYPE`,
`DESCRIPTION`,
`PRIORITY`,
`STATUS`,
`ID_USER_CREATED`,
`ID_USER_ASSIGNED`,
`DATE_PROJECTED_START`,
`DATE_PROJECTED_FINISH`,
`DATE_ACTUAL_START`,
`DATE_ACTUAL_FINISH`,
`COMMENT`)
SELECT
NOW(),
`ID`,
`TYPE`,
`DESCRIPTION`,
`PRIORITY`,
`STATUS`,
`ID_USER_CREATED`,
`ID_USER_ASSIGNED`,
`DATE_PROJECTED_START`,
`DATE_PROJECTED_FINISH`,
`DATE_ACTUAL_START`,
`DATE_ACTUAL_FINISH`,
`COMMENT`
FROM D_TASK;


So my question is, how to implement an SQL call to do so? Just regular PHP calls or do I have to do something fancy in dataface?

And, would this be something we could add to the tracker (if I have not done so already)?

Thank you kindly.

[njw]

Can't answer your question, but it might be a good idea to have a field in your history record that tells you why you put it there e.g. Record inserted, record amended (perhaps with a list of fields amended), record deleted?

Just a thought.

Neil

[Aoirthoir]

Great thoughts. Thank you kindly Mr. Neil.

I can compare one record to the previous to see which fields were amended but, some method to just tag the fields changed might be easier...perhaps a boolean for every field? This would assist with a field level undo.

Also a field to state whether it is an insert, alter, or delete would be good too. Then we know if it is the first record.

Not sure if this is what you are thinking..basically the ID field in H_ matches the ID in D_. Normally an ID_ is the autoincrement for the specific table. But I wanted to be able to do my instert/selects matching field names exactly....with no questions. So the ID in H_ would determine which record in D_ was changed. I am looking to extend this eventually into a class..so you specify two tables...Dataface determines which fields are common between the two and then updates the history (inserts) whenever the data one is altered (insert/alter/delete). This way, for folks who dont want to track changes to all the fields, just key fields, they could have a much smaller history table.

[njw]

I've used a boolean array to show this in the past and I've also used a different structure for the history table which speeds things up and reduces space. Have a think around your History table being something like:

H_ID - Unique key
D_ID - ID of record that has changed
Change - Add, Amend, Delete
Amended_field
Old_Value
New_Value

And a deleted records table that only holds the deleted records.

If you needed to reconstruct a record you can do so backwards, and the amount of space used is much lower than storing the whole record every time.

(You can tell my age: when I was a real programmer, hardware was expensive, so you did everything in the most efficient way!!! My first professional job was on a PDP 11/47, 24 users on machine with 64kb of memory ...)

Neil