>> Table of Contents >> Article
<< Tables Index Forms >>

Views

Basically "views" are stored select-statement. Thus they may span multiple tables, aggregate data or hide information. To the user they may act like real tables. Views are widely used for forms and user interfaces.
However, you should note that there are some restrictions. If you want to change data in a view, the view has to be updatable. The support for this features depends on the chosen DBMS. Some vendors limit this to certain (very simple) scenarios. Basically spoken, an "updatable view" must know the primary key and source table for each and every column in the view. The where-clause of the statement specifies some sort of constraint and a view may demand, that every updated or inserted column is still part of the view and thus justifying this constraint.

Element View

  ELEMENT view (description?, grant*, field+, select*)>
  ATTRIBUTE
       name        string
       readonly    bool
       tables      string
       where       string
       orderby     string
       sorting     string
       checkoption string
       title       string
Attributes
Attributes Type Mandatory Default Description
name string yes n/a An unique name that identifies this view. It should be lowercased and a valid XML and SQL identifier.
readonly bool - no You may set the view to be read-only to prevent any changes. A view that is "read-only" is not updatable.
tables nmtokens - n/a A comma-separated list of tables used in the current view. If the list contains more than one table, the first is the base table and all other tables are joined to this.
For don't forget to define a where-clause for all joined tables.
where string - n/a This is a generic information. It sets the where-clause of the view.
orderby string - n/a A comma-separated list of columns, after whose the output is sorted. By default the table-output is ordered by it's primary-key.
sorting string - ascending
ascending
sort in ascending order
descending
sort in descending order
checkoption string - none
none
no check option
cascaded
recursive check
local
local check only
title string - n/a The title is a label text that should be displayed in the UI when viewing this object.
Description

The "view" element defines the name, documentation and generic properties of a database view. These are it's base tables, where-clause and other information, which should allow an application to map elements of the view to elements of real tables. This should enable an implementation to simulate simple and updatable view for such DBMS, that don't support this feature. An implementation may use the generic information for the simulation of views. The implementation may throw an error message, if it is not possible to simulate the view and the DBMS doesn't support the requested feature.

Implementation

The software must consider a view to be "updatable", unless the attribute "readonly" is set to "yes". It may however decide that the view is not updatable, if a required primary key is missing. Where applicable the implementation may automatically add a primary key column to the generic query, to make the view updatable. For DBMS that don't support updatable views, the implementation may simulate these by generating insert or update statements based on the given generic information. The implementation may throw an error if the user tries to update a view for such DBMS and it is unable to simulate the desired behavior.

The attribute "where" defined the where-clause of the view. This information must be taken into account when simulating the views. The implementation may define the required syntax of this element itself. Note! The following example is not recommended "time < now()" since the function "now()" may not be compatible between various DBMS. However, you may define explicit SQL-statements for any target-DBMS of your choice. The syntax for generic where-clauses, as supported by the Yana Framework, is: {[column]=[value]{ AND [column]=[value]}*}.

The attribute "checkoption" influences the evaluation of the where-clause. The difference between 'local' and 'cascaded' applies only to situations, where a view is built recursively upon another view and the parent view declares a check-option itself. If this is the case, the setting 'local' will prevent the DBS from recursively evaluating the check option(s) of the parent view(s). Note that this is not supported by all DBMS.
For example, MySQL and PostgreSQL both support this feature, while MSSQL does not.

Element Select

  ELEMENT select (#PCDATA)
  ATTRIBUTE
       dbms     string
Attributes
Attributes Type Mandatory Default Description
dbms string - generic The name of the target DBMS. The value "generic" means that the definition is suitable for any DBMS. Usually this is used as a fall-back option for DBMS you haven't thought of when creating the database structure or for those that simply doesn't have the feature in question.
Description

The "select" element is a DBMS-dependent SQL-statement. It must comply with the defined names of the base tables, fields and other properties of the view. For simple views the generic information may already be enough, so that no additional select elements need to be defined. Thus you should avoid the select element where possible.

Element Field

  ELEMENT field EMPTY
  ATTRIBUTE
       table        string
       column       string
       alias        string
Attributes
Attributes Type Mandatory Default Description
table string - n/a Name of base table
column string - n/a Name of base column
alias string - n/a optional alias
Description

A column reference, that identifies the name of a column in the view (see attribute "alias") with the names of the physical table and column it is based on.

<< Tables Index Forms >>

Author: Thomas Meyer, www.yanaframework.net