Now that we have a fuse program that can list tables as directories, open rows as a file, it is time to write the saving logic.
This will be similar to the read in that given a path we know what table and row we need to update. Once we have that information, we need to get the current contents of the modified file and use that to generate a sql query that will update the row in sqlite.
The first step is to write our write function and add it to the ops:
static int sfs_write(const char *path, const char *buf, size_t size, off_t offset, struct fuse_file_info *fi) {
printf("Writing: %s\n",path);
return strlen(buf);
}
All we're doing for now is printing out the path and returning the length of the buffer. This is what tells the fuse program if a write was finished.
Now we need to add it to the ops:
static struct fuse_operations sfs_ops = {
.getattr = sfs_getattr,
.readdir = sfs_readdir,
.read = sfs_read,
.write = sfs_write,
};
Now we can compile and run our program:
gcc -lsqlite3 -lfuse -D_FILE_OFFSET_BITS=64 sfs.c -o sfs
./sfs -f db.sqlite dir
We can now open a file in another terminal window and try saving it, we should then get the following output in our fuse program:
Writing: /mytable/1
We could do a test where we change the write function to instead open a file, write the buffer there and then close the file. This would save the buffer to a regular file that we can then verify is being written to.
However we will skip that and go to directly generating the sql and executing it to update the sqlite database.
static int sfs_write(const char *path, const char *buf, size_t size, off_t offset, struct fuse_file_info *fi) {
printf("Writing: %s\n",path);
int slashes = count_slashes(path);
if (slashes != 2) {
return 0;
}
char *new_path = malloc(strlen(path) + 1);
strcpy(new_path, path);
char *tableName = strtok(new_path, "/");
char *key = strtok(NULL, "/");
sq_updateData(tableName, key, buf);
return strlen(buf);
}
We go through the same steps as the read function. We check to see if the path contains 2 slashes as those are the only valid things we deal with. We then tokenize the path to get the table name and key.
We then will call our update function which will contain the code to generate and execute the sql query. We pass in the buffer as this is what will contain the contents of the file.
Let's look at our update function:
void sq_updateData(char *tableName, char *key, const char *buffer) {
sqlite3 *db;
int rc = sqlite3_open("db.sqlite", &db);
char *sql = "update mytable set body = 'Hi' where title = '1';";
sqlite3_stmt *stmt = NULL;
sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
rc = sqlite3_step(stmt);
while (rc != SQLITE_DONE && rc != SQLITE_OK) {
rc = sqlite3_step(stmt);
}
rc = sqlite3_finalize(stmt);
rc = sqlite3_close(db);
}
This function will run a hardcoded update, it will update the row with the title 1 with a new body.
We can compile and run our program:
gcc -lsqlite3 -lfuse -D_FILE_OFFSET_BITS=64 sfs.c -o sfs
./sfs -f db.sqlite dir
In another terminal we can open dir/mytable/1 and save it immediately.
We can then close and re-enter the file and we should see that our file has been updated!
We will now write the code to generate the sql from the buffer. In our read we chose the format to display the data in something that we can use directly in sql. This is going to be the naive way of doing things as we are simply going to try and write out exactly what we have in the file.
An example:
title = 'SomeTitle'
body = 'PostContent1'
We should now be able to use this directly in our sql with very little processing.
int sq_updateData(char *tableName, char *key, const char *buffer) {
sqlite3 *db;
int rc = sqlite3_open("db.sqlite", &db);
char *data = strdup(buffer);
int datalen = strlen(data);
int i;
for (i = 0; i<datalen; i++) {
if (data[i] == '\n') {
data[i] = ',';
}
}
if (data[datalen-1] == ',') {
data[datalen-1] = '\0';
}
int length = snprintf(NULL, 0, "update %s set %s where title = '%s';", tableName, data, key);
char *query = (char*) malloc(length + 1);
snprintf(query, length, "update %s set %s where title = '%s';", tableName, data, key);
sqlite3_stmt *stmt = NULL;
sqlite3_prepare_v2(db, query, -1, &stmt, NULL);
rc = sqlite3_step(stmt);
rc = sqlite3_finalize(stmt);
rc = sqlite3_close(db);
free(data);
return rc;
}
We start by opening the database like usual. We then duplicate our buffer and go through it. The first thing we'll do is change the new lines to commas. We then terminate it properly if the last character is a comma.
Now we can generate our update query using snprintf.
We then can step through the update and this should be a single call.
With that we are done! We can close the database and free our duplicate string and return back to our write function.
We could add error handing so that if something went wrong we return the rc and then we trigger errors in our write function. But I'll leave that for the future as the goal is to just get this working right now.
Compile and run:
gcc -lsqlite3 -lfuse -D_FILE_OFFSET_BITS=64 sfs.c -o sfs
./sfs -f db.sqlite dir
And in another terminal we can open a record and change it! We should then be able to close it and come back and everything should be updated.
At this point we are done! We have a way of exposing sqlite as a file system now where we can see tables as directories and see rows as files. We can read and write these files and this will update our sqlite database.
Now that this is done, the next step is to make this into a json file so that I can push straight json files into sqlite. That would make much easier to use I think.