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.

Modifications in Version 1.1

The following changes was made since Version 1.0:

Querys of more than 2 tables are possible.
The 'ORDER BY' clausel is supported.
The access of the tables outside the actual directory or from other websites is usally by the variable "publicSQL.tablePath".
There are error numbers and error texts are established.
The function publicSQL.show() additionaly displays row numbers.
An indication error by the function publicSQL.show() was corrected.
An error by the function publicSQL.arraySort() was corrected.

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 an onload property to the <head>-area:

<body onLoad="showTab()">
Insert the following lines Schreiben inside the <body>-area to execute the sql query:

<script language="javascript">
function showTab() {
publicSQL.query("select * from continents", "publicSQL.show");
}
</script>
Save the file as "geo1a.htm" in the same directory.
Open the file "geo1a.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

Alternative

The last example works with the onload property. It's also possible to execute the query while the page ist loading, but the internet explorer dosn't work correctly when the query implies more than one table.

Delete the onLoad-property from the <body>-area:

<body>
Write the query without the showTab function in the <body>-area:

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

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().
To load the table after the page was loaded we use the function showTab(). Th function call of it stands on the onload-property of the body tag. If the query use only one table, it's also possible to execute the query by loading the page.

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>
Insert an onload property to the <head>-area:

<body onLoad="showTab()">
Insert the following lines inside the <body>-area:

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

Alternative

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".
Delete the onLoad-property from the <body>-area:

<body>
Write the query without the showTab function in the <body>-area:

<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>
Save the file as "geo2b.htm" in the same directory.
Open the file "geo2b.htm" into the 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 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.

Alternative: 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.

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

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.

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

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"
NOT negates the WHERE condition. Example:
SELECT Name FROM customers WHERE NOT Name = "Myers"
NOT has a higher priority as AND. AND has a higher priority as OR. The order can be changed by brackets. Beispiel:
SELECT Name, State, City FROM customers WHERE Name <> "Myers" AND (City = "Nashville" OR State <> "Florida")
ORDER BY
With the ORDER BY statement the result of the query can be sorted by variable columns. Example:
SELECT * FROM customers ORDER BY Name
Optional a sort direction can be declared. Use ASC (default) for ascending sort and DESC for descending sort. Example for descending sort:
SELECT * FROM customers ORDER BY Name DESC
It's possible to use more than one columnt. If the field values are equal the next column was used. Example:
SELECT * FROM customers ORDER BY Name, City

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"';

Show the query result

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

Query by distant tables

It's possible to access to distant 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's also possible to use tables which are saved on another server, it 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 errortext for it was saved in publicSQL.errorText. If there is no error the publicSQL.errorNumber is 0 and publicSQL.errorText is an empty string.

PublicSQL-Referenz

Variables

porTables

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

publicSQL.tableNames

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.
Maybe porTables[publicSQL.tableNames["customers"]]i is the call to use the table 'customers'.

publicSQL.tableExtension

This string includes the file extension for PublicSQL tables. Default is "ptf" (Portable Table Format).
Usually this value must not modified. It the value was modified table names must not exist with the self name without extension. PublicSQL identifies the tables on the basis of the names without extensions.

publicSQL.tablePath

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

publicSQL.htmlStandardTable

Includes the standard ID for the show() function, If there is no parameter with the table id assigned in the function show(). Isn't there a table with this ID the table was created.

publicSQL.nullValue

Includes the string which was displayed für NULL values. Default is '-'.

publicSQL.errorNumber

10:No SELECT command found
11 Error in SELECT command
12: Table of SELECT command not found
13: Table name doesn't exist in SELECT command. (Required if the query has several tables)
20: No FROM command found
21: Error in FROM command
30: Error in WHERE section
50: Error in ORDER-BY section
51: Table not found in ORDER-BY section.
52: Table name dosn't exist in ORDER-BY section (Required if the query has several tables)

publicSQL.errorText

String with query section in relation to the error number.

Functions

publicSQL.query(qselect, qfunc)

Executes the SQL query.
The first parameter includes the SQL string of the query.
The second parameter includes the name of the function which was called after the query was executed. This function gets 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 uses 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 an array in row 0 with the names of the columns. The follow rows includes an array with one record. The following lines explains the access to the array variable (here t) 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 include 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 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 cleared and used 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

publicSQL.arraySort(arr, col, asc)

This function sorts the query result. It's an internal function which used by the "ORDER BY"-statement. It also can used to sort the query result.
The first parameter contains the query result which should be sorted.
The second parameter contains the column number of the column which should be sorted or an array with the column numbers of the columns which should 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 defined the sort direction of the columns from the second parameter. This parameter is optional - then it should be sorted ascending.

Examples

The followed examples shows some funcion calls. The variable t contains 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 it should be sorted by column 0:
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);

Portable Table Format

This chapter is only available in german language in the moment!

Für die Tabellen in PublicSQL wird das "Portable Table Format" verwendet. Dieses Format orientiert sich am CSV-Format. Da Javascript nur Javascript-Dateien laden kann wurde das Format entsprechend angepasst. Zunächst ein Beispiel:

/* 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);

Die 1. Zeile kennzeichnet das Tabellenformat.
Die 2. Zeile ist immer gleich und darf nicht geändert werden. Hier wird intern ein neues Array erstellt, in dem der Tabelle-Inhalt gespeichert wird.
Die 3. Zeile enthält die Spalten-Namen.
Von der 4. Zeile bis zur vorletzten Zeile befinden sich die eigentlichen Daten der Tabelle. Alle Werte werden durch Komma getrennt. Strings müssen durch einfache oder doppelte Anführungszeichen eingeschlossen werden. Numerische Werte dürfen nicht mit Anführungszeichen begrenzt werden. Wichtig: Auch nach dem letzten Feld in einer Zeile muss noch ein Komma folgen - anders kann Javascript die Tabelle nicht lesen.
Die letzte Zeile enthält die Anzahl Spalten.
Hinweis: Obwohl für PublicSQL die zeilenweise Einteilung nicht nötig ist muss diese auf jeden Fall eingehalten werden. Andernfalls können externe Programme (Editoren, Converter etc.) die Dateien nicht verarbeiten.

Das Portable Table Format unterstützt die Datentypen String, Number, Boolean und undefined.
PublicSQL unterstützt in der aktuellen Version offiziell noch keine Boolean-Werte. Es werden die Datentypen String und Number unterstützt. Datums- und Zeit-Vergleiche können stattfinden, da ein Stringvergleich das gleiche Ergebnis liefert. Felder ohne Inhalt (NULL in SQL) enthalten den Wert null (siehe Beispiel oben). Achtung: null darf nicht in Anführungszeichen eingeschlossen werden und muss klein geschrieben werden!

SQL-Feldtypen in publicSQL-Tabellen:

Zahlen
unterstützt werden die von Javascript unterstützten Zahlen: 8 Byte lange Fließkommazahl von +/-2.2250738585072014*10^-308 bis +/-1.7976931348623157*10³⁰⁸ . Diese entsprechen den SQL-Typen integer, float, decimal und numeric. Zahlen können folgendermassen gespeichert werden:
- Dezimalzahlen. Beispiele: 5 , 4.36 , 3.6E-23
- Oktale Zahlen, werden durch führende "0" gekennzeichnet. Beispiel: 023
- Hexadezimalzahlen, werden durch führendes "0X" bzw. "0x" gekennzeichnet. Beispiel: 0xFF
Strings
Zeichenketten die durch einfache oder doppelte Anführungszeichen begrenzt werden. Anführungszeichen die auch als Begrenzer dienen müssen durch ein vorangestelltes "\" maskiert werden. Beispiel: "Sein Name war \"Meier\""
Zeit und Datum
Felder mit Zeit- und Datumswertenkönnen als String gespeichert werden. Durch die Reihenfolge (Jahr-Monat-Tag-Stunde-Minute-Sekunde) können diese auf übliche Weise verglichen werden. wichtig ist das gegebenenfalls führende Nullen eingefügt werden (nicht "2009-1-26" sondern "2009-01-26"):
- Date (Datum), Format "YYYY-MM-DD",Beispiel:"2009-01-26"
- Time (Zeit), Format "HH:MM:SS", Beispiel: "23:05:33"
- DateTime, Format "YYYY-MM-DD HH:MM:SS", Beispiel::"2009-04-14 11:12:30"

Die Standard-Dateiendung lautet ".ptf". Da die Groß- und Kleinschreibung von Dateinamen nicht von allen Betriebssystemen unterschieden wird sollte auschließlich Kleinschreibung verwendet werden. Auch mit Sonderzeichen im Dateinamen sollte man vorsichtig umgehen und nur solche verwenden, die von allen Betriebssystemen unterstützt werden. Empfohlen wird folgende Syntax:

1. Zeichen = Kleinbuchstabe
Folgezeichen = Kleinbuchstabe, Zahl, Unterstrich oder Bindestrich (Minus).

Die Tabelle, die mit rubriken in der SELECT-Anweisung angesprochen wird, heißt z. B. "rubriken.ptf".
Mit Hilfe der Variablen publicSQL.tablePath kann ein anderes Verzeichnis oder eine andere Website für die Tabellen angegeben werden.

Hinweis:
Im Beispiel oben ist in der ersten Zeile die Versions-Nummer 0.9 angegeben:
/* Portable Table Format 0.9 */
Die erste offizielle Versions-Nummer wird 1.0 sein. Das Portable-Table-Format wurde paralell zu PublicSQL entwickelt. Da bisher noch keiner Erfahrungen mit dem Format gesammelt werden konnten wird zunächst einige Zeit die Version 0.9 benutzt.
Sobald die Version 1.0 des Portable-Table-Formats veröffentlicht wird, sollte diese benutzt werden. Sollten sich noch Änderungenen gegenüber der Version 0.9 ergeben wird selbstverständlich die passende PublicSQL-Version parallel aktualisiert.
Hintergrund: Im Gegensatz zu Programmen ist bei Datei-Formaten eine häufige Aktualisierung ein Nachteil: Alle Programme die das Format benutzen müssen angepasst werden und zwischen den verschiedenen Versionen unterscheiden können. Gleichzeitig existieren viele unterschiedliche Versionen von Dateien des Formats. Daher wird Wert darauf gelegt, dass die erste offizielle Version (1.0) eine möglichst lange Lebensdauer hat und nicht ständig angepaßt werden muss.