Ever had one of those problems at work that seems almost impossible (i.e. very hard) to solve at first? well i did and not so long ago, and tuns out that there is a solution to it.
Problem
Read the contents of a Sqlite database client-side and present them in an HTML based GUI. So my first reaction was, even if there is a solution to this, it surely cant be very efficient reading an entire Sqlite database client-side? well those were the requirements and there were some genuine reasons to back up those requirements, so i had to stop whinging and direct my efforts towards an actual solution.
Solution
Turns out that there is a solution for this! Ever heard of emscripten? Funnily enough i had used it while doing my research to port the OpenCV library to javascript. Anyway there is sql.js, which is a javascript port of the Sqlite database, which could do what i wanted it to do. The problem is the documentation of that project is somewhat lacking, so you are left to Stackoverflow posts and issues on their GitHub project page to figure out the solution to what you need.
Requirements
One of the columns in the database i was reading was of type BLOB and i had to parse the contents of that blob.
Ok enough talk now let’s talk about the solution, for the sake of keeping things simple, let us say we are selecting the sqlite DB file from a web page. Something like this…
<input type="file" id="input" onchange="handleFiles(this.files)" />
The handleFiles function basically takes the file and reads it’s contents using a FileReader. The function looks something like this
function handleFiles(files) {
var file = files[0];
var reader = new FileReader();
reader.readAsBinaryString(file);
openSqliteDb(reader);
}
Once we have the file i.e.sqlite database, let us open the database and the openSqliteDb function is as follows.
function openSqliteDb(reader) {
setTimeout(function () {
if(reader.readyState == reader.DONE) {
var database = SQL.open(bin2Array(reader.result));
executeQuery("SELECT col1, col2, hex(col3) FROM table", database);
} else {
openSqliteDb(reader);
}
}, 500);
So reading a file may take some time, so we use a timeout and we recursively keep trying till we have finished reading the file. Once the reader has read the file, we execute our query. Now if you look at the select query, it selects col1 and col2 and uses the hex() function to open col3. Now the hex function is a way to read the contents of a column which is of type blob. I was struggling over this till i found one of the sql.js project issues on their GitHub page. Anyway prior to finding that i too posted a question on Stackoverflow, which managed to give me alternatives to the solution i had.
SELECT col1, col2, hex(col3) FROM table
Ok now let us have a look at the executeQuery function.
function executeQuery(commands, database) {
commands = commands.replace(/n/g, '; ');
try {
var data = database.exec(commands);
processData(data);
} catch(e) {
console.log(e);
}
}
The data returned by the exec function is JSON data so we can use that data as follows. Here’s is an example of what the process function would look like.
function processData(data) {
var colHeaders = [];
var colMap = {};
var tableContent = [];
var tableHtml = [];
tableHtml.push("<table border='1' cellspacing='2' cellpadding-'3'>");
for (var i = 0; i < data.length; i++) {
tableContent.push("<tr>");
var dataElem = data[i];
if (dataElem instanceof Array) {
for (var j = 0; j < dataElem.length; j++) {
var element = dataElem[j];
if (element.column && !colMap[element.column]) {
colHeaders.push("<th>" + element.column + "</th>");
colMap[element.column] = colHeaders.length;
tableContent.push("<td> " + element.value + "</td>");
} else {
tableContent.push("<td> " + element.value + "</td>");
}
}
} else {
if (element.column && !colMap[element.column]) {
colHeaders.push(element.column);
colMap[element.column] = colHeaders.length;
}
}
tableContent.push("</tr>");
}
tableHtml.push(colHeaders.join(" "));
tableHtml.push(tableContent.join(" "));
tableHtml.push("</table>");
document.getElementById("table").innerHTML = tableHtml.join(" ");
}Conclusion
In this post we looked at how we can use read the contents of a Sqlite database with client-side Javascript, hopefully the above solution saves you the time that it took me to figure it all out.
As usual, if you find any of my posts useful support us by buying or even trying one of our products and leave us a review on the app store.
Comments