[odb-users] Executing native selects for no mapped type

[Boris Kolpackov]

Hi Roland,

Roland Kloeters <roland.kloeters at xcom.de> writes:

> As I cannot set the value by default using #pragma db id auto I have to
> select the value from the sequence directly.
> 
> I am using postgresql as RDMS so my select would be: "select
> nextval('sequence')".
> 
> [...]
> 
> Is there any chance of executing native select statements and retrieving
> the results?

One way to do it would be to get the native Postgres connection handle
from odb::pgsql::connection and execute the query using the low-level
C API (libpq). This is not the most convenient way but it is always an
option.

Also we have been thinking about supporting what we call 'view' objects.
A view is a read-only object that can only be loaded using a query. They
could be useful for retrieving only a porting of an object, supporting
ad-hoc table joins, or performing stored procedure calls. Here is an
example:

#pragma db view query("SELECT person.name, employer.name FROM person LEFT JOIN employer ON person.employer = employer.id")
struct name_and_employer
{
  string name;
  string employer;
};

typedef query<name_and_employer> query;
typedef result<name_and_employer> result;

result r (
  db->query<name_and_employer> (
    "WHERE person.age < " + query::_val(30));

Your case would have been handled like this:

#pragma db view query("SELECT nextval('sequence')")
struct sequence_value
{
  unsigned long long value;
};

result r (db->query<sequence_value> ());

Or you could specify the actual sequence name at the query execution time:

#pragma db view
struct sequence_value
{
  unsigned long long value;
};

string seq_name = ...;

result r (
  db->query<sequence_value> (
    "SELECT nextval('" + query::_val (seq_name) + "')");

Do you think something like this will work well for your use case?

In fact, it is possible to kind of emulate views using the existing
objects functionality. First we define the object (it must contain
the dummy object id):

#pragma db object
struct sequence_value
{
  #pragma db id
  unsigned int dummy;

  unsigned long long value;
};

Then the transaction that gets the next value would look like this
(PostgreSQL-specific code):

#include <odb/pgsql/result.hxx>
#include <odb/pgsql/connection.hxx>
#include <odb/pgsql/transaction-impl.hxx>

namespace pgsql = odb::pgsql;

transaction t (db->begin ());

odb::result<sequence_value> r;

{
  pgsql::connection& conn (pgsql::transaction::current ().connection ());

  odb::query<sequence_value> q;

  odb::details::shared_ptr<pgsql::select_statement> st (
    new (odb::details::shared) pgsql::select_statement (
      conn,
      "sequence_value_query" // Statement name.
      "select 0, nextval('sequence')",
      q.parameter_types (),
      q.parameter_count (),
      q.parameters_binding (),
      conn.statement_cache ().find<sequence_value> ().out_image_binding ()));

  st->execute ();
  st->deallocate ();

  odb::details::shared_ptr<odb::result_impl<sequence_value> > ri (
    new (odb::details::shared) pgsql::result_impl<sequence_value> (
      q, st, conn.statement_cache ().find<sequence_value> ()));

  r = odb::result<sequence_value> (ri);
}

// Iterate over the query result as usual. For example, r->begin ()->value
// is the returned sequence value.

t.commit ();

This could be a temporary solution until views are supported natively by
ODB.

Boris