Most of the table configurations is done at Tables window, located at System > Utilities > Database Structure > Tables. The Tables window contains the following tabs:
The Tables tab lists each table created using the Tables window. It also shows any inventory and class-codes tables created in the Asset Type window. These tables may or may not actually be in the database; the In DB check box indicates those tables that are actually part of the database.
Keep Change History: This check box allows you to introduce temporality into the database. When this check box is selected for a table, the application maintains previous versions of the data in the table. You may then create an "as-of date" window to display the data at a certain time in the past. (See How to Make an "As-of Date" Window for more information.)
Note: If you introduce temporality into a table, all related tables must also have temporality. If the table has LOC_IDENT and/or COMMENTS_ID, the system will automatically create mirror tables for these. You must manually set up temporality for any SETUP_* table having a column in the main table as well as the SETUP_NETWORK_LINES table (and any other alternate location-referencing tables). Finally, if the main table has GEOM in it, then the map also has temporality. |
LRS Retire Type: This field specifies when an LRS Transaction retires a route, what will happen to the associated events / records on that route. This is explained in detail below.
When an LRS Transaction (either an internal LRS Transaction or through an LRS Interface) realigns or retires part of a route, by default, the events on the old parts of the routes will be considered deleted. In AgileAssets system, the LRS Gateway will delete records where the linear location is retired (when LOC_IDENT column is part of the unique index on the table – which is the typical configuration for tables that stores location data).
In many cases for Pavement Analyst module it is valid to delete the record with the linear location because this data is no longer relevant as the route has changed e.g. pavement condition data.
But for Maintenance Manager or Structure Manager modules, normally no assets should be deleted. Instead, the status of the asset is set to an inactive asset status to a corresponding value, e.g. Removal, Abandoned. This is because assets have many records linked to them e.g. work orders, inspections, etc.
If not configured correctly the default LRS retire behavior will for point assets delete the record in the XX_INVENTORY table that LRS gateway says is retired - this can lead to data loss if not configured correctly.
There are 3 options in LRS Retire Type field:
Note: The "Set asset status and clear LRS location" option is only available for "Inventory" and "Inventory Details" Table Types. This option has one additional configuration step and that is to set the specific asset status to use for a specific asset type when an LRS retire happens. This is configured on the relevant asset type in the System > Settings > Asset Inventory Settings > Asset Type window's LRS Retire Status field. The field will display asset statuses for the select asset. |
When you right-click the tab, the system displays a shortcut menu. This menu contains the following special commands that apply to the table selected in the Tables tab:
Apply Changes: This command modifies the table structure and/or indexes in the database. After you make changes in the Columns tab or Indexes tab, select this command to implement the changes in the database.
Note: Any additions or modifications to the Comments column are sent to the Oracle system table that stores comments when you execute this command. |
Delete: This command deletes all appropriate records form "metadata" tables (SETUP_TABLES, SETUP_TABLE_COLUMNS, etc.). After deletion, you will also need to delete the menu item for the table in the Menus window.
Note: This command is not available for inventory and class code tables; to delete these types of tables, delete the name of the table from the Table Name column of the Asset Type window. |
Note: Inventory tables are omitted from this list because they are created in the Asset Type window. See Create Location Tables for more detailed instructions on creating a data table with location information; see Create a Setup (List) Table for more detailed instructions on creating a table that will provide list data. |
Note: When a column is configured as a String and set to a size of zero, it will be interpreted as a CLOB (character large object). |
The Columns tab shows the columns in the table selected in the Tables tab. When the Apply Changes right-click command in the Tables tab is executed, it updates the table structure using the column definitions from this tab.
The columns in this tab provide the following information:
Audit: When the check box in this column is selected, the system keeps a change history log for the data in the associated column. You access this log by clicking the Audit Link column's value in the appropriate data entry window.
Note: Auditing only works on tables that have a single-column unique index. |
SELECT ( update_source ) AS column_name FROM table_name A WHERE where_clause
Note: The best way to learn to write Update Source SQL is to find several existing examples and place them into the SELECT statement configuration shown above. |
Use these tips to test your Update Source SQL:
When you right-click a row, the system displays a shortcut menu. This menu contains the following commands in addition to the common commands:
Add Existing Column: This is the first of two ways in which records can be inserted into this pane. This command accesses an existing column name and applies it to the current table. When you select this command, the system displays a new window. Enter the column's ID in this window (you need to know the exact ID). If it does not already exist, an error message will appear and this process will be canceled. If it does exist, then a new record will be inserted into this pane and the column will be added to the table selected in the Tables pane.
Note: A list of column IDs is available in the Columns window. |
Add New Column: This is the second of two ways in which records can be inserted into this pane. This command defines a new column name in the system and applies it to the current table. When you select this command, the system displays the first of a series of dialog boxes. Enter the column's ID (by convention this should be in upper case and end in _ID), column label (name), right to see, right to edit, View Type (see description above), Split Rules, and other attributes of the column. If the column's ID already exists, an error message will appear and this process will be canceled. If it does not already exist, then a record will be inserted into this pane. The column will be added to the table selected in the Tables pane and will also be added to the available columns shown in the Columns window.
Note: Any information you include in the Comments text field in the dialog box is sent to the Oracle system table that stores comments. |
The Indexes pane in the upper part of the tab lists the indexes for the table. The Available Columns and Index Columns panes in the lower part of the window are used to see and adjust the column components of the currently selected index. The Indexes tab shows the indexes pertinent to the table identified in the Tables tab. When the Apply Changes right-click command in the Tables tab is executed, it updates the index definitions from this tab.
The Indexes tab contains three panes:
The Index tab also contains two buttons with arrows to add and remove columns from the index. The upper arrow key adds the column selected in the Available Columns pane to the Indexes pane. The lower arrow key removes the column selected in the Indexes pane and places it in the Available Columns pane.
The columns in this tab provide the following information:
The Java Triggers tab lists the Java scripts that trigger changes in the table selected in the Tables tab. It also lists AfterChange type Groovy scripts that affect the table. Since Groovy scripts cannot run in an Oracle environment as such, each is given a Java "wrapper" that enables the Groovy script to run. The name of this wrapper is the same for all AfterChange Groovy scripts: com.agileassetsinc.trigger.GroovyScriptTrigger. The particular AfterChange Groovy script is given in the Dynamic Create Parameters column, which shows the ID number of the Groovy script.
When you right-click a record that shows com.agileassetsinc.trigger.GroovyScriptTrigger in the Class Name column, the following command becomes available:
Edit Groovy Script: This command displays the Edit Groovy Script dialog box. You use this dialog box to create the Groovy script. For information on the command buttons found in this dialog box, see Description of the Groovy Script Dialog Box. After you create and test the Groovy script, you click the OK button to close the dialog box. The system then inserts the ID number of the new Groovy script in the Dynamic Create Parameters column (regardless of what was entered in the Name field of the Edit Groovy Script dialog box). The system also adds the new Groovy script to the Groovy Scripts window under the AfterChange type of scripts.
Note: If you wish to use an existing AfterChange Groovy script rather than create a new one, do not use this command. Instead insert a new record in the Java Triggers tab, enter com.agileassetsinc.trigger.GroovyScriptTrigger in the Class Name column, and enter the ID number of the existing Groovy script in the Dynamic Create Parameters column. |