SQLite-gegevens­typen: een nadere blik op opslagklassen

SQLite gebruikt een flexibel typesysteem gebaseerd op opslagklassen en type-affiniteit. In tegenstelling tot traditionele RDBMS'en die strikte types afdwingen, staat SQLite een dynamischere omgang met types toe. Inzicht in dit model is belangrijk om gegevensconsistentie te waarborgen en subtiele bugs in applicatielogica te voorkomen.

In dit artikel verken ik het typesysteem van SQLite en laat ik je zien hoe je met gangbare gegevens­typen in SQLite werkt. Ben je nieuw met SQL, begin dan met onze cursus Introduction to SQL om een solide basis op te bouwen. Ook vind ik de SQL Basics Cheat Sheet, die je kunt downloaden, een handig naslagwerk omdat die alle meestgebruikte SQL-functies bevat.

Kernconcepten van het typesysteem van SQLite

Om met SQLite te werken, is het belangrijk te begrijpen hoe het intern gegevens opslaat en interpreteert. Hieronder leg ik uit hoe je met het SQLite-typesysteem betrouwbare en efficiënte applicaties ontwerpt.

Uitleg van opslagklassen

SQLite gebruikt vijf opslagklassen om waarden intern weer te geven:

• NULL staat voor een ontbrekende of ongedefinieerde waarde.

• INTEGER staat voor een geheel getal met teken, opgeslagen in 1 tot 8 bytes, afhankelijk van de grootte.

• REAL beschrijft een drijvendekommagetal opgeslagen als een 8-byte IEEE-754-waarde.

• TEXT slaat tekststrings op, gecodeerd in UTF-8, UTF-16BE of UTF-16LE.

• BLOB slaat binaire gegevens exact op zoals ingevoerd, zonder transformatie.

SQLite-gegevens­typen. Beeldbron: OpenAI

Deze opslagklassen verschillen van de gedeclareerde gegevens­typen in een tabelschema. Een kolom kan worden gedeclareerd als VARCHAR(50) of BOOLEAN, maar de daadwerkelijke waarde wordt opgeslagen met een van de vijf opslagklassen op basis van de inhoud. Deze scheiding geeft SQLite zijn dynamische en vergevingsgezinde karakter, maar vereist ook dat ontwikkelaars aandacht besteden aan gegevensvalidatie en typeconsistentie in hun applicatiecode.

Type-affiniteit en kolomgedrag

Type-affiniteit in SQLite is een aanbevolen type dat aan een kolom wordt toegekend op basis van het gedeclareerde gegevens­type. SQLite gebruikt de volgende regels om de affiniteit te bepalen op basis van de tekst van het gedeclareerde type.

• Kolommen gedeclareerd als VARCHAR, CHAR of TEXT krijgen TEXT-affiniteit.

• Typen als INT en INTEGER krijgen INTEGER-affiniteit.

• Declaraties zoals DECIMAL, NUMERIC of BOOLEAN krijgen NUMERIC-affiniteit.

• Typen als REAL, FLOAT of DOUBLE krijgen REAL-affiniteit.

• Als er geen herkend type is gedeclareerd, is de affiniteit standaard BLOB.

Een kolom die bijvoorbeeld is gedeclareerd als VARCHAR(100) heeft TEXT-affiniteit, wat betekent dat SQLite bij opslag zal proberen waarden naar tekst te converteren. Deze affiniteit stuurt hoe SQLite gegevens coerct en opslaat, maar beperkt niet dat er andere typen in die kolom worden opgeslagen.

Manifest typing en de implicaties

SQLite gebruikt manifest typing, wat betekent dat het gegevens­type is gekoppeld aan de individuele waarde en niet aan de kolomcontainer. Hierdoor kunnen meerdere typen waarden naast elkaar bestaan in één kolom. Zo kan een kolom die is gedeclareerd als INTEGER ook TEXT- of BLOB-waarden bevatten.

Manifest typing stelt ontwikkelaars in staat diverse gegevens­typen in te voegen zonder het schema te wijzigen. Het nadeel is echter dat dit kan leiden tot inconsistente gegevens, onvoorspelbare sortering en meer afhankelijkheid van applicatielogica om typerregels af te dwingen.

Ik raad onze cursus Database Design aan om databasebeheer te leren, waaronder het organiseren van gegevens in verschillende gegevens­typen en het beheren van databases.

Werken met gangbare gegevens­typen in SQLite

