SQL form views and table definitions in the data dictionary

You can use the view or the base tables to read data from the database. The SQL views are especially useful when a third-party report writer is used because the names of its tables and columns are easier to use than their internal, numeric representations in the base tables.

Related topics

Data-management-in-the-AR-System-database

Relationship-between-forms-and-database-tables

For each table that is built in the system except for the attachment tables, an SQL view is automatically created. This view uses the form name as the view name and the field names in one of the views as the column names and not a display label. The names are created by using the following rules:

  • All alphabetic and numeric characters remain as defined.
  • All other characters are converted to an underscore (_ ).
  • If the first character is not alphanumeric, a leading A is added to the name.
  • If the name of a field is blank, a field name with a leading A followed by the fieldId is used.
  • If the name is one of the reserved words for the database, the string _x is appended.
  • If the name exceeds 30 characters, the string length is trimmed to 30 characters. Also if the 30 characters of the name contain any multi-byte characters, the name is further trimmed to accommodate the 30 bytes.

The name of the table must be unique after the conversion. If it is not, three digits are appended to it, beginning with 001 (if necessary, the name is truncated to fit the maximum length allowed for an SQL name). If 001 does not make the name unique, 002 is tried, then 003, and so on until a unique name is created. Column names must also be unique, so the same naming convention is used.

The name of the SQL view must also be unique after the conversion. If it is not, the schema ID is appended to it and if necessary, the name is truncated to fit the maximum length allowed for an SQL name. Column names must also be unique, so the same naming convention is used.

The SQL view of the status history table follows the same strategy as the SQL view of the base table. The name of the table is created by adding SH_ to the front of the name of the base table view. The column names are mapped to the name of the Request ID field, and the names of each of the Status values with _TIME and _USER appended. So a form with two states, New and Closed, ends up with columns in the view named Entry_Id, New_USER, New_TIME, Closed_USER, and Closed_TIME.

These SQL views are re-created when the name for the field is changed or when a change such as deleting a field, adding a field, or changing the length of a field, is made to the form that affects the underlying table 

The SQL commands that define the data dictionary for the AR System database are in the following files:

  • ARSystemServerInstallDir/Logs/arsystem_create_tab.txt
    This file is created when you install the AR System server for the first time or when you overwrite your current server installation. It contains all the SQL commands required to create the default data dictionary for your AR System database type. This file is not deleted or modified when you upgrade your server.

  • ARSystemServerInstallDir/Logs/arsystem_upgrade_sql.txt
    When you upgrade your AR System server, this file is created to record the differences between the SQL commands used to create the data dictionary in your previous version of the AR System database and in your upgraded version.

    Each subsequent upgrade overwrites this file. Therefore, to save its contents, rename it before performing another upgrade.

    The names of these files are the same for all database types.
    To view the contents of these files in a reader-friendly format, open them in WordPad. 

For more information about the SQL commands that define the AR System data dictionary, see Transact-SQL Desk Reference: For Microsoft SQL Server  and Oracle SQL Reference Manual.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*