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 :
key-spec, data-spec, data-spec, data-spec, ... ;
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
write table table-name ;
which determines the information to be written by referring back to the
table declaration that defined table-name.
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,
[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.
In the simplest case, the data-specs are the names
of model components indexed over the same set:
table Foods OUT: [FoodName], f_min, Buy, f_max;
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:
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
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
table SteelProd OUT: [PROD, TIME], Make, Sell, Inv;
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:
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
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.
If this form is applied to writing suffixed variable or
constraint names, such as the dual and slack values related to the
constraint diet,
table Nutrs OUT: [Nutrient],
diet.lslack, diet.ldual, diet.uslack, diet.udual; # ERROR
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:
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 situation requires that each AMPL expression be followed by the
operator ~ and a corresponding valid column name for use in
the relational table:
table Nutrs OUT: [Nutrient],
diet.lslack ~ lb_slack, diet.ldual ~ lb_dual,
diet.uslack ~ ub_slack, diet.udual ~ ub_dual;
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:
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
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.
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
table Purchases OUT: [FoodName],
Buy ~ servings, {j in FOOD} 100*Buy[j]/f_max[j] ~ percent;
or
table Purchases OUT: [FoodName],
{j in FOOD} (Buy[j] ~ servings, 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:
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
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
table SteelTotal OUT: [TIME],
{t in 1..T} (sum {p in PROD} Make[p,t] ~ Made,
sum {p in PROD} Sell[p,t] ~ Sold);
As a two-dimensional example, a table of the amounts sold and the
fractions of demand met could be specified by
table SteelSales OUT: [PROD, TIME],
Sell, {p in PROD, t in 1..T} Sell[p,t]/market[p,t] ~ MeetDemand;
The resulting external table would have key columns PROD and
TIME, and data columns Sell and MeetDemand.
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
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
set-name[subscript-list]
{ set-expr }
{ index-list in set-expr }
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
key-col-name
index ~ key-col-name
where the index is an alternative dummy index as explained in
the examples below.
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:
table Foods OUT: FOOD -> [FoodName], f_min, Buy, f_max;
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 -> [FOOD], f_min, Buy, f_max;
In this special case only, the key-spec can also be written in the
abbreviated form [FOOD] OUT.
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
table Nutrs OUT: NUTR -> [Nutrient],
diet.lslack ~ lb_slack, diet.ldual ~ lb_dual,
diet.uslack ~ ub_slack, diet.udual ~ ub_dual;
and write table Nutrs would give the same
table as previously shown.
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 },
table Purchases OUT: {j in FOOD} -> [FoodName],
Buy[j] ~ servings, 100*Buy[j]/f_max[j] ~ percent;
or in a key-col-spec of the form index ~ key-col-name:
table Purchases OUT: FOOD -> [j ~ 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 SteelTotal OUT: {t in 1..T} -> [TIME],
sum {p in PROD} Make[p,t] ~ Made,
sum {p in PROD} Sell[p,t] ~ Sold;
or
table SteelTotal OUT: {1..T} -> [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.
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
table SteelProd OUT:
{PROD, 1..T} -> [PROD, TIME], Make, Sell, Inv;
and a subsequent write table steelprod would
produce a table of the form
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
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.
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,
table SteelSales OUT:
{p in PROD, t in 1..T} -> [PROD, TIME],
Sell[p,t] ~ sold, Sell[p,t]/market[p,t] ~ met;
or with dummy indices added to the key-col-specs:
table SteelSales OUT:
{PROD,1..T} -> [p ~ PROD, t ~ 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.
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.
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
table FoodInput IN "ODBC" "DIET.mdb" "Diet":
FOOD <- [FoodNames], cost, f_min, f_max;
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 FoodOutput "ODBC" "DIET.mdb" "Diet":
[FoodNames], cost IN, f_min IN, Buy OUT, f_max IN;
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.
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
table FoodOutput "ODBC" "DIET.mdb" "Diet": [FoodNames], Buy OUT;
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 INOUT;
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.)
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:
- The key-spec may use the arrow <- to write contents
of the key columns into an AMPL set, -> to write members of
an AMPL set into the key columns, or <-> to do both.
- A data-spec may specify read/write status IN for a
column that will only be read into AMPL, OUT for a column that
will only be written out from AMPL, or INOUT for a column that
will be both read and written.
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.
As an example, the declarations defining FoodInput and
FoodOutput above could be replaced by
table Foods "ODBC" "DIET.mdb" "Diet":
FOOD <- [FoodNames], cost IN, f_min IN, Buy OUT, f_max IN;
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.
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
set-spec <-
[key-col-spec, key-col-spec, ...]
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.
For writing, if the key-spec has one of the forms
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.
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.
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:
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.
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:
table DietSens {j in FOOD} OUT "ODBC" "DietSens.mdb" ("Sens" & j):
[Food], f_min, Buy, f_max;
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:
for {j in FOOD} {
let cost[j] := cost[j] / 2;
solve;
write table DietSens[j];
let cost[j] := cost[j] * 2;
}
For the data in diet2a.dat, the set
FOOD has 8 members, so 8 tables are written in the Access
database:
If instead the table declaration were to give a string expression
for the second string in the string-list, which specifies the Access
filename,
table DietSens {j in FOOD} OUT "ODBC" ("DietSens" & j & ".mdb"):
[Food], f_min, Buy, f_max;
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.)
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:
table DietSens {j in FOOD} "ODBC" "DietSens.mdb":
[Food], Buy ~ ("Buy" & j);
Then running the script shown above will result in the following Access table:
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.
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
{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 
