Database views of the View type
Database views with the View type represent subsets of the underlying tables. Database views with the View type are mainly used to map roles. For example, the Department, Locality, and Profitcenter database views are subsets of the Basetree base table.
Database views with the View type are predefined database views. Templates and formatting rules can be defined for columns in these views.
The following information is used to define a database view of the View type.
Table 17: Properties for defining a database view of the View type
| Table |
Name of the table in the data model. |
| Type |
View type of table. |
| Base table |
Base table that the view is based on. |
| Condition for view definition |
Restricting condition for creating the database view as a WHERE clause for database queries. The condition relates to the underlying base table. |
| Columns |
A reference is required for each column of the database view to a column in the underlying base column. Make the assignment in the column definition. |
| Insert values |
Default settings for individual columns that are assigned when a new data set is added. The values are entered in VB.Net syntax. |
| Selection script |
Selection script as a VB.Net term, to determine during runtime whether the object passed belongs to the view. |
Example
The Department table is defined as a database view of the View type. When you enter data in the Department table, the UID_OrgRoot column should be populated with the QER-V-Department value.
Table 18: Example of defining a database view of type "View"
|
Table |
Department |
|
Type |
View |
|
Base table |
BaseTree |
|
Condition for view definition |
UID_OrgRoot = 'QER-V-Department' |
| Insert values |
base.putvalue("UID_OrgRoot", "QER-V-Department") |
| Selection script |
Value = (String.Equals($UID_OrgRoot$, "QER-V-Department", StringComparison.OrdinalIgnoreCase)) |
|
Columns -->base columns (excerpt from column definition) |
Department.DepartmentName-->BaseTree.Ident_Org
Department.Description-->BaseTree.Description |
|
Resulting view definition |
create view dbo.Department as
select
Ident_Org as DepartmentName,
Description as Description,
...
from BaseTree
where UID_OrgRoot = 'QER-V-Department' |
Related topics
Database views of the proxy type
Database views with the Proxy table type are union views of different tables. Columns are mapped between a database view of the Proxy type and the underlying tables by means of the column definitions and proxy view extensions. The DBQueue Processor calculates the actual view definition from the column mapping. This only takes into account tables that are not disabled by a preprocessor condition. Templates and formatting rules cannot be defined for columns in these views.
Database views of the Proxy type are mainly used for mapping the Unified Namespace. For example, the UNSRoot database view is used for mapping of the ADSDomain or LDAPDomain tables in the Unified Namespace.
The following information is used to define a database view of the Proxy type.
Table 19: Properties for defining a database view of the proxy type
| Table |
Name of the table in the data model. |
| Type |
Type of Proxy table |
| Additional view definition |
Database query generated as a SELECT statement for setting up the database view. View definition extensions are generated by the DBQueue Processor. The following are taken into account when generating:
- Tables in which the database view is entered as the proxy view
- Columns that have a reference to a proxy view column
- Columns that are defined as extensions to the proxy view
The extensions are linked to each other internally with the Union operator. |
| Condition for view definition |
Restricting condition for creating the database view as a WHERE clause for database queries. |
| Columns |
Database view columns. |
Example
The following mappings are required to map the ADSDomain table in the Unified Namespace to the USRoot database view.
- The UNSRoot database view is entered as a proxy view in the ADSDomain table.
- The columns of the ADSDomain table to be mapped in the Unified Namespace are given a reference to the corresponding columns in the proxy view.
For example, the Ident_Domain column in the ADSDomain table is mapped to the Ident_root column of the UNSRoot proxy view.
- Columns that are expected in the UNSRoot database view but are not contained in the ADSDomain table must be entered in the ADSDomain table as extensions to the proxy view.
For example, the UNSRoot view expects input of the target system type in the UID_DPRNameSpace column. This column is not in the ADSDomain tables. Therefore, as an extension to the proxy view 'ADS-DPRNameSpace-ADS' as UID_DPRNameSpace is entered in the ADSDomain table.
The DBQueue Processor generates the extended view definition from the data. The following statement is a excerpt from the generated extension.
select ... Ident_Domain as Ident_UNSRoot..., 'ADS-DPRNameSpace-ADS' as UID_DPRNameSpace from ADSDomain
Related topics
Database views of the Union type
Database views with the Union table type are union views of various tables and are mainly used to group various object types with the same context. In the QERAccProductUsage union view, for example, you determine which service items are used in which IT Shop products.
Database views with the Union type are predefined database views. Templates and formatting rules cannot be defined for columns in these views. In the view definition, the object key column (XObjectKey) must be referenced. This makes it possible to create single object with its valid permissions.
Database views of the Union type are mainly used for editing the user interface and creating reports.
The following information is used to define a database view of the Union type.
Table 20: Properties for defining a database view of the Union type
| Table |
Name of the table in the data model. |
| Type |
Type of table Union. |
| Additional view definition |
Database query as a SELECT statement for setting up the database view.
NOTE: Never select NULL as <Column>. Instead, convert this explicitly to the requested value type.
Example:
convert(nvarchar(max), NULL) as <column>
convert(varchar(38), NULL) as UID_<column>
convert(varchar(138), NULL) as ObjectKey<column>
Several extensions for the view definition can be defined. The extensions are linked to each other internally with the Union operator.
When you add a column to a custom table, an entry is created in the DialogColumn table. When you delete a column, the entry is removed from the DialogColumn table. Changes to the schema of default database views are not permitted. |
| Condition for view definition |
Restricting condition for creating the database view as a WHERE clause for database queries. |
| Columns |
Database view columns. |
Example
The QERAccProductUsage table is defined as a database view of the Union type. In the union view, you establish which service item is used in which products. The following example shows an except from the definition based on system entitlements (table ESet) and report subscriptions (RPSReport table).
Table 21: Example of defining a database view of Union type
|
Table |
QERAccProductUsage |
|
Type |
Union |
|
Columns |
TableName, UID_AccProduct, XObjectKey |
|
Extension 1: Additional view definition |
ESet |
|
Extension 1: Query |
select 'ESet' as TableName, g.XObjectKey, g.UID_AccProduct
from ESet g |
|
Extension 2: Additional view definition |
RPSReport |
|
Extension 2: Query |
select 'RPSReport' as TableName, g.XObjectKey, g.UID_AccProduct
from RPSReport g |
|
Resulting view definition |
create view dbo.QERAccProductUsage as
select * from
(
select convert(varchar(11), null) as TableName, convert(varchar(38), null) as UID_AccProduct, convert(varchar(138), null) as XObjectKey where 1=0
union all
select xxTab.TableName, xxTab.UID_AccProduct, xxTab.XObjectKey
from (
select 'ESet' as TableName, g.XObjectKey, g.UID_AccProduct
from ESet g
) as xxTab
union all
select xxTab.TableName, xxTab.UID_AccProduct, xxTab.XObjectKey
from (
select 'RPSReport' as TableName, g.XObjectKey, g.UID_AccProduct
from RPSReport g
) as xxTab
) as x |
Related topics
Database views of the Read-only type
Database views with the Read only table type may be subsets or unions of the underlying tables. Database view with the Read only type are predefined database views. Templates and formatting rules cannot be defined for columns in these views.
Database views of the Read only type are for display only and are mainly used for editing the user interface and creating reports.
The following information is used to define a database view of the Read only type.
Table 22: Properties for defining a database view of the Read-only type
| Table |
Name of the table in the data model. |
| Type |
Read only type of table. |
| Additional view definition |
Database query as a SELECT statement for setting up the database view.
NOTE: Never select NULL as <Column>. Instead, convert this explicitly to the requested value type.
Example:
convert(nvarchar(max), NULL) as <column>
convert(varchar(38), NULL) as UID_<column>
convert(varchar(138), NULL) as ObjectKey<column>
Several extensions for the view definition can be defined. The extensions are linked to each other internally with the Union operator.
When you add a column to a custom table, an entry is created in the DialogColumn table. When you delete a column, the entry is removed from the DialogColumn table. Changes to the schema of default database views are not permitted. |
| Condition for view definition |
Restricting condition for creating the database view as a WHERE clause for database queries. The condition is attached to the view definition generated from the extension. |
| Columns |
Database view columns. |
Related topics
