$title 'Test Connect agent SQLWriter' (CASQLW,SEQ=910)

$onText
This test ensures the correctness of the Connect agent SQLWriter against the following DBMS
1. SQLite
2. Postgres
3. MySQL
4. SQL-Server
5. MS-Access
6. SQLAlchemy

Contributor: Vaibhavnath Jha, March 2023
$offText

$if not setEnv GMSPYTHONLIB $abort.noError Embedded code Python not ready to be used
$log --- Using Python library %sysEnv.GMSPYTHONLIB%

$onEcho > t.gms
Set ij(*,*) /
'new-york'.'seattle',
'chicago'.'san-diego'/;

Set ij_with_text(*,*) /
'new-york'.'seattle' 'east-west',
'chicago'.'san-diego'/;

Set i(*) canning plants /
'seattle',
'san-diego' /;

Set j(*) markets /
'new-york',
'chicago',
'topeka' /;

Parameter a(i) capacity of plant i in cases /
'seattle' 350,
'san-diego' 600 /;

Parameter b(j) demand at market j in cases /
'new-york' 325,
'chicago' 300,
'topeka' 275 /;

Parameter d(i,j) distance in thousands of miles /
'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 /;

Scalar f freight in dollars per case per thousand miles / 90 /;

Parameter c(i,j) transport cost in thousands of dollars per case /
'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 /;

positive Variable x(i,j) shipment quantities in cases /
'seattle'.'new-york'.L 50,
'seattle'.'chicago'.L 300,
'seattle'.'topeka'.M 0.036,
'san-diego'.'new-york'.L 275,
'san-diego'.'chicago'.M 0.009,
'san-diego'.'topeka'.L 275 /;

free     Variable z total transportation costs in thousands of dollars /L 153.675 /;

Equation cost define objective function /M 1, LO 0, UP 0 /;

Equation supply(i) observe supply limit at plant i /
'seattle'.L 350,
'seattle'.M Eps,
'seattle'.LO -Inf,
'seattle'.UP 350,
'san-diego'.L 550,
'san-diego'.LO -Inf,
'san-diego'.UP 600 /;

Equation demand(j) satisfy demand at market j /
'new-york'.L 325,
'new-york'.M 0.225,
'new-york'.LO 325,
'new-york'.UP +Inf,
'chicago'.L 300,
'chicago'.M 0.153,
'chicago'.LO 300,
'chicago'.UP +Inf,
'topeka'.L 275,
'topeka'.M 0.126,
'topeka'.LO 275,
'topeka'.UP +Inf /;

Set bulk /index1*index2/;

alias(bulk,i1,i2,i3,i4);

Parameter pbulk(i1,i2,i3,i4);

pbulk(i1,i2,i3,i4) = uniform(0,1);

Set jnew(*) new markets/
'toronto',
'vancouver'
/;

Set j_with_element_text /j1 'This is j1', j2 'This is j2'/;

$offEmpty
$offEcho
$call.checkErrorLevel gams t.gms lo=%gams.lo% gdx t.gdx

$onEmbeddedCode Python:
import os
import string   
from random import choices
import psycopg2, pymysql, pymssql

schemaName = ''.join(choices(string.ascii_lowercase, k=10))
os.environ['CONNECTSQLSCHEMASUFFIX'] = schemaName
os.environ['CONNECTSQLTABLEPREFIX'] = schemaName + '.'
  
with psycopg2.connect(**{'user':'postgres', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 22000, 'host': 'internal2.gams.com', 'database': 'testdb'}) as conn:
    cur = conn.cursor()
    cur.execute(f"CREATE SCHEMA {schemaName};")
    conn.commit()
    cur.close()
    
with pymysql.connect(**{'user':'root', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 21000, 'host': 'internal2.gams.com', 'database': 'testdb'}) as conn:
    cur = conn.cursor()
    cur.execute(f"CREATE SCHEMA {schemaName};")
    conn.commit()
    cur.close()
   
with pymssql.connect(**{'user':'sa', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 23000, 'host': 'internal2.gams.com'}, autocommit=True) as conn:
    cur = conn.cursor()
    cur.execute(f"CREATE DATABASE {schemaName};")
    conn.commit()
    cur.close()
$offEmbeddedCode

$onEchoV > writeTest.gms
set i / i4, i3, i2, i1 /;
set j / j1*j4 /;
set k / k4, k3, k2, k1 /;
set l / l1*l4 /;
parameter p4(i,j,k,l);
p4(i,j,k,l)$(uniform(0,1)<0.1) = uniform(0,1);

$log test ifExists set to `fail`
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
         - name: j
           newName: j_write_all_tbl 
- SQLWriter:
    connectionType: %CONNECTIONTYPE%
    connection: %CONNECTIONDICT%
    ifExists: "replace"
    schemaName: %SCHEMASUFFIX%
- SQLWriter:
    connectionType: %CONNECTIONTYPE%
    connection: %CONNECTIONDICT%
    symbols: all
    ifExists: "fail"
    schemaName: %SCHEMASUFFIX%
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test invalid option for symbols scope to raise an exception
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
         - name: j
- SQLWriter:
    connectionType: %CONNECTIONTYPE%
    connection: %CONNECTIONDICT%
    schemaName: %SCHEMASUFFIX%
    symbols:
      - name: j
        invalidOption: invalid
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$ifThenE.enclosed_tableName_test sameas('%CONNECTIONTYPE%','postgres')

$log test creating a table with the same name but with enclosed tableName
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
        - name: i
        - name: j
- SQLWriter:
    connection: %CONNECTIONDICT%
    connectionType: postgres
    schemaName: %SCHEMASUFFIX%
    symbols:
      - name: i
        tableName: i_tbl_repeat
      - name: j
        tableName: '"i_tbl_repeat"'
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$elseIfE.enclosed_tableName_test sameas('%CONNECTIONTYPE%','sqlite')

$log test creating a table with the same name but with enclosed tableName, sqlite escChar = []
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
        - name: i
        - name: j
- SQLWriter:
    connection: %CONNECTIONDICT%
    symbols:
      - name: i
        tableName: i_tbl_repeat
      - name: j
        tableName: "[i_tbl_repeat]"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test creating a table with the same name but with enclosed tableName, sqlite escChar = ``
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
        - name: i
        - name: j
- SQLWriter:
    connection: %CONNECTIONDICT%
    symbols:
      - name: i
        tableName: i_tbl_repeat_back_tick
      - name: j
        tableName: "`i_tbl_repeat_back_tick`"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test creating a table with the same name but with enclosed tableName, sqlite escChar = ""
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
        - name: i
        - name: j
- SQLWriter:
    connection: %CONNECTIONDICT%
    symbols:
      - name: i
        tableName: i_tbl_repeat_dbl_quote
      - name: j
        tableName: '"i_tbl_repeat_dbl_quote"'
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$else.enclosed_tableName_test
$log test creating a table with the same name but with enclosed tableName, mysql and sqlserver
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
        - name: i
        - name: j
- SQLWriter:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    schemaName: %SCHEMASUFFIX%
    symbols:
      - name: i
        tableName: i_tbl_repeat
      - name: j
        tableName: "%ENCLOSEL%i_tbl_repeat%ENCLOSER%"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors
$endIf.enclosed_tableName_test

$log test symbols=all(default) & ifExists
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
         - name: j
           newName: j_write_all_tbl 
         - name: d
           newName: d_write_all_tbl
         - name: f
           newName: f_write_all_tbl
- SQLWriter:
    connectionType: %CONNECTIONTYPE%
    connection: %CONNECTIONDICT%
    ifExists: "replace"
    schemaName: %SCHEMASUFFIX%
$offEmbeddedCode

$log test set without explanatory text
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: j_write_all_tbl
        query: "SELECT * FROM %TABLEPREFIX%j_write_all_tbl;"
        type: set
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["j_write_all_tbl"].records.values.tolist()
        expected = [['new-york',''],
                    ['chicago',''],
                    ['topeka','']]
        if data != expected:
            raise Exception("Unexpected data j_write_all_tbl.")
$offEmbeddedCode

$log test 2-dim parameter
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: d_write_all_tbl
        query: "SELECT * FROM %TABLEPREFIX%d_write_all_tbl;"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["d_write_all_tbl"].records.values.tolist()
        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]]
        if data != expected:
            raise Exception("Unexpected data d_write_all_tbl.")
$offEmbeddedCode

$log test scalar 
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: f_write_all_tbl
        query: "SELECT * FROM %TABLEPREFIX%f_write_all_tbl;"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["f_write_all_tbl"].records.values.tolist()
        expected = [[90.0,]]
        if data != expected:
            raise Exception("Unexpected data f_write_all_tbl.")
$offEmbeddedCode

$log adding list of symbols
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
        - name: x
        - name: z
- Projection:
    name: x.all(i,j)
    newName: px(i,j)
- Projection:
    name: x.l(i,j)
    newName: xl(i,j)
- Projection:
    name: z.all
    newName: pz
- SQLWriter:
    connectionType: %CONNECTIONTYPE%
    connection: %CONNECTIONDICT%
    schemaName: %SCHEMASUFFIX%
    symbols:
       - name: px
         tableName: x_tbl
         ifExists: "replace"
         valueSubstitutions: %VALUESUBST%
         unstack: True
       - name: xl
         tableName: xl_tbl
         ifExists: "replace"
         valueSubstitutions: %VALUESUBST%
       - name: pz
         tableName: z_tbl
         ifExists: "replace"
         valueSubstitutions: %VALUESUBST%
$offEmbeddedCode

$log test positive variable x
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: x_tbl
        query: "SELECT i, j, level FROM %TABLEPREFIX%x_tbl;"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["x_tbl"].records.values.tolist()
        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]]
        if data != expected:
            raise Exception("Unexpected data x_tbl.")
$offEmbeddedCode

$log test xl
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: xl_tbl
        query: "SELECT * FROM %TABLEPREFIX%xl_tbl;"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["xl_tbl"].records.values.tolist()
        expected = [['seattle', 'new-york', 50.0],
                    ['seattle', 'chicago', 300],
                    ['seattle', 'topeka', 0.0],
                    ['san-diego', 'new-york', 275.0],
                    ['san-diego', 'chicago', 0.0],
                    ['san-diego', 'topeka', 275.0]]
        if data != expected:
            raise Exception("Unexpected data xl_tbl.")
