GLPK/Table Statement

Table statements can be used to read data from external data sources into model objects, including sets and parameters. Table statements can also be used to write model results back to external data sources. Drivers are currently provided for ODBC databases generally, MySQL databases specifically, CSV files, and dBase files.

Official documentation

The official GLPK documentation file doc/gmpl.pdf (before GLPK 4.45 doc/tables.pdf) contains a full reference for the GMPL tables functionality. See obtaining GLPK. This page only provides pointers and examples.

As of version 4.45 the following information was missing:
A field name consists of alphabetic [A-Z,a-z] and numeric [0-9] characters and is case sensitive. It does not need to be unique [1].

CSV

The format for comma-separated values (CSV) files is described in RFC 4180.

For CSV files, the driver identifier is "CSV". The table OUT statement completely replaces any existing file.

The CSV table driver has the following limitations built in:

• The number of columns must be less or equal 50.
• The length of each value must be less or equal 100 characters.

The following examples show reading and writing of a CSV file:

# test1.mod writes CSV file
set I := {1..300};
set J := {1..20};
set K := {1..100};
param  x{I,J,K} := Uniform01();
table tout {i in I, j in J, k in K} OUT "CSV" "test.csv" :
i, j, k, x[i,j,k];
end;

# test2.mod reads CSV file
set I, dimen 3;
param  x{I};
table tin IN "CSV" "test.csv" :
I <- [i,j,k], x;
printf "Number of values: %d\n", card(I);
printf "Average value: %f\n", (sum{(i,j,k) in I} x[i,j,k]) / card(I);
end;

dBase

For dBase files, the driver identifier is "xBASE". The table OUT statement completely replaces any existing file.

The dBase table driver has the following limitations built in:

• The number of columns must be less or equal 50.
• The length of each value must be less or equal 100 characters.

SQL

For ODBC database connections, the driver identifier is "ODBC". For native MySQL database connections, the driver identifier is "MySQL".

Be aware that table UPDATE and table INSERT statements must be followed by a table OUT statement in order to effect the changes (as described in the execute SQL section).

When using the MySQL table driver, the table statements can make full use of the SQL commands provided by the connected database. The MySQL REPLACE statement, a non-standard extension to SQL, implies the subsequent table OUT statement.

table IN

The table IN statement, in combination with other SQL statements, can be used to select data from a relational database. For instance:

set I, dimen 2;
param st{I};
table t IN
  'ODBC' 'FILEDSN=supply.dsn'
  'SELECT product, period, quantity'
  '  FROM stock' :
  I <- [product, period], st ~ quantity;

Instead of a SELECT statement, the name of a table can be provided. In this case the complete table is selected.

set I, dimen 2;
param st{I};
table t IN
  'ODBC' 'FILEDSN=supply.dsn'
  'stock' :
  I <- [product, period], st ~ quantity;

The SELECT statement (or the table name) can be preceded by additional SQL statements:

set I, dimen 2;
param st{I};
table t IN
  'ODBC' 'FILEDSN=supply.dsn'
  'DROP TABLE IF EXISTS result;'
  'CREATE TABLE result ('
  '  product  TEXT(40),'
  '  period   INTEGER,'
  '  quantity FLOAT,'
  '  PRIMARY KEY ( product(40), period ) );'
  'SELECT product, period, quantity'
  '  FROM stock' :
  I <- [product, period], st ~ quantity;

A SQL statement can be split into multiple strings to overcome the 100 character single line string length limitation in GLPK. In this case, the termination of the SQL statement is signaled by the presence of an unquoted semicolon as the last character of the multi-line call (as shown above).

As of GLPK 4.44, strings are automatically trimmed of trailing space (0x20) chars by the GLPK strtrim() function when using the ODBC and MySQL drivers.

table OUT

The table OUT statement, in combination with other SQL statements, can be used to insert, update, or delete records in a relational database.

The INSERT statement can be used to insert records into a relational database. For example:

table t {p in P, t in T} OUT
  'ODBC' 'FILEDSN=supply.dsn'
  'INSERT INTO stock'
  '  ( product, period, quantity) '
  '  VALUES (?,?,?)' :
  p, t, st[p, t];

If the last SQL statement does not contain question marks as placeholders for the values, a table name is expected. This table name and the column names provided will be used to construct an INSERT statement.

table t {p in P, t in T} OUT
  'ODBC' 'FILEDSN=supply.dsn'
  'stock':
  p ~ product, t ~ period, st[p, t] ~ quantity;

The final SQL statement that is iterated over can be preceded by additional SQL statements:

table t {p in P, t in T} OUT
  'ODBC' 'FILEDSN=supply.dsn'
  'DELETE FROM stock;'
  'INSERT INTO stock'
  '  ( product, period, quantity) '
  '  VALUES (?,?,?)' :
  p, t, st[p, t];

This tactic can be used to avoid duplicate insertions.

It is possible to issue statements other than INSERT. For instance, the UPDATE statement can be used to update records in a relational database. For example:

table t {p in P, t in T} OUT
  'ODBC' 'FILEDSN=supply.dsn'
  'UPDATE stock'
  '  SET quantity = ?'
  '  WHERE'
  '    product = ? AND period = ?;' :
  st[p, t], p, t;

The DELETE statement can be used to delete records from a relational database. For example:

table t {p in P, t in T} OUT
  'ODBC' 'FILEDSN=supply.dsn'
  'DELETE FROM stock'
  '  WHERE'
  '    product = ? AND period = ?;' :
  p, t;

Executing the SQL

At present, SQL support within the GMPL language only allows for SQL commands to be issued within "table IN" and "table OUT" statement blocks.

Within a "table IN" block, the final SELECT statement (or table name) can be preceded by any number of preparatory SQL statements.

Within a "table OUT" block, the final INSERT/DELETE/UPDATE statement, which is iterated over the domain, can be preceded by any number of preparatory SQL statements.

To completely separate the preparatory statement(s) from the database I/O, a dummy output statement can be employed:

table prep {i in 1..1} OUT 'ODBC'
  'DSN=glpk;UID=glpk;PWD=gnu'
  # The preparatory SQL statement(s) follow
  'DELETE FROM stock;'
  # The next SQL statement is needed to satisfy the GMPL syntax.
  'SELECT ?;' : i; 

Table statement bug (fixed GLPK 4.45)

GLPK versions prior to version 4.45 may encounter a memory overrun error when processing table statements. The issue is described in this thread and is now resolved.