News

October 2009 : a new release (bibus 1.5.0)

  • OOo3.1 compatibility
  • Infinite number of file/http links
  • Clever capitalization (lord of war => Lord of War ...)
  • Better handling of journal abbreviations
  • Import/Export filters can be added easily (plugin)
  • journal abbreviations and shortcuts can be easily edited (simple csv file that can be edited in OOo/excel or a text editor)
  • Lot of bug fixes
  • Requires python2.5 (not a problem anymore under Windows since OOo3.1 includes 2.6)
  • Requires wxpython-2.8

Automatic creation of an edit history using MySQL 5

From Bibus

Jump to: navigation, search

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

Contents


The first section implements full data versioning (without summary) and has been tested for years. The second section implements the same versioning but adds a summary table which is more user friendly (this feature is more recent and thus is not as widely tested). The second method also ensures that IDs of previously deleted references are not re-used, so is interesting in terms of data consistency as well.

Full data versioning without summary (bibhistory table)

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)

Full data versioning with summary (bibhistory and bibeditsummary tables)

The above procedure is good in terms of data preservation: it keeps a copy of any modified data. However, generally, the edition of a reference is as follows: The first edit is the creation of the reference, with many fields (often 4 to 12) inserted. The following edits are corrections (for instance for typos) or small additions (for instance you forgot to insert the page numbers for an article). In this case, most fields are left unchanged after the initial INSERT and most of the data in the bibhistory table are redundant. For the ordinary user, it is simpler to have a summary of this in a single field (with only one record for each reference), in a bibeditsummary table. The following SQL code connects to a database called 'bibusbiblio' and create triggers that manage two tables:

  • the bibhistory table
  • the bibeditsummary table

The following already includes the triggers of the first section.

USE bibusbiblio;

-- display existing triggers
SELECT TRIGGER_NAME -- , EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_STATEMENT
    FROM INFORMATION_SCHEMA.TRIGGERS
    WHERE TRIGGER_SCHEMA='bibusbiblio';

DROP TABLE IF EXISTS biblastid;
CREATE TABLE `biblastid` (`lastid` INTEGER NOT NULL);

INSERT INTO biblastid (select IF(MAX(id) IS NULL, 0, MAX(id)) from bibref);

USE bibusbiblio;
DROP TABLE IF EXISTS bibeditsummary;
create table bibeditsummary (id INTEGER, INDEX (id), edits TEXT);


-- ----------
-- With the default setting, IDs are unique at a given time but ID of deleted references might be used for new references;
-- hence it might be dangerous or difficult to recover a deleted reference. More precisely, usually, if you delete the
-- row with the biggest AUTO_INCREMENT value (id), the same id will be reused for the next reference. See this thread:
-- http://stackoverflow.com/questions/3718229/stop-mysql-reusing-auto-increment-ids
-- One workaround could be to increase the auto_increment using: ALTER TABLE bibref AUTO_INCREMENT= but this cannot (?) be used in a trigger. A stored procedure requires root rights

DELIMITER ;
DROP TRIGGER historybeforeinsert;
DROP TRIGGER historyafterinsert;
DROP TRIGGER historyupdate;
DROP TRIGGER historydelete;
DELIMITER |

-- Makes sure id is not identical or lower than thehighest used one
CREATE TRIGGER historybeforeinsert BEFORE INSERT ON bibref
  FOR EACH ROW 
    IF NEW.id<=(SELECT lastid FROM biblastid) THEN
      SET NEW.id=(SELECT lastid+1 FROM biblastid LIMIT 1);
    END IF;
|

