PublicSQL
 

Home | Documentation | Test | Download | License | Partner | Contact


Documentation

Introduction

PublicSQL is a SQL version which works with JavaScript. It allocates SQL queries for tables. The tables must be created as a "Portable Table Format" - a special format similar to the CSV format. Because JavaScript can not save files only queries are possible. It is not possible to create tables or change tables in JavaScript. So PublicSQL mainly supports the SELECT statement.

PublicSQL is currently available for use in all personal or commercial projects under MIT-License.

Modification in Version 1.2.1

Modifications in Version 1.2

Modifications in Version 1.1

The following modifications were made since Version 1.0:

Tutorial

The optimum method to reproduce the follow sections is to make a new directory with the name “geoquery”. Then copy the file “publicsql.js” into this directory.

Simple Query

  1. Create a new PublicSQL table. To do so build a new text file with the following content (you can copy this text). Save the file as "continents.ptf".

    /* Portable Table Format 0.9 */
    porTables[porTables.length] = new Array(
    "id", "Continent",

    1, "Africa",
    2, "America",
    3, "Asia",
    4, "Australia",
    5, "Europe",

    2);

  2. Create a new HTML-File. Write the following lines inside the <head> section to include PublicSQL:

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

  3. Insert an onLoad property to the <head> section:

    <body onLoad="showTab()">

  4. Insert the following lines inside the <body> section to execute the SQL query:

    <script language="javascript">
    function showTab() {
    publicSQL.query("select * from continents", "publicSQL.show");
    }
    </script>

  5. Save the file as "geo1a.htm" in the same directory.

  6. Open the file "geo1a.htm" in your browser. The result is the content of the continents table. This was your first PublicSQL-Query.

Click here to show this example

Alternative

The last example used the onLoad property. Another sample is to execute the query while the page is loading, but the Internet Explorer does not work correctly when the query implies more than one table.

  1. Delete the onLoad-property from the <body> section:

    <body>
  2. Write the query without the showTab function into the <body> section:

    <script language="JavaScript">
    publicSQL.query("select * from continents", "publicSQL.show");
    </script>
  3. Save the file as "geo1b.htm" in the same directory.
  4. Open the file "geo1b.htm" in your browser. The result is the content of the continents table.

Click here to show this example

Explanation

All functions and variables in PublicSQL start with 'publicSQL'. In the previous example we used the functions publicSQL.query() and publicSQL.show().
To load the table after the page was loaded we use the function showTab(). The function call for it is contained in the on the onLoad-property of the body tag. If the query only uses one table, it's also possible to execute the query during the loading phase the page.

With publicSQL.query() you start a query. This function has 2 arguments:
Argument 1 contains the query string.
Argument 2 contains the name of the function which handles the result of the query - in this case the function "publicSQL.show".
The result of the query was passed automatically to this function as two-dimensional array. The function "publicSQL.show()" automatically generates a table with the query result.

Joined tables

  1. Create the following table with the 10 cities with the most population (in 2008). Use your text editor to create a new text file with the following content and save this file as "countries.ptf".

    /* Portable Table Format 0.9 */
    porTables[porTables.length] = new Array(
    "id", "contid", "Country", "Population",
    1, 3, "China", 1324.7,
    2, 3, "India", 1149.3,
    3, 2, "USA", 304.5,
    4, 3, "Indonesia", 239.9,
    5, 2, "Brazil", 195.1,
    6, 3, "Pakistan", 172.8,
    7, 1, "Nigeria", 148.1,
    8, 3, "Bangladesh", 147.3,
    9, 3, "Russia", 141.9,
    10, 3, "Japan", 127.7,
    4);

  2. Create a new HTML-File with the following lines inside the <head> section:

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

  3. Insert an onLoad property to the <head> section:

    <body onLoad="showTab()">


  4. Insert the following lines inside the <body> section:

    <script language="javascript">
    function showTab() {
    publicSQL.query("SELECT countries.Country, continents.Continent, countries.Population AS 'Million People' FROM countries, continents WHERE continents.id = countries.contid ORDER BY countries.Country", "publicSQL.show");
    }
    </script>

  5. Save the file as "geo2a.htm" in the same directory.

    Open the file "geo2a.htm" in your browser. The result is the content of the continents-table linked with the countries-table.

Click here to show this example

Alternative

The tables will be loaded directly to have them available at the time the query is issued.

  1. Write the following lines inside the <head> section under the line where "publicsql.js" was loaded:

    <script type="text/javascript" src="countries.ptf"></script>
    <script type="text/javascript" src="continents.ptf"></script>
    <script language="javascript">
    publicSQL.tableNames["countries"] = 0;
    publicSQL.tableNames["continents"] = 1;
    </script>

    Save the file as "geo2b.htm".

  2. Delete the onLoad-property from the <body> section:

    <body>

  3. Write the query without the showTab function in the <body> section:

    <script language="javascript">
    publicSQL.query("SELECT countries.Country, continents.Continent, countries.Population AS 'Million People' FROM countries, continents WHERE continents.id = countries.contid ORDER BY countries.Country", "publicSQL.show");
    </script>


  4. Save the file as "geo2b.htm" in the same directory.

  5. Open the file "geo2b.htm" in your browser. The result is the content of the continents-table linked with the countries-table.

Click here to show this example

Explanation

The fields from the table 'countries' are joined via the 'contid' field with the fields from the table 'continents'.

The fields will be selected by "SELECT continents.Continent, countries.Country, countries.Population". The relevant tables will be selected by "FROM countries, continents " and the tables are joined by the WHERE-condition " continents.id = countries.contid ".
The query is included in the function showTab() because the tables must be loaded after the page was loaded. Thus these function was executed by the onload-event.

Alternative: publicSQL.tableNames contains the indices of the tables that were automatically created during the query of new tables. If the tables are loaded directly we must set the indices manually. The first table has index 0, the second index 1 - and so on.

Search function

  1. Create a new HTML-File with the following lines inside the <head> section:

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

    <script language="javascript">
    function textSearch() {
    var s = "SELECT continents.Continent, countries.Country FROM continents, countries WHERE continents.id = countries.contid";
    s = s + " AND (continents.Continent = \"" + document.form1.searchText.value + "\"";
    s = s + " OR countries.Country = \"" + document.form1.searchText.value + "\")";
    publicSQL.query(s , "publicSQL.show");
    }
    </script>


    Insert the following lines inside the <body> section:

    <form action="" name="form1">
    <input name="searchText" type="text">
    <input name="searchButton" type="button" onClick="textSearch()" value="textSearch">
    </form>

    Save the file as "geo3.htm" into the same director

  2. Open the file "geo3.htm" in your browser. To test the search function, insert a country or continent in the search field. The result is a table with the continent and the country. If you search for a continent it shows all countries of this continent.

Click here to show this example

Explanation

We have constructed a simple form including a text field and a button. The search function is started by a click at the button: onClick="searchText()".

In the searchText() function the select statement was built into the variable s. The WHERE-condition includes the query if the search text exists in the fields continents.Continent or countries.Country. The variable document.form1.suchText.value contains the search text.

Please note that the backslash character is used to define quotes. To work without them one could use single and double quotes for the JavaScript string delimiter and for the string delimiter inside the JavaScript string. Example:
s = s + " OR countries.Country = ' " + document.form1.searchText.value + "')";

Test queries

  1. Create a new HTML-File with the following lines inside the <head> section: :

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

    <script language="javascript">
    function myQuery() {
    publicSQL.query(document.form1.queryText.value, "publicSQL.show");
    }
    </script>

    Write the following Lines inside the <body> section:

    <form action="" name="form1">
    <input name="queryText" type="text" id="queryText" size="120">
    <input name="queryButton" type="button" id="queryButton" onClick="myQuery()" value="my query">
    </form>

    Save the file as "geo4.htm".

  2. Open the file "geo4.htm" in your browser and make some test queries - for example:

    SELECT * FROM continents
    SELECT * FROM countries
    SELECT id , Country AS "State", Population AS "(Mio)" FROM countries WHERE contid = 2
    SELECT Country, Population AS "Mio" FROM countries WHERE Population > 1000
    SELECT a.Country, b.Continent, a.Population AS "(Mio)" FROM countries a, continents b WHERE b.id = a.contid ORDER BY a.Country

Click here to show this example

Creation of a bar chart

  1. Create a new HTML-File with the following lines inside the <head> section::

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

    <script language="javascript">
    function goQuery() {
      publicSQL.query("SELECT countries.Country, countries.Population FROM countries", "showGraphic");
    }
    function showGraphic(t) {
      var myDiv, myText, myArea;
      for (var i=1; i<t.length; i++) {
        myDiv = document.createElement("div");
        myText = document.createTextNode(t[i][0] + ":" + Math.round(t[i][1]));
        myDiv.appendChild(myText);
        myDiv.style.backgroundColor = "rgb(" + (i*8 + 150) + "," + (i*8 + 150) + "," + (i*8 + 150) + ")";
        myDiv.style.width = Math.round(t[i][1] / 1.4) + "px";
        myArea = document.getElementById("Chart");
        myArea.appendChild(myDiv);
      }
    }
    </script>

  2. Insert an onload-property to the <body>-tag:

    <body onLoad="goQuery()">

  3. Write the following Lines inside the <body>-section:

    <p id="Chart"></p>

  4. Save the file as "geo5.htm" into the same directory.

  5. Open the file "geo5.htm" in your browser. The result is a bar chart with the names and population of the countries.

Click here to show this example

Explanation

After the page was loaded the function goQuery is called to execute a PublicSQL query. For the analysis we pass the function showGraphic() as the second parameter.

Into the function showGraphic we build a DIV element for each table row with the country name and the population (rounded to million). The background colour for the DIV elements was built depending on the loop counter. The result is a colour gradient. The width of the DIV elements was calculated by the population. The DIV elements will be attached by the <p> element with the ID "Chart".

 

Implementation

To include PublicSQL in HTML pages write the following lines into the <head> section:
<script type="text/javascript" src="publicsql.js"></script>
Then you can make queries like
publicSQL.query("select * from adress", "report");
The 1. parameter is a string with the SQL query. The 2. parameter is the name of a function which is called after the query execution.
Note: Don't write source after the query request - the script should then run with the source of the function from the 2.parameter.

Structure of the SQL Query

The first parameter includes the SELECT-statement which supports a subset of the SQL-92 standard.

Supported elements from the SELECT-statement: SELECT, AS, DISTINCT, FROM, WHERE, ORDER

