Helma Logo
main list history

Object-Relational Mapping Tutorial

This tutorial walks you through setting up Helma and implementing a simple addressbook application that stores its data inside a relational database.


Preparing Helma

I assume you already managed to install a [Unhandled macro: shortcut] and that it is now up and running.

First of all, you have to tell Helma about your new application. Add a line with a more or less meaningful name (e.g. addressbook) to the file apps.properties in the Helma installation directory (you might find some lines containing other application names already):

apps.properties:
# List of apps to start.

base
base.mountpoint = /

manage

addressbook

Helma will now create a new folder in the apps directory called addressbook, inside of which you will later place the Javascript files and skins that define your application.

The application's name is also the first part of the URL path if you want to access the application from a browser. If Helma is running with the default settings, you should be able to request the URL http://localhost:8080/addressbook/>.

However, there is nothing there yet and you only will get an error message stating "Error in application 'addressbook': Action not found". Since the request didn't reference a particular HopObject and/or specify a specific action, Helma was looking for the "main" action of the "root" HopObject, which you have not yet specified.

This main action will later contain the code that generates a page listing your address book entries. For now, you may just create the following main action, in order to verify that your otherwise empty addressbook application is configured correctly.

apps/addressbook/Root/main.hac:
res.data.title = "Helma Address Book";
renderSkin("html");

This action attempts to render a skin named "html", which you create inside the "Global" directory:

apps/addressbook/Global/html.skin:
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
   <title><% response.title %></title>
   <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="white">

Under development: <% response.title %>

</body>
</html>

Instead of the above mentioned error message, you should now receive the following response.

Under development: Helma Address Book


Database Connection

While Helma features an embedded object-oriented database, where HopObjects are persisted when they are not mapped to an external database, Helma is able to connect to almost any database system in the world. Well, at least if there is a so-called JDBC driver available for it. See the short guide about using MySQL, which should help you preparing the freely available MySQL server for a connection with Helma.

To wire the MySQL database to your Helma application, you need a file called db.properties in your application root (ie. the folder addressbook according to my example). Use your favorite text editor and enter the following lines:

apps/addressbook/db.properties:
myDataSource.url      = jdbc:mysql://localhost/addressbook
myDataSource.driver   = org.gjt.mm.mysql.Driver
myDataSource.user     = helma
myDataSource.password = password

This defines myDataSource as a connector to the MySQL database called addressbook for the user helma identified by the password password – which you should replace with your password as chosen when setting up the MySQL installation (you certainly can use a different user/password combination as well, as long as it allows to connect to the MySQL server).

If you want to use a different database system or if you gave a different name to your database, that's no problem for Helma at all. Simply replace your JDBC driver or fill in the appropriate name. And certainly, you also can connect to a database server on another machine by arranging the URL, though you might have to set the database access privileges differently.

If you have done everything alright, you should restart either Helma or at least the addressbook application.

To restart an application visit the manage-interface at http://localhost:8080/manage/>. If you haven't visited it up to now you'll get asked to set an initial password. Unless you are experimenting with a local Helma installation, you will probably have to edit the server.properties file and add your (local) IP address to the allowAdmin property.


Object-Relational Mapping

The goal is now, to "wire" the relational tables in the MySQL database to the object-oriented structure of Helma. While MySQL (and any other relational database system) structures data in tabular form, each row representing a data set, in Helma each table row becomes a HopObject using the columns as named subnodes, also called properties.

To achieve this, you will now create a prototype such HopObjects can be adapted from. While this sounds complex in theory, in practice you do this simply by adding a new directory to your application (e.g. by issueing mkdir apps/addressbook/Person).
+----+-----------+-----------+---------------------+
| id | firstname | lastname  | email               | 
+====+===========+===========+=====================+
|  1 | Hannes    | Wallnoefer | hannes@helma.org    |
+----+-----------+-----------+---------------------+
|  2 | Robert    | Gaggl     | robert.gaggl@orf.at |
+----+-----------+-----------+---------------------+
|  3 | Tobi      | Schaefer  | tobi@helma.org      |
+----+-----------+-----------+---------------------+
fig.1 A relational database table.

+-------------+
| HopObject 1 |
+===========+=+----------------+
| FIRSTNAME | Hannes           |
+-----------+------------------+
| LASTNAME  | Wallnoefer        |
+-----------+------------------+
| EMAIL     | hannes@helma.org |
+-----------+------------------+