DELIMITER |
CREATE TRIGGER historyafterinsert AFTER INSERT ON bibref
  FOR EACH ROW BEGIN
    -- Update table storing last id.
    -- This prepares id for next created record. If this record is deleted while it
    -- is the one with the highest id, its id will not be reused (contrary to MySQL default behaviour)
    UPDATE biblastid SET lastid=NEW.id;
    
    INSERT INTO bibeditsummary SET 
      id = NEW.id, 
      edits=CONCAT(
      'id: ', NEW.id, '\n',
      '----------------------------- \n',
      -- 'Version: 1 \n',
      'User / Time: ', USER(), ' / ', NOW(), '\n',
      'Identifier: ',
      -- if identifier not available, it's probably because is invalid/duplicate and being regenerated by bibus
      -- the following tries to guess what bibus generated in this case
      IF(NEW.identifier !='', 
        NEW.identifier,
        CONCAT(
          IF( LENGTH(NEW.Author)=0, 'Anonymous',  -- if author is empty, bibus puts "Anonymous"
            REPLACE(
              IF (LOCATE(',', NEW.Author) = 0, -- if there is no comma,...
                NEW.Author, -- take full author field
                LEFT(NEW.Author, LOCATE(',',NEW.Author)-1)),-- else take everything before the comma
            ' ', '_') -- replace spaces by underscores
          )
          , NEW.Year, '#', NEW.id, 'L' -- and add #999L where 999 is the id
        )
      ),
      '\n',
      'BibliographicType: ', CASE NEW.BibliographicType
          WHEN 0 THEN 'ARTICLE'  WHEN 1 THEN 'BOOK'  WHEN 2 THEN 'BOOKLET'  WHEN 3 THEN 'CONFERENCE'  WHEN 4 THEN 'INBOOK'
          WHEN 5 THEN 'INCOLLECTION'  WHEN 6 THEN 'INPROCEEDINGS'  WHEN 7 THEN 'JOURNAL'  WHEN 8 THEN 'MANUAL'
          WHEN 9 THEN 'MASTERTHESIS'  WHEN 10 THEN 'MISC'  WHEN 11 THEN 'PHDTHESIS'  WHEN 12 THEN 'PROCEEDINGS'
          WHEN 13 THEN 'TECHREPORT'  WHEN 14 THEN 'UNPUBLISHED'  WHEN 15 THEN 'EMAIL'   WHEN 16 THEN 'WWW'
          WHEN 17 THEN 'CUSTOM1' WHEN 18 THEN 'CUSTOM2'  WHEN 19 THEN 'CUSTOM3'  WHEN 20 THEN 'CUSTOM4'
          WHEN 21 THEN 'CUSTOM5'  ELSE 'unknown'  END, '\n',
      IF(NEW.Address !='', CONCAT('Address: ', NEW.Address, '\n'), ''),
      IF(NEW.Annote !='', CONCAT('Annote: ', NEW.Annote, '\n'), ''),
      IF(NEW.Author !='', CONCAT('Author: ', NEW.Author, '\n'), ''),
      IF(NEW.Booktitle !='', CONCAT('Booktitle: ', NEW.Booktitle, '\n'), ''),
      IF(NEW.Chapter !='', CONCAT('Chapter: ', NEW.Chapter, '\n'), ''),
      IF(NEW.Edition !='', CONCAT('Edition: ', NEW.Edition, '\n'), ''),
      IF(NEW.Editor !='', CONCAT('Editor: ', NEW.Editor, '\n'), ''),
      IF(NEW.Howpublished !='', CONCAT('Howpublished: ', NEW.Howpublished, '\n'), ''),
      IF(NEW.Institution !='', CONCAT('Institution: ', NEW.Institution, '\n'), ''),
      IF(NEW.Journal !='', CONCAT('Journal: ', NEW.Journal, '\n'), ''),
      IF(NEW.Month !='', CONCAT('Month: ', NEW.Month, '\n'), ''),
      IF(NEW.Note !='', CONCAT('Note: ', NEW.Note, '\n'), ''),
      IF(NEW.Number !='', CONCAT('Number: ', NEW.Number, '\n'), ''),
      IF(NEW.Organizations !='', CONCAT('Organizations: ', NEW.Organizations, '\n'), ''),
      IF(NEW.Pages !='', CONCAT('Pages: ', NEW.Pages, '\n'), ''),
      IF(NEW.Publisher !='', CONCAT('Publisher: ', NEW.Publisher, '\n'), ''),
      IF(NEW.School !='', CONCAT('School: ', NEW.School, '\n'), ''),
      IF(NEW.Series !='', CONCAT('Series: ', NEW.Series, '\n'), ''),
      IF(NEW.Title !='', CONCAT('Title: ', NEW.Title, '\n'), ''),
      IF(NEW.Report_Type !='', CONCAT('Report_Type: ', NEW.Report_Type, '\n'), ''),
      IF(NEW.Volume !='', CONCAT('Volume: ', NEW.Volume, '\n'), ''),
      IF(NEW.Year !='', CONCAT('Year: ', NEW.Year, '\n'), ''),
      IF(NEW.URL !='', CONCAT('URL: ', NEW.URL, '\n'), ''),
      IF(NEW.Custom1 !='', CONCAT('Custom1: ', NEW.Custom1, '\n'), ''),
      IF(NEW.Custom2 !='', CONCAT('Custom2: ', NEW.Custom2, '\n'), ''),
      IF(NEW.Custom3 !='', CONCAT('Custom3: ', NEW.Custom3, '\n'), ''),
      IF(NEW.Custom4 !='', CONCAT('Custom4: ', NEW.Custom4, '\n'), ''),
      IF(NEW.Custom5 !='', CONCAT('Custom5: ', NEW.Custom5, '\n'), ''),
      IF(NEW.ISBN !='', CONCAT('ISBN: ', NEW.ISBN, '\n'), ''),
      IF(NEW.abstract !='', CONCAT('abstract: ', CHAR_LENGTH(NEW.abstract), ' characters \n'), '')
      )
    ;
    INSERT INTO bibhistory SET 
      user =  USER(), 
      action = 'INSERT',
      id = NEW.id, 
      identifier = IF(NEW.identifier !='', 
        NEW.identifier,
        CONCAT(
          IF( LENGTH(NEW.Author)=0, 'Anonymous',  -- if author is empty, bibus puts "Anonymous" (TODO: is it Locale-dependent?)
            REPLACE(
              IF (LOCATE(',', NEW.Author) = 0, -- if there is no comma,...
                NEW.Author, -- take full author field
                LEFT(NEW.Author, LOCATE(',',NEW.Author)-1)),-- else take everything before the comma
            ' ', '_') -- replace spaces by underscores
          )
          ,  NEW.Year, '#', NEW.id, 'L' -- and add #999L where 999 is the id
        )
      ),
      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 ;


