Re: Proposal for Table Functions
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): > > 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 <https://code.djangoproject.com/newticket> > > > > On Monday, May 18, 2020 at 8:31:04 AM UTC+2, Petr Přikryl wrote: >> >> 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 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/
Re: Proposal for Table Functions
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 > 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 . >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/django-developers/6d16fdfc-332f-4a72-83ae-04ee5c9fd28a%40googlegroups.com >> >> <https://groups.google.com/d/msgid/django-developers/6d16fdfc-332f-4a72-83ae-04ee5c9fd28a%40googlegroups.com?utm_medium=email_source=footer> >> . >> > -- 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-developers+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/ae02ee22-d858-4dd2-ae56-b219e53305d1%40googlegroups.com.
Proposal for Table Functions
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-developers+unsubscr...@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.
Re: Proposal for better managed raw SQL migrations
Hi Matt, It looks nice and the idea is very similar to https://github.com/festicket/django-migrate-sql. Problems you are noticing are same as my. Easy tracking Git changes, code reviews, one place where the final definitions live (like models). It would be nice if Django can do that. Petr Dne středa 25. března 2020 4:49:42 UTC+1 schinckel napsal(a): > > > > On Wednesday, March 25, 2020 at 1:45:48 AM UTC+10:30, Petr Přikryl wrote: >> >> Hi Adam, >> thank you for your reply. >> >> We usually have few indices, functions and triggers. But the most used >> database object is view. We used them for data synchronizing from some >> third party databases. These databases have complex schema which we want >> simplify. So we are building low-level DB API via views. Then we create >> Django models for these views. Then is easy to use ORM for data access or >> sync operations. >> >> > Hi Petr, > > I too have a bunch of database Raw SQL. I came up with a mechanism for > doing this that allows for/generates numbered versions of each file. > > https://schinckel.net/2017/06/07/versioning-complex-database-migrations/ > > There's no way to hook in to the migrations framework to get this to > happen during `makemigrations`, but I have used the checks framework to > examine the project for any files that appear to be out of date, and moan > about those, as well as a custom management command that generates the > migrations for any that have changed, as well as the versions. > > There's also a command that copies the current version of the file to the > "newest" migration which is useful for development. > > I'll try to publish the actual code that does it soon. > > Matt. > -- 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-developers+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/f0bbb2e6-123e-48df-bdc8-bc6511aac641%40googlegroups.com.
Re: Proposal for better managed raw SQL migrations
FROM now()) * 1000 AND
pd_installment.valid_to > extract(EPOCH FROM now()) * 1000
LEFT JOIN {quality_view} q_vat_0
ON q_vat_0.uniform_id = q.uniform_id AND NOT
q_vat_0.is_vat_version AND q.is_vat_version
LEFT JOIN article a_vat_0 -- #26502
ON a_vat_0.id <> a.id
AND a_vat_0.fk_id_vat = 'some-id' -- zero VAT
AND a.fk_id_vat = 'some-id' -- 21 VAT
AND a.fk_id_master_product = a_vat_0.fk_id_master_product
AND a.fk_id_color_rgb = a_vat_0.fk_id_color_rgb
AND q_vat_0.cf_article_quality =
COALESCE(a_vat_0.cf_article_quality, '{new_quality_code}')
WHERE
a.fk_id_vat IS NOT NULL
AND a.fk_id_article_producer IS NOT NULL
AND (
a.is_spare_part -- spare part exception #27658
OR a.master -- masters exception (do not have cf_bo_for_sell,
cf_www_visible and active2 filled)
OR (
NOT a.is_spare_part -- article is not spare part (must have
active2)
AND a.active2 IS TRUE
)
)
ORDER BY
a.id,
q.cf_article_quality,
a_vat_0.id
) sq
'''.format(
view_name=Article._meta.db_table,
base_article_view=base_article_view_sql_item.name,
quality_view=quality_view_sql_item.name,
new_quality_code=NEW_QUALITY_CODE,
),
reverse_sql='DROP VIEW IF EXISTS
{view_name}'.format(view_name=Article._meta.db_table),
replace=True,
dependencies=(
('my_django_app', quality_view_sql_item.name),
('my_django_app', base_article_view_sql_item.name),
),
)
You can see that SQL can be very complex. And if it is changing very often
than is easier to manage it in one place with definition instead of
volatile migrations. In your case you must go through all your migrations
to find the final definition of some object. And with so many objects it
could be complex. Next if we remove (clean) migrations in our project than
we don't have easy way how to re-generate fake-able intial migrations from
our code base. I know that cleaning migrations is a edge case but I think
it would be nice if you could re-generate the whole schema also with low
level SQL objects into migrations.
Next I think that some simple views could be generated from Django ORM
instead of writing full SQL. But I am afraid of that ORM can't cover all
situations for example sophisticated SQL functions. Because the database
differences are too big. So from my perspective it is fine to have high
abstraction ORM classes (Model, Index) but it would be even better to have
low abstraction classes for functions, triggers, views and indices. Than
you could easily manage the whole application database layer using same
programming API in one place with final Python/SQL definition.
SQLItem objects could have definitions for multiple backends. So the
re-usable Django apps could prepare custom SQLItems for all supported
vendor backends.
Thanks,
Petr
Dne středa 11. března 2020 11:20:56 UTC+1 Adam Johnson napsal(a):
>
> Hi Petr
>
> I too often end up managing some database objects like triggers, normally
> in order to support database migrations. I have always been happy using
> RawSQL migration operations though.
>
> What types of database objects are you mostly using? And how?
>
> I think django-migrate-sql is a neat idea, but since it requires full
> authorship of the SQL it doesn't provide the most ORM-like experience. It's
> also not backend agnostic, which is something preferable for anything we
> add to Django. I'd be more interested in implementing classes that
> represent the underlying object type, like Models represent tables, and
> translating changes to them into migration operations.
>
> Thanks,
>
> Adam
>
> On Wed, 11 Mar 2020 at 09:04, Petr Přikryl >
> wrote:
>
>> In our apps we have a lot of database objects which are hard to manage in
>> classic Django migrations. Next, we clean our migrations time to time to
>> speed up deployment process. And it would be awesome if Django would
>> have system for raw SQL "models" and handle migrations and dependencies
>> automatically in makemigrations and migrate commands like
>> django-migrate-sql-deux https://pypi.org/project/django-migrate-sql-deux/.
>> What do you think?
>>
>> --
>> 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 .
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/django
Proposal for better managed raw SQL migrations
I have only proposal for making Django migrations better handling raw SQL migrations. I like the way how django-migrate-sql do that https://github.com/festicket/django-migrate-sql. And it would be interested if Django has this feature build-in. We use a lot of raw SQL objects in our apps and time to time we are cleaning migrations so storing to SQL itself in migrations isn't right way for us. I like the idea, looking on SQL objects like on models and generate migrations for them automatically based on their definition changes. What do you think? -- 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-developers+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/1382459f-7d54-479c-9e38-0e4a434ad379%40googlegroups.com.
Proposal for better managed raw SQL migrations
In our apps we have a lot of database objects which are hard to manage in classic Django migrations. Next, we clean our migrations time to time to speed up deployment process. And it would be awesome if Django would have system for raw SQL "models" and handle migrations and dependencies automatically in makemigrations and migrate commands like django-migrate-sql-deux https://pypi.org/project/django-migrate-sql-deux/. What do you think? -- 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-developers+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/c078a0ae-9643-4d5c-b1de-f82cec2a7f33%40googlegroups.com.
