*/

sexta-feira, 2 de dezembro de 2011

Configurando SyntaxHighlighter no Blogspot, ou usando cores para sintaxe de linguagens e scripts no seu Blogger


Configurando SyntaxHighlighter no Blogspot, ou usando cores para sintaxe de linguagens e scripts no seu Blogger.

Uma das coisas legais para quem é blogueiro principalmente com o Blogger da Google é você usar scripts com cores dos
comandos destacados, principamente linguagens e scripts.

Esse artigo se propõe a ensinar a dica de como configurar isso no Blogger (Blogspot) da Google, para tal usamos um artefato, framework, chamado
SyntaxHighlighter em http://alexgorbatchev.com/SyntaxHighlighter/ desenvolvido em JavaScript por Alex Gorbatchev

Aqui vai o passo a passo para configurar o SyntaxHighlighter no seu blog da Blogger.



No seu Blogspot ou Blogger da Google, acesse a guia [Design] clicar no item [Editar HTML]

Por segurança clicar no item [Baixar modelo completo] antes de fazer qualquer alteração no modelo.

Já com uma cópia do seu template feito, acesse o campo [Editar Modelo], e encontre o texto </head> tag de fechamento.
Antes dessa tag, adicione o seguinte:

<!-- inicio configuracao do Syntax Highlighting -->
<link href='http://alexgorbatchev.com/pub/sh/current/styles/shCore.css' rel='stylesheet' type='text/css'/>
<!-- configuracao do tema, css usado, default, branco, inicio -->
<link href='http://alexgorbatchev.com/pub/sh/current/styles/shThemeDefault.css' rel='stylesheet' type='text/css'/>
<!-- configuracao do tema, css usado, default, branco, fim -->
<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shCore.js' type='text/javascript'/>

<!-- adicionando Script para SyntaxHighlighter --> 
<script type='text/javascript'>
  SyntaxHighlighter.config.bloggerMode = true;
  SyntaxHighlighter.all();
</script>

<!-- configuracao de linguagens usadas, inicio -->
<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushJScript.js' type='text/javascript'/>
<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushBash.js' type='text/javascript'/>
<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushSql.js' type='text/javascript'/>
<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushXml.js' type='text/javascript'/>
<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushCpp.js' type='text/javascript'/>
<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushJava.js' type='text/javascript'/>
<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushPhp.js' type='text/javascript'/>
<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushPerl.js' type='text/javascript'/>
<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushDelphi.js' type='text/javascript'/>
<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushPlain.js' type='text/javascript'/>
<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushDiff.js' type='text/javascript'/>
<script src='http://alexgorbatchev.com/pub/sh/current/scripts/shBrushVb.js' type='text/javascript'/>
<!-- configuracao de linguagens usadas, fim -->

<!-- fim configuracao do Syntax Highlighting -->

Um breve comentário do que foi adicionado ao modelo original ...

A partir do comentário [<!-- configuracao do tema, css usado, default, branco, inicio -->]
Nesse caso é o tema padrão (default) branco, mas tem vários temas a ser visto no site do SyntaxHighlighter em http://alexgorbatchev.com/SyntaxHighlighter/manual/themes/
No meu blog eu o uso um tema cinza preto, estilo console do linux, no qual seria este:
<link href='http://alexgorbatchev.com/pub/sh/current/styles/shThemeFadeToGrey.css'/>

A partir do comentário [<!-- configuracao de linguagens usadas, inicio -->]
Adicione as linguagens que você planeja usar, no seu blog.

Por exemplo, estou habilitando as seguintes linguagens ou scripts (brushes ou escovas):
JavaScript, Bash, SQL, XML/HTML, C++, Java, PHP, Perl, Delphi, Plain, Diff, Vb.

Salve o seu modelo modificado, clicando em [SALVAR MODELO].

Agora você está habilitado para adicionar destaque de sintaxe para o seu blog.
Envolva seu código em uma tag <pre> e especifique a classe brush, utilizando o atributo de classe a linguagem ou script.
Por exemplo, para destacar um bloco de SQL:

<pre class="brush:sql">

SELECT *
FROM usuario
WHERE id_usuario = 777;

</pre>


Veja como aparece, quando formatado usando o SyntaxHighlighter:

SELECT * 
   FROM usuario
  WHERE id_usuario = 777;
 

O código que você está formatando deve ser HTML, já que o framework foi desenvolvido em JavaScript.

Espero ter ajudado, APSJ!

sábado, 26 de novembro de 2011

Remover acentos em SQL SERVER 2005 / 2008 / 2012


APDSJ!

Função (store function) para remover acentos em SQL Server 2005, SQL Server 2008 e SQL Server 2012.

Funciona para maiúsculas e minúsculas e com banco de dados em latin1, mas pode ser adaptada facilmente para outro encoding.

Espero ter ajudado!


IF EXISTS (
            SELECT * 
              FROM sys.objects 
             WHERE object_id = OBJECT_ID(N'[dbo].[usf_rm_accent_pt_latin1]') 
               AND type IN (N'FN')
           )
 DROP FUNCTION usf_rm_accent_pt_latin1;
GO
CREATE FUNCTION usf_rm_accent_pt_latin1 (@txt varchar(max)) RETURNS varchar(max) 
-- Nome Artefato/Programa..: usf_rm_accent_pt_latin1.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com) 
-- Data Inicio ............: 26/11/2011
-- Data Atualizacao........: 08/10/2012
-- Versao..................: 0.03
-- Compilador/Interpretador: T-SQL (Transact SQL) 
-- Sistemas Operacionais...: Windows
-- SGBD....................: MS SQL Server 2005/2008/2012
-- Kernel..................: Nao informado!
-- Finalidade..............: store function para remover acentos maiúsculos e minúsculos em latin1 pt
-- OBS.....................: só funciona para banco configurado com o collate latin1, para saber qual o collate do seu banco execute essa query 
-- ........................: SELECT DatabasePropertyEx(db_name(),'Collation');
--
BEGIN
DECLARE @txt0 varchar(max) 
 --caixa baixa
    SET @txt0 = replace(@txt COLLATE Latin1_General_BIN, char(225),'a')  --SELECT 'á',ASCII('á'); --225
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(224),'a')  --SELECT 'à',ASCII('à'); --224   
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(227),'a')  --SELECT 'ã',ASCII('ã'); --227 
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(226),'a')  --SELECT 'â',ASCII('â'); --226
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(233),'e')  --SELECT 'é',ASCII('é'); --233 
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(232),'e')  --SELECT 'è',ASCII('è'); --232
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(234),'e')  --SELECT 'ê',ASCII('ê'); --234
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(237),'i')  --SELECT 'í',ASCII('í'); --237 
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(236),'i')  --SELECT 'ì',ASCII('ì'); --236
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(238),'i')  --SELECT 'î',ASCII('î'); --238 
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(243),'o')  --SELECT 'ó',ASCII('ó'); --243
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(242),'o')  --SELECT 'ò',ASCII('ò'); --242
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(244),'o')  --SELECT 'ô',ASCII('ô'); --244
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(245),'o')  --SELECT 'õ',ASCII('õ'); --245
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(250),'u')  --SELECT 'ú',ASCII('ú'); --250
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(249),'u')  --SELECT 'ù',ASCII('ù'); --249
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(251),'u')  --SELECT 'û',ASCII('û'); --251 
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(252),'u')  --SELECT 'ü',ASCII('ü'); --252 
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(231),'ç')  --SELECT 'ç',ASCII('ç'); --231
 -- caixa alta
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(193),'A')  --SELECT 'Á',ASCII('Á'); --193
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(192),'A')  --SELECT 'À',ASCII('À'); --192  
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(194),'A')  --SELECT 'Â',ASCII('Â'); --194
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(195),'A')  --SELECT 'Ã',ASCII('Ã'); --195
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(201),'E')  --SELECT 'É',ASCII('É'); --201
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(200),'E')  --SELECT 'È',ASCII('È'); --200
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(202),'E')  --SELECT 'Ê',ASCII('Ê'); --202
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(205),'I')  --SELECT 'Í',ASCII('Í'); --205
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(204),'I')  --SELECT 'Ì',ASCII('Ì'); --204
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(206),'I')  --SELECT 'Î',ASCII('Î'); --206
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(211),'O')  --SELECT 'Ó',ASCII('Ó'); --211
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(210),'O')  --SELECT 'Ò',ASCII('Ò'); --210
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(212),'O')  --SELECT 'Ô',ASCII('Ô'); --212
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(213),'O')  --SELECT 'Õ',ASCII('Õ'); --213
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(218),'U')  --SELECT 'Ú',ASCII('Ú'); --218
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(217),'U')  --SELECT 'Ù',ASCII('Ù'); --217
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(219),'U')  --SELECT 'Û',ASCII('Û'); --219 
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(220),'U')  --SELECT 'Ü',ASCII('Ü'); --220
    SET @txt0 = replace(@txt0 COLLATE Latin1_General_BIN,char(199),'C')  --SELECT 'Ç',ASCII('Ç'); --199
     
 RETURN (@txt0)
END
GO
    
-- Exemplo de uso da function

-- Ex.: 01 - Removendo acentos nas consultas
-- SELECT dbo.usf_rm_accent_pt_latin1('café, Sofá, pé,caÇa, CAÇA, aviões, sapê, à, sítio, Avião, LÂMPADA, mesa, cadeira, AVIÕES');  
-- Retorno: cafe, Sofa, pe,caCa, CACA, avioes, sape, a, sitio, Aviao, LAMPADA, mesa, cadeira, AVIOES


-- Ex.: 02 - Removendo acentos no campo obs da tabela1 
-- UPDATE tabela1 SET obs = dbo.usf_rm_accent_pt_latin1(obs); 
--





O Peregrino agradece o acesso, tenha um bom trabalho!
:)

domingo, 20 de novembro de 2011

Como Validar Código de Barras GTIN, antigo EAN13 no SQL SERVER 2008


Como Validar Código de Barras GTIN, antigo EAN13 no SQL SERVER 2008

Com as exigências da SEFAZ, no tocante a Nota Fiscal Eletrônica, tornou-se uma exigência do Governo Federal Brasileiro o uso do código de barras EAN13 ou GTIN (Global Trade Item Number), válido.

Então segue uma store function desenvolvida em T-SQL do SQL Server 2008, para validar esse troço.

Depois vai ser o chip na mão e na testa ... Espero está na Glória com Cristo, para não vê o resultado final... 666 tá chegando...

Mas, enquanto não chega, segue uma store function para aliviar o stress de quem trabalhar com isso...

-- Nome Artefato/Programa..: sp_valida_ean13.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com) 
-- Data Inicio ............: 20/11/2011
-- Data Atualizacao........: 22/11/2011
-- Versao..................: 0.01
-- Compilador/Interpretador: T-SQL (Transact SQL) 
-- Sistemas Operacionais...: Windows
-- SGBD....................: MS SQL Server 2005/2008
-- Kernel..................: Nao informado!
-- Finalidade..............: store procedure (function) para testa se um um código de barras (EAN13-GTIN) é valido ou não, retorna 1 para verdadeiro e 0 para falso 
-- OBS.....................: 
--

