Tables

• Add, modify, copy, and delete the records in the tables table;
• Query the records in the tables table;
• Validate the data records in a given table; and,
• Enable audit history tracking on a given table.

The tables table defines the tables for a database, and may also define views or synonyms for a database. Database views can be used as an abstraction for a physical table implementation that is not normalized, thereby making it appear normalized. This in turn ensures that any application using metadata can work properly.

Note: A maintenance view cannot be created on a database view; it can only be used for inquiries.

The following special actions are available:

• The Validate All Transactional Data action is a batch validation routine that validates one, many, or all of the records for a given table, and then reports the errors encountered in an Excel spreadsheet.
  • The spreadsheet contains one worksheet with summary information. The worksheet title is Summary and it contains the following columns:
    • database name
    • table name
    • rows processed
    • invalid rows

  • A distinct worksheet is also created for each table that is found to contain at least one error. The database name concatenated with the table name serves as the worksheet's title. Each worksheet contains the following columns:
    • one for each business key column in the table;
    • the relevant error message (note that there can be multiple errors for a given record)

  • The metadata validations include the following:
    • Is the value required?
    • Is the value within the specified range?
    • Is the value among allowed values (domain values)?
    • Do foreign keys exist?

  • Table-specific validation logic is also performed for tables that implement pre and post validate extensions.

  • The validation can be used at any time. However, it is recommended that it be used:
    • after initial data population or migration;
    • before and after an upgrade; and,
    • any time data corruption is suspected (e.g. following the unexpected failure of an application).

  • Records belonging to parent-maintained tables cannot be validated directly. They must be validated through their associated parent records.

  • The validation duration is greatly affected by the number of records in the tables selected and the number of foreign key relationships; therefore, it is recommended to validate one table at a time.

• The Enable Audit History action is used to enable the Auditing feature on one or more selected tables. When you select this action, and as long as you have the authorization to do so, the parallel tables are created in the audit schema and the metadata for the new tables are generated. A modal confirmation window is then displayed to inform you of the operation's success. Click OK to confirm. Note that an error message will appear in the following circumstances:
  • The table's database does not have a corresponding audit database;
  • The table is already enabled for auditing; or,
  • The table is not a physical table.

  You are now expected to use the Refresh Data Dictionary and Literals resource to refresh the metadata.

The following list defines every field available in the tables (md_table) table in alphabetical order:

• Alternate Identifier - an alternate to the table name. One usage of this field is to generate the method names for Java Table Objects and their supporting classes. In the future, may also be used for Web Services SOAP tags.

• Created By - displays the name of the user who created the record you are viewing or maintaining. May not be overridden.

• Created On - displays the date on which the record that you are creating or maintaining was created. May not be overridden. May also represent the creation date of the records you are attempting to retrieve.

• Database Name - the foreign key to the database entity.

• Default View Name - an expression column that identifies the default view associated with the table.
  • This field is populated when a new record is created. The generated view name is assigned based on the following standard: Database Name + Underscore + Table Name (e.g. meta_md_table). The value of this field may not be overridden.

  • This field can be added to any table and the view can be launched with a simple click using the Launch View icon.

• Description Literal Key - the literal key for the description. Validated to ensure it exists. Also validated to ensure it has not already been assigned to another table, as two or more tables cannot share the same literal key.

• Extra Attributes - a CLOB-type field used to store additional JSON information.

• Identifying Map - this field is not maintainable. A visual representation of the table's identifying child relationships, generated by the system based on defined metadata.

• Identifying Parent Cardinality - this field is not maintainable. The table's identifying parent cardinality.
  • The following options are available: 1 - Zero One or More, 2 - One or More, or 3 - Zero or One.

  • A table can only have one identifying parent. When it has one, there is a relation between them. The cardinality of this relation is what is referred to as the identifying parent cardinality. The relation cardinality is from the perspective of the parent.

  • The value is null when the table does not have an identifying parent.

• Identifying Tree - this field is not maintainable. The table's identifying child relationships, generated by the system based on defined metadata.
  • The Table Identifying Trees view help provides more details.

• Is Create Allowed - the following options are available: Yes and No (default).
  • A value of Yes indicates that you wish to allow create operations to be performed against this table.

  • A value of No indicates that you do not wish to allow create operations to be performed against this table.

  Note: When the table is parent-maintained, any value in this field is ignored.

• Is Delete Allowed - the following options are available: Yes and No (default).
  • A value of Yes indicates that you wish to allow delete operations to be performed against this table.

  • A value of No indicates that you do not wish to allow delete operations to be performed against this table.

  Note: When the table is parent-maintained, any value in this field is ignored.

• Is Identifying Root - an expression column that indicates whether or not the table represents the root table in an identifying tree. The following options are available: Yes and No.

• Is Included in Global View - an expression column that indicates whether or not the field is included in the global comparison tool. The following options are available:
  • Yes - indicates that the field is included in the global comparison tool.

  • No - (default) indicates that the field is not included in the global comparison tool.

