Difference between revisions of "Data Validation"

From Lianjapedia
Jump to: navigation, search
(Data Types)
(Data Types)
Line 20: Line 20:
 
===Data Types===
 
===Data Types===
  
Each value manipulated by Lianja Database has a data type. The data type of a value associates a fixed set of properties with the value. These properties cause Lianja to treat values of one data type differently from values of another. For example, you can add values of '''numeric''' data type, but not values of '''logical''' data type.
+
Each value manipulated by the Lianja Database engine has a data type. The data type of a value associates a fixed set of properties with the value. These properties cause Lianja to treat values of one data type differently from values of another. For example, you can add values of '''numeric''' data type, but not values of '''logical''' data type.
  
 
When you create a table, you must specify a data type for each of its columns. These data types define the domain of values that each column can contain.  For example, any attempt to store a character string into a numeric column will be rejected by the database engine and an error reported.
 
When you create a table, you must specify a data type for each of its columns. These data types define the domain of values that each column can contain.  For example, any attempt to store a character string into a numeric column will be rejected by the database engine and an error reported.

Revision as of 07:18, 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

Each value manipulated by the Lianja Database engine has a data type. The data type of a value associates a fixed set of properties with the value. These properties cause Lianja to treat values of one data type differently from values of another. For example, you can add values of numeric data type, but not values of logical data type.

When you create a table, you must specify a data type for each of its columns. These data types define the domain of values that each column can contain. For example, any attempt to store a character string into a numeric column will be rejected by the database engine and an error reported.

This behavior extends into the UI when a column is data bound to a UI Control e.g a TextBox. For each data bound UI Control, Lianja will restrict the domain of data based on the data type of its controlsource.

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

The following methods can be used to call server side procedures (JavaScript or Lianja/VFP).

Lianja.evaluate()

The Lianja.evaluate() method can be used to call a Lianja/VFP function/procedure.

var result = Lianja.evaluate("myproc()");

To call a procedure in a library, prefix the procedure name with the library name and '::'.

var result = Lianja.evaluate("mylib::mylibproc()");

Lianja.evaluateJavascript()

The Lianja.evaluateJavascript() method can be used to call a JavaScript function.

var result = Lianja.evaluateJavascript("myfunc()");

The file 'myfunc.js' should be in the app or library directory and contain a function with the same name as the file. If the 'myfunc.js' file does not exist in the app or library directory and the file 'server_functions.js' exists then the function 'myfunc' is assumed to be defined in the 'server_functions.js' file.

To call a function in a library, prefix the function name with the library name and '::'.

var result = Lianja.evaluateJavascript("myjslib::mylibfunc()");

An alternative syntax using the Lianja.evaluate() method is also available:

var result = Lianja.evaluate("javascript:myfunc()");
var result = Lianja.evaluate("javascript:myjslib::mylibfunc()");

exports.conf

A function can be called directly if it is declared in exports.conf.

var result = myfunc();


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

You can define constraints that are maintained at the database engine at column/field level. Details can be found Here.

Validation Rules for Tables

You can define constraints that are maintained at the database engine at table level. Details can be found SQL_Constraints|here.