February 11, 2010

Using P4toDB to store history for 'p4 counters'

Traceability

P4toDB presents a lot of opportunity to do interesting things with your data that was not possible before. For instance, many SQL servers have a trigger system (similar to what the Perforce server provides) that you can take advantage of to do more or less arbitrary things.

In this example, I'll illustrate how to make MySQL triggers that version the history for the 'p4 counters' command. Counters are an unversioned key/value store on the Perforce server. Basic counter usage does not require history, but it might be nice to have in some situations.

Note that when doing any work that involves writing to the P4toDB-controlled database, that it's important to avoid modifying the replicated data itself, as P4toDB expects it to be the way it left it. One can also break replication or make it slow via a trigger, so beware.

First up, we need a new table to store the data in. The `name` column is the key and will never change. The only new data that we add to this table is the time when the `value` was modified. I'm assuming you've already populated the database and replication is currently running, and that you've got the MySQL command-line tool open with administrative access.

CREATE TABLE `counter_history` ( `name` NVARCHAR(2000), `value` LONGTEXT, `modified` DATETIME );

Now we'll create three new triggers, one to record newly added counters, one for updates to existing ones, and one that runs when a counter is removed. Two commands you'll need to know about when playing around with these things are SHOW TRIGGERS; and DROP TRIGGER `trigger_name`;.

Here's the trigger to catch newly-added counters. MySQL presents the old and new values for a row with the "OLD" and "NEW" variables. Note that you should use the "AFTER" trigger, not "BEFORE", since there's less opportunity to accidentally modify the replicated data.

DELIMITER //
CREATE TRIGGER `counter_history_insert_trigger` AFTER INSERT ON `counters` FOR EACH ROW BEGIN
INSERT INTO `counter_history` VALUES ( NEW.name, NEW.value, NOW() );
END//
DELIMITER ;

The trigger to catch modified counters has some extra logic. The job, jobreview, journal, logger and review counters are all reserved by the Perforce server and aren't very interesting to track. There are others I've ommitted, like the upgrade counter, which might be worth recording.

The 'change' counter is special-cased, with the reason being that under normal circumstances it will always grow by one, but if it doesn't (e.g. it went down), it's almost always a human mistake with ill-consequences to follow, so it's also worth tracking. Note that even though the `value` column's type is varchar, that MySQL treats it as a number when used in the numeric context below.

DELIMITER //
CREATE TRIGGER `counter_history_update_trigger` AFTER UPDATE ON `counters` FOR EACH ROW BEGIN
IF OLD.name <> 'change'    AND
   OLD.name <> 'job'       AND
   OLD.name <> 'jobreview' AND
   OLD.name <> 'journal'   AND
   OLD.name <> 'logger'    AND
   OLD.name <> 'review'     OR
  (OLD.name = 'change' AND (1 + OLD.value) <> NEW.value)
                                THEN
       INSERT INTO `counter_history` VALUES ( OLD.name, NEW.value, NOW() );
END IF;
END//
DELIMITER ;

The only thing to point out on the delete-trigger is that the word "deleted" is the value added to the counter history table.

DELIMITER //
CREATE TRIGGER `counter_history_delete_trigger` AFTER DELETE ON `counters` FOR EACH ROW BEGIN
INSERT INTO `counter_history` VALUES ( OLD.name, "deleted", NOW() );
END//
DELIMITER ;

Before going to examples, be aware that it's easy to make a mistake with a trigger that will break stuff. For instance, if P4toDB exits and you find the following entry at the end of its log file after creating a trigger, you should go examine what you did.

Feb 10, 2010 8:32:31 AM com.perforce.p4todb.Messages log
SEVERE: SQL exception:  Unknown column 'name' in 'field list'
	SQL state:  42S22
	Vendor error code:  1,054
	Fetched from position:  1/9269 0 true true
	Lines:  1

In my case, the error was caused by not prefixing the `name` column correctly. I used:

if    `name` <> 'change'

Instead of:

if OLD.name  <> 'change'

On to the examples. First, see what you've already got on the Perforce server side:

cmd> p4 counters
change = 12
journal = 1
upgrade = 21

And on the SQL server:

sql> select * from counter_history;
Empty set (0.00 sec)

Now, create a new key/value pair in Perforce:

cmd> p4 counter a_new_string "one two three"
Counter a_new_string set.

And see the insert-trigger working:

sql> select * from counter_history;
+--------------+---------------+---------------------+
| name         | value         | modified            |
+--------------+---------------+---------------------+
| a_new_string | one two three | 2010-02-10 09:28:15 |
+--------------+---------------+---------------------+

Then make some more changes to observe the update-trigger:

cmd> p4 counter a_new_string "four five six"
Counter a_new_string set.
sql> select * from counter_history;
+--------------+---------------+---------------------+
| name         | value         | modified            |
+--------------+---------------+---------------------+
| a_new_string | one two three | 2010-02-10 09:28:15 |
| a_new_string | four five six | 2010-02-10 09:28:45 |
+--------------+---------------+---------------------+

And now the delete:

cmd> p4 counter -d a_new_string
Counter a_new_string deleted.
sql> select * from counter_history;
+--------------+---------------+---------------------+
| name         | value         | modified            |
+--------------+---------------+---------------------+
| a_new_string | one two three | 2010-02-10 09:28:15 |
| a_new_string | four five six | 2010-02-10 09:28:45 |
| a_new_string | deleted       | 2010-02-10 09:29:30 |
+--------------+---------------+---------------------+

And there you have it! Triggers can be a useful tool when you want to capture every change made. But if that's not a requirement, then it'll be easier to use a sampling-based approach to exporting the data to tables you control.