[Django] #32672: Primary Key, type double and type unsigned integer ain't detected correctly for Sqlite3 Database

Django

unread,

Apr 21, 2021, 7:15:18 AM4/21/21

to django-...@googlegroups.com

#32672: Primary Key, type double and type unsigned integer ain't detected correctly
for Sqlite3 Database
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
juliangrube1988 |
Type: Bug | Status: new
Component: Database | Version: 3.2
layer (models, ORM) | Keywords: SQLite3 PrimaryKey
Severity: Normal | Datatypes
Triage Stage: | Has patch: 1
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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`) )

--
Ticket URL: <https://code.djangoproject.com/ticket/32672>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Apr 21, 2021, 7:58:03 AM4/21/21

to django-...@googlegroups.com

#32672: Primary Key, type double and type unsigned integer ain't detected correctly
for Sqlite3 Database
-------------------------------------+-------------------------------------

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by juliangrube1988):

* easy: 0 => 1

Old description:

> 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`) )

New description:

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

--

--
Ticket URL: <https://code.djangoproject.com/ticket/32672#comment:1>

Django

unread,

Apr 21, 2021, 11:27:41 AM4/21/21

to django-...@googlegroups.com

#32672: Primary Key, type double and type unsigned integer ain't detected correctly
for Sqlite3 Database
-------------------------------------+-------------------------------------

Reporter: jgr88 | Owner: jgr88
Type: Bug | Status: assigned

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Girish Sontakke):

* owner: nobody => jgr88
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/32672#comment:2>

Django

unread,

Apr 21, 2021, 2:15:43 PM4/21/21

to django-...@googlegroups.com

#32672: Primary Key, type double and type unsigned integer ain't detected correctly
for Sqlite3 Database
-------------------------------------+-------------------------------------

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by jgr88):

Replying to [ticket:32672 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

--
Ticket URL: <https://code.djangoproject.com/ticket/32672#comment:3>

Django

unread,

Apr 28, 2021, 3:02:54 AM4/28/21

to django-...@googlegroups.com

#32672: Primary Key, type double and type unsigned integer ain't detected correctly
for Sqlite3 Database
-------------------------------------+-------------------------------------

Keywords: SQLite3 PrimaryKey | Triage Stage: Accepted
Datatypes |
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Carlton Gibson):

* stage: Unreviewed => Accepted

Comment:

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'
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/32672#comment:4>

Django

unread,

Apr 29, 2021, 6:24:13 AM4/29/21

to django-...@googlegroups.com

#32672: Primary Key, type double and type unsigned integer ain't detected correctly
for Sqlite3 Database
-------------------------------------+-------------------------------------

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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 [https://www.sqlite.org/datatype3.html#affname
"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.

--
Ticket URL: <https://code.djangoproject.com/ticket/32672#comment:5>

Django

unread,

Apr 29, 2021, 6:26:06 AM4/29/21

to django-...@googlegroups.com

#32672: Primary Key, type double and type unsigned integer ain't detected correctly
for Sqlite3 Database
-------------------------------------+-------------------------------------

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* needs_better_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/32672#comment:6>

Django

unread,

Apr 29, 2021, 6:26:21 AM4/29/21

to django-...@googlegroups.com

#32672: Primary Key, type double and type unsigned integer ain't detected correctly
for Sqlite3 Database
-------------------------------------+-------------------------------------

Reporter: jgr88 | Owner: jgr88
Type: | Status: assigned
Cleanup/optimization |

* type: Bug => Cleanup/optimization

--
Ticket URL: <https://code.djangoproject.com/ticket/32672#comment:7>

Django

unread,

Sep 6, 2021, 1:48:18 PM9/6/21

to django-...@googlegroups.com

#32672: Primary Key, type double and type unsigned integer ain't detected correctly
for Sqlite3 Database
-------------------------------------+-------------------------------------

Reporter: jgr88 | Owner: Anvesh
Type: | Mishra
Cleanup/optimization | Status: assigned

Changes (by Anvesh Mishra):

* owner: jgr88 => Anvesh Mishra

--
Ticket URL: <https://code.djangoproject.com/ticket/32672#comment:8>

Django

unread,

Oct 3, 2021, 2:19:59 PM10/3/21

to django-...@googlegroups.com

#32672: Primary Key, type double and type unsigned integer ain't detected correctly
for Sqlite3 Database
-------------------------------------+-------------------------------------

Comment (by 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`. 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])
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/32672#comment:9>

Django

unread,

Oct 7, 2021, 5:31:27 PM10/7/21

to django-...@googlegroups.com

#32672: Primary Key, type double and type unsigned integer ain't detected correctly
for Sqlite3 Database
-------------------------------------+-------------------------------------

Comment (by Simon Charette):

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

{{{#!diff
diff --git a/django/db/backends/sqlite3/introspection.py
b/django/db/backends/sqlite3/introspection.py
index f109b5d5ee..983c000e8b 100644
--- a/django/db/backends/sqlite3/introspection.py
+++ b/django/db/backends/sqlite3/introspection.py
@@ -214,12 +214,15 @@ def get_primary_key_column(self, cursor,
table_name):
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):
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/32672#comment:10>

Django

unread,

Oct 25, 2021, 1:29:17 PM10/25/21

to django-...@googlegroups.com

#32672: Primary Key, type double and type unsigned integer ain't detected correctly
for Sqlite3 Database
-------------------------------------+-------------------------------------

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* needs_better_patch: 1 => 0

--
Ticket URL: <https://code.djangoproject.com/ticket/32672#comment:11>

Django

unread,

Oct 26, 2021, 3:51:38 AM10/26/21

to django-...@googlegroups.com

#32672: Primary key constraints aren't detected on SQLite.

Keywords: SQLite3 PrimaryKey | Triage Stage: Ready for
Datatypes | checkin
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/32672#comment:12>

Django

unread,

Oct 26, 2021, 6:19:37 AM10/26/21

to django-...@googlegroups.com

#32672: Primary key constraints aren't detected on SQLite.
-------------------------------------+-------------------------------------
Reporter: jgr88 | Owner: Anvesh
Type: | Mishra

Cleanup/optimization | Status: closed

Component: Database layer | Version: 3.2
(models, ORM) |

Severity: Normal | Resolution: fixed

Keywords: SQLite3 PrimaryKey | Triage Stage: Ready for
Datatypes | checkin

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 1 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak <felisiak.mariusz@…>):

* status: assigned => closed
* resolution: => fixed

Comment:

In [changeset:"69af4d09bae7782f733e01b19cf69fa6f36d7165" 69af4d09]:
{{{
#!CommitTicketReference repository=""
revision="69af4d09bae7782f733e01b19cf69fa6f36d7165"
Fixed #32672 -- Fixed introspection of primary key constraints on SQLite.