User Tools

Site Tools


python:database:sqlite:history_log

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

python:database:sqlite:history_log [2016/08/08 09:28]
ozan created
python:database:sqlite:history_log [2016/08/08 09:41] (current)
ozan
Line 2: Line 2:
  
 I needed a history log table showing old and new values of a table after insert, update and delete operations. I achieved to create history log for each DML operation by creating triggers on the table which I want to have history log and creating a new history log table to hold old and new values of each column. Simply, triggers create new records for each row inserted/​updated/​deleted. Instead of giving an example for this topic, I created and shared a TestCase using unittest to verify all operations are completed successfully. I needed a history log table showing old and new values of a table after insert, update and delete operations. I achieved to create history log for each DML operation by creating triggers on the table which I want to have history log and creating a new history log table to hold old and new values of each column. Simply, triggers create new records for each row inserted/​updated/​deleted. Instead of giving an example for this topic, I created and shared a TestCase using unittest to verify all operations are completed successfully.
 +
 +Let's assume we have a table ''​main''​ as follows
 +<code sql>
 +CREATE TABLE IF NOT EXISTS "​main"​ (
 +id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
 +name TEXT,
 +surname TEXT,
 +age INTEGER,
 +birthdate TIMESTAMP
 +);
 +</​code>​
 +
 +Since we want to record all history of all columns, let's create a history table called ''​main_history''​ as follows. I added three more columns namely ''​operation'',​ ''​user''​ and ''​timestamp''​ to track which user do which operation when.
  
 Since SQLite does not have multi user feature, I created a ''​get_user''​ function to fetch the user from application side and registered that function. Since SQLite does not have multi user feature, I created a ''​get_user''​ function to fetch the user from application side and registered that function.
 +
 +<code sql>
 +CREATE TABLE IF NOT EXISTS "​main_history"​
 +("​id"​ INTEGER PRIMARY KEY ,
 +"​old_id"​ INTEGER,
 +"​new_id"​ INTEGER,
 +"​old_name"​ TEXT,
 +"​new_name"​ TEXT,
 +"​old_surname"​ TEXT,
 +"​new_surname"​ TEXT,
 +"​old_age"​ INTEGER,
 +"​new_age"​ INTEGER,
 +"​old_birthdate"​ TIMESTAMP,
 +"​new_birthdate"​ TIMESTAMP,
 +"​operation"​ TEXT,
 +"​user"​ TEXT DEFAULT (get_user()),​
 +"​timestamp"​ TIMESTAMP NOT NULL DEFAULT (datetime('​now','​localtime'​))
 +);
 +</​code>​
 +
 +The important part is to create the triggers so that history becomes available.
 +Here are the triggers'​ statements
 +
 +<code sql>
 +CREATE TRIGGER IF NOT EXISTS "​main_ai"​ AFTER INSERT ON "​main"​ FOR EACH ROW 
 +BEGIN
 +     ​INSERT INTO "​main_history"​ (new_id, new_name,​new_surname,​new_age,​new_birthdate,​ operation) VALUES
 +    (NEW.id, NEW.name, NEW.surname,​ NEW.age, NEW.birthdate,"​INSERT"​);​
 +END;
 +CREATE TRIGGER IF NOT EXISTS "​main_bu"​ BEFORE UPDATE ON "​main"​ FOR EACH ROW 
 +BEGIN
 +     ​INSERT INTO "​main_history"​ (old_id, new_id, old_name, new_name,​old_surname,​ new_surname,​old_age,​ new_age,​old_birthdate,​ new_birthdate,​ operation) VALUES
 +    (OLD.id, NEW.id, OLD.name, NEW.name, OLD.surname,​ NEW.surname,​ OLD.age, NEW.age, OLD.birthdate,​ NEW.birthdate,​ "​UPDATE"​);​
 +END;
 +CREATE TRIGGER IF NOT EXISTS "​main_ad"​ AFTER DELETE ON "​main"​ FOR EACH ROW 
 +BEGIN
 +     ​INSERT INTO "​main_history"​ (old_id, old_name,​old_surname,​old_age,​old_birthdate,​ operation) VALUES
 +    (OLD.id, OLD.name, OLD.surname, ​ OLD.age, OLD.birthdate,​ "​DELETE"​);​
 +END;
 +</​code>​
 +Triggers insert the records to ''​main_history''​ table appropriately as can be seen from the SQL code above.
  
 Following code works under Python 2.7 and 3.x versions Following code works under Python 2.7 and 3.x versions
Line 334: Line 388:
 </​code>​ </​code>​
  
 +{{tag>​SQLite sql Python history_log audit unittest trigger}}
 +~~DISCUSSION~~
python/database/sqlite/history_log.txt · Last modified: 2016/08/08 09:41 by ozan