Database tables - configuration dialog box

Editing of all objects in the D2000 CNF is performed in the configuration dialog box, a specific part of which is common for all editable objects, and another part depends on the type of edited object.

The configuration dialog box of objects of Database table type consists of several parts (tabs), which contain similar parameters.

General properties
Groups
Table

General properties

Description

A text string describing the database table. Maximum: 128 characters.
Possibility to use the Dictionary (to open, press CTRL+L).

Table

Structure type

An object of the Structure definition type, which defines the structure type. If the database table is being used, then it is not possible to change the structure type.

The New button enables to creation of a new Structure definition according to the table definition in the database. The name of the Structure definition is derived from the name of the DB Table object in the D2000 System.
(The relevant system process D2000 DBManager must be running when creating the new definition.)

States when creating the new structure definition

• Definition is not in the system - the system requires the definition of the database table and creates the new Structure definition. After saving the definition, the system ensures the connection of the Structure definition to the DB Table.
• Definition is in the system - after confirming, the system ensures the connection of the Structure definition to the DB Table.

Conversion table

Mapping columns between the table in the database and the columns in the structure definition is carried out by name. When generating SQL D2000 commands, D2000 DBManager captures names into quotation marks by default. In some cases, this is unwanted activity, so the D2000 DBManager process have the /NQ parameter.

Access

Selection of access rights to the database.

• None - no access to the database
• Read only - the database cannot be modified (only read)
• Modify - the database can be read and modified

Table

Name of the table in the database. This name can be either simple (e.g., table1) or compound. A compound table name consists of dot dot-separated user name and a simple name of the table (e.g., user1.table1). Compound names are supported by e.g., MsSql, Oracle, and Sybase, simple names are required by e.g., Microsoft Access databases and MySql. The following rules for working with the table apply:

• If the name is simple, it is automatically extended with the user name (the parameter User) defined for the parent object of Database type. If the user name is not configured, the simple name of the table is used.
• If the name is compound, it is directly used.
• If the name is in the form of .table1 or "".table1, the simple name table1 is used, and it is not extended with the user name (the parameter User) defined for the parent object of Database type.
  Note: This rule is not valid for dbmanager_ora.exe, which always uses the compound name of the table.

Note: The name of the table (including the name of the user separated by a comma) can be up to 64 characters. The length of the table name depends on the particular database.

Index

Column (columns), which is (are) regarded as a key item. A key item is an item that uniquely defines a row in the database. The list of possible key items is equal to the names of columns according to the Structure definition. The parameter is optional.

Optional

The column (columns) that is (are) considered to be optional. The optional column doesn't have not exist in a database. The list of possible optional columns is equal to the columns in the Structure definition. To verify the existence of required columns in the database table (i.e., all that were not marked as optional), use the Test button for the object of Table type or the Test table for the object of Database type.

Note: Just as some columns may be present in the Structure definition and not exist in the database table, there may also be columns that exist in the database table and are not defined in the Structure definition. Even one database table can be represented by multiple Table objects, each of which uses a different Structure definition. Each such Table object can be used in a different case (e.g., sometimes I need to retrieve only the ID and name, sometimes the basic list of columns, and sometimes all of them). Using a Table object with a reduced list of columns speeds up data work (fewer items of the structured variable are retrieved and transferred).

Not Null

Column (columns) that is (are) considered to be NOT NULL.
The value of a NOT NULL column must be defined before inserting or modification in the database table (the operation to insert or modify the record tables). The list of possible NOT NULL columns is equal to the columns defined in the Structure definition. If there are undefined values in these columns before inserting or modifying tables, the action is terminated with an error. All the values that do not comply with the NOT NULL condition are listed; however, at most 10 for 1 column (it is in contrast with the DB engine - it returns only the first conflicting column in the first conflicting row).

Example of error message, which is displayed in DBManager:

%D2DBM-E-*** Error in con 1:
%D2DBM-E-con 1: DBS_INSERT : Column "column1" [row # 7], "column3" [row # 3 7 8], "column5" [row # 1 2 3 4 5 6 7 8 9 10 ...] in table "dba"."test_js_column_multi" cannot be NULL!

Data category

Allows assignment of data category from a business point of view to a table column using the Data category object type. By assigning a data category to a column, a reference to the assigned object comes into existence. It is then easily possible to find all usages of a given data category. By assigning a data category, the attribute Export monitored is automatically selected.

Data purpose

Assignment of the Data purpose object into a column activates the anonymization process on the given column. Anonymization is an automatic process that modifies values in the anonymized column, for which the validity time, as given in the assigned Data purpose object, has expired. New value will be set using the value given in the replacement attribute. Anonymization is run periodically, by default every hour, and is performed by the D2000 DBManager process. For each column, which has a Data purpose assigned, those rows will be anonymized whose value in the time column increased by processing time is smaller than the current time and is not between anonymized time intervals. By assigning a Data purpose, the attribute Export monitored is automatically selected.

Time column

An attribute that marks the absolute time type column of the configured table, from which the processing time of data in the given column is measured. The attribute is mandatory if the Data purpose is defined.

Replacement

An attribute that defines a value that will be set into a column during the process of anonymization. If the value is not defined, an empty (null) value is set, which can be interpreted as deletion of the value. For textual values, it is possible to use a combination of predefined text and a mask of date and time, which will be replaced by actual values of date and time at the time of anonymization. The mask is written between brace brackets and uses the same characters as the ESL function %TimeToStr. It is possible to use a time mask multiple times in text, e.g., "Anonymized on {dd.mm.yyyy} at {hh:mi:ss}.". For values of absolute time type, only mask format {hh:mi:ss dd-mm-yyyy} can be used, or actual date and time value using the same format, or empty (null) value.

Test

The button allows testing the database table connection functionality. Before running the test, it is necessary to click the Save button if you performed any changes in the parameters Structure type, Access, or Table.
Testing requires that the parent D2000 DBManager process must be running.

When testing a database table, process D2000 DBManager reloads the table definition from the SQL database using the ODBC function SQLColumns. The feature can be used for working in on-line system, when adding a column/columns into a table in the SQL database (the column/columns already exist in the Structure definition in D2000 system) and it is necessary, that process D2000 DBManager reloads the table definition in the SQL database for working with the added column/columns. Clicking the button Test reloads the table definition from the SQL database, and the added column/columns will also be taken into account. Tables that have already been opened (DB_CONNECT, PG_CONNECT) will use "original" columns (columns that exist at the time of opening the tables); tables opened later can use the added columns.
An alternative to this method is restarting the parent D2000 DBManager process. If you add a column to a Structure definition in the D2000 system, the parent D2000 DBManager process automatically reloads the table definition from the SQL database.
If the test is successful (and the table is found), the parent D2000 DBManager process notifies the D2000 CNF process about the successful result. If some columns of the Structure definition are not present in the table in the SQL database, the D2000 DBManager process displays a warning containing the list of columns that were not found.
If a column of a table is defined as a text column in the SQL database, but its type in the respective Structure definition is different, the warning is shown, as well. That behaviour has been implemented for Oracle database (e.g., a column is of Int type defined in D2000 system, and the same column is of VARCHAR type in Oracle database, then page access to the respective table in D2000 may not be able to show all pages correctly).
If some columns, which are not defined as optional in the configuration, are not in the table, a warning containing a list of columns is displayed.

Note: When using dbmanager.exe (ODBC version), the test will not be successful if the first row that is read from the table in the SQL database contains a text column that cannot be converted to a non-text type. The problem is in the ODBC driver (current version of Oracle ODBC 9.02.00.65). If the first row is correct (or the table contains no rows), the error message is shown correctly.
When using dbmanager_ora.exe (OCI version), this problem does not occur because the D2000 DBManager, unlike the Oracle ODBC driver, correctly handles error states generated by the OCI layer.

SQL definition

The button Copy to clipboard, depending on the access type to the database, enables copying the proper Oracle SQL definition to the clipboard. If a read-only access is configured, the VIEW definition, which contains the columns from the Structure definition, will be copied to the clipboard. Otherwise, the SQL command to create a table of the appropriate name, columns, and primary key will be copied to the clipboard.

To create a VIEW, manually insert its name and a SELECT, which matches the column order, into the SQL command.

CREATE OR REPLACE VIEW VW_"Name of table" (
...
names of columns 
...
) AS 

The command for creating a table doesn't have to be supplemented. Its syntax is as follows.

CREATE TABLE "Name of table" (
...
names of columns, remapped D2000 types, NULL/NOT NULL
...
)

ALTER TABLE  "Name of table"
  ADD CONSTRAINT  pk_"Name of table" PRIMARY KEY (
    names of columns, whose field "Key" is marked 
 )

The mapping of D2000 types to Oracle database types is stated below in the table.

History depth - column

Name of a column of the Absolute time type in the database. If the column name is entered, the parent D2000 DBManager process will automatically delete all the rows, values of which in the specified column are older than the given history depth.

History depth - Months, Days, Hours

Time definition of the history depth.

History depth - Data purpose

Alternative form of history depth definition using Data purpose object type.