NAME

sqlbatch - run a sequence of SQL-database SQL-scripts and client-code

SYNOPSIS

sqlbatch [-help|-man] sqlbatch [-directory=...] sqlbatch [-directory=...] [-configfile=...] sqlbatch [-directory=...] [-datasource=...] [-username=...] [-password=...] [-tags=...] [-from_file=...] [-to_file=...] [-exclude_files=...] [-verbosity=...]

DESCRIPTION

This program will read the given SQL-batch file(s) and execute the logic against an SQL database.

SQL-batch file(s) reside within a certain directory and have the extension ".sb".

OPTIONS

Options have precedens above their counterpart in the configuration. Option though have precedence over their counterparts in a configuration file.

GENERIC OPTIONS

-configfile=path

Path to configuration file. If option is set to '-' then ./sb.conf or sb.conf in SQL-batch directory (-directory) will be used.

-datasource=connectionstring

DBI-datasource connectionstring

-directory=path

Path to directory for SQL-batch-files and default configuration file

-fileextension=ext

Change the fileextension of SQL-batch-files. Default is "sb".

-help

Print a brief help message and exits.

-man

Prints the manual page and exits.

-password=pw

Database-connection password

-username=user

Database-connection username

-verbosity=value

Level of verbosity. Default is 1.

FILTER OPTIONS

-exclude_files=...

Comma separated list of file to exclude in execution

-from_file=startfile.sb

Name of the file to start execution from

-tags=tag1,tag2,tag3

Comma separated list of tags that specifies running the tagged instructions or not

-to_file=endfile.sb

Name of the file where the execution finishes

CONFIGURATION FILE

The configuration file is a JSON-file. Most program options can also be defined via the configuration file.

Example:

    {
        "datasource" : "DBI:RAM:",
        "username" : "user",
        "password" : "pw",
        "force_autocommit" : 1
        "tags" : [ "tag1,"tag2" ]
    }

Extra configuration items

The configuration file though can contain additional other configuration items.

Some of them are predefined as in following list:

force_commit

Enforce transaction committing in Perl DBI.

Other configuration items can be free choosen. They will then be available for initializing any dynamic loaded Perl classes in sql-batch file.

SQL-BATCH FILES

A SQL-batch-file is an UTF-8 encoded textfile. The file contains a sequence of instruction blocks.

Section setup

A instruction indentifier line starts with instruction string and is followed by the instruction arguments

--SQL-- -id=creation tags=setup,!production

The instruction block ends with special ending instruction --END--.

Text between the instruction indentifier line and the instruction block end is called instruction content.

Instruction content can contain various data and information for the instruction to be executed.

Text between the dedicated instruction blocks is not used.

Common section identifier arguments

-end=...

Defines an alternative naming of the section end.

Example:

Instead of --END-- a --MYEND-- can be defined by -end=MYEND

-id=...

An optional specific identifier for the section.

-tags=...

Comma separated list for matching tags to execute the instruction og non-matching tags ("!tag")

SQL-BATCH file example

    # Comment
    --SQL-- -id=sql1
    create table t (a int,b varchar)
    --END--
    an undefined line outside a section
    --INSERT-- -id=fill_table_t -table=t
    'a';'b'
    '1';'2'
    '3';'4'
    --END--
    # Next section runs a delete when the "usertest"-tag is not defined
    --DELETE-- -table=t --id=delete1 -tags=!usertest
    'a';'b'
    '1';'2'
    --END--
    # Next section runs in when "setup"-tag is defined and NOT "production" tag
    --SQL-- -id=sql2 -tags=setup,!production
    create table x (a int)
    --END--

SQL-BATCH instructions

--BEGIN--

Begin a transaction, if autocommit-mode is not enabled

--COMMIT--

Commit a transaction, if autocommit-mode is not enabled

--DELETE--

Deleting table rows that match the instruction content data which is CSV-formattet (with a header column)

Example

    --DELETE-- -table=x
    'a';'b'
    '1';'2'
    ...

Would create a SQL-statement delete from table where a=1 and b=2.

Arguments

--table--

Name of the table where to delete.

--INSERT--

Insert into table rows that match the instruction content data which is CSV-formattet (with a header column)

Example

    --INSERT-- -table=x
    'a';'b'
    '1';'2'
    ...

Would create a SQL-statement insert into x (a,b) values (1,2).

Arguments

--table--

Name of the table where to insert.

--PERL--

Execute Perl code in a Perl-class derived from SqlBatch::InstructionBase

--ROLLBACK--

Rollback a transaction, if autocommit-mode is not enabled

--SQL--

Execute the SQL-statement written into the instruction content.

AUTHOR

Sascha Dibbern (sascha at dibbern.info)

LICENCE

This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

cpanm SqlBatch

perl -MCPAN -e shell
install SqlBatch