$title 'Test Connect agent SQLReader' (CASQLR,SEQ=909)

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

Contributor: Vaibhavnath Jha, March 2023
$offText

* On the major platforms (Windows, Linux, Mac), GMSPYTHONLIB gets automatically set
* to use the internal Python installation in sysdir/GMSPython.
$if not setEnv GMSPYTHONLIB $abort.noError Embedded code Python not ready to be used
$log --- Using Python library %sysEnv.GMSPYTHONLIB%

$onEmbeddedCode Python:
import string, os
from random import choices

schemaName = ''.join(choices(string.ascii_lowercase, k=10))
os.environ['CONNECTSQLSCHEMASUFFIX'] = schemaName
os.environ['CONNECTSQLTABLEPREFIX'] = schemaName + '.'
$offEmbeddedCode

$onEcho> sqlitescript.sql
CREATE TABLE scalarTable 
(
    value_col INTEGER PRIMARY KEY
);

INSERT INTO scalarTable(value_col) VALUES(653);

CREATE TABLE parameterTable 
(
    label TEXT,
    value_col1 DOUBLE PRECISION,
    value_col2 DOUBLE PRECISION
);

INSERT INTO parameterTable (label, value_col1, value_col2) VALUES('i3', 0.453, 0.532);
INSERT INTO parameterTable (label, value_col1, value_col2) VALUES('i2', 0.638, 0.478);
INSERT INTO parameterTable (label, value_col1, value_col2) VALUES('i1', 0.987, 0.804);

CREATE TABLE deciTable 
(
    label TEXT,
    value_col1 DOUBLE PRECISION
);

INSERT INTO deciTable (label, value_col1) VALUES('i3', 121.12);
INSERT INTO deciTable (label, value_col1) VALUES('i2', 122.22);
INSERT INTO deciTable (label, value_col1) VALUES('i1', 123.32);

CREATE TABLE setTable
(
    id INTEGER PRIMARY KEY,
    label1 TEXT,
    label2 TEXT,
    expText1 TEXT,
    expText2 TEXT
);

INSERT INTO setTable (id, label1, label2, expText1, expText2) VALUES(1, 'i1', 'j1', 'Text1', 'RandomText1');
INSERT INTO setTable (id, label1, label2, expText1, expText2) VALUES(2, 'i1', 'j2', 'Text2', 'RandomText2');
INSERT INTO setTable (id, label1, label2, expText1, expText2) VALUES(3, 'i1', 'j3', 'Text3', 'RandomText3');
INSERT INTO setTable (id, label1, label2, expText1, expText2) VALUES(4, 'i2', 'j1', NULL, 'RandomText4');
INSERT INTO setTable (id, label1, label2, expText1, expText2) VALUES(5, 'i2', 'j2', 'Text5', 'RandomText5');
INSERT INTO setTable (id, label1, label2, expText1, expText2) VALUES(6, 'i2', 'j3', NULL, 'RandomText6');

CREATE TABLE distanceTableOne
(
    id INTEGER PRIMARY KEY,
    source TEXT,
    newyork DOUBLE PRECISION,
    chicago DOUBLE PRECISION,
    topeka DOUBLE PRECISION
);

INSERT INTO distanceTableOne (id, source, newyork, chicago, topeka) VALUES(1, 'seattle', 2.5, 1.7, 1.8);
INSERT INTO distanceTableOne (id, source, newyork, chicago, topeka) VALUES(2, 'san-diego', 2.5, 1.8, 1.4);

CREATE TABLE distanceTableTwo
(
    id INTEGER PRIMARY KEY,
    i_place TEXT,
    j_place TEXT,
    miles DOUBLE PRECISION
);

INSERT INTO distanceTableTwo (id, i_place, j_place, miles) VALUES(1, 'seattle','new-york', 2.5);
INSERT INTO distanceTableTwo (id, i_place, j_place, miles) VALUES(2, 'seattle','chicago', 1.7);
INSERT INTO distanceTableTwo (id, i_place, j_place, miles) VALUES(3, 'seattle','topeka', 1.8);
INSERT INTO distanceTableTwo (id, i_place, j_place, miles) VALUES(4, 'san-diego','new-york', 2.5);
INSERT INTO distanceTableTwo (id, i_place, j_place, miles) VALUES(5, 'san-diego','chicago', 1.8);
INSERT INTO distanceTableTwo (id, i_place, j_place, miles) VALUES(6, 'san-diego', 'topeka', 1.4);

CREATE TABLE notSortedTableOne
(
    id INTEGER PRIMARY KEY,
    i TEXT,
    j TEXT,
    value DOUBLE PRECISION
);

INSERT INTO notSortedTableOne (id, i, j, value) VALUES(1, 'i1','j2', 1.0);
INSERT INTO notSortedTableOne (id, i, j, value) VALUES(2, 'i1','j1', 2.0);
INSERT INTO notSortedTableOne (id, i, j, value) VALUES(3, 'i2','j1', 4.0);
INSERT INTO notSortedTableOne (id, i, j, value) VALUES(4, 'i2','j2', 3.0);

CREATE TABLE notSortedTableTwo
(
    i TEXT,
    j TEXT,
    k1 DOUBLE PRECISION,
    k2 DOUBLE PRECISION
);

