$title 'Simple Connect Example for SQL' (CONNECT04,SEQ=147)

$onText
This model uses GAMS Connect to read and write to SQL

Contributor: Vaibhavnath Jha, July 2022
$offText

$onEchoV > whouse.sql
DROP TABLE IF EXISTS [timeTable];
DROP TABLE IF EXISTS [priceTable];
DROP TABLE IF EXISTS [iniStockTable];
DROP TABLE IF EXISTS [storeCostTable];
DROP TABLE IF EXISTS [storeCapTable];

CREATE TABLE [timeTable]
(
    [t_qrt] NVARCHAR(15) PRIMARY KEY
);
INSERT INTO [timeTable] ([t_qrt]) VALUES("q-1");
INSERT INTO [timeTable] ([t_qrt]) VALUES("q-2");
INSERT INTO [timeTable] ([t_qrt]) VALUES("q-3");
INSERT INTO [timeTable] ([t_qrt]) VALUES("q-4");

CREATE TABLE [priceTable]
(
    [t_qrt] NVARCHAR(15) PRIMARY KEY,
    [price] NUMERIC(10,3)
);
INSERT INTO [priceTable] ([t_qrt], [price]) VALUES("q-1", 10);
INSERT INTO [priceTable] ([t_qrt], [price]) VALUES("q-2", 12);
INSERT INTO [priceTable] ([t_qrt], [price]) VALUES("q-3", 8);
INSERT INTO [priceTable] ([t_qrt], [price]) VALUES("q-4", 9);

CREATE TABLE [iniStockTable]
(
    [t_qrt] NVARCHAR(15) PRIMARY KEY,
    [stock] NUMERIC(10,3)
);
INSERT INTO [iniStockTable] ([t_qrt], [stock]) VALUES("q-1", 50);

CREATE TABLE [storeCostTable] 
(
    [cost] NUMERIC(5, 4) PRIMARY KEY
);
INSERT INTO [storeCostTable] ([cost]) VALUES(1);

CREATE TABLE [storeCapTable] 
(
    [cap] NUMERIC(5, 4) PRIMARY KEY
);
INSERT INTO [storeCapTable] ([cap]) VALUES(100);
$offEcho

$onEmbeddedCode Python:
import sqlite3

with open('whouse.sql', 'r') as sql_file:
    sql_script = sql_file.read()

with sqlite3.connect('whouse.db') as sqliteConnection:    
    cursor = sqliteConnection.cursor()
    cursor.executescript(sql_script)
    cursor.close()
$offEmbeddedCode

Set t 'time in quarters';

Parameter
   price(t)  'selling price ($ per unit)'
   istock(t) 'initial stock      (units)';

Scalar
   storecost 'storage cost  ($ per quarter per unit)'
   storecap  'stocking capacity of warehouse (units)';

$onEmbeddedCode Connect:
- SQLReader:
    connection: {"database": "whouse.db"}
    symbols:
      - name: t
        query: "SELECT * FROM timeTable;"
        type: set
      - name: price
        query: "SELECT * FROM priceTable;"
      - name: istock
        query: "SELECT * FROM iniStockTable;"
      - name: storecost
        query: "SELECT * FROM storeCostTable;"
      - name: storecap
        query: "SELECT * FROM storeCapTable;"
- GAMSWriter:
    symbols: all
$offEmbeddedCode

Variable
   stock(t)  'stock stored at time t (units)'
   sell(t)   'stock sold at time t   (units)'
   buy(t)    'stock bought at time t (units)'
   cost      'total cost                 ($)';
Positive Variable stock, sell, buy;

Equation
   sb(t)     'stock balance at time t (units)'
   at        'accounting: total cost      ($)';

sb(t).. stock(t) =e= stock(t-1) + buy(t) - sell(t)  + istock(t);

at..    cost =e= sum(t, price(t)*(buy(t) - sell(t)) + storecost*stock(t));

stock.up(t) = storecap;

Model swp 'simple warehouse problem' / all /;

solve swp minimizing cost using lp;

embeddedCode Connect:
- GAMSReader:
    symbols: all
- Projection:
    name: stock.l(t)
    newName: stock_level(t)
- Projection:
    name: sell.l(t)
    newName: sell_level(t)
- Projection:
    name: buy.l(t)
    newName: buy_level(t)
- SQLWriter:
    connection: {"database": "whouse.db"}
    ifExists: replace
    symbols:
      - name: stock_level
        tableName: stock_level
      - name: sell_level
        tableName: sell_level
      - name: buy_level
        tableName: buy_level
endEmbeddedCode