Sqlstone - Part 2: Every User Owns A Personal Copy of the Database: Solving Backend Challenges (Data Availability / Ownership)

• Download sqlstone_v002.zip - 20.5 MB

GitHub - raddevus/sqlstone: C# .NET Core 8.x ASP.NET MVC which creates a new sqlite db for every registered user to store their own data in. Because? Why not?[^]

Introduction

You can read the first article in this series, here on CP at:

Sqlstone: Every User Owns A Personal Copy of the Database: Solving Backend Challenges (Data Availability / Ownership)[^]

What This Article Covers: Enhancements

In the previous article I promised to add four enhancements:

1. User delete of individual Journal Entries - Not Complete Yet
2. Allow user to download of entire Sqlite database (and how user can use it locally) - Completed
3. User delete of remote database and UUID. - Partially Completed - explained in this article
4. Add QR Code generator for UUID for easily getting UUID and loading data on other device - Completed
5. I also added one additional enhancement which verifies that the UUID is something close to what I'm expecting in the UUID.  The new code requires the UUID value to 1) contain exactly 4 hyphens and 2) be exactly 36 chars in length (including the 4 hyphens).  I noticed that some users were saving UUID values like "test1" or whatever.  This new enhancement helps protect the file system.

Development Theme

If I were to explain a consistent theme of Software Development I might use the phrase:

Anonymous:

"In theory, practice and theory are the same.  But, in practice they are not."

I might just slightly change that to:

raddevus:

"In Dev (localhost), practice and theory are the same.  But, in Prod (public URL) they are not."

That's because some of the functionality I attempted to get working was easy to get working in the Dev environment but worked different in my Prod environment.  "Works on my Server!"

Well, we'll get to all of that, but for now let me show you the easiest enhancement that I made and take a look at what it does.

Generate QRCode From UUID

In an effort to allow the user to access her data from anywhere, I've added a simple way to get the UUID from one device to another.  I've added the ability to generate a QRCode from the UUID.

All the user has to do is click the barcode icon button next to the UUID field and they'll get a QRCode generated.  

Yes, You Could Hack

Yes, you could take the following steps to write to my UUID database:

1.  point your device camera at the image in this article
2. grab the UUID from the QRCode
3. Go to https://newlibre.com/journal^
4. Paste the UUID into the UUID text box
5. Click the [Gen/Set UUID]
6. See the entries that are inserted into that db
7. add new entries to that copy of the db
8. download a copy of this specific db (more on this later)

UUID : "Fast Login"

Since no one is going to guess the UUID I just log the user in using the UUID. 

• Yes, if someone hacks my web site they will see all of the UUID folders and be able to get to any of the Sqlite databases. 
• Yes, that is not quite secure. 
• Yes, this is a prototype and I'm going to show you how to solve that issue later.

Ok, let's download the user database.

Download the User Database

All the user has to do is click the link on the page to download a copy of their Sqlite database.

It'll download it to your Web Browser's Download directory.  It's that simple.  But, the WebAPI (C#) and JavaScript behind it isn't entirely simple, so let's look at that.

Here's the code that you can find in the UserController class file.

[HttpPost]
    public ActionResult DownloadSqliteDb([FromQuery] String uuid)
    {
        var userDir = Path.Combine(webRootPath,uuid);
        var journalDb = Path.Combine(userDir,templateDbFile);
        Console.WriteLine(journalDb);
        return new PhysicalFileResult(journalDb, "application/x-sqlite3");
    }

The user has to pass in the UUID to identify which db she is attempting to download.

We then just create the User File Space path to the sqlite db (remember from first article the template Sqlite db is named sqlstone_journal.db).

I then discovered that you have to us PhysicalFileResult() to return the bytes of the file properly.  There is a very odd thing that will happen if you try returning a FileResult().  That odd thing happend to me and I nearly gnawed my arm off trying to fix the issue.  Then I finally discovered some documentation about it and wrote it up at my blog^.

You Need JavaScript To Handle PhysicalFileResult

When the file is returned, I decided I wanted to save it with a name which would contain a simple timestamp so that the downloading browser wouldn't just name it file(1).db, file(2).db, etc.
Also, that means if you have multiple UUID journals that you can download them without overwriting them.

Here's the JavaScript fetch call that we make in main.cs.

