Skip to content

Instantly share code, notes, and snippets.

@jamesnyika

jamesnyika/Datomic-HSQLDB Storage.md

Last active June 14, 2018 16:55
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 3 You must be signed in to fork a gist
  • Save jamesnyika/77c3e281cdfcb3c2452f6eff751bbb17 to your computer and use it in GitHub Desktop.
Save jamesnyika/77c3e281cdfcb3c2452f6eff751bbb17 to your computer and use it in GitHub Desktop.
Download ZIP
Datomic - HSQLDB Storage Setup
Raw
Datomic-HSQLDB Storage.md

Howto - Setting Up an HSQLDB Storage for Datomic

This gist walks you through the steps of setting up a storage for your datomic starter instance to use an HSQLDB instance (running as a standalone server - i do not address embedded, http, in memory or other forms of the server). I am sure the same can be applied to any other SQL based storage so it should be portable to Apache Derby. I am writing this for those who may find it useful in getting this done quickly without spending a week as I did trying to figure out what I was doing wrong.

Part I : Install & Setup Your Storage: the HSQLDB Server

  1. Download the latest version of the database from here
  2. The most important file you will need is the hsqldb.jar file. This is the database. This will need to be in your classpath to be able to run the server.
  3. I then create a bash script for easy execution of the Network Server version of the database as shown below.
#!/usr/bin/env bash

### the best and fastest way to trun this server
 java -cp ./datomic/lib/hsqldb.jar org.hsqldb.server.Server --port 9000 --database.0 file:./data/hsqldb/dedb --dbname.0 de

Let us unpack this a little :

  • java : a call to the JVM runtime. As of the time of writing this gist, I was working on Java8 although Java10 was round the corner
  • -cp : the classpath option to the java command indicating the path to my hsqldb.jar file
  • org.hsqldb.server.Server : this is the class that indicates you are running in Server mode.
  • --port : this is your choice of the port you want your Server to listen to for incoming connection requests
  • --database.0 : the first database it will create
  • file a parameter indicating where you want the datafile for this database.0 to reside. dedb is the final file name in a directory called hsqldb. It actually creates more than 1 file.
  • --dbname.0 an alias for the database. In this case that db was called de

Once executed you should see output similar to the following:

➜  de ./scripts/startHSQLNetworkDB.sh
[Server@2f92e0f4]: Startup sequence initiated from main() method
[Server@2f92e0f4]: Could not load properties from file
[Server@2f92e0f4]: Using cli/default properties only
[Server@2f92e0f4]: Initiating startup sequence...
[Server@2f92e0f4]: Server socket opened successfully in 11 ms.
[Server@2f92e0f4]: Database [index=0, id=0, db=file:./data/hsqldb/dedb, alias=de] opened successfully in 1435 ms.
[Server@2f92e0f4]: Startup sequence completed in 1448 ms.
[Server@2f92e0f4]: 2017-11-23 21:14:49.795 HSQLDB server 2.4.0 is online on port 9000
[Server@2f92e0f4]: To close normally, connect and execute SHUTDOWN SQL
[Server@2f92e0f4]: From command line, use [Ctrl]+[C] to abort abruptly

  1. Your server is now up and available on the network.

  2. Verify connectivity by using an SQL Client to connect to the server. Typical JDBC Strings used are :

    JDBC URL: jdbc:hsqldb:hsql://localhost:9000/de
host: localhost
port: 9000 (this matches the ---port option that you supply when started the server)
user: <whatever you choose>
password: <whatever you choose>
Driver: org.hsqldb.jdbcDriver
Driver jar file is : hsqldb.jar

So the base format is : jdbc:hsqldb:hsql://{host}[:{port}]/[{database}]

Part II : Set Up Your Transactor Properties

Now that we have a database server, we need to configure, set up and execute a datomic transactor to work with the storage. Here is how.

  1. The following is my hsqldb properties file. Works well:
################################################################

protocol=sql
host=localhost
port=4334



################################################################
# See http://docs.datomic.com/storage.html

license-key=<put your license key here. No quotes or other marks around it>



################################################################
# See http://docs.datomic.com/storage.html


### HSQLDB is case sensitive
sql-url=jdbc:hsqldb:hsql://localhost:9000/DE
sql-user=datomic
sql-password=datomic

# The Postgres driver is included with Datomic. For other SQL
# databases, you will need to install the driver on the
# transactor classpath, by copying the file into lib/,
# and place the driver on your peer's classpath.
sql-driver-class=org.hsqldb.jdbc.JDBCDriver

# Driver specified params, as semicolon-separated pairs.
# Optional
# sql-driver-params=<param1=val1&param2=val2...>

# The query used to validate JDBC connection.
# Optional
sql-validation-query="SELECT * FROM DE.DATOMIC_KVS;"



