From c3853f6dd796615f332fefcaaaf563794d867ee4 Mon Sep 17 00:00:00 2001 From: Richard Knight Date: Tue, 17 Mar 2020 18:47:36 +0000 Subject: initial --- src/connection.cpp | 218 ++++++++++++++++ src/mysql.cpp | 145 +++++++++++ src/opcodes.cpp | 749 +++++++++++++++++++++++++++++++++++++++++++++++++++++ src/postgresql.cpp | 135 ++++++++++ src/sqlite3.cpp | 159 ++++++++++++ 5 files changed, 1406 insertions(+) create mode 100644 src/connection.cpp create mode 100644 src/mysql.cpp create mode 100644 src/opcodes.cpp create mode 100644 src/postgresql.cpp create mode 100644 src/sqlite3.cpp (limited to 'src') diff --git a/src/connection.cpp b/src/connection.cpp new file mode 100644 index 0000000..5ac0aa1 --- /dev/null +++ b/src/connection.cpp @@ -0,0 +1,218 @@ +/* + connection.cpp + Copyright (C) 2019 Richard Knight + + + This program is free software; you can redistribute it and/or + modify it under the terms of the GNU Lesser General Public + License as published by the Free Software Foundation; either + version 3 of the License, or (at your option) any later version. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + Lesser General Public License for more details. + + You should have received a copy of the GNU Lesser General Public License + along with this program; if not, write to the Free Software Foundation, + Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + + */ + +#include +#include "connection.h" + + +const char* badDatabase = "database type not supported"; +const char* badConnection = "connection not open"; + +// tried to do this with templates, failed with various approaches +// hence the copious number of switches etc (horrible?). Due a revisit/refactor. + +void ConnectionData::Init(csnd::Csound* csound, LoginData* login) { + type = login->dbType; + switch (type) { +#ifdef BUILD_POSTGRES + case POSTGRES: + postgres = (PostgresConnection*) csound->malloc(sizeof(PostgresConnection)); + postgres->Init(csound, login); + break; +#endif +#ifdef BUILD_SQLITE + case SQLITE: + sqlite = (SqliteConnection*) csound->malloc(sizeof(SqliteConnection)); + sqlite->Init(csound, login); + break; +#endif +#ifdef BUILD_MYSQL + case MYSQL: + mysql = (MySQLConnection*) csound->malloc(sizeof(MySQLConnection)); + mysql->Init(csound, login); + break; +#endif + default: + throw std::runtime_error(badDatabase); + } + open = true; +} + +void ConnectionData::Close(csnd::Csound* csound) { + if (!open) { + throw std::runtime_error(badConnection); + } + + switch (type) { +#ifdef BUILD_POSTGRES + case POSTGRES: + postgres->Close(csound); + break; +#endif +#ifdef BUILD_SQLITE + case SQLITE: + sqlite->Close(csound); + break; +#endif +#ifdef BUILD_MYSQL + case MYSQL: + mysql->Close(csound); + break; +#endif + default: + throw std::runtime_error(badDatabase); + } + open = false; +} + +void ConnectionData::Exec(char* sql) { + if (!open) { + throw std::runtime_error(badConnection); + } + + switch (type) { +#ifdef BUILD_POSTGRES + case POSTGRES: + postgres->Exec(sql); + break; +#endif +#ifdef BUILD_SQLITE + case SQLITE: + sqlite->Exec(sql); + break; +#endif +#ifdef BUILD_MYSQL + case MYSQL: + mysql->Exec(sql); + break; +#endif + default: + throw std::runtime_error(badDatabase); + } +} + +MYFLT ConnectionData::Scalar(char* sql, int row=0, int col=0) { + if (!open) { + throw std::runtime_error(badConnection); + } + + switch (type) { +#ifdef BUILD_POSTGRES + case POSTGRES: + return postgres->Scalar(sql, row, col); + break; +#endif +#ifdef BUILD_SQLITE + case SQLITE: + return sqlite->Scalar(sql, row, col); + break; +#endif +#ifdef BUILD_MYSQL + case MYSQL: + return mysql->Scalar(sql, row, col); + break; +#endif + default: + throw std::runtime_error(badDatabase); + } + +} + +char* ConnectionData::ScalarString(char* sql, int row=0, int col=0) { + if (!open) { + throw std::runtime_error(badConnection); + } + + switch (type) { +#ifdef BUILD_POSTGRES + case POSTGRES: + return postgres->ScalarString(sql, row, col); + break; +#endif +#ifdef BUILD_SQLITE + case SQLITE: + return sqlite->ScalarString(sql, row, col); + break; +#endif +#ifdef BUILD_MYSQL + case MYSQL: + return mysql->ScalarString(sql, row, col); + break; +#endif + default: + throw std::runtime_error(badDatabase); + } + +} + +void ConnectionData::ArrayQuery(char* sql, csnd::Csound* csound, ARRAYDAT* array) { + if (!open) { + throw std::runtime_error(badConnection); + } + + switch (type) { +#ifdef BUILD_POSTGRES + case POSTGRES: + return postgres->ArrayQuery(sql, csound, array); + break; +#endif +#ifdef BUILD_SQLITE + case SQLITE: + return sqlite->ArrayQuery(sql, csound, array); + break; +#endif +#ifdef BUILD_MYSQL + case MYSQL: + return mysql->ArrayQuery(sql, csound, array); + break; +#endif + default: + throw std::runtime_error(badDatabase); + } + +} + +void ConnectionData::ArrayQueryString(char* sql, csnd::Csound* csound, ARRAYDAT* array) { + if (!open) { + throw std::runtime_error(badConnection); + } + + switch (type) { +#ifdef BUILD_POSTGRES + case POSTGRES: + return postgres->ArrayQueryString(sql, csound, array); + break; +#endif +#ifdef BUILD_SQLITE + case SQLITE: + return sqlite->ArrayQueryString(sql, csound, array); + break; +#endif +#ifdef BUILD_MYSQL + case MYSQL: + return mysql->ArrayQueryString(sql, csound, array); + break; +#endif + default: + throw std::runtime_error(badDatabase); + } + +} diff --git a/src/mysql.cpp b/src/mysql.cpp new file mode 100644 index 0000000..040670e --- /dev/null +++ b/src/mysql.cpp @@ -0,0 +1,145 @@ +/* + mysql.cpp + Copyright (C) 2019 Richard Knight + + + This program is free software; you can redistribute it and/or + modify it under the terms of the GNU Lesser General Public + License as published by the Free Software Foundation; either + version 3 of the License, or (at your option) any later version. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + Lesser General Public License for more details. + + You should have received a copy of the GNU Lesser General Public License + along with this program; if not, write to the Free Software Foundation, + Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + + */ + +#include +#include +#include +#include +#include +#include +#include "mysql_connection.h" +#include "connection.h" +#include "mysql.h" +namespace mysql = sql; + + +void MySQLConnection::Init(csnd::Csound* csound, LoginData* login) { + driver = get_driver_instance(); + char host[256]; + snprintf(host, 256, "tcp://%s:3306", login->dbHost); + + conn = driver->connect(host, login->dbUser, login->dbPass); + conn->setSchema(login->dbName); + + if (conn->isClosed()) { + throw std::runtime_error("connection not established"); + } +} + +void MySQLConnection::Close(csnd::Csound* csound) { + conn->close(); + delete conn; +} + +void MySQLConnection::Exec(char* sql) { + mysql::Statement* stmt = conn->createStatement(); + stmt->execute(sql); + delete stmt; +} + +mysql::ResultSet* MySQLConnection::Query(char* sql) { + mysql::Statement* stmt = conn->createStatement(); + mysql::ResultSet* result = stmt->executeQuery(sql); + delete stmt; + return result; +} + +MYFLT MySQLConnection::Scalar(char* sql, int row=0, int col=0) { + mysql::ResultSet* res = Query(sql); + mysql::ResultSetMetaData* meta = res->getMetaData(); + int colCount = meta->getColumnCount(); + if (col > colCount - 1) { + throw std::runtime_error("column number out of range"); + } + + res->next(); + MYFLT result = (MYFLT) res->getDouble(col + 1); + + delete res; + + return result; +} + +char* MySQLConnection::ScalarString(char* sql, int row=0, int col=0) { + mysql::ResultSet* res = Query(sql); + mysql::ResultSetMetaData* meta = res->getMetaData(); + + int colCount = meta->getColumnCount(); + if (col > colCount - 1) { + throw std::runtime_error("column number out of range"); + } + + int rowIndex = 0; + for (int rowIndex = 0; rowIndex <= row; rowIndex++) { + res->next(); + } + char* result = res->getString(col + 1).c_str(); + + delete res; + + return result; +} + + +void MySQLConnection::ToArray(mysql::ResultSet* result, csnd::Csound* csound, ARRAYDAT* array, bool asString) { + mysql::ResultSetMetaData* meta = result->getMetaData(); + int colNum = meta->getColumnCount(); + int rowNum = result->rowsCount(); + int totalResults = colNum * rowNum; + array->sizes = csound->calloc(sizeof(int32_t) * 2); + array->sizes[0] = rowNum; + array->sizes[1] = colNum; + array->dimensions = 2; + CS_VARIABLE *var = array->arrayType->createVariable(csound, NULL); + array->arrayMemberSize = var->memBlockSize; + array->data = csound->calloc(var->memBlockSize * totalResults); + STRINGDAT* strings; + if (asString) { + strings = (STRINGDAT*) array->data; + } + + int colIndex; + int index = 0; + + while (result->next()) { + colIndex = 0; + while (colIndex < colNum) { + if (asString) { + char* item = result->getString(colIndex + 1).c_str(); + strings[index].size = strlen(item) + 1; + strings[index].data = csound->strdup(item); + } else { + array->data[index] = (MYFLT) result->getDouble(colIndex + 1); + } + colIndex++; + index++; + } + } + delete result; +} + +void MySQLConnection::ArrayQueryString(char* sql, csnd::Csound* csound, ARRAYDAT* array) { + ToArray(Query(sql), csound, array, true); +} + +void MySQLConnection::ArrayQuery(char* sql, csnd::Csound* csound, ARRAYDAT* array) { + ToArray(Query(sql), csound, array, false); +} diff --git a/src/opcodes.cpp b/src/opcodes.cpp new file mode 100644 index 0000000..3527f83 --- /dev/null +++ b/src/opcodes.cpp @@ -0,0 +1,749 @@ +/* + opcodes.cpp + Copyright (C) 2019 Richard Knight + + + This program is free software; you can redistribute it and/or + modify it under the terms of the GNU Lesser General Public + License as published by the Free Software Foundation; either + version 3 of the License, or (at your option) any later version. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + Lesser General Public License for more details. + + You should have received a copy of the GNU Lesser General Public License + along with this program; if not, write to the Free Software Foundation, + Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + + */ +#include +#include +#include +#include "connection.h" + +const char* dbname = "::dbconnection%d"; +const char* badHandle = "cannot obtain connection from handle"; + +#define LOCK(connection) csound->get_csound()->LockMutex(connection->mutex); +#define UNLOCK(connection) csound->get_csound()->UnlockMutex(connection->mutex); + +/* + * Obtain connection from global variables by handle + */ +ConnectionData* getConnection(csnd::Csound* csound, MYFLT handle) { + char buffer[32]; + snprintf(buffer, 32, dbname, (int)handle); + return (ConnectionData*) csound->query_global_variable(buffer); +} + + +/* + * Create connection in global variables returning handle + */ +MYFLT CreateHandle(csnd::Csound* csound, ConnectionData** connection) { + char buffer[32]; + int handle = 0; + snprintf(buffer, 32, dbname, handle); + while ((*connection = (ConnectionData*) csound->query_global_variable(buffer)) != NULL) { + snprintf(buffer, 32, dbname, ++handle); + } + csound->create_global_variable(buffer, sizeof(ConnectionData)); + *connection = (ConnectionData*) csound->query_global_variable(buffer); + + return FL(handle); +} + +/* + * Thread for k rate query handling + */ +class QueryThread : public csnd::Thread { + std::atomic_bool spinlock; + std::atomic_bool on; + ConnectionData* connection; + QueryData* queryData; + int sleepTime; + +public: + bool pending; // if there is a query waiting to be processed + char* charData; + int charSize; + char* error; + int status; + MYFLT flData; + bool done; + int ident; + QueryThread(csnd::Csound *csound, ConnectionData* connection, QueryData* queryData) : + Thread(csound), + done(false), + queryData(queryData), + charSize(1), + status(0), + spinlock(false), + pending(false), + on(true), + connection(connection), + sleepTime(10) {}; + + + uintptr_t run() { + while(on) { + lock(); + if (pending) { + LOCK(connection); + try { + switch (queryData->queryType) { + case SCALARSTRING: { + std::string resultString = connection->ScalarString(queryData->sql, queryData->row, queryData->col); + if (charData != NULL) { + csound->free(charData); + } + charData = csound->strdup(resultString.c_str()); + charSize = resultString.length() + 1; + } + break; + case SCALAR: { + flData = connection->Scalar(queryData->sql, queryData->row, queryData->col); + } + break; + case EXEC: { + connection->Exec(queryData->sql); + + } + break; + case ARRAY: { + connection->ArrayQuery(queryData->sql, csound, queryData->array); + } + break; + case ARRAYSTRING: { + connection->ArrayQueryString(queryData->sql, csound, queryData->array); + } + break; + } + status = 0; + done = true; + pending = false; + } catch (const std::exception &e) { + status = 1; + done = true; + pending = false; + error = csound->strdup(e.what()); + } + UNLOCK(connection); + } + unlock(); + csound->sleep(sleepTime); + } + return 0; + } + + void do_query(char* sql, int row=0, int col=0) { + if (queryData->sql != NULL) { + //csound->free(queryData->sql); + } + + queryData->sql = sql; + queryData->row = row; + queryData->col = col; + pending = true; + + } + + void lock() { + while (spinlock == true) { + csound->sleep(sleepTime); // have as one k cycle ?? // was 100 + } + spinlock = true; + } + + void unlock() { + spinlock = false; + } + + void stop() { + on = false; + } +}; + + + + +struct dbconnect_full : csnd::Plugin<1, 5> { + static constexpr char const *otypes = "i"; + static constexpr char const *itypes = "SSSSS"; + ConnectionData* connection; + + int init() { + csound->plugin_deinit(this); + outargs[0] = CreateHandle(csound, &connection); + connection->mutex = csound->get_csound()->Create_Mutex(0); + + STRINGDAT &dbType = inargs.str_data(0); + STRINGDAT &dbHost = inargs.str_data(1); + STRINGDAT &dbName = inargs.str_data(2); + STRINGDAT &dbUser = inargs.str_data(3); + STRINGDAT &dbPass = inargs.str_data(4); + LoginData* login = (LoginData*) csound->malloc(sizeof(LoginData)); + + try { + if (!strcmp(dbType.data, "postgresql")) { + login->dbType = POSTGRES; + } else if (!strcmp(dbType.data, "mysql")) { + login->dbType = MYSQL; + } else { + return csound->init_error("database type not supported"); + } + login->dbHost = dbHost.data; + login->dbName = dbName.data; + login->dbUser = dbUser.data; + login->dbPass = dbPass.data; + + connection->Init(csound, login); + + } catch (const std::exception &e) { + return csound->init_error(e.what()); + } + return OK; + } + + int deinit() { + bool error; + LOCK(connection); + try { + connection->Close(csound); + } catch (const std::exception &e) { + error = true; + } + UNLOCK(connection); + csound->get_csound()->DestroyMutex(connection->mutex); + return (error)? NOTOK: OK; + } +}; + +struct dbconnect_short : csnd::Plugin<1, 2> { + static constexpr char const *otypes = "i"; + static constexpr char const *itypes = "SS"; + ConnectionData* connection; + + int init() { + csound->plugin_deinit(this); + outargs[0] = CreateHandle(csound, &connection); + connection->mutex = csound->get_csound()->Create_Mutex(0); + + STRINGDAT &dbType = inargs.str_data(0); + STRINGDAT &dbName = inargs.str_data(1); + + LoginData* login = (LoginData*) csound->malloc(sizeof(LoginData)); + try { + if (strcmp(dbType.data, "sqlite")) { + return csound->init_error("database type not supported"); + } + login->dbType = SQLITE; + login->dbName = dbName.data; + + connection->Init(csound, login); + } catch (const std::exception &e) { + return csound->init_error(e.what()); + } + return OK; + } + + int deinit() { + bool error; + LOCK(connection); + try { + connection->Close(csound); + } catch (const std::exception &e) { + error = true; + } + UNLOCK(connection);; + csound->get_csound()->DestroyMutex(connection->mutex); + return (error)? NOTOK: OK; + } +}; + + + + + +/* + * Base struct for threaded k-rate queries + */ +template struct DBPluginBaseK : csnd::Plugin { + using csnd::Plugin::inargs; + using csnd::Plugin::outargs; + using csnd::Plugin::csound; + ConnectionData* connection; + QueryThread query; + bool singleRun; + bool singleComplete; + + int setup(int queryType, int row=0, int col=0, ARRAYDAT* array=NULL) { + if (!(connection = getConnection(csound, inargs[0]))) { + return csound->init_error(badHandle); + } + csound->plugin_deinit(this); + + QueryData* queryData = (QueryData*) csound->malloc(sizeof(QueryData)); + queryData->queryType = queryType; + + queryData->row = row; + queryData->col = col; + + if (array) { + queryData->array = array; + } + + if (inargs[2] == FL(-1)) { + singleRun = true; + } + + csnd::constr(&query, csound, connection, queryData); + return OK; + } + + int deinit() { + query.stop(); + query.join(); + csnd::destr(&query); + return OK; + } + + MYFLT kcycle(int row=0, int col=0) { + outargs[0] = FL(0); + if (singleComplete && singleRun) { + return OK; + } + + if (UNLIKELY(query.done)) { + if (query.status) { + return csound->perf_error(query.error, this); + } + if (singleRun) { + singleComplete = true; + } + query.lock(); + query.done = false; + query.unlock(); + outargs[0] = FL(1); + } + + if (UNLIKELY(!query.pending && + (inargs[2] == FL(1) || + singleRun) + )) { + query.lock(); + query.do_query(csound->strdup(inargs.str_data(1).data), row, col); + query.unlock(); + } + + return OK; + } + +}; + + +// threaded k rate opcodes + +struct dbexec_k : DBPluginBaseK<1, 3> { + static constexpr char const *otypes = "k"; + static constexpr char const *itypes = "iSk"; + + int init() { + return setup(EXEC); + } + + int kperf() { + return kcycle(); + } +}; + + + +struct dbscalar_k : DBPluginBaseK<2, 5> { + static constexpr char const *otypes = "kk"; + static constexpr char const *itypes = "iSkOO"; + + int init() { + return setup(SCALAR, (int)inargs[3], (int)inargs[4]); + } + + int kperf() { + int response = kcycle((int)inargs[3], (int)inargs[4]); + outargs[1] = query.flData; + return response; + } +}; + + +struct dbscalarstr_k : DBPluginBaseK<2, 5> { + static constexpr char const *otypes = "kS"; + static constexpr char const *itypes = "iSkOO"; + + int init() { + return setup(SCALARSTRING, (int)inargs[3], (int)inargs[4]); + } + + int kperf() { + int response = kcycle((int)inargs[3], (int)inargs[4]); + STRINGDAT &result = outargs.str_data(1); + result.size = query.charSize; + result.data = query.charData; + return response; + } +}; + + +struct dbarray_k : DBPluginBaseK<2, 3> { + static constexpr char const *otypes = "kk[][]"; + static constexpr char const *itypes = "iSk"; + + int init() { + return setup(ARRAY, NULL, NULL, (ARRAYDAT*) outargs(1)); + } + + int kperf() { + return kcycle(); + } + +}; + + +struct dbarraystr_k : DBPluginBaseK<2, 3> { + static constexpr char const *otypes = "kS[][]"; + static constexpr char const *itypes = "iSk"; + + int init() { + return setup(ARRAYSTRING, NULL, NULL, (ARRAYDAT*) outargs(1)); + } + + int kperf() { + return kcycle(); + } +}; + + + + +struct dbexec : csnd::InPlug<2> { + static constexpr char const *otypes = ""; + static constexpr char const *itypes = "iS"; + ConnectionData* connection; + + int init() { + if (!(connection = getConnection(csound, args[0]))) { + return csound->init_error(badHandle); + } + + STRINGDAT &sql = args.str_data(1); + LOCK(connection); + try { + connection->Exec(sql.data); + return OK; + } catch (const std::exception &e) { + return csound->init_error(e.what()); + } + UNLOCK(connection); + + } +}; + + + +struct dbscalar : csnd::Plugin<1, 4> { + static constexpr char const *otypes = "i"; + static constexpr char const *itypes = "iSoo"; + ConnectionData* connection; + + int init() { + if (!(connection = getConnection(csound, inargs[0]))) { + return csound->init_error(badHandle); + } + STRINGDAT &sql = inargs.str_data(1); + try { + LOCK(connection); + outargs[0] = connection->Scalar(sql.data, inargs[2], inargs[3]); + UNLOCK(connection); + return OK; + } catch (const std::exception &e) { + return csound->init_error(e.what()); + } + } +}; + + + + +struct dbscalarstr : csnd::Plugin<1, 4> { + static constexpr char const *otypes = "S"; + static constexpr char const *itypes = "iSoo"; + ConnectionData* connection; + + int init() { + if (!(connection = getConnection(csound, inargs[0]))) { + return csound->init_error(badHandle); + } + STRINGDAT &sql = inargs.str_data(1); + STRINGDAT &result = outargs.str_data(0); + try { + LOCK(connection); + std::string resultString = connection->ScalarString(sql.data, inargs[2], inargs[3]); + UNLOCK(connection); + result.size = resultString.length() + 1; + result.data = csound->strdup(resultString.c_str()); + return OK; + } catch (const std::exception &e) { + return csound->init_error(e.what()); + } + } +}; + + + + + + +struct dbarray : csnd::Plugin<1, 2> { + static constexpr char const *otypes = "i[][]"; + static constexpr char const *itypes = "iS"; + ConnectionData* connection; + + int init() { + if (!(connection = getConnection(csound, inargs[0]))) { + return csound->init_error(badHandle); + } + STRINGDAT &sql = inargs.str_data(1); + + ARRAYDAT* array = (ARRAYDAT*) outargs(0); + + try { + LOCK(connection); + connection->ArrayQuery(sql.data, csound, array); + UNLOCK(connection); + return OK; + } catch (const std::exception &e) { + return csound->init_error(e.what()); + } + } +}; + + + + + +struct dbarraystr : csnd::Plugin<1, 2> { + static constexpr char const *otypes = "S[][]"; + static constexpr char const *itypes = "iS"; + ConnectionData* connection; + + int init() { + if (!(connection = getConnection(csound, inargs[0]))) { + return csound->init_error(badHandle); + } + STRINGDAT &sql = inargs.str_data(1); + + ARRAYDAT* array = (ARRAYDAT*) outargs(0); + + try { + LOCK(connection); + connection->ArrayQueryString(sql.data, csound, array); + UNLOCK(connection); + return OK; + } catch (const std::exception &e) { + return csound->init_error(e.what()); + } + } +}; + + + + + + +// k rate blocking opcodes + + +struct dbexec_kb : csnd::InPlug<2> { + static constexpr char const *otypes = ""; + static constexpr char const *itypes = "iS"; + ConnectionData* connection; + + int init() { + if (!(connection = getConnection(csound, args[0]))) { + return csound->init_error(badHandle); + } + return OK; + + } + + int kperf() { + STRINGDAT &sql = args.str_data(1); + try { + LOCK(connection); + connection->Exec(sql.data); + UNLOCK(connection); + return OK; + } catch (const std::exception &e) { + return csound->perf_error(e.what(), this); + } + } +}; + + + +struct dbscalar_kb : csnd::Plugin<1, 4> { + static constexpr char const *otypes = "k"; + static constexpr char const *itypes = "iSOO"; + ConnectionData* connection; + + int init() { + if (!(connection = getConnection(csound, inargs[0]))) { + return csound->init_error(badHandle); + } + return OK; + } + + int kperf() { + STRINGDAT &sql = inargs.str_data(1); + try { + LOCK(connection); + outargs[0] = connection->Scalar(sql.data, inargs[2], inargs[3]); + UNLOCK(connection); + return OK; + } catch (const std::exception &e) { + return csound->perf_error(e.what(), this); + } + } +}; + + + + +struct dbscalarstr_kb : csnd::Plugin<1, 4> { + static constexpr char const *otypes = "S"; + static constexpr char const *itypes = "iSOO"; + ConnectionData* connection; + + int init() { + if (!(connection = getConnection(csound, inargs[0]))) { + return csound->init_error(badHandle); + } + return OK; + } + + int kperf() { + + STRINGDAT &sql = inargs.str_data(1); + STRINGDAT &result = outargs.str_data(0); + try { + LOCK(connection); + std::string resultString = connection->ScalarString(sql.data, inargs[2], inargs[3]); + UNLOCK(connection); + result.size = resultString.length() + 1; + result.data = csound->strdup(resultString.c_str()); + return OK; + } catch (const std::exception &e) { + return csound->perf_error(e.what(), this); + } + } +}; + + + + + + +struct dbarray_kb : csnd::Plugin<1, 2> { + static constexpr char const *otypes = "k[][]"; + static constexpr char const *itypes = "iS"; + ConnectionData* connection; + + int init() { + if (!(connection = getConnection(csound, inargs[0]))) { + return csound->init_error(badHandle); + } + return OK; + } + + int kperf() { + STRINGDAT &sql = inargs.str_data(1); + + ARRAYDAT* array = (ARRAYDAT*) outargs(0); + + try { + LOCK(connection); + connection->ArrayQuery(sql.data, csound, array); + UNLOCK(connection); + return OK; + } catch (const std::exception &e) { + return csound->perf_error(e.what(), this); + } + } +}; + + + + + +struct dbarraystr_kb : csnd::Plugin<1, 2> { + static constexpr char const *otypes = "S[][]"; + static constexpr char const *itypes = "iS"; + ConnectionData* connection; + + int init() { + if (!(connection = getConnection(csound, inargs[0]))) { + return csound->init_error(badHandle); + } + return OK; + } + + int kperf() { + STRINGDAT &sql = inargs.str_data(1); + + ARRAYDAT* array = (ARRAYDAT*) outargs(0); + + try { + LOCK(connection); + connection->ArrayQueryString(sql.data, csound, array); + UNLOCK(connection); + return OK; + } catch (const std::exception &e) { + return csound->perf_error(e.what(), this); + } + } +}; + + + + + + + + +#include + +void csnd::on_load(csnd::Csound *csound) { + + csnd::plugin(csound, "dbconnect.f", csnd::thread::i); + csnd::plugin(csound, "dbconnect.s", csnd::thread::i); + + csnd::plugin(csound, "dbexec", csnd::thread::i); + csnd::plugin(csound, "dbscalar", csnd::thread::i); + csnd::plugin(csound, "dbscalar.S", csnd::thread::i); + csnd::plugin(csound, "dbarray", csnd::thread::i); + csnd::plugin(csound, "dbarray.S", csnd::thread::i); + + csnd::plugin(csound, "dbexec_k", csnd::thread::ik); + csnd::plugin(csound, "dbscalar_k", csnd::thread::ik); + csnd::plugin(csound, "dbscalar_k.S", csnd::thread::ik); + csnd::plugin(csound, "dbarray_k", csnd::thread::ik); + csnd::plugin(csound, "dbarray_k.S", csnd::thread::ik); + + csnd::plugin(csound, "dbexec_kb", csnd::thread::ik); + csnd::plugin(csound, "dbscalar_kb", csnd::thread::ik); + csnd::plugin(csound, "dbscalar_kb.S", csnd::thread::ik); + csnd::plugin(csound, "dbarray_kb", csnd::thread::ik); + csnd::plugin(csound, "dbarray_kb.S", csnd::thread::ik); + +} \ No newline at end of file diff --git a/src/postgresql.cpp b/src/postgresql.cpp new file mode 100644 index 0000000..53cd102 --- /dev/null +++ b/src/postgresql.cpp @@ -0,0 +1,135 @@ +/* + postgresql.cpp + Copyright (C) 2019 Richard Knight + + + This program is free software; you can redistribute it and/or + modify it under the terms of the GNU Lesser General Public + License as published by the Free Software Foundation; either + version 3 of the License, or (at your option) any later version. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + Lesser General Public License for more details. + + You should have received a copy of the GNU Lesser General Public License + along with this program; if not, write to the Free Software Foundation, + Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + + */ + +#include +#include +#include +#include "connection.h" +#include "postgresql.h" + + + +void PostgresConnection::Init(csnd::Csound* csound, LoginData* login) { + //conn = (pqxx::connection*) csound->malloc(sizeof(pqxx::connection)); + char connectionString[256]; + + snprintf(connectionString, 256, + "dbname=%s user=%s password=%s hostaddr=%s", + login->dbName, login->dbUser, login->dbPass, login->dbHost + ); + + conn = new pqxx::connection(connectionString); + + // ignore notices + std::auto_ptr np(new(pqxx::nonnoticer)); + conn->set_noticer(np); + + if (!conn->is_open()) { + throw std::runtime_error("Connection not open"); + } +} + +void PostgresConnection::Close(csnd::Csound* csound) { + if (conn->is_open()) { + conn->disconnect(); + } + delete conn; +} + +void PostgresConnection::Exec(char* sql) { + pqxx::nontransaction nt(*conn); + nt.exec(sql); +} + +pqxx::result PostgresConnection::Query(char* sql) { + pqxx::nontransaction nt(*conn); + pqxx::result result(nt.exec(sql)); + return result; +} + +MYFLT PostgresConnection::Scalar(char* sql, int row=0, int col=0) { + pqxx::result result = Query(sql); + + // checks as libpqxx not throwing if this happens + if (row > result.size() - 1) { + throw std::runtime_error("row number out of range"); + } + if (col > result[row].size() -1) { + throw std::runtime_error("column number out of range"); + } + + return result[row][col].as(); +} + +char* PostgresConnection::ScalarString(char* sql, int row=0, int col=0) { + pqxx::result result = Query(sql); + + // checks as libpqxx not throwing if this happens + if (row > result.size() - 1) { + throw std::runtime_error("row number out of range"); + } + if (col > result[row].size() -1) { + throw std::runtime_error("column number out of range"); + } + + return result[row][col].c_str(); + +} + + +void PostgresConnection::ToArray(pqxx::result result, csnd::Csound* csound, ARRAYDAT* array, bool asString) { + int totalResults = result.size() * result[0].size(); + array->sizes = csound->calloc(sizeof(int32_t) * 2); + array->sizes[0] = result.size(); + array->sizes[1] = result[0].size(); + array->dimensions = 2; + CS_VARIABLE *var = array->arrayType->createVariable(csound, NULL); + array->arrayMemberSize = var->memBlockSize; + array->data = csound->calloc(var->memBlockSize * totalResults); + STRINGDAT* strings; + if (asString) { + strings = (STRINGDAT*) array->data; + } + + int index = 0; + for (int rowNum = 0; rowNum < result.size(); ++rowNum) { + const pqxx::result::tuple row = result[rowNum]; + for (int colNum = 0; colNum < row.size(); ++colNum) { + const pqxx::result::field field = row[colNum]; + if (asString) { + char* item = field.c_str(); + strings[index].size = strlen(item) + 1; + strings[index].data = csound->strdup(item); + } else { + array->data[index] = field.as(); + } + index++; + } + } +} + +void PostgresConnection::ArrayQueryString(char* sql, csnd::Csound* csound, ARRAYDAT* array) { + ToArray(Query(sql), csound, array, true); +} + +void PostgresConnection::ArrayQuery(char* sql, csnd::Csound* csound, ARRAYDAT* array) { + ToArray(Query(sql), csound, array, false); +} diff --git a/src/sqlite3.cpp b/src/sqlite3.cpp new file mode 100644 index 0000000..824e165 --- /dev/null +++ b/src/sqlite3.cpp @@ -0,0 +1,159 @@ +/* + sqlite3.cpp + Copyright (C) 2019 Richard Knight + + + This program is free software; you can redistribute it and/or + modify it under the terms of the GNU Lesser General Public + License as published by the Free Software Foundation; either + version 3 of the License, or (at your option) any later version. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + Lesser General Public License for more details. + + You should have received a copy of the GNU Lesser General Public License + along with this program; if not, write to the Free Software Foundation, + Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + + */ + +#include +#include +#include +#include "connection.h" +#include "sqlite.h" + + +void SqliteConnection::Init(csnd::Csound* csound, LoginData* login) { + int result = sqlite3_open(login->dbName, &conn); + std::cout << "Y"; + if (result) { + throw std::runtime_error("connection not established"); + } +} + +void SqliteConnection::Close(csnd::Csound* csound) { + sqlite3_close(conn); +} + +void SqliteConnection::Exec(char* sql) { + sqlite3_stmt* stmt = Query(sql); + int rc = sqlite3_step(stmt); + rc = sqlite3_finalize(stmt); +} + +sqlite3_stmt* SqliteConnection::Query(char* sql) { + sqlite3_stmt* stmt = NULL; + int rc = sqlite3_prepare_v2(conn, sql, -1, &stmt, NULL); + if (rc != SQLITE_OK) { + throw std::runtime_error(sqlite3_errmsg(conn)); + } + return stmt; +} + +MYFLT SqliteConnection::Scalar(char* sql, int row=0, int col=0) { + sqlite3_stmt *stmt = Query(sql); + int colCount = sqlite3_column_count(stmt); + int rc = sqlite3_step(stmt); + int rowIndex = 0; + while (rc != SQLITE_DONE && rc != SQLITE_OK) { + if (rowIndex == row) { + + if (col > colCount -1) { + rc = sqlite3_finalize(stmt); + throw std::runtime_error("column number out of range"); + } + MYFLT result = (MYFLT) sqlite3_column_double(stmt, col); + rc = sqlite3_finalize(stmt); + return result; + } + rc = sqlite3_step(stmt); + rowIndex++; + } + rc = sqlite3_finalize(stmt); + throw std::runtime_error("no result"); +} + +char* SqliteConnection::ScalarString(char* sql, int row=0, int col=0) { + sqlite3_stmt *stmt = Query(sql); + int colCount = sqlite3_column_count(stmt); + int rc = sqlite3_step(stmt); + int rowIndex = 0; + while (rc != SQLITE_DONE && rc != SQLITE_OK) { + if (rowIndex == row) { + + if (col > colCount -1) { + rc = sqlite3_finalize(stmt); + throw std::runtime_error("column number out of range"); + } + char* result = sqlite3_column_text(stmt, col); + rc = sqlite3_finalize(stmt); + return result; + } + rc = sqlite3_step(stmt); + rowIndex++; + } + rc = sqlite3_finalize(stmt); + throw std::runtime_error("no result"); + +} + +int SqliteConnection::RowCount(sqlite3_stmt* stmt) { + int rowCount = 0; + int rc = sqlite3_step(stmt); + while (rc != SQLITE_DONE && rc != SQLITE_OK) { + rc = sqlite3_step(stmt); + rowCount ++; + } + rc = sqlite3_reset(stmt); + return rowCount; +} + +void SqliteConnection::ToArray(sqlite3_stmt* stmt, csnd::Csound* csound, ARRAYDAT* array, bool asString) { + int colNum = sqlite3_column_count(stmt); + int rowNum = RowCount(stmt); + int totalResults = colNum * rowNum; + array->sizes = csound->calloc(sizeof(int32_t) * 2); + array->sizes[0] = rowNum; + array->sizes[1] = colNum; + array->dimensions = 2; + CS_VARIABLE *var = array->arrayType->createVariable(csound, NULL); + array->arrayMemberSize = var->memBlockSize; + array->data = csound->calloc(var->memBlockSize * totalResults); + STRINGDAT* strings; + if (asString) { + strings = (STRINGDAT*) array->data; + } + + int colIndex; + int rowIndex; + int index = 0; + int rc = sqlite3_step(stmt); + while (rc != SQLITE_DONE && rc != SQLITE_OK) { + colIndex = 0; + while (colIndex < colNum) { + if (asString) { + char* item = sqlite3_column_text(stmt, colIndex); + strings[index].size = strlen(item) + 1; + strings[index].data = csound->strdup(item); + } else { + array->data[index] = (MYFLT) sqlite3_column_double(stmt, colIndex); + } + colIndex ++; + index++; + } + rc = sqlite3_step(stmt); + rowIndex++; + } + rc = sqlite3_finalize(stmt); +} + +void SqliteConnection::ArrayQueryString(char* sql, csnd::Csound* csound, ARRAYDAT* array) { + ToArray(Query(sql), csound, array, true); +} + +void SqliteConnection::ArrayQuery(char* sql, csnd::Csound* csound, ARRAYDAT* array) { + ToArray(Query(sql), csound, array, false); +} -- cgit v1.2.3