PostGreSQL Prático, Notas de estudo de Cultura

Baixe PostGreSQL Prático e outras Notas de estudo em PDF para Cultura, somente na Docsity!

PostgreSQL Prático

(versão 8.1.4)

Ribamar FS – [email protected] – http://ribafs.tk 17 de setembro de 2006

Livros Grátis

http://www.livrosgratis.com.br

Milhares de livros grátis para download.

14 - Ferramentas.......... 108 14.1 - psql 14.2 - phpPgAdmin 14.3 - PgAdmin 14.4 - EMS PostgreSQL 14.5 - Azzurry Clay (modelagem com o Eclipse) 14.6 - dbVisualizer 14.7 - OpenOffice Base 15 – Apêndices.......... 124 15.1 – Planejamento e Projeto de Bancos de Dados 15.2 – Implementação de Banco de Dados com o PostgreSQL 15.3 - Integridade Referencial - PostgreSQL 15.4 – Dicas Práticas de uso do SQL 15.5 – Dicas sobre Desempenho e Otimizações do PostgreSQL 16 – Exercícios.......... 149 17 - Referências.......... 154

1 - Introdução História dos SGDBs Anos 60 - utilizados sistemas gerenciadores de arquivos (ISAM e VSAM), usados até hoje. Anos 70 - Gerenciadores de Bancos de dados de rede. Extinguiram-se nos anos 90. Anos 80 - SGBDRs (Oracle, DB2, SQLServer) Anos 90 - SGBDOR (Oracle, DB2, PostgreSQL e Informix) Anos 90 - SGBDOO (Caché) SGBD = Composto por programas de gerenciamento, armazenamento e acesso aos dados, com a finalidade de tornar ágil e eficiente a manipulação dos dados. Dicionário de dados - metadados, dados sobre os dados, ou seja, informações sobre a estrutura dos bancos de dados (nomes de tabelas, de campos, tipos de dados, etc). DBA - Database Administrator, com as funções de:

