Muitas vezes sentimos a necessidade de usar o conceito de cross-database, isto é, criar consultas SQL cruzando informações entre campos de tabelas que estão em Banco de Dados distintos. Exemplo, a tabela pessoa tem o campo id do banco database1, vamos cruzar informações com a tabela pessoa com o campo id do banco database2 gerando uma consulta SQL como esta:
SELECT db1.id, db2.id
FROM database1.pessoa db1, Database2.pessoa db2
Logicamente, esta é uma query ilustrativa, mas serve como exemplo para demonstrar o que é um Cross-Database. Fazendo uma referência como esta, é possível acessar diversos objetos do banco, por exemplo, db2 (tables, views, indexes, functions etc.).
Apenas com a query ilustrativa podemos imaginar o quanto isso é útil. Nossas aplicações agora podem ser divididas em diversos bancos por diversos motivos: Segurança e organização são apenas dois exemplos.
A partir da versão 7.3, o PostgreSQL iniciou o suporte a Schema, no qual é possível criar um espaço lógico (namespace) dentro do banco de dados para armazenar os objetos: Dados, tabelas, funções, sequence, etc.
Este conceito – SCHEMA - é semelhante ao cross-database, a diferença é que o cross-database relaciona objetos (Tabelas, índices, sequence, etc) de banco de dados distintos, já o Schema relaciona objetos (Tabelas, índices, sequence, etc) que estão no MESMO BANCO DE DADOS, mas em estruturas lógicas (namespace) distintas – schema.
Exemplo
Vejamos uma demonstração básica do Esquema, dentro do banco teste:
teste_carlos=# CREATE SCHEMA sch_sqlmagazine1;
CREATE SCHEMA
teste_carlos=# CREATE TABLE sch_sqlmagazine1.info (id INT, txt TEXT);
CREATE TABLE
teste_carlos=# INSERT INTO sch_sqlmagazine1.info VALUES(1, 'Este é o Schema sch_sqlmagazine1');
INSERT 149312822 1
teste_carlos=# CREATE SCHEMA sch_sqlmagazine2;
CREATE SCHEMA
teste_carlos=# CREATE TABLE sch_sqlmagazine2.info (id INT, txt TEXT);
CREATE TABLE
teste_carlos=# INSERT INTO sch_sqlmagazine2.info VALUES(1, 'Este é o Schema sch_sqlmagazine2');
INSERT 149312829 1
teste_carlos=# SELECT sch_sqlmagazine1.info.txt,
teste_carlos=# sch_sqlmagazine2.info.txt
teste_carlos-# FROM sch_sqlmagazine1.info, sch_sqlmagazine2.info
teste_carlos-# WHERE
teste_carlos-# sch_sqlmagazine1.info.id=sch_sqlmagazine2.info.id;
|
txt |
txt |
|
Este é o Schema sch_sqlmagazine1 |
Este é o Schema sch_sqlmagazine2 |
SELECT sch_sqlmagazine1.info.txt
Sch_sqlmagazine1 = Nossa Schema
Info = Tabela
Txt = Coluna.
Logo, estou buscando os dados da coluna txt que está na tabela info do Schema sch_sqlmagazine1.
O Schema Público
Quando criamos um novo Banco de Dados, o schema “public” é criado por padrão e qualquer objeto que criarmos neste banco, por padrão será criado no schema public:
teste_carlos=# create table tabela_exemplo(id int4);
teste_carlos=# \dt
|
List of relations | |||
|
Schema |
Name |
Type |
Owner |
|
public |
tabela_exemplo |
table |
postgres |
Uso Prático do Schema
Como foi comentado, qualquer objeto criado sem especificar um SCHEMA é criado por padrão no public. No entanto, é possível definirmos um OUTRO schema como padrão através de uma variável interna do PostgreSQL chamado search_path. Esta variável informa ao banco PostgreSQL qual é o Usuário Padrão e o Schema padrão para manipular os objetos.
teste_carlos=# SHOW search_path;
search_path
--------------
$user,public
(1 row)
Nestas configurações-padrão, o $user é o nome do usuário corrente, ou seja, o usuário no qual logamos no Banco de Dados:
|
postgres@venus:~$ psql teste_carlos -U postgres Type: \copyright for distribution terms teste_carlos=# |
Podemos mudar o Schema padrão alterando nosso Search_Path:
teste_carlos=# SET search_path TO sch_sqlmagazine1;
SET
teste_carlos=# SHOW search_path;
search_path
------------------
sch_sqlmagazine1
(1 row)
teste_carlos=# SET search_path TO sch_sqlmagazine1;
teste_carlos=# \dv
No relations found.
teste_carlos=# \dt
|
List of relations | |||
|
Schema |
Name |
Type |
Owner |
|
sch_sqlmagazine1 |
info |
table |
postgres |
Veja como alterar o Schema padrão de forma permanente:
ALTER Database teste_carlos SET search_path TO $user,sch_sqlmagazine1;
teste=# SET search_path TO sch_sqlmagazine2, sch_sqlmagazine1;
SET
Como o Schema sch_sqlmagazine1 é agora o padrão:
teste_carlos=# SELECT txt FROM info;
Txt
----------------------------------
Este é o Schema sch_sqlmagazine1
(1 row)
teste_carlos-# \dt
|
List of relations | |||
|
Schema |
Name |
Type |
Owner |
|
sch_sqlmagazine1 |
info |
table |
postgres |
Permissão e Segurança
Um schema só pode ser criado por usuários com permissão para isso (geralmente o usuário ”postgres”).
Para criar um Schema no qual queremos que OUTRO usuário (que não seja o usuário “postgres”) acesse o Schema (Tarzan), fazemos o seguinte:
CREATE SCHEMA tarzan AUTHORIZATION tarzan;
ou
CREATE SCHEMA AUTHORIZATION tarzan;
No caso, criamos o schema tarzan que será acessado pelo usuário tarzan.
Schemas Especiais
Todo Banco de Dados PostgreSQL contém schemas especiais que são necessários para o Backend (processo postgreSQL que estabelece a conexão com o cliente) por isso, eles não podem ser removidos ou renomeados.
Os Schemas especiais:
- pg_catalog: Contém informações sobre as tabelas, funções, views e metainformações sobre o banco;
- pg_temp_x: É um schema usado na criação e armazenamento de tabelas temporárias;
- pg_toast: Relações internas do PostgreSQL usadas para várias atividades, como a reindexão (vacuum), por exemplo.
Limitações
Não é possível transferir objetos entre Schemas. Para solucionar isso, precisamos contornar o problema usando SQL. Por exemplo: com a query a seguir, iremos criar um novo schema com uma nova tabela e preencher esta tabela com dados do schema antigo:
CREATE TABLE new_schema.tabela AS SELECT * FROM old_schema.tabela
Na versão 7.4 é possível renomear um schema através do seguinte comando:
ALTER SCHEMA .. RENAME TO ...
Abraços e até logo.
