diff options
Diffstat (limited to 'glpk-5.0/examples/sql')
| -rw-r--r-- | glpk-5.0/examples/sql/README | 5 | ||||
| -rwxr-xr-x | glpk-5.0/examples/sql/mysql_setup.sh | 6 | ||||
| -rw-r--r-- | glpk-5.0/examples/sql/sudoku.sql | 101 | ||||
| -rw-r--r-- | glpk-5.0/examples/sql/sudoku_mysql.mod | 113 | ||||
| -rw-r--r-- | glpk-5.0/examples/sql/sudoku_odbc.mod | 111 | ||||
| -rw-r--r-- | glpk-5.0/examples/sql/transp.sql | 45 | ||||
| -rw-r--r-- | glpk-5.0/examples/sql/transp_mysql.mod | 71 | ||||
| -rw-r--r-- | glpk-5.0/examples/sql/transp_odbc.mod | 72 |
8 files changed, 524 insertions, 0 deletions
diff --git a/glpk-5.0/examples/sql/README b/glpk-5.0/examples/sql/README new file mode 100644 index 0000000..f78ec15 --- /dev/null +++ b/glpk-5.0/examples/sql/README @@ -0,0 +1,5 @@ +This subdirectory contains files which demonstrate using data tables +in MathProg models for MySQL and iODBC. + +Script mysql_setup.sh is used to load the data from the *.sql files to +a MySQL database. Change the username, if necessary. diff --git a/glpk-5.0/examples/sql/mysql_setup.sh b/glpk-5.0/examples/sql/mysql_setup.sh new file mode 100755 index 0000000..1dce8ed --- /dev/null +++ b/glpk-5.0/examples/sql/mysql_setup.sh @@ -0,0 +1,6 @@ +#!/bin/sh +# This file can be used to create database glpk in MySQL. +echo MySQL is called for user root. +mysql -f -u root -p < sudoku.sql +echo MySQL is called for user root. +mysql -f -u root -p < transp.sql diff --git a/glpk-5.0/examples/sql/sudoku.sql b/glpk-5.0/examples/sql/sudoku.sql new file mode 100644 index 0000000..2fe40d7 --- /dev/null +++ b/glpk-5.0/examples/sql/sudoku.sql @@ -0,0 +1,101 @@ +CREATE DATABASE glpk; +CREATE USER glpk@localhost IDENTIFIED BY 'gnu'; +GRANT ALL PRIVILEGES ON glpk.* TO glpk@localhost; +USE glpk; +DROP TABLE sudoku; +CREATE TABLE sudoku ( + ID INT , + COL INT , + LIN INT , + VAL INT , + PRIMARY KEY ( ID, COL, LIN ) + ); +DROP TABLE sudoku_solution; +CREATE TABLE sudoku_solution ( + ID INT , + COL INT , + LIN INT , + VAL INT , + PRIMARY KEY ( ID, COL, LIN ) + ); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 1, 1, 5); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 1, 2, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 1, 3, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 1, 4, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 1, 5, 4); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 1, 6, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 1, 7, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 1, 8, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 1, 9, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 2, 1, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 2, 2, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 2, 3, 3); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 2, 4, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 2, 5, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 2, 6, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 2, 7, 6); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 2, 8, 2); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 2, 9, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 3, 1, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 3, 2, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 3, 3, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 3, 4, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 3, 5, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 3, 6, 9); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 3, 7, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 3, 8, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 3, 9, 4); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 4, 1, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 4, 2, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 4, 3, 6); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 4, 4, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 4, 5, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 4, 6, 7); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 4, 7, 2); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 4, 8, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 4, 9, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 5, 1, 8); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 5, 2, 1); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 5, 3, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 5, 4, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 5, 5, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 5, 6, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 5, 7, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 5, 8, 4); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 5, 9, 3); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 6, 1, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 6, 2, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 6, 3, 9); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 6, 4, 1); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 6, 5, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 6, 6, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 6, 7, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 6, 8, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 6, 9, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 7, 1, 7); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 7, 2, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 7, 3, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 7, 4, 5); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 7, 5, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 7, 6, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 7, 7, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 7, 8, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 7, 9, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 8, 1, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 8, 2, 9); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 8, 3, 2); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 8, 4, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 8, 5, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 8, 6, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 8, 7, 8); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 8, 8, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 8, 9, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 9, 1, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 9, 2, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 9, 3, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 9, 4, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 9, 5, 3); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 9, 6, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 9, 7, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 9, 8, 0); +INSERT INTO sudoku (ID, COL, LIN, VAL) VALUES (1, 9, 9, 6); diff --git a/glpk-5.0/examples/sql/sudoku_mysql.mod b/glpk-5.0/examples/sql/sudoku_mysql.mod new file mode 100644 index 0000000..6e56f2c --- /dev/null +++ b/glpk-5.0/examples/sql/sudoku_mysql.mod @@ -0,0 +1,113 @@ +/* SUDOKU, Number Placement Puzzle */ + +/* Written in GNU MathProg by Andrew Makhorin <mao@mai2.rcnet.ru> */ + +/* This example shows how to use the table statement. + The sudoku to be solves is read from file sudoku_in.csv. + The solution is written to sudoku_out.csv. + The file format is CSV as defined in + RFC 4180 - Common Format and MIME Type for + Comma-Separated Values (CSV) Files */ + +/* Sudoku, also known as Number Place, is a logic-based placement + puzzle. The aim of the canonical puzzle is to enter a numerical + digit from 1 through 9 in each cell of a 9x9 grid made up of 3x3 + subgrids (called "regions"), starting with various digits given in + some cells (the "givens"). Each row, column, and region must contain + only one instance of each numeral. + + Example: + + +-------+-------+-------+ + | 5 3 . | . 7 . | . . . | + | 6 . . | 1 9 5 | . . . | + | . 9 8 | . . . | . 6 . | + +-------+-------+-------+ + | 8 . . | . 6 . | . . 3 | + | 4 . . | 8 . 3 | . . 1 | + | 7 . . | . 2 . | . . 6 | + +-------+-------+-------+ + | . 6 . | . . . | 2 8 . | + | . . . | 4 1 9 | . . 5 | + | . . . | . 8 . | . 7 9 | + +-------+-------+-------+ + + (From Wikipedia, the free encyclopedia.) */ +set fields dimen 2; + +param id; + +param givens{1..9, 1..9}, integer, >= 0, <= 9, default 0; +/* the "givens" */ + +/* +table ti IN 'MySQL' 'Database=glpk;UID=glpk;PWD=gnu' + 'sudoku' : + fields <- [COL, LIN], givens ~ VAL; +*/ +table ti IN 'MySQL' 'Database=glpk;UID=glpk;PWD=gnu' + 'SELECT * FROM sudoku WHERE ID = ' & id : + fields <- [COL, LIN], givens ~ VAL; + +var x{i in 1..9, j in 1..9, k in 1..9}, binary; +/* x[i,j,k] = 1 means cell [i,j] is assigned number k */ + +s.t. fa{i in 1..9, j in 1..9, k in 1..9: givens[i,j] != 0}: + x[i,j,k] = (if givens[i,j] = k then 1 else 0); +/* assign pre-defined numbers using the "givens" */ + +s.t. fb{i in 1..9, j in 1..9}: sum{k in 1..9} x[i,j,k] = 1; +/* each cell must be assigned exactly one number */ + +s.t. fc{i in 1..9, k in 1..9}: sum{j in 1..9} x[i,j,k] = 1; +/* cells in the same row must be assigned distinct numbers */ + +s.t. fd{j in 1..9, k in 1..9}: sum{i in 1..9} x[i,j,k] = 1; +/* cells in the same column must be assigned distinct numbers */ + +s.t. fe{I in 1..9 by 3, J in 1..9 by 3, k in 1..9}: + sum{i in I..I+2, j in J..J+2} x[i,j,k] = 1; +/* cells in the same region must be assigned distinct numbers */ + +/* there is no need for an objective function here */ + +solve; + +table ta{(i,j) in fields} OUT + 'MySQL' 'Database=glpk;UID=glpk;PWD=gnu' + 'DELETE FROM sudoku_solution' + 'WHERE ID = ' & id & ';' + 'INSERT INTO sudoku_solution' + '(ID, COL, LIN, VAL)' + 'VALUES(?, ?, ?, ?);' : + id ~ ID, i ~ COL, j ~ LIN, (sum{k in 1..9} x[i,j,k] * k) ~ VAL; + +printf "\nSudoku to be solved\n"; +for {i in 1..9} +{ for {0..0: i = 1 or i = 4 or i = 7} + printf " +-------+-------+-------+\n"; + for {j in 1..9} + { for {0..0: j = 1 or j = 4 or j = 7} printf(" |"); + printf " %d", givens[i,j]; + for {0..0: j = 9} printf(" |\n"); + } + for {0..0: i = 9} + printf " +-------+-------+-------+\n"; + } +printf "\nSolution\n"; +for {i in 1..9} +{ for {0..0: i = 1 or i = 4 or i = 7} + printf " +-------+-------+-------+\n"; + for {j in 1..9} + { for {0..0: j = 1 or j = 4 or j = 7} printf(" |"); + printf " %d", sum{k in 1..9} x[i,j,k] * k; + for {0..0: j = 9} printf(" |\n"); + } + for {0..0: i = 9} + printf " +-------+-------+-------+\n"; +} + +data; + +param id := 1; +end; diff --git a/glpk-5.0/examples/sql/sudoku_odbc.mod b/glpk-5.0/examples/sql/sudoku_odbc.mod new file mode 100644 index 0000000..9ffa3ab --- /dev/null +++ b/glpk-5.0/examples/sql/sudoku_odbc.mod @@ -0,0 +1,111 @@ +/* SUDOKU, Number Placement Puzzle */ + +/* Written in GNU MathProg by Andrew Makhorin <mao@mai2.rcnet.ru> */ + +/* This example shows how to use the table statement. + The sudoku to be solves is read from file sudoku_in.csv. + The solution is written to sudoku_out.csv. + The file format is CSV as defined in + RFC 4180 - Common Format and MIME Type for + Comma-Separated Values (CSV) Files */ + +/* Sudoku, also known as Number Place, is a logic-based placement + puzzle. The aim of the canonical puzzle is to enter a numerical + digit from 1 through 9 in each cell of a 9x9 grid made up of 3x3 + subgrids (called "regions"), starting with various digits given in + some cells (the "givens"). Each row, column, and region must contain + only one instance of each numeral. + + Example: + + +-------+-------+-------+ + | 5 3 . | . 7 . | . . . | + | 6 . . | 1 9 5 | . . . | + | . 9 8 | . . . | . 6 . | + +-------+-------+-------+ + | 8 . . | . 6 . | . . 3 | + | 4 . . | 8 . 3 | . . 1 | + | 7 . . | . 2 . | . . 6 | + +-------+-------+-------+ + | . 6 . | . . . | 2 8 . | + | . . . | 4 1 9 | . . 5 | + | . . . | . 8 . | . 7 9 | + +-------+-------+-------+ + + (From Wikipedia, the free encyclopedia.) */ +set fields dimen 2; + +param id; + +param givens{1..9, 1..9}, integer, >= 0, <= 9, default 0; +/* the "givens" */ + +table ti IN 'iODBC' + 'DSN=glpk;UID=glpk;PWD=gnu' + 'SELECT * FROM sudoku' + 'WHERE ID = ' & id : + fields <- [COL, LIN], givens ~ VAL; + +var x{i in 1..9, j in 1..9, k in 1..9}, binary; +/* x[i,j,k] = 1 means cell [i,j] is assigned number k */ + +s.t. fa{i in 1..9, j in 1..9, k in 1..9: givens[i,j] != 0}: + x[i,j,k] = (if givens[i,j] = k then 1 else 0); +/* assign pre-defined numbers using the "givens" */ + +s.t. fb{i in 1..9, j in 1..9}: sum{k in 1..9} x[i,j,k] = 1; +/* each cell must be assigned exactly one number */ + +s.t. fc{i in 1..9, k in 1..9}: sum{j in 1..9} x[i,j,k] = 1; +/* cells in the same row must be assigned distinct numbers */ + +s.t. fd{j in 1..9, k in 1..9}: sum{i in 1..9} x[i,j,k] = 1; +/* cells in the same column must be assigned distinct numbers */ + +s.t. fe{I in 1..9 by 3, J in 1..9 by 3, k in 1..9}: + sum{i in I..I+2, j in J..J+2} x[i,j,k] = 1; +/* cells in the same region must be assigned distinct numbers */ + +/* there is no need for an objective function here */ + + +solve; + +table ta {(i, j) in {i1 in 1..9} cross {i2 in 1..9}} OUT + 'iODBC' 'DSN=glpk;UID=glpk;PWD=gnu' + 'DELETE FROM sudoku_solution' + 'WHERE ID = ' & id & ';' + 'INSERT INTO sudoku_solution' + '(ID, COL, LIN, VAL)' + 'VALUES(?, ?, ?, ?);' : + id ~ ID, i ~ COL, j ~ LIN, (sum{k in 1..9} x[i,j,k] * k) ~ VAL; + +printf "\nSudoku to be solved\n"; +for {i in 1..9} +{ for {0..0: i = 1 or i = 4 or i = 7} + printf " +-------+-------+-------+\n"; + for {j in 1..9} + { for {0..0: j = 1 or j = 4 or j = 7} printf(" |"); + printf " %d", givens[i,j]; + for {0..0: j = 9} printf(" |\n"); + } + for {0..0: i = 9} + printf " +-------+-------+-------+\n"; + } +printf "\nSolution\n"; +for {i in 1..9} +{ for {0..0: i = 1 or i = 4 or i = 7} + printf " +-------+-------+-------+\n"; + for {j in 1..9} + { for {0..0: j = 1 or j = 4 or j = 7} printf(" |"); + printf " %d", sum{k in 1..9} x[i,j,k] * k; + for {0..0: j = 9} printf(" |\n"); + } + for {0..0: i = 9} + printf " +-------+-------+-------+\n"; +} + +data; + +param id := 1; +end; diff --git a/glpk-5.0/examples/sql/transp.sql b/glpk-5.0/examples/sql/transp.sql new file mode 100644 index 0000000..8737333 --- /dev/null +++ b/glpk-5.0/examples/sql/transp.sql @@ -0,0 +1,45 @@ +CREATE DATABASE glpk; +CREATE USER glpk@localhost IDENTIFIED BY 'gnu'; +GRANT ALL PRIVILEGES ON glpk.* TO glpk@localhost; +USE glpk; +# production capacity +DROP TABLE transp_capa; +CREATE TABLE transp_capa ( + PLANT TEXT(127), + CAPA REAL, + PRIMARY KEY ( PLANT(127) ) + ); +INSERT INTO transp_capa ( PLANT, CAPA ) VALUES ( 'Seattle', 350 ); +INSERT INTO transp_capa ( PLANT, CAPA ) VALUES ( 'San Diego', 600 ); +# demand +DROP TABLE transp_demand; +CREATE TABLE transp_demand ( + MARKET TEXT(127), + DEMAND REAL, + PRIMARY KEY ( MARKET(127) ) + ); +INSERT INTO transp_demand ( MARKET, DEMAND ) VALUES ( 'New York', 325 ); +INSERT INTO transp_demand ( MARKET, DEMAND ) VALUES ( 'Chicago', 300 ); +INSERT INTO transp_demand ( MARKET, DEMAND ) VALUES ( 'Topeka', 275 ); +# distance +DROP TABLE transp_dist; +CREATE TABLE transp_dist ( + LOC1 TEXT(127), + LOC2 TEXT(127), + DIST REAL, + PRIMARY KEY ( LOC1(127), LOC2(127) ) + ); +INSERT INTO transp_dist ( LOC1, LOC2, DIST ) VALUES ( 'Seattle', 'New York', 2.5 ); +INSERT INTO transp_dist ( LOC1, LOC2, DIST ) VALUES ( 'Seattle', 'Chicago', 1.7 ); +INSERT INTO transp_dist ( LOC1, LOC2, DIST ) VALUES ( 'Seattle', 'Topeka', 1.8 ); +INSERT INTO transp_dist ( LOC1, LOC2, DIST ) VALUES ( 'San Diego', 'New York', 2.5 ); +INSERT INTO transp_dist ( LOC1, LOC2, DIST ) VALUES ( 'San Diego', 'Chicago', 1.8 ); +INSERT INTO transp_dist ( LOC1, LOC2, DIST ) VALUES ( 'San Diego', 'Topeka', 1.4 ); +# result +DROP TABLE transp_result; +CREATE TABLE transp_result ( + LOC1 TEXT(127), + LOC2 TEXT(127), + QUANTITY REAL, + PRIMARY KEY ( LOC1(127), LOC2(127) ) + ); diff --git a/glpk-5.0/examples/sql/transp_mysql.mod b/glpk-5.0/examples/sql/transp_mysql.mod new file mode 100644 index 0000000..f375fa3 --- /dev/null +++ b/glpk-5.0/examples/sql/transp_mysql.mod @@ -0,0 +1,71 @@ +# A TRANSPORTATION PROBLEM +# +# This problem finds a least cost shipping schedule that meets +# requirements at markets and supplies at factories. +# +# References: +# Dantzig G B, "Linear Programming and Extensions." +# Princeton University Press, Princeton, New Jersey, 1963, +# Chapter 3-3. + +set I; +/* canning plants */ + +param a{i in I}; +/* capacity of plant i in cases */ + +table plants IN "MySQL" + 'Database=glpk;UID=glpk;PWD=gnu' + 'SELECT PLANT, CAPA AS CAPACITY FROM transp_capa' : + I <- [ PLANT ], a ~ CAPACITY; + +set J; +/* markets */ + +param b{j in J}; +/* demand at market j in cases */ + +table markets IN "MySQL" + 'Database=glpk;UID=glpk;PWD=gnu' + 'transp_demand' : + J <- [ MARKET ], b ~ DEMAND; + +param d{i in I, j in J}; +/* distance in thousands of miles */ + +table dist IN "MySQL" + 'Database=glpk;UID=glpk;PWD=gnu' + 'transp_dist' : + [ LOC1, LOC2 ], d ~ DIST; + +param f; +/* freight in dollars per case per thousand miles */ + +param c{i in I, j in J} := f * d[i,j] / 1000; +/* transport cost in thousands of dollars per case */ + +var x{i in I, j in J} >= 0; +/* shipment quantities in cases */ + +minimize cost: sum{i in I, j in J} c[i,j] * x[i,j]; +/* total transportation costs in thousands of dollars */ + +s.t. supply{i in I}: sum{j in J} x[i,j] <= a[i]; +/* observe supply limit at plant i */ + +s.t. demand{j in J}: sum{i in I} x[i,j] >= b[j]; +/* satisfy demand at market j */ + +solve; + +table result{i in I, j in J: x[i,j]} OUT "MySQL" + 'Database=glpk;UID=glpk;PWD=gnu' + 'DELETE FROM transp_result;' + 'INSERT INTO transp_result VALUES (?,?,?)' : + i ~ LOC1, j ~ LOC2, x[i,j] ~ QUANTITY; + +data; + +param f := 90; + +end; diff --git a/glpk-5.0/examples/sql/transp_odbc.mod b/glpk-5.0/examples/sql/transp_odbc.mod new file mode 100644 index 0000000..36d807e --- /dev/null +++ b/glpk-5.0/examples/sql/transp_odbc.mod @@ -0,0 +1,72 @@ +# A TRANSPORTATION PROBLEM +# +# This problem finds a least cost shipping schedule that meets +# requirements at markets and supplies at factories. +# +# References: +# Dantzig G B, "Linear Programming and Extensions." +# Princeton University Press, Princeton, New Jersey, 1963, +# Chapter 3-3. + +set I; +/* canning plants */ + +param a{i in I}; +/* capacity of plant i in cases */ + +table plants IN "iODBC" + 'DSN=glpk;UID=glpk;PWD=gnu' + 'SELECT PLANT, CAPA AS CAPACITY' + 'FROM transp_capa' : + I <- [ PLANT ], a ~ CAPACITY; + +set J; +/* markets */ + +param b{j in J}; +/* demand at market j in cases */ + +table markets IN "iODBC" + 'DSN=glpk;UID=glpk;PWD=gnu' + 'transp_demand' : + J <- [ MARKET ], b ~ DEMAND; + +param d{i in I, j in J}; +/* distance in thousands of miles */ + +table dist IN "iODBC" + 'DSN=glpk;UID=glpk;PWD=gnu' + 'transp_dist' : + [ LOC1, LOC2 ], d ~ DIST; + +param f; +/* freight in dollars per case per thousand miles */ + +param c{i in I, j in J} := f * d[i,j] / 1000; +/* transport cost in thousands of dollars per case */ + +var x{i in I, j in J} >= 0; +/* shipment quantities in cases */ + +minimize cost: sum{i in I, j in J} c[i,j] * x[i,j]; +/* total transportation costs in thousands of dollars */ + +s.t. supply{i in I}: sum{j in J} x[i,j] <= a[i]; +/* observe supply limit at plant i */ + +s.t. demand{j in J}: sum{i in I} x[i,j] >= b[j]; +/* satisfy demand at market j */ + +solve; + +table result{i in I, j in J: x[i,j]} OUT "iODBC" + 'DSN=glpk;UID=glpk;PWD=gnu' + 'DELETE FROM transp_result;' + 'INSERT INTO transp_result VALUES (?,?,?)' : + i ~ LOC1, j ~ LOC2, x[i,j] ~ QUANTITY; + +data; + +param f := 90; + +end; |