INSERT INTO notSortedTableTwo (i, j, k1, k2) VALUES('i1', 'j2', NULL, 1.0);
INSERT INTO notSortedTableTwo (i, j, k1, k2) VALUES('i1', 'j1', 2.0, 3.0);
INSERT INTO notSortedTableTwo (i, j, k1, k2) VALUES('i2', 'j1', 6.0, NULL);
INSERT INTO notSortedTableTwo (i, j, k1, k2) VALUES('i2', 'j2', 4.0, 5.0);
$offEcho

$onEcho > testScript.sql 
CREATE TABLE %sysEnv.CONNECTSQLTABLEPREFIX%scalarTable 
(
    value_col INTEGER PRIMARY KEY
);

INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%scalarTable(value_col) VALUES(653);

CREATE TABLE %sysEnv.CONNECTSQLTABLEPREFIX%parameterTable 
(
    label TEXT,
    value_col1 DOUBLE PRECISION,
    value_col2 DOUBLE PRECISION
);

INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%parameterTable (label, value_col1, value_col2) VALUES('i3', 0.453, 0.532);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%parameterTable (label, value_col1, value_col2) VALUES('i2', 0.638, 0.478);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%parameterTable (label, value_col1, value_col2) VALUES('i1', 0.987, 0.804);

CREATE TABLE %sysEnv.CONNECTSQLTABLEPREFIX%deciTable 
(
    label TEXT,
    value_col1 DOUBLE PRECISION
);

INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%deciTable (label, value_col1) VALUES('i3', 121.12);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%deciTable (label, value_col1) VALUES('i2', 122.22);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%deciTable (label, value_col1) VALUES('i1', 123.32);

CREATE TABLE %sysEnv.CONNECTSQLTABLEPREFIX%setTable
(
    id INTEGER PRIMARY KEY,
    label1 TEXT,
    label2 TEXT,
    expText1 TEXT,
    expText2 TEXT
);

INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%setTable (id, label1, label2, expText1, expText2) VALUES(1, 'i1', 'j1', 'Text1', 'RandomText1');
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%setTable (id, label1, label2, expText1, expText2) VALUES(2, 'i1', 'j2', 'Text2', 'RandomText2');
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%setTable (id, label1, label2, expText1, expText2) VALUES(3, 'i1', 'j3', 'Text3', 'RandomText3');
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%setTable (id, label1, label2, expText1, expText2) VALUES(4, 'i2', 'j1', NULL, 'RandomText4');
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%setTable (id, label1, label2, expText1, expText2) VALUES(5, 'i2', 'j2', 'Text5', 'RandomText5');
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%setTable (id, label1, label2, expText1, expText2) VALUES(6, 'i2', 'j3', NULL, 'RandomText6');

CREATE TABLE %sysEnv.CONNECTSQLTABLEPREFIX%distanceTableOne
(
    id INTEGER PRIMARY KEY,
    source TEXT,
    newyork DOUBLE PRECISION,
    chicago DOUBLE PRECISION,
    topeka DOUBLE PRECISION
);

INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%distanceTableOne (id, source, newyork, chicago, topeka) VALUES(1, 'seattle', 2.5, 1.7, 1.8);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%distanceTableOne (id, source, newyork, chicago, topeka) VALUES(2, 'san-diego', 2.5, 1.8, 1.4);

CREATE TABLE %sysEnv.CONNECTSQLTABLEPREFIX%distanceTableTwo
(
    id INTEGER PRIMARY KEY,
    i_place TEXT,
    j_place TEXT,
    miles DOUBLE PRECISION
);

INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%distanceTableTwo (id, i_place, j_place, miles) VALUES(1, 'seattle','new-york', 2.5);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%distanceTableTwo (id, i_place, j_place, miles) VALUES(2, 'seattle','chicago', 1.7);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%distanceTableTwo (id, i_place, j_place, miles) VALUES(3, 'seattle','topeka', 1.8);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%distanceTableTwo (id, i_place, j_place, miles) VALUES(4, 'san-diego','new-york', 2.5);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%distanceTableTwo (id, i_place, j_place, miles) VALUES(5, 'san-diego','chicago', 1.8);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%distanceTableTwo (id, i_place, j_place, miles) VALUES(6, 'san-diego', 'topeka', 1.4);

CREATE TABLE %sysEnv.CONNECTSQLTABLEPREFIX%notSortedTableOne
(
    id INTEGER PRIMARY KEY,
    i TEXT,
    j TEXT,
    value DOUBLE PRECISION
);

INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%notSortedTableOne (id, i, j, value) VALUES(1, 'i1','j2', 1.0);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%notSortedTableOne (id, i, j, value) VALUES(2, 'i1','j1', 2.0);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%notSortedTableOne (id, i, j, value) VALUES(3, 'i2','j1', 4.0);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%notSortedTableOne (id, i, j, value) VALUES(4, 'i2','j2', 3.0);

CREATE TABLE %sysEnv.CONNECTSQLTABLEPREFIX%notSortedTableTwo
(
    i TEXT,
    j TEXT,
    k1 DOUBLE PRECISION,
    k2 DOUBLE PRECISION
);

INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%notSortedTableTwo (i, j, k1, k2) VALUES('i1', 'j2', NULL, 1.0);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%notSortedTableTwo (i, j, k1, k2) VALUES('i1', 'j1', 2.0, 3.0);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%notSortedTableTwo (i, j, k1, k2) VALUES('i2', 'j1', 6.0, NULL);
INSERT INTO %sysEnv.CONNECTSQLTABLEPREFIX%notSortedTableTwo (i, j, k1, k2) VALUES('i2', 'j2', 4.0, 5.0);
$offEcho

