Difference between revisions of "Data Validation"

Revision as of 06:06, 5 December 2017

Under Construction

Validation Rules

Validation rules prevent bad data being saved in a table.

Validating data in Lianja allows you to check data whilst it is being entered by the database user and will enable you to not save the data if it breaks any validation rules.

It is easy for an end user to make a mistake when they are entering data or even to leave out important details that must be entered, so we need to check this data when it is being entered into the database.

In an business application its not only users who perform CRUD (Create Read Update Delete) operations on data but also external processes such as loading tariffs, prices etc into tables periodically in batch.

There must be protections in place to prevent data corruption and invalid data being stored in a database.

This is where validation rules come into play.

Validation Rules in the UI

Data Types

Input masks

Validation Expressions

Validation in Custom Delegates

Required/Mandatory input

Cross Table Lookups

Choice Lists

Autosuggestions

Client-Side Validation in Web/Mobile Apps

Writing client-side JavaScript code is made easier by the fact that Lianja has many VFP compatible functions available for you to use. The complete list can be found here.

The Lianja HTML5 Client maintains local data cursors for tables that are actively being used in sections within pages. Each local data cursor has the concept of an "Active Record". As you navigate data and the sections are refreshed the "Active Record" for these local data cursors is kept in sync with what is displayed in the UI. In desktop apps, the Lianja/VFP database engine provides direct access to these using the familiar alias.fieldname notation.

In your Web and Mobile database Apps you can also get access to this data using Lianja.getCursor("tablename") and then use the getData() and setData() methods on the cursor object that is returned from Lianja.getCursor().

Additionally, seeing as the cursor state is known at any time you can use {alias.fieldname} macros in your validation expressions and any URLs that you have associated with WebView sections. Note that if you create a dynamic local cursor using Lianja.createCursor() then macros will not be evaluated against this cursor, you need to do that manually in your custom JavaScript code.

Local cursors provide the ability to dynamically query and refresh section contents based on the values of fields in other sections.

Referencing an existing cursor

You can get a reference to an existing cursor in desktop, web or mobile apps. This is typically how you would inspect the Active record and it’s fields.

var orders = Lianja.getCursor("orders");
var name = orders.getData("name");
var unitcost = orders.getData("unitcost");
var quantity = orders.getData("quantity");
orders.setData("total", unitcode * quantity);
orders.update( 
    function() {    
        // onsuccess
        Lianja.showSuccessMessage("Record was updated");
        Lianja.showDocument("page:orders?action=refresh");
    },
    function() {
       // onerror
       Lianja.showErrorMessage("Record could not be updated", "Update failed");
    }
);

See the Cursor documentation for details of the properties and methods available.

Understanding macro substitution in the Lianja Web/Mobile Client

Just as you can use {expression} macros in the Lianja Desktop Client you can also use these in your Web and Mobile Apps.

Lianja.evaluate("calculateCustomerBalance('{customers.name}'"),
    function(result)
    {
        Lianja.getElementByID("mypage.mysection.myfield").text = result;
    },
    function(errormessage)
    {
    }
);
// execution continues before the result is returned

Macros are evaluated from left to right:

Lianja.evaluate("calculateCustomerBalance('{customers.name}','{customerid.id'}"),
    function(result)
    {
        Lianja.getElementByID("mypage.mysection.myfield").text = result;
    },
    function(errormessage)
    {
    }
);
// execution continues before the result is returned

Macros can be nested so that the inner macros are evaluated before the outer macros. This provides the ability to query information from the server and have that information substituted into another call to the server.

Lianja.evaluate("calculateCustomerBalance('{customers.name}'," + "{getCustomerID('{{customers.custid}}')}")),
    function(result)
    {
        Lianja.getElementByID("mypage.mysection.myfield").text = result;
    },
    function(errormessage)
    {
    }
);
// execution continues before the result is returned

This will result in the following macro substitutions being performed in this order.

{{customers.custid}} lets call this result3 {customers.name} let's call this result1 {getCustomerID("result3")} let's call this result2 Lianja.evaluate("calculateCustomerBalance('result1', "result2")

Bear in mind that just as in the Lianja Desktop Client we can specify validation expressions that use {...} macros and also use {} to be substituted for the current value of the UI control being validated.

Server-Side Validation in Web/Mobile Apps

There are several ways for client-side JavaScript code to call server-side procedures/functions.

Using Lianja.evaluate()

As we saw in the code examples above we can call server side procedures/functions using Lianja.evaluate().

If you want to write all your code in JavaScript you can also call server-side javascript using Lianja.evaluateJavaScript().

Seamless Integration using exports.conf

The exports.conf file enables a simplified approach for calling server-side business procedures directly from the JavaScript client.

This is accomplished by defining the libraries, functions and scripting language for the functions in the exports.conf file which can reside in the app and/or the library directory.

Example exports.conf file:

# This is an example exports.conf file
#
# Each line describes a function that will be available directly from JavaScript code in the client
#
# library, function, type
# (source library, name of the function, type: vfp or javascript)
# (php and python will be added later)
#
# or
#
# library, function 
# (implied type of vfp)
#
# or
#
# function 
# (file expected to exist with a .dbo extension)
#
mylib,myfunc1,vfp
myjslib,myfunc2,javascript
myfunc3,vfp
myfunc4

In the Lianja JavaScript Web/Mobile App the server-side functions can be called directly:

// Note that arguments are automatically converted into the correct format for the 
// remote function call and the function call is proxied
// using a synchronous  Lianja.evaluate() call
var result = myfunc1("hello world", 2015, true);
var result2 = myfunc2();
var result3 = myfunc3();

// When calling remote functions with objects as parameters the object is automatically 
// converted to JSON and sent base64 encoded
// This allows you to send and receive complete JSON encoded objects between the 
// client and the server
var result4 = myfunc4( {"name":"barry", "company":"Lianja" });

Validation Rules for Columns

Column Constraints

Validation Rules for Tables

Table Constraints