Defining a Derived View¶
The administrator can use the base views of the system to define new relations. These new relations are called derived views.
Derived views are created through the statement
CREATE [ OR REPLACE ] VIEW <name:identifier> [ FOLDER = <literal> ] [ DESCRIPTION = <literal> ] [ <primary key> ] [ ( <field properties> [, <field properties> ]* ) ] AS <select> [ USING PARAMETERS ( <parameter> [, <parameter> ]* ) ] [ ORDER BY <field name> [ ASC | DESC ] [, <field name> [ ASC | DESC ] ]* ] [ WITH [ CASCADED | LOCAL ] CHECK OPTION ] [ CONTEXT ( <context information> [, <context information>]* ) ] <field properties> ::= <name:identifier> ( <property list> ) <parameter> ::= <fieldname:identifier> : <fieldtype:identifier> [<default:literal>]
As you can see, a name and the query that defines it are specified, when
creating a view. The query is specified using the syntax of the
SELECT statement, which has been explained in detail in the
section Queries: SELECT Statement.
Therefore, the administrator can create new derived views by combining other existing views using operators such as unions, joins, cartesian products, selections, projections, group-by operations, intersections, the minus operation and the flatten operation.
Furthermore, existing derived views can be also used to create new derived views, allowing view trees with as many levels as required.
For example, considering the views A, B and R as base relations (those that directly access the sources to obtain their data) the administrator can define a view G as the join of the result of applying the union (A, B) with R, as can be seen in the figure below.
ORDER BY clause indicates that when querying the view,
the results will be ordered by those field(s).
ASC sorts in
ascending order and
DESC, in descending order. If
DESC are omitted, Virtual DataPort will sort in ascending order.
The creation of a view also accepts the SQL standard clause
WITH CHECK OPTION, which is related to the updating of view contents
DELETE statements. The function of
this modifier is described in detail in the section Use of WITH CHECK OPTION.
The use of the
OR REPLACE modifier specifies that, if there is a
view with the name indicated, this must be replaced by the new view.
Where, due to the change in view definition, the query capabilities of some derived views have been altered (e.g. due to the
addition of another field or a query restriction that did not previously
exist), Virtual DataPort will update the schema and query capabilities of the
upper level derived views wherever possible.
PRIMARY KEY clause sets the definition of the primary key of the
view. See more information about primary keys in the section Primary Keys of Views of the Administration Guide.
USING PARAMETERS defines the parameters of the derived
view. See more about this in the section Parameters of Derived Views
of the Administration Guide.