VTL Velosurf reference

Table of content

Introduction
Database attributes and methods
Entity attributes and methods
Attribute attributes and methods
Action attributes and methods
Instance attributes and methods
HTTPQuery attributes and methods
Localizer attributes and methods
Authenticator attributes and methods

Introduction

You should have some knowledge of the Velocity Template Language before reading this page.

In a MVC Webapp, operations that issue changes to the database should not be done from inside the templates but from a separate controller. One way to enforce this is to set the read-only flag on in Velosurf configuration file (the controller can change this flag programmatically for its own connection).

All methods that take a "Map values" argument expect column names as keys. They are quite convenient to use with the $query variable (that contains all the HTTP query parameters) when coming from an HTML form: you only have to ensure that the names of HTML input fields map correctly to the names of the corresponding table columns, by using column aliases if necessary.

Plain keywords represent actual names of properties or methods, while keywords in italic stand for user properties or variables.

Database attributes and methods

`$db.entity`	returns an entity
`$db.attribute`	gets an attribute, returns a scalar, an instance, or an iterable attribute
`$db.action`	executes an action, returns the number of affected rows
`$db.external-parameter`	gets or sets an external parameter
`$db.error`	returns the last encountered error, if any
`$db.locale`	returns the current locale

$db.entity

Returns an entity.

#foreach($u in $db.user) user name : $u.name #end

#set( $current_user = $db.user.fetch($query.user_id) )

$db.attribute

Returns an attribute, or directly the resulting value of the attribute for scalar or row attributes.

Today, there are $db.today_events_count events.

#foreach($event in $db.today_events) Today event: $event #end

You can optionally directly specify external parameters values:

## requires the Velosurf uberspector Number of events for $date: $db.events_count({'day':$date}) ## without the Velosurf uberspector Number of events for $date: $db.getWithParams('events_count',{'day':$date})

$db.action

Executes a root action, returns the number of affected rows.

#set( $nb = $db.cleanEvents ) Deleted $nb event(s).

You can optionally directly specify external parameters values:

## requires the Velosurf uberspector #set( $nb = $db.cleanDailyEvents({'date':$date}) ) Deleted $nb event(s). ## without the Velosurf uberspector #set( $nb = $db.getWithParams('cleanDailyEvents',{'date':$date}) ) Deleted $nb event(s).

$db.external-parameter

Sets or returns the named external parameter, for use within a root attribute or action.

#set( $db.publicationYear = 2006 ) Number of published books for year $db.publicationYear: $db.countBooks

Note that you can specify external parameters directly when calling attributes or actions.

$db.error

Returns the last encountered SQL error message, if any.

#set( $nb = $db.myAction ) #if($nb == 0 && $db.error)Error: $db.error

$db.locale

Returns the current locale.

Current locale: $db.locale.displayName

Entity attributes and methods

`$entity.name`	returns the name of this entity
`$entity.columns`	returns the column names of this entity
`$entity.fetch(key)`	returns the instance uniquely identified by key if it does exist, null otherwise
`#foreach( $instance in $entity )`	iterates over this entity's instances
`$entity.rows`	returns the list of this entity's instances
`#set( $entity.order = 'order' )`	specifies how to order this entity's instances
`$entity.refine( 'condition' )`	adds a filtering condition on this entity's instances
`$entity.clearRefinement()`	clears any previously set refinement
`$entity.newInstance()`	returns a new empty instance for this entity
`$entity.newInstance(Map values)`	returns a new instance for this entity, initialized with the given values
`$entity.validate(Map values)`	validate data against this entity's constraints, returns the boolean success status
`$entity.insert(Map values)`	inserts an instance of the entity, returns the boolean success status
`$entity.lastInsertID`	returns the last inserted ID for this entity's autoincremented primary key
`$entity.update(Map values)`	updates an instance of the entity, returns the boolean success status
`$entity.delete(Map values)`	delete an instance of the entity, returns the boolean success status
`$entity.delete(key)`	delete the instance uniquely identified by key, return true if it did exist
`$entity.upsert(Map values)`	upsert (update or insert) an instance of the entity, returns the boolean success status
`$entity.count`	returns the number of rows in this entity's table

$entity.name

Returns the name of this entity.

$entity.columns

Returns the column names of this entity.

#foreach($col in $user.entity.column) column $col = $user.get($col) #end

$entity.fetch(key)

Returns the instance uniquely identified by key if it does exist, null otherwise. key specifies this entity's primary key value and can be a scalar value (string or number, for single-column keys), a list of values (for multi-column keys), or a map (where key values are stored under their column name).

