Helma Logo
main list history

Object-Relational Mapping

Helma 1 comes with its own fully featured Object-Relational Mapping framework.

Overview

Helma O/R maps rows from relational tables against JavaScript host objects called HopObjects. HopObjects are defined by prototype directories, which contain the JavaScript code and templates (called skins in Helma lingo) to be applied to them. The object-relational mapping itself is defined in a type.properties file, which may look like this:

_db = antville
_table = AV_TEXT

_parent = site.stories
_id = TEXT_ID
_prototype = TEXT_PROTOTYPE

# object references
creator = object (user)
creator.local = TEXT_F_USER_CREATOR
creator.foreign = USER_ID

# primitive properties
title = TEXT_TITLE
text = TEXT_TEXT
createtime = TEXT_CREATETIME
modifytime = TEXT_MODIFYTIME

# collections
comments = collection (comment)
comments.local = TEXT_ID
comments.foreign = TEXT_F_TEXT_STORY
comments.filter = TEXT_ISONLINE > 0
comments.order = TEXT_MODIFYTIME DESC

The different kinds of entries and their use are described below.

Basic Mappings

The _db entry describes the database to use for storing objects of this type. dbname is the name of a database as defined in the db.properties file.

_db = dbname

The _table entry tells Helma which table to use for objects of this type within the database.

_table = TABLENAME

The _id entry defines the column to use as primary key. Helma requires a single primary key with no other functionality.

_id = ID_COLUMN

The optional _name entry tells Helma which database column to use as object name. This is important for the user prototype, since it defines which column constitutes the user name.

_name = NAME_COLUMN

The _parent entry contains a comma-separated list of properties of objects of this type to be used as parent. Objects must know their parent in order to generate correct URLs in their href() function.

_parent = property1, property2.collectionX, root.collectionY

If an entry in the _parent item does not contain a dot, it is interpreted as an object property of the current object that acts as the object's parent. If an entry contains a dot character, the part after the dot is interpreted as a collection in the parent object in which the current object is contained. If the _parent is specified as "root", it is not interpreted as a property of the current object. Instead, the application's root object is used as parent object.

A type can inherit its data mapping and functions from another type. This is done with the _extends entry. Simply specify the name of the parent type:

_extends = parenttype

Extended prototypes are usually stored in the same relational database table. Thus, Helma needs a way to tell which row in the table should be instantiated with which object prototype. This is done using the _prototype entry, specifying a column in the table containing the name of the prototype for each row.

_prototype = PROTO_COLUMN

Simple Property Mappings

The mappings for simple properties (versus properties that are objects containing further properties) simply map a property name of the specified object type to a column of the relational database table. The type of the property or column need not be specified. Helma does all necessary conversion when reading or writing a property value.

prop1 = DB_COLUMN_1
prop2 = DB_COLUMN_2

To prevent a property from being overwritten by the application it can be made readonly.

prop1.readonly = true

In a similar manner marking a property as private will prevent any corresponding collection or reference from being updated but still perform all necessary updates in the database.

prop1.private = true

Object Reference Mappings

Properties that contain other objects are defined by the object keyword and the prototype name of the referenced object in brackets. The object reference is resolved via two entries:

prop3 = object (protoype) 
prop3.local = LOCAL_DB_COLUMN 
prop3.foreign = FOREIGN_DB_COLUMN

prop3.local specifies the part of the reference in the local database-table, prop3.foreign the column in the table where objects of the referenced prototype are stored. By executing the assignment obj.prop3 = someObject, the database-column specified by prop3.local will be set to the value of FOREIGN_DB_COLUMN, or null if someObject == null.

Collection Mappings

The following section describes the entries used to define collections of objects. There are two kinds of collections: First, every object can itself act as a collection, and second, it can define additional properties to be used as collections. Direct descendents are defined with entries starting with _children, while additional collections are defined using their property name. Otherwise the syntax for the two is the same.

The following are examples for simple collection mappings for both the direct descendants and those contained in a collection-property called prop4. The .foreign entry is used in both cases to specify a column in the foreign table used to collect the objects. Exactly those objects will be collected whose FOREIGN_DB_COLUMN value equals the LOCAL_DB_COLUMN value of the object holding the collection. Leaving away .local and .foreign creates a collection of all available objects of the specified prototype.

_children = collection (protoype) 
_children.local = LOCAL_DB_COLUMN 
_children.foreign = FOREIGN_DB_COLUMN 

prop4 = collection (protoype) 
prop4.local = LOCAL_DB_COLUMN 
prop4.foreign = FOREIGN_DB_COLUMN

By executing obj.add(otherObj) resp. obj.prop4.add(otherObj), the FOREIGN_DB_COLUMN will be set to the value of LOCAL_DB_COLUMN.

By default, child objects can be accessed from the containing parent object through their index-position or ID value. Additionally the entry .accessname can be used to specify a database column in the child table. The value of this column will be used as name of the objects contained in this collection.

prop4.accessname = DB_COLUMN

With the above mapping, an object contained in the prop4 collection with a DB_COLUMN value of "xyz" would be reachable as this.prop4.xyz from the containing object.

