Change quoting style to MySQL, PostgreSQL or SQLite

diff --git a/ChangeLog b/ChangeLog
index 76d89b942dd5d00a5d498fc7043fa12dfef7a186..d84b6372b82590edfebe5e6a3440fa1d6da205c9 100644 (file)
--- 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 12e574b3676702d30b42ac3f37c18b5bd43c5049..0035c555f29d7c7e528763f12ca6d982cf641d15 100644 (file)
--- 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
+<https://www.postgresql.org/docs/9.1/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-ESCAPE>`_.
+`-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 112130ea398c707878d6e78e16088a8e6a3628ba..f06234aeaf3ef48ede0765eb2a1e9f9f1a85ed43 100755 (executable)
--- 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 fa9c4ec2bced5bc6f92d4bf5106a54cce740cf64..27921526f4850570157cbcf2506b950b65cb5299 100644 (file)
--- 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 218655aa976090c7af1a79c6881ca6d35b0311e0..91ef98d66c65cd86ce93baddec518ccb980eb7f6 100644 (file)
--- 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)