sqlrun.template.properties

###############################################################################
# sqlrun config file                                                          #
# https://github.com/tbrugz/sqldump                                           #
###############################################################################

# "main()" class is 'tbrugz.sqldump.sqlrun.SQLRun'

basedir=work/input/
errordir=work/output/statements-errors

#
# global 'dir' & 'loginvalidstatments' properties may be defined
#

sqlrun.dir=${basedir}
sqlrun.loginvalidstatments=${errordir}/base_statements_errors.sql
# prop 'sqlrun.commit.strategy' defines, hum, the commit strategy
# (one of: 'autocommit', 'file', 'execid', 'run', 'none' ; default: file)
sqlrun.commit.strategy=execid
# TODO: sqlrun.commit.strategy=statement
# TODO: 'sqlrun.commit.inverval' - for 'statement' commit strategy (default is 100?)
# TODO: sqlrun.commit.inverval=1000

# default file encoding (default is UTF-8)
#sqlrun.defaultencoding=ISO-8859-1

# tokenizer class (defaults first) (StringSpliter is deprecated)
#sqlrun.sqltokenizerclass=SQLStmtScanner|SQLStmtTokenizer|SQLStmtNgScanner

# only executes specified ids
#sqlrun.filterbyids=05, 10

# fail on error ? (default is true)
#sqlrun.failonerror=true|false

# use, or not, prepared statements (default is true)
#sqlrun.usepreparedstatement=true|false

# use, or not, savepoints (default is true)
#sqlrun.use-savepoint=true|false

# to select a different connection properties prefix
#sqlrun.connpropprefix=sqlrun.conn-x

# to trust all SSL certificates from https urls (default is false)
#sqlrun.trust-all-certs=true

#
# monitoring: jmx mbean
# (option to create a JMX Managed Bean, default is false)
#
#sqlrun.jmx.create-mbean=false


#
# execs are processed in alphanumeric-ordered way
#
# exec suffixes are: .file, .files/.files.glob, .files.regex, .statement, .import
#

sqlrun.exec.05.statement=delete from <schema>.<table>

sqlrun.exec.10.file=${basedir}/statements1.sql
sqlrun.exec.10.loginvalidstatments=${errordir}/statements1_errors.sql
# log each X statements read (default is 1000)
sqlrun.exec.10.log-each-x-input-rows=500

sqlrun.exec.20.file=${basedir}/statements2.sql
sqlrun.exec.20.loginvalidstatments=${errordir}/statements2_errors.sql

sqlrun.exec.55.statement=update <table> set <col> = null

sqlrun.exec.60.file=${basedir}/statements3.sql
sqlrun.exec.60.loginvalidstatments=${errordir}/statements3_errors.sql

sqlrun.exec.65.statement=insert into <table> (<cols>) values (<vals>)

sqlrun.exec.68.statement=update table_x set id_y = 0 where id_z = ?
sqlrun.exec.68.param.1=42

# execute multiple files (using glob pattern)
sqlrun.exec.70.files=TABLE_*.sql
sqlrun.exec.70.files=**/TABLE_*.sql
sqlrun.exec.70.files.glob=**/TABLE_*.sql
# execute multiple files (using regex pattern)
sqlrun.exec.70.files.regex=TABLE_.*\\.sql
sqlrun.exec.70.dir=${basedir}
sqlrun.exec.70.loginvalidstatments=${errordir}/statements3_errors.sql
# 'split' suffix splits (tokenizes) content by semicolon - ';' (default=true)
sqlrun.exec.70.split=false
sqlrun.exec.70.encoding=ISO-8859-1
# may be used to escape "\'", as "''" - mysql uses it, default is false
#sqlrun.exec.70.escapebackslashedapos=false

