Artigo "Novidades do Firebird 2.5": Linguagem PSQL

Posted on in artigo, firebird

A linguagem procedural do Firebird, a PSQL, foi incrementada com novos comandos e outras melhorias. A primeira melhoria que irei apresentar se trata do uso de subqueries como expressões PSQL. Antes, o desenvolvedor era obrigado a criar uma variável e fazer um SELECT INTO para obter um valor e usá-lo posteriormente em outro comando. Agora as subqueries fazem parte do conjunto de expressões PSQL e podem ser usadas da mesma maneira que já podiam ser usadas em comandos SQL. A listagem 15 mostra o uso de uma subquery em uma comparação e em seguida na atribuição de um valor retornado a uma variável.

Listagem 15: Usando subqueries como expressões PSQL.

SET TERM !;

EXECUTE BLOCK RETURNS (COD VARCHAR(10)) AS
BEGIN
  IF ((SELECT COUNT(*) FROM DOCUMENTOS) > 3) THEN
  BEGIN
     COD = (SELECT MAX(CODIGO) FROM DOCUMENTOS);
     SUSPEND;
  END
END!

SET TERM ;!

Na listagem 15 o parâmetro de retorno COD foi definido com o tipo VARCHAR(10) e recebe um valor lido da coluna CODIGO da tabela DOCUMENTOS. Uma alteração do tipo desta coluna poderia deixar este bloco de código inconsistente ou inválido. No Firebird 2.1 foi adicionado o suporte ao uso de domains em PSQL. Neste caso, o desenvolvedor precisaria criar um domínio e usá-lo na definição da tabela e na definição do parâmetro de retorno do EXECUTE BLOCK para ter um código consistente. Dependendo da situação, a criação de domínios para todas as colunas pode se tornar uma tarefa extremamente burocrática. Pensando nisso, o Firebird 2.5 adiciona a cláusula TYPE OF COLUMN. Com essa cláusula é possível declarar parâmetros e variáveis ou fazer casts usando o tipo definido de uma coluna. Ao alterar o tipo da coluna, a alteração se reflete automaticamente em todas as stored procedures e triggers que façam uso de seu tipo. A listagem 16 mostra o mesmo código da listagem 15 mas usando TYPE OF COLUMN para o parâmetro COD.

Listagem 16: Usando a cláusula TYPE OF COLUMN.

EXECUTE BLOCK RETURNS (COD TYPE OF COLUMN DOCUMENTOS.CODIGO) AS
BEGIN
  IF ((SELECT COUNT(*) FROM DOCUMENTOS) > 3) THEN
  BEGIN
     COD = (SELECT MAX(CODIGO) FROM DOCUMENTOS);
     SUSPEND;
  END
END!

No Firebird 2.1 foram acrescentados os triggers de eventos de banco de dados, como o evento ON CONNECT. Uma das funcionalidades que podiam ser feitas com este tipo de trigger era a rejeição de uma conexão, permitindo que os desenvolvedores implementassem um sistema de segurança dentro do banco de dados. Para rejeitar uma conexão é necessário lançar uma exceção dentro do trigger. Nota: este tipo de segurança não impede que o usuário SYSDBA tenha acesso ao banco, pois os administradores podem desativar os triggers de banco de dados conectando-se usando a opção -nodbtriggers do ISQL. O problema é que alguns desenvolvedores gostariam de gravar uma entrada em uma tabela de log registrando o evento, mas a exceção causava o rollback da criação deste registro. Esta e outras situações (como comunicação com outros sistemas através de UDFs) agora podem se beneficiar de um novo comando, o IN AUTONOMOUS TRANSACTION. Assim como um IF ou WHILE, este comando aceita um subcomando (ou vários, com o uso de BEGIN END). No início da execução do IN AUTONOMOUS TRANSACTION é aberta uma nova transação e o subcomando é executado dentro desta transação. Caso o subcomando execute por completo, é feito um COMMIT na transação criada. Caso ocorra uma exceção, é feito um rollback. A listagem 17 apresenta um trigger ON CONNECT que aceita apenas conexões dos usuários SYSDBA e ADMIN e grava o nome dos outros usuários que tiverem a conexão rejeitada. Note que a exceção usada para rejeitar a conexão é lançada fora do bloco IN AUTONOMOUS TRANSACTION, que já havia terminado e, consequentemente, inserido o registro e finalizado a transação com COMMIT.

Listagem 17: Usando o comando IN AUTONOMOUS TRANSACTION.

CREATE TABLE CONN_LOG (USUARIO VARCHAR(128), DATA TIMESTAMP);

CREATE EXCEPTION E_CONN 'Acesso negado.';

SET TERM !;

CREATE TRIGGER T_CONN ON CONNECT AS
BEGIN
  IF (CURRENT_USER <> 'SYSDBA' AND CURRENT_USER <> 'ADMIN') THEN
  BEGIN
     IN AUTONOMOUS TRANSACTION DO
     BEGIN
        INSERT INTO CONN_LOG
          VALUES (CURRENT_USER, CURRENT_TIMESTAMP);
     END

     EXCEPTION E_CONN;
  END
END!

SET TERM ;!

O comando EXECUTE STATEMENT ganha algumas novidades importantes na versão 2.5. Uma delas é uma das funcionalidades mais pedidas pelos usuários: ler e atualizar dados em outros bancos de dados. Através da cláusula ON EXTERNAL DATA SOURCE é possível especificar o banco de dados em que o comando executará. É permitido também que o desenvolvedor especifique o nome, senha e role do usuário para fazer a conexão, e o funcionamento da transação iniciada para rodar o comando no banco de dados externo. Com WITH COMMON TRANSACTION (opção default), a transação da conexão externa é agrupada à transação interna atual e o comando COMMIT ou ROLLBACK executado na transação atual é propagado às transações externas. Com WITH AUTONOMOUS TRANSACTION o funcionamento se torna similar as transações autônomas, sendo feito o COMMIT ou ROLLBACK logo após a execução sucedida ou não do comando externo. A listagem 18 mostra o uso deste comando para ler e gravar dados em outro banco de dados.

Listagem 18: Executando comandos em outros bancos de dados com EXECUTE STATEMENT.

SET TERM !;

EXECUTE BLOCK RETURNS (NOME VARCHAR(60)) AS
  DECLARE BANCO VARCHAR(60) = 'localhost:/db/pessoas.fdb';
  DECLARE USUARIO VARCHAR(10) = 'SYSDBA';
  DECLARE SENHA VARCHAR(10) = 'masterke';
  DECLARE NOME_INSERIR VARCHAR(20) = 'Sicrano';
BEGIN
  FOR EXECUTE STATEMENT 'SELECT NOME FROM PESSOAS'
      ON EXTERNAL DATA SOURCE BANCO
      AS USER USUARIO PASSWORD SENHA
      INTO NOME
  DO
  BEGIN
      SUSPEND;
  END

  EXECUTE STATEMENT
    'INSERT INTO PESSOAS (NOME) VALUES (''' || NOME_INSERIR || ''')'
    ON EXTERNAL DATA SOURCE BANCO
    AS USER USUARIO PASSWORD SENHA
    WITH AUTONOMOUS TRANSACTION;
END!

SET TERM ;!

A listagem 18 mostra como era feita a criação de strings de SQL dinâmico, usando concatenação de strings. A criação de comandos com concatenação é insegura, pois um usuário mal-intencionado pode gerar comandos não previstos pelo desenvolvedor, causando o ataque conhecido como injeção de SQL [1]. No novo EXECUTE STATEMENT é possível passar parâmetros de duas formas diferentes. A listagem 19 mostra o uso de parâmetros anônimos, com o símbolo de interrogação.

Listagem 19: Usando EXECUTE STATEMENT com parâmetros anônimos.

SET TERM !;

EXECUTE BLOCK AS
  DECLARE NOME VARCHAR(20) = 'Sicrano';
  DECLARE DATA_NASCIMENTO DATE = DATE '2000-01-01';
BEGIN
  EXECUTE STATEMENT
    ('INSERT INTO PESSOAS (NOME, DATA_NASCIMENTO) VALUES (?, ?)')
    (NOME, DATA_NASCIMENTO);
END!

SET TERM ;!

Além da passagem de parâmetros anônimos, o comando EXECUTE STATEMENT agora suporta o uso de parâmetros nomeados. As vantagens dos parâmetros nomeados são que o desenvolvedor não precisa se preocupar com a ordem e também pode usar um mesmo parâmetro mais de uma vez sem a necessidade de criação de variáveis. O uso dos parâmetros dentro da string é similar a passagem de parâmetros feita com o símbolo dois-pontos em bibliotecas Delphi, como expliquei na parte referente à construção ? IS NULL. Os valores são associados aos nomes dos parâmetros usando-se o operador := (dois pontos, igual). A listagem 20 mostra o uso de parâmetros nomeados.

Listagem 20: Usando EXECUTE STATEMENT com parâmetros nomeados.

SET TERM !;

EXECUTE BLOCK AS
  DECLARE NOME VARCHAR(20) = 'Sicrano';
  DECLARE DATA_NASCIMENTO DATE = DATE '2000-01-01';
BEGIN
  EXECUTE STATEMENT
    ('INSERT INTO PESSOAS (NOME, DATA_NASCIMENTO)
        VALUES (:P_NOME, :P_DATA_NASCIMENTO)')
    (P_DATA_NASCIMENTO := DATA_NASCIMENTO, P_NOME := NOME);
END!

SET TERM ;!

Nota

[1] Injeção de SQL é o nome de um ataque em que o usuário mal-intencionado preenche dados de um formulário de forma que um comando gerado no servidor através de concatenação de strings seja alterado. Geralmente o ataque é feito usando-se aspas simples e um restante de código que termine o comando iniciado pelo desenvolvedor, seguindo com outro comando completo e o símbolo de início de comentário (--) para invalidar o restante do comando.