InariSystems opened a new issue, #2671: URL: https://github.com/apache/arrow-adbc/issues/2671
### 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') ``` -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: issues-unsubscr...@arrow.apache.org.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