• Is Maintained by Parent - the parent-maintained flag. The following options are available:
  • Yes - indicates that this is a parent-maintained table.

  • No - (default) indicates that this is not a parent-maintained table.

  A parent-maintained table is also called a parallel table. The parent-maintained table is a physical table with an identifying relationship (one to zero or one) to a parent table. Each physical table may have one or more parent-maintained tables defined for it.

  Parent-maintained tables allow users the ability to add columns to an existing physical table without having to alter the table itself. EliteSeries will treat parent-maintained child tables as if they were part of the original table. For example, if we wanted to add physical columns to the customers table, we would create a customer_2 table and define it as being maintained by its parent and EliteSeries would treat the two tables as one. In other words, if a given view includes columns from the customers table, it can also include the columns from customer_2, and if the customers table is maintainable, then all columns from the customer_2 table are also maintainable.

  The general rule, concerning parent-maintained tables, is that when a record exists in the parent table, a record must also exist in the child table. As a result, EliteSeries automatically allows CRUD operations to be performed on the columns from the primary table as well as the columns from the parent-maintained child tables. The CRUD operations will do the following:

  • Create: Insert the record for the parent table and for all parent-maintained child tables.

  • Read: If the view includes a column from the child table, the value is read.

  • Update: Update the record for the parent table and for all parent-maintained child tables. If the update to the parent table fails, an error message is issued. An update to the child table will be executed when the values are changed (i.e. the view includes a column from the child table and its value has changed). If the update to the child table fails, EliteSeries inserts the record into the child table.

  • Delete: Delete the record from the parent table and from all parent-maintained child tables. If the record does not exist in the parent table, an error message is issued. If the record does not exist in the child table, an error message is not logged.

  Notes:

  • A relationship from the parent table to the child table must be defined.

  • The child table can have a non-maintainable view (i.e. a view can be created for it, but all columns are defined as read-only).

  • Only one primary key can be defined. The primary key must match either the primary key or the business key of the parent table.

  • The parent-maintained table cannot be defined as an identifying parent in other relationships.

  • The parent-maintained table cannot have system-maintained table options. All system-maintained table options are ignored by EliteSeries.

  • All columns associated with the parent-maintained table must be physical columns.

  • The values of the Is Create Allowed and Is Delete Allowed fields have no impact on the code. EliteSeries does not check these values in this context.

  • A view can be created on the parent-maintained child table but all columns are rendered as read-only.

  • While in personalization mode, should you include at least one column from a parent-maintained table, for which cardinality is defined as 1 - Zero One or More or 3 - Zero or One, then a subview for it is automatically rendered in the Details page.

  • A table cannot be changed to be parent-maintained when there is an identifying relationship for which cardinality is defined as 1 - Zero One or More or 2 - One or More.

• Is Transaction Entry Point - the transaction entry point is the attribute that determines whether the metadata engine will treat the table as though it is maintainable within the context of a transactional unit. Only the highest level table (the root node of an identifying tree) should be designated as the transaction entry point.

  The following options are available:

  • Yes - indicates that the table in question is the primary table associated with a group of tables. Setting the transaction entry point to Yes is part of the setup mechanism necessary to treat this table, and its subordinates, as a transactional unit. When a maintenance view associated with this table is run, the data accessed (e.g. the sales order) is locked so that no other user can access the same information.

  • No - (default) indicates that this table is not the root node in a transactional group.

  There is a Transaction Update Policy feature:

  • As part of the Transaction Update Policy feature, a view attribute may be added for transaction entry point tables. This attribute indicates that Web service requests will be created at the line level, rather than at the transaction level. This means that the parent and all of the children that are part of the same request will be added to the database using separate database transactions. In certain cases, setting the Transaction Update Policy to the line level will decrease the locking time on certain tables.

  • The session application cleanups table is affected by this process. Upon processing a header record and its children, a record is added to the session application cleanups table. The attributes added within the record include the view name and the header's primary key. If the update is successful, the record is removed at the end of the process. However, if an error occurs, then neither the lock on the header nor the session application cleanup extension is removed. A single attempt will be made to delete the header and its children, either when the session application times out or once the user logs out of the session. If the deletion is unsuccessful, the following occurs:
    • an email message is sent to the administrator;
    • an errorCleanup attribute is added to the extension, with the first 250 characters of the stacktrace in the Value field; and,
    • the header remains locked.

    The administrator is then required to clean the data manually.

• Is Update Allowed - the following options are available:
  • Yes - indicates that you wish to allow update operations to be performed against this table.

  • No - (default) indicates that you do not wish to allow update operations to be performed against this table.

  Note: When the table is parent-maintained, any value in this field is ignored

• Modification Counter - this field is not maintainable. The date and time a given record was last modified.

• Modified By - this field is not maintainable. The user or system process that last modified the record.

• Modified On - this field is not maintainable. The date on which the record was last modified.

• Note - optional. User-defined information about the current entity.
  • The information is available to the metadata administrator.

• Table Name - the unique identifier.

• Topic Help - this field is not maintainable. The hyperlink to the table entity's help topic file, if one exists.

• Track Changes - the following options are available:
  • Yes - indicates that this table will keep track of changes made if the affected database and column are also tracking changes.

  • No - (default) indicates that no changes for this table will be tracked. This will override the Track Changes flag at the database and column level.

• Type - the following options are available:
  • Table - (default) indicates that this is a table that is physically present in the database.

  • View - indicates that this is a table data source that represents a database view, not an actual physical table.

  • Extension - indicates that this is a non-physical table, which means that it does not correspond to a table in the database, and thus all columns belonging to the table are non-physical.

  Note: While personalizing a given view, you are given the option to indicate whether or not subtotals only, subtotals and grand totals, or grand totals only are to be included in the search results. This is possible for physical tables and view tables only. You are not allowed to define a summary mode view for an extension-type table.

For the functionality of each action (i.e. button) available on this view, refer to the About the Actions topic.