#set( $book = $db.book.fetch( $query.book_id ) ) or: #set( $book = $db.book.fetch( $query ) )

#set( $resp = $db.responsability.fetch( [$user_id,$role_id]) )

#foreach( $instance in $entity )

Iterates over this entity's instances. Concerned instances may have previously refined or ordered using the refine() and order() methods.

All our books: #foreach($book in $db.book) $book.title ($book.author.name) #end

$entity.rows

Returns the list of this entity's instances. Concerned instances may have previously refined or ordered using the refine() and order() methods.

#set( $entity.order = 'order' )

Sets the column(s) to be used for ordering this entity's instances when issuing a #foreach. This setting is local to the current Velocity context. The argument follows the syntax of a standard SQL ORDER BY clause, that is, several columns can be present in a comma separated list, and when postfixed to a column name the DESC keyword means descending order.

#set( $db.user.order = "lastname" ) #foreach($user in $db.user) ... #end

$entity.refine( 'condition' )

Specifies an additional SQL condition before issuing a #foreach on this entity. This method can be called several times to accumulate conditions. The refinement is local to the current Velocity context.

Refinements can be reset using the clearRefinement() method.

Use of this method is not encouraged, as it breaks the principle of SQL code isolation. It may be removed from future versions of Velosurf.

Books written by $author: $db.book.refine("author_id = $autor.author_id") #foreach($book in $db.books) $book.title #end

$entity.clearRefinement()

clears any previously set refinement.

$entity.newInstance()

Creates a new instance for this entity, meant for later insertion.

#set( $user = $db.user.newInstance() ) #set( $user.firstname = 'Joe' ) #set( $user.lastname = 'Smith' ) $db.user.insert( $user )

$entity.newInstance(Map values)

Creates a new instance for this entity, meant for later insertion. It is initialized with the values given in the provided Map.

#set( $user = $db.user.newInstance($query) ) $db.user.insert( $user )

$entity.validate(Map values)

Validates values against this entity's constraints, returns a boolean.

$entity.insert(Map values)

Inserts the given values as a new instance of this entity and returns the boolean success status of the operation. Unless auto-incremented, primary key values must be present in the map. If this entity's primary key is an auto-incremented index, its value can be retrieved using $entity.lastInsertID.

If there are validation constraints for this entity, values are validated against them before the insertion.

#set( $success = $db.author.insert($query) ) #if(!$success) insertion failed: ## display validation errors for($error in $db.author.validationErrors) $error #end ## display SQL error $!db.error #end

$entity.lastInsertID

Returns the last inserted ID value for this entity's autoincremented primary key.

$entity.update(Map values)

Updates the values of an instance of this entity and returns the boolean success status of the operation. Primary key values must be present in the map along with values to be updated.

Note that this method cannot be used to update the values of the primary key itself (use insert(new key) plus delete(old key)).

If there are validation constraints for this entity, values are validated against them before the update.

#set( $success = $db.profile.update($query) ) #if(!$success) update failed: ## display validation errors for($error in $db.author.validationErrors) $error #end ## display SQL error $!db.error #end

$entity.delete(Map values)

Deletes an instance of the entity and returns the boolean success status of the operation. Primary key values must be present in the map.

#set($db.book.delete( $query )) #if(!$success) delete failed: $!db.error #end

$entity.delete(key)

Deletes the instance uniquely identified by its primary key. Returns true if it did exist and was sucessfully deleted.

#set($db.book.delete( $query )) #if(!$success) delete failed: $!db.error #end

$entity.upsert(Map values)

Upserts (updates or inserts) the values of an instance of this entity and returns the boolean success status of the operation. Primary key values must be present in the map along with values. If a row with this key already exists then the instance is updated, otherwise a new instance is inserted.

If there are validation constraints for this entity, values are validated against them before the upsert.

#set( $success = $db.profile.upsert($query) ) #if(!$success) upsert failed: ## display validation errors for($error in $db.author.validationErrors) $error #end ## display SQL error $!db.error #end

$entity.count

Returns the number of rows in this entity's table. Takes into account the current refinement.

Attribute attributes and methods

`#foreach( $instance in $attribute )`	iterates over the instances of this attribute
`$attribute.rows`	returns the list of this attribute's resulting instances
`$attribute.scalars`	returns a list of this attribute's first resulting column
`$attribute.map`	returns a map built with this attribute's first two resulting columns
`$attribute.instanceMap`	returns a map of all resulting instances indexed by their primary key
`#set( $attribute.order = 'order' )`	specifies how to order this attribute's resulting instances
`$attribute.refine( 'condition' )`	adds a filtering condition on this entity's instances
`$attribute.clearRefinement()`	clears any previously set refinement

