Code

Opened 8 years ago

Closed 8 years ago

Last modified 3 years ago

#1935 closed defect (fixed)

[patch] sqlite3 can't handle initial data with more than one statement

Reported by: jpellerin@… Owned by: adrian
Component: Core (Other) Version: master
Severity: major Keywords:
Cc: landonf@… Triage Stage: Unreviewed
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

Using django trunk [2947], pysqlite 2.2.2 and sqlite 3.3.5, I can't include more than one statement in my models' initial data files, or sqlite chokes with an error: "You can only execute one statement at a time". Various googlable comments here and elsewhere indicate that there is some magical combination of sqlite version and pysqlite version that will make this work, but I've been unable to find it. Seems likely that others are probably unable also, so I think a workaround in django will be valuable.

Attached is a patch against [2947] django.core.management.py that uses a regular expression to split multiple statements in a model's sql initial data on ;, so that they can be fed to badly-behaving backends one at a time.

Attachments (3)

django.core.management.diff (1.5 KB) - added by jpellerin@… 8 years ago.
patch to split sql initial data into individual sql statements
patch-initial_sql_regex (575 bytes) - added by anonymous 8 years ago.
patch-initial_sql_regex-v2 (804 bytes) - added by Landon Fuller <landonf@…> 8 years ago.

Download all attachments as: .zip

Change History (12)

Changed 8 years ago by jpellerin@…

patch to split sql initial data into individual sql statements

comment:1 Changed 8 years ago by jpellerin@…

Further information: according to the sqlite source, executing multiple sql statements in one execute() is explicitly disallowed by DB-API.

Ref: http://www.initd.org/tracker/pysqlite/changeset/177#file3

comment:2 Changed 8 years ago by adrian

  • Resolution set to fixed
  • Status changed from new to closed

(In [2985]) Fixed #1935 -- Initial SQL data now works in SQLite if there are multiple statements. Thanks, jpellerin@…

comment:3 Changed 8 years ago by anonymous

  • Resolution fixed deleted
  • Status changed from closed to reopened

The current regular expression does not correctly handle ';' terminated SQL statements. Because the regular expression considers '\n' valid within an SQL statement, but ';' is considered a terminator, the following occurs:

ALTER TABLE "assets_company" ADD CONSTRAINT "vvalid_name" CHECK (LENGTH(name) != 3);

is parsed as:

['ALTER TABLE "assets_company" ADD CONSTRAINT "valid_name" CHECK (LENGTH(name) != 0)', '\n']

The postgresql driver errors out upon receiving a statement containing only '\n'. A patch to correctly handle statement termination is attached.

Changed 8 years ago by anonymous

comment:4 Changed 8 years ago by landonf@…

Comment to note non-anonymous source of regex patch and bug re-opening.

comment:5 Changed 8 years ago by Landon Fuller <landonf@…>

  • Cc landonf@… added

comment:6 Changed 8 years ago by Landon Fuller <landonf@…>

  • Severity changed from normal to major

I've updated the patch to also fix: #2034, #2044, and #2119. I've only tested against Postgesql. Pasting it inline as well as attaching it, since it's short:
Index: management.py
===================================================================
--- management.py (revision 3137)
+++ management.py (working copy)
@@ -337,9 +337,9 @@

r"""( # each statement is...
(?: # one or more chunks of ...

(?:[;'"]+) # not the end of a statement or start of a quote

  • | (?:'[']*') # something in single quotes
  • | (?:"["]*") # something in double quotes
  • )+)""", re.VERBOSE)

+ | (?:'(?:[']|<?=\')*') # something in single quotes
+ | (?:"(?:["]|<?=\")*") # something in double quotes
+ )+;?[ \t\n]?)""", re.VERBOSE)

# Find custom SQL, if it's available.
sql_files = [os.path.join(app_dir, "%s.%s.sql" % (opts.object_name.lower(), settings.DATABASE_ENGINE)),

Changed 8 years ago by Landon Fuller <landonf@…>

comment:7 Changed 8 years ago by Landon Fuller <landonf@…>

Okay, so I obviously don't know how to use wiki formatting. Sorry!

Index: management.py
===================================================================
--- management.py       (revision 3137)
+++ management.py       (working copy)
@@ -337,9 +337,9 @@
         r"""(           # each statement is...
         (?:             # one or more chunks of ...
             (?:[^;'"]+) # not the end of a statement or start of a quote
-          | (?:'[^']*') # something in single quotes
-          | (?:"[^"]*") # something in double quotes
-        )+)""", re.VERBOSE)
+          | (?:'(?:[^']|<?=\')*') # something in single quotes
+          | (?:"(?:[^"]|<?=\")*") # something in double quotes
+        )+;?[ \t\n]?)""", re.VERBOSE)
 
     # Find custom SQL, if it's available.
     sql_files = [os.path.join(app_dir, "%s.%s.sql" % (opts.object_name.lower(), settings.DATABASE_ENGINE)),

comment:8 Changed 8 years ago by mtredinnick

  • Resolution set to fixed
  • Status changed from reopened to closed

(In [3178]) Fixed #2119 -- fixed problems with splitting SQL statements into separate
statements. Uses a patch from eaw@… and some contributions from
jpellerin@…. Also fixes #2034 and #1935.

comment:9 Changed 3 years ago by al_the_x

This may not be a worthwhile concern, but the current implementation does not properly account for SQL comments after a semicolon. Consider:

DROP VIEW "some_view" IF EXISTS;

CREATE VIEW "some_view" AS
    SELECT "some_field" FROM "some_table"
        LEFT JOIN "some_other_table" USING "some_other_id"
        WHERE ( "some_field" BETWEEN 1 AND 1000 )
; -- CREATE VIEW "some_view"

For an exceptionally large CREATE statement, the comments are helpful. SQLite will complain about making two queries in the same execute() statement, because the current code doesn't address trailing comments.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


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

 
Note: See TracTickets for help on using tickets.