Opened 17 months ago
Last modified 17 months ago
#34671 closed Bug
Inspectdb collation issue with oracle views — at Initial Version
Reported by: | Philipp Maino | Owned by: | |
---|---|---|---|
Component: | Core (Management commands) | Version: | 4.2 |
Severity: | Normal | Keywords: | oracle, inspectdb, collation |
Cc: | Triage Stage: | Accepted | |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
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'.
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'