artigo escrito por Daiani Pavi, trata do banco de dados HSQLDB, enjoy
SubConsultas
Introdução
SubConsultas são declarações de SELECTS dentro de outras declações.
Exemplo :
SELECT * FROM table1 WHERE coluna1 = ( SELECT coluna1 FROM table2)No exemplo acima, SELECT * FROM table1 é a consulta externa, enquanto ( SELECT coluna1 FROM table2 ) é a subconsulta.
A subconsulta fica dentro de uma declaração externa, logo, uma subconsulta consiste basicamente de uma declaração de SELECT dentro de outras declarações.
As subconsultas devem sempre aparecerem dentro de parenteses.
Subconsultas podem conter outras subconsultas na sua declaração
As principais vantagens de utilizarmos subconsultas são :
• Permitem consultas que são estruturadas, com elas podemos isolar cada parte da nossa declaração.
• Elas providenciam maneiras alternativas para fazermos consultas que poderiam ser complexas ao utilizarmos joins e unions.
• Elas são mais fáceis de ler que complexos joins e unions, oque nos aproxima mais da ideia de SQL ( Structured Query Language )
Uma subconsulta pode nos retornar um único valor, uma única linha, uma única coluna, ou uma tabela.
Na sua forma simples, uma subconsulta é simplesmente um operando, ela pode ser utilizada no lugar de qualquer operando, onde requer um valor de uma coluna, de uma linha, e assim por diante.
Exemplo :
CREATE TABLE t1 (c1 int);
CREATE TABLE t2(c1 int);
INSERT INTO t1 VALUES(10);
INSERT INTO t2 VALUES(10);
SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2);
No exemplo acima a subconsulta é utilizada como um operando que retorna um único valor para a declaração externa.
A subconsulta retorna 10, fazendo com que a declaração externa fique igual a :
SELECT * FROM t1 WHERE c1 = 10;
Comparações com SubConsultas
Geralmente as comparações com subconsultas tem a seguinte forma :
operando operador_comparação ( subconsulta )
exemplo :
operando( SELECT * FROM t1 ) operador_comparação ( WHERE t1 = ) subconsulta ( ( SELECT * FROM t2 ) )
Os operadores de comparação podem ser:
= > < >= <= <>
Com subconsultas podemos fazer algumas coisas que não podemos com JOINS, porque JOINS nâo trabalham com agregações de colunas.
exemplo :
SELECT c1 FROM t1 WHERE c1 = (SELECT SUM(c1) FROM t2);
no exemplo SELECT retorna os valores em que a coluna c1 da tabela t1 é igual à soma de todos valores da coluna c1 da tabela t2.
SubConsultas com ANY, IN, e SOME
Sintaxe :
operando operador_comparação ANY ( subconsulta )
operando IN ( subconsulta )
operando operador_comparação SOME ( subconsulta )
O predicado ANY deve ser utilizado após um operador de comparação, ele significa "retorne TRUE se qualquer comparação da declaração externa for TRUE com o resultado da subconsulta".
por exemplo :
SELECT c1 FROM t1 WHERE c1 > ANY (SELECT c1 FROM t2);
Supondo que os valores da coluna c1 da tabela t2 sejam 30, 20, e 10, enquanto o da coluna c1 da tabela t1 seja 15.
A operação retornar o valor 15, porque ele é um valor da declaração externa que retornou TRUE quando comparado com ANY (qualquer) valor da subconsulta.
Isso acontece porque ele foi comparado ( também ) com o valor 10, e 15 > 10.
O predicado IN é um alias para " = ANY", mas apenas quando utilizado com subconsultas.
IN é utilizado para comparar uma consulta com uma lista
por exemplo:
SELECT c1 FROM t1 WHERE c1 = ANY (SELECT c1 FROM t2);
SELECT c1 FROM t1 WHERE c1 IN (SELECT c1 FROM t2); retornam o mesmo resultado, mas o operador IN também pode ser utilizado para procurar valores em listas
exemplo :
SELECT c1 FROM t1 WHERE c1 IN (10, 20, 30);
temos que observar que NOT INT não é um alias para <> ANY, mas sim para <> ALL, nos tópicos seguintes veremos um pouco sobre subconsultas com ALL.
SOME é um alias para ANY, portanto as duas declarações seguintes são equivalentes
SELECT c1 FROM t1 WHERE c1 <> ANY (SELECT c1 FROM t2);
SELECT c1 FROM t1 WHERE c1 <> SOME (SELECT c1 FROM t2);para algumas pessoas, é mais fácil entender o significado de SOME do que ANY.
SubConsultas com ALL
A palavra ALL significa "retorne TRUE se o valor da declaração externa for TRUE para todos os valores dos resultados da subconsulta".
exemplo :
SELECT c1 FROM t1 WHERE c1 > ALL (SELECT c1 FROM t2);o exemplo acima retornará os valores de t1 que são maiores do que ALL ( todos ) resultados da subconsulta.
NOT INT é um alias para <> ALL, assim as duas declarações seguintes são iguais :
SELECT c1 FROM t1 WHERE c1 <> ALL (SELECT c1 FROM t2);
SELECT c1 FROM t1 WHERE c1 NOT IN (SELECT c1 FROM t2);
SubConsultas de Linhas
Até agora, tratamos de subconsultas com valores de uma única coluna, ou valores únicos, mas também podemos utilizar valores de várias colunas.
Infelizmente o HSQDB não dá um suporte especial à SubConsultas de Linhas, porém explicarei o necessário para que possam ter um entendimento do que se trata.
As declarações seguintes foram testadas no MySQL 5.0.45.
Uma subconsulta de linhas é uma subconsulta que retorna valores de uma linha ( ROW ) inteira, podendo retornar vários valores.
Exemplos :
SELECT c1,c2 FROM t1 WHERE (c1,c2) = (SELECT c1, c2 FROM t2);
SELECT * FROM t1 WHERE (c1,c2) = (1,1);
SELECT * FROM t1 WHERE c1 = 1 AND c2 = 1;
EXISTS e NOT EXISTS
Se uma subconsulta tiver como resultado qualquer linha, EXISTS retorna TRUE, e NOT EXISTS retorna false.
EXISTS verifica se existe um valor de retorno, e NOT EXISTS verifica se não houve um valor de retorno.
Exemplos :
SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2 );no exemplo acima, SELECT * FROM t1 só é executado se EXISTS resultado da subconsulta.
SELECT * FROM t1 WHERE NOT EXISTS ( SELECT * FROM t2 );neste, SELECT * FROM t1 só é executado se NOT EXISTS ( não existe ) valor de retorno da subconsulta.
SubConsultas Correlativas
Uma subconsulta correlativa, é uma subconsulta que contem referência para a tabela da declaração externa.
Segue um exemplo em que a declaração externa retornará os valores da t1 onde c1 é igual a qualquer um dos resultados da subconsulta.
SELECT * FROM t1 WHERE c1 = ANY (SELECT * FROM t2.c1 WHERE t2.c1 = t1.c1);Podemos observer que a subconsulta faz uma referência à coluna c1 da tabela t1, mesmo com a tabela t1 não sendo utilizada explicitamente na clausura FROM da subconsulta, por isso, o HSQLDB procura a tabela t1 na declaração externa, no nosso caso, em SELECT * FROM t1...
Para uma explicação detalhada, vamos fazer uma experiência.
Primeiro criamos as tabelas t1 e t2, ambas com uma coluna c1.
CREATE TABLE t1(c1 INT);
CREATE TABLE t2(c1 INT);
em seguida vamos inserir o valor 10 nas tabelas.
INSERT INTO t1 VALUES(10);
INSERT INTO t2 VALUES(10);Agora fazemos :
SELECT c1 FROM t1 WHERE c1 = ANY ( SELECT c1 FROM t2 WHERE c1 = t1.c1);O retorno será 10.
Oque acontece é o seguinte :
1. O HSQLDB obtém o valor de SELECT c1 FROM t2, que é 10.
2. Em seguida, o HSQL tenta selecionar um valor da coluna c1 da tabela t1, que for igual a 10
3. Ele não consegue achar o valor da coluna c1 da tabela t1, porque não existe nenhuma declaração na subconsulta que pegou esse valor, então ele procura esse valor na declaração externa.
4. Chegando lá, ele encontra o valor que foi pego com a declaração externa SELECT c1 FROM t1 que retorna o valor 10.
5. Com esse valor em memória, ele pode continuar tentando analisar a comparação da subconsulta, que no caso podemos traduzir para SELECT c1 FROM t2 WHERE 10 = 10. O resultado da comparação é TRUE, 10 é igual a 10, ou melhor, a coluna c1 da tabela t2 é igual à coluna c1 da tabela t1, logo, o valor da coluna c1 é retornado, já que ele atende a exigência ( ser igual ao valor da coluna c1 da tabela t1 ).
6. Agora que o HSQL já conseguiu analisar a subconsulta, ele resolverá a declaração externa. O valor de retorno da subconsulta foi 10 (t2.c1), então ele quer saber o seguinte :
SELECT c1 FROM t1 WHERE c1 = 10, como o valor da coluna c1 da tabela t1 é 10, ela atende as exigências da comparação, logo, seu valor é o resultado da consulta.
Controle de acesso
Criação de usuários
No HSQLDB o controle é baseado em banco de dados, e não em servidor.
Temos um usuário chamado de DBA ( Data Base Administrator ) que é automaticamente iniciado quando criamos um banco de dados, o nome dele é SA e a sua senha padrão é uma string vazia.
Apenas os DBAs podem criar banco de dados, eles tem privilégios de root, fazem qualquer coisa.
As contas que são criadas sem privilégios de DBAs ou ADMIN inicialmente não tem nenhuma permissão, é necessário que o DBA a configure de acordo com as necessidades, concedendo privilégios com o comando GRANT.
Para trocarmos a senha de uma conta, podemos utilizar o comando SET PASSWORD "senha".
Podemos criar contas de administradores de duas maneiras, a primeira é adicionando a opção ADMIN no comando CREATE USER, exemplo :
CREATE USER usuário PASSWORD senha ADMIN;A segunda é criarmos o usuário com o comando CREATE USER, e após isso, dar privilégio com o comando GRANT, exemplo :
CREATE USER usuário PASSWORD senha;
GRANT DBA TO usuário;
Por questões de segurança, quando iniciarmos o banco de dados com a conta SA, devemos escolher uma senha, de preferência que não exista no dicionário e com letras e números, porque há muitos programas de brute-force que contem todo o dicionário na sua word-list e eles podem quebrar senhas fáceis em minutos.
Criação de esquema
Sintaxe
CREATE SCHEMA <schemaname> AUTHORIZATION <grantee>
[<createStatement> [<grantStatement>] [...];Cria um esquema com o proprietário especificado em authorization. O privilégio de authorization pode ser um usuário do banco de dados ou uma regra.
Podemos usar opcionalmente os comandos CREATE e GRANT apenas para novos objetos no esquema, exemplo :
CREATE SCHEMA ACCOUNTS AUTHORIZATION DBA
CREATE TABLE AB(A INTEGER)
CREATE TABLE CD(C CHAR)
GRANT SELECT ON AB TO test1;
No exemplo acima devemos observar que apenas a ultima declaração de SQL deve terminar com ponto e virgula ( ; ), porque o primeiro ponto e virgula que for encontrado após o CREATE SCHEMA terminará a declaração do esquema.
Permissões de leitura, gravação e exclusão
Os usuários DBA tem privilégios para tudo, por isso é aconselhado apenas para funções administrativas.
No uso comum de funções, como INSERT e SELECT, devemos criar um usuário apropriado, porque se acontecer uma invasão no sistema, com estes comandos os crackers estarão limitados.
Para criarmos usuários comuns utilizamos o comando CREATE USER, exemplo :
CREATE USER usuário PASSWORD senha;
Inicialmente esse usuário não terá privilégios, para isso, devemos dar permissões com o comando GRANT, exemplo :
GRANT insert, select ON t1 TO marcelo
No exemplo acima foi dado permissão para INSERT e SELECT na tabela t1 para o usuário marcelo.
Como no HSQLDB o controle é orientado a banco de dados e não a servidor, não podemos utilizar por exemplo o seguinte comando :
GRANT insert, select,delete ON databaseX.* TO cracker
O comando acima tenta dar permissão a um cracker no banco de dados databaseX.
Supondo que um cracker obtenha acesso indevido a um usuário DBA no banco de dados chamado PRODUTOS, ele descobre através de um script em PHP, que há um banco de dados chamado CC ( cartões de crédito ), então ele continua verificando o código desse script e consegue achar o usuário e a senha que foram utilizados para se conectar a esse banco de dados.
Como ele está atrás de informações valiosas, logo se conecta ao banco de dados CC.
Depois da conexão ele descobre que não tem permissão para visualizar as tabelas com os dados, e muito menos para fazer um backup.
Então ele pensa "vou utilizar a conta de DBA que tenho acesso no banco de dados PRODUTOS para dar permissões a esse usuário do banco de dados CC", agora que ele executou o comando
GRANT insert, select,delete ON databaseX.* TO crackerEle conseguiu permissões para visualizar a tabela com o número dos cartões de crédito de uma famosa empresa vendedora de produtos pela internet.
É por causa da ficção acima que o comando GRANT não pode ser utilizado para dar permissões a outros banco de dados, isso comprometeria a segurança.
Apesar do comando GRANT não poder ser utilizado, o cracker poderia utilizar um sniffer, onde os dados enviados pelo cliente para o servidor seriam capturados, por isso a segurança sempre deve ser levada em conta, não apenas no banco de dados, mas também em todos os aplicativos e hardware.
O HSQLDB tem algumas propriedades não muito confiáveis, pelo fato de poderem ser modificadas por comandos SQL, como é o caso das modificações que são feitas na memória serem gravadas em logs, podemos especificar o tempo através do comando
SET WRITE_DELAY {{TRUE | FALSE} | <seconds> | <milliseconds>
Com o HSQL podemos utilizar mecanismos de criptografia como SSL, TLS, entre outros.
Segurança é um assunto muito extenso, e não é o principal foco do nosso trabalho.
O tópico Referências terá alguns sites de segurança onde o assunto se estende.
Stored Procedures / Functions
No HSQLDB, Stored Procedures e Functions são métodos estáticos em Java que são chamados pela linguagem SQL.
Eles podem ser chamados direto, ou indiretamente utilizando ALIAS ,e é necessário que o método seja acessível ao banco de dados. A sintaxe é :
"java.lang.Math.sqrt"(25)
Isso significa que o método deve estar entre aspa duplas.
Podemos criar um ALIAS com o comando CREATE ALIAS:
CREATE ALIAS SQRT FOR "java.lang.Math.sqrt"
Depois do alias ser definido podemos utiliza-lo para chamar a função.
Com um ALIAS para java.lang.Math.sqrt chamado SQRT podemos fazer o seguinte :
SELECT SQRT(25) FROM t1;
Que é o mesmo que fazer SELECT 5 FROM t1, pois o método sqrt no java nos retorna a raiz quadrada de um número, e a raiz quadrada de 25 é 5.
Apenas métodos estáticos podem ser utilizados como Stored Procedures / Functions, se dentro da mesma classe existir métodos sobrecarregados com o mesmo número de argumentos, então o primeiro encontrado será utilizado pelo banco de dados.
No HSQLDB temos acesso a milhares de built-in functions e stored procedures que estão divididas em categorias como numérica, string,data/time,system/connection, e podem ser encontradas na documentação.
Triggers
Triggers são classes que seram chamadas quando ocorrer determinada operação no banco de dados, segue um exemplo para criação de Trigger :
CREATE TRIGGER <name> {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON <table>
[FOR EACH ROW] [QUEUE n] [NOWAIT] CALL <TriggerClass>;
TriggerClass é uma classe que implementa a interface org.hsqldb.Trigger, e que tenha um método chamado fire. Podemos dar qualquer nome a essa classe.
Quando ocorrer algum evento que ative o trigger no banco de dados, ele chamará o método fire da classe TriggerClass passando os seguintes argumentos :
public void fire(int type,
java.lang.String trigName,
java.lang.String tabName,
java.lang.Object[] oldRow,
java.lang.Object[] newRow)
onde oldRow e newRow representam o status antes e depois de uma row que foi modificada. Cada coluna da row será um membro do array.
Quando UPDATE triggers forem fired, oldRow conterá o valor que existia na coluna, e newRow o novo valor.
Em INSERT triggers, oldRow conterá o valor null e newRow a row em que o valor será inserido.
No DELETE triggers, newRow é null e oldRow contém a linha que será deletada.
Significados:
trigName – O nome do trigger
tabName – O nome da tabela sobre qual o trigger está ocorrendo.
oldRow – A linha antiga
newRow – A nova linha
Se um método do trigger quiser acessar o banco de dados, ele deve estabelecer sua própria conexão com o banco de dados. Isso pode causar inconsistência de dados e outros problemas, assim não é recomendado. O jdbc:default:connection:URL atualmente não está suportado.
Se QUEUE for especificado como 0, o método fire será executado na mesma thread que a engine do banco de dados. Isso permite que o trigger altere dados que serão inseridos no banco de dados. Os dados podem ser checados e modificados em BEFORE INSERT / UPDATE + FOR EACH ROW triggers.
Se um valor inválido for inserido na tabela pelo trigger, essa ação não será checada e resultará em dados inconsistentes na tabela.
Se um trigger for utilizado para fazer comucações externas, e não para checar e validar dados, QUEUE pode ser maior que zero. Isso pode ser bom, uma vez que diferentes triggers serão executados em diferentes threads e aguardaram que seus eventos ocorram, sem ter que bloquear a thread main do banco de dados. Existe uma fila de eventos esperando para serem executados por cada thread, isso é particulamente bom para "FOR EACH ROW" triggers, onde um grande número de triggers ocorrem em sucessões rápidas, sem alguma thread ganhar chance para ser executada.
Se a QUEUE ficar cheia, adições subseqüentes podem causar que o HSQLDB suspenda a espera por espaços na fila, é bom tomar cuidado com isso porque pode acontecer um deadlock. Isso pode ser evitado verificando se o parâmetro QUEUE tem um tamanho suportado pelo banco de dados, ou usando o parâmetro NOWAIT que faz com que um novo evento de triggers sobreescreva o evento mais recente que aconteceu na fila.
O valor default da fila é de 1024.
Referências
Hsqldb User Guide by Blaine Simpson, Fred Toussi
http://hsqldb.org/web/hsqlDocsFrame.html (http://hsqldb.org/web/hsqlDocsFrame.html)
The MySQL Access Privilege System
http://dev.mysql.com/doc/refman/5.0/en/ ... ystem.html (http://dev.mysql.com/doc/refman/5.0/en/privilege-system.html)
Guide.pdf of 1.8.0.9 version
Trigger Sample by Peter Hudson - http://hsqldb.org/doc/src/org/hsqldb/sa ... ample.html (http://hsqldb.org/doc/src/org/hsqldb/sample/TriggerSample.html)