$offEmbeddedCode

$log test free variable z
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: z_tbl
        query: "SELECT * FROM %TABLEPREFIX%z_tbl;"
        valueColumns: "lastCol"
        valueSubstitutions: %VALUESUBSTREADER%
- PythonCode:
    code: |
        data = connect.container.data["z_tbl"].records.values.tolist()
        expected = [['level', 153.675],
                    ['marginal', 0.0],
                    ['lower', float('-inf')],
                    ['upper', float('inf')],
                    ['scale', 1.0]]
        if data != expected:
            raise Exception("Unexpected data z_tbl.")
$offEmbeddedCode

$log test 1-dim parameter
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
      - name: b
- SQLWriter:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: b
        tableName: b_tbl
        ifExists: "replace"
        unstack: True
        schemaName: %SCHEMASUFFIX%
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: b_tbl
        query: "SELECT * FROM %TABLEPREFIX%b_tbl;"
        valueColumns: ["new-york", "chicago", "topeka"]
- PythonCode:
    code: |
        data = connect.container.data["b_tbl"].records.values.tolist()
        expected = [['new-york', 325.0],
                    ['chicago', 300.0],
                    ['topeka', 275.0]]
        if data != expected:
            raise Exception("Unexpected data d_tbl.")
$offEmbeddedCode

$log test 1-dim set
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
      - name: j
- SQLWriter:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: j
        tableName: j_tbl_unstack
        ifExists: "replace"
        unstack: True
        schemaName: %SCHEMASUFFIX%
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: j_tbl_unstack
        query: "SELECT * FROM %TABLEPREFIX%j_tbl_unstack;"
        type: set
        valueColumns: ["new-york", "chicago", "topeka"]
- PythonCode:
    code: |
        data = connect.container.data["j_tbl_unstack"].records.values.tolist()
        expected = [['new-york', 'Y'],
                    ['chicago', 'Y'],
                    ['topeka', 'Y']]
        if data != expected:
            raise Exception("Unexpected data j_tbl_unstack.")
$offEmbeddedCode

$log test 2-dim set with global unstack
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
      - name: ij
- SQLWriter:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    unstack: True
    symbols:
      - name: ij
        tableName: ij_tbl
        ifExists: "replace"
        schemaName: %SCHEMASUFFIX%
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: ij_tbl
        query: "SELECT * FROM %TABLEPREFIX%ij_tbl;"
        type: set
        valueColumns: ["seattle", "san-diego"]
- PythonCode:
    code: |
        data = connect.container.data["ij_tbl"].records.values.tolist()
        expected = [['new-york', 'seattle', 'Y'],
                    ['chicago', 'san-diego', 'Y']]
        if data != expected:
            raise Exception("Unexpected data ij_tbl.")
$offEmbeddedCode

$log test 1-dim set written with SQLAlchemy with global unstack
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
      - name: bulk
- SQLWriter:
    connection: %CONNECTIONDICTSQLALCHEMY%
    connectionType: sqlalchemy
    unstack: True
    symbols:
      - name: bulk
        tableName: sqlalchemy_tbl
        ifExists: "replace"
        schemaName: %SCHEMASUFFIX%
- SQLReader:
    connection: %CONNECTIONDICTSQLALCHEMY%
    connectionType: sqlalchemy
    symbols:
      - name: sqlalchemy_tbl
        query: "SELECT * FROM %TABLEPREFIX%sqlalchemy_tbl;"
        type: set
        valueColumns: ["index1", "index2"]
- PythonCode:
    code: |
        data = connect.container.data["sqlalchemy_tbl"].records.values.tolist()
        expected = [['index1', 'Y'],
                    ['index2', 'Y']]
        if data != expected:
            raise Exception("Unexpected data sqlalchemy_tbl.")
$offEmbeddedCode

$log test SQLAlchemy with symbols=all(default)
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
      - name: demand
- Projection:
    name: demand.l(j)
    newName: pdemand(j)
- SQLWriter:
    connection: %CONNECTIONDICTSQLALCHEMY%
    connectionType: sqlalchemy
    ifExists: "replace"
    schemaName: %SCHEMASUFFIX%
- SQLReader:
    connection: %CONNECTIONDICTSQLALCHEMY%
    connectionType: sqlalchemy
    symbols:
      - name: pdemand_tbl
        query: "SELECT * FROM %TABLEPREFIX%pdemand;"
- PythonCode:
    code: |
        data = connect.container.data["pdemand_tbl"].records.values.tolist()
        expected = [['new-york', 325.0], 
                    ['chicago', 300.0], 
                    ['topeka', 275.0]]
        if data != expected:
            raise Exception("Unexpected data pdemand_tbl.")
$offEmbeddedCode

$log test appending to existing table
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
         - name: jnew
           newName: j_write_all_tbl
- SQLWriter:
    connectionType: %CONNECTIONTYPE%
    connection: %CONNECTIONDICT%
    symbols: all
    ifExists: "append"
    schemaName: %SCHEMASUFFIX%
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: j_append_data
        query: "SELECT * FROM %TABLEPREFIX%j_write_all_tbl;"
        type: set
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["j_append_data"].records.values.tolist()
        expected = [['new-york',''],
                    ['chicago',''],
                    ['topeka',''],
                    ['toronto',''],
                    ['vancouver','']]
        if data != expected:
            raise Exception("Unexpected data j_append_data.")
$offEmbeddedCode

$log test skipText
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
         - name: j_with_element_text
- SQLWriter:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    ifExists: replace
    schemaName: %SCHEMASUFFIX%
    symbols:
        - name: j_with_element_text
          tableName: j_without_text
          skipText: True
        - name: j_with_element_text
          tableName: j_with_text
          skipText: False
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
        - name: j1_without_text
          query: "SELECT * FROM %TABLEPREFIX%j_without_text;"
          type: set
        - name: j2_with_text
          query: "SELECT * FROM %TABLEPREFIX%j_with_text;"
          type: set
          valueColumns: lastCol
- PythonCode:
    code: |
        data1 = connect.container["j1_without_text"].records.values.tolist()
        expected1 = [['j1', ''], ['j2', '']]
        data2 = connect.container["j2_with_text"].records.values.tolist()
        expected2 = [['j1', 'This is j1'], ['j2', 'This is j2']]
        if data1 != expected1:
            raise Exception("Unexpected data j_without_text")
        if data2 != expected2:
            raise Exception("Unexpected data j_with_text")
$offEmbeddedCode

$log test skipText together with unstack
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
        - name: ij_with_text
- SQLWriter:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    schemaName: %SCHEMASUFFIX%
    ifExists: replace
    unstack: True
    symbols:
        - name: ij_with_text
          tableName: ij_with_unstack
        - name: ij_with_text
          tableName: ij_with_unstack_skipEleText
          skipText: True
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
        - name: read_ij_with_unstack
          query: "SELECT * FROM %TABLEPREFIX%ij_with_unstack;"
          type: set
          valueColumns: ["seattle", "san-diego"]
        - name: read_ij_with_unstack_skipEleText
          query: "SELECT * FROM %TABLEPREFIX%ij_with_unstack_skipEleText;"
          type: set
          valueColumns: ["seattle", "san-diego"]
- PythonCode:
    code: |
        data1 = connect.container["read_ij_with_unstack"].records.values.tolist()
        data2 = connect.container["read_ij_with_unstack_skipEleText"].records.values.tolist()
        expected1 = [['new-york', 'seattle', 'east-west'], ['chicago', 'san-diego', 'Y']]
        expected2 = [['new-york', 'seattle', 'Y'], ['chicago', 'san-diego', 'Y']]

        if data1 != expected1:
            raise Exception("Unexpected data in read_ij_with_unstack")
            
        if data2 != expected2:
            raise Exception("Unexpected data in read_ij_with_unstack_skipEleText")
$offEmbeddedCode

$log test writing empty symbols to databases
$onEmbeddedCode Connect:
- PythonCode:
    code: |
      connect.container.addSet("k", domain=["*"])
      connect.container.addParameter("p", domain=["*"])
- SQLWriter:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    schemaName: %SCHEMASUFFIX%
    symbols: all
    ifExists: "replace"
$offEmbeddedCode

$log test retrieving empty tables created by empty symbols
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: k
        query: "SELECT * FROM %TABLEPREFIX%k"
        type: set
        valueColumns: lastCol
      - name: p
        query: "SELECT * FROM %TABLEPREFIX%p"
        type: par
- PythonCode:
    code: |
      data_k = connect.container["k"].records
      cols_k = [0, 'element_text']
      data_p = connect.container["p"].records
      cols_p = [0, 'value']

      if data_k is None or not data_k.empty:
        raise Exception("Expected set >k< to have empty records.")
    
      if data_p is None or not data_p.empty:
        raise Exception("Expected parameter >p< to have empty records.")
        
      if (data_k.columns != cols_k).any():
        raise Exception("Unexpected columns for set >k<.")
        
      if (data_p.columns != cols_p).any():
        raise Exception("Unexpected columns for parameter >p<.")
$offEmbeddedCode


* test for correct categoricals
EmbeddedCode Connect:
- GAMSReader:
    symbols:
      - name: p4
- SQLWriter:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    schemaName: %SCHEMASUFFIX%
    symbols:
      - name: p4
        tableName: p4Table
        ifExists: "replace"
        unstack: True
endEmbeddedCode
EmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: p4
        query: "SELECT * FROM %TABLEPREFIX%p4Table;"
        valueColumns: ["l1","l2","l3","l4"]
