Columns

Overview

This guide will demonstrate how to work with Columns in your database tables, including add/dropping as well as column properties that affect the behavior of applications.

Get Started

To work with your database columns, you should first connect to your database and open the table in the Table Editor, where you will manage you columns.

Add a Column

While developers are free to use any DB admin tool to manage their database columns, it's easy to add a column in Servoy Developer.

Add a Column

1. In the Table Editor, click the Add button to create a column.

2. Set the property with a valid column name.

3. Choose the Data property from the list. Select the type of column you want to create.

4. Certain data types require that you set a value for the property

5. Save the editor. When you save, your new column is both written to the database and tracked in your Database Information file so changes are under revision control

Data Type Servoy uses a powerful data abstraction layer which generalizes SQL data types into six simple types. This ensures that your columns are compatible with any database and can be changed in the back-end at any time.

Drop a Column

While developers are free to use any DB admin tool to manage their database columns, it's easy to drop a column in Servoy Developer.

Drop a Column

To drop a column from your database table, select your column in the Table Editor and click the delete icon. You will be asked to confirm the delete.

No Undo When you drop a column from a database table, there is no undo. While you can re-add the column at any time, any data in that column will still be lost.

Unresolved Data Bindings When you drop a column, you may also create errors in your application if you have objects bound to that column, such as a field on a form. These errors can be resolved in Servoy Developer.

Manage Column Properties

You may use the Table Editor to manage additional properties of the column, such as the or the .

Manage Column Properties

To manage column properties, select your column in the Table Editor and click through the various tabs in the column properties.

Reference Documentation See the reference docs for a complete list of properties of a Column object and how to use them.

Auto Enter

You can configure the default value for a column by setting a value for the Auto Enter property. Below are some common examples of Auto Enter settings.

To enable an auto-enter setting for a column, select the column in the Table Editor and click the Auto Enter tab. Choose the auto-enter type for your column. Below are some examples of the various auto-enter types:

System Value

The System Value auto-enter type will automatically update the column value when the record is created or updated (depending on the type chosen). This setting is ideal for tracking when a record was created or updated and by which users.

Column Auto-Enter Creation Datetime

For example, to auto-fill a column with creation/modification timestamps or user Ids, select the auto-enter type System Value and choose the desired input type, such as creation server datetime.

Custom Value

You can auto-fill a column with a declared literal value by choosing the Custom Value auto-enter type.

Column Auto-Enter Custom Value

Once you have selected the type, enter the literal value of your choice.

Lookup Values

You can auto-fill a column with a value which is looked-up from elsewhere in your application, including your data model or your business logic by selecting the Lookup Value auto-enter type.

Column Auto-Enter Look-up Value

Once you have selected this type, click the ... button to open the Data Provider Chooser. Select your Data Provider or Method and click OK.

For example, when an order recorded is created, you can auto-fill the shipping address columns with a value which is looked up from the related customer address orders_to_customers.address.

Using Sequences

You can auto-fill a column with a value taken from a sequence defined in your database by choosing the auto-enter type Sequence.

Once you select the sequence option, enter the name of the sequence as it is defined in this table's database.

Reference Documentation

See the full reference docs for a complete list of options for the Auto-Enter feature.

Validation

You can declare data validation rules at the column level. There are several built-in validators to choose from, but it is also quite common for developers to implement their own validators in code by writing a method validator in JavaScript.

Built-In Validators

To apply validation to your column using one of the built-in validators, select your column in the Table Editor, then click the Validation tab.

Column Validation - Numeric Range Validator

Choose the the validator of your choice. Some validators, such as a Size Validator or RegEx Validator will have an additional input value.

For example, to enforce that your order_details.discount column is between 0 and 1, you could apply a Number Range Validator to your column.

Reference Documentation

For a complete list of column validator properties, see the reference docs.

When is validation enforced?

It is recommended that you leave the validator property enabled to executeonlyon save/validate. Alternatively, validators will run at any moment a column value changes. While this is more strict and difficult to manage in the user experience.

What happens when validation fails?

Failed validation is will apply validation markers to the record. If a save was attempted it will fail. While the declared rule is always enforced, the user experience is not prescribed. The developer may show any validation errors as they see fit.

Method Validator

You can apply custom validation to a column choosing a Global Method Validator. This essentially binds a column to a custom method that will handle the validation event.

Column Validation - Custom Method

The method will take the new column value as input and evaluate if the value is valid. The method may add validation markers to describe the problem.

/**
 * Validate Ship Date
 *
 * @param value The value to be validated.
 * @param {String} dataproviderid The dataprovider name that is being validated (to use for reporting and problem).
 * @param {JSRecordMarkers} recordMarkers The recordMarkers object to report problems on.
 * @param customObject The optional customObject given by the caller.
 * @properties={typeid:24,uuid:"A5B42DDA-9886-4251-992A-D71C199E2191"}
 */
function isShipDateValid(value, dataproviderid, recordMarkers, customObject) {
	if(value < recordMarkers.record.orderdate){
		recordMarkers.report(
			'Ship Date cannot be earlier than Order Date',
			'shippeddate',
			LOGGINGLEVEL.ERROR);
	}
}

In this example, the custom logic will check if the shippeddate column is earlier than the orderdate column and flag the record with an error message.

Scripted Validation Logic

This guide is concerned with the no-code aspects of setting up a data model. For more information on writing scripting, please see the Programming Guide section on validation.

Conversion

Some scenarios require that a value is 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 three implementations: Serialization and Custom Converters.

Serialization Converters

Servoy supports object persistence using Serialization, which converts a runtime (JavaScript) object into a String or Binary format, which can then be persisted 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.

Column Conversion - Serialization

To enable column conversion using serialization, select your column in the Table Editor and click the Conversion tab, then select StringSerializer or BlobSerializer depending on the column's data type.

//  Construct an object to capture some info in a database column "custom_settings"
var obj = new Object();
obj.name = 'foobar';
obj.message = 'Hello World'; 
 
// Here it is serialized when it is saved because serialization enabled on the column
custom_settings = obj;
databaseManager.saveData();
 
// ...read object properties at a later time...
application.output(custom_settings.message + 'My name is: ' + custom_settings.name);

When the column is used in code, the data is automatically serialized as shown in this sample.

String Serialization can only be used for column type TEXT

Blob Serialization can only be used for column type MEDIA

Custom Converters

A common use case is to store data in a single standardized unit and allow clients to read and write in a unit of their choice. For example, suppose you store product dimensions in centimeters and allow clients to use centimeters or inches. You could write a custom converter to switch between units.

Column Conversion - Custom Converter

1. To create a Custom Converter, select you column in the Table Editor and click the Conversion tab. Then select the GlobalMethodConverter option.

2. Double-click the DB-Value-to-Object event and create a new method in a scope. This handler will convert values as they are read from the database

3. Double-click the Object-to-DB-Value event and create a new method in a scope. This handler will convert values as they are written to the database.

4. Set the converted object type. This is needed in situations where the database value is a different data type than the object value. When they are the same, you can leave it "as-is".

5. Implement the custom logic in your methods.

Example Convert from Celsius to client units when reading from the database

DB-2-Object Converter

/**
 * 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;
    }
}

Example Convert from client units back to Celsius when writing to the DB

/**
 * This method auto-converts from client units to Celsius as the value is being written
 * @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;
    }
}

Additional Resources

Programming Guide

In Servoy, developers can write scripts and program logic using robust data APIs. For mor information see the Programming Guide's chapter, Working With Data.

Reference Documentation

See our complete reference documentation on Column objects.