malformed database schema error

[jacobleft]

I am experiencing this error from zothero.

12:31:39 workflow.py:2114 ERROR    malformed database schema (autoexport_setting_insert) - trigger autoexport_setting_insert cannot reference objects in database betterbibtex
Traceback (most recent call last):
  File "/Users/jacob/Library/Application Support/Alfred/Alfred.alfredpreferences/workflows/user.workflow.3536781B-080F-473B-B07D-8AD609D0784F/lib/workflow/workflow.py", line 2107, in run
    func(self)
  File "/Users/jacob/Library/Application Support/Alfred/Alfred.alfredpreferences/workflows/user.workflow.3536781B-080F-473B-B07D-8AD609D0784F/zh.py", line 734, in main
    return do_search(query)
           ^^^^^^^^^^^^^^^^
  File "/Users/jacob/Library/Application Support/Alfred/Alfred.alfredpreferences/workflows/user.workflow.3536781B-080F-473B-B07D-8AD609D0784F/zh.py", line 130, in do_search
    if app.stale and not running:
       ^^^^^^^^^
  File "/Users/jacob/Library/Application Support/Alfred/Alfred.alfredpreferences/workflows/user.workflow.3536781B-080F-473B-B07D-8AD609D0784F/lib/zothero/core.py", line 166, in stale
    if self.index.empty:
       ^^^^^^^^^^
  File "/Users/jacob/Library/Application Support/Alfred/Alfred.alfredpreferences/workflows/user.workflow.3536781B-080F-473B-B07D-8AD609D0784F/lib/zothero/core.py", line 159, in index
    self._index.update(self.zotero)
  File "/Users/jacob/Library/Application Support/Alfred/Alfred.alfredpreferences/workflows/user.workflow.3536781B-080F-473B-B07D-8AD609D0784F/lib/zothero/index.py", line 277, in update
    if not self._update(zot, force):
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/jacob/Library/Application Support/Alfred/Alfred.alfredpreferences/workflows/user.workflow.3536781B-080F-473B-B07D-8AD609D0784F/lib/zothero/index.py", line 325, in _update
    for e in it:
  File "/Users/jacob/Library/Application Support/Alfred/Alfred.alfredpreferences/workflows/user.workflow.3536781B-080F-473B-B07D-8AD609D0784F/lib/zothero/zotero.py", line 292, in all_entries
    yield self._load_entry(row)
          ^^^^^^^^^^^^^^^^^^^^^
  File "/Users/jacob/Library/Application Support/Alfred/Alfred.alfredpreferences/workflows/user.workflow.3536781B-080F-473B-B07D-8AD609D0784F/lib/zothero/zotero.py", line 344, in _load_entry
    e.citekey = self.bbt.citekey('{}_{}'.format(e.library, e.key))
                ^^^^^^^^
  File "/Users/jacob/Library/Application Support/Alfred/Alfred.alfredpreferences/workflows/user.workflow.3536781B-080F-473B-B07D-8AD609D0784F/lib/zothero/zotero.py", line 214, in bbt
    self._bbt = BetterBibTex(self.bibpath_copy)
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/jacob/Library/Application Support/Alfred/Alfred.alfredpreferences/workflows/user.workflow.3536781B-080F-473B-B07D-8AD609D0784F/lib/zothero/betterbibtex.py", line 54, in __init__
    row = conn.execute(SQL).fetchone()
          ^^^^^^^^^^^^^^^^^
sqlite3.DatabaseError: malformed database schema (autoexport_setting_insert) - trigger autoexport_setting_insert cannot reference objects in database betterbibtex
12:31:39 workflow.py:2116 INFO     for assistance, see: https://github.com/giovannicoppola/zothero/issues

Reload Zotero Data can not fix this. I suspect the cause is that it is incompatible with the last version of betterbibtex, which is 6.7.128 as of today.

[tophee]

I am having the same issue and I'm wondering (given that changes in BBT repeatedly broke Zothero) whether it might be an idea to switch from accessing Zotero via BetterBibtex to accessing the Zotero API. Then again, it would probably require a major rewrite of the workflow...

[giovannicoppola]

