Editing The GoCD H2 DB Engine with H2 Shell

Posted on Wed 18 November 2015 in ci

After encountering a few anomalies using Go by Thoughtworks ("GoCD"), I wanted to take a look at the DB under the hood.

Unfortunately, Thoughtworks wants you to pay at least $15,000 for their PostgreSQL ORM (with a 250 pipeline limit). So instead, understanding the included H2 database engine seemed more attractive. Call me crazy.

Step 1: Get the Right Tools

Install Java (at least 1.7).
Get a copy of the $SERVER_WORK_DIR/db/h2db/cruise.h2.db file.
- Alternatively, shut down your GoCD instance and navigate to $SERVER_WORK_DIR/db/h2db.
Get a copy of h2*.jar, available at $SERVER_WORK_DIR/work/jetty-0.0.0.0-8153-cruise.war-_go-any-/webapp/WEB-INF/lib. This jar contains the actual tool we're going to invoke.

Step 2: Run Them

Navigate to the directory containing the h2*.jar and run the following:

java -cp h2*.jar org.h2.tools.Shell

This will start the H2 Shell, which will prompt you about connection information:

Welcome to H2 Shell 1.3.168 (2012-07-13)
Exit with Ctrl+C
[Enter]   jdbc:h2:~/test
URL

Specify the full path to the cruise.h2.db file, but leave off the .h2.db extension.

If you are using the installed, stopped instance of GoCD and are using the default $SERVER_WORK_DIR directory, then this is what you would type in for the URL:

URL       jdbc:h2:/var/lib/go-server/db/h2db/cruise

Accept the driver (org.h2.Driver) and username (sa) defaults, and leave the password blank (you will need to hit Enter twice):

[Enter]   org.h2.Driver
Driver
[Enter]   sa
User
[Enter]   Hide
Password
Password

If all goes well, you should see output similar to the following:

Connected
Commands are case insensitive; SQL statements end with ';'
help or ?      Display this help
list           Toggle result list / stack trace mode
maxwidth       Set maximum column width (default is 100)
autocommit     Enable or disable autocommit
history        Show the last 20 statements
quit or exit   Close the connection and exit
sql>

Step 3: Profit

You are now free to view and update the H2 database:

sql> show tables;
TABLE_NAME                     | TABLE_SCHEMA
AGENTS                         | PUBLIC
ARTIFACTPLANS                  | PUBLIC
ARTIFACTPROPERTIESGENERATOR    | PUBLIC
BUILDCAUSEBUFFER               | PUBLIC
BUILDS                         | PUBLIC
BUILDSTATETRANSITIONS          | PUBLIC
CHANGELOG                      | PUBLIC
ENVIRONMENTVARIABLES           | PUBLIC
GADGETOAUTHACCESSTOKENS        | PUBLIC
GADGETOAUTHAUTHORIZATIONCODES  | PUBLIC
GADGETOAUTHCLIENTS             | PUBLIC
MATERIALS                      | PUBLIC
MODIFICATIONS                  | PUBLIC
MODIFIEDFILES                  | PUBLIC
NOTIFICATIONFILTERS            | PUBLIC
OAUTHAUTHORIZATIONS            | PUBLIC
OAUTHCLIENTS                   | PUBLIC
OAUTHTOKENS                    | PUBLIC
PIPELINELABELCOUNTS            | PUBLIC
PIPELINEMATERIALREVISIONS      | PUBLIC
PIPELINES                      | PUBLIC
PIPELINESELECTIONS             | PUBLIC
PLUGINS                        | PUBLIC
PREFFERED                      | PUBLIC
PROPERTIES                     | PUBLIC
RESOURCES                      | PUBLIC
SERVERBACKUPS                  | PUBLIC
STAGEARTIFACTCLEANUPPROHIBITED | PUBLIC
STAGES                         | PUBLIC
USERS                          | PUBLIC
_BUILDS                        | PUBLIC
_STAGES                        | PUBLIC
(32 rows, 11 ms)

DISCLAIMER: I recommend NOT doing any of this unless you take a backup of cruise.h2.db first.