Javascript in Hype to query a read-only SQLite database

Hi,

This question is as much about Javascript as about Hype, but it involves making the two work together and I reckon the the experts on this forum will be able to give me the best insights into how to proceed.

I'm thinking about building a Hype project that is basically a quiz -- it selects questions from an extendable read-only 'database' containing a fairly large number of questions and answers that are classified according to difficulty and subject. Questions within a given subject and difficulty level to be chosen at random. This is fun project for children aged 6 to 10.

At first I thought I'd hard-code the Q & As but clearly that is inefficient and difficult to update. Then I thought I'd create one or more JSON files, but the thought of getting to grips with the complex syntax of JS fetch() and handling all those promises makes me go slightly pale. (I'm not really up to developer level at JS.) And I thought that implementing future changes with a JSON set-up could be quite daunting.

Then I thought what about an SQLite database for the Q & As? I could easily load and edit the database with the DB Browser for SQLite app from SQLitebrowser.org and I could save the .db file in the Hype resources folder. It all sounds very elegant, but how to read from the database with JS functions in the Hype project?

I'm guessing it needs some sort of SQLite library for JS that defines an SQLite object with some associated methods for querying the DB. Does one exist? There is a module for node.js called sqlite3, but I'm not expert enough to say whether that might be relevant to my problem. And there is sql.js on GitHub, but I'm guessing that's for a server-side database. I get the horrible feeling that, in my efforts at simplicity and elegance, I am now swimming into deeper and shark-infested waters. In fact, I swear I can hear the music from Jaws. Should I swim back to wrestling with JSON? Or what?

Any advice or comment much appreciated.

Is this for your offline project? If so, then what I just did for Widgets seems like a good match for you.

Widgets is an HTML/JavaScript/CSS project in a WKWebview. (It's "wrapped" with Wrapping.) The Bible, Dictionary, Zip Code and other data was stored with JavaScript, which was a quick way to get the project started. But as more and more data was added, the memory management was getting worse. So, I needed a proper database. Like you, I started looking for a JavaScript solution, but I wasn't happy with what I found.

It looks like you already found the app I would have recommended.
THAT APP IS AWESOME. LOVE IT!

So, you solved a big part of the problem — creating an SQLite database. Your problem is how do you read that data?

That's why I chucked the Widgets data into Xcode. From there, many people would recommend using one of the libraries to manage SQLite, like this... https://github.com/ccgus/fmdb ..or this... https://github.com/stephencelis/SQLite.swift ...or this... https://github.com/groue/GRDB.swift ...but I just wrote my own code, because all I needed to do was just search the database.

I'm not sure if this is the route you want to go though, because it involves coding. It could involve A LOT OF CODING. That's the problem I had the last couple of months... whew!

That solution works for me, but it might not work for you. I'm building an app. Maybe you don't want to go that route. So, let's look more closely at your problem...

Is it though? Here's an example...

...and here's another example built with the same idea...

...they're both using multidimensional arrays to manage the data. Is it hard to update? Dynamically, yeah that's a problem, because it's not pulling data from anywhere. It's self-contained. But if I wanted to update the Hype project, that's easy. I just use some comma-separated data exported from a spreadsheet and then I convert it with this...

https://shancarter.github.io/mr-data-converter/

It's basically cut-and-paste... and then cut-and-paste... with some minor modifications. The problem is memory. Realizing that Widgets needed to scale... adding even more data... I needed a proper database. But if this is just a small, static, rarely updated quiz, then eh... I might go with the easier route.

In the "Capitals" example, the questions are randomized by JavaScript. No problem.

Does that help?

2 Likes

It seems like you arrived at using JSON as a way to use an external file instead of editing inside of Hype, and possibly allowing for updates without re-uploading the whole Hype document?

I think you can have an easier flow without any javascript required to load, just by using a <script src="https://server.com/path/to/data.js"></script> at the top of your document. (Alternately just put the .js file in the Hype Resources folder)

Just set a global javascript variable to your object, which contains the quiz data. ex:

window.quizData = [
   {"question" : "what is your name?", answer : 0, "responses" : ["arthur", "lancelot", "tim"]},
    {"question" : "what is your favorite color?", answer : 0, "responses" : ["blue", "green", "don't know"]}
];

Then in Hype the window.quizData variable will be available to you without any loading.

1 Like