company logo

Database views

ODABA provides different ways to define views to the database. Typically, database views are defined in terms of a SELECT statement, which includes following query operations:

  • FROM - Defining query source as collection, path collection or/and product set of several collections.
  • WHERE - condition defining a filterfor the previous result collection or calling object
  • GROUP - provides a distinct set of subsets for previous result collection for a number of grouping attributes
  • SELECT - evaluates attributes for result instances
  • HAVING - condition defining a final filterfor the previous result collection
  • ORDER - defines an order for the previous result collection by creating an appropriate index
  • toFile | toDatabase - additional output operations

ODABA supports ad-hoc queries by means of SELECT statements or predefined views (VIEW definitions).

SELECT operation

In order to run ad-hoc queries, the SELECT statement or query operations may be used within OSI functions. When defining a SELECT statement, this is restricted to the query operations mentioned below.

Query operations are optional and may be passed in any order except SELECT, which introduces the query statement. In any case, operations will be processed in the sequence listed below, except SELECT, which is evaluated after GROUP BY. When using query operations in an operation path, the sequence of operands determines the processing sequence.

The traditional query format is supported for convenience, only. In contrast to traditional query languages, OSI considers each part of the operation as independent operation or function, which can be called for any collection. Thus, queries may also be defined as operation path (see below). Using an operation path, any number of query operations may be combined in the path in any order.

More details about defining and using query operations see "ODABA Script Interface - OSI" section "8.1.4    Query Operations".


// SELECT statement

// sequence of query operations except SELECT may change

// operand order does not affect processing order

SELECT ( parm_list ) [ FROM (parm_list ) ]

                 [ WHERE  ( condition ) ]

                 [ GROUP BY (parm_list ) ]

                 [ HAVING ( condition ) ]

                 [ ORDER BY (parm_list ) ]

                 [ TO FILE | TO DATABASE (out_spec) ] ;

// operatopn path

// query operations may apear as many times as necessary

// operand order determins processing order

[FROM(parm_list)][.WHERE(condition)]

                 [.GROUP(parm_list)].SELECT(parm_list)

                 [.HAVING(condition)][.ORDER(parm_list)];

                

View definition

VIEW definitions are considered as operations. View definitions may be provided via ODL script (see example below) or being defined using ClassEditor. Similar to SELECT statements, query operands defined in the VIEW definition are optional except the SELECT operation (or view body), which defines the data type for result instances.

Views may be defined as class methods operating on instance collections (references, relationships) or as static views operating on extents or extent paths.

More details about defining and using views see "ODABA Script Interface - OSI" section "5.6    View definition".

// Group Persons by age, sex

VIEW myView

  FROM ( Persons )

  GROUP BY (sex,

            string inc_group = (income < 1000   ? 'poor' :

                                income < 5000   ? 'medium':

                                income < 100000 ? 'rich':

                                                  'very rich' ),

            string age_group = (age < 20 ? 'young' :

                                age < 50 ? 'middle':

                                           'old' ) )

{ // SELECT attributes

  ATTRIBUTE {

    string    age_group;

    string    inc_group;

    Sex       sex;

    INT(10,2) inc = sum(income);

    INT(10,2) avr_inc = average(income); }; };

Notes:

Grouping attributes are not automatically attributes of the result structure, but have to be mentioned explicitly in the attribute list, in addition. Grouping attributes not referenced in the attribute list will be ignored for output.

Subtopics

  1. from - FROM operands
  2. where - Where specification
  3. group - Group operands
  4. GetSelect - Select expression
  5. having - Having specification
  6. order - Order specification
  7. Output specification