Re: [Help-glpk] Database connectivity in MathProg

[xypron]

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;

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.

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.

Best regards

Xypron

Links:
iODBC - http://www.iodbc.org/

Literature:
Ampl: A Modeling Language for Mathematical Programming  
Robert Fourer, David M. Gay, and Brian W. Kernighan
2nd ed., Brooks Cole, 2003


Andrew Makhorin wrote:
> 
>> Back in November, you and Xypron had a brief discussion on his mods to
>> introduce MySQL connectivity into MathProg.  Simple declarative
>> statements, as you'd proposed, would improve its functionality for me. 
> 
>> Can you tell me if that is an issue that is moving forward?  If it's
>> not, what would be required to move this forward?
> 
> To add this feature to the mathprog translator it should be well
> specified on the language as well as on the translator levels.
> I could made necessary changes in the translator, however, at the moment
> I do not have enough time to implement an interface to the database.
> 
> Andrew Makhorin
> 
> 
> 
> _______________________________________________
> Help-glpk mailing list
> address@hidden
> http://lists.gnu.org/mailman/listinfo/help-glpk
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Database-connectivity-in-MathProg-tp15228661p15367211.html
Sent from the Gnu - GLPK - Help mailing list archive at Nabble.com.