Computed Fields and Constraints

Tables can contain fields defined by expressions that are computed on the fly, called computed fields.  Constraints are related to computed fields in that they also are expressions computed on the fly.  Primarily used for data integrity checks, constraints are  expressions which must evaluate to a boolean value of true for a table record to be inserted.

 

Tables can be created with a query in the form:

 

CREATE TABLE <table> (<item>, ...)

 

Items created within a new table can be fields or indexes, or computed fields and constraints.   Computed fields are created in the form:

 

<field> <type> WITH [[ <context> ]] AS [[ <expression> ]]

 

Constraints are created in the form:

 

CONSTRAINT <constraint> WITH [[ <context> ]] AS [[ <condition> ]]

 

In both cases an optional WITH clause allows specifying the context for the computed field or constraint, for example, defining a function that the computed field's expression or the constraint's condition could utilize.  In both cases the AS clause specifies the expression that is evaluated.  In the case of the computed field the expression provides the value for that compute field.  In the case of a constraint, the expression must evaluate to TRUE for the condition required by the constraint to be fulfilled.

 

Example:  Create a table with a computed field

 

CREATE TABLE [Prices] (

  [mfd_id] INT64,

  [Cost] INT32,

  [Retail] INT32 AS [[ [Cost]*2 ]],

  INDEX [mfd_id_x] BTREE ([mfd_id])

);

 

The above SQL creates a table where if we open the table and enter 55 as a value for the Cost field of the first record the Retail field will automatically be populated with the value 110

 

Example: Create a table with a constraint

 

CREATE TABLE [Prices] (

  [mfd_id] INT64,

  [Cost] INT32,

  [Retail] INT32,

  CONSTRAINT [Profitable] AS [[ (

    [Retail] > [COST]*2

  ) ]],

  INDEX [mfd_id_x] BTREE ([mfd_id])

);

 

The above SQL creates a table with a constraint called Profitable such that a value specified for the Retail field in a record must be more than twice as large as the value in the Cost field.   We could not, for example, manually try to add a record to the table for which the value in the Retail field is not at least twice that which we have entered for the Cost field.  

Execution Context

The optional WITH clause for computed fields and constraints allows specifying one or more statements to prepare the execution context for the expression which computes values. This allows expressions for computed fields or constraints to use functions and scripts.  Each computed field or constraint uses its own execution context.  Functions and values defined in the context of a given computed field or constraint are inaccessible from other computed fields or constraints.

Examples

The following examples use the ALTER TABLE statement, assuming we have already created a table.

 

We have a table of provinces in Mexico to which we want to add a Country field that is populated with Mexico (since each province is in the country of Mexico).  Following are ways to do that:

 

ALTER TABLE [Mexico Table] (

  ADD [Country] nvarchar AS [[ 'Mexico' ]]

);

 

The above SQL adds a field with a static value.

 

ALTER TABLE [Prices] (

  ADD [Govt] INT32 AS [[ [Cost]*4 ]]

);

 

The above adds a computed field.  Adding records with values in the Cost field will automatically put a value four times that in the Govt field.

 

Computed fields can use an optional WITH clause to specify execution context.  The context can be sophisticated statements such as function definitions and scripts.

 

Add a computed field that defines a function for the execution context:

 

ALTER TABLE [Inventory] (

  ADD [ExtraStock] INT32

    WITH [[ FUNCTION f(@x INT32) INT32 AS @x+100 END; ]]

    AS [[ f([Stock]) ]]

);

 

The function f above adds 100 to the argument.   Defining it in the execution context makes it available for use in the expression that gives the value for the computed field.   Execution contexts can also specify global values.  

 

Add a computed field that uses the context to specify a global value:

 

ALTER TABLE [Inventory] (

  ADD [ExtraStock] INT32

    WITH [[ VALUE @mul INT32 = 500; ]]

    AS [[ [Stock] * @mul ]]

);

 

Add a computed field that uses an inline script to set insert date for each record:

 

ALTER TABLE t (

  ADD insertdate DATETIME

    WITH

[[

 

SCRIPT funcs ENGINE 'c#' [[

  class Script

  {

    static System.DateTime F() { return System.DateTime.Now; }

  }

]];

FUNCTION currentdate() DATETIME AS SCRIPT INLINE funcs ENTRY 'Script.F';

 

]]

    AS [[ currentdate() ]]

);

 

After running the above query, existing records in the table will get a new field called insertdate that contains the current date. If we wait a minute or so to allow the current time noticably to change, and then insert a new record either manually or using INSERT, the new record will have the new, now-current value in the insertdate field.

Constraints Examples

Constraints are similar in that they can use an optional WITH clause to specify an execution context, and their AS clause specifies an expression that is evaluated.  In the case of a constraint the expression must evaluate to TRUE for the condition required by the constraint to be fulfilled.

 

We have a table listing components that will be subject to some action we plan.  We want to add a constraint to that table that requires all of the component names to be names found in the [mfd_root] table, that is names of components in the project:

 

ALTER TABLE [My Components] (

  ADD CONSTRAINT Ncomp AS (

    [Component Name] IN (SELECT [Name] FROM [mfd_root])

  )

);

 

Add a constraint that uses a global value specified in the WITH clause:

 

ALTER TABLE t (

  ADD CONSTRAINT a_c

    WITH [[ VALUE @min INT32 = 0; ]]

    AS [[ a>=@min ]]

);

 

Opening a Project: Computed Fields or Constraints that Fail to Load

Loading a .map project file resolves expressions in computed fields and constraints in a way that allows successfully loading expressions from cross-referencing tables.  Loading a project file also allows expressions in computed fields and constraints to fail to load. All existing data including data in computed fields with expressions that failed to load is fully and safely accessible. Computed fields and constraints with expressions that failed to load can be safely removed.

Example: Computed fields and expressions that fail to load.

We create a table with a computed field that takes data from another table:

 

A countries table:

 

CREATE TABLE countries (

  mfd_id INT64, INDEX mfd_id_x BTREE (mfd_id),

  name NVARCHAR, INDEX name_x BTREE (name),

  footballperf NVARCHAR);

INSERT INTO countries (name, footballperf) VALUES

  ('France', 'champion'),

  ('Croatia', '#2'),

  ('Belgium', '#3'),

  ('United Kingdom', '#4');

 

A places table:

 

CREATE TABLE places (

  mfd_id INT64, INDEX mfd_id_x BTREE (mfd_id),

  name NVARCHAR,

  country NVARCHAR,

  countryfootballperf NVARCHAR

    WITH [[

      FUNCTION countryperf(@country NVARCHAR) NVARCHAR AS (

        SELECT footballperf FROM countries WHERE name=@country

      ) END;

    ]]

    AS [[ countryperf(country) ]]

);

 

In the computed field above, for each record in places, we go into countries, look up the record for that country and get the field value.

 

We insert some records to see how that works:

 

INSERT INTO places (name, country) VALUES

  ('Paris', 'France'),

  ('London', 'United Kingdom'),

  ('Lisbon', 'Portugal');

 

So far we have nothing out of the ordinary .  If we save the project as a .map file and then we close the project and re-open it from the .map file, it opens with no problems.

 

Suppose we now add a computed field that transports data in the reverse direction:

 

ALTER TABLE countries (

  ADD exampleplace NVARCHAR

    WITH [[

      FUNCTION anyplace(@country NVARCHAR) NVARCHAR AS (

        SELECT First(name) FROM places WHERE country=@country

      ) END;

    ]]

    AS [[ anyplace(name) ]]

);

 

In the above computed field, for each record in countries, we go into places, and look up any record corresponding to the country and get the field value.

 

The second computed field creates a puzzle for the loader. If the system starts by loading the countries table, it must try to load the expression for the exampleplace computed field.  To do that, the system must try to load the places table,  to see what fields it has and what types are those fields.   But then the places table will try to load the expression for the countryfootballperf computed field and that expression will try to load the countries table.

 

The result is a circular dependency: loading the countries table requires the places table to be loaded first, but loading the places table requires the countries table to be loaded first.   Neither of the two tables can load first.   Earlier versions of the system would abort the process and the .map project would refuse to open.  That was not helpful since a simple addition of a field to a table could render an entire .map file useless.   To eliminate such problems, current editions of Manifold use a different loading technique which allows both fields to load successfully.

 

We can see that new technology in action by deleting the countries table in the above example.  We now have a single table, places, with a computed field that tries to take data from  a countries table which no longer exists.

 

In previous versions of Manifold, if we saved the .map file and then closed the project and reopened the saved .map file, it would fail to load. In the current Manifold editions, the .map file  loads and the computed field that references a nonexistent countries table marks itself as having failed to load and returns NULL values.   That allows us to open the places table and to see all of the data that table otherwise contains.    We can insert new records, we can delete existing records,  and we can edit existing records.   Editing the country field, which normally would recompute the value of the computed field, will not result in an attempted recomputation.    Finally, we can delete the computed field that failed to load.  If desired, we could add a replacement field.

 

Example: Constraints with expressions that fail to load.

All of the discussion in the previous example applies to constraints as well.   We will demonstrate with a new project:

 

First, we create a countries table, the same as before:

 

CREATE TABLE countries (

  mfd_id INT64, INDEX mfd_id_x BTREE (mfd_id),

  name NVARCHAR, INDEX name_x BTREE (name),

  footballperf NVARCHAR);

INSERT INTO countries (name, footballperf) VALUES

  ('France', 'champion'),

  ('Croatia', '#2'),

  ('Belgium', '#3'),

  ('United Kingdom', '#4');

 

A places table, using a constraint:

 

CREATE TABLE places (

  mfd_id INT64, INDEX mfd_id_x BTREE (mfd_id),

  place NVARCHAR,

  country NVARCHAR,

  CONSTRAINT countryvalid_c

    AS [[ country IN (SELECT name FROM countries) ]]

);

 

We add some records:

 

INSERT INTO places (place, country)

  VALUES ('Paris', 'France');

INSERT INTO places (place, country)

  VALUES ('London', 'United Kingdom');

INSERT INTO places (place, country)

  VALUES ('Lisbon', 'Portugal'); -- fails

 

The first two records are successfully inserted, but the third INSERT INTO fails due to the constraint firing, as expected.   There is no Portugal in the countries table.

 

We now delete the countries table, we save the .map file for the project, and then we close the project and reload it again from the .map file.  Older versions of Manifold would not have opend the .map file.   Current versions of Manifold will open and load the .map, internally marking the constraint has having failed to load but otherwise bringing in all data.

 

The constraint in the places table can no longer find  a countries table that is used by the constraint, but the places table still loads. We cannot insert new records until the constraint is removed, because it keeps returning NULL values and that will rejects all new records.   However, the existing records are still there and once we delete the constraint we can add new records.  

See Also

Tables

 

Queries

 

SQL Statements

 

Example: Add a Computed Field to a Table - In this example we add a field to a table.  We first set the values for a field dynamically with a computed field using the Add Computed Field option in the New Field dialog.   We then illustrate what happens when we fill a field statically with values using the Add Field option.  Last, we show what the Add Component choice does in the New Field dialog.

 

Example: Add a Second Computed Geom Field to a Table  - We can create tables with more than one geom field in the table and then we can create drawings which use those additional geom fields.   This topic shows how to create a second geom that is a computed field based on the first geom.  The topic also shows some "real world" methods, such as how to remember the use of a geometry function to do what we want, and how to restore a geom that has been moved.    We close with some illustrations of how multiple geoms might be used, and how selection from any drawing or labels based on the same record selects the corresponding objects or labels in all other components based on that record.

 

Example: Create a Table with a Constraint - Create a simple table that includes a simple constraint upon one of its fields.