• Definir e modificar esquemas, estruturas de armazenamento e métodos de acesso
• Liberar privilégios de acesso
• Especificação de restrição de integridade Simplificando temos (no PostgreSQL), em termos de estrutura:
• Um SGBD é formado por bancos de dados, tablespaces, usuários e alguns programas auxiliares;
• Um banco de dados é formado pelos esquemas e linguagens;
• Um esquema é formado por funções de agrupamento, funções, triggers, procedures, sequências, tabelas e views;
• Tabelas são formadas por campos, constraints, índices e triggers.
• Em termos de dados uma tabela é formada por registros e campos. Segundo a Wikipedia (http://pt.wikipedia.org): ... A apresentação dos dados pode ser semelhante à de uma planilha eletrônica, porém os sistemas de gestão de banco de dados possuem características especiais para o armazenamento, classificação e recuperação dos dados. Os bancos de dados são utilizados em muitas aplicações, abrangendo praticamente todo o campo dos programas de computador. Os bancos de dados são o método de armazenamento preferencial para aplicações multiusuário, nas quais é necessário haver coordenação entre vários usuários. Entretanto, são convenientes também para indivíduos, e muitos programas de correio eletrônico e organizadores pessoais baseiam-se em tecnologias padronizadas de bancos de dados. Em Março, 2004 , AMR Research (como citado em um artigo da CNET News.com listado na secção de "Referências") previu que aplicações de banco de dados de código aberto seriam amplamente aceitas em 2006. Esquemas – são subdivisões de bancos de dados, cuja função é permitir um melhor nível de organização. Projetos de mesma categoria, que precisem acessar uns aos outros devem ficar em um mesmo banco, podendo ficar em esquemas separados.

Traz também opções de extensão pelo usuário:

• Tipos de dados
• Funções
• Operadores
• Funções de Agregação (Agrupamento)
• Métodos de Índice
• Linguagens Procedurais (Stored Procedures) Licença Sua licença é BSD, portanto pode ser utilizado, modificado e distribuído por qualquer pessoa ou empresa para qualquer finalidade, sem qualquer encargo, em quaisquer dos sistemas operacionais suportados. Empresas que Utilizam PostgreSQL BASF (PDF format) Fujitsu Apple RedHat Sun Pervasive Mohawk Software Proximity Radio Paradise Shannon Medical Center Spiros Louis Stadium The Dravis Group OSS Report Vanten Inc. SRA Rambler Netezza VA Software Travel Post National Weather Service Aplicações Corporativas de Alto Volume: Uma Solução com o PostgreSQL A utilização da dupla PostgreSQL+Linux nas empresas cresce rapidamente e é um exemplo de como produtos Open Source podem ajudar empresas a racionalizar os custos de TI. Uma das características do PostgreSQL é a sua capacidade de lidar com um grande volume de dados. E-xistem aplicações em produção com tabelas possuindo mais de 100 milhões de linhas. No Brasil, existem casos de sucesso de empresas lidando com bases com dezenas de milhões de registros gerenciadas pelo PostgreSQL. Uma das maiores implantações de PostgreSQL no Brasil é na Atrium Telecom, empresa de tele-fonia corporativa de São Paulo. O PostgreSQL é utilizado como banco de dados do sistema de billing e tem uma base de dados de mais de 100GB e efetua 1 milhão de transações diárias. As maiores tabelas do sistema contam com mais de 70 milhões de linhas. A utilização do banco de dados PostgreSQL é cada vez mais ampla nas empresas que buscam um servidor de banco de dados altamente sofisticado, com alta performance, estável e capacitado para lidar com grandes volumes de dados. O fato de ser um produto Open Source, sem custos de licença para nenhum uso, torna o PostgreSQL uma alternativa extremamente atraente para empresas que buscam um custo total de propriedade (TCO) menor para os ativos de TI.

Citação de: http://www.dib.com.br/dib%20cd/LC2003/P%C3%A1ginas/LC2003_Conf.html Metrô de São Paulo e DATAPREV também utilizam o PostgreSQL. Sobre o Autor Ribamar FS Desenvolvedor de aplicativos web para a Intranet do DNOCS (Departamento Nacional de Obras Contra as Secas). Desenvolve atualmente em PHP com PostgreSQL. Trabalhou no DNOCS por algum tempo como administrador de redes Linux e FreeBSD. É graduado em Engenharia Civil pela Universidade de Fortaleza (UNIFOR) Com especialização em Irrigação e Drenagem pela UFC/IRYDA Cursando Especialização em Java na UNIFOR Concluiu o Curso de PostgreSQL pela dbExpert (São Paulo) e pelo Evolução (Fortaleza) Concluiu o curso de Administração Linux pelo Evolução (Fortaleza) Foi escritor colaborador da Revista Forum Access (na área de Access) É escritor colaborador da Revista Web Mobile (artigo sobre Joomla 02/2006) Foi professor de cursos de extensão na UNIFOR (PHP+MySQL e PHP + PostgreSQL) em 2005 e 2006 Apresentou palestra sobre PostgreSQL na UNIFOR no dia 29/03/2006. Apresentou palestra sobre PostgreSQL na UFC no dia 21/09/2006 (II Semana de Software Livre da UFC). Compartilha seus conhecimentos através do site: http://ribafs.tk (http://www.geocities.com/ribafsindex) e http://www.ribafs.net

Pré-requisitos para instalação do PostgreSQL num UNIX:

• make do GNU (gmake ou make)
• compilador C, preferido GCC mais recente
• gzip
• biblioteca readline (para psql)
• gettext (para NLS)
• kerberos, openssl e pam (opcional, para autenticação) Instalação no Linux Várias distribuições já contam com binários para instalação do PostgreSQL (Ubuntu, Debian, Slackware, RedHat, Fedora, etc). Em uma instalação padrão do Ubuntu veja o que precisa para instalar os fontes: Antes de instalar: sudo apt-get install build-essential sudo apt-get install libreadline-dev sudo apt-get install zlib1g-dev sudo apt-get install gettext E use make ao invés de gmake. Mas caso queira ter um controle maior instalando os fontes, apenas faça o download e descompacte (gosto de descompactar em /usr/local/src e instalar no diretório default, que é /usr/local/pgsql). Instalar pelos binários da distribuição tem as vantagens de já instalar e configurar praticamente tudo automaticamente, mas instalar dos fontes dá um maior controle sobre as configurações (você sabe que tudo ficará no /usr/local/pgsql), possibilidade de instalar sempre a última versão. Aqui a instalação é no modo texto, mas mesmo assim não dá trabalho. Após descompactar visualize ou edite o arquivo INSTALL e siga as recomendações resumidas existentes no início do arquivo, reproduzidas abaixo: make distclean (adicionei, para o caso de ter que repetir os procedimentos) ./configure make (build – construir) su (mudar para superusuário, ou no Ubuntu usar sudo para as linhas abaixo) make install (instalar) groupadd postgres (criar o grupo postgres) useradd -g postgres -d /usr/local/pgsql postgres (criar o usuário postgres) mkdir /usr/local/pgsql/data chown postgres /usr/local/pgsql/data (tornar o postgres dono da pasta data) passwd postgres su - postgres (se logar como postgres) /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data >logfile 2>&1 & (startar) /usr/local/pgsql/bin/createdb test /usr/local/pgsql/bin/psql test Opcionalmente: ./configure –enable-nls=pt_BR –with-openssl (para mensagens em português e autenticação SSL).

Copiar o script de inicialização “linux” para o /etc/init.d (Debians): De /usr/local/src/postgresql-8.1.4/contrib/start-script/linux para /etc/init.d/postgresql Dar permissão de execução: chmod u+x /etc/init.d/postgresql Se no Ubuntu ou outro Debian: su - postgres gedit .bash_profile (e adicione a linha): PATH=/usr/local/pgsql/bin:$PATH Pós Instalação (sh,bash,ksh e zsh): LD_LIBRARY_PATH=/usr/local/pgsql/lib export LD_LIBRARY_PATH Ou no ~/.bash_profile do usuário postgres initdb – inicializa o cluster, cria os scripts de configuração default. postmaster – inicia o processo do servidor responsável por escutar por pedidos de conexão. Para suporte aos locales do Brasil usar: /usr/local/pgsql/bin/initdb -locale=pt_BR -D /usr/local/pgsql/data A instalação via fontes (sources) em algumas distribuições muito enxutas, voltadas para para desktop, pode não funcionar da primeira vez, pois faltarão algumas bibliotecas, compiladores, etc. Após a instalação está criado o agrupamento principal (cluster main) de bancos de dados do PostgreSQL. Caso não se tenha confiança nos usuários locais é recomendável utilizar a opção -W, -- pwprompt ou –pwfile do initdb, que atribuirá uma senha ao superusuário. No arquivo pg_hba.conf utilizar autenticação tipo md5, password ou cript, antes de iniciar o servidor pela primeira vez. Quando o programa que inicia o servidor (postmaster) está em execução, é criado um PID e armazenado dentro do arquivo postmaster.pid, dentro do subdiretório data. Ele impede que mais de um processo postmaster seja executado usando o mesmo cluster e diretório de dados. Baixar PostgreSQL via Anonymous CVS: Baixar CVS de - http://www.nongnu.org/cvs/ Instalar e Logar com qualquer senha: cvs -d :pserver:[email protected]:/projects/cvsroot login Baixar fontes: cvs -z3 -d :pserver:[email protected]:/projects/cvsroot co -P pgsql Isto irá instalar o PostgreSQL num subdiretório pgsql do diretório atual. Atualizar a última instalação via CVS: Acesse o diretório pgsql e execute - cvs -z3 update -d -P Isto irá baixar somente as alterações ocorridas após a última instalação.

Sugestão de Padrão

• Nomes de bancos no plural
• Nomes de tabelas no singular
• Exemplo:
  • banco – clientes
  • tabela - cliente Criar Novo Cluster Caso sinta necessidade pode criar outros clusters, especialmente indicado para grupos de tabelas com muito acesso. O comando para criar um novo cluster na versão atual (8.1.3) do PostgreSQL é: banco=# \h create tablespace Comando: CREATE TABLESPACE Descrição: define uma nova tablespace Sintaxe: CREATE TABLESPACE nome_tablespace [ OWNER usuário ] LOCATION 'diretório' Exemplo: CREATE TABLESPACE ncluster OWNER usuário LOCATION '/usr/local/pgsql/nc'; CREATE TABLESPACE ncluster [OWNER postgres] LOCATION 'c:\ncluster'; O diretório deve estar vazio e pertencer ao usuário. Criando um banco no novo cluster: CREATE DATABASE bdcluster TABLESPACE = ncluster; Obs: Podem existir numa mesma máquina vários agrupamentos de bancos de dados (cluster) gerenciados por um mesmo ou por diferentes postmasters. Se usando tablespace o gerenciamento será de um mesmo postmaster, se inicializados por outro initdb será por outro. Setar o Tablespace default: SET default_tablespace = tablespace1; Listar os Tablespaces existentes: \db SELECT spcname FROM pg_tablespace; Detalhes extras no item 14.5 do manual oficial.

3 - DDL (Data Definition Language) 3.1 - Criação e exclusão de bancos, esquemas, tabelas, views, etc Obs.: Nomes de objetos e campos não podem usar hífen (-). Alternativamente usar sublinhado (_). campo-1 Inválido campo_1 Válido Nomes de Identificadores Utiliza-se por convenção as palavras chaves do SQL em maiúsculas e os identificadores dos objetos que criamos em minúsculas. Identificadores digitados em maiúsculas serão gravados em minúsculas, a não ser que venham entre aspas “”. Revisões da Linguagem SQL SQL – 1989 SQL – 1992 SQL – 1999 SQL – 2003 Divisões da SQL DML – Linguagem de Manipulação de Dados DDL – Linguagem de Definição de Dados DCL – Linguagem de Controle de Dados (autorização de dados e licença de usuários para controlar quem tem acesso aos dados). DQL – Linguagem de Consulta de Dados (Tem apenas um comando: SELECT).

Criar um banco para outro usuário: CREATE DATABASE nomebanco OWNER nomeuser; createdb -O nomeusuario nomebanco Obs.: requer ser superusuário para poder criar banco para outro usuário. Criar Tabela postgres=# \h create table Comando: CREATE TABLE Descrição: define uma nova tabela Sintaxe: CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE nome_tabela ( [ { nome_coluna tipo_dado [ DEFAULT expressão_padrão ] [ restrição_coluna [ ... ] ] | restrição_tabela | LIKE tabela_pai [ { INCLUDING | EXCLUDING } DEFAULTS ] } [, ... ] ] ) [ INHERITS ( tabela_pai [, ... ] ) ] [ WITH OIDS | WITHOUT OIDS ] [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] [ TABLESPACE tablespace ] onde restrição_coluna é: [ CONSTRAINT nome_restrição ] { NOT NULL | NULL | UNIQUE [ USING INDEX TABLESPACE tablespace ] | PRIMARY KEY [ USING INDEX TABLESPACE tablespace ] | CHECK (expressão) | REFERENCES tabela_ref [ ( coluna_ref ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE ação ] [ ON UPDATE ação ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] e restrição_tabela é: [ CONSTRAINT nome_restrição ] { UNIQUE ( nome_coluna [, ... ] ) [ USING INDEX TABLESPACE tablespace ] | PRIMARY KEY ( nome_coluna [, ... ] ) [ USING INDEX TABLESPACE tablespace ] | CHECK ( expressão ) | FOREIGN KEY ( nome_coluna [, ... ] ) REFERENCES tabela_ref [ ( coluna_ref [, ... ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE ação ] [ ON UPDATE ação ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] Obs.: Atenção: nesta versão (8.1.3) WITH OID é opcional. As tabelas são criadas sem OID. \d – visualizar tabelas e outros objetos \d nometabela – visualizar estrutura da tabela

CREATE TABLE primeira_tabela ( primeiro_campo text, segundo_campo integer ); Excluindo Tabela DROP TABLE primeira_tabela; Valor Default (padrão) Para Campos Ao definir um valor default para um campo, ao ser cadastrado o registro e este campo não for informado, o valor default é assumido. Caso não seja declarado explicitamente um valor default, o valor nulo (NULL) será o valor default. CREATE TABLE produtos ( produto_no integer, descricao text, preco numeric DEFAULT 9. ); Constraints (Restrições) CHECK Ao criar uma tabela podemos prever que o banco exija que o valor de um campo satisfaça uma expressão CREATE TABLE produtos ( produto_no integer, descricao text, preco numeric CHECK (preco > 0) ); Dando nome à restrição check****. Isso ajuda a tornar mais amigável as mensagens de erro. CREATE TABLE produtos ( produto_no integer, descricao text, preco numeric CONSTRAINT preco_positivo CHECK (preco > 0) ); CREATE TABLE produtos ( produto_no integer, descricao text, desconto numeric CHECK (desconto > 0 AND desconto < 0.10), preco numeric CONSTRAINT preco_positivo CHECK (preco > 0), check (preco > desconto) ); Constraint NOT NULL Obrigar o preenchimento de um campo. Ideal para campos importantes que não devem ficar sem preenchimento. Mas devemos ter em mente que até um espaço em branco atende a esta restrição. CREATE TABLE produtos ( cod_prod integer NOT NULL CHECK (cod_prod > 0),

Chaves Primárias (Primary Key) A chave primária de uma tabela é formada internamente pela combinação das constraints UNIQUE e NOT NULL. Uma tabela pode ter no máximo uma chave primária. A teoria de bancos de dados relacional dita que toda tabela deve ter uma chave primária. O PostgreSQL não obriga que uma tabela tenha chave primária, mas é recomendável seguir, a não ser que esteja criando uma tabela para importar de outra que contenha registros duplicados para tratamento futuro ou algo parecido. CREATE TABLE produtos ( cod_prod integer UNIQUE NOT NULL, nome text, preco numeric ); CREATE TABLE produtos ( cod_prod integer PRIMARY KEY, nome text, preco numeric ); CREATE TABLE exemplo ( a integer, b integer, c integer, PRIMARY KEY (a, c) ); Chave Estrangeira (Foreign Key) Criadas com o objetivo de relacionar duas tabelas, mantendo a integridade referencial entre ambas. Especifica que o valor da coluna (ou grupo de colunas) deve corresponder a algum valor existente em um registro da outra tabela. Normalmente queremos que na tabela estrangeira existam somente registros que tenham um registro relacionado na tabela principal. Como também garantir que não se remova um registro na tabela principal que tenha registros relacionados na estrangeira. Tabela primária CREATE TABLE produtos ( cod_prod integer PRIMARY KEY, nome text, preco numeric ); CREATE TABLE pedidos ( cod_pedido integer PRIMARY KEY, cod_prod integer, quantidade integer, CONSTRAINT pedidos_fk FOREIGN KEY (cod_prod) REFERENCES produtos (cod_prod) );

CREATE TABLE t0 ( a integer PRIMARY KEY, b integer, c integer, FOREIGN KEY (b, c) REFERENCES outra_tabela - - a coluna de destino será a PK ); CREATE TABLE t1 ( a integer PRIMARY KEY, b integer, c integer, FOREIGN KEY (b, c) REFERENCES outra_tabela (c1, c2) ); OBS.: Preferir sempre criar FK, utilizando a palavra reservada FOREIGN KEY e não somente com REFERENCES. Obviamente, o número de colunas e tipo na restrição devem ser semelhantes ao número e tipo das colunas referenciadas. SIMULANDO ENUM Para simular a constraint enum do MySQL, podemos usar a constraint check. Dica do site "PostgreSQL & PHP Tutorials". CREATE TABLE pessoa( codigo int null primary key, cor_favorita varchar(255) not null, check (cor_favorita IN ('vermelha', 'verde', 'azul')) ); INSERT INTO pessoa (codigo, cor_favorita) values (1, 'vermelha'); -- OK INSERT INTO pessoa (codigo, cor_favorita) values (1, 'amarela'); -- Erro, amarelo não consta Herança Podemos criar uma tabela que herda todos os campos de outra tabela existente. CREATE TABLE cidades ( nome text, populacao float, altitude int -- (em pés) ); CREATE TABLE capitais ( estado char(2) ) INHERITS (cidades); capitais assim passa a ter também todos os campos da tabela cidades. Segundo uma entrevista (vide DBFree Magazine No. 2) com a equipe de desenvolvimento do PostgreSQL, evite utilizar herança de tabelas. Esquemas (Schema)