The relational tables read and written by AMPL reside in files whose names and locations you specify as part of the table declaration. To work with these files, AMPL relies on database handlers, which are add-ons that can be loaded as needed. Handlers may be made available by the vendors of solvers or of database software. At least one handler must be installed to use the features described here. If you have access to AMPL under Microsoft Windows, you can download a standard AMPL database handler to experiment with these features.
The initial part of this presentation is tutorial in nature. We begin by describing, through a series of examples, how AMPL entities can be put into correspondence with the columns of relational tables. We then revisit these examples to show how the same correspondences can be described and implemented by use of AMPL's table declaration.
Subsequent sections describe the use of the table declaration in detail. Following a description of the overall form of the declaration, we separately present and illustrate the alternatives for reading and for writing external relational tables; then we describe some further complications that arise when reading and writing the same table. Finally, we present extensions for working with indexed collections of tables or table columns, giving examples for automatically writing a series of tables or columns and for reading "two-dimensional" tables of spreadsheet data.
We conclude by providing detailed instructions for the standard and built-in handlers used in our examples. The standard handler supports packages, including Microsoft Excel and Access, that can communicate via the Open Database Connectivity (ODBC) standard under Windows. The built-in handlers support simple ASCII and binary table formats intended mainly for debugging and demonstration purposes.
The facility described here is a part of the standard, command-line version of AMPL. Thus it is independent of the database facilities provided by the AMPL Plus graphical user interface for AMPL under Windows.
As a simple first example, consider the following declarations
from diet.mod in Chapter
1 of the AMPL book, defining the set FOOD
and three parameters indexed over it:
The table has 8 rows of data, one for each set member.
Thus each row contains all of the table's data corresponding to one
member -- one food, in this example.
In the context of database software, the table rows are often
viewed as data records, and the columns as fields within
each record. Thus a data entry form has one entry field for each
column. A form for the diet example (from Microsoft Access) might look
like this:
Parameters are not the only entities of interest indexed over
the set FOOD in this example. There are also the variables,
Other entities of diet.mod
are indexed over the set NUTR of nutrients: parameters
n_min and n_max, dual prices and other values
associated with constraint diet, and expressions involving
these. Since nutrients are entirely distinct from foods, however, the
values indexed over nutrients go into a separate relational table from
the one for foods discussed above. It might look like this:
AMPL entities indexed over the same higher-dimensional set
have a similar correspondence to a relational table, but with one key
column for each dimension. In the case of steelT.mod, for example, the following
parameters and variables are indexed over the same two-dimensional set
of product-time pairs:
General principles of data correspondence
We begin by explaining how several similarly-indexed AMPL
parameters, variables, or expressions can be put into correspondence
with one relational table. The AMPL declarations and commands to
define and use these correspondences will then be introduced in the
remaining sections.
A relational table giving values for these components can be
regarded as being laid out in the following form:
set FOOD;
param cost {FOOD} > 0;
param f_min {FOOD} >= 0;
param f_max {j in FOOD} >= f_min[j];
There are 4 columns in this table. The column headed
FOOD lists the members of the AMPL set also named FOOD.
This is the table's key column; entries in a key column must be
unique, like a set's members. Next the column headed cost
gives the values of the like-named parameter indexed over set
FOOD; here the value of cost["BEEF"] is specified as
3.19, cost["CHK"] as 2.59, and so forth. The remaining two
columns similarly give values for the other two parameters indexed over
FOOD.
FOOD cost f_min f_max
BEEF 3.19 0 100
CHK 2.59 0 100
FISH 2.29 0 100
HAM 2.89 0 100
MCH 1.89 0 100
MTL 1.99 0 100
SPG 1.99 0 100
TUR 2.49 0 100
Data records, one for each table row, can be entered or viewed one at a
time by use of the controls at the bottom of the form.
and assorted result expressions that may be displayed:
var Buy {j in FOOD} >= f_min[j], <= f_max[j];
All of these can be included in the relational table for values indexed
over FOOD:
ampl: model diet.mod;
ampl: data diet2a.dat;
ampl: solve;
MINOS 5.5: optimal solution found.
13 iterations, objective 118.0594032
ampl: display Buy, Buy.rc, {j in FOOD} Buy[j]/f_max[j];
: Buy Buy.rc Buy[j]/f_max[j] :=
BEEF 5.36061 8.88178e-16 0.536061
CHK 2 1.18884 0.2
FISH 2 1.14441 0.2
HAM 10 -0.302651 1
MCH 10 -0.551151 1
MTL 10 -1.3289 1
SPG 9.30605 0 0.930605
TUR 2 2.73162 0.2
;
Whereas the first 4 columns would typically be read into AMPL from a
database, the last 3 are results that would be written back from AMPL
to the database. We have invented the column headings BuyRC
and BuyFrac, because the AMPL expressions for the quantities
in those columns are typically not valid column heading in database
management systems. The AMPL table declaration provides for
input/output and naming distinctions such as these, as subsequent
sections will show.
FOOD cost f_min f_max Buy BuyRC BuyFrac
BEEF 3.19 0 100 5.36061 8.88178e-16 0.536061
CHK 2.59 0 100 2 1.18884 0.2
FISH 2.29 0 100 2 1.14441 0.2
HAM 2.89 0 100 10 -0.302651 1
MCH 1.89 0 100 10 -0.551151 1
MTL 1.99 0 100 10 -1.3289 1
SPG 1.99 0 100 9.30605 0 0.930605
TUR 2.49 0 100 2 2.73162 0.2
As this example suggests, any AMPL model having more than one indexing
set will require more than one relational table to properly hold its
data and results. Databases that consist of multiple tables are a
standard feature of relational data management, to be found in all but
the simplest "flat file" database packages.
NUTR n_min n_max NutrDual
A 700 20000 0
B1 700 20000 0
B2 700 20000 0.404585
C 700 20000 0
CAL 16000 24000 0
NA 0 50000 -0.00306905
A corresponding relational table thus has two key columns -- one
containing members of PROD and the other members of
1..T -- and then a column of values for each parameter and
variable. Here's an example, corresponding to the data in steelT.dat:
set PROD; # products
param T > 0; # number of weeks
param market {PROD,1..T} >= 0;
param revenue {PROD,1..T} >= 0;
var Make {PROD,1..T} >= 0;
var Sell {p in PROD, t in 1..T} >= 0, <= market[p,t];
Each ordered pair of items in the two key columns is unique in this table, just as these pairs are unique in the set {PROD,1..T}. Thus the market column of the table implies, for example, that market["bands",1] is 6000 and that market["coils",3] is 3500. Reading across the first row, we see also that revenue["bands",1] is 25, Make["bands",1] is 5990, and Sell["bands",1] is 6000. Again various names from the AMPL model are used as column headings, except for TIME, which must be invented to stand for the expression 1..T. As in the previous example, the column headings can be any identifiers acceptable to the database software, and the table declaration will take care of the correspondences (in a manner to be explained below).PROD TIME market revenue Make Sell bands 1 6000 25 5990 6000 bands 2 6000 26 6000 6000 bands 3 4000 27 1400 1400 bands 4 6500 27 2000 2000 coils 1 4000 30 1407 307 coils 2 2500 35 1400 2500 coils 3 3500 37 3500 3500 coils 4 4200 39 4200 4200
AMPL entities that have sufficiently similar indexing generally fit into the same relational table. We could extend our steelT.mod table, for instance, by adding a column for values of
The table would then have the following layout:var Inv {PROD,0..T} >= 0;
We use a "." here to mark table entries that correspond to values not defined by the model and data. There is no market["bands",0] in the data for this model, for example, although there does exist a value for Inv["bands",0] in the results. Database software packages can vary somewhat in the handling of "missing" entries of this sort.PROD TIME market revenue Make Sell Inv bands 0 . . . . 10 bands 1 6000 25 5990 6000 0 bands 2 6000 26 6000 6000 0 bands 3 4000 27 1400 1400 0 bands 4 6500 27 2000 2000 0 coils 0 . . . . 0 coils 1 4000 30 1407 307 1100 coils 2 2500 35 1400 2500 0 coils 3 3500 37 3500 3500 0 coils 4 4200 39 4200 4200 0
Parameters and variables may also be indexed over a set of pairs that is read as data rather than being constructed from one-dimensional sets. For instance, in transp3.mod we have:
A corresponding relational table has two key columns corresponding to the two components of the indexing set LINKS, plus a column each for the parameter and variable that are indexed over LINKS:set LINKS within {ORIG,DEST}; param cost {LINKS} >= 0; # shipment costs per unit var Trans {LINKS} >= 0; # actual units to be shipped
The structure here is the same as in our previous example. There is a row in the table only for each origin-destination pair that is actually in the set LINKS, however, rather than for every possible origin-destination pair.ORIG DEST cost Trans GARY DET 14 0 GARY LAF 8 600 GARY LAN 11 0 GARY STL 16 800 CLEV DET 9 1200 CLEV FRA 27 0 CLEV LAF 17 400 CLEV LAN 12 600 CLEV STL 26 0 CLEV WIN 9 400 PITT FRA 24 900 PITT FRE 99 1100 PITT STL 28 900 PITT WIN 13 0
To transfer information between an AMPL model and a relational
table, you begin with a table declaration that establishes the
correspondence between them. Certain details of this declaration
depend on the software being used to create and maintain the table. In
the case of the 4-column table of diet data defined above, some of the
possibilities are as follows:
For a Microsoft Excel range from a workbook file
diet.xls,
For an ASCII text table in file
Foods.tab,
Examples of AMPL table-handling statements
We begin our presentation of AMPL's statements for relational
tables by presenting some examples that could be used with the tables
defined above. All of the features shown in these examples are
explained in much more detail in the sections following.
For a Microsoft Access table in a database file
diet.mdb,
Each table declaration has two parts. Before the colon, the
declaration provides general information. First comes the table name
-- Foods in our examples above -- which will be the name by
which the table is known within AMPL. The keyword IN states
that the default for all non-key table columns will be read-only; AMPL
will read values in from these columns and will not write out to
them. Details for locating the table in an external database file are
provided by the character strings such as "ODBC" and
"diet.mdb", with the AMPL table name (Foods)
providing a default where needed:
table Foods IN "ODBC" "diet.mdb": FOOD <- [FOOD], cost, f_min, f_max;
table Foods IN "ODBC" "diet.xls": FOOD <- [FOOD], cost, f_min, f_max;
table Foods IN: FOOD <- [FOOD], cost, f_min, f_max;
In general, the format of the character strings in the table
declaration depends upon the database handler being used. The strings
required by the handlers used in our examples are explained in the
section on standard and built-in table handlers
at the end of the writeup.
After the colon, the table declaration gives the details of the correspondence between AMPL entities and relational table columns. The four comma-separated entries correspond to four columns in the table, starting with the key column distinguished by surrounding [...]. In this example, the names of the table's columns -- FOOD, cost, f_min, f_max -- are the same as the names of the corresponding AMPL components. The expression FOOD <- [FOOD] indicates that the entries in the key column FOOD are to be copied into AMPL to define the members of the set FOOD.
The table declaration only defines a correspondence. To actually read values from columns of a relational table into AMPL sets and parameters, it is necessary to give an explicit read table command. Thus, if the data values were in a Microsoft Access relational table like this,
(The display command is shown here just to confirm that the database values were read as intended.) If the data values were instead in a Microsoft Excel worksheet range like this,ampl: model diet.mod; ampl: table Foods IN "ODBC" "diet.mdb": FOOD <- [FOOD], cost, f_min, f_max; ampl: read table Foods; ampl: display cost, f_min, f_max; : cost f_min f_max := BEEF 3.19 2 10 CHK 2.59 2 10 FISH 2.29 2 10 HAM 2.89 2 10 MCH 1.89 2 10 MTL 1.99 2 10 SPG 1.99 2 10 TUR 2.49 2 10 ;
And if the values were in a file Foods.tab containing a text table like this,ampl: model diet.mod; ampl: table Foods IN "ODBC" "diet.xls": FOOD <- [FOOD], cost, f_min, f_max; ampl: read table Foods;
then the previously shown declaration for a text table would be used:ampl.tab 1 3 FOOD cost f_min f_max BEEF 3.19 2 10 CHK 2.59 2 10 FISH 2.29 2 10 HAM 2.89 2 10 MCH 1.89 2 10 MTL 1.99 2 10 SPG 1.99 2 10 TUR 2.49 2 10
Because the AMPL table name is the same -- Foods -- in all three of these examples, the read table command is the same for all three: read table Foods. In general, the read table command only specifies the AMPL name of the table to be read. All information about what is to be read, and how it is to be handled, is taken from the named table's definition in the preceding table declaration.ampl: model diet.mod; ampl: table Foods IN: FOOD <- [FOOD], cost, f_min, f_max; ampl: read table Foods;
To create the second (7-column) relational table example of the previous section, we could use a pair of table declarations,
or a single table declaration combining the input and output information:table ImportFoods IN "ODBC" "diet.mdb" "Foods": FOOD <- [FOOD], cost, f_min, f_max; table ExportFoods OUT "ODBC" "diet.mdb" "Foods": FOOD <- [FOOD], Buy, Buy.rc ~ BuyRC, {j in FOOD} Buy[j]/f_max[j] ~ BuyFrac;
These examples show how the AMPL table name (such as ExportFoods) may be different from the name of the corresponding table within the external file (as indicated by the subsequent string "Foods"). A number of other useful options are also seen here:table Foods "ODBC" "diet.mdb": [FOOD] IN, cost IN, f_min IN, f_max IN, Buy OUT, Buy.rc ~ BuyRC OUT, {j in FOOD} Buy[j]/f_max[j] ~ BuyFrac OUT;
To write meaningful results back to the Access database, we would need to read all of the diet model's data, then solve, and then give a write data command. Here's how it all might look using the separate table declarations to read and write the Access table Foods,
and here is an alternative using a single declaration to both read and write Foods:ampl: model diet.mod; ampl: table ImportFoods IN "ODBC" "diet.mdb" "Foods": ampl? FOOD <- [FOOD], cost, f_min, f_max; ampl: table Nutrs IN "ODBC" "diet.mdb": NUTR <- [NUTR], n_min, n_max; ampl: table Amts IN "ODBC" "diet.mdb": [NUTR, FOOD], amt; ampl: read table ImportFoods; ampl: read table Nutrs; ampl: read table Amts; ampl: solve; ampl: table ExportFoods OUT "ODBC" "diet.mdb" "Foods": FOOD <- [FOOD], ampl? Buy, Buy.rc ~ BuyRC, {j in FOOD} Buy[j]/f_max[j] ~ BuyFrac; ampl: write table ExportFoods;
Either way, the Access table Foods would end up having three additional columns:ampl: model diet.mod; ampl: table Foods "ODBC" "diet.mdb": ampl? [FOOD] IN, cost IN, f_min IN, f_max IN, ampl? Buy OUT, Buy.rc ~ BuyRC OUT, ampl? {j in FOOD} Buy[j]/f_max[j] ~ BuyFrac OUT; ampl: table Nutrs IN "ODBC" "diet.mdb": NUTR <- [NUTR], n_min, n_max; ampl: table Amts IN "ODBC" "diet.mdb": [NUTR, FOOD], amt; ampl: read table Foods; ampl: read table Nutrs; ampl: read table Amts; ampl: solve; ampl: write table Foods;
The table declaration is the same for multidimensional AMPL entities, except that there must be more than one key column specified between the brackets [ and ]. For the steel production example discussed previously, the correspondence to a relational table could be set up like this:
Here the key columns PROD and TIME are not specified as IN. This is because the parameters to be read in -- market and revenue -- are indexed in the AMPL model over the set {PROD, 1..T}, whose membership would be specified by use of other, simpler tables. The read table steelprod command merely uses the PROD and TIME entries of each database row to determine the pair of indices (subscripts) that are to be associated with the market and revenue entries in the row.table steelprod "ODBC" "steel.mdb": [PROD, TIME], market IN, revenue IN, Make OUT, Sell OUT, Inv OUT;
Our transportation example also involves a relational table for two-dimensional entities, and the associated table declaration is similar:
The difference here is that LINKS, the AMPL set of pairs over which cost and Trans are indexed, is part of the data rather than being determined from simpler sets or parameters. Thus we write LINKS <- [ORIG, DEST], to request that pairs from the key columns be read into LINKS at the same time that the corresponding values are read into cost. This distinction is discussed further in the section on reading data from relational tables below.table transLinks "ODBC" "trans.xls" "Links": LINKS <- [ORIG, DEST], cost IN, Trans OUT;
A model's table declarations and read table and write table commands are normally used in an AMPL script, rather than being typed interactively. There is typically one table declaration for each set that indexes parameters to be read or variables and expressions to be written (or both). Complete sample scripts and Access or Excel files for our diet, production, and transportation examples can be accessed as shown in Figure 1 below.
Model | Scripts | Data files | Notes |
---|---|---|---|
diet.mod
diet.mdb.run | diet.xls.run diet.mdb | diet.xls | |
diet.mdb2.run diet.xls2.run | diet.mdb diet.xls | Same, but using separate table declarations for
reading and writing. | |
steelT.mod
steel.mdb.run | steel.xls.run steel.mdb | steel.xls Reads an unindexed parameter from a database. Writes variables having
slightly different indexing sets.
| |
transp3.mod
trans.mdb.run | trans.xls.run trans.mdb | trans.xls Reads an AMPL set of ordered pairs from a database.
| |
If you are just getting started, you may want to skim this
section and then look through the following two sections --
Reading data from relational tables and
Writing data to relational tables -- for examples
that can be adapted to your situation. It is usually easiest to start
by doing the reading and writing separately, with separate
table declarations, but if you will frequently be doing both
then you may also want to look at the section entitled Reading and writing the same table.
In the most general terms, the syntax of the table
declaration is
The table-name is the name by which a relational table
is known within an AMPL model. This name is used to identify the table
in subsequent read table and write
table commands.
The optional {indexing-expr} specifies an
indexed collection of tables, in the same way that other indexed
collections of entities are specified in AMPL. Since unindexed tables
are expected to be much the more common case, we initially assume that
no indexing is specified. The modifications necessary for indexing are
described later in the section entitled Indexed collections
of tables and columns.
An inout keyword specifies a default read/write setting
for the table's data (non-key) columns. The recognized keywords and their
interpretations are:
The string-list is a sequence of AMPL character strings
that specify where the external relational table is located and what
external software is used to access it. The interpretation of this
information is specific to the software used, but typically the first
string identifies a handler for the AMPL/table interface, the
second identifies a file to be read or written, and the third
identifies a table within the file; see the final section (Standard and built-in table handlers) below for some
examples. Omitted strings are typically given default values derived
from the table-name. If the string-list is omitted
entirely, a file called table-name.tab containing a
single text table is assumed.
The key-spec associates key columns of an external
relational table with AMPL sets. Its general syntactic form is one of
Each data-spec associates a data column of an external
relational table with an AMPL entity, usually a parameter but possibly
a variable, a suffixed variable or
constraint, or an expression. Its general syntactic form is one of
Detailed rules for forming and interpreting key-specs
and data-specs are given in the following sections. To avoid
inessential complications in the initial presentation, the next two
sections focus on common cases in which a table declaration is
used only in connection with reading from a
database or writing to a database. Further
sections then describe some less common situations, such as the use of
a table declaration for reading and
writing the same table and for defining indexed
collections of tables and columns.
We begin by describing the two fundamental variants of the
table declaration, one for reading only parameter values from
data columns, and one for reading a set's members from the key columns
as well as parameter values from any data columns. We then explain how
a correspondence between database columns and AMPL parameters may be
established in several common situations where the naming or
organization of columns does not precisely match that of the
parameters. Finally, we identify additional kinds of values --
relating to variables and constraints -- that can be read from
relational tables in much the same ways as parameter values.
Reading parameters only. To assign values from
data columns to like-named AMPL parameters, it suffices to give a
bracketed list of key columns and then a list of data columns.
The simplest case, where there is only one key column, is
exemplified by
When read table Foods is executed,
the relational table is read one row at a time. A row's entry in the
key column is interpreted as a subscript to each of the parameters, and
these subscripted parameters are assigned the row's entries from the
associated data columns. For example, if the relational table is
At the time that the read table command is
executed, AMPL makes no assumption as to how the parameters are
declared; they need not be indexed over a set actually named
FOOD, and indeed the members of their indexing sets may not
yet even be known. Only later, when AMPL first uses each parameter in
some computation, does it actually check the entries read from key
column FOOD to be sure that each is a valid subscript for that
parameter.
The situation is analogous for multidimensional parameters.
The name of each data column must also be the name of an AMPL
parameter, and the dimension of the parameter's indexing set must equal
the number of key columns. Thus, for example, when two key columns are
listed within the brackets,
When read table SteelProd is
executed, each row's entries in the key columns are interpreted as a
pair of subscripts to each of the parameters. Thus if the relational
table has contents
Since a relational table has only one collection of key
columns, it applies the same subscripting to each of the parameters
named by the data columns. These parameters are thus usually indexed
over the same set. Parameters indexed over similar sets may also be
accommodated in one table, however, by leaving blank any entries in
rows corresponding to invalid subscripts. The way in which a blank
entry is indicated is specific to the database software being used.
Values of unindexed (scalar) parameters may be supplied
by a relational table that has one row. There must be one data column
for each parameter to be read, and the single row's value in such a
column must be of course the value to be assigned to the associated
parameter. Any key columns are ignored, and the list of key columns is
left empty in the corresponding table declaration. To read a
value for the parameter T that gives the number of periods in
steelT.mod, for example, the
table declaration is
Reading a set and parameters. It is often
convenient to read the members of a set from a table's key column or
columns, at the same time that parameters indexed over that set are
read from the data columns. To indicate that a set should be read from
a table, the key-spec in the table declaration is written
in the form
The simplest case involves reading a one-dimensional set and
the parameters indexed over it, as in this example for diet.mod:
An analogous syntax is employed for reading a multidimensional
set along with parameters indexed over it. In the case of transp3.mod, for instance, the
table declaration could be:
As in our previous multidimensional example, the names in
brackets need not correspond to sets in the AMPL model. The bracketed
names serve only to identify the key columns. The name to the left of
the arrow is the only one that must name a previously declared AMPL
set; this set must have been declaried to have the same dimension or
arity, moreover, as the number of key columns.
It makes sense to read the set LINKS from a relational
table, because LINKS is specifically declared in the model in
a way that leaves the corresponding data to be read separately:
When a table declaration specifies an AMPL set to be
assigned members, its list of data-specs may be empty. In that
case only the key columns are read, and the only action of read table is to assign the key column values as members of the
specified AMPL set.
Establishing correspondences. An AMPL model's
set and parameter declarations do not necessarily correspond in all
respects to the organization of tables in relevant databases. Where
the difference is substantial, it may be necessary to use the
database's query language to derive temporary tables that have the
structure required by the model -- see for example the use of SQL
queries in the section on Standard and built-in table
handlers. A number of common, simple differences can be handled
directly, however, through features of the table declaration.
Differences in naming are perhaps the most common. A
table declaration can associate a data column with a
differently named AMPL parameter by use of a data-spec of the
form param-name ~ data-col-name. Thus, for
example, if table Foods were instead defined by
A similarly generalized form, index ~
key-col-name, can be used to associate a kind of dummy index
with a key column. This index may then be used in a subscript to the
optional param-name in one or more data-specs. Such an
arrangement is useful in a number of situations where the key
column entries do not exactly correspond to the subscripts of the
parameters that are to receive table values. Here are three common
cases:
It is tempting to try to shorten declarations of these kinds by dropping
the ~ data-col-name, to produce, say,
More generally, a ~ synonym will have to be used in
any situation where the AMPL expression for the recipient of a column's
data is not itself a valid name for a database column. The rules for
valid column names tend to be the same as the rules for valid component
names in AMPL models, but they can vary in details depending on the
database software that is being used to create and maintain the
tables.
Reading other values. In a table
declaration used for input, an assignable AMPL expression may
appear anywhere that a parameter name would be allowed. An expression
is assignable if it can be assigned a value, such as by placing it on
the left side of a := in a let command.
Variable names are assignable expressions. Thus a
table declaration can specify columns of data to be read into
variables, for purposes of evaluating a previously stored solution or
providing a good initial solution for a solver.
Constraint names are also assignable expressions. Values "read
into a constraint" are interpreted as initial dual values for some
solvers, such as MINOS.
Any variable or constraint name qualified by an assignable suffix is also an assignable expression.
Assignable suffixes include the predefined suffix .sstatus as
well as any user-defined suffixes. For example, if the diet problem
were changed to have integer variables, the following table
declaration could help to provide useful information for the solver:
A table declaration for writing specifies an external
file and possibly a relational table within that file, either
explicitly in the string-list or implicitly by default rules.
Normally the named file or table is created if it does not exist, or is
overwritten otherwise. To specify that certain columns are to be
replaced or are to be added to a table, the table declaration
must incorporate one or more data-specs that have read/write
status IN or INOUT, as discussed in Reading and writing the same table. In any
case, detailed rules for when files and tables are modified or
overwritten depend on the database handler being used; see Standard and built-in table handlers for some
examples.
The key-specs and data-specs of table
declarations for writing external tables superficially resemble those for
reading. The range of AMPL expressions allowed when writing is much
broader, however, including essentially all set-valued and
numerical-valued expressions. Moreover, whereas the table rows to be
read are those of some existing table, the rows to be written must be
determined from AMPL expressions in some part of a table
declaration. Specifically, rows to be written can be inferred either
from the data-specs, using the same conventions as in AMPL
display commands, or from the key-spec. Each of
these alternatives employs a characteristic table syntax as
described below.
Writing rows inferred from the data-specs:
If the key-spec is simply a bracketed list of the names of key
columns,
In the simplest case, the data-specs are the names
of model components indexed over the same set:
If this form is applied to writing suffixed variable or
constraint names, such as the dual and slack values related to the
constraint diet,
More general expressions for the values in data columns require
the use of dummy indices, in the same way that they are used in the
data-list of a display command. Since indexed AMPL
expressions are rarely valid column names for a database, they should
generally be followed by ~ data-col-name to provide a
valid name for the corresponding relational table column that is to be
written. To write a column servings containing the number of
servings of each food to be bought and a column percent giving
the amount bought as a percentage of the maximum allowed, for example,
the table declaration could be given as either
Writing rows inferred from the key-spec:
An alternative form of table declaration specifies that one table
row is to be written for each member of an explicitly specified AMPL
set. For the declaration to work in this way, the key-spec must
be written as
The simplest case involves writing a column for each of several
model components indexed over the same one-dimensional set, as in this
example for diet.mod:
The use of ~ with AMPL names and suffixed names is
governed by the considerations previously described, so that the
example of diet slack and dual values would be written
More general expressions for the values in data columns require
the use of dummy indices. Since the rows to be written are determined
from the key-spec, however, the dummies are also defined there
(rather than in the data-specs as in the alternative form
above). To specify a column containing the amount of a food bought as
a percentage the maximum allowed, for example, it is necessary to write
100*Buy[j]/f_max[j], which in turn requires that dummy index
j be defined. The definition may appear either in a
set-spec of the form { index-list in
set-expr },
Tables corresponding to higher-dimensional sets are handled
analogously, with the number of key-col-specs listed in brackets
being equal to the dimension of the set-spec. Thus a table
containing the results from steelT.mod could be defined as
The options for dummy indices in higher dimensions are the same
as in one dimension. Thus our example SteelSales could be
written either using dummy indices defined in the set-spec,
Reading and writing using two table
declarations. A single external table can be read by use of one
table declaration and later written by use of another. The two
table declarations follow the rules for reading and writing,
respectively, as previously stated.
In this situation, however, one usually wants write
table to add or rewrite selected columns, rather than
overwriting the entire table. This preference can be communicated to
the AMPL table handler by including input as well as output columns in
the table declaration that is to be used for writing. Columns
intended for input to AMPL can be distinguished from those intended for
output to the external table by specifying a read/write status column
by column (rather than for the table as a whole).
As an example, an external table for diet.mod might consist of columns
cost, f_min and f_max containing input for
the model, and a column Buy containing the results. If this
is maintained as a Microsoft Access table named Diet within a
file Diet.mdb, then the table declaration for reading
data into AMPL could be
Although details may vary with the database software used, the
general convention is that overwriting of any existing table or file is
intended only when all data columns in the table
declaration have read/write status OUT. Selective rewriting
or addition of columns is intended otherwise. Thus if our AMPL table
for output had been declared
Reading and writing using the same table
declaration. In many cases, all of the information for both
reading and writing an external table can be specified in the same
table declaration:
As an example, the declarations defining FoodInput and
FoodOutput above could be replaced by
General rules for specifying read/write status.
The keywords IN, OUT, and INOUT can be used
to specify the default read/write status of a table or the read/write
status of individual data columns.
A table's default read/write status may be specified by a keyword
following the table-name. It is taken to be INOUT if
no keyword is given.
A data column's read/write status may be specified by a keyword
following the data-col-name. It is taken to be the same as the
table's default read/write status if no keyword is given.
Because key columns have a special interpretation, their
read/write status is handled separately. For reading, if the
key-spec has one of the forms
For writing, if the key-spec has one of the forms
To illustrate indexed collections of tables, we present a
script that automatically solves a series of scenarios stored
separately. To illustrate indexed collections of columns, we show how
a "two-dimensional" spreadsheet table can be read.
Indexed collections of tables. AMPL table
declarations can be indexed in much the same way as AMPL sets,
parameters, and other model components. An optional
{indexing-expr} follows the table-name:
As an example, the following declaration defines a collection of
AMPL tables indexed over the set of foods in diet.mod, each table corresponding
to a different database table in the Microsoft Access file
DietSens.mdb:
A string expression can be used in a similar way to cause each
AMPL table to correspond to the same Access table, but with a different
data-col-name for the optimal amounts:
Indexed collections of data columns. Because
there is a natural correspondence between data columns of a relational
table and indexed collections of entities in an AMPL model, each
data-spec in a table declaration normally refers to a
different AMPL parameter, variable, or expression. Occasionally the
values for one AMPL entity are split among multiple data columns,
however. Such a case can be handled by defining a collection of data
columns, one for each member of a specified indexing set.
The general form for specifying an indexed collection of
table columns is
The most common use of this feature is to read or write
"two-dimensional" tables . For example, the data for the parameter
A similar approach works to write two-dimensional tables to
spreadsheets. For example, after steelT.mod is solved, the results could be written to a spreadsheet using the
following table declaration,
The initial release of the database access feature includes a
"standard"
Microsoft Windows database handler that communicates via the Open
Database Connectivity (ODBC) application programming interface. It
recognizes relational tables in the formats used by Microsoft Access,
Microsoft Excel, and any other application for which an ODBC driver
exists on your computer. (To see a list of ODBC drivers installed,
open the ODBC or ODBC Data Sources
control panel.)
In addition to any supplied handlers, minimal ASCII and binary
relational table file handlers are built into AMPL for purposes of
testing. Vendors may exercise the option of including other handlers as
built-in.
The built-in set _HANDLERS gives a list of handlers
currently seen by AMPL. A built-in symbolic parameter
_handler_lib indexed over _HANDLERS records the
shared library in which each handler was found (or
<built-in> for built-in handlers). When the
above-mentioned handlers are accessible, for example, this handler
information appears as follows:
The remainder of this section describes the string-lists
that are recognized by AMPL's standard ODBC handler. Following a
general introduction, specific instructions are provided for the two
applications, Access and Excel, that are used in many of the examples
in preceding sections. A final subsection describes the
string-lists recognized by the built-in binary and ASCII table
handlers.
Using the standard ODBC table handler. The netlib
AMPL directory provides a (gzip-compressed) Windows dynamic link
library
ampltabl.dll
that supports database connections to via ODBC. To make this handler
available to AMPL sessions, place it in the same directory as the AMPL
program file (normally ampl.exe) or at another location
recognized by the ampltabl.dll loading
rules for Windows.
In the context of a declaration that begins table
table-name ..., the general form of the
string-list for the standard ODBC table handler is
The second string identifies the external database file that is
to be read or written upon subsequent execution of the command
read table table-name or write
table table-name, respectively. There are several
possibilities, depending on the form of the connection-spec and
the configuration of Windows ODBC on your computer:
The third string normally gives the name of the relational
table, within the specified file, that is to be read or written upon
execution of read table table-name or
write table table-name. If the third string
is omitted, then the name of the relational table is taken to be the
same as the table-name of the containing table
declaration. For writing, if the indicated table does not exist, it is
created; if the table exists but all of the table declaration's
data-specs have read/write status OUT, then it is
overwritten. Otherwise, writing causes the existing table to be
modified; each column written either overwrites an existing column of
the same name, or becomes a new column appended to the table.
Alternatively, if the third string has the special form
As an example, if you wanted to read as data for diet.mod only those foods having a
cost of $2.49 or less, you could use an SQL query to extract the
relevant records from the Foods table of your database:
Data values representing specific dates and times -- so-called
timestamp data -- can be read and written by AMPL through
table declarations in the same way as other data. When read
into an AMPL numeric parameter, timestamp values are integers of the
form YYYYMMDDhhmmss, with YYYY giving the year, MM
the month, DD the day, and hhmmss the hours, minutes, and
seconds. When written from a numeric parameter to an existing database
column that has a timestamp format, integers of this form are
automatically recognized as times and are handled accordingly. When
integers of this form are written to a new column, however, it is
necessary to tell the database handler that they are to be interpreted
as times. This is done by adding the following string after the first
three strings in the string-list:
The string "verbose" after the first three strings
requests diagnostic messages -- such as the DSN= string that
ODBC reports using -- whenever the containing table declaration is used
by a read table or write table
command. (The ordering of strings after the first three in the
string-list does not matter.)
Using the standard ODBC table handler with
Access. To set up a relational table correspondence for reading
or writing Microsoft Access files, specify the ext in the second
string of the string-list as mdb:
Using the standard ODBC table handler with
Excel. To set up a relational table correspondence for reading
or writing Microsoft Excel spreadsheets, specify the ext in the
second string of the string-list as xls:
The external-table-name specified by the third string
identifies a spreadsheet range, within the specified file, that is to
be read or written; if this string is absent, it is taken to be the
table-name given at the start of the table declaration.
For reading, the specified range must exist in the Excel file. For
writing, if the range does not exist, it is created, at the upper left
of a new worksheet having the same name. If the range exists but all
of the table declaration's data-specs have read/write
status OUT, then it is overwritten. Otherwise, writing causes
the existing range to be modified. Each column written either
overwrites an existing column of the same name, or becomes a new column
appended to the table; each row written either overwrites entries in an
existing row having the same key column entries, or becomes a new row
appended to the table.
When writing causes an existing range to be extended, rows or
columns are added at the bottom or right of the range, respectively.
The cells of added rows or columns must be empty; otherwise, the
attempt to write the table fails and the write table
command elicits an error message. After a table is successfully
written, the corresponding range is created or adjusted to contain
exactly the cells of that table.
Built-in table handlers for text and binary files.
For debugging and demonstration purposes, AMPL has built-in handlers
for two very simple relational table formats. These formats store one
table per file and convey equivalent information. One produces ASCII
files that can be examined in any text editor, while the other creates
binary files that are much faster to read and write.
For these handlers, the table declaration's
string-list contains at most one string, identifying the
external file that contains the relational table. If the string has
the form
For reading, the indicated file must exist. For writing, if
the file does not exist, it is created. If the file exists but all of
the table declaration's data-specs have read/write status
OUT, then it is overwritten. Otherwise, writing causes the
existing file to be modified; each column written either replaces an
existing column of the same name, or becomes a new column added to the
table.
The format for the text files can be examined by simply writing
one and viewing the results in a text editor. For example, the
following AMPL session,
General forms of the table declaration
To use AMPL's relational database access features, you must
supply one table declaration for each different relational table
to be read or written (or both). This and the following four sections
describe aspects of the table declaration that are intended to
be independent of external software, while the final section describes
aspects of AMPL's standard implementation for Microsoft Access and
Excel (and other Windows software accessible via ODBC) and for "plain"
text and binary files.
table table-name
{indexing-expr}opt inoutopt string-listopt :
The part before the colon (:) deals with the relational table
as a whole, while the part after establishes correspondences with
particular columns of the table.
key-spec, data-spec, data-spec, data-spec, ... ;
IN
contents to be read in to AMPL from a relational table
When no inout keyword is specified, INOUT is assumed.
OUT
contents to be written out from AMPL to a relational table
INOUT
contents to be both read and written
[key-col-spec, key-col-spec, ...] inoutopt
where the key-col-spec is either of
set-expr arrow [key-col-spec, key-col-spec, ...]
key-col-name
Each key-col-spec names a key column of the table; the optional
index is for use in subsequent data-specs, where it takes
values from the key column. The set-expr is an expression for a
corresponding AMPL set, whose arity must equal the number of
key-col-specs. The inout keyword or arrow symbol
(either <-, ->, or <->) indicates whether set
members are to be read or written (or both).
index ~ key-col-name
data-col-name inoutopt
where col-name identifies the data column and the optional
expr identifies the corresponding AMPL entity.
data-expr ~ data-col-name inoutopt
Reading data from relational tables
To use an external relational table for reading only, you
should employ a table declaration that specifies a read/write
status of IN. Thus it should have the general form
table table-name IN string-listopt :
where the optional string-list is specific to the database type
and access method being used. (In the interest of brevity, most
subsequent examples do not show a string-list.) Data values are
subsequently read from the table into AMPL entities by use of the command
key-spec, data-spec, data-spec, data-spec, ... ;
read table table-name ;
which determines the values to be read by referring back to the
table declaration that defined table-name.
This indicates that the relational table has 4 columns, comprising a
key column FOOD and data columns cost, f_min
and f_max. The data columns are associated with parameters
cost, f_min and f_max in the current AMPL
model. Since there is only one key column, all of these parameters
must be indexed over one-dimensional sets.
table Foods IN: [FOOD], cost, f_min, f_max;
then the processing of the first row assigns 3.19 to parameter
cost['BEEF'], 0 to f_min['BEEF'], and 100 to
f_max['BEEF']; the processing of the second row assigns 2.59
to parameter cost['CHK'], 0 to f_min['CHK'], and 100
to f_max['CHK']; and so forth through the 6 remaining rows.
FOOD cost f_min f_max
BEEF 3.19 0 100
CHK 2.59 0 100
FISH 2.29 0 100
HAM 2.89 0 100
MCH 1.89 0 100
MTL 1.99 0 100
SPG 1.99 0 100
TUR 2.49 0 100
the listed data columns, market and revenue, must
correspond to AMPL parameters market and revenue that
are indexed over two-dimensional sets.
table SteelProd IN: [PROD, TIME], market, revenue;
then the processing of the first row assigns 6000 to
market['bands',1] and 25 to revenue['bands',1]; the
processing of the second row assigns 6000 to market['bands',2]
and 26 to revenue['bands',2]; and so forth through all 8
rows. The pairs of subscripts given by the key column entries must be
valid for market and revenue when the values of these
parameters are first needed by AMPL, but the parameters need not be
declared over sets named PROD and TIME. (In fact, in
the model from which this example is taken, the parameters are indexed
by {PROD, 1..T} where T is a previously
defined parameter.)
PROD TIME market revenue
bands 1 6000 25
bands 2 6000 26
bands 3 4000 27
bands 4 6500 27
coils 1 4000 30
coils 2 2500 35
coils 3 3500 37
coils 4 4200 39
and the corresponding relational table has one column, also named
T, whose one entry is a positive integer.
table SteelPeriods IN: [], T;
set-name <-
[key-col-spec, key-col-spec, ...]
The <- symbol is intended as an "arrow" pointing in the
direction that the information is moved: from the key columns to the
AMPL set.
When read table Foods is executed, all
entries in the key column FoodName of the relational table
are read into AMPL as members of the set FOOD, and the
entries in the data columns cost, f_min and
f_max will be read into the like-named AMPL parameters as
previously described. If the key column is named FOOD like
the AMPL set, then the appropriate table declaration becomes
table Foods IN: FOOD <- [FoodName], cost, f_min, f_max;
In this special case only, the key-spec can also be written in
the abbreviated form [FOOD] IN.
table Foods IN: FOOD <- [FOOD], cost, f_min, f_max;
When read table TransLinks is executed, each
row of the table provides a pair of entries from key columns
ORIG and DEST. All such pairs are read into AMPL as
members of the 2-dimensional set LINKS. Finally, the entries
in column cost are read into parameter cost in the
usual way.
table TransLinks IN: LINKS <- [ORIG, DEST], cost;
In the similar model transp2.mod, by contrast,
LINKS is defined in terms of two one-dimensional sets,
set ORIG;
set DEST;
set LINKS within {ORIG,DEST};
param cost {LINKS} >= 0;
and in transp.mod, no
named two-dimensional set is defined at all:
set ORIG;
set DEST;
set LINKS := {ORIG,DEST};
param cost {LINKS} >= 0;
In these latter cases, a table declaration would still be needed
for reading parameter cost, but it would not specify the
reading of any associated set:
set ORIG;
set DEST;
param cost {ORIG,DEST} >= 0;
Separate relational tables would instead be used to provide
members for the one-dimensional sets ORIG and DEST
and values for the parameters indexed over them.
table TransLinks IN: [ORIG, DEST], cost;
then the AMPL parameters f_min and f_max would be
read from data columns lowerlim and upperlim in the
relational table. (Parameter cost would be read from column
cost as before.)
table Foods IN: [FOOD], cost, f_min ~ lowerlim, f_max ~ upperlim;
table SteelProd IN: [p ~ PROD, t ~ TIME],
market[p,t+1] ~ market, revenue[p,t+1] ~ revenue;
table SteelProd IN: [p ~ PROD, t ~ TIME],
market, revenue[t,p] ~ revenue;
table SteelProd IN: [t ~ TIME],
revenue["bands",t] ~ revbands, revenue["coils",t] ~ revcoils;
This will usually be rejected as an error, however, because
revenue[t,p] is not a valid name for a relational table column
in most database software. Instead it is necessary to write
revenue[t,p] ~ revenue to indicate that the
AMPL parameters revenue[t,p] receive values from the column
revenue of the table.
table SteelProd IN: [p ~ PROD, t ~ TIME], market, revenue[t,p]; # ERROR
An execution of read table Foods would
supply members for set FOOD and values for parameters
cost, f_min and f_max in the usual way, and
would also assign initial values and branching priorities to the
Buy variables.
table Foods IN: FOOD IN, cost, f_min, f_max, Buy, Buy.priority ~ prior;
Writing data to relational tables
To use an external relational table for writing only, you
should employ a table declaration that specifies its read/write
status to be OUT. The general form of such a declaration is
table table-name OUT string-listopt :
where the optional string-list is specific to the database type
and access method being used. (In the interest of brevity, most
subsequent examples do not show a string-list.) AMPL expression
values are subsequently written to the table by use of the command
key-spec, data-spec, data-spec, data-spec, ... ;
write table table-name ;
which determines the information to be written by referring back to the
table declaration that defined table-name.
[key-col-name, key-col-name, ...]
then the table declaration works much like the AMPL
display command. It determines the external table rows to
be written by taking the union of the indexing sets stated or implied
in the data-specs. The format of the data-spec list is
the same as in display, except that all of the items listed
must be indexed over sets of the same dimension.
When write table Foods is executed, it
creates a key column FoodName and data columns f_min,
Buy, and f_max. Since the AMPL components
corresponding to the data columns are all indexed over the AMPL set
FOOD, one row is created for each member of FOOD. In
a representative row, a member of FOOD is written to the key
column FoodNames, and the values of f_min,
Buy, and f_max subscripted by that member are written
to the like-named data columns. For the data used in our diet example,
the resulting relational table would be:
table Foods OUT: [FoodName], f_min, Buy, f_max;
Tables corresponding to higher-dimensional sets are handled
analogously, with the number of bracketed key-column names listed in
the key-spec being equal to the dimension of the items in the
data-spec. Thus a table containing the results from steelT.mod could be defined as
FoodName f_min Buy f_max
BEEF 2 5.36061 10
CHK 2 2 10
FISH 2 2 10
HAM 2 10 10
MCH 2 10 10
MTL 2 10 10
SPG 2 9.30605 10
TUR 2 2 10
Make and Sell are indexed over {PROD,1..T},
while Inv is indexed over {PROD,0..T}. Thus a
subsequent write table SteelProd command
would produce a table having one row for each member of the union of
these sets:
table SteelProd OUT: [PROD, TIME], Make, Sell, Inv;
Two rows are empty in the columns for Make and Sell,
because ("bands",0) and ("coils",0) are not members
of the index sets of Make and Sell. We use a
"." here to indicate the empty table entries, but the actual
appearance and handling of empty entries will vary depending on the
database software being used.
PROD TIME Make Sell Inv
bands 0 . . 10
bands 1 5990 6000 0
bands 2 6000 6000 0
bands 3 1400 1400 0
bands 4 2000 2000 0
coils 0 . . 0
coils 1 1407 307 1100
coils 2 1400 2500 0
coils 3 3500 3500 0
coils 4 4200 4200 0
then a subsequent write table Nutrs
command is likely to be rejected, because names having a "dot" in the
middle are not valid column names in most databases:
table Nutrs OUT: [Nutrient],
diet.lslack, diet.ldual, diet.uslack, diet.udual; # ERROR
This situation requires that each AMPL expression be followed by the
operator ~ and a corresponding valid column name for use in
the relational table:
ampl: write table Nutrs;
Error executing "write table" command:
Error writing table Nutrs with table handler ampl.odbc:
Column 2's name "diet.lslack" contains non-alphanumeric character '.'.
This says that the values represented by diet.lslack should be
placed in a column named lb_slack, the values represented by
diet.ldual should be placed in a column named
lb_dual, and so forth. With the table defined in this way, a
write table Nutrs command produces the
intended relational table:
table Nutrs OUT: [Nutrient],
diet.lslack ~ lb_slack, diet.ldual ~ lb_dual,
diet.uslack ~ ub_slack, diet.udual ~ ub_dual;
The ~ can also be used with unsuffixed names, if it is desired
to assign the dabatase column a name different from that of the
corresponding AMPL entity.
Nutrient lb_slack lb_dual ub_slack ub_dual
A 1256.29 0 18043.7 0
B1 336.257 0 18963.7 0
B2 0 0.404585 19300 0
C 982.515 0 18317.5 0
CAL 3794.62 0 4205.38 0
NA 50000 0 0 -0.00306905
or
table Purchases OUT: [FoodName],
Buy ~ servings, {j in FOOD} 100*Buy[j]/f_max[j] ~ percent;
Either way, since both data-specs give expressions indexed over
the AMPL set FOOD, the resulting table has one row for each
member of that set:
table Purchases OUT: [FoodName],
{j in FOOD} (Buy[j] ~ servings, 100*Buy[j]/f_max[j] ~ percent);
The expression in a data-spec may also use operators like
sum that define their own dummy indices. Thus a table of
total production and sales by period for steelT.mod could be specified by
FoodName servings percent
BEEF 5.36061 53.6061
CHK 2 20
FISH 2 20
HAM 10 100
MCH 10 100
MTL 10 100
SPG 9.30605 93.0605
TUR 2 20
As a two-dimensional example, a table of the amounts sold and the
fractions of demand met could be specified by
table SteelTotal OUT: [TIME],
{t in 1..T} (sum {p in PROD} Make[p,t] ~ Made,
sum {p in PROD} Sell[p,t] ~ Sold);
The resulting external table would have key columns PROD and
TIME, and data columns Sell and MeetDemand.
table SteelSales OUT: [PROD, TIME],
Sell, {p in PROD, t in 1..T} Sell[p,t]/market[p,t] ~ MeetDemand;
set-spec ->
[key-col-spec, key-col-spec, ...]
In contrast to the "arrow" <- that points from a
key-column list to an AMPL set, indicating values to be read into the
set, this form uses an arrow -> that points from an AMPL set
to a key column list, indicating information to be written from the set
into the key columns. An explicit expression for the row index set is
given by the set-spec, which can be any of
set-name
where set-expr may be any AMPL set-valued expression, and the
optional index-list specifies dummy indices running over the
set. Each key-col-spec may be either of
set-name[subscript-list]
{ set-expr }
{ index-list in set-expr }
key-col-name
where the index is an alternative dummy index as explained in
the examples below.
index ~ key-col-name
When write table Foods is executed, a table
row is created for each member of the AMPL set FOOD. In that
row, the set member is written to the key column FoodNames,
and the values of f_min, Buy, and f_max
subscripted by the set member are written to the like-named data
columns. (For the data used in our diet example, the resulting table
would be the same as for FoodName table given previously in
this section.) If the key column has the same name, FOOD, as
the AMPL set, then the appropriate table declaration becomes
table Foods OUT: FOOD -> [FoodName], f_min, Buy, f_max;
In this special case only, the key-spec can also be written in the
abbreviated form [FOOD] OUT.
table Foods OUT: FOOD -> [FOOD], f_min, Buy, f_max;
and write table Nutrs would give the same
table as previously shown.
table Nutrs OUT: NUTR -> [Nutrient],
diet.lslack ~ lb_slack, diet.ldual ~ lb_dual,
diet.uslack ~ ub_slack, diet.udual ~ ub_dual;
or in a key-col-spec of the form index ~ key-col-name:
table Purchases OUT: {j in FOOD} -> [FoodName],
Buy[j] ~ servings, 100*Buy[j]/f_max[j] ~ percent;
These two forms are equivalent. Either way, as each row is written,
the index j takes the value written to the key column, and
this value is then used in interpreting the expressions that give the
values for the data columns. For our example, the resulting table --
having key column FoodName and data columns servings
and percent, is the same as previously shown. Similarly, the
previous example of the table SteelTotal could be written as
either
table Purchases OUT: FOOD -> [j ~ FoodName],
Buy[j] ~ servings, 100*Buy[j]/f_max[j] ~ percent;
or
table SteelTotal OUT: {t in 1..T} -> [TIME],
sum {p in PROD} Make[p,t] ~ Made,
sum {p in PROD} Sell[p,t] ~ Sold;
The result will have a key column TIME containing the integers
1 through T, and data columns Made and Sold
containing the values of the two summations.
table SteelTotal OUT: {1..T} -> [t ~ TIME],
sum {p in PROD} Make[p,t] ~ Made,
sum {p in PROD} Sell[p,t] ~ Sold;
and a subsequent write table steelprod would
produce a table of the form
table SteelProd OUT:
{PROD, 1..T} -> [PROD, TIME], Make, Sell, Inv;
This result is not quite the same as the table produced by the previous
SteelProd example, because the rows to be written here
correspond explicitly to the members of the AMPL set {PROD,
1..T}, rather than being inferred from the indexing sets of
Make, Sell, and Inv. In particular, the
values of Inv["bands",0] and Inv["coils",0] do not
appear in this table.
PROD TIME Make Sell Inv
bands 1 5990 6000 0
bands 2 6000 6000 0
bands 3 1400 1400 0
bands 4 2000 2000 0
coils 1 1407 307 1100
coils 2 1400 2500 0
coils 3 3500 3500 0
coils 4 4200 4200 0
or with dummy indices added to the key-col-specs:
table SteelSales OUT:
{p in PROD, t in 1..T} -> [PROD, TIME],
Sell[p,t] ~ sold, Sell[p,t]/market[p,t] ~ met;
If dummy indices happen to appear in both the set-spec and the key-col-specs, ones in the key-col-specs take precedence.
table SteelSales OUT:
{PROD,1..T} -> [p ~ PROD, t ~ TIME],
Sell[p,t] ~ sold, Sell[p,t]/market[p,t] ~ met;
Reading and writing the same table
To read data from a relational table and then write results to
the same table, you can use a pair of table declarations that
reference the same file and table names. You may also be able to
combine these declarations into one that specifies some columns to be
read and others to be written. This section gives examples and
instructions for both of these possibilities.
The corresponding declaration for writing the results would have a
different AMPL table-name but would refer to the same Access
table and file:
table FoodInput IN "ODBC" "DIET.mdb" "Diet":
FOOD <- [FoodNames], cost, f_min, f_max;
When read table FoodInput is executed, only
the three columns listed in the table FoodInput
declaration are read; if there is an existing column named Buy,
it is ignored. Later, when the problem has been solved and
write table FoodOutput is executed, only the
one column that has read/write status OUT in the
table FoodOutput declaration is written to the Access
table, while the table's other columns are left unmodified.
table FoodOutput "ODBC" "DIET.mdb" "Diet":
[FoodNames], cost IN, f_min IN, Buy OUT, f_max IN;
then all of the data columns in Access table Diet would have
been deleted by write table FoodOutput;
but the alternative
table FoodOutput "ODBC" "DIET.mdb" "Diet": [FoodNames], Buy OUT;
would have only overwritten the column Buy, just as in the
example we originally gave, since there is a data column (namely
Buy itself) that does not have read/write status
OUT. (In fact INOUT could be omitted here, since it
is the default for read/write status. Rules for specifying read/write
status are summarized at the end of this section.)
table FoodOutput "ODBC" "DIET.mdb" "Diet": [FoodNames], Buy INOUT;
A read table table-name command reads only
the columns, key or data, that are specified in the declaration of
table-name as being IN or INOUT. A
write table table-name command analogously
writes to only the columns that are specified as OUT or
INOUT.
A read table Foods would then read only from
key column FoodNames and data columns cost,
f_min and f_max. A later write
table Foods would write only to the column
Buy.
table Foods "ODBC" "DIET.mdb" "Diet":
FOOD <- [FoodNames], cost IN, f_min IN, Buy OUT, f_max IN;
set-spec <-
[key-col-spec, key-col-spec, ...]
then the contents of the key columns are read into the AMPL set
set-spec. Otherwise, values are not read into any AMPL set.
set-spec <->
[key-col-spec, key-col-spec, ...]
set-spec ->
[key-col-spec, key-col-spec, ...]
then the contents of the key columns are written from the AMPL set
set-spec. Otherwise, the contents of the key columns are
written from an AMPL set that is inferred from the data-specs,
as explained in Writing data to relational tables
above.
set-spec <->
[key-col-spec, key-col-spec, ...]
Indexed collections of tables and columns
In some circumstances, it is convenient to declare an indexed
collection of tables, or to define an indexed collection of data
columns within a table. This section explains how indexing of these
kinds can be specified within the table declaration.
table table-name
{indexing-expr}opt inoutopt string-listopt : ...
One table is defined for each member of the set specified by the
indexing-expr. Individual tables in this collection are denoted
in the usual way, by appending a bracketed subscript or subscripts to
the table-name.
Following the rules for the standard ODBC table
handler, the Access table names are given by the third item in the
string-list, the AMPL string expression
("Sens" & j). Thus the AMPL table DietSens["BEEF"]
is associated with the Access table SensBEEF, the AMPL table
DietSens["CHK"] is associated with the Access table
SensCHK, and so forth. The following AMPL
script uses these tables to record the optimal diet when there is a
two-for-the-price-of-one sale on each of the foods:
table DietSens {j in FOOD} OUT "ODBC" "DietSens.mdb" ("Sens" & j):
[Food], f_min, Buy, f_max;
For the data in diet2a.dat, the set
FOOD has 8 members, so 8 tables are written in the Access
database:
for {j in FOOD} {
let cost[j] := cost[j] / 2;
solve;
write table DietSens[j];
let cost[j] := cost[j] * 2;
}
If instead the table declaration were to give a string expression
for the second string in the string-list, which specifies the Access
filename,
then 8 different Access database files, named
DietSensBEEF.mdb, DietSensCHK.mdb, and so forth would
be written, each containing a single table named (by default)
DietSens. (These files would all need to have been created
before the write table commands were executed.)
table DietSens {j in FOOD} OUT "ODBC" ("DietSens" & j & ".mdb"):
[Food], f_min, Buy, f_max;
Then running the script shown above will result in the following Access table:
table DietSens {j in FOOD} "ODBC" "DietSens.mdb":
[Food], Buy ~ ("Buy" & j);
The AMPL tables in this case were deliberately left with the default
read/write status, INOUT. Had the read/write status been
specified as OUT, then each write table
would have overwritten the columns created by the previous one.
{indexing-expr} < data-spec, data-spec, data-spec, ... >
where each data-spec has any of the forms previously given. For
each member of the set specified by the indexing-expr, AMPL
generates one copy of each data-spec within the "angle brackets"
<...>. As in other cases of AMPL indexing, the
indexing-expr also defines one or more dummy indices that run
over the index set; these indices are employed in the usual way within
AMPL expressions in the data-specs, and these indices also
appear within string expressions that give the
names of columns in the external database.
from diet.mod might be
represented in an Excel spreadsheet as a table with nutrients labeling
the rows and foods the columns:
param amt {NUTR,FOOD} >= 0;
To read this table using AMPL's external database features, we must
regard it as having one key column, under the heading NUTR,
and data columns headed by the names of individual foods. Thus we
require a table declaration whose key-spec is
one-dimensional and whose data-specs are indexed over the AMPL
set FOOD:
The key-spec [i ~ NUTR] associates
the first table column with the set NUTR in the standard way.
The data-spec of the form {j in FOOD}
<...> causes AMPL to generate an individual data-spec
for each of the members of set FOOD. Specifically, for each
j in FOOD, AMPL generates the
data-spec amt[i,j] ~ (j), where
(j) is the AMPL string expression for the heading of the
external table column for food j, and amt[i,j]
denotes the AMPL parameter to which the values in that column are to be
written. (According to the convention used here and in other AMPL
declarations and commands, the parentheses around (j) cause it
to be interpreted as an expression for a string; without the
parentheses it would denote a column-name consisting of the
single character j.)
table dietAmts IN "ODBC" "diet2D.xls":
[i ~ NUTR], {j in FOOD} <amt[i,j] ~ (j)>;
or, equivalently, using display-style indexing:
table Results1 OUT "ODBC" "steel2out.xls":
{p in PROD} -> [Product],
Inv[p,0] ~ Inv0,
{t in 1..T} < Make[p,t] ~ ('Make' & t),
Sell[p,t] ~ ('Sell' & t), Inv[p,t] ~ ('Inv' & t) >;
The key column labels the rows with product names. The data columns
include one for the initial inventories, and then three representing
production, sales, and inventories, respectively, for each period:
table Results2 OUT "ODBC" "steel2out.xls":
[Product],
{p in PROD} Inv[p,0] ~ Inv0,
{t in 1..T} < {p in PROD} (
Make[p,t] ~ ('Make' & t),
Sell[p,t] ~ ('Sell' & t), Inv[p,t] ~ ('Inv' & t) ) >;
Conceptually, there is a symmetry between the row and column indexing
of a two-dimensional table. But because the tables in these examples
are being treated as relational tables, the table declaration
must treat the row indexing and the column indexing in different ways.
As a result, the declaration's expressions describing the row indexing
are substantially different from its expressions describing the column
indexing.
Standard and built-in table handlers
To work with external database files, AMPL relies on database
handlers. These are add-ons, usually in the form of shared or
dynamic link libraries, that can be loaded as needed. Handlers may be
supplied by vendors of AMPL or of database software.
A built-in symbolic parameter _handler_desc is also indexed
over _HANDLERS. The value of _handler_desc[h] is
normally a longer string that gives a summary of instructions for using
handler h. For example:
ampl: display _HANDLERS;
set _HANDLERS := tab bit odbc;
ampl: display _handler_lib;
_handler_lib [*] :=
tab '<built-in>'
bit '<built-in>'
odbc ampltabl.dll
;
As these summaries suggest, AMPL communicates with handlers through the
string-list in the table declaration. The form and
interpretation of the string-list are specific to each handler.
ampl: print _handler_desc['tab'];
Builtin file.tab (ASCII table) handler: at most one string
(the file name, ending in ".tab") expected before ":[...]";
table_name.tab is assumed if there are no strings.
ampl: print _handler_desc['bit'];
Builtin file.bit (binary table) handler: exactly one string
(the file name, ending in ".bit") expected before ":[...]".
ampl: print _handler_desc['odbc'];
AMPL ODBC handler: expected 2-5 strings before ":[...]":
'ODBC', connection_spec ['external_table_name'] ['time=...'] ['verbose']
For IN tables, 'external_table_name' can also have the form 'SQL=sqlstmt',
where sqlstmt is a SQL statement, such as a SELECT statement.
Alternatives for connection_spec:
'filename.ext', where ext is a registered ODBC extension;
'filename.dsn' (written by the ODBC control panel's "File DSN");
an explicit connection string of the form 'DSN=...' or 'DRIVER=...';
or an ODBC data source name (see the ODBC control panel).
"ODBC" "connection-spec"
"external-table-spec"opt
"time=data-column-list"opt
"verbose"opt
The first string tells AMPL that data transfers using this table
declaration should employ the standard ODBC handler. Subsequent
strings then provide directions to that handler as follows.
Information about your computer's configuration of ODBC drivers, data
source names, file data sources, and related entities can be examined
and changed through your ODBC control panel. The name of this control
panel may be ODBC, ODBC Data
Sources, or ODBC Data Sources
(32bit), depending on the specifics of your installation.
"SQL=sql-query"
then the table declaration applies to the relational table that
is (temporarily) created by a statement in the Structured Query
Language, commonly abbreviated SQL. Specifically, a relational
table is first constructed by executing the SQL statement given by
sql-query, with respect to the database file given by the second
string in the table declaration's string-list. Then the
usual actions of the table declaration are applied to the
constructed table. All columns specified in the table
declaration should have read/write status IN, since it would
make no sense to write to a temporary table. Normally the
sql-query is a SELECT statement, which is SQL's primary
device for operating on tables to create new ones.
Then to read the relevant data for parameter amt, which is
indexed over nutrients and foods, you would want to read only those
records that pertained to a food having a cost of $2.49 or less. Here
is one way that an SQL query could be used to extract the desired records:
table cheapFoods IN "ODBC" "diet.mdb"
"SQL=SELECT * FROM Foods WHERE cost <= 2.49":
FOOD <- [FOOD], cost, f_min, f_max;
Here we have used an AMPL option to store the string containing the SQL
query. Then the table declaration's third string can be given
by the relatively short string expression
"SQL=" & $selectAmts.
option selectAmts "SELECT NUTR, Amounts.FOOD, amt FROM Amounts, Foods "
"WHERE Amounts.FOOD = Foods.FOOD and cost <= 2.49";
table cheapAmts IN "ODBC" "diet.mdb" ("SQL=" & $selectAmts):
[NUTR, FOOD], amt;
"time=data-column-list"
The data-column-list is a comma-separated list of external
database columns that are to have timestamp data type when created.
"ODBC" "file-name.mdb"
"external-table-spec"opt
"time=data-column-list"opt
"verbose"opt
The file called "file-name.mdb" must exist,
though for writing it may be a database that does not yet contain any
tables.
"ODBC" "file-name.xls"
"external-table-name"opt
"time=data-column-list"opt
"verbose"opt
In this case, the second string identifies the external Excel workbook
file that is to be read or written. For writing, the file specified by
the second string is created if it does not exist already.
"file-name.tab"
then the file is taken to be an ASCII text file; if it has the form
"file-name.bit"
then it is taken to be a binary file. If no string-list is
given, then a text file table-name.tab is assumed.
produces a file DietOpt.tab that can be seen to have the
following content:
ampl: model diet.mod;
ampl: data diet2a.dat;
ampl: solve;
MINOS 5.5: optimal solution found.
13 iterations, objective 118.0594032
ampl: table ResultList OUT "DietOpt.tab":
ampl? [FOOD], Buy, Buy.rc, {j in FOOD} Buy[j]/f_max[j];
ampl: write table ResultList;
In the first line, ampl.tab identifies this as an AMPL
relational table text file, and is followed by the numbers of key and
non-key columns, respectively. The second line gives the names of the
table's columns, which may be any strings. (Use of the ~
operator to specify valid column-names is not necessary in this case.)
Each subsequent line gives the values in one table row; numbers are
written in full precision, with no special formatting or alignment.
ampl.tab 1 3
FOOD Buy Buy.rc 'Buy[j]/f_max[j]'
BEEF 5.360613810741678 8.881784197001252e-16 0.5360613810741678
CHK 2 1.188840579710143 0.2
FISH 2 1.144407502131287 0.2
HAM 10 -0.3026513213981231 1
MCH 10 -0.551150895140665 1
MTL 10 -1.3289002557544745 1
SPG 9.306052855924984 0 0.9306052855924983
TUR 2 2.7316197783461194 0.2
Comments or questions?
Write to info@ampl.com
or use our comment form.
Return to the AMPL update page.