sqlrun.exec.80.import=csv
# csvplain: do not interpret quoted Strings & multilines 
#sqlrun.exec.80.import=csvplain
# .inserttable or .insertsql should be defined
sqlrun.exec.80.inserttable=ins_table
sqlrun.exec.80.importfile=${basedir}/file1.csv
# output appended data as the file grows - inspired by 'tail -f'
sqlrun.exec.80.follow=true|false
# default recorddelimiter: "\r?\n" (regex)
sqlrun.exec.80.recorddelimiter=\r\n
sqlrun.exec.80.columndelimiter=;
sqlrun.exec.80.encoding=ISO-8859-1
sqlrun.exec.80.skipnlines=1
# option to set null (in prepared statement) when string is equal to a constant
#sqlrun.exec.80.nullconstant=NULL
# option to set null (in prepared statement) when string is equal to any of some constants
#sqlrun.exec.80.nullconstants=-, ,NULL
# option to set null (in prepared statement) when string is empty (default is false)
#sqlrun.exec.80.emptystringasnull=false|true
# commit each 'x' rows
#sqlrun.exec.80.x-commiteachxrows=100
# skips lines based on regex match (find). ex: lines beginning with '#'
#sqlrun.exec.80.skip-line-regex=^#
# limit the number of lines to be imported (e.g. max number of lines, not counting skipped lines or errors - see '.skipnlines')
#sqlrun.exec.80.limit=20
# limit the number of lines to be read/processed (e.g. max number of lines, not counting skipped lines - see '.skipnlines')
#sqlrun.exec.80.limit-input=20
# log each X rows read (default is 10000)
sqlrun.exec.80.log-each-x-input-rows=50000
# log each X rows imported (default is 0 - no logging)
sqlrun.exec.80.log-each-x-output-rows=10000
# execute statement after import
sqlrun.exec.80.statement-after=detete from ins_table where col1='A'

sqlrun.exec.81.import=regex
sqlrun.exec.81.pattern=(.+?) \\[(.+?)\\] "(.+?)"
# pattern flags may be defined: decimal bit mask of flag options (see Pattern.compile(regex, flags))
# 01: unix-lines ; 02: case-insensitive; 04: comments; 08: multiline; 16: literal ; 32: dotall; 64: unicode-case; 128: canon-eq
#sqlrun.exec.81.patternflags=32
# sub-patterns to ignore (after split)
sqlrun.exec.81.subpatterns2ignore=<abc> | <def>
sqlrun.exec.81.insertsql=insert into ins_table (col1, col2, col3) values (?, ?, ?)
# .insertsql may use positional parameters that references column in import file. ex:
# sqlrun.exec.<xx>.insertsql=insert into ins_table (col1, col2, col3) values (${2}, ${1}, ${0})
# .insertsql may be an update. ex:
# sqlrun.exec.81.insertsql=update ins_table set col3=${2} where col1=${0}
sqlrun.exec.81.importfile=${basedir}/file2.log
# '.importfiles.glob' (glob pattern) may be used instead of '.importfile'.
# for glob pattern syntax see: https://docs.oracle.com/javase/7/docs/api/java/nio/file/FileSystem.html#getPathMatcher(java.lang.String)
# if '.importdir' is not defined current (working) dir is assumed. Ex:
#sqlrun.exec.81.importfiles.glob=dir/file_*.csv
#sqlrun.exec.81.importfiles.glob=dir/**/file_*.csv
# '.importfiles.regex' (regex pattern) may be used instead of '.importfile'
# if '.importdir' is not defined current (working) dir is assumed. Ex:
#sqlrun.exec.81.importdir=${basedir}
#sqlrun.exec.81.importfiles.regex=file.*\\.log
# '.importfiles' is an alias for '.importfiles.glob'
#sqlrun.exec.81.importfiles=dir/**/file_*.csv

# default recorddelimiter: "\n"
# column types to be used by prepared statement
# types: int, string, double, doublec (parsed as double with comma), date[<date-format>],
#   blob-location (imports blob from file location), text-location (imports clob/text/varchar from file location),
#   object (experimental), null (experimental),
#   - (skips column)
sqlrun.exec.81.columntypes=int, string, int, date[yyyy-MM-dd], ...

# batch updates may be used (defaults is false) 
#sqlrun.exec.81.batchmode=false
#sqlrun.exec.81.batchsize=1000
# on error with batchmode==true, retry with batchmode off
#sqlrun.exec.81.batchmode.retry-with-batch-off=true

# url may be used instead of file
sqlrun.exec.81.importurl=http://www.example.com/example.csv
# if behind a proxy, you should set these system properties: http.proxyHost, http.proxyPort
# optional message body (POST data)
sqlrun.exec.81.urlmessagebody=name=Tim&age=45
sqlrun.exec.81.urlmethod=POST
# may add headers to request
sqlrun.exec.81.urlheader@SOAPAction=""

