Tipi di dati in SQLite: uno sguardo ravvicinato alle classi di archiviazione

SQLite utilizza un sistema di tipi flessibile basato su classi di archiviazione e affinità di tipo. A differenza dei tradizionali RDBMS che impongono un tipaggio rigoroso, SQLite consente una gestione più dinamica dei tipi. Comprendere questo modello è importante per garantire la coerenza dei dati ed evitare bug sottili nella logica applicativa.

In questo articolo, esplorerò il sistema di tipi di SQLite e ti mostrerò come lavorare con i tipi di dati comuni in SQLite. Se sei alle prime armi con SQL, valuta di iniziare dal nostro corso Introduction to SQL per costruire basi solide. Inoltre, trovo utile come riferimento lo SQL Basics Cheat Sheet, che puoi scaricare, perché contiene tutte le funzioni SQL più comuni.

Concetti fondamentali del sistema di tipi di SQLite

Per lavorare con SQLite, è importante capire come memorizza e interpreta i dati internamente. Di seguito spiegherò come il sistema di tipi di SQLite aiuta a progettare applicazioni affidabili ed efficienti.

Classi di archiviazione spiegate

SQLite utilizza cinque classi di archiviazione per rappresentare internamente i valori:

• NULL rappresenta un valore mancante o indefinito.

• INTEGER rappresenta un intero con segno memorizzato da 1 a 8 byte, a seconda della magnitudine.

• REAL descrive un numero in virgola mobile memorizzato come valore IEEE-754 a 8 byte.

• TEXT memorizza stringhe di testo codificate in UTF-8, UTF-16BE o UTF-16LE.

• BLOB memorizza dati binari esattamente come in input, senza alcuna trasformazione.

Tipi di dati in SQLite. Fonte immagine: OpenAI

Queste classi di archiviazione differiscono dai tipi di dati dichiarati nello schema di una tabella. Una colonna può essere dichiarata come VARCHAR(50) o BOOLEAN, ma il valore effettivo viene memorizzato usando una delle cinque classi di archiviazione in base al suo contenuto. Questa separazione conferisce a SQLite la sua natura dinamica e tollerante, ma richiede anche agli sviluppatori di prestare attenzione alla validazione dei dati e alla coerenza dei tipi nel codice applicativo.

Affinità di tipo e comportamento delle colonne

L’affinità di tipo in SQLite è un tipo consigliato assegnato a una colonna in base al tipo di dato dichiarato. SQLite usa le seguenti regole per determinare l’affinità dal testo del tipo dichiarato.

• Le colonne dichiarate come VARCHAR, CHAR o TEXT ottengono affinità TEXT.

• Tipi come INT e INTEGER ottengono affinità INTEGER.

• Dichiarazioni come DECIMAL, NUMERIC o BOOLEAN ottengono affinità NUMERIC.

• Tipi come REAL, FLOAT o DOUBLE ottengono affinità REAL.

• L’affinità predefinita è BLOB se non viene dichiarato alcun tipo riconosciuto.

Per esempio, una colonna dichiarata come VARCHAR(100) avrà affinità TEXT, il che significa che SQLite proverà a convertire i valori in testo quando li memorizza. Questa affinità guida il modo in cui SQLite coerce e memorizza i dati, ma non impedisce di archiviare altri tipi in quella colonna.

Tipaggio manifesto e relative implicazioni

SQLite utilizza il tipaggio manifesto, cioè il tipo di dato è associato al singolo valore, non al contenitore colonna. Questo consente a più tipi di valori di coesistere nella stessa colonna. Per esempio, una colonna dichiarata come INTEGER può contenere valori TEXT o BLOB.

Il tipaggio manifesto permette agli sviluppatori di inserire tipi di dati eterogenei senza modificare lo schema. Tuttavia, il rovescio della medaglia è che può portare a dati incoerenti, ordinamenti imprevedibili e a una maggiore dipendenza dalla logica applicativa per far rispettare le regole sui tipi.

