Page History

...

When a user types a value into a text field (which is bound to a specific column of the database table) and clicks out, the Servoy Application Server issues a SQL update command to the database to modify the selected record. The resulting change is also broadcast|display/DOCS/Data+Broadcasting||||||||||||\ to all connected clients.

...

The fundamental unit of data binding in both the GUI and the API is the Servoy Foundset|display/DOCS/Foundsets+Concepts||||||||||||\ object.

Client Cache

...

Servoy's find mode can be used to easily satisfy even complex search requirements. Remember that any related foundset may be used to enter criteria and that any number of search records may be used in any foundset and any operators may be used in combination for every data provider.

Servoy's data layer offers an assortment of design-time settings, defined at the table and column level,
to specify metadata and data-bound business rules.

Column Properties

Servoy will read column definitions from relational database tables. The properties that it reads include:

Column Name

This is the name that is returned by the JDBC Driver. The name will always be displayed as lower case. The column name is also used as the Data Provider ID when working with foundsets and records.

Data Type

While relation databases support many different data types, Servoy will generalize the data type of a column into one of five general types. In this way, Servoy can support a wide range of database vendors. The five generalized data types include the following:

1. Text: Any alpha-numeric characters (i.e. char, varchar, memo, CLOB, etc.)
2. Integer: Whole numbers (i.e. bit, short, long, bigint, etc.)
3. Number: Decimal numbers (i.e. float, double, etc)
4. Datetime: Temporal values (i.e. date, datetime, timestamp, etc.)
5. Media: Binary data (i.e. BLOB)

Column Length

For certain data types, databases must enforce the amount of storage allocated to single column for a single record. Data types which accommodate variable length entries, such as text, decimal numbers and binary data will have a length property. Servoy will infer and display this property in the column definition.

Row Identifiers

Servoy is designed to work with regular database tables as well as SQL Views. Regular database tables will have a primary key, consisting of one or more columns, who's value uniquely identifies a record in the table. Servoy will infer the primary key from the database table. However, in the case of SQL Views, which don't have a built-in primary key, the developer must specify which column(s) can be considered the unique row identifier.

Null Values Allowed

Relational database tables may enforce non-null constraints on certain columns, typically for primary key and other essential columns. Servoy will infer from any such constraints from database table and reflect

Column Meta Data

Column definitions include several metadata properties, which store information that is used both in development and at runtime.

Title

The Title property of a column is simply the human-readable name for a column. When a field is placed on a form with the Place with labels option, the label's text property will be initialized to the title property for the column to reach the field is bound. For multilingual applications, it is ideal to populate a column's title property with an i18n message key, thus allowing field labels to default to message key, which is translated at runtime.

Default Format

The Default Format property of a column will enforce the formatting that is used when the column is bound to a field element. The field element's format property will assume the default format of the column unless it is overridden in the element.

Foreign Type

This is a simple metadata property to indicate that a column is a foreign key to another table in the same database. One can set the Foreign Type property to the target table. This provides metadata so developers will know that a column is used as a foreign key. Servoy will use this information when new relations are created between the tables and auto-fill the keys. This property is also used by the mergeRecords method of the databaseManager API to update any affected related records, such that they'll reference a new key. 

Exclude Flag

Enabling a column's Excluded Flag will completely exclude a column from the Servoy runtime environment. This means that Servoy will exclude this column for every query that it issues. This option is ideal to enforce that certain columns are never available in a Servoy application.

UUID Flag

Servoy supports the use of Universally Unique Identifiers (UUID). A UUID is a 16-byte number which can be (practically) guaranteed to be unique across computing environments, making it ideal to use for sequences in scenarios where traditional numeric sequences are not adequate, for example when syncing data which is generated offline. It is generally not feasible to store UUIDs as numeric data types because the number is so large. Rather UUIDs are most easily stored as 36-character strings. When using a text column to store UUIDs, one should mark the column's UUID flag. Thus, Servoy will provide programmatic access to this column in the form of a built-in UUID data type, which allows both string and byte representation.

Description

A column's description property is a simple container for additional metadata, such as programmer notes about the column's purpose, etc.

Auto-Enter Definitions

