#ifndef UDO_PGDB
#define UDO_PGDB ##
/*
	PostgreSQL connection and tools

	This file is part of the SONICS UDO collection by Richard Knight 2021, 2022
		License: GPL-2.0-or-later
		http://1bpm.net
*/

#include "__config__.udo"
#define USING_DB ##

gidb dbconnect "postgresql", "$PGDB_HOST", "$PGDB_NAME", "$PGDB_USER", "$PGDB_PASSWORD"


/*
	Escape a SQL string

	Soutput sqlescape Sinput

	Sinput		string to be escaped
	Soutput		escaped string
	
*/
opcode pgdb_sqlescape, S, S
    Sdata xin
    Sout = ""
    ilen = strlen(Sdata)
    index = 0
    while (index < ilen) do
        Schar = strsub(Sdata, index, index+1)
        if (strcmp(Schar, "'") == 0) then
            Sout = strcat(Sout, "''")
        else
            Sout = strcat(Sout, Schar)
        endif
        index += 1
    od
    xout Sout
endop

/*
	Escape a SQL string at k-rate

	Soutput sqlescape Sinput

	Sinput		string to be escaped
	Soutput		escaped string
	
*/
opcode pgdb_sqlescapek, S, S
    Sdata xin
    Sout = ""
    klen = strlenk(Sdata)
    kindex init 0
    while (kindex < klen) do
        Schar = strsubk(Sdata, kindex, kindex+1)
        if (strcmpk(Schar, "'") == 0) then
            Sout = strcatk(Sout, "''")
        else
            Sout = strcatk(Sout, Schar)
        endif
        kindex += 1
    od
    xout Sout
endop


/*
	Save a JSON object to the database with a given name and unit

	pgdb_json_save Sname, Sunit, iJson

	Sname		name of object
	Sunit		category of object
	iJson 		JSON object
*/
opcode pgdb_json_save, 0, SSi
	Sname, Sunit, iJson xin
	Squery = sprintf("DELETE FROM savejson WHERE name = '%s' AND unit = '%s'; INSERT INTO savejson (name, unit, data, created) VALUES ('%s', '%s', '%s', current_timestamp)",\
		Sname, Sunit, Sname, Sunit, jsondumps(iJson, 0)\
	)
	dbexec gidb, Squery
endop


/*
	Load a JSON object from the database

	iJson pgdb_json_load Sname, Sunit

	iJson		the JSON object if successful, otherwise -1 if the requested data does not exist
	Sname		name of saved object
	Sunit		category of saved object
	
*/
opcode pgdb_json_load, i, SS
	Sname, Sunit xin
	Squery = sprintf("SELECT data::text FROM savejson WHERE name = '%s' AND unit = '%s' UNION SELECT 'void'", Sname, Sunit)
	Sresult dbscalar gidb, Squery
	if (strcmp(Squery, "void") == 0) then
		iJson = -1
	else
		iJson = jsonloads(Sresult)
	endif
	xout iJson
endop






/*
	Save an array to the database with a given name

	pgdb_array_save Sname, Sunit, iarray[]

	Sname		name of the save data
	Sunit		unit to associate save data with
	iarray[]	the array to save

*/
opcode pgdb_array_save, 0, SSi[]
	Sname, Sunit, iarray[] xin
	Sname = pgdb_sqlescape(Sname)
	Sunit = pgdb_sqlescape(Sunit)
	dbexec gidb, sprintf("DELETE FROM savearray WHERE name = '%s' AND unit = '%s'", Sname, Sunit)
	ilen = lenarray(iarray)
	index = 0
	Sdata = ""
	while (index < ilen) do
		if (index != 0) then
			Sdata = strcat(Sdata, ",")
		endif
		Sdata = strcat(Sdata, sprintf("%f", iarray[index]))
		index += 1
	od
	Squery = sprintf("INSERT INTO savearray (name, data, unit, created) VALUES ('%s', array[%s], '%s', current_timestamp)", Sname, Sdata, Sunit)
	dbexec gidb, Squery
endop

opcode pgdb_array_save, 0, SSS[]
	Sname, Sunit, Sarray[] xin
	Sname = pgdb_sqlescape(Sname)
	Sunit = pgdb_sqlescape(Sunit)
	dbexec gidb, sprintf("DELETE FROM savearray WHERE name = '%s' AND unit = '%s'", Sname, Sunit)
	ilen = lenarray(Sarray)
	index = 0
	Sdata = ""
	while (index < ilen) do
		if (index != 0) then
			Sdata = strcat(Sdata, ",")
		endif
		Sdata = strcat(Sdata, sprintf("'%s'", Sarray[index]))
		index += 1
	od
	Squery = sprintf("INSERT INTO savearray (name, textdata, unit, created) VALUES ('%s', array[%s], '%s', current_timestamp)", Sname, Sdata, Sunit)
	dbexec gidb, Squery
