initial

1 files changed, 313 insertions, 0 deletions

diff --git a/site/udo/pgdb.udo b/site/udo/pgdb.udo
new file mode 100755
index 0000000..230d74a
--- /dev/null
+++ b/site/udo/pgdb.udo
@@ -0,0 +1,313 @@
+#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