O Elefante arborícola

(1)

. . . .

Sumário Introdução

,230 0 00 0 0,

O Elefante arborícola

Consultas recursivas com expressões comuns de tabelas

Leandro Guimarães Faria Corcete Dutra

1_{Comunidade Brasileira de PostgreSQL} 2_ArsData

III Conferência brasileira de PostgreSQL (2009)

Leandro Guimarães Faria Corcete Dutra PostgreSQL BR, ArsData O Elefante arborícola

(2)

(3)

. . . . Sumário Introdução

Besteirol

I NoSQL I SGBDs OO I SGBDs multivalorados I Prevayler I LDAP I …

(4)

Besteirol

(5)

Besteirol

(6)

Besteirol

(7)

Besteirol

(8)

Besteirol

(9)

. . . .

Problemas aparentes

I Diﬁculdade de aprendizado

I Não OO

I Não parecido com C, Java &c

I Computacionalmente incompleto

I Não lidar com dados ‘ricos’ ou ‘desestruturados’

I Não lidar com hierarquias

I …

I Mentiras! :-)

(10)

Problemas aparentes

I Não OO

I …

(11)

. . . .

Problemas aparentes

I Não OO

I …

I Mentiras! :-)

(12)

Problemas aparentes

I Não OO

I …

(13)

. . . .

Problemas aparentes

I Não OO

I …

I Mentiras! :-)

(14)

Problemas aparentes

I Não OO

I …

(15)

. . . .

Problemas aparentes

I Não OO

I …

I Mentiras! :-)

(16)

Problemas aparentes

I Não OO

I …

(17)

. . . .

Falsos problemas

I Forma mais simples já imaginada de lidar com dados

I Modelo relacional é ortogonal com OO

I SQL não é única implementação relacional possível

I Dados ricos ou desetruturados são ortogonais (tipos)

I SQL é computacionalmente completo desde o ISO SQL:2008…

I …e o PostgreSQL, desde a versão 8.4…

I …graças à recursividade (SQL99) e funções de janela

I Recursividade nos dá hierarquia, grafos &c!

(18)

Falsos problemas

(19)

. . . .

Falsos problemas

(20)

Falsos problemas

(21)

. . . .

Falsos problemas

(22)

Falsos problemas

(23)

. . . .

Falsos problemas

(24)

Falsos problemas

(25)

. . . .

Soluções para hierarquias‽

I Processamento externo

I Caro em recursos — escalabilidade

I Não relacional

I Enumeração de caminhos materializados

I Funções & procedimentos armazenados

I Conjuntos aninhados (nested sets)

I Caro na escrita

I Complicado

I Estúpido

I Oracle CONNECT BY

I Antigo, e implementado no contrib

I Incomum, e obsoleto no Oracle 11gr2

I Recursividade: expressões comuns de tabelas.

(26)

Soluções para hierarquias‽

I Não relacional

I Caro na escrita

I Complicado

I Estúpido

I Oracle CONNECT BY

(27)

. . . .

Soluções para hierarquias‽

I Não relacional

I Caro na escrita

I Complicado

I Estúpido

I Oracle CONNECT BY

(28)

Soluções para hierarquias‽

I Não relacional

I Caro na escrita

I Complicado

I Estúpido

I Oracle CONNECT BY

(29)

. . . .

Soluções para hierarquias‽

I Não relacional

I Caro na escrita

I Complicado

I Estúpido

I Oracle CONNECT BY

(30)

Soluções para hierarquias‽

I Não relacional

I Caro na escrita

I Complicado

I Estúpido

I Oracle CONNECT BY

(31)

. . . .

Soluções para hierarquias‽

I Não relacional

I Caro na escrita

I Complicado

I Estúpido

I Oracle CONNECT BY

(32)

Soluções para hierarquias‽

I Não relacional

I Caro na escrita

I Complicado

I Estúpido

I Oracle CONNECT BY

(33)

. . . .

Soluções para hierarquias‽

I Não relacional

I Caro na escrita

I Complicado

I Estúpido

I Oracle CONNECT BY

(34)

Soluções para hierarquias‽

I Não relacional

I Caro na escrita

I Complicado

I Estúpido

I Oracle CONNECT BY

(35)

. . . .

Soluções para hierarquias‽

I Não relacional

I Caro na escrita

I Complicado

I Estúpido

I Oracle CONNECT BY

(36)

Soluções para hierarquias‽

I Não relacional

I Caro na escrita

I Complicado

I Estúpido

I Oracle CONNECT BY

(37)

. . . .

Soluções para hierarquias‽

I Não relacional

I Caro na escrita

I Complicado

I Estúpido

I Oracle CONNECT BY

(38)

Recursividade

I Solução correta para o problema da hierarquia

I Solução genérica: grafos &c

I Interessante: conjuntos de Mandelbrot, caixeiro�viajante &c

I Relacional (na medida do possível em SQL)

(39)

. . . .

Recursividade com expressões comuns de tabelas

I Padrão ISO SQL:1999

I prenunciado como a solução desde 1997 ao menos

I relacional, ao menos em espírito e na prática

I veloz, econômico…

I e (relativamente) simples

I ISO SQL, Yoshiyuki Asaba, Ishii Tatsuo, Jeﬀ Davis,

Gregory Stark, Tom Lane, David Fetter, comunidade japonesa

(40)

Expressões de tabelas: WITH

WITH a p e l i d o ( l i s t a _ a t r i b u t o s ) AS ( c o n s u l t a ) [ , … ] SELECT l i s t a _ p r o j e ç ã o

FROM a p e l i d o [ , … ] …

A ordem em que aparecem os apelidos é essencial. Uma tabela para o exemplo: CREATE TABLE p e d i d o s ( r e g i ã o , p r o d u t o , v a l o r ,

(41)

. . . .

WITH v e n d a s _ r e g i ã o AS

(SELECT r e g i ã o , SUM ( montante ) AS t o t a i s FROM p e d i d o s

GROUP BY r e g i ã o ) , m e l h o r e s _ r e g i õ e s AS (SELECT r e g i ã o

FROM v e n d a s _ r e g i ã o

WHERE t o t a i s > (SELECT SUM( t o t a i s ) / 1 0 FROM v e n d a s _ r e g i ã o ) ) SELECT r e g i ã o , p r o d u t o , SUM( q u a n t i d a d e ) AS u n i d a d e s , SUM( montante ) AS v a l o r FROM p e d i d o s WHERE r e g i ã o IN (SELECT r e g i ã o FROM m e l h o r e s _ r e g i õ e s ) GROUP BY r e g i ã o , p r o d u t o ;

(42)

Mecanismo de recursão

I Condição inicial

I Passo de recursão (consulta)

I Condição terminal

Se não…

I GNU Hurd

I Gnu Não é Unix

(43)

. . . .

Expressões recursivas de tabelas

WITH RECURSIVE a p e l i d o [ ( a t r i b u t o s ) ] AS ( t e r m o _ n ã o _ r e c u r s i v o UNION [ ALL ] t e r m o _ r e c u r s i v o ) c o n s u l t a ;

(44)

Exemplo simplicíssimo, do manual

WITH RECURSIVE t ( n ) AS (VALUES ( 1 ) UNION ALL SELECT n + 1 FROM t WHERE n < 100 ) SELECT SUM ( n ) FROM t ;