endop


/*
	Save a ftable to the database with a given name

	pgdb_table_save Sname, Sunit, ifn

	Sname		name of the save data
	Sunit		unit to associate save data with
	ifn			ftable number

*/
opcode pgdb_table_save, 0, SSi
	Sname, Sunit, ifn xin
	Sname = pgdb_sqlescape(Sname)
	Sunit = pgdb_sqlescape(Sunit)
	ilen = ftlen(ifn)
	index = 0
	Sdata = ""
	while (index < ilen) do
		Sdata = strcat(Sdata, sprintf("%f", table:i(index, ifn))) 
		if (index + 1 < ilen) then
			Sdata = strcat(Sdata, ",")
		endif
		index += 1
	od
	Squery = sprintf("DELETE FROM savearray WHERE name = '%s' AND unit = '%s'; INSERT INTO savearray (name, data, unit, created) VALUES ('%s', array[%s], '%s', current_timestamp)",\
		Sname, Sunit, Sname, Sdata, Sunit\
	)
	dbexec gidb, Squery
endop


; broken
opcode pgdb_table_savek, k, SSkk
	Sname, Sunit, kfn, ktrig xin
	;Sname pgdb_sqlescape Sname
	;Sunit pgdb_sqlescape Sunit	
	if (ktrig == 1) then
		printk2 tablekt:k(1, kfn)
		klen = tableng(kfn)
		kindex = 0
		Sdata = ""
		while (kindex < klen) do
			Sdata = strcatk(Sdata, sprintfk("%f", tablekt:k(kindex, kfn))) 
			if (kindex + 1 < klen) then
				Sdata = strcatk(Sdata, ",")
			endif
			kindex += 1
		od
		Squery sprintfk "DELETE FROM savearray WHERE name = '%s' AND unit = '%s'; INSERT INTO savearray (name, data, unit, created) VALUES ('%s', array[%s], '%s', current_timestamp)\n",\
			Sname, Sunit, Sname, Sdata, Sunit
		kdone dbexec_k gidb, Squery, ktrig
		printf Squery, ktrig
	endif
	
	xout kdone
endop


/*
	Get an array from the database with a given name

	iarray[] array_get Sname

	Sname		name of the save data
	iarray[]	the resulting array

*/
opcode pgdb_array_get, i[], SS
	Sname, Sunit xin
	Sname = pgdb_sqlescape(Sname)
	Sunit = pgdb_sqlescape(Sunit)
	Squery = sprintf("SELECT UNNEST(data) FROM savearray WHERE name = '%s' AND unit = '%s'", Sname, Sunit)
	iresult[][] dbarray gidb, Squery
	idata[] init lenarray(iresult)
	index = 0
	
	; TODO : can use getcol here??

	while (index < lenarray(idata)) do
		idata[index] = iresult[index][0]
		index += 1
	od
	xout idata
endop


opcode pgdb_array_get, S[], SS
	Sname, Sunit xin
	Sname = pgdb_sqlescape(Sname)
	Sunit = pgdb_sqlescape(Sunit)
	Squery = sprintf("SELECT UNNEST(textdata) FROM savearray WHERE name = '%s' AND unit = '%s'", Sname, Sunit)
	Sresult[][] dbarray gidb, Squery
	Sdata[] init lenarray(Sresult)
	index = 0
	
	; TODO : can use getcol here??

	while (index < lenarray(Sdata)) do
		Sdata[index] = Sresult[index][0]
		index += 1
	od
	xout Sdata
endop



/*
	Get a ftable from the database with a given name: load to an existing table if ifnexisting not specified

	ifn pgdb_table_get Sname, [ifnexisting]

	Sname		name of the save data
	ifn			the resulting ftable
	ifnexisting	the table to load to, if not long enough, values will be limited

*/
opcode pgdb_table_get, i, SSj
	Sname, Sunit, ifnexisting xin
	Sname = pgdb_sqlescape(Sname)
	Sunit = pgdb_sqlescape(Sunit)
	Squery = sprintf("SELECT UNNEST(data) FROM savearray WHERE name = '%s' AND unit = '%s'", Sname, Sunit)
	iresult[][] dbarray gidb, Squery
	if (lenarray(iresult) > 0) then
		ifn = (ifnexisting == -1) ? ftgen(0, 0, lenarray(iresult), 7, 0) : ifnexisting
		index = 0
		while (index < min(lenarray(iresult), ftlen(ifn))) do
			tablew iresult[index][0], index, ifn
			index += 1
		od
	endif 
	xout ifn
endop




/*
	Prewarm a relation (load to memory)

	pgdb_prewarm Srelation

	Srelation	name of relation (ie table/view)
*/
opcode pgdb_prewarm, 0, S
	Srelation xin
	icheck dbscalar gidb, sprintf("SELECT COALESCE(pg_prewarm('%s'), 0)", Srelation)
endop


#end