Servoy provides several ways in which a column may be automatically populated when a record is created. Some of the auto-enter options are also applicable when an existing record is updated. 

System Values

...

Database-Managed

...

Indicates that the value is deferred to the database at the time of insert. The value is populated and controlled by the database and it will not be overwritten from Servoy.

...

Creation User UID

...

The UID parameter that was supplied at the time of login, entered at the time of record creation.

...

Modification User UID

...

The UID parameter that was supplied at the time of [login|https://wiki.servoy.com:8443/display/DOCS/Security#Security-login|Link to login method in reference guide], reentered each time the record is modified.

...

Creation Datetime

...

The current date and time on the client, entered at the time of record creation.

...

Creation Server Datetime

...

The current date and time on the application server, entered at the time of record creation.

...

Modification Datetime

...

The current date and time on the client, reentered each time the record is modified.

...

Modification Server Datetime

...

The current date and time on the application server, reentered each time the record is modified.

Custom Value

A custom value is simply a literal value (i.e. 'Blue', 1.5) which may be used as a default. This option is only available for Integer, Number and Text data types.

Database Default

This indicates that the value is deferred to the database at the time of insert. However, unlike Database-Managed system values, this value can be modified from Servoy after the record is inserted.

Lookup Value

Lookup Values provide the option to auto-enter a value that is contextual to the record being inserted. Options include any of the record's data providers, any data providers from foundsets related to the record, as well as an global relations or variables.

Sequence

Sequences may be used to auto-increment a column's value. This is ideal for populating primary key columns, which must be unique.

...

Servoy Sequence

...

This is a sequence which is defined in the application tier and managed by Servoy. The sequence will generate integer values using a given next value and step value.

In deployment, Servoy Sequences are stored in the repository database and there are options to recalculate the sequence's next value from existing data.

...

Database Sequence

...

Servoy will call a named sequence in the database to populate the value. The column will be populated and available prior to inserting the record

...

Database Identity

...

The sequential values are managed and populated by the database. The column is not populated until after the record is inserted

...

UUID Generation

...

Servoy will automatically populate a text column with a textual representation of a UUID. Be sure that the column's UUID Flag is also enabled.

Column Validation

Servoy provides an opportunity to implement validation rules at the column level. There are several built-in validation rules, which may be implemented at design-time. Additionally, custom validation rules may be written in as a JavaScript method which is bound to a column. A validation event occurs at the moment a record's value for a column changes. This may be the result of a user's action or some code which is executed. When validation fails, a Servoy Exception is raised for Invalid Input, which may be trapped in a solution's onError event hanlder.

Numeric Range Validation

Servoy provides built-in numeric validation for Integer and Number data types. Providing upper and lower bounds will automatically enforce that any value entered is between (inclusive) the range provided. Providing only a lower bound will enforce that any value entered is greater-than-or-equal-to the bound. Providing only an upper bound will enforce that any value entered is less-than-or-equal-to the bound.

Size/Length Validation

Servoy provides built-in validation for the size/length of a value in a column. This rule is applicable to Text and Media data types. Setting the length property for Text columns will enforce that value entered has a length of characters which is less-than-or-equal-to the length specified in the rule. Setting the size property for Media columns will enforce that value entered has a size, measured in number of bytes, which is less-than-or-equal-to the size specified in the rule.

RegEx Validation

Servoy offers the flexible pattern matching capability of Regular Expressions as a means to apply validation rules to Text columns. Providing a RegEx value will enforce that any value entered into the Text column must match on the expression. RegEx is an excellent way to match on patterns, such as phone numbers, email addresses, and much more. RegExLib is a useful site containing user-generated libraries of expressions to suit many needs.

Email Validation

Servoy provides a built-in email validation rule, which enforces that any Text column matches a pattern which is similar to email addresses. This pattern is ideal for most use cases. However, developers may implement their own RegEx validation to ensure an exact match on the pattern of their choice.

Custom Validation

Apart from the built-in validation rules, Servoy allows developers to author business logic to enforce their own validation rule for a column. A Global Method may be bound to a column, such that when a validation event occurs for the column, the method is invoked. The value that is entered is passed into the method and a developer may then execute any evaluation of the value before returning a boolean value; true indicates that validation is successful.

/**
 * Custom Validation rule: Must be Dog or Cat (case insensitive)
 * @param {Object} obj The value that will be validated
 * @returns {Boolean} True when successful
 * @properties={typeid:24,uuid:"655B9F0E-A1A2-4B0B-84CD-8E299546DB57"}
 */
function validateColumn(obj) {
	return 'Dog'.equalsIgnoreCase(obj) || 'Cat'.equalsIgnoreCase(obj);
}

Column Conversion

Some scenarios require that a value be stored in a database column in one form and written to and read from the database column in another form. Servoy supports this requirement with a feature called Column Conversion and it has two applications, String Serialization and Global Method Conversion.

String Serialization

Servoy supports object persistence using String Serialization, which involves the conversion of a runtime object into a string format, which can then persist in a database column. When the column is read from the database, the persistent string will be deserialized back into a runtime object. Because Servoy uses JavaScript as its scripting language, runtime objects will be serialized into standard JSON format. 

//  Construct an object to capture some custom settings and write it directly to a Text column called 'custom_settings'
var obj = new Object();
obj.name = 'foobar';
obj.message = 'Hello World'; 

// at this point it is serialized into the string: "{name:'foobar',message:'Hello World'}"
custom_settings = obj;
databaseManager.saveData();

// ...read object properties at a later time...
application.output(custom_settings.message + 'My name is: ' + custom_settings.name);

// For Example
my_data_provider.property = 'Foobar'; This will have no effect on the data provider

// Instead
var obj = my_data_provider; // read the data provider into a runtime object
obj.property = 'Foo Bar';   // Modify the Object's instance properties
my_data_provider = obj;     // And reassign it to the data providerdatabaseManager.saveData();

Global Method Conversion

Servoy allows a database column to be bound to custom business logic, giving developers control over how a value is converted when it is written to, and read from the data provider.

The column is bound to two methods which facilitate the conversion between the Object Value and the Database Value. A developer may also specify an optional Object Data Type, prompting Servoy to provide the data in an alternate data type in lieu of the default column type. This is useful when values are stored in a non-standard storage type to accommodate legacy systems, but should be treated like standard data type in the runtime. 

...

Object to Database Method

...

This method is called anytime a value is written to the data provider. It will be called regardless of the origin of the action, i.e. GUI event or programmatically. It will be called before data is committed to the database.

Parameters

Object - The value that is being written to the data provider

String - The column's data type: TEXT, INTEGER, NUMBER, DATETIME, MEDIA

Returns

Object - The converted value that will actually be written to the data provider.

Example

Perhaps the most classic use case is the conversion between SI Units, where a database is standardized on a certain unit, but an application requires that values be written and read in multiple units, often to support different locales / preferences. Imagine a database column for temperature, which is standardized on Celsius, but an application which allows data entry in Celsius, Fahrenheit and Kelvin.

/**
 * This method auto-converts from client units to Celsius as the value is being written to the data provider
 * @parameter {Object} value The value of the runtime object
 * @parameter {String} columnType The data type of the column
 * @returns {Object} The value converted into celsius
 * @properties={typeid:24,uuid:"303ACB93-3B0E-4B9C-9550-D78FF17343C2"}
 */
function objectToDB(value, columnType) {

	// evaluate client unit settings
	switch(tempUnits){

		// Already in C, just return it as is
		case C :
			return value;

		// Fahrenheit,use conversion formula
		case F :
			return (5/9)*(value-32);

		// Kelvin,use conversion formula
		case K :
			return value - 273;
	}
}

...

Database to Object Method

...

This method is called anytime a value is read from the data provider. It will be called when it is displayed in the GUI or read programmatically.

Parameters

Object - The value that is being read from the data provider

String - The column's data type: TEXT, INTEGER, NUMBER, DATETIME, MEDIA

Returns

Object - The converted value that will actually be displayed in the GUI and read programmatically.

Example

Perhaps the most classic use case is the conversion between SI Units, where a database is standardized on a certain unit, but an application requires that values be written and read in multiple units, often to support different locales / preferences. Imagine a database column for temperature, which is standardized on Celsius, but an application which allows data entry in Celsius, Fahrenheit and Kelvin.

/**
 * This method converts database values (Celsius) into the current client units for degrees
 * @parameter {Object} value The value stored in the column
 * @parameter {String} columnType The data type of the column
 * @returns {Object} The value that was converted into current client units
 * @properties={typeid:24,uuid:"63C4D552-531C-48DB-A6C6-ED02F4603C20"}
 */
function dbToObject(value, columnType) {

	//	evaluate client unit settings
	switch(tempUnits){

		// Already using C, just return it as is
		case C :
			return value;

		// Fahrenheit, use conversion formula
		case F :
			return (9/5) * value + 32;

		// Kelvin, use conversion formula
		case K :
			return value + 273;
	}
}
}