+-------------+
| HopObject 2 |
+===========+=+-------------------+
| FIRSTNAME | Robert              |
+-----------+---------------------+
| LASTNAME  | Gaggl               |
+-----------+---------------------+
| EMAIL     | robert.gaggl@orf.at |
+-----------+---------------------+

+-------------+
| HopObject 3 |
+===========+=+--------------+
| FIRSTNAME | Tobi           |
+-----------+----------------+
| LASTNAME  | Schaefer        |
+-----------+----------------+
| EMAIL     | tobi@helma.org |
+-----------+----------------+
fig.2 Three HopObjects representing the three
rows of the table.

These so-called type mappings are set-up in the file type.properties for each HopObject. We create such a file in the next step.


"Wiring" The Prototype

A type.properties file that fits with the example MySQL database addressbook looks like this:

apps/addressbook/Person/type.properties:
_db         = myDataSource
_table      = PERSON
_id         = ID

firstname   = FIRSTNAME
lastname    = LASTNAME
email       = EMAIL

What exactly happens here? The two lines at the beginning tell Helma to map the prototype Person and all its HopObjects derived from it to the table PERSON (please note the different case!) found via the database connection established with myDataSource – the MySQL connector as defined before.

So, _db defines the database connection (or: data source) and _table the database table to be used. What's missing are the details of how the relational table columns should be mapped to the object-oriented database.

Most important is the mapping of the table's primary key index to the object-oriented database index: this key is necessary to provide a unique identifier for both, each entry in the relational database and each HopObject. And in consequence, _id maps the two to each other.

Btw. _db, _table and _id are internal Helma properties. That's why they start with an underscore for not getting in the way of your custom properties. However, they are mandatory for each type.properties file.

The five following lines assign each column of the relational table to a property of the HopObject. E.g. the data that is contained in the column FIRSTNAME of the table, becomes the value of the property firstname of the HopObject, the column LASTNAME is being mapped to the property lastname and so on.

A recommended naming convention is to use lowercase (resp. mixed case) letters for HopObject properties and uppercase letters for database columns resp. table names.

So, you achieved to represent the figure of HopObjects from the prior step with a few lines in type.properties. Save the type.properties in the folder of the corresponding HopObject - in this case in the Person folder.

To generate the corresponding table in your database, you should be able to use an SQL statement such as the following.

CREATE TABLE `PERSON` (
`ID` int(11) NOT NULL default '0',
`FIRSTNAME` varchar(128) NOT NULL default '',
`LASTNAME` varchar(128) NOT NULL default '',
`EMAIL` varchar(128) NOT NULL default '',
PRIMARY KEY (`ID`)
);

As a side remark, be aware of what names to choose for your HopObject's properties. Just as the database columns can be made up of any name as long as they do not conflict with MySQL keywords, you are restricted to use property names that do not represent reserved HopObject or ECMAScript keywords. A look in this documentation's reference section might help if you are in doubt.


Attaching HopObjects

We now successfully established a database connection from Helma to the MySQL server and mapped the relational table data to HopObject properties. But still, these derived HopObjects are freely floating around in Helma space. There is no possibility to access HopObjects, yet. Helma even does not know about them. We need a HopObject that already is accessible, that is known by Helma. That's what the Root HopObject of an application is for.

One could say the Root HopObject is the default place for Helma to go: "go for the root", that's what Helma can do very good. When you enter the base URL of any application, Helma is going for Root, is retrieving data from the Root HopObject first.

And as any HopObject has its prototype, there is also one for Root, ie. the directory called – you guessed it – Root.

To attach our Person HopObjects onto Root, we have to create yet another file called type.properties, this time for the Root prototype. It contains the following lines and goes into the – you guessed it again – Root folder.

apps/addressbook/Root/type.properties:
_children            = collection(Person)
_children.accessname = ID
_children.order      = LASTNAME asc

You can read this assignment the way "attach all HopObjects of the prototype Person as subnodes, indexed by the column ID of the corresponding relational table PERSON, and sorted ascending by the values in column LASTNAME".

And because we save this document in Root they will be attached to the HopObject Root (you are right when you assume that you could do this with any other HopObject simply by creating such type.properties for its prototype – in fact, we have done it before with the Person prototype).


Shaping The Output

Because the Person HopObjects are now attached to Root, we just need a simple interface to access them and make them display in the browser.

Here's a simple XHTML layout we can use:

apps/addressbook/Global/html.skin:
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
   <title><% response.title %></title>
   <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="white">

<% response.body %>

</body>
</html>

Instead of defining a generic title, I am using a placeholder <% response.title %>, just as the whole text body is substituted by <% response.body %>. Both placeholders will be replaced by "real" content, later.

Helma calls such placeholder structures "macros", the files fitted with such macros are called "skins", resp. "skin files". Skin files are behaving like templates, except that you absolutely cannot evaluate any scripting code in those. And that's again what macros are for. Macros are replaced by either the result of a function call or, like in this case, by the value of an object's property.

This is one of Helma's big virtues: the separation of data and presentation. Because it is a good idea to avoid putting layout elements into your code just as it is a good idea to avoid including scripting code in your layout. Helma's macro and skin concepts help you to prevent these big no-nos and if you follow this path your web applications will become more flexible and easier to deploy, even more beautiful.

To see how the two macros will work here, save this skin as html.skin in the directory addressbook/Global.


Improvements

Helma's skin and macro features allow you to easily create layouts following the same hierarchical structure the represented data was built of.

We will now create another skin for the Person prototype to be used by all Person HopObjects in consequence:

apps/addressbook/Person/link.skin:
<a href="mailto:<% this.email %>"><% this.firstname %> <% this.lastname %></a><br />

I think the three macros in this skin are pretty self-explanatory. They all refer to the current HopObject by this (ie. this will be evaluated to a Person HopObject at runtime) and three of the five custom properties we defined for it. Each of them will be replaced by the corresponding HopObject's property value.

Save this skin as link.skin in the Person directory.

The code for actions performed on an object is stored in hac files which are just text-files with a senseful name and the file-extension hac - for instance edit.hac.

The default action for an object is stored in main.hac

We will now define the default action for the application's Root object and change the file called main.hac in the Root folder to contain code that looks like this:

apps/addressbook/Root/main.hac:
var str = "";
for (var i=0; i< root.size(); i++) {
   var person = root.get(i);
   str += person.renderSkinAsString("link");
}

res.data.title = "Helma Address Book";
res.data.body = str;
renderSkin("html");

If everything is done right, Helma will now render the skin Person/link.skin for each person HopObject using the corresponding object data. And so your browser display should look much more beautiful:

Hannes Wallnoefer
Robert Gaggl
Tobi Schaefer

See how easy Helma skins and macro work? And in effect, you create very flexible and modular web applications. And you even can use your own custom macros – just continue reading...


Custom Macros

The way we filled the MySQL database with some data is quite unefficient. Why not do it with Helma?

As a first step, we enable editing the database entries and add an appropriate link to the skin file Person/link.skin (in a later step we also will make it possible to create new entries):

<a href="mailto:<% this.email %>"><% this.firstname %> <% this.lastname %></a> 
<small><a href="<% this.href action="edit" %>">edit</a></small><br />

Other than the first three macros, this.href does not refer to a property of a person HopObject. It is a custom macro that needs a macro handler, ie. a function to work:

apps/addressbook/Person/macros.js:
function href_macro(param) {
  return(this.href(param.action));
}

Enter the above lines in a new file and save this as macros.js in the Person directory.

Now let's put it altogether:

Helma transforms the macro structure <% this.href action="edit" %> into a function call of this.href_macro(param) with param being a generic object containing the property action (along with its value).

That means param.action contains the string "edit" as it was assigned in the macro structure. The function href_macro(param) then returns the URL of the actual person HopObject plus "edit".

A look at the browser will proove if these considerations are right:

Hannes Wallnöfer edit
Robert Gaggl edit
Tobi Schäfer edit

Great! It works. But yet the links lead into Helmatic nirvana. We have to enable the edit form first.


Handling User Input

To enter data into Helma from a browser we first create an adequate layout for an HTML form:

apps/addressbook/HopObject/edit.skin:
<form action="edit" method="post">
First Name: <input type="text" name="firstname" 
    value="&lt;% this.firstname encoding="form" %&gt;" /&gt;&lt;br /&gt;
Last Name: <input type="text" name="lastname"
    value="&lt;% this.lastname encoding="form" %&gt;" /&gt;&lt;br /&gt;
e-mail: <input type="text" name="email"
    value="&lt;% this.email encoding="form" %&gt;" /&gt;
<p />
<input type="submit" name="submit" value=" Save " />
</form>

Save this skin as edit.skin but this time in the HopObject directory (we will reveal the reason for this later).

The corresponding action edit.hac looks like this – it needs to be saved in the Person directory:

apps/addressbook/Person/edit.hac:
res.data.title = "Edit Helma Address Book Entry";
res.data.body = this.renderSkinAsString("edit");
renderSkin("html");

Now the edit links from the main resource of our address book application should work and you should see the HTML form containing the chosen HopObject's data.

[Unhandled macro: image]

The edit.hac / edit.skin combo works analogously to main.hac and link.skin in the previous sections. So nothing really new here except the encoding parameters in the skin markup.

They avoid HTML markup entered in the database from messing up the HTML layout. Each value returned from the macro handler is encoded like it was wrapped into a encodeForm() function.

Although the Save button already works, the data remains yet unchanged. The necessary code has to be added before the first line of edit.hac:

if (req.data.submit) {
  this.firstname = req.data.firstname;
  this.lastname = req.data.lastname;
  this.email = req.data.email;
  this.modifytime = new Date();
  res.redirect(root.href());
}

Try out to change some values and click Save afterwards – you should notice the changes immediately in the list view.


Creating A HopObject

We are only a few more steps away from successfully having built a simple application with the basic and most important Helma features.

One thing that's still missing is to create a new person HopObject. Let's do this now.

Because the create form almost exactly looks like the edit form in HopObject/edit.skin from the previous step, we will recycle it for this purpose.

Therefore some slight adjustments are necessary:

Change the first line of HopObject/edit.skin from

<form action="edit" method="post">

to

<form action="<% response.action %>" method="post">

Then add the line

res.data.action = "edit";

just before the last line containing renderSkin("html"); in Person/edit.hac (this is just to keep this action file compatible with our changes).

Create the action file Root/create.hac as follows:

apps/addressbook/Root/create.hac:
if (req.data.submit) {
  var p = new Person();
  p.firstname = req.data.firstname;
  p.lastname = req.data.lastname;
  p.email = req.data.email;
  p.createtime = new Date();
  p.modifytime = new Date();
  root.add(p);
  res.redirect(root.href());
}

res.data.title = "Create Helma Address Book Entry";
res.data.body = this.renderSkinAsString("edit");
res.data.action = "create";
renderSkin("html");