DELIMITER |
CREATE TRIGGER historyupdate AFTER UPDATE ON bibref
  FOR EACH ROW BEGIN

    IF (SELECT COUNT(*) FROM bibeditsummary WHERE bibeditsummary.id=NEW.id)=0 THEN
      INSERT INTO bibeditsummary (id, edits) VALUES (NEW.id, '(This reference existed before versioning was installed)\n');
    END IF;
    
    UPDATE bibeditsummary 
      SET edits=CONCAT(edits,
        '----------------------------- \n',
        -- 'Version: \n', --TODO: implement automatic version number (useful for recovering a reference)
        'User / Time: ', USER(), ' / ', NOW(), '\n', 
        IF(NEW.identifier !=OLD.identifier,
          CONCAT('Identifier: ',
            IF(NEW.identifier !='', 
              NEW.identifier,
              CONCAT(
                IF( LENGTH(NEW.Author)=0, 'Anonymous',  -- if author is empty, bibus puts "Anonymous"
                  REPLACE(
                    IF (LOCATE(',', NEW.Author) = 0, -- if there is no comma,...
                      NEW.Author, -- take full author field
                      LEFT(NEW.Author, LOCATE(',',NEW.Author)-1)),-- else take everything before the comma
                  ' ', '_') -- replace spaces by underscores
                )
                , NEW.Year, '#', NEW.id, 'L' -- and add #999L where 999 is the id
              )
            ),
          '\n'),
          ''),
        IF(NEW.BibliographicType !=OLD.BibliographicType,
          CONCAT('BibliographicType: ', CASE NEW.BibliographicType
            WHEN 0 THEN 'ARTICLE'  WHEN 1 THEN 'BOOK'  WHEN 2 THEN 'BOOKLET'  WHEN 3 THEN 'CONFERENCE'  WHEN 4 THEN 'INBOOK'
            WHEN 5 THEN 'INCOLLECTION'  WHEN 6 THEN 'INPROCEEDINGS'  WHEN 7 THEN 'JOURNAL'  WHEN 8 THEN 'MANUAL'
            WHEN 9 THEN 'MASTERTHESIS'  WHEN 10 THEN 'MISC'  WHEN 11 THEN 'PHDTHESIS'  WHEN 12 THEN 'PROCEEDINGS'
            WHEN 13 THEN 'TECHREPORT'  WHEN 14 THEN 'UNPUBLISHED'  WHEN 15 THEN 'EMAIL'   WHEN 16 THEN 'WWW'
            WHEN 17 THEN 'CUSTOM1' WHEN 18 THEN 'CUSTOM2'  WHEN 19 THEN 'CUSTOM3'  WHEN 20 THEN 'CUSTOM4'
            WHEN 21 THEN 'CUSTOM5'  ELSE 'unknown'  END, '\n'),
        ''),
      IF(NEW.Address !=OLD.Address, CONCAT('Address: ', NEW.Address, '\n'), ''),
      IF(NEW.Annote !=OLD.Annote, CONCAT('Annote: ', NEW.Annote, '\n'), ''),
      IF(NEW.Author !=OLD.Author, CONCAT('Author: ', NEW.Author, '\n'), ''),
      IF(NEW.Booktitle !=OLD.Booktitle, CONCAT('Booktitle: ', NEW.Booktitle, '\n'), ''),
      IF(NEW.Chapter !=OLD.Chapter, CONCAT('Chapter: ', NEW.Chapter, '\n'), ''),
      IF(NEW.Edition !=OLD.Edition, CONCAT('Edition: ', NEW.Edition, '\n'), ''),
      IF(NEW.Editor !=OLD.Editor, CONCAT('Editor: ', NEW.Editor, '\n'), ''),
      IF(NEW.Howpublished !=OLD.Howpublished, CONCAT('Howpublished: ', NEW.Howpublished, '\n'), ''),
      IF(NEW.Institution !=OLD.Institution, CONCAT('Institution: ', NEW.Institution, '\n'), ''),
      IF(NEW.Journal !=OLD.Journal, CONCAT('Journal: ', NEW.Journal, '\n'), ''),
      IF(NEW.Month !=OLD.Month, CONCAT('Month: ', NEW.Month, '\n'), ''),
      IF(NEW.Note !=OLD.Note, CONCAT('Note: ', NEW.Note, '\n'), ''),
      IF(NEW.Number !=OLD.Number, CONCAT('Number: ', NEW.Number, '\n'), ''),
      IF(NEW.Organizations !=OLD.Organizations, CONCAT('Organizations: ', NEW.Organizations, '\n'), ''),
      IF(NEW.Pages !=OLD.Pages, CONCAT('Pages: ', NEW.Pages, '\n'), ''),
      IF(NEW.Publisher !=OLD.Publisher, CONCAT('Publisher: ', NEW.Publisher, '\n'), ''),
      IF(NEW.School !=OLD.School, CONCAT('School: ', NEW.School, '\n'), ''),
      IF(NEW.Series !=OLD.Series, CONCAT('Series: ', NEW.Series, '\n'), ''),
      IF(NEW.Title !=OLD.Title, CONCAT('Title: ', NEW.Title, '\n'), ''),
      IF(NEW.Report_Type !=OLD.Report_Type, CONCAT('Report_Type: ', NEW.Report_Type, '\n'), ''),
      IF(NEW.Volume !=OLD.Volume, CONCAT('Volume: ', NEW.Volume, '\n'), ''),
      IF(NEW.Year !=OLD.Year, CONCAT('Year: ', NEW.Year, '\n'), ''),
      IF(NEW.URL !=OLD.URL, CONCAT('URL: ', NEW.URL, '\n'), ''),
      IF(NEW.Custom1 !=OLD.Custom1, CONCAT('Custom1: ', NEW.Custom1, '\n'), ''),
      IF(NEW.Custom2 !=OLD.Custom2, CONCAT('Custom2: ', NEW.Custom2, '\n'), ''),
      IF(NEW.Custom3 !=OLD.Custom3, CONCAT('Custom3: ', NEW.Custom3, '\n'), ''),
      IF(NEW.Custom4 !=OLD.Custom4, CONCAT('Custom4: ', NEW.Custom4, '\n'), ''),
      IF(NEW.Custom5 !=OLD.Custom5, CONCAT('Custom5: ', NEW.Custom5, '\n'), ''),
      IF(NEW.ISBN !=OLD.ISBN, CONCAT('ISBN: ', NEW.ISBN, '\n'), ''),
      IF(NEW.abstract !=OLD.abstract, CONCAT('abstract: ', CHAR_LENGTH(NEW.abstract), ' characters (was ',CHAR_LENGTH(OLD.abstract),' char.) \n'), '')
      )
      WHERE bibeditsummary.id=NEW.id;
  
    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
    UPDATE bibeditsummary 
      SET edits=CONCAT(edits,
        '----------------------------- \n',
        'User / Time: ', USER(), ' / ', NOW(), '\nREFERENCE DELETED.')
      WHERE bibeditsummary.id=OLD.id;

    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 ;

