Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

How to implement connection.create_function() ? #10

Open
alanjds opened this issue Jan 17, 2017 · 14 comments
Open

How to implement connection.create_function() ? #10

alanjds opened this issue Jan 17, 2017 · 14 comments

Comments

@alanjds
Copy link
Contributor

alanjds commented Jan 17, 2017

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 (#9)

@otoolep
Copy link
Member

otoolep commented Jan 18, 2017

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

@8TAnyx
Copy link

8TAnyx commented Jan 3, 2018

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

@alanjds
Copy link
Contributor Author

alanjds commented Jan 3, 2018

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

@otoolep
Copy link
Member

otoolep commented Jan 3, 2018

What do you mean by "PL"?

@alanjds
Copy link
Contributor Author

alanjds commented Jan 3, 2018

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
Copy link
Contributor Author

alanjds commented Jan 3, 2018

@8TAnyx
Copy link

8TAnyx commented Jan 4, 2018

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
Copy link
Contributor Author

alanjds commented Jan 4, 2018

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
Copy link
Contributor Author

alanjds commented Jan 4, 2018

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
Copy link
Contributor Author

alanjds commented Jan 4, 2018

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
Copy link

8TAnyx commented Jan 4, 2018

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
Copy link
Contributor Author

alanjds commented Jan 4, 2018

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
Copy link
Contributor

sum12 commented Jul 13, 2018

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

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

@otoolep
Copy link
Member

otoolep commented Jul 20, 2018

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

4 participants