...

Converted Object Type

...

One can optionally specify the data type of the Object Value. This is useful in situations where the stored value is a different data type than the object value

Example

The application talks to a database that is storing dates as 8-character text columns to support legacy applications. By setting the Converted Object Type setting to DATETIME, Servoy will treat the column as a date object. Moreover, the two conversion methods written by the developer should assume the Object Value is a Date object.

/**
 * This method converts Text data stored in the database column, presenting it as Date object
 * @parameter {Object} value The value stored in the column
 * @parameter {String} columnType The data type of the column
 * @returns {Object} The value that was converted
 * @properties={typeid:24,uuid:"16BDC049-E63B-47C4-B49C-595D916FD51B"}
 */
function dbToObj(value, columnType) {
	return utils.dateFormat(value,'MMddyyyy');
}

Calculations

A Calculation is very much like a database column, except that its value, rather than being stored, is dynamically computed each time it is requested. This is achieved by creating a JavaScript function which is bound to a database table. The function takes no arguments and returns a value, which like a real database column, is declared to be one of the five general data types. The scope of the JavaScript function is an individual record object. Therefore any of the record's other data providers, and related foundsets are immediately available, as well as global variables and globally related foundsets.

A calculation is declared at the solution level, and is available throughout the solution in which it is declared, as well as any modules containing it. To support this, there is one JavaScript file per table, per solution, which holds calculation functions. For example, the database tables 'orders' may have a corresponding file 'orders_calculations.js' in any solution. And this file could contain many individual calculation functions.

Just like real database columns, calculations may be placed as fields on forms, used in data labels, and requested programmatically.

Example

A simple calculation subtotal on a database table order_details which yields a Number value; the unit price, multiplied by the quantity, with a discount applied.

/**/**
 * @properties={type:6,typeid:36,uuid:"644DCF7D-11C7-475E-82CD-F4F60ED00D77"}
 */
function subtotal()
{
	return unitprice * quantity * (1 - discount);
}

This function is executed for the scope of an individual record. Notice that the record's other data providers, unitprice and quantity are immediately available.

This calculation could now be placed on a form or used in a method, just like any other real database column. The next code example shows another calculation, this time declared for the orders table, which uses the previous calculation to determine the total amount for the entire order.

/**/**
 * @properties={type:6,typeid:36,uuid:"d5458801-bbd2-43a1-994d-4ac2f6d12595"}
 */
function order_total()
{
	var sum = 0;
	for(var i = 1; i <= orders_to_order_details.getSize(); i++){
		sum += orders_to_order_details.getRecord(i).subtotal;
	}
	return sum + frieght;
}

Notice that the related foundset, orders_to_order_details, a property of an order record, is immediately available. Also notice that the previous calculation, subtotal, may be referenced, just like any other column. Note that while, calculations are a JavaScript function, unlike regular methods, one does not use the parentheses to invoke their value. Both calculations may be placed on forms like any other data provider. (See image below - calculations are highlighted in green) Image Added

...

Stored Calculations

...

Calculations may be optionally stored back to a real database column. This is called a Stored Calculation, and is achieved by creating a calculation which is the same name and data type as a real column. When the calculation is referenced, it will be executed and its result will be stored back in the database column. 

Aggregations

An aggregation is a data provider which represents a database column that is aggregated over a set of records. At design-time aggregations have the following properties:

• Name - The name by which the aggregation will be available as a data provider throughout the solution in which it is declared, as well as any modules containing it.
• Type - There are five types of aggregations:
  • Count - The number of records in an entire foundset containing a non-null value for a column
  • Sum - The sum of all the values for numeric column in an entire foundset
  • Minimum - The smallest value for a numeric column in an entire foundset
  • Maximum - The largest value for a numeric column in an entire foundset
  • Average - The average value for a numeric column in an entire foundset
• Column - The column containing values that are aggregated

At runtime, aggregations are computed in the context of a foundset. The value is derived from a SQL query, which takes the form of a SQL Aggregate Function and appends the WHERE clause used by the foundset's query.

Example

Assume an aggregation, record_count, declared on the customer table. The aggregation type is count and the column is customerid. The aggregation is available for any foundset based on the customers table.

function printRecordCcount(event) {function printRecordCcount(event) {
	application.output(record_count);		// print record count before find
	if(foundset.find()){				// Find all customers where city starts with 'B'
		city = 'B%';
		foundset.search();
		application.output(record_count);	// print the record count after find
	}
}

After the find, the aggregation is re-queried using the foundset's new WHERE clause.

SELECT COUNT(customerid) AS record_count FROM customers WHERE city LIKE ? LIMIT ?

Table Events

Servoy provides an event model at the data layer, giving developers the opportunity to implement validation and execute business logic just before and after data changes are committed to the database. There are six Table Events, each of which may be bound to global methods.

The first three events occur just prior to the change being committed to the database. Moreover, the event handler has the opportunity to veto the event, preventing the change from being committed. This is and ideal location to implement fail-safe data rules.

• onRecordInsert - occurs prior to a new record being inserted into the table
• onRecordUpdate - occurs prior to an existing record being updated in the database
• onRecordDelete - occurs prior to an existing record being deleted from the database

An onRecordXXX event is bound to a global method, which is invoked when the event occurs. The record that is modified is passed in as an argument and the method can veto the change by returning false or throwing an exception.

Parameters

JSRecord - the record object that is to be inserted, updated or deleted

Returns

Boolean - Return true from this method to allow the change to commit to the database. Returning false will result in a Servoy Exception  with a SAVE FAILED error code, which may be handled in the solution's onError event handler.

Example

This is an example of an onRecordDelete handler for an invoices table. The data rule is that posted invoices will never be deleted by a the application.

/**/**
 * Record pre-delete trigger.
 * Validate the record to be deleted.
 * When false is returned the record will not be deleted in the database.
 * When an exception is thrown the record will also not be deleted in the database but it will be added to databaseManager.getFailedRecords(),
 * the thrown exception can be retrieved via record.exception.getValue().
 *
 * @param {JSRecord} record record that will be deleted
 * @returns {Boolean} true to allow a delete
 * @properties={typeid:24,uuid:"A3F02F99-B899-46BE-9125-66E4189F043F"}
 */
function onRecordDeleteInvoice(record) {

	if(record.is_posted)
		throw "Cannot delete a posted invoice";

	return true;
}

The next three events occur immediately following the commit to the database. This is an ideal mechanism to update the data model after data is known to have changed.

• afterRecordInsert - occurs subsequent to a new record being inserted into the table
• afterRecordUpdate - occurs subsequent to an existing record being updated in the database
• afterRecordDelete - occurs subsequent to an existing record being deleted from the database

An afterRecordXXX event is bound to a global method, which is invoked when the event occurs.

Parameters

JSRecord  - the record object that was recently inserted, updated or deleted

Example

This is an example of an afterRecordInsert handler for a projects table. The data rule is that a new project record will be linked, via the projects_users table, to the current user.

/**/**
 * Record after-insert trigger.
 *
 * @param {JSRecord} record record that is inserted
 *
 * @properties={typeid:24,uuid:"92834B20-1CAC-472F-B022-DD97FEFEA792"}
 */
function afterRecordInsert(record) {
	if(record.projects_to_projects_users.newRecord()){				// create a link record
		record.projects_to_projects_users.user_id = globals.currentUserID;	// associate to current user
		databaseManager.saveData();
	}
}

Editing Tables in Servoy

...