On Jan 7, 2016, at 5:22 PM, Jim Callahan <jim.callahan.orlando at gmail.com> wrote: > > I believe R has remarkably good interface packages for SQLite
That?s the appropriate level: the high-level language's DB access layer should map the low-level C record-at-a-time API to an appropriate language-level abstraction. R almost forces you to do this because of things like data.frame. But, that?s no argument for other HLL DBMS API writers not to provide similar affordances. I?ve been involved with two different C++ DBMS wrapper libraries, and both of them provide a way to get a std::vector<> as a result set instead of iterate over individual rows. As with R?s SQLite wrapper, I felt it was my C++ code?s responsibility to do this repackaging, not the underlying C DBMS access API. That?s not to say that the SQLite C API has no warts: 1. sqlite3_column_*() uses 0-based indices but sqlite3_bind_*() uses 1-based indices. I can cope with either base, but please pick one! (And make it the correct base for programming, 0. (Yes, I know I just praised R above. R?s use of 1-based arrays is WRONG.)) 2. There is no ?preview? mechanism. That is, you can?t bind some parameters to a prepared query string and then get the resulting SQL because SQLite substitutes the values into the query at a layer below the SQL parser. This means that if you have an error in your SQL syntax or your parameters cause a constraint violation, your debug logging layer can only log the prepared query string, not the parameters that went into it, which makes it unnecessarily difficult to determine which code path caused you to get the error when looking at logs of a running system. 3. The query finalization code could be less picky. If I prepare a new query without finalizing the previous one, I?d rather that SQLite didn?t punish me by throwing errors unless I put it into a ?lint? mode. Just toss the half-finished prior query and move on, please. 4. There are several signs of backwards compatible extensions which make the API more complex than if it were designed with the features from the start. (e.g. _v2() APIs, the various ways to get error codes, etc.) Hopefully those doing the SQLite4 effort will feel free to break the API, jettisoning this historical baggage.
