Django

Code

Ticket #1935 (closed: fixed)

Opened 2 years ago

Last modified 2 years ago

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

Reported by: jpellerin@gmail.com Assigned to: adrian
Milestone: Component: Core framework
Version: SVN Keywords:
Cc: landonf@opendarwin.org Triage Stage: Unreviewed
Has patch: 1 Needs documentation: 0
Needs tests: 0 Patch needs improvement: 0

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

django.core.management.diff (1.5 kB) - added by jpellerin@gmail.com on 05/19/06 15:42:35.
patch to split sql initial data into individual sql statements
patch-initial_sql_regex (0.6 kB) - added by anonymous on 06/17/06 21:02:26.
patch-initial_sql_regex-v2 (0.8 kB) - added by Landon Fuller <landonf@opendarwin.org> on 06/17/06 22:07:21.

Change History

05/19/06 15:42:35 changed by jpellerin@gmail.com

  • attachment django.core.management.diff added.

patch to split sql initial data into individual sql statements

05/21/06 14:57:46 changed by jpellerin@gmail.com

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

05/26/06 00:20:22 changed by adrian

  • status changed from new to closed.
  • resolution set to fixed.

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

06/17/06 21:01:55 changed by anonymous

  • status changed from closed to reopened.
  • resolution deleted.

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.

06/17/06 21:02:26 changed by anonymous

  • attachment patch-initial_sql_regex added.

06/17/06 21:03:56 changed by landonf@opendarwin.org

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

06/17/06 21:05:46 changed by Landon Fuller <landonf@opendarwin.org>

  • cc set to landonf@opendarwin.org.

06/17/06 22:07:07 changed by Landon Fuller <landonf@opendarwin.org>

  • 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)),

06/17/06 22:07:21 changed by Landon Fuller <landonf@opendarwin.org>

  • attachment patch-initial_sql_regex-v2 added.

06/17/06 22:09:03 changed by Landon Fuller <landonf@opendarwin.org>

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

06/20/06 03:00:45 changed by mtredinnick

  • status changed from reopened to closed.
  • resolution set to fixed.

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

Add/Change #1935 ([patch] sqlite3 can't handle initial data with more than one statement)




Change Properties
Action