Reworking the Database Queries

January 15, 2017
behind the scenes

Here is a sneak preview on the new database query API i’m working on. It will be used as some abstraction for the services to query various database types with the same code. The database handler itself will be able to traverse over the query and build the database specific query from the intermediate structure.

Here is how the services will create a query:

using Query::Var;
using Query::Constant;
using Query::make_var;
using Query::make_constant;

Query::Select stmt = db
  .select("id")
  .select("name")
  .from("test")
  .where(
    make_var("name") == make_constant("test")
    ||
    make_var("id") < make_constant("3")
  )
  .order_by("id", "ASC");

Hereby is Query::Select nothing more than:

namespace Query {
  struct QuerySelect_Store {
    std::list<std::string> what;
    std::string from;
    std::unique_ptr<Query::Statement> filter;
    std::list<OrderStatement> order;
  };
  using Select = std::unique_ptr<QuerySelect_Store>;
}

And a traversal could look like this in the end:

cout << "SELECT" << endl;
for (auto& s : stmt->what)
  cout << "  " << s << endl;
cout << "FROM " << stmt->from << endl;
cout << "WHERE" << endl;
stmt->filter->traverse(Query::TraverseCallbacks{
  // one level down
  []{cout << '(';},
  // one level up
  []{cout << ')';},
  // variables
  [](const std::string& name){ cout << '`' << name << '`'; },
  // constants
  [](const std::string& name){ cout << escapeString(name); },
  // operations
  [](Query::Op op){
    switch(op) {
      case Query::Op::EQ:  cout << " == "; break;
      case Query::Op::LT:  cout << " < ";  break;
      case Query::Op::NEQ: cout << " != "; break;
      case Query::Op::OR:  cout << " || "; break;
    }
  }
});

This is however not the final result and things could be optimized even more (like make_constant could maybe be removed) in the next days.

Read more