C++ SQLite Yet Another Api (YAPI)

Perhaps not C++, but a real storage.
Perhaps not C++, but a real storage.

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”.

The Usage

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

core_tests.h contains (yes 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 SQLite reserved name is ":memory:" , for in memory database that vanishes after you app is done, that is after dbj::db::database instance goes out of scope.

We are always catching this “special” exception dbj::db::exception. It contains the SQLite error messages and error codes.

If you are new the SQLite you will soon realize it is very forgiving. It relatively rarely returns error codes and thus throw of dbj::db::exception happens 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.

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 second, when printing this to the windows console you will see “squigly bits” or sometimes nothing, depending to which language is your Windows set.  To remedy this situation, please use the u8 prefix to your string literals.

Or compile with /utf-8. Details here.And then when using your console app, set the 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.

This is all fascinating and huge subject. If brave and curious, for the good entry point please start from here. Now to back the 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. But.

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 ( look into sqlite3.h ) or … you develop your own API in standard C++. Or 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 or 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. I was always wondering myself, why some people try to make an RDBMS server out of humble 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 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.

Still here?

I hope you are still reading this and you are 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 )

dbj++sql

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.