IF EXISTS (
            SELECT * 
              FROM sys.objects 
             WHERE object_id = OBJECT_ID(N'[dbo].[sp_valida_ean13]') 
               AND type IN (N'FN')
           )
 DROP FUNCTION sp_valida_ean13;
GO
CREATE FUNCTION sp_valida_ean13(@number varchar(max)) RETURNS smallint AS
BEGIN
 DECLARE @c bigint
 DECLARE @r smallint
 DECLARE @i smallint
 DECLARE @x smallint
 DECLARE @sum smallint
 DECLARE @mult smallint
 DECLARE @number_split varchar(1)
 SET @x   = 0
 SET @sum = 0 
 SET @i   = 1 
 SET @c   = len(@number)

 -- se cb for maior que 13 
 IF @c > 13 BEGIN
    SET @r = 0
 END

 -- se cb for menor que 13
 IF @c < 13 BEGIN
    SET @r = 0
 END

 --se for nulo 
 IF @number IS NULL BEGIN
    SET @r = NULL  
 
 END ELSE BEGIN

    --se não for nulo pode ser válido 
    SET @x = (SELECT CASE WHEN patindex('%[^0-9]%', @number) > 0 THEN 0 ELSE 1 END) 
 
    IF @c = 13 AND @x = 1  BEGIN

       WHILE @i<=(@c-1) BEGIN

          --fazendo um split do cb 
          SET @number_split=substring (@number,@i,1)
          --multiplicam-se os digitos do cb por 1 e por 3, em sequencia repetitiva de 1 e 3;
          IF (@i % 2) = 0 BEGIN
             SET @sum = @sum + cast(@number_split as int) * 3  
          END ELSE BEGIN
             SET @sum = @sum + cast(@number_split as int) * 1  
          END
          --incremento
          SET @i=@i+1

       END

       --com a soma do resultado das multiplicacoes...
       --sera necessario encontrar o multiplo de 10 mais proximo do resultado da soma. 
       --atencao: o multiplo de 10 deve ser maior ou igual ao resultado da soma (nunca menor).  
       SET @mult = ( ( 1 + cast(@sum / 10 as int) ) * 10 ) 
       --subtraindo o resultado da soma do multiplo que foi encontrado
       SET @mult = @mult - @sum 
       --o resultado desta subtracao será o digito verificador
       --entao se o ultimo digito do cb, isto e, 
       --13o numero for igual ao resultado desta subtracao entao e um cb valido

       IF cast(substring(@number,13,1) as int) = @mult BEGIN
          SET @r = 1
       END ELSE BEGIN
          SET @r = 0
       END   

    END ELSE BEGIN
       SET @r = 0 
    END    
 
 END --fim_se
 
 RETURN (@r)

END;
GO



-- Exemplo de Uso: 

-- Exemplo: 01, uso simples
-- O EAN13 impresso na Lata de Leite Ninho 400g
-- SELECT dbo.sp_valida_ean13('7891000142202');   --retorna 1, pois é valido 

-- Exemplo: 02, descobrir todos os produtos com EAN13 ou GTIN inválido 
-- SELECT * FROM produto WHERE dbo.sp_valida_ean13(gtim)=0; 
-- GO

Querys Recursivas no SQL Server

Querys Recursivas no SQL Server



Segue um exemplo prático de como fazer querys recursivas no SQL Server 2005/2008, usando genealogia.

O conceito de query recursiva é montar o resultado por níveis, identificando quem são as ocorrências "pais", depois "os filhos de primeiro nível", em seguida, "os filhos de segundo nível" e por conseguinte, mergulhando até encontrar, o último nível.

O Algorítmo da Query Recursiva é o seguinte:
Passo 1 - Encontrar todas as ocorrências de nível 1, o qual são chamados de ocorrências âncora.
Passo 2 - Baseado nas âncoras, identificar e selecionar todas as ocorrências de nível 2, recursividade
Passo 3 - Já em um processo recursivo, tomando por base o nível 2, identificar e selecionar todas as ocorrências de nível 3 e assim sucessivamente até chegar ao último nível.

Obs: Use esse recurso com moderação, pois quanto mais níveis, mais depreciado tende a ser o desempenho desta query.

Segue script para teste na prática de como fazer:
--
-- Nome Artefato/Programa..: querys_recursivas_no_sqlserver_2008 
-- Empresa.................: 
-- Autor(es)...............: Emerson Hermann (emersonhermann at gmail.com) http://emersonhermann.blogspot.com
-- Data Inicio ............: 19/11/2011
-- Data Atual..............: 19/11/2011
-- Versao..................: 0.01
-- Compilador/Interpretador: SQL Server 2008 
-- Sistemas Operacionais...: Windows
-- SGBD....................: SQL Server 2005/2008 R2
-- Kernel..................: Nao informado!
-- Finalidade..............: Usar querys recursivas no Sql Server 2008  
-- ........................: 
-- OBS.....................: 
--
 
 
/* testando no SQL Server 2008*/
 
--DROP TABLE genealogia;
CREATE TABLE genealogia
(
    id_genealogia integer     PRIMARY KEY
  , nome varchar(25)         NOT NULL
  , id_genealogia_pai integer NULL FOREIGN KEY REFERENCES genealogia(id_genealogia)
 
);
 
--TRUNCATE TABLE genealogia;
 