Laten we nu bekijken hoe je met gangbare gegevens­typen in SQLite omgaat, zoals tekst, getallen, booleans, datums en binaire gegevens.

Tekst, getallen en booleans

Veelgebruikte gegevensdeclaraties in SQLite zijn TEXT, INTEGER, REAL, NUMERIC en BOOLEAN, maar waarden worden opgeslagen op basis van type-affiniteit, niet op strikte types. Hieronder staan gebruiksscenario's voor elk gegevens­type in een schema uit de praktijk.

• TEXT slaat strings op, geschikt voor namen, e-mails, URL's en algemene tekstuele gegevens.

• INTEGER is ideaal voor gehele getallen zoals aantallen, ID's en codes, met efficiënte opslag en sortering.

• REAL slaat drijvendekommagetallen op voor metingen, valuta of niet-gehele waarden.

• De NUMERIC-affiniteit dekt waarden die zijn gedeclareerd als DECIMAL, BOOLEAN of vergelijkbaar. Afhankelijk van de waarde probeert SQLite deze op te slaan als INTEGER of REAL.

• BOOLEAN is geen native opslagklasse maar wordt gesimuleerd door 0 (FALSE) of 1 (TRUE) als INTEGER-waarden op te slaan.

Ik raad aan INTEGER te gebruiken voor booleans met expliciete constraints aan de applicatiekant, terwijl NUMERIC geschikt is voor waarden die flexibele getalparsing vereisen.

Datums, tijden en temporele strategieën

SQLite heeft geen native DATE- of DATETIME-type, maar ondersteunt de volgende formaten voor het opslaan van temporele gegevens:

• TEXT: Slaat datums/tijden op als ISO8601-strings zoals YYYY-MM-DD HH:MM:SS. Dit formaat is leesbaar voor mensen en compatibel met veel tools, maar kan trager zijn bij vergelijkingen.

• REAL: Slaat datums op als Juliaanse daggetallen (floating-point), handig voor datumaritmetiek en bereikqueries, maar minder leesbaar.

• INTEGER: Slaat Unix-tijdstempels op, seconden sinds 1970-01-01. Dit is efficiënt qua opslag en snel voor numerieke vergelijkingen, maar niet leesbaar voor mensen.

Bij het werken met temporele gegevens kun je deze functies gebruiken: date(), time(), datetime(), julianday() en strftime() voor het parsen, formatteren en converteren tussen temporele formaten.

Binaire en numerieke randgevallen

SQLite ondersteunt ook binaire gegevens via de opslagklasse BLOB. BLOB's zijn handig voor het opslaan van afbeeldingen, bestanden of andere niet-tekstuele gegevens. Gebruik bij het werken met BLOB-gegevens­typen altijd geparameteriseerde queries of bindings om BLOB's in te voegen. Je kunt grote binaries ook extern opslaan en ernaar verwijzen met paden of hashes.

Hoewel SQLite gehele getallen tot 8 bytes kan opslaan (-2^63 tot 2^63-1), kunnen waarden buiten dit bereik stilzwijgend worden geconverteerd naar het REAL-gegevenstype. Hierdoor kunnen waarden precisie verliezen. SQLite dwingt geen strikte decimale precisie af, dus gebruik voor financiële gegevens wanneer je precisie nodig hebt altijd externe libraries of applicatielogica.

Structuur afdwingen met STRICT-tabellen

In tegenstelling tot het traditionele flexibele typesysteem introduceerde SQLite STRICT-tabellen in versie 3.37.0. Een STRICT-tabel biedt een strikter typemodus die exacte type-overeenstemming voor kolomwaarden afdwingt. Wanneer je een tabel als STRICT declareert, moet elke ingevoegde waarde overeenkomen met het gedeclareerde type van de kolom, en elke mismatch resulteert in een fout. 

De STRICT-modus verbetert gegevensconsistentie en validatie. Het helpt ook typegerelateerde fouten vroeg in de ontwikkeling te onderscheppen en maakt het gedrag van SQLite voorspelbaarder, dichter bij traditionele RDBMS'en.

Voordat je als ontwikkelaar STRICT gebruikt, moet je weten dat het alleen beschikbaar is in SQLite 3.37.0 en hoger. Ook kun je STRICT en manifest typing niet in dezelfde tabel combineren.

Prestaties en ontwerpoverwegingen

SQLite heeft een flexibel typesysteem met prestatie-implicaties. SQLite gebruikt variabele-lengtecoder­ing voor INTEGER- en REAL-waarden, wat betekent dat kleinere getallen minder bytes gebruiken. Dit helpt de databasegrootte te minimaliseren, vooral bij grote datasets. 