- PythonCode:
    code: |
      # we expect the original UEL order: l1, l2, l3, l4
      expected = [['i4', 'j1', 'k2', 'l1', 0.500210669],
                  ['i4', 'j3', 'k2', 'l3', 0.33855027200000004],
                  ['i4', 'j4', 'k2', 'l1', 0.175661049],
                  ['i4', 'j4', 'k3', 'l1', 0.102514669],
                  ['i4', 'j4', 'k3', 'l4', 0.792360642],
                  ['i4', 'j4', 'k1', 'l1', 0.5851311730000001],
                  ['i3', 'j1', 'k2', 'l2', 0.202015557],
                  ['i3', 'j1', 'k2', 'l3', 0.269613052],
                  ['i3', 'j3', 'k1', 'l1', 0.40134625700000004],
                  ['i3', 'j4', 'k3', 'l1', 0.7831020040000001],
                  ['i3', 'j2', 'k2', 'l3', 0.576299805],
                  ['i3', 'j2', 'k2', 'l4', 0.006008368],
                  ['i2', 'j1', 'k2', 'l4', 0.15034771600000002],
                  ['i2', 'j3', 'k2', 'l1', 0.196093864],
                  ['i2', 'j3', 'k3', 'l1', 0.177822574],
                  ['i2', 'j3', 'k3', 'l2', 0.016643898],
                  ['i2', 'j4', 'k3', 'l4', 0.948836169],
                  ['i2', 'j2', 'k3', 'l3', 0.187448731],
                  ['i2', 'j2', 'k3', 'l4', 0.540400638],
                  ['i2', 'j2', 'k1', 'l4', 0.543870155],
                  ['i1', 'j1', 'k3', 'l1', 0.36863057200000005],
                  ['i1', 'j1', 'k3', 'l4', 0.84181978],
                  ['i1', 'j1', 'k1', 'l1', 0.7737034340000001],
                  ['i1', 'j3', 'k2', 'l2', 0.32300194000000004],
                  ['i1', 'j3', 'k4', 'l4', 0.263857554],
                  ['i1', 'j4', 'k1', 'l3', 0.37419850000000004],
                  ['i1', 'j4', 'k4', 'l4', 0.41459935800000003],
                  ['i1', 'j2', 'k2', 'l4', 0.485176103],
                  ['i1', 'j2', 'k1', 'l1', 0.698580858]]
      sym = connect.container.data["p4"]
      data_sorted = sym.records.sort_values(sym.records.columns[:-1].tolist()).values.tolist()
      if data_sorted != expected:
        raise Exception("Unexpected sorted data p4")
- SQLWriter:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    schemaName: %SCHEMASUFFIX%
    symbols:
      - name: p4
        tableName: p4Table_ref
        ifExists: "replace"
        unstack: True
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: p4_ref
        query: "SELECT * FROM %TABLEPREFIX%p4Table_ref;"
        valueColumns: ["l1","l2","l3","l4"] 
- PythonCode:
    code: |
      # we expect the original UEL order: l1, l2, l3, l4
      expected = [['i4', 'j1', 'k2', 'l1', 0.500210669],
                  ['i4', 'j3', 'k2', 'l3', 0.33855027200000004],
                  ['i4', 'j4', 'k2', 'l1', 0.175661049],
                  ['i4', 'j4', 'k3', 'l1', 0.102514669],
                  ['i4', 'j4', 'k3', 'l4', 0.792360642],
                  ['i4', 'j4', 'k1', 'l1', 0.5851311730000001],
                  ['i3', 'j1', 'k2', 'l2', 0.202015557],
                  ['i3', 'j1', 'k2', 'l3', 0.269613052],
                  ['i3', 'j3', 'k1', 'l1', 0.40134625700000004],
                  ['i3', 'j4', 'k3', 'l1', 0.7831020040000001],
                  ['i3', 'j2', 'k2', 'l3', 0.576299805],
                  ['i3', 'j2', 'k2', 'l4', 0.006008368],
                  ['i2', 'j1', 'k2', 'l4', 0.15034771600000002],
                  ['i2', 'j3', 'k2', 'l1', 0.196093864],
                  ['i2', 'j3', 'k3', 'l1', 0.177822574],
                  ['i2', 'j3', 'k3', 'l2', 0.016643898],
                  ['i2', 'j4', 'k3', 'l4', 0.948836169],
                  ['i2', 'j2', 'k3', 'l3', 0.187448731],
                  ['i2', 'j2', 'k3', 'l4', 0.540400638],
                  ['i2', 'j2', 'k1', 'l4', 0.543870155],
                  ['i1', 'j1', 'k3', 'l1', 0.36863057200000005],
                  ['i1', 'j1', 'k3', 'l4', 0.84181978],
                  ['i1', 'j1', 'k1', 'l1', 0.7737034340000001],
                  ['i1', 'j3', 'k2', 'l2', 0.32300194000000004],
                  ['i1', 'j3', 'k4', 'l4', 0.263857554],
                  ['i1', 'j4', 'k1', 'l3', 0.37419850000000004],
                  ['i1', 'j4', 'k4', 'l4', 0.41459935800000003],
                  ['i1', 'j2', 'k2', 'l4', 0.485176103],
                  ['i1', 'j2', 'k1', 'l1', 0.698580858]]
      sym = connect.container.data["p4_ref"]
      data_sorted = sym.records.sort_values(sym.records.columns[:-1].tolist()).values.tolist()
      if data_sorted != expected:
        raise Exception("Unexpected sorted data p4_ref")
endEmbeddedCode

$log test value substitutions for special values (default, depending on DATABASE)
$onEmbeddedCode Connect:
- PythonCode:
    code: |
      import pandas as pd
      data = [
        ['i1', "EPS"],
        ['i2', "-INF"],
        ['i3', "INF"],
        ['i4', "UNDEF"],
        ['i5', "NA"],
        ['i6', 0],
        ['i7', 1]
      ]
      df = pd.DataFrame(data)
      connect.container.addParameter('p_sv', ['*'], records=df)
- SQLWriter:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    schemaName: %SCHEMASUFFIX%
    ifExists: replace
    symbols: all
    valueSubstitutions: %VALUESUB_WRITE% 
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    valueSubstitutions: %VALUESUB_READ%
    symbols:
      - name: p_new                                                                                       
        query: "SELECT * FROM %TABLEPREFIX%p_sv;"
- PythonCode:
    code: |
      import numpy as np
      import gams.transfer as gt
      data_p = connect.container["p_sv"].records.values.tolist()
      data_p_new = connect.container["p_new"].records.values.tolist()
      if not np.array_equal(data_p, data_p_new):
        raise Exception("Unexpected data.")
      if not gt.SpecialValues.isEps(data_p[0][-1]) or not gt.SpecialValues.isEps(data_p_new[0][-1]):
        raise Exception("Unexpected data EPS.")
      if not gt.SpecialValues.isNegInf(data_p[1][-1]) or not gt.SpecialValues.isNegInf(data_p_new[1][-1]):
        raise Exception("Unexpected data -INF.")
      if not gt.SpecialValues.isPosInf(data_p[2][-1]) or not gt.SpecialValues.isPosInf(data_p_new[2][-1]):
        raise Exception("Unexpected data INF.")
      if not gt.SpecialValues.isUndef(data_p[3][-1]) or not gt.SpecialValues.isUndef(data_p_new[3][-1]):
        raise Exception("Unexpected data UNDEF.")
      if not gt.SpecialValues.isNA(data_p[4][-1]) or not gt.SpecialValues.isNA(data_p_new[4][-1]):
        raise Exception("Unexpected data NA.")
      if data_p[5][-1] != 0 or data_p_new[5][-1] != 0:
        raise Exception("Unexpected data 0.")
      if data_p[6][-1] != 1 or data_p_new[6][-1] != 1:
        raise Exception("Unexpected data 1.")
$offEmbeddedCode

$log test value substitutions for special values
$onEmbeddedCode Connect:
- PythonCode:
    code: |
      import pandas as pd
      data = [
        ['i1', "EPS"],
        ['i2', "-INF"],
        ['i3', "INF"],
        ['i4', "UNDEF"],
        ['i5', "NA"],
        ['i6', 0],
        ['i7', 1]
      ]
      df = pd.DataFrame(data)
      connect.container.addParameter('p_sv_1', ['*'], records=df)
- SQLWriter:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    schemaName: %SCHEMASUFFIX%
    ifExists: replace
    valueSubstitutions: {
      "EPS": 0,
      "-INF": 1,
      "INF": 2,
      "UNDEF": 3,
      "NA": 4
    }
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: p_sv_new
        query: "SELECT * FROM %TABLEPREFIX%p_sv_1;"
- PythonCode:
    code: |
      expected = [
        ['i1', 0.],
        ['i2', 1.],
        ['i3', 2.],
        ['i4', 3.],
        ['i5', 4.],
        ['i6', 0.],
        ['i7', 1.]
      ]

      data_p_new = connect.container["p_sv_new"].records.values.tolist()
      if expected != data_p_new:
        raise Exception("Unexpected data")
$offEmbeddedCode

$log test value substitutions for special values not being transitive
$onEmbeddedCode Connect:
- PythonCode:
    code: |
      import pandas as pd
      data = [
        ['i1', "EPS"],
        ['i2', "-INF"],
        ['i3', "INF"],
        ['i4', "UNDEF"],
        ['i5', "NA"],
        ['i6', 0],
        ['i7', 1]
      ]
      df = pd.DataFrame(data)
      connect.container.addParameter('p_sv_2', ['*'], records=df)
- SQLWriter:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    schemaName: %SCHEMASUFFIX%
    ifExists: replace
    valueSubstitutions: {
      "EPS": .nan,  # gets dropped - .nan is gt.SpecialValues.Undef and should not be replaced by the UNDEF:1 value substitution
      "-INF": 0,
      "INF": .nan,  # gets dropped
      "UNDEF": 1,
      "NA": 2
    }
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: p_sv_new
        query: "SELECT * FROM %TABLEPREFIX%p_sv_2;"
- PythonCode:
    code: |
      expected = [
        ['i2', 0.],
        ['i4', 1.],
        ['i5', 2.],
        ['i6', 0.],
        ['i7', 1.]
      ]
      data_p_new = connect.container["p_sv_new"].records.values.tolist()
      if expected != data_p_new:
        raise Exception("Unexpected data")
$offEmbeddedCode

$ifThenE sameas('%CONNECTIONTYPE%','mysql')or(sameas('%CONNECTIONTYPE%','postgres'))
* Add bulkInsert tests for select DBMS

$log test 4-dim parameter written with bulkInsert & unstack: True
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
      - name: pbulk
        newName: pbulk_tbl_unstk