SELECT * FROM genealogia;
 
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (1,'ABRAÃO',NULL);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (2,'ISAC',1);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (3,'ESAÚ',2);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (4,'JACÓ',2);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (5,'RÚBEN',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (6,'SIMEÃO',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (7,'LEVI',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (8,'JUDÁ',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (9,'ISSACAR',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (10,'ZEBULON',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (11,'JOSÉ',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (12,'BENJAMIM',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (13,'DÃ',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (14,'NAFTALI',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (15,'GADE',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (16,'ASER',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (17,'DINÁ',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (18,'PEREZ',8);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (19,'ZERA',8);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (20,'ESRON',18);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (21,'ARÃO ',20);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (22,'AMINADABE',21);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (23,'NASSON',22);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (24,'SALMON',23);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (25,'BOAZ',24);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (26,'OBEDE',25);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (27,'JESSÉ',26);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (28,'DAVI',27);
 
SELECT * FROM genealogia;
 
 
--query 1, AUTO RELACIONAMENTO
 
            SELECT g1.nome
                 , g1.id_genealogia
                 , g2.id_genealogia_pai 
              FROM genealogia g1
         LEFT JOIN genealogia g2 
                ON g1.id_genealogia = g2.id_genealogia_pai 
                 ;   
 
--query 2, RECURSIVA 
 
WITH cte_recursiva (id_genealogia,nome,nivel,arvore)
AS
(
    -- 1a PARTE ANCORA
    SELECT id_genealogia
         , nome
         , 1 AS nivel
         , CAST(nome AS VARCHAR(255)) AS arvore 
      FROM genealogia
     WHERE id_genealogia_pai IS NULL
    
    UNION ALL
    
    -- 2a PARTE RECURSIVA
    SELECT g.id_genealogia
         , g.nome
         , c.nivel + 1 AS nivel
         , CAST((c.arvore + '/' + g.nome) AS VARCHAR(255)) AS arvore 
      FROM genealogia g
INNER JOIN cte_recursiva c 
        ON g.id_genealogia_pai = c.id_genealogia
    
)
SELECT nivel, arvore FROM cte_recursiva;




Sem stress...

Querys Recursivas no Postgres


Querys Recursivas no Postgres

Segue um exemplo prático de como fazer querys recursivas no Postgres, usando genealogia.

O conceito de query recursiva é montar o resultado por níveis, identificando quem são as ocorrências "pais", depois os "os filhos de primeiro nível", em seguida, "os filhos de segundo nível" e por conseguinte, mergulhando até encontrar, o último nível.

O Algorítmo da Query Recursiva é o seguinte:
Passo 1 - Encontrar todas as ocorrências de nível 1, o qual são chamados de ocorrências âncora.
Passo 2 - Baseado nas âncoras, identificar e selecionar todas as ocorrências de nível 2, recursividade
Passo 3 - Já em um processo recursivo, tomando por base o nível 2, identificar e selecionar todas as ocorrências de nível 3 e assim sucessivamente até chegar ao último nível.

O Modo como o Postgres, processa querys recursivas é bem semelhante ao do SGBDR SQL Server.

Obs: Use esse recurso com moderação, pois quanto mais níveis, mais depreciado tende a ser o desempenho desta query.

Segue script para teste na prática de como fazer:

--
-- Nome Artefato/Programa..: querys_recursivas_no_postgres 
-- Empresa.................: 
-- Autor(es)...............: Emerson Hermann (emersonhermann at gmail.com) http://emersonhermann.blogspot.com
-- Data Inicio ............: 19/11/2011
-- Data Atual..............: 19/11/2011
-- Versao..................: 0.01
-- Compilador/Interpretador: Postgres
-- Sistemas Operacionais...: Linux/Windows
-- SGBD....................: Postgres 8.4/9.0/9.1
-- Kernel..................: Nao informado!
-- Finalidade..............: Usar querys recursivas no Postgres 
-- ........................: 
-- OBS.....................: 
--
 
 
/* testando no Postgres 8.4/9.0/9.1 */
 
--DROP TABLE IF EXISTS genealogia;
CREATE TABLE genealogia
(
    id_genealogia integer     PRIMARY KEY
  , nome varchar(25)         NOT NULL
  , id_genealogia_pai integer NULL REFERENCES genealogia(id_genealogia)
 
);
 
--TRUNCATE TABLE genealogia;
 
SELECT * FROM genealogia;
 
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (1,'ABRAÃO',NULL);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (2,'ISAC',1);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (3,'ESAÚ',2);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (4,'JACÓ',2);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (5,'RÚBEN',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (6,'SIMEÃO',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (7,'LEVI',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (8,'JUDÁ',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (9,'ISSACAR',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (10,'ZEBULON',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (11,'JOSÉ',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (12,'BENJAMIM',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (13,'DÃ',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (14,'NAFTALI',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (15,'GADE',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (16,'ASER',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (17,'DINÁ',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (18,'PEREZ',8);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (19,'ZERA',8);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (20,'ESRON',18);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (21,'ARÃO ',20);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (22,'AMINADABE',21);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (23,'NASSON',22);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (24,'SALMON',23);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (25,'BOAZ',24);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (26,'OBEDE',25);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (27,'JESSÉ',26);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (28,'DAVI',27);
 
SELECT * FROM genealogia;
 
 
--query 1, AUTO RELACIONAMENTO
 
            SELECT g1.nome
                 , g1.id_genealogia
                 , g2.id_genealogia_pai 
              FROM genealogia g1
         LEFT JOIN genealogia g2 
                ON g1.id_genealogia = g2.id_genealogia_pai 
                 ;   
 
--query 2, RECURSIVA 
 
 
WITH RECURSIVE cte_recursiva (id_genealogia,nome,nivel,arvore)
AS
(
    -- 1a PARTE ANCORA
    SELECT id_genealogia
         , nome
         , 1 AS nivel
         , CAST(nome AS VARCHAR(255)) AS arvore
      FROM genealogia
     WHERE id_genealogia_pai IS NULL
    
    UNION ALL
    
    -- 2a PARTE RECURSIVA
    SELECT g.id_genealogia
         , g.nome
         , c.nivel + 1 AS nivel
         , CAST((c.arvore || '/' || g.nome) AS VARCHAR(255)) AS arvore 
      FROM genealogia g
INNER JOIN cte_recursiva c 
        ON g.id_genealogia_pai = c.id_genealogia
    
)
SELECT nivel
     , arvore 
  FROM cte_recursiva
     ;


Sem stress...

terça-feira, 9 de agosto de 2011

Querys Recursivas no Oracle





Querys Recursivas no Oracle

Segue um exemplo prático de como fazer querys recursivas no Oracle, usando genealogia.

Recomendado para quem está estudando para a obtenção da Certificação Oracle Database: SQL Expert (OCE – Oracle Certified Expert), Exame 1Z0-047 Oracle Database SQL Expert.


--
-- Nome Artefato/Programa..: querys_recursivas_no_oracle 
-- Empresa.................: 
-- Autor(es)...............: Emerson Hermann (emersonhermann@gmail.com) 
-- Data Inicio ............: 07/04/2011
-- Data Atual..............: 22/07/2011
-- Versao..................: 0.01
-- Compilador/Interpretador: Oracle
-- Sistemas Operacionais...: Linux/Windows/Outros SOs
-- SGBD....................: Oracle 9i/10g/11g
-- Kernel..................: Nao informado!
-- Finalidade..............: uso de querys recursivas no oracle com com start with ... connect by ... 
-- ........................: 
-- OBS.....................: 
--


/* testando no oracle com start with ... connect by */

--DROP TABLE genealogia;
CREATE TABLE genealogia
(
    id_genealogia integer     PRIMARY KEY 
  , nome varchar2(25)         NOT NULL
  , id_genealogia_pai integer NULL --FOREIGN KEY fk_genealogia REFERENCES genealogia(id_genealogia)

);

--TRUNCATE TABLE genealogia;

SELECT * FROM genealogia;

INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (1,'ABRAÃO',NULL);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (2,'ISAC',1);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (3,'ESAÚ',2);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (4,'JACÓ',2);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (5,'RÚBEN',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (6,'SIMEÃO',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (7,'LEVI',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (8,'JUDÁ',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (9,'ISSACAR',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (10,'ZEBULON',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (11,'JOSÉ',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (12,'BENJAMIM',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (13,'DÃ',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (14,'NAFTALI',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (15,'GADE',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (16,'ASER',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (17,'DINÁ',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (18,'PEREZ',8);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (19,'ZERA',8);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (20,'ESRON',18);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (21,'ARÃO ',20);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (22,'AMINADABE',21);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (23,'NASSON',22);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (24,'SALMON',23);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (25,'BOAZ',24);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (26,'OBEDE',25);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (27,'JESSÉ',26);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (28,'DAVI',27);

SELECT * FROM genealogia;


--query 1, AUTO RELACIONAMENTO

            SELECT g1.nome
                 , g1.id_genealogia
                 , g2.id_genealogia_pai 
              FROM genealogia g1
         LEFT JOIN genealogia g2 
                ON g1.id_genealogia = g2.id_genealogia_pai 
                 ;   

--query 2, CONNECT BY PRIOR

            SELECT nome
                 , id_genealogia
                 , id_genealogia_pai 
              FROM genealogia
  CONNECT BY PRIOR id_genealogia = id_genealogia_pai
                 ;   

--query 3, LEVEL              

            SELECT nome
                 , id_genealogia
                 , id_genealogia_pai 
                 , LEVEL 
              FROM genealogia
  CONNECT BY PRIOR id_genealogia = id_genealogia_pai
                 ;   

--query 4,  START WITH

            SELECT nome
                 , id_genealogia
                 , id_genealogia_pai 
                 , LEVEL
              FROM genealogia
        START WITH id_genealogia = 4  --JACÓ
  CONNECT BY PRIOR id_genealogia = id_genealogia_pai
          ORDER BY LEVEL ASC
                 ;
                 
--query 5, COM ARVORE

            SELECT RPAD(LPAD(' ', 5*(LEVEL-1))||nome,30) AS arvore  
                 , nome
                 , id_genealogia
                 , id_genealogia_pai 
                 , LEVEL
              FROM genealogia
        START WITH id_genealogia = 1
  CONNECT BY PRIOR id_genealogia = id_genealogia_pai
          ORDER BY LEVEL ASC
                 ;

--query 6, SYS_CONNECT_BY_PATH

            SELECT LPAD(' ', 5*(LEVEL-1)) || nome AS representacao_arvore1
                 , SYS_CONNECT_BY_PATH(nome, '/') AS represencao_arvore2  
                 , nome
                 , id_genealogia
                 , id_genealogia_pai 
                 , LEVEL
              FROM genealogia
        START WITH id_genealogia = 1
  CONNECT BY PRIOR id_genealogia = id_genealogia_pai
          ORDER BY LEVEL ASC
                 ;

--query 7, ORDER SIBLINGS BY

            SELECT LPAD(' ', 5*(LEVEL-1)) || nome AS representacao_arvore1
                 , SYS_CONNECT_BY_PATH(nome, '/') AS represencao_arvore2  
                 , nome
                 , id_genealogia
                 , id_genealogia_pai 
                 , LEVEL
              FROM genealogia
        START WITH id_genealogia = 1
  CONNECT BY PRIOR id_genealogia = id_genealogia_pai
 ORDER SIBLINGS BY nome ASC
                 ;
                 
--query 8, CONNECT_BY_ROOT

CREATE OR REPLACE  VIEW  vw_genealogia  AS 
            SELECT LPAD('>', 5*(LEVEL-1)) || nome AS representacao_arvore1
                 , SYS_CONNECT_BY_PATH(nome, '\') AS represencao_arvore2  
                 , CONNECT_BY_ROOT nome AS raiz 
                 , nome
                 , id_genealogia
                 , id_genealogia_pai 
                 , LEVEL AS nivel 
              FROM genealogia
        START WITH id_genealogia = 1
--CONNECT BY NOCYCLE PRIOR  id_genealogia = id_genealogia_pai
  CONNECT BY PRIOR id_genealogia = id_genealogia_pai
 --ORDER SIBLINGS BY level  ASC
ORDER  BY level  ASC;

SELECT * FROM vw_genealogia;

Sem stress...

segunda-feira, 8 de agosto de 2011

Função isdigit no Postgres


Função isdigit no Postgres

Esta função escalar excelente para testar se um derterminado campo tem strings, letras ou apenas números em Postgres.

Retorna true para verdadeiro caso seja apenas números e false para falso, caso encontre textos dentro do campo.

Espero ter ajudado.

Segue fonte abaixo:

-- apagar se existir 
DROP FUNCTION IF EXISTS usf_isdigit(text);
CREATE FUNCTION usf_isdigit(text) RETURNS BOOLEAN AS
$$
--
-- Nome Artefato/Programa..: usf_isdigit.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com) adaptado de Manuel Sugawara em http://bytes.com/topic/postgresql/answers/174214-determine-if-string-digit
-- Data Inicio ............: 08/07/2011
-- Data Atualizacao........: 12/04/2018
-- Versao..................: 0.03
-- Compilador/Interpretador: PostgreSql
-- Sistemas Operacionais...: Linux/Windows
-- SGBD....................: Postgres 8.x/9.x/10.x
-- Kernel..................: Nao informado!
-- Finalidade..............: store procedure (user store function - usf) para testa se um campo eh digito ou nao, retorna true para verdadeiro e false para falso 
-- OBS.....................: 
--

SELECT CASE WHEN (array_upper(string_to_array($1, '.'),1) > 2 ) THEN 
                FALSE 
            ELSE 
                $1 ~ '^(-)?\.|[0-9]+$' 
            END AS usf_isdigit
/*
Exemplo: 
 
SELECT usf_isdigit('ISSO É UM VALOR NUMERICO?'); -- retorna false
SELECT usf_isdigit('ISSO É UM VALOR N.UMER.IC.O?'); -- retorna false
SELECT usf_isdigit('ISSO É UM VALOR N.UMERICO?'); -- retorna false
SELECT usf_isdigit('3000'); --retorna true
SELECT usf_isdigit('2700.00'); --retorno true 
SELECT usf_isdigit('2700.00.00'); --retorno false

*/ 
$$ 
LANGUAGE SQL;

domingo, 7 de agosto de 2011

Remover Letras em campos do Postgres

Remover Letras em campos do Postgres



Mesmo teretêtê, que foi publicado no artigo Remover Letras em campos do Postgres, publicado no blog O Peregrino (http://emersonhermann.blogspot.com) para o SGBD SQL Server, só que dessa vez para o SGBD Postgres ... :)

Vez por outra, precisamos de fazer uso de campos concatenados com números e strings e em alguns casos só procisamos
dos números nos joins ou em ETL para extração de dados.

Poderiamos simplesmente usar a function substring se o campo string fosse de tamanho fixo, e nesses casos a coisa complica.

A exemplo, um campo nome id_tabela de uma tabela qualquer, em algumas ocorrencias, isto é, linhas o id_tabela está ABCZ00001
e em outra ZCBA00002X, e uma outra linha também poderia está preenchida dessa forma ABZ00003.

Então sendo assim:

Aqui segue a dica, de uma function desenvolvida em PL/pgSQL que resolve esse problema:

--
-- Nome Artefato/Programa..: sp_rm_letters_pg.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann at gmail.com) O Peregrino http://emersonhermann.blogspot.com
-- Data Inicio ............: 25/07/2011
-- Data Atual..............: 16/08/2011
-- Versao..................: 0.03
-- Compilador/Interpretador: PostgreSql
-- Sistemas Operacionais...: Linux/Windows
-- SGBD....................: PostgreSql 8.x/9.x
-- Kernel..................: Nao informado!
-- Finalidade..............: store procedure (function) para remever letras das strings
-- OBS.....................: 
--
-- DROP FUNCTION IF EXISTS sp_rm_letters(number text);
CREATE OR REPLACE FUNCTION sp_rm_letters(number text) RETURNS text AS
$$
DECLARE 
     c         integer;
     number_r  text;
BEGIN 
     c         := 65;
     number_r  := number;
     --até 126, isto é, mais 62, contempla os caracteres padrão ANSI original, até 255 (191) contempla o ASCII extendido .
     WHILE c<(65+62) LOOP
          number_r  := replace(number_r,chr(c),'');
          c         := c + 1;
     END LOOP;
     RETURN(number_r);
END;
$$
LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;

/*
-- Abaixo, letras, caracteres que serão removidos da string, pela function sp_rm_letters:
ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~áàâãäéèêëíìïóòôõöúùûüÁÀÂÃÄÉÈÊËÍÌÏÓÒÔÕÖÚÙÛÜçÇ

-- Chamado a função 

SELECT sp_rm_letters('ABC123Z4') -- Retorno 1234

-- Ou

SELECT sp_rm_letters('éab~Z|||zcABC123éáúZ4'||chr(127)) -- Retorno 1234
 
-- Ou

SELECT sp_rm_letters('ABC123Z4'); -- Retorno 1234

-- A função sp_rm_letters não remove caracteres especiais mas pode ser combinada com a function sp_rm_special_chars, para remover caracteres especiais.
select sp_rm_special_chars(sp_rm_letters('éab~Z|||zcABC123éáúZ4z2'||chr(127)));

-- Uma implementação da função 
SELECT * FROM tabela1 t1 JOIN tabela2 t2 ON t1.id_campo=sp_rm_letters(t2.id_campo);
*/

Que DEUS abençoe o Postgres e quem trabalha com o mesmo!

Valeu!

sábado, 6 de agosto de 2011

Vida de DBA

Essa tirinha eu copiei do site: http://vidadeprogramador.com.br/
Muito bom!
Essa é a vida do DBA, é pura realidade, baseado em fatos reais.
:)

UPPER JOIN

kkkkkkkkkkkkkkkkkkkkkkkkkk

sexta-feira, 5 de agosto de 2011

Função isdigit no SQL Server


Função isdigit no SQL Server

Esta função escalar excelente para testar se um derterminado campo tem strings, letras ou apenas números em SQL Server.

Retorna 1 para verdadeiro caso seja apenas números e 0 para falso, caso encontre textos dentro do campo.

Espero ter ajudado.

Segue fonte abaixo:

--
-- Nome Artefato/Programa..: sp_isdigit.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com) adaptado de http://www.simple-talk.com/sql/t-sql-programming/sql-string-user-function-workbench-part-1/#eighth
-- Data Inicio ............: 08/07/2011
-- Data Atualizacao........: 05/08/2011
-- Versao..................: 0.02
-- Compilador/Interpretador: T-SQL (Transact SQL) 
-- Sistemas Operacionais...: Windows
-- SGBD....................: MS SQL Server 2008
-- Kernel..................: Nao informado!
-- Finalidade..............: store procedure (function) para testa se um campo é digito ou não, retorna 1 para verdadeiro e 0 para falso 
-- OBS.....................: 
--

IF EXISTS (
            SELECT * 
              FROM sys.objects 
             WHERE object_id = OBJECT_ID(N'[dbo].[sp_isdigit]') 
               AND type IN (N'FN')
           )
 DROP FUNCTION sp_isdigit
GO

CREATE FUNCTION sp_isdigit (@string varchar(max))  

RETURNS INT
AS 
BEGIN
   RETURN 
   (  
     SELECT CASE WHEN PATINDEX('%[^0-9]%', @string) > 0 THEN 
       0
      ELSE 
       1
      END AS sp_isdigit
   )
END;
GO
/*
Exemplo: 

SELECT dbo.sp_isdigit('ISSO É UM VALOR NÚMERICO?'); -- 0 
SELECT dbo.sp_isdigit('3000'); --retorno 1
SELECT dbo.sp_isdigit('2700.00'); --retorno 0 
*/

sexta-feira, 15 de julho de 2011

Remover Letras em campos do SQL Server



Remover Letras em campos do SQL Server

Vez por outra, precisamos de fazer uso de campos concatenados com números e strings e em alguns casos só procisamos
dos números nos joins ou em ETL para extração de dados.

Poderiamos simplesmente usar a function substring se o campo string fosse de tamanho fixo, mas não é, e nesses casos a coisa complica.

A exemplo, um campo nome id_tabela de uma tabela qualquer, em algumas ocorrencias, isto é, linhas o id_tabela está ABCZ00001
e em outra ZCBA00002X, e uma outra linha também poderia está preenchida dessa forma ABZ00003.

Então sendo assim:

Aqui segue a dica, de uma function desenvolvida em T-SQL que resolve esse problema:

--
--
-- Nome Artefato/Programa..: sp_rm_letters.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com) adaptado de http://www.softstuff-consulting.com/kbase/showkb.asp?id=56  
-- ........................: Com colobaração de Felipe (felipegramos [at] yahoo.com.br)
-- Data Inicio ............: 08/07/2011
-- Data Atualizacao........: 16/08/2011
-- Versao..................: 0.03
-- Compilador/Interpretador: T-SQL (Transact SQL) 
-- Sistemas Operacionais...: Windows
-- SGBD....................: MS SQL Server 2008
-- Kernel..................: Nao informado!
-- Finalidade..............: store procedure (function) para remocao de caracteres de uma string deixando só os números
-- OBS.....................: 
--

IF EXISTS (
            SELECT * 
              FROM sys.objects 
             WHERE object_id = OBJECT_ID(N'[dbo].[sp_rm_letters]') 
               AND type IN (N'FN')
           )
 DROP FUNCTION sp_rm_letters
GO


CREATE FUNCTION sp_rm_letters(@number varchar(max)) RETURNS varchar(max) AS
BEGIN
 DECLARE @c int
 SET @c=65
 WHILE @c<(65+62) BEGIN
  SET @number=replace(@number,char(@c),'')
  SET @c=@c+1
 END
 RETURN(@number)
END

/*
-- Abaixo, letras, caracteres que serão removidos da string, pela function sp_rm_letters:
ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~áàâãäéèêëíìïóòôõöúùûüÁÀÂÃÄÉÈÊËÍÌÏÓÒÔÕÖÚÙÛÜçÇ

-- Chamando a função via PRINT 
PRINT dbo.sp_rm_letters('ABC123Z4') -- Retorno 1234

-- Ou
SELECT dbo.sp_rm_letters('ABC123Z4'); -- Retorno 1234

-- Uma implementação da função 

SELECT * FROM tabela1 t1 JOIN tabela2 t2 ON t1.id_campo=dbo.sp_rm_letters(t2.id_campo);

-- A função sp_rm_letters não remove caracteres especiais mas pode ser combinada com a function sp_rm_special_chars (publicada também no blog http://emersonhermann.blogspot.com), para remover caracteres especiais.
SELECT dbo.sp_rm_special_chars(dbo.sp_rm_letters('éab~ZzcABC123éáúZ4z|||2'+char(13)+'ç['+char(127)));


Mais uma vez espero ter ajudado.

terça-feira, 5 de julho de 2011

Full Text Search, Busca Textual no PostgreSQL

Adaptei uma apresentação(slides) que fiz sobre FTS no PG ...

Full Text Search no PostgreSQL (FTS)
Teoria, Utilização, Possibilidades e Aplicabilidade


> Conceito de FTS (Full Text Search)

É uma técnica de pesquisa e recuperação de informações de texto armazenada em banco de dados,
usando linguagem natural como critério para busca em banco(querys),
opcionalmente podendo ordena-la por relevância da consulta.


> Busca Textual Tradicional

* Operadores de ~, ~ *, LIKE, ILIKE para tipos de dados textuais.
* Não há suporte linguístico.
* Não há ordenação do resultados de pesquisa(ranking)
* Há uma tendência a serem lentos por não haver apoio ao uso de índice.


> Busca Textual com FTS

* Indexação completa de texto e pré-processamento de documentos salva na própria entidade(tuplas).
* Uso de Dicionários.
* Busca por similaridade.
* Mesmo conceito de sites de busca a exemplo do Google (www.google.com)

> Definições

>> Documento: É unidade de busca do FTS, texto, atributo da entidade que sub-divide em:
>>> Tokens (símbolos): Texto classificado, fatiado em símbolos
>>> Lexema: É uma palavra única (palavra-chave) (token normalizado) normalizada de um documento.
>>> Palavras de Parada (Stop Words): São palavras muito comuns, aparece em quase todos os documentos, não tem valor de descriminação.

>> Tsvector: Conjunto de lexemas e posições, representação compacta de um documento.

>> Tsquery: Termos da busca, que deve ser normalizada já com uso de lexemas, e podem combinar vários termos usando operadores lógicos &, |, !

>> @@: Operador de casamento de padrões.


> Dicionários

>> Dicionários permitem controle detalhado sobre como os símbolos(tokens) são normalizados.
* Definir stop words que não devem ser indexados
* Mapa sinônimos para uma única palavra
* Mapa de frases a uma única palavra usando um dicionário de sinônimos
* Mapa de diferentes variações de uma palavra de uma forma canônica usando um dicionário.
* Mapa de diferentes variações de uma palavra de uma forma canônica usando regras stemmer Snowball.


>Precisão versus Recuperação

>> Uso de linguagem natural acarreta resultados imprecisos
* Ambiguidade
* Recuperação de documentos irrelevantes
* Vocabulários controlados resolvem esse problema de imprecisão
* Entretanto apresenta baixa de retorno de resultados nas consultas, não fazendo uso de derivação, sinônimos, etc.


> Ordenação, Desempenho, uso de Índices e Clusters
>> Falar sobre FTS, é quase que obrigatório falar sobre ordenação:
>> Índices: Agrupamento, ordenação lógica de entidades em arquivos separados, são atualizados conforme atualização de entidades.
>> Clusters: Agrupamento, ordenação física de dados da entidade, baseado em índices criados.


>Uso de Índices

>> É recomendável utilizar índices nas seguintes cláusulas SQL e atributos de entidade:
- FOREIGN KEY
- ORDER BY
- WHERE
- ON
- GROUP BY
- HAVING
- @@ (FTS)


> Tipos de Índices PostgreSQL

>> B-tree (padrão);
* Usado com Operadores: <, <=, =, >=, >, LIKE, ILIKE, ~, ~*
>> R-tree (espaciais);
* Usado com Operadores: <<, &<, &>, >>, @, ~=, &&
>>Hash (igualdade simples);
* Desancorajado, usar (B-tree ou GiST);
>>GiST e GIN
* Usados no FTS, não é obrigatório, mas recomendado;


> Comparativo GiST e GIN no FTS

>> GIN efetua pesquisas aprox. três vezes mais rápido do que GiST;
>> GIN demoram aprox. três vezes mais para serem construídos do que GiST; (pode ser contornado em alterando o parametro maintenance_work_mem postgresql.conf)
>> GIN são lentos para atualização de índices;
>> GiST são mais rápidos para atualização de índices;
>> GIN são de duas a três vezes maior do que GiST


Quando usar GiST ou GIN no FTS?

Como regra geral usar índices GIN para dados estáticos, porque as pesquisas são mais rápidas.
E usar índices GiST para dados dinâmicos, porque são mais rápidos para atualização.


> Limitações do FTS PostgreSQL

>> O comprimento de cada lexema deve ser inferior a 2K bytes
>> O comprimento de um tsvector (lexemas + posições) deve ser menor que 1 megabyte
>> O número de lexemas deve ser inferior a 2 64
>> Valores Posição no tsvector deve ser maior que 0 e não mais de 16.383
>> Não mais do que 256 posições por lexema
>> O número de nós (lexemas + operadores) em um tsquery deve ser inferior a 32.768


>Tsearch

>> Tsearch é o módulo de busca textual do PostgreSQL

>>> Tsearch1 já era poderoso mas não dava suporte a muitas features como ranking de relevância

>>> Tsearch2 já vem pré-instalado a partir da versão 8.3
* A versão 2 acrescentou ranking, headline, tabelas de configuração e etc.
* Mais fácil de configurar e usar
* Não é necessário compilar ou instalar módulos contrib/tsearch2


> Tipos de dados e Operadores do FTS

>> Tipos de Dados do FTS

>>> tsvector: tipo de dados que representa um documento
* Com lista ordenada de lexemas (tokens)
* Com posições no texto

>>> tsquery: tipo de dado para busca textual que suporta operadores booleanos |, & e !
* Ex.: ‘gato & rato’

>> Operadores do FTS
* @@: operador booleano que retorna True se um tipo tsquery está contido num tipo tsvector


> FTS na Prática
* E o FTS no Postgres como fica na prática ?

/*

FTS na prática usando a base de dados da Bíblia.

Para praticar os conceitos expostos nesse artigo, usaremos a base de dados da Bíblia, 
publicado nesse blog no seguinte endereço: 

http://emersonhermann.blogspot.com/2011/04/biblia-do-dba.html



*/

-- Criando um indice ...
DROP INDEX IF EXISTS idx_palavra_texto; 
CREATE INDEX idx_palavra_texto
  ON palavra
  USING btree
  (texto);

-- Ordenacao fisica da tabela, com base em indice criado anteriormente, 
--  a tabela fica indisponivel (em modo ACCESS EXCLUSIVE) para qualquer outra operação, no momento da execução do comando cluster. 

-- Ordena fisicamente com base em indice criado anteriormente  
CLUSTER idx_palavra_texto ON Palavra;

-- Reagrupando fisicamente 
CLUSTER Palavra;

-- Todas as tabelas configuradas 
CLUSTER; 

-- Voltando ao FTS ... 

-- Tsvector: Tipo de dados que representa um documento
     -- Com lista ordenada de lexemas (tokens)
     -- Com posições no texto

-- Tsquery: Tipo de dado para busca textual que suporta operadores booleanos |, & e !
     -- Ex.: 'gato & rato'

-- @@: Operador booleano que retorna True se um tipo tsquery está contido num tipo tsvector

--Operador &
SELECT 'gato & rato':: tsquery @@ 'O rato roeu a roupa do rei de Roma'::tsvector; --false
SELECT 'gato & rato':: tsquery @@ 'O gato comeu o rato que roeu a roupa do rei de Roma'::tsvector; --true
 
-- Operador | 
SELECT 'gato | rato':: tsquery @@ 'O rato roeu a roupa do rei de Roma'::tsvector; --true
SELECT 'gato | cão':: tsquery @@ 'O rato roeu a roupa do rei de Roma'::tsvector; --false

-- Operador ! 
SELECT '!rainha':: tsquery @@ 'O rato roeu a roupa do rei de Roma'::tsvector; --true
SELECT '!rei':: tsquery @@ 'O rato roeu a roupa do rei de Roma'::tsvector; --false 

-- Pode-se obter um vetor de lexemas em tempo de execução, usando a função to_tsvector
 
SELECT to_tsvector('O gato comeu o rato que roeu a roupa do rei de Roma');  --'a':8 'comeu':3 'de':12 'do':10 'gato':2 'o':1,4 'que':6 'rato':5 'rei':11 'roeu':7 'roma':13 'roupa':9

-- Em  um ambiente de produção, deve-se levar em conta o custo da criação do vetor em tempo de execução


-- Acredita-se que a melhor opção é criar um campo do tipo tsvector na tabela

-- A tabela biblioteca.cache_entidades_marc antes de adição do campo vetorfts 
SELECT * FROM Palavra LIMIT 10;

-- Adicionando o campo vetorfts do tipo tsvector
ALTER TABLE Palavra ADD COLUMN vetorfts tsvector;

-- a tabela biblioteca.cache_entidades_marc depois de adição do campo vetorfts  
SELECT vetorfts, * FROM Palavra LIMIT 10;

-- Vetorfts é um campo vetorizado para uso do índice FTS propriamente dito

-- Povoando a coluna vertorfts  ... 
-- Exemplo povoando o campo vetorizado criado anteriormente 
     UPDATE Palavra
        SET vetorfts=to_tsvector(texto);

-- Exemplo povoando o campo vetorizado criado anteriormente, mas informando a linguagem do catálogo 
     UPDATE Palavra
        SET vetorfts=to_tsvector('portuguese', texto);

-- Usa-se a função to_tsvector sobre o campo que se deseja indexar. Vários campos podem ser utilizados também no mesmo índice por concatenação, simulando o mesmo comportamento do Google:

     UPDATE Palavra
        SET vetorfts=to_tsvector('portuguese', id_livro || ' ' || capitulo || ' ' || versiculo || ' ' || texto);

-- Pode-se atribuir labels para os valores dos campos indexados pelo vetor, usando a função setweight
-- Ao mesmo tempo, são atribuídos pesos para valores de campos diferentes:

     UPDATE Palavra
        SET vetorfts = setweight(to_tsvector('portuguese',coalesce(cast (id_livro as text),'')), 'A') ||
                       setweight(to_tsvector('portuguese',coalesce(cast (capitulo as text),'')), 'B') ||
                       setweight(to_tsvector('portuguese',coalesce(cast (versiculo as text),'')), 'C') || 
                       setweight(to_tsvector('portuguese',coalesce(texto,'')), 'D')
          ;

-- Após criar o campo, e povoa-lo é recomendável criar um índice GiST ou GIN para ele
-- Exemplo de indice criado com vetortfs  
     DROP INDEX IF EXISTS idx_palavra_vetorfts;
     CREATE 
      INDEX idx_palavra_vetorfts
         ON Palavra
      USING gin(vetorfts)
          ;

          
-- Exemplo de indice criado sem vetorfts  
     /*
     DROP INDEX IF EXISTS idx_palavra_texto_fts;     
     CREATE 
      INDEX idx_palavra_texto_fts
         ON Palavra
      USING gin(to_tsvector('portuguese'::regconfig, texto))
          ;
     */
          

-- Para realizar uma consulta FTS sobre o vetor, utiliza-se a função to_tsquery juntamente como  operador FTS booleano '@@':
--sem o campo vertorizado 
     SELECT * 
       FROM Palavra
      WHERE to_tsvector('portuguese', texto) @@ to_tsquery( 'JESUS' ) 
          ; 

--com o campo vetorizado
     SELECT *
       FROM Palavra
      WHERE vetorfts @@ to_tsquery('JESUS')  
          ; 


-- Será considerado o conteúdo de todos os campos indexados pelo índice vetorfts


-- Uma consulta normal (ANSI) próximo do equivalente ao FTS seria:

     SELECT *       
       FROM Palavra 
      WHERE texto ILIKE '%JESUS%' 
          ;

  
-- E se tivéssemos mais de um parâmetro?
-- OR OR OR OR OR OR OR OR OR OR OR OR OR OR OR 
-- ou
-- LIKE '%param1%param2%paramN%'


-- É possível ainda definir o campo que deve ser consultado dentro do vetor FTS através do label:
     SELECT *
       FROM Palavra
      WHERE vetorfts @@ to_tsquery('JESUS:D')  
          ; 

-- Antes foi considerado o conteúdo de todos os campos indexados pelo índice vetorfts.
-- Nesta query foi considerado apenas os valores correspondentes ao campo do label informado.

-- Para obter o ranking das consultas, usa-se a função ts_rank_cd
     SELECT *
          , ts_rank_cd(vetorfts, to_tsquery('JESUS:D')) AS rank
       FROM Palavra
      WHERE vetorfts @@ to_tsquery('JESUS:D')  
   ORDER BY rank DESC
          ;

-- Um parâmetro opcional pode ser especificado para definir se o tamanho do documento afetará o cálculo do ranking
-- CUIDADO: o uso de ranking pode ser caro pois é preciso consultar o tsvector de todos os documentos onde há matching

-- É possível alterar os pesos dos campos na cláusula SQL:
     SELECT *
          , ts_rank_cd('{0.8, 0.6, 0.4, 0.0}', vetorfts, to_tsquery('JESUS:D')) AS rank
       FROM Palavra
      WHERE vetorfts @@ to_tsquery('JESUS:D')  
   ORDER BY rank DESC
          ;

-- A função ts_headline mostra um trecho do texto onde a palavra pesquisada foi encontrada, e ainda a destaca em negrito:
     SELECT *
          , ts_headline(texto , to_tsquery('JESUS:D') ) AS headline
       FROM Palavra
      WHERE vetorfts @@ to_tsquery('JESUS:D')
          ;

-- É possível também obter estatísticas dos lexemas em um vetor FTS usando ts_stat:
-- Valores retornados: 
     SELECT word         -- Lexema
          , ndoc         -- Num. documentos
          , nentry       -- Num. ocorrências           
       FROM ts_stat('SELECT vetorfts FROM Palavra ') 
   ORDER BY ndoc DESC
          , nentry DESC
          , word ASC
          ;

-- A função ts_debug mostra informações de como uma palavra foi tratada pelo analisador e quais dicionários foram utilizados.
SELECT ts_debug('JESUS');
SELECT ts_debug('portuguese', 'JESUS'); 
SELECT ts_debug('english', 'JESUS'); 



>Considerações Finais

>>Observou-se que o uso de FTS na prática, consome muito processamento, é recomendável ter processador(es) de alto desempenho, para evitar gargalos.

>>Ainda não existe padronização para o FTS, isso implica dizer que cada SGBDR tem a sua forma de fazer FTS, recomenda-se interfacear na aplicação, para manter a portabilidade.

>>Entretando o custo benefício é viável, haja visto desempenho e funcionalidade.

Que DEUS te abençõe, sempre...

segunda-feira, 4 de julho de 2011

Remover caracteres especiais em campos do SQL Server

Function scalar no SQL SERVER para remover caracteres especiais, exatamente 33 caracteres de controle (especiais) menos o NULL ficando 32 ao todo, recomendo usar essa store function, (store procedure) em procedimentos de ETL, migração de dados ou em consultas que precisem remover esses tais caracteres da tabela ASCII.

Fica aqui o compartilhamento do conhecimento.

Abraços.

--
-- Nome Artefato/Programa..: sp_rm_special_chars.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann at gmail.com)  O Peregrino (http://www.emersonhermann.blogspot.com) 
-- Data Inicio ............: 04/07/2011
-- Data Atual..............: 04/07/2011
-- Versao..................: 0.01
-- Compilador/Interpretador: T-SQL (Transact SQL) 
-- Sistemas Operacionais...: Windows
-- SGBD....................: MS SQL Server 2008
-- Kernel..................: Nao informado!
-- Finalidade..............: store procedure (function) para remocao de caracteres especiais do ASCII (33) caracteres nao imprimiveis 
-- OBS.....................: Remover "caracteres especiais" em campos do SQL Server
--

IF EXISTS
(
    SELECT * 
      FROM sys.objects 
     WHERE object_id = OBJECT_ID(N'[dbo].[sp_rm_special_chars]') 
       AND type IN (N'FN')
)
    DROP FUNCTION sp_rm_special_chars
GO

CREATE FUNCTION sp_rm_special_chars (@stexto varchar(max))
RETURNS varchar(max)
AS 
 BEGIN
 IF @stexto IS NULL BEGIN  
     RETURN NULL 
 END 
 -- Tabela ASCII
 -- Caracteres não imprimiveis, 33 ao total, menos o NUL fica 32 
 --  hex d abr ctl descricao  
 --  00  0 NUL ^@ Null - Nulo desativado, pois o nulo no postgres funciona em forma de cascata      
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(1),''))  --  01  1 SOH ^A Start of Header - Início do cabeçalho
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(2),''))  --  02  2 STX ^B Start of Text - Início do texto
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(3),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(4),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(5),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(6),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(7),''))  --  07  7 BEL ^G Bell - Campainha
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(8),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(9),''))  --  09  9 HT  ^I  Horizontal Tabulation - Tabulação horizontal
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(10),'')) --  0A 10 LF  ^J Line-Feed - Alimenta linha
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(11),'')) --  0B 11 VT  ^K  Vertical Tabulation - Tabulação vertical
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(12),'')) --  0C 12 FF  ^L Form-Feed - Alimenta formulário
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(13),'')) --  13 19 DC3 ^S Device-Control 3
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(14),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(15),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(16),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(17),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(18),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(19),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(20),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(21),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(22),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(23),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(24),'')) -- 18 24 CAN ^X Cancel
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(25),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(26),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(27),'')) -- 1B 27 ESC ^[ Escape
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(28),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(29),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(30),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(31),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(127),'')) -- 7F127 DEL ^?  Delete     

 RETURN @stexto     
END
GO

-- Exemplo de uso: 

-- SELECT texto = dbo.sp_rm_special_chars ('legal e bom'), getdate() as data;  -- 'legal e bom' 
-- SELECT texto = dbo.sp_rm_special_chars ('NULL'), getdate() as data;  -- 'NULL'
-- SELECT texto = dbo.sp_rm_special_chars (NULL), getdate() as data;   -- NULL

Remover caracteres especiais em campos do SQL Server

Function scalar no SQL SERVER para remover caracteres especiais, exatamente 33 caracteres de controle (especiais) menos o NULL ficando 32 ao todo, recomendo usar essa store function, (store procedure) em procedimentos de ETL, migração de dados ou em consultas que precisem remover esses tais caracteres da tabela ASCII.

Fica aqui o compartilhamento do conhecimento.

Abraços.

--
-- Nome Artefato/Programa..: sp_rm_special_chars.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann at gmail.com)  O Peregrino (http://www.emersonhermann.blogspot.com) 
-- Data Inicio ............: 04/07/2011
-- Data Atual..............: 04/07/2011
-- Versao..................: 0.01
-- Compilador/Interpretador: T-SQL (Transact SQL) 
-- Sistemas Operacionais...: Windows
-- SGBD....................: MS SQL Server 2008
-- Kernel..................: Nao informado!
-- Finalidade..............: store procedure (function) para remocao de caracteres especiais do ASCII (33) caracteres nao imprimiveis 
-- OBS.....................: Remover "caracteres especiais" em campos do SQL Server
--

IF EXISTS
(
    SELECT * 
      FROM sys.objects 
     WHERE object_id = OBJECT_ID(N'[dbo].[sp_rm_special_chars]') 
       AND type IN (N'FN')
)
    DROP FUNCTION sp_rm_special_chars
GO

CREATE FUNCTION sp_rm_special_chars (@stexto varchar(max))
RETURNS varchar(max)
AS 
BEGIN
 IF @stexto IS NULL BEGIN  
     RETURN NULL 
 END 
 -- Tabela ASCII
 -- Caracteres não imprimiveis, 33 ao total, menos o NUL fica 32 
 --  hex d abr ctl descricao  
 --  00  0 NUL ^@ Null - Nulo desativado, pois o nulo no postgres funciona em forma de cascata      
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(1),''))  --  01  1 SOH ^A Start of Header - Início do cabeçalho
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(2),''))  --  02  2 STX ^B Start of Text - Início do texto
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(3),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(4),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(5),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(6),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(7),''))  --  07  7 BEL ^G Bell - Campainha
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(8),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(9),''))  --  09  9 HT  ^I  Horizontal Tabulation - Tabulação horizontal
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(10),'')) --  0A 10 LF  ^J Line-Feed - Alimenta linha
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(11),'')) --  0B 11 VT  ^K  Vertical Tabulation - Tabulação vertical
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(12),'')) --  0C 12 FF  ^L Form-Feed - Alimenta formulário
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(13),'')) --  13 19 DC3 ^S Device-Control 3
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(14),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(15),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(16),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(17),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(18),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(19),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(20),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(21),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(22),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(23),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(24),'')) -- 18 24 CAN ^X Cancel
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(25),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(26),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(27),'')) -- 1B 27 ESC ^[ Escape
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(28),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(29),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(30),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(31),''))
 SET @stexto = (SELECT REPLACE(@stexto, CHAR(127),'')) -- 7F127 DEL ^?  Delete     

 RETURN @stexto     
END
GO

-- Exemplo de uso: 

-- SELECT texto = dbo.sp_rm_special_chars ('legal e bom'), getdate() as data;  -- 'legal e bom' 
-- SELECT texto = dbo.sp_rm_special_chars ('NULL'), getdate() as data;  -- 'NULL'
-- SELECT texto = dbo.sp_rm_special_chars (NULL), getdate() as data;   -- NULL

sexta-feira, 24 de junho de 2011

Identando Querys


Segue a dica:

Quem trabalha como DBA sabe o moido que é, pegar uma query e sair identando no sentido de entende-la e otimiza-la, ou mesmo desenvolvendo usando hibernate entender sem identação as tripas de SQL que o mesmo gera.

Dica do amigo William Rocha, essa ferramenta ajuda nesse sentido, inclusive até para formatar para códigos fontes para diversas linguagens, totalmente web seu nome é Instant SQL Formatter

Link: http://www.dpriver.com/pp/sqlformat.htm

Mais uma vez, espero ter ajudado!

Flashback Query no Oracle 11g


O Oracle 11g implementa várias tecnologias de Flashback como Flashback Database, Flashback Table, Flashback Drop entre outros,
abordarei o uso do recurso de Flashback Query.

Introduzido com o Oracle 9i, este recurso fornece a habilidade de visualizar os dados como eles estavam em um determinado tempo no passado.
Por padrão, operações no banco de dados usam os dados disponíveis mais recentemente "comitados".
Se você quiser pesquisar determinados dados em algum ponto no passado, você precisará utilizar o recurso de Flashback Query
na qual será necessário especificar um "horário" ou um SCN (System change Number) para efetuar a pesquisa.

