PublicSQL ist eine SQL-Variante für Javascript. Es werden SQL-Befehle zur Verfügung gestellt um Datenbankabfragen auf vorhandene Tabellen durchzuführen. Die Tabellen müssen im "Portable Table Format" vorliegen - ein spezielles Format das am bekannten CSV-Format angelehnt ist. Da Javascript keine Dateien speichern kann sind nur Abfragen möglich - nicht das Anlegen oder Ändern von Tabellen. Unterstützt wird vor allem die SELECT-Anweisung.
PublicSQL ist Open-Source und steht unter der MIT-Lizenz. Die deutsche Übersetzung ist bei wikipedia.de zu finden.
Dieses kleine Tutorial führt Sie in die Arbeitsweise von PublicSQL ein. Erstellen Sie für dieses Tutorial am besten ein neues Verzeichnis z. B. mit dem Namen "geoabfrage" in das Sie die Datei "publicsql.js" kopieren.
Alle Funktionen und Variablen in PublicSQL beginnen mit publicSQL. Wir haben in diesem Beispiel die Funktionen publicSQL.query() und publicSQL.show() verwendet.
Mit publicSQL.query starten Sie eine Abfrage. Die Funktion hat 2 Parameter:
Der 1. Parameter enthält den Abfragestring.
Der 2. Parameter enthält den Namen einer Funktion, die das Abfrageergebnis weiterverarbeitet - in diesem Fall die Funktion "publicSQL.show".
Das Ergebnis der Abfrage wird automatisch als 2-dimensionalles Array an diese Funktion übergeben. Die Funktion "publicSQL.show()" erstellt automatische eine Tabelle mit dem Ergebnis der Abfrage.
Die Felder der Tabelle laender sind über das Feld kontid mit der Tabelle kontinente verbunden.
In der Abfrage werden mit "SELECT kontinente.Kontinent, laender.Land, laender.Einwohner" die Felder für die Anzeige ausgewählt. Mit "FROM laender, kontinente" werden die relevanten Tabellen angegeben und der WHERE-Teil "kontinente.id = laender.kontid" verbindet die beiden Tabellen miteinander.
Damit die Tabellen erst nach dem vollständigen Laden der Seite geladen werden haben wir die Abfrage in die Funktion showTab() geschrieben. Diese wird über das onload-Ereignis ausgeführt sobald die Seite vollständig geladen wurde.
Anzeigebeispiel: So sieht's aus
Wir haben ein einfaches Formular erstellt mit einem Textfeld und einem Button. Beim Anklicken des Buttons wird mit der Anweisung onClick="suchen()" die Funktion suchen() augerufen.
In der Funktion suchen() wird in der Variablen s die Select-Anweisung zusammengebaut. Dabei wird im WHERE-Teil abgefragt, ob das Wort im Suchfeld in einem der Felder kontinente.Kontinent oder laender.Land vorkommt. Auf den Text im Suchfeld greifen wir mit document.form1.suchText.value zu.
Beachten Sie, dass die Anführungszeichen mit einem Backslash maskiert wurden. In diesem Fall könnte man das vermeiden indem man entweder bei der Abfrage oder zur Abgrenzung des Javascript-Strings einfache Anführungszeichen benutzt: s = s + " OR laender.Land = ' " + document.form1.suchText.value + "')";
<script language="javascript">
function abfrage() {
publicSQL.query(document.form1.queryText.value, "publicSQL.show");
}
</script>
Schreiben Sie im <body>-Bereich die folgenden Zeilen:
<form action="" name="form1">
<input name="queryText" type="text" id="queryText" size="120">
<input name="queryButton" type="button" id="queryButton" onClick="abfrage()" value="Abfrage">
</form>
Speichern Sie die Datei unter dem Namen "geo4.htm" im selben Verzeichnis.
Anzeigebeispiel: So sieht's aus
Anzeigebeispiel: So sieht's aus
Sobald die Seite geladen ist wird die Funktion goQuery() ausgeführt. Diese führt die PublicSQL-Abfrage aus. Für die Auswertung wurde bei der Funktion PublicSQL.query() als 2. Parameter die Funktion showGraphic() angegeben.
In der Funktion showGraphic wird in einer Schleife für jede Tabellenzeile ein DIV-Element mit dem Ländernamen und der Einwohnerzahl (gerundet, in Mio.) als Text erstellt. Die Hintergrund-Farbe des DIV-Elements wird abhängig vom Schleifenzähler errechnet so dass ein Farbverlauf entsteht. Die Breite der DIV-Elemente wird aus der Einwohnerzahl errechnet. Die DIV-Elemente werden an das <p>-Elemtent mit der ID "Chart" angehängt.
Um PublicSQL in HTML-Seiten einzubinden muss die Datei "publicsql.js" im HEAD-Bereich eingebunden werden:
<script type="text/javascript" src="publicsql.js"></script>
anschließend kann folgendermaßen auf die Tabellen zugegriffen werden (Beispiel):
publicSQL.query("select * from adressen", "anzeige");
Der 1. Parameter ist ein String mit der SQL-Abfrage, der 2. Paremeter ist der Name der Funktion die anschließend aufgerufen wird. Wichtig: Nach dem Aufruf sollte kein Quelltext mehr erfolgen, vielmehr sollte das Script nach Ausführen der Abfrage durch die Funktion im 2. Parameter weiter aufgeführt werden.
Der 1. Parameter enthält die SELECT-Anweisung. Diese unterstützt zur Zeit eine Untermenge aus dem SQL92-Standard.
Unterstützte Elemente der SELECT-Anweisung: SELECT, AS, DISTINCT, FROM, WHERE
Strings sollten in Anführungszeichen gesetzt werden. Dies ist auf jeden Fall nötig, wenn diese Leerzeichen oder andere Zeichen(-folgen) enthalten, die auch innerhalb der SELECT-Anweisung eine Bedeutung haben. Es werden einfache (') und doppelte (") Anführungszeichen sowie Backticks (`) unterstützt. Maßgeblich ist das erste gefundene Zeichen - es dürfen also innerhalb einer SELECT-Anweisung nicht unterschiedliche Anführungszeichen zur Eingrenzung von Strings gemischt werden.
Kommen die Anführungszeichen, die zur Eingrenzung eines Strings verwendet werden innerhalb dieses Strings vor, müssen diese durch einen Backslash maskiert werden.
Das Gleiche gilt für den Backslash selbst. Beispiel:
SELECT Bezeichnung FROM artikel WHERE Bezeichnung = "ganz \"toller\" Artikel zum halben Preis"
Wird die SQL-Anweisung als String direkt an eine Variable übergeben, müssen die in Javascript als Begrenzer verwendeten Anführungszeichen innerhalb des Strings ebenfalls maskiert werden.
Beispiel:
meinsql = "SELECT Name, Bundesland, Ort FROM kunden WHERE Name <> \"Meier\" AND Ort = \"Hannover\" OR Bundesland <> \"Niedersachsen\"";
Auch das Backslash-Zeichen muss natürlich gegebenenfalls maskiert werden, da es ja eine Sonderfunktion hat.
Es bietet sich an, unterschiedliche Anführungszeichen zu verwenden.
Beispiel:
meinsql = 'SELECT Name, Bundesland, Ort FROM kunden WHERE Name <> "Meier" AND Ort = "Hannover" OR Bundesland <> "Niedersachsen"';
Der 2. Parameter enthält den Namen der Funktion, die für die weitere Verarbeitung zuständig ist. Diese Funktion erhält als Parameter ein 2-dimensionalles Array mit den Zeilen und Spalten des Abfrage-Ergebnisses. Die folgende Funktion gibt beispielsweise das Ergebnis in einer vorhandenen Tabelle mit der id "mytable" aus:
function anzeige(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);
}
}
}
Der HTML-Quelltext für die Tabelle:
<table id="mytable">
<tr>
<td>Tabellen-Inhalt vorher</td>
</tr>
</table>
Sie können als 2. Parameter auch die PublicSQL-Funktion publicSQL.show() angeben.
Das Array porTables enthält alle bereits geladenen Tabellen. Das zweidimensionale Array hat den Aufbau:
porTables[Tabellen-Nummer][Tabellen-Inhalt]
Über Tabellen-Nummer erfolgt der Zugriff auf die gewünschte Tabelle. Der Tabellen-Inhalt stellt wiederum ein Array dar. Der Aufbau des Tabellen-Inhalts wird weiter unten beim "Portable Table Format" beschrieben.
Array mit den Tabellen-Nummern aller vorhandener Tabellen. Der Zugriff erfolgt über den Tabellenname. Das Ergebnis ist der Index für das Array porTables.
Mit der Anweisung porTables[publicSQL.tableNames["kunden"]] greift man beispielsweise auf die Tabelle kunden zu.
String mit der Dateiendung für PublicSQL-Tabellen. Voreinstellung ist "ptf" für "Portable Table Format".
Der Wert braucht normalerweise nicht geändert zu werden. Wird dieser Wert geändert, darf der Tabellenname ohne Dateiendung noch nicht vorhanden sein. publicSQL identifiziert die Tabellen intern nur nach den Tabellennamen ohne Dateiendung.
String mit Standard-ID für die Funktion show(). Wird in der Funktion show() keine Tabellen-ID angegeben verwendet show() für die Ausgabe die Tabelle mit dieser ID. Existiert diese noch nicht wird Sie erzeugt.
String der bei der Ausgabe mit show() für NULL-Werte ausgegeben wird.
Voreinstellung ist '-'.
Führt ein SQL-Abfrage aus.
Der 1. Parameter enthält den SQL-String für die Abfrage.
Der 2. Parameter enthält den Namen der Funktion, die anschließend aufgerufen werden soll. Diese aufzurufende Funktion erhält als ersten Parameter das Abfrageergebnis als 2-dimensionalles Array.
publicSQL.query("SELECT Name, Adresse FROM kunden ", "publicSQL.show");
publicSQL.query("SELECT k.Name, a.Auftragsnummer FROM kunden k, auftrag a WHERE k.ID = a.KundenID", "publicSQL.show");
publicSQL.query("SELECT Name FROM kunden WHERE NOT Name = 'Meier'", "anzeige");
In den ersten beiden Beispielen wird zur Ausgabe der Name der PublicSQL-Funktion show() übergeben. Im letzten Beispiel wird der Name der selbstgeschriebenen Funktion anzeige() übergeben.
Die (gekürzte) Syntax für die SQL-Abfrage lautet:
SELECT [DISTINCT] Auswahl-Liste
FROM Tabellen-Liste
[WHERE Suchbedingung]
Auswahl-Liste ::=
*
| Spalte [ { , Spalte }... ]
Spalte ::= Spalten-Element [ [AS] Spalten-Alias]
Spalten-Element ::=
Spalten-Name | Spalten-Name.Tabelle | Spalten-Name.Tabellen-Alias
Tabellen-Liste ::= Tabellen-Element [ { , Tabellen-Element }... ]
Tabellen-Element ::=
Tabelle | Tabelle Tabellen-Alias
Suchbedingung ::= Such-Ausdruck | Such-Ausdruck [ AND | OR ] Suchbedingung
Such-Ausdruck ::= [Linke-Klammern] [NOT] [Linke-Klammern] Wert Vergleichs-Operator Wert [Rechte-Klammern]
Wert ::= Zeichenkette | Zahl | Spalte
Vergleichs-Operator ::= = | <> | > | < | >= | <=
Zeichenkette ::= "[ { Zeichen }... ]"
Zahl ::= Ziffer [ { Ziffer }... ] [ . { Ziffer }... ]
Das Abfrageergebnis enthält in Zeile 0 ein Array mit den Namen der Spalten. Die folgenden Zeilen enthalten ein Array mit den jeweiligen Datensatz. Wird das Array mit dem Abfrageergebnis an die Variable t übergeben, kann folgendermaßen darauf zugegriffen werden:
t[0] = Array mit alle Spalten-Namen
t[1] = Array mit den Feld-Werten der 1. Zeile
t[2] = Array mit den Feld-Werten der 2. Zeile
...
t[t.length] = Array mit den Feld-Werten der letzten Zeile
t[0][0] = Name der 1. Spalte
t[0][1] = Name der 2. Spalte
...
t[0][t[0].length-1] = Name der letzten Spalte
t[1][0] = Wert der 1. Zeile und 1. Spalte
t[1][1] = Wert der 1. Zeile und 2. Spalte
...
t[3][6] = Wert der 3. Zeile und 7. Spalte
...
t[t.length-1][t[0].length-1] = Wert der letzten Zeile und letzten Spalte
Zeigt das Abfrageergebnis in Tabellenform an.
Der 1. Parameter enthält das Array mit dem Abfrageergebnis.
Der 2. Parameter enthält entweder einen String mit der ID des <table>-Elements oder den Index der Tabelle (0 für die erste Tabelle, 1 für die 2. Tabelle, usw.) oder das Tabellenelement als Object. Der Parameter ist optional. Wird kein Wert übergeben erstellt die Funktion eine neue Tabelle mit der in publicSQL.htmlStandardTable enthaltenen <table>-ID. Existiert bereits eine Tabelle mit dieser ID, wird diese geleert und für die Anzeige genutzt.
löscht den Inhalt einer Tabelle.
Der Parameter enthält entweder einen String mit der ID des <table>-Elements oder den Index der Tabelle (0 für die erste Tabelle, 1 für die 2. Tabelle, usw.) oder das Tabellenelement als Object.
Mit der Funktion arraySort kann das Abfrageergebnis sortiert werden.
Der 1. Parameter enthält das zu sortierende Abfrageergebnis.
Der 2. Parameter enthält die Spalte-Nummer der zu sortierenden Spalte oder ein Array mit den Spaltennummern der zu sortierenden Spalten.
Der 3. Parameter enthält den Boolean-Wert true wenn aufsteigend sortiert werden soll oder false für absteigende Sortierung oder ein Array mit Boolean-Werten das die Sortierrichtung für die im 2. Parameter übergebenen Spalten enthält. Der Parameter kann auch weggelassen werden, dann wird aufsteigend sortiert.
Bei den nachfolgenden Beispielen steht das Array mit dem Abfrageergebnis in der Variablen t:
aufsteigende Sortierung
nach Spalte 1:
publicSQL.arraySort(t, 1);
aufsteigende Sortierung für die Spalte 2, bei gleichen Werten wird weiter nach Spalte 0 sortiert:
var cols = new Array(2,0);
publicSQL.arraySort(t, cols);
absteigende Sortierung nach Spalte 2:
publicSQL.arraySort(t, 2, false);
Unterschiedliche Sortierungs-Richtungen für die Spalten 1, 0,2:
var cols = new Array(1, 0, 2);
var asc = new Array(true, false, true);
publicSQL.arraySort(t, cols, asc);
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.
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:
In der aktuellen Version müssen die Tabellen im gleichen Verzeichnis wie die HTML-Seite liegen. 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:
Die Tabelle, die mit rubriken in der SELECT-Anweisung angesprochen wird, heißt z. B. "rubriken.ptf".
Für spätere Versionen ist auch ein Zugriff auf andere Verzeichnisse oder Websites vorgesehen.
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 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 lange Lebensdauer hat und nicht ständig angepaßt werden muss.