Bruce Adams
2015-10-06 15:42:47 UTC
This is a hopefully unbiased comparison intended to inform & inquire not to flame.
TL;DR;
DTL vs SOCI
mindshare: SOCI wins
availability: SOCI wins
ORM: DTL wins
embedding SQL: SOCI wins
3 specific issues:
 select distinct: uglier in DTL
 bulk transfer: DTL wins
 type conversions: DTL wins
SOCI Questions:
1. What is the barrier to making bulk fetches work with custom types?
2. why does SOCI from_base() insist my SQLite date-time column is a string?
3. why doesn't values have a size() type?
Now the long version:
Some time ago (a few years) I evaluated several options for database access in C++ including SOCI.
For the particular application I had at the time DTL came out on top
(http://sourceforge.net/p/soci/mailman/message/27768062/).
I now have a slightly different scenario and am looking at re-evaluating things.
I have *not* looked at performance this time around instead focusing on differences to me as a programmer.
Although DTL has changed very little SOCI has been developed more actively.
However, great kudos to DTL is that it hasn't really needed any changes. It just works.
SOCI I think has a larger mind share.
It is also included in RedHat's EPEL repository which makes for an easy install.
By contrast I have to package and ship DTL in order to use it.
In my new scenario I am using sqlite3.
This means I must also package an SQLite ODBC driver (http://www.ch-werner.de/sqliteodbc/).
And this is on top of requiring unixODBC to be installed to use DTL.
None of this is particularly hard but is extra work with extra layers of things to go wrong.
The choice of ODBC when there is no requirement to support anything other than SQLite3
also seems less than optimal..
Anyway, starting with a working DTL implementation I have looked at what is different to make
things work with SOCI.
Coming from the DTL side I am naturally focused on the ORM (Object Relational Mapping) aspect.
Differences between SOCI and DTL
================================
insert vs select
----------------
DTL uses the context to determine whether we are inserting or selecting.
DTLs DBView container is analogous to a SOCI rowset. Reading from the container is a select
writing to it is an update.
E.g.
SOCI:
   rowset<int> r = (sql.prepare << "select foo from bar");
   for(row: r)
      //do stuff
DTL:
  DBView<int> view("bar","foo");
  for(row: view)
      //do stuff
ORM is partly about hiding the fact that a container happens to be a database table.
I prefer the ORM view as per DTL but recently came across a case where DTL's abstraction leaks.
Currently implementing "select distinct" is not as simple as writing "select distinct" or using
an appropriate modifier.
(https://groups.yahoo.com/neo/groups/DatabaseTemplateLibrary/conversations/messages/2690)
type conversions
----------------
Both DTL and SOCI allow you to add conversions for custom types.
E.g.
struct Foo
{
  int columnA;
  int columnB;
};
SOCI:
namspace SOCI
{
template <>
struct type_conversion<Foo>
{
  typedef values base_type;
 Â
  static void from_base(values const & v, indicator /* ind */, Foo& p)
  {
     p.columnA = v.get<int>("columnA");
     p.columnB = v.get<int>("columnB");
  }
  static void to_base(values const & v, indicator /* ind */, Foo& p) ...
}
} //end namespace
Foo foo
sql << "select columnA,columnB from bar", into(foo);
or just:
sql << "select columnA,columnB from bar", into(foo.columnA), into(foo.columnB);
DTL:
DBView<foo> view("bar",
           BCA(foo,
          COLS["columnA"] >> foo.columnA &&
                    COLS["columnB"] >> foo.columnB));
The conversion in SOCI is via a global template. In DTL it is an object and can be used as widely
or narrowly as necessary (frequently just in the one query as above).
The "into" part is perhaps nicer syntactic sugar.
In fact the DTL equivalent of a SOCI type conversion is:
template<> class dtl::DefaultBCA<Foo>
{
public:
  void operator()(BoundIOs& cols, Foo& rowbuf)
  {
     cols["columnA"] == rowbuf.columnA;
     cols["columnB"] == rowbuf.columnB;
  }
}
(Note operator>> is read, operator<< is write, operator== is both.)
Bulk transfers with custom types just work in DTL see below.
The type mapping appears to be completely fixed for from_base() type_conversion methods.
This could be down to my ignorance rather than a flaw in SOCI.
In from_base() if I do
  int colA = v.get<int>("columnA");
I will get a bad_cast if columnA is not an int.
This includes getting just the sign wrong as in:
  unsigned int colA = v.get<unsigned int>("columnA");
Provided the value can be converted this just works in DTL.
This seems to be a particular problem for SQLite where the schema does not dictate the types.
For example, I have a time-stamp in my SQLite database in the form:
    2015-01-01 12:00:00
but the column type is integer as SQLite3 has no time-stamp (see https://www.sqlite.org/datatype3.html)
If I do:
   int timestamp = v.get<int>("startTime");
I get "2015"
(This is an SQLite pecularity and true in DTL as well)
If I do
   tm timestamp = v.get<std::tm>("startTime");
or anything else including std::string I get std::bad_cast
I am surprised that string conversion is not a legal fallback for all cases.
Q Why does the "values" container which represents a row not have a size() method?
This would be useful for debugging a query. E.g. iterate over each column showing column name, type & value.
I would also argue that containers should follow the STL container pattern where possible.
The std::bad_cast is more cryptic than necessary. SOCI could trap this an return an exception that
identifies the column for instance (I believe DTL does - actually this may be from ODBC?).
An obvious workaround would seem to be modify the column:
E.g.
      "select cast(strftime('%s',startTime) as INTEGER) as startTime from someTable"
In spite of the cast I always get the number of seconds as a string. get<int> throws a std::bad_cast.
I can get that soci conversion to work but I need to add an extra level of conversion on the string (e.g. via a std::stringstream).
Surely there is a better way?
Strangely though if I use the embedded sql idiom it just works:
   Foo rowbuf;
   statement st = (sql.prepare << "select cast(strftime('%s',startTime) as INTEGER) as startTime,someOtherColumn from bar",
                   into(rowbuf.startTime),info(rowbuf.someOtherColumn);
So the difference between DTL and SOCI is:
 default type_conversion is better in DTL
 DTL does the equivalent of letting you create converter classes representing generic "into(x),into(y)" clauses for re-use.
 SOCIs "into" syntactic sugar is argubly a little nicer.
DTL also has types that reflect the underlying database types that can be used for more efficient data transfer.
E.g. tcstring<10> is more efficient for (bulk) transfers of varchar<10> than std::string though std::string will just work.
I'm not sure if soci has anything similar.
bulk transfer
-------------
DTL has a helper function/template for more efficient bulk transfers:
DTL:
  DBView<int> view("bar","foo");
  bulk_fetch_helper(view, num_rows_at_a_time, functor);
In SOCI you can do this using a vector but only for builtin types:
SOCI:
  std::vector<int> valsOut(100);
  sql << "select foo from bar", into(valsOut);
However there are some major drawbacks.
You can't use other containers e.g. std::set
You can't use composite types.
The latter restriction is documented.
SOCI is using the vector as a proxy for bulk fetches.
If you want to insert into a set you would instead write an explicit loop:
   std::set<int> valsOut;
   rowset<int> r = (sql.prepare << "select foo from bar");
   for(row: r)
     valsOut.insert(row);
I'm not sure that either of these restrictions are really necessary.
I think it would be better to explicitly indicate bulk transfers using some other means and
extend the into syntax to allow any container.
(this is not a new idea by any means e.g. http://sourceforge.net/p/soci/mailman/message/1887435/)
Writing to a contiguous area of memory via a vector is probably the only way for efficient bulk transfer.
I think that should be an implementation detail. selecting into an arbitrary container is syntactic sugar
optimising for programmer time.
If an implementation decides to insert one row at a time or bulk fetch into a vector behind the scenes that is its purview.
Now that we know "vector" means bulk transfer.
I still don't understand why you cannot perform bulk transfers of custom types.
E.g.
  struct Foo
  {
     int columnA;
     int columnB;
  };
  std::vector<Foo> valsOut(100);
  sql << "select a,b from bar", into(valsOut);
In SOCI rowset<> seems to exist for use with STL algorithms requiring iterators
In DTL dbview<> is the fundamental abstraction.
Q What is the barrier to making bulk transfers of custom types work in SOCI?
Regards,
Bruce
PS For those still stuck on C++03 I'm using C++11 style for(value: container).
TL;DR;
DTL vs SOCI
mindshare: SOCI wins
availability: SOCI wins
ORM: DTL wins
embedding SQL: SOCI wins
3 specific issues:
 select distinct: uglier in DTL
 bulk transfer: DTL wins
 type conversions: DTL wins
SOCI Questions:
1. What is the barrier to making bulk fetches work with custom types?
2. why does SOCI from_base() insist my SQLite date-time column is a string?
3. why doesn't values have a size() type?
Now the long version:
Some time ago (a few years) I evaluated several options for database access in C++ including SOCI.
For the particular application I had at the time DTL came out on top
(http://sourceforge.net/p/soci/mailman/message/27768062/).
I now have a slightly different scenario and am looking at re-evaluating things.
I have *not* looked at performance this time around instead focusing on differences to me as a programmer.
Although DTL has changed very little SOCI has been developed more actively.
However, great kudos to DTL is that it hasn't really needed any changes. It just works.
SOCI I think has a larger mind share.
It is also included in RedHat's EPEL repository which makes for an easy install.
By contrast I have to package and ship DTL in order to use it.
In my new scenario I am using sqlite3.
This means I must also package an SQLite ODBC driver (http://www.ch-werner.de/sqliteodbc/).
And this is on top of requiring unixODBC to be installed to use DTL.
None of this is particularly hard but is extra work with extra layers of things to go wrong.
The choice of ODBC when there is no requirement to support anything other than SQLite3
also seems less than optimal..
Anyway, starting with a working DTL implementation I have looked at what is different to make
things work with SOCI.
Coming from the DTL side I am naturally focused on the ORM (Object Relational Mapping) aspect.
Differences between SOCI and DTL
================================
insert vs select
----------------
DTL uses the context to determine whether we are inserting or selecting.
DTLs DBView container is analogous to a SOCI rowset. Reading from the container is a select
writing to it is an update.
E.g.
SOCI:
   rowset<int> r = (sql.prepare << "select foo from bar");
   for(row: r)
      //do stuff
DTL:
  DBView<int> view("bar","foo");
  for(row: view)
      //do stuff
From the ORM side I would argue having to write "select" is additional noise.
Coming from the embedding SQL end you can argue it is documenting the SQL actually used.ORM is partly about hiding the fact that a container happens to be a database table.
I prefer the ORM view as per DTL but recently came across a case where DTL's abstraction leaks.
Currently implementing "select distinct" is not as simple as writing "select distinct" or using
an appropriate modifier.
(https://groups.yahoo.com/neo/groups/DatabaseTemplateLibrary/conversations/messages/2690)
type conversions
----------------
Both DTL and SOCI allow you to add conversions for custom types.
E.g.
struct Foo
{
  int columnA;
  int columnB;
};
SOCI:
namspace SOCI
{
template <>
struct type_conversion<Foo>
{
  typedef values base_type;
 Â
  static void from_base(values const & v, indicator /* ind */, Foo& p)
  {
     p.columnA = v.get<int>("columnA");
     p.columnB = v.get<int>("columnB");
  }
  static void to_base(values const & v, indicator /* ind */, Foo& p) ...
}
} //end namespace
Foo foo
sql << "select columnA,columnB from bar", into(foo);
or just:
sql << "select columnA,columnB from bar", into(foo.columnA), into(foo.columnB);
DTL:
DBView<foo> view("bar",
           BCA(foo,
          COLS["columnA"] >> foo.columnA &&
                    COLS["columnB"] >> foo.columnB));
The conversion in SOCI is via a global template. In DTL it is an object and can be used as widely
or narrowly as necessary (frequently just in the one query as above).
The "into" part is perhaps nicer syntactic sugar.
In fact the DTL equivalent of a SOCI type conversion is:
template<> class dtl::DefaultBCA<Foo>
{
public:
  void operator()(BoundIOs& cols, Foo& rowbuf)
  {
     cols["columnA"] == rowbuf.columnA;
     cols["columnB"] == rowbuf.columnB;
  }
}
(Note operator>> is read, operator<< is write, operator== is both.)
Bulk transfers with custom types just work in DTL see below.
The type mapping appears to be completely fixed for from_base() type_conversion methods.
This could be down to my ignorance rather than a flaw in SOCI.
In from_base() if I do
  int colA = v.get<int>("columnA");
I will get a bad_cast if columnA is not an int.
This includes getting just the sign wrong as in:
  unsigned int colA = v.get<unsigned int>("columnA");
Provided the value can be converted this just works in DTL.
This seems to be a particular problem for SQLite where the schema does not dictate the types.
For example, I have a time-stamp in my SQLite database in the form:
    2015-01-01 12:00:00
but the column type is integer as SQLite3 has no time-stamp (see https://www.sqlite.org/datatype3.html)
If I do:
   int timestamp = v.get<int>("startTime");
I get "2015"
(This is an SQLite pecularity and true in DTL as well)
If I do
   tm timestamp = v.get<std::tm>("startTime");
or anything else including std::string I get std::bad_cast
I am surprised that string conversion is not a legal fallback for all cases.
Q Why does the "values" container which represents a row not have a size() method?
This would be useful for debugging a query. E.g. iterate over each column showing column name, type & value.
I would also argue that containers should follow the STL container pattern where possible.
The std::bad_cast is more cryptic than necessary. SOCI could trap this an return an exception that
identifies the column for instance (I believe DTL does - actually this may be from ODBC?).
An obvious workaround would seem to be modify the column:
E.g.
      "select cast(strftime('%s',startTime) as INTEGER) as startTime from someTable"
In spite of the cast I always get the number of seconds as a string. get<int> throws a std::bad_cast.
I can get that soci conversion to work but I need to add an extra level of conversion on the string (e.g. via a std::stringstream).
Surely there is a better way?
Strangely though if I use the embedded sql idiom it just works:
   Foo rowbuf;
   statement st = (sql.prepare << "select cast(strftime('%s',startTime) as INTEGER) as startTime,someOtherColumn from bar",
                   into(rowbuf.startTime),info(rowbuf.someOtherColumn);
So the difference between DTL and SOCI is:
 default type_conversion is better in DTL
 DTL does the equivalent of letting you create converter classes representing generic "into(x),into(y)" clauses for re-use.
 SOCIs "into" syntactic sugar is argubly a little nicer.
DTL also has types that reflect the underlying database types that can be used for more efficient data transfer.
E.g. tcstring<10> is more efficient for (bulk) transfers of varchar<10> than std::string though std::string will just work.
I'm not sure if soci has anything similar.
bulk transfer
-------------
DTL has a helper function/template for more efficient bulk transfers:
DTL:
  DBView<int> view("bar","foo");
  bulk_fetch_helper(view, num_rows_at_a_time, functor);
In SOCI you can do this using a vector but only for builtin types:
SOCI:
  std::vector<int> valsOut(100);
  sql << "select foo from bar", into(valsOut);
However there are some major drawbacks.
You can't use other containers e.g. std::set
You can't use composite types.
The latter restriction is documented.
SOCI is using the vector as a proxy for bulk fetches.
If you want to insert into a set you would instead write an explicit loop:
   std::set<int> valsOut;
   rowset<int> r = (sql.prepare << "select foo from bar");
   for(row: r)
     valsOut.insert(row);
I'm not sure that either of these restrictions are really necessary.
I think it would be better to explicitly indicate bulk transfers using some other means and
extend the into syntax to allow any container.
(this is not a new idea by any means e.g. http://sourceforge.net/p/soci/mailman/message/1887435/)
Writing to a contiguous area of memory via a vector is probably the only way for efficient bulk transfer.
I think that should be an implementation detail. selecting into an arbitrary container is syntactic sugar
optimising for programmer time.
If an implementation decides to insert one row at a time or bulk fetch into a vector behind the scenes that is its purview.
Now that we know "vector" means bulk transfer.
I still don't understand why you cannot perform bulk transfers of custom types.
E.g.
  struct Foo
  {
     int columnA;
     int columnB;
  };
  std::vector<Foo> valsOut(100);
  sql << "select a,b from bar", into(valsOut);
In SOCI rowset<> seems to exist for use with STL algorithms requiring iterators
In DTL dbview<> is the fundamental abstraction.
Q What is the barrier to making bulk transfers of custom types work in SOCI?
Regards,
Bruce
PS For those still stuck on C++03 I'm using C++11 style for(value: container).