Baixe Criando tabelas agrupadas: Criação de tabelas e chaves em PostgreSQL e outras Notas de estudo em PDF para Informática, somente na Docsity!
- A duplicação de dados exige pouco espaço no armazenamento;
- É muito pouco provável que os dados mudem.
19.3 Criando tabelas agrupadas
Realizada a criação das tabelas enquete e resposta, veremos como podemos realizar o agrupamento das tabelas. Na criação das mesmas todas as colunas estão com letras minúsculas, podendo ter sido feito com letras maiúsculas, mas o PostgreSQL ignora e considera todos os nomes de coluna em letra minúscula. O único modo de fazer com que o nome da tabela ou o nome da coluna seja considerada em caixa alta é na de nição da tabela, usar aspas duplas no nome do campo, como em "Nome do usuário", inclusive sendo permitido usar espaços. Não é recomendado essa prática pois cada vez que deve-se referenciar a coluna, devemos colocar as aspas duplas, di cultando a leitura dos comandos. Sem o uso das aspas duplas, nomes de coluna só podem ter letras, números e underscore. Qualquer outro caractere não é permitido. Em seguida, teremos os comandos de criação das tabelas antes desenhadas.
php=> CREATE TABLE enquete ( php(> id serial, php(> enquete char(300), php(> data date php(> ); NOTICE: CREATE TABLE will create implicit sequence 'enquete_id_seq' for SERIAL column 'enquete.id' NOTICE: CREATE TABLE/UNIQUE will create implicit index 'enquete_id_key' for table 'enquete' CREATE
php=> CREATE TABLE resposta ( php(> id serial, php(> resposta char(200), php(> contador integer, php(> id_resposta integer php(> ); NOTICE: CREATE TABLE will create implicit sequence 'resposta_id_seq' for SERIAL column 'resposta.id' NOTICE: CREATE TABLE/UNIQUE will create implicit index 'resposta_id_key' for table 'resposta' CREATE
Como já explanado anteriormente, o tipo serial será explicado mais a frente. Essas duas tabelas tem uma relação de um pra n, pois uma enquete pode ter várias respostas mas uma resposta é de apenas uma enquete. Agora, adicionaremos uma pergunta e colocaremos duas respostas.
php=> INSERT INTO enquete (enquete,data) VALUES
php-> ('Quando será que a greve acabará','07/11/2001'); INSERT 19013 1
php=> INSERT INTO resposta (resposta,contador,id_enquete) VALUES php-> ('No natal',0,1); INSERT 19073 1
php=> INSERT INTO resposta (resposta,contador,id_enquete) VALUES php-> ('Quando Paulo Renato morrer',0,1); INSERT 19074 1
php=> INSERT INTO resposta (resposta,contador,id_enquete) VALUES php-> ('Quando a universidade acabar',0,1); INSERT 19075 1
Neste exemplo, o id_enquete na tabela é 1 pois a primeira resposta recebe esse número com o uso do serial.
19.4 Realizando agrupamentos
Quando os dados estão separados através de múltiplas tabelas, recuperar os dados nessas tabelas se torna uma importante questão. Outro exemplo mostra como encontrar a enquete para dada resposta na tabela resposta.
php=> SELECT id,id_enquete FROM resposta WHERE resposta='No natal'; id | id_enquete ----+------------ 1 | 1 (1 row)
php=> SELECT enquete FROM enquete WHERE id=1; enquete
Quando será que a greve acabará (1 row)
Na primeira consulta, achamos qual o número da enquete a qual pertence a resposta 'No Natal' (o campo id_enquete). O id_enquete foi 1. O campo id_enquete tem o mesmo dado do campo id na tabela enquete. Achado esse valor, consultamos em uma segunda query, qual era a enquete que tinha o id igual a 1. Nós chamamos essas consultas de agrupamento manual, devido ao fato do usuário ter tomado o resultado da primeira query e ter colocado o resultado na cláusula WHERE na segunda query. Já esta query mostra quais são os elementos necessários para realizar o agrupamento direto.
- O código de duas letras é fácil para os usuários digitarem e de fácil memorização;
- Os códigos dos estados são únicos;
- Códigos de estados não mudam;
- Agrupamento usando chave com 2 caracteres não são muito mais lentos que agrupamento numérico;
- A diferença de armazenamento não é muito maior para guardar dois caracteres em vez de números.
Essencialmente, duas escolhas para chaves de agrupamento existem: números de indenti cação ou pequenos códigos numéricos.Nenhuma regra universal dita quando você deverá escolher códigos alfanu- méricos ou números de identi cação. Os estados do Brasil são claramente um exemplo em que é melhor o uso de códigos de caracteres, por que só 27 existem. Os códigos resultantes são pequenos, únicos, e bem conhecidos pela maioria dos usuários. Geralmente é melhor usar códigos a números de identi cação quando o campo exige poucos registros.
19.6 Agrupamentos um-para-muitos
Até agora, no agrupamento de duas tabelas, uma linha de uma tabela combinou exatamente com uma linha de outra tabela, realizando o chamado agrupamento um-para-um. Mas se unirmos agora todas as respostas que fazem parte de uma única enquete? Múltiplas respostas seriam impressas. No agrupamento um-para-muitos, uma linha na tabela enquete seria agrupada a mais de uma linha na tabela respostas. Agora suponha que nenhuma resposta existe para dada enquete. Mesmo que existe uma enquete válida, se não existir nenhuma resposta para essa enquete, nenhuma linha será retornada. Nós podemos chamar esse caso de agrupamento de um-para-nenhum, o chamado outer join.
php=> SELECT resposta FROM enquete,resposta WHERE enquete.id=1; resposta
No natal Quando Paulo Renato morrer Quando a universidade acabar (3 rows)
Para a enquete com o código igual a 1, houve o retorno de 3 linhas, indicando claramente o agrupamento um para muitos.
19.7 Chaves primárias e Chaves estrangeiras
Um agrupamento é realizado pela comparação de duas colunas, como em enquete.id e resposta.id_enquete. A enquete.id é chamada de chave primária porque ela é o identi cador único e primário para a tabela enquete. A resposta.id_enquete é chamada de chave estrangeira por que ela armazena dados de uma chave primária de outra tabela.
Esta seção lidou com técnica a técnica de criar uma disposição organizada usando múltiplas tabe- las. Para adquirir esta habilidade é necessário prática. Sempre espere mudar o seu banco de dados várias vezes para a criação de um banco de dados organizado e de nitivo. Uma boa disposição pode fazer seu trabalho car mais fácil. A má disposição pode tornar seu trabalho em consultas um inferno. Ao começar a criar suas tabelas para uma necessidade real, você será capaz de identi car os bancos de dados bem planejados. Não tenha preguiça ou medo de recomeçar seu banco de dados do zero, pois é um duro trabalho, mas quando feito apropriadamente, as consultas tornam-se mais fáceis de serem realizadas.
php=> SELECT OID,* FROM enquete; oid | id | enquete | data -------+----+---------+------------ 19013 | 1 | Quando será que a greve acabará | 2001-07- (1 row)
Caso queiramos usar uma tabela que contenha chave estrangeira do tipo OID, temos que na sua criação, em vez de usar o tipo integer ou mesmo char, usar o tipo OID como no exemplo abaixo.
php=> CREATE TABLE teste ( php(> id integer, php(> nome char(100), php(> chave oid php(> ); CREATE
Uma coluna do tipo OID é semelhante a uma coluna do tipo integer, mas de nindo-a como um tipo OID documenta que a coluna receberá valores OID. Não confunda uma coluna com o tipo OID com a coluna chamada OID. Toda linha tem uma coluna chamada OID, a qual normalmente é invisível. Uma linha pode ter zero, uma ou mais colunas com o tipo OID. Uma coluna do tipo OID não é automaticamente atribuída valor especial a ela. Somente a coluna OID é atribuída um valor especial.
20.2 Limitações dos Números de Identi cação de Objetos
Esta seção cobre as três limitacões dos OIDs.
20.2.1 Numeração não sequencial
A natureza global de um OID signi ca que a maioria dos OIDs de uma tabela não são sequenciais. Por exemplo, se for inserida uma resposta hoje e um outra só amanhã, as duas respostas não terão números sequenciais. De fato, os seus OIDs podem ser diferentes em milhares por que qualquer comando INSERT em outros banco de dados em outras tabelas incrementariam o contador de objetos. Se o OID não está visível aos usuários isso não é um problema. A numeração não sequencial não interfere o processamento de query's. Entretanto, se os usuários podem ver esses números, parece estranho os grandes buracos entre esses números.
20.2.2 Não modi cável
Um OID é atribuído para cada linha no comando INSERT. UPDATE não pode modi car esse campo gerado pelo sistema.
20.2.3 Não é feito o back up por padrão
Durante a cópia de segurança do banco de dados, o OID normalmente não é copiado. Um ag deve ser usado para habilitar essa cópia.
20.3 Sequências
PostgreSQL oferece outro método de numeração de linhas sequências. Sequências são contadores nomeados criados por usuários. Após sua criação, uma sequência pode ser atribuída para uma tabela como um DEFAULT para a coluna. Usando sequências, números únicos podem ser automaticamente gerados durante o comando INSERT. A vantagem das sequências é que elas evitam os buracos como no caso dos OIDs 2. Sequências são ideais para o uso de um número de identi cação visível pelo usuário. Se uma resposta é criada hoje e outra amanhã, as duas linhas terão números sequenciais porque nenhuma outra tabela reparte o contador 3.
20.4 Criando Sequências
Sequências não são criadas automaticamente, como os OIDs. Ao invés, é necessário usar o comando CREATE SEQUENCE. Três funções controlam o contador, como mostrado na tabela 23.
Função Ação nextval('nome') Retorna o próximo valor da sequência e atualiza o contador curval('nome') Retorna o valor corrente da sequência setval('nome','valor') Muda o valor do contador para o especi cado no segundo argumento Tabela 23: Funções de Acesso aos contadores
Abaixo, alguns exemplos na criação de sequências e do uso das funções acima descritas.
php=> CREATE SEQUENCE ph_teste; CREATE
php=> SELECT nextval('ph_teste'); nextval
1 (1 row)
php=> SELECT currval('ph_teste'); currval
1 (1 row)
php=> SELECT setval('ph_teste','20'); setval
20 (1 row) (^2) Os buracos podem ocorrer quando uma transação é abortada (^3) Tabelas diferentes podem usar o mesmo contador
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'professor_id_key' for table 'professor' CREATE
Algumas pessoas imaginam porque OIDs e sequências são necessárias. Por que um usuário do banco de dados não pode encontrar o maior número em uso, adicionar um, e usar o resultado para numerar a próxima linha a ser adicionada? Realmente, OIDs e sequências são preferidas devidos a vários fatos:
- Performance;
- Concorrência;
- Padronização.
Primeiramente, usualmente é um processo demorado procurar todos os números que estão sendo usados correntemente para encontrar o próximo número possível. Referir-se a um contador localizado separadamente é mais rápido. Segundo, se um usuário achar o número mais alto, e um outro usuário está procurando pelo número mais alto ao mesmo tempo, os dois usuários podem achar o mesmo número. É claro, neste exemplo, o número deixaria de ser único. Tal problema de concorrência não ocorre com OIDs e sequências. Terceiro, é mais con ável usar um número dado pelo bando de dados do que um número baseado em uma consulta manual.
21 Performance
21.1 Índices
Quando acessando uma tabela, PostgreSQL normalmente lê do começo para o nal da tabela, procu- rando por registros relevantes. Com um índice, é possível achar rapidamente valores especí cos no índice, podendo assim ir diretamente para os registros procurados. Desse modo, índices permitem uma procura rápida de linhas especí cas em uma tabela. Por exemplo, considere a query SELECT * FROM aluno where id=145;. Sem um índice, PostgreSQL deve procurar em toda a tabela procurando por linhas que tenham 145 no campo 145. Com um índice em id, o banco de dados pode ir direto para as linhas onde o id é 145. Para tabelas grandes, pode-se levar minutos para checar cada linha. Usando um índice, encontrar a linha desejada leva apenas segundos.PostgreSQL não cria índices automaticamente^4. Em vez disso, usuários devem criar índices em colunas muito utilizadas em cláusulas WHERE. Para criar um índice, usa-se o comando CREATE INDEX nome , como mostrado abaixo.
php=> CREATE INDEX aluno_nome_idx ON aluno (nome); CREATE
Onde nome_aluno é o nome do índice criado, aluno é a tabela a qual o índice vai se referenciar, e nome é o campo a ser indexado. Apesar de ser liberado o uso de qualquer nome para o índice, um bom uso de criação de nome de índices é como foi mostrado. O nome do índice contém a informação da tabela que ele se faz parte aluno, contém o nome da coluna indexada, nome, e após isso, o underscore idx para indicar que foi criado um índice.
Você pode criar quantos índices desejar. É claro, porém, um índice em uma coluna raramente usada é desperdício de espaço de disco. Também, a performance na verdade pode diminuir caso existam muitos índices, por que qualquer mudança de registros é necessária uma atualização em cada índice.
Também é possível a criação de um índice que indexe mais de uma coluna. A única mudança do co- mando anterior na criação de um índice simples é a inclusão de mais uma coluna entre os parentêses. Esse tipo de índice é indicado quando indexamos as linhas de uma tabela, mas mesmo assim, temos muitas linhas com o mesmo valor da primeira linha indexada. Nesse caso, começa a ser feito a indexação pela segunda coluna do índice.
Índices podem ser úteis também em agrupamentos. Eles podem aumentar a velocidade na pesquisa quando é usado cláusulas ORDER BY.
21.2 Indíces Únicos
A única diferença para o índice ordinário, como o próprio nome diz, eles previnem valores duplicados de ocorrerem na tabela, na coluna indexada. O comando é semelhante, apenas com o acréscimo da palavra (^4) exceção quando se utiliza o campo do tipo serial
22 Controlando Resultados
Quando uma query SELECT é digitada no psql, o comando vai até o servidor de banco de dados, é executado, e o resultado é enviado de volta ao psql para ser mostrado. PostgreSQL permite que seja feito um controle sobre quais linhas são retornadas.
22.1 Limites
As cláusulas LIMIT e OFFSET do comando SELECT permitem ao usuário escolhes quais linhas a serem retornadas.
php=> SELECT COUNT(id) FROM aluno; count
2 (1 row)
A função count conta todos os registros existentes na tabela. Como visto, ela retornou o valor 2, indicando que existem apenas dois registros existem na tabela.
php=> SELECT * FROM aluno ORDER BY id LIMIT 2; id | nome | tel ----+------+----------------- 1 | Osama | 666666 2 | Saddam | 666 (2 rows)
php=> SELECT * FROM aluno ORDER BY id LIMIT 2 OFFSET 1; id | nome | tel ----+--------+---- 2 | Saddam | 666 (1 row)
Constate que na segunda consulta apenas uma linha foi retornada. Isso ocorreu devido a existência de apenas dois registros. Portanto, quando a saída da consulta foi deslocadas usando OFFSET, mesmo com o LIMIT indicando duas linhas, apenas uma linha surgiu como resposta. Veri que que as duas query usam ORDER BY. Apesar dessa cláusula não ser necessária, LIMIT sem ORDER BY retorna linhas randômicas da consulta, o que não é muito útil.
LIMIT aumenta a performance reduzindo o número de linhas retornadas ao cliente.
23 Administração de Tabelas
Essa seção mostra como criar tabelas temporárias e como realizar a alteração de tabelas ou de colunas.
23.1 Tabelas Temporárias
Tabelas temporárias são tabelas de curta duração de vida elas só existem durante a sessão do banco de dados. Quando a sessão de banco de dados termina, suas tabelas temporárias são automaticamente destruídas. O exemplo na tabela 24ilustra esse conceito. Nele, CREATE TEMPORARY TABLE cria uma ta- bela temporária. Na saída do programa psql, a tabela temporária é destruída. Quando for reiniciado o programa psql, veri camos que não existe mais tal tabela.
As tabelas temporárias são visíveis apenas para a sessão onde foi criada, elas permanecem invisíveis para os outros usuários. De fato, vários usuários podem criar tabelas temporárias com o mesmo nome, e cada usuário verá apenas a tabela criada por ele.
Usuário1 Usuário 2 CREATE TEMPORARY TABLE temptest (col INTEGER) CREATE TEMPORARY TABLE temptest (col INTEGER) INSERT INTO temptest VALUES (1) INSERT INTO temptest VALUES (2) SELECT col FROM temptest retorna 1 SELECT col FROM temptest retorna 2 Tabela 24: Duas sessões diferentes
Seu uso é ideal para armazenar dados intermediários usado pela sessão. Por exemplo, suponha que é necessário realizar várias consultas para uma query complexa. Uma estratégia e ciente é realizar a consulta uma vez, e depois guardar o valor em uma tabela temporária.
SELECT * INTO TEMPORARY telfonern FROM aluno WHERE tel like '84%'
Esse comando criou uma tabela temporária com dados 'herdados' da tabela aluno contendo os registros que tenham 84 no começo do número do telefone.
23.2 Alterações de Tabelas
ALTER TABLE permite as seguintes operações:
- Renomear tabelas;
- Renomear colunas;
- Adicionar colunas;
- Adicionar colunas com valores padrão;
- Remover colunas com valores padrão.
24 Chaves e constantes
Essa seção cobrirá a última parte a ser vista no curso. Referências e chaves são os tópicos mais importantes para a criação de um banco de dados relacional, evitando assim a criação de um bando de dados sem utilidade.
24.1 Not Null
A palavra NOT NULL evita o aparecimento de valores nulos de aparecer em uma coluna. Essa cláusula deve ser colocada após o tipo da coluna. Após isso, qualquer inserção que tente colocar um valor nulo na coluna descrita, falhará, gerando um erro de execução. Uma boa saída para evitar esse tipo de erro é colocar um valor padrão para a coluna, podendo ser assim a de nição.
php=> CREATE TABLE notenull ( php(> id INTEGER NOT NULL DEFAULT 32); CREATE
24.2 Unique
A cláusula UNIQUE evita a existência de valores duplicados de aparecerem na coluna. É implementado pela criação de um índice único na coluna. O exemplo abaixo ilustra o uso da cláusula.
php=> CREATE TABLE unico (numero INTEGER UNIQUE); NOTICE: CREATE TABLE/UNIQUE will create implicit index 'unico_numero_key' for table 'unico' CREATE
24.3 Chave Primária
A palavra PRIMARY KEY , de ne a coluna que identi cará unicamente cada linha, é uma combinação do uso das cláusulas NOT NULL e UNIQUE. Com esses tipos de restrições, UNIQUE evita duplicação enquanto NOT NULL evita a inclusão de valores nulos. Abaixo temos a criação de uma chave primária.
php=> CREATE TABLE chaveprimaria (col INTEGER PRIMARY KEY); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'chaveprimaria_pkey' for table 'chaveprimaria' CREATE php=> \d chaveprimaria Table "chaveprimaria" Attribute | Type | Modifier -----------+---------+---------- col | integer | not null Index: chaveprimaria_pkey
Note que um índice é criado automaticamente, e a coluna é de nida como sendo não nula. Se uma chave primária tiver mais de uma coluna, a clásula deve ser de nida em uma linha separada para formar a chave.
php=> CREATE TABLE chaveprimaria ( php(> chave1 INTEGER, php(> chave2 INTEGER, php(> PRIMARY KEY(chave1,chave2) php(> ); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'chaveprimaria_pkey' for table 'chaveprimaria' CREATE
Uma tabela não pode ter mais que uma chave primária. Chaves primárias têm um valor especial quando usamos relações (chaves estrangeiras).
24.4 Chave Estrangeira, REFERENCES
Chaves estrangeiras são mais complexas que chaves primárias. Chaves primárias fazem uma coluna ser não nula e única. Chaves estrangeiras, por sua vez, armazenam dados baseados em outras tabelas. Elas são assim chamadas por que seus dados não as pertecem, e sim a outra tabela. Para exempli car iremos escrever as tabelas enquete e resposta com suas chaves e relações.
php=> CREATE TABLE enquetef ( php(> id SERIAL PRIMARY KEY, php(> enquete CHAR(300), php(> data DATE php(> ); NOTICE: CREATE TABLE will create implicit sequence 'enquetef_id_seq' for SERIAL column 'enquetef.id' NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'enquetef_pkey' for table 'enquetef' CREATE
O comando acima cria a tabela enquetef, sendo a chave primária o campo com o tipo serial.
php=> CREATE TABLE respostaf ( php(> id SERIAL PRIMARY KEY, php(> resposta CHAR(200), php(> contador INTEGER, php(> id_enquete INTEGER REFERENCES enquetef php(> ); NOTICE: CREATE TABLE will create implicit sequence 'respostaf_id_seq' for SERIAL column 'respostaf.id'
25 Php e sua interação com PostgreSQL
Nesta seção veremos como acessar o banco de dados através do Php usando funções de fácil enten- dimento e clareza. Todos os comandos abaixo são assumidos e testados nas versões PHP 4 ou acima dela.
25.1 Abrir Conexão com o banco de dados
A função usada para abrir uma conexão com o banco de dados PostgreSQL é a função pg_connect. Vários parâmetros são necessários e alguns são opcionais. A seguir temos várias possibilidade do uso da função.
int pg_connect (string host, string port, string dbname) int pg_connect (string host, string port, string options, string dbname) int pg_connect (string host, string port, string options, string tty, string dbname) int pg_connect (string conn_string) Tabela 25: Alguns exemplos do cabeçalho da função
A seguir, temos o signi cado de cada um dos argumentos listados na Tabela 25.
host Indica qual máquina deseja-se conectar;
port Indica a porta utilizada na conexão;
dbname Indica o nome do banco de dados a ser aberta a conexão;
tty Indica qual terminal a conexão deve ser realizada;
options De ne vários outros argumentos tal como usuário e senha.
A função pg_connect retorna um índice em caso de sucesso, ou falso se a conexão não puder ser aberta. Os argumentos devem ser passados usando-se aspas duplas. Se uma segunda chamada é feita pelo pg_connect com os mesmo argumentos, nenhuma nova conexão será aberta, mas em vez disso, será retornado apenas o índice da conexão já aberta anteriormente.
25.2 Executar uma consulta
A função usada para realizar uma consulta é a função pg_exec. Dois parâmetros são necessários para a conexão, a variável que guarda o índice da conexão e uma string, que no caso é a query a ser executada.
int pg_exec (int connection, string query)
Tabela 26: Cabeçalho da função pg_exec
A seguir, temos o signi cado de cada um dos argumentos listados na Tabela 26.
connection Indica qual índice de conexão previamente aberto deseja-se conectar;
query Indica a porta utilizada na conexão.
Abaixo, temos exemplos do uso da função pg_exec.
Usamos primeiramente a função pg_connect para abrir uma conexão com o banco de dados, e no- meamos o seu índice com a variável open. Após isso, usamos uma variável para guardar toda a string da consulta, prática indicada para facilitar o entendimento no uso das funções, não sendo preciso digitar todo o comando SQL dentro da função responsável pela execução da query, a função pg_exec, que recebe apenas duas variáveis, a primeira sendo o identi cador da abertura da conexão e a segunda sendo a própria query a ser executada. Retorna como resultado um índice se a query for executada, e falso se a conexão falhar ou o índice da conexão não for válido. Detalhes sobre erros podem ser recuperados usando a função pg_ErrorMessage() se a conexão for válida. O valor de retorno da função pg_exec é um índice que pode ser usado para acessar resultados da query por outras funções PostgreSQL.
25.3 Tratamento dos dados enviados na consulta
Tendo aprendido como abrir e executar comandos SQL por uma página PHP, temos que saber como tratar esses dados vindos do banco de dados, para poder utilizá-los de forma e ciente. Existem várias funções que podem tratar os dados, mas aqui por questão de simplicidade veremos apenas uma função, que é su ciente para adquirir os dados de uma forma simples e clara. A função pg_result tem como entrada a execução de uma query, a linha desejada e a coluna. Seu cabeçalho é de nido na tabela 27.
mixed pg_result (int result_id, int row_number, mixed eldname)
Tabela 27: Cabeçalho da função pg_result