@jacobleft how do you check the current betterBibtex version? I updated Zotero and Zothero it is still working for me (I don't use either, so I might be missing something).

[jacobleft]

I am on Zotero 7-beta. Click Tools->Add-ons, find Better BibTex, click ...-> Manage, then you see the version. But I am curious. Why maintain this repo if you don't use it?

[giovannicoppola]

Yes, it seems it's due to the new Better BibTeX, as the beta itself did not change the database and works fine. I will try to fix it, but I suspect this will break Zothero for non-beta users. @tophee using the API will not help because I understand Zotero (inexplicably!?) does not support citekeys natively. As for my motivation: just trying to keep a great workflow alive! If you use BIbTeX there is a great alternative in the Gallery.

[giovannicoppola]

Yes that error message comes directly from trying to open the Better BibTeX sqlite database in the newer version. Hopefully the developer will fix soon

[tophee]

Does this mean that downgrading to an earlier version of BetterBibtex would solve the issue?

I seem to remember that I had to do exactly that a while ago to resolve a similar issue. Maybe we should just turn off automatic updates for BetterBibtex?

Zotero (inexplicably!?) does not support citekeys natively.

Oh, right, now I understand why everyone is going via BetterBibTeX.

But just to double check: isn't it sufficient that betterbibtex creates and manages the citekeys but once they are stored in the zotero database, they can be accessed without BetterBibteX? - Oh, or is there no api-support for that, perhaps?

In that case accessing the sqlite database locally would probably be the way to go.

[giovannicoppola]

yes, a previous version (I tried 6.7.71) works (but in Zotero 6, not 7 beta). Yes, Zothero uses the sqlite databases created by Zotero/Better BibTeX. In theory you can use them without Better BibTeX, assuming that you don't need to update your library.

[jacobleft]

@giovannicoppola You are the real hero. I can confirm ZotHero used to work on (old?) 7-beta with old betterbibtex. It just does not work with the latest of both.

[tophee]

Oh, that sqlite db is created by BBT! Learning so much today...

[s4npii]

I'm so glad I found this thread. Now I know that it's not my fautl ;)

[giovannicoppola]

@tophee's idea should work as a temporary workaround, again provided that you don't need to update your library:

1. downgrade to Zotero 6, and to a previous version of BBT (e.g. 6.7.71) to create the database
2. upgrade to Zotero 7 beta, without installing BBT
3. Zothero should now work

[retorquere]

The database schema for auto-exports did change, and another change is slated for the citation key database.

[lutefiasco]

Hi, all - just FYI, if you haven't updated to Zotero 7, it's possible to just downgrade to BBT (I used 6.7.71 via Zotero tools/addons/install add on from file). There's a "corrupt database" error message, but reloading Zotero data via zotconf seems to have resolved all issues with the workflow for now.

[retorquere]

There should be no need to downgrade all the way back to .71, 6.7.125 introduced the schema change. Please don't encourage people to downgrade that far back. Zero support on such old versions.

Apps that read or write the database directly are going to have to change. The citationkey database (which zothero does read) is set to change too. I will see if I can prepare a PR for zothero.

[retorquere]

Untested but this should work:

diff --git a/src/lib/zothero/betterbibtex.py b/src/lib/zothero/betterbibtex.py
index df9f23a..eee83ec 100644
--- a/src/lib/zothero/betterbibtex.py
+++ b/src/lib/zothero/betterbibtex.py
@@ -16,9 +16,6 @@ from .util import timed
 log = logging.getLogger(__name__)

-SQL = "SELECT data FROM `better-bibtex` WHERE name = 'better-bibtex.citekey';"
-
-
 class BetterBibTex(object):
     """Read citkeys from BetterBibTex database.

@@ -47,15 +44,14 @@ class BetterBibTex(object):

             return

-        conn = sqlite3.connect(dbpath)
+        conn = sqlite3.connect(':memory:')
+        conn.execute('ATTACH DATABASE ? AS betterbibtex', dbpath)

         with timed('load Better Bibtex data'):

-            row = conn.execute(SQL).fetchone()
-            data = json.loads(row[0])['data']
             self._refkeys = {
-                str(ck['libraryID']) + '_' + ck['itemKey']: ck['citekey']
-                for ck in data
+                str(ck['libraryID']) + '_' + ck['itemKey']: ck['citationKey']
+                for c in conn.execute('select * from betterbibtex.citationkey')
             }
         self.exists = True

[retorquere]

Hi, all - just FYI, if you haven't updated to Zotero 7, it's possible to just downgrade to BBT (I used 6.7.71 via Zotero tools/addons/install add on from file). There's a "corrupt database" error message, but reloading Zotero data via zotconf seems to have resolved all issues with the workflow for now.

Which Zotero will undo within 48 hours by finding a newer BBT and installing that automatically. Please apply the patch to Zothero.

[n-eagle-eye]

@retorquere thanks for the patch. But would you give me some guidance on how to apply the patch?

I am a noob/beginner, but I was wondering if I should simply replace the code in the betterbibtex.py by the one you put.

If so, would you tell me which one of code blocks should I use?

Sorry again if the answer is obvious!

[retorquere]

No, those are targeted changes, you can't replace the whole file. The author of this program will have to get involved.

[retorquere]

I am a noob/beginner, but I was wondering if I should simply replace the code in the betterbibtex.py by the one you put.

If so, would you tell me which one of code blocks should I use?

Every line that starts with - should be removed, every line that starts with + should be added. The other lines provide context on where to make these changes.

[retorquere]

switch from accessing Zotero via BetterBibtex to accessing the Zotero API

The zotero API does not have these citation keys.

[fty1777]

Untested but this should work:

diff --git a/src/lib/zothero/betterbibtex.py b/src/lib/zothero/betterbibtex.py
index df9f23a..eee83ec 100644
--- a/src/lib/zothero/betterbibtex.py
+++ b/src/lib/zothero/betterbibtex.py
@@ -16,9 +16,6 @@ from .util import timed
 log = logging.getLogger(__name__)

-SQL = "SELECT data FROM `better-bibtex` WHERE name = 'better-bibtex.citekey';"
-
-
 class BetterBibTex(object):
     """Read citkeys from BetterBibTex database.