Este recurso também é muito útil, quando você precisa restaurar dados que foram erroneamente deletados ou alterados.
Antes da versão Oracle 9i, você tinha que recuperar o banco de dados até um determinado ponto que lhe interessasse.
Dependendo do tamanho do banco de dados, este processo poderia ser lento e demorado.

Apenas para elucidar uma situação em que muitos DBAs, já passaram ou poderão passar, vou ilustrar um diálogo entre Usuário e DBA:

Usuário: meus dados sumiram, pode me ajudar?
DBA: o que vc fez?
Usuário: fiz um delete e esqueci de colocar where
DBA: vc deu commit ?
Usuario: claro!
DBA: humm...

Este artigo é para quem já passou por isso ou ainda vai passar usando um SGBDR Oracle.

Antes de mais nada, para você poder usar o recurso de Flashback Query, é necessário configurar o seu banco de dados para usar o gerenciamento automático de UNDO (Automatic Undo Management).

Verifique se o seu banco de dados já está com esta configuração setada, caso contrário, altere o parâmetro com o valor e privilégios apropriados.

Conforme script abaixo:
-- É necessário configurar o seu banco de dados para usar o gerenciamento automático de UNDO (Automatic Undo Management).
-- Checando parametros, verificando se está setado para Flashback

c:\>sqlplus /nolog

CONNECT / AS SYSDBA;

show parameter undo_management;

-- No parâmetro abaixo, o valor é especificado (em segundos) no qual está no valor padrão 900,  15 minutos de retenção de dados de undo.

show parameter undo_retention;

CREATE USER emerson IDENTIFIED BY emerson DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;

GRANT CONNECT TO emerson;

show user;

CONNECT / AS SYSDBA;

show user;

-- dando permissões 

GRANT EXECUTE ON dbms_flashback TO emerson;
GRANT CREATE ANY TABLE TO emerson;
GRANT COMMENT ANY TABLE TO emerson;

CONNECT emerson/emerson@orcl;

-- criando a tabela
-- DROP TABLE produto;
CREATE TABLE produto
(
  cod  NUMBER PRIMARY KEY,
  data DATE,
  descricao VARCHAR2 (100)
);

-- inserindo registros ...
INSERT INTO produto VALUES (1, sysdate, 'Computador');
INSERT INTO produto VALUES (2, sysdate, 'Laptop');
INSERT INTO produto VALUES (3, sysdate, 'Impressora');
INSERT INTO produto VALUES (4, sysdate, 'Monitor');
INSERT INTO produto VALUES (5, sysdate, 'Mouse');

COMMIT;

-- listando a tabela produto com detalhes de horas e minutos e segundos
SELECT cod, to_char(data,'dd/mm/yyyy hh24:mi:ss') data, descricao FROM produto; 

-- esperar um tempo para inserir estes para simular um recuperação baseada no tempo
INSERT INTO produto VALUES (6, sysdate, 'Teclado');
INSERT INTO produto VALUES (7, sysdate, 'HD');
INSERT INTO produto VALUES (8, sysdate, 'Processador');

COMMIT;

-- listando a tabela produto com detalhes de horas e minutos e segundos
SELECT cod, to_char(data,'dd/mm/yyyy hh24:mi:ss') data, descricao FROM produto; 

-- apenas para entender como funciona as datas
SELECT SYSTIMESTAMP, (to_timestamp('02/06/2011 13:37:07','DD/MM/YYYY HH24:MI:SS')) FROM dual;
SELECT SYSTIMESTAMP FROM dual; -- mesma coisa que sysdate porem com horas e fuso horario

-- habilitando a linha do tempo no flashback, altera tempo conf. necessidade
EXECUTE dbms_flashback.enable_at_time (to_timestamp('02/06/2011 13:37:07','DD/MM/YYYY HH24:MI:SS'));
-- EXECUTE dbms_flashback.enable_at_time (SYSTIMESTAMP - 60); -- mesma coisa de outra forma,  também  em minutos

COMMIT;

SELECT cod, to_char(data,'dd/mm/yyyy hh24:mi:ss') data, descricao FROM produto;

-- disabilitando o flashback
EXECUTE dbms_flashback.disable;

-- listando a tabela produto com detalhes de horas e minutos e segundos
SELECT cod, to_char(data,'dd/mm/yyyy hh24:mi:ss') data, descricao FROM produto;

-- linha do tempo para recuperação
SELECT * FROM produto AS OF TIMESTAMP (to_timestamp('02/06/2011 13:37:07','DD/MM/YYYY HH24:MI:SS'));  

-- mesma coisa de outra forma, linha do tempo para recuperação 1 minuto
-- SELECT * FROM produto AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' MINUTE); 


Referencias:
http://eduardolegatti.blogspot.com/2007/05/utilizando-flashback-query-no-oracle-9i.html
http://www.dbasupport.com/oracle/ora11g/Flashback-TIMESTAMP-or-SCN.shtml
http://www.eduardomorelli.com/
Oracle 9i, SQL PL/SQL e Administração - Morelli, Eduardo
Dominando Oracle 9i - Modelagem e Desenvolvimento - Fanderuff, Damaris
OCA Oracle Database 11g Administração I ( Guia do Exame 1Z0-052 ) - Watson, John

Instalando Oracle 11g no Windows 7

