hosted by

From Bibus

Note: this article applies only to Bibus installations that use MySQL > 5.0.10 as a database engine. You should have a reasonable knowledge of (My)SQL before trying to apply the things below

If you use Bibus with MySQL and multiple users in a workgroup it might be a good thing to protcoll edits made to the database. In case of accidents, you will able to restore references on a per user or date basis. I had already made a feature request for this to Pierre when I found out now that with the new features of MySQL 5 (triggers) this can be easily done on the database level alone. Each time a reference in the Bibus bibref table is modified, a trigger is activated and a copy of the reference will be written to a new table (bibhistory). Additional columns in the bibhistory table track the user, date-time and the type of action (UPDATE,INSERT,DELETE). Since the bibhistory table is updated via triggers, also changes made via other MySQL clients (mysql.exe for example) are tracked in the same way. The first script below creates the bibhistory table (execute this first) and the second one defines the triggers for UPDATE,INSERT,DELETE actions. A cool feature is that the additional table makes it possible to use bibus to query for the most recent articles that were added or updated (see below).

/*
create_history_table.sql
*/
USE bibus41;
DROP TABLE IF EXISTS bibhistory;
create table bibhistory like bibref;
ALTER TABLE bibhistory DROP COLUMN id;
ALTER TABLE bibhistory DROP COLUMN identifier;
ALTER TABLE bibhistory  ADD COLUMN identifier VARCHAR(255) FIRST;
ALTER TABLE bibhistory  ADD COLUMN id INT UNSIGNED FIRST;
ALTER TABLE bibhistory  ADD INDEX (id);
ALTER TABLE bibhistory  ADD COLUMN action ENUM('UPDATE','INSERT','DELETE') FIRST;
ALTER TABLE bibhistory  ADD COLUMN user VARCHAR(255) FIRST;
ALTER TABLE bibhistory  ADD COLUMN mdate TIMESTAMP FIRST;
ALTER TABLE bibhistory  ADD INDEX (mdate);

/*
history_trigger.sql
klaus schwarzburg 2006
TESTED with MySQL 5.0.18 and Bibus 1.2

FUNCTION:
With an additional table (bibhistory) and SQL triggers all action on the BIBUS bibref 
table will be protcolled automatically. If you change a reference, delete or insert a reference
the previous reference will be copied to the bibhistory table. The bibhistory has 3 additional columns:
'user','action','mdate' at the beginning that are filled once one of the triggers is involked. 
'user'		: MySQL username including host
'action'	: 'UPDATE','INSERT','DELETE'
'mdate'	: timestamp when action occured


INSTALLATION:
1) edit and run the create_history_table.sql script to create the bibhistory table
- change the 'USE bibus41' statement at the beginning to match your bibus db name
2) edit (USE...) and run this script to create the triggers


NOTES:
The full username (including the host) is stored in the 'user' field of the bibhistory table.
If you wantto retrive only the username without the host use a query like this:
SELECT SUBSTRING_INDEX(user,'@',1) FROM bibhistory


*/

USE bibus41;

-- uncomment the following lines if you want to modify already existing triggers:

--DROP TRIGGER historyupdate;
--DROP TRIGGER historydelete;
--DROP TRIGGER historyinsert;





DELIMITER |

CREATE TRIGGER historyupdate AFTER UPDATE ON bibref
	FOR EACH ROW BEGIN
		INSERT INTO bibhistory SET 
		user =  USER(), 
		action = 'UPDATE',
		id = OLD.id, 
		identifier = OLD.identifier,
		BibliographicType = OLD.BibliographicType,
		Address = OLD.Address,
		Annote = OLD.Annote,
		Author = OLD.Author,
		Booktitle = OLD.Booktitle,
		Chapter = OLD.Chapter,
		Edition = OLD.Edition,
		Editor = OLD.Editor,
		Howpublished = OLD.Howpublished,
		Institution = OLD.Institution,
		Journal = OLD.Journal,
		Month = OLD.Month,
		Note = OLD.Note,
		Number = OLD.Number,
		Organizations = OLD.Organizations,
		Pages = OLD.Pages,
		Publisher = OLD.Publisher,
		School = OLD.School,
		Series = OLD.Series,
		Title = OLD.Title,
		Report_Type = OLD.Report_Type,
		Volume = OLD.Volume,
		Year = OLD.Year,
		URL = OLD.URL,
		Custom1 = OLD.Custom1,
		Custom2 = OLD.Custom2,
		Custom3 = OLD.Custom3,
		Custom4 = OLD.Custom4,
		Custom5 = OLD.Custom5,
		ISBN = OLD.ISBN,
		abstract = OLD.abstract
    ;
  END;
|
DELIMITER ;





DELIMITER |

CREATE TRIGGER historydelete BEFORE DELETE ON bibref
	FOR EACH ROW BEGIN
		INSERT INTO bibhistory SET 
		user =  USER(), 
		action = 'DELETE',
		id = OLD.id, 
		identifier = OLD.identifier,
		BibliographicType = OLD.BibliographicType,
		Address = OLD.Address,
		Annote = OLD.Annote,
		Author = OLD.Author,
		Booktitle = OLD.Booktitle,
		Chapter = OLD.Chapter,
		Edition = OLD.Edition,
		Editor = OLD.Editor,
		Howpublished = OLD.Howpublished,
		Institution = OLD.Institution,
		Journal = OLD.Journal,
		Month = OLD.Month,
		Note = OLD.Note,
		Number = OLD.Number,
		Organizations = OLD.Organizations,
		Pages = OLD.Pages,
		Publisher = OLD.Publisher,
		School = OLD.School,
		Series = OLD.Series,
		Title = OLD.Title,
		Report_Type = OLD.Report_Type,
		Volume = OLD.Volume,
		Year = OLD.Year,
		URL = OLD.URL,
		Custom1 = OLD.Custom1,
		Custom2 = OLD.Custom2,
		Custom3 = OLD.Custom3,
		Custom4 = OLD.Custom4,
		Custom5 = OLD.Custom5,
		ISBN = OLD.ISBN,
		abstract = OLD.abstract
    ;
  END;
|
DELIMITER ;






DELIMITER |

CREATE TRIGGER historyinsert AFTER INSERT ON bibref
	FOR EACH ROW BEGIN
		INSERT INTO bibhistory SET 
		user =  USER(), 
		action = 'INSERT',
		id = NEW.id, 
		identifier = NEW.identifier,
		BibliographicType = NEW.BibliographicType,
		Address = NEW.Address,
		Annote = NEW.Annote,
		Author = NEW.Author,
		Booktitle = NEW.Booktitle,
		Chapter = NEW.Chapter,
		Edition = NEW.Edition,
		Editor = NEW.Editor,
		Howpublished = NEW.Howpublished,
		Institution = NEW.Institution,
		Journal = NEW.Journal,
		Month = NEW.Month,
		Note = NEW.Note,
		Number = NEW.Number,
		Organizations = NEW.Organizations,
		Pages = NEW.Pages,
		Publisher = NEW.Publisher,
		School = NEW.School,
		Series = NEW.Series,
		Title = NEW.Title,
		Report_Type = NEW.Report_Type,
		Volume = NEW.Volume,
		Year = NEW.Year,
		URL = NEW.URL,
		Custom1 = NEW.Custom1,
		Custom2 = NEW.Custom2,
		Custom3 = NEW.Custom3,
		Custom4 = NEW.Custom4,
		Custom5 = NEW.Custom5,
		ISBN = NEW.ISBN,
		abstract = NEW.abstract
    ;
  END;
|
DELIMITER ;

If everything is working, we can use Bibus to query for updated or inserted references by time! Here's one example that returns the new references from the last 7 days:

id in
(
select b.Id from bibhistory as b
where b.action = 'INSERT'
AND
DAYOFYEAR(CURDATE()) - DAYOFYEAR(mdate) <= 7
)

(Enter this query in expert mode)