SQL Selects in JavaScript with PublicSQL

Popular Demand

By Peter Kreußel and Nils Magnus

Most state-of-the-art websites include some form of server-side database, but developers building HTML pages onto CD-ROMs have fewer options. Other situations also might favor keeping the data and program logic on the client.

Enter PublicSQL [1], a JavaScript program that acts as a simple SQL database for access from JavaScript apps. The JavaScript security model doesn't let the script store new data because the browser is not allowed to use JavaScript to write to disk, which means that PublicSQL is better suited for read-only settings.

Although HTML 5 has its own brand of client-side SQL database within the browser that is similar to, and actually better than, PublicSQL, it is not yet supported by most browsers, so PublicSQL should be useful for at least three more years [2].

The PublicSQL database can handle predefined data with familiar SQL commands (see the "Installation and Use" box). PublicSQL supports SELECT statements with WHERE and ORDER BY clauses (Table 1). In addition to string and numeric fields, tables can contain date columns. Multiple tables can be linked with inner joins, and the instruction set includes a DISTINCT instruction. The feature scope is not huge, but many tasks difficult to handle with pure JavaScript shrink to a couple of lines of code if you use PublicSQL. A sample application that searches the fields of a database for fruits and vegetables (Figure 1) demonstrates this. Figure 2 shows the schematic layout. The whole application is available for downloading on the Linux Magazine website [3], so you can try it out locally.

Figure 1: PublicSQL implements a local search over an arbitrary number of fields by using JavaScript to create a dynamic SQL statement.

Figure 2: Event handlers: after clicking, the Field Selection drop-down box and the input box for the search box appear on the page.

PublicSQL needs a JavaScript-accessible file for each table. The file can either reside on the local filesystem - as with a data CD - or be loaded retrospectively with HTTP. Each file contains an array you can create easily from CSV files in production use; you just need to add two lines at the beginning and end [4].

<script type="text/javascript" src="publicsql.js"></script>

One-Way Street

Listing 1 is a simple example of HTML with PublicSQL. If no server exists to respond with a new page to values sent by POST or GET, a permanently open page can display the results - working as a search form at the same time. Lines 7 through 11 define a form with just two buttons: the + Search criterion button (line 8) uses the add_searchfield() event handler linked to it to add a line with a search condition (Figure 1). Clicking the Execute search button (line 10) starts the process. Below the form in the code is an empty div tag (line 13) into which the execute_search() function drops the search results.

To start a search, click on + Search criterion. To insert a list field, select the field you want to search (Figure 1, left). The add_searchfield() event handler (Listing 2) handles the insertion. code populates the first line of the function with the opening tag of a drop-down list field: <select name="searchindex" onclick="add_input(Index, this)">. The onclick event handler here is explained in the next section. A loop (lines 10-14) adds an option tag for each element in the fieldnames array

<option value="0">[please select]</option>
   <option value="1">Fruit or vegetable</option>

and so on. Line 15 closes the select tag and adds an empty span tag with the ID input_search fieldcount, making it easier for add_input() to insert the input field in the second column later. The fieldcount line counter helps keep the divs apart.

01 var fieldcount=0;
02 var fieldnames=[ "[Please select]",
03    "Fruit or vegetable", "Color",
04    "Taste", "Size" ];
05
06 function add_searchfield() {
07    var code = '\n<select name="search' +
08      fieldcount + '" onclick="add_input(' +
09      fieldcount + ', this)">' + '\n';
10      for (i in fieldnames) {
11         code += '<option value="' + i +
12           '">' + fieldnames[i] +
13           '</option>\n';
14      }
15      code += '</select>\n<span id="input_search' +
16        fieldcount + '"></span>';
17    fieldcount++;
18    var newSpan=document.createElement('div');
19    newSpan.innerHTML=code;
20    document.getElementById("inputarea").
21    appendChild(newSpan);
22 }

publicSQL.query("select * from projects", "publicSQL.show");

/* Portable Table Format 0.9 */
porTables[porTables.length] = new Array("country", "sum", "activity", "Germany", 100000, "Tunnel building", "Belgium", 23456, "Promotional project", [...], "France", 76543, "Infrastructure", 3);

Second Choice

In the drop-down field, the user can select from a list of search fields defined in the code (Figure 1). Because the value tag the field created in line 11 contains a numeric value between 0 and 4. Clicking the drop-down field calls the onclick handler with fieldcount and a reference to the drop-down field that called it (this) - all of this is pure theory so far because it is not until lines 18 through 21 that the HTML code is built into the page.

Initially Empty

The createElement() function starts by creating an empty div element; line 19 populates it with the content of code. Following this, appendChild() appends the content of the div inputarea which was empty when the page was loaded. appendChild() does not overwrite existing code, so you can add any number of lines with search parameters. The add_input() event handler (Listing 3, line 17) adds the second column for a value parameter when selecting a search field. Unfortunately, this does not work with Konqueror because Konqueror's script engine ignores dynamically added event handlers.

