#dbix-class

[armitige3]

Hey, How’s About I Actually Post About Coding?

So, yeah.  I’m a programmer; a web developer; full-stack developer; software developer... meh, it depends upon the task, I suppose.  I build everything from one-off task solutions to full-stack web sites.  My primary language of choice (although I’m trying to expand that) is Perl (object-oriented), usually using the Dancer2 web framework and DBIx::Class library to speak to databases, running on some flavor of Linux and under Apache, plackup, nginx or starfish.

I also do front-end work, mostly working with either the Bootstrap framework or Foundation, with javascript and jQuery under the hood.

Right now, I’ve got about 4 projects sitting on my desk, two contracted, two personal.  My primary project is working for ISC out in California (they’re the guys who made BIND, the stuff that makes the internet work).  It’s my main money-maker.  My second contract is with a small, local store, The Quilt Patch.  I maintain their website, post class updates, and other maintenance stuff.  Little do they know, I’ve been working in my spare time to completely rewrite their (horrible) website to be current with modern practices, use responsive design, and give some better customer support and capabilities.  Essentially, I’m dragging them kicking and screaming into the present.

After that, my two priority projects are trying to launch my game company, Infinite Monkeys Games, and get my online art gallery back online, and updated to modern standards.

It often feels overwhelming, especially since I’m also a stay-at-home parent.  But, when things work and are finished, it’s an awesome feeling.

[davorg]

Training Courses - More Details

Training Courses – More Details

Last week I mentioned the public training courses that I’ll be running in London next February. A couple of people got in touch and asked if I had more details of the contents of the courses. That makes sense of course, I don’t expect people to pay £300 for a days training without knowing a bit about the syllabus. So here are details of the first two courses (the Moose one and the DBIx::Class…

[gnuthought]

History Tables with MariaDB/MySQL - 3 - History Tables and Triggers

Last time we showed the data tables and the triggers to auto-update history fields in those tables. Now we’re going to look at how to implement separate history tables the triggers to populate them. This time we will add logging the SQL query that triggered the change.

We'll start with simpler source data tables this time. We'll keep just a revision, modification time, and create time.

/* Drop tables in the right sequence before recreating. */ DROP TABLE IF EXISTS Service; DROP TABLE IF EXISTS User; CREATE TABLE `User` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Numeric user identifier.', `name` varchar(64) NOT NULL COMMENT 'Unique login name for user.', `email` varchar(64) NOT NULL COMMENT 'User email address.', `admin` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Boolean indicating user is an administrator.', `revision` int(10) unsigned NOT NULL COMMENT 'Revision count for User.', `mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Timestamp of User last change.', `ctime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Timestamp of User creation.', PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Application User.'; CREATE TABLE `Service` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Numeric service identifier.', `name` varchar(64) NOT NULL COMMENT 'Unique text service identifier.', `description` text NOT NULL COMMENT 'Service description.', `owner_id` int(10) unsigned NOT NULL COMMENT 'Foreign key to user that owns service.', `revision` int(10) unsigned NOT NULL COMMENT 'Revision count for Service.', `mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Timestamp of Service last change.', `ctime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Timestamp of Service creation.', PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`), KEY `Service_owner` (`owner_id`), CONSTRAINT `Service_owner` FOREIGN KEY (`owner_id`) REFERENCES `User` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Service belonging to a user.';

We'll again want some triggers that run before insert and update, but just setting the revision and timestamps.

delimiter // DROP TRIGGER IF EXISTS before_insert_User; CREATE TRIGGER before_insert_User BEFORE INSERT ON User FOR EACH ROW BEGIN /* Force overide values of revision, mtime, and ctime. */ SET NEW.revision = 0; SET NEW.ctime = CURRENT_TIMESTAMP; SET NEW.mtime = CURRENT_TIMESTAMP; END // DROP TRIGGER IF EXISTS before_update_User; CREATE TRIGGER before_update_User BEFORE UPDATE ON User FOR EACH ROW BEGIN /* Force overide values of revision, mtime, and ctime. */ SET NEW.revision = OLD.revision + 1; SET NEW.ctime = OLD.ctime; SET NEW.mtime = CURRENT_TIMESTAMP; END // DROP TRIGGER IF EXISTS before_insert_Service; CREATE TRIGGER before_insert_Service BEFORE INSERT ON Service FOR EACH ROW BEGIN /* Force overide values of revision, mtime, and ctime. */ SET NEW.revision = 0; SET NEW.ctime = CURRENT_TIMESTAMP; SET NEW.mtime = CURRENT_TIMESTAMP; END // DROP TRIGGER IF EXISTS before_update_Service; CREATE TRIGGER before_update_Service BEFORE UPDATE ON Service FOR EACH ROW BEGIN /* Force overide values of revision, mtime, and ctime. */ SET NEW.revision = OLD.revision + 1; SET NEW.ctime = OLD.ctime; SET NEW.mtime = CURRENT_TIMESTAMP; END // delimiter ;

