$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

$onEcho > t.gms
set i /i1*i2/;
set j /i1*i2/;
set k /i1*i2/;

executeTool 'sqlitewrite ids=k o=check_k.db';
$offEcho

$call gams t.gms gdx=out.gdx lo=%gams.lo% > %system.nullfile%

$log test writing a table with same table name
$call gamstool sqlitewrite gdxIn=out.gdx o=check_k.db append=Y > %system.nullfile% 2>&1
$ifE errorLevel=0 $abort 'Table >k< already exist in the database. This should have failed!'

$log test global commit behavior
$onEmbeddedCode Connect:
- SQLReader:
    connection: {'database':'check_k.db'}
    symbols:
      - name: check_j
        query: "SELECT * FROM j;"
$offEmbeddedCode
$if errorFree $abort 'Table >j< should not exist in the database. This should have failed!'
$clearErrors

$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

* Test option fast
glo(i,i2) = UniformInt(30,40)
executeTool.checkErrorLevel 'sqlitewrite ids=glo o=out_fast.db fast=yes';

embeddedCode Connect:
- SQLReader:
    connection: {'database':'out_fast.db'}
    symbols:
      - name: glo
        query: "SELECT * FROM glo;"
- PythonCode:
    code: |
        expected = {
        "glo" : [['seattle', 'seattle', 33.0],
                ['seattle', 'san-diego', 39.0],
                ['san-diego', 'seattle', 30.0],
                ['san-diego', 'san-diego', 35.0]],
                # Test option fast
        }

        m = connect.container

        for key, values in expected.items():
            if values != m[key].records.values.tolist():
                raise Exception(f"Unexpected data >{key}<")
endEmbeddedCode

* Test option small
glo(i,i2) = UniformInt(50,60)
executeTool.checkErrorLevel 'sqlitewrite ids=glo o=out_small.db small=Y';

embeddedCode Connect:
- SQLReader:
    connection: {'database':'out_small.db'}
    symbols:
      - name: glo_table
        query: "SELECT * FROM [glo$];"
      - name: glo_view
        query: "SELECT * FROM [glo];"
- PythonCode:
    code: |
        expected = {
        "glo_table" : [['1', '1', 60.0],
                    ['1', '2', 56.0],
                    ['2', '1', 60.0],
                    ['2', '2', 58.0]],
        "glo_view" : [['seattle', 'seattle', 60.0],
                    ['seattle', 'san-diego', 56.0],
                    ['san-diego', 'seattle', 60.0],
                    ['san-diego', 'san-diego', 58.0]],
                # Test option small
        }

        m = connect.container

        for key, values in expected.items():
            if values != m[key].records.values.tolist():
                raise Exception(f"Unexpected data >{key}<")
endEmbeddedCode