Re: [Help-glpk] Database connectivity in MathProg

[Andrew Makhorin]

Xypron,

Thank you very much for your proposal.

> The AMPL table syntax is powerful enough to encompass the foreseeable needs
> of database connectivity. Implementing the full scope would be nice but is
> not necessary to make database connectivity useful. Hence I would suggest to
> use a subset of the AMPL syntax:

> Definitions should be made in the model:

> table TABLENAME;

> Before the the minimize statement read statements can be used to fill sets
> and parameters:

> read table TABLENAME;

In principle, in MathProg this statement is not needed. Any reference
to a set or parameter, which is linked to a table in the data section,
from an expression to be evaluated could automatically perform reading
the corresponding table. Can this statement be omitted in AMPL?

> After the solve statement write statements can be used to write sets and
> parameters to the data base:

> write table TABLENAME;

> In the data section the detailed definition of the tables will be made. Only
> two forms are needed:

> For a table to be read:
> table TABLENAME IN STRING-LIST : KEY-SPEC, DATA-SPEC, DATA-SPEC, ...;

> For a table to be written:
> table TABLENAME OUT STRING-LIST : KEY-SPEC, DATA-SPEC, DATA-SPEC, ...;

> TABLENAME must be the name of a table defined in the model section.

> IN defines that the columns given by DATA-SPEC shall be read.
> OUT defines that the columns given by DATA-SPEC shall be written.
> (support for INOUT defined by AMPL may be added later if needed)

> STRING-LIST can be one or more strings contained in apostrophes, e.g.
> 'iODBC' 'DRIVER=MySQL;DSN=testdb;UID=testuser;PWD=testpw' 'testtable'

> The first string will define the data base connectivity library used. The
> significance of the following strings may depend on the library.

> KEY-SPEC defines which key columns of a table match the dimensions of a set
> that is to be read
> SETNAME <- [key-col-name1, key-col-name2, ...]

> or written
> SETNAME -> [key-col-name1, key-col-name2, ...]

> <- specifies the content of the keys is moved to the set,
> -> specifies the the content of the set is moved to the keys.

> There may be zero or more DATA-SPECs. These define the parameters indexed by
> the set defined in KEY-SPEC that shall be read and if the column name is not
> the same as the name of the parameter a column name can be specified in the
> form
> PARAM ~ COLUMN.

> Hence a complete statement for a table to be read could be:

> table TABLENAM1 IN 'iODBC' 'DRIVER=MySQL;DSN=testdb;UID=testuser;PWD=testpw'
> 'table1' :
> SET1 <- [ key1, key2 ], PAR1 ~ col1;

> For a table to be written the statement could be:

> table TABLENAM2 OUT 'mySQL' 'DSN=testdb;UID=testuser;PWD=testpw' 'table2' :
> SET1 -> [ key1 ], PAR2 ~ col1, PAR3 ~ col2;

> In the case of reading a SELECT statement shall be executed.

Do I correctly understand that the select statement for the example
above would look like:

select key1, key2, col1 from table1;

where key1 and key2 gives a 2-tuple included in SET1 while col1 gives
a value assigned to PAR1[key1,key2]? What if one does not need to use
the corresponding set? It is unclear to me how to specify the parameter
indices in such case.

> In the case of writing a sequence of DROP, CREATE, INSERT shall be executed.

> If agreement on this proposal for the syntax can be reached, the next step
> should be to create a formal language description in the style of the GNU
> MathProg Language Reference.

I need a time to think over this new feature and necessary changes in
the translator. Probably the first step could be implementing tables
in a flat (text or csv) format.


Andrew Makhorin