Cross Platform Mobile and Web Development with C++ Explained

Part 6: Add SQLite support for offline and data manipulation

Stéphane Konstantaropoulos
Level Up Coding

--

There is a good chance that if you are going to write complex software, you will need data storage and manipulation. We are lucky SQLite is there for us, with a small binary footprint, portable, and fully fledged. Let me show you how you can use it from your C++ code with just a bit of setup. And even better: without writing any SQL.

The code for this tutorial is available at https://github.com/skonstant/xptuto/tree/part6_sqlite

SQLite: Fully Loaded yet tiny

SQLite is a Relational Database Management System that supports SQL-92 with plenty of cool things: full-text search, JSON, hierarchical, recursive queries, and more.

It is an embeddable database, not a server system, perfect for what we need, and we will include it in our builds. Lots of major players use it in their own packages too.

Other systems will use what they call the amalgamation. All the code of SQLite in one single .c file, albeit a large one but with our ccache setup compilation time is not a problem.

XCode has SQLite as part of its iOS libraries, Mac OS too (working with CMake), Android NDK does not — surprisingly since it is the backbone of the SQL storage on Android since the beginning, it is not exposed in the NDK. Web/Emscripten does not have it of course.

For our iOS project, let’s just add it in Build Phases 🡲 Link Binary with Libraries and add libsqlite3.tbd. Done, headers are visible and linking works.

For our CMake projects (Android, local computer and Emscripten), we use the CMake find_package() command.

find_package (SQLite3)
if (SQLITE3_FOUND)
include_directories(${SQLITE3_INCLUDE_DIRS})
else (SQLITE3_FOUND)
# add amalgamation code and include
set(SRC ${SRC} sqlite-amalgamation-3310100/sqlite3.c)
include_directories(sqlite-amalgamation-3310100)
endif (SQLITE3_FOUND)

This is all there is to it, now we have it in our build and can start playing around with data and SQL in-memory, only so far because we need to sort out the file storage problem.

The Storage Problem

We need to be able to write to a file in order to save our data. On Android and iOS we have access to the folders that only our app can use, we will choose the cache folders on both since this is just for caching. Beware that data in the application’s cache folders can disappear at any time. If you need something more reliable, use the file directories. Either way, these are private to your apps and require no special permission.

Note that the files folders are backed up to the Google or Apple cloud by default, you can opt-out in configuration.

We have added a file path parameter to our Xptuto make_instance() factory method. For Android we do:

Xptuto.makeInstance(
new JavaHttpClient(getApplicationContext()),
new AndroidThreads(),
getCacheDir().getAbsolutePath()); // or getFilesDir()

For iOS, we do:

auto cacheDirectory = [NSSearchPathForDirectoriesInDomains(
NSCachesDirectory, NSUserDomainMask, true) lastObject];
// or NSApplicationSupportDirectory
x = Xptuto::make_instance(
std::make_shared<AppleHttpClient>(),
std::make_shared<AppleThreads>(),
djinni::String::toCpp(cacheDirectory));

For web, it is more difficult. The browser has no filesystem for us, emscript has the Filesystem API which is a bit incoherent. You need to do things in the Javascript layer in order to create the filesystem you need.

In our initialization code we do:

var Module = {
onRuntimeInitialized: function() {
FS.mkdir('/cache');
FS.mount(IDBFS, {}, '/cache');
FS.syncfs(true, function (err) {
window.xptuto = Module.createInstance();
});
}
};
// then in C++
xptuto::Xptuto::make_instance(
std::make_shared<WebHttpClient>(),
std::make_shared<WebThreads>(), "/cache");

This looks simple enough. The problem is further down the line: we need to synchronize manually if we do writes to our files. In our case, when we write to our database. We can sync any time, the app works even without it, the changes will just be lost if we leave the web page without synchronising, bear this in mind. And we cannot synchronize in onbeforeunload because FS.syncfs() is asynchronous. Various strategies can be used to overcome this problem. In my example, I sync in the callback.

callback = new Module.JSGetReposCb({
on_error: (error) => console.error(error),
on_success: (repos, user) => {
...
FS.syncfs(function (err) {});

This is not great but it is a small price to pay, I feel.

Stay Object Oriented: use the ORM

As much as I like SQL, I dislike using strings in my code. I find Object Relational Mappers very useful: we stay in C++, so we can take advantage of our tools to check our code before deploying, and we are sure the generated SQL is always valid.

We will use SQLite ORM a header-only C++17 library, very easy to integrate and to use. Here is our definition of our Users table:

make_storage(dbFile,
make_table("users",
make_column("id", &User::id, primary_key()),
make_column("login", &User::login, unique()),
make_column("avatar_url", &User::avatar_url),
make_column("created_at", &User::created_at))

It deduces the SQLite types from our field types, I have added support for std::chrono::time_point myself (I pass them as milliseconds since epoch). Like our JSON library, it provides template methods that we just specialise for our custom types.

Now we have users in a table, let’s write a little test:

TEST_F(Xptuto, OrmTest) {
auto instance = std::make_shared<XptutoImpl>(
stubHttp,stubThreads, std::nullopt);
stubHttp->path = "/responses/users_aosp.json";

auto user = instance->get_user_sync("aosp");

auto storage = SQLStorage(":memory:");
storage.store_user(user.value());
auto u = storage.get_user("aosp");
EXPECT_EQ(u.created_at, user->created_at);
EXPECT_EQ(u.login, "aosp");
}
// get_user():
User SQLStorage::get_user(const std::string &login) {
auto allUsersList =
storage.get_all<User, std::vector<User>>(
where(c(&User::login) == login));
return allUsersList.at(0);
}

Simple as that, no SQL strings. SQLite Orm can do much more complex queries of course, and in last resort, it lets you write raw SQL. Note that for the test, we use “:memory:” as the database file, this tells SQLite to use an in-memory database.

I chose in this method to return the found user or throw, hence the use of std::vector:::at(). I find exceptions meaningful, sometimes more than returning nulls (std::nullopt better).

Quick aside: Exceptions are disabled in Emscripten by default, for “optimization” reasons. In my view this is the wrong place to optimise, the C++ standard library is full of APIs that throw exceptions, it is a cleaner way to get errors than crashes. So enable exceptions by adding -s DISABLE_EXCEPTION_CATCHING=0 to all your compile and link flags. On other platforms, exceptions are enabled by default.

I also defined my Repo object in a table, with a foreign key to the user.

make_table("repos",
make_column("id", &Repo::id, primary_key()),
make_column("name", &Repo::name),
make_column("full_name", &Repo::full_name),
make_column("owner", &Repo::owner),
make_column("priv", &Repo::priv),
make_column("descr", &Repo::descr),
make_column("created_at", &Repo::created_at),
foreign_key(&Repo::owner).references(&User::id)));
// get repos for a user:
storage.get_all<Repo, std::vector<Repo>>(
where(c(&Repo::owner) == user.id));

Now we can vastly improve our get_user() method (and all the other get… methods.

void XptutoImpl::get_user(const std::string &login,
const std::shared_ptr<GetUserCb> &cb) {
auto me = shared_from_this();

threads->create_thread("get_user",
std::make_shared<ThreadFuncImpl>([me, cb, login]() {
try {
try {
auto user = me->storage.get_user(login);
me->threads->run_on_main_thread(
std::make_shared<ThreadFuncImpl>([cb, user]() {
cb->on_success(user);
}));
} catch (...) {
auto user = me->get_user_sync(login);
if (!user) {
me->threads->run_on_main_thread(
std::make_shared<ThreadFuncImpl>([cb]() {
cb->on_error("could not load user");
}));
} else {
me->storage.store_user(user.value());
me->threads->run_on_main_thread(
std::make_shared<ThreadFuncImpl>([cb, user]() {
cb->on_success(user.value());
}));
}
}
} catch (...) {
me->threads->run_on_main_thread(
std::make_shared<ThreadFuncImpl>([cb]() {
cb->on_error("could not load user");
}));
}
}));
}
  1. try and get the user from the local DB 🡲success callback
  2. try from network and store to DB 🡲 success callback
  3. error callback.

All callbacks are called on the main thread.

Example apps

React

This time we created little example apps, so you can test this out. For web, I created a tiny React App, here is the User view:

class UserView extends React.Component {

render() {
const user = this.props.user;
if (user) {
return (
<div className="userDetails">
<p>Name: {user.login}</p>
<p>Created on: {new Date(user.created_at.millis()).toLocaleDateString()}</p>
<p><img className="avatar" alt="avatar" src={user.avatar_url}/></p>
</div>
);
} else {
return null;
}
}
}

The only thing worth noting is that we create a date from milliseconds.

The user is loaded from a form:

if (!this.callback) {
// eslint-disable-next-line no-undef
this.callback = new Module.JSGetReposCb({
on_error: (error) => console.error(error),
on_success: (repos, user) => {
this.props.onUserChange(user);
this.props.onReposChange(repos);

// eslint-disable-next-line no-undef
FS.syncfs(function (err) {
...
});


}
});
}

window.xptuto.get_repos_for_user_name(
this.state.value,this.callback);

Nothing special here either, the work is done in the C++ layer, the view layer knows nothing about what is happening in the background. The only thing to remember is to sync the pseudo filesystem. Note the avatar is loaded automatically by setting the src attribute of the image.

Also, the classes and functions available in the WebAssembly module are not visible to the Javascript tool (eslint and Babel here), so we need to disable checking when using them, maybe at some point the tools will be able to find what is exposed by embind, who knows.

Android

For Android, I made two activities, one for the user search and details and one to list the repositories.

x.getUser(v.getText().toString(), new GetUserCb() {
@Override
public void
onSuccess(User user) {
if(!isFinishing() && !isDestroyed()){
progressBar.hide();
details.setVisibility(View.VISIBLE);
userName.setText(user.login);
creationDate.setText(DateFormat.getDateInstance(
DateFormat.MEDIUM,
Locale.getDefault()).format(user.createdAt));
avatar.setImageDrawable(null);
Glide.with(MainActivity.this)
.load(user.avatarUrl).into(avatar);
}
}

@Override
public void
onError(String error) {
if(!isFinishing() && !isDestroyed()){
progressBar.hide();
notFound.setVisibility(View.VISIBLE);
}
}
});

Easy again. We use Java’s Dateformat to format the date in a localised way. Glide is used to load the avatar image, that’s the best breed image loading library for Android.

When opening the list activity, we just pass the user name in the intent, as we have the user in storage, we can retrieve it from there. I have been caught a few times passing too much data in an Intent or a Bundle, better pass an identifier.

iOS

Similar to Android, two ViewControllers this time, one with the search form and the details and one with the repositories list, here is how we get the user:

__weak auto welf = self;

auto x = Xptuto::get_instance();

x->get_user(djinni::String::toCpp(_userInput.text), std::make_shared<GetUserCbImpl>(
[welf](const User &user) {
[welf.progress stopAnimating];
[welf showDetails:user];
}, [welf](const std::string &error) {
[welf.progress stopAnimating];
welf.notFoundLabel.hidden = false;
}));

Simple and easy. we use a weak pointer to the viewcontroller so our callback does not hold on to it.

Conclusion

I have showed you offline and complex data manipulation is just one compilation away, you can dust your database modelling book and go implement your on portable device data cache now, no excuse.

And no need for funny stuff like trying to pull the SQLite databaste file out of the phone or the simulator, you can exercise your code on the computer, in memory or to a file, load it in the SQLite Browser GUI and it will behave exactly the same way on Web, iOS and Android.

Reference

Also read:

Introduction:

We present our technical choice.

1. Project Setup

We will configure a project that compiles and runs on iOS/Xcode, Android Studio and Emscripten/CMake and show how to run and debug the 3 of them.

2. Pass objects around

In this one we will show how to pass objects around from the business logic layer to the various view layers.

3. Unit test

Here we setup unit tests in the C++ code using the commonly used Google Test framework.

4. ReST Client

We implement a minimal ReST client using the platform HTTP implementations.

5. Multi-Threading

Yes, you can use threads in all three environments in a portable way!

Next time we will talk about OpenGL.

--

--