Find itemkey based on SQLite database

[thecodehen]

One method to solve the issue of getting the itemkey for linked attachments is to read the SQLite database ~/Zotero/zotero.sqlite, iterate through each attachment, then find the corresponding itemkey. I've peeped into the database and saw that there are two tables that would be useful: itemAttachments and items.

One can query the itemID (or perhaps parentItemID) of each attachment in the itemAttachments table then lookup the corresponding key in the items table. I've attached photos of example entries of each table.

Note that the absolute path of an attachment can be found if the attachment is linked. We can also filter attachments by whether the path column starts with storage:.

Expert from itemAttachments:

Expert from items:

[mhucka]

The thing is, according to a response by one of the Zotero developers to a question I asked on the Zotero forum, it is not possible to identify them 100% uniquely even when reading the database.

My question was:

Is there any way to recover the attachment key for the files in this case?

Reply:

Not without reading the database (and even then not uniquely, because there could theoretically be more than one linked-file attachment pointing at the same file, though that wouldn't usually be the case). Linked files don't "get stored by Zotero" at all — those are files that already exist somewhere on the disk and are simply linked to.

For that reason, I'm a bit worried about going down this road and giving people something that might produce the wrong answer in some cases.