- SQLWriter:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    insertMethod: bulkInsert
    unstack: True
    ifExists: replace
    schemaName: %SCHEMASUFFIX%
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: pbulk_unstack_tbl
        query: "SELECT * FROM %TABLEPREFIX%pbulk_tbl_unstk;"
        valueColumns: ["index1", "index2"]
- PythonCode:
    code: |
        data = connect.container.data["pbulk_unstack_tbl"].records.values.tolist()
        expected = [['index1', 'index1', 'index1', 'index1', 0.17174713200000002],
                    ['index1', 'index1', 'index1', 'index2', 0.843266708],
                    ['index1', 'index1', 'index2', 'index1', 0.550375356],
                    ['index1', 'index1', 'index2', 'index2', 0.301137904],
                    ['index1', 'index2', 'index1', 'index1', 0.292212117],
                    ['index1', 'index2', 'index1', 'index2', 0.22405286700000002],
                    ['index1', 'index2', 'index2', 'index1', 0.34983050400000004],
                    ['index1', 'index2', 'index2', 'index2', 0.856270347],
                    ['index2', 'index1', 'index1', 'index1', 0.067113723],
                    ['index2', 'index1', 'index1', 'index2', 0.500210669],
                    ['index2', 'index1', 'index2', 'index1', 0.9981176270000001],
                    ['index2', 'index1', 'index2', 'index2', 0.578733378],
                    ['index2', 'index2', 'index1', 'index1', 0.991133039],
                    ['index2', 'index2', 'index1', 'index2', 0.7622504670000001],
                    ['index2', 'index2', 'index2', 'index1', 0.130692483],
                    ['index2', 'index2', 'index2', 'index2', 0.6397187590000001]]
        if data != expected:
            raise Exception("Unexpected data pbulk_unstack_tbl.")
$offEmbeddedCode

$log test 4-dim parameter written with bulkInsert
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
      - name: pbulk
- SQLWriter:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    insertMethod: bulkInsert
    schemaName: %SCHEMASUFFIX%
    symbols:
      - name: pbulk
        tableName: pbulk_tbl
        ifExists: "replace"
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: pbulk_tbl
        query: "SELECT * FROM %TABLEPREFIX%pbulk_tbl;"
- PythonCode:
    code: |
        data = connect.container.data["pbulk_tbl"].records.values.tolist()
        expected = [['index1', 'index1', 'index1', 'index1', 0.17174713200000002],
                    ['index1', 'index1', 'index1', 'index2', 0.843266708],
                    ['index1', 'index1', 'index2', 'index1', 0.550375356],
                    ['index1', 'index1', 'index2', 'index2', 0.301137904],
                    ['index1', 'index2', 'index1', 'index1', 0.292212117],
                    ['index1', 'index2', 'index1', 'index2', 0.22405286700000002],
                    ['index1', 'index2', 'index2', 'index1', 0.34983050400000004],
                    ['index1', 'index2', 'index2', 'index2', 0.856270347],
                    ['index2', 'index1', 'index1', 'index1', 0.067113723],
                    ['index2', 'index1', 'index1', 'index2', 0.500210669],
                    ['index2', 'index1', 'index2', 'index1', 0.9981176270000001],
                    ['index2', 'index1', 'index2', 'index2', 0.578733378],
                    ['index2', 'index2', 'index1', 'index1', 0.991133039],
                    ['index2', 'index2', 'index1', 'index2', 0.7622504670000001],
                    ['index2', 'index2', 'index2', 'index1', 0.130692483],
                    ['index2', 'index2', 'index2', 'index2', 0.6397187590000001]]
        if data != expected:
            raise Exception("Unexpected data pbulk_tbl.")
$offEmbeddedCode

$log test 2-dim set with unstack and NULL values using bulkInsert
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
      - name: ij
- SQLWriter:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    schemaName: %SCHEMASUFFIX%
    symbols:
      - name: ij
        tableName: ij_tbl
        ifExists: "replace"
        unstack: True
        insertMethod: bulkInsert
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: ij_tbl
        query: "SELECT * FROM %TABLEPREFIX%ij_tbl;"
        type: set
        valueColumns: ["seattle", "san-diego"]
- PythonCode:
    code: |
        data = connect.container.data["ij_tbl"].records.values.tolist()
        expected = [['new-york', 'seattle', 'Y'],
                    ['chicago', 'san-diego', 'Y']]
        if data != expected:
            raise Exception("Unexpected data ij_tbl.")
$offEmbeddedCode

$endIf
$ifThenE.bcp sameas('%CONNECTIONTYPE%','sqlserver')and(sameas('%HAVEBCP%',1))
$log adding symbol thru insertMethod: bcp
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
      - name: pbulk
        newName: pbulk_tbl_bcp
- SQLWriter:
    connection: %CONNECTIONDICT%
    connectionType: sqlserver
    insertMethod: bcp
    ifExists: replace
    schemaName: %SCHEMASUFFIX%
$offEmbeddedCode

$log test 4-dim parameter written with insertMethod: bcp
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: sqlserver
    symbols:
      - name: pbulk_tbl_bcp
        query: "SELECT * FROM %TABLEPREFIX%pbulk_tbl_bcp;"
- PythonCode:
    code: |
        data = connect.container.data["pbulk_tbl_bcp"].records.values.tolist()
        expected = [['index1', 'index1', 'index1', 'index1', 0.17174713200000002],
                    ['index1', 'index1', 'index1', 'index2', 0.843266708],
                    ['index1', 'index1', 'index2', 'index1', 0.550375356],
                    ['index1', 'index1', 'index2', 'index2', 0.301137904],
                    ['index1', 'index2', 'index1', 'index1', 0.292212117],
                    ['index1', 'index2', 'index1', 'index2', 0.22405286700000002],
                    ['index1', 'index2', 'index2', 'index1', 0.34983050400000004],
                    ['index1', 'index2', 'index2', 'index2', 0.856270347],
                    ['index2', 'index1', 'index1', 'index1', 0.067113723],
                    ['index2', 'index1', 'index1', 'index2', 0.500210669],
                    ['index2', 'index1', 'index2', 'index1', 0.9981176270000001],
                    ['index2', 'index1', 'index2', 'index2', 0.578733378],
                    ['index2', 'index2', 'index1', 'index1', 0.991133039],
                    ['index2', 'index2', 'index1', 'index2', 0.7622504670000001],
                    ['index2', 'index2', 'index2', 'index1', 0.130692483],
                    ['index2', 'index2', 'index2', 'index2', 0.6397187590000001]]
        if data != expected:
            raise Exception("Unexpected data pbulk_tbl_bcp.")
$offEmbeddedCode
$elseIfE.bcp sameas('%CONNECTIONTYPE%','sqlserver')and(sameas('%HAVEBCP%',0))
$log bcp utility not found. Skipping bcp tests.
$endIf.bcp

$ifThenE.sqlite_specific_tests sameas('%CONNECTIONTYPE%','sqlite')
$log test option skipText with SQLITE
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
         - name: j
           newName: j_skip_ele_text
- SQLWriter:
    connection: %CONNECTIONDICT%
    symbols: all
    ifExists: "replace"
    skipText: True
$offEmbeddedCode

$log test set without explanatory text
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    symbols:
      - name: j_skip_element_text
        query: "SELECT count() as num_of_columns FROM PRAGMA_TABLE_INFO('j_skip_ele_text');"
- PythonCode:
    code: |
        data = connect.container.data["j_skip_element_text"].records.values.tolist()
        expected = [[1.0]]
        if data != expected:
            raise Exception("Unexpected data j_skip_element_text.")
$offEmbeddedCode

$log test option fast
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
      - name: a
- SQLWriter:
    connection: {'database': '%sysEnv.CONNECTSQLSCHEMASUFFIX%.db'}
    fast: True
    symbols: all
$offEmbeddedCode

$onEmbeddedCode Connect:
- SQLReader:
    connection: {'database': '%sysEnv.CONNECTSQLSCHEMASUFFIX%.db'}
    symbols:
      - name: a_fast_table
        query: "SELECT * FROM a;"
- PythonCode:
    code: |
        data = connect.container.data["a_fast_table"].records.values.tolist()
        expected = [['seattle', 350.0],
                    ['san-diego',600.0]]
        if data != expected:
            raise Exception("Unexpected data a_fast_table.")
$offEmbeddedCode

$log test option fast and small together
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
      - name: a
- SQLWriter:
    connection: {'database': '%sysEnv.CONNECTSQLSCHEMASUFFIX%_fast_small.db'}
    fast: True
    small: True
    symbols: all
$offEmbeddedCode

$onEmbeddedCode Connect:
- SQLReader:
    connection: {'database': '%sysEnv.CONNECTSQLSCHEMASUFFIX%_fast_small.db'}
    symbols:
      - name: a_fast_small_table
        query: "SELECT * FROM a;"
- PythonCode:
    code: |
        data = connect.container.data["a_fast_small_table"].records.values.tolist()
        expected = [['seattle', 350.0],
                    ['san-diego',600.0]]
        if data != expected:
            raise Exception("Unexpected data a_fast_small_table.")
$offEmbeddedCode

$log test global option small
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
      - name: j
- SQLWriter:
    connection: {'database': '%sysEnv.CONNECTSQLSCHEMASUFFIX%_global_small.db'}
    small: True
    skipText: True
    symbols: all
$offEmbeddedCode

$onEmbeddedCode Connect:
- SQLReader:
    connection: {'database': '%sysEnv.CONNECTSQLSCHEMASUFFIX%_global_small.db'}
    symbols:
      - name: j_small_table
        query: "SELECT * FROM j;"
        type: set
- PythonCode:
    code: |
        data = connect.container.data["j_small_table"].records.values.tolist()
        expected = [['new-york',''],
                    ['chicago',''],
                    ['topeka','']]
        if data != expected:
            raise Exception("Unexpected data j_small_table.")
$offEmbeddedCode

$log check UEL$ table entries
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
      - name: j
      - name: j_with_element_text
      - name: jnew
- SQLWriter:
    connection: {'database': '%sysEnv.CONNECTSQLSCHEMASUFFIX%_uel_table.db'}
    small: True
    symbols: all
$offEmbeddedCode

