Re: [Help-glpk] Database connectivity in MathProg

[Andrew Makhorin]

>> In general case the input table to be read from a database has the
>> following syntax (some elements may be omitted):
>>
>>    table t IN "..." : [ i ~ I, j ~ J, ... ] s <- [k ~ K, ...],
>>       p[...] ~ P, q[...] ~ Q, ... ;
>>
>> where t is a symbolic name of the data table; i, j, k, ... are dummy
>> indices; I, J, K, ... are column names for dummy indices; s is a set;
>> p, q, ... are symbolic names of parameters; P, Q, ... are column names
>> for parameters.

> The use of [ i ~ I, j ~ J, ... ] is unclear to me. k ... are the index
> parameters of set s{k, ....}.

In order to allow dummy indices, which are not to be included in the set.

Probably the following syntax is sufficient:

   table t IN "..." : s <- [ i ~ I, j ~ J, ... ],
      p[...] ~ P, q[...] ~ Q, ... ;

where 's <-' is optional and may be omitted.

Further simplification would be omitting '[...]' with the assumption
that all parameters have the same subscript list '[i,j,...]'. This
would make the input table equivalent to the parameter data block in
the tabbing format used in MathProg (see section 5.3 of the reference).
This does not limit the feature, because, if necessary, one can use
such "raw" data to compute other sets and parameters actually needed.

>>
>> The statement 'read table' is equivalent to the following query:
>>
>>    select I, J, K, ..., P, Q from ...
>>
>> Once a next record has been read from the database, values of I, J, K,
>> ... are assigned to the dummy indices i, j, k, ..., the corresponding
>> n-tuple is added to set s, indexing expressions are evaluated, and
>> values P, Q, ... are assigned to corresponding members of parameters
>> p, q, ... .
>>
>> The output table to be written to a database has the following syntax:
>>
>>    table t { ... } OUT "..." : expr ~ A, expr ~ B, ... ;
>>
>> where { ... } is a domain, expr, ... are arbitrary expressions, and
>> A, B, ... are column names.
>>

> I would rather stick to:

>     table t OUT "..." : { ... } -> [k ~ K], expr ~ A, expr ~ B, ... ;

> The domain, expr, ... may have multiple indices. These indices are needed
> as columns for the SQL table to be written. When creating the SQL table
> they should become key columns.

> { ... } can be a set:   s{k, ...}

A dummy index is a particular case of expression, so the syntax of the
output table I suppose is sufficient.

As to key columns, I think the complete structure of a database,
including output tables, should be specified separately and out of
MathProg. On writing the output table attributes of its column should
not be destroyed, in which case it is sufficient to specify only column
names '~ A', '~ B', ... .

>> The statement 'write table' is equivalent to the following:
>>
>>    drop ...
>>    create ...
>>    insert into ... (A, B, ...) values(...)
>>    insert into ... (A, B, ...) values(...)
>>    . . .
>>
>> Expressions are evaluated over the specified domain and their values
>> are assigned to corresponding columns.

> For the reduced scope to be implemented this is correct.

> AMPL used the drop, create, insert sequence only if the disposition of
> all columns of a write table is OUT. In other cases missing columns are
> added as needed and existing data is only updated but not deleted.

> I guess in SQL multiple lines can be inserted with a single INSERT
> statement. Hence all values should be passed to the C handler routine at
> once.

I am waiting for your comments.


Andrew Makhorin