Only multivalued attributes (attributes that return a row set) can be referenced from templates. Others are directly evaluated when referenced.

#foreach( $instance in $attribute )

Iterates over this attribute's resulting instances. Concerned instances may have previously refined or ordered using the refine() and order() methods.

Logged users: #foreach( $user in $db.logged_users ) $user.login #end

$attribute.rows

Returns the list of this attribute's resulting instances. Concerned instances may have previously refined or ordered using the refine() and order() methods.

$attribute.scalars

Returns the list of this attribute's first resulting column. Concerned instances may have previously refined or ordered using the refine() and order() methods.

$attribute.map

Returns a map built with this attribute's first two resulting columns. Concerned instances may have previously refined or ordered using the refine() and order() methods.

$attribute.instanceMap

Returns a map of all resulting instances indexed by their primary key. Concerned instances may have previously refined or ordered using the refine() and order() methods.

#set( $attribute.order = 'order' )

Sets the column(s) to be used for ordering this attributes's resulting instances when issuing a #foreach. This setting is local to the current Velocity context. The argument follows the syntax of a standard SQL ORDER BY clause, that is, several columns can be present in a comma separated list, and when postfixed to a column name the DESC keyword means descending order.

logged users: #set( $db.logged_users.order = 'login' ) #foreach( $user in $db.logged_users ) $user.login #end

$attribute.refine( 'condition' )

Specifies an additional SQL condition before issuing a #foreach on this attribute. This method can be called several times to accumulate conditions. The refinement is local to the current Velocity context.

Refinements can be reset using the clearRefinement() method.

Use of this method is not encouraged, as it breaks the principle of SQL code isolation. It may be removed from future versions of Velosurf.

Logged users for country $country.name: $db.logged_users.refine( "country = $country.id" ) #foreach( $user in $db.logged_users ) $user.login #end

$attribute.clearRefinement()

Clears any previously set refinement.

Action attributes and methods

An action has no attribute and no method. It is executed when referenced, and returns the number of affected rows.

Instance attributes and methods

`$instance.column`	Gets or sets the value of the specified column
`$instance.attribute`	calls an attribute of this instance, returned value depend on the attribute result type
`$instance.action`	executes an action of this instance and returns the number of affected rows
`$instance.entity`	returns this instance's entity or `null` for a generic instance
`$instance.primaryKey`	returns a list of two-elements maps ('name'→name and 'value'→value) of this instance's key column(s)
`$instance.validate()`	validates this instance's data
`$instance.putAll(Map values)`	put all the key/value pairs of the provided Map in this instance
`$instance.setColumnValues(Map values)`	put all the key/value pairs of the provided Map whose key resolves into a column of this instance into it
`$instance.insert()`	inserts this instance in the database
`$instance.update()`	updates this instance with current values and return the boolean success state of the operation
`$instance.update( Map values )`	updates this instance with values in `values` and return the boolean success state of the operation
`$instance.delete()`	deletes this instance from the database
`$instance.upsert()`	upserts (updates or inserts) this instance with current values and return the boolean success state of the operation
`$instance.upsert( Map values )`	upserts (updates or inserts) this instance with values in `values` and return the boolean success state of the operation

$instance.column

Gets or sets the value of the specified column.

Previous title was: $book.title #set($book.title = $newtitle ) New title is: $book.title

$instance.attribute

Returns an attribute of this instance. Scalar and row attributes are evaluated directly while row set attributes must be evaluated later using #foreach. You can optionally directly specify external parameters values:

## requires the Velosurf uberspector Number of events for user $user.name for $date: $user.events_count({'day':$date}) ## without the Velosurf uberspector Number of events for user $user.name for $date: $user.getWithParams('events_count',{'day':$date})

$instance.action

Executes an action of this instance and returns the number of affected rows.

$book.setOutOfStock

You can optionally directly specify external parameters values:

## requires the Velosurf uberspector $book.setOutOfStock({'sold_out',$date}) ## without the Velosurf uberspector $book.getWithParams('setOutOfStock',{'sold_out',$date})

$instance.entity

Returns this instance's entity or null for a generic instance.

$instance.primaryKey

Returns a list of two-elements maps ('name'→name and 'value'→value) of this instance's key column(s).

#foreach($key in $instance.primaryKey) <input type="hidden" name="$key.name" value="$key.value" #end

$instance.validate()

Validates the data currently set in the instance.

$instance.putAll(Map values)

Put all the key/value pairs of the provided Map in this instance.

$instance.setColumnValues(Map values)

Put the key/value pairs of the provided Map whose key resolve to a column name of this instance into it, and ignore other values.