Using H2 as a teaching tool

This post would be about the usage of H2 database for the purpose of using it as an educational tool for Database Systems lessons and classes.

Recently, I have seen institutions that offers Database courses and they would use Oracle as their DBMS for educating students in Computer Science and Information Technology courses. In fact, many schools and institutions uses Oracle. The sad thing is few of these institutions are creative or innovation enough to find a good tool other than Oracle to teach SQL and Database subjects.

The most recent encounter with a school / institution (which I shall not name) uses Oracle as well. The students would either use SSH or Oracle SQLDeveloper to access the school's educational Oracle Database.

Because of the problems of allowing student's CREATE, INSERT, UPDATE and DELETE statements that may affect the Database as a whole, all of the students only had 'SELECT' privileges enabled. When the students had to test out their hands on the CREATE, INSERT, UPDATE and DELETE statements, there would be the usual error for the lack of privilege. How is a student going to test out his or her CREATE, INSERT, UPDATE and DELETE statements on the Oracle database if they couldn't do anything except SELECT ?

Mind you, not even view creation is allowed. That is how restricted the privileges are.

Some schools would advocate the students to install the free Oracle (version 9, 10, 11)G of the free database and it would be gigabytes of space wasted and hours long of installation. It would be tedious and we all know setting up Oracle database is a pain in the ass. The good thing is that since the database is setup on the student's side, they have the freedom to test out their CREATE, INSERT, UPDATE and DELETE statements. They would be given SQL scripts to copy into their databases to setup the database if anything goes wrong.

This is where H2 database steps in to save the day for students and teachers.

H2 database is only slightly more than 1 MB. How big can that be ? All you need is to unzip the zipped H2 compressed file to the Desktop or somewhere you want to keep it and then go to the '/bin' file and a double-click on the 'H2-(version).jar' file and it would simply load up the H2 web based console with NO HASSLE at all.

Now, this, as I have described above, is so easy. How difficult can using H2 be ?

Many schools and institutions may not want to give student SQL scripts because they include SQL statements which the students may reference for their assignments and even copy them wholesale. This is not a problem with H2. 

According to H2 database's  online help: "http://h2database.com/html/tutorial.html?highlight=CSVREAD&search=CSVREAD#firstFound", under the subheading of "Importing Data from a CSV File", you could create tables and import data using CSV File (but lacking of the constraints and data types) or you could simply just import data.

The strategy I would propose is for the teacher to hand out an SQL script file that handles the creation of tables and setting of constraints and also a CSV file that contains the sample data for the tables. The SQL file and CSV file would be placed next to each other. The student simply needs to open the SQL file and edit the location of the CSV file within the SQL statement that reads the CSV file to populate the database tables and when all is done, the student uses the H2 command 'RUNSCRIPT FROM (filepath of SQL file);' and H2 would run the SQL script file and automatically create the tables and populate the data.

Below is a set of SQL commands on H2 database to prove my point. Use the H2 Console (or double click the H2-.jar file).

-- Comment: Create the test table and insert values --
CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255));
INSERT INTO TEST VALUES (1, 'JOHN');
INSERT INTO TEST VALUES (2, 'MENA');

-- Comment: Script out the test table to a CSV file --
CALL CSVWRITE('/home/linux_user/Desktop/test.csv', 'SELECT * FROM TEST');

-- Comment: Go to the location and check if the CSV file is successfully made --

-- Comment: Delete table for the demo --
DROP TABLE TEST;

-- Comment: Creating tables using your own SQL statement and then reading the data from CSV file to populate table--
CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))
    AS SELECT * FROM CSVREAD('/home/linux_user/Desktop/test.csv');

As you can see, the H2 database provides nifty functions as above to setup the database and tables without exposing INSERT, UPDATE, DELETE statements that students may copy directly into their assignment sheets for submission.

Most databases have very horrendous interactive consoles or methods of interaction that really put people off but H2 database provides a very user friendly web based GUI for interaction with the databases. If using of GUI is too 'noobie', then H2 also provide terminal consoles too.

