obsolete quoting fix in sqlite

[Mike Bayer]

referring here to a941dff48f04b9dc0a81ce2673c67b2ba24971ac. This behavior was fixed in sqlite per this changeset: http://www.sqlite.org/src/info/600482d161, which would have been released as of sqlite 3.6.14 (see http://www.sqlite.org/changes.html).

testing on sqlite 2.0 with that schema, only foreign_key_list has an issue, and its only with the tablename:

sqlite> CREATE TABLE "django_content_type" (
   ...>             "id" integer NOT NULL PRIMARY KEY,
   ...>             "django_stuff" text NULL
   ...> )
   ...> ;
sqlite>         CREATE TABLE "django_admin_log" (
   ...>             "id" integer NOT NULL PRIMARY KEY,
   ...>             "action_time" datetime NOT NULL,
   ...>             "content_type_id" integer NULL REFERENCES "django_content_type" ("id"),
   ...>             "object_id" text NULL,
   ...>             "change_message" text NOT NULL
   ...> );
sqlite> pragma table_info("django_content_type");
0|id|integer|99||1
1|django_stuff|text|0||0
sqlite> pragma table_info(django_content_type);
0|id|integer|99||1
1|django_stuff|text|0||0
sqlite> pragma foreign_key_list(django_admin_log);
0|0|"django_content_type"|content_type_id|id

on modern sqlite, there's no issue:

sqlite> CREATE TABLE "django_content_type" (
   ...>             "id" integer NOT NULL PRIMARY KEY,
   ...>             "django_stuff" text NULL
   ...> )
   ...> ;
sqlite>         CREATE TABLE "django_admin_log" (
   ...>             "id" integer NOT NULL PRIMARY KEY,
   ...>             "action_time" datetime NOT NULL,
   ...>             "content_type_id" integer NULL REFERENCES "django_content_type" ("id"),
   ...>             "object_id" text NULL,
   ...>             "change_message" text NOT NULL
   ...> );
sqlite> pragma foreign_key_list(django_admin_log);
0|0|django_content_type|content_type_id|id|NO ACTION|NO ACTION|NONE

the regexp makes it impossible to support a col name that actually has a quote in it.

[Mike Bayer]

9e3458c4793e863737df371774b39c4c05858abd da626917a8e9d5514717de07893c713c0de85b7d

[Former user]

Beside quotes, it happens with older sqlite that table names are returned enclosed in square brackets.

sqlalchemy 0.7.9, sqlite3 2.4.1, sqlite 3.5.9 on win32.

Usually happens when I remove a column from a table: related tables are returned with square brackets, i.e. {{{related_table}}}.

Makes sense (to me) to mirror (at least a part of) the behaviour of sqlite3Dequote.

To implement the square brackets I've used something like:

if self.dbapi.sqlite_version_info < (3, 6, 14):
  rtbl = re.sub(r'^(\"|\[)|(\"|\]()|(\"|\))$', '', rtbl)

[Mike Bayer]

sure, let me add observations between ancient/current sqlite:

SQLite version 2.8.17
Enter ".help" for instructions
sqlite> create table target (id integer not null primary key);
sqlite> create table a (tid integer references target(id));
sqlite> create table b (tid integer references "target"(id));
sqlite> create table c (tid integer references `target`(id));
assertion "pVm==0 || sqlite_malloc_failed" failed: file "./src/main.c", line 642, function "sqlite_exec"
Abort trap (core dumped)
-bash-4.2$ sqlite
SQLite version 2.8.17
Enter ".help" for instructions
sqlite> create table target (id integer not null primary key);
sqlite> create table a (tid integer references target(id));
sqlite> create table b (tid integer references "target"(id));
sqlite> 
sqlite> create table c (tid integer references [target](target)(id));
sqlite> create table d (tid integer references 'target'(id));
sqlite> pragma foreign_key_list(a);
0|0|target|tid|id
sqlite> pragma foreign_key_list(b);
0|0|"target"|tid|id
sqlite> pragma foreign_key_list(c);
0|0|[target](target)|tid|id
sqlite> pragma foreign_key_list(d);
0|0|'target'|tid|id
sqlite>



SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table target (id integer not null primary key);
sqlite> create table a (tid integer references target(id));
sqlite> create table b (tid integer references "target"(id));
sqlite> create table c (tid integer references `target`(id));
sqlite> create table d (tid integer references 'target'(id));
sqlite> create table e (tid integer references [target](target)(id));
sqlite> pragma foreign_key_list(a);
0|0|target|tid|id|NO ACTION|NO ACTION|NONE
sqlite> pragma foreign_key_list(b);
0|0|target|tid|id|NO ACTION|NO ACTION|NONE
sqlite> pragma foreign_key_list(c);
0|0|target|tid|id|NO ACTION|NO ACTION|NONE
sqlite> pragma foreign_key_list(d);
0|0|target|tid|id|NO ACTION|NO ACTION|NONE
sqlite> pragma foreign_key_list(e);
0|0|target|tid|id|NO ACTION|NO ACTION|NONE
sqlite>

[Mike Bayer]

apply to 0.7.10 as well

[Mike Bayer]

850e881ce2cadd1cac58e7e1dfa5e3fbb2db4faf (default)

3f72142df053a9e0e447c967455871d6744ed703 (0.7)

[Mike Bayer]

Removing milestone: 0.8.0final (automated comment)