Database Manager

The Database Manager module manages the database defined by the DbConnectionIFM or DbConnectionAFM setting.

Style Sheet

Custom file database\style.css specifies page styles.

Overview

The Database Manager module accesses tables and fields defined in the schema tables sisfm_tbls and sisfm_flds.

Access to this module is controlled by the ModuleDatabase access function, and its functionality by the AddRow EditRow RemoveRow AddField EditField RemoveField access functions.

If the DbConnectionAFM setting is in effect, functions that would update tables sisfm_tbls or sisfm_flds (such as adding tables and fields) are not available: Archibus must be used for these functions.

When a table is selected, the sisfm_flds table is updated if its fields for that table do not match those in the database, when the following messages may be issued:

Table table field field found in schema table sisfm_flds but not in database - removed from schema.
Table table field field not in schema table sisfm_flds - added with data type data type.

If data type is Char it is added with a size of 50, which must be adjusted as below to match the size in the database.

If the DbConnectionAFM setting is in effect, these messages are issued without actions.

The Database Manager module has a toolbar initially containing a database table selector and an EXIT button, and left and right panels, with the latter's width defined by the DatabaseInfoPanelWidth setting. When a table is selected, buttons Rows, Fields, Remove Table, Import and Export are shown, and the Rows button is selected.

With the Rows button selected:

the left panel shows the rows of the selected table, with its Primary Key field(s) and the field with sisfm_flds.sisfm_type=2055 (Desc) as description, as modified by the user's web storage.
when a row is selected, the right panel shows all fields for that row, under headings Primary Key, Required and Optional (sisfm_flds.allow_null=1 and 0 respectively), and Add Row, Remove Row, Edit Row (or Save Row) buttons.

With the Fields button selected:

the Import and Export buttons are not shown.
the left panel shows the fields of the selected table, with their sisfm_flds.ml_heading as description.
when a field is selected, the right panel shows the attributes for that field, under headings as above, and Add Field, Remove Field, Edit Field (or Save Field) buttons.

With either Rows or Fields selected, the following changes can be made to the left panel table:

Select a column heading to sort by that field.
Select the left [...] button to select fields to include.
Select a column [...] button to filter by that field.

Edit, Add and Remove buttons are not provided for rows or fields of the schema tables sisfm_tbls and sisfm_flds, or for database views (sisfm_tbls.is_sql_view=1).

The table selector also includes an Add Table entry, for which a new table name and title are entered. Because some types of database do not allow a table with no fields, a new table is created with a field named dummy_field which is not shown and is replaced when the first field is added.

Remove Table removes the current table.

When adding or removing a table or field, or editing a field, the database is updated first, and if this is successful the schema tables sisfm_tbls and sisfm_flds are updated correspondingly.

There are a number of circumstances in which such database changes may fail, when the database error message is displayed. For example, a database object may be locked because it is in use by others.

Selecting Export displays a save dialog for a CSV or TXT file (see DatabaseExportFormat setting) containing the selected table rows and fields.

Selecting Import displays a new window to select a CSV or TXT file to import, with options to Check data only, Insert only, Update only, Insert and Update, and Delete. Rows are logged with a CSV line number, and after header rows show the primary key fields of each row processed, in black with "inserted" or "updated" if successful, in green with "would succeed" if skipped, and in red with the full row followed by one of the following error messages:

1: does not begin with #table
1: does not begin with field list, (Primary Key field(s))
1: for delete this may only include field list, (Primary Key field(s))
1: table does not include field field
1: cannot insert field field which has data type AUTOINCREMENT
1: cannot import field field which has data type 2170 (Doc_Stg)
1: cannot import without mandatory field field
n: failed - row to delete does not exist
n: failed - error deleting row: message
n: [ failed | would fail ] - field heading is required
n: [ failed | would fail ] - field heading is not a number
n: [ failed | would fail ] - field heading is not an integer
n: [ failed | would fail ] - field heading is not a date
n: [ failed | would fail ] - field heading is longer than sisfm_size characters
n: [ failed | would fail ] - field heading has more than decimals decimal places
n: [ failed | would fail ] - field heading may not be less than min_val
n: [ failed | would fail ] - field heading may not be greater than min_val
n: [ failed | would fail ] - field heading value is not in table ref_table

Creating a Database

The SISfm page database/getDDL.aspx is available to generate the Data Definition Language SQL for creating a new database from the current database, and displays the following if its querystring parameters are absent or invalid:

Data Definition Language to Create TablesThe following querystring parameters are required:
target - one of: msaccess,mssql,oracle
tables - a table name, or multiple table names using * as wildcard, or all tables using * only
exclude - if sisfm_tbls.comments or sisfm_flds.comments contains this value, the table or field is excluded

INSERT statements are also generated to populate the schema tables sisfm_tbls or sisfm_flds if these are included.

The resulting page contents can be selected, copied and pasted into the SISfm page database/SQL.aspx (or into a suitable DBA tool) to be executed on an empty database.

Note that:

Primary Key constraints are named PK_table_name and Foreign Key constraints are named FK_table_name__field_name. The Database Manager module requires these naming conventions for correct operation.
MS Access can issue "Record is too Large" for a long CREATE TABLE statement. If this is encountered, the solution is to remove some fields from the statement and use the Access GUI to add them manually afterwards.
MS Access has a problem with null values for child fields in Foreign Keys. This affects fields fl_id and rm_id in table eq for example. The only solution so far found is to drop the Foreign Keys for these fields.

Using Database SQL

The SISfm page database/SQL.aspx is available for executing any SQL statement(s) on any database, once its connection string has been specified.

Multiple SQL statements can be entered, when each must be terminated by a semi-colon (;) at the end of a line, and each result is preceded by its SQL statement.

CAUTION - do not execute SQL that changes the SISfm database model or its schema tables sisfm_tbls or sisfm_flds - this will corrupt it..

Web Storage

Web Storage (the successor to cookies) is used to persist the following between browser sessions:

for each table, with the Rows button selected, the fields to include in the left panel.