Subject Area : 102 - Data Access Scripts |
Subject Area Name | Definition | |||||
---|---|---|---|---|---|---|
102 - Data Access Scripts | Any component that requires the generation of SQL DDL scripts that are based on dynamic item definitions can use this facility. When a container or item is published, these tables are checked for scripts related to the item type and subtype and are used to generate schema and table specific views and stored procedures. | |||||
ER Diagram Name | Physical Display Level | |||||
Logical | Column | |||||
Physical | Column | |||||
Physical Name | Entity/Table Name | Logical Only | Do Not Generate | Definition | ||
cntnr_defn | Container Definition | true | true | The definition of a Container and the associated Configuration_Settings at the container level. This would include the collection of Item Definitions. The Container_Definition will have a Container Class identifier (Workspace, CMDB, Address Book) and may have specializations such as Workspace_Container_Definition. As much as possible, the Container Definition will suffice to describe any Container Type. There will be an association to a default Container Template, as well as more application specific Container Templates that can be chosen to initially populate the Container Template. There will also be Container Group Definition, and Container Group Templates. See the database views v_cntnr_defn, v_cntnr_defn_active. | ||
Physical Name | Attribute/Column Name | Definition | Physical Data Type | |||
db_schema_name | Database Schema Name | The name of the database schema. It is a database object name, limited to 30 characters in length. | varchar(30) | |||
defn_id | Definition Id | A Foreign Key to the Definition table. | bigint | |||
defn_subtype | Definition Subtype | false | The subtype of the definition used as a discriminator for the class of definition. Each subtype will have different configuration settings and associated definitions. These include associated built-in field definitions for item subtypes. See the database view v_defn_subtype. | |||
Physical Name | Attribute/Column Name | Definition | Physical Data Type | |||
defn_subtype_name | Definition Subtype Name | The name of the Definition Subtype. | varchar(255) | |||
defn_subtype_descr | Definition Subtype Description | A descriptive name of the Definition Subtype. This descriptive name may be localized. | varchar(512) | |||
defn_subtype_id | Definition Subtype Id | The automatically generated primary key of the defn_subtype table. | int | |||
defn_parent_subtype_id | definition parent subtype id | A Foreign Key to the Definition Subtype table. | int | |||
defn_type_name | Definition Type Name | The unique string identifier for an instance of Definition Type. This identifier must be kept in sync with programming code that uses an enumeration constant to identify particular values. A scan program is generally used to populate this table. | varchar(255) | |||
max_in_parent | Maximum In Parent | A Foreign Key to the Definition Subtype table. | int | |||
dyn_table_script | Dynamic Table Script | false |
A DDL script with substitution parameters that is executed by Data Access during the publish process. It contains data structures that must be created along with the tables that are generated for the dynamic item. Parameters may include: a) schema name b) item name prefix (derived from the item name, possibly exactly equal to it) c) container id d) item definition Id Generally, the scripts can be run post level (container, Item) creation using the Type Order and then the Script Order. If the script is changed in the directory then it is reloaded and gets a new identifier. This will cause it to re-run. Scripts must be fundamentally re-runnable. |
|||
Physical Name | Attribute/Column Name | Definition | Physical Data Type | |||
target_dbms_id | Target DBMS Id | The unique identifier for an instance of Target DBMS. This identifier must be kept in sync with programming code that uses an enumeration or fixed constant to identify particular values. | int | |||
dyn_table_script_id | Dynamic Table Script Id | The automatically generated primary key of the dyn_table_script table. | bigint | |||
dyn_table_script_name | Dynamic Table Script Name | The name of the Dynamic Table Script. | varchar(255) | |||
script_type_id | Script Type Id | A Foreign Key to the Dynamic Table Script Type table. | bigint | |||
is_active | Is Active | Indicates if the script is the active version, or if it is a superceded version. When deployment loads the script, the SQL text of the script is compared with any existing active row. If it is equal, the script is not loaded. If unequal, the existing script is marked inactive, and a new active script is loaded. | bit | |||
script_sort_order | Script Sort Order | A sort number used to order a list of Dynamic Table Script. | int | |||
sql_script | SQL Script | The value of a Dynamic Table Script, stored as a long text object. | varchar(max) | |||
defn_subtype_id | Definition Subtype Id | A Foreign Key to the Definition Subtype table. | int | |||
run_as_proxy | Run As Proxy | Used for Oracle, indicating if a proxy connection to impersonate the schema account is needed. | bit | |||
dyn_table_script_history | Dynamic Table Script History | false | Records the successful execution of the Dynamic Table Script for a Container and/or Dynamic Item. This is used to control re-execution of scripts when the script version changes, or when a new script is added. | |||
Physical Name | Attribute/Column Name | Definition | Physical Data Type | |||
dyn_table_script_id | Dynamic Table Script Id | A Foreign Key to the Dynamic Table Script table. | bigint | |||
dyn_table_script_history_id | Dynamic Table Script History Id | The automatically generated primary key of the dyn_table_script_history table. | bigint | |||
item_defn_id | Item Definition Id | A Foreign Key to the Definition table for an instance of type Definition. | bigint | |||
create_date | Create Date | The date that the Dynamic Table Script History was created. | datetime | |||
cntnr_defn_id | Container Definition Id | A Foreign Key to the Definition table. | bigint | |||
dyn_table_script_type | Dynamic Table Script Type | false | The type of Dynamic Table script, which is important for locating and re-running the script if need be. Examples include: Activity Data Mart Creation, Status Change Data Mart Creation. Generally, the scripts can be run post level (container, Item) creation using the Type Order and then the Script Order. | |||
Physical Name | Attribute/Column Name | Definition | Physical Data Type | |||
dyn_table_script_type_id | Dynamic Table Script Type Id | The automatically generated primary key of the dyn_table_script_type table. | bigint | |||
dyn_table_script_type_name | Dynamic Table Script Type Name | The unique string identifier for an instance of Dynamic Table Script Type. This identifier must be kept in sync with programming code that uses an enumeration constant to identify particular values. A scan program is generally used to populate this table. | varchar(255) | |||
dyn_table_script_type_descr | Dynamic Table Script Type Description | A descriptive name of the Dynamic Table Script Type. This descriptive name may be localized. | varchar(512) | |||
type_sort_order | Type Sort Order | A sort number used to order a list of Dynamic Table Script Type. | int | |||
item_defn | Item Definition | true | true | The definition of an Item, which includes the Item Name and localizations and the collection of Item Fields. It also includes customizations and localizations of string resources for Item Names, and Item Field names. | ||
Physical Name | Attribute/Column Name | Definition | Physical Data Type | |||
item_table_name | Item Table Name | The name of the DBMS_Object_Name. It is a database object name, limited to 30 characters in length. | varchar(30) | |||
item_defn_id | Item Definition Id | A Foreign Key to the Definition table for an instance of type Definition. | bigint | |||
target_dbms | Target DBMS | false | A lookup table for the Target Database Management System types. Values include: SQLServer, PostgreSQL, MySQL, Oracle. | |||
Physical Name | Attribute/Column Name | Definition | Physical Data Type | |||
target_dbms_id | Target DBMS Id | The unique identifier for an instance of Target DBMS. This identifier must be kept in sync with programming code that uses an enumeration or fixed constant to identify particular values. | int | |||
target_dbms_name | Target DBMS Name | The name of the Target DBMS. | varchar(255) |