@@ -47,15 +44,14 @@ class BetterBibTex(object):

             return

-        conn = sqlite3.connect(dbpath)
+        conn = sqlite3.connect(':memory:')
+        conn.execute('ATTACH DATABASE ? AS betterbibtex', dbpath)

         with timed('load Better Bibtex data'):

-            row = conn.execute(SQL).fetchone()
-            data = json.loads(row[0])['data']
             self._refkeys = {
-                str(ck['libraryID']) + '_' + ck['itemKey']: ck['citekey']
-                for ck in data
+                str(ck['libraryID']) + '_' + ck['itemKey']: ck['citationKey']
+                for c in conn.execute('select * from betterbibtex.citationkey')
             }
         self.exists = True

Thank you so much for the solution!

I have to do a bit adjustments to make it work:

1. Add an additional conn.row_factory = sqlite3.Row to access the data in row using str as indices. Iterating conn.execute('select ...') would return tuples otherwise.
2. Wrap dbpath with parentheses and a comma (like (dbpath, )) to expand the argument correctly into the sql command.
3. Use for ck in conn.execute('select * from betterbibtex.citationkey') instead of for c in .... This might be a typo.

The whole __init__ method now looks like this:

    def __init__(self, datadir):
        """Load Better Bibtex database from Zotero data directory.

        Args:
            datadir (unicode, optional): Zotero's data directory.

        Raises:
            RuntimeError: Raised if Better Bibtex database doesn't exist.

        """

        self._refkeys = {}
        self.exists = False
        #dbpath = os.path.join(datadir, 'better-bibtex.sqlite')
        dbpath = datadir

        if not os.path.exists(dbpath):

            return

        conn = sqlite3.connect(':memory:')
        conn.row_factory = sqlite3.Row
        conn.execute('ATTACH DATABASE ? AS betterbibtex', (dbpath, ))

        with timed('load Better Bibtex data'):

            # for ck in conn.execute('select * from betterbibtex.citationkey'):
            #     print(ck)
            self._refkeys = {
                str(ck['libraryID']) + '_' + ck['itemKey']: ck['citationKey']
                for ck in conn.execute('select * from betterbibtex.citationkey')
            }
        self.exists = True

Now everything works like a charm.

Hope this can help you guys.

[giovannicoppola]

thanks @fty1777! If you want to send me a PR I will be happy to incorporate, or I can just make the edits.

[fty1777]

thanks @fty1777! If you want to send me a PR I will be happy to incorporate, or I can just make the edits.

This might break the support for previous versions of Zotero/BetterBibtex and I am not familiar with how to deal with such multi-version supporting stuff. Or we may just drop the support for out-dated versions and apply the changes directly? The changes I did are trivial and it would be nice if you could make these edits. Thanks again to @giovannicoppola and you @retorquere for your fabulous work.

[retorquere]

I can tell you how you recognize old or new schema. It's not really hard to support both.

[fty1777]

That's great! I'm very happy if I can help!

[retorquere]

You issue

SELECT COUNT(*) FROM betterbibtex.sqlite_master WHERE type='table' AND name = 'citationkey'

If that returns 1, you have the new database (get the keys from betterbibtex.citationkey), if it returns 0 you have the old database (get the keys from betterbibtex."better-bibtex").

[fty1777]

You issue

SELECT COUNT(*) FROM betterbibtex.sqlite_master WHERE type='table' AND name = 'citationkey'

If that returns 1, you have the new database (get the keys from betterbibtex.citationkey), if it returns 0 you have the old database (get the keys from betterbibtex."better-bibtex").

Thanks! I will give it a try.

[giovannicoppola]

thanks @fty1777 and @retorquere. I merged the pull request and released version 2.2 which should fix this.