################################################################
# See http://docs.datomic.com/capacity.html


# Recommended settings for -Xmx4g production usage.
memory-index-threshold=32m
memory-index-max=256m
object-cache-max=512m

# Recommended settings for -Xmx1g usage, e.g. dev laptops.
#memory-index-threshold=32m
#memory-index-max=512m
#object-cache-max=256m



## OPTIONAL ####################################################


# Set to false to disable SSL between the peers and the transactor.
# Default: true
# encrypt-channel=true

# Data directory is used for dev: and free: storage, and
# as a temporary directory for all storages.
# data-dir=data

# Transactor will log here, see bin/logback.xml to configure logging.
# log-dir=log

# Transactor will write process pid here on startup
# pid-file=transactor.pid



## OPTIONAL ####################################################
# See http://docs.datomic.com/storage.html
# Memcached configuration.

# memcached=host:port,host:port,...
# memcached-username=datomic
# memcached-password=datomic



## OPTIONAL ####################################################
# See http://docs.datomic.com/capacity.html


# Soft limit on the number of concurrent writes to storage.
# Default: 4, Miniumum: 2
# write-concurrency=4

# Soft limit on the number of concurrent reads to storage.
# Default: 2 times write-concurrency, Miniumum: 2
# read-concurrency=8



## OPTIONAL ####################################################
# See http://docs.datomic.com/aws.html
# Optional settings for rotating logs to S3
# (Can be auto-generated by bin/datomic ensure-transactor.)

# aws-s3-log-bucket-id=



## OPTIONAL ####################################################
# See http://docs.datomic.com/aws.html
# Optional settings for Cloudwatch metrics.
# (Can be auto-generated by bin/datomic ensure-transactor.)

# aws-cloudwatch-region=

# Pick a unique name to distinguish transactor metrics from different systems.
# aws-cloudwatch-dimension-value=your-system-name



## OPTIONAL ####################################################
# See http://docs.datomic.com/ha.html


# # The transactor will write a heartbeat into storage on this interval.
# A standby transactor will take over if it sees the heartbeat go
# unwritten  for 2x this interval. If your transactor load leads to
# long gc pauses, you can increase this number to prevent the standby
# transactor from unnecessarily taking over during a long gc pause.
# Default: 5000, Miniumum: 5000
# heartbeat-interval-msec=5000

The most important points here are :

  • If you are using datomic-pro or datomic-starter you MUST put your license key in at the top
  • Set the correct driver for your storage : sql-driver-class=org.hsqldb.jdbc.JDBCDriver
  • Set your sql-url value to the JDBC url used to access the storage engine. You can get this from step 1. It will look like this : sql-url=jdbc:hsqldb:hsql://localhost:9000/DE
  • Note the protocol (sql), port (4334) and host (localhost) for the transactor. Want to change these ? change them at the top of the file. This is where your peer is going to automatically connect to .
  1. For convenience, create a bash script to run the transactor :
#!/bin/bash
## modify the path to the transactor and your hsql properties file. I used an absolute path but you can try relative ones too
./datomic/bin/transactor /Volumes/PRJ/prj/de/config/de-hsqldb.properties
  1. Now your transactor properties are set up. But before you fire up the transactor, you need to make sure a table and users exist on the database server.

Part III : Prepare the Storage for the transactor

This step can be performed when you set up the database but essentially you need to create the chosen user (datomic) for our datomic database as well as the table where the data will reside.

Here is the script to do it

CREATE SCHEMA "DE";

SET DATABASE DEFAULT INITIAL SCHEMA DE;

CREATE USER "datomic" password 'datomic' ADMIN;

CREATE TABLE DE.DATOMIC_KVS
(
id varchar(640) NOT NULL,
rev integer,
map CLOB,
val BLOB,
PRIMARY KEY (id)
)

GRANT ALL ON DE.DATOMIC_KVS TO "datomic";

SELECT * FROM DE.DATOMIC_KVS

Key points here to note are :

  • Create a schema and make it the default schema
  • create your user account to access and manage the database. You do not have to make the user admin. But you will grant them permissions later on. On HSQLDB specifically, it converts all usernames and passwords not in double or single quotes into uppercase. To prevent that ensure that username is in DOUBLE quotes and the password is in SINGLE quotes. It's a thing!
  • create the datomic key values table. Important here is that values is a binary large object type. The key needs to be text/or character large object in type. You will need to adjust the column types for different databases.
  • grant all permissions on the database table (note the schema is used in the name) to our user. Some people prefer to create a role then assign the permissions to the role then role to the user. Either one works.
  • Finally, verify the table exists.

Part IV : Fire off the transactor!!

You should now be able to execute your transactor. First make sure the storage/database server is alerady running and then kick off the transactor script given to you earlier at the end of Part II. You should see output like so:

➜  de ./scripts/startTransactor.sh
Launching with Java options -server -Xms1g -Xmx1g -XX:+UseG1GC -XX:MaxGCPauseMillis=50
Starting datomic:sql://<DB-NAME>?jdbc:hsqldb:hsql://localhost:9000/DE?user=datomic&password=datomic, you may need to change the user and password parameters to work with your jdbc driver ...
System started datomic:sql://<DB-NAME>?jdbc:hsqldb:hsql://localhost:9000/DE?user=datomic&password=datomic, you may need to change the user and password parameters to work with your jdbc driver

You should see a new row of data appear in the table datomic_kvs on the stoage engine. Rerun this query to verify that you see a new row SELECT * FROM DE.DATOMIC_KVS

Part V : Configure your peers to connect appropriately

WARNING: IF YOU ARE ON A LAPTOP AND YOU LET IT SLEEP THE TRANSACTOR WILL FAIL AND NOT SURVIVE. YOU WILL HAVE TO RESTART IT. It's also a thing!

Now you have a running storage, running and connected transactor.

  1. Ensure your project is using the correct dependency import. IT IS CRITICAL that you use the same version number in your dependency as that of the datomic release you kicked of your transactor with before. I usually find this by looking at the files in the home directory of my datomic release (e.g. CHANGES.md - where I picked the most recent line value "Changed in 0.9.5385 ")
  2. Your dependency with then be : [com.datomic/datomic-pro "0.9.5385"] Always use com.datomic/datomic-pro for starter or pro releases and free for the dev or free instances. I then put the version number i pulled from my CHANGES.md file. It must be in double quotes. I usually also exclude a bunch of things. Here is my full dependency in my project.clj file
;;datomic versions matter. Match the version here to your datomic server. Makers it easier.
               [com.datomic/datomic-pro "0.9.5385" :exclusions [joda-time
                                                                 org.slf4j/slf4j-nop
                                                                 org.slf4j/slj4j-log4j12]]
  1. You also need to make sure you have added the jdbc driver to your project or else your project(peer) cannot connect to the storages.
;;JDBC Drivers
                 ;;[org.apache.derby/derby "10.13.1.1"]
                 [org.hsqldb/hsqldb "2.4.0"]

Here you see also a commented out example of adding an apache derby driver if you were using Derby.

So here is the final complete project.clj (with uncessary stuff commented out)

(defproject de "1.0"
 :description "This is my project description"
 :url "http://example.com/FIXME"
 :license {:name "None - All Rights Reserved"
 ;; this allows me to automatically download my datomic-starter dependencies.
           :url "http://example.com/allrightsreserved"}
 :repositories {"my.datomic.com" {:url "https://my.datomic.com/repo"
                                  :creds :gpg}}
 :dependencies [[org.clojure/clojure "1.9.0-RC1"]
                [org.clojure/tools.logging "0.3.1" :exclusions [javax.mail/mail
                                                                javax.jms/jms
                                                                com.sun.jdmk/jmxtools
                                                                com.sun.jmx/jmxri]]
                ;;datomic versions matter. Match the version here to your datomic server. Makers it easier.
                [com.datomic/datomic-pro "0.9.5385" :exclusions [joda-time
                                                                  org.slf4j/slf4j-nop
                                                                  org.slf4j/slj4j-log4j12]]
                [ch.qos.logback/logback-classic "1.1.3"]
                [clj-time "0.9.0"]
                ;;JDBC Drivers
                ;;[org.apache.derby/derby "10.13.1.1"]
                [org.hsqldb/hsqldb "2.4.0"]


                ]
 :main ^:skip-aot de.core
 :target-path "target/%s"
 :profiles {:uberjar {:aot :all}}
 )

You can now fire up your project in a repl and connect to the database and perform queries.

Part VI : Fire off the Datomic Console to actually see your data as you enter it or to run queries.

The Datomic Console comes for free with your Datomic starter instance and is very useful

  1. Set up a bash script to fire it off with this content
#!/usr/bin/env bash

## Using an HSQLDB storage engine
## KEY THING TO NOTE: THE <DB-NAME> is missing from the URI string. This is the ONLY change we made to it.
../datomic/bin/console -p 8080 de 'datomic:sql://?jdbc:hsqldb:hsql://localhost:9000/DE?user=datomic&password=datomic'

VERY IMPORTANT: Notice that I did not put anything after the '....sql//'. The original URI actually has an arbitrary name you give the db. you MUST omit it here.

  1. Once executed, navigate a brower to http://localhost:8080/browser and you should be able to see the client and select your database at the top. This will then load up any datom nodes you might have.

You're done! Congratulations and let me know if any of this fails on you so that I can improve it.

Cheers

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment