Skip to content

sqlite.md

Latest commit

 

History

History
207 lines (159 loc) · 6.1 KB

sqlite.md

File metadata and controls

207 lines (159 loc) · 6.1 KB
Raw

SQLite Features and Usage

Overview

SQLite converts in both directions, and every pair is verified by loading the result into a real server. The fixture on the SQLite side is sqlite3 .schema output. This page covers what SQLite does differently and what that costs on the way out.

Supported Features

Data Types

SQLite's dynamic type system supports five basic data types:

  • NULL: Null value
  • INTEGER: Integer values
  • REAL: Floating point numbers
  • TEXT: Text values
  • BLOB: Binary data

Note: Due to SQLite's "type flexibility" feature, data types from other database systems are converted to these five basic types.

Table Features

  • Auto-incrementing fields (AUTOINCREMENT)
  • Table constraints
  • Temporary tables (TEMPORARY TABLE)
  • WITHOUT ROWID tables
  • Virtual tables (with FTS and R-Tree modules)

Indexes

  • Unique indexes
  • Composite indexes
  • Partial indexes
  • Descending indexes

Constraints

  • NOT NULL
  • UNIQUE
  • PRIMARY KEY
  • FOREIGN KEY (must be explicitly enabled)
  • CHECK
  • DEFAULT values

Usage Examples

Simple Table Creation

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    created_at TEXT DEFAULT (datetime('now', 'localtime')),
    active INTEGER DEFAULT 1 CHECK (active IN (0,1))
);

Related Tables

-- Enable foreign key support
PRAGMA foreign_keys = ON;

CREATE TABLE categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    category_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    price REAL NOT NULL CHECK (price > 0),
    stock INTEGER DEFAULT 0,
    FOREIGN KEY (category_id) REFERENCES categories(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Index Usage

-- Unique index
CREATE UNIQUE INDEX idx_user_email ON users(email);

-- Composite index
CREATE INDEX idx_product_category ON products(category_id, name);

-- Partial index
CREATE INDEX idx_active_users ON users(id) WHERE active = 1;

-- Descending index
CREATE INDEX idx_product_price ON products(price DESC);

View Creation

CREATE VIEW active_products AS
SELECT p.*, c.name as category_name
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.stock > 0;

Trigger Creation

CREATE TRIGGER tr_update_stock
AFTER INSERT ON order_details
BEGIN
    UPDATE products
    SET stock = stock - NEW.quantity
    WHERE id = NEW.product_id;
END;

Virtual Table (FTS) Usage

-- Create FTS5 table
CREATE VIRTUAL TABLE articles USING fts5(
    title,
    content,
    tags
);

-- Search example
SELECT * FROM articles
WHERE articles MATCH 'python AND programming';

Conversion Notes

The mappings below are what the converter actually produces, checked by loading the result into each server.

Types, from SQLite

SQLite MySQL PostgreSQL Oracle SQL Server
INTEGER INT INTEGER NUMBER(10) INT
REAL FLOAT REAL BINARY_FLOAT REAL
NUMERIC(p,s) DECIMAL(p,s) NUMERIC(p,s) NUMBER(p,s) DECIMAL(p,s)
TEXT TEXT TEXT CLOB NVARCHAR(MAX)
BLOB BLOB BYTEA BLOB VARBINARY(MAX)

INTEGER PRIMARY KEY AUTOINCREMENT becomes each target's own identity: MySQL INT AUTO_INCREMENT PRIMARY KEY, PostgreSQL SERIAL PRIMARY KEY, Oracle NUMBER(10) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, SQL Server INT IDENTITY(1,1) PRIMARY KEY.

What SQLite's looseness costs

SQLite gives a column no length, no boolean and no date type, and every strict target has an opinion about that:

  • A text column a key touches has to be bounded. MySQL will not index a TEXT column without a prefix length, so the key reads email(255). SQL Server rejects NVARCHAR(MAX) as a key column, so the column becomes NVARCHAR(450). Oracle cannot index a CLOB at all, so it becomes VARCHAR2(1000). A column no key touches keeps the unbounded type.
  • A flag is an integer, not a boolean. A view saying WHERE is_active is rewritten to WHERE is_active <> 0 for PostgreSQL, which is the only target with a strict boolean and answers "argument of WHERE must be type boolean, not type integer" otherwise.
  • A timestamp default on a text column is a conversion. Oracle refuses to assign a timestamp to a character column, so DEFAULT CURRENT_TIMESTAMP on a TEXT column becomes DEFAULT TO_CHAR(SYSTIMESTAMP).

Into SQLite

Every source type maps to one of the five storage classes, with the precision of an exact numeric kept because SQLite accepts it and it records what the column was for. Lengths on text are dropped: SQLite does not enforce them.

Foreign keys are written inline rather than added afterwards, because SQLite has no ALTER TABLE ADD CONSTRAINT. It accepts a reference to a table declared later, so a cycle between two tables needs no special handling. Note that SQLite does not enforce foreign keys unless the connection sets PRAGMA foreign_keys = ON.

sqlite_sequence and anything else named sqlite_* belongs to SQLite rather than to the schema, and is skipped on the way in.

Best Practices

  1. Explicitly enable foreign key support
  2. Use indexes carefully (too many indexes can degrade performance in SQLite)
  3. Use separate tables for large BLOB data
  4. Make effective use of transactions
  5. Consider using WAL (Write-Ahead Logging) mode

Limitations

  • SQLite has triggers and nothing else: no stored functions, no procedures. One coming from another database is written out commented rather than dropped, so it stays in front of whoever has to port it.
  • Types are simplified on the way in, because SQLite has five storage classes and no way to express more.
  • Partitioning, materialized views and the rest of what a bigger database offers have no SQLite equivalent and do not survive.