Python/adbc/sqlite User-defined function support
- Dominant language
- C#
- Stars
- 627
- Forks
- 217
- Avg merge
- 17h
- Merged PRs (30d)
- 57
Description
### What feature or improvement would you like to see?
Within sqlite, according to the documentation, user defined functions (UDFs) are connection-specific:
> If an application uses more than one database connection then application-defined SQL functions must be added to each database connection separately.
[source](https://www.sqlite.org/c3ref/create_function.html)
As such, for any system using adbc's sqlite driver, if a UDF is to be used, currently there's no method, as far as I can see, of being able to register a UDF. To do so, the underlying sqlite3 class structure would need to be accessed, which doesn't seem to exist at current.
To showcase this, you can look to the example code below where you can see a failure. To explain the code beforehand, a Database is created in memory, and a connection to that database is shared between a sqlite3 connection, and an adbc-sqlite connection. a table is created, and a trigger is created for this table, where upon insertion of data onto the table, a UDF called world will be used to add a "world" string to the world column.
This UDF will be only registered on the sqlite3 connection, and the insert will be done by a adbc_ingest() function call. This will lead to a failure, as the adbc connection does not have that function.
```
import adbc_driver_sqlite.dbapi as adbc
import sqlite3
import pyarrow as pa
#creating in memory connection string
db="file::memory:?cache=shared"
#making connections/cursor
sqlite_conn=sqlite3.connect(database=db,autocommit=True)
arrow_conn=adbc.connect(db,autocommit=True)
arrow_cursor=arrow_conn.cursor()
#making pyarrow table to ingest
schema=pa.schema([("hello",pa.string())])
table=pa.table(["hello"],schema=schema)
#setup for the UDF, note only sqlite3 connection gets the UDF
def world():
return "world"
sqlite_conn.create_function('world',1,world)
#generating database table
sqlite_conn.execute("""CREATE TABLE hello_world(
hello TEXT,
world TEXT
);""")
#making the trigger
sqlite_conn.execute("""
CREATE TRIGGER table_update
AFTER INSERT ON hello_world
BEGIN
UPDATE hello_world
SET world = world();
END;
""")
#ingesting the data, error will happen here
arrow_cursor.adbc_ingest('hello_world',table,'append')
```
Contributor guide
Assessment
This issue has not been assessed yet.