Now we're ready to make our history tables. What we do is take our definitions of our source data tables and make just a few tweaks:

Make the id column no longer AUTO_INCREMENT.

Make the primary key a combination of the id and the revision.

Remove foreign key relationships.

Remove UNIQUE keys or convert them to non-unique.

Remove NOT NULL constraints and have columns default to NULL except for id, revision, mtime, and ctime.

Add columns to track user, application user, SQL action, and SQL statement.

For this to work as shown we need the column definitions to be the same types as in the source tables. Care must be taken when modifying the source tables to make the same changes to the history tables.

We make the id column to store the value of the source id, but without a foreign key relationship. We are doing this so that it will be possible to delete data out of our source tables without removing the corresponding history entries. Do note that even though the source data can be deleted, this design will not allow reuse of id values. Reusing an id is probably a bad idea anyway. If you wanted to support id reuse (or update), then we would put a cascading foreign key relationship on our history tables. The other route would be to have a hard foreign key relationship on our history tables to simply prevent deletes or changes of id in our source tables. This is a common design approach to not actually delete entries out of a table, but simply set a deleted or inactive boolean flag on the row.

I suggest removing unique constraints so that restrictions on fields like name in the User table can be reused. Again, that's a design decision on whether you want to allow that sort of thing.

I am also suggesting removing NOT NULL constraints and have unset columns default to NULL as a general approach to track data values that were not collected historically. This isn't important initially, but is more to do with how you add columns later on. If you add a column to the source tables that does not allow NULL, your history tables still should allow NULL so that when you modify your history tables you don't make it look like there was data in that column before.

Here's what our tables will look like:

