I'm having a database class. At a certain point, my professor proposed us an SQL lab practice were we would be tasked to write SQL queries to interact with a database installed in our lab computers. For this practice, each lab computer would need to have a DBMS installed along with a populated database that would need to be replicated between the computers. I proposed to my professor for us to work together during the weekend to create a portable application and web interface to interact with a remote database.
The application is a self contained HTTP python server with a python CGI script to access a local sqlite database. A web page interfaces the CGI script. The user writes SQL queries on the web interface and these queries are sent to the CGI script. The CGI script sends the queries to the sqlite database, which are executed by the DBMS, results are returned to the web interface and displayed to the user.
The web interface has a text editor with SQL syntax highlighting. There is a button that sends the SQL query to the remote database server, returning the resulting table or an error message to the web interface. There is also a query button that fetches the current table schema from the remote server. For teaching purposes, there are no restrictions towards the type of the SQL query being executed, be it DDL or DML.
The database schema appears just below the editor. In the picture above, there are multiple tables along with its names and the name of its columns. The database structure reference is useful when the user is writing the SQL query on the editor.
Below to the schema it is shown the server response to the sent query. It can be a table, generated after the query execution, or a database error message. The error message's content is generated by the remote database system and it is useful so there is a response in the case of the SQL query containing errors, so it can give useful information such as indicating the error type and in which part of the query this error is located.
There is also a side menu for navigation between the editor, the schema and the server response. When clicking one of the buttons on the menu, the browser window rolls to the relative position indicated by the button.
The web interface is built using the Bootstrap CSS framework, which allowed me to develop a sleek and responsive site with minimum effort, at the cost of originality.
The web interface's text editor is the embed version of Ace, a web based editor with syntax highlighting, smart indent, customizable and free software. It's easy to install and it gives a nice elegant touch to the interface.
The server side code is distributed together with the project. It's a python script that creates the instance of a CGI HTTP server, serving the files inside the project's directory, making the project portable and multi platform but with impacts to the performance.
Python has multiple libraries that facilitate applications' development. The server-side script that interfaces the database to the client's web interface makes use of the CGI library to answer to POST requests and the sqlite library to query the database. The result from the SQL queries, the query status and the generated table are formatted to JSON and sent back to the client's web interface. A JSON encoded return example is shown below.
[ [0, ""], ["nome", "endereco"], [ ["CAMILA DIOGENES PONTUAL", "RUA F"], ["JOANA MOREIRA FURTADO", "RUA B"], ["JOAO JOSE DA SILVA", "RUA X"] ] ]
In this example of a JSON encoded return from a SQL query, there is a 3 slots array.
The first slot corresponds to the return status. Its a 2 slots array. The first field holds te error code. Any number bigger than zero indicates an error. The second slot is the error message, empty when there is no error.
The second slot is an array with the returning table columns' names. Each lot in this array holds a string with the name of the returning table's column.
The third slot provides the returning table's tuples from the SQL script execution. Each tuple is an array and its lenth matches the column's name array.
The SQL script is sent by a POST request from the client's web interface to the server using a jQuery function. The function's callback returns JSON encoded data both corresponding to SQL script's execution returning table as well as an eventual error message.
The application was put to a test in class, when around 30 students used the application hosted in my raspberry pi and interacted simultaneously with the sql database made by my professor for this class, populated with 6 tables and multiple tuples.
Download the application.