HSQLDB a DBMS written in Java. Install it and do it working in Linux

• ...
• Original code, based on in-depth study of database theory and the SQL Standard
• Ahead of other open-source solutions in supported SQL features
• The fastest overall open-source SQL implementation for small and medium sized databases
• Three transaction control models, including lock based and MVCC models
• Fully multithreaded
• Compact code footprint
• ...

"Install" HSQLDB(ever the latest release) in Linux

HSQLDB (also known as HyperStructuredSQL or HyperSQL ---its old name was Hypersonic or HSQL---) it's a portable RDBMS writting 100% in Java. It can be embedded with your application as well as can be used separately(offer Embedded and server modes).

So as i just say HSQLDB can also run as a database server, handling connections from different JVMs, and also as a purely in-memory engine. It has a JDBC driver and supports a large subset of SQL-92 and SQL:2008 standards.^[1]

It is a a fast,^[2]  and very small (around 1100 kilobytes in version 2.0) database engine that supports almost all features of a standard database system(offers both in-memory and disk-based tables). 

It comes with a small jar file hsqldb.jar (This includes the JDBC driver and the database engine) that can be found in:

hsqldb_x_y_z/hsqldb/lib 

folder. The hsqldb.jar  file must be put on the application classpath.

Installation

1. Start by downloading the latest stable release, from SourceForge. The file to look for is in a directory called hsqldb_x_y_z and is named hsqldb-x.y.z.zip (Actually it's hsqldb-2.1.0.zip .  Possibly in future you can find it with a higher version number).
2. Unzip the archive in your home directory
3. You now have a directory called hsqldb-2.1.0 containing a directory hsqldb containing all files related to the DBMS. You will see the following contents as in Fig. above
4.  Here "bin" directory contains some Windows(only) batch files(*.bat). 

Now we must "translate" Windows batch files(*.bat) in hsqldb_x_y_z/hsqldb/bin into the corresponding Linux shell scripts(for parallels between *.bat and *.sh  take a look in Advanced Bash-Scripting Guide). Let's go open our editor(i.e Geany) and do the following substitutions in the Windows batch files:

• *.bat -->  *.sh

• "\"      -->  "/"
• "%"    -->  "$"
• "@"    -->  "set +v" 
• don't forget to control the correct conversion of line endings "CR/LF" --> "LF"  (in Geany go to Document > Set line endings > click on Convert and set to LF(Unix) )
• rename the file filename.bat  -->  filename.sh

Let's look in the content of the runManagerSwing.sh  file (-- a Swing based GUI client(tool). For now the only one of interest for us) and control if all goes well:

runManagerSwing.sh(Notice that runManager(AWT based) do the same work although is weirdly to view)

cd ../data
set +v
java -classpath ../lib/hsqldb.jar org.hsqldb.util.DatabaseManagerSwing $1 $2 $3 $4 $5 $6 $7 $8 $9

Now i will  describe how got HSQLDB working:

How to run a standalone HyperSQL (hsqldb) Server

The most common use of HyperSQL is for in-memory operations, but could happen that persistent standalone server is required. Exist three ways (But here i examine two of them) to start the HSQLDB as a standalone server:

1. Starting standalone HyperSQL with command line arguments: That's the best and fastest way of running a standalone hsqldb server:

   harrykar@harrysas:~/Activities/DBMS/hsqldb-2.1.0/hsqldb$ java -classpath lib/hsqldb.jar org.hsqldb.server.Server --database.0 file:hsqldb/hemrajdb --dbname.0 testdb

2. Starting standalone HyperSQL with configuration file:
1. hsqldb.jar ( The most important file of the hsqldb RDBMS system ) in hsqldb_x_y_z/hsqldb/lib must be in our classpath. 
2. Now check whether java in the (execution command) path or not. Just execute javac(included in the Java Development Kit (JDK) ) command and wait. If path has been set to java, then it will display some textual output showing command options.
3. Now (in hsqldb directory) prepare a file named server.properties like this(Note that is only an example). We want create our DB in hsqldb directory that will contain four hemrajdb.* files after server execution:

   server.database.0=file:hsqldb/hemrajdb
   server.dbname.0=testdb

1. Start the server. As below command executes you will see some output and a new directory will be created into current path named hsqldb in which you will find the following contents: hemrajdb.lck, hemrajdb.log, hemrajdb.tmp, hemrajdb.script   hemrajdb.properties. 
   

   harrykar@harrysas:~/Activities/DBMS/hsqldb-2.1.0/hsqldb$ java -classpath lib/hsqldb.jar org.hsqldb.server.Server
   [Server@1c691f36]: [Thread[main,5,main]]: checkRunning(false) entered
   [Server@1c691f36]: [Thread[main,5,main]]: checkRunning(false) exited
   [Server@1c691f36]: Startup sequence initiated from main() method
   [Server@1c691f36]: Loaded properties from [/home/harrykar/Activities/DBMS/hsqldb-2.1.0/hsqldb/server.properties]
   [Server@1c691f36]: Initiating startup sequence...
   [Server@1c691f36]: Server socket opened successfully in 8 ms.
   [Server@1c691f36]: Database [index=0, id=0, db=file:hsqldb/hemrajdb, alias=testdb] opened sucessfully in 589 ms.
   [Server@1c691f36]: Startup sequence completed in 598 ms.
   [Server@1c691f36]: 2011-05-08 22:19:06.930 HSQLDB server 2.1.0 is online on port 9001
   [Server@1c691f36]: To close normally, connect and execute SHUTDOWN SQL
   [Server@1c691f36]: From command line, use [Ctrl]+[C] to abort abruptly

2. Once you created the database then you have to run the command in the same way as before with some extra arguments to connect with existing database e.g.

   harrykar@harrysas:~/Activities/DBMS/hsqldb-2.1.0/hsqldb$ java -classpath lib/hsqldb.jar org.hsqldb.server.Server --database.0 file:hsqldb/hemrajdb --dbname.0 testdb
   [Server@6ef0eed6]: [Thread[main,5,main]]: checkRunning(false) entered
   [Server@6ef0eed6]: [Thread[main,5,main]]: checkRunning(false) exited
   [Server@6ef0eed6]: Startup sequence initiated from main() method
   [Server@6ef0eed6]: Loaded properties from [/home/harrykar/Activities/DBMS/hsqldb-2.1.0/hsqldb/server.properties]
   [Server@6ef0eed6]: Initiating startup sequence...
   [Server@6ef0eed6]: Server socket opened successfully in 3 ms.
   [Server@6ef0eed6]: Database [index=0, id=0, db=file:hsqldb/hemrajdb, alias=testdb] opened sucessfully in 583 ms.
   [Server@6ef0eed6]: Startup sequence completed in 587 ms.
   [Server@6ef0eed6]: 2011-05-09 00:17:54.641 HSQLDB server 2.1.0 is online on port 9001
   [Server@6ef0eed6]: To close normally, connect and execute SHUTDOWN SQL
   [Server@6ef0eed6]: From command line, use [Ctrl]+[C] to abort abruptly

3. Starting standalone HyperSQL with Java Code: I refer the thrid way of starting a standalone HyperSQL server through java code here only for completeness sake.
   

   HsqlProperties p = new HsqlProperties();
   p.setProperty("server.database.0","file:/opt/db/accounts");
   p.setProperty("server.dbname.0","an_alias");
   // set up the rest of properties
   Server server = new Server();
   server.setProperties(p);
   server.setLogWriter(null); // can use custom writer
   server.setErrWriter(null); // can use custom writer
   server.start();

Access the Database trough a GUI client

1. Now  you need to run ./runManagerSwing.sh file from /bin directory. Before start you have to enter some connection configurations and then press ok button to connect to the graphical user interface to database.
1. You can enter also the following:

   Type: HSQL Database Engine Standalone
   Driver: org.hsqldb.jdbcDriver
   URL: jdbc:hsqldb:file:/path/to/your/app/demobase
   User: SA

   
2. Now create a table and put some data into it (click Execute-button after copying the following to the text field):

   CREATE TABLE Bookmarks (title VARCHAR(50), url VARCHAR(255));
   INSERT INTO Bookmarks (title, url) VALUES ('Java Technology', 'http://java.sun.com/');
   INSERT INTO Bookmarks (title, url) VALUES ('HSQLDB 100% Java Database', 'http://hsqldb.sourceforge.net/');
   INSERT INTO Bookmarks (title, url) VALUES ('Apache Jakarta Tomcat', 'http://jakarta.apache.org/tomcat/');
   CHECKPOINT

3. The first four lines are standard SQL. The final line is HSQLDB specific. To persist changes to your file after modifications, use the CHECKPOINT command.
4. You can test that it worked, by executing this(you must get the Bookmarks table ordinated by title column as you can see below in the screenshot):

   SELECT * FROM Bookmarks ORDER BY title;

5. Exit the manager.

Now you have almost four files /path/to/your/app (that represent our DB):

• demobase.log, 
• demobase.properties and 
• demobase.script (and if you didn't exit the manager, you'll also have a demobase.lck file). 

How-to: changing user password on HSQL

In order to connect to any database you need to provide four properties:

1. driver class
2. URL to database
3. user name
4. user password

For HSQL it might look something similar to this:

driver=org.hsqldb.jdbcDriver
url=jdbc:hsqldb:file:app.db;shutdown=true
username=sa
password=

The default user ‘sa’ has empty password. This works fine as long as you don’t need to change the password for the default user.  But there are times when you need to change it. For instance Glassfish Application Server won’t let you create a data source with empty password. So the question remains – how to change the password of embedded database?
You have to log to the database as “SA” user and run below SQL script:

ALTER USER sa SET PASSWORD ''put-here-your-password';

There are many ways to do it. I will show you one of them:

• Open your command line, go to folder where you keep your hsql.jar and run:

  java -jar url=jdbc:hsqldb:file:app.db;shutdown=true -sql “ALTER USER sa SET PASSWORD ‘put-here-your-password’;”

• This yet still won’t work as system will inform you that you have not entered credentials needed for database manipulation.

  Failed to retrieve connection info for database ‘jdbc.hsqldb:file:app.db’: Please set up authentication file ‘sqltool.rc’

• You need to create file sqltool.rc in the same folder where you keep your hsql.jar. This file will keep entry configuration for your database. Insert text below to your sqltool.rc file:

  urlid conn1
  url jdbc:hsqldb:file:C:/hsqldbs/app.db;shutdown=true
  username sa
  password 

• Note that we gave this entry an id (urlid) called conn1. Now we can execute our SQL once again this time adding entry point urlid at the end:

  java -jar url=jdbc:hsqldb:file:app.db;shutdown=true -sql “ALTER USER sa SET PASSWORD ‘put-here-your-password’;” conn1

• This time the connection will succeed and password of the SA user will be change.

A bit of details 

HSQLDB does not support full ACID transactions;

JDBC URLs

JDBC URLs for HSQLDB look like the following:

• jdbc:hsqldb:file:filepathname : File-persistence
• jdbc:hsqldb:hsql:DB_NAME: database server
• jdbc:hsqldb:mem:DBname: in-memory only

By default, for file-persistence, the user name is "sa" and the password is "". See the HSQLDB documentation for further details.The database JDBC driver is class org.hsqldb.jdbcDriver. 
--------------------------------------------------
Todo: SqlTool HSQLDB's  CLI

Resources

• New Features in HyperSQL 2.0 Vs 1.8.x
• Features Summary
• Getting Started
• Søren's Blog
• Running and Using HyperSQL
• HSQLDB Beginner's Guide
• info1 (thread)