Principles

Using the Velocity variable that references the Velosurf tool in the Velocity context, template writers can access all (or a subset of) the tables of a database, that are called Velosurf entities. Entities properties are attributes (that can be table columns as well as customized queries). Entities methods that trigger database update/delete/insert queries are actions. Each realization of an entity is an instance.

Velosurf uses an XML configuration file that:

• contains all necessary parameters to connect to the database.

• contains the definition of all needed custom SQL entities, attributes and actions.

• defines the access rights to entities.

All queries are done via a pool of prepared statements automatically handled by Velosurf.

Entities, Instances, Attributes and Actions

• Entities are the objects that map database tables. By default, Velosurf defines an entity for each table. Entities are used to access Instances, either by the mean of #foreach loops or via one of the fetch methods.

• Instances map rows of a table (or of the result set of a customized query): they are a realization of their corresponding entity. Instances are used to access specific fields, attributes or actions.

• Attributes belong either to the database itself ('root' attributes) or to specific entities, in which case they can be accessed from the corresponding instances. Attributes have a result type which is one of 'scalar', 'row', or 'rowset'. One can specify the resulting entity of row or row set attributes, to access nested properties on them. By default, Velosurf defines a scalar attribute for each table column.

• Actions are like attributes, except their SQL query is a modification query (insert/update/delete), or even a succession of such modification queries (a transaction that will rollback as a whole in case of failure). They return the number of affected rows. If present, the root action named 'startup' is executed at startup.

What does the syntax look like?

Exampe #1: fetch a single value of the database and display it

Assuming that you've got the following table, called "message", in your database:

id	text
1	"Hello World!"
2	"How are you?"

then you can write the following VTL expression:

$db.message.fetch(1).text

Or, to be more explicit:

#set( $hello = $db.message.fetch(1) )
$hello.text

and this would produce:

Hello World!

Example #2: fetch multiple rows of a single column and display them in sequential order

Still with the same table:

#foreach( $message in $db.message )
    $message.text
#end

and this would produce:

Hello World!
How are you?

Example #3: update or insert a value

Note: it's not advised to issue updates and inserts from inside templates, since it breaks the MVC paradigm. You should rather achieve this kind of stuff from your controller.

## update a value
#set( $message = $db.message.fetch(1) )
#set( $message.text = "Hello everybody!" )
#set( $success = $message.update() )
#if(!$success)
  An error occured: $db.error
#end

## insert a value
#set( $newMessage = { id:3; text:'Fine, thank you.'})
#set( $success = $db.message.insert($newMessage) )
#if(!$success)
  An error occured: $db.error
#end


If you are using the velosurf.tool.HttpQueryTool tool to parse HTTP query parameters, you can pass it directly to an insert or an update method:

#set( $success = $db.message.insert($query) )
#if(!$success)
  An error occured: $db.error
#end


A simple example

Suppose we've got two joined tables user (id integer primary key, name varchar(50)) and message(id integer primary key autoincrement, userid integer references user(id),txt varchar(255)).

We want to list the messages per user, and to be able to add or delete messages. We'll rely on the Velosurf reverse engineering to define the $db.user and $db.message entities, as well as the $message.user and $user.messages attributes.

First, the toolbox.xml (that references all Velocity tools) defines the needed tools:

• db will be the key to access the database
• query will be the key to access http parameters

Then, the velosurf.xml configuration file, which gives connection parameters. Nothing more to put here than connection parameters, since we use reverse engineering.

Now, the template itself:

Of course, there is no data validation, no html escaping, no authentication (each user can see the messages of others), so we'd need some more work towards a real-life message board, but we already have something functional with few lines of code.

The output would be something like this: