This week I will tell you how we finally concluded a problem that had been pending since the last GSoC. It took the combined effort of my mentor, me and Angela(contributor who worked on this problem under GSoC 2021) to put this problem to bed. The problem was very simple😉. We needed to make the TWP42: Bases de Datos lecture interactive. These set of lectures aim at teaching basic SQL concepts to the students. The core problem with this was the same as with the pygame lecture. The skulpt interpreter did not support the sqlite3 library which was to be used to teach these lectures. In the rest of the blog I will try to explain the initial approaches made by Angela last year and the final approach that we came up with to tackle the problem.
Let me take you back
Back in August, 2021, Angela had 3 approaches to solve this problem. All these approaches were good in their own way but came with their own problems. I have listed the 3 approaches here with their problems below in Angela's own words:
-
Using the activecode SQL language from Runestone: SQL activecode approach #185
Some configuration to use this SQL tool from Runestone must be done, because when you try to access the filesurf.db, a CORS error shows up in console and nothing is shown. There might be something related with the book not being served by Runestone because in this error it tries to access to the db from an URL in runestone site:/runestone/books/published/PyZombis/_static/sql-wasm.wasmasuming that the book is published. -
Using sql.js library from javascript: Refactor lecture TWP42: Bases de datos (sql.js approach) #184
This other option loads the database using the.. raw:htmlinstruction from runestone.
This is the best attempt since you need to find a way to access to the global javascript variabledbfrom the brython activecode block. Enter this PR for more detail, but if you call db in browser console you can see the database. -
With indexedDB: Refactor Lecture TWP42: Bases de datos (indexedDB approach) #171
This aproach works, but the syntax changes a lot from the original lecture wheresqlite3was used.
Out of all these approaches, the second one(one using sql.js) seemed the most solvable to me as there was one problem with it: inability to access the global databse variable from within the activecode block. Although the variable was global, accessing it from within the activecode block was giving problems. As it turns out the solution to this was just a line of code 🤡.
My initial approach and the sqlite3 wrapper
The path to the one line solution to our problem was long. First, I wrote a sqlite3 wrapper that used sql.js at its core to process and execute the queries. Since the database variable was not accessible, I had to pass the queries using attributes of the script tag. To achieve this I created a mutation observer that reloaded the contents of the script with id "create-db-script", anytime the attributes of this particular tag was changed. This accounted for multiple queries to be executed. However handling COMMIT and ROLLBACK commands was another problem, as they required knowledge of the previous command and their results.
The following code explains my method:
<script id="create-db-script" ,="" query="" result="" flag="0" error="" first_exec_flag="">
config = {locateFile: filename => `https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.5.0/sql-wasm.wasm`}
// a fuction is created that returns a promise
// this function can be called everytime the query is executed
function getData(config){
return initSqlJs(config).then(function(SQL){
if(document.getElementById("create-db-script").getAttribute("first_exec_flag") == "true"){
console.log("first_exec_flag is true")
//Create the database
window.db = new SQL.Database();
//Create table surfers
db.run("CREATE TABLE surfers (id, nombre, pais, media, estilo, edad)");
//Insert records
db.run("INSERT INTO surfers (id, nombre, pais, media, estilo, edad) VALUES (101, \"Johnny 'wave-boy' Jones\",\"USA\",8.32,\"Fish\",21)");
db.run("INSERT INTO surfers (id, nombre, pais, media, estilo, edad) VALUES (102, \"Juan Martino\",\"Spain\",9.01,\"Gun\",36)");
db.run("INSERT INTO surfers (id, nombre, pais, media, estilo, edad) VALUES (103, \"Joseph 'smitty' Smyth\",\"USA\",8.85,\"Cruizer\",18)");
db.run("INSERT INTO surfers (id, nombre, pais, media, estilo, edad) VALUES (104, \"Stacey O'Neill\",\"Ireland\",8.91,\"Malibu\",22)");
db.run("INSERT INTO surfers (id, nombre, pais, media, estilo, edad) VALUES (105, \"Aideen 'board babe' Wu\",\"Japan\",8.65,\"Fish\",24)");
db.run("INSERT INTO surfers (id, nombre, pais, media, estilo, edad) VALUES (106, \"Zack 'bonnie-lad' MacFadden\",\"Scotland\",7.82,\"Thruster\",26)");
db.run("INSERT INTO surfers (id, nombre, pais, media, estilo, edad) VALUES (107, \"Aaron Valentino\",\"Italy\",8.98,\"Gun\",19)");
}
// the below code sets the flag and query attributes of the script tag depending on the result of the query
// this is done to avoid the need for a callback function
// the query attribute is set to the query string
// the result attribute is set to the result of the query
// the flag attribute is set to 0 initially
// the flag attribute is set to "data found" if the query is successful and the result is not empty
// the flag attribute is set to "no data" if the query is successful and the result is empty
// the flag attribute is set to "query error" if the query is unsuccessful
try{
res = db.exec(document.getElementById("create-db-script").getAttribute("query"));
console.log(res);
} catch(e) {
console.log(e);
document.getElementById("create-db-script").setAttribute("error",e);
document.getElementById("create-db-script").setAttribute("flag","query error flag");
document.getElementById("create-db-script").setAttribute("result",JSON.stringify("query error"));
res=undefined;
console.log(document.getElementById("create-db-script").getAttribute("flag"));
console.log(document.getElementById("create-db-script").getAttribute("result"));
}
if(res !== undefined && res.length > 0 && res[0].values.length > 0) {
document.getElementById("create-db-script").setAttribute("result",JSON.stringify(res));
document.getElementById("create-db-script").setAttribute("flag","data found");
console.log(document.getElementById("create-db-script").getAttribute("flag"));
console.log(document.getElementById("create-db-script").getAttribute("result"));
}
else if(res.length==0){
document.getElementById("create-db-script").setAttribute("flag","no data");
document.getElementById("create-db-script").setAttribute("result",JSON.stringify("no data"));
console.log(document.getElementById("create-db-script").getAttribute("flag"));
console.log(document.getElementById("create-db-script").getAttribute("result"));
}
});
}
let coun = 0;
// creating a mutation observer function to observe changes in attributes of script with id "create-db-script"
var observer = new MutationObserver(function(mutations) {
mutations.forEach(function(mutation) {
if (mutation.type === "attributes") {
if(coun == 0){
console.log("first mutation");
coun++;
document.getElementById("create-db-script").setAttribute("first_exec_flag","true");
}
else{
console.log("next mutation");
document.getElementById("create-db-script").setAttribute("first_exec_flag","false");
}
getData(config);
}
});
});
// the observer below only observes the attribute "query" of the script tag with id "create-db-script"
// if the query attribute is changed, the function getData is called
observer.observe(document.querySelector('#create-db-script'), {
attributes: true ,
attributeFilter: ['query']
});
</script>
After this I designed a basic sqlite3 wrapper around sql.js that could mimic the basic functionalities of the original package:
import document, time, json
def formato_datos(data):
"""
esta función formatea el resultado de sql.js para que coincida con el formato de salida de sqlite3
params -> data: diccionario con los datos a formatear
"""
final_list = []
columns = data['columns']
values = data['values']
for v in values:
Dict = {}
for c,d in zip(columns,v):
Dict[c] = d
final_list.append(Dict)
return final_list
# creando un contenedor para imitar el módulo sqlite3
# actualmente solo algunas funciones como connect, execute, fetchall son imitados
class Cursor:
def __init__(self):
self.prev_query = """"""
self.curr_query = """"""
self.prev_result = None
def execute(self, query):
query = query.strip()
if(query[-1] != ";"):
query += ";"
self.curr_query += query
self.curr_query += "\n"
# the below function returns the result of the query
def fetchall(self):
# get the script tag with id "create-db-script"
script_element = document.getElementById("create-db-script")
# set the "query" attribute of the script tag to the query string
if(self.curr_query == ""):
self.curr_query = self.prev_query
if(script_element.getAttribute("first_exec_flag") is None):
self.curr_query = "BEGIN TRANSACTION;\n" + self.curr_query
if(self.prev_query == self.curr_query):
return formato_datos(self.prev_result)
else:
script_element.setAttribute("query", self.curr_query)
self.prev_query = self.curr_query
self.curr_query = ""
# the below code waits for result
# the result of the query can be one of 4 things
# 1. the result of the query is data present in the table
# 2. the result of the query is an empty list as no data is present in the table satisfying the query
# 3. the result of the query is an error because the query is invalid
# 4. the query is taking too long to execute, in this case if the qury takes more than 5 seconds, the flag is set to "query error flag" and the result is set to "query error"
start = time.time()
while(script_element.getAttribute("flag") != "data found" and script_element.getAttribute("flag") != "query error flag" and script_element.getAttribute("flag") != "no data"):
time.sleep(0.1)
if(time.time()-start > 5):
script_element.setAttribute("flag", "query error flag")
script_element.setAttribute("result", "query error")
break
result = json.loads(document.getElementById("create-db-script").getAttribute("result"))
if(result=="no data"):
document.getElementById("create-db-script").setAttribute("result", "")
document.getElementById("create-db-script").setAttribute("flag", "0")
raise Exception("No data found in the table for the query.\n\nException raised by sqlite3")
elif(result=="query error"):
document.getElementById("create-db-script").setAttribute("result", "")
document.getElementById("create-db-script").setAttribute("flag", "0")
error_msg = str(document.getElementById("create-db-script").getAttribute("error")) + "\n\nException raised by sqlite3"
raise SyntaxError(error_msg)
else:
result = result[0]
self.prev_result = result
document.getElementById("create-db-script").setAttribute("result", "")
document.getElementById("create-db-script").setAttribute("flag", "0")
return formato_datos(result)
# the below code resets the flag and result attributes of the script tag for the next query
def close(self):
document.getElementById("create-db-script").setAttribute("result", "")
document.getElementById("create-db-script").setAttribute("flag", "0")
class Connection:
def __init__(self):
self.curr_cursor_obj = Cursor()
def cursor(self):
return self.curr_cursor_obj
def commit(self):
self.curr_cursor_obj.curr_query = self.curr_cursor_obj.curr_query +"COMMIT TRANSACTION;\nBEGIN TRANSACTION;\n"
def rollback(self):
self.curr_cursor_obj.curr_query = self.curr_cursor_obj.curr_query +"ROLLBACK TRANSACTION;\n"
def close(self):
pass
def connect(table_name=None): return Connection()
It can be seen that the code got unnecessarily complex and a better way to handle different queries was needed.
What was the solution?
The final solution to this problem came to my mentor who discovered that the global Database variable could be accessed within the activecode block by using
db = window.parent.DatabaseThis simple solution simplified the wrapper a lot and also ended the need to pass queries to attributes and need of a mutation observer.
The database could now be initialised or even loaded from a file very simply:
<script type="module">
const sqlPromise = initSqlJs({
locateFile: file => `https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.5.0/sql-wasm.wasm`
});
const dataPromise = fetch("../../_static/surfers.db").then(res => res.arrayBuffer());
const [SQL, buf] = await Promise.all([sqlPromise, dataPromise])
window.Database = new SQL.Database(new Uint8Array(buf));
</script>Thus came the end of one of the important issues in my proposal this year. Issue #193 could now be closed😊. The final PR can be seen here.
