Primary key constraints aren't detected on SQLite.

[jgr88]

While creating models with "inspectdb" i discovered, that there are currently some issues with SQLite3 Databases.

• PrimaryKeys ain't detected properly
• Datatype double ain't detected properly
• Datatype unsigned int ain't detected properly

Reproduce these issues by creating a SQLite3 Database:
CREATE TABLE "test" ( pId INTEGER NOT NULL, doubleField DOUBLE NOT NULL, uInt UNSIGNED INTEGER NOT NULL, PRIMARY KEY(pId) )

I added a pullrequest:
​https://github.com/django/django/pull/14293

[jgr88]

Replying to jgr88:

While creating models with "inspectdb" i discovered, that there are currently some issues with SQLite3 Databases.

• PrimaryKeys ain't detected properly
• Datatype double ain't detected properly
• Datatype unsigned int ain't detected properly

Reproduce these issues by creating a SQLite3 Database:
CREATE TABLE "test" ( pId INTEGER NOT NULL, doubleField DOUBLE NOT NULL, uInt UNSIGNED INTEGER NOT NULL, PRIMARY KEY(pId) )

I added a pullrequest:
​https://github.com/django/django/pull/14293

Update:
I added ​https://github.com/django/django/pull/14297 since i messed up the first one

[Carlton Gibson]

OK, this seems a reasonable proposal to me.

Generated model:

class Test(models.Model):
    pid = models.AutoField(db_column='pId')  # Field name made lowercase.
    doublefield = models.TextField(db_column='doubleField')  # Field name made lowercase. This field type is a guess.
    uint = models.TextField(db_column='uInt')  # Field name made lowercase. This field type is a guess.

    class Meta:
        managed = False
        db_table = 'test'

[Mariusz Felisiak]

I'm fine with fixing detection of primary key, but I think we should not add datatypes that are not used by Django itself. I also don't see a reason to add only DOUBLE.

SQLite uses complicated rules to determine a datatype affinity based on its name (see ​Determination Of Column Affinity which have a lot of caveats, e.g.

• CHARINT is mapped to INT,
• POINT is mapped to INT,
• STRING is mapped to NUMERIC.

I would prefer to warn users about datatypes that are not used by Django instead of mapping some/all of them.

[Anvesh Mishra]

​PR

[Anvesh Mishra]

So as per Charettes's comment on my PR ​https://github.com/django/django/pull/14878#discussion_r713268066, I implemented this issue with sqlparse but failed testcases one of them was inspectdb.tests.InspectDBTestCase.test_attribute_name_not_python_keyword. The change which I did was

method_match = sqlparse.parse(field_desc)[0]
if method_match:
    if len(method_match.tokens) == 3 and method_match.tokens[0].value == "PRIMARY":
        column_name_token = str(method_match.tokens[2])
        column_name = column_name_token[4:len(column_name_token) - 1]
        return column_name
    elif len(method_match.tokens) > 6 and method_match.tokens[6].value == "PRIMARY":
        return str(method_match.tokens[0])

[Simon Charette]

Looks like you forgot to strip leading and trailing quotes, Identifier.get_real_name() does that

django/db/backends/sqlite3/introspection.py

@@ -214 +214 @@
         if table_type == 'view':
             # Views don't have a primary key.
             return None
-        fields_sql = create_sql[create_sql.index('(') + 1:create_sql.rindex(')')]
-        for field_desc in fields_sql.split(','):
-            field_desc = field_desc.strip()
-            m = re.match(r'(?:(?:["`\[])(.*)(?:["`\]])|(\w+)).*PRIMARY KEY.*', field_desc)
-            if m:
-                return m[1] if m[1] else m[2]
+        latest_identifier = None
+        fields = sqlparse.parse(create_sql)[0].tokens[-1]
+        for index, token in enumerate(fields):
+            if isinstance(token, sqlparse.sql.Identifier):
+                latest_identifier = token
+                continue
+            elif (token.match(sqlparse.tokens.Keyword, 'PRIMARY') and
+                    fields.token_next(index)[1].match(sqlparse.tokens.Keyword, 'KEY')):
+                return latest_identifier.get_real_name()
         return None
 
     def _get_foreign_key_constraints(self, cursor, table_name):

[Mariusz Felisiak <felisiak.mariusz@…>]

In 69af4d09:

Fixed #32672 -- Fixed introspection of primary key constraints on SQLite.

Thanks Simon Charette for the implementation idea.