Type-affiniteit beïnvloedt ook hoe waarden worden opgeslagen en vergeleken. Inconsistente types in een kolom kunnen de effectiviteit van indexen verminderen, omdat SQLite tijdens opzoekacties extra typeconversies kan uitvoeren, waardoor queries trager worden.

Overweeg de volgende strategieën om efficiënte schema's en queries te ontwerpen.

• Definieer kolommen met duidelijke en consistente affiniteiten om overhead door typecoercie te minimaliseren.

• Overweeg expliciete casting in queries wanneer typeconversie nodig is om consistent gedrag te garanderen.

• Gebruik INTEGER-affiniteit voor numerieke ID's en tellers, REAL voor drijvendekommag­egevens en TEXT voor strings.

• Vermijd het mixen van gegevens­typen in dezelfde kolom om eigenaardigheden bij sorteren en vergelijken te voorkomen.

• Normaliseer schema's om overbodige typecoercie of gegevensduplicatie te voorkomen.

• Gebruik EXPLAIN QUERY PLAN om prestaties te analyseren en queries te fine-tunen.

Voorbeelden van SQLite-gegevens­typen met schemapatronen

De volgende tabelschema's illustreren het doordachte gebruik van de gegevens­typen, type-affiniteit en constraints van SQLite.

Users-tabel met booleansimulatie en constraints

In het onderstaande voorbeeld:

• is_active simuleert een boolean met INTEGER en een CHECK-constraint.

• TEXT zorgt voor consistentie bij stringwaarden, terwijl INTEGER dient voor boolean-achtige logica.

 -- Create users table with auto-incrementing rowid and enforce uniqueness
CREATE TABLE users (
    id INTEGER PRIMARY KEY, 
    username TEXT NOT NULL UNIQUE, 
    email TEXT NOT NULL, 
    is_active INTEGER NOT NULL DEFAULT 1 CHECK (is_active IN (0, 1))  -- Simulates boolean
);Mogelijk gemaakt door

Orders-tabel met temporele gegevens en numerieke precisie

In de onderstaande query gebruikt total NUMERIC voor valuta-achtige waarden zodat de precisie aan de applicatiekant wordt afgehandeld. order_date wordt opgeslagen als TEXT in ISO-8601-formaat, wat leesbaarheid en compatibiliteit met datumfuncties waarborgt.

-- Create orders table with row identifier and foreign key in practice
-- ISO date format "YYYY-MM-DD"
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,  
    customer_id INTEGER NOT NULL, 
    total NUMERIC NOT NULL, 
    order_date TEXT NOT NULL CHECK (length(order_date) = 10)  );Mogelijk gemaakt door

Files-tabel met binaire gegevens en typecontroles

In de onderstaande query gebruikt content BLOB om ruwe binaire gegevens op te slaan. uploaded_at slaat tijd op als een INTEGER omwille van ruimte- en prestatie-efficiëntie.

-- Create files table with raw binary data and Unix timestamp (epoch seconds)
CREATE TABLE files (
    file_id INTEGER PRIMARY KEY, 
    name TEXT NOT NULL, 
    content BLOB NOT NULL,  
    uploaded_at INTEGER NOT NULL 
);Mogelijk gemaakt door

Je kunt het type op runtime verifiëren met de volgende query:

SELECT typeof(uploaded_at), typeof(content) FROM files;Mogelijk gemaakt door

Ik raad onze cursus Introduction to Relational Databases in SQL aan om meer te leren over datastructuren en hoe je tabellen in databases maakt.

SQLite vergelijken met andere databases

Het typesysteem van SQLite is flexibeler dan dat van traditionele RDBMS'en zoals MySQL of PostgreSQL. De onderstaande tabel vergelijkt SQLite met deze databases.

Conclusie

SQLite heeft een flexibel typesysteem dat verschillende voordelen biedt wanneer het doordacht wordt gebruikt. Door opslagklassen, type-affiniteit en manifest typing te begrijpen, kunnen ontwikkelaars schema's ontwerpen die flexibiliteit en betrouwbaarheid in balans brengen. Functies zoals STRICT-tabellen, consistent typegebruik en normalisatie helpen structuur af te dwingen waar dat nodig is.

Als volgende stap raad ik de cursussen Exploratory Data Analysis in SQL en Data Manipulation in SQL aan om te leren hoe je gegevens van verschillende gegevens­typen analyseert met geavanceerde SQL-queries.