Introduction

The H2 Database Engine is a supported JS7 - Database. Management operations on H2 databases can be performed from the command line invoking the H2 Console or H2 Shell.

The following Managaement Script is offered for Unix Shell to perform frequently used operations such as creating backups.

• The script is available for Linux and MacOS® using bash shell. The script acts as a wrapper to classes provided by the H2 Java binary.
• The script terminates with exit code 0 to signal successful execution, with exit code 1 for command line argument errors and other exit codes for non-recoverable errors.
• The script is intended as a baseline example for customization by JS7 users and by SOS within the scope of professional services. Examples make use of H2 release 2.3.232.
  

Prerequisites

The Script requires an H2 database and the H2 Java binary.

Download

Download: h2.sh

Usage

Invoking the script without arguments displays the usage clause:

Usage: h2.sh [Command] [Options] [Switches]

  Commands:
    console                    --classpath [--web-port]
    shell                      --classpath --url [--file|--sql] [--driver-class] [--user] [--password]
    export                     --classpath --url --file [--user] [--password] [--file-versions]
    import                     --classpath --url --file [--user] [--password]

  Options:
    --classpath=<path>         | required: JDBC Driver classpath, default: ./h2-2.3.232.jar
    --url=<url>                | optional: URL or path to database, default: jdbc:h2:./h2/joc
    --driver-class=<name>      | optional: JDBC Driver class, default: org.h2.Driver
    --user=<account>           | optional: database User, default: joc
    --password=<password>      | optional: database password
    --file=<path>              | optional: file for export/import and SQL statements
    --sql=<sql>                | optional: SQL statement
    --web-port=<port>          | optional: console web server port, default: 8082
    --file-versions=<number>   | optional: create versions of backup files on export, default: 1

  Switches:
    -h | --help                | displays usage
    -v | --verbose             | displays verbose output
    -p | --password            | asks for password

Commands

• console
  • Invokes the H2 Console for use with a browser.
  • Users can specify the --web-port option and otherwise use default port 8082.
• shell
  • Invokes the H2 Shell for execution of SQL statements:
    • SQL statements can be specified using the --sql option.
    • Using the --file option a file can be specified holding SQL statements.
    • Without the --sql and --file options the interactive H2 Shell will be used.
• export
  
  • Exports the database specified to a .zip compressed .archive file.
  • The archive file includes the script.sql file holding SQL statements that can be used to recreate the database.
  • When using the --file-versions option, backup files will be stored in versions:
    • Each newly created backup file is appended the suffix -001.
    • An existing backup file with the suffix -001 is renamed to -002. A backup file with the suffix -002 is renamed to the suffix -003 etc.
    • When the number of versions specified with the option is exeeded then the earliest backup file is removed.
• import
  • Imports data from a previous backup. Backup files hold the full backup of the database. To restore a database it is recommended to (re)move the existing H2 database. The database will be recreated on import.
  • The backup file created using the export command must be extracted to the resulting script.sql file.
  • The path to the script.sql file is specified with the --file option.

Options

• --classpath
  • H2 ships from a single Java binary file, for example h2-2.3.232.jar.
  • The option specifies the path to the H2 Java binary file.
• --url
  • Specifies the location of the H2 database. The JS7 - Database using H2 is located with JOC Cockpit from the <jetty-base>/resources/joc/h2 directory. The name of the database is joc.
  • The URL is prefixed with jdbc:h2: followed by the relative or absolute path to the database. If the prefix is omitted, it will be added by the script.
  • Examples:
    • jdbc:h2:/var/sos-berlin.com/js7/joc/resources/joc/h2/joc
    • /var/sos-berlin.com/js7/joc/resources/joc/h2/joc
• --driver
  • Specifies the class name of the H2 JDBC Driver, default: org.h2.Driver.
• --user
  • Specifies the account for the H2 database, default: joc.
• --password
  • Specifies the password used for the account specified with the --user option for the H2 database, default: no password.
  • Password input from the command line is considered insecure. Consider use of the -p switch offering a secure option for interactive keyboard input.
• --file
  • When used with the export command, specifies the path to the .zip archive file that holds the database backup.
  • When used with the import command, specifies the path to the .sql script file used to restore the database.
  • When used with the shell command, specifies the path to the .sql script file that holds SQL statements that should be executed.
• --sql
  • When used with the shell command specifies the SQL statement that should be executed.
  • Consider to apply quotes as in --sql="SELECT COUNT(*) FROMM JOC_VARIABLES;". SQL statements should be terminated by semicolon.
• --web-port
  • When used with the console command specifies the port used by the H2 Console, default: 8082.
• --file-versions
  • When used with the export command, specifies the number of versions for which backup files will be stored

Switches

• -h | --help
  • Displays usage.
• -v | --verbose
  • Displays verbose log output that includes invocation of the H2 Java binary.
• -p | --password
  • Asks the user for interactive keyboard input of the password used for the account specified with the --user option..
  • The switch is used for secure interactive input as an alternative to use of the option --password=<password>.

Exit Codes

• 0: operation successful
• 1: argument errors

Examples

The following examples illustrate typical use cases.

Invoking H2 Console

# invoking the H2 Console for default port 8082
./h2.sh console

# invoking the H2 Console for the port specified using the Java binary from the current directory
./h2.sh console --web-port=8081 --classpath=./h2-2.3.232.jar

Invoking H2 Shell

# invoking the H2 Shell using the Java binary from the current directory
./h2.sh shell --url="/home/sos/h2/joc" --classpath=./h2-2.3.232.jar

Running SQL Statements

# execute SQL statement from the command line
./h2.sh shell --url="/home/sos/h2/joc" --user=joc --sql="SELECT COUNT(*) FROMM JOC_VARIABLES;"

# execute SQL statement from file
./h2.sh shell --url="/home/sos/h2/joc" --user=joc --file=/tmp/run.sql

Exporting and Importing

# export database to .zip archive file that holds the script.sql file
./h2.sh export --url="/home/sos/h2/joc" --user=joc --file=/tmp/database.zip

# export database to .zip archive file with up to 3 versions
./h2.sh export --url="/home/sos/h2/joc" --user=joc --file=/tmp/database.zip --file-versions=3

# step 1: (re)move existing database
mv /home/sos/h2 /home/sos/h2.backup

# step 2: unzip .zip archive file to receive the script.sql file
unzip /tmp/database.zip

# step 3: perform import, the database is automatically created
./h2.sh import --url="/home/sos/h2/joc" --user=joc --file=/tmp/script.sql