Define database views

Database views are searchable database constructs based on a native SQL query with a SELECT statement or an Alfabet query. A database view is like a virtual table represented by the native SQL query or Alfabet query. The query syntax used to define database views in native SQL on the database server level is:

• CREATE VIEW ViewTableName AS SELECT ViewContentDefinition

For the Alfabet database, database views can be created and maintained in the tool Alfabet Expand without the need to directly access and configure the database server.

A configuration object Database View is available for defining the database view. The name of the database view table and the SELECT statement for the content of the database view are defined in separate attributes of the configuration object. When the configuration is saved, the CREATE_VIEW statement for creating the view is automatically built and executed based on the defined information.

The content of the table resulting from the output of the native SQL query or Alfabet query defined as ViewContentDefinition is not stored in the database. Instead, the native SQL query is executed each time data is requested from the database view table defined with ViewTableName by any other native SQL query or other mechanism targeting the database view table. Targeting the database view table in a native SQL query is done in the same way as targeting a standard database table. In the Alfabet query language targeting of a database view table is not supported.

Database views do not store data and therefore take up very little database space. They offer the following advantages for building queries for example for reporting:

• Simplicity: Complex parts of queries with multiple joins across multiple database tables can be stored in a database view. Query definition during solution configuration can then be based on simple queries referencing the columns of the database view without any joins required.
• Reusability: If multiple queries in a configuration gather the same information from the tables in the database as part of the query output, a database view can be created to implement the part that is identical for all the queries.
• Consistency: The way for example complex KPI calculations are done in queries can be defined in database views and the results are then consistent in all queries defined for Alfabet.
• Easy debugging in case of database changes: Changes to the class model, for example when upgrading to a new Alfabet release or when deleting custom object class properties, may require re-definition of queries defined for configuring Alfabet. If information is first gathered in a database view and then re-used in many queries that are defined for configuring Alfabet, you do not have to change all queries but only the underlying database view query if a change in the class model requires a re-definition of the query content. In addition, a mechanism is implemented in Alfabet Expand that can be used to detect errors in database view configurations after changes to the class model.

The content of a database view can either be defined as a native SQL query or an Alfabet query. The result dataset is built as follows depending on the type of query defined:

• Native SQL: For native SQL queries, the columns and column name of the database view table are derived from the SELECT statement of the native SQL query. Unlike other native SQL query definitions for configuring Alfabet, the first definition of the SELECT statement is NOT removed from the resulting dataset. The complete definition in the SELECT statement results in database view table columns.

  The table names defined as for example APPLICATION.NAME will result in a column name NAME only. To avoid problems with name specifications it is recommended to define an alias with the desired name in the SELECT statement for the database view.

• Alfabet Query: Which properties of the object classes added to the query are added to the database view as table columns depend on the following settings:
  • If Show properties are defined for the Alfabet query, the number of columns correspond to the number of show properties defined. Please note that Show Properties of the type RoleType and Indicator are ignored. Only Show Properties of the type Property are used to create columns in the database view.
  • If no show properties are defined, all properties except for properties of the type ReferenceArray are added to the database view. Optionally, properties of defined data types can be excluded from the database view table by setting the attribute Filter Show Properties of the database view.

  The column names in the database view are derived from the query in the following way:

  • The column name is concatenated as object class caption followed by property caption delimited by a whitespace. For example for the property Start Date of an Application , the resulting column name is Application Start Date. Please note that special characters are not stripped from the captions when building the database view column captions. If no caption is defined, the Name of the object class or object class property is used.
  • If Show Properties are defined and an Alias is defined for a property in the Show Properties, the defined Alias is used instead of the concatenated column name.
  • If an Alias is defined for the object class in the Alfabet query, the alias is used in the column name instead of the class name. An alias can be used for example to avoid name abbreviation by the system for column names longer than 30 characters.
  • If the resulting column name is longer than 30 characters, the name will be cut after 30 characters, corresponding to the string length restrictions that apply on Oracle® database servers. Depending on the property caption, this might cause problems if the resulting column names are identical in the first 30 characters. Alias definitions in the query can help solving such problems.