How to implement connection.create_function() ?

[alanjds]

After implementing most of Python's sqlite3 type extensions, I tried to adapt a Django DB Backend for RQLite based on the SQLite one. But Django' stock SQLite backend do use the non-standard Connection.create_function() to add REGEX and some more stuff into SQLite, that have no serverside PL features AFAIK.

As this works by connecting a C callable to a SQLite custom function, and being C callables not possible to be transferred to the RQLite server, I am now clueless on how to implement/emulate this feature.

I am halting the development of this Django backend. This issue is just a tip/heads-up for someone trying to do the same in the future. If this could be solved, seems pretty possible to have a Django backend over a high-available relational database based on the SQLite one.

Thanks @otoolep for the help during the implementation of the types extensions (https://github.com/rqlite/pyrqlite/pull/9)

[otoolep]

Thank you @alanjds -- I'll leave this ticket open.

[8TAnyx]

@alanjds I am currently trying to achieve the same thing with django, has there been made any progress on this issue?

[alanjds]

@8TAnyx Unfortunately, no. Without a way to have serverside PL features, this seems as a dead-end.

[otoolep]

What do you mean by "PL"?

[alanjds]

Programming Language. As PL/SQL, PLpg/SQL, Python/SQL.

SQLite have not this stuff because it runs on the user machine. Then it allows you to assign a C function via sqlite3_create_function. Python binding to SQLite exposes this feature via Connection.create_function()

What Django does is using this facility to teach the SQLite how to do a regex "serverside". Creates the regex() function for SQLite. This and other functions. Then Django can send SQL queries using the regex() function inside it.

[alanjds]

See:

• https://sqlite.org/c3ref/create_function.html
• https://stackoverflow.com/a/8283265/798575

[8TAnyx]

It was my first thought too, to reuse django's sqlite backend but since it seems impossible my second thought was that it might be simpler to write an interface which translates the ORM to a json object and sends it per http request to rqlited? How I anyhow imagined the rqlite backend should work would be that it calls a shell command and executes the params in rqlite, or do I have the same problem then again?

For the project I am working on now, I will simply use SQL Queries since that worked well for me, for the future though it would be really nice to have such a backend!

[alanjds]

You will get the same problem, if using the Django ORM. IIUC, the ORM expects some features to be available on the DB, and the driver ensures that the needed features are there for when the queries came.

Some features are optional, as transactions, savepoints, triggers, etc. But a DB-side programming language to input some stored procedures is a nonoptional feature, for the point I had read Django code up to now.

[alanjds]

Maybe embeding some scripting language translatable to C on rqlite server, to be used on sqlite3_create_function there... I do not know really...

[alanjds]

All this should be available for Django ORM to work:

        conn.create_function("django_date_extract", 2, _sqlite_date_extract)
        conn.create_function("django_date_trunc", 2, _sqlite_date_trunc)
        conn.create_function("django_datetime_cast_date", 2, _sqlite_datetime_cast_date)
        conn.create_function("django_datetime_cast_time", 2, _sqlite_datetime_cast_time)
        conn.create_function("django_datetime_extract", 3, _sqlite_datetime_extract)
        conn.create_function("django_datetime_trunc", 3, _sqlite_datetime_trunc)
        conn.create_function("django_time_extract", 2, _sqlite_time_extract)
        conn.create_function("django_time_trunc", 2, _sqlite_time_trunc)
        conn.create_function("django_time_diff", 2, _sqlite_time_diff)
        conn.create_function("django_timestamp_diff", 2, _sqlite_timestamp_diff)
        conn.create_function("regexp", 2, _sqlite_regexp)
        conn.create_function("django_format_dtdelta", 3, _sqlite_format_dtdelta)
        conn.create_function("django_power", 2, _sqlite_power)
        conn.execute('PRAGMA foreign_keys = ON')

[8TAnyx]

Well that is a pity. Maybe some Django persons will give us a pony =) but probably not....

Thanks for the fast answers to both of you!

[alanjds]

I would not expect they (Django devs) to solve this. In fact the problem is on us. They already provided a clean API and needs. How to implement is a DB problem ;)

You are welcome.

[sum12]

Hey, I was able to get this to work. But there are bunch of fixes/changes I had to make.

• pyrqlite https://github.com/rqlite/pyrqlite/pull/28
• rqlite https://github.com/rqlite/rqlite/pull/523
• go-sqlite3 https://github.com/mattn/go-sqlite3/pull/604

And here is the backend: https://github.com/sum12/rqlite-django

[otoolep]

Thanks @sum12 -- I'd like to hear from @zmedico before we merge your changes.