The bible is essentially a read ony database. Users really shouldn't go around updating verses to suit their own purposes. So how about using MySQL's triggers to do a little snooping on anybody trying to tamper? The idea is anyone who attempts to update a row in the bible will have their change reported in the meddlers table.
First we'll create the meddlers table.
CREATE TABLE meddlers
(
autokey INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
user VARCHAR(255),
`DATE` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`table` VARCHAR(32),
id INT UNSIGNED,
line_old VARCHAR(530),
line_new TEXT
);
Two things to note about this table definition. You have to surround `table` in backticks when it is used as a column name, because table is, of course, a mysql keyword. Strangely enough, mysql lets you get away with not surrounding date in backticks when it too is used as a column name. I have here because 'date' spends 99% of its time as a keyword, and doesn't usually get demoted to plain old columnname. . (Unfortunately my syntax highlighter (GeSHi) insists on capitalizing it and colouring it green, even though date isn't a keyword in this context.) The other thing: line_old is VARCHAR(530) as per the definition in the bible table, but for line_new I'm using the TEXT datatype. TEXT fields take up to 65,545 characters. That way it's not subject to the 530 line length limit imposed by VARCHAR(530). Handy if somebody tries to insert a really long line in to the bible.
Now lets create our first trigger. As usual I drop it first, as it makes for easier cutting and pasting if you want to alter the CREATE TRIGGER statement later.
CREATE TRIGGER bible_au
AFTER UPDATE ON bible
FOR EACH ROW
INSERT INTO meddlers(user, `table`, id, line_old, line_new) SELECT USER(),'bible', OLD.id, OLD.line, NEW.line;
The trigger definition says that after every update to the bible table, loop through each row that was updated and insert both the old line and the new line into the meddlers table, as well as the user who updated it. Note the trigger naming convention: bible_au, for after update. This trigger takes effect after an update statement.
Rememeber that famous first line from the bible?
mysql> select * from bible limit 1; +----+------+-----+-------+--------------------------------------------------------+ | id | book | cap | verse | line | +----+------+-----+-------+--------------------------------------------------------+ | 1 | Ge | 1 | 1 | In the beginning God created the heaven and the earth. | +----+------+-----+-------+--------------------------------------------------------+
Let's suppose a user comes along with a better version of the first line.
The update happens as the user intended:
mysql> SELECT * FROM bible LIMIT 1; +----+------+-----+-------+--------------------------------------+ | id | book | cap | verse | line | +----+------+-----+-------+--------------------------------------+ | 1 | Ge | 1 | 1 | Once upon a time God was a bit bored | +----+------+-----+-------+--------------------------------------+
But a glance at the meddlers table reveals who made the change and when!
mysql> SELECT * FROM meddlers \G
*************************** 1. row ***************************
autokey: 1
user: heretic@vatican.org
date: 2008-02-03 16:34:38
table: bible
id: 1
line_old: In the beginning God created the heaven and the earth.
line_new: Once upon a time God was a bit bored
1 row in set (0.00 sec)
Gotcha, heretic! Consider yourself excommunicated.
Suppose you drop the meddlers table. The trigger which uses it will stil be intact, and itching to fire after any update to the bible table. So what will happen when wenext update the bible table then?
UPDATE bible SET
line = "Far out in the unchartered backwaters of the unfashionable end of the western spiral arm of the Galaxy lies a small unregarded yellow sun"
WHERE id = 1;
When we attempt the update, we get quite an obscure error:
ERROR 1100 (HY000): Table 'meddlers' was not locked with LOCK TABLES
Which I suppose is MySQL's way of saying 'Trigger bible_au references table `meddlers` which doesn't exist.' Note to the MySQL devteam: How about a nice clear error message for the next release of MySQL?
Not only does the trigger fail, the update fails to take place either:
mysql> select * from bible limit 1; +----+------+-----+-------+--------------------------------------+ | id | book | cap | verse | line | +----+------+-----+-------+--------------------------------------+ | 1 | Ge | 1 | 1 | Once upon a time God was a bit bored | +----+------+-----+-------+--------------------------------------+ 1 row in set (0.00 sec)
Anyway, let's recreate the meddlers table using the CREATE TABLE statement above, and proceed.
If we're really going to nail all meddlers, we would need pretty much the same trigger to happen when the basic english bible gets updated too. Annoyingly, we can't put them all together in one trigger. It's a strict one-trigger-per-table rule. (Or more strictly, one trigger type per table) So we will define a second trigger for the bible_basic table, but this time I'm going to make the trigger happen BEFORE the update, and use it to basically roll back the update.
delimiter //
CREATE TRIGGER bible_basic_bu
BEFORE UPDATE ON bible_basic
FOR EACH ROW
BEGIN
INSERT INTO meddlers(user, `table`, id,line_old, line_new) SELECT USER(),'bible_basic', OLD.id, OLD.line, NEW.line;
SET NEW.line = OLD.line;
END;
//
delimiter ;
Boy is this a crafty query. Note the use of delimiter // before and after defining our trigger, otherwise the mysql client program wouldn't even let us enter the trigger in the first place, thanks to the semicolons within the compound statment (between BEGIN and END).
SELECT * FROM bible_basic LIMIT 1; +----+------+-----+-------+-------------------------------------------------+ | id | book | cap | verse | line | +----+------+-----+-------+-------------------------------------------------+ | 1 | Ge | 1 | 1 | At the first God made the heaven and the earth. | +----+------+-----+-------+-------------------------------------------------+ 1 row in set (0.00 sec) UPDATE bible_basic SET line = "You better not never tell nobody but God." WHERE id =1; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0
Note how 0 lines were changed this time. That's because the instruction
SET NEW.line = OLD.line;
effectively rolled back the change before it happened.
But our meddlers table still has the change attempt
SELECT * FROM meddlers;
mysql localhost bible> SELECT * FROM meddlers \G
*************************** 1. row ***************************
autokey: 1
user: claranet@localhost
date: 2008-02-03 16:50:07
table: bible_basic
id: 1
line_old: At the first God made the heaven and the earth.
line_new: You better not never tell nobody but God.
1 row in set (0.00 sec)
One more refinement to triggers. NOt every update will cause a change, so let's alter our trigger to log only those update attempts which would succeed;
delimiter //
CREATE TRIGGER bible_au
AFTER UPDATE ON bible
FOR EACH ROW
IF new.line != old.line THEN
INSERT INTO meddlers(user, `table`, id, line_old, line_new) SELECT USER(),'bible', OLD.id, OLD.line, NEW.line;
END IF
//
delimiter ;
TRUNCATE meddlers; mysql> select * from bible where id = 26559; +-------+------+-----+-------+-------------+ | id | book | cap | verse | line | +-------+------+-----+-------+-------------+ | 26559 | John | 11 | 35 | Jesus wept. | +-------+------+-----+-------+-------------+ 1 row in set (0.00 sec) UPDATE bible SET line = 'Jesus wept.' WHERE id >= 26559 LIMIT 3; Query OK, 2 rows affected (0.00 sec) Rows matched: 3 Changed: 2 Warnings: 0
The updates happen, but only the rows which were updated get recorded in meddlers
SELECT * FROM meddlers \G
*************************** 1. row ***************************
autokey: 1
user: claranet@localhost
date: 2008-02-03 17:04:02
table: bible
id: 26560
line_old: Then said the Jews, Behold how he loved him!
line_new: Jesus wept.
*************************** 2. row ***************************
autokey: 2
user: claranet@localhost
date: 2008-02-03 17:04:02
table: bible
id: 26561
line_old: And some of them said, Could not this man, which opened the eyes of the blind, have caused that even this man should not have died?
line_new: Jesus wept.
2 rows in set (0.00 sec)
Triggers can also be defined for INSERT and DELETE statements, so we can also snoop on these wishing to add our delete to the bible.
DELETE FROM bible WHERE id = 1;
Because this trigger happens after the delete, the row still goes but at least it is logged.
select * from meddlers where line_new = 'DELETED'\G
*************************** 1. row ***************************
autokey: 3
user: claranet@localhost
date: 2008-02-03 17:10:24
table: bible
id: 1
line_old: In the beginning God created the heaven and the earth.
line_new: DELETED
1 row in set (0.00 sec)
Let's try and go one better. We will log and frustrate attempted deletions by inserting again what has just beenm deleted.
delimiter //
CREATE TRIGGER bible_ad
AFTER DELETE ON bible
FOR EACH ROW
BEGIN
INSERT INTO meddlers(user, `table`, id, line_old, line_new) SELECT USER(),'bible', OLD.id, OLD.line, 'ATTEMPTED DELETE';
INSERT INTO bible VALUES (OLD.id, OLD.book, OLD.cap, OLD.verse, OLD.line);
END
//
delimiter ;
DELETE FROM bible WHERE id = 100;
#ERROR 1442 (HY000): Can't update table 'bible' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
mysql localhost bible>
Drat and blast! That didn't work. Let's try it a different way, changing the trigger to 'before' rather than 'after'.
DROP TRIGGER bible_ad;
delimiter //
CREATE TRIGGER bible_bd
BEFORE DELETE ON bible
FOR EACH ROW
BEGIN
INSERT INTO meddlers(user, `table`, id, line_old, line_new) SELECT USER(),'bible', OLD.id, OLD.line, 'ATTEMPTED DELETE';
END
//
delimiter ;