Custom data types

[matthijs]

I am looking into migrating to sqlpp23. I found one issue.

I am using a lot of strong types. Mapping enum fields in the database to enum fields in C++, strong typed id's, so that mistakes like: tableA_id == tableB_id cannot happen. Well, a small reproducible example says more then words:

#include <sqlpp23/mock_db/mock_db.h>
#include <sqlpp23/sqlpp23.h>
#include <sqlpp23/tests/core/make_test_connection.h>

// Strong type
struct TestId {
    int64_t value{0};
    explicit TestId(int64_t v) : value(v) {}
    TestId() = default;
    bool operator==(const TestId&) const = default;
};


// Table definition
struct Test_ {
    struct Id {
        SQLPP_CREATE_NAME_TAG_FOR_SQL_AND_CPP(id, id);
        using data_type   = TestId;           // <-- strong type as data_type
        using has_default = std::false_type;
    };
    struct Symbol {
        SQLPP_CREATE_NAME_TAG_FOR_SQL_AND_CPP(symbol, symbol);
        using data_type   = ::sqlpp::text;
        using has_default = std::false_type;
    };
    SQLPP_CREATE_NAME_TAG_FOR_SQL_AND_CPP(stock, stock);
    template<typename T>
    using _table_columns = sqlpp::table_columns<T, Id, Symbol>;
    using _required_insert_columns = sqlpp::detail::type_set<
        sqlpp::column_t<sqlpp::table_t<Test_>, Id>,
        sqlpp::column_t<sqlpp::table_t<Test_>, Symbol>>;
};
using Test = ::sqlpp::table_t<Test_>;


// Glue needed for sqlpp
template <>
struct sqlpp::data_type_of<TestId> {
    using type = sqlpp::integral;
};

template <typename Context>
auto to_sql_string(Context& ctx, const TestId& id) {
    return to_sql_string(ctx, id.value);
}

template <>
struct sqlpp::result_data_type_of<TestId> {
    using type = TestId;    // result row will hold a TestId directly
};
// End glue


int main() {
    auto db = sqlpp::mock_db::make_test_connection({});
    const Test stock;

    for (const auto& row : db(select(all_of(stock)).from(stock))) {
        TestId id = row.id;
        (void)id;
    }

    return 0;
}

Compiling with: clang++ -std=c++23 -I include -I tests/include test.cpp gave the following output:

In file included from test.cpp:1:
In file included from include/sqlpp23/mock_db/mock_db.h:30:
In file included from include/sqlpp23/mock_db/database/connection.h:32:
include/sqlpp23/core/query/result_row.h:55:12: error: no matching member function for call to
      'read_field'
   55 |     target.read_field(index, _field::operator()());
      |     ~~~~~~~^~~~~~~~~~
include/sqlpp23/core/query/result_row.h:72:36: note: in instantiation of function template
      specialization 'sqlpp::detail::result_field<0, sqlpp::field_spec_t<Test_::Id::_sqlpp_name_tag,
      TestId>>::_read_field<sqlpp::mock_db::text_result_t>' requested here
   72 |     (result_field<Is, FieldSpecs>::_read_field(target), ...);
      |                                    ^
include/sqlpp23/core/query/result_row.h:134:12: note: in instantiation of function template
      specialization 'sqlpp::detail::result_row_impl<std::integer_sequence<unsigned long, 0, 1>,
      sqlpp::field_spec_t<Test_::Id::_sqlpp_name_tag, TestId>, sqlpp::field_spec_t<Test_::Symbol::_sqlpp_name_tag,
      sqlpp::text>>::_read_fields<sqlpp::mock_db::text_result_t>' requested here
  134 |     _impl::_read_fields(target);
      |            ^
include/sqlpp23/core/query/result_row.h:151:9: note: in instantiation of function template
      specialization 'sqlpp::result_row_t<sqlpp::field_spec_t<Test_::Id::_sqlpp_name_tag, TestId>,
      sqlpp::field_spec_t<Test_::Symbol::_sqlpp_name_tag, sqlpp::text>>::_read_fields<sqlpp::mock_db::text_result_t>'
      requested here
  151 |     row._read_fields(target);
      |         ^
include/sqlpp23/mock_db/text_result.h:92:42: note: in instantiation of function template specialization
      'sqlpp::detail::result_row_bridge::read_fields<sqlpp::field_spec_t<Test_::Id::_sqlpp_name_tag, TestId>,
      sqlpp::field_spec_t<Test_::Symbol::_sqlpp_name_tag, sqlpp::text>, sqlpp::mock_db::text_result_t>' requested here
   92 |       sqlpp::detail::result_row_bridge{}.read_fields(result_row, *this);
      |                                          ^
include/sqlpp23/core/result.h:70:13: note: in instantiation of function template specialization
      'sqlpp::mock_db::text_result_t::next<sqlpp::result_row_t<sqlpp::field_spec_t<Test_::Id::_sqlpp_name_tag, TestId>,
      sqlpp::field_spec_t<Test_::Symbol::_sqlpp_name_tag, sqlpp::text>>>' requested here
   70 |     _result.next(_result_row);
      |             ^
include/sqlpp23/core/clause/select_column_list.h:166:12: note: in instantiation of member function
      'sqlpp::result_t<sqlpp::mock_db::text_result_t, sqlpp::result_row_t<sqlpp::field_spec_t<Test_::Id::_sqlpp_name_tag,
      TestId>, sqlpp::field_spec_t<Test_::Symbol::_sqlpp_name_tag, sqlpp::text>>>::result_t' requested here
  166 |     return {statement_handler_t{}.select(std::forward<Statement>(self), db)};
      |            ^
include/sqlpp23/core/query/statement_handler.h:38:47: note: in instantiation of function template
      specialization 'sqlpp::select_result_methods_t<sqlpp::column_t<sqlpp::table_t<Test_>, Test_::Id>,
      sqlpp::column_t<sqlpp::table_t<Test_>, Test_::Symbol>>::_run<const sqlpp::statement_t<sqlpp::select_t,
      sqlpp::select_column_list_t<std::tuple<>, std::tuple<sqlpp::column_t<sqlpp::table_t<Test_>, Test_::Id>,
      sqlpp::column_t<sqlpp::table_t<Test_>, Test_::Symbol>>>, sqlpp::from_t<sqlpp::table_t<Test_>>, sqlpp::no_where_t,
      sqlpp::no_group_by_t, sqlpp::no_having_t, sqlpp::no_order_by_t, sqlpp::no_limit_t, sqlpp::no_offset_t,
      sqlpp::no_union_t, sqlpp::no_for_update_t> &, sqlpp::mock_db::connection_base>' requested here
   38 |     return std::forward<Statement>(statement)._run(db);
      |                                               ^
include/sqlpp23/mock_db/text_result.h:106:8: note: candidate function not viable: no known conversion
      from 'TestId' to 'bool &' for 2nd argument
  106 |   void read_field(size_t index, bool& value) {
      |        ^                        ~~~~~~~~~~~
include/sqlpp23/mock_db/text_result.h:111:8: note: candidate function not viable: no known conversion
      from 'TestId' to 'double &' for 2nd argument
  111 |   void read_field(size_t index, double& value) {
      |        ^                        ~~~~~~~~~~~~~
include/sqlpp23/mock_db/text_result.h:115:8: note: candidate function not viable: no known conversion
      from 'TestId' to 'int64_t &' (aka 'long &') for 2nd argument
  115 |   void read_field(size_t index, int64_t& value) {
      |        ^                        ~~~~~~~~~~~~~~
include/sqlpp23/mock_db/text_result.h:119:8: note: candidate function not viable: no known conversion
      from 'TestId' to 'uint64_t &' (aka 'unsigned long &') for 2nd argument
  119 |   void read_field(size_t index, uint64_t& value) {
      |        ^                        ~~~~~~~~~~~~~~~
include/sqlpp23/mock_db/text_result.h:123:8: note: candidate function not viable: no known conversion
      from 'TestId' to 'std::span<const uint8_t> &' (aka 'span<const unsigned char> &') for 2nd argument
  123 |   void read_field(size_t index, std::span<const uint8_t>& value) {
      |        ^                        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
include/sqlpp23/mock_db/text_result.h:129:8: note: candidate function not viable: no known conversion
      from 'TestId' to 'std::string_view &' (aka 'basic_string_view<char> &') for 2nd argument
  129 |   void read_field(size_t index, std::string_view& value) {
      |        ^                        ~~~~~~~~~~~~~~~~~~~~~~~
include/sqlpp23/mock_db/text_result.h:134:8: note: candidate function not viable: no known conversion
      from 'TestId' to 'std::chrono::sys_days &' (aka 'time_point<std::chrono::system_clock, duration<long, ratio<86400>>>
      &') for 2nd argument
  134 |   void read_field(size_t index, std::chrono::sys_days& value) {
      |        ^                        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
include/sqlpp23/mock_db/text_result.h:162:8: note: candidate function not viable: no known conversion
      from 'TestId' to '::sqlpp::chrono::sys_microseconds &' (aka 'time_point<std::chrono::system_clock, duration<long,
      ratio<1, 1000000>>> &') for 2nd argument
  162 |   void read_field(size_t index, ::sqlpp::chrono::sys_microseconds& value) {
      |        ^                        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
include/sqlpp23/mock_db/text_result.h:190:8: note: candidate function not viable: no known conversion
      from 'TestId' to '::std::chrono::microseconds &' (aka 'duration<long, ratio<1, 1000000>> &') for 2nd argument
  190 |   void read_field(size_t index, ::std::chrono::microseconds& value) {
      |        ^                        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
include/sqlpp23/mock_db/text_result.h:219:8: note: candidate template ignored: could not match
      'std::optional<T>' against 'TestId'
  219 |   auto read_field(size_t index, std::optional<T>& value) -> void {
      |        ^
1 error generated.

As you can see in the example, I am implementing the to_sql_string function, I expected something like a from_sql_string to do the other way around.

With sqlpp11 I had implementation of different data_types which handled exactly this. Hopefully this functionality can be implemented in sqlpp23!

Thank you for the wonderful library!

Regards, Matthijs

[MeanSquaredError]

@rbock

I just checked the source code of the connectors and it seems that at the moment we don't have an easy way of adding custom types, because for each connector that uses a custom type (e.g. CustomType) we need to implement the corresponding method of test_result_t:

void read_field(size_t _index, CustomType& value);

and there is no way of doing that without modifying the connector source code.

I suggest adding an extra template method for the strong enums (enum class), something like this:

template <typename E>
    requires std::is_enum_v<E>
void read_field(size_t _index, E& value)
{
    read_field(_index, static_cast<std::underlying_type_t<E>&> (value));
}

WDYT?

EDIT: I just tested it and it won't work with the example provided by @matthijs, because he doesn't really use enum class but a custom class type instead. So I guess this issue will require a bit of thinking about how exactly sqlpp23 should behave from a user point of view.

[matthijs]

Isn't it possible to hand it off to the user to implement?

template <typename CustomType>
  //requires ??
void read_field(size_t _index, CustomType& value)
{
    from_sql_string(PQgetvalue(_pg_result.get(), _row_index, index), value)
}

This is just pseudo code, but hopefully the idea is clear.

[MeanSquaredError]

@matthijs

Isn't it possible to hand it off to the user to implement?

Allowing arbitrary user types in the result row is one option. The other is just allowing enum and enum class in the result types, which will probably handle 95% of the non-standard cases where users want custom result types.

So I really see more benefit from support for enum class instead of support for arbitrary user types, because enum class would require less glue code from the user . However it is probably possible to have support for both (enum class and arbitrary user types) in sqlpp23, provided that users really need both.

In your case do you really need the TestId custom type or do you think that your program get away just with enum class ?

[MeanSquaredError]

@matthijs

Handling arbitrary result types through a user-defined function (in your example you called it from_sql_string) is definitely possible, but such a custom function will have to be implemented per connector, because it wouldn't actually convert from a string but rather from some connector-specific binary format (and even if it actually were to convert from a string, the string format would most likely be connector-specific).

[matthijs]

I have simplified the testcase, but in my application I am using uuid as type and using it as primary key/foreign keys in the database. Than I have for every primary key its own type. This prevents me from doing weird things at compile time (for example comparing with a key from a different table in the where statement).

db(select(all_of(tab)).from(tab).where(tab.id == id_from_different_table));

I have a lot of other types which maps to the type in the database. So no, I cannot get away with simple enum class, although I also have enum's in my database.

I understand that such a function is connector specific. Maybe a different function name per connector? My impression is that people who are working with a database usually also knows how to handle the binary format right? Or am I to optimistic here?

The function could also be called: parse_into_postgres(...), parse_into_mysql(...) etc.

Would that be an idea to handle this case?

[MeanSquaredError]

I have simplified the testcase, but in my application I am using uuid as type and using it as primary key/foreign keys in the database. Than I have for every primary key its own type. This prevents me from doing weird things at compile time (for example comparing with a key from a different table in the where statement).

db(select(all_of(tab)).from(tab).where(tab.id == id_from_different_table));

I have a lot of other types which maps to the type in the database. So no, I cannot get away with simple enum class, although I also have enum's in my database.

Thank you for the clarificaiton. I see why enum class is not sufficient in your case.

I understand that such a function is connector specific. Maybe a different function name per connector? My impression is that people who are working with a database usually also knows how to handle the binary format right? Or am I to optimistic here?

The function could also be called: parse_into_postgres(...), parse_into_mysql(...) etc.

Would that be an idea to handle this case?

I think that we don't really need different function names. I think that the sqlpp23 style is to use the same function name and do overload resolution based on the parameter types. So in this case you would just define the same function but with different parameter types, e.g.

void from_sql_string(CustomType1& value, MYSQL_RES* resource, size_t index);
void from_sql_string(CustomType1& value, PGresult* resource, size_t index);
void from_sql_string(CustomType2& value, MYSQL_RES* resource, size_t index);
void from_sql_string(CustomType2& value, PGresult* resource, size_t index);

Then it would automatically do overload resolution based on the custom destination type and the resource source type.

Actually there could be multiple connectors using the same underlying resource type (e.g. the MySQL and MariaDB connectors would both use resources of type MYSQL_RES, so maybe the function should actually look like this:

template <typename value_t, typename connector_t>
void from_sql_string(value_t& value, typename connector_t::resource_t* resource, size_t index);

(I my writing this off the top of my head, so it may need some adjustments)

Then the user would specialize from_sql_string based on his connector and custom type and the library would call the function if it exists to extract the corresponding field from the result.

The first version has the benefit that it allows the user to define from_sql_string in his own namespace and then the library would still find the function using ADL, while the second (template) version of the function would have to be specialized in either in the sqlpp's namespace or in the global namespace.

[matthijs]

I think that we don't really need different function names. I think that the sqlpp23 style is to use the same function name and do overload resolution based on the parameter types. So in this case you would just define the same function but with different parameter types, e.g.

void from_sql_string(CustomType1& value, MYSQL_RES* resource, size_t index);
void from_sql_string(CustomType1& value, PGresult* resource, size_t index);
void from_sql_string(CustomType2& value, MYSQL_RES* resource, size_t index);
void from_sql_string(CustomType2& value, PGresult* resource, size_t index);

Then it would automatically do overload resolution based on the custom destination type and the resource source type.

This would solve the problem for me and it gives the user automatically a strong typed result.

I guess there are also changes needed to the sqlpp23-ddl2cpp script. On these lines the data_type gets written with ::sqlpp:: in front of it. While the type can (and most likely) will be outside of the sqlpp namespace.

Looking at that, I would probably add an extra option where you can pass in a file with a mapping between <table_name>_<column_name>=<strong type>. And then extract the lines above into a seperate function which returns the correct type and defaulting what it is now:

def _get_data_type(column, table_name):
    name = table_name + '_' + column.name
    data_type = "::sqlpp::" + column.data_type
    if name in _strong_type_map:
        data_type = _strong_type_map[name]
    if column.is_nullable:
        return "std::optional<" + data_type + ">"
    return data_type

// The call side will look like:
// print("      using data_type = " + const_prefix + _get_data_type(column, table.name) + ";", file=header)

Agree? If you like the approach I can try to come up with a PR.

[MeanSquaredError]

Overall, I agree that such a change would be needed for ddl2cpp

Looking at that, I would probably add an extra option where you can pass in a file with a mapping between <table_name>_<column_name>=<strong type>

I wouldn't call it a <strong type> because it is not necessarily "strong". IMHO the proper term would be a "custom type" or a "forced type". We already have custom types in ddl2cpp, which do something else: they allow us to create aliases for existing sql types. So the term "custom types" is already in use and we should probably call the new types map "forced types", or maybe therе could be some other suitable term, since I don't insist on calling them exactly "forced types".

On a side note, I think that the existing "custom types" should probably be renamed to "sql type aliases" or something like that, but that's another a subject for another discussion.

I think it is worth waiting for rbock to see what he, as the library author, thinks about this issue as a whole.

[matthijs]

Overall, I agree that such a change would be needed for ddl2cpp

Looking at that, I would probably add an extra option where you can pass in a file with a mapping between <table_name>_<column_name>=<strong type>

I wouldn't call it a <strong type> because it is not necessarily "strong". IMHO the proper term would be a "custom type" or a "forced type". We already have custom types in ddl2cpp, which do something else: they allow us to create aliases for existing sql types. So the term "custom types" is already in use and we should probably call the new types map "forced types", or maybe therе could be some other suitable term, since I don't insist on calling them exactly "forced types".

Agreed, but the same applies to from_sql_string. If we go with the signature as suggested then the proper function would be: from_sql_resource or from_sql_row.

I think it is worth waiting for rbock to see what he, as the library author, thinks about this issue as a whole.

Agreed, lets wait for that.

[rbock]

Back from a couple of offline days :-)

Thanks for the discussion so far @matthijs and @MeanSquaredError

Summarizing:
Allowing library users to customize reading from the database for specific types makes perfect sense. Currently this is not possible since read_field instances are member functions. Delegating this to from_sql_string might not be enough since mysql prepared statements and sqlite use binary formats.

I suggest moving forward in the following steps:

1. Make read_field a free function. My first instinct would be to add this in each of the connector namespaces. See adb0c07 for how this could look like (PostgreSQL only).
2. Write a specialized overload for a custom type
3. Write tests
4. [Optional[ Maybe add generic support for enum? I am not sure how this would work, though. A nice generic implementation might require reflection?
5. Revisit custom type in ddl2cpp
6. Look into other customization points for custom types, e.g. parameters for prepared statements
7. Add documentation.Does that seem reasonable? We can start with one connector for now.

WDYT?

@matthijs Welcome to sqlpp23! I am curious to know how you customized sqlpp11?

Best,
Roland

[matthijs]

Thank you for the welcome!

Never thought about the free function approach, that's even better. That moves the responsibility out of text_result_t.

One question coming up: what's the reason for naming it text_result_t? I think bind_result_t catches the responsibility better. But that is up to you.

As you already pointed out, reflection is probably needed for generic enum support. With PostgreSQL the enum value is returned as string so you need reflection to be able to set the correct value. As this library targets c++23 and reflection is targeted for c++26, so I guess this is not possible for now.

For the sqlpp11 customization, I copied one of the include/sqlpp11/data_types/... and adjusted those to my needs. I will create a small repository with an example.

[MeanSquaredError]

@rbock
Your suggestion to make read_field a free function sounds good and is quite similar to the first approach that I've suggested above.

E.g. with my approach the free function would be

void from_sql_string(CustomType1& value, PGresult* resource, size_t index);

and with your approach the free function would be

inline void read_field(const text_result_t& result, size_t field_index, uint64_t& value);

Overall it is a good approach, but it has one drawback: The user's application cannot have an application for which all of the following are true at the same time:

• The appiation uses two different connector types that share the same sqlpp implementation (e.g. MySQL and MariaDB, or SQLite3 and SQLCipher).
• Both connector types read and deserialize to the same custom object type, e.g. CustomType
• The deserialization code for CustomType is different for the two connector types, so they need two different free functions.

The two deserialization functions (in your case they are the free read_field overloads) would have the same signature which would obviously cause an error during compilation. Actually in your case they are declared as inline, so I guess that the modern compilers would just choose one of the two function implementations and would silently drop the other.

I am not sure how often someone would need something like that (almost same connector type, same destination type, but different deserialization code). II don't see any other problems with your suggestion, so if the above scenario is rare, then there is no problem at all with your suggestion.

[rbock]

Thanks for the feedback!

@matthijs
text_result_t: Mostly because fields are represented as text.
bind_result_t: That name has historical reasons (the first connector was for MySQL, which uses the word bind a lot in the context of prepared statements. I am tempted to rename it to binary_result_t which is closer to the actual meaning.

@MeanSquaredError
I consider that scenario theoretical only. And in theory, it might happen today for serialization, too. If an when this ever happens, it would be possible to react to that, e.g. by using the different representation of that custom type.

OK, so I'll move forward with the current approach in branch custom_type. My next step will be to migrate other connectors to the free-function-approach. It might take a couple of days, though.

@matthijs I'd welcome a PR with a sample custom type implementation with tests.

[MeanSquaredError]

I consider that scenario theoretical only. And in theory, it might happen today for serialization, too. If an when this ever happens, it would be possible to react to that, e.g. by using the different representation of that custom type.

If we ignore this scenario, then the free function approach is the most straightforward indeed. Actually I think (haven't tried it) that the overloads of read_field for the custom types don't even have to be in the connector namespaces (sqlpp::postgresql in our case). An overload can be in the user namespace where the corresponding custom type is defined and it will still work fine because of the ADL.