Lastly, H2 database is FREE as in you can modify the source codes, the source codes are freely available and you don't have to pay a single cent to get the source codes or the executable files. 

The forum for H2 database (http://groups.google.com/group/h2-database?pli=1) is a very friendly place and answers are quickly answered and handled. Thomas Mueller, the original and lead developer of H2 database is always hard at work trying to improve H2 database and it's user experience.

Do consider using H2 database as an alternative teaching tool for Database related courses and subjects in educational institutions and schools. I am sure it would not only improve the user experience for the students but for the teachers too.

Frustration of H2 consumers

Many people have took the beauty of H2 database for granted and have asked for designs like compatibilities for Oracle and MySQL and other databases. 

Many have forgotten that H2 database IS H2 database... and not MySQL, Oracle .... whatever they are asking for. H2 is such a well thought out design, that people have simply been asking for ridiculous feature requests.

For consumers of H2 database, DO NOT EXPECT TO BE ABLE TO USE THE FULL ORACLE OR MYSQL OR OTHER SUPPORTED DATABASES SYNTAX.

Another down side of supporting too many database syntax other than from the standards or from H2's own syntax, is the slow bloating of H2. 

H2's goal is to be as lightweight and a jar file of around 1 MB, not some totally bloated... everything can do database system.

I hope consumers can have a consideration for H2 database and it's birlliant developers who have made such a fascinatingly powerful database.

Yes, things do break and can be frustrating (when using H2) but this is the same for all other software applications. Some impatient consumers would simply swarm to the H2 database forum and switch on their Capslock and type a message for help expecting an immediate fix.

Rather than complaining, consumers should actively try to support H2 and it's team by helping out with creating patches if they think they are up to it or submit some useful source codes they hope that could be included into H2 releases in the future.

I have personally done some hacking with H2 to get what I want from it since I acknowledge that H2 cannot always fulfil the features I want.

I hope H2 consumers could be patient with the H2 team and not be unreasonable with feature requests while always keeping in mind H2's goal (I don't mean to stop everyone from submitting their feature requests, but be tactful and considerate).

H2 simplicity

The H2 database start off is purely brainless. You just click on the H2.jar or something like that and you are off, it launches an internal server (web and servlet server and database server) and your default web browser just pops up a web servlet for you to connect to your database and you are in. You manipulate tables and data using the servlets and forms. I love the pure simplicity and brainlessness of using H2.

The build of H2 jars can be a bit complex since you need to setup JAVA_HOME and paths here and there but these are the fault of Sun Java because they don't automatically provide these setups and leave users hanging there and to figure out for themselves. Otherwise, when all thes eenvironment values are up, the building of different H2 jarfiles for different uses are purely brainless too. Just open a console and 'cd' to the 'bin' folder and type 'build xxx' where xxx is whatever kind of jar you want to build. Simple isn't it ?

If you like consoles to access databases, H2 also provides console (main jar rather than the rebuild jarSmall version). The console is as simple as just 'java -jar h2.jar' . What else can you expect ?

Look at other databases... do they provide such simplicity and luxury ? Java DB aka Derby ... I wanted to learn how to use it but just a glance at the manuals and it put me off...

Overly complex and untidy documentations. Looks of setups here and there, console without any GUI... not simple to use in general. Derby really need to do something to make it's manuals easy to use. I know some have gotten use to the doc in Derby but how about beginners ? Given the choice of H2 and Derby, I think people would prefer H2.

I am not bias towards H2, but the fact is H2 clearly won hands down by it's simple, brainless , usability. When I started to find a java database, H2 and Derby are the options I have. But I move towards H2 and consequently use H2 as my default database whether for storing data or for using them in my software products since it's so easy to handle compared to Derby.

H2 have consistently always try to be considerate to it's users by making it easy to use and that is the very strength of H2.

If you don't believe, try downloading the latest H2 database and use it and also download a Derby and use it. Within seconds after you install or unzip H2, the next thing up and running is a H2 ... or maybe let's leave it for you to experiment and see for yourselves.