function downloadSqliteDb(){
    console.log("1");
    if (currentUuid != null){
        fetch(`${baseUrl}User/DownloadSqliteDb?uuid=${currentUuid}`,{
                method: 'POST',
            })
            .then(resp => {console.log(resp); return resp.blob();})
            .then(blob => {
                downloadFile(blob,`journal-${GetFileTimeFormat(new Date())}.db`);
            });
    }
    else{
        
        uuidRegisterAlert("You need to register your UUID to be able to download your sqlite data.\Please register your UUID & try again.",true);
    }

}

As you can see I also call another JS function GetFileTimeFormat() which I use to name the file with the timestamp.  I'll let you look at that code in the source.

The interesting code is the downloadFile() JavaScript. It's weird but it works.

It temporarily adds a child node a href tag then removes it.  

function downloadFile(blob, name = "journal.db") {
    const href = URL.createObjectURL(blob);
    const a = Object.assign(document.createElement("a"), {
      href,
      style: "display:none",
      download: name,
    });
    document.body.appendChild(a);
    a.click();
    URL.revokeObjectURL(href);
    a.remove();
}

Crazy stuff.

Just one more piece of functionality to talk about here.  I wanted to make it so a user could destroy her account.  That way she owns her own data.

Destroy the Account & Related Sqlite DB

Who am I to judge?  If you want to get rid of your journal data, I'm happy to let you do it.

Head back over to the source code in UserController.cs and take a look at DestroyUserAccount().

[HttpPost]
   public ActionResult DestroyUserAccount([FromQuery] String uuid){
       // Every valid UUID will have 4 hyphens & be 36 chars long
       int hyphenCounter = uuid.AsSpan().Count('-');
       if (uuid.Length != 36 || hyphenCounter != 4){
           return new JsonResult(new {result=false,message="Your uuid doesn't look like a valid value.\nCannot delete account."});
       }
       var userDir = Path.Combine(webRootPath,uuid);
       try{
           Directory.Delete(userDir,true);
       }
       catch(Exception ex){
           return new JsonResult(new {result=false,message=$"Error! Delete Failed. \nCannot delete account. {ex.Message}"});
       }
       return new JsonResult(new {result=true,message="The user account and all associated data has been destroyed."});
   }

Post in the UUID and after I do a couple of checks to insure it looks like a valid ID the WebAPI will call Directory.Delete() with the true parameter (which indicates that it should destroy all folders and files within).

Verification of UUID

I discovered I needed to do this or some wise-guy would come along and make his UUID css or some other folder name and then delete my web folders.  Haha! Wouldn't that be funny?  NOT!

So I just check that the UUID has 4 hyphens and is exactly 36 chars.  If it fails either test then I reject the user's post.  I reject you and all that you are!! 😁

Here's the associated code in JavaScript which calls the DestroyUserAccount endpoint.

function destroyAccount(){
    
    fetch(`${baseUrl}User/DestroyUserAccount?uuid=${currentUuid}`,{
        method: 'POST',
    })
    .then(response => response.json())
    .then(data => {
       if (data.result == false){
            alert(`Could not destroy the account. \nError -> ${data.message}`);
            return;
       }
       uuidRegisterAlert("The UUUID, User Account & all associated data was permanently deleted.");
       localStorage.removeItem("currentUuid");
       currentUuid = null;
       document.querySelector("#uuid").value = "";
       window.location.reload();
    });
    document.querySelector("#modalCloseBtn").click();
}

The Problem With Deleting Accounts

This all worked great on my localhost.  The delete succeeded every single time.

But when I moved the code to my Production server (https://newlibre.com/journal) I started getting a 500 error.

I was totally confused until I put a try/catch in the API call.  Once I did that I discovered that there was an error deleting the Sqlite DB because the service thinks the Sqlite DB has a connection open.  Since it thinks the file is in use it will not allow me to delete the Sqlite db and remove the directory.  

Rabbit-Hole: EF Core, Sqlite DB Connections, etc.

Now I will dive down the rabbit-hole and try to discover a way to force the app to close the connection but there is a lot of technology going on there and it may be difficult to discover who is keeping the connection open. 

Alas, this is the life of a software developer.

How To Initiate the Account Delete?

Click the trash can icon (highlighted in image below) and a warning dialog will pop up.
If you want to destory the account, click the [Destroy Account] button.
You will get another pop up (cuz everyone love popups) to let you know if it succeeded or not.

Check out the source code, try it out and let me know what you think.