From 9fbf91db06a6d4f4b5cd8bb45389a731bb86bf22 Mon Sep 17 00:00:00 2001 From: Richard Date: Sun, 13 Apr 2025 18:48:02 +0100 Subject: initial --- site/udo/pgdb.udo | 313 ++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 313 insertions(+) create mode 100755 site/udo/pgdb.udo (limited to 'site/udo/pgdb.udo') 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 -- cgit v1.2.3