#34671: Inspectdb collation issue with oracle views
-------------------------------------+-------------------------------------
Reporter: Philipp Maino | Owner: (none)
Type: Bug | Status: new
Component: Error reporting | Version: 4.2
Severity: Normal | Resolution:
Keywords: oracle, inspectdb, | Triage Stage:
collation | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Philipp Maino:
Old description:
> Given a simple oracle table with a primary id column and a VARCHAR column
> as well as an oracle view that simply selects all column from that table
> without any manipulation.
>
> When using inspectdb to introspect the **table** we get a
> {{{models.CharField}}} for the VARCHAR column **without** a
> {{{db_collation}}} argument.
> When using inspectdb to introspect the **view** we get a
> {{{models.CharField}}} for the VARCHAR column **with** a
> {{{db_collation}}} argument.
>
> I believe this to be a bug that's being caused by
> {{{DatabaseIntrospection.get_table_description}}} in
> ''django/db/backends/oracle/introspection.py''.
> This will cause an issue when {{{DiscoverRunner.run_checks}}} triggers
> {{{CharField._check_db_collation()}}} in
> ''django/db/models/fields/__init__.py'' as this section will return an
> error if {{{db_collation is not None}}}.
>
> **Recreate Issue**
> {{{
> CREATE TABLE COLLATION_TEST
> (
> ID NUMBER,
> TEST VARCHAR2(10),
> TEST2 NVARCHAR2(10),
> TEST3 VARCHAR(10)
> );
> CREATE VIEW COLLATION_TEST_VIEW AS
> SELECT *
> FROM COLLATION_TEST;
> SELECT *
> FROM COLLATION_TEST_VW;
> }}}
>
> Run {{{manage.py inspectdb COLLATION_TEST}}}:
> {{{
> class CollationTest(models.Model):
> id = models.FloatField(blank=True, null=True)
> test = models.CharField(max_length=10, blank=True, null=True)
> test2 = models.CharField(max_length=10, blank=True, null=True)
> test3 = models.CharField(max_length=10, blank=True, null=True)
>
> class Meta:
> managed = False
> db_table = 'collation_test'
> }}}
>
> Run {{{manage.py inspectdb COLLATION_TEST_VIEW}}}.
>
> {{{
> class CollationTestView(models.Model):
> id = models.FloatField(blank=True, null=True)
> test = models.CharField(max_length=10, db_collation='USING_NLS_COMP',
> blank=True, null=True)
> test2 = models.CharField(max_length=10,
> db_collation='USING_NLS_COMP', blank=True, null=True)
> test3 = models.CharField(max_length=10,
> db_collation='USING_NLS_COMP', blank=True, null=True)
>
> class Meta:
> managed = False
> db_table = 'collation_test_view'
> }}}
>
> If you consider this as a bug as well then this could be fixed for
> example by adjusting {{{get_table_description}}}.
> You could check first whether {{{table_name}}} is a table or a view by
> querying {{{user_object}}} as column ''OBJECT_TYPE'' identifies an obhect
> as ''TABLE' or "VIEW'.
> [[Image(collation.png)]]
>
> If it's a view you have to adjust the introspection query to use
> {{{user_views}}} instead of {{{user_tables}}}. This will yield the
> correct CharField definition without db_collation.
> {{{
> SELECT user_tab_cols.column_name,
> user_tab_cols.data_default,
> CASE
> WHEN user_tab_cols.collation = user_views.default_collation
> THEN NULL
> ELSE user_tab_cols.collation
> END collation,
> CASE
> WHEN user_tab_cols.char_used IS NULL
> THEN user_tab_cols.data_length
> ELSE user_tab_cols.char_length
> END as display_size,
> CASE
> WHEN user_tab_cols.identity_column = 'YES' THEN 1
> ELSE 0
> END as is_autofield,
> CASE
> WHEN EXISTS (SELECT 1
> FROM user_json_columns
> WHERE user_json_columns.table_name =
> user_tab_cols.table_name
> AND user_json_columns.column_name =
> user_tab_cols.column_name)
> THEN 1
> ELSE 0
> END as is_json,
> user_col_comments.comments as col_comment
> FROM user_tab_cols
> LEFT OUTER JOIN
> user_views ON user_views.view_name = user_tab_cols.table_name
> LEFT OUTER JOIN
> user_col_comments ON
> user_col_comments.column_name =
> user_tab_cols.column_name AND
> user_col_comments.table_name = user_tab_cols.table_name
> WHERE user_tab_cols.table_name = 'COLLATION_TEST_VIEW'
> }}}
New description:
Given a simple oracle table with a primary id column and a VARCHAR column
as well as an oracle view that simply selects all column from that table
without any manipulation.
When using inspectdb to introspect the **table** we get a
{{{models.CharField}}} for the VARCHAR column **without** a
{{{db_collation}}} argument.
When using inspectdb to introspect the **view** we get a
{{{models.CharField}}} for the VARCHAR column **with** a
{{{db_collation}}} argument.
I believe this to be a bug that's being caused by
{{{DatabaseIntrospection.get_table_description}}} in
''django/db/backends/oracle/introspection.py''.
This will cause an issue when {{{DiscoverRunner.run_checks}}} triggers
{{{CharField._check_db_collation()}}} in
''django/db/models/fields/__init__.py'' as this section will return an
error if {{{db_collation is not None}}}.
**Recreate Issue**
{{{
CREATE TABLE COLLATION_TEST
(
ID NUMBER,
TEST VARCHAR2(10),
TEST2 NVARCHAR2(10),
TEST3 VARCHAR(10)
);
CREATE VIEW COLLATION_TEST_VIEW AS
SELECT *
FROM COLLATION_TEST;
SELECT *
FROM COLLATION_TEST_VW;
}}}
Run {{{manage.py inspectdb COLLATION_TEST}}}:
{{{
class CollationTest(models.Model):
id = models.FloatField(blank=True, null=True)
test = models.CharField(max_length=10, blank=True, null=True)
test2 = models.CharField(max_length=10, blank=True, null=True)
test3 = models.CharField(max_length=10, blank=True, null=True)
class Meta:
managed = False
db_table = 'collation_test'
}}}
Run {{{manage.py inspectdb COLLATION_TEST_VIEW}}}.
{{{
class CollationTestView(models.Model):
id = models.FloatField(blank=True, null=True)
test = models.CharField(max_length=10, db_collation='USING_NLS_COMP',
blank=True, null=True)
test2 = models.CharField(max_length=10, db_collation='USING_NLS_COMP',
blank=True, null=True)
test3 = models.CharField(max_length=10, db_collation='USING_NLS_COMP',
blank=True, null=True)
class Meta:
managed = False
db_table = 'collation_test_view'
}}}
If you consider this as a bug as well then this could be fixed for example
by adjusting {{{get_table_description}}}.
You could check first whether {{{table_name}}} is a table or a view by
querying {{{user_object}}} as column ''OBJECT_TYPE'' identifies an obhect
as ''TABLE' or "VIEW'.
[[Image(collation.png)]]
If it's a view you have to adjust the introspection query in
{{{get_table_description}}} to use {{{user_views}}} instead of
{{{user_tables}}}. This will yield the correct CharField definition
without db_collation.
{{{
SELECT user_tab_cols.column_name,
user_tab_cols.data_default,
CASE
WHEN user_tab_cols.collation = user_views.default_collation
THEN NULL
ELSE user_tab_cols.collation
END collation,
CASE
WHEN user_tab_cols.char_used IS NULL
THEN user_tab_cols.data_length
ELSE user_tab_cols.char_length
END as display_size,
CASE
WHEN user_tab_cols.identity_column = 'YES' THEN 1
ELSE 0
END as is_autofield,
CASE
WHEN EXISTS (SELECT 1
FROM user_json_columns
WHERE user_json_columns.table_name =
user_tab_cols.table_name
AND user_json_columns.column_name =
user_tab_cols.column_name)
THEN 1
ELSE 0
END as is_json,
user_col_comments.comments as col_comment
FROM user_tab_cols
LEFT OUTER JOIN
user_views ON user_views.view_name = user_tab_cols.table_name
LEFT OUTER JOIN
user_col_comments ON
user_col_comments.column_name = user_tab_cols.column_name
AND
user_col_comments.table_name = user_tab_cols.table_name
WHERE user_tab_cols.table_name = 'COLLATION_TEST_VIEW'
}}}
--
--
Ticket URL: <https://code.djangoproject.com/ticket/34671#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/01070188e258d283-76735be1-52e6-4d35-9666-8d1295135ae4-000000%40eu-central-1.amazonses.com.
