"No one is harder on a talented person than the person themselves" - Linda Wilkinson ; "Trust your guts and don't follow the herd" ; "Validate direction not destination" ;

July 16, 2013

HSQLDB - Getting Started

This post is on learning HSQLDB. HSQLDB is Rdbms db written in java
  1. Download HSQLDB from link 
  2. Getting started guide useful from link 
  3. Java is already installed on my laptop. Adding JAVA_HOME , PATH and CLASSPATH provided in link
Lets get started and try out some basic examples
Step 1 - To Start and create a DB
  
From command line
 
 Command text -  C:\Program Files\Java\jre7\bin>java.exe -cp "E:\HSQLDB\hsqldb-2.3.0\hsqldb-2.3.0\hsqldb\lib\hsqldb.jar" org.hsqldb.server.Server --database.0 file:mydb --dbname.0 xdb
Step 2 - DB files would be created as below
 
 
Step 3 - Opening the DB Manager
 
 
Command text - C:\Program Files\Java\jre7\bin>java.exe -cp "E:\HSQLDB\hsqldb-2.3.0\hsqldb-2.3.0\hsqldb\lib\hsqldb.jar" org.hsqldb.util.DatabaseManagerSwing
 
Command text - Connecting to DB Instance
jdbc:hsqldb:hsql://localhost/xdb

Step 4 - Basics on Table Creation

The three types of persistent tables are MEMORY tables, CACHED tables and TEXT tables
  • Memory Tables - Data stored in Files
  • Cached Tables - Cached detail remains in memory not in File
  • Text Files - Use CSV Supported Files
Step 5 - Table Creation
 
CREATE TABLE PUBLIC.TEST_TABLE
 (COL1 INTEGER NOT NULL,
 COL2 VARCHAR(25) NOT NULL,
 PRIMARY KEY (COL1))
 
Step 6 - Load Data and Select Query
 
INSERT INTO "PUBLIC"."TEST_TABLE"( "COL1", "COL2" ) VALUES ( 10, 'Test')
INSERT INTO "PUBLIC"."TEST_TABLE"( "COL1", "COL2" ) VALUES ( 20, 'Ram')
INSERT INTO "PUBLIC"."TEST_TABLE"( "COL1", "COL2" ) VALUES ( 30, 'Raj')
INSERT INTO "PUBLIC"."TEST_TABLE"( "COL1", "COL2" ) VALUES ( 40, 'Ravi')
INSERT INTO "PUBLIC"."TEST_TABLE"( "COL1", "COL2" ) VALUES ( 50, 'Raja')
 
SELECT * FROM "PUBLIC"."TEST_TABLE"
 
Step 7 - MVCC Basics
 
Reading only committed data, Driven by isolation level settings. More details in link

Supported Isolation levels
  • SET TRANSACTION READ ONLY
  • SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
  • SET TRANSACTION READ WRITE, ISOLATION LEVEL READ COMMITTED 

Step 8 - MVCC Example

Start Two Instances of Data Manager

Window1 - Run below query

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET DATABASE TRANSACTION CONTROL MVCC;
set autocommit false;
INSERT INTO "PUBLIC"."TEST_TABLE"( "COL1", "COL2" ) VALUES ( 10, 'Test')
INSERT INTO "PUBLIC"."TEST_TABLE"( "COL1", "COL2" ) VALUES ( 20, 'Ram')
INSERT INTO "PUBLIC"."TEST_TABLE"( "COL1", "COL2" ) VALUES ( 30, 'Raj')
INSERT INTO "PUBLIC"."TEST_TABLE"( "COL1", "COL2" ) VALUES ( 40, 'Ravi')
INSERT INTO "PUBLIC"."TEST_TABLE"( "COL1", "COL2" ) VALUES ( 50, 'Raja')
commit
INSERT INTO "PUBLIC"."TEST_TABLE"( "COL1", "COL2" ) VALUES ( 70, '7Ravi')
INSERT INTO "PUBLIC"."TEST_TABLE"( "COL1", "COL2" ) VALUES ( 80, '8Raja')
 
Window2 - Run below query

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM PUBLIC.TEST_TABLE;

The Result from Window2 will not include 70, and 80 the uncommitted records


Step 9 - Explore the system created files - mydb.script, mydb.log file using notepad, You would see details on DB Settings and properties

References

Happy Learning!!!