Views and Materialized Views

You will be able to store a query under a name, say what it costs and what it cannot do, and build the materialized view SQLite lacks out of a table and a transaction.

A CTE names a query for one statement, and a transaction makes several statements one. A view is the named query that outlives the statement: it lives in the schema, and any later query can use it.

A view stores a query, not rows

CREATE TABLE books (id INTEGER, title TEXT, shelf TEXT, price INTEGER);
INSERT INTO books VALUES (1,'Dune','sf',12), (2,'Emma','cl',9), (3,'Ubik','sf',11);

CREATE VIEW catalog (item, shelf) AS
SELECT title, shelf FROM books;

SELECT * FROM catalog ORDER BY item;

Dune|sf
Emma|cl
Ubik|sf

books is a base table: an ordinary table that stores data, which the view reads from. The optional column list renames the output columns. Nothing was copied: the view is re-run on every read, so a row added later shows up at once. SQLite has no CREATE OR REPLACE VIEW; to change a view, drop it and create it again.

When a view earns its place

• One name for a repeated join (shelf_days below).
• Narrowing. catalog never exposes price.
• A stable interface. If the data moves to a table shelf_items whose column is label, recreate the view and no caller changes:

-- was: CREATE VIEW titles AS SELECT title FROM books;
DROP VIEW titles;
CREATE VIEW titles AS SELECT label AS title FROM shelf_items;

A view over a join:

CREATE TABLE loans (book_id INTEGER, days INTEGER);
INSERT INTO loans VALUES (1,14), (1,7), (2,21), (3,5);

CREATE VIEW shelf_days AS
SELECT b.shelf, SUM(l.days) AS days
FROM books b JOIN loans l ON l.book_id = b.id
GROUP BY b.shelf;

SELECT * FROM shelf_days gives cl|21 and sf|26. A view can also select from another view.

A view hides work. The engine flattens it: it substitutes the view's query where the name appears, so SELECT * FROM shelf_days WHERE days > 25 runs as SELECT * FROM (<the shelf_days query>) WHERE days > 25. Every read pays for the join and GROUP BY again, and a view has no storage to hold an index. Index the base tables; the view benefits. The Explain lesson shows the flattened plan.

Views are read-only here

INSERT INTO catalog VALUES ('X','y'); fails with cannot modify catalog because it is a view. Accepting writes takes an INSTEAD OF trigger, which runs your statements in place of the write; the Triggers lesson builds one.

Materialized views: a table you refresh

A materialized view stores a query's result so reads are cheap, and goes stale (out of step with the base tables) until refreshed. SQLite has none, so build it: a snapshot, an ordinary table holding the rows a query returned at one moment, plus a refresh.

CREATE TABLE shelf_days_snap AS SELECT * FROM shelf_days;
INSERT INTO loans VALUES (2,3);

CREATE TABLE ... AS SELECT takes column names and rows from the SELECT. It copies values only: no primary key, constraint or index, so a snapshot you query often needs its own CREATE INDEX.

Now the view reads cl|24 and the snapshot still reads cl|21: fast reads, numbers as fresh as the last refresh. To check what you built, ask sqlite_master, the built-in table listing every table, view, index and trigger by type and name: SELECT type, name FROM sqlite_master WHERE name = 'shelf_days_snap' returns table|shelf_days_snap, and the same query for shelf_days returns view|shelf_days.

The refresh replaces the contents in one transaction, so nobody sees a half-empty table:

BEGIN;
DELETE FROM shelf_days_snap;
INSERT INTO shelf_days_snap SELECT * FROM shelf_days;
COMMIT;

INSERT ... SELECT fills columns by position, so both sides need the same count; otherwise it stops with table shelf_days_snap has 2 columns but 3 values were supplied.

Common mistakes

• Building the view with CREATE TABLE ... AS. sqlite_master lists it as a table, and its numbers freeze.
• Never refreshing, or refreshing too early. The snapshot column prints old figures beside the live ones.
• Refreshing with INSERT and no DELETE. The old rows stay beside the new ones, so the snapshot holds each group twice (the stale figure and the fresh one) and the report prints extra lines.
• An INNER JOIN or a bare SUM in the view. An entity with no child rows vanishes, or its total is NULL and the whole || line prints blank.

Reference: PostgreSQL

It has CREATE and REFRESH MATERIALIZED VIEW. A simple one-table view is auto-updatable: writes pass through, and WITH CHECK OPTION rejects a write the view's WHERE would then hide. GRANT SELECT lets a user read an object, so granting it on catalog alone makes narrowing a real permission. SQLite has none of these.

Your exercise

Build a per-customer totals view and a snapshot of its id and total columns. The starter then changes the orders, and you refresh the snapshot atomically. The likeliest slip is a refresh that leaves old rows in place.