Re: Proposal for better managed raw SQL migrations

[Petr Přikryl]

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

[schinckel]

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/2226e719-785b-481c-90f7-fde346399cd7%40googlegroups.com.

Re: Proposal for better managed raw SQL migrations

[Petr Přikryl]

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. 

I can show you some example from our code for one third party database:

sql_items = [
datetime_function_sql_item,
is_seo_valid_function_sql_item,
quality_view_sql_item,
article_category_view_sql_item,
base_article_view_sql_item,
article_view_sql_item,
article_b2b_view_sql_item,
master_article_view_sql_item,
reclamation_mat_view_sql_item,
purchase_mat_view_sql_item,
purchase_id_index_sql_item,
purchase_date_of_purchase_index_sql_item,
sale_mat_view_sql_item,
sale_id_index_sql_item,
sale_date_of_sale_index_sql_item,
incentive_sale_mat_view_sql_item,
incentive_item_mat_view_sql_item,
product_view_sql_item,
color_master_view_sql_item,
product_special_offers_view_sql_item,
series_view_sql_item,
stock_products_view_sql_item,
pl_master_product_view_sql_item,
pl_master_product_quality_view_sql_item,
pl_product_view_sql_item,
pl_product_variant_view_sql_item,
pl_stock_products_sold_view_sql_item,
]

#
#  module containing `datetime_function_sql_item` and 
`is_seo_valid_function_sql_item` follows

#

from migrate_sql.config import SQLItem

"""
This third party app stores datetime as DATE column and INT column with seconds 
passed that day.
This function converts these columns into PostgreSQL datetime type.
"""
datetime_function_sql_item = SQLItem(
name='datetime',
sql='''
CREATE OR REPLACE FUNCTION datetime(day date, seconds numeric) returns 
timestamp without time zone
LANGUAGE plpgsql AS $$
  BEGIN
RETURN day + (floor(seconds / 3600) || ':' || floor(seconds % 3600 
/ 60) || ':' || seconds % 60)::TIME;
  END;
$$;
''',
reverse_sql='DROP FUNCTION IF EXISTS datetime(day date, seconds numeric)',
replace=True,
)


is_seo_valid_function_sql_item = SQLItem(
name='is_seo_valid',
sql='''
CREATE OR REPLACE FUNCTION is_seo_valid(seo text) returns boolean
LANGUAGE plpgsql AS $$
  BEGIN
RETURN seo ~ '^[a-zA-Z0-9]+(-[a-zA-Z0-9]+)*$';
  END;
$$;
''',
reverse_sql='DROP FUNCTION IF EXISTS is_seo_valid(seo text)',
replace=True,
)


#
#  module containing `article_view_sql_item` follows

#

article_view_sql_item = SQLItem(
name=Article._meta.db_table,
sql='''
CREATE OR REPLACE VIEW {view_name} AS
SELECT
  sq.*,
  CASE
WHEN sq.buying_price <> 0 THEN round((sq.price / sq.buying_price - 
1) * 100, 2)
ELSE 0
  END   
  AS margin,
  GREATEST(
LEAST(
  NULLIF(sq.price_for_installment_calculation, 0),  -- zero turns 
off this price
  sq.eshop_price
),
0
  ) 
  AS installment_price
FROM (
  SELECT
DISTINCT ON (a.id)
a.id  AS id,
a.nameAS "name",
a.codeAS code,
COALESCE(a.master, FALSE) AS master,
a.created AS created,
a.fk_id_article_producer  AS fk_id_article_producer,
a.fk_id_master_productAS fk_id_master_product,
a.fk_id_color_rgb AS fk_id_color_rgb,
a.fk_id_vat   AS fk_id_vat,
a.cf_article_name AS cf_article_name,
a.cf_article_short_name   AS cf_article_short_name,
a.cf_seo_name AS cf_seo_name,
a.cf_article_type AS cf_article_type,
q.cf_article_quality  AS cf_article_quality,
COALESCE(a.active2, FALSE)  
  AS active2,
COALESCE(a.cf_www_visible, FALSE)   
  AS cf_www_visible,
COALESCE(a.cf_bo_for_sell, FALSE)   
  AS cf_bo_for_sell,
COALESCE(a.cf_buy_up_recommended, FALSE)
  AS cf_buy_up_recommended,
COALESCE(a.cf_clearance_sale, FALSE)
  AS cf_clearance_sale,
COALESCE(a.cf_article_short_name, a.name)   
  AS short_name,
COALESCE(a.cf_article_name, a.name) 
  AS long_name,
COALESCE(q.uniform_id, 1)   

Re: Proposal for better managed raw SQL migrations

[Adam Johnson]

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-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
> 
> .
>


-- 
Adam

-- 
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/CAMyDDM02yEMRP5tvwWEY9mwK4Xn6z%3Dr%3Dw05_D1RxWRKUQ4gjpA%40mail.gmail.com.

Proposal for better managed raw SQL migrations

[Petr Přikryl]

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

[Petr Přikryl]

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.