* The following SQL Script creates 5 tables in MS-SQL(SQL Server).
* Two similar(almost) scripts are created because SQL-Server uses T-SQL queries.

$onEcho > SQLServerScript.sql
CREATE TABLE dbo.scalarTable 
(
    value_col INTEGER PRIMARY KEY
);

INSERT INTO dbo.scalarTable(value_col) VALUES(653);


CREATE TABLE dbo.parameterTable 
(
    label TEXT,
    value_col1 DOUBLE PRECISION,
    value_col2 DOUBLE PRECISION
);

INSERT INTO dbo.parameterTable VALUES('i3', 0.453, 0.532);
INSERT INTO dbo.parameterTable VALUES('i2', 0.638, 0.478);
INSERT INTO dbo.parameterTable VALUES('i1', 0.987, 0.804);

CREATE TABLE dbo.deciTable 
(
    label TEXT,
    value_col1 DOUBLE PRECISION
);

INSERT INTO dbo.deciTable (label, value_col1) VALUES('i3', 121.12);
INSERT INTO dbo.deciTable (label, value_col1) VALUES('i2', 122.22);
INSERT INTO dbo.deciTable (label, value_col1) VALUES('i1', 123.32);

CREATE TABLE dbo.setTable
(
    id INTEGER PRIMARY KEY,
    label1 TEXT,
    label2 TEXT,
    expText1 TEXT,
    expText2 TEXT
);

INSERT INTO dbo.setTable VALUES(1, 'i1', 'j1', 'Text1', 'RandomText1');
INSERT INTO dbo.setTable VALUES(2, 'i1', 'j2', 'Text2', 'RandomText2');
INSERT INTO dbo.setTable VALUES(3, 'i1', 'j3', 'Text3', 'RandomText3');
INSERT INTO dbo.setTable VALUES(4, 'i2', 'j1', NULL, 'RandomText4');
INSERT INTO dbo.setTable VALUES(5, 'i2', 'j2', 'Text5', 'RandomText5');
INSERT INTO dbo.setTable VALUES(6, 'i2', 'j3', NULL, 'RandomText6');


CREATE TABLE dbo.distanceTableOne
(
    id INTEGER PRIMARY KEY,
    source TEXT,
    newyork DOUBLE PRECISION,
    chicago DOUBLE PRECISION,
    topeka DOUBLE PRECISION
);

INSERT INTO dbo.distanceTableOne VALUES(1, 'seattle', 2.5, 1.7, 1.8);
INSERT INTO dbo.distanceTableOne VALUES(2, 'san-diego', 2.5, 1.8, 1.4);


CREATE TABLE dbo.distanceTableTwo
(
    id INTEGER PRIMARY KEY,
    i_place TEXT,
    j_place TEXT,
    miles DOUBLE PRECISION
);

INSERT INTO dbo.distanceTableTwo VALUES(1, 'seattle','new-york', 2.5);
INSERT INTO dbo.distanceTableTwo VALUES(2, 'seattle','chicago', 1.7);
INSERT INTO dbo.distanceTableTwo VALUES(3, 'seattle','topeka', 1.8);
INSERT INTO dbo.distanceTableTwo VALUES(4, 'san-diego','new-york', 2.5);
INSERT INTO dbo.distanceTableTwo VALUES(5, 'san-diego','chicago', 1.8);
INSERT INTO dbo.distanceTableTwo VALUES(6, 'san-diego', 'topeka', 1.4);


CREATE TABLE dbo.notSortedTableOne
(
    id INTEGER PRIMARY KEY,
    i TEXT,
    j TEXT,
    value DOUBLE PRECISION
);

INSERT INTO dbo.notSortedTableOne VALUES(1, 'i1','j2', 1.0);
INSERT INTO dbo.notSortedTableOne VALUES(2, 'i1','j1', 2.0);
INSERT INTO dbo.notSortedTableOne VALUES(3, 'i2','j1', 4.0);
INSERT INTO dbo.notSortedTableOne VALUES(4, 'i2','j2', 3.0);


CREATE TABLE dbo.notSortedTableTwo
(
    i TEXT,
    j TEXT,
    k1 DOUBLE PRECISION,
    k2 DOUBLE PRECISION
);

INSERT INTO dbo.notSortedTableTwo (i, j, k1, k2) VALUES('i1', 'j2', NULL, 1.0);
INSERT INTO dbo.notSortedTableTwo (i, j, k1, k2) VALUES('i1', 'j1', 2.0, 3.0);
INSERT INTO dbo.notSortedTableTwo (i, j, k1, k2) VALUES('i2', 'j1', 6.0, NULL);
INSERT INTO dbo.notSortedTableTwo (i, j, k1, k2) VALUES('i2', 'j2', 4.0, 5.0);
$offEcho


* Executing SQL Scripts
$onEmbeddedCode Python:
import sqlite3, psycopg2, pymysql, pymssql
import os

# Create test schemas for server based databases  
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()


# Executing prepared scripts
with sqlite3.connect(**{'database': f'{schemaName}.db'}) as conn:
    with open('sqlitescript.sql', 'r') as sql_file:
        sql_script = sql_file.read()
        cur = conn.cursor()
        cur.executescript(sql_script)
        conn.commit()
        cur.close()
    
with open('testScript.sql', 'r') as sql_file:
    sql_script = sql_file.read()
    sql_script_stripped = sql_script.split(";")
    sql_script_stripped.pop()
    
with psycopg2.connect(**{'user':'postgres', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 22000, 'host': 'internal2.gams.com', 'database': 'testdb'}) as conn:
    cur = conn.cursor()
    cur.execute(sql_script)
    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()
    for ele in sql_script_stripped:
        cur.execute(ele+ ";")
    conn.commit()
    cur.close()

with open('SQLServerScript.sql', 'r') as sql_file:
    sql_script = sql_file.read()
    
with pymssql.connect(**{'user':'sa', 'password': '%sysEnv.GCONNECTSQLDBPASSWORD%','port': 23000, 'host': 'internal2.gams.com', 'database': schemaName}) as conn:
    cur = conn.cursor()
    cur.execute(sql_script)
    conn.commit()
    cur.close()
$offEmbeddedCode


$onEchoV > readTest.gms  
$log %CONNECTIONTYPE% test connection dictionary missing
$onEmbeddedCode Connect:
- SQLReader:
    symbols:
      - name: scalarTable
        query: "SELECT * FROM %TABLEPREFIX%scalarTable;"
        valueColumns: "lastCol"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log %CONNECTIONTYPE% test name missing
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - query: "SELECT * FROM %TABLEPREFIX%scalarTable;"
        valueColumns: "lastCol"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log %CONNECTIONTYPE% test SQL query missing
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: scalarTable
        valueColumns: "lastCol"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log %CONNECTIONTYPE% test type option "set" missing
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: setTable
        query: "SELECT * FROM %TABLEPREFIX%setTable;"
        valueColumns: "lastCol"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

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

$log %CONNECTIONTYPE% reading parameter
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: parameterTable
        query: "SELECT label, value_col1 FROM %TABLEPREFIX%parameterTable;"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["parameterTable"].records.values.tolist()
        expected = [['i3', 0.453],
                    ['i2', 0.638],
                    ['i1', 0.987]]
        if data != expected:
            raise Exception("Unexpected data parameterTable.")
$offEmbeddedCode

$log %CONNECTIONTYPE% reading parameter with dTypeMap
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: deciTable
        query: "SELECT label, value_col1 FROM %TABLEPREFIX%deciTable;"
        dTypeMap: {'value_col1': 'int64'}
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["deciTable"].records.values.tolist()
        expected = [['i3', 121],
                    ['i2', 122],
                    ['i1', 123]]
        if data != expected:
            raise Exception("Unexpected data deciTable.")
$offEmbeddedCode

$log %CONNECTIONTYPE% reading set with SQLAlchemy
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICTSQLALCHEMY%
    connectionType: sqlalchemy
    symbols:
      - name: setTable
        query: "SELECT label1, label2, expText1 FROM %TABLEPREFIX%setTable;"
        type: set
        valueColumns: "lastCol"
        valueSubstitutions: {.NaN: ""}
- PythonCode:
    code: |
        data = connect.container.data["setTable"].records.values.tolist()
        expected = [['i1', 'j1', 'Text1'],
                    ['i1', 'j2', 'Text2'],
                    ['i1', 'j3', 'Text3'],
                    ['i2', 'j1', ''],
                    ['i2', 'j2', 'Text5'],
                    ['i2', 'j3', '']]
        if data != expected:
            raise Exception("Unexpected data setTable.")     
$offEmbeddedCode


$log %CONNECTIONTYPE% reading set without explanatoryText
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: setTable
        query: "SELECT * FROM %TABLEPREFIX%setTable;"
        type: set
        valueColumns: none
- PythonCode:
    code: |
        data = connect.container.data["setTable"].records.values.tolist()
        expected = [['1', 'i1', 'j1', 'Text1', 'RandomText1', ''],
                    ['2', 'i1', 'j2', 'Text2', 'RandomText2', ''],
                    ['3', 'i1', 'j3', 'Text3', 'RandomText3', ''],
                    ['5', 'i2', 'j2', 'Text5', 'RandomText5', '']]
        if data != expected:
            raise Exception("Unexpected data setTable.") 
$offEmbeddedCode


$log %CONNECTIONTYPE% reading set without explanatoryText and substituting NULL values with Empty string
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: setTable
        query: "SELECT * FROM %TABLEPREFIX%setTable;"
        type: set
        valueColumns: none
        indexSubstitutions: {.NaN: ""}
- PythonCode:
    code: |
        data = connect.container.data["setTable"].records.values.tolist()
        expected = [['1', 'i1', 'j1', 'Text1', 'RandomText1', ''],
                    ['2', 'i1', 'j2', 'Text2', 'RandomText2', ''],
                    ['3', 'i1', 'j3', 'Text3', 'RandomText3', ''],
                    ['4', 'i2', 'j1', '', 'RandomText4', ''],
                    ['5', 'i2', 'j2', 'Text5', 'RandomText5', ''],
                    ['6', 'i2', 'j3', '', 'RandomText6', ''],]
        if data != expected:
            raise Exception("Unexpected data setTable.") 
$offEmbeddedCode


$log %CONNECTIONTYPE% reading 1-dim parameter from 2-dim data with SELECT query
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: distanceTableOne
        query: "SELECT source, newyork FROM %TABLEPREFIX%distanceTableOne;"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["distanceTableOne"].records.values.tolist()
        expected = [['seattle', 2.5],
                    ['san-diego', 2.5]]
        if data != expected:
            raise Exception("Unexpected data distanceTableOne.") 
$offEmbeddedCode