Tools to help administer and view versioning data

To find out how to view the triggers you have created, look here.

To view versioning data, you need to access the database directly with a client capable of sending any query (bibus-biblio alone does not do this).

You can use phpMyAdmin if it is installed on the database server. Advanced users will probably write a SQL command. The others can click on the bibhistory table, then click on "Search", write a number in the text box which is in front of "id =", then click "Go". You can click on a column header to sort the data according to this column.

If you want a simple client, you can use one of the following (both are free/GNU GPL):

  • HeidiSQL is the easiest to use. Works on Windows (and Linux with wine). Is portable (just unzip in a folder or on a USB key). To view the history of reference number 5: Double-click on your database, click on bibhistory, click on "Data" tab, click "Filter" button and type "id=5" (without the quotes). You can click on a column header to sort the data according to this column.
  • MySQL Query Browser is the official client (by Sun Microsystems). It exists for Windows, Linux and MacOSX 10.4 or above. You need to have a basic knowledge of SQL to use it. After connecting, you need to expand the table names under the database name (right pane), then drag and drop the bibhistory table in the upper blank text pane. This will insert "SELECT * FROM bibhistory b LIMIT 0,1000". Then you need to type " where id=5 " (without the quotes) after "FROM bibhistory b", then click "Execute". To sort the data, you need to use the "order by" command. A complete command is, for example:
SELECT * FROM bibhistory b where id=5 order by mdate
Personal tools