DDL — Definição do Schema

DDL (Data Definition Language) é o subconjunto de SQL que define a estrutura da base de dados — tabelas, colunas, tipos, constraints, índices, views e sequências. Ao contrário do DML, que manipula dados, o DDL manipula o schema em si. Os comandos DDL fazem commit implícito na maioria dos SGBD — não podem ser revertidos com ROLLBACK (PostgreSQL é uma excepção: DDL é transaccional e pode ser revertido dentro de um bloco BEGIN/COMMIT). Compreender DDL é compreender como o SGBD organiza e armazena informação em disco.

CREATE TABLE

-- Forma completa de CREATE TABLE com todas as opções relevantes:

CREATE TABLE IF NOT EXISTS utilizador (
    -- ── Colunas ──────────────────────────────────────────────────────────────
    id            BIGSERIAL       PRIMARY KEY,
    -- BIGSERIAL = BIGINT NOT NULL DEFAULT nextval('utilizador_id_seq')
    -- Cria automaticamente uma sequência e usa-a como default

    email         VARCHAR(255)    NOT NULL,
    nome          VARCHAR(100)    NOT NULL,
    password_hash CHAR(60)        NOT NULL,
    -- CHAR(60): bcrypt produz sempre 60 caracteres — comprimento fixo garantido

    pais          CHAR(2)         NOT NULL    DEFAULT 'PT',
    bio           TEXT,
    -- TEXT sem NOT NULL → aceita NULL (campo opcional)

    activo        BOOLEAN         NOT NULL    DEFAULT TRUE,
    criado_em     TIMESTAMPTZ     NOT NULL    DEFAULT NOW(),
    actualizado_em TIMESTAMPTZ    NOT NULL    DEFAULT NOW(),

    -- ── Constraints ao nível da tabela ───────────────────────────────────────
    CONSTRAINT uq_utilizador_email UNIQUE (email),
    -- Nomear constraints facilita identificar erros: "duplicate key value violates
    -- unique constraint 'uq_utilizador_email'" vs "uq_utilizador_pkey"

    CONSTRAINT chk_email_formato
        CHECK (email ~* '^[A-Za-z0-9._%+\-]+@[A-Za-z0-9.\-]+\.[A-Za-z]{2,}$'),
    -- ~* = regex case-insensitive no PostgreSQL

    CONSTRAINT chk_nome_comprimento
        CHECK (char_length(nome) BETWEEN 2 AND 100)
);

-- IF NOT EXISTS: não lança erro se a tabela já existir
-- Útil em scripts de inicialização — idempotente

-- Ver a definição gerada:
\d utilizador          -- psql: descreve a tabela
\d+ utilizador         -- psql: versão detalhada com storage e comentários

Sequências e Identidade

-- Sequências são objectos independentes que geram valores inteiros únicos
-- SERIAL/BIGSERIAL são atalhos que criam uma sequência automaticamente

-- Criar sequência manualmente (mais controlo):
CREATE SEQUENCE seq_numero_encomenda
    START WITH  1000        -- primeiro valor
    INCREMENT BY 1
    MINVALUE     1000
    MAXVALUE     9999999999
    CACHE        20         -- pré-aloca 20 valores em memória — mais rápido mas
                            -- pode criar lacunas se o servidor reiniciar
    NO CYCLE;               -- erro quando atinge MAXVALUE (não recomeça do início)