$onEmbeddedCode Connect:
- SQLReader:
    connection: {'database': '%sysEnv.CONNECTSQLSCHEMASUFFIX%_uel_table.db'}
    symbols:
      - name: uel_table
        query: "SELECT * FROM [UEL$];"
      - name: root_table_j
        query: "SELECT uni FROM [jnew$];"
        type: set
      - name: jnew_table
        query: "SELECT uni FROM jnew;"
        type: set
- PythonCode:
    code: |
        m = connect.container
        expected = {
                    "uel_table": [
                        ["new-york", 1.0],
                        ["chicago", 2.0],
                        ["topeka", 3.0],
                        ["toronto", 4.0],
                        ["vancouver", 5.0],
                        ["j1", 6.0],
                        ["j2", 7.0],
                    ],
                    "root_table_j": [["4", ""], ["5", ""]],
                    "jnew_table": [["toronto", ""], ["vancouver", ""]],
                }
        for key, values in expected.items():
            if values != m[key].records.values.tolist():
                raise Exception(f"Unexpected data >{key}<")
$offEmbeddedCode

$log test symbol option unstack with global small
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
      - name: d
      - name: ij_with_text
      - name: f
- SQLWriter:
    connection: {'database': '%sysEnv.CONNECTSQLSCHEMASUFFIX%_symbol_small.db'}
    small: True
    symbols:
      - name: d
        tableName: d_tbl
        unstack: True
      - name: ij_with_text
        tableName: ij_tbl
        unstack: True
      - name: f
        tableName: f_tbl
$offEmbeddedCode

$onEmbeddedCode Connect:
- SQLReader:
    connection: {'database': '%sysEnv.CONNECTSQLSCHEMASUFFIX%_symbol_small.db'}
    symbols:
      - name: ij_with_text_unstack
        query: "SELECT * FROM ij_tbl;"
        type: set
        valueColumns: ["seattle", "san-diego"]
      - name: d_small_unstack
        query: "SELECT * FROM d_tbl;"
      - name: f_scalar_small_unstack
        query: "SELECT * FROM f_tbl;"
- PythonCode:
    code: |
        m = connect.container
        expected = {
            "d_small_unstack": [['seattle', '2.5', '1.7', 1.8], ['san-diego', '2.5', '1.8', 1.4]],
            "ij_with_text_unstack": [['new-york', 'seattle', 'east-west'], ['chicago', 'san-diego', 'Y']],
            "f_scalar_small_unstack": [[90.0]]
        }
        for key, values in expected.items():
            if values != m[key].records.values.tolist():
                raise Exception(f"Unexpected data >{key}<")
$offEmbeddedCode

$log test symbol option ifExists=append with global small
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
      - name: i
      - name: j
- SQLWriter:
    connection: {'database': '%sysEnv.CONNECTSQLSCHEMASUFFIX%_symbol_small_append.db'}
    small: True
    skipText: True
    symbols:
      - name: i
        tableName: i_tbl
      - name: j
        tableName: i_tbl
        ifExists: append
$offEmbeddedCode

$onEmbeddedCode Connect:
- SQLReader:
    connection: {'database': '%sysEnv.CONNECTSQLSCHEMASUFFIX%_symbol_small_append.db'}
    symbols:
      - name: i_j_append_small
        query: "SELECT * FROM i_tbl;"
        type: set
- PythonCode:
    code: |
        data = connect.container.data["i_j_append_small"].records.values.tolist()
        expected = [['seattle', ''], ['san-diego', ''], ['new-york', ''], ['chicago', ''], ['topeka', '']]
        if data != expected:
            raise Exception("Unexpected data i_j_append_small.")
$offEmbeddedCode

$log test symbol option ifExists=replace with global small
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
      - name: a
      - name: b
- SQLWriter:
    connection: {'database': '%sysEnv.CONNECTSQLSCHEMASUFFIX%_symbol_small_replace.db'}
    small: True
    symbols:
      - name: a
        tableName: a_tbl
      - name: b
        tableName: a_tbl
        ifExists: replace
$offEmbeddedCode

$onEmbeddedCode Connect:
- SQLReader:
    connection: {'database': '%sysEnv.CONNECTSQLSCHEMASUFFIX%_symbol_small_replace.db'}
    symbols:
      - name: a_b_replace
        query: "SELECT * FROM a_tbl;"
- PythonCode:
    code: |
        data = connect.container.data["a_b_replace"].records.values.tolist()
        expected = [['new-york', 325.0], ['chicago', 300.0], ['topeka', 275.0]]
        if data != expected:
            raise Exception("Unexpected data a_b_replace.")
$offEmbeddedCode
$endIf.sqlite_specific_tests
$offEcho

$onEchoV > writeAccess.gms
set i / i4, i3, i2, i1 /;
set j / j1*j4 /;
set k / k4, k3, k2, k1 /;
set l / l1*l4 /;
parameter p4(i,j,k,l);
p4(i,j,k,l)$(uniform(0,1)<0.1) = uniform(0,1);

$set connection {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
$set VALUESUB_WRITE {UNDEF: -2}
$set VALUESUB_READ {-2: UNDEF, .nan: NA}

$log test ifExists set to `fail`
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
         - name: j
           newName: j_write_all_table 
- SQLWriter:
    connectionType: access
    connection: %connection%
    ifExists: "replace"
- SQLWriter:
    connectionType: access
    connection: %connection%
    ifExists: "fail"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test creating a table with the same name but with enclosed tableName, escChar = []
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
        - name: i
        - name: j
- SQLWriter:
    connectionType: access
    connection: %connection%
    symbols:
      - name: i
        tableName: i_tbl_repeat
      - name: j
        tableName: '[i_tbl_repeat]'
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test creating a table with the same name but with enclosed tableName, escChar = ``
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
        - name: i
        - name: j
- SQLWriter:
    connectionType: access
    connection: %connection%
    symbols:
      - name: i
        tableName: i_tbl_repeat
      - name: j
        tableName: '[i_tbl_repeat]'
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test creating a table with the same name but with enclosed tableName, escChar = ""
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
        - name: i
        - name: j
- SQLWriter:
    connectionType: access
    connection: %connection%
    symbols:
      - name: i
        tableName: i_tbl_repeat
      - name: j
        tableName: '"i_tbl_repeat"'
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test global symbols=all(default) & ifExists
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
         - name: j
           newName: j_write_all_table 
         - name: d
           newName: d_write_all_table
         - name: f
           newName: f_write_all_table
- SQLWriter:
    connectionType: access
    connection: %connection%
    ifExists: "replace"
$offEmbeddedCode

$log test set without explanatory text
$onEmbeddedCode Connect:
- SQLReader:
    connection: %connection%
    connectionType: access
    symbols:
      - name: j_write_all_table
        query: "SELECT * FROM j_write_all_table;"
        type: set
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["j_write_all_table"].records.values.tolist()
        expected = [['new-york',''],
                    ['chicago',''],
                    ['topeka','']]
        if data != expected:
            raise Exception("Unexpected data j_write_all_table.")
$offEmbeddedCode

$log test 2-dim parameter
$onEmbeddedCode Connect:
- SQLReader:
    connection: %connection%
    connectionType: access
    symbols:
      - name: d_write_all_table
        query: "SELECT * FROM d_write_all_table;"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["d_write_all_table"].records.values.tolist()
        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]]
        if data != expected:
            raise Exception("Unexpected data d_write_all_table.")
$offEmbeddedCode

$log test scalar 
$onEmbeddedCode Connect:
- SQLReader:
    connection: %connection%
    connectionType: access
    symbols:
      - name: f_write_all_table
        query: "SELECT * FROM f_write_all_table;"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["f_write_all_table"].records.values.tolist()
        expected = [[90.0,]]
        if data != expected:
            raise Exception("Unexpected data f_write_all_table.")
$offEmbeddedCode

$log test 1-dim parameter
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
      - name: b
- SQLWriter:
    connection: %connection%
    connectionType: access
    symbols:
      - name: b
        tableName: b_table
        ifExists: "replace"
        unstack: True
$offEmbeddedCode
$onEmbeddedCode Connect:
- SQLReader:
    connection: %connection%
    connectionType: access
    symbols:
      - name: b_table
        query: "SELECT * FROM b_table;"
        valueColumns: ["new-york", "chicago", "topeka"]
- PythonCode:
    code: |
        data = connect.container.data["b_table"].records.values.tolist()
        expected = [['new-york', 325.0],
                    ['chicago', 300.0],
                    ['topeka', 275.0]]
        if data != expected:
            raise Exception("Unexpected data d_table.")
$offEmbeddedCode

$log test 1-dim set
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
      - name: j
- SQLWriter:
    connection: %connection%
    connectionType: access
    symbols:
      - name: j
        tableName: j_table_unstack
        ifExists: "replace"
        unstack: True
$offEmbeddedCode
$onEmbeddedCode Connect:
- SQLReader:
    connection: %connection%
    connectionType: access
    symbols:
      - name: j_table_unstack
        query: "SELECT * FROM j_table_unstack;"
        type: set
        valueColumns: ["new-york", "chicago", "topeka"]
- PythonCode:
    code: |
        data = connect.container.data["j_table_unstack"].records.values.tolist()
        expected = [['new-york', 'Y'],
                    ['chicago', 'Y'],
                    ['topeka', 'Y']]
        if data != expected:
            raise Exception("Unexpected data j_table_unstack.")
$offEmbeddedCode

$log test 2-dim set with global unstack
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
      - name: ij
- SQLWriter:
    connection: %connection%
    connectionType: access
    unstack: True
    symbols:
      - name: ij
        tableName: ij_table
        ifExists: "replace"
$offEmbeddedCode
$onEmbeddedCode Connect:
- SQLReader:
    connection: %connection%
    connectionType: access
    symbols:
      - name: ij_table
        query: "SELECT * FROM ij_table;"
        type: set
        valueColumns: ["seattle", "san-diego"]
- PythonCode:
    code: |
        data = connect.container.data["ij_table"].records.values.tolist()
        expected = [['new-york', 'seattle', 'Y'],
                    ['chicago', 'san-diego', 'Y']]
        if data != expected:
            raise Exception("Unexpected data ij_table.")
$offEmbeddedCode

$log add to existing table
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
         - name: jnew
           newName: j_write_all_table
- SQLWriter:
    connection: %connection%
    connectionType: access
    ifExists: "append"
