• Os processos precisavam ser separados em ativos, cancelados e encerrados.

• Criar tabelas para cada um desses processos.
  • Problema: como referenciar um determinado processo se não se sabe em qual tabela ele se encontra?
• Fazer um particionamento da tabela processo.
  • Problema: implementação complexa
• Criar um campo status na tabela processo e a partir dele criar views.
  • SOLUÇÃO IMPLEMENTADA!

CREATE VIEW processo_ativo AS
	SELECT id, tipo_processo, estado FROM processo WHERE status = 1;

• processo_ativo: nome da view.
• id, tipo_processo, estado: colunas da view.
• status = 1: parte da tabela que será acessada pela view.

• Uma vez que for feito um SELECT em cima dessa VIEW, automaticamente só serão mostrados processos ativos.
• Com a criação da view, caso você faça um select do tipo:

  SELECT id, tipo_processo FROM processo_ativo WHERE estado = 1;

  O banco reescreve isso para:
  

  SELECT id, tipo_processo FROM processo WHERE estado = 1 AND status = 1;

• O importante é notar que este é um select comum. Você poderia rodar direto o segundo e ter os mesmos resultados. Usamos a view apenas porque é conveniente. Facilita a vida do programador, que não precisa se lembrar de colocar "AND status = 1" no final de toda consulta.

• Não é possível executar INSERT, DELETE ou UPDATE em uma view.

Para que isso seja possível, é preciso a criação de rules.

• Após a criação da view processo_ativo, ao tentar inserir nessa view acontece o seguinte erro:
  ERROR: cannot insert into a view
  HINT: You need an unconditional ON INSERT DO INSTEAD rule.

• Inserir diretamente na tabela processo.
  • Problema: terá sempre que lembrar de colocar "...AND status = 1" na cláusula WHERE do INSERT.
• Criar uma "regra" que permita que essas ações sejam feitas.
  • SOLUÇÃO IMPLEMENTADA!

CREATE RULE processo_ativ_ins AS ON INSERT TO processo_ativo
	DO INSTEAD
	INSERT INTO processo VALUES (
		NEW.id,
		NEW.tipo_processo,
		NEW.estado,
		1);

• processo_ativ_ins: nome da rule.
• INSERT TO processo_ativo: evento sobre a qual a regra será aplicada.
• INSERT INTO processo(...): ação que será executada quando o evento for detectado.

CREATE RULE processo_ativ_upd AS ON UPDATE TO processo_ativo
	DO INSTEAD
	UPDATE processo SET
		id = NEW.id,
		tipo_processo = NEW.tipo_processo,
		estado = NEW.estado,
	WHERE id = OLD.id;

CREATE RULE processo_ativ_del AS ON DELETE TO processo_ativo
	DO INSTEAD
	DELETE FROM processo
	WHERE id = OLD.id;

• NEW: é válido nas regras ON INSERT e ON UPDATE para se referir à nova linha sendo inserida ou atualizada.
• OLD: é válido nas regras ON UPDATE e ON DELETE para se referir à linha existente sendo atualizada ou excluída.

• Com a criação dessas regras, as ações de INSERT, UPDATE ou DELETE na view processo_ativo resultarão, respectivamente, em INSERT, UPDATE ou DELETE na tabela processo.
• De acordo com a view, o campo status foi fixado na rule como sendo sempre 1.
• Sendo assim, toda inserção na view resultará em uma inserção na tabela processo com campo status = 1.

• A maioria das consultas do sistema são feitas em cima dos processos ativos de estados diferentes. Logo, busca-se uma forma de tornar essas consultas mais rápidas.

• Criar um índice sobre a coluna estado.
  • Problema: será criado um índice sobre toda a tabela, sem necessidade.
• Criar um índice parcial sobre a coluna estado apenas dos processos ativos.
  • SOLUÇÃO IMPLEMENTADA!

CREATE INDEX idx_processo_estado
	ON processo (estado)
	WHERE status = 1;

• idx_processo_estado: nome do índice.
• processo (estado): tabela (coluna na qual o índice será criado).
• status = 1: determina em qual parte da tabela o índice será criado. Ou seja, apenas nos casos onde status = 1.

• Após criado o índice parcial, toda consulta que fizer "...WHERE status= 1 AND estado = ?" vai usar este índice. Ele será bem pequeno, pois vai criar a estrutura apenas para os ativos.
• Se você fizer um update inativando o processo, ele sai do índice, mantendo a consulta aos ativos sempre rápida.

• Quando você faz um select sobre uma view e o banco o reescreve, o select reescrito é então rodado sobre a tabela.
• A partir daí, vale a mesma regra que vale para qualquer select: se o otimizador achar que vale a pena usar o índice existente, ele o usará.

• Todo usuário do tipo professor precisa constar também na tabela professor.
  • Logo, busca-se uma forma de tornar essa operação automática, ou seja, sempre que for inserido um usuário com tipo igual a 2 (identificador do tipo 'Professor'), esse mesmo usuário será inserido também na tabela professor.

• Implementar esse comportamento no código da aplicação.
  • Problema: sempre que incluirmos no código um comando para inserir um usuário, teremos que nos preocupar em verificar se o mesmo é professor. Caso seja, inserir também na tabela professor.
• Criar uma trigger para ser disparada sempre após uma inserção na tabela usuário.
  • SOLUÇÃO IMPLEMENTADA!

Caso a linguagem PL/pgSQL ainda não esteja instalada no PostgreSQL, é necessário criá-la com os seguintes comandos no terminal:

• $ cd /usr/lib/postgresql/8.3/bin
• $ createlang plpgsql -U postgres prod

Com a linguagem criada, primeiramente crie a função a ser executada pela trigger.

Para o nosso caso, criaremos a stored procedure sp_cria_professor():

CREATE FUNCTION bau.sp_cria_professor () RETURNS trigger AS '
  DECLARE
    -- Declara a variável para guardar o tipo do usuário.
    descr_tipo_usuario VARCHAR(50);
  BEGIN
    -- Guarda o tipo do novo usuário na variável descr_tipo_usuario.
    -- NEW.tipo guarda o valor dado à coluna tipo no comando insert
    SELECT INTO descr_tipo_usuario descricao FROM tipo_usuario WHERE id = NEW.tipo;
    -- Verifica se o novo usuário é do tipo professor.
    IF descr_tipo_usuario = ''Professor'' THEN
      -- Insere também na tabela professor.
      INSERT INTO professor (id_professor) VALUES (NEW.id);
    END IF;
    
    RETURN NEW;
  END;
' LANGUAGE 'plpgsql';

Com a função criada, criaremos o trigger tg_cria_professor que irá dispará-la:

CREATE CONSTRAINT TRIGGER tg_cria_professor
    AFTER INSERT -- comando que irá disparar o trigger (no nosso caso, disparará após um insert)
    ON bau.usuario -- tabela que irá disparar o trigger
    FOR EACH ROW -- o trigger é disparado uma vez para cada linha modificada pela operação
    EXECUTE PROCEDURE bau.sp_cria_professor (); -- função que será chamada

Após criado o gatilho, todo insert na tabela bau.usuario vai executar a stored procedure bau.sp_cria_professor, e dependendo do valor da coluna tipo, pode resultar em um insert na tabela bau.professor.

Utilize os comandos a seguir para listar as stored procedures e os triggers existentes.

• stored procedure

SELECT proname, prosrc
FROM pg_proc
WHERE proname ilike 'sp_%';

SELECT proname
FROM pg_proc proc JOIN pg_language lang
ON proc.prolang = lang.oid
WHERE lang.lanname = 'plpgsql';

• trigger

SELECT * FROM pg_trigger WHERE tgname ILIKE 'tg_%';

O PostgreSQL tem um conceito de dicionários e configurações de busca textual. Dicionários servem para mapear texto. Pode ser um mapeamento entre letras ou entre palavras inteiras. Com o mapeamento de letras, é possível buscar por "Tomás" e encontrar "TOMAS" e vice-versa; com o mapeamento de palavras, é possível buscar por "aluno" e o banco encontrar tanto "aluna" quanto "alunos". Isso é configurável.

Para o nosso caso, o que interessa é a função unaccent.

$ psql <banco> postgres             # onde <banco> é o nome do banco; tem que fazer cada banco separadamente
banco=# create extension unaccent;
CREATE EXTENSION
banco=# \dFd
...
  public     | unaccent        | 

Se a extensão já estiver instalada, o comando 'create extension' acima vai mostrar a mensagem: "ERROR: extension "unaccent" already exists".

SELECT unaccent('áéíóú');

prod=> select id_pessoa, nome_ideal from pessoa where nome_ideal ilike '%carlos jose%';     # só nomes sem acento
 id_pessoa |           nome_ideal           
-----------+--------------------------------
     42288 | Carlos Jose Valle-Diaz
     75639 | CARLOS JOSE COUTINHO NETTO
     76006 | CARLOS JOSE AMORIM DA SILVA
     78087 | CARLOS JOSE NUNES DE SOUSA
     54709 | CARLOS JOSE DE OLIVEIRA MORAES
(5 rows)

prod=> select id_pessoa, nome_ideal from pessoa where nome_ideal ilike '%carlos josé%';     # só nomes com acento
 id_pessoa |          nome_ideal           
-----------+-------------------------------
      6464 | Carlos José Nogueira
     13795 | Carlos José Guimarães Cova
     16286 | Carlos José Gomez Estevez
      4077 | Carlos José Salgado Rohner
     25280 | Carlos José de Oliveira
      3636 | Carlos José Pereira de Lucena
(6 rows)

prod=> select id_pessoa, nome_ideal from pessoa where unaccent(nome_ideal) ilike unaccent('%carlos josé%');     # nomes com e sem acento
 id_pessoa |           nome_ideal           
-----------+--------------------------------
      6464 | Carlos José Nogueira
     13795 | Carlos José Guimarães Cova
     16286 | Carlos José Gomez Estevez
      4077 | Carlos José Salgado Rohner
     42288 | Carlos Jose Valle-Diaz
     25280 | Carlos José de Oliveira
      3636 | Carlos José Pereira de Lucena
     75639 | CARLOS JOSE COUTINHO NETTO
     76006 | CARLOS JOSE AMORIM DA SILVA
     78087 | CARLOS JOSE NUNES DE SOUSA
     54709 | CARLOS JOSE DE OLIVEIRA MORAES
(11 rows)

A função cond_ilike (local do módulo bau.pessoa) recebe o nome de uma coluna, uma string fornecida pelo usuário com o que se quer procurar e o caractere-coringa (opcional). Ela retorna um trecho de SQL que insere o coringa no lugar dos espaços em branco e também retira os acentos.

cond_ilike ('nome_ideal', 'carlos jose', '&') --> "replace (unaccent (nome_ideal), ' ', '&') = replace (unaccent ('carlos jose'), ' ', '&')"

Em Lua, podemos retirar acentos usando a função sintra.string.criaremoveacentos. Ela gera uma função que remove os acentos de strings de uma dada codificação.