-- DROP SCHEMA x_link;

CREATE SCHEMA x_link AUTHORIZATION postgres;

-- x_link.db_connection definition

-- Drop table

-- DROP TABLE x_link.db_connection;

CREATE TABLE x_link.db_connection (
	id serial4 NOT NULL,
	"name" varchar NOT NULL,
	url varchar NULL,
	CONSTRAINT db_connection_pk PRIMARY KEY (id),
	CONSTRAINT db_connection_unique_name UNIQUE (name)
);

-- DROP FUNCTION x_link."_execute"(text, text);

--DROP FUNCTION x_link."_execute"(text, text);

CREATE OR REPLACE FUNCTION x_link._execute(conn_name text, sql_query text,params json default null)
 RETURNS boolean
 LANGUAGE plpython3u
AS $function$
import sqlalchemy
from sqlalchemy import text
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
import psycopg2.extras
import plpy

import plpy
import json


query = f"SELECT url FROM x_link.db_connection WHERE name = '{conn_name}'"
result = plpy.execute(query)
if len(result) == 0:
    return []
conn_url = result[0]['url']
engine = create_engine(conn_url)
Session = sessionmaker(bind=engine)
session = Session()

try:
    if params is not None:
       session.execute(text(sql_query.replace('??',':')),dict(json.loads(params)))
    else:
        session.execute(text(sql_query))
    session.commit()

except:
    session.close()
    return False
finally:
    session.close()
return True
$function$
;

-- DROP FUNCTION x_link."_select"(text, text, json);

CREATE OR REPLACE FUNCTION x_link._select(conn_name text, sql_query text, params json DEFAULT NULL::json)
 RETURNS SETOF record
 LANGUAGE plpython3u
AS $function$
import sqlalchemy
from sqlalchemy import text
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
import psycopg2.extras
import plpy

import plpy
import json

if 'connections' not in globals():
    global connections
    connections = {}
else:
    connections

if conn_name not in connections:
    query = f"SELECT url FROM x_link.db_connection WHERE name = '{conn_name}'"
    result = plpy.execute(query)
    if len(result) == 0:
        return 
    conn_url = result[0]['url']
    engine = create_engine(conn_url)
    Session = sessionmaker(bind=engine)
    session = Session()
    connections[conn_name] = session
else:
    session = connections[conn_name]
try:
    if params is not None:
        result_set = session.execute(text(sql_query.replace('??',':')),dict(json.loads(params)))
    else:
        result_set = session.execute(text(sql_query))
    for row in result_set:
         yield(row)

finally:
    session.close()
return 
$function$
;
