From 51d04d1ba8643d8a0c89d8307bf43697223f31e2 Mon Sep 17 00:00:00 2001
From: Petri Lehtinen <petri@digip.org>
Date: Sun, 12 Feb 2012 21:05:31 +0200
Subject: Fix sqlite3.Connection.iterdump on tables/fields with reserved names
or quotes
Closes #9750
---
Lib/sqlite3/dump.py | 50 +++++++++++++++++++++++++++---------------------
Lib/sqlite3/test/dump.py | 8 ++++++++
Misc/ACKS | 1 +
Misc/NEWS | 4 ++++
4 files changed, 41 insertions(+), 22 deletions(-)
diff --git a/Lib/sqlite3/dump.py b/Lib/sqlite3/dump.py
index 409a405..da6be68 100644
--- a/Lib/sqlite3/dump.py
+++ b/Lib/sqlite3/dump.py
@@ -1,6 +1,12 @@
# Mimic the sqlite3 console shell's .dump command
# Author: Paul Kippes <kippesp@gmail.com>
+# Every identifier in sql is quoted based on a comment in sqlite
+# documentation "SQLite adds new keywords from time to time when it
+# takes on new features. So to prevent your code from being broken by
+# future enhancements, you should normally quote any identifier that
+# is an English language word, even if you do not have to."
+
def _iterdump(connection):
"""
Returns an iterator to the dump of the database in an SQL text format.
@@ -15,49 +21,49 @@ def _iterdump(connection):
# sqlite_master table contains the SQL CREATE statements for the database.
q = """
- SELECT name, type, sql
- FROM sqlite_master
- WHERE sql NOT NULL AND
- type == 'table'
+ SELECT "name", "type", "sql"
+ FROM "sqlite_master"
+ WHERE "sql" NOT NULL AND
+ "type" == 'table'
"""
schema_res = cu.execute(q)
- for table_name, type, sql in schema_res.fetchall():
+ for table_name, type, sql in sorted(schema_res.fetchall()):
if table_name == 'sqlite_sequence':
- yield('DELETE FROM sqlite_sequence;')
+ yield('DELETE FROM "sqlite_sequence";')
elif table_name == 'sqlite_stat1':
- yield('ANALYZE sqlite_master;')
+ yield('ANALYZE "sqlite_master";')
elif table_name.startswith('sqlite_'):
continue
# NOTE: Virtual table support not implemented
#elif sql.startswith('CREATE VIRTUAL TABLE'):
# qtable = table_name.replace("'", "''")
# yield("INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)"\
- # "VALUES('table','%s','%s',0,'%s');" %
- # qtable,
+ # "VALUES('table','{0}','{0}',0,'{1}');".format(
# qtable,
- # sql.replace("''"))
+ # sql.replace("''")))
else:
- yield('%s;' % sql)
+ yield('{0};'.format(sql))
# Build the insert statement for each row of the current table
- res = cu.execute("PRAGMA table_info('%s')" % table_name)
+ table_name_ident = table_name.replace('"', '""')
+ res = cu.execute('PRAGMA table_info("{0}")'.format(table_name_ident))
column_names = [str(table_info[1]) for table_info in res.fetchall()]
- q = "SELECT 'INSERT INTO \"%(tbl_name)s\" VALUES("
- q += ",".join(["'||quote(" + col + ")||'" for col in column_names])
- q += ")' FROM '%(tbl_name)s'"
- query_res = cu.execute(q % {'tbl_name': table_name})
+ q = """SELECT 'INSERT INTO "{0}" VALUES({1})' FROM "{0}";""".format(
+ table_name_ident,
+ ",".join("""'||quote("{0}")||'""".format(col.replace('"', '""')) for col in column_names))
+ query_res = cu.execute(q)
for row in query_res:
- yield("%s;" % row[0])
+ yield("{0};".format(row[0]))
# Now when the type is 'index', 'trigger', or 'view'
q = """
- SELECT name, type, sql
- FROM sqlite_master
- WHERE sql NOT NULL AND
- type IN ('index', 'trigger', 'view')
+ SELECT "name", "type", "sql"
+ FROM "sqlite_master"
+ WHERE "sql" NOT NULL AND
+ "type" IN ('index', 'trigger', 'view')
"""
schema_res = cu.execute(q)
for name, type, sql in schema_res.fetchall():
- yield('%s;' % sql)
+ yield('{0};'.format(sql))
yield('COMMIT;')
diff --git a/Lib/sqlite3/test/dump.py b/Lib/sqlite3/test/dump.py
index f40876a..b200333 100644
--- a/Lib/sqlite3/test/dump.py
+++ b/Lib/sqlite3/test/dump.py
@@ -13,6 +13,14 @@ class DumpTests(unittest.TestCase):
def CheckTableDump(self):
expected_sqls = [
+ """CREATE TABLE "index"("index" blob);"""
+ ,
+ """INSERT INTO "index" VALUES(X'01');"""
+ ,
+ """CREATE TABLE "quoted""table"("quoted""field" text);"""
+ ,
+ """INSERT INTO "quoted""table" VALUES('quoted''value');"""
+ ,
"CREATE TABLE t1(id integer primary key, s1 text, " \
"t1_i1 integer not null, i2 integer, unique (s1), " \
"constraint t1_idx1 unique (i2));"
diff --git a/Misc/ACKS b/Misc/ACKS
index 313aed5..f79eced 100644
--- a/Misc/ACKS
+++ b/Misc/ACKS
@@ -503,6 +503,7 @@ Lenny Kneler
Pat Knight
Greg Kochanski
Damon Kohler
+Marko Kohtala
Vlad Korolev
Joseph Koshy
Maksim Kozyarchuk
diff --git a/Misc/NEWS b/Misc/NEWS
index 73bdf82..a00331d 100644
--- a/Misc/NEWS
+++ b/Misc/NEWS
@@ -113,6 +113,10 @@ Core and Builtins
Library
-------
+- Issue #9750: Fix sqlite3.Connection.iterdump on tables and fields
+ with a name that is a keyword or contains quotes. Patch by Marko
+ Kohtala.
+
- Issue #10287: nntplib now queries the server's CAPABILITIES again after
authenticating (since the result may change, according to RFC 4643).
Patch by Hynek Schlawack.
--
cgit v0.12