Introduction

To create a table in the software, you need to use a tool named valfil. This tool is directly called by the development environment integrated your application, in the following cases: table creation from the table editor, table modification from the same function, data extraction or integration, patch integration leading to a change to the structure of a table in the database.

At the most basic level, a table is defined by the physical files found in the FIL directory of the folder, and by a database table. The physical files are the following:

  • An XXX.srf file, that contains the description of the file structure (in ASCII format described below);
  • An XXX.fde file, which contains the description of the file structure (in a compiled format used by the application engine:

When the table is not stored in the database, but has been released as a transportable file, the data is stored in two or three additional files:

  • An XXX.dat file, which contains the data in the form of a file constructed of fixed length records;
  • An XXX.seq file, which contains the next sequence number associated with the table. This information is important insofar as each table is associated with a sequence number that makes it possible to create unique numbers (this corresponds to the uniqid([abv]) function, where abv is the table abbreviation);
  • An XXX.blb file, which contains long data (blobs and clobs), if existing.

The information contained in these different files is used in the creation of a table with the "minimum" options as shown below:

  • The sizing of the table (planned sized, if necessary – for Oracle – in the form of an initial segment and extents size) is defined in the file with the .srf extension (the calculation being carried out from the number of lines planned in the table, defined by the sizing variables and formulas);
  • The sizing of the index is defined pro rata to the size of the table (the initial segment size is multiplied as a function of the key length with respect to the line length in the table, then the next extent has the same size as theinitial imposed on it);
  • The file groups (under SQLServer) are those defined in the .adxodbc file if it exists. If the file does not exist, the FOLDER_DAT and FOLDER_IDX file groups are used, FOLDER being the name of the folder; If these groups do not exist, the default group defined for the database is used.
  • The storage tablespaces (for Oracle) are those defined in the .adxora file, if any. If the file does not exist, the tablespace associated with the user FOLDER is used for data, or the Folder_IDX tablespace for indexes.

It can be useful to define the additional information used during the table creation and at each update, modify the sizing or localization rules, taking into account the specificities of each database for better performances. However, this is assuming that these elements are stored outside the database, in a way that the valfil tool can apply them each time a table is validated. This is why it is possible to create a file with the extension .cfg in the FIL directory. This file is where you save all the optional rules associated with the Create table or Create Index SQL instruction run by valfil. As with the files with the extension .srf, it is in ASCII format (described below).

The contents of this configuration file are displayed at the bottom of the index definition tab in Table management. You will now be guided through the configuration file syntax.

Syntax

This file is composed of text lines, which are grouped into sections. Each section starts with a label prefixed by the $ character, followed by a code indicating the database to which the section is linked (either ORACLE, or MSSQL) followed by an underlined character and the name of the table or index.

This is followed by clauses, which open with the {" characters (opening curly bracket + double quote) and end with the "} characters (double quote + closing curly bracket). These clauses are sent as such to the database during the creation or modification of the table or index. If several exist, they are sent one after the other, separated by an end of line. The number of characters in a clause is limited to 256.

Following this is found the key-word End, which ends a section.

Comments can be inserted into the configuration file, in the form of free text prefixed by the # character. A comment can only be inserted at the start of a line.

Once a section with at least one clause exists, all the default sizing or storage rules for the element described (table or index) are ignored and replaced by the clauses sent from the section.

A section corresponding to a non-existent element or relative to a database that is not the current database is simply ignored.

Examples of Oracle syntaxes

For example, the storage clauses can be:

Imposed tablespace

{" Tablespace ts1 "}

Table extents sizing

{" Storage (Initial 100K Next 50K Maxextents 10 Pctincrease 20) "}

Partitioning of a table over several tablespaces according to a field value

{" partition by range (DHIDAT_0) (partition p1 values less than ('01-APR-1999') tablespace ts1, "}
{" partition p2 values less than ('01-APR-2001') tablespace ts2, "}
{" partition p3 values less than (maxvalue) tablespace ts4) "}

Examples of SQLServer syntax

The only storage clause possible is:

Imposed volume

{" On volume1 "}

From version 6.4 on, it is also possible to define the first index (only this one) as "clustered", which means that the table data are physically ordered following the order of this key. This can be used for optimization purposes, to do this the following section must be added (XXXX being the name of the specific index):

$CLUSTERED
{ "XXXX" }
End

It is primordial, so that this can be properly taken into account, to revalidate the table in forced mode after modification of the configuration file.

Warning: this syntax used to define the "clustered" index is temporary. Indeed, in the next major version, this type of index definition will be performed in a more natural way in the dictionary.

File example

You will find below an example of a configuration file. Please note that only part of the rules apply (only those that apply to the database actually used are applied).

Additionally, please note that no configuration file is provided by default, but that any update will preserve the existing configuration files. Configuration files are considered to be implementation elements and are therefore linked to a given installation, and therefore are not standard as such.

 

#---  Rule for Oracle: Invoice file
$ORACLE_SINVOICE
#--- A different tablespace is imposed
{" Tablespace DEMO_DAT2 "}
#--- Mandatory sizing rules applied once something is imposed
{" Storage (Initial 100M  Next 50M Pctincrease 20) "}
End

#--- Rule for Sql Server:
$MSSQL_SINVOICE
{" On DEMO_DAT2 "}
End

#--- First index under Oracle (No rules for the other indexes)
$ORACLE_SIH0
{" Tablespace DEMO_IDX2 "}
{" Storage (Initial 5M Next 2M Pctincrease 30) "}
End

Appendix: the ASCII files used with the Adonix engine

The Adonix engine uses ASCII files of the UNIX type, that is to say that the line separator is the Line Feed (code 10 character) and not by Carriage Return, Line Feed (characters 13, then 10) such as for the Windows™ text files. It is therefore fundamental that these texts are not edited using the notepad (or at least to not re-write with notepad), in order that the Adonix engine will be able to use them. On the other hand, under UNIX, the vi editor can be used. The Adonix editor correctly manages these files.

In addition, it should be noted that the format used by these files is in reality the UTF8 format (this format allows the processing of UNICODE characters - Chinese for example - in a totally transparent fashion). It is in reality a coding over 1 to 4 bytes for a single character. The UTF8 format corresponds to ASCII for all non-accented characters, but once bit weight is greater than 1, the character is coded over more than a byte. This means that French accented characters are not viewed correctly in "classic" editors (but the Adonix editors process this format correctly).

Default sizing rules for tables

In the absence of the configuration file, the sizing algorithm used to size the Oracle tables is the following:

  • First, the number of lines likely to be stored in the file is determined. The number of lines is calculated by means of the sizing formulas, which are calculated from the sizing elements. This number of lines is compared with the number of lines given in the Number of records field found in the first tab of the table dictionary. The maximum of these two numbers is kept.
  • Then, the size of a line in the table is calculated as a number of bytes. The internal type associated with each data type for the fields in the table is considered. A field of the type text takes 1 byte, a field of the type integer 2 bytes, a field of the type long integer 4 bytes, a field of the type decimal 8 bytes. The alphanumeric type fields and with a maximum length L take L+1 bytes, multiplied by a multiplication coefficient that is 1 if the database is in ASCII, and which will have the value of the environment variable STUSIZE if the database is UNICODE (in the absence of a value, 2 is used). It should be noted that this size can be obtained by the Option / Information function accessible in the tables management.
  • Similarly, the length (in bytes) is calculated for each index, by applying the same sizing rules to the index fields.
  • The global size of the table is calculated by multiplying the number of lines by the size of a line, and by applying a coefficient to take into account the fact that loading is never maximal (this coefficient will be 0.5 by default).
  • In the case of an Oracle database, the initial segment size is defined by taking the table size if it is less than 10 Mb. If not, the initial segment size is restricted to a value given by the environment variable ADXEXTSIZE (expressed in Kbytes). This value can in any case not exceed 1.5 Gbytes.