SQLite, like most DBMS, allows the creation of user defined functions so the functionality of DBMS can be extended. This is done using the create_function function. Here is a sample of how it works, using anki’s database. I create a function get_field that takes a “flds” attribute of a note and an index, and retrieves the corresponding field (0-based):
import sqlite3
conn = sqlite3.connect('/tmp/collection.anki2')
def _get_field(st,idx):
lst = st.split("\x1f")
if (len(lst)> idx):
return(lst[idx])
else:
return None
conn.create_function("get_field", 2, _get_field)
cursor = conn.cursor()
print(list(cursor.execute("select get_field(flds, 1), get_field(flds,2) from notes limit 10;")))
Note how by writing this simple UDF, one can query and retrieve the field values individually. While the fields cannot be indexed by the DBMS, they can still be compared using LIKE in the WHERE clause.
I tried to add a UDF to my addon. However, I could not find a way to get a connection fo the database, so I could register the UDF.
My question is, would it be possible to either:
- Add a UDF get_fields (code above) that can be used anywhere in anki and its addons?
or
- Expose the connection in the API of the database layer, so that addon developers can add their own UDFs?