Haftpflicht Blogger
Virtual Server von Host Europe

SQLite Datatypes und Type Affinity

| 29. Dezember 2013 | Ein Kommentar

SQLite LogoFür jemanden wie mich, der seit jahrzehnten mit relationalen Datenbanken wie Oracle oder MySQL arbeitet, sind die Storage Classes und Datatypes von SQLite etwas gewöhnungsbedürftig.

Die festen Datentypzuweisungen wie VARCHAR(28), TIMESTAMP, DOUBLE, … etc. verlieren ihre Bedeutung.  In SQLite kann man bis auf die Ausnahme der INTEGER-PRIMARY-KEY Spalte, in jedes Datenbankfeld alles abspeichern. Das nennt sich dann "Type Affinity". Ein Abspeichern eines Textes in ein als INTEGER definierte Tabellenspalte ist damit kein Problem!

Das Konzept von SQLite beruht mehr auf Speicherklassen (storage classes: NULL, INTEGER, REAL, TEXT, BLOB) als auf Datentypen.

Man muss sich das in etwa so vorstellen. Ich möchte den String "Dies ist ein Test" in die Datenbanktabelle abcEntity abspeichern.  Bei einer "normalen" SQL-Datenbank ist es hierfür erforderlich, dass die Spalte (column) hierfür beim CREATE-Statement mit dem Datentyp TEXT (oder ähnlichem wie VARCHAR(xx), …) definiert wurde.  
Bei SQLite ist die Eigenschaft, dass es sich bei "Dies ist ein Test" um ein Textfeld handelt, eine innere Eigenschaft des Wertes selbst. Die Abspeicherung des Wertes kann aber in einem Tabellenfeld erfolgen, welches mit der Speicherklasse INTEGER definiert wurde!

Zugegeben etwas verwirrend und wie gesagt, gewöhnungsbedürftig für Programmierer, die andere SQL-Datenbank Engines gewöhnt sind.

 

Mein Best-Practice Vorgehen bei Tabellenfelddeklarationen

Was mache ich jetzt mit meinem CREATE-Statement, welches TEXT, VARCHAR(xx), BOOLEAN, DATE, FLOAT, … Datentypen enthält?  Letztendlich könnte man doch (Ausnahme das Primary-Key Feld) dann alle Felder einfach als TEXT definieren.

Ich würde als Best-Practice einen Mittelweg beschreiten.

  • Zum einen möchte ich die wichtige Information, dass das Feld einen BOOLEAN-Wert, ein DATE, einen INTEGER (Ganzzahlenwert) oder eine Fließkommazahl (FLOAT, DOUBLE) enthält, nicht unter den Tisch fallen lassen.
  • Zum anderen jedoch kann man sich eben auf das wesentliche konzentrieren. Es spielt keine Rolle mehr, wieviel Zeichen maximal in das Feld passen (VARCHAR(33) und wie groß der Zahlenwert werden kann (TINYINT, INTEGER, LONG).
    Damit kann man sich auf die Verwendung der folgenden Datentypbezeichner im CREATE-Statement beschränken:

    • TEXT für CHAR, CHARACTER(20), TEXT, VARCHAR(255), CLOB, …
    • DATE für DATE, DATETIME, TIMESTAMP, …
    • FLOAT für FLOAT, REAL, DOUBLE, …
    • INTEGER für INT, INTEGER, SMALLINT, MEDIUMINT, BIGINT, TINYINT, …
    • BLOB für alle unspezifizierbaren Rohdatenformate (anstatt CLOB den Datentyp TEXT, s.o.)
    • BOOLEAN für Wahrheitswerte
       

Phantasie-Datentyp

In der Dokumentation von SQLite bin ich im Kapitel 2.2 "Affinity Names Examples" (siehe [3]) auf das Regelwerk gestoßen, nach welchem SQLite die Datentypen, die im CREATE Statement angegeben werden, auf die internen SQLite storage classes (type affinity) abbildet.

Das Geschriebene hat mich auf eine interessante Idee gebracht. Nach diesem Regelwerk müßte es möglich sein als Datentyp für ein Feld

a) überhaupt nichts als Datentyp anzugeben

b) einen Phantasie-Datentyp wie "MEINEGROSSMUTTER" zu verwenden.

Und siehe da, ein SQLite Test hat dies bestätigt ­čÖé .  Dem dort angegebenen Regelwerk zufolge wird der Fall a), keine Datentypangabe auf NONE gemapped und der Fall b), die Großmutter auf NUMERIC.

Ein SQLite-Test zeigt, Werte lasen sich in diesen, obskuren Feld abspeichern und auch wieder auslesen :-). 

SQLite ist hier sehr nachsichtig. Das klingt zunächst recht gut, kann doch aber auch ein Fallstrick sein.  Was ist, wenn jemand zwar keinen Phantasie-Datentyp verwendet, sondern sich einfach nur verschreibt. Also z.B. anstatt TEXT als Datentyp TXT schreibt.  Dann wird TXT der storage class NUMERIC zugeordnet. Das ist zwar fehlertorerant, aber sicher nicht ganz im Sinne des Programmierers.

 

Magie der Feldtypabspeicherung

Die Eingangs erwähnte Magie von SQLite, dass also der Datentyp nicht ein fixer Bestandteil der Tabellendefinition (CREATE-Statement) ist, sondern der Datentyp quasi mit dem Feld abgespeichert wird, hat mich noch etwas in der Dokumentation recherchieren lassen. Die Technik dahinter ist recht komplex und ein Detailwissen (SQLite arbeitet mit B-Trees und speziellen Record Formats für Felder), das für einen Android-Programmierer eigentlich zweitrangig ist. Wer sich damit tiefer beschäftigen möchte, findet mehr Informationen darüber auf der Internetseite SQLite-Record Format  [4]. 

 

Speicherplatzbedarf von Datentypen

Als letztes in diesem Blogartikel über SQLite-Datentypen möchte ich noch auf die SQLite Dokumentationseite über die "Limits In SQLite [5]" hinweisen.

Dort findet man solche Informationen wie, über den Speicherplatzbedarf von Datentypen

  • INTEGER immer als signed 64-bit Wert abspeichert und Optimierungen bei kleineren Werten werden von SQLite selbst intern vorgenommen
  • REAL Werte werden als 64-bit abgespeichert und
  • TEXT und BLOB haben eine Default-Größe von 1-Billion (s.a. preprocessor macro SQLITE_MAX_LENGTH [5])

oder über die maximale Datenbankgröße (ca. 140 Terrybytes)

oder über die maximale Anzahl von Tabellenspalten ( default = 2000)

oder über die maximale Länge eines SQL-Statements (ca. 1 Mio Bytes).

 

Ich wünsche Euch noch viel Spaß bei Eurer Entdeckungstour der SQLite Dokumention.

Manfred

 

Quellen:

[1] SQLite: Datatypes In SQLite Version 3

[2] SQLite: Date And Time Functions

[3] SQLite: Affinity Names Examples,
                 Offizielle SQLite Regeln und Beispiele für die Typzuordnung z.B. VARCHAR(20) zu TEXT

[4] SQLite: Record Format

[5] SQLite: Limits In SQLite

 

Stichworte: , , ,

Kategorie: Android, SQL

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht ver├Âffentlicht. Erforderliche Felder sind mit * markiert.