[odb-users] Variant type mapping?

[Boris Kolpackov]

Hi Alexander,

Alexander A. Prokhorov <prokher at gmail.com> writes:

> I was thinking about using ODB in my project, I've tried several trivial  
> cases and it showed itself well. Then I've faced tricky case. I need to  
> store Variant type (QVariant or boost::variant, whatever) in DB. Do you  
> have any recommendations how to perform this?

Hm, interesting question. The best approach would be to map a variant to
something equivalent in SQL. MS SQL and Oracle have something like this
(sql_variant and anytype, respectively) but other databases that ODB
currently supports (MySQL, PG, and SQLite) don't have anything like this.
Plus ODB currently doesn't have support for sql_variant and anytype,
either, though we can add it if there is a need.

A more portable approach would be to store the data as some sort of a
type id plus text or, as Luis suggested, binary representation. Text
might be a better option since it will be easier to access by other
users of the data. I found this thread on the PostgreSQL mailing list
quite interesting, in particular the fact that the text approach might
actually be more efficient, storage-wise than the native variant type:

http://archives.postgresql.org/pgsql-hackers/2011-05/msg00140.php

Finally, the third option that I can think is to have a separate
NULL-able column for each possible data type (this will probably
work better for boost::variant than for QVariant). For each variant
value only one of these columns will contain the data while all
others will be NULL. The problem here is that there is no nice way
to map a variant to a set of values like this in ODB, yet. Though,
once we have support for the virtual data members feature (coming
in 1.10.0) this will change. For more information on virtual member,
see this thread:

http://www.codesynthesis.com/pipermail/odb-users/2011-October/000352.html

With virtual members, the mapping would look along these lines:

#include <odb/nullable.hxx> // Can use boost::optional instead.

#pragma db object
class object
{
  ...

private:  
  friend class odb::access;

  #pragma db transient
  boost::variant<int, std::string> v_;

  typedef odb::nullable<int> nint;
  typedef odb::nullable<std::string> nstring;

  #pragma db member(v_int_) virtual(nint) \
    get(this->v_.which () == 0 ? nint (get<int> (this->v_)) : nint ()) \
    set(if (?) this->v_ = (?))

  #pragma db member(v_string_) virtual(nstring) \
    get(this->v_.which () == 1 ? nstring (get<std::string> (this->v_)) : nstring ()) \
    set(if (?) this->v_ = (?))
};

In the meantime, the same can be emulated using callbacks and a bit
of custom code:

#include <odb/nullable.hxx>

#pragma db object callback(convert_variant)
class object
{
  ...

private:  
  friend class odb::access;

  #pragma db transient
  boost::variant<int, std::string> v_;

  mutable odb::nullable<int> v_int_;
  mutable odb::nullable<std::string> v_string_;

  void
  convert_variant (odb::callback_event e, odb::database&)
  {
    switch (e)
    {
    case odb::callback_event::post_load:
    {     
      if (v_int_)
        v_ = *v_int_;
      else if (v_string_)
        v_ = *string_;

      break;
    }
    case odb::callback_event::pre_persist:
    case odb::callback_event::pre_update:
    {
      // Delegate to const version.
      static_cast<const object*> (this)->convert_variant ();
      break;
    }
    default:
      break;
    }
  }

  void
  convert_variant (odb::callback_event e, odb::database&) const
  {
    switch (e)
    { 
    case odb::callback_event::pre_persist:
    case odb::callback_event::pre_update:
    {
      switch (v_.which ())
      {
      case 0:
        v_int_ = get<int> (v_);
        break;
      case 1:
        v_string_ = get<std::string> (v_);
        break;
      }     
      break;
    }
    default:
      break;
    }
  }
};

Boris