matthijs / sqlpp11-connector-postgresql Goto Github PK

PostgreSQL connector for sqlpp11 library

License: BSD 2-Clause "Simplified" License

Python 2.84% C++ 90.47% C 1.43% CMake 5.26%

sqlpp11-connector-postgresql's Issues

Linker error: undefined reference to `sqlpp::postgresql::connection::connection`

Hi,
I've created a cmake project and installed sqlpp11 using vcpkg and then I added those two lines in my CMakeList.txt:

find_package(Sqlpp11 CONFIG REQUIRED)
...
target_link_libraries(my-target sqlpp11 HinnantDate::Date)

I then installed sqlpp11-connector-postgresql by cloning the repo and installing it system-wide (it wasn't available through vcpkg).

find_package(Sqlpp-connector-postgresql CONFIG REQUIRED) finds the package but gives this error: Target sqlpp11::ddl2cpp already defined

Looking at the CMakeList.txt file of sqlpp11-connector-postgresql it seems that there is no need find sqlpp1.
Removing find_package(Sqlpp11 CONFIG REQUIRED) solved the error, but does not solve the linking error:

/usr/bin/ld: libhdl_ip_registry_cpp_lib.a(auth_services.cpp.o): in function `db_helper::connect(std::function<void (sqlpp::postgresql::connection*)> const&)':
[...]/src/services/auth/../../utils/db_helper.hpp:33: undefined reference to `sqlpp::postgresql::connection::connection(std::shared_ptr<sqlpp::postgresql::connection_config> const&)'
/usr/bin/ld: [...]/src/services/auth/../../utils/db_helper.hpp:33: undefined reference to `sqlpp::postgresql::connection::~connection()'
/usr/bin/ld: [...]/src/services/auth/../../utils/db_helper.hpp:33: undefined reference to `sqlpp::postgresql::connection::~connection()'
/usr/bin/ld: libhdl_ip_registry_cpp_lib.a(auth_services.cpp.o): in function `sqlpp::postgresql::context_t::escape(std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&)':
/usr/local/include/sqlpp11/postgresql/connection.h:357: undefined reference to `sqlpp::postgresql::connection::escape(std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&) const'
/usr/bin/ld: libhdl_ip_registry_cpp_lib.a(auth_services.cpp.o): in function `std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > db_helper::connect<std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > >(std::function<std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > (sqlpp::postgresql::connection*)> const&)':
[...]/src/services/auth/../../utils/db_helper.hpp:26: undefined reference to `sqlpp::postgresql::connection::connection(std::shared_ptr<sqlpp::postgresql::connection_config> const&)'
/usr/bin/ld: [...]/src/services/auth/../../utils/db_helper.hpp:26: undefined reference to `sqlpp::postgresql::connection::~connection()'
/usr/bin/ld: [...]/src/services/auth/../../utils/db_helper.hpp:26: undefined reference to `sqlpp::postgresql::connection::~connection()'
/usr/bin/ld: libhdl_ip_registry_cpp_lib.a(auth_services.cpp.o): in function `sqlpp::postgresql::bind_result_t sqlpp::postgresql::connection::select<sqlpp::statement_t<void, sqlpp::insert_t, sqlpp::into_t<void, db::challenge_histories>, sqlpp::insert_list_t<void, sqlpp::assignment_t<sqlpp::column_t<db::challenge_histories, db::challenge_histories_::expires_in_t>, sqlpp::unsigned_integral_operand>, sqlpp::assignment_t<sqlpp::column_t<db::challenge_histories, db::challenge_histories_::challenge_hash_t>, sqlpp::text_operand> >, sqlpp::postgresql::no_on_conflict_t, sqlpp::postgresql::returning_column_list_t<void, sqlpp::column_t<db::challenge_histories, db::challenge_histories_::session_id_t> > > >(sqlpp::statement_t<void, sqlpp::insert_t, sqlpp::into_t<void, db::challenge_histories>, sqlpp::insert_list_t<void, sqlpp::assignment_t<sqlpp::column_t<db::challenge_histories, db::challenge_histories_::expires_in_t>, sqlpp::unsigned_integral_operand>, sqlpp::assignment_t<sqlpp::column_t<db::challenge_histories, db::challenge_histories_::challenge_hash_t>, sqlpp::text_operand> >, sqlpp::postgresql::no_on_conflict_t, sqlpp::postgresql::returning_column_list_t<void, sqlpp::column_t<db::challenge_histories, db::challenge_histories_::session_id_t> > > const&)':
/usr/local/include/sqlpp11/postgresql/connection.h:166: undefined reference to `sqlpp::postgresql::connection::select_impl(std::__cxx11::basic_string<char, std::char_traits<char>, std::allocator<char> > const&)'
/usr/bin/ld: libhdl_ip_registry_cpp_lib.a(auth_services.cpp.o): in function `void sqlpp::postgresql::bind_result_t::next<sqlpp::result_row_t<sqlpp::postgresql::connection, sqlpp::field_spec_t<db::challenge_histories_::session_id_t::_alias_t, sqlpp::text, false, false> > >(sqlpp::result_row_t<sqlpp::postgresql::connection, sqlpp::field_spec_t<db::challenge_histories_::session_id_t::_alias_t, sqlpp::text, false, false> >&)':
/usr/local/include/sqlpp11/postgresql/bind_result.h:74: undefined reference to `sqlpp::postgresql::bind_result_t::next_impl()'
/usr/bin/ld: libhdl_ip_registry_cpp_lib.a(auth_services.cpp.o): in function `void sqlpp::result_field_t<sqlpp::postgresql::connection, sqlpp::field_spec_t<db::challenge_histories_::session_id_t::_alias_t, sqlpp::text, false, false> >::_bind<sqlpp::postgresql::bind_result_t>(sqlpp::postgresql::bind_result_t&, unsigned long)':
[...]/vcpkg/installed/x64-linux/include/sqlpp11/data_types/text/result_field.h:49: undefined reference to `sqlpp::postgresql::bind_result_t::_bind_text_result(unsigned long, char const**, unsigned long*)'

Could you guide me for correctly installing this package? Thanks

Code generator question

Hi Matthijs,
I am about to update the structure of the column specifications a bit (this is very rare), I'll make a second issue regarding the changes.

I just looked at your script to see if it is affected by the change and stumbled across

        # Default value
        if column[5]:
            _writeLine(fd, 4, "using _must_not_insert = std::true_type;")
            _writeLine(fd, 4, "using _must_not_update = std::true_type;")

That says that columns with a default value cannot be changed at any time by sqlpp11.

"must not" == "not allowed to"

I am using those flags for columns with autoincrement.

Regards,

Roland

libpq-fe.h No such file or directory

I am trying to use this library but when i include #include <sqlpp11/postgresql/postgresql.h>
I am getting this error:

In file included from /usr/local/include/sqlpp11/postgresql/connection.h:35:0,
                 from /usr/local/include/sqlpp11/postgresql/postgresql.h:31,
                 from ./crow.cpp:6:
/usr/local/include/sqlpp11/postgresql/result.h:35:22: fatal error: libpq-fe.h: No such file or directory
compilation terminated.

I tried to install postgres driver but the error continues.

OS: Ubuntu 16.04.4 LTS (xenial)

I saw a related post here: #17 but i pass the cmake and make install commands successfully

Add support for date and time

JSON field in where clause

My table has a json column named properties. Currently I am using that field as text. But now I need to query the json attributes in where clause.

I'd like a feature like where(reports.properties["colour"] == "red") which will be translated to properties ->> 'colour' = 'red'

Can there be any hackish way to achieve this ? Like a special field type ? I was trying like the following. But I believe there would be some easy way to incorporate the existing operator overloading templates instead of writing one from scratch.

  template <typename Lhs>
  struct associative_t
  {
    using _lhs_t = Lhs;

    associative_t(_lhs_t lhs, const std::string& rhs) : _lhs(lhs), _rhs(rhs) {}
    associative_t(const associative_t&) = default;
    associative_t(associative_t&&) = default;
    associative_t& operator=(const associative_t&) = default;
    associative_t& operator=(associative_t&&) = default;
    ~associative_t() = default;

    _lhs_t _lhs;
    std::string _rhs;
    
    // TODO need operator overload
  };
  
  template <typename Context, typename Lhs>
  struct serializer_t<Context, associative_t<Lhs>>
  {
    using T = associative_t<Lhs>;
    using _serialize_check = serialize_check_of<Context, typename T::_lhs_t>;

    static Context& _(const T& t, Context& context)
    {
      serialize(simple_column(t._lhs), context);
      context << "->>";
      serialize_operand(t._rhs, context);
      return context;
    }
  };

Support of UPSERT using ON CONFICT DO UPDATE ?

Postgresql >= 9.5 support UPSERT feature (update or insert) using INSERT ... ON CONFLICT DO UPDATE SET ...
This doesn't look easy to implement in sqlpp11, is there any one working on that feature?

wrong time point inserted by prepared statement on windows

When inserting a timepoint with time zone on windows, the time that's inserted is modified by some hours when inserting with a prepared statement on windows. inserting with a prepared statement on linux is fine, and on windows with a direct insert it works fine as well.

example code that triggers the issue:

#include "tables/public/dates.h"

#include <chrono>
#include <iostream>
#include <date/date.h>
#include <sqlpp11/postgresql/postgresql.h>
#include <sqlpp11/sqlpp11.h>

int main()
{
	auto conf = std::make_shared<sqlpp::postgresql::connection_config>();
	conf->host = "127.0.0.1";
	conf->user = "test";
	conf->password = "test";
	conf->dbname = "test";

	auto connection = sqlpp::postgresql::connection(conf);

	connection.execute(R"(DROP TABLE IF EXISTS dates;)");
	connection.execute(R"(CREATE TABLE dates
               (
                 id VARCHAR(40) NOT NULL,
			     time TIMESTAMP WITH TIME ZONE
               ))");

	auto now = std::chrono::system_clock::now();

	model::dates dates;
	connection(
		sqlpp::insert_into(dates)
		.set(
			dates.id = "directly",
			dates.time = date::floor<std::chrono::microseconds>(now)));

	auto prepared = connection.prepare(sqlpp::insert_into(dates).set(dates.id = sqlpp::parameter(dates.id),
		dates.time = sqlpp::parameter(dates.time)));
	prepared.params.id = "prepared";
	prepared.params.time = date::floor<std::chrono::microseconds>(now);
	connection(prepared);

	using namespace date;
	std::cout << "original: " << now << std::endl;
	for (const auto& row : connection(sqlpp::select(dates.id, dates.time).from(dates).unconditionally()))
	{
		std::cout << row.id.value() << ": " << row.time.value() << std::endl;
	}
}

connection::execute is not implemented

Missing implementation of
size_t sqlpp::postgresql::connection::execute::execute(const std::string& command);

Better failure return types

Hi Matthijs,

MSVC users had a hard time debugging certain sqlpp11 errors, since MSVC did not run into the same static_assert as other compilers. As a result

I needed to find a better error reporting strategy
I am giving a talk about this at CppCon and MeetingC++ :-)
I just pushed a new release of sqlpp11 that is using more differentiated failure return types.

This has a small impact on the connector libraries as well. You should update your code like this:

See rbock/sqlpp11-connector-sqlite3@f2cd303

Best,

Roland

Code generator change

Hi Matthijs,

I am currently changing the sqlpp11's code generator requirements slightly.

Old code:

using _value_type = sqlpp::bigint;
struct _column_type
{
    using _must_not_insert = std::true_type;
    using _must_not_update = std::true_type;
    using _can_be_null = std::true_type;
};

New code:

using _traits = sqlpp::make_traits<sqlpp::bigint, sqlpp::tag::must_not_insert, sqlpp::tag::must_not_update, sqlpp::tag::can_be_null>;

This is currently on the develop branch. I hope to get the release ready by the end of this week.

Cheers,

Roland

Timezones not handled properly

(I've updated to the latest master for these tests)

When inserting a timestamp directly, timezones are not kept into account regardless of the column type in the db. What's more, the date is explicitly inserted as a timestamp, thus inserting what is a UTC time stamp directly into a table that might be using the 'Europe/Berlin' timezone. E.g. if both the machine and the db use that timezone during daylight savings time, at 14:00, it will insert 12:00, which will be stored as 12:00+02, or 10:00 UTC.

During Select, such a column gets manipulated, but wrongly, calculating one timestamp that is wrong, and returning another that is also wrong.

Prepared statements also do this calculating when retrieving a datetime, but on inserting do additional work in an attempt to insert the correct timestamp, however it seems to apply the local time timezone offset without changing the actual time. (i.e. 12:00UTC is inserted as 12:00+01 instead of 13:00+01)

See below a program that shows some insertions that go wrong. Set the timezone of the test database to a different one from the local machine to fully see the issue.

#include "tables/public/dates.h"

#include <chrono>
#include <iostream>
#include <sqlpp11/postgresql/postgresql.h>
#include <sqlpp11/sqlpp11.h>

#include <string>

#include <date/date.h>
#include <date/tz.h>

int main()
{
	auto conf = std::make_shared<sqlpp::postgresql::connection_config>();
	conf->host = "127.0.0.1";
	conf->user = "test";
	conf->password = "test";
	conf->dbname = "test";
  conf->debug = true;

	auto connection = sqlpp::postgresql::connection(conf);

	connection.execute(R"(DROP TABLE IF EXISTS dates;)");
	connection.execute(R"(CREATE TABLE dates
               (
                 id VARCHAR(40) NOT NULL,
			     time TIMESTAMP WITH TIME ZONE
               ))");

  using namespace date;
  using namespace std::chrono;

  // 2pm on August 1st 2019
  std::chrono::system_clock::time_point insert_time1 = sys_days{2019_y/August/1} + 14h;
  std::chrono::system_clock::time_point insert_time2 = sys_days{2019_y/November/1} + 14h;

	model::dates dates;
	connection(
		sqlpp::insert_into(dates)
		.set(
			dates.id = "direct_daylight_saving",
			dates.time = date::floor<std::chrono::microseconds>(insert_time1)));
	connection(
		sqlpp::insert_into(dates)
		.set(
			dates.id = "directl_no_daylight_saving",
			dates.time = date::floor<std::chrono::microseconds>(insert_time2)));

	auto prepared = connection.prepare(sqlpp::insert_into(dates).set(dates.id = sqlpp::parameter(dates.id),
		dates.time = sqlpp::parameter(dates.time)));
	prepared.params.id = "prepared_daylight_saving";
	prepared.params.time = date::floor<std::chrono::microseconds>(insert_time1);
	connection(prepared);
	prepared.params.id = "prepared_no_daylight_saving";
	prepared.params.time = date::floor<std::chrono::microseconds>(insert_time2);
	connection(prepared);

	using namespace date;
	std::cout << "original_daylight_saving: " << insert_time1 << std::endl;
	std::cout << "original_no_daylight_saving: " << insert_time2 << std::endl;
	for (const auto& row : connection(sqlpp::select(dates.id, dates.time).from(dates).unconditionally()))
	{
		std::cout << row.id.value() << ": " << row.time.value() << std::endl;
	}
}

Proposal:

If serialization of normal insertions can be specialized for postgresql, I recommend always inserting utc directly, without specifying the column type. Postgresql will ignore the timezone specification for normal timestamp columns, and parse it correctly for timestamp with time zone columns. E.g. insert VALUES ('2019-08-01 12:00:00+00') instead of VALUES (TIMESTAMP '2019-08-01 12:00:00')

When selecting data, postgresql will automatically add the right timezone offset for the database, for example returning '2019-08-01 14:00:00+02'. All that needs to be done when binding results is subtract the timezone offset if it is present.

It might be nice to require the timezone module for hinnant's date library as well, and map 'timestamp' to local_time and 'timestamp with time zone' to sys_time

Connection state

Hi,
Is it possible to get connection state and reconnect on error?

Missing build instructions

I am pretty new to cmake. So far I have succeeded in compiling and installing sqlpp11.

I have tried to call cmake with a number of options but failed. sqlpp11 and HinnantDate are cloned in paralell directories to this. sqlpp is also installed.

Uppercase tablename problem

The postgres lower case / upper case problem is not handled properly. I have a database with upper case table names. The generated sqlpp datamodel uses the upper case syntax.

terminate called after throwing an instance of 'sqlpp::exception'
what(): PostgreSQL error: PGRES_FATAL_ERROR: ERROR: column regref.pk does not exist
LINE 1: SELECT regref.PK,regref.UsrId,regref.FldId,regref.Nr,regref....

So in order to use postgres with sqlpp11 I will need to make all column names lower case in the database, which is not acceptable.

fatal error: postgresql/libpq-fe.h: No such file or directory

I've just updated sqlpp11 and your project and I can't compile it.

$ cmake ..
-- The CXX compiler identification is GNU 5.3.0
-- Check for working CXX compiler: /usr/bin/c++
-- Check for working CXX compiler: /usr/bin/c++ -- works
-- Detecting CXX compiler ABI info
-- Detecting CXX compiler ABI info - done
-- Detecting CXX compile features
-- Detecting CXX compile features - done
-- Using /usr/bin/c++ (compiler id: GNU)
-- Found Sqlpp11: /usr/local/include/sqlpp11
-- Configuring done
-- Generating done
-- Build files have been written to: /home/capsel/libs/sqlpp11-connector-postgresql/build
[capsel@ai build]$ make
Scanning dependencies of target sqlpp-postgresql
[ 20%] Building CXX object src/CMakeFiles/sqlpp-postgresql.dir/bind_result.cpp.o
In file included from /home/capsel/libs/sqlpp11-connector-postgresql/src/bind_result.cpp:34:0:
/home/capsel/libs/sqlpp11-connector-postgresql/src/detail/prepared_statement_handle.h:35:33: fatal error: postgresql/libpq-fe.h: No such file or directory
compilation terminated.
src/CMakeFiles/sqlpp-postgresql.dir/build.make:62: recipe for target 'src/CMakeFiles/sqlpp-postgresql.dir/bind_result.cpp.o' failed
make[2]: *** [src/CMakeFiles/sqlpp-postgresql.dir/bind_result.cpp.o] Error 1
CMakeFiles/Makefile2:85: recipe for target 'src/CMakeFiles/sqlpp-postgresql.dir/all' failed
make[1]: *** [src/CMakeFiles/sqlpp-postgresql.dir/all] Error 2
Makefile:127: recipe for target 'all' failed
make: *** [all] Error 2

postgresql version is 9.5.2.

Underlying connector does not support size()

Cannot get number of records returned while executing a select query.

SQLSTATE[42803]: Grouping error

Hi!
Postgresql forces columns to be in group_by clause or aggregate function when you use it side by side with other aggregate function, so compiling this code
https://gist.github.com/cszawisza/054a94c527cd86afb1a5 should fail, but instead it generates a buggy code, which generates SQLSTATE[42803]: Grouping error

Missing error check in last_insert_id()

Hi,

When calling last_insert_id(), the function doesn't check for errors (e.g. when passed an invalid table or column name). The application usually segfaults.

PR follows.

Select on empty table crash

I have an issue on a very simple example, it crashs on a very simple select command.

Here is my SQL create table command:

-- Database: sqlpp

DROP DATABASE sqlpp;

CREATE DATABASE sqlpp
  WITH OWNER = admin
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'en_US.UTF-8'
       LC_CTYPE = 'en_US.UTF-8'
       CONNECTION LIMIT = -1;

CREATE TABLE Person
(
id SERIAL PRIMARY KEY,
name varchar(255) NOT NULL
);

Here is the sample code:

#include <iostream>
#include <sqlpp11/sqlpp11.h>
#include <sqlpp11/postgresql/postgresql.h>

struct Id
{
  struct _name_t
  {
    static constexpr const char* _get_name() { return "id"; }
    template<typename T>
    struct _member_t
      {
        T id;
        T& operator()() { return id; }
        const T& operator()() const { return id; }
      };
  };
  using _traits = sqlpp::make_traits<sqlpp::integer, sqlpp::tag::must_not_insert, sqlpp::tag::must_not_update, sqlpp::tag::can_be_null>;
};

struct Name
{
    struct _name_t
    {
        static constexpr const char* _get_name() { return "name"; }
        template<typename T>
        struct _member_t
        {
            T name;
            T& operator()() { return name; }
            const T& operator()() const { return name; }
        };
    };
    using _traits = sqlpp::make_traits<sqlpp::varchar, sqlpp::tag::require_insert>;
};

struct Person: sqlpp::table_t<Person, Id, Name >
{
  struct _name_t
  {
    static constexpr const char* _get_name() { return "Person"; }
    template<typename T>
    struct _member_t
    {
      T tabPerson;
      T& operator()() { return tabPerson; }
      const T& operator()() const { return tabPerson; }
    };
  };
};

namespace postgresql = sqlpp::postgresql;

int main(int argc, char** argv)
{
    auto config = std::make_shared<postgresql::connection_config>();
    config->host = "127.0.0.1";
    config->port = 5432;
    config->user = "admin";
    config->dbname = "sqlpp";
    postgresql::connection db( config );

    ///@todo all
    Person p;

    for (const auto& row : db.run(select(p.name, p.id).from(p).where(p.name.like("Herb%"))))
    {
        int64_t id = row.id;
        std::string name = row.name;
    }

    return 0;
}

And here is the result:

./main
row number 0 is out of range 0..-1
row number 0 is out of range 0..-1
row number 0 is out of range 0..-1
Segmentation fault: 11

[question] Example of using extensions from contrib package

Hi,
I am new to this library so please bear with me. Is there any example on how to use extensions from contrib package or other custom functions with this connector? If we have to extend the source code, what is the best route to go about it?

Thanks in advance.

Cheers,

Missing operator

Hi,

I am developping an example with sqlpp11, but when using postgresql connector, I get this error:
applications/debtsReminder/src/main.cpp:67:28: error: type 'postgresql::connection' does not provide a call operator.
Line 67 is:
for (const auto& row : db(select(p.name).from(p).where(p.name.like("Herb%"))))

Here is my very simple main:

struct Id
{
  struct _name_t
  {
    static constexpr const char* _get_name() { return "id"; }
    template<typename T>
    struct _member_t
      {
        T id;
        T& operator()() { return id; }
        const T& operator()() const { return id; }
      };
  };
  using _traits = sqlpp::make_traits<sqlpp::integer, sqlpp::tag::must_not_insert, sqlpp::tag::must_not_update, sqlpp::tag::can_be_null>;
};

struct Name
{
    struct _name_t
    {
        static constexpr const char* _get_name() { return "name"; }
        template<typename T>
        struct _member_t
        {
            T name;
            T& operator()() { return name; }
            const T& operator()() const { return name; }
        };
    };
    using _traits = sqlpp::make_traits<sqlpp::varchar, sqlpp::tag::require_insert>;
};

struct TabPerson: sqlpp::table_t<TabPerson, Id, Name >
{
  struct _name_t
  {
    static constexpr const char* _get_name() { return "tab_person"; }
    template<typename T>
    struct _member_t
    {
      T tabPerson;
      T& operator()() { return tabPerson; }
      const T& operator()() const { return tabPerson; }
    };
  };
};

namespace postgresql = sqlpp::postgresql;

int main(int argc, char** argv)
{
    auto config = std::make_shared<postgresql::connection_config>();
    config->host = "127.0.0.1";
    config->port = 5432;
    config->user = "test";
    config->dbname = "sqlpp";
    config->debug = true;
    postgresql::connection db(config);

    ///@todo all
    TabPerson p;

    for (const auto& row : db(select(p.name).from(p).where(p.name.like("Herb%"))))
    {
            int64_t id = row.id;
            std::string name = row.name;
    }

    return 0;
}

Compile for armv7

I'm trying to compile using a docker for armv7 (https://github.com/dockcross/dockcross) using the command: cmake -DHinnantDate_ROOT_DIR=/date -DHinnantDate_INCLUDE_DIR=/date/include -DPostgreSQL_LIBRARY=/usr/lib/x86_64-linux-gnu/libpq.so -DPostgreSQL_INCLUDE_DIR=/usr/include/postgresql -DPostgreSQL_TYPE_INCLUDE_DIR=/usr/include/postgresql
All directories exist on my container, but I got this error message:

CMake Error at src/CMakeLists.txt:41 (add_library):
Target "sqlpp11-connector-postgresql-dynamic" links to target
"HinnantDate::Date" but the target was not found. Perhaps a find_package()
call is missing for an IMPORTED target, or an ALIAS target is missing?

CMake Error at src/CMakeLists.txt:31 (add_library):
Target "sqlpp11-connector-postgresql" links to target "HinnantDate::Date"
but the target was not found. Perhaps a find_package() call is missing for
an IMPORTED target, or an ALIAS target is missing?

CMake Error at tests/CMakeLists.txt:44 (add_executable):
Target "sqlpp11-connector-postgresql_tests" links to target
"HinnantDate::Date" but the target was not found. Perhaps a find_package()
call is missing for an IMPORTED target, or an ALIAS target is missing?

cmake for HinnantDate was successfully executed.
cmake && make && make install for sqlpp11 was also successfully executed (I've removed the tests directory because they weren't successfully running).

Primary key value of the inserted row

Once a record is inserted it is required to know the id of that inserted record. Preferably the entire record inserted (with default values put in).

How to use it with sqlpp::connection_pool

Hi!
Seems like you've missed required using or typedef for connection entity in postgresql connection_config. Without it I cannot use it with connection_pool.

E.g. in sqlpp11-connector-mysql it's done: https://github.com/rbock/sqlpp11-connector-mysql/blob/master/include/sqlpp11/mysql/connection_config.h#L39

Thank you!

P.S. The same issue I've met with sqlpp-connector-sqlite3.

Option enable_tests

Is there a need for 'ENABLE_TESTS'? Why not building the tests anyway?

The tests are only executed when you do a 'make test'.

Regard, Matthijs

How to contribute

Hello guys!

Good project :) I'd like to contribute. How can I do that?
Thanks!

default move assignment connection& operator=(connection&&) does not compile

The following code does not compile with the default move assignment operator

sqlpp11_data_store.h

#ifndef SQLPP11_DATA_STORE_H
#define SQLPP11_DATA_STORE_H

#include <string>

#include <sqlpp11/sqlpp11.h>
#include <sqlpp11/postgresql/postgresql.h>

class DataStore2 {
public:
    void
    open (const std::string & name);

//private:
    sqlpp::postgresql::connection db;
};

extern DataStore2 store2;

#endif // SQLPP11_DATA_STORE_H

sqlpp11_data_store.c

#include "sqlpp11_data_store.h"
#include <sqlpp11/postgresql/postgresql.h>

void
DataStore2::open (const std::string & name)
{
    auto config = std::make_shared <sqlpp::postgresql::connection_config> ();
    config->dbname = name;
    db = sqlpp::postgresql::connection {config};
}

Replacing the default one by a declaration in include/sqlpp11/postgresql/connection.h allows the code fragment to compile. I've create the below dummy move assignment and the application links fine.

connection& connection::operator= (connection&& other)
{
  if (this != &other) {
    // TODO: check this logic
    this->_transaction_active = other._transaction_active;
    this->_handle = std::move(other._handle);
  }
  return *this;
}

Connection (& everything) silently fails

I've just attempted to get started with this library but it seems everything silently fails with no indication anything is wrong.

  dbconn & conn() {
    static bool connection_made = false;
    if (!connection_made) {
      auto config = Config::get_config();
      dbconfig->host = config.dbhost;
      dbconfig->user = config.dbuser;
      dbconfig->password = config.dbpass;
      dbconfig->dbname = config.dbname;
      connection_made = true;
      std::cout << "Connected to database!\n";
    }
    static dbconn due_db(dbconfig);
    return due_db;
  }

I attempt to connect to my database & then insert some test values. However it does not seem like any connection (or anything happens) in fact I can put whatever junk values I like for the database config and there won't be any error at all -- same as using (what I think) is the correct config.

Even if I've done something wrong surely there should be some from of error if a connection or insert fails. Right now I have no idea what the issue is.

Add UUID support

Hi!
Seems like PostgreSQL connector doesn't support yet UUID type. Is there any easy way to work with UUID?
I've found similar discussion here: rbock/sqlpp11#166

Missing support for null text fields

A previous pull request got lost.
I've a change ready.

Returning clause in insert/update statements

Are there any plans to implement RETURNING clause in driver?

fix double precision floating point.

Hi,
The use of std::to_string(double) narrows the value to a fixed precision.
Find attached a patch.
Best regards.
fp_values.zip

boolean expressions always treated as true

HI!

When reading a boolean value from db, driver always return true:
https://gist.github.com/cszawisza/9d2b09a5feee3e42e0fe
gives "User exists"
System openSuse 13.2
Postgresql 9.3.6

I don't know what info can be helpful, so if you need something please let me know

Timezone conversion

Hi matthijs,

Recently i noticed a problem with extraction timezone from the db.

Test generates datetime '2018-03-26 22:46:03.478427'
It's uploaded as '2018-03-26 22:46:03.478427-8:0' ('2018-03-27 06:46:03.478427+0')
But the server returns '2018-03-26 23:46:03.478427-07' ('2018-03-27 06:46:03.478427+0')

They are same, but as I understand currently timezone is ignored.
Have I missed something?

PostgreSQL debug: preparing: UPDATE tab_date_time SET col_day_point=$1,col_time_point=$2
...
---- running prepared update ----
...
PostgreSQL debug: binding date_time parameter string: 2018-03-26 22:46:03.478427-8:0
...
---- finished prepared update ----
PostgreSQL debug: executing: SELECT tab_date_time.col_day_point,tab_date_time.col_time_point FROM tab_date_time
...
PostgreSQL debug: binding date_time result at index: 1
PostgreSQL debug: got date_time string: 2018-03-26 23:46:03.478427-07
PostgreSQL debug: calculated timestamp 
900-01-01 00:00:00 CEST
141: TIMESTAMP '2018-03-26 23:46:03.478427' != TIMESTAMP '2018-03-26 22:46:03.478427'

Visual Studio support.

Hi,
I'm trying to build the library on Windows but there are some errors:
(e.g include\sqlpp11/postgresql/returning_column_list.h(439): error C3520: 'T': parameter pack must be expanded in this context).
Also, there is no appveyor build script to test it.

Returning clause not compiling on insert without set

Hello,
I have a table with only an auto-increment primary key column.
This code fails to compile:
const auto id = db(sql::insert_into(tb).returning(tb.id)).front().id;
with:
error: ‘struct sqlpp::assert_insert_values_t’ has no member named ‘front’
and
error: ‘_’ is not a member of ‘sqlpp::run_check_t<sqlpp::postgresql::context_t, sqlpp::statement_t<void, sqlpp::insert_t, sqlpp::into_t<void, db::tb>, sqlpp::no_insert_value_list_t, sqlpp::postgresql::returning_column_list_t<void, sqlpp::column_t<db::tb, db::tb::Id> > > > {aka sqlpp::assert_insert_values_t}’

Trying to build tells me HinnantDate is found, then tells me it's not found

Making the project first tells me that it's found HinnantDate, but then tells me that it couldn't be found.
This is the output from my attempt to build the project:

cmake .. -DHinnantDate_ROOT_DIR=/src/date/
---> Running in 0fccd9d1026b
-- The CXX compiler identification is GNU 6.3.0
-- Check for working CXX compiler: /usr/local/bin/c++
-- Check for working CXX compiler: /usr/local/bin/c++ -- works
-- Detecting CXX compiler ABI info
-- Detecting CXX compiler ABI info - done
-- Detecting CXX compile features
-- Detecting CXX compile features - done
-- Found HinnantDate: /src/date
CMake Error at CMakeLists.txt:32 (find_package):
Found package configuration file:

/usr/local/lib/cmake/Sqlpp11/Sqlpp11Config.cmake
but it set Sqlpp11_FOUND to FALSE so package "Sqlpp11" is considered to be
NOT FOUND. Reason given by package:

Sqlpp11 could not be found because dependency HinnantDate could not be
found.

-- Configuring incomplete, errors occurred!

I'm afraid I'm not very experienced with CMake -- could you please advise me as to what I've done wrong, here?
Thanks.

AppVeyor failed

Hi, AppVeyor has been failing for the last 2 month for MSVC.
Has MSVC support been droped?

Using distinct flag couses a compiler error

Hi
In my application I need to use distinct keyword in one of my queries, but sadly code below:

        auto conf = std::shared_ptr<sqlpp::postgresql::connection_config>( new sqlpp::postgresql::connection_config );
        conf->host = "localhost";
        conf->dbname = "postgres";
        conf->user = "postgres";
        conf->password = "postgres";
        //    conf->debug = true;

        sqlpp::postgresql::connection db (conf);
        schema::users u;
        db( select( sqlpp::distinct, u.c_unixperms ).from(u).where(true) );

generates

no member named 'from' in 'sqlpp::bad_statement'
        db( select( sqlpp::distinct, u.c_unixperms ).from(u).where(true) );
            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ^
static_assert failed "at least one argument is not a selectable expression in columns()"
                                                static_assert(decltype(_check_args(args...))::value, "at least one argument is not a selectable expression in columns()");
                                                ^             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

where schema::users -> https://github.com/cszawisza/QElectricPartsCatalog/blob/dev/server/sql/users.h

Regards,
cszawisza

Why boost dependency?

Hey, I was just wondering, why does this connector have Boost as a dependency while sqlpp11 doesn't?

Update to sqlpp 0.30

Hi Matthijs,

sqlpp11 0.30 finally got released a few days ago. It requires minor changes in the code generator and you might want to adjust the connection object a bit, too (see for instance tests/MockDb.h). This can help to reduce the amount of template error code when something is done wrong.

The changes should be fairly simple, but that is my biased opinion, of course. If you have questions, please let me know.

Best,

Roland

Add additional types to ddl2cpp.py?

Not sure whether this is an issue, but I wanted to leave my information somewhere. When I tried to generate code based on an existing internal testing database, I had to add the following types to the type map in ddl2cpp.py:

'name' : 'varchar',
'oid' : 'varchar',
'ARRAY' : 'varchar',
'pg_node_tree' : 'varchar',
'"char"' : 'varchar',
'real' : 'floating_point',
'xid' : 'varchar',
'regproc' : 'varchar',
'double precision' : 'floating_point',
'pg_lsn' : 'varchar',
'inet' : 'varchar',
'abstime' : 'time_point',
'interval' : 'varchar',
'bytea' : 'varchar',
'anyarray' : 'varchar',

I didn't investigate further, also haven't tried the generated code, but at least the code generation succeeded. Most notable types that might be required in the future are real and name. Some of the other types are internal to postgres.

Missing default constructor for connection::connection()

Looks like I forgot to share a change I did in my "old" tree. The default constructor for connection::connection() is missing. As such my application fails to compile My version was quite simple.

connection::connection()
{
}

In need a default constructor because my DataClass is default construable without an open session and an explicit open is issued. This is because I have a single global variable for the Data class. I tried to include the old code but with all the recent changes I get run time errors after a few SQL statements, which is very odd or maybe because of other changes (date?, the pull request you rejected today) I made in the past.

class DataStore {
public:
//    DataStore() {}
    void
    open (const std::string & name);

    sqlpp::transaction_t<sqlpp::postgresql::connection>
    begin_transaction ();
    static void
    commit_transaction (sqlpp::transaction_t<sqlpp::postgresql::connection> & transaction);
    static void
    rollback_transaction (sqlpp::transaction_t<sqlpp::postgresql::connection> & transaction);

    sqlpp::postgresql::connection db;
};

tests does not compile since commit ac500e2 1 of sqlpp

test fails to compile with the following errors:

In file included from /home/serge/githubs/sqlpp11-connector-postgresql/include/sqlpp11/postgresql/postgresql.h:31,
                 from /home/serge/githubs/sqlpp11-connector-postgresql/tests/DateTest.cpp:30:
/home/serge/githubs/sqlpp11-connector-postgresql/include/sqlpp11/postgresql/connection.h: In instantiation of ‘decltype (((sqlpp::postgresql::connection*)this)->sqlpp::postgresql::connection::_prepare(t, sqlpp::prepare_check_t<sqlpp::postgresql::context_t, T>{})) sqlpp::postgresql::connection::prepare(const T&) [with T = sqlpp::statement_t<void, sqlpp::update_t, sqlpp::single_table_t<void, TabDateTime>, sqlpp::update_list_t<void, sqlpp::assignment_t<sqlpp::column_t<TabDateTime, TabDateTime_::ColDayPoint>, sqlpp::parameter_t<sqlpp::day_point, sqlpp::column_t<TabDateTime, TabDateTime_::ColDayPoint> > >, sqlpp::assignment_t<sqlpp::column_t<TabDateTime, TabDateTime_::ColTimePoint>, sqlpp::parameter_t<sqlpp::time_point, sqlpp::column_t<TabDateTime, TabDateTime_::ColTimePoint> > > >, sqlpp::where_t<void, sqlpp::unconditional_t> >; decltype (((sqlpp::postgresql::connection*)this)->sqlpp::postgresql::connection::_prepare(t, sqlpp::prepare_check_t<sqlpp::postgresql::context_t, T>{})) = sqlpp::prepared_update_t<sqlpp::postgresql::connection, sqlpp::statement_t<void, sqlpp::update_t, sqlpp::single_table_t<void, TabDateTime>, sqlpp::update_list_t<void, sqlpp::assignment_t<sqlpp::column_t<TabDateTime, TabDateTime_::ColDayPoint>, sqlpp::parameter_t<sqlpp::day_point, sqlpp::column_t<TabDateTime, TabDateTime_::ColDayPoint> > >, sqlpp::assignment_t<sqlpp::column_t<TabDateTime, TabDateTime_::ColTimePoint>, sqlpp::parameter_t<sqlpp::time_point, sqlpp::column_t<TabDateTime, TabDateTime_::ColTimePoint> > > >, sqlpp::where_t<void, sqlpp::unconditional_t> > >]’:
/home/serge/githubs/sqlpp11-connector-postgresql/tests/DateTest.cpp:131:35:   required from here
/home/serge/githubs/sqlpp11-connector-postgresql/include/sqlpp11/postgresql/connection.h:319:60: error: ‘_’ is not a member of ‘sqlpp::prepare_check_t<sqlpp::postgresql::context_t, sqlpp::statement_t<void, sqlpp::update_t, sqlpp::single_table_t<void, TabDateTime>, sqlpp::update_list_t<void, sqlpp::assignment_t<sqlpp::column_t<TabDateTime, TabDateTime_::ColDayPoint>, sqlpp::parameter_t<sqlpp::day_point, sqlpp::column_t<TabDateTime, TabDateTime_::ColDayPoint> > >, sqlpp::assignment_t<sqlpp::column_t<TabDateTime, TabDateTime_::ColTimePoint>, sqlpp::parameter_t<sqlpp::time_point, sqlpp::column_t<TabDateTime, TabDateTime_::ColTimePoint> > > >, sqlpp::where_t<void, sqlpp::unconditional_t> > >’ {aka ‘sqlpp::consistent_t’}
         sqlpp::prepare_check_t<_serializer_context_t, T>::_();
         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^~

The following patch fixes the problem:

[serge@archlinux sqlpp11-connector-postgresql]$ git diff include/sqlpp11/postgresql/connection.h 
diff --git a/include/sqlpp11/postgresql/connection.h b/include/sqlpp11/postgresql/connection.h
index 3405ff8..668e26c 100644
--- a/include/sqlpp11/postgresql/connection.h
+++ b/include/sqlpp11/postgresql/connection.h
@@ -299,7 +299,7 @@ namespace sqlpp
       template <typename T>
       auto operator()(const T& t) -> decltype(this->_run(t, sqlpp::run_check_t<_serializer_context_t, T>{}))
       {
-        sqlpp::run_check_t<_serializer_context_t, T>::_();
+        sqlpp::run_check_t<_serializer_context_t, T>{};
         return _run(t, sqlpp::run_check_t<_serializer_context_t, T>{});
       }
 
@@ -316,7 +316,7 @@ namespace sqlpp
       template <typename T>
       auto prepare(const T& t) -> decltype(this->_prepare(t, sqlpp::prepare_check_t<_serializer_context_t, T>{}))
       {
-        sqlpp::prepare_check_t<_serializer_context_t, T>::_();
+        sqlpp::prepare_check_t<_serializer_context_t, T>{};
         return _prepare(t, sqlpp::prepare_check_t<_serializer_context_t, T>{});
       }

Compilation error when using group by

I tried to use count while performing join using group by

auto records = _connection(
    select(
        reports.id, 
        reports.aoi, 
        reports.toi, 
        reports.roi, 
        reports.story, 
        reports.created, 
        reports.properties,
        reports.author,
        users.firstName, 
        users.lastName,
        count(places.id)
    ).from(
        reports.join(users).on(reports.author == users.id).left_outer_join(places).on(reports.id == places.report)
    ).group_by(reports.id).limit(limit).offset(offset).order_by(reports.id.asc()).unconditionally()
);
nlohmann::json spatial_reports = nlohmann::json::array();
std::size_t last_id;
for(const auto& record: records){
    // ...
}
if(records.size() > 0){
    // ...
}

It gave me compilation errors.

/home/neel/Projects/wee/spatial.cpp:259:29: error: ‘void __for_begin ’ has incomplete type
  259 |     for(const auto& record: records){
      |                             ^~~~~~~
/home/neel/Projects/wee/spatial.cpp:259:29: error: ‘void __for_end ’ has incomplete type
/home/neel/Projects/wee/spatial.cpp:288:16: error: ‘struct sqlpp::assert_no_unknown_aggregates_t’ has no member named ‘size’

function connectUsing

The function connectUsing is used in tests (for example Returning.cpp) but it is never implemented. According to its spec it should be non throwing:

void connectUsing(const std::shared_ptr<connection_config>& config) noexcept(false);

In every test I see the following:

try
{
  db.connectUsing(config);
}
catch (const sql::broken_connection&)
{
  std::cerr << "For testing, you'll need to create a database sqlpp_postgresql" << std::endl;
  throw;
}

That needs an implementation of connectUsing. But currently there is no implementation for this function.

Version compatibility

Hi,
Thanks for the connector. I'm trying to add it to the Conan package manager now.
Can this library be used with the new sqlpp11 version (0.54)?
Also, will you add tags to the project?

if schema is public, generated code does not compile

public is a c++ keyword, so using that for a namespace does not work. Perhaps the schema name could be changed or the script could detect when schema is called public, as that is quite common

incorporate changes from fork @cszawisza

Hi,

I'm not sure how to contact you through github, but your repo doesn't have an issues tab so I'll try to contact you by using this issue report @cszawisza .

May I incorporate your changes in the fork you created in this repository? Of course I'll put you in the copyright notice so that it is obvious that you helped with this library.

Regards, Matthijs

Dynamic loading fails on ArchLinux 2018.4

On ArchLinux cmake creates an invalid compile_commands.json where the variable DSQLPP_DYNAMIC_LOADING_FILENAME is filled with the value of "usr/usr/lib/libpq.so.so".

Changing src/CMakeLists.txt with

if (WIN32)
        get_filename_component(POSTGRESQL_LIB_FILE ${PostgreSQL_LIBRARIES} NAME)
elseif(APPLE)
        set(POSTGRESQL_LIB_FILE "lib${PostgreSQL_LIBRARIES}.dylib")
else()
        #set(POSTGRESQL_LIB_FILE "lib${PostgreSQL_LIBRARIES}.so")
        set(POSTGRESQL_LIB_FILE "${PostgreSQL_LIBRARIES}")
endif()

Fixes the problem.

matthijs / sqlpp11-connector-postgresql Goto Github PK

sqlpp11-connector-postgresql's Issues

Proposal:

Recommend Projects

Recommend Topics

Recommend Org