01 var input_code=[
02    '',
03    '<select class="result" name="type">
04     <option value="0">fruit</option>
05     <option value="1">vegetables</option>
06     </select><br/>',
07    '<input class="result" type="text"
08      name="color"><br/>',
09    '<input class="result" type="text"
10      name="taste"><br/>',
11    '<select class="result" name="size">
12     <option value="0">small</option>
13     <option value="1">medium</option>
14     <option value="2">large</option>
15     </select><br/>' ];
16
17 function add_input(n, e) {
18    document.getElementById('input_search' + n).innerHTML =
19      "\n = " + input_code[e.value];
20 }

add_input() uses the n line counter to find the correct input_searchn div element. e returns a reference to the left drop-down field, e.value, which is the numeric value selected by clicking the field. On this basis, add_input() the checks the code table in the input_code[] array. To match the search file selected in the left column, this contains the HTML code for the right input field/search field - either another drop-down box or a text input field. The input field's name attribute also matches the field name selected to the left. This completes the search form, which can be clicked together on the fly. Now it's time to look at the PublicSQL-based search form.

In contrast to forms sent to the server, where the data is automatically located in the POST headers or GET parameters, the program logic first has to parse the permanently open, client-side form in the DOM tree.

The document.form.elements attribute, as seen in line 7 of Listing 4, returns the list of input elements in the form - unfortunately, this includes the buttons, which do not contain any data. Additionally, we only need the input fields from the right column. The name and value pair contains everything needed to query the database. Luckily, the HTML code in the input_code in Listing 3 has thought of this and provides a result class attribute filter criterion.

01 function execute_search(){
02    var element;
03    var sql;
04    var firstloop = true;
05
06    sql = "SELECT * FROM plants WHERE ";
07    for (e in document.form.elements) {
08       element = document.form.elements[e];
09       if (element) {
10          if (element.className == 'result') {
11             if (!firstloop) sql += " AND ";
12               firstloop = false;
13               sql += element.name + " = '" +
14               element.value + "'";
15          }
16       }
17    }
18    sql += " ORDER BY type, size, name";
19    publicSQL.query(sql, "show_result");
20 }

The for loop in lines 7 through 17 of the execute_search function now has no trouble filtering out the required data via the element.className element. In Internet Explorer, the list of form elements (document.form.elements) contains blank entries - nobody knows why, apart from Microsoft. To keep Internet Explorer happy, you thus need to query whether elements[e] is set (lines 8, 9).

The loop in lines 7 through 17 creates the WHERE clause for the SQL statement from the element.name and element.value contents of all the elements in the result class. For a read-only database that stores its data client-side, SQL injection isn't an issue.

Script Dynamically Composes Query

Line 11 (Listing 4) uses AND to link the conditions. Line 6 stores the header for the query in the sql variable; line 18 adds an ORDER BY clause, and line 19 does the work: It calls PublicSQL with the resulting query as its first parameter. As the second parameter, the database engine expects the name of a callback function (show_result(), see Listing 5), which it calls after a successful query with a results array as a parameter.

01 var types=["fruit", "vegetables"];
02 var sizes=["small", "medium", "large",]
03
04 function show_result(result){
05    var html = "\n<table>\n<tr><th>Name</th>
06      <th>Fruit or vegetables</th>
07      <th>Color</th>
08      <th>Taste</th>
09      <th>Size</th></th>";
10    for (line =1; line < result.length; line++) {
11       html+="\n<tr>";
12       html+="<td>"+result[line][1]+"</td>";
13       html+="<td>"+types[result[line][2]]+"</td>";
14       html+="<td>"+result[line][3]+"</td>";
15       html+="<td>"+result[line][4]+"</td>";
16       html+="<td>"+sizes[result[line][5]]+"</td>";
17       html+="</tr>";
18    }
19    html+="</table>";
20    document.getElementById('result').
21      innerHTML = html;
22 } number one

In the result returned by PublicSQL, result[0][column 0 through column n] contains the field names and result[n] [column 0 through column x] the data for the nth line. That is, show_result() need only iterate over array elements starting with index 1 and distribute the columns over the cells of the table. Columns 2 and 5 do not contain the numeric values from the database. The corresponding long text comes from the types and sizes arrays (Listing 5, lines 1, 2). At line 20, JavaScript writes the HTML code to the position reserved in the static basic framework - the div tag (Listing 1, line 9).

01 <head>
02    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
03    <script type="text/javascript" src="publicsql.js"></script>
04 </head>
05 <body>
06    Search for:
07    <form name="form" action="">
08       <button onclick="add_searchfield(); return false">+ Searchcriterion</button>
09       <div id="inputarea"></div>
10       <button onclick="execute_search(); return false">Execute search</button>
11    </form>
12    Search result:
13    <div id="result"></div>
14 </body>

Qualified Functionality

The sample application gives you an idea of the service PublicSQL can give JavaScript programmers. To create a complex search function, you simply need to craft the matching SQL code rather than the complex queries in JavaScript. The same applies to interactive applications that select, recombine, and visualize data at run time. Having the library pass data to a callback function instead of returning them as an array or object is impractical. The search results are only available inside the callback function. If you try to bind the results array to a global variable to keep it available for the downstream program flow, the attempt tends to fail from a race condition.

The number of records is restricted: For tables with more than 10,000 entries, the Mozilla engine returns a Too many constructor Elements error (see the "Performance" box); Internet Explorer can handle a few more. For queries against tables of this size with a resulting volume of no more than 100 records, the browser will respond without a noticeable delay.