Ti consiglio di seguire il nostro corso Database Design per imparare la gestione dei database, inclusa l’organizzazione dei dati in diversi tipi di dato e la gestione dei database.

Lavorare con i tipi di dati comuni in SQLite

Vediamo ora come gestire i tipi di dati comuni in SQLite, come testo, numeri, booleani, date e dati binari.

Testo, numeri e booleani

Le dichiarazioni di dati comunemente usate in SQLite includono TEXT, INTEGER, REAL, NUMERIC e BOOLEAN, ma i valori sono memorizzati in base all’affinità di tipo, non a tipi rigidi. Di seguito sono riportati alcuni casi d’uso per ciascun tipo di dato in uno schema reale.

• TEXT memorizza stringhe, adatto per nomi, email, URL e dati testuali generali.

• INTEGER è ideale per numeri interi come conteggi, ID e codici, offrendo archiviazione e ordinamento efficienti.

• REAL memorizza numeri in virgola mobile per misurazioni, valuta o valori non interi.

• L’affinità NUMERIC copre i valori dichiarati come DECIMAL, BOOLEAN o simili. A seconda del valore, SQLite tenta di memorizzarli come INTEGER o REAL.

• BOOLEAN non è una classe di archiviazione nativa ma è simulata memorizzando 0 (FALSE) o 1 (TRUE) come valori INTEGER dietro le quinte.

Consiglio di usare INTEGER per i booleani con vincoli espliciti lato applicazione, mentre NUMERIC è adatto per valori che richiedono un parsing numerico flessibile.

Date, orari e strategie temporali

SQLite non ha un tipo nativo DATE o DATETIME, ma supporta i seguenti formati per memorizzare dati temporali:

• TEXT: Memorizza date/ore come stringhe ISO8601 del tipo YYYY-MM-DD HH:MM:SS. Questo formato è leggibile e compatibile con molti strumenti, ma può risultare più lento per i confronti.

• REAL: Memorizza le date come numeri del giorno giuliano (virgola mobile), utile per aritmetica sulle date e query per intervalli, ma meno leggibile.

• INTEGER: Memorizza timestamp Unix, secondi dal 1970-01-01. È efficiente per l’archiviazione e rapido nei confronti numerici, ma non è leggibile.

Quando lavori con dati temporali, puoi usare queste funzioni: date(), time(), datetime(), julianday() e strftime() per il parsing, la formattazione e la conversione tra formati temporali.

Casi limite per dati binari e numerici

SQLite supporta anche i dati binari tramite la classe di archiviazione BLOB. I BLOB sono utili per memorizzare immagini, file o qualsiasi dato non testuale. Quando lavori con tipi di dati BLOB, usa sempre query parametrizzate o binding per inserire i BLOB. Puoi anche memorizzare grandi binari esternamente e farvi riferimento con percorsi o hash.

Sebbene SQLite possa memorizzare interi fino a 8 byte (-2^63 a 2^63-1), i valori oltre questo intervallo possono essere convertiti silenziosamente nel tipo di dato REAL. Ciò può far perdere precisione ai valori. SQLite non impone rigorosamente la precisione decimale, quindi usa sempre librerie esterne o logica applicativa per i dati finanziari quando ti serve precisione.

Imporre struttura con le tabelle STRICT

A differenza del tradizionale sistema di tipi flessibile, SQLite ha introdotto le tabelle STRICT nella versione 3.37.0. Una tabella STRICT fornisce una modalità di tipaggio più rigorosa che impone la corrispondenza esatta del tipo per i valori delle colonne. Quando dichiari una tabella come STRICT, ogni valore inserito deve corrispondere al tipo dichiarato della colonna e qualsiasi mancata corrispondenza genera un errore. 

La modalità STRICT migliora la coerenza e la validazione dei dati. Aiuta anche a intercettare in anticipo gli errori legati ai tipi durante lo sviluppo e rende il comportamento di SQLite più prevedibile, più vicino ai RDBMS tradizionali.

