PublicSQL

PublicSQL-Documentation

Introduction

PublicSQL is ae sql version which works with Javascript. It allocates sql instruction to query tables. The tables must created as a "Portable Table Format" - a specail format alike the CSV format. Because Javascript can't save files only querys are possible - it's not possible to create tables or change tables in Javascript. So PublicSQL supported mainly the SELECT statement.

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

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

Create an new table. Therefor build an new textfile with the following content (maybe 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);
Create a new HTML-File. Write the following lines inside the head-area to include PublicSQL:

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

Insert the following lines Schreiben inside the <body>-area to execute the sql query:

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

Save the file as "geo1.htm" in the same directory.
Open the file "geo1.htm" into the browser. The result is the content of the continets table. This was your first PublicSQL-Query.

Click here to show this example

Explanation

All functions and variables in PublcSQL begins with 'publicSQL'. In this example we used the functions publicSQL.query() and publicSQL.show().

With publicSQL.query you start a query. This function has 2 arguments:
The 1. argument contains the query string.
The 2. argument 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 asl a two-dimensional array. The function "publicSQL.show()" generates automatically a table with the query result.

Joined tables

Create the following table with the 10 cities with the most population (2008) Therefor build an new textfile 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);
Create a new HTML-File with the following lines inside the head-area:

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

At first insert the following lines inside the <body>-area:

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

Save the file as "geo2a.htm" in the same directory.
Open the file "geo2a.htm" into the browser. The result is the content of the continents-table linked with the countries-table.

Click here to show this example

But it doesn't work with the Internet Explorer. There is a problem because the tables was loaded before the page is loaded ready. It's unreproducible why the first example ("Simple Query"") works. There are two ways to solve the problem:

Alternative 1

The tables will be loaed directly.
Write the following lines inside the head-area 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".
Now it shows correct by the Internet-Explorrer too. In publicSQL.tableNames you find the indices of the tables.These otherwise was created automatically. If the tables will be loaded direct we must set the indices manual. The first table has index 0, the second index 1 - and so on.

Click here to show this example

Alternative 2

We can wait until the page is loaded before we run the query.
Delete the source where the tables are loaded directly (Alternative 1) from the <head>-area. Write the following Lines after the Line which included 'publicsql.js' :

<script language="javascript">
function showTab() {
publicSQL.query("SELECT continents.Continent, countries.Country, countries.Population AS 'Million People' FROM countries, continents WHERE continents.id = countries.contid", "publicSQL.show");
}
</script>
Delete the lines from the <body>-area. Insert an onload-property to the <body>-tag:

<body onLoad="showTab()">

Save the file as "geo2c.htm".
Now it shows correct by the Internet-Explorer too.

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 kontinents

The fields will selected by "SELECT continents.Continent, countries.Country, countries.Population". The relevat talbes will selected by "FROM laender, kontinente" and the tables will joined by the WHERE-condition "kontinente.id = laender.kontid".
The query is included in the function showTab() because the tables must be loaded after the page was loaded. Therefore these function was execute by the onload-event.

Search function

Create a new HTML-File with the following lines inside the <head>-area:

<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>-area:

<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 directory.
Open the file "geo3.htm" into the 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 include a text field and a button. By click the button the search function was started: onClick="searchText()".

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

Please note that escape sequences are used to define quotes. To work without escape sequences use different 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

Create a new HTML-File with the following lines inside the <head>-area:

<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>-area:

<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".
Open the file "geo4.htm" into the 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

Click here to show this example

Create a bar chart

Create a new HTML-File with the following lines inside the <head>-area:

<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>
Insert an onload-property to the <body>-tag:

<body onLoad="goQuery()">
Write the following Lines inside the <body>-area:

<p id="Chart"></p>
Save the file as "geo5.htm" into the same directory.
Open the file "geo5.htm" into the 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 was called. These 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). The background color for the DIV elements was builded depends on the loop counter. The result is a color gradient. The width of the DIV elements was calculated by the populaton. The DIV elements will be attached by the <p> element with the ID "Chart".

Practice

To include PublicSQL in HTML pages write the following lines into the <head>-area:
<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 was called after the query executed. Note: Don't write source after the query request - the script next run with the source of the functiion from the 2.parameter.

First parameter

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

SELECT
after SELECT the fieldname alternatively some comma seperated fieldnames. Exemple:
SELECT Name, Adress FROM customers
Aadditionally if there are several tables the table name must given seperated by a point. example:
SELECT customers.Name, orders.Number FROM customers, orders WHERE customers.ID = orders.CustomerID
In place of the table name you can use an alias for the table name. Examle:
SELECT c.Name, o.Order FROM customers c, orders o WHERE c.ID = o.CustomerID
DISTINCT
Use DISTINCT to avoid duplicate records. The following example select locations with multiple customers once only:
SELECT DISTINCT city FROM customers
AS
Change the field names of the result table with the AS statement. Example:
SELECT Name AS CustomerName FROM customers
AS is optional, you can leave it. Example:
SELECT Name CustomerName FROM customers

FROM
The FROM statement includes all tables which was required for the query. Example:
SELECT customers.Name, orders.Number FROM customers, orders
You can use an alias for the table names. Example:
SELECT c.Name, o.Number FROM customers c, orders o WHERE c.ID = o.CustomerID
WHERE
With the WHERE statement -Klausel the result can be restrained. Example:
SELECT Name FROM customers WHERE Name <> "Myers"
PublicSQL supports the followed Unterstützt werden die relational operators: <, >, <>, =, <=, >=. Several terms can be linked with AND alternatively OR. Example:
SELECT Name, State, City FROM customers WHERE Name <> "Myers" AND City = "Nashville" OR State <> "Florida"
Mit NOT kann eine Bedingung negiert werden. Beispiel:
SELECT Name FROM kunden WHERE NOT Name = "Meier"
NOT hat eine höhere Priorität wie AND. AND hat eine höhere Priorität wie OR. Mit Klammern kann man die Reihenfolge der Auswertung ändern. Beispiel:
SELECT Name, Bundesland, Ort FROM kunden WHERE Name <> "Myers" AND (Ort = "Hannover" OR Bundesland <> "Niedersachsen")

Strings should be delimited by quotes. This is nedfull if the strings included spaces or other chars which have a special function. You can use simple quotes ('), double quotes (") or backticks (`). Relevant is the first founded char of these - you can not use differnent qoutes into the self SELECT statement.
It the qoutes which are used for delimeted strings are present into the strings, you must escaped these by a backslash. This also applies for the backslash self. 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 escaped too. Example:
mysql = "SELECT Name, State, City FROM customers WHERE Name <> \"Myers\" AND City = \"Nashville\" OR State <> \"Florida\"";
In this case it's expedient to use differnt quotes. Example:
mysql = 'SELECT Name, State, City FROM customers WHERE Name <> "Myers" AND City = "Nashville" OR State<> "Florida"';

2. Parameter

The second parameter includes the names of the function for the further processing. This function includes an 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":

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.