Daily backups are always a good idea – but sometimes a convenient accessible history is more helpful than having to work with raw database dumps.
For I, Librarian backups are rather easy. This document describes the workaround necessary in order to introduce shadowing and change history for the database used for the metadata of each paper.
The basic idea is to add a SQL TRIGGER to record any raw changes made to the SQLite database. Enter the library/database folder of your installation, and access the database by
sqlite3 library.sq3
The following commands create a table holding the history data and the corresponding triggers.
CREATE TABLE library_shadow (
library_id integer,
file text NOT NULL DEFAULT '',
authors text NOT NULL DEFAULT '',
affiliation text NOT NULL DEFAULT '',
title text NOT NULL DEFAULT '',
journal text NOT NULL DEFAULT '',
secondary_title text NOT NULL DEFAULT '',
year text NOT NULL DEFAULT '',
volume text NOT NULL DEFAULT '',
issue text NOT NULL DEFAULT '',
pages text NOT NULL DEFAULT '',
abstract text NOT NULL DEFAULT '',
keywords text NOT NULL DEFAULT '',
editor text NOT NULL DEFAULT '',
publisher text NOT NULL DEFAULT '',
place_published text NOT NULL DEFAULT '',
reference_type text NOT NULL DEFAULT '',
uid text NOT NULL DEFAULT '',
doi text NOT NULL DEFAULT '',
url text NOT NULL DEFAULT '',
addition_date text NOT NULL DEFAULT '',
rating integer NOT NULL DEFAULT '',
authors_ascii text NOT NULL DEFAULT '',
title_ascii text NOT NULL DEFAULT '',
abstract_ascii text NOT NULL DEFAULT '',
added_by integer NOT NULL DEFAULT '',
modified_by integer NOT NULL DEFAULT '',
modified_date text NOT NULL DEFAULT '',
custom1 text NOT NULL DEFAULT '',
custom2 text NOT NULL DEFAULT '',
custom3 text NOT NULL DEFAULT '',
custom4 text NOT NULL DEFAULT '',
bibtex text NOT NULL DEFAULT '',
ch_time text NOT NULL DEFAULT '',
d text NOT NULL DEFAULT ''
);
CREATE INDEX shadow_index on library_shadow(library_id);
CREATE TRIGGER shadow_library_insert AFTER INSERT ON library FOR EACH ROW
BEGIN
INSERT INTO library_shadow VALUES(new.id, new.file, new.authors, new.affiliation, new.title,new.journal, new.secondary_title, new.year, new.volume, new.issue, new.pages, new.abstract, new.keywords, new.editor, new.publisher, new.place_published, new.reference_type, new.uid, new.doi, new.url, new.addition_date, new.rating, new.authors_ascii, new.title_ascii, new.abstract_ascii, new.added_by, new.modified_by, new.modified_date, new.custom1, new.custom2, new.custom3, new.custom4, new.bibtex, strftime('%s','now'), 'i');
END;
CREATE TRIGGER shadow_library_delete BEFORE DELETE ON library FOR EACH ROW
BEGIN
INSERT INTO library_shadow VALUES(old.id, old.file, old.authors, old.affiliation, old.title,old.journal, old.secondary_title, old.year, old.volume, old.issue, old.pages, old.abstract, old.keywords, old.editor, old.publisher, old.place_published, old.reference_type, old.uid, old.doi, old.url, old.addition_date, old.rating, old.authors_ascii, old.title_ascii, old.abstract_ascii, old.added_by, old.modified_by, old.modified_date, old.custom1, old.custom2, old.custom3, old.custom4, old.bibtex, strftime('%s','now'), 'd');
END;
CREATE TRIGGER shadow_library_update AFTER UPDATE ON library FOR EACH ROW
BEGIN
INSERT INTO library_shadow VALUES(new.id, new.file, new.authors, new.affiliation, new.title,new.journal, new.secondary_title, new.year, new.volume, new.issue, new.pages, new.abstract, new.keywords, new.editor, new.publisher, new.place_published, new.reference_type, new.uid, new.doi, new.url, new.addition_date, new.rating, new.authors_ascii, new.title_ascii, new.abstract_ascii, new.added_by, new.modified_by, new.modified_date, new.custom1, new.custom2, new.custom3, new.custom4, new.bibtex, strftime('%s','now'), 'u');
END;
During initial setup, a current snapshot of all entries has to be added to the database. This is done by
INSERT INTO library_shadow(library_id, file, authors, affiliation, title,journal, secondary_title, year, volume, issue, pages, abstract, keywords, editor, publisher, place_published, reference_type, uid, doi, url, addition_date, rating, authors_ascii, title_ascii, abstract_ascii, added_by, modified_by, modified_date, custom1, custom2, custom3, custom4, bibtex, ch_time, d) SELECT new.id, new.file, new.authors, new.affiliation, new.title,new.journal, new.secondary_title, new.year, new.volume, new.issue, new.pages, new.abstract, new.keywords, new.editor, new.publisher, new.place_published, new.reference_type, new.uid, new.doi, new.url, new.addition_date, new.rating, new.authors_ascii, new.title_ascii, new.abstract_ascii, new.added_by, new.modified_by, new.modified_date, new.custom1, new.custom2, new.custom3, new.custom4, new.bibtex, strftime('%s','now'), 'i' FROM library new;
Now you can leave the console SQLite client by typing .exit
Now, the source has to be patched. The base version for this patchset is 2.10. Download this patch to your installation directory and test it
patch -p1 --dry-run < update.diff
If you see no error messages, you can safely remove the –dry-run flag from this command to actually modify your installation.
Now there is a new entry in the tools section: “restore deleted entries”. This will only restore the metadata but not the PDF document associated with the entry. For each paper, the changes to the metadata are recorded and added to the details page.