/* Drop tables before recreating. */ DROP TABLE IF EXISTS UserHistory; DROP TABLE IF EXISTS ServiceHistory; CREATE TABLE `UserHistory` ( `id` int(10) unsigned NOT NULL COMMENT 'Numeric user identifier.', `name` varchar(64) COMMENT 'Unique login name for user.', `email` varchar(64) COMMENT 'User email address.', `admin` tinyint(1) COMMENT 'Boolean indicating user is an administrator.', `revision` int(10) unsigned NOT NULL COMMENT 'Revision count for User.', `mtime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Timestamp of User last change.', `ctime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Timestamp of User creation.', `user` varchar(256) NOT NULL COMMENT 'Database user & host that made this change.', `appuser` varchar(64) NOT NULL COMMENT 'Application user that made this change.', `action` enum('insert','update','delete') NOT NULL COMMENT 'SQL action.', `stmt` longtext NOT NULL COMMENT 'SQL Statement that initiated this change.', PRIMARY KEY (`id`,`revision`), KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='User history.'; CREATE TABLE `ServiceHistory` ( `id` int(10) unsigned NOT NULL COMMENT 'Numeric service identifier.', `name` varchar(64) COMMENT 'Unique text service identifier.', `description` text COMMENT 'Service description.', `owner_id` int(10) unsigned COMMENT 'Foreign key to user that owns service.', `revision` int(10) unsigned NOT NULL COMMENT 'Revision count for Service.', `mtime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Timestamp of Service last change.', `ctime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Timestamp of Service creation.', `user` varchar(256) NOT NULL COMMENT 'Database user & host that made this change.', `appuser` varchar(64) NOT NULL COMMENT 'Application user that made this change.', `action` enum('insert','update','delete') NOT NULL COMMENT 'SQL action.', `stmt` longtext NOT NULL COMMENT 'SQL Statement that initiated this change.', PRIMARY KEY (`id`,`revision`), KEY `name` (`name`), KEY `Service_owner` (`owner_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Service history.';

The four fields we've added, and will populate with our triggers are user, appuser, action, and stmt. Let's discuss each of these in turn before putting it all together with our triggers.

The user column will store the value returned by a call to the function USER(). This will give the user name and host that connected and authenticated to the database. If you're managing your database users effectively, then this should tell you exactly what application or script made the change and from where it ran.

The appuser is additional information that the application can provide to tell us about who was using the application at the time. It could be a username authenticated to a web interface or the login name of a user who launched a shell script. We'll require that any code making an update have a session variable @appuser set.

The action is simply insert, update, or delete as corresponds to the trigger which creates the history entry.

The stmt is the SQL statement as retrieved from the database engine by using:

SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID()

Here are our triggers all put together:

delimiter // DROP TRIGGER IF EXISTS after_insert_User // CREATE TRIGGER after_insert_User AFTER INSERT ON User FOR EACH ROW BEGIN DECLARE stmt longtext; SET stmt = (SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID()); INSERT INTO UserHistory ( id, name, email, admin, revision, mtime, ctime, user, appuser, action, stmt ) VALUES ( NEW.id, NEW.name, NEW.email, NEW.admin, NEW.revision, NEW.mtime, NEW.ctime, USER(), @appuser, 'insert', stmt ); END // DROP TRIGGER IF EXISTS after_update_User // CREATE TRIGGER after_update_User AFTER UPDATE ON User FOR EACH ROW BEGIN DECLARE stmt longtext; SET stmt = (SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID()); INSERT INTO UserHistory ( id, name, email, admin, revision, mtime, ctime, user, appuser, action, stmt ) VALUES ( NEW.id, NEW.name, NEW.email, NEW.admin, NEW.revision, NEW.mtime, NEW.ctime, USER(), @appuser, 'update', stmt ); END // DROP TRIGGER IF EXISTS after_delete_User // CREATE TRIGGER after_delete_User AFTER DELETE ON User FOR EACH ROW BEGIN DECLARE stmt longtext; SET stmt = (SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID()); INSERT INTO UserHistory ( id, name, email, admin, revision, mtime, ctime, user, appuser, action, stmt ) VALUES ( OLD.id, OLD.name, OLD.email, OLD.admin, OLD.revision+1, OLD.mtime, OLD.ctime, USER(), @appuser, 'delete', stmt ); END // DROP TRIGGER IF EXISTS after_insert_Service // CREATE TRIGGER after_insert_Service AFTER INSERT ON Service FOR EACH ROW BEGIN DECLARE stmt longtext; SET stmt = (SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID()); INSERT INTO ServiceHistory ( id, name, description, owner_id, revision, mtime, ctime, user, appuser, action, stmt ) VALUES ( NEW.id, NEW.name, NEW.description, NEW.owner_id, NEW.revision, NEW.mtime, NEW.ctime, USER(), @appuser, 'insert', stmt ); END // DROP TRIGGER IF EXISTS after_update_Service // CREATE TRIGGER after_update_Service AFTER UPDATE ON Service FOR EACH ROW BEGIN DECLARE stmt longtext; SET stmt = (SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID()); INSERT INTO ServiceHistory ( id, name, description, owner_id, revision, mtime, ctime, user, appuser, action, stmt ) VALUES ( NEW.id, NEW.name, NEW.description, NEW.owner_id, NEW.revision, NEW.mtime, NEW.ctime, USER(), @appuser, 'update', stmt ); END // DROP TRIGGER IF EXISTS after_delete_Service // CREATE TRIGGER after_delete_Service AFTER DELETE ON Service FOR EACH ROW BEGIN DECLARE stmt longtext; SET stmt = (SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID()); INSERT INTO ServiceHistory ( id, name, description, owner_id, revision, mtime, ctime, user, appuser, action, stmt ) VALUES ( OLD.id, OLD.name, OLD.description, OLD.owner_id, OLD.revision+1, OLD.mtime, OLD.ctime, USER(), @appuser, 'delete', stmt ); END // delimiter ;

This code may look a bit tedious with all of the column names listed explicitly. We list all the columns rather than using a SELECT * approach so that we can add a column to our source table without taking the database down. With this design we can add the column to the source table, then the history table, and then update the trigger. It would be tedious if we had to maintain it by hand, but we don't have to do that! Before we're done with this series I'll show you how to automate the creation of your history tables and triggers.

Some more things to point out about those triggers:

On insert and update we get the value of mtime for the history from the parent table. That way we know our timestamps will match.

We need to store the value of the original query statement in a separate local variable instead of a subquery to get the value we really want.

On delete there is no new value, so we'll increment the revision from the old and set the mtime to CURRENT_TIMESTAMP.

Let's see it in action:

MariaDB [example]> INSERT INTO User (name,email) VALUES ('jtk','jtk@uic.edu'); Query OK, 1 row affected, 1 warning (0.09 sec) MariaDB [example]> UPDATE User SET admin=TRUE WHERE name='jtk'; Query OK, 1 row affected (0.23 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [example]> SELECT * FROM User; +----+------+-------------+-------+----------+---------------------+---------------------+ | id | name | email | admin | revision | mtime | ctime | +----+------+-------------+-------+----------+---------------------+---------------------+ | 1 | jtk | jtk@uic.edu | 1 | 1 | 2015-03-14 11:44:02 | 2015-03-14 11:43:39 | +----+------+-------------+-------+----------+---------------------+---------------------+ 1 row in set (0.00 sec) MariaDB [example]> SELECT * FROM UserHistory; +----+------+-------------+-------+----------+---------------------+---------------------+----------------+---------+--------+------------------------------------------------------------+ | id | name | email | admin | revision | mtime | ctime | user | appuser | action | stmt | +----+------+-------------+-------+----------+---------------------+---------------------+----------------+---------+--------+------------------------------------------------------------+ | 1 | jtk | jtk@uic.edu | 0 | 0 | 2015-03-14 11:43:39 | 2015-03-14 11:43:39 | root@localhost | jtk | insert | INSERT INTO User (name,email) VALUES ('jtk','jtk@uic.edu') | | 1 | jtk | jtk@uic.edu | 1 | 1 | 2015-03-14 11:44:02 | 2015-03-14 11:43:39 | root@localhost | jtk | update | UPDATE User SET admin=TRUE WHERE name='jtk' | +----+------+-------------+-------+----------+---------------------+---------------------+----------------+---------+--------+------------------------------------------------------------+ 2 rows in set (0.00 sec) MariaDB [example]> INSERT INTO Service (name,owner_id) VALUES ('a-service-name',1); Query OK, 1 row affected, 2 warnings (0.09 sec) MariaDB [example]> UPDATE Service SET description='Some ipsum...'; Query OK, 1 row affected (0.08 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [example]> SELECT * FROM Service; +----+----------------+---------------+----------+----------+---------------------+---------------------+ | id | name | description | owner_id | revision | mtime | ctime | +----+----------------+---------------+----------+----------+---------------------+---------------------+ | 1 | a-service-name | Some ipsum... | 1 | 1 | 2015-03-14 12:02:08 | 2015-03-14 12:02:03 | +----+----------------+---------------+----------+----------+---------------------+---------------------+ 1 row in set (0.00 sec) MariaDB [example]> DELETE FROM Service WHERE id=1; Query OK, 1 row affected (0.21 sec) MariaDB [example]> SELECT * FROM ServiceHistory; +----+----------------+---------------+----------+----------+---------------------+---------------------+----------------+---------+--------+-----------------------------------------------------------------+ | id | name | description | owner_id | revision | mtime | ctime | user | appuser | action | stmt | +----+----------------+---------------+----------+----------+---------------------+---------------------+----------------+---------+--------+-----------------------------------------------------------------+ | 1 | a-service-name | | 1 | 0 | 2015-03-14 12:02:03 | 2015-03-14 12:02:03 | root@localhost | jtk | insert | INSERT INTO Service (name,owner_id) VALUES ('a-service-name',1) | | 1 | a-service-name | Some ipsum... | 1 | 1 | 2015-03-14 12:02:08 | 2015-03-14 12:02:03 | root@localhost | jtk | update | UPDATE Service SET description='Some ipsum...' | | 1 | a-service-name | Some ipsum... | 1 | 2 | 2015-03-14 12:02:08 | 2015-03-14 12:02:03 | root@localhost | jtk | delete | DELETE FROM Service WHERE id=1 | +----+----------------+---------------+----------+----------+---------------------+---------------------+----------------+---------+--------+-----------------------------------------------------------------+ 3 rows in set (0.00 sec)

Next up we'll see how to automate the creation of your history tables and triggers.

Continued...