How to find the directory where PDF attachments are stored in zotero.sqlite?

mrli6666

Zotero stores attachments in directories like D:\storage\2A4QZCJ8. I have found the attachment path in the itemAttachments table in zotero.sqlite, and the result is: "storage:Rousseau - 2006.pdf".

I want to find the directory corresponding to storage，just like: 2A4QZCJ8. Could you please tell me in which table of zotero.sqlite I can find the information about the directory where attachments are stored? Thank you!

tim820

'itemID' in that itemAttachments table maps to the same 'itemID' in the items table, where you find the 'key' field, which is the folder name (under the Zotero data directory's 'storage' folder) where attachments with that 'storage:' prefix are stored (the path for linked attachment files is handled differently).

So you assemble the file path from those pieces of information. For example with javascript/SQL like the following to JOIN those tables to get a list of *all* local attachmnt files (run under Developer\Run Javascript):

var filepathnames = await Zotero.DB.columnQueryAsync("SELECT TRIM(key || '\\' || REPLACE(path,'storage:','')) AS filepathnames FROM itemAttachments JOIN items USING (itemID) WHERE libraryID=1 AND path IS NOT NULL AND path LIKE ? ORDER BY key",'storage:%');
return filepathnames.join('\n');

You of course *know* what your Zotero data directory path is, but if you want to get it in code, and then use it to assemble the whole pathname, here's an example of how to do that (in this case for PDF attachments only):

var datadir = Zotero.DataDirectory.dir;
var sql = "SELECT (? || ? || key || ? || REPLACE(path,?,?)) AS filepathnames FROM itemAttachments JOIN items USING (itemID) WHERE libraryID=1 AND path IS NOT NULL AND path LIKE ? ORDER BY filepathnames";
var filepathnames = await Zotero.DB.columnQueryAsync(
sql,
[
datadir,
'\\storage\\',
'\\',
'storage:',
'',
'storage:%.pdf'
]
);
return filepathnames.join('\n');

(there is probably a more elegant way of doing this, for example with just javascript Zotero functions, but I don't know what that is)

Caveat: the handling of backslashes in the above code is for Windows. You'll need something slightly different for Macs and Linux.