$offEmbeddedCode

$log test appended table
$onEmbeddedCode Connect:
- SQLReader:
    connection: %connection%
    connectionType: access
    symbols:
      - name: j_write_all_table
        query: "SELECT * FROM j_write_all_table;"
        type: set
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["j_write_all_table"].records.values.tolist()
        expected = [['new-york',''],
                    ['chicago',''],
                    ['topeka',''],
                    ['toronto',''],
                    ['vancouver','']]
        if data != expected:
            raise Exception("Unexpected data j_write_all_table.")
$offEmbeddedCode

$log adding list of symbols
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
        - name: x
        - name: z
- Projection:
    name: x.all(i,j)
    newName: px(i,j)
- Projection:
    name: x.l(i,j)
    newName: xl(i,j)
- Projection:
    name: z.all
    newName: pz
- SQLWriter:
    connectionType: access
    connection: %connection%
    symbols:
       - name: px
         tableName: x_table
         ifExists: "replace"
         unstack: True
       - name: xl
         tableName: xl_table
         ifExists: "replace"
       - name: pz
         tableName: z_table
         ifExists: "replace"
$offEmbeddedCode

$log test positive variable x
$onEmbeddedCode Connect:
- SQLReader:
    connection: %connection%
    connectionType: access
    symbols:
      - name: x_table
        # level is a reserved keyword in MS-Access, needs to be enclosed within []
        query: "SELECT i, j, [level] FROM x_table;"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["x_table"].records.values.tolist()
        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]]
        if data != expected:
            raise Exception("Unexpected data x_table.")
$offEmbeddedCode

$log test xl
$onEmbeddedCode Connect:
- SQLReader:
    connection: %connection%
    connectionType: access
    symbols:
      - name: xl_table
        query: "SELECT * FROM xl_table;"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["xl_table"].records.values.tolist()
        expected = [['seattle', 'new-york', 50.0],
                    ['seattle', 'chicago', 300],
                    ['seattle', 'topeka', 0.0],
                    ['san-diego', 'new-york', 275.0],
                    ['san-diego', 'chicago', 0.0],
                    ['san-diego', 'topeka', 275.0]]
        if data != expected:
            raise Exception("Unexpected data xl_table.")
$offEmbeddedCode

$log test free variable z
$onEmbeddedCode Connect:
- SQLReader:
    connection: %connection%
    connectionType: access
    symbols:
      - name: z_table
        query: "SELECT * FROM z_table;"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["z_table"].records.values.tolist()
        expected = [['level', 153.675],
                    ['marginal', 0.0],
                    ['lower', float('-inf')],
                    ['upper', float('inf')],
                    ['scale', 1.0]]
        if data != expected:
            raise Exception("Unexpected data z_table.")
$offEmbeddedCode

$log test skipText
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
         - name: j_with_element_text
- SQLWriter:
    connection: %connection%
    connectionType: access
    ifExists: replace
    symbols:
        - name: j_with_element_text
          tableName: j_without_text
          skipText: True
        - name: j_with_element_text
          tableName: j_with_text
          skipText: False
- SQLReader:
    connection: %connection%
    connectionType: access
    symbols:
        - name: j1_without_text
          query: "SELECT * FROM j_without_text;"
          type: set
        - name: j2_with_text
          query: "SELECT * FROM j_with_text;"
          type: set
          valueColumns: lastCol
- PythonCode:
    code: |
        data1 = connect.container["j1_without_text"].records.values.tolist()
        expected1 = [['j1', ''], ['j2', '']]
        data2 = connect.container["j2_with_text"].records.values.tolist()
        expected2 = [['j1', 'This is j1'], ['j2', 'This is j2']]
        if data1 != expected1:
            raise Exception("Unexpected data j_without_text")
        if data2 != expected2:
            raise Exception("Unexpected data j_with_text")
$offEmbeddedCode

$log test skipText together with unstack
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
        - name: ij_with_text
- SQLWriter:
    connection: %connection%
    connectionType: access
    ifExists: replace
    unstack: True
    symbols:
        - name: ij_with_text
          tableName: ij_with_unstack
        - name: ij_with_text
          tableName: ij_with_unstack_skipEleText
          skipText: True
- SQLReader:
    connection: %connection%
    connectionType: access
    symbols:
        - name: read_ij_with_unstack
          query: "SELECT * FROM ij_with_unstack;"
          type: set
          valueColumns: ["seattle", "san-diego"]
        - name: read_ij_with_unstack_skipEleText
          query: "SELECT * FROM ij_with_unstack_skipEleText;"
          type: set
          valueColumns: ["seattle", "san-diego"]
- PythonCode:
    code: |
        data1 = connect.container["read_ij_with_unstack"].records.values.tolist()
        data2 = connect.container["read_ij_with_unstack_skipEleText"].records.values.tolist()
        expected1 = [['new-york', 'seattle', 'east-west'], ['chicago', 'san-diego', 'Y']]
        expected2 = [['new-york', 'seattle', 'Y'], ['chicago', 'san-diego', 'Y']]

        if data1 != expected1:
            raise Exception("Unexpected data in read_ij_with_unstack")
            
        if data2 != expected2:
            raise Exception("Unexpected data in read_ij_with_unstack_skipEleText")
$offEmbeddedCode

$log test writing empty symbols to access
$onEmbeddedCode Connect:
- PythonCode:
    code: |
      connect.container.addSet("k", domain=["*"])
      connect.container.addParameter("p", domain=["*"])
- SQLWriter:
    connectionType: access
    connection: %connection%
    symbols: all
    ifExists: "replace"
$offEmbeddedCode

$log test retrieving empty tables created by empty symbols
$onEmbeddedCode Connect:
- SQLReader:
    connectionType: access
    connection: %connection%
    symbols:
      - name: k
        query: "SELECT * FROM k"
        type: set
        valueColumns: lastCol
      - name: p
        query: "SELECT * FROM p"
        type: par
- PythonCode:
    code: |
      data_k = connect.container["k"].records
      cols_k = [0, 'element_text']
      data_p = connect.container["p"].records
      cols_p = [0, 'value']

      if data_k is None or not data_k.empty:
        raise Exception("Expected set >k< to have empty records.")
    
      if data_p is None or not data_p.empty:
        raise Exception("Expected parameter >p< to have empty records.")
        
      if (data_k.columns != cols_k).any():
        raise Exception("Unexpected columns for set >k<.")
        
      if (data_p.columns != cols_p).any():
        raise Exception("Unexpected columns for parameter >p<.")
$offEmbeddedCode

* test for correct categoricals
EmbeddedCode Connect:
- GAMSReader:
    symbols:
      - name: p4
- SQLWriter:
    connection: %connection%
    connectionType: access
    symbols:
      - name: p4
        tableName: p4Table
        ifExists: "replace"
        unstack: True
endEmbeddedCode
EmbeddedCode Connect:
- SQLReader:
    connection: %connection%
    connectionType: access
    symbols:
      - name: p4
        query: "SELECT * FROM p4Table;"
        valueColumns: ["l1","l2","l3","l4"]
- PythonCode:
    code: |
      # we expect the original UEL order: l1, l2, l3, l4
      expected = [['i4', 'j1', 'k2', 'l1', 0.500210669],
                  ['i4', 'j3', 'k2', 'l3', 0.33855027200000004],
                  ['i4', 'j4', 'k2', 'l1', 0.175661049],
                  ['i4', 'j4', 'k3', 'l1', 0.102514669],
                  ['i4', 'j4', 'k3', 'l4', 0.792360642],
                  ['i4', 'j4', 'k1', 'l1', 0.5851311730000001],
                  ['i3', 'j1', 'k2', 'l2', 0.202015557],
                  ['i3', 'j1', 'k2', 'l3', 0.269613052],
                  ['i3', 'j3', 'k1', 'l1', 0.40134625700000004],
                  ['i3', 'j4', 'k3', 'l1', 0.7831020040000001],
                  ['i3', 'j2', 'k2', 'l3', 0.576299805],
                  ['i3', 'j2', 'k2', 'l4', 0.006008368],
                  ['i2', 'j1', 'k2', 'l4', 0.15034771600000002],
                  ['i2', 'j3', 'k2', 'l1', 0.196093864],
                  ['i2', 'j3', 'k3', 'l1', 0.177822574],
                  ['i2', 'j3', 'k3', 'l2', 0.016643898],
                  ['i2', 'j4', 'k3', 'l4', 0.948836169],
                  ['i2', 'j2', 'k3', 'l3', 0.187448731],
                  ['i2', 'j2', 'k3', 'l4', 0.540400638],
                  ['i2', 'j2', 'k1', 'l4', 0.543870155],
                  ['i1', 'j1', 'k3', 'l1', 0.36863057200000005],
                  ['i1', 'j1', 'k3', 'l4', 0.84181978],
                  ['i1', 'j1', 'k1', 'l1', 0.7737034340000001],
                  ['i1', 'j3', 'k2', 'l2', 0.32300194000000004],
                  ['i1', 'j3', 'k4', 'l4', 0.263857554],
                  ['i1', 'j4', 'k1', 'l3', 0.37419850000000004],
                  ['i1', 'j4', 'k4', 'l4', 0.41459935800000003],
                  ['i1', 'j2', 'k2', 'l4', 0.485176103],
                  ['i1', 'j2', 'k1', 'l1', 0.698580858]]
      sym = connect.container.data["p4"]
      data_sorted = sym.records.sort_values(sym.records.columns[:-1].tolist()).values.tolist()
      if data_sorted != expected:
        raise Exception("Unexpected sorted data p4")
- SQLWriter:
    connection: %connection%
    connectionType: access
    symbols:
      - name: p4
        tableName: p4Table_ref
        ifExists: "replace"
        unstack: True
- SQLReader:
    connection: %connection%
    connectionType: access
    symbols:
      - name: p4_ref
        query: "SELECT * FROM p4Table_ref;"
        valueColumns: ["l1","l2","l3","l4"] 
- PythonCode:
    code: |
      # we expect the original UEL order: l1, l2, l3, l4
      expected = [['i4', 'j1', 'k2', 'l1', 0.500210669],
                  ['i4', 'j3', 'k2', 'l3', 0.33855027200000004],
                  ['i4', 'j4', 'k2', 'l1', 0.175661049],
                  ['i4', 'j4', 'k3', 'l1', 0.102514669],
                  ['i4', 'j4', 'k3', 'l4', 0.792360642],
                  ['i4', 'j4', 'k1', 'l1', 0.5851311730000001],
                  ['i3', 'j1', 'k2', 'l2', 0.202015557],
                  ['i3', 'j1', 'k2', 'l3', 0.269613052],
                  ['i3', 'j3', 'k1', 'l1', 0.40134625700000004],
                  ['i3', 'j4', 'k3', 'l1', 0.7831020040000001],
                  ['i3', 'j2', 'k2', 'l3', 0.576299805],
                  ['i3', 'j2', 'k2', 'l4', 0.006008368],
                  ['i2', 'j1', 'k2', 'l4', 0.15034771600000002],
                  ['i2', 'j3', 'k2', 'l1', 0.196093864],
                  ['i2', 'j3', 'k3', 'l1', 0.177822574],
                  ['i2', 'j3', 'k3', 'l2', 0.016643898],
                  ['i2', 'j4', 'k3', 'l4', 0.948836169],
                  ['i2', 'j2', 'k3', 'l3', 0.187448731],
                  ['i2', 'j2', 'k3', 'l4', 0.540400638],
                  ['i2', 'j2', 'k1', 'l4', 0.543870155],
                  ['i1', 'j1', 'k3', 'l1', 0.36863057200000005],
                  ['i1', 'j1', 'k3', 'l4', 0.84181978],
                  ['i1', 'j1', 'k1', 'l1', 0.7737034340000001],
                  ['i1', 'j3', 'k2', 'l2', 0.32300194000000004],
                  ['i1', 'j3', 'k4', 'l4', 0.263857554],
                  ['i1', 'j4', 'k1', 'l3', 0.37419850000000004],
                  ['i1', 'j4', 'k4', 'l4', 0.41459935800000003],
                  ['i1', 'j2', 'k2', 'l4', 0.485176103],
                  ['i1', 'j2', 'k1', 'l1', 0.698580858]]
      sym = connect.container.data["p4_ref"]
      data_sorted = sym.records.sort_values(sym.records.columns[:-1].tolist()).values.tolist()
      if data_sorted != expected:
        raise Exception("Unexpected sorted data p4_ref")
endEmbeddedCode

$log test access, value substitutions for special values (default)
$onEmbeddedCode Connect:
- PythonCode:
    code: |
      import pandas as pd
      data = [
        ['i1', "EPS"],
        ['i2', "-INF"],
        ['i3', "INF"],
        ['i4', "UNDEF"],
        ['i5', "NA"],
        ['i6', 0],
        ['i7', 1]
      ]
      df = pd.DataFrame(data)
      connect.container.addParameter('p_sv', ['*'], records=df)
- SQLWriter:
    connection: %connection%
    connectionType: access
    ifExists: replace
    valueSubstitutions: %VALUESUB_WRITE%
- SQLReader:
    connection: %connection%
    connectionType: access
    valueSubstitutions: %VALUESUB_READ%
    symbols:
      - name: p_new
        query: "SELECT * FROM p_sv;"
- PythonCode:
    code: |
      import numpy as np
      import gams.transfer as gt
      data_p = connect.container["p_sv"].records.values.tolist()
      data_p_new = connect.container["p_new"].records.values.tolist()
      if not np.array_equal(data_p, data_p_new):
        raise Exception("Unexpected data.")
      if not gt.SpecialValues.isEps(data_p[0][-1]) or not gt.SpecialValues.isEps(data_p_new[0][-1]):
        raise Exception("Unexpected data EPS.")
      if not gt.SpecialValues.isNegInf(data_p[1][-1]) or not gt.SpecialValues.isNegInf(data_p_new[1][-1]):
        raise Exception("Unexpected data -INF.")
      if not gt.SpecialValues.isPosInf(data_p[2][-1]) or not gt.SpecialValues.isPosInf(data_p_new[2][-1]):
        raise Exception("Unexpected data INF.")
      if not gt.SpecialValues.isUndef(data_p[3][-1]) or not gt.SpecialValues.isUndef(data_p_new[3][-1]):
        raise Exception("Unexpected data UNDEF.")
      if not gt.SpecialValues.isNA(data_p[4][-1]) or not gt.SpecialValues.isNA(data_p_new[4][-1]):
        raise Exception("Unexpected data NA.")
      if data_p[5][-1] != 0 or data_p_new[5][-1] != 0:
        raise Exception("Unexpected data 0.")
      if data_p[6][-1] != 1 or data_p_new[6][-1] != 1:
        raise Exception("Unexpected data 1.")
$offEmbeddedCode

$log test access, value substitutions for special values
$onEmbeddedCode Connect:
- PythonCode:
    code: |
      import pandas as pd
      data = [
        ['i1', "EPS"],
        ['i2', "-INF"],
        ['i3', "INF"],
        ['i4', "UNDEF"],
        ['i5', "NA"],
        ['i6', 0],
        ['i7', 1]
      ]
      df = pd.DataFrame(data)
      connect.container.addParameter('p_sv_1', ['*'], records=df)
- SQLWriter:
    connection: %connection%
    connectionType: access
    ifExists: replace
    valueSubstitutions: {
      "EPS": 0,
      "-INF": 1,
      "INF": 2,
      "UNDEF": 3,
      "NA": 4
    }
- SQLReader:
    connection: %connection%
    connectionType: access
    symbols:
      - name: p_sv_new
        query: "SELECT * FROM p_sv_1;"
- PythonCode:
    code: |
      expected = [
        ['i1', 0.],
        ['i2', 1.],
        ['i3', 2.],
        ['i4', 3.],
        ['i5', 4.],
        ['i6', 0.],
        ['i7', 1.]
      ]

      data_p_new = connect.container["p_sv_new"].records.values.tolist()
      if expected != data_p_new:
        raise Exception("Unexpected data")
$offEmbeddedCode

$log test access, value substitutions for special values not being transitive
$onEmbeddedCode Connect:
- PythonCode:
    code: |
      import pandas as pd
      data = [
        ['i1', "EPS"],
        ['i2', "-INF"],
        ['i3', "INF"],
        ['i4', "UNDEF"],
        ['i5', "NA"],
        ['i6', 0],
        ['i7', 1]
      ]
      df = pd.DataFrame(data)
      connect.container.addParameter('p_sv_2', ['*'], records=df)
- SQLWriter:
    connection: %connection%
    connectionType: access
    ifExists: replace
    valueSubstitutions: {
      "EPS": .nan,  # gets dropped - .nan is gt.SpecialValues.Undef and should not be replaced by the UNDEF:1 value substitution
      "-INF": 0,
      "INF": .nan,  # gets dropped
      "UNDEF": 1,
      "NA": 2
    }
- SQLReader:
    connection: %connection%
    connectionType: access
    symbols:
      - name: p_sv_new
        query: "SELECT * FROM p_sv_2;"
- PythonCode:
    code: |
      expected = [
        ['i2', 0.],
        ['i4', 1.],
        ['i5', 2.],
        ['i6', 0.],
        ['i7', 1.]
      ]
      data_p_new = connect.container["p_sv_new"].records.values.tolist()
      if expected != data_p_new:
        raise Exception("Unexpected data")
$offEmbeddedCode

$log adding 4-dim parameter with insertMethod: bulkInsert
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
      - name: pbulk
- SQLWriter:
    connection: %connection%
    connectionType: access
    insertMethod: bulkInsert
    symbols:
      - name: pbulk
        tableName: pbulk_table
        ifExists: "replace"
$offEmbeddedCode

$log test 4-dim parameter written with bulkInsert
$onEmbeddedCode Connect:
- SQLReader:
    connection: %connection%
    connectionType: access
    symbols:
      - name: pbulk_table
        query: "SELECT * FROM pbulk_table;"
- PythonCode:
    code: |
        data = connect.container.data["pbulk_table"].records.values.tolist()
        expected = [['index1', 'index1', 'index1', 'index1', 0.17174713200000002],
                    ['index1', 'index1', 'index1', 'index2', 0.843266708],
                    ['index1', 'index1', 'index2', 'index1', 0.550375356],
                    ['index1', 'index1', 'index2', 'index2', 0.301137904],
                    ['index1', 'index2', 'index1', 'index1', 0.292212117],
                    ['index1', 'index2', 'index1', 'index2', 0.22405286700000002],
                    ['index1', 'index2', 'index2', 'index1', 0.34983050400000004],
                    ['index1', 'index2', 'index2', 'index2', 0.856270347],
                    ['index2', 'index1', 'index1', 'index1', 0.067113723],
                    ['index2', 'index1', 'index1', 'index2', 0.500210669],
                    ['index2', 'index1', 'index2', 'index1', 0.9981176270000001],
                    ['index2', 'index1', 'index2', 'index2', 0.578733378],
                    ['index2', 'index2', 'index1', 'index1', 0.991133039],
                    ['index2', 'index2', 'index1', 'index2', 0.7622504670000001],
                    ['index2', 'index2', 'index2', 'index1', 0.130692483],
                    ['index2', 'index2', 'index2', 'index2', 0.6397187590000001]]
        if data != expected:
            raise Exception("Unexpected data pbulk_table.")
$offEmbeddedCode

$log adding 4-dim parameter with unstack: True
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
      - name: pbulk
        newName: pbulk_table_unstack
- SQLWriter:
    connection: %connection%
    connectionType: access
    insertMethod: bulkInsert
    unstack: True
    ifExists: replace
$offEmbeddedCode

$log test 4-dim parameter written with bulkInsert & unstack: True
$onEmbeddedCode Connect:
- SQLReader:
    connection: %connection%
    connectionType: access
    symbols:
      - name: pbulk_table_unstack
        query: "SELECT * FROM pbulk_table_unstack;"
        valueColumns: ["index1", "index2"]
- PythonCode:
    code: |
        data = connect.container.data["pbulk_table_unstack"].records.values.tolist()
        expected = [['index1', 'index1', 'index1', 'index1', 0.17174713200000002],
                    ['index1', 'index1', 'index1', 'index2', 0.843266708],
                    ['index1', 'index1', 'index2', 'index1', 0.550375356],
                    ['index1', 'index1', 'index2', 'index2', 0.301137904],
                    ['index1', 'index2', 'index1', 'index1', 0.292212117],
                    ['index1', 'index2', 'index1', 'index2', 0.22405286700000002],
                    ['index1', 'index2', 'index2', 'index1', 0.34983050400000004],
                    ['index1', 'index2', 'index2', 'index2', 0.856270347],
                    ['index2', 'index1', 'index1', 'index1', 0.067113723],
                    ['index2', 'index1', 'index1', 'index2', 0.500210669],
                    ['index2', 'index1', 'index2', 'index1', 0.9981176270000001],
                    ['index2', 'index1', 'index2', 'index2', 0.578733378],
                    ['index2', 'index2', 'index1', 'index1', 0.991133039],
                    ['index2', 'index2', 'index1', 'index2', 0.7622504670000001],
                    ['index2', 'index2', 'index2', 'index1', 0.130692483],
                    ['index2', 'index2', 'index2', 'index2', 0.6397187590000001]]
        if data != expected:
            raise Exception("Unexpected data pbulk_table_unstack.")
$offEmbeddedCode
$offEcho

$onEcho > sub.txt
eolonly 1
--VALUESUBST {INF: 99999, -INF: -99999}
--VALUESUBSTREADER {99999: INF, -99999: -INF}
$offEcho
$onEcho > nosub.txt
eolonly 1
--VALUESUBST {}
--VALUESUBSTREADER {}
$offEcho

$onEcho > connect.txt
eolonly 1
--SCHEMASUFFIX Null
--TABLEPREFIX
--CONNECTIONTYPE sqlite
--ENCLOSEL 
--ENCLOSER 
--CONNECTIONDICT {'database': '%sysEnv.CONNECTSQLSCHEMASUFFIX%.db'}
--CONNECTIONDICTSQLALCHEMY {'drivername': 'sqlite', 'database': '%sysEnv.CONNECTSQLSCHEMASUFFIX%.db'}
--VALUESUB_WRITE {UNDEF: -2, EPS: -1}
--VALUESUB_READ {-2: UNDEF, -1: EPS, .nan: NA}
$offEcho
$log testing sqlite
$call gams writeTest.gms lo=%gams.lo% pf=connect.txt pf=nosub.txt
$ifE errorLevel<>0 $goTo cleanup

$onEcho > connect.txt
eolonly 1
--SCHEMASUFFIX %sysEnv.CONNECTSQLSCHEMASUFFIX%
--TABLEPREFIX %sysEnv.CONNECTSQLTABLEPREFIX%
--CONNECTIONTYPE mysql
--ENCLOSEL `
--ENCLOSER `
--CONNECTIONDICT {'user':'root', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 21000, 'host': 'internal2.gams.com', 'database': 'testdb'}
--CONNECTIONDICTSQLALCHEMY {'username':'root', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 21000, 'host': 'internal2.gams.com', 'database': 'testdb', 'drivername': 'mysql+pymysql'}
--VALUESUB_WRITE {UNDEF: -2, EPS: -1, INF: 10, -INF: -10}
--VALUESUB_READ {-2: UNDEF, -1: EPS, .nan: NA, 10: INF, -10: -INF}
$offEcho
$log testing mysql
$call gams writeTest.gms lo=%gams.lo% pf=connect.txt pf=sub.txt
$ifE errorLevel<>0 $goTo cleanup

$onEcho > connect.txt
eolonly 1
--SCHEMASUFFIX %sysEnv.CONNECTSQLSCHEMASUFFIX%
--TABLEPREFIX %sysEnv.CONNECTSQLTABLEPREFIX%
--CONNECTIONTYPE postgres
--ENCLOSEL 
--ENCLOSER 
--CONNECTIONDICT {'user':'postgres', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 22000, 'host': 'internal2.gams.com', 'database': 'testdb'}
--CONNECTIONDICTSQLALCHEMY {'username':'postgres', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 22000, 'host': 'internal2.gams.com', 'database': 'testdb', 'drivername': 'postgresql+psycopg2'}
--VALUESUB_WRITE {UNDEF: -2, EPS: -1}
--VALUESUB_READ {-2: UNDEF, -1: EPS, .nan: NA}
$offEcho
$log testing postgres
$call gams writeTest.gms lo=%gams.lo% pf=connect.txt pf=nosub.txt
$ifE errorLevel<>0 $goTo cleanup

$log testing sqlserver
$set HAVEBCP 0
$ifI     %system.FileSys% == unix $call which bcp > /dev/null 2>&1
$ifI not %system.FileSys% == unix $call where -q bcp > nul
$ifE errorLevel==0 $set HAVEBCP 1

$onEcho > connect.txt
eolonly 1
--SCHEMASUFFIX Null
--TABLEPREFIX
--CONNECTIONTYPE sqlserver
--ENCLOSEL [
--ENCLOSER ]
--CONNECTIONDICT {'user':'sa', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%', 'port': 23000, 'host': 'internal2.gams.com', 'database': '%sysEnv.CONNECTSQLSCHEMASUFFIX%'}
--CONNECTIONDICTSQLALCHEMY {'username':'sa', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 23000, 'host': 'internal2.gams.com', 'database': '%sysEnv.CONNECTSQLSCHEMASUFFIX%', 'drivername': 'mssql+pymssql'}
--HAVEBCP %HAVEBCP%
--VALUESUB_WRITE {UNDEF: -2, EPS: -1, INF: 10, -INF: -10}
--VALUESUB_READ {-2: UNDEF, -1: EPS, .nan: NA, 10: INF, -10: -INF}
$offEcho
$call gams writeTest.gms lo=%gams.lo% pf=connect.txt pf=sub.txt
$ifE errorLevel<>0 $goto cleanup


$label cleanup
$log dropping test schemas
$onEmbeddedCode Python:
import psycopg2, pymysql, pymssql


with psycopg2.connect(**{'user':'postgres', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 22000, 'host': 'internal2.gams.com', 'database': 'testdb'}) as conn:
    cur = conn.cursor()
    cur.execute(f"DROP SCHEMA IF EXISTS {schemaName} CASCADE;")
    conn.commit()
    cur.close() 

with pymysql.connect(**{'user':'root', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 21000, 'host': 'internal2.gams.com', 'database': 'testdb'}) as conn:
    cur = conn.cursor()
    cur.execute(f"DROP SCHEMA IF EXISTS {schemaName};")
    conn.commit()
    cur.close()
   
with pymssql.connect(**{'user':'sa', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 23000, 'host': 'internal2.gams.com'}, autocommit=True) as conn:
    cur = conn.cursor()
    cur.execute("USE MASTER;")
    cur.execute(f"""IF DB_ID('{schemaName}') IS NOT NULL DROP DATABASE {schemaName};""")
    conn.commit()
    cur.close()
$offEmbeddedCode
$ifE errorLevel<>0 $abort "Error occurred! Check log"

$log testing access
$if set RUNACCESS $call.checkErrorLevel gams writeAccess.gms lo=%gams.lo%


$onEcho>writePyodbc.gms
$log test creating a table with the same name but with enclosed tableName, mysql, escChar = ``
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
        - name: i
        - name: j
- SQLWriter:
    connectionType: pyodbc
    connection: %connection%
    columnEncloser: "``"
    ifExists: replace
    symbols:
      - name: i
        tableName: i_tbl_repeat
      - name: j
        tableName: '`i_tbl_repeat`'
        ifExists: fail
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test global dTypeMap and columnEncloser
$onEmbeddedCode Connect:
- GDXReader:
    file: t.gdx
    symbols:
         - name: j
           newName: j_pyodbc_dtype_table 
- SQLWriter:
    connectionType: pyodbc
    connection: %connection%
    ifExists: "replace"
    dTypeMap: {"text": "TINYTEXT"}
    columnEncloser: "`"
    
- SQLReader:
    connection: %connection%
    connectionType: pyodbc
    symbols:
      - name: j_pyodbc_dtype
        query: "SELECT COLUMN_NAME, DATA_TYPE
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = 'testdb'
                AND TABLE_NAME = 'j_pyodbc_dtype_table'
                AND COLUMN_NAME = 'uni';"
        type: set
        valueColumns: none
- PythonCode:
    code: |
        data = connect.container.data["j_pyodbc_dtype"].records.values.tolist()
        expected = [['uni','tinytext', '']]
        if data != expected:
            raise Exception("Unexpected data j_pyodbc_dtype.")
$offEmbeddedCode

$log test writing empty symbols using pyodbc
$onEmbeddedCode Connect:
- PythonCode:
    code: |
      connect.container.addSet("k", domain=["*"])
      connect.container.addParameter("p", domain=["*"])
- SQLWriter:
    connectionType: pyodbc
    connection: %connection%
    columnEncloser: "`"
    symbols: all
    ifExists: "replace"
$offEmbeddedCode

$log test retrieving empty tables created by empty symbols
$onEmbeddedCode Connect:
- SQLReader:
    connectionType: pyodbc
    connection: %connection%
    symbols:
      - name: k
        query: "SELECT * FROM k"
        type: set
        valueColumns: lastCol
      - name: p
        query: "SELECT * FROM p"
        type: par
- PythonCode:
    code: |
      data_k = connect.container["k"].records
      cols_k = [0, 'element_text']
      data_p = connect.container["p"].records
      cols_p = [0, 'value']

      if data_k is None or not data_k.empty:
        raise Exception("Expected set >k< to have empty records.")
    
      if data_p is None or not data_p.empty:
        raise Exception("Expected parameter >p< to have empty records.")
        
      if (data_k.columns != cols_k).any():
        raise Exception("Unexpected columns for set >k<.")
        
      if (data_p.columns != cols_p).any():
        raise Exception("Unexpected columns for parameter >p<.")
$offEmbeddedCode
$offEcho

$log testing pyodbc options on KUMANEW as the DSN is setup only on KUMANEW
$onecho > connect.txt
eolonly 1
--CONNECTION {'DSN':'kumaNewMySQL'}
$offEcho

$ifE sameas('%system.computername%','KUMANEW') $call.checkErrorLevel gams writePyodbc.gms lo=%gams.lo% pf=connect.txt