Database-Query-Config.md

'Query' object (database-driven)

The Query object enables the definition of more complex multi-table queries (Joins) that would primarily result in a database View. The primary table Name for the query is required to be specified. Multiple queries can be specified for the same table(s). The IncludeColumns and ExcludeColumns provide a shorthand to include or exclude selected columns; with the AliasColumns providing a means to rename where required (for example duplicate name). Additional Where and Order configuration can also be added as required.

Example

A YAML configuration example is as follows:

queries:
- { name: Table, schema: Test, view: true, viewName: vwTestQuery, excludeColumns: [CreatedBy, UpdatedBy], permission: TestSec,
    joins: [
      { name: Person, schema: Demo, excludeColumns: [CreatedDate, UpdatedDate], aliasColumns: [RowVersion ^ RowVersionP],
        on: [
          { name: PersonId, toColumn: TableId }
        ]
      }
    ]
  }

Property categories

The Query object supports a number of properties that control the generated code output. These properties are separated into a series of logical categories.

Category	Description
Key	Provides the key configuration.
Columns	Provides the Columns configuration.
View	Provides the View configuration.
Auth	Provides the Authorization configuration.
Infer	Provides the special Column Name inference configuration.
Collections	Provides related child (hierarchical) configuration.

The properties with a bold name are those that are more typically used (considered more important).

Key

Provides the key configuration.

Property	Description
name	The name of the primary table of the query. [Mandatory]
schema	The schema name of the primary table of the view. † Defaults to CodeGeneration.Schema.
alias	The Schema.Table alias name. † Will automatically default where not specified.
viewReplace	Indicates whether the existing View database object should be replaced/altered or whether the object is dropped and recreated. † Defaults to CodeGeneration.Replace.

Columns

Provides the Columns configuration.

Property	Description
includeColumns	The list of Column names to be included in the underlying generated output. † Where not specified this indicates that all Columns are to be included.
excludeColumns	The list of Column names to be excluded from the underlying generated output. † Where not specified this indicates no Columns are to be excluded.
aliasColumns	The list of Column and Alias pairs (split by a ^ lookup character) to enable column aliasing/renaming. † Each alias value should be formatted as Column + ^ + Alias; e.g. PCODE^ProductCode

View

Provides the View configuration.

Property	Description
view	Indicates whether a View is to be generated.
viewName	The View name. † Defaults to vw + Name; e.g. vwTableName.
viewSchema	The schema name for the View. † Defaults to Schema.

Auth

Provides the Authorization configuration.

Property	Description
permission	The permission to be used for security permission checking. † The suffix is optional, and where not specified will default to .READ.

Infer

Provides the special Column Name inference configuration.

Property	Description
columnNameIsDeleted	The column name for the IsDeleted capability. † Defaults to CodeGeneration.IsDeleted.
columnNameTenantId	The column name for the TenantId capability. † Defaults to CodeGeneration.TenantId.
columnNameOrgUnitId	The column name for the OrgUnitId capability. † Defaults to CodeGeneration.OrgUnitId.
columnNameRowVersion	The column name for the RowVersion capability. † Defaults to CodeGeneration.RowVersion.
columnNameCreatedBy	The column name for the CreatedBy capability. † Defaults to CodeGeneration.CreatedBy.
columnNameCreatedDate	The column name for the CreatedDate capability. † Defaults to CodeGeneration.CreatedDate.
columnNameUpdatedBy	The column name for the UpdatedBy capability. † Defaults to CodeGeneration.UpdatedBy.
columnNameUpdatedDate	The column name for the UpdatedDate capability. † Defaults to CodeGeneration.UpdatedDate.
columnNameDeletedBy	The column name for the DeletedBy capability. † Defaults to CodeGeneration.UpdatedBy.
columnNameDeletedDate	The column name for the DeletedDate capability. † Defaults to CodeGeneration.UpdatedDate.

Collections

Provides related child (hierarchical) configuration.

Property	Description
joins	The corresponding QueryJoin collection. A Join object provides the configuration for a joining table.
order	The corresponding QueryOrder collection. An Order object defines the order (sequence).
where	The corresponding QueryWhere collection. A Where object defines the selection/filtering.