sql - sqlite更新上的“没有这样的功能”错误

Question

总结:我正在尝试更新包含许多科学文章的元数据的数据库。特别是，数据库用于 Papers。应用。我的问题是，当我尝试 UPDATE 一个表时，我得到了

错误:没有这样的函数:enableTransactionLog。

问题是什么？如果你不知道，你能指出我应该在哪里寻找调试吗？我在 SO 和 Google 上进行了大量搜索，但找不到任何对我有帮助的东西。

更多背景:

我的表概览:

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

我的命令:

sqlite> UPDATE Publication SET citekey=NULL;
Error: no such function: enableTransactionLog

架构。

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

我使用的是 MacBook Pro 附带的 sqlite3。如果您有兴趣，我正在努力完成 this discussion 的第三篇文章中描述的任务。 .

解决方案(在 Dan D. 让我完成了 99.9% 的过程之后):

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;

Answer 1

我看到两个选项:

1. 使用返回 false 的 UDF 模拟 enableTransactionLog()。这不能从 sqlite3 控制台完成。

2. 删除触发器，运行更新，然后使用从 .schema 输出的语句重新创建触发器。这可以从 sqlite3 控制台完成。