This is an utility to run blocks/chunks of sql in a postgresql database to test if they are valid on the block level.

The program will output a brief description which blocks passed and which contained errors. In the case of an error psqlchunks tries to give a detailed description including the SQL code from and around the failing line.

The usecase for this tool is including database modifcations in a continous integraton process to ensure the validity of SQL files before they are deployed.

Usage

Most of the usage parameters are described in the help text the program prints when called with

psqlchunks help

Result:

Usage :  
psqlchunks command [options] files
version: 0.6.0

use - as filename to read from stdin.
Definition of a chunk of SQL:
  A chunk of SQL is block of SQL statements to be executed together,
  and is delimited by the following markers:

  -------------------------------------------------------------
  -- start: creating my table
  -------------------------------------------------------------
  create table mytable (myint integer, mytext text);
  -------------------------------------------------------------
  -- end: creating my table
  -------------------------------------------------------------

  The end marker is optional and may be ommited.
  The shortest marker syntac understood by this tool is:

  ----
  -- start: creating my table
  create table mytable (myint integer, mytext text);


Commands:
  print        print all SQL files and write the formatted output to stdout.
               This command as the following aliasses: echo, concat.
  help         display this help text
  list         list chunks
  run          run SQL chunks in the database
               This will not commit the SQL. But be aware that this tool
               does not parse the SQL statments and will not filter out
               COMMIT statements from the SQL files. Should there be any
               in the files, the SQL WILL BE COMMITED and this tool will
               terminate.

General:
  -F           hide filenames from output

Filters:
  -L [lines]   use only chunks which span the given lines.
               lines is a commaseperated list of line numbers. Example:
               1,78,345
  -I [regex]   match description comments with a regular expression.
               (POSIX extended regular expression, case insensitive)
  -S [regex]   SQL has to match this POSIX extended regular expression,
               also case insensitive.

SQL Handling:
  -C           commit SQL to the database. Default is performing a rollback
               after the SQL has been executed. A commit will only be
               executed if no errors occured. (default: rollback)
  -a           abort execution after first failed chunk. (default: continue)
  -l           number of lines to output before and after failing lines
               of SQL. (default: 2)
  -E           set the client_encoding of the database connection. This
               setting is useful when the encoding of sql file differs
               from the default client_encoding of the database server.

Connection parameters:
  -d [database name]
  -U [user]
  -W           ask for password (default: don't ask)
  -h [host/socket name]

Return codes:
  0            no errors
  1            invalid usage of this program
  2            the SQL contains errors
  3            (internal) database error

Build requirements

• a C++ compiler. GCC and clang are tested.
• PostgreSQL libpq development headers. These are available in the package libpq-dev on Debian-based systems.

Limitations

• No transactions inside the SQL file allowed. This will cause the main transaction to be commited, which may result in an inconsitent state in the database.
• COPY statements may not be executed.
• NOTICES emited by the database server will simply be printed to stdout.