diff options
Diffstat (limited to 'Lib/sqlite3')
| -rw-r--r-- | Lib/sqlite3/dump.py | 50 | ||||
| -rw-r--r-- | Lib/sqlite3/test/dump.py | 8 |
2 files changed, 36 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));" |