[odb-users] PostgreSQL NUMERIC type

[Davide Anastasia]

Hi Boris, 
Thanks a lot for your answer, it was really helpful.

Best,
Davide

-----Original Message-----
From: Boris Kolpackov [mailto:boris at codesynthesis.com] 
Sent: 28 March 2012 08:37
To: Davide Anastasia
Cc: odb-users at codesynthesis.com
Subject: Re: [odb-users] PostgreSQL NUMERIC type

Hi Davide,

Davide Anastasia <Davide.Anastasia at qualitycapital.com> writes:

> We are currently trying to write a bespoke C++ class to support the 
> NUMERIC type on PostgreSQL. However, I haven't found an hint on how to

> plug such a class into ODB seamlessly. Is there something I can read 
> that would help me find the right way?

There is an example in the odb-examples package called 'mapping' that
shows how to map (and re-map) C++ types to database types. But
generally, the process involves the following steps:

1. Implement odb::<database>::value_traits specialization for the C++
   type. In your case, if we assume the C++ type is called numeric, then
   it will be:

#include <odb/pgsql/traits.hxx>

namespace odb
{
  namespace pgsql
  {
    template <>
    class value_traits<numeric, id_numeric>
    {
    public:
      typedef numeric value_type;
      typedef numeric query_type;
      typedef details::buffer image_type;

      static void
      set_value (numeric& v,
                 const details::buffer& b,
                 std::size_t n,
                 bool is_null)
      {
        ...
      }

      static void
      set_image (details::buffer& b,
                 std::size_t& n,
                 bool& is_null,
                 const numeric& v)
      {
        ...
      }
    };
  }
}

   Generally, the signatures of the set_value()/set_image() vary
depending
   on the database type (identifier by the second template argument in
the
   template specialization; id_numeric in our case).

   The implementations of the above functions will need to read/write
   PostgreSQL NUMERIC values in the binary format. The PostgreSQL
   documentation has the following note about this format:

   "Values passed in binary format require knowledge of the internal
    representation expected by the backend. For example, integers must
    be passed in network byte order. Passing numeric values requires
    knowledge of the server storage format, as implemented in
    src/backend/utils/adt/numeric.c::numeric_send() and 
    src/backend/utils/adt/numeric.c::numeric_recv()."

   I also found this thread on the pgsql-interfaces mailing list that
   could be helpful:

   http://archives.postgresql.org/pgsql-interfaces/2004-08/msg00000.php

2. Once the specialization is implemented and saved to, say, traits.hxx,
   the next step is to include it into the generated header files with
   the --hxx-prologue ODB compiler option:

   odb -d pgsql ... --hxx-prologue '#include "traits.hxx"' file.hxx

3. The final step is to tell the ODB compiler that data members of
   the numeric C++ type should be mapped to the NUMERIC PostgreSQL
   type. This can be done on the per-type or per-member basis (or
   both). For example, we could map the  numeric C++ type to NUMERIC
   with default precision and scale and then customize the precision
   and/or scale on the per-member basis if needed:

   #pragma db value(numeric) type("NUMERIC")

   #pragma db object
   class person
   {
     ...

     #pragma db type("NUMERIC(3,1)")
     numeric hight_;                   // mapped to NUMERIC(3,1)

     numeric weight_;                  // mapped to NUMERIC
   };

Boris