Segue os links de como instalar Oracle 11g no Windows 7, divididos em três partes, compilados pelo amigo Gilvan Costa, da CSTHost, tutorial excelente, recomendo.


Instalação do Oracle 11g no Windows 7 - Parte I
Instalação do Oracle 11g no Windows 7 - Parte II
Instalação do Oracle 11g no Windows 7 – Final


Parte 1/3
Parte 2/3
Parte 3/3

terça-feira, 31 de maio de 2011

3a. Conferência Brasileira de PostgreSQL - Novembro 2011

3a. Conferência Brasileira de PostgreSQL - Novembro 2011
O PGBR (antes conhecido como PGCon Brasil) é o maior evento sobre PostgreSQL das Américas: em 2009 e 2008, o evento trouxe mais de 300 profissionais de TI e, em 2007, mais de 200. Em 2011, serão 3 salas simultâneas com tutoriais, palestras e mesas de alto nível, contando com desenvolvedores nacionais e internacionais do PostgreSQL além de profissionais renomados no mercado brasileiro.

O evento acontecerá nos dias 3 e 4 de Novembro de 2011, no Hotel Century Paulista em São Paulo.

O PGBR é um evento de alta qualidade, e que vale a pena participar. Este ano acontecerá em São Paulo ao invés de Campinas, para facilitar o deslocamento de participantes.

Se você faz algo com o PostgreSQL, participe da lista de discussão e envie uma proposta de trabalho ou palestra (a chamada de trabalhos está prestes a ser publicada). Inscreva-se hoje mesmo!


PGBR 2011 - Conferência Brasileira PostgreSQL

sexta-feira, 8 de abril de 2011

A Bíblia do DBA

Neste artigo, irei descrever como instalar e configurar um base de dados, partindo do ponto de que o banco, serviço, já estão instalados.

Serão usados algums recursos práticos de ETL no Postgres, e conversão de encondig de arquivos.

Tomei por base a Bíblia Sagrada na Tradução de João Ferreira de Almeida, Edição Revista e Corrigida em Português-Brasil.

Como estudioso da Bíblia e crente, entendo que o Autor da Bíblia está vivo, assim sempre que tenho alguma dúvida sobre sobre algum tópico deste livro consulto a Ele, o criador de todas as coisas, DEUS.

Essa base em csv pode ser também usada com algumas adaptações em outros SGBDs a exemplo Oracle, SQL Server, MySQL, Firebird ou outro que você queira usar, neste artigo usei
Postgres

Tomei como referencia uns arquivos gerados em csv com encoding em UTF8, neste caso podendo até serem abertos como planilhas Open-Office ou MS-Excel.

Deixo aqui meu profundo agradecimento ao irmãos que voluntariamente, tiveram o trabalho de converter uma base txt para csv, não é fácil, que DEUS os abençõe!

Mãos a massa!

Antes, apenas definindo o que é um arquivo CSV ou melhor lembrando... :)
Retirado do Wikipedia em: http://pt.wikipedia.org/wiki/Comma-separated_values

Comma-separated values (CSV), em português Valores Separados por Vírgula, é um formato de arquivo que armazena dados tabelados, cujo grande uso data da época dos mainframes. Por serem bastante simples, arquivos .csv são comuns em todas as plataformas de computador.

O CSV é um implementação particular de arquivos de texto separados por um delimitador, que usa a vírgula e a quebra de linha para separar os valores. O formato também usa as aspas em campos no qual são usados os caracteres reservados (vírgula e quebra de linha). Essa robustez no formato torna o CSV mais amplo que outros formatos digitais do mesmo segmento.

Caso queira mudar o encoding, você poderá utilizar o iconv do Linux em http://www.gnu.org/software/libiconv/documentation/libiconv/iconv.1.html ou salvar como txt e abrir pelo MS-Excel, que o mesmo pedirá qual encoding utilizar-converter.

Segue os arquivos em formato CSV da Bíblia:

Biblia_JFARC.zip

No nosso caso, o arquivo está zipado e contém três CSV 1-Testamento_JFARC, 2-Livro_JFARC, 3-Palavra_JFARC:

Para que o script SQL funcione sem alterações descompactei o arquivo no Windows em: c:\Biblia_JFARC, no Linux em: /home/postgres/Biblia_JFARC/ lembrando de dá permissões para acesso com usuário postgres

Além dos Scripts SQL para Postgres, também estão a base de dados para SQL Server e Firebird para Bíblia conforme link abaixo:


Biblia_JFARC.zip

Neste artigo iremos abordar a criação de scripts DDL/DML (SQL) para criação da base de dados da Bíblia no Postgres:

--
-- Nome Artefato/Programa..: Biblia_ddl.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann@gmail.com)  
-- Data Inicio ............: 04/02/2011 as 20:45
-- Data Atual..............: 07/02/2011 as 18:50
-- Versao..................: 0.05
-- Compilador/Interpretador: PostgreSql/PLpgSQL
-- Sistemas Operacionais...: Linux/Windows
-- SGBD....................: PostgreSql 8.x/9.x
-- Kernel..................: Nao informado!
-- Finalidade..............: Script de criação de estrutura de base de dados da Bíblia Sagrada  
-- ........................: 
-- OBS.....................:  
--
/*
--Poderia se criar dominio, como exemplo abaixo, já que é uma tabela pequena e imutavél, 
--porém por questão de compatibilidade com outros SGBDRs, deixamos como um tabela.
DROP DOMAIN IF EXISTS id_testamento_dominio;
CREATE DOMAIN id_testamento_dominio AS smallint
CHECK 
 (
 VALUE IN 
  (
    1 --Antigo Testamento
  , 2 --Novo Testamento 
  ) 
 );
COMMENT ON DOMAIN id_testamento_dominio IS 'Dominio do Testamento';
 
*/

DROP TABLE IF EXISTS Testamento;
CREATE TABLE testamento
(
   id_testamento smallint NOT NULL PRIMARY KEY
 , nome varchar(21) NOT NULL
);
COMMENT ON TABLE Testamento IS 'Tabela que armazena os Testamentos da Bíblia';
COMMENT ON COLUMN Testamento.id_testamento IS 'Chave primária da tabela';
COMMENT ON COLUMN Testamento.nome IS 'Nome do Testamento';



DROP TABLE IF EXISTS Livro;
--DROP TABLE IF EXISTS Livro CASCADE;
CREATE TABLE Livro  
(
   id_livro serial 
 , ordem_livro smallint NOT NULL 
 , id_testamento smallint NOT NULL
 , nome varchar(42) NOT NULL
 , abrev varchar(4) NOT NULL
 , CONSTRAINT Livro_pkey PRIMARY KEY (id_livro)
 , CONSTRAINT Livro_id_testamento_fkey FOREIGN KEY (id_testamento)
   REFERENCES Testamento (id_testamento) 
 
);
COMMENT ON TABLE Livro IS 'Tabela que armazena os Livros da Bíblia';
COMMENT ON COLUMN Livro.id_livro IS 'Id do Livro da Bíblia';
COMMENT ON COLUMN Livro.ordem_livro IS 'Número de  ordem do Livro da Bíblia';
COMMENT ON COLUMN Livro.id_testamento IS 'Número do Testamento';
COMMENT ON COLUMN Livro.nome IS 'Nome completo do Livro da Bíblia';
COMMENT ON COLUMN Livro.abrev IS 'Nome abreviado do Livro da Bíblia';



DROP TABLE IF EXISTS Palavra;
CREATE TABLE Palavra
(
   id_palavra serial 
 , id_livro integer NOT NULL 
 , capitulo smallint NOT NULL
 , versiculo smallint NOT NULL
 , texto TEXT
 , CONSTRAINT Palavra_pkey PRIMARY KEY (id_palavra)
 , CONSTRAINT Palavra_id_livro_fkey FOREIGN KEY (id_livro)
   REFERENCES Livro (id_livro) 


);
COMMENT ON TABLE Palavra IS 'Tabela que armazena os versiculos da Bíblia';
COMMENT ON COLUMN Palavra.id_livro IS 'Id do Livro da Bíblia';
COMMENT ON COLUMN Palavra.capitulo IS 'Número do capítulo da Bíblia';
COMMENT ON COLUMN Palavra.versiculo IS 'Número do versículo da Bíblia';
COMMENT ON COLUMN Palavra.texto IS 'Texto do versículo da Bíblia';



INSERT INTO Testamento (id_testamento, nome) VALUES (1,'Velho Testamento');
INSERT INTO Testamento (id_testamento, nome) VALUES (2,'Novo Testamento');

SELECT * FROM Testamento;

--linux
 COPY livro(ordem_livro, id_testamento, nome, abrev) 
 FROM '/home/postgres/Biblia_JFARC/2_Livro_JFARC.csv' 
  CSV 
   HEADER;

--windows
 COPY livro(ordem_livro, id_testamento, nome, abrev) 
 FROM 'c:/Biblia_JFARC/2_Livro_JFARC.csv' 
  CSV 
   HEADER;


SELECT * FROM Livro;

--linux
 COPY palavra (id_livro, capitulo, versiculo, texto)  
 FROM '/home/postgres/Biblia_JFARC/3_Palavra_JFARC.csv' 
  CSV  
   HEADER;

--windows

 COPY palavra (id_livro, capitulo, versiculo, texto)  
 FROM 'c:/Biblia_JFARC/3_Palavra_JFARC.csv' 
  CSV  
   HEADER;


SELECT * FROM Palavra;


Eu sinceramente creio que DEUS fala pela sua Palavra, a Bíblia, além da leitura tradicional, já tive diversas experiências usando um recurso o qual chamo de Consulta a Palavra, no qual oro a DEUS, clamando pelo sangue de JESUS, peço que fale comigo através da sua Palavra; fecho os olhos e logo em seguida abro a Biblia (Livro) apontanto para um versiculo, abro os olhos e leio.

Uma dessas experiências vou citar agora:

Certa vez fui acusado injustamente de roubo no meu trabalho e por relexo meu, perdi o documento que comprovava minha inocência, diante de tal situação fui pra casa, na eminência de perder o emprego e pior, ser acusado de ladrão, mas a primeira coisa que fiz foi pedir socorro a quem podia me socorrer, DEUS, e orei ao Senhor que me desse livramento, pois havia perdido o documento, que iria me inocentar.

Então falei com DEUS da seguinte forma:

Fala comigo DEUS, pelo sangue de Jesus a ti clamo, me dá um livramento dessa situação, pois de alguma forma estou errado, porque não vigiei e acabei perdendo esse documento, agora estou sendo acusado injustamente de roubo e estou na eminência de perder o emprego, depois abri a Bíblia e pedi uma palavra, e DEUS falou comigo, através do seguinte versículo da Bíblia:

havendo riscado a cédula que era contra nós nas suas ordenanças, a qual de alguma maneira nos era contrária, e a tirou do meio de nós, cravando-a na cruz.
CL 2:14

No dia seguinte o fornecedor, reconheceu o erro, enviou a sua documentação comprovando a minha inocência e corrigindo a situação, quitando o débito; o montante que teria que pagar caso não conseguir provar minha inocência, era de aprox. 20 vezes o meu salário; fui inocentado, e ainda trabalhei um bom tempo nessa empresa, e após 3 anos de desligado dessa empresa, já trabalhando em outra lugar, um amigo encontrou o tal documento que finalmente provou a minha inocência.

