Summary:
I am trying to update a database that contains metadata for many scientific articles. In particular, the database is for the Papers application. My problem is that when I try to UPDATE a table, I get
Error: no such function: enableTransactionLog.
What is the problem? If you don't know, can you point me to where I should look for debugging? I've done a lot of searching on SO and Google, but I can't find anything that helps me.
More background:
An overview of my table:
sqlite> PRAGMA table_info(Publication);
0|ROWID|INTEGER|0||1
1|abbreviation|TEXT|0||0
2|accepted_date|TEXT|0||0
3|attributed_subtitle|TEXT|0||0
4|attributed_title|TEXT|0||0
5|author_string|TEXT|0||0
6|author_year_string|TEXT|0||0
7|body|TEXT|0||0
8|bundle|TEXT|0||0
9|bundle_string|TEXT|0||0
10|canonical_title|TEXT|0||0
11|citekey|TEXT|0||0
...I cut off the last ~50 lines of output
My command:
sqlite> UPDATE Publication SET citekey=NULL;
Error: no such function: enableTransactionLog
The schema.
CREATE TABLE "Publication" (ROWID INTEGER PRIMARY KEY, abbreviation TEXT, accepted_date TEXT, attributed_subtitle TEXT, attributed_title TEXT, author_string TEXT, author_year_string TEXT, body TEXT, bundle TEXT, bundle_string TEXT, canonical_title TEXT, citekey TEXT, citekey_base TEXT, copyright TEXT, created_at DOUBLE, document_number TEXT, doi TEXT, draft INTEGER, editor_string TEXT, endpage TEXT, extensible_properties BLOB, factor FLOAT, flagged INTEGER, full_author_string TEXT, full_editor_string TEXT, full_photographer_string TEXT, full_translator_string TEXT, imported_date DOUBLE, initial TEXT, institution TEXT, keyword_string TEXT, kind TEXT, label INTEGER, language TEXT, lastread_date DOUBLE, location TEXT, manuscript INTEGER, marked_deleted INTEGER, marked_duplicate INTEGER, marked_edited INTEGER, matched INTEGER, newly_added INTEGER, notes TEXT, number TEXT, open_access INTEGER, photographer_string TEXT, place TEXT, printed_date DOUBLE, privacy_level INTEGER, publication_count INTEGER, publication_date TEXT, publication_string TEXT, publisher TEXT, quality INTEGER, rating INTEGER, read_status INTEGER, refreshed_at DOUBLE, revision_date TEXT, searchresult INTEGER, startpage TEXT, status TEXT, submission_date TEXT, subtitle TEXT, subtype INTEGER, summary TEXT, tag_string TEXT, times_cited INTEGER, times_read INTEGER, title TEXT, translator_string TEXT, type INTEGER, update_count INTEGER, updated_at DOUBLE, user_label TEXT, uuid TEXT UNIQUE NOT NULL, version TEXT, volume TEXT, FOREIGN KEY (bundle) REFERENCES Publication (uuid) ON DELETE RESTRICT);
CREATE INDEX Publication_abbreviation_IDX ON Publication (abbreviation);
CREATE INDEX Publication_accepted_date_IDX ON Publication (accepted_date);
CREATE INDEX Publication_citekey_IDX ON Publication (citekey);
CREATE INDEX Publication_citekey_base_IDX ON Publication (citekey_base);
CREATE INDEX Publication_created_at_IDX ON Publication (created_at);
CREATE INDEX Publication_doi_IDX ON Publication (doi);
CREATE INDEX Publication_endpage_IDX ON Publication (endpage);
CREATE INDEX Publication_factor_IDX ON Publication (factor);
CREATE INDEX Publication_imported_date_IDX ON Publication (imported_date);
about 50 lines of output deleted here
CREATE INDEX Publication_version_IDX ON Publication (version);
CREATE INDEX Publication_volume_IDX ON Publication (volume);
CREATE TRIGGER _del_log_Publication AFTER DELETE ON Publication WHEN enableTransactionLog() AND (OLD."searchresult" <> 1 AND NOT (OLD."type" < 0 AND OLD."type" > -1000)) BEGIN
INSERT INTO changeLog (modifiedDate, modTable, modUUID, modType, modColumn, modValue, device, dbRevision) VALUES ((strftime("%s", "now") + strftime("%f", "now") - strftime("%S", "now")), "Publication", OLD."uuid", 1, NULL, NULL, device(), dbRevision());END;
CREATE TRIGGER _del_log_Publication_Bundle AFTER DELETE ON Publication WHEN enableTransactionLog() AND (OLD."searchresult" <> 1 AND (OLD."type" < 0 AND OLD."type" > -1000)) BEGIN
INSERT INTO changeLog (modifiedDate, modTable, modUUID, modType, modColumn, modValue, device, dbRevision) VALUES ((strftime("%s", "now") + strftime("%f", "now") - strftime("%S", "now")), "Publication", OLD."uuid", 1, "abbreviation,accepted_date,attributed_subtitle,attributed_title,body,bundle,canonical_title,citekey,citekey_base,copyright,created_at,document_number,doi,draft,endpage,extensible_properties,factor,flagged,imported_date,initial,institution,kind,label,language,lastread_date,location,manuscript,marked_deleted,marked_duplicate,marked_edited,matched,newly_added,notes,number,open_access,place,printed_date,privacy_level,publication_date,publication_string,publisher,quality,rating,read_status,refreshed_at,revision_date,searchresult,startpage,status,submission_date,subtitle,subtype,summary,tag_string,times_cited,times_read,title,type,updated_at,user_label,uuid,version,volume", quote(OLD."abbreviation")||','||quote(OLD."accepted_date")||','||quote(OLD."attributed_subtitle")||','||quote(OLD."attributed_title")||','||quote(OLD."body")||','||quote(OLD."bundle")||','||quote(OLD."canonical_title")||','||quote(OLD."citekey")||','||quote(OLD."citekey_base")||','||quote(OLD."copyright")||','||quote(OLD."created_at")||','||quote(OLD."document_number")||','||quote(OLD."doi")||','||quote(OLD."draft")||','||quote(OLD."endpage")||','||quote(OLD."extensible_properties")||','||quote(OLD."factor")||','||quote(OLD."flagged")||','||quote(OLD."imported_date")||','||quote(OLD."initial")||','||quote(OLD."institution")||','||quote(OLD."kind")||','||quote(OLD."label")||','||quote(OLD."language")||','||quote(OLD."lastread_date")||','||quote(OLD."location")||','||quote(OLD."manuscript")||','||quote(OLD."marked_deleted")||','||quote(OLD."marked_duplicate")||','||quote(OLD."marked_edited")||','||quote(OLD."matched")||','||quote(OLD."newly_added")||','||quote(OLD."notes")||','||quote(OLD."number")||','||quote(OLD."open_access")||','||quote(OLD."place")||','||quote(OLD."printed_date")||','||quote(OLD."privacy_level")||','||quote(OLD."publication_date")||','||quote(OLD."publication_string")||','||quote(OLD."publisher")||','||quote(OLD."quality")||','||quote(OLD."rating")||','||quote(OLD."read_status")||','||quote(OLD."refreshed_at")||','||quote(OLD."revision_date")||','||quote(OLD."searchresult")||','||quote(OLD."startpage")||','||quote(OLD."status")||','||quote(OLD."submission_date")||','||quote(OLD."subtitle")||','||quote(OLD."subtype")||','||quote(OLD."summary")||','||quote(OLD."tag_string")||','||quote(OLD."times_cited")||','||quote(OLD."times_read")||','||quote(OLD."title")||','||quote(OLD."type")||','||quote(OLD."updated_at")||','||quote(OLD."user_label")||','||quote(OLD."uuid")||','||quote(OLD."version")||','||quote(OLD."volume"), device(), dbRevision());END;
I cutoff the rest of the output
I'm using the sqlite3 that shipped with my MacBook Pro. If you're interested, I'm trying to accomplish the task described in the third post of this discussion.
The solution (after Dan D. got me 99.9% of the way there):
sqlite> DROP TRIGGER _upd_log_Publication_citekey;
sqlite> UPDATE Publication SET citekey = NULL;
sqlite> CREATE TRIGGER _upd_log_Publication_citekey AFTER UPDATE OF citekey ON Publication
...> WHEN enableTransactionLog() AND (NEW."searchresult" = 0) AND (OLD."citekey" IS NULL AND NEW."citekey" IS NOT NULL OR OLD."citekey" IS NOT NULL AND NEW."citekey" IS NULL OR OLD."citekey" <> NEW."citekey") BEGIN
...> DELETE FROM changeLog WHERE modUUID = NEW."uuid" AND modColumn="citekey" AND modType=2; -- delete old changes immediately, for cleanup
...> INSERT INTO changeLog (modifiedDate, modTable, modUUID, modType, modColumn, modValue, device, dbRevision) VALUES ((strftime("%s", "now") + strftime("%f", "now") - strftime("%S", "now")), "Publication", NEW."uuid", 2, "citekey", NEW."citekey", device(), dbRevision());END;
