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 Operation Script is offered for Unix Shell to perform frequently used operations such as creating backups by JS7 users.

• 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: operate-h2.sh

Usage

Invoking the script without arguments displays the usage clause:

...

Usage: operate-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 with --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
./operate-h2.sh console

# invoking the H2 Console for the port specified using the Java binary from the current directory
./operate-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
./operate-h2.sh shell --url="/home/sos/h2/joc" --classpath=./h2-2.3.232.jar

Running SQL Statements

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

# execute SQL statement from file
./operate-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
./operate-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
./operate-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
./operate-h2.sh import --url="/home/sos/h2/joc" --user=joc --file=/tmp/script.sql