Prima di usare STRICT come sviluppatore, dovresti notare che è disponibile solo in SQLite 3.37.0 e versioni successive. Inoltre, non puoi mescolare STRICT e tipaggio manifesto nella stessa tabella.

Considerazioni su prestazioni e design

SQLite ha un sistema di tipi flessibile con implicazioni sulle prestazioni. SQLite utilizza una codifica a lunghezza variabile per i valori INTEGER e REAL, il che significa che i numeri più piccoli usano meno byte. Questo aiuta a ridurre le dimensioni del database, soprattutto per dataset di grandi dimensioni. 

L’affinità di tipo influenza anche il modo in cui i valori sono memorizzati e confrontati. Tipi incoerenti in una colonna possono ridurre l’efficacia degli indici, poiché SQLite potrebbe eseguire conversioni di tipo extra durante le ricerche, rallentando le query.

Considera le seguenti strategie per progettare schemi e query efficienti.

• Definisci colonne con affinità chiare e coerenti per ridurre al minimo il costo della coercizione dei tipi.

• Valuta cast espliciti nelle query quando è necessaria la conversione di tipo per garantire un comportamento coerente.

• Usa affinità INTEGER per ID numerici e contatori, REAL per dati in virgola mobile e TEXT per stringhe.

• Evita di mescolare tipi di dati nella stessa colonna per prevenire anomalie in ordinamento e confronto.

• Normalizza gli schemi per evitare coercizioni di tipo ridondanti o duplicazioni di dati.

• Usa EXPLAIN QUERY PLAN per analizzare le prestazioni e ottimizzare le query.

Esempi di tipi di dati in SQLite con pattern di schema

Gli schemi di tabella seguenti illustrano l’uso accorto dei tipi di dati di SQLite, dell’affinità di tipo e dei vincoli.

Tabella Users con simulazione booleana e vincoli

Nell’esempio seguente:

• is_active simula un booleano usando INTEGER con un vincolo CHECK.

• TEXT garantisce coerenza per i valori stringa mentre INTEGER serve per la logica di tipo booleano.

 -- 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
);Offerto da

Tabella Orders con dati temporali e precisione numerica

Nella query seguente, total usa NUMERIC per valori simili a valuta per gestire la precisione a livello applicativo. order_date è memorizzato come TEXT in formato ISO-8601, garantendo leggibilità e compatibilità con le funzioni data.

-- 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)  );Offerto da

Tabella Files con binari e controlli di tipo

Nella query seguente, content usa BLOB per memorizzare dati binari grezzi. uploaded_at memorizza l’orario come INTEGER per efficienza in termini di spazio e prestazioni.

-- 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 
);Offerto da

Puoi verificare il tipo a runtime con la seguente query:

SELECT typeof(uploaded_at), typeof(content) FROM files;Offerto da

Ti consiglio di seguire il nostro corso Introduction to Relational Databases in SQL per saperne di più sulle strutture dati e su come creare tabelle nei database.

Confronto tra SQLite e altri database

Il sistema di tipi di SQLite è più flessibile rispetto ai tradizionali RDBMS come MySQL o PostgreSQL. La tabella seguente confronta SQLite con questi database.

Conclusione

SQLite ha un sistema di tipaggio flessibile che offre diversi vantaggi se usato con criterio. Gli sviluppatori possono progettare schemi che bilanciano adattabilità e affidabilità comprendendo classi di archiviazione, affinità di tipo e tipaggio manifesto. Funzionalità come le tabelle STRICT, l’uso coerente dei tipi e la normalizzazione aiutano a imporre struttura dove necessario.

Come prossimo passo, ti consiglio i nostri corsi Exploratory Data Analysis in SQL e Data Manipulation in SQL per imparare ad analizzare dati di tipi diversi usando query SQL avanzate.