The DatabaseTable Declaration

Database tables

You can make a database table known to AIMMS by means of a DatabaseTable declaration in your application. Inside this declaration you can specify the ODBC data source name of the database and the name of the database table from which you want to read, or to which you want to write. The list of attributes of a DatabaseTable is given in this table.

Attribute	Value-type	See also page
IndexDomain	index-domain	The IndexDomain attribute
DataSource	string-expression
TableName	string-expression
Owner	string-expression
Property	ReadOnly
Mapping	mapping-list
Text	string	The Text and Comment attributes
Comment	comment string	The Text and Comment attributes
Convention	convention	Globally Overriding Units Through Conventions

The DataSource attribute

The mandatory DataSource attribute specifies the ODBC data source name of the database you want to link with. Its value must be a string or a string parameter. If you are unsure about the data source name by which a particular database is known, AIMMS will help you. While completing the declaration form of a database table, AIMMS will automatically let you choose from the available data sources on your system using the DataSource wizard. AIMMS supports the following data source types:

• ODBC file data sources (.dsn extension, only available on Windows), and

• ODBC user and system data sources (no extension), and

• ODBC connection string.

In addition, you can specify the name of an AIMMS string parameter, holding the name of any of the above data source types. If the data source you are looking for is not available in this list, you can set up a link to that database from within the wizard.

The TableName attribute

With the TableName attribute you must specify the name of the table or view within the data source to which the DatabaseTable is mapped. Once you have provided the DataSource attribute, the TableName wizard will let you select any table or view available in the specified data source.

Example

The following declaration illustrates the simplest possible DatabaseTable declaration.

DatabaseTable RouteData {
    DataSource  :  "Topological Data";
    TableName   :  "Route Definition";
}

It will connect to an ODBC user or system data source called Topological Data, and in that data source search for a table named Route Definition.

The Owner attribute

The Owner attribute is for advanced use only. By default, when connecting to a database server, you will have access to all tables and stored procedures which are visible to you. In case a table name appears more than once, but is owned by different users, by default a connection is made to the table instance owned by yourself. By specifying the Owner attribute you can gain access to the table instance owned by the indicated user.

The Property attribute

With the Property attribute of a DatabaseTable you can specify whether the declared table is ReadOnly. Specifying a database table as ReadOnly will prevent you from inadvertently modifying its content. If you do not provide this property, the database table will default to read-write permissions unless the server does not allow write access.

The Mapping attribute

By default, AIMMS tries to map the column names used in a database table onto the AIMMS identifiers of the same name. Such an implicit mapping is, of course, not always possible. When you link to an existing database that was not specifically designed for your AIMMS application, it is very likely that the column names do not correspond to the names of your AIMMS identifiers. Therefore, the Mapping attribute lets you override this default. The database columns explicitly mapped through the Mapping attribute are added to the set of implicit mappings constructed by AIMMS. The column names from the database table used in a mapping list must be quoted. If the implicit mapping is not desirable you can provide the property No Implicit Mapping.

Example

The following declarations demonstrate the use of mappings in a DatabaseTable declaration. This example assumes the set and parameter declarations of A Basic Example plus the existence of the relation Routes given by

Set Routes {
    SubsetOf     : (Cities, Cities);
}

The following mapped database declaration will take care of the necessary column to identifier mapping.

DatabaseTable RouteData {
    DataSource   :  "Topological Data";
    TableName    :  "Route Definition";
    Mapping      : {
        "from"        --> i,                              ! name substitution
        "to"          --> j,
        "dist"        --> Distance(i,j),

        "fcost"       --> TransportCost(i,j,'fixed'),     ! slicing
        "vcost"       --> TransportCost(i,j,'variable'),

        ("from","to") --> Routes                          ! mapping to relation
    }
}

Name substitution

The first three lines of the Mapping attribute provide a simple name translation from a column in the database table to an AIMMS identifier. You can only use this type of mapping if the structural form of the database table (i.e. the primary key) coincides with the domain of the AIMMS identifier.

Mapping columns to slices

If the number of attributes in the primary key of a database table is lower than the dimension of the intended AIMMS identifier, you can also map a column name to a slice of an AIMMS identifier of the proper dimension, as shown in the fcost and vcost mapping. You can do this by replacing one or more of the indices in the identifier’s index space with a reference to a fixed element.

Mapping primary key to relation

As shown in the last line of the Mapping attribute, you can let the complete primary key in a database table correspond with a simple set, or with a relation (see Relations) in AIMMS. This correspondence is specified by mapping the tuple of primary attributes of the table onto the AIMMS set itself, or onto an index into this set. The primary attributes in the tuple are mapped in a one-to-one fashion onto the indices in the relation.

Syntax

The syntax of the Mapping attribute is given by the following diagram.

mapping-list:

The Convention attribute

With the Convention attribute you can indicate to AIMMS that the external data is stored with the units provided in the specified convention. If the unit specified in the convention differs from the unit that AIMMS uses to store its data internally, the data is scaled at the time of transfer. For the declaration of Conventions you are referred to Globally Overriding Units Through Conventions.

Date conversions

In addition, you can use Conventions to convert calendar data from the calendar slot format used within your model to the format expected by the database and vice versa. The use of Conventions for this purpose is discussed in full detail in Working in Multiple Time Zones. For non-calendar related date-time values you can use the predefined identifier OBDCDateTimeFormat to accomplish this (see Dealing with Date-Time Values).