filter can be used to add an additional condition for the selection of objects which is not dependent on the object holding the collection. order contains the sorting to be applied to the child objects. group tells Helma to group the objects in the collection according to the value in DB_GROUPCOLUMN.

prop4.filter = DB_FILTERCOLUMN is not null 
prop4.order = DB_ORDERCOLUMN desc 
prop4.group = DB_GROUPCOLUMN 
prop4.group.order = DB_GROUPORDERCOLUMN
prop4.group.prototype = SomePrototype

Notice the two appearences of order above. The first one, prop4.order sorts the objects, the second, prop4.group.order sorts the grouped objects, that Helma will create as a result of the group entry. With this it's also possible to sort the grouped objects ascending, but the contents of the grouped objects descending.

The prop4.group.prototype property sets the Prototype to be used for the group objects generated by Helma.

Filters can contain references to the values of the current object's database entry fields. This is the syntax for these variables: ${FIELD_NAME}. The variables are then replaced by the actual values of the fields before the query is executed. With these variables, the above collection could also be described like this:

prop4 = collection (protoype) 
prop4.filter = FOREIGN_DB_COLUMN = ${LOCAL_DB_COLUMN}

filter.additionalTables allows adding additional tables to the query, so that their fields can be used in the condition of the filter as well. The table of the collection's prototype is allways the first table in the query. Table references can be aliased using TABLE_NAME AS ALIAS_NAME or TABLE_NAME ALIAS_NAME.

#Node is stored in in the database table NODE
#subChildren is a collection containing all the Node's children's children:
subChildren = collection(Node)
subChildren.filter.additionalTables = NODE AS CHILDREN
subChildren.filter = NODE.FOREIGN_DB_COLUMN = ${LOCAL_DB_COLUMN} AND NODE.LOCAL_DB_COLUMN = CHILDREN.FOREIGN_DB_COLUMN

Another example: Short arrays of objects of which the sequence can be changed without using a position field for each object can be created with the SQL statements ID_FIELD IN (ID_LIST) and FIND_IN_SET(ID_FIELD, ID_LIST), ID_LIST being a string field that contains the IDs seperated by commas. This comes in handy when the objects are used in more than one such array and the objects stored in such a way should know about all their referencing objects.

Object1/type.properties:

#the id list string containing a comma seperated list of OBJECT2 ids:
strObject2Ids = OBJECT2_IDS
#object2s lists the Object2 objects as referenced by strObject2Ids:
object2s = collection(Object2)
object2s.filter = OBJECT2.ID IN (${OBJECT1.OBJECT2_IDS})

Object2/type.properties:

#object1s lista all Object1 objects that are referencing this Object2 through their object2s collection:
object1s = collection(Object1)
object1s.filter.additionalTables = OBJECT2
object1s.filter = OBJECT2.ID = ${ID} AND FIND_IN_SET(OBJECT2.ID, OBJECT1.OBJECT2_IDS)

Additional Join Conditions

For both object and collection mappings, it is possible to provide any number of additional restraints used to join the local with the other table to further limit the selected objects.

prop4.local.1 = FIRST_LOCAL_COLUMN 
prop4.foreign.1 = FIRST_FOREIGN_COLUMN 
prop4.local.2 = SECOND_LOCAL_COLUMN 
prop4.foreign.2 = SECOND_FOREIGN_COLUMN

Whenever one specifies more than one restraint, the use of .number suffix is mandatory.

Mountpoint Mappings

Mountpoints provide a simple way to directly attach a prototype to the URL path. If the path contains the name of a mountpoint the prototype's objects are accessible simply by adding their id (or a property defined by ._accessname) to the URL string.

myMountpoint = mountpoint(myProto)

In the example above, the path /myMountpoint/23/test refers to the action test.hac of the object with id 23, which is derived from the myProto prototype.

Advanced Settings

Some techniques to optimize Helma's strategy for managing objects from a relational table.

_children.loadmode=aggressive
When this is set for _children or a virtual node, all child objects will be fetched in one query. The default way is to just fetch an index of children consisting of the object's keys, and then fetch the objects on demand, which allows the Hop to scale to large child collections where only few of the objects are actually accessed.

_children.cachemode=aggressive
By default, Hop refreshes a children index when any object with the prototype contained in the subnodes was modified since the last access. By setting this flag on _children or a virtual node, children will only be refreshed when an object is added to or removed from the child collection itself, or an object already contained in it is modified.

_children.maxsize=XX
When specifying children (either directly or within a virtual container) an optional maxsize property can be used to limit the number of objects contained. A setting of e.g. 100 would cause the collection to contain maximally 100 objects, regardless of how many (matching) rows the database table contains.

propname.readonly=true
Use the readonly flag to tell the Hop never to update this column in the database. Actually the column won't be updated if the property is not modified, but this is like an extra layer of security.

propname.private=true
The readonly flag tells Hop to update this column in the database only, but to prevent any collections containing or any other object referring to the very object with that property.

Links to this page: Caching, Helma and MySQL, Documentation