From 9412933a68ffdcb20dacb9ff927d4eb285fded2e Mon Sep 17 00:00:00 2001 From: Oleg Broytman Date: Tue, 27 Sep 2016 00:32:48 +0300 Subject: [PATCH] Change quoting style to MySQL, PostgreSQL or SQLite --- ChangeLog | 4 ++++ docs/mysql2sql.rst | 17 ++++++++++++++--- scripts/mysql2sql | 28 +++++++++++++++++++++++++--- sqlconvert/__version__.py | 2 +- sqlconvert/process_mysql.py | 4 +++- 5 files changed, 47 insertions(+), 8 deletions(-) diff --git a/ChangeLog b/ChangeLog index 76d89b9..d84b637 100644 --- a/ChangeLog +++ b/ChangeLog @@ -1,3 +1,7 @@ +Version 0.0.7 (2016-09-??) + + Change quoting style to MySQL, PostgreSQL or SQLite. + Version 0.0.6 (2016-09-25) Condense a sequence of newlines after a /*! directive */; diff --git a/docs/mysql2sql.rst b/docs/mysql2sql.rst index 12e574b..0035c55 100644 --- a/docs/mysql2sql.rst +++ b/docs/mysql2sql.rst @@ -6,8 +6,8 @@ convert mysqldump (especially with extended INSERT syntax) to standard SQL to load at least to PostgreSQL or SQLite. The program is in the early stage of development and currently cannot do much. -It removes /\*! directives \*/, unescapes strings and passes everything else -unmodified. +It removes /\*! directives \*/, unescapes strings and escapes them to a +different quoting style, and passes everything else unmodified. .. highlight:: none @@ -20,7 +20,7 @@ mysql2sql Usage:: - mysql2sql [-e encoding] [-E output_encoding] [infile] [[-o] outfile] + mysql2sql [-e encoding] [-E output_encoding] [-m/-p/-s] [infile] [[-o] outfile] Options:: @@ -30,10 +30,21 @@ Options:: separate output encoding, default is the same as `-e` except for console; for console output charset from the current locale is used + -m, --mysql MySQL/MariaDB quoting style + -p, --pg, --postgres PostgreSQL quoting style + -s, --sqlite Generic SQL/SQLite quoting style (default) -P, --no-pbar Inhibit progress bar infile Input file, stdin if absent or '-' -o, --outfile outfile Output file, stdout if absent or '-' +Options `-m/-p/-s` change quoting style. `-m` sets MySQL quoting style; it's +added to use the program in the following scenario: convert MySQL dumps with +extended INSERTs to SQL with plain INSERTS suitable to be fed back to MySQL. +`-p` sets PostgreSQL quoting style; it's like MySQL with additional `E''-style +quoting +`_. +`-s` sets generic SQL/SQLite quoting style; this is the default. + If stderr is connected to the console the program displays a text mode progress bar. Option `-P/--no-pbar` inhibits it. diff --git a/scripts/mysql2sql b/scripts/mysql2sql index 112130e..f06234a 100755 --- a/scripts/mysql2sql +++ b/scripts/mysql2sql @@ -25,7 +25,7 @@ def get_fsize(fp): return size -def main(infile, encoding, outfile, output_encoding, use_pbar): +def main(infile, encoding, outfile, output_encoding, use_pbar, quoting_style): if use_pbar: size = get_fsize(infile) if size is None: @@ -56,7 +56,7 @@ def main(infile, encoding, outfile, output_encoding, use_pbar): got_directive = is_directive_statement(statement) if got_directive: continue - process_statement(statement) + process_statement(statement, quoting_style) print_tokens(statement, outfile=outfile, encoding=output_encoding) tokens = grouper.close() @@ -76,6 +76,13 @@ if __name__ == '__main__': help='separate output encoding, default is the same ' 'as -e except for console; for console output ' 'charset from the current locale is used') + parser.add_argument('-m', '--mysql', action='store_true', + help='MySQL/MariaDB quoting style') + parser.add_argument('-p', '--pg', '--postgres', action='store_true', + help='PostgreSQL quoting style') + parser.add_argument('-s', '--sqlite', action='store_true', + help='Generic SQL/SQLite quoting style; ' + 'this is the default') parser.add_argument('-o', '--outfile', help='output file name') parser.add_argument('-P', '--no-pbar', action='store_true', help='inhibit progress bar') @@ -83,6 +90,13 @@ if __name__ == '__main__': parser.add_argument('output_file', nargs='?', help='output file name') args = parser.parse_args() + if int(args.mysql) + int(args.postgres) + int(args.sqlite) > 1: + print("Error: options -m/-p/-s are mutually incompatible, " + "use only one of them", + file=sys.stderr) + parser.print_help() + sys.exit(1) + if args.infile: if args.infile == '-': infile = sys.stdin @@ -127,4 +141,12 @@ if __name__ == '__main__': infile.close() raise - main(infile, args.encoding, outfile, output_encoding, not args.no_pbar) + if args.mysql: + quoting_style = 'mysql' + elif args.postgres: + quoting_style = 'postgres' + else: + quoting_style = 'sqlite' + + main(infile, args.encoding, outfile, output_encoding, not args.no_pbar, + quoting_style) diff --git a/sqlconvert/__version__.py b/sqlconvert/__version__.py index fa9c4ec..2792152 100644 --- a/sqlconvert/__version__.py +++ b/sqlconvert/__version__.py @@ -1 +1 @@ -__version__ = '0.0.6' +__version__ = '0.0.7' diff --git a/sqlconvert/process_mysql.py b/sqlconvert/process_mysql.py index 218655a..91ef98d 100644 --- a/sqlconvert/process_mysql.py +++ b/sqlconvert/process_mysql.py @@ -1,6 +1,7 @@ from sqlparse.sql import Comment from sqlparse import tokens as T +from .process_tokens import escape_strings def _is_directive_token(token): @@ -69,7 +70,8 @@ def unescape_strings(token_list): token.normalized = token.value = value -def process_statement(statement): +def process_statement(statement, quoting_style='sqlite'): remove_directive_tokens(statement) requote_names(statement) unescape_strings(statement) + escape_strings(statement, quoting_style) -- 2.39.2