sqlrun.exec.81.recorddelimiter=\r\n
# failover option. prop '.insertsql' may be used
# failover/regex: '.pattern' may be used ; /csv: '.columndelimiter' may be used
sqlrun.exec.81.failover.1.pattern=(.+?) \\[(.+?)\\] "(.+?)" "(.+?)"
sqlrun.exec.81.failover.1.insertsql=insert into ins_table (col1, col2, col3, col4) values (?, ?, ?, ?)
sqlrun.exec.81.failover.1.columntypes=int, string, int, string, doublec
# log or not malformed records/lines (default is true)
sqlrun.exec.81.logmalformedline=true|false

# '.query' suffix for simple queries
sqlrun.exec.83.query=select count(*) from ins_table
sqlrun.exec.83.outputstream=<stderr>
sqlrun.exec.83.dumpsyntax=FFCDataDump
# optional .queryname suffix
sqlrun.exec.83.queryname=number of records in ins_table

# fixed column size (ffc) importer
sqlrun.exec.85.import=ffc
# defines size of columns on each line 
sqlrun.exec.85.columnsizes=2,8,10,1,5
sqlrun.exec.85.importfile=${basedir}/file2.txt
sqlrun.exec.85.columntypes=int,string,int,string,int
# example skipping 2nd and 3rd columns
sqlrun.exec.85.columntypes=int,-,-,string,int
# if error on parsing int, set predefined value (default is to set null)
sqlrun.exec.85.onerror.type-int-value=0
sqlrun.exec.85.insertsql=insert into table_z (a, b, c, d, e) values (?, ?, ?, ?, ?)

# excel (xls) importer
sqlrun.exec.86.import=xls
sqlrun.exec.86.importfile=${basedir}/file3.xls(x)
# option to import multiple files with glob syntax (see above for syntax)
sqlrun.exec.86.importfiles=${basedir}/data/**/file*.xlsx
# define sheet-number or sheet-name - otherwise 1st sheet will be used
sqlrun.exec.86.sheet-number=0
sqlrun.exec.86.sheet-name=abc
# if 1st line is header (default is true)
sqlrun.exec.86.1st-line-is-header=true|false
# set column names from header - if 1st line is header (default is false)
sqlrun.exec.86.1st-line-as-column-names=false|true
# 'columntypes' is optional
sqlrun.exec.86.columntypes=int,string,int,string,double
# use 'insertsql' or 'inserttable'
sqlrun.exec.86.insertsql=insert into table_xls (a, b, c, d, e) values (?, ?, ?, ?, ?)
sqlrun.exec.86.inserttable=table_xls
# create a table for import...
sqlrun.exec.86.do-create-table=false|true
# define column names to be used on insert - suffix '.inserttable' (also used with '.do-create-table')
sqlrun.exec.86.columnnames=a, b, c, d, e
# execute statement before import...
sqlrun.exec.86.statement-before=truncate table table_xls

# sql/database importer - import data from another database/connection
# uses tbrugz.sqldump.datadump.InsertIntoDatabase (iidb) syntax/importer - see 'sqldump.template.properties'
sqlrun.exec.88.import=sql
sqlrun.exec.88.read-connection-prefix=sqlrun
sqlrun.exec.88.sql=select * from t1
# use 'insertsql' or 'inserttable'
sqlrun.exec.88.inserttable=t2
sqlrun.exec.88.insertsql=insert into t2 values (?, ?)


#
# asserts are also processed in alphanumeric-ordered way
#
# assert suffixes are: .sql, .sqlfile
#
# assert
sqlrun.assert.90.sql=select * from table_xls
#sqlrun.assert.90.sqlfile=../assert-query.sql
# assert by query row count: '.row-count.[eq|gt|lt]' (equals|greater than|less than)
sqlrun.assert.90.row-count.eq=3
sqlrun.assert.90.row-count.gt=3
sqlrun.assert.90.row-count.lt=3
# assert by query row/col value (equals comparisson): '.row@<row-number>.col@<column-name>.eq'
# (testing column <column-name> from row <row-number> - row number starts at 1)
sqlrun.assert.90.row@1.col@XYZ.eq=123
sqlrun.assert.90.row@2.col@ABC.eq=something

#----------------------

#
# connection properties are similar to sqldump
#

# Derby Embedded
#sqlrun.driverclass=org.apache.derby.jdbc.EmbeddedDriver
#sqlrun.dburl=jdbc:derby:<db-path>
#sqlrun.user=sa
#sqlrun.password=sa