In this blog I will explain the details of implementing the second sql lecture.

In week no. 3 I dove deep into the SQL lectures, trying to make them as interactive as possible and making the learning experience more similar to that of SQlite.

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:

1. 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 file surf.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.wasm asuming that the book is published.

2. 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:html instruction from runestone.
   This is the best attempt since you need to find a way to access to the global javascript variable db from the brython activecode block. Enter this PR for more detail, but if you call db in browser console you can see the database.

3. With indexedDB: Refactor Lecture TWP42: Bases de datos (indexedDB approach) #171
   This aproach works, but the syntax changes a lot from the original lecture where sqlite3 was 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.Database

This 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.

This week like the previous one will be dedicated to a POC that has been pending since the last GSoC. Like Pygame, the skulpt interpreter doesn't support sqlite3 library which is the decided library to be used to teach the SQL(Bases de Datos) lectures. The lectures aim to teach the basics like reading a database file, writing sql queries and fetching results.

What did I do this week?

I spent this week resolving some conflicts and upgrading a proof-of-concept PR that I had made earlier. This PR aims at serving as an example on which the SQL lectures can be based on. To sum up my work this week, I switched the code interpreter from Skulpt to Brython, so that I can use JS elements more directly and  freely. I was also shown a solution by my mentor on how to use the database elements directly without using flags or complex logic of any sort. I ended this week by modifying the ssqlite3 wrapper that I had already written from scratch. This wrapper is supposed to almost exactly mimic the original sqlite3 library with functions such as COMMIT, ROLLBACK and BEGIN TRANSACTION.

What is coming up next?

The wrapper is still very new has limited functionality. To teach the lectures more efficiently, more functions of the original sqlite3 library has to be added. The wrapper has sql.js at its core and thus the wrapper has to be designed keeping this fact in mind too. Up next I need to work more on the wrapper and give more sqlite3 like functionalities to it. Only than can we move forward with designing the final lectures.

Did I get stuck anywhere?'

Although most of the upgrade was not major, I had a difficult time finding out a way to implement the load database from a file functionality. 

base_datos = connect('surfers.db')

This line loads the database surfers.db to the variable base_datos. Implementing this using sql.js was troublesome for me as there is no straightforward method to load a database in sql.js. The closest one can get is to first convert the database into an array buffer and then load the array buffer. Figuring out this bit took some tinkering and surfing stack overflow 😂, but I found a solution that served our needs.

The inability of the skulpt interpreter to provide support for pygame and sqlite3 is what caused my selection into this year's GSoC program, well mostly(😉). One of my major under-takings this summer is to make the Pygame lectures interactive so that students may test their code on the go as they learn, which is the case for earlier exercises. This blog describes in detail how I reached to the solution(for now 😁) and what is left to be done.

PYGJS to the rescue

After several unsuccessful attempts of making the old brython fork work, I decided it was time I tried something else. It was then I came across this abandoned fork of pygjs. This library was originally intended to serve the same purpose I was currently looking for, making Pygame work in the browser. Initially it seemed liked the previous brython fork that I had to deal witth, but as I dove deeper into the code I found that this library uses gamejs along with brython for its implementation. Now, all that was left was to find a way to make use of this library considering it worked(😅).

Nothing is as easy as it seems

Although pygjs proved to be a very good option there will still some problems that need to be sorted before it could be used in the lectures. The first problem I faced was due to the project being abandoned. Though most of the code was usable, there were changes or modifications required to make the library behave as it was expected. For example, there were bugs in the key and mouse modules of pygjs that were causing unexpected behaviour. Secondly, the library needed to be used in a specific way . What I mean here is running some very basic instructions in brython like endless while loops, caused the JS thread to be blocked and the crashed the window. This problem was solved using timer.set_interval function offered by brython. One of the other problems was speed. Brython allows import of packages if they have a __init__.py present. But importing packages in this way in the browser makes the loading very slow. To solve this I had to package the pygame module into a pygame.brython.js file. This made loading the pyagame package mush simpler and faster. This PR serves as the final POC for the pygame wrapper and can be used to develop the exercises later.

Does it work?

I think it's safe for to say that Yes, it works for now and can be used to teach Pygame to the students. I have tried using all of the functionalities that may be required to teach Pygame to beginners and this library handled them all, starting from basic drawing to making animations and to playing music in the browser. You can check this out for yourself here: http://pyar.github.io/PyZombis/222/index.html .