From 1b40e87a3f54a70d4994262dd7fad4297caaeca3 Mon Sep 17 00:00:00 2001 From: Richard Knight Date: Fri, 9 Jul 2021 17:35:01 +0100 Subject: moved from libpqxx to libpq --- CMakeLists.txt | 12 +-- README.md | 6 +- cmake/Modules/FindPostgreSQL.cmake | 173 +++++++++++++++++++++++++++++++++++++ include/postgresql.h | 8 +- src/postgresql.cpp | 74 ++++++++-------- 5 files changed, 225 insertions(+), 48 deletions(-) create mode 100644 cmake/Modules/FindPostgreSQL.cmake diff --git a/CMakeLists.txt b/CMakeLists.txt index 43c251d..fb38a90 100644 --- a/CMakeLists.txt +++ b/CMakeLists.txt @@ -26,7 +26,7 @@ set(BUILDING_CSOUND_PLUGINS ON) # ---------------------------------------------- include(FindCsound) -include(FindPQXX) +include(FindPostgreSQL) include(FindSqlite3) include(FindMySQL) @@ -154,7 +154,7 @@ if(NOT CSOUND_FOUND) message(FATAL_ERROR "Csound installation not found") endif() -if(NOT PQXX_FOUND AND NOT SQLITE3_FOUND AND NOT MYSQLCONNECTORCPP_FOUND) +if(NOT PostgreSQL_FOUND AND NOT SQLITE3_FOUND AND NOT MYSQLCONNECTORCPP_FOUND) message(FATAL_ERROR "No database libraries could be found") endif() @@ -168,7 +168,7 @@ endif() set(CPPFILES src/opcodes.cpp src/connection.cpp) -if (PQXX_FOUND) +if (PostgreSQL_FOUND) message(STATUS "Using PostgreSQL") list(APPEND CPPFILES "src/postgresql.cpp") set(CMAKE_CXX_FLAGS "${CMAKE_CXX_FLAGS} -DBUILD_POSTGRES") @@ -192,9 +192,9 @@ endif() include_directories(${CSOUND_INCLUDE_DIRS}) include_directories(include) -if (PQXX_FOUND) - link_libraries(${PQXX_LIBRARIES}) - include_directories(${PQXX_INCLUDE_DIRECTORIES}) +if (PostgreSQL_FOUND) + link_libraries(${PostgreSQL_LIBRARIES}) + include_directories(${PostgreSQL_INCLUDE_DIRS}) endif() if (SQLITE3_FOUND) diff --git a/README.md b/README.md index a73a9f8..478c7bf 100644 --- a/README.md +++ b/README.md @@ -24,9 +24,9 @@ Connector/C++ (https://dev.mysql.com/downloads/connector/cpp/) # apt get install libmysqlcppconn-dev ### PostgreSQL -libpqxx (http://pqxx.org/development/libpqxx/wiki/DownloadPage) +libpq (https://www.postgresql.org/download/) - # apt get install libpqxx-dev + # apt get install libpq-dev ### SQLite libsqlite (https://www.sqlite.org/download.html) @@ -49,4 +49,4 @@ A number of examples are included in the examples directory. Generally the synta -By Richard Knight 2019 \ No newline at end of file +By Richard Knight 2019 diff --git a/cmake/Modules/FindPostgreSQL.cmake b/cmake/Modules/FindPostgreSQL.cmake new file mode 100644 index 0000000..4b17df1 --- /dev/null +++ b/cmake/Modules/FindPostgreSQL.cmake @@ -0,0 +1,173 @@ +# - Find the PostgreSQL installation. +# In Windows, we make the assumption that, if the PostgreSQL files are installed, the default directory +# will be C:\Program Files\PostgreSQL. +# +# This module defines +# PostgreSQL_LIBRARIES - the PostgreSQL libraries needed for linking +# PostgreSQL_INCLUDE_DIRS - the directories of the PostgreSQL headers +# PostgreSQL_VERSION_STRING - the version of PostgreSQL found (since CMake 2.8.8) + +#============================================================================= +# Copyright 2004-2009 Kitware, Inc. +# +# Distributed under the OSI-approved BSD License (the "License"); +# see accompanying file Copyright.txt for details. +# +# This software is distributed WITHOUT ANY WARRANTY; without even the +# implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. +# See the License for more information. +#============================================================================= +# (To distribute this file outside of CMake, substitute the full +# License text for the above reference.) + +# ---------------------------------------------------------------------------- +# History: +# This module is derived from the module originally found in the VTK source tree. +# +# ---------------------------------------------------------------------------- +# Note: +# PostgreSQL_ADDITIONAL_VERSIONS is a variable that can be used to set the +# version mumber of the implementation of PostgreSQL. +# In Windows the default installation of PostgreSQL uses that as part of the path. +# E.g C:\Program Files\PostgreSQL\8.4. +# Currently, the following version numbers are known to this module: +# "9.1" "9.0" "8.4" "8.3" "8.2" "8.1" "8.0" +# +# To use this variable just do something like this: +# set(PostgreSQL_ADDITIONAL_VERSIONS "9.2" "8.4.4") +# before calling FIND_PACKAGE(PostgreSQL) in your CMakeLists.txt file. +# This will mean that the versions you set here will be found first in the order +# specified before the default ones are searched. +# +# ---------------------------------------------------------------------------- +# You may need to manually set: +# PostgreSQL_INCLUDE_DIR - the path to where the PostgreSQL include files are. +# PostgreSQL_LIBRARY_DIR - The path to where the PostgreSQL library files are. +# If FindPostgreSQL.cmake cannot find the include files or the library files. +# +# ---------------------------------------------------------------------------- +# The following variables are set if PostgreSQL is found: +# PostgreSQL_FOUND - Set to true when PostgreSQL is found. +# PostgreSQL_INCLUDE_DIRS - Include directories for PostgreSQL +# PostgreSQL_LIBRARY_DIRS - Link directories for PostgreSQL libraries +# PostgreSQL_LIBRARIES - The PostgreSQL libraries. +# +# ---------------------------------------------------------------------------- +# If you have installed PostgreSQL in a non-standard location. +# (Please note that in the following comments, it is assumed that +# points to the root directory of the include directory of PostgreSQL.) +# Then you have three options. +# 1) After CMake runs, set PostgreSQL_INCLUDE_DIR to /include and +# PostgreSQL_LIBRARY_DIR to wherever the library pq (or libpq in windows) is +# 2) Use CMAKE_INCLUDE_PATH to set a path to /PostgreSQL<-version>. This will allow find_path() +# to locate PostgreSQL_INCLUDE_DIR by utilizing the PATH_SUFFIXES option. e.g. In your CMakeLists.txt file +# SET(CMAKE_INCLUDE_PATH ${CMAKE_INCLUDE_PATH} "/include") +# 3) Set an environment variable called ${PostgreSQL_ROOT} that points to the root of where you have +# installed PostgreSQL, e.g. . +# +# ---------------------------------------------------------------------------- + +set(PostgreSQL_INCLUDE_PATH_DESCRIPTION "top-level directory containing the PostgreSQL include directories. E.g /usr/local/include/PostgreSQL/8.4 or C:/Program Files/PostgreSQL/8.4/include") +set(PostgreSQL_INCLUDE_DIR_MESSAGE "Set the PostgreSQL_INCLUDE_DIR cmake cache entry to the ${PostgreSQL_INCLUDE_PATH_DESCRIPTION}") +set(PostgreSQL_LIBRARY_PATH_DESCRIPTION "top-level directory containing the PostgreSQL libraries.") +set(PostgreSQL_LIBRARY_DIR_MESSAGE "Set the PostgreSQL_LIBRARY_DIR cmake cache entry to the ${PostgreSQL_LIBRARY_PATH_DESCRIPTION}") +set(PostgreSQL_ROOT_DIR_MESSAGE "Set the PostgreSQL_ROOT system variable to where PostgreSQL is found on the machine E.g C:/Program Files/PostgreSQL/8.4") + + +set(PostgreSQL_ROOT_DIRECTORIES $ENV{PostgreSQL_ROOT}) +if(PostgreSQL_ROOT_DIRECTORIES) + file(TO_CMAKE_PATH ${PostgreSQL_ROOT_DIRECTORIES} PostgreSQL_ROOT_DIRECTORIES) +endif(PostgreSQL_ROOT_DIRECTORIES) + +set(PostgreSQL_KNOWN_VERSIONS ${PostgreSQL_ADDITIONAL_VERSIONS} + "9.1" "9.0" "8.4" "8.3" "8.2" "8.1" "8.0") + +# Define additional search paths for root directories. +if ( WIN32 ) + foreach (suffix ${PostgreSQL_KNOWN_VERSIONS} ) + set(PostgreSQL_ADDITIONAL_SEARCH_PATHS ${PostgreSQL_ADDITIONAL_SEARCH_PATHS} "C:/Program Files/PostgreSQL/${suffix}" ) + endforeach(suffix) +endif( WIN32 ) +set( PostgreSQL_ROOT_DIRECTORIES + ${PostgreSQL_ROOT_DIRECTORIES} + ${PostgreSQL_ROOT} + ${PostgreSQL_ADDITIONAL_SEARCH_PATHS} +) + +# +# Look for an installation. +# +find_path(PostgreSQL_INCLUDE_DIR + NAMES libpq-fe.h + PATHS + # Look in other places. + ${PostgreSQL_ROOT_DIRECTORIES} + PATH_SUFFIXES + pgsql + postgresql + include + # Help the user find it if we cannot. + DOC "The ${PostgreSQL_INCLUDE_DIR_MESSAGE}" +) + +find_path(PostgreSQL_TYPE_INCLUDE_DIR + NAMES catalog/pg_type.h + PATHS + # Look in other places. + ${PostgreSQL_ROOT_DIRECTORIES} + PATH_SUFFIXES + postgresql + pgsql/server + postgresql/server + include/server + # Help the user find it if we cannot. + DOC "The ${PostgreSQL_INCLUDE_DIR_MESSAGE}" +) + +# The PostgreSQL library. +set (PostgreSQL_LIBRARY_TO_FIND pq) +# Setting some more prefixes for the library +set (PostgreSQL_LIB_PREFIX "") +if ( WIN32 ) + set (PostgreSQL_LIB_PREFIX ${PostgreSQL_LIB_PREFIX} "lib") + set ( PostgreSQL_LIBRARY_TO_FIND ${PostgreSQL_LIB_PREFIX}${PostgreSQL_LIBRARY_TO_FIND}) +endif() + +find_library( PostgreSQL_LIBRARY + NAMES ${PostgreSQL_LIBRARY_TO_FIND} + PATHS + ${PostgreSQL_ROOT_DIRECTORIES} + PATH_SUFFIXES + lib +) +get_filename_component(PostgreSQL_LIBRARY_DIR ${PostgreSQL_LIBRARY} PATH) + +if (PostgreSQL_INCLUDE_DIR AND EXISTS "${PostgreSQL_INCLUDE_DIR}/pg_config.h") + file(STRINGS "${PostgreSQL_INCLUDE_DIR}/pg_config.h" pgsql_version_str + REGEX "^#define[\t ]+PG_VERSION[\t ]+\".*\"") + + string(REGEX REPLACE "^#define[\t ]+PG_VERSION[\t ]+\"([^\"]*)\".*" "\\1" + PostgreSQL_VERSION_STRING "${pgsql_version_str}") + unset(pgsql_version_str) +endif() + +# Did we find anything? +include(FindPackageHandleStandardArgs) +find_package_handle_standard_args(PostgreSQL + REQUIRED_VARS PostgreSQL_LIBRARY PostgreSQL_INCLUDE_DIR PostgreSQL_TYPE_INCLUDE_DIR + VERSION_VAR PostgreSQL_VERSION_STRING) +set( PostgreSQL_FOUND ${POSTGRESQL_FOUND}) + +# Now try to get the include and library path. +if(PostgreSQL_FOUND) + + set(PostgreSQL_INCLUDE_DIRS ${PostgreSQL_INCLUDE_DIR} ${PostgreSQL_TYPE_INCLUDE_DIR} ) + set(PostgreSQL_LIBRARY_DIRS ${PostgreSQL_LIBRARY_DIR} ) + set(PostgreSQL_LIBRARIES ${PostgreSQL_LIBRARY_TO_FIND}) + + #message("Final PostgreSQL include dir: ${PostgreSQL_INCLUDE_DIRS}") + #message("Final PostgreSQL library dir: ${PostgreSQL_LIBRARY_DIRS}") + #message("Final PostgreSQL libraries: ${PostgreSQL_LIBRARIES}") +endif(PostgreSQL_FOUND) + +mark_as_advanced(PostgreSQL_INCLUDE_DIR PostgreSQL_TYPE_INCLUDE_DIR PostgreSQL_LIBRARY ) diff --git a/include/postgresql.h b/include/postgresql.h index d7db5d9..5209057 100644 --- a/include/postgresql.h +++ b/include/postgresql.h @@ -23,18 +23,18 @@ #define POSTGRESQL_H #include -#include +#include "libpq-fe.h" #include "connection.h" struct PostgresConnection { - pqxx::connection* conn; + PGconn* conn; void Init(csnd::Csound* csound, LoginData* login); void Close(csnd::Csound* csound); void Exec(char* sql); - pqxx::result Query(char *sql); + PGresult* Query(char *sql); MYFLT Scalar(char* sql, int row, int col); char* ScalarString(char* sql, int row, int col); - void ToArray(pqxx::result result, csnd::Csound* csound, ARRAYDAT* array, bool asString); + void ToArray(PGresult* result, csnd::Csound* csound, ARRAYDAT* array, bool asString); void ArrayQuery(char* sql, csnd::Csound* csound, ARRAYDAT* array); void ArrayQueryString(char* sql, csnd::Csound* csound, ARRAYDAT* array); }; diff --git a/src/postgresql.cpp b/src/postgresql.cpp index 882081f..f2d42be 100644 --- a/src/postgresql.cpp +++ b/src/postgresql.cpp @@ -21,14 +21,13 @@ #include #include -#include +#include "libpq-fe.h" #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, @@ -36,90 +35,95 @@ void PostgresConnection::Init(csnd::Csound* csound, LoginData* login) { login->dbName, login->dbUser, login->dbPass, login->dbHost ); - conn = new pqxx::connection(connectionString); + conn = PQconnectdb(connectionString); - if (!conn->is_open()) { + if (PQstatus(conn) == CONNECTION_BAD) { throw std::runtime_error("Connection not open"); } } void PostgresConnection::Close(csnd::Csound* csound) { - if (conn->is_open()) { - conn->disconnect(); - } - delete conn; + PQfinish(conn); } void PostgresConnection::Exec(char* sql) { - pqxx::nontransaction nt(*conn); - nt.exec(sql); + PGresult* result = PQexec(conn, sql); + PQclear(result); } -pqxx::result PostgresConnection::Query(char* sql) { - pqxx::nontransaction nt(*conn); - pqxx::result result(nt.exec(sql)); - return result; +PGresult* PostgresConnection::Query(char* sql) { + return PQexec(conn, sql); } MYFLT PostgresConnection::Scalar(char* sql, int row, int col) { - pqxx::result result = Query(sql); + PGresult* result = Query(sql); - // checks as libpqxx not throwing if this happens - if (row > result.size() - 1) { + int rows = PQntuples(result); + int cols = PQnfields(result); + + if (row > rows - 1) { throw std::runtime_error("row number out of range"); } - if (col > result[row].size() -1) { + if (col > cols - 1) { throw std::runtime_error("column number out of range"); } - return result[row][col].as(); + MYFLT value = (MYFLT) atof(PQgetvalue(result, row, col)); + PQclear(result); + return value; } char* PostgresConnection::ScalarString(char* sql, int row, int col) { - pqxx::result result = Query(sql); + PGresult* result = Query(sql); - // checks as libpqxx not throwing if this happens - if (row > result.size() - 1) { + int rows = PQntuples(result); + int cols = PQnfields(result); + + if (row > rows - 1) { throw std::runtime_error("row number out of range"); } - if (col > result[row].size() -1) { + if (col > cols -1) { throw std::runtime_error("column number out of range"); } - return result[row][col].c_str(); + char* value = (char*) PQgetvalue(result, row, col); + PQclear(result); + return value; + } -void PostgresConnection::ToArray(pqxx::result result, csnd::Csound* csound, ARRAYDAT* array, bool asString) { - int totalResults = result.size() * result[0].size(); +void PostgresConnection::ToArray(PGresult* result, csnd::Csound* csound, ARRAYDAT* array, bool asString) { + int rows = PQntuples(result); + int cols = PQnfields(result); + int totalResults = rows * cols; array->sizes = csound->calloc(sizeof(int32_t) * 2); - array->sizes[0] = result.size(); - array->sizes[1] = result[0].size(); + array->sizes[0] = rows; + array->sizes[1] = cols; array->dimensions = 2; CS_VARIABLE *var = array->arrayType->createVariable(csound, NULL); array->arrayMemberSize = var->memBlockSize; - array->data = csound->calloc(var->memBlockSize * totalResults); + array->data = (MYFLT*) 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::row row = result[rowNum]; - for (int colNum = 0; colNum < row.size(); ++colNum) { - const pqxx::field field = row[colNum]; + for (int rowNum = 0; rowNum < rows; ++rowNum) { + for (int colNum = 0; colNum < cols; ++colNum) { if (asString) { - char* item = field.c_str(); + char* item = (char*) PQgetvalue(result, rowNum, colNum); strings[index].size = strlen(item) + 1; strings[index].data = csound->strdup(item); } else { - array->data[index] = field.as(); + array->data[index] = (MYFLT) atof(PQgetvalue(result, rowNum, colNum)); } index++; } } + PQclear(result); } void PostgresConnection::ArrayQueryString(char* sql, csnd::Csound* csound, ARRAYDAT* array) { -- cgit v1.2.3