C++ SQLite Yet Another Api (YAPI)

Perhaps not C++?
Data Jars

Not for cookies, but for data and just very recently, I had a need for a local storage, aka desktop database. Of course, it has to be small but complete, mature and functional. And the API, I am creating, has to be simple.

Thus “C++ SQLite Yet Another API (YAPI)”, was born.

Update: just checked in, a much lighter version “1.0.1”.

NOTE: This library is part of the dbj++ suite of utilities. It depends on it. For easy no struggle results, fork the whole dbj-laboratorium of which this is one project.

There are things in there which are important and not currently documented in this post. Like using syslog, windows client and server, and a such. Or proper usage of std::system_error etc. Time is short and documentation is surely comming. Please stay tuned.

For immediate professional help please do mail me at: dbj@dbj.org

The Usage

The whole API is in the file dbj++sql.h . The only one you need to include.

If you look into the GitHub project you will spot: core_tests.h , It contains (yes, a good guess) core tests. Please study these test functions first. I assume you know basic SQL.  In that case usage is really very simple.

(Side note: for creation and management of SQLite databases I use SQLiteStudio. )

The dbj++sql basic usage:

The SQLite reserved name is ":memory:" , for in memory database that vanishes after your app is done, that is after dbj::db::database instance goes out of scope.

You might have  noticed, we are always catching this “special” exception dbj::db::exception. It contains the SQLite error messages and error codes.

If you are new to the SQLite you will soon realize it is somewhat forgiving. It rarely returns error codes and thus throws of dbj::db::exception do happen relatively rarely, vs some other “normal” c++ code, you have been using before.

Windows users please pay attention

Very (very) likely you use Visual Studio.  Equally likely, you use a lot of string literals, for your SQL statements in your C++ code.

And, not very rarely you need to use non ANSI characters in your SQL statements, in them string literals. For example:

SQLite threats all “traffic” as UTF-8 encoded strings. It does not deal with “wide” wchar_t at all. Thus for the above, and first of all , the Visual Studio 2017 (15.9.0) or better, will throw an warning when compiling the above.

And then, when printing this to the windows console you will see “squigly bits” or sometimes nothing, depending to which language is your Windows set, and how is your console set.  To remedy this situation, first please use the u8 prefix to your string literals.

Or, compile the whole project/solution with /utf-8. Details here. And then when using your console app, set the console code page to 65001 with the command chcp 65001. Even then you need to guess which font to use to be able to show all the characters you are using in the desired language.

To help you out I have developed a little windows console tool, one can use to “guess” easier the font required to display all the text. It is here on the beginning of this post.

From the SQLite documentation: “..The use of the UTF-8 interfaces is preferred, as SQLite currently does all parsing using UTF-8. The UTF-16 interfaces are provided as a convenience. The UTF-16 interfaces work by converting the input text into UTF-8, then invoking the corresponding UTF-8 interface…”.

This is all fascinating (to some) and huge subject. If brave and curious, for the good entry point please start from here.

Now back to the subject of dbj++sql and

The Implementation

SQLite is one embedded, but very complete and mature SQL capable library for using local database (like) files. All you need, but without an RDBMS server, imposed on you. And without a licence fee too. Also.

SQLite is written in C.

Not a standard C++, we all love and hate.  All of the SQLite is in one (very) large c file. And one equally large C header. To use it in your C++ project you need to build a library and use them c files as a such. And then you either use its very large C set of functions making the API ( try to look into sqlite3.h ) .Or. You develop your own API in standard C++. Or you decide you have no time for that and you trust world is dotted with well behaved C++ experts and teams, and you head for the GitHub, possibly to find some of the dozen or so, open source SQLite C++ API’s.

(for UWP and  .NET / C# jockeys, Microsoft is including the SQLite in a form of a .NET assembly )

Quick GitHub search revealed few, for my taste, either over engineered C++ solutions or not standard C++ (14, or 11 or even before). And inevitably there are few extremely complex ones. (Side note: I was always wondering myself, why some people try to make an RDBMS server out of perfectly functional SQLite.)

And then, in the search results, I spotted Kenny Kerr’s somewhat short but useful set of 3 articles . With working code, as usual. And Kenny has this uncanny ability to simplify C API’s.

V.s. Kenny’s, very sober and pragmatic solution, my API is conceptually further from the raw C of the SQLite API. That is perhaps more comfortable but certainly not that loved by SQLite jockeys who memorize hundred or so sqllite3_*  functions and use them all day every day.

Well, this API is not for them, it is made for you, a casual or uber casual C++ developer, needing quick but functional local storage solution.

Thus, I have also deliberately not provided some functionality to which “real SQL” people are used to when dealing with C/C++ api’s, to their RDBMS servers.

Perhaps a chief example is SQLite statement “binding”.  Where  users of the SQLite “naked” API can have “parameterized” SQL statement string, to which they can “bind” the actual arguments

NOTE: as a sample DB, I am using an English dictionary in a file, which I have transformed in the SQLite 3 DB file. It has a single tablewords, with a single text column named word.

Above, the ‘?’ mark is the placeholder to which one can “bind” the actual values. Replacing te ‘?’ mark with a value.
My API does not provide a facility for “binding”. I am sure you are more than capable to do this best yourself in your standard C++ code.

Here I also have difficulties to understand the use-cases when one might need “binding” from a database “driver”? This is where we hit the big subject: why there are no clearly separated modules, why is SQLIte a “monolith”? Or is that a “megalith”. Well, “let’s not go there” right now, perhaps some other time.

Still here?

I hope you are still reading this and you are still looking for simple and comfortable SQLite,  standard c++ wrapper.

My objective here is to develop the minimal but useful SQLite C++ API. First, I will explain a bit about this API, and then we will go through a few examples and hopefully prove the usability of my approach. Thus there will be more than one post, devoted to this subject.

(Resulting static lib is on GitHub )


This first thing I have done was to wrap this huge sqlite3 C API in the C++ namespace. Like so:

I feel much safer dealing with large C API’s this way. If I am really into using dozens of raw C, sqlite3_* functions I can always do

inside the namespace or a function where I do need them.

The Interface

is (almost) all in one class:

Yes, there is only one method through which you can (and will) pass your SQL queries. If callback is sent it will be used. Here is one example:

Above is a callback defined for the following demo code:

But, wait the minute? What’s that dbj::db::value_decoder & val_user in the signature of the callback, above?

That is our little C++ helper so we do not use any value getting SQLite naked C code, for getting the actual values from the result set columns. We just get them as if by magic. Already in the proper type.

Before using the callback and from inside query_result method, we make and send as an argument to it, an instance of the above struct. Its single functional call operator returns anvalue_decoder::transformer instance for the particular column, from the result set.

The crucial line from a callback above once more, But with an explanation this time:

This is actually a well know and old C++ technique. In here we meet head on, the fact we are dealing with a database. Internally result set values, contains values of an unsigned char * type. Whatever type you ask for SQLite will dutifully do the cast and then return its representation in the desired type. The assumption is you know the structure of the database you are dealing with.

This is just a first post about this API. Development is going on, but the interface is very unlikely to change.
What might change is more exceptions popping out in case you try and are allowed to do naughty things.

Next post is explaining what are the user-defined functions, and how do we deal with them using dbj++sql.

Enjoy standard C++.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.