| Révision | eaf78dada4c30a035f0710e169b524827d44ba8e (tree) |
|---|---|
| l'heure | 2019-12-22 02:20:48 |
| Auteur |  Dreas Nielsen <dreas.nielsen@gmai...> |
| Commiter | Dreas Nielsen |
Added the BEGIN SQL and END SQL metacommands.
CHANGELOG.rst (diff) doc/source/metacommands.rst (diff) doc/source/usage.rst (diff)
execsql/execsql.py (diff) setup.py (diff)| @@ -1,6 +1,7 @@ | ||
| 1 | 1 | ========== ========== ================================================================================= |
| 2 | 2 | Version Date Features |
| 3 | 3 | ========== ========== ================================================================================= |
| 4 | +1.54.0 2019-12-20 Added the BEGIN/END SQL metacommands. | |
| 4 | 5 | 1.53.0 2019-10-27 Added Oracle support. |
| 5 | 6 | 1.52.0 2019-10-11 Added export to XML. |
| 6 | 7 | 1.51.0 2019-10-10 Added WHILE and UNTIL loop control to EXECUTE SCRIPT; implemented deferred variable substitution. |
| @@ -324,6 +324,40 @@ | ||
| 324 | 324 | the SQL statements in that incomplete batch will not be committed. |
| 325 | 325 | |
| 326 | 326 | |
| 327 | + | |
| 328 | +.. index:: !BEGIN SQL metacommand | |
| 329 | + | |
| 330 | +.. _beginsql: | |
| 331 | + | |
| 332 | +BEGIN SQL and END SQL | |
| 333 | +--------------------------------------- | |
| 334 | + | |
| 335 | +:: | |
| 336 | + | |
| 337 | + BEGIN SQL | |
| 338 | + | |
| 339 | +:: | |
| 340 | + | |
| 341 | + END SQL | |
| 342 | + | |
| 343 | +The BEGIN SQL and END SQL metacommands define a block of lines in the script | |
| 344 | +file that will be treated as a single SQL statement. Within the block of lines | |
| 345 | +defined by these metacommands, a semicolon at the end of the line will *not* be | |
| 346 | +treated as the end of a SQL statement. | |
| 347 | + | |
| 348 | +The primary intended use case for these metacommands is to bracket procedure | |
| 349 | +and function definitions. A function definition may contain multiple SQL | |
| 350 | +statements, each of which is ended by a semicolon, but which should all be sent | |
| 351 | +to the DBMS as a single statement, not as a series of invididual SQL statements. | |
| 352 | + | |
| 353 | +The BEGIN SQL and END SQL metacommands are an alternative to the use of | |
| 354 | +:ref:`line continuation characters <continuationchars>`. | |
| 355 | + | |
| 356 | +Metacommands that appear within a BEGIN/END SQL block will be treated as | |
| 357 | +comments and will be ignored: they will not be executed when the SQL | |
| 358 | +statement is run, and no error message will be produced. | |
| 359 | + | |
| 360 | + | |
| 327 | 361 | .. index:: ! BEGIN SCRIPT metacommand |
| 328 | 362 | single: CREATE SCRIPT metacommand |
| 329 | 363 | single: Scripting |
| @@ -27,13 +27,21 @@ | ||
| 27 | 27 | SQL Statement Recognition and SQL Syntax |
| 28 | 28 | ---------------------------------------------- |
| 29 | 29 | |
| 30 | +.. index:: | |
| 31 | + single: Continuation characters | |
| 32 | + | |
| 33 | +.. _continuationchars: | |
| 34 | + | |
| 30 | 35 | execsql recognizes a SQL statement as consisting of a sequence of |
| 31 | 36 | non-comment lines that ends with a line ending with a semicolon. A |
| 32 | 37 | backslash ("\") at the end of a line is treated as a line continuation |
| 33 | 38 | character. Backslashes do not need to be used for simple SQL |
| 34 | -statements, but must be used for procedure and function definitions, | |
| 39 | +statements, but can be used for procedure and function definitions, | |
| 35 | 40 | where there are semicolons within the body of the definition, and a |
| 36 | -semicolon appears at the end of lines for readability purposes. | |
| 41 | +semicolon appears at the end of lines for readability purposes. The | |
| 42 | +:ref:`BEGIN SQL <beginsql>` and :ref:`END SQL <beginsql>` metacommands | |
| 43 | +can be used to bracket procedure and function definitions instead | |
| 44 | +of using backslashes. | |
| 37 | 45 | Backslashes may not be used as continuation characters for |
| 38 | 46 | :ref:`metacommands <metacommands>`. |
| 39 | 47 |
| @@ -27,12 +27,12 @@ | ||
| 27 | 27 | # |
| 28 | 28 | # =============================================================================== |
| 29 | 29 | |
| 30 | -__version__ = "1.53.3" | |
| 31 | -__vdate = "2019-11-16" | |
| 30 | +__version__ = "1.54.0" | |
| 31 | +__vdate = "2019-12-21" | |
| 32 | 32 | |
| 33 | 33 | primary_vno = 1 |
| 34 | -secondary_vno = 53 | |
| 35 | -tertiary_vno = 3 | |
| 34 | +secondary_vno = 54 | |
| 35 | +tertiary_vno = 0 | |
| 36 | 36 | |
| 37 | 37 | import os |
| 38 | 38 | import os.path |
| @@ -11213,11 +11213,16 @@ | ||
| 11213 | 11213 | # The following metacommands are executed IMMEDIATELY during this process: |
| 11214 | 11214 | # * BEGIN SCRIPT <scriptname> |
| 11215 | 11215 | # * END SCRIPT |
| 11216 | + # * BEGIN SQL | |
| 11217 | + # * END SQL | |
| 11216 | 11218 | beginscript = re.compile(r'^--\s*!x!\s*(?:BEGIN|CREATE)\s+SCRIPT\s+(?P<scriptname>\w+)(?:(?P<paramexpr>\s*\S+.*))?$', re.I) |
| 11217 | 11219 | endscript = re.compile(r'^--\s*!x!\s*END\s+SCRIPT(?:\s+(?P<scriptname>\w+))?\s*$', re.I) |
| 11220 | + beginsql = re.compile(r'^--\s*!x!\s*BEGIN\s+SQL\s*$', re.I) | |
| 11221 | + endsql = re.compile(r'^--\s*!x!\s*END\s+SQL\s*$', re.I) | |
| 11218 | 11222 | execline = re.compile(r'^--\s*!x!\s*(?P<cmd>.+)$', re.I) |
| 11219 | 11223 | cmtline = re.compile(r'^--') |
| 11220 | 11224 | in_block_cmt = False |
| 11225 | + in_block_sql = False | |
| 11221 | 11226 | sz, dt = file_size_date(sql_file_name) |
| 11222 | 11227 | exec_log.log_status_info("Reading script file %s (size: %d; date: %s)" % (sql_file_name, sz, dt)) |
| 11223 | 11228 | scriptname = os.path.basename(sql_file_name) |
| @@ -11231,6 +11236,7 @@ | ||
| 11231 | 11236 | file_lineno += 1 |
| 11232 | 11237 | line = line.strip() |
| 11233 | 11238 | is_comment_line = False |
| 11239 | + comment_match = cmtline.match(line) | |
| 11234 | 11240 | metacommand_match = execline.match(line) |
| 11235 | 11241 | if len(line) > 0: |
| 11236 | 11242 | if in_block_cmt: |
| @@ -11245,50 +11251,63 @@ | ||
| 11245 | 11251 | if line[-2:] == u"*/": |
| 11246 | 11252 | in_block_cmt = False |
| 11247 | 11253 | else: |
| 11248 | - if cmtline.match(line): | |
| 11254 | + if comment_match: | |
| 11249 | 11255 | is_comment_line = not metacommand_match |
| 11250 | 11256 | if not is_comment_line: |
| 11251 | 11257 | if metacommand_match: |
| 11252 | 11258 | if len(currcmd) > 0: |
| 11253 | 11259 | write_warning("Incomplete SQL statement starting on line %s at metacommand on line %s." % (sqlline, file_lineno)) |
| 11254 | - begs = beginscript.match(line) | |
| 11255 | - if not begs: | |
| 11256 | - ends = endscript.match(line) | |
| 11257 | - if begs: | |
| 11258 | - # This is a BEGIN SCRIPT metacommand. | |
| 11259 | - scriptname = begs.group('scriptname').lower() | |
| 11260 | - paramnames = None | |
| 11261 | - paramexpr = begs.group('paramexpr') | |
| 11262 | - if paramexpr: | |
| 11263 | - withparams = re.compile(r'(?:\s+WITH)?(?:\s+PARAM(?:ETER)?S)?\s*\(\s*(?P<params>\w+(?:\s*,\s*\w+)*)\s*\)\s*$', re.I) | |
| 11264 | - wp = withparams.match(paramexpr) | |
| 11265 | - if not wp: | |
| 11266 | - raise ErrInfo(type="cmd", command_text=line, other_msg="Invalid BEGIN SCRIPT metacommand on line %s of file %s." % (file_lineno, sql_file_name)) | |
| 11260 | + if beginsql.match(line): | |
| 11261 | + in_block_sql = True | |
| 11262 | + if in_block_sql: | |
| 11263 | + if endsql.match(line): | |
| 11264 | + in_block_sql = False | |
| 11265 | + if len(currcmd) > 0: | |
| 11266 | + cmd = ScriptCmd(sql_file_name, sqlline, 'sql', SqlStmt(currcmd.strip())) | |
| 11267 | + if len(subscript_stack) == 0: | |
| 11268 | + sqllist.append(cmd) | |
| 11269 | + else: | |
| 11270 | + subscript_stack[-1].add(cmd) | |
| 11271 | + currcmd = '' | |
| 11272 | + else: | |
| 11273 | + begs = beginscript.match(line) | |
| 11274 | + if not begs: | |
| 11275 | + ends = endscript.match(line) | |
| 11276 | + if begs: | |
| 11277 | + # This is a BEGIN SCRIPT metacommand. | |
| 11278 | + scriptname = begs.group('scriptname').lower() | |
| 11279 | + paramnames = None | |
| 11280 | + paramexpr = begs.group('paramexpr') | |
| 11281 | + if paramexpr: | |
| 11282 | + withparams = re.compile(r'(?:\s+WITH)?(?:\s+PARAM(?:ETER)?S)?\s*\(\s*(?P<params>\w+(?:\s*,\s*\w+)*)\s*\)\s*$', re.I) | |
| 11283 | + wp = withparams.match(paramexpr) | |
| 11284 | + if not wp: | |
| 11285 | + raise ErrInfo(type="cmd", command_text=line, other_msg="Invalid BEGIN SCRIPT metacommand on line %s of file %s." % (file_lineno, sql_file_name)) | |
| 11286 | + else: | |
| 11287 | + param_rx = re.compile(r'\w+', re.I) | |
| 11288 | + paramnames = re.findall(param_rx, wp.group('params')) | |
| 11289 | + # If there are no parameter names to pass, paramnames will be None | |
| 11290 | + subscript_stack.append(CommandList([], scriptname, paramnames)) | |
| 11291 | + elif ends: | |
| 11292 | + # This is an END SCRIPT metacommand. | |
| 11293 | + endscriptname = ends.group('scriptname') | |
| 11294 | + if endscriptname is not None: | |
| 11295 | + endscriptname = endscriptname.lower() | |
| 11296 | + if len(subscript_stack) == 0: | |
| 11297 | + raise ErrInfo(type="cmd", command_text=line, other_msg="Unmatched END SCRIPT metacommand on line %s of file %s." % (file_lineno, sql_file_name)) | |
| 11298 | + if len(currcmd) > 0: | |
| 11299 | + raise ErrInfo(type="cmd", command_text=line, other_msg="Incomplete SQL statement\n (%s)\nat END SCRIPT metacommand on line %s of file %s." % (currcmd, file_lineno, sql_file_name)) | |
| 11300 | + if endscriptname is not None and endscriptname != scriptname: | |
| 11301 | + raise ErrInfo(type="cmd", command_text=line, other_msg="Mismatched script name in the END SCRIPT metacommand on line %s of file %s." % (file_lineno, sql_file_name)) | |
| 11302 | + sub_script = subscript_stack.pop() | |
| 11303 | + savedscripts[sub_script.listname] = sub_script | |
| 11304 | + else: | |
| 11305 | + # This is a non-IMMEDIATE metacommand. | |
| 11306 | + cmd = ScriptCmd(sql_file_name, file_lineno, 'cmd', MetacommandStmt(metacommand_match.group('cmd').strip())) | |
| 11307 | + if len(subscript_stack) == 0: | |
| 11308 | + sqllist.append(cmd) | |
| 11267 | 11309 | else: |
| 11268 | - param_rx = re.compile(r'\w+', re.I) | |
| 11269 | - paramnames = re.findall(param_rx, wp.group('params')) | |
| 11270 | - # If there are no parameter names to pass, paramnames will be None | |
| 11271 | - subscript_stack.append(CommandList([], scriptname, paramnames)) | |
| 11272 | - elif ends: | |
| 11273 | - # This is an END SCRIPT metacommand. | |
| 11274 | - endscriptname = ends.group('scriptname') | |
| 11275 | - if endscriptname is not None: | |
| 11276 | - endscriptname = endscriptname.lower() | |
| 11277 | - if len(subscript_stack) == 0: | |
| 11278 | - raise ErrInfo(type="cmd", command_text=line, other_msg="Unmatched END SCRIPT metacommand on line %s of file %s." % (file_lineno, sql_file_name)) | |
| 11279 | - if len(currcmd) > 0: | |
| 11280 | - raise ErrInfo(type="cmd", command_text=line, other_msg="Incomplete SQL statement\n (%s)\nat END SCRIPT metacommand on line %s of file %s." % (currcmd, file_lineno, sql_file_name)) | |
| 11281 | - if endscriptname is not None and endscriptname != scriptname: | |
| 11282 | - raise ErrInfo(type="cmd", command_text=line, other_msg="Mismatched script name in the END SCRIPT metacommand on line %s of file %s." % (file_lineno, sql_file_name)) | |
| 11283 | - sub_script = subscript_stack.pop() | |
| 11284 | - savedscripts[sub_script.listname] = sub_script | |
| 11285 | - else: | |
| 11286 | - # This is a non-IMMEDIATE metacommand. | |
| 11287 | - cmd = ScriptCmd(sql_file_name, file_lineno, 'cmd', MetacommandStmt(metacommand_match.group('cmd').strip())) | |
| 11288 | - if len(subscript_stack) == 0: | |
| 11289 | - sqllist.append(cmd) | |
| 11290 | - else: | |
| 11291 | - subscript_stack[-1].add(cmd) | |
| 11310 | + subscript_stack[-1].add(cmd) | |
| 11292 | 11311 | else: |
| 11293 | 11312 | # This line is not a comment and not a metacommand, therefore should be |
| 11294 | 11313 | # part of a SQL statement. |
| @@ -11300,7 +11319,7 @@ | ||
| 11300 | 11319 | currcmd = line |
| 11301 | 11320 | else: |
| 11302 | 11321 | currcmd = u"%s \n%s" % (currcmd, line) |
| 11303 | - if cmd_end: | |
| 11322 | + if cmd_end and not in_block_sql: | |
| 11304 | 11323 | cmd = ScriptCmd(sql_file_name, sqlline, 'sql', SqlStmt(currcmd.strip())) |
| 11305 | 11324 | if len(subscript_stack) == 0: |
| 11306 | 11325 | sqllist.append(cmd) |
| @@ -4,7 +4,7 @@ | ||
| 4 | 4 | long_description = f.read() |
| 5 | 5 | |
| 6 | 6 | setuptools.setup(name='execsql', |
| 7 | - version='1.53.3', | |
| 7 | + version='1.54.0', | |
| 8 | 8 | description="Runs a SQL script against a PostgreSQL, MS-Access, SQLite, MS-SQL-Server, MySQL, MariaDB, Firebird, or Oracle database, or an ODBC DSN. Provides metacommands to import and export data, copy data between databases, conditionally execute SQL and metacommands, and dynamically alter SQL and metacommands with substitution variables. Data can be exported in 13 different formats, including CSV, TSV, ODS, HTML, JSON, LaTeX, and Markdown tables, and using custom templates.", |
| 9 | 9 | author='Dreas Nielsen', |
| 10 | 10 | author_email='dreas.nielsen@gmail.com', |
Fork