Strings should be delimited by quotes. This is required if the strings include spaces or other special characters. You can use single quotes ('), double quotes (") or backticks (`). Relevant is the first found delimiter character - you can not use different quotes into the same SELECT statement.
If the quotes used for delimiting strings are also part of the string, you must use backslash (\) as escape character. This also applies for the backslash itself.
Example:
SELECT description FROM products WHERE description = "much \"fantastic \" article for half the price"
If the SQL command was passed as a string to a function, the JavaScript quotes must be escaped too.
Example:
mysql = "SELECT Name, State, City FROM customers WHERE Name <> \"Myers\" AND City = \"Nashville\" OR State <> \"Florida\"";
In this case it's suggested to use different quotes.
Example:
mysql = 'SELECT Name, State, City FROM customers WHERE Name <> "Myers" AND City = "Nashville" OR State<> "Florida"';

Show the query result

The second parameter includes the names of the function for further processing. This function includes a two-dimensional array with rows and columns of the query result. For example the following function shows the result for an output in a table with the id "mytable":

function showMytable(t) {
 var i,j;
 var newTR, newTD, newTDText;
 publicSQL.htmlTableDelete("mytable")
 for (i=0; i<t.length; i++) {
  newTR = document.getElementById("mytable").insertRow(i);
  for (j=0; j<t[i].length; j++) {
   newTD = document.createElement("td");
   newTDtext = document.createTextNode(t[i][j]);
   newTD.appendChild(newTDtext);
   newTR.appendChild(newTD);
  }
 }
}

The html-source of the table:

<table id="mytable">
 <tr>
  <td>Tabellen-Inhalt vorher</td>
 </tr>
</table>

You can also use the function publicSQL.show() as the second parameter.

Query by remote tables

It's possible to access remote tables with the variable publicSQL.tablePath.
Example:
publicSQL.tablePath = "queries/";
The tables of the follow SQL Queries are searched then by the subdirectory "queries".

It is also possible to use tables which are saved on another server, if the path is specified.
Example:
publicSQL.tablePath = "http://www.mysqltables.com/queries";
The tables of the follow SQL Queries are searched then by the URL "http://www.mysqltables.com" in the directory /queries".

Error handling

A few errors can be identified by the publicSQL.errorNumber variable. The error text for it is saved in publicSQL.errorText. If there is no error the publicSQL.errorNumber is 0 and publicSQL.errorText is an empty string.

 

PublicSQL-Reference

Variables

porTables

The array porTables include all existing tables. The two-dimensional array has the following structure:
porTables[table-number][table-content]
The access to the table will be made by the table number. The table content is another array. The structure of the table content is described at "Portable Table Format" below.

publicSQL.tableNames

Is an array with the table numbers of all existing tables. The access is possible by the table name. The result is the index of the array porTables.
porTables[publicSQL.tableNames["customers"]] is the call to use the table 'customers'.

publicSQL.tableExtension

This string defines the file extension for PublicSQL tables. Default is "ptf" (Portable Table Format).
Usually this value does not need to be modified. If the value was modified table names must not exist with the same name without extension. PublicSQL identifies the tables on the basis of the names without file extensions.

publicSQL.tablePath

This string includes the path for the queries. Default is an empty string (""), thus the tables will be searched for in the actual directory. The path can be modified before the query to load the tables from another directory or web address. .

publicSQL.htmlStandardTable

Includes the standard ID for the show() function, If there is no parameter in the function show() for the table id, this standard ID is used. If a table with this ID does not yet exist, it will be created.

publicSQL.nullValue

Includes the string used to display a NULL value. Default is '-'.

publicSQL.errorNumber

publicSQL.errorText

String with query section in relation to the error number.

Functions

publicSQL.query(qselect, qfunc)

Executes the SQL query function.
The first parameter includes the SQL string of the query.
The second parameter includes the name of the function which is to be called after the query was executed. This function receives a two-dimensional array with the query result as parameter.

Examples

publicSQL.query("SELECT Name, Adress FROM customers", "publicSQL.show");
publicSQL.query("SELECT c.Name, o.ordernumber FROM customers c, order o WHERE c.ID = o.CustomerID", "publicSQL.show");
publicSQL.query("SELECT Name FROM customers WHERE NOT Name = 'Myers'", "showfunction");
publicSQL.query("SELECT Name, City FROM customers ORDER BY City", "showfunction");

In the first and second example the output was given by the PublicSQL function 'show()' . The next examples use the user defined function 'showfunction'.

SQL-Syntax

The (reduced) syntax for a SQL query:
SELECT [DISTINCT] Select-List FROM Table-List [WHERE Search-Condition] [ORDER BY Sort-Instruction]
Select-List ::= * | Column [ { , Column }... ]
Column ::= Column-Item [ [AS] Column-Alias]
Table-List ::= Table-Item [ { , Table-Item }... ]
Table-Item ::=
Table | Table Table-Alias
Search-Condition ::= [Left-Brackets] Search-Term | Search-Term [ AND | OR ] Search-Condition
[Right-Brackets]
Search-Term ::= [Left Brackets] [NOT] [Left-Brackets] Value Relational-Operator Value [Right-Brackets]
Value ::= String | Number |
Column
Relational-Operator ::= = | <> | > | < | >= | <=

String ::= "[ { Character }... ]"
Number ::=
Digit [ { Digit }... ] [ . { Digit }... ]
Sort-Instruction ::= Sort-Term [, Sort-Instruction]
Sort-Term ::= Column-Item [ASC|DESC]
Column-Item ::= Column-Name | Column-Name.Table | Column-Name.Table-Alias

Query-Result

The query result includes the names of the columns in row 0. The following rows include an array with one data record each. The following lines explain the access to the array variable (called t in this example) which includes the query result:

t[0] = array with all column names
t[1] = array with field values from the first row
t[2] = array with field values from the second row.
...
t[t.length] = array with field values from the last row.

t[0][0] = name of column one.
t[0][1] = name of column two.
...
t[0][t[0].length-1] = name of the last column.

t[1][0] = value of the first column of the first row.
t[1][1] = value of the second column of the first row.
...
t[3][6] = value of the seventh column of the third row.
...
t[t.length-1][t[0].length-1] = value of the last column of the last row.

publicSQL.show(t, ident)

Shows the query result in a table including row numbers.
The first parameter contains the array with the query result.
The second parameter contains a string with the ID of the <table>-element or an index of the <table>-element (0 = first, 1 = second etc.) or the <table>-element as an object. The second parameter is optional. If no parameter is assigned the function generates a new table with the ID from publicSQL.htmlStandardTable. If a table exists with this ID this one will be discarded and reused for the output. .

publicSQL.htmlTableDelete(tid)

Deletes the content of a table.
The parameter contains a string with the ID of the <table>-element or an index of the table (0 = first, 1 = second etc.) or the table element as object.

publicSQL.arraySort(arr, col, asc)

This function sorts the query result. It is an internal function used by the "ORDER BY"-statement and can also be used to re-sort the existing result.
The first parameter contains the query result which should be sorted.
The second parameter contains the column number of the column to be sorted or an array with the column numbers of the columns to be sorted.
The third parameter contains the boolean value true if it should be sorted ascending or false if it should be sorted descending or an array with boolean values which defines the sort direction of the columns from the second parameter. This parameter is optional and default do true (sort ascending).

Examples

The followed examples show some function calls. The variable t is used to contain the array with the query result.

ascending sort by column 1:
publicSQL.arraySort(t, 1);

ascending sort by column 2, if the values are equal column 0 will be used to determine the correct order:
var cols = new Array(2,0);
publicSQL.arraySort(t, cols);

descending sort by column 2:
publicSQL.arraySort(t, 2, false);

different sort directions by the columns 1, 0 and 2:
var cols = new Array(1, 0, 2);
var asc = new Array(true, false, true);
publicSQL.arraySort(t, cols, asc);

publicSQL.setTableCache(b)

This function is used to allow or disable loading of tables from browser cache.
Parameters: true or false
Default: true

Depending on browser preferences tables may be loaded from the internal browser cache rather than from the server. This may cause loading of already changed tables.
Specify „false“ to disable loading of tables from browser cache.

Example:
publicSQL.setTableCache(false) – will disable the loading of tables from the browser cache
publicSQL.setTableCache(true) - will enable the loading of tables from the browser cache

publicSQL.getTableCache

This function returns the actual setting of browser cache usage by returning true or false.

Example:
if (publicSQL.getTableCache() == true) alert('Table data may be out of date!');

 

Portable Table Format

Tables in PublicSQL use the “Portable Table Format” which is similar to the known CSV format.
Since JavaScript only allows for loading of JavaScript files we had to adapt the table format to fit these requirements.

/* Portable Table Format 0.9 */
porTables[porTables.length] = new Array(
"Name", "Ort",
"Meyer", "Hamburg",
"Müller", "Hamburg",
"Schmidt", "Hamburg",
null, "Buxtehude",
"Müller", null,
"Müller", "Buxtehude",
2);

The first line defines the table format
The second line needs to be exactly as written here and must not be changed. This statement creates a new internal array to hold the table contents.
The third line defines the column names.
The next lines define the table data, using comma as column separator. Strings must be delimited by single or double quotes. Numeric values must not be delimited by quotes. Each row data must contain a comma as last character (defined by JavaScript).
The last row contains the number of columns for the table.
Important Notice: To allow for processing the table data you must use the single line format as in the example.

PTF supports the following data types: String, Number, Boolean and Undefined.
The actual Version of PublicSQL only supports string and number. Date comparison is done via string values which will produce equivalent results.
Empty fields (NULL in SQL) are defined as null (lowercase character as in sample above) without quotes.

PublicSQL support for variables:

The default file type is “.ptf”. Since different operating systems support for upper/lower case we recommend using lower case only.
The same applies to the file name. For compatibility reasons you should avoid special characters.
We recommend the following convention for file names:

For example we use “rubriken.ptf”.
The path can be change from default (current directory) to any other directory or server path using variable publicSQL.tablePath.

Version Number:
In the above mentioned sample we use Version 0.9:
/* Portable Table Format 0.9 */
The first official Version Number will be set to 1.0 which should be used upon availability of the first official version of PublicSQL.
This is a requirement for future changes and probable expanding development. Our plan is to keep the table format stable over a long period of time.