sql - C++ DBI Class - best/developer friendliest style -

in our current project, need high level dbi different databases. should provide following features:

• in memory cache - dbi should able cache reads, , update cache on writing calls (the application coding on heavy threaded, , needs fast access current data time). memory cache based on boost::multi_index
• automatic sql building - don't want parse sql statement lookup in memory cache

as need provide functions for: defining table layout, selects, inserts, updates, joins, ..., interface complex.

we need way invoke interface function.

there many styles around, not find useful our usage.

here few examples:

soci

sql << "select name, salary persons id = " << id, into(name), into(salary); 

we don't want sql statements, have define what , from different way.

pqxx

conn.prepare("select_salary",     "select name, salary persons id = $1")     ((string)"integer",prepare::treat_direct); 

the heavy usage of overloaded operator() ugly, work too.

any suggestions how design interface?

how using object relational mapping? here's code fragment ideas off top of head - i've done in python, never in c++, , simple databases. there's list of frameworks on wikipedia should avoid wheel-related r&d.

class people: public dbi_table {     // id column handled dbi_table.     name: string_column;     salary: money_column; };  class cost_center: public dbi_table {     name: string_column;     office: foreign_key<offices>; };  class people_cost_center_link: public link_table {     // many-many relationships. }; 

then can manipulate records objects, relational stuff handled framework. querying done defining query object , getting iterator results (see the odb wikipedia page code example).