Concluindo essa experiência... posso dizer que DEUS realmente riscou a escrito de dívida, que de alguma forma era contra mim. DEUS É FIEL!

Muitas sãos as aflições do justo, mas o Senhor o livra de todas. Salmos 34:19

Usando esse mesmo conceito de Consulta a Palavra na Bíblia, no universo digital, usei uma função randômica (function) que traz um versículo da Bíblia, aleatoriamente em PL/pgSQL




--
-- Nome Artefato/Programa..: sp_palavra.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann@gmail.com)  
-- Data Inicio ............: 16/07/2009
-- Data Atual..............: 07/02/2011 as 18:50
-- Versao..................: 0.05
-- Compilador/Interpretador: PostgreSql/PLpgSQL
-- Sistemas Operacionais...: Linux/Windows
-- SGBD....................: PostgreSql 8.x/9.x
-- Kernel..................: Nao informado!
-- Finalidade..............: Store Procedure/Function Funcao de Consulta a Palavra, não é apenas uma 
-- ........................: caixinha de promessa, pois toda a Biblia Sagrada é usada para o mesmo
-- OBS.....................:  
--


DROP FUNCTION IF EXISTS Palavra();
CREATE OR REPLACE FUNCTION Palavra() RETURNS TEXT AS
$body$
  SELECT p.texto
      || ' '
      || rtrim(l.abrev)
      || ' '
      || cast(p.capitulo AS text)
      || ':'
      || cast(p.versiculo AS text)
      AS "Consulta a Palavra - Clame pelo Sangue de Jesus!!!" 
    FROM Palavra p
    JOIN Livro l
      ON l.id_livro = p.id_livro 
    JOIN Testamento te
      ON te.id_testamento  = l.id_testamento
ORDER BY RANDOM() 
   LIMIT 1
       ; 
$body$ 
LANGUAGE 'sql';
COMMENT ON FUNCTION Palavra() IS 'Função de Consulta a Palavra (Bíblia Sagrada) não é apenas uma caixinha de promessa, pois toda a Bíblia Sagrada é usada para o mesmo';

SELECT * FROM Palavra();


A chamada dessa function em PL/pgSQL é:

SELECT * FROM Palavra();


Observações interessantes:

Os seguintes números são baseados na versão Nova Tradução na Linguagem de Hoje

Antigo Testamento | Novo Testamento | Total
Livros | 39 | 27 | 66
Capítulos | 929 | 260 | 1.189
Versículos | 23.146 | 7.957 | 31.103


Comparando com outras traduções, esses números podem ser um pouco diferentes:

A versão Almeida Revista e Atualizada, por exemplo, tem 31.104 versículos (o final de 1Samuel 20.42 se torna o versículo 43)

A versão Almeida Revista e Corrigida tem 31.105 versículos (além de 1Samuel 20, o final de Juízes 5.31 se torna o versículo 32).

A versão King James, por sua vez, tem 31.102 versículos, pois ajunta os versículos 14 e 15 de 3João.

Os textos originais em hebraico e grego, por sua vez, trazem um total de 31.171 versículos (21.213 no Antigo Testamento e 7.958 no Novo Testamento).

A maior diferença está no Livro de Salmos:

Vários deles trazem títulos que aparecem como o versículo 1 no texto hebraico e que, nas traduções, não são numerados.

Fonte: www.sbb.org.br

Este artigo usou a Versão Almeida Revista e Corrigida, porém com compatibilidade de versículos em quantidade com a versão King James, isto é, um total de 31102 versículos, conforme script abaixo:
-- Edição Almeida Revista e Corrigida 
select count(*) from Palavra; -- 31102
select 31105 - 31102; -- 3

select * from Livro;
--o final de Juízes 5.31 se torna o versículo 
select * from Palavra where id_livro = 7 and capitulo= 5 and versiculo = 31; 
--o final de 1Samuel 20.42 se torna o versículo 43
select * from Palavra where id_livro = 9 and capitulo= 20 and versiculo = 42; 
--pois ajunta os versículos 14 e 15 de 3João
select * from Palavra where id_livro = 64 and capitulo= 1 and versiculo = 14; 

mas nós pregamos a Cristo crucificado, que é escândalo para os judeus e loucura para os gregos. 1CO 1:23


Que DEUS te abençõe sempre....

sexta-feira, 1 de abril de 2011

O moido do NOT IN, o Postgres estaria mentindo, seria um bug ?

Esse problema-desafio foi exposto por Leonardo Campos, amigo e companheiro de trabalho, poderia ser mais uma questão de concurso público voltado para SGBD Postgres, estilo casca de banana.

Vamos ao Problema:

O moido dessa vez é saber pq o NOT IN não estava funcionando corretamente em alguns casos, no PG 9.0, seria um bug ou banco estava mentindo ?

A experiencia tem me mostrado que o banco não mente, o irônico é dizer isso no dia primeiro de abril, mas, cito a fato de que SQL tem base matemática na teoria dos conjuntos e algebra relacional, e dizer isso de um o banco SGBD Relacional em particular o Postgres, um projeto de muitos anos; é dá murro em ponta de faca.

Apresentando o desafio...

--Problema exposto 

DROP TABLE IF EXISTS tb1;
CREATE TEMP TABLE tb1 
( 
       id_tb1 integer PRIMARY KEY 
     , descricao varchar(28) NOT NULL
); 

DROP TABLE IF EXISTS tb2;
CREATE TEMP TABLE tb2 
(
       id_tb2 integer PRIMARY KEY 
     , descricao varchar(28) NOT NULL 
     , id_tb1 integer NULL 
     , CONSTRAINT fk_tb2_id_tb1 FOREIGN KEY (id_tb1) REFERENCES tb1 (id_tb1) 
);

INSERT INTO tb1 (id_tb1, descricao) VALUES (1,'TESTE1');
INSERT INTO tb1 (id_tb1, descricao) VALUES (2,'TESTE2');
INSERT INTO tb1 (id_tb1, descricao) VALUES (3,'TESTE3');
INSERT INTO tb1 (id_tb1, descricao) VALUES (4,'TESTE4');
INSERT INTO tb1 (id_tb1, descricao) VALUES (5,'TESTE5');
INSERT INTO tb1 (id_tb1, descricao) VALUES (6,'TESTE6');
INSERT INTO tb1 (id_tb1, descricao) VALUES (7,'TESTE7');

SELECT * FROM tb1;
/* Resultado 
 id_tb1 | descricao 
--------+-----------
      1 | TESTE1
      2 | TESTE2
      3 | TESTE3
      4 | TESTE4
      5 | TESTE5
      6 | TESTE6
      7 | TESTE7
(7 rows)
*/

INSERT INTO tb2 (id_tb2, descricao, id_tb1) VALUES (8,'TESTE1',1);
INSERT INTO tb2 (id_tb2, descricao, id_tb1) VALUES (9,'TESTE2',2);
INSERT INTO tb2 (id_tb2, descricao, id_tb1) VALUES (10,'TESTE3',3);
INSERT INTO tb2 (id_tb2, descricao, id_tb1) VALUES (11,'TESTE4',4);
INSERT INTO tb2 (id_tb2, descricao, id_tb1) VALUES (12,'TESTE5',5);
INSERT INTO tb2 (id_tb2, descricao, id_tb1) VALUES (13,'TESTE7',NULL);

SELECT * FROM tb2;
/* Resultado
 id_tb2 | descricao | id_tb1 
--------+-----------+--------
      8 | TESTE1    |      1
      9 | TESTE2    |      2
     10 | TESTE3    |      3
     11 | TESTE4    |      4
     12 | TESTE5    |      5
     13 | TESTE7    |   NULL   
(6 rows)

*/

--Query 1
     SELECT * 
       FROM tb1 
      WHERE id_tb1 IN 
                         (
                              SELECT id_tb1
                                FROM tb2
                         )
          ;
/* Resultado
 id_tb1 | descricao 
--------+-----------
      1 | TESTE1
      2 | TESTE2
      3 | TESTE3
      4 | TESTE4
      5 | TESTE5
(5 rows)
*/          

--Query 2 (Seria um bug do Postgres? Retornou nada!)      
     SELECT * 
       FROM tb1 
      WHERE id_tb1 NOT IN 
                         (
                              SELECT id_tb1
                                FROM tb2
                         )
          ;
--Nada, aparentemente o correto seria trazer os registros especificados, será o que SGBD (o banco) Postgres está mentindo ?
/* Resultado
 id_tb1 | descricao 
--------+-----------
(0 rows)
*/

--Análise 1, uma consulta equivalente com not in, apenas para testar
     SELECT * 
       FROM tb1 
      WHERE id_tb1 <> ALL
                         (
                              SELECT id_tb1
                                FROM tb2
                         )
          ;
--Idem ao problema apresentado na Query 2 
/* Resultado
 id_tb1 | descricao 
--------+-----------
(0 rows)
*/

--Análise 2, o que está havendo?
     SELECT 
   DISTINCT *
       FROM tb2
   ORDER BY 1
          ;
--Existe um campo NULL no final do resultado. Será que esse NULL se propaga no NOT IN, mas pq não no IN   ?
/* Resultado
 id_tb2 | descricao | id_tb1 
--------+-----------+--------
      8 | TESTE1    |      1
      9 | TESTE2    |      2
     10 | TESTE3    |      3
     11 | TESTE4    |      4
     12 | TESTE5    |      5
     13 | TESTE7    |   NULL   
(6 rows)
*/

-- Exemplo 1, Solução

     SELECT * 
       FROM tb1 
      WHERE id_tb1 NOT IN 
                         (
                              SELECT id_tb1
                                FROM tb2
                               WHERE id_tb1 IS NOT NULL
                         )
          ;
--Com a inclusão da condicional removendo os nulos, deu certo, pq?, Qual a explicação disso?
/* Resultado 
 id_tb1 | descricao 
--------+-----------
      6 | TESTE6
      7 | TESTE7
(2 rows)
*/





Conclusão

No Postgres o null se propaga, isto é, qualquer coisa concatenada com NULL se torna NULL exemplo:

SELECT 'NULL' || NULL AS teste1; 

SELECT 'Isto ' || 'é' ||' apenas '|| ' um ' || NULL || ' teste .... ' AS teste2; 


Esse mesmo conceito se aplica também ao NOT IN.

Então:

Deve ser observado que se o resultado da expressão à esquerda for nulo,
ou se não houver nenhum valor igual à direita e uma das linhas à direita tiver o valor nulo,
o resultado da construção NOT IN será nulo, e não verdade.
Isto está de acordo com as regras normais do SQL para combinações booleanas de valores nulos. [1]
Idem na versões demais versões do Postgres e a ultima 9.0 [2]

Concluindo isso não é um bug mas sim um comportamento padrão do NOT IN, que está em conformidade com as normas SQL/ANSI,
lembrando que uso de NOT IN não é recomendável quando se pensa em desempenho.

E finalmente o banco (SGBDR) não mente! :)

[1]
http://pgdocptbr.sourceforge.net/pg80/functions-subquery.html#FUNCTIONS-NOT-IN-SUBQ
[2]
http://www.postgresql.org/docs/9.0/static/functions-comparisons.html#AEN16842