Description
This program tests the basic sqlitewrite functionality by producing a sqlite database via the sqlitewrite utility and interrogate this database with GAMS Connect agent SQLReader. Contributor: Vaibhavnath Jha, September 2024
Small Model of Type : GAMS
Category : GAMS Test library
Main file : gdxsqlite1.gms
$title 'Test basic functionality of SQLITEWRITE tool' (gdx2sqlite01,SEQ=662)
$onText
This program tests the basic sqlitewrite functionality by producing
a sqlite database via the sqlitewrite utility and interrogate this
database with GAMS Connect agent SQLReader.
Contributor: Vaibhavnath Jha, September 2024
$offText
$call gamslib -q trnsport
$if errorlevel 1 $abort Problems retrieving trnsport from GAMS Model Library
$call gams trnsport.gms gdx=sqlite_test lp=soplex lo=%gams.lo%
$if errorlevel 1 $abort Problems running GAMS model trnsport
$call gamstool sqlitewrite -gdxin=sqlite_test.gdx -o sqlite_test.db > %system.nullfile%
$if errorlevel 1 $abort Problems creating database using sqlitewrite
$onEmbeddedCode Connect:
- SQLReader:
connection: {'database':'sqlite_test.db'}
symbols:
- name: i
query: "select * from i;"
type: set
- name: j
query: "select * from j;"
type: set
- name: c
query: "select * from c;"
- name: d
query: "select j,value from d where i=='seattle';"
- name: supply
query: "select i,lower from supply;"
- name: scalars
query: "select * from scalars;"
- name: scalarvariables
query: "select * from scalarvariables;"
valueColumns: ["level", "marginal", "lower", "upper", "scale"]
- name: scalarequations
query: "select * from scalarequations;"
valueColumns: ["level", "marginal", "lower", "upper", "scale"]
- PythonCode:
code: |
import numpy as np
m = connect.container
expected = {
"i" : [['seattle', ''], ['san-diego', '']],
"j" : [['new-york', ''], ['chicago', ''], ['topeka', '']],
"c" : [['seattle', 'new-york', 0.225],
['seattle', 'chicago', 0.153],
['seattle', 'topeka', 0.162],
['san-diego', 'new-york', 0.225],
['san-diego', 'chicago', 0.162],
['san-diego', 'topeka', 0.126]],
"d" : [['new-york', 2.5],
['chicago', 1.7],
['topeka', 1.8]],
"supply" : [['seattle', -np.inf],
['san-diego', -np.inf]],
"scalars" : [['f', 90.0]],
"scalarvariables" : [['z', 'level', 153.675],
['z', 'marginal', 0.0],
['z', 'lower', -np.inf],
['z', 'upper', np.inf],
['z', 'scale', 1.0]],
"scalarequations" : [['cost', 'level', 0.0],
['cost', 'marginal', 1.0],
['cost', 'lower', 0.0],
['cost', 'upper', 0.0],
['cost', 'scale', 1.0]]
}
for key, values in expected.items():
if values != m[key].records.values.tolist():
raise Exception(f"Unexpected data >{key}<")
$offEmbeddedCode
$onText
The following tests check the complie-time and execution-time
usage of SQLITEWRITE. The tool can export the symbols directly from
GAMS database if the option -gdxin is not specified.
$offText
Set i,j;
Parameter d(i<,j<);
Scalar f;
Variable x(i,j);
Variable z;
equation demand(j), cost;
$gdxIn 'sqlite_test'
$load d,demand,f,x,z,cost
* compile-time tests
$callTool.checkErrorLevel sqlitewrite ids=d,demand,f,x,z,cost o=out.db
$onEmbeddedCode Connect:
- SQLReader:
connection: {'database':'out.db'}
symbols:
- name: d
query: "SELECT * FROM d;"
- name: x
query: "SELECT i,j,level FROM x;"
- name: f
query: "SELECT * FROM scalars;"
- name: demand
query: "SELECT j,level FROM demand;"
- name: scalarequation
query: "SELECT * FROM scalarequations;"
valueColumns: ["level", "lower", "upper","marginal","scale"]
- name: scalarvariable
query: "SELECT name,level,upper FROM scalarvariables;"
valueColumns: ["level","upper"]
- PythonCode:
code: |
import numpy as np
d_expected = [['seattle', 'new-york', 2.5],
['seattle', 'chicago', 1.7],
['seattle', 'topeka', 1.8],
['san-diego', 'new-york', 2.5],
['san-diego', 'chicago', 1.8],
['san-diego', 'topeka', 1.4]]
x_expected = [['seattle', 'new-york', 50.0],
['seattle', 'chicago', 300.0],
['seattle', 'topeka', 0.0],
['san-diego', 'new-york', 275.0],
['san-diego', 'chicago', 0.0],
['san-diego', 'topeka', 275.0]]
f_expected = [['f', 90.0]]
demand_expected = [['new-york', 325.0],
['chicago', 300.0],
['topeka', 275.0]]
scalarequation_expected = [['cost', 'level', 0.0],
['cost', 'marginal', 1.0],
['cost', 'lower', 0.0],
['cost', 'upper', 0.0],
['cost', 'scale', 1.0]]
scalarvariable_expected = [['z', 'level', 153.675],
['z', 'upper', np.inf]]
if d_expected != connect.container["d"].records.values.tolist():
raise Exception("Unexpected data parameter d.")
if x_expected != connect.container["x"].records.values.tolist():
raise Exception("Unexpected data variable x.")
if f_expected != connect.container["f"].records.values.tolist():
raise Exception("Unexpected data scalar f.")
if demand_expected != connect.container["demand"].records.values.tolist():
raise Exception("Unexpected data demand equation.")
if scalarequation_expected != connect.container["scalarequation"].records.values.tolist():
raise Exception("Unexpected data cost equation.")
if scalarvariable_expected != connect.container["scalarvariable"].records.values.tolist():
raise Exception("Unexpected data variable z.")
$offEmbeddedCode
Set ii(i), jj(j);
ii(i) = yes$(ord(i)<= 1);
jj(j) = yes$(ord(j)<= 2);
Alias (i,i2);
Parameter glo(i,i2);
glo(i,i2) = UniformInt(10,20);
Variable y(i,i2);
y.m(i,i2) = 5;
Parameter is(i);
is(i) = UniformInt(1,10);
execute_unload 'out_i2.gdx';
* execution-time tests
executeTool.checkErrorLevel 'sqlitewrite ids=ii,jj,glo,y,is o=out.db append=yes';
embeddedCode Connect:
- SQLReader:
connection: {'database':'out.db'}
symbols:
- name: ii
query: "SELECT * FROM ii;"
type: set
- name: jj
query: "SELECT * FROM jj;"
type: set
- name: glo
query: "SELECT * FROM glo;"
- name: y
query: "SELECT * FROM y;"
- name: is
query: "SELECT * FROM [is];"
- PythonCode:
code: |
expected = {
"ii" : [['seattle', '']],
"jj" : [['new-york',''], ['chicago','']],
"glo" : [['seattle', 'seattle', 11.0],
['seattle', 'san-diego', 19.0],
['san-diego', 'seattle', 16.0],
['san-diego', 'san-diego', 13.0]],
# Test a parameter with an alias in the domain
"y" : [['seattle', 'seattle', '0.0', '5.0', '-inf', 'inf', 1.0],
['seattle', 'san-diego', '0.0', '5.0', '-inf', 'inf', 1.0],
['san-diego', 'seattle', '0.0', '5.0', '-inf', 'inf', 1.0],
['san-diego', 'san-diego', '0.0', '5.0', '-inf', 'inf', 1.0]],
# Test a variable with an alias in the domain
"is" : [['seattle', 3.0], ['san-diego', 3.0]] # Test adding a reserved keyword for SQL
}
m = connect.container
for key, values in expected.items():
if values != m[key].records.values.tolist():
raise Exception(f"Unexpected data >{key}<")
endEmbeddedCode
*Tool should skip Alias
executeTool.checkErrorLevel 'sqlitewrite gdxIn=out_i2.gdx o=out_i2.db';
embeddedCode Python:
import sqlite3 as sql
with sql.connect('out_i2.db') as conn:
cur = conn.cursor()
cur.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='i2';")
if cur.fetchone() != None:
raise Exception("Alias i2 should be skipped. Table >i2< must not exist.")
endEmbeddedCode