Code

Opened 2 years ago

Last modified 18 months ago

#17202 new Bug

Inspectdb produces incorrect max_length for NVARCHAR2 columns with Oracle

Reported by: rodolfo.3+django@… Owned by: nobody
Component: Database layer (models, ORM) Version: 1.3
Severity: Normal Keywords: inspectdb oracle max_length
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

I created a table in a Oracle database with a "NVARCHAR2" field with size=150:

CREATE table "TEST" (
    "CHAR_150"   NVARCHAR2(150)
)

Then, when I run "manage.py inspectdb", the generated code is:

class Test(models.Model):
    char_150 = models.CharField(max_length=300, blank=True)
    class Meta:
        db_table = u'test'

A field type "NVARCHAR2(150)" generate a "max_length=300" CharField.

Attachments (0)

Change History (2)

comment:1 Changed 2 years ago by ikelly

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Summary changed from Double max_length using inspectdb with Oracle to Inspectdb produces incorrect max_length for NVARCHAR2 columns with Oracle
  • Triage Stage changed from Unreviewed to Accepted

The problem is that the cursor description only includes the size of the column in bytes, not in characters. For a 16-bit encoding, each character occupies two bytes. For another encoding, the ratio may be different. The other problem is that we can't distinguish between VARCHAR2 and NVARCHAR2 using the cursor description, which would make it impossible to correct for the problem by determining the appropriate factor and dividing.

To fix this, we'll need to generalize the column introspection code into the backends, and rewrite it for the oracle backend, to have it inspect columns by querying the all_tab_cols view instead of just relying on a cursor description.

comment:2 Changed 18 months ago by mark0978

A similar problem happens with mysql 5.5.28CE on linux where the size is 3x greater than that specified, given the SQL:

CREATE TABLE primaryfields (

fpost int(11) NOT NULL,
index1911 char(36) DEFAULT NULL,
ssn char(11) DEFAULT NULL,
middleinitial char(1) DEFAULT NULL,
firstname char(30) DEFAULT NULL,
lastname char(30) DEFAULT NULL,
employeeid char(10) DEFAULT NULL,
PRIMARY KEY (fpost),
CONSTRAINT primaryfields_ibfk_1 FOREIGN KEY (fpost) REFERENCES filenames (fpost)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

You get the Python:

class Primaryfields(models.Model):

fpost = models.ForeignKey(Filenames, primary_key=True, db_column='fpost')
index1911 = models.CharField(max_length=108, blank=True)
ssn = models.CharField(max_length=33, blank=True)
middleinitial = models.CharField(max_length=3, blank=True)
firstname = models.CharField(max_length=90, blank=True)
lastname = models.CharField(max_length=90, blank=True)
employeeid = models.CharField(max_length=30, blank=True)
class Meta:

db_table = u'primaryfields'

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as new
The owner will be changed from nobody to anonymous. Next status will be 'assigned'
as The resolution will be set. Next status will be 'closed'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.