MySQL offers a great feature which is the possibility to implement stored routines. Stored routines allows to code all the logic related to the database model directly in SQL. Stored routines offer :
- Security : Stored routines are in general not vulnerable to SQL injection attacks.
- Data coherency : All the database update logic is located within the stored routines. Thus, the application layer don’t need to have the knowledge of the database internals like the relation between tables or the different tables that need to be updated for a given operation.The database is exposed to the application as a set of procedures. This concept is very close to the API (Application Programming Interface) one.
- Performances: as the SQL logic is directly embedded inside the database, performances are maximal.
For all these reasons, it is generally a very good idea to use as much as possible stored routines, at least for all the UPDATE operations. We will see in this article how stored routines can be very useful and practical to edit a table and to keep track of all the modifications (who modified a record, how and when ?) in a separate historization table.
I. A bit of ternary logic
The main difficulty I faced to establish a historization logic is the ternary logic of MySQL. MySQL distinguishes indeed the ZERO value (0 for INTEGER, ” for VARCHAR, ’0000-00-00′ for DATES) from the NULL value. If A is equal to NULL and B is not equal to NULL, than the test A!=B will return FALSE !
Therefore, the test A!=B has to be translated in SQL to:
( A != B ) OR (A IS NOT NULL AND B IS NULL) OR (A IS NULL AND B IS NOT NULL)
II. Use case
The following use case will serve as a basis to establish the historization logic. Let consider a simple SQL table named users having five different fields :
mysql> desc users;
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| first_name | varchar(50) | YES | | NULL | |
| last_name | varchar(50) | NO | | NULL | |
| is_staff | tinyint(1) | NO | | 0 | |
| birth_date | date | YES | | NULL | |
+------------+------------------+------+-----+---------+----------------+
Id is the primary key, first_name and last_name are of type VARCHAR, is_staff is boolean, birthday is a date.
The goal is to define a stored routine named edit_users that will be the unique procedure to call to perform modifications on table users. This same procedure will historize all the modifications performed on users in a table called users_history :
mysql> desc users_history;
+-----------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| requestor_id | int(10) unsigned | NO | | NULL | |
| user_id | int(11) | NO | | NULL | |
| action_flag | varchar(40) | NO | | NULL | |
| order_date | datetime | NO | | NULL | |
| processing_date | datetime | YES | | NULL | |
| first_name | varchar(50) | YES | | NULL | |
| last_name | varchar(50) | YES | | NULL | |
| is_staff | tinyint(1) | YES | | NULL | |
| birth_date | date | YES | | NULL | |
+-----------------+------------------+------+-----+---------+----------------+
We can find in this table the five fields of the first table : users_id (and not id which is the primary key of users_history), first_name, last_name, is_staff, birth_date. The other fields will be used to save the “historization context” :
- requestor_id is the id that identifies the person that performed the modification
- action_flag is used to save a keyword that identifies the type of modification performed on users (‘User_Create’, ‘User_Modify’)
- order_date is used to save the date of the modification
- processing_date will not be used in this example, but the idea is to use this field to manage the case when a modification request requires a additional action from a piece of code external to the database.
The idea is to have a edit_user procedure clever enough to manage transparently users and users_history tables ; edit_users should take six parameters as input :
- parameter 1 : key (id) of the users entry to modify. If NULL is set, than users_history will consider that a new user should be created (A new id will be automatically created for the user)
- parameter 2 : new first_name value to possibly update in users and historize in users_history. The term possibly means that edit_users should be clever enough to ignore the update and historization if users(id)->firstname is already set to parameter 2. If the value NULL is used for parameter 2, edit_users should ignore completely the check : the first_name will not be updated.
- parameter 3 : new last_name value to possibly update in users and historize in users_history.
- parameter 4 : new is_staff value to possibly update in users and historize in users_history.
- parameter 5 : new birth_date value to possibly update in users and historize in users_history.
- parameter 6 : id of the person that performed the modification (needed by users_history).
So with the above logic a creation of a user should be done with only one call :
mysql> CALL edit_user(NULL, 'John', 'Doe', 1, '1978-04-03', 2);
mysql> select * from users;
+----+------------+-----------+----------+------------+
| id | first_name | last_name | is_staff | birth_date |
+----+------------+-----------+----------+------------+
| 1 | John | DOE | 1 | 1978-04-03 |
+----+------------+-----------+----------+------------+
mysql> select * from users_history;
+----+--------------+---------+-------------+---------------------+---------------------+------------+-----------+----------+------------+
| id | requestor_id | user_id | action_flag | order_date | processing_date | first_name | last_name | is_staff | birth_date |
+----+--------------+---------+-------------+---------------------+---------------------+------------+-----------+----------+------------+
| 1 | 2 | 1 | User_Create | 2010-06-28 20:40:24 | 2010-06-28 20:40:24 | John | DOE | 1 | 1978-04-03 |
+----+--------------+---------+-------------+---------------------+---------------------+------------+-----------+----------+------------+
A modification of this entry should be as simple as this :
mysql> CALL edit_user(1, 'Johny', 'Doe', 1, '1978-04-03', 2);
mysql> select * from users;
+----+------------+-----------+----------+------------+
| id | first_name | last_name | is_staff | birth_date |
+----+------------+-----------+----------+------------+
| 1 | Johny | DOE | 1 | 1978-04-03 |
+----+------------+-----------+----------+------------+
mysql> select * from users_history;
+----+--------------+---------+-------------+---------------------+---------------------+------------+-----------+----------+------------+
| id | requestor_id | user_id | action_flag | order_date | processing_date | first_name | last_name | is_staff | birth_date |
+----+--------------+---------+-------------+---------------------+---------------------+------------+-----------+----------+------------+
| 1 | 2 | 1 | User_Create | 2010-06-28 20:40:24 | 2010-06-28 20:40:24 | John | DOE | 1 | 1978-04-03 |
| 2 | 2 | 1 | User_Modify | 2010-06-28 21:31:17 | 2010-06-28 21:31:17 | Johny | NULL | NULL | NULL |
+----+--------------+---------+-------------+---------------------+---------------------+------------+-----------+----------+------------+
2 rows in set (0.00 sec)
We can see in the previous example that edit_users has correctly updated the modified fields (first_name) in users. It has also historized in users_history all the fields that have changed. As the NULL value is used to indicate that the update should be ignored (except for the first parameter where NULL means create a new entry), this last example could have also been successfully performed with the following call :
mysql> CALL edit_user(1, 'Johny', NULL, NULL, NULL, 2);
We have now defined the concept of a convenient procedure that can be used to create and historize a new entry as well as to modify and historize an arbitrary number of fields. We will formalize a little bit more the concept in the next section to establish the logic that will finally be implemented as a MySQL stored routine.
III. Specification of the historization logic
Based on the above example, let consider all the possibilities that we would like to have for the couples (initial value L, new value N) and let list for each couple, the update value U that we would like to update in the data table (users) and the differential value H to historize in the historization table (users_history). As in the example above, we will consider that N=NULL means that the field should be ignored. We will also consider that H=NULL means ‘historization is not needed’ and that C or C’ means ‘any value not NULL and not equal to ZERO.
List of (L, N) couples with update and historization actions wanted :
| (NULL, NULL) |
=> |
Nothing to do |
| (NULL, ZERO) |
=> |
U=NULL (*), H=NULL (**) |
| (NULL, C) |
=> |
U=C , H=C |
| (ZERO, NULL) |
=> |
Nothing to do |
| (ZERO, ZERO) |
=> |
Nothing to do |
| (ZERO, C) |
=> |
U=C , H=C |
| (ZERO, NULL) |
=> |
Nothing to do |
| (C, ZERO) |
=> |
U=NULL (*), H=ZERO |
| (C, NULL) |
=> |
Nothing to do |
| (C, C) |
=> |
Nothing to do |
| (C, C') |
=> |
U=C', H=C' |
(*) If NULL value is not authorised by the table schema, MySQL set automatically the ZERO value and emits a warning.
(**) It seems preferable here not to historize because we would often have in practise a useless historization entry. It is indeed often the case that a field is not set in an entry.
The test IF ( N IS NOT NULL AND L != N ) allows us to identify the cases where an update and historization action is required :
| (NULL, ZERO) |
=> |
U=NULL (***), H=NULL |
| (C, ZERO) |
=> |
U=NULL (***), H=ZERO |
| (NULL, C) |
=> |
U=C , H=C |
| (ZERO, C) |
=> |
U=C , H=C |
| (C, C') |
=> |
U=C', H=C' |
(***) If NULL value is not authorised by the table schema, MySQL set automatically the ZERO value and emits a warning
We can therefore establish the following logic :
IF ( N IS NOT NULL AND L != N ) THEN
IF ( N = ZERO AND L IS NULL ) THEN
U=NULL, H=NULL
ELSEIF (N = ZERO AND L IS NOT NULL) THEN
U=NULL, H=ZERO
ELSE THEN
U=N, H=N
END IF;
END IF;
In SQL ternary logic , the first line of this logic should be translated to :
N IS NOT NULL AND (( L != N ) OR (L IS NOT NULL AND N IS NULL) OR (L IS NULL
AND N IS NOT NULL) ))
Which hopefully can be simplified to :
N IS NOT NULL AND ( L != N OR L IS NULL )
Finally, we have established :
IF ( N IS NOT NULL AND ( L != N OR L IS NULL ) )
IF ( N = ZERO AND L IS NULL ) THEN
U=NULL, H=NULL
ELSEIF (N = ZERO AND L IS NOT NULL) THEN
U=NULL, H=ZERO
ELSE THEN
U=N,H=N
END IF;
END IF;
IV. Implementation of this logic
DELIMITER $$
/*******************************************************/
-- > Emulation of an error signal. This emulation will not be required anymore
-- in MySQL 5.2 that is expected to implement the SIGNAL directive.
SELECT 'emulate_signal' AS Proc
$$
DROP PROCEDURE IF EXISTS emulate_signal
$$
CREATE PROCEDURE emulate_signal (IN in_errortext VARCHAR(255))
BEGIN
SET @sql=CONCAT('UPDATE `', in_errortext, '` SET xyz=1');
PREPARE my_signal_stmt FROM @sql;
EXECUTE my_signal_stmt;
DEALLOCATE PREPARE my_signal_stmt;
END
$$
/********************************
* Procedure called by edit_user
********************************/
SELECT 'check_diff_varchar' AS Proc
$$
DROP PROCEDURE IF EXISTS check_diff_varchar
$$
CREATE PROCEDURE check_diff_varchar (
IN in_var_old VARCHAR(1024),
IN in_var_new VARCHAR(1024),
OUT out_var_update VARCHAR(1024),
OUT out_var_diff VARCHAR(1024),
OUT out_is_update_required BOOL
)
BEGIN
SET out_is_update_required = 0;
IF ( in_var_new IS NOT NULL AND ( in_var_new != in_var_old OR in_var_old IS NULL ) ) THEN
IF ( in_var_new = '' ) THEN
SET out_is_update_required = 1;
SET out_var_update = NULL;
IF ( in_var_old IS NULL ) THEN
SET out_var_diff = NULL;
ELSE
SET out_var_diff = '';
END IF;
ELSE
SET out_is_update_required = 1;
SET out_var_update = in_var_new;
SET out_var_diff = in_var_new;
END IF;
END IF;
END
$$
SHOW WARNINGS
$$
/********************************
* Procedure called by edit_user
********************************/
SELECT 'check_diff_date' AS Proc
$$
DROP PROCEDURE IF EXISTS check_diff_date
$$
CREATE PROCEDURE check_diff_date (
IN in_var_old DATE,
IN in_var_new DATE,
OUT out_var_update DATE,
OUT out_var_diff DATE,
OUT out_is_update_required BOOL
)
BEGIN
SET out_is_update_required = 0;
IF ( in_var_new IS NOT NULL AND ( in_var_new != in_var_old OR in_var_old IS NULL ) ) THEN
IF ( in_var_new = '000-00-00' ) THEN
SET out_is_update_required = 1;
SET out_var_update = NULL;
IF ( in_var_old IS NULL ) THEN
SET out_var_diff = NULL;
ELSE
SET out_var_diff = '0000-00-00';
END IF;
ELSE
SET out_is_update_required = 1;
SET out_var_update = in_var_new;
SET out_var_diff = in_var_new;
END IF;
END IF;
END
$$
SHOW WARNINGS
$$
/********************************
* Procedure called by edit_user
********************************/
SELECT 'check_diff_integer' AS Proc
$$
DROP PROCEDURE IF EXISTS check_diff_integer
$$
CREATE PROCEDURE check_diff_integer (
IN in_var_old INTEGER,
IN in_var_new INTEGER,
OUT out_var_update INTEGER,
OUT out_var_diff INTEGER,
OUT out_is_update_required BOOL
)
BEGIN
SET out_is_update_required = 0;
IF ( in_var_new IS NOT NULL AND ( in_var_new != in_var_old OR in_var_old IS NULL ) ) THEN
IF ( in_var_new = 0 ) THEN
SET out_is_update_required = 1;
SET out_var_update = NULL;
IF ( in_var_old IS NULL ) THEN
SET out_var_diff = NULL;
ELSE
SET out_var_diff = 0;
END IF;
ELSE
SET out_is_update_required = 1;
SET out_var_update = in_var_new;
SET out_var_diff = in_var_new;
END IF;
END IF;
END
$$
SHOW WARNINGS
$$
/********************************
* Procedure edit_user
********************************/
SELECT 'edit_user' AS Proc
$$
DROP PROCEDURE IF EXISTS edit_user
$$
CREATE PROCEDURE edit_user (
IN in_id INTEGER,
IN in_first_name VARCHAR(50),
IN in_last_name VARCHAR(50),
IN in_is_staff BOOL,
IN in_birth_date DATE,
IN in_requestor_id INTEGER
)
BEGIN
DECLARE v_id INTEGER;
DECLARE v_first_name VARCHAR(50);
DECLARE v_first_name_update VARCHAR(50);
DECLARE v_first_name_diff VARCHAR(50);
DECLARE v_last_name VARCHAR(50);
DECLARE v_last_name_update VARCHAR(50);
DECLARE v_last_name_diff VARCHAR(50);
DECLARE v_is_staff BOOL;
DECLARE v_is_staff_update BOOL;
DECLARE v_is_staff_diff BOOL;
DECLARE v_birth_date DATE;
DECLARE v_birth_date_update DATE;
DECLARE v_birth_date_diff DATE;
DECLARE v_is_update_required BOOL;
DECLARE v_new_id INTEGER;
DECLARE v_error_msg VARCHAR(255);
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
CALL emulate_signal(v_error_msg);
END;
START TRANSACTION;
SET v_error_msg = 'edit_user : rollback';
-- modification of en entry
IF ( in_id IS NOT NULL ) THEN
SELECT id, first_name, last_name, is_staff, birth_date INTO v_id, v_first_name, v_last_name, v_is_staff, v_birth_date FROM users WHERE id=in_id;
CALL check_diff_varchar(v_first_name, in_first_name, v_first_name_update, v_first_name_diff, v_is_update_required);
IF ( v_is_update_required = 1 ) THEN
UPDATE users SET first_name=v_first_name_update WHERE id = in_id;
END IF;
CALL check_diff_varchar(v_last_name, UPPER(in_last_name), v_last_name_update, v_last_name_diff, v_is_update_required);
IF ( v_is_update_required = 1 ) THEN
UPDATE users SET last_name=v_last_name_update WHERE id = in_id;
END IF;
CALL check_diff_date(v_birth_date, in_birth_date, v_birth_date_update, v_birth_date_diff, v_is_update_required);
IF ( v_is_update_required = 1 ) THEN
UPDATE users SET birth_date=v_birth_date_update WHERE id = in_id;
END IF;
CALL check_diff_integer(v_is_staff, in_is_staff, v_is_staff_update, v_is_staff_diff, v_is_update_required);
IF ( v_is_update_required = 1 ) THEN
UPDATE users SET is_staff=v_is_staff_update WHERE id = in_id;
END IF;
-- Differential historization
IF ( v_first_name_diff IS NOT NULL OR v_last_name_diff IS NOT NULL OR v_is_staff_diff IS NOT NULL OR v_birth_date_diff IS NOT NULL ) THEN
INSERT INTO users_history (requestor_id, user_id, order_date, processing_date, action_flag, first_name, last_name, is_staff, birth_date) VALUES(in_requestor_id, in_id, NOW(), NOW(), 'User_Modify', v_first_name_diff, UPPER(v_last_name_diff), v_is_staff_diff, v_birth_date_diff);
END IF;
-- Creation of an entry
ELSE
INSERT INTO users (first_name, last_name, is_staff, birth_date) VALUES(in_first_name, UPPER(in_last_name), in_is_staff, in_birth_date );
SELECT MAX(id) INTO v_new_id FROM users;
INSERT INTO users_history (requestor_id, user_id, order_date, processing_date, action_flag, first_name, last_name, is_staff, birth_date) VALUES(in_requestor_id, v_new_id, NOW(), NOW(), 'User_Create', in_first_name, UPPER(in_last_name), in_is_staff, in_birth_date);
END IF;
COMMIT;
END
$$
SHOW WARNINGS
$$
Screenshot of my new screensaver
Selection of Phosphor screensaver in the KDE4 screensaver list once the xscreensaver rpm has been installed
Configuration of the phosphor screensaver with the xscreensaver-demo command
Configuration of the phosphor screensaver with the xscreensaver-demo command (advanced tab)
A blowfish by saragoldsmith.
