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: Discuss ticket 20264: URLValidator should allow underscores in local hostname

[Pavel Savchenko]

Hey folks,

Sorry for not providing a more specific scenario before, was short on time 
and just wanted to kick this off.

The most common scenario that I can think of (and the one that most similar 
to our usage) would be a *form field* on a Django site, that allows users 
to input a URL which is saved and later displayed *as a link to* other 
users (e.g in blogs, comments, CMS systems, etc).

Here's an example of a site, though clearly not a very reputable one: 
http://online_casino_news.hundredpercentgambling.com/ . Note that google 
groups automatically converted this one to a URL for me, and I was able to 
click and follow it both on Chrome and Firefox.

In the above use case, by validating the correctness of the URL, we protect 
a user from making a mistake, but we don't really care about adhering to 
standards beyond that, the usability wins.

There are other use cases, that might care about RFC 952 
/1034 
 guidelines about 
hostname. For example, if we're building a hosting or a name server 
management system, or maybe SSL certificates vendor.
In such cases, it might actually benefit the user if the platform alerts on 
the validity of the hostname chosen by the user (at the very least to 
advise the users).

However, I would guess that the first use case, of taking a URL to store 
and render it as a link, would be more common and thus more frequently 
needing to override the class.

I can also propose a solution that would still work for both: (deprecate 
and) rename the current class to StrictURLValidator (or 
URLValidatorRFC1034), to still be easily used for the less common scenarios.

What do you think?

Best Regards,
Pavel


On Tuesday, March 24, 2020 at 2:36:33 PM UTC+1, Adam Johnson wrote:
>
> Hi Pavel
>
> The ticket ( https://code.djangoproject.com/ticket/20264 ) doesn't 
> mention any specific use cases, and nor have you. What has this behaviour 
> blocked for you?
>
> Thanks,
>
> Adam
>
> On Tue, 24 Mar 2020 at 12:46, Pavel Savchenko  > wrote:
>
>> Hi Folks,
>>
>> I've just encountered this issue, and it seems Django's URLValidator 
>> regex for host is trying to abide to RFC 1034 recommendation 
>>  , when there are many 
>> sites in the wild that use underscore in their domain name.
>>
>> Can we please discuss this issue here, so we can eventually decide to 
>> reopen the ticket (or not) and perhaps allow for a pull-request to fix it?
>>
>> I found this stackoverflow question helpful, with many answers/comments 
>> with additional references: 
>> https://stackoverflow.com/questions/2180465/can-domain-name-subdomains-have-an-underscore-in-it
>>
>> Best regards,
>> Pavel
>>
>> -- 
>> 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/6982245f-2b5a-4a32-8fe5-a063c7459b7c%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/2506854e-9566-444a-8f83-e227215613ea%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: Discuss ticket 20264: URLValidator should allow underscores in local hostname

['1337 Shadow Hacker' via Django developers (Contributions to Django itself)]

> when there are many sites in the wild that use underscore in their domain 
> name.

Can you share some examples please ?

In general, we should abide by standards unless we have a really good reason.

In my experience I always had to replace underscores by dashes for a reason or 
another in hostnames that were setup by people who don't read RFCs anyway, so 
I'm not sure Django itself can make a big difference.

Nonetheless, can't you override the validation on your side ?

Best

-- 
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/4P0Vp6NoChXt46knjUSR74oJYbPbNbK2mEe7Z1fRy3aFONIVQ7Icz5Nujmy_EHuwUA7PjjvnUiaqMdb1ADWmPOAf2XfonKEE51DUpt4Oqcc%3D%40protonmail.com.

Re: Discuss ticket 20264: URLValidator should allow underscores in local hostname

[Adam Johnson]

Hi Pavel

The ticket ( https://code.djangoproject.com/ticket/20264 ) doesn't mention
any specific use cases, and nor have you. What has this behaviour blocked
for you?

Thanks,

Adam

On Tue, 24 Mar 2020 at 12:46, Pavel Savchenko  wrote:

> Hi Folks,
>
> I've just encountered this issue, and it seems Django's URLValidator regex
> for host is trying to abide to RFC 1034 recommendation
>  , when there are many
> sites in the wild that use underscore in their domain name.
>
> Can we please discuss this issue here, so we can eventually decide to
> reopen the ticket (or not) and perhaps allow for a pull-request to fix it?
>
> I found this stackoverflow question helpful, with many answers/comments
> with additional references:
> https://stackoverflow.com/questions/2180465/can-domain-name-subdomains-have-an-underscore-in-it
>
> Best regards,
> Pavel
>
> --
> 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/6982245f-2b5a-4a32-8fe5-a063c7459b7c%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/CAMyDDM3PgXz3g%3Dk8BbV%3DaXtgT41PZgv5zmPCYaPHFr2i2%2BQ9%3Dw%40mail.gmail.com.

Discuss ticket 20264: URLValidator should allow underscores in local hostname

[Pavel Savchenko]

Hi Folks,

I've just encountered this issue, and it seems Django's URLValidator regex 
for host is trying to abide to RFC 1034 recommendation 
 , when there are many 
sites in the wild that use underscore in their domain name.

Can we please discuss this issue here, so we can eventually decide to 
reopen the ticket (or not) and perhaps allow for a pull-request to fix it?

I found this stackoverflow question helpful, with many answers/comments 
with additional references: 
https://stackoverflow.com/questions/2180465/can-domain-name-subdomains-have-an-underscore-in-it

Best regards,
Pavel

-- 
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/6982245f-2b5a-4a32-8fe5-a063c7459b7c%40googlegroups.com.