$log %CONNECTIONTYPE% Substituting multi-level indices through local indexSubstitutions
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: distanceTableOne_indexSub
        query: "SELECT * FROM distanceTableOne;"
        valueColumns: ["newyork", "chicago", "topeka"]
        indexSubstitutions: {'san-diego': 'detroit', 'newyork': 'miami'}
- PythonCode:
    code: |
        data = connect.container.data["distanceTableOne_indexSub"].records.values.tolist()
        expected = [['1', 'seattle', 'miami', 2.5], 
                    ['1', 'seattle', 'chicago', 1.7], 
                    ['1', 'seattle', 'topeka', 1.8], 
                    ['2', 'detroit', 'miami', 2.5], 
                    ['2', 'detroit', 'chicago', 1.8], 
                    ['2', 'detroit', 'topeka', 1.4]]
        if data != expected:
            raise Exception("Unexpected data distanceTableOne_indexSub.")
$offEmbeddedCode


$log %CONNECTIONTYPE% Substituting indices through global indexSubstitutions
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    indexSubstitutions: {'san-diego': 'detroit', 'newyork': 'miami', 'i2': 'i3', .NaN: ''}
    symbols:
      - name: distanceTableOne_indexSub
        query: "SELECT * FROM distanceTableOne;"
        valueColumns: ["newyork", "chicago", "topeka"]
      - name: setTable_indexSub
        query: "SELECT * FROM setTable;"
        type: set
        valueColumns: none
- PythonCode:
    code: |
        data1 = connect.container.data["distanceTableOne_indexSub"].records.values.tolist()
        expected1 = [['1', 'seattle', 'miami', 2.5], 
                    ['1', 'seattle', 'chicago', 1.7], 
                    ['1', 'seattle', 'topeka', 1.8], 
                    ['2', 'detroit', 'miami', 2.5], 
                    ['2', 'detroit', 'chicago', 1.8], 
                    ['2', 'detroit', 'topeka', 1.4]]
        if data1 != expected1:
            raise Exception("Unexpected data distanceTableOne_indexSub.")
        data2 = connect.container.data["setTable_indexSub"].records.values.tolist()
        expected2 = [['1', 'i1', 'j1', 'Text1', 'RandomText1', ''],
                    ['2', 'i1', 'j2', 'Text2', 'RandomText2', ''],
                    ['3', 'i1', 'j3', 'Text3', 'RandomText3', ''],
                    ['4', 'i3', 'j1', '', 'RandomText4', ''],
                    ['5', 'i3', 'j2', 'Text5', 'RandomText5', ''], 
                    ['6', 'i3', 'j3', '', 'RandomText6', '']]
        if data2 != expected2:
            raise Exception("Unexpected data setTable_indexSub.")
$offEmbeddedCode


$log %CONNECTIONTYPE% Substituting values through global valueSubstitutions
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    valueSubstitutions: {653: 700, 122.22: 400}
    symbols:
      - name: scalarTable_valueSub
        query: "SELECT * FROM scalarTable;"
      - name: deciTable_valueSub
        query: "SELECT label, value_col1 FROM deciTable;"
- PythonCode:
    code: |
        data1 = connect.container.data["scalarTable_valueSub"].records.values.tolist()
        expected1 = [[700.0]]
        if data1 != expected1:
            raise Exception("Unexpected data scalarTable_valueSub.")
        data2 = connect.container.data["deciTable_valueSub"].records.values.tolist()
        expected2 = [['i3', 121.12], ['i2', 400.0], ['i1', 123.32]]
        if data2 != expected2:
            raise Exception("Unexpected data deciTable_valueSub.")
$offEmbeddedCode


$log %CONNECTIONTYPE% reading scalar from 2-dim data with SELECT query
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: distanceTableOne
        query: "SELECT newyork FROM %TABLEPREFIX%distanceTableOne WHERE distanceTableOne.source LIKE 'seattle';"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["distanceTableOne"].records.values.tolist()
        expected = [[2.5]]
        if data != expected:
            raise Exception("Unexpected data distanceTableOne.") 
$offEmbeddedCode


$log %CONNECTIONTYPE% reading 2-dim parameter from 2-dim data
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: distanceTableTwo
        query: "SELECT i_place, j_place, miles FROM %TABLEPREFIX%distanceTableTwo;"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["distanceTableTwo"].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 distanceTableTwo.") 
$offEmbeddedCode

$log test SQLReader create categorical but never sort and SQLWriter always sort
$log %CONNECTIONTYPE% reading 2-dim parameter from 2-dim data
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: notSortedTableOne
        query: "SELECT i, j, value FROM %TABLEPREFIX%notSortedTableOne;"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        # checks that categoricals are correct
        sym = connect.container.data["notSortedTableOne"]
        data_sorted = sym.records.sort_values(sym.records.columns[:-1].tolist()).values.tolist()
        expected = [['i1', 'j2', 1.0],
                    ['i1', 'j1', 2.0],
                    ['i2', 'j2', 3.0],
                    ['i2', 'j1', 4.0]]
        if data_sorted != expected:
            raise Exception("Unexpected sorted data notSortedTableOne.")
        
        # reorder UELs to check that SQLWriter sorts
        sym.reorderUELs(uels=['j1', 'j2'], dimensions=1)
