User defined function to access field data in queries

Development
1

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:

  1. Add a UDF get_fields (code above) that can be used anywhere in anki and its addons?

or

  1. Expose the connection in the API of the database layer, so that addon developers can add their own UDFs?
1 Like
2

Not clear but would a mis-coded user-added function added to sqlite corrupt the whole database?

3

It seems support for custom sql functions was removed with the move to Rust’s sqlite libraries.

From the changelog:

Custom sql functions are no longer supported, and named DB arguments (eg “where id = :id”) are deprecated.

But luckily for you, Anki already exposes the field_at_index sql function to do the same thing. See anki/sqlite.rs at d2c580033b9b59c3a8f5e0e25649ba90fc0b5784 · ankitects/anki · GitHub

4 Likes
4

Thanks. This was useful, as field_at_index was the most important function I wanted to have. However, it would be nice to be able to define other UDFs as needed.

5