---

Now we are going to put away our fuse things and pick up some sqlite. The goal here is to get a program that can open a database and execute queries. Once we finish that, we can then see how to go about connecting this piece with the fuse logic.

Remember, the goal is to run some sqlite statement to get a list of tables in our readdir function.

Let's get started!

Connecting to SQLite Database

As always, let's work on the simplest thing first. The first thing we need to do is open a connection to sqlite.

Place the below code in a new sq.c file. This will be file that we are just working with, this isn't going to be part of our fuse program.

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>

int main(int argc, char* argv[]) {
    sqlite3 *db;
    int rc;

    rc = sqlite3_open("db.sqlite", &db);

    if( rc ) {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        return(0);
    } else {
        fprintf(stderr, "Opened database successfully\n");
    }

    sqlite3_close(db);
    return 0;
}

All this program does is initialize a sqlite pointer and a return code. We then open our database and we will get a return code from that. If we get a 1, then there was an error. If we get a 0, this means it was successful.

We then close the database and exit out program.

We can now compile this program:

gcc -lsqlite3 sq.c -o sq

We can then run:

./sq

Which should result in the following:

Opened database successfully

Async SQLite

Now we have a very basic sqlite example working. Let's write a simple select statement.

We want to run the following query:

select name from sqlite_master where type = 'table' and name not like 'sqlite_%';

This query is going to give us the tables that exist in our database.

Now before we do this in C, know that there are 2 ways of running queries in sqlite. Due to sqlite being a database so that means it may need to go to disk, it would be very slow to have to wait for sqlite to return data before we could continue. Instead sqlite allows for asynchronous calls. You can submit a query and once the data is returned, it will execute a callback function you define.

This example is the simpler example so we will go over that first.

int main(int argc, char* argv[]) {
    sqlite3 *db;
    char *zErrMsg = 0;
    int rc;
    char *sql;

    rc = sqlite3_open("db.sqlite", &db);

    sql = "select name from sqlite_master where type = 'table' and name not like 'sqlite_%';";

    rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);

    sqlite3_close(db);
    return 0;
}

This is the updated main. I have removed the checks against rc and we're going to pray that everything succeeds. The sqlite3_exec function is where the magic happens. It will execute our sql against the database and run a callback once it finishes.

Now for our callback:

static int callback(void *NotUsed, int argc, char **argv, char **azColName) {
    int i;
    for(i = 0; i<argc; i++) {
        printf("%s\n", argv[i]);
    }
    return 0;
}

This will print our the names of the tables in our database.

We can compile and run this program:

gcc -lsqlite3 sq.c -o sq
./sq

Which hopefully outputs:

Opened database successfully
mytable

Now if your program doesn't show that, then you can add the rc checks back in and do some debugging.

Sync SQLite

Now with this function we run queries against out sqlite database and use it with our fuse program. But wait! Sqlite runs a callback function, how do we pass readdir as a callback? Instead of figuring that out, I figured it would be easier to just make the sqlite synchronous. This will involve a little bit more code as we will need to do what the sqlite3_exec function was abstracting away.

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>

int sq_getTables() {
    sqlite3 *db;
    int rc = sqlite3_open("db.sqlite", &db);

    char *sql = "select name from sqlite_master where type = 'table' and name not like 'sqlite_%';";

    sqlite3_stmt *stmt = NULL;
    sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);

    rc = sqlite3_step(stmt);

    while (rc != SQLITE_DONE && rc != SQLITE_OK) {
        const unsigned char * table = sqlite3_column_text(stmt,0);
        printf("%s\n", table);
        rc = sqlite3_step(stmt);
    }

    rc = sqlite3_finalize(stmt);
    rc = sqlite3_close(db);

    return rc;
}

int main(int argc, char* argv[]) {
    int rc = sq_getTables();
    return 0;
}

Here I've written a full getTables function. This will open the database, run the query and it will print out the table names.

The sqlite3_exec function is a wrapper for the sqlite3_prepare_v2, sqlite3_step and sqlite3_finalize functions. We are going to manually run these.

The code is that we now have a while loop that we use to get data.

Now we can compile and run this program:

gcc -lsqlite3 sq.c -o sq
./sq

Which should give us a familiar output:

./sq
mytable

Voila! With that we have the first function we need to get table names!

Now there are some large caveats here. Due to my own lack of knowledge, I'm going to roll with this implementation. Really the function should return an array of names that I can use. This would make it so that I re-use this function for a few different things that will come up. However for now I'm going to be copying and modifying this version to do the same thing with different queries.

This function should definitely be re-written in the future.