Views

-- 1) Basic view
CREATE VIEW active_users AS
SELECT id, email, name, created_at
FROM   users
WHERE  deleted_at IS NULL AND status = 'active';

-- Query like a table
SELECT * FROM active_users WHERE created_at >= NOW() - INTERVAL 30 DAY;

-- 2) Replace + drop
CREATE OR REPLACE VIEW active_users AS
SELECT id, email, name, created_at FROM users WHERE status = 'active';

DROP VIEW IF EXISTS active_users;

-- 3) View with joins + computed columns
CREATE VIEW order_summary AS
SELECT
    o.id,
    o.user_id,
    u.email                     AS user_email,
    o.total,
    o.status,
    COUNT(i.id)                 AS item_count,
    o.created_at
FROM   orders o
JOIN   users u  ON u.id = o.user_id
LEFT JOIN order_items i ON i.order_id = o.id
GROUP BY o.id;

SELECT * FROM order_summary WHERE total >= 100;

-- 4) Algorithm — MERGE vs TEMPTABLE vs UNDEFINED
CREATE VIEW v1 ALGORITHM = MERGE AS SELECT * FROM users WHERE active;
CREATE VIEW v2 ALGORITHM = TEMPTABLE AS SELECT * FROM users WHERE active;

-- MERGE: query rewritten to use base table (fast, uses indexes)
-- TEMPTABLE: result materialised into a temp table (forced when DISTINCT, GROUP BY, etc.)
-- UNDEFINED (default): MySQL chooses

-- 5) Updatable views — INSERT / UPDATE / DELETE through them
CREATE VIEW recent_users AS
SELECT id, email, name, created_at
FROM   users
WHERE  created_at >= NOW() - INTERVAL 30 DAY;

-- Works:
UPDATE recent_users SET name = 'New' WHERE id = 1;

-- Doesn't work (multiple tables, GROUP BY, aggregates, DISTINCT):
UPDATE order_summary SET item_count = 5;   -- ERROR

-- 6) WITH CHECK OPTION — enforce view's WHERE on writes
CREATE VIEW active_admins AS
SELECT id, email, role
FROM   users
WHERE  status = 'active' AND role = 'admin'
WITH   CHECK OPTION;

-- INSERT INTO active_admins (email, role) VALUES ('x@x.com', 'user');
-- ERROR — violates WHERE clause; can't insert non-admin via this view

-- 7) Security — hide columns from users
REVOKE ALL ON users FROM analyst@'%';

CREATE VIEW user_public AS
SELECT id, name, created_at
FROM   users;

GRANT SELECT ON user_public TO analyst@'%';
-- Analyst reads names, can't see emails / passwords

-- 8) View dependencies
SHOW CREATE VIEW order_summary;

-- View definition
SELECT view_definition
FROM   information_schema.views
WHERE  table_schema = 'mydb' AND table_name = 'order_summary';

-- List all views
SELECT table_name FROM information_schema.views WHERE table_schema = 'mydb';

-- 9) DEFINER + INVOKER — security context
CREATE
    DEFINER = 'admin'@'%'
    SQL SECURITY INVOKER
VIEW user_public AS
SELECT id, name, created_at FROM users;

-- DEFINER (default): runs with creator's permissions
-- INVOKER: runs with caller's permissions

-- 10) Cascading drop
DROP VIEW user_public, order_summary;

-- View depending on dropped table → becomes invalid; CHECK TABLE shows error
CHECK TABLE my_view;

-- 11) View limitations
--   ❌ No CHECK constraints (use WITH CHECK OPTION instead)
--   ❌ No triggers ON the view (triggers fire on base tables)
--   ❌ Can't index views directly (use base-table indexes)
--   ❌ ALGORITHM = TEMPTABLE can't be updatable
--   ❌ Subqueries in FROM, UNION → not updatable

-- 12) No materialised views in MySQL!
-- Workarounds:
--   • Cached result table refreshed via event scheduler
--   • Application-side cache (Redis)
--   • Switch to Postgres / Oracle for true materialised views

-- DIY materialised view via table + event
CREATE TABLE daily_revenue (
    day DATE PRIMARY KEY,
    revenue DECIMAL(10, 2),
    order_count INT
);

CREATE EVENT refresh_daily_revenue
ON SCHEDULE EVERY 15 MINUTE
DO
    REPLACE INTO daily_revenue
    SELECT DATE(created_at) AS day,
           SUM(total)        AS revenue,
           COUNT(*)           AS order_count
    FROM   orders
    WHERE  status = 'paid'
    GROUP BY day;

-- Or via stored procedure called by external scheduler

-- 13) Common patterns

-- a) API shape — hide internal fields
CREATE VIEW api_users AS
SELECT
    id,
    email,
    name,
    created_at,
    (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count
FROM users u;

-- b) Backwards compatibility
ALTER TABLE orders RENAME COLUMN amt TO total;

CREATE OR REPLACE VIEW orders_compat AS
SELECT id, user_id, total AS amt, total, status, created_at
FROM orders;

-- c) Department-restricted view
CREATE VIEW my_team_orders AS
SELECT *
FROM   orders
WHERE  team_id = (SELECT team_id FROM users WHERE id = CURRENT_USER());

-- 14) Performance
-- ALGORITHM = MERGE: rewrites query to use base table (preferred when possible)
-- ALGORITHM = TEMPTABLE: materialises view first, then queries it (slower, no index reuse)
-- Use EXPLAIN to see how a view query gets executed:
EXPLAIN SELECT * FROM order_summary WHERE total > 100;

-- 15) Common bugs
--   ❌ Forgetting WITH CHECK OPTION on updatable views → bad data lands in base table
--   ❌ Using DEFINER with overly-broad role → privilege escalation risk
--   ❌ Many nested views → optimiser struggles
--   ❌ Assuming materialised behaviour → MySQL re-runs the underlying query
--   ❌ INSERT through view with required columns missing in view definition

-- 16) When to use views vs alternatives
-- View              : query reuse, security, abstraction
-- Stored procedure  : business logic with side effects
-- Generated columns : derived values stored alongside the row
-- Application cache : when MySQL alone can't make it fast enough
--                      (Redis / Memcached / app-side memoisation)

-- 17) Updatable view limits — must satisfy ALL:
--   • SELECT from ONE table
--   • No DISTINCT, GROUP BY, HAVING
--   • No aggregate functions (SUM, COUNT, etc.)
--   • No subqueries in SELECT list
--   • No UNION / UNION ALL
--   • No JOIN (in most cases)
--   • No LEFT JOIN with rows missing on the joined side
--   • Columns to be inserted/updated must come from base table

-- 18) Best practices
--   ✅ Document views with COMMENT in version control (MySQL doesn't store comments on views)
--   ✅ Use SQL SECURITY INVOKER unless you specifically need DEFINER for delegation
--   ✅ ALGORITHM = MERGE when the view CAN be merged — better index reuse
--   ✅ Use views for security (column-level) — pair with REVOKE on base table
--   ✅ Don't over-nest — 3+ levels of views slow the optimiser
--   ✅ EXPLAIN the underlying query — same indexes still apply
--   ✅ For aggregate caching, use a real table + event scheduler — no native materialised view
--   ✅ Consider switching to Postgres if you need real materialised views

CREATE VIEW active_users AS
    SELECT * FROM users WHERE last_login > NOW() - INTERVAL 30 DAY;