Proposal for Table Functions

[Petr Přikryl]

Hi, I have just implemented Table Function support on Django 2.1. What do you think about adopting it into Django itself? 

https://gist.github.com/petrprikryl/7cd765cd723c7df983de03706bf27d1a

It is all about passing function parameters into BaseTable and Join classes https://github.com/django/django/blob/master/django/db/models/sql/datastructures.py

Here are other thoughts which were inspiring me https://schinckel.net/2019/10/31/functions-as-tables-in-django-and-postgres/

Thanks,

Petr

[Ahmad A. Hussein]

I'm not an expert or even an amateur at the ORM, but here are my thoughts on the matter:

1. Does it carry over well across the four databases?

2. Is it necessary to include it as part of core versus a third-party app?

3. What specific use case will benefit from adopting this idea?

I ask the second question because it seems from your gist that you don't need your proposal to be part of core for it to work. It seems it would work completely well and flourish even as a third-party app given how you're subclassing the API and expanding on its usage. Contrast this with django-pytest for example that can have a harder problem running pytest on Django due to how the test runner is currently built (namely that setup assumes a subclass of the Django test runner).

Regards,

Ahmad

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/6d16fdfc-332f-4a72-83ae-04ee5c9fd28a%40googlegroups.com.

[Petr Přikryl]

ad 1. I was testing it on PostgreSQL only. But:

• Oracle is feasible through custom extendings in compiler https://livesql.oracle.com/apex/livesql/file/content_C87XCH8SE085LMS3C5KR03VFS.html
• MySQL is also feasible https://stackoverflow.com/a/23421816/1763888
• SQLite has some special limited table functions which should be syntax-compatible with Postgres https://www.sqlite.org/vtab.html#tabfunc2

ad 2. Yes, "it is necessary". Because I must do some nasty hacks in my gist. For example:

• SQL regex parsing https://gist.github.com/petrprikryl/7cd765cd723c7df983de03706bf27d1a#file-function-py-L189. Which could be removed if base method would know about parameters.
• Classic "Join" overriding https://gist.github.com/petrprikryl/7cd765cd723c7df983de03706bf27d1a#file-function-py-L277. I don't like it because I need work with "level" (order) of joins to map it to user lookups correctly. If ithis would be in core, then the lookups from "filter" could be merged witth lookups from "table_function" more easily. There is also possibility to merge logic from table_function into filter and use only filter method for passing parameters into table functions.

ad 3. The use case is optimization for me. Because I had SQL View with recursion. The View was mapped into Django through model. But operations with this view takes long time for my app. So I found out that I can optimize that using table function instead view with parameter limiting the recursion depth. But I couldn't switch view to table function because no ORM support. With this gist I could.

ad tests. I used them because I am used to use them :-). And I think that it should be re-writable to classic Django tests.

Petr

Dne neděle 17. května 2020 23:08:08 UTC+2 Ahmad A. Hussein napsal(a):

I'm not an expert or even an amateur at the ORM, but here are my thoughts on the matter:

1. Does it carry over well across the four databases?

2. Is it necessary to include it as part of core versus a third-party app?

3. What specific use case will benefit from adopting this idea?

I ask the second question because it seems from your gist that you don't need your proposal to be part of core for it to work. It seems it would work completely well and flourish even as a third-party app given how you're subclassing the API and expanding on its usage. Contrast this with django-pytest for example that can have a harder problem running pytest on Django due to how the test runner is currently built (namely that setup assumes a subclass of the Django test runner).

Regards,

Ahmad

On Sun, May 17, 2020 at 4:36 PM Petr Přikryl <nic...@gmail.com> wrote:

Hi, I have just implemented Table Function support on Django 2.1. What do you think about adopting it into Django itself? 

https://gist.github.com/petrprikryl/7cd765cd723c7df983de03706bf27d1a

It is all about passing function parameters into BaseTable and Join classes https://github.com/django/django/blob/master/django/db/models/sql/datastructures.py

Here are other thoughts which were inspiring me https://schinckel.net/2019/10/31/functions-as-tables-in-django-and-postgres/

Thanks,

Petr

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.

To unsubscribe from this group and stop receiving emails from it, send an email to django-d...@googlegroups.com.

[Ahmad A. Hussein]

Concerning tests, I did not mean to comment about the tests you wrote. I used the third party py-test django app as an example of an app that does need work in core to function better, not as a comment about your usage of tests. You're more than free to work the way you want to :) (I like pytest too)

If there's an optimization to be had in including table functions and it'll be backwards compatible without breaking anything, then I think there's merit in the idea. I suggest you open a ticket on the issue tracker and mention how significant the optimization was. It'll help with getting this new feature adopted.

You can open it here

[Petr Přikryl]

Linking the issue https://code.djangoproject.com/ticket/31609

Dne pondělí 18. května 2020 13:57:49 UTC+2 Ahmad A. Hussein napsal(a):