Finally, add these three macro functions to a new file called macros.js (don't use Person/macros.js):

apps/addressbook/Root/macros.js:
function firstname_macro() {
  return("");
}

function lastname_macro() {
  return("");
}

function email_macro() {
  return("");
}

Before we look at the result of these additions and modifications, let me explain some details.

The introduction of res.data.action and its corresponding macro <% response.action %> gives the skin file HopObject/edit.skin more flexibility since the action file that is invoking it determines the form action for it.

The reason why edit.skin is stored in the HopObject directory is to make it available for both, the Root and the Person prototypes. Everything that's belonging to the HopObject prototype also belongs to any other custom HopObject prototype. This way the Root and Person prototypes can share the same skin file.

Moreover, and this is a big difference to when we would have saved edit.skin as global skin file, we can use the scope variable this. It always refers to the actual HopObject, either the root object or a particular person object.

This is very convenient, since that way we can retrieve any HopObjects properties directly via this.firstname, this.lastname and this.email.

Because root does not have any of these properties, we have to avoid the output of error messages by adding corresponding macro functions.

So while the <% this.firstname %> macro for any person HopObject refers to its property stored in the database, <% this.firstname %> for the root HopObject refers to the macro function root.firstname_macro(), and respectively do the other two macros.

Now point your browser to the URL http://localhost:8080/addressbook/create> and you will get an empty form. Fill in some data and press Save. Et voilà! You immediately should see a new item at the end of the list.

Here is the file structure you should have ended up with:

 - apps
   - addressbook
       db.properties
     - Global
         html.skin
     - HopObject
         edit.hac
         edit.skin
     - Person
         link.skin
         macros.js
         type.properties
     - Root
         create.hac
         macros.js
         main.hac
         type.properties


Links to this page: Documentation

Comments

#1 by jGc at 2009/11/05 13:03

Seems like there are some errors with html markups. When you click links, extra characters are passed to the server. (as of Nov 5, 2009)

#2 by jGc at 2009/11/05 13:30

If you want to use Sqlite3 in lieu of MySql, get the latest jdbc jar from http://www.zentus.com/sqlitejdbc/ and put in /lib/ext as described above. (I am using sqlitejdbc-v056-pure.jar, slower version for my slow dev laptop, which is fast enough for my old age.)

Here is an example db.properties:

myDataSource.url = jdbc:sqlite::memory:
myDataSource.driver = org.sqlite.JDBC
myDataSource.user = helma
myDataSource.password = password

http://localhost:8080/sqlshell

And do a qry like:

CREATE TABLE sometable (
  id   INT,
  created_at TIMESTAMP,
  first_name VARCHAR(255),
  last_name VARCHAR(255),
  password VARCHAR(255),
  email VARCHAR(255),
  email_alt VARCHAR(255),
  phone VARCHAR(255),
  opt_in VARCHAR(255),
  consent VARCHAR(255)

);

Clicking 'explore' at sqlshell page seems to display meta data about the table.

(FYI: I am not writing to a file in the above setup. Do not sue me when you realize some valuable data are not saved. I guessed driver class name by comparing MySQL and Sqlite java example codes. I further assumed user and password can be anything since sqlite3 does not use them.
(It turns out using memory is not the best idea testing http interactions. You may want to try acmeDataSource.url = jdbc:sqlite:test.db test.db file will be create at your run ponint i.e. at dir that start.sh file is if you do ./start.sh))

#3 by jGc at 2009/11/06 14:36

Following this tutorial, I am running into a couple of problems. Person data is created and displayed by Helma. But in the Sqlite DB, only ID field is valid. Other fields were null. I have no idea where the data was saved to. xml file did not seem to have those data displyed on the browser.

Next, I tried to add _prototype = PERSON_PROTOTYPE to type.properties. (after adding PERSON_PROTOTYPE column to the table). And now I am running into

[2009/11/06 08:13:54] [ERROR] [acme-1] post:create java.lang.NumberFormatException: For input string: "Person"
java.lang.NumberFormatException: For input string: "Person"
	at java.lang.NumberFormatException.forInputString(NumberFormatException.java:48)
	at java.lang.Long.parseLong(Long.java:403)
	at java.lang.Long.parseLong(Long.java:461)
	at helma.objectmodel.db.NodeManager.setStatementValue(NodeManager.java:2062)
	at helma.objectmodel.db.NodeManager.insertRelationalNode(NodeManager.java:513)
	at helma.objectmodel.db.NodeManager.insertNode(NodeManager.java:440)

Initially I thought it was Sqlite ID column. (INT Long vs INT(11) mismatch?)

After googling around

This exception seem to appear at various posts. W.r.t Helma, some xml and Mail funtionality at different OS were mentioned.

Some claimed it was documented bug in Sun's beta of J2SE 1.5.0. (I am on OSX 1.5.0_20).

I tried plain HopObject and it seemed to work fine by saving into xml.

Will try it out on Ubuntu/Java 6. See what happens.

#4 by jGc at 2009/11/06 18:05

Uploaded my apps to Helma EC2 Ubuntu Jaunty with Sun JDK 6.

I did tar cvxf my apps/myapp1/ along with apps.properties and lib/ext/ and then uploaded to my Google App Engine app.

wget both Helma 1.6.3 and my tgz file.
tar cvzf in the order and they were ready to GO.

NOT!

One complication was OSX added ._xxx.js files (behind my back) to my tgz file and that created strange errors when browser was pointed at my apps (e.g. http://ec2-75-101-175-237.compute-1.amazonaws.com:8080/acme/ )

Once I removed them, most of them ran! (give this a try if you have the same issue
find ./apps -name "._*" -type f -exec rm {} \;
)

Both plain HOP and MySQL versions ran and dutifully saved to the table.

But, SQLite version still displayed the same error. So either there is problem with Helma using SQLite or it could be jdbc driver I am using. Will test it out at some point.

Tried another jdbc driver for SQLite. The same erorror.

Changing my suspicion on jdbc driver since
helma.Database.getInstance("acmeDataSource").query( sql )
and
helma.Database.getInstance("acmeDataSource").execute( sql )
both work fine.

It's Helma ORM with SQLite that acts up. This is not a show stopper but does make it harder to try out Helma. Given my lack of Java mojo and NG moving more toward Obj DB, I guess it may not get fixed anytime soon. Need a drink.