-- Usar a sequência:
CREATE TABLE encomenda (
    id             BIGINT      PRIMARY KEY DEFAULT nextval('seq_numero_encomenda'),
    numero_display VARCHAR(20) GENERATED ALWAYS AS ('ENC-' || id::TEXT) STORED,
    -- GENERATED ALWAYS AS ... STORED: coluna computada e armazenada em disco
    -- Recalculada em cada INSERT/UPDATE — não pode ser escrita manualmente
    cliente_id     BIGINT      NOT NULL,
    criada_em      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- SQL standard (PostgreSQL 10+): GENERATED AS IDENTITY
-- Mais portável que SERIAL — recomendado em código novo:
CREATE TABLE produto (
    id    INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    -- GENERATED ALWAYS: impede inserção manual do id
    -- GENERATED BY DEFAULT: permite inserção manual (útil para testes/imports)
    nome  VARCHAR(200) NOT NULL
);

-- Consultar e manipular sequências:
SELECT nextval('seq_numero_encomenda');    -- avança e retorna o próximo valor
SELECT currval('seq_numero_encomenda');    -- retorna o valor actual (da sessão)
SELECT lastval();                          -- último nextval() da sessão actual
ALTER SEQUENCE seq_numero_encomenda RESTART WITH 5000; -- repor (cuidado: duplicados!)

ALTER TABLE

-- ALTER TABLE modifica a estrutura de uma tabela existente
-- No PostgreSQL, ALTER TABLE é transaccional — pode ser revertido

-- ── Adicionar colunas ─────────────────────────────────────────────────────────
ALTER TABLE utilizador
    ADD COLUMN telefone    VARCHAR(20),
    ADD COLUMN ultimo_login TIMESTAMPTZ;
-- Múltiplas alterações numa só instrução = uma única passagem pela tabela

-- Adicionar coluna NOT NULL com valor default (sem bloquear a tabela):
-- ❌ PERIGOSO em produção com tabelas grandes:
ALTER TABLE utilizador ADD COLUMN pontos INTEGER NOT NULL DEFAULT 0;
-- Este comando reescreve TODA a tabela para preencher o novo campo
-- Em PostgreSQL 11+: adicionar NOT NULL DEFAULT é instantâneo para defaults constantes
-- (o default é guardado no catálogo, não reescrito linha a linha)

-- ✅ Padrão seguro para versões anteriores ou defaults não-constantes:
ALTER TABLE utilizador ADD COLUMN pontos INTEGER;          -- 1. adicionar nullable
UPDATE utilizador SET pontos = 0 WHERE pontos IS NULL;     -- 2. preencher em batches
ALTER TABLE utilizador ALTER COLUMN pontos SET NOT NULL;   -- 3. adicionar constraint
ALTER TABLE utilizador ALTER COLUMN pontos SET DEFAULT 0;  -- 4. definir default


-- ── Modificar colunas ─────────────────────────────────────────────────────────
ALTER TABLE utilizador
    ALTER COLUMN telefone TYPE VARCHAR(30),          -- alargar comprimento
    ALTER COLUMN nome     SET NOT NULL,              -- adicionar NOT NULL
    ALTER COLUMN bio      SET DEFAULT 'Sem bio.',    -- definir default
    ALTER COLUMN activo   DROP DEFAULT;              -- remover default

-- Converter tipo de dados (requer CAST explícito se os tipos são incompatíveis):
ALTER TABLE produto
    ALTER COLUMN preco TYPE NUMERIC(12, 2)
    USING preco::NUMERIC(12, 2);
-- USING: expressão de conversão — necessário quando PostgreSQL não consegue converter implicitamente


-- ── Renomear ──────────────────────────────────────────────────────────────────
ALTER TABLE utilizador RENAME COLUMN bio TO biografia;
ALTER TABLE utilizador RENAME TO utilizador_v2;         -- renomear tabela inteira


-- ── Remover colunas ───────────────────────────────────────────────────────────
ALTER TABLE utilizador DROP COLUMN telefone;
ALTER TABLE utilizador DROP COLUMN IF EXISTS telefone;  -- não lança erro se não existir


-- ── Constraints ───────────────────────────────────────────────────────────────
-- Adicionar:
ALTER TABLE encomenda
    ADD CONSTRAINT chk_total_positivo CHECK (total >= 0);

ALTER TABLE encomenda
    ADD CONSTRAINT fk_encomenda_cliente
        FOREIGN KEY (cliente_id) REFERENCES utilizador(id)
        ON DELETE RESTRICT;

-- Remover:
ALTER TABLE encomenda DROP CONSTRAINT chk_total_positivo;
ALTER TABLE encomenda DROP CONSTRAINT IF EXISTS chk_total_positivo;

-- Desactivar/activar temporariamente (útil em imports massivos):
ALTER TABLE linha_encomenda DISABLE TRIGGER ALL;
-- ... import de milhões de linhas ...
ALTER TABLE linha_encomenda ENABLE TRIGGER ALL;

-- Verificar constraint sem bloquear escritas (PostgreSQL):
ALTER TABLE produto
    ADD CONSTRAINT chk_preco_pos CHECK (preco > 0) NOT VALID;
-- NOT VALID: a constraint não verifica linhas existentes — apenas novas inserções
-- Validar depois, sem bloquear:
ALTER TABLE produto VALIDATE CONSTRAINT chk_preco_pos;
-- Valida linhas existentes com lock mínimo — seguro em produção

DROP e TRUNCATE

-- ── DROP — remove o objecto e toda a sua estrutura ───────────────────────────

DROP TABLE utilizador;
-- Erro se existirem chaves estrangeiras a referenciar esta tabela

DROP TABLE utilizador CASCADE;
-- CASCADE: remove também todos os objectos dependentes
-- (chaves estrangeiras, views, triggers, índices)
-- ⚠️ MUITO PERIGOSO — pode apagar muito mais do que se espera
-- Em produção: listar dependências antes de usar CASCADE:
SELECT dependent_ns.nspname, dependent_view.relname
FROM pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
JOIN pg_class dependent_view ON pg_rewrite.ev_class = dependent_view.oid
JOIN pg_namespace dependent_ns ON dependent_ns.oid = dependent_view.relnamespace
WHERE pg_depend.refobjid = 'utilizador'::regclass;

DROP TABLE IF EXISTS utilizador;         -- não lança erro se não existir
DROP TABLE IF EXISTS utilizador RESTRICT; -- RESTRICT (default): erro se há dependências

-- Remover múltiplas tabelas:
DROP TABLE IF EXISTS linha_encomenda, encomenda, produto, categoria CASCADE;
-- Ordem: tabelas filhas antes das tabelas pai (ou usar CASCADE)


-- ── TRUNCATE — apaga todos os dados, mantém a estrutura ──────────────────────

TRUNCATE TABLE encomenda;
-- Muito mais rápido que DELETE sem WHERE — não regista cada linha apagada no WAL
-- Faz reset das sequências associadas? Não por default

TRUNCATE TABLE encomenda RESTART IDENTITY;
-- RESTART IDENTITY: repõe sequências ao valor inicial (id começa de 1 novamente)

TRUNCATE TABLE encomenda CASCADE;
-- CASCADE: faz TRUNCATE também nas tabelas com FK para esta tabela
-- (linha_encomenda é truncada automaticamente)

-- TRUNCATE vs DELETE:
-- TRUNCATE: sem WHERE, sem log por linha, muito rápido, não dispara row-level triggers
-- DELETE:   pode ter WHERE, log completo, dispara triggers, pode ser mais lento
-- Em PostgreSQL: ambos são transaccionais (podem ser revertidos com ROLLBACK)

-- Uso típico de TRUNCATE: limpar tabelas de staging/temporárias antes de um import
TRUNCATE TABLE staging_produtos RESTART IDENTITY;
-- ... carregar novos dados ...
INSERT INTO produto SELECT * FROM staging_produtos;

Views e Views Materializadas

-- ── View — query guardada como objecto virtual ───────────────────────────────
-- Uma view não armazena dados — é uma query executada a cada acesso
-- Útil para: simplificar queries complexas, controlo de acesso (expor apenas certas colunas)

CREATE OR REPLACE VIEW v_encomendas_activas AS
SELECT
    e.id,
    e.criada_em,
    u.email        AS cliente_email,
    u.nome         AS cliente_nome,
    COUNT(l.produto_id) AS num_produtos,
    e.total
FROM encomenda e
JOIN utilizador u ON u.id = e.cliente_id
JOIN linha_encomenda l ON l.encomenda_id = e.id
WHERE e.estado NOT IN ('cancelado', 'entregue')
GROUP BY e.id, e.criada_em, u.email, u.nome, e.total;

-- Usar como tabela normal:
SELECT * FROM v_encomendas_activas WHERE total > 100 ORDER BY criada_em DESC;

-- Updatable views (PostgreSQL):
-- Uma view simples (sem JOIN, GROUP BY, DISTINCT) pode aceitar INSERT/UPDATE/DELETE
-- Para views complexas: usar INSTEAD OF triggers ou WITH CHECK OPTION

CREATE OR REPLACE VIEW v_utilizadores_publicos AS
SELECT id, nome, pais, criado_em   -- sem email, sem password_hash
FROM utilizador
WHERE activo = TRUE
WITH CHECK OPTION;  -- garante que INSERT/UPDATE não viola a condição WHERE


-- ── Materialized View — resultado guardado em disco ───────────────────────────
-- Armazena o resultado da query — leitura muito mais rápida
-- Dados podem ficar desactualizados — requer refresh manual ou agendado

CREATE MATERIALIZED VIEW mv_resumo_vendas_mensal AS
SELECT
    DATE_TRUNC('month', e.criada_em)        AS mes,
    c.nome                                   AS categoria,
    COUNT(DISTINCT e.id)                     AS num_encomendas,
    SUM(l.quantidade)                        AS unidades_vendidas,
    SUM(l.quantidade * l.preco_unit)         AS receita_total,
    AVG(l.preco_unit)                        AS preco_medio
FROM linha_encomenda l
JOIN encomenda  e ON e.id = l.encomenda_id
JOIN produto    p ON p.id = l.produto_id
JOIN categoria  c ON c.id = p.categoria_id
WHERE e.estado = 'entregue'
GROUP BY DATE_TRUNC('month', e.criada_em), c.nome
WITH DATA;  -- WITH DATA: executa a query imediatamente ao criar
            -- WITH NO DATA: cria vazia, preencher depois com REFRESH

-- Índice na materialized view (necessário para REFRESH CONCURRENTLY):
CREATE UNIQUE INDEX ON mv_resumo_vendas_mensal (mes, categoria);

-- Actualizar:
REFRESH MATERIALIZED VIEW mv_resumo_vendas_mensal;
-- Bloqueia leituras durante o refresh

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_resumo_vendas_mensal;
-- Não bloqueia leituras — requer índice UNIQUE
-- Mais lento que o refresh normal mas seguro em produção

-- Remover:
DROP MATERIALIZED VIEW IF EXISTS mv_resumo_vendas_mensal;
DROP VIEW IF EXISTS v_encomendas_activas;

Schemas (Namespaces)

-- Um schema é um namespace dentro de uma base de dados
-- Permite organizar tabelas em grupos lógicos e controlar permissões por grupo
-- Por default, as tabelas vão para o schema "public"

-- Criar schemas:
CREATE SCHEMA loja;
CREATE SCHEMA auditoria;
CREATE SCHEMA IF NOT EXISTS staging;

-- Criar tabela num schema específico:
CREATE TABLE loja.produto (
    id   SERIAL PRIMARY KEY,
    nome VARCHAR(200) NOT NULL
);

CREATE TABLE auditoria.log_alteracoes (
    id          BIGSERIAL   PRIMARY KEY,
    tabela      VARCHAR(100) NOT NULL,
    operacao    CHAR(1)      NOT NULL,   -- 'I', 'U', 'D'
    utilizador  VARCHAR(100) NOT NULL DEFAULT current_user,
    timestamp   TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    dados_antes JSONB,
    dados_depois JSONB
);

-- search_path: define a ordem de procura de schemas (como PATH no shell)
SET search_path TO loja, public;
-- Agora: SELECT * FROM produto → procura loja.produto primeiro, depois public.produto

-- Definir search_path permanente para um utilizador:
ALTER ROLE app_user SET search_path TO loja, public;

-- Listar schemas:
SELECT schema_name FROM information_schema.schemata;

-- Remover schema:
DROP SCHEMA IF EXISTS staging CASCADE;  -- CASCADE remove todas as tabelas dentro
DROP SCHEMA IF EXISTS staging RESTRICT; -- RESTRICT: erro se o schema não estiver vazio

Comentários e Documentação do Schema

-- Documentar o schema directamente no SGBD — fica persistente e acessível via ferramentas

COMMENT ON TABLE utilizador
    IS 'Utilizadores registados na plataforma. Inclui clientes e administradores.';

COMMENT ON COLUMN utilizador.password_hash
    IS 'Hash bcrypt da password (cost=12). Nunca armazenar a password em texto claro.';

COMMENT ON COLUMN utilizador.pais
    IS 'Código ISO 3166-1 alpha-2 do país de residência. Ex: PT, ES, US.';

COMMENT ON CONSTRAINT uq_utilizador_email ON utilizador
    IS 'Cada endereço de email só pode estar associado a um utilizador.';

-- Ver comentários:
SELECT
    c.column_name,
    c.data_type,
    c.is_nullable,
    pgd.description
FROM information_schema.columns c
LEFT JOIN pg_class pc ON pc.relname = c.table_name
LEFT JOIN pg_description pgd
    ON pgd.objoid = pc.oid
    AND pgd.objsubid = c.ordinal_position
WHERE c.table_name = 'utilizador'
ORDER BY c.ordinal_position;

Checklist DDL

CREATE TABLE IF NOT EXISTS torna scripts de inicialização idempotentes — seguros para executar múltiplas vezes.
Nomear todas as constraints explicitamente (CONSTRAINT nome_descritivo) — facilita diagnóstico de erros e migrações.
GENERATED ALWAYS AS IDENTITY é o standard SQL moderno; preferir a SERIAL em código novo.
Em PostgreSQL, DDL é transaccional — usar BEGIN/COMMIT em migrações para reverter em caso de erro.
Adicionar colunas NOT NULL com default em tabelas grandes: usar o padrão em 3 passos (add nullable → update → set not null) em versões anteriores ao PostgreSQL 11.
ADD CONSTRAINT ... NOT VALID + VALIDATE CONSTRAINT: adicionar constraints sem bloquear a tabela em produção.
TRUNCATE ... RESTART IDENTITY CASCADE para limpar tabelas de staging antes de imports.
Views simples para controlo de acesso — expor apenas colunas necessárias sem duplicar dados.
Materialized views para relatórios pesados — REFRESH CONCURRENTLY requer índice UNIQUE.
DROP ... CASCADE só após listar dependências — pode remover objectos inesperados.
Usar schemas como namespaces para separar domínios (loja, auditoria, staging) e aplicar permissões por grupo.

Normalização de Tabelas DML + DTL — Manipulação & Transacções