- SQLWriter:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    schemaName: %SCHEMASUFFIX%
    symbols:
      - name: notSortedTableOne
        tableName: SortedTableOne
        ifExists: "replace"
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: SortedTableOne
        query: "SELECT * FROM %TABLEPREFIX%SortedTableOne;"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        # checks that SQLWriter sorts
        sym = connect.container.data["SortedTableOne"]
        data_sorted = sym.records.sort_values(sym.records.columns[:-1].tolist()).values.tolist()
        expected = [['i1', 'j1', 2.0],
                    ['i1', 'j2', 1.0],
                    ['i2', 'j1', 4.0],
                    ['i2', 'j2', 3.0]]
        if data_sorted != expected:
            raise Exception("Unexpected sorted data SortedTableOne.")
$offEmbeddedCode

$log %CONNECTIONTYPE% reading 3-dim parameter from 3-dim data
$onEmbeddedCode Connect:
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: notSortedTableTwo
        query: "SELECT i, j, k1, k2 FROM %TABLEPREFIX%notSortedTableTwo;"
        valueColumns: ["k1", "k2"]
- PythonCode:
    code: |
        # checks that categoricals are correct
        sym = connect.container.data["notSortedTableTwo"]
        data_sorted = sym.records.sort_values(sym.records.columns[:-1].tolist()).values.tolist()
        expected = [['i1', 'j2', 'k2', 1.0],
                    ['i1', 'j1', 'k1', 2.0],
                    ['i1', 'j1', 'k2', 3.0],
                    ['i2', 'j2', 'k1', 4.0],
                    ['i2', 'j2', 'k2', 5.0],
                    ['i2', 'j1', 'k1', 6.0]]
        if data_sorted != expected:
            raise Exception("Unexpected sorted data notSortedTableTwo.")

        # reorder UELs to check that SQLWriter sorts
        sym.reorderUELs(uels=['i2', 'i1'], dimensions=0)
        sym.reorderUELs(uels=['j1', 'j2'], dimensions=1)
        sym.reorderUELs(uels=['k2', 'k1'], dimensions=2)
- SQLWriter:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    schemaName: %SCHEMASUFFIX%
    symbols:
      - name: notSortedTableTwo
        tableName: SortedTableTwo
        ifExists: "replace"
        unstack: True
- SQLReader:
    connection: %CONNECTIONDICT%
    connectionType: %CONNECTIONTYPE%
    symbols:
      - name: SortedTableTwo
        query: "SELECT * FROM %TABLEPREFIX%SortedTableTwo;"
        valueColumns: ["k1", "k2"]
- PythonCode:
    code: |
        # checks that SQLWriter sorts
        sym = connect.container.data["SortedTableTwo"]
        data_sorted = sym.records.sort_values(sym.records.columns[:-1].tolist()).values.tolist()
        expected = [['i2', 'j1', 'k1', 6.0],
                    ['i2', 'j2', 'k2', 5.0],
                    ['i2', 'j2', 'k1', 4.0],
                    ['i1', 'j1', 'k2', 3.0],
                    ['i1', 'j1', 'k1', 2.0],
                    ['i1', 'j2', 'k2', 1.0]]
        if data_sorted != expected:
            raise Exception("Unexpected sorted data SortedTableTwo.")
$offEmbeddedCode
$offEcho


$onEchoV.access > readAccessTest.gms
$call rm -f testdb.accdb
$call rm -f distanceTableOne.csv
$call rm -f distanceTableTwo.csv
$call rm -f parameterTable.csv
$call rm -f deciTable.csv
$call rm -f scalarTable.csv
$call rm -f setTable.csv

$onEcho > distanceTableOne.csv
id,source,newyork,chicago,topeka
1,seattle,2.5,1.7,1.8
2,san-diego,2.5,1.8,1.4
$offEcho

$onEcho > distanceTableTwo.csv
id,i_place,j_place,miles
1,seattle,new-york,2.5
2,seattle,chicago,1.7
3,seattle,topeka,1.8
4,san-diego,new-york,2.5
5,san-diego,chicago,1.8
6,san-diego,topeka,1.4
$offEcho

$onEcho > parameterTable.csv
label,value_col1,value_col2
i3,0.453,0.532
i2,0.638,0.478
i1,0.987,0.804
$offEcho

$onEcho > deciTable.csv
label,value_col1
i3,121.12
i2,122.22
i1,123.32
$offEcho

$onEcho > scalarTable.csv
value_col
653
$offEcho

$onEcho > setTable.csv
id,label1,label2,expText1,expText2
1,i1,j1,Text1,RandomText1
2,i1,j2,Text2,RandomText2
3,i1,j3,Text3,RandomText3
4,i2,j1,,RandomText4
5,i2,j2,Text5,RandomText5
6,i2,j3,,RandomText6
$offEcho
 

$onEmbeddedCode Python:
import win32com.client as win32
import pyodbc
Access = win32.Dispatch("Access.Application")
#Create a new .accdb file, this takes care of dropping old existing tables
Access.NewCurrentDataBase(r"%system.fp%testdb.accdb") 
Access.CloseCurrentDataBase()
Access.Quit()
del Access

with pyodbc.connect(**{'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': r'%system.fp%testdb.accdb'}) as conn:
    cur = conn.cursor()
    csvs = ['distanceTableOne', 'distanceTableTwo', 'parameterTable', 'deciTable', 'scalarTable', 'setTable']
    for file in csvs:
        cur.execute(r"SELECT * INTO [{0}] FROM [text;HDR=Yes;FMT=Delimited(,);Database=%system.fp%].{0}.csv;".format(file))
    conn.commit()
    cur.close()
$offEmbeddedCode


$log test connection dictionary missing
$onEmbeddedCode Connect:
- SQLReader:
    connectionType: access
    symbols:
      - name: scalarTable
        query: "SELECT * FROM scalarTable;"
        valueColumns: "lastCol"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test wrong connection type (default: sqlite)
$onEmbeddedCode Connect:
- SQLReader:
    connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
    connectionType: wrongpyodbc
    symbols:
      - name: scalarTable
        query: "SELECT * FROM scalarTable;"
        valueColumns: "lastCol"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test name missing
$onEmbeddedCode Connect:
- SQLReader:
    connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
    connectionType: access
    symbols:
      - query: "SELECT * FROM scalarTable;"
        valueColumns: "lastCol"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test SQL query missing
$onEmbeddedCode Connect:
- SQLReader:
    connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
    connectionType: access
    symbols:
      - name: scalarTable
        valueColumns: "lastCol"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log test type option "set" missing
$onEmbeddedCode Connect:
- SQLReader:
    connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
    connectionType: access
    symbols:
      - name: setTable
        query: "SELECT * FROM setTable;"
        valueColumns: "lastCol"
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log reading scalar
$onEmbeddedCode Connect:
- SQLReader:
    connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
    connectionType: access
    symbols:
      - name: scalarTable
        query: "SELECT * FROM scalarTable;"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["scalarTable"].records.values.tolist()
        expected = [[653.0]]
        if data != expected:
            raise Exception("Unexpected data scalarTable.")
$offEmbeddedCode


$log reading parameter
$onEmbeddedCode Connect:
- SQLReader:
    connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
    connectionType: access
    symbols:
      - name: parameterTable
        query: "SELECT label, value_col1 FROM parameterTable;"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["parameterTable"].records.values.tolist()
        expected = [['i3', 0.453],
                    ['i2', 0.638],
                    ['i1', 0.987]]
        if data != expected:
            raise Exception("Unexpected data parameterTable.")
$offEmbeddedCode

$log reading parameter with dTypeMap
$onEmbeddedCode Connect:
- SQLReader:
    connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
    connectionType: access
    symbols:
      - name: deciTable
        query: "SELECT label, value_col1 FROM deciTable;"
        dTypeMap: {'value_col1': 'int64'}
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["deciTable"].records.values.tolist()
        expected = [['i3', 121],
                    ['i2', 122],
                    ['i1', 123]]
        if data != expected:
            raise Exception("Unexpected data deciTable.")
$offEmbeddedCode


$log reading set with explanatoryText
$onEmbeddedCode Connect:
- SQLReader:
    connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
    connectionType: access
    symbols:
      - name: setTable
        query: "SELECT label1, label2, expText1 FROM setTable;"
        type: set
        valueColumns: "lastCol"
        valueSubstitutions: {.NaN: ""}
- PythonCode:
    code: |
        data = connect.container.data["setTable"].records.values.tolist()
        expected = [['i1', 'j1', 'Text1'],
                    ['i1', 'j2', 'Text2'],
                    ['i1', 'j3', 'Text3'],
                    ['i2', 'j1', ''],
                    ['i2', 'j2', 'Text5'],
                    ['i2', 'j3', '']]
        if data != expected:
            raise Exception("Unexpected data setTable.")     
$offEmbeddedCode


$log reading set without explanatoryText
$onEmbeddedCode Connect:
- SQLReader:
    connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
    connectionType: access
    symbols:
      - name: setTable
        query: "SELECT * FROM setTable;"
        type: set
        valueColumns: none
- PythonCode:
    code: |
        data = connect.container.data["setTable"].records.values.tolist()
        expected = [['1', 'i1', 'j1', 'Text1', 'RandomText1', ''],
                    ['2', 'i1', 'j2', 'Text2', 'RandomText2', ''],
                    ['3', 'i1', 'j3', 'Text3', 'RandomText3', ''],
                    ['5', 'i2', 'j2', 'Text5', 'RandomText5', '']]
        if data != expected:
            raise Exception("Unexpected data setTable.") 
$offEmbeddedCode


$log reading set without explanatoryText and substituting NULL values with Empty string
$onEmbeddedCode Connect:
- SQLReader:
    connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
    connectionType: access
    symbols:
      - name: setTable
        query: "SELECT * FROM setTable;"
        type: set
        valueColumns: none
        indexSubstitutions: {.NaN: ""}
- PythonCode:
    code: |
        data = connect.container.data["setTable"].records.values.tolist()
        expected = [['1', 'i1', 'j1', 'Text1', 'RandomText1', ''],
                    ['2', 'i1', 'j2', 'Text2', 'RandomText2', ''],
                    ['3', 'i1', 'j3', 'Text3', 'RandomText3', ''],
                    ['4', 'i2', 'j1', '', 'RandomText4', ''],
                    ['5', 'i2', 'j2', 'Text5', 'RandomText5', ''],
                    ['6', 'i2', 'j3', '', 'RandomText6', ''],]
        if data != expected:
            raise Exception("Unexpected data setTable.") 
$offEmbeddedCode


$log reading 1-dim parameter from 2-dim data with SELECT query
$onEmbeddedCode Connect:
- SQLReader:
    connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
    connectionType: access
    symbols:
      - name: distanceTableOne
        query: "SELECT source, newyork FROM distanceTableOne;"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["distanceTableOne"].records.values.tolist()
        expected = [['seattle', 2.5],
                    ['san-diego', 2.5]]
        if data != expected:
            raise Exception("Unexpected data distanceTableOne.") 
$offEmbeddedCode


$log reading scalar from 2-dim data with SELECT query
$onEmbeddedCode Connect:
- SQLReader:
    connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
    connectionType: access
    symbols:
      - name: distanceTableOne
        query: "SELECT newyork FROM distanceTableOne WHERE distanceTableOne.source LIKE 'seattle';"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["distanceTableOne"].records.values.tolist()
        expected = [[2.5]]
        if data != expected:
            raise Exception("Unexpected data distanceTableOne.") 
$offEmbeddedCode


$log reading 2-dim parameter from 2-dim data
$onEmbeddedCode Connect:
- SQLReader:
    connection: {'DRIVER':'Microsoft Access Driver (*.mdb, *.accdb)', 'DBQ': '%system.fp%testdb.accdb'}
    connectionType: access
    symbols:
      - name: distanceTableTwo
        query: "SELECT i_place, j_place, miles FROM distanceTableTwo;"
        valueColumns: "lastCol"
- PythonCode:
    code: |
        data = connect.container.data["distanceTableTwo"].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 distanceTableTwo.") 
$offEmbeddedCode
$offEcho.access

$onEcho > connect.txt
eolonly 1
--SCHEMASUFFIX Null
--TABLEPREFIX
--CONNECTIONTYPE sqlite
--CONNECTIONDICT {'database': '%sysEnv.CONNECTSQLSCHEMASUFFIX%.db'}
--CONNECTIONDICTSQLALCHEMY {'drivername': 'sqlite', 'database': '%sysEnv.CONNECTSQLSCHEMASUFFIX%.db'}
$offEcho
$log testing sqlite
$call gams readTest.gms lo=%gams.lo% pf=connect.txt
$ifE errorLevel<>0 $goTo cleanup

$onEcho > connect.txt
eolonly 1
--SCHEMASUFFIX %sysEnv.CONNECTSQLSCHEMASUFFIX%
--TABLEPREFIX %sysEnv.CONNECTSQLTABLEPREFIX%
--CONNECTIONTYPE mysql
--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'}
$offEcho
$log testing mysql
$call gams readTest.gms lo=%gams.lo% pf=connect.txt 
$ifE errorLevel<>0 $goTo cleanup

$onEcho > connect.txt
eolonly 1
--SCHEMASUFFIX %sysEnv.CONNECTSQLSCHEMASUFFIX%
--TABLEPREFIX %sysEnv.CONNECTSQLTABLEPREFIX%
--CONNECTIONTYPE postgres
--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'}
$offEcho
$log testing postgres
$call gams readTest.gms lo=%gams.lo% pf=connect.txt
$ifE errorLevel<>0 $goTo cleanup

$onEcho > connect.txt
eolonly 1
--SCHEMASUFFIX Null
--TABLEPREFIX dbo.
--CONNECTIONTYPE sqlserver
--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'}
$offEcho
$log testing sqlserver
$call gams readTest.gms lo=%gams.lo% pf=connect.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 readAccessTest.gms lo=%gams.lo%
*$ifE sameas('%system.FileSys%','MSNT')and(not(sameas('%system.computername%','KERMIT'))) $call.checkErrorLevel gams readAccessTest.gms lo=%gams.lo%

$log Test that empty table reads as an empty symbol (Not None)
$onEmbeddedCode Connect:
- PythonCode:
    code: |
      import sqlite3
      
      conn = sqlite3.connect('%sysEnv.CONNECTSQLSCHEMASUFFIX%.db')
      cursor = conn.cursor()
      
      create_table = """
      CREATE TABLE %sysEnv.CONNECTSQLSCHEMASUFFIX%emptyTable (
          id INTEGER PRIMARY KEY,
          name TEXT,
          age INTEGER
      );
      """
      
      cursor.execute(create_table)
      conn.commit()
      conn.close()

- SQLReader:
    connection: {"database": "%sysEnv.CONNECTSQLSCHEMASUFFIX%.db"}
    symbols:
      - name: d
        query: "SELECT * FROM %sysEnv.CONNECTSQLSCHEMASUFFIX%emptyTable;"
        type: par

- PythonCode:
    code: |
      data = connect.container['d'].records
      expected_cols = [0, 1, 'value']
    
      if data is None or not data.empty:
        raise Exception("Expected >d< to have an empty DataFrame.")

      if (data.columns != expected_cols).any():
        raise Exception("Unexpected columns for >d<.")
$offEmbeddedCode

$if not errorFree $abort 'Errors'
$log Test raising an exception for existing symbol name
$onEmbeddedCode Connect:
- PythonCode:
    code: |
      connect.container.addSet("i")
      connect.container.addSet("j")
      connect.container.addParameter("d", domain=["i", "j"])
- PythonCode:
    code: |
      import sqlite3
      
      conn = sqlite3.connect('%sysEnv.CONNECTSQLSCHEMASUFFIX%.db')
      cursor = conn.cursor()
      
      create_table = """
      CREATE TABLE %sysEnv.CONNECTSQLSCHEMASUFFIX%existingSymbol (
          i INTEGER PRIMARY KEY,
          j TEXT,
          age INTEGER
      );
      """
      cursor.execute(create_table)
      conn.commit()
      conn.close()
- SQLReader:
    connection: {"database": "%sysEnv.CONNECTSQLSCHEMASUFFIX%.db"}
    symbols:
      - name: d
        query: "SELECT * FROM %sysEnv.CONNECTSQLSCHEMASUFFIX%existingSymbol;"
        type: par
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors