New database support in OPL

Overview

OPL allows reading data from data bases using a .dat file. This is done by means of three different statements:

• XXXConnection conn(CONNECTION_STRING, INITIAL_UPDATE);
• data from XXXRead(conn, SQL_QUERY);
• results to XXXPublish(conn, SQL_UPDATE);

Here XXX is a placeholder for a database connection type, for example JDBC, ODBC, SQLite, MySQL, and so on. OPL will search for and select an appropriate database driver based on this type.

CONNECTION_STRING is a database connection string that is passed to the driver in order to connect to the database. For example:

"jdbc:mysql:database?username=john&password=unknown"

INITIAL_UPDATE is explained in the section Prebuilt CSVConnection.

SQL_QUERY is an SQL query string such as:

"FROM datatable SELECT col1, col3"

OPL will run the query using the specified connection and assign the result to the OPL elements to be filled. Each row in the query result fills one element in the OPL model.

SQL_UPDATE is an SQL update expression such as:

"INSERT INTO resulttable VALUES (?, ?)"

OPL will execute this statement once for each element that is published using this statement.

Here is a short example:

• .mod file:

  
  tuple D {
    string product;  // name of product
    int    stock;    // initial stock of this product
    double profit;   // profit for selling one unit of this product
  };
  {D} Products = ...;
  
  dvar int+ sell[Products];
  maximize sum(p in Products) sell[p];
  subject to {
   // Some constraints that make this
   // an interesting model
  }
  
  tuple R {
    string product;
    int    sell;
  };
  {R} Results = { <p, x[p]> | p in Products };

• .dat file:

  
  JDBCConnection conn("jdbc:sqlite:database.db",
                      "DROP TABLE IF EXISTS results; CREATE TABLE results (Product VARCHAR, Sell INTEGER)");
  Products from JDBCRead(conn, "SELECT ProductName, InitialStock, Profit FROM ProductTable");
  Results to JDBCPublish(conn, "INSERT INTO results VALUES(?, ?)");

• SQLite database file database.db has one table ProductTable:

  
     ProductName | Profit | InitialStock
     ------------+--------+-------------
      Product1   |    100 |     10
      Product2   |    200 |     15

With this data the following will be executed:

1. OPL will load the .dat file and recognize the JDBCConnection statement. It takes the connection string "jdbc:sqlite:database.db" and passes that to the generic Java JDBC driver. That driver will inspect the connection string and as a result will look for a SQLite driver and connect to the database called database.db file using that driver. It will return a handle to that connection to OPL. OPL stores that handle as well as the string:

   "DROP TABLE ...; CREATE TABLE ..."

   Note that the string is stored for later use. It is not executed at this point in time.
2. OPL will execute JDBCRead statement to fill the Products set with data from the database. It will read the records from the query result line by line and create one tuple for each line. As a result, the Products set will have this form:

   { <"Product1", 10, 100>, <"Product2", 15, 200> }

   Note how the order of the queried fields in the statement matches the order in which the fields are defined in the tuple:

   ProductName, InitialStock, Profit

   The names of the columns are not required to match the names of the tuple fields. Only the order of fields must match. This also implies that the order of columns in the database is not required to be the same as the fields in the tuple. Also the table in the database can have more columns than the tuple has fields.
3. Next OPL will build and optimize the mathematical optimization model.
4. After the optimal solution is found, OPL will populate the Results set.
5. Finally, OPL will execute the JDBCPublish operation. Before executing the first XXXPublish operation for a connection, OPL will execute the SQL statement that was passed as the second argument to XXXConnection(). In your example this is the string:

   DROP TABLE IF EXISTS results; CREATE TABLE results (Product VARCHAR, Sell INTEGER)

   That string (re)creates the results table from scratch. After executing this, OPL will publish the results set into the results table using the specified SQL update statement. Here the ? in the publish SQL statement matches the fields in the tuple. The values for ? are assigned in the order of the tuple fields. Again, the name of tuple fields and columns in the table need not match, only the order is important. Assuming the optimal solution was

   sell[Product1] = 5 and Sell[Product2] = 7

   , after executing the JDBCPublish() statement the database will have an additional table called "results" of this form:

   
    Product  | Sell
    ---------+-----`
    Product1 | 5
    Product2 | 7

Some important technical details

An XXXConnection() statement may result in up to two connections being made to the database. As soon as the XXXConnection() statement is executed, OPL creates a read-only connection to the database. This connection is used to process XXXRead() statements. Once all data has been read into the model, this connection is closed. Upon encountering an XXXConnection() statement, OPL also saves the CONNECTION_STRING and INITIAL_UPDATE strings. If during post-processing an XXXPublish() statement is encountered for that connection, then OPL creates a new connection that is also open for writing. This connection is used to process the XXXPublish() statements. Before the first statement is executed, OPL also executes the INITIAL_UPDATE command. Note that if there is no XXXPublish() statement for a connection, then the INITIAL_UPDATE is NOT executed!

Additional information

In the prebuilt database connectors that ship with OPL, all string arguments are preprocessed with the following benefits:

• Environment variables can be referenced in a string via the ${ENVIRONMENT_VARIABLE_NAME} syntax and will have their value interpolated before the string is passed on to the driver.
• Model elements can be referenced in a string via the $(MODEL_ELEMENT_NAME) syntax and will have their value interpolated before the string is passed to the driver.
• Characters can be escaped using %xx, where xx is the byte value for the character (in hexadecimal notation and exactly two characters wide). Note that with updates using %xx, multi-byte characters require multiple escape sequences (one for each byte).

The first two bullet points allow for building dynamic queries.

Prebuilt JDBCConnection

The JDBCConnection type is always supported. The code for it is compiled into the OPL core code. The connection uses generic Java libraries to connect to databases. This implies that OPL must run within a Java Virtual Machine (JVM) or must be able to create a Java Virtual Machine. So, for example, on non-Windows platforms, you must use oplrunjava instead of oplrun if you want to use this connection type.

The connection passes the connection string to the JDBC code. That, in turn, determines the database type from the string and attempts to find an appropriate driver and establish a connection using that driver. This implies that an appropriate driver must be installed on your machine and the respective JAR must be found by the generic JDBC code.

The SQL_QUERY supports usual SQL. When loading tuples, you can also have something like this:

  tuple T {
    int field1;
    string field2;
  }
  {T} tuples = ...;
  tuples from JDBCRead(conn, "SELECT stringcol AS field2, intcol AS field1 FROM dataTable");

That is, you can use the SQL keyword AS to specify how columns in the database should be mapped to tuple fields. If the tuple contains subtuples, then the name in the subtuple is the fully-qualified name in which segments are separated by a dot:

  tuple S {
    int subfield1;
    float subfield2;
  }
  tuple T {
    int field1;
    S sub;
  }
  {T} tuples = ...;
  tuples from JDBCRead(conn, "SELECT col1 AS sub.subfield1, col2 AS sub.subfield2, col3 AS field1");

The AS keyword must be used for either all or none of the tuple queries for a particular connection. If the AS keyword is used, then this must be indicated by setting the named parameter to true in the connection string.

If no AS keyword is specified, then select results are matched left-to-right to the tuple fields in order of their definition.

Since JDBC does not support named parameters, the SQL_UPDATE strings for JDBCConnection cannot have named parameters.

The CONNECTION_STRING argument to JDBCConnection() is a regular JDBC connection string that can have additional URL-encoded arguments. These arguments are stripped before passing on the string to the JDBC code. The supported arguments are:

• username: specifies the username for database connection.
• password: specifies the password for username.
• named: indicates whether tuple fields are queried using the AS keyword. This must be set to true if tuple fields are queried by name. The default value is false.
• maxbatch: gives the maximum number of batches to use for update operations.

If the JDBC code for your database is in a JAR that is not on the default classpath, you need to tell the JVM used by OPL where it can find this code. This is done by passing the argument -classpath /path/to/driver.jar, either by setting environment variable ODMS_JAVA_ARGS or using command line argument -Xjavaargs for oplrun (or oplrunjava on non-Windows).

Prebuilt CSVConnection

The CSVConnection is always available in .dat files since its code is compiled directly into OPL. It uses the IloCsvReader class from Concert. The CONNECTION_STRING for CSVConnection() is the name of the CSV file which can have additional URL-encoded arguments. The supported arguments are:

• firstIsHeader: if true, then it is assumed that the first line of the file is a column header (that is, it is not data but, instead, the column names).
• inputSeparator: a string of characters that are considered record separators on input (the default is ",;\t").
• decimalPoint: the character that is used as the decimal point (the default is dot).
• comment: the character that is used to introduce comments. Lines that start with this character are ignored.
• outputSeparator: string that is used to separate records on output.
• outputOnly: if true, then the file is only opened for output.
• append: if true, then the file is appended on output (the default is to truncate the file at the first CSVPublish() operation).

If the INITIAL_UPDATE string for this connection type is non-empty, then this string is printed along with the first CSVPublish() statement as header for the file.

The SQL_QUERY string for this connection type is a comma-separated list of fields to select from the table. A field selector can either be the 0-based index of the column or the name of the column (if columns have names). If SQL_QUERY is the empty string, then all columns from the file are read. In this case, beware of trailing separators since they will create an empty field at the end of the line.

The SQL_UPDATE string for this connection type is ignored. For each element to be published the connection always writes out all data. For a tuple that means that all fields are printed to the file in order of their definition. In order to be compatible with potential future changes, the SQL_UPDATE string should always be the empty string for this connection.

The example <Install_dir>/opl/examples/foodcsv/foodcsv.dat shows how to use the CSVConnection in OPL.

Prebuilt ODBCConnection

This connection is similar to the JDBCConnection but uses ODBC instead. The code is not compiled directly into OPL but is provided in the opltabODBC shared library. The source code can be found in <Install_dir>/opl/examples/opl_interfaces/libopltabODBC.c.

The connection requires appropriate ODBC libraries and drivers to be installed.

The CONNECTION_STRING is an ODBC connection string that will be passed to the ODBC library after environment variables have been expanded. Such a connection string is a semi-colon separated list of name/value pairs. The following arguments can be provided and will be handled/stripped before the string is passed to the ODBC library:

• named: same as the named parameter for JDBCConnection.

Prebuilt MySQLConnection

This connection directly connects to a MySQL database, without taking a detour through a JDBC or ODBC abstraction layer. The code is not compiled directly into OPL but is provided in the opltabMySQL shared library. The source code can be found in <Install_dir>/opl/examples/opl_interfaces/libopltabMySQL.c and can be used as a starting point for implementing other database connections.

The CONNECTION_STRING is a semi-colon separated list of name/value pairs that specify the database and credentials that are used for connection. The following arguments are recognized:

• hostname: hostname on which the database server runs.
• port: port on which the server listens.
• database: name of the database.
• username: username for database connection.
• password: password for username.
• named: same as named in JDBCConnection and ODBCConnection.

Prebuilt SQLiteConnection

This connection directly connects to a SQLite database, without taking a detour through a JDBC or ODBC abstraction layer. The code is not compiled directly into OPL but is provided in the opltabSQLite shared library. The source code can be found in <Install_dir>/opl/examples/opl_interfaces/libopltabSQLite.c and can be used as a starting point for implementing other database connections.

The CONNECTION_STRING for this connection type is the name of the file that contains the database. Additionally, there can be the following URL-encoded arguments:

• writeonly: the connection will only perform SQLitePublish() operations (no SQLiteRead() operations). Without this, the file will be created unless it exists as soon as the SQLiteConnection() statement executed. With this, it will only be created if there is at least one SQLitePublish() statement for this connection.
• named: same as named in JDBCConnection() and ODBCConnection().

Note that SQLite supports named parameters. So when publishing tuples via SQLitePublish() you can use commands such as:

  data to SQLitePublish(conn, "INSERT INTO results VALUES (?4, ?6)")
  data to SQLitePublish(conn, "INSERT INTO results VALUES (?field1, ?sub.subfield2")

The value after the ? is either the 0-based field index or the fully qualified field name.

Building your own custom database connection type

In order to create your own database connection, you have to create a shared library (also called dll on some platforms) that provides the connection and can be loaded at runtime. As soon as OPL encounters a statement such as:

 MYOWNConnection conn(...)

, OPL looks for a shared library called opltabMYOWN<version>, where <version> is the CPLEX Optimization Studio version without periods. For example on Linux this is a file called libopltabMYOWN2010.so, on Windows it is opltabMYOWN2010.dll, and so on. In that library, OPL looks for a function MYOWNconstruct.

You can find the API for implementing your own table data source handler in <Install_dir>/opl/include/ilopl/data/iloopltabledatasource.h. Note that such a handler does not require being backed up by a database. The only requirement is that the data is presented in a table-like view.

You can see examples of table data handlers in the files <Install_dir>/opl/examples/opl_interfaces/libopltab*.c. You can start from one of these examples and adapt to your needs.

OPL database support examples

In OPL, there are prebuilt database connection types for JDBCConnection, CSVConnection, ODBCConnection, MySQLConnection, and SQLiteConnection. In addition, you can build a custom database connection type. The following examples show how to use this functionality: