Examples for OidMap & Custom types support is sql queries

[neogenie]

Can't found any examples or docs about types system

Example from docs:

struct my_row {
    std::int64_t id;
    std::optional<std::string> name;
};

BOOST_HANA_ADAPT_STRUCT(my_row, id, name);

This is enough to do select queries like this:

std::vector<my_row> res;
const auto query = "SELECT id, name FROM users_info WHERE amount>="_SQL + std::int64_t(25);
ozo::request(ozo::make_connector(io, conn_info), query, ozo::into(res),
            [&](ozo::error_code ec, auto conn) {
});

1. Question 1: How to pass this struct and(or) vector of that structs as SQL param to stored procedure/function:

For example I have custom user-defined type in Postgres:

CREATE TYPE t_my_row AS
(
    id        INTEGER,
    name      TEXT,
);

And Stored Procedure:

CREATE OR REPLACE PROCEDURE test(VARIADIC data t_my_row[])
    LANGUAGE plpgsql
AS
$$
BEGIN
...
END;
$$;

that take an array of records like this:

CALL test((1, 'Record A'), (2, 'Record B'));

What will the calling code look like from the ozo side?

2. Question 2: What's the difference between a type resulting from a _SQL literal

like this:

auto query = "SELECT id, name FROM users_info WHERE amount>="_SQL + std::int64_t(25);

and a type resulting from a call make_query

auto query = ozo::make_query("SELECT id, name FROM users_info WHERE amount>=$1", std::int64_t(25))

It is clear that in one case the request is constexpr, and in the other constexpr is only part without parameters, but this is completely unclear from the documentation and there is some confusion in the interface of these types, since one has text as string_view member and another has text() constexpr method from boost::hana...

[thed636]

Hi Neo!

1. I'll try to answer more later, to produce an example that is relevant to your code but here is the short answer that may help you. Look at the request_integration.cpp. Here you can find tests for sending and receiving composites. Please pay attention to adaptation and definition of the custom_type. So the array may be represented via std::vector, std::list and std::array using corresponding adaptations from ozo/ext headers.
2. The difference is - _SQL literal is the syntactic sugar that allows the user to do not bother with argument indexes and write a query in the most natural way. The ozo::make_query() is closer to the libpq interface so a run-time created string may be used here. Both mechanisms construct an object that models the Query concept. Thus both of them use the binary representation of the query parameters. Besides these two ways, the user may create their own model of the Query concept to satisfy the needs.

Hope that helps.

[neogenie]

Sergey, Thanks for the answers!

About point 1:

struct my_row {
    std::int64_t id;
    std::optional<std::string> name;
};

BOOST_HANA_ADAPT_STRUCT(my_row, id, name);
OZO_PG_DEFINE_CUSTOM_TYPE(my_row, "t_my_row");
...
std::vector<my_row> elements = {my_row{.id = 1, .name = "One"}, my_row{.id = 2, .name = "Two"}, my_row{.id = 3, .name = "Three"}};

auto sql = ozo::make_query("CALL test($1::t_my_row[])", std::move(elements));

doesn't compile:

error: static_assert failed due to requirement 'integral_constant<bool, false>::value' "type OID for T can not be found in the OidMap, it should be registered via register_type()" static_assert(decltype(hana::find(map.impl, key) != hana::nothing)::value, ^ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ _deps/ozo-src/include/ozo/type_traits.h:629:12: note: in instantiation of function template specialization 'ozo::type_oid<std::__1::vector<my_row, std::__1::allocator<my_row>>, my_row>' requested here return type_oid<std::decay_t<T>>(map);

Connection Pool creates with type aliases:

using OzoOidMap = ozo::oid_map_t<Types...>;
using OzoConnectionInfo = ozo::connection_info<OzoOidMap>;
using OzoConnectionPoolConfig = ozo::connection_pool_config;
using OzoConnectionPool = ozo::connection_pool<OzoConnectionInfo>;

OzoConnectionPool connectionPool = ozo::make_connection_pool(
          OzoConnectionInfo("..."),
          OzoConnectionPoolConfig{...})

[thed636]

This might be not obvious, but the user should register the array type of the custom type too. So in your case std::vector<my_row> should be pointed in the register_type() explicitly in the same way as my_row. I tried to give some additional information about the situation via the error message "type OID for T can not be found in the OidMap,...". This thing is slightly not convenient, but with the current type system, there is no good solution to do not force the user to map an array on a certain predefined C++ type. I had thought about this problem and there two possible solutions: leave it as is or make the type system more complicated and force the user to pay an extra fee even if the array is not used. I had chosen the first one. So this is the cost for customization abilities to use various types as an array representation and relative simplicity. But here the user may always implement a good helper according to their preferences here. So it might be not a big problem.

[neogenie]

@thed636 Thanks for the answers!

Do you plan to implement a proper builder pattern for SQL?
Something like that:

auto query = Query().select("*").from("users").where(...);

Not sure if this will be possible easy to implement in compile time and make that expression constexpr, but this is just necessary for the implementation of a more or less full-fledged ORM. Now I am trying to make an ActiveRecord pattern based on the library and I cannot yet imagine how this can be done without the support of the true query builder. The current implementation of the Query concept is quite suitable for pre-prepared simple queries, but obviously insufficient for the implementation of full-fledged ORM.

[thed636]

Well, I have no such plans. I do not realize the problem that such a builder may solve. The library is not an ORM regarding reflecting high-level database entities with their relations into an application code. It is a middleware for handling IO-related issues like type safety, high availability, and performance. I suppose it would be nice to have an ORM, but it should be a different project IMO.

Regarding the Query concept. Any product of custom query builder may be adopted via ozo::get_query_text_impl and ozo::get_query_params_impl specialization. So you do not need to use library builders to get a query. This is a customization point that users may use with any different model of the Query concept. And it is very simple to integrate into the library.