I primarily use jalvesaq/zotcite to interact with zotero from neovim. I primarily use it

• search for an article and open its attachements.
• extract annotations
• check info of selected reference

In this I'm trying to extend it to

1. use bbt-citekeys (to keep citing consistent with vimtex)
2. read the code to understand how can I use pythons script to bridge sioyeks and zotero annotations. i.e. I should be able to add highlights from sioyek to zotero database.
3. Allow filtering live filtering for collections in Zseek or make it collections support tex files or just move entire thing to vimtex

import sqlite3
b = "/Users/anand.kumar/Zotero/better-bibtex.sqlite"
z = "/Users/anand.kumar/Zotero/zotero.sqlite"

conn = sqlite3.connect(z)
cur  = conn.cursor()
queryA = f"ATTACH DATABASE '{b}' AS bbt;" 
cur.execute(queryA)

getting to understand databases :

citekeys = cur.execute("select itemID, citationKey from citationkey").fetchall()
items    = cur.execute("select itemID, key from items").fetchall()
itemData  = cur.execute("select fieldID, valueID from itemData").fetchall()
itemDataValue = cur.execute("select value, valueID from itemDataValues").fetchall()
fields = cur.execute("select  fieldID, fieldName from fields").fetchall()

code below returns [itemId, itemKey, citationKey] table. All the fields are unique. All rows are available. No need to delete anything.

queryB = """
SELECT citationkey.itemID, citationkey.itemKey, citationkey.citationKey
FROM citationkey 
LEFT JOIN items on citationkey.itemID = items.itemID
"""
join = cur.execute(queryB).fetchall()

Zotero stores each field agains ID e.g. title, author, abstract etc has separate ids. Query below maps [itemID, fieldName, fieldValue]. Thus, if we want all titles, filter second column and want all details for particular item, filter first column.

queryC = """
SELECT itemData.itemID, fields.fieldName, itemDataValues.value 
FROM itemData
JOIN fields ON itemData.fieldID = fields.fieldID
JOIN itemDataValues ON itemData.valueID = itemDataValues.valueID
"""
data = cur.execute(queryC).fetchall()