Pplware Open Source part II

Administração de Postgresql com pgAdmin, criação de tabelas, campos e afins. No segundo artigo do Projecto Pplware OpenSource demonstrarei como administrar o Postgresql através do pgAdmin, a qual é uma excelente ferramenta para administração da base de dados. O pgAdmin permite realizar todas as tarefas necessárias de administração da base de dados. No entanto algumas tarefas mais complexas serão feitas através do comando psql.

O pgAdmin é instalado automaticamente quando se instala o Postgresql. Pode ser encontrado em Iniciar / Programas / Postgresql 8.1 / pgAdmin III. A versão instalada é a 1.4.2, recentemente foi lançada a versão 1.6.1 que pode ser recolhida aqui.

A versão 1.6.1 do pgAdmin vem com muitas melhorias em termos de produção, uma delas é o AutoComplete de SQL, onde com um CTRL + Espaço tem uma relação de todas as tabelas e Views da base de dados. Já na parte administrativa o pgAdmin 1.6.1 não teve muitas mudanças em relação à versão 1.4.2. Este artigo é escrito fundamentado no pgAdmin 1.4.2, mas pode ser facilmente acompanhado na versão 1.6.1.Ao abrir o pgAdmin verá o seguinte ecrã.

Do lado esquerdo, têm as conexões com o servidor de banco de dados. O tema proposto aborda apenas uma conexão. Mas poderá criar conexões com outros servidores clicando em File / Add Server. A instalação do Postgresql já deixou configurado o acesso ao servidor local, basta dar dois cliques em cima de PostgreSQL Database Server 8.1 para ter acesso.

Escreva a senha definida na instalação e clique em OK. Se desejar clique em Store Password para não ter que digitar a senha novamente.

Assim que conectar no servidor, no lado esquerdo aparecerá à árvore demonstrada na imagem acima.

Em Databases tem todos os bancos de dados configurados no servidor.

Em Group Rules e Login Rules são definidos os grupos e os usuários do banco de dados. Aí será configurado o controle de acesso ao servidor. É importante ter um controle de acesso rígido em um sistema grande, neste caso não será dada tanta atenção para esse tipo de controle.Ao clicar em (+) da opção Databases terá o banco de dados Postgresql que é criado automaticamente na instalação. Esse banco de dados não será utilizado e não poderá ser excluído por se tratar de um banco utilizado para administração do Postgresql.

Crie seu banco de dados: Clique com o botão direito do rato em cima de Databases e escolha a opção New Database.

Os campos: Name: Nome do banco de dados. É recomendável ser em letras minúsculas. Owner: Usuário proprietário do banco de dados. Selecione o usuário postgres. Enconding: A codificação dos caracteres dda BD. Utilize Latin1, pois atente a todos os caracteres e acentuações do dicionário. Template: É a base de dados que será utilizada como padrão para a criação da nossa BD. Utilize Template0. Após configurar clique em OK e terá seu banco de dados devidamente criado.

Para ter acesso à base de dados, clique em cima do seu nome. Logo de seguida clique no sinal + em Schemas e faça o mesmo para public.

Terá uma visão da base de dados.

Cada opção dessa lista contém um tipo de objecto de banco de dados. Os objectos mais utilizados são: Functions: contêm as Stored Procedures que retorna algum valor. Trigger Functions: contêm as Funções de Gatilhos. A Trigger Function é como uma Stored Procedures, só que é accionado por uma Trigger de uma determinada tabela. Procedures: contêm as Stored Procedures que retorna “void”. Ou seja, não retorna nada. Operators: contêm os operadores matemáticos criadas. Essa opção é pouco utilizada. Sequences: contêm as sequências utilizadas para auto-numeração de um campo. Tables: contêm as tabelas. Type: contêm os tipos definidos pelo programador, normalmente utilizados para que as Functions retornem mais de uma coluna. View: contêm as Visões criadas de uma ou mais tabela.

Demonstrarei apenas como criar tabelas, para utilizar os outros objectos de base de dados recomendo que leiam o manual do Postgresql disponível aqui e qualquer dúvida responderei nos comentários.

Para criar uma tabela utilize a Interface gráfica do pgAdmin ou crie comandos SQL. Crie utilizando o pgAdmin.

Clique com o botão direito do rato em Tables e seleccione a opção New Table.

A opção Name é o nome da tabela e a opção Comment é um comentário sobre a tabela. Este comentário não é obrigatório e só é exibido em ferramentas de administração de base de dados.

Após determinar o nome da tabela clique na guia Columns para criar as colunas de dados da tabela. Na guia Columns clique no botão Add.

Name: determina qual o nome da coluna de dados da tabela. Por definição não utilize qualquer tipo de acentuação e caracteres especiais no nome da coluna, excepto o underline (_).

Data Type: é o tipo de dados do campo. A lista de tipo de dados suportados pelo Postgresql pode ser obtida aqui. Os tipos de dados mais usados são caracteres, numéricos, data/hora e lógicos.

Length: tamanho do campo. Este campo será do tipo varchar (caracter) e suportará no máximo 50 caracteres.

Default Value: é o valor padrão para o campo. Estse valor será determinado quando não for definido na inserção do registro.

Not NULL: indica que esse campo não pode ser nulo, ou seja, é obrigatório o seu preenchimento.

Comment: é igual ao Comment da tabela, citado acima.

Após definir os campos, clique em OK. Defina todos os campos da tabela.

Como podem ver na imagem acima, o campo “país” tem uma propriedade default ‘Portugal’. Desta forma se o utilizador não escrever nada para esse campo, o país será assumido automaticamente Portugal. Atenção que Portugal deve estar entre apostrofes, pelo fato de ser do tipo caracter.

Clique na guia Constraint. As Constraint são regras definidas para uma determinada coluna. Quando definir que a coluna “codigo” é Not Null, estará a definir uma regra para essa coluna. A regra mais utilizada é a Primary Key (PK ou Chave Primária). Essa regra indica que um campo será Not Null e UNIQUE (único), ou seja, é obrigatório informar o seu valor e esse valor deve ser único (não o pode repetir na tabela).

Outra constraint utilizada com certa frequência é a Check Contraint onde definirá uma regra para aceitação do campo. Por exemplo: “idade > 0”, só será aceito idades introduzidas maiores que 0 (zero).

Crie uma Chave Primária. Seleccione Primary Key na lista e clique em Add.

Escreva o nome da Chave Primária no campo name. Pode ser informado qualquer nome, mas por definição prefixe os nomes dos campos que será coloca Chave Primária com “pk_”. Coloque a chave primária no campo “codigo” então a nossa Chave Primária terá o nome pk_codigo. Após definir o nome clique na guia Columns.

Seleccione a coluna que será Chave Primária (no caso a coluna “codigo”) e clique em Add. É possível definir mais do que uma coluna como Chave Primária, isso criaria uma Chave Composta. Nesse caso utilize uma Chave Simples, depois de adicionar o campo clique em OK e terá a Constraint.

Na guia Privileges encontrará os privilégios de acesso a tabela. Mas não entrarei em detalhes.

Na guia SQL, existe o código SQL montado pelo pgAdmin.

O SQL mostrado na guia SQL é que será executado na db para criar a tabela. Esta informação pode ser copiada e guardada para criar um Script.

Para confirmar a criação da tabela clique em OK.

Tem a tabela criada. Para visualizar o conteúdo, seleccione a tabela e clique no botão demonstrado na figura abaixo.

Nesse quadro é possível inserir, alterar e excluir dados na tabela. Para isso basta clicar na primeira coluna e escrever, ao teclar ENTER a informação será salva.

Verifique os seguintes pontos:

Tente escrever o nome sem ter introduzido o código. Mostra mensagem de erro porque o campo código necessita de um valor.

Digite um código já existente e tecle ENTER até ao final das colunas. Mostra mensagem de erro porque o campo código deve ser único.

Não escreva o país. Vai assumir Portugal.

Crie mais uma tabela.

Esta tabela irá conter os telefones das entidades registadas. Faremos um relacionamento de forma que cada entidade possa ter N telefones. Para isso crie um Foreign Key (FK ou Chave Estrangeira). Uma chave estrangeira cria o relacionamento entre a tabela filha (“entidades_telefone”) e uma coluna (“codigo”) com a tabela pai (“entidades”) e uma coluna (“codigo”). Este relacionamento é baseado na teoria Modelo Relacional. Para criar uma Chave Estrangeira, clique em Contraint, seleccione Foreign Key na lista e clique em Add.

Defina o nome da Chave Estrangeira. Por definição o nome da chave estrangeira deve ser prefixado por “fk_” seguido do nome da tabela pai e tabela filhas separadas por “_”. Neste caso a Chave Estrangeira irá chamar: fk_entidades_entidades_telefone. Clique na guia Columns e seleccione a coluna que irá compor a nossa Chave Estrangeira.

No campo Local Column será seleccionado o campo da tabela filha (“entidades_telefone”) e no campo Refencing será seleccionado o campo da tabela pai (“entidades”). Seleccione o campo “codigo” nas duas tabelas. Clique no botão Add. Na guia Action terá a acção que será executada caso haja Alteração (On Update) ou Exclusão (On Delete) no registro da tabela pai. Para maiores informações consulta a documentação do Postgresql.

Depois de definir a Chave Estrangeira, clique em OK. Volte para o ecrã de nova Tabela. Clique em OK novamente para criar a nova tabela.

Agora temos duas tabelas na base de dados, sendo que a tabela “entidades_telefone” depende da tabela “entidades”.

Faça alguma inserção na tabela “entidades_telefone”.

Verifique os seguintes pontos:

Informe um código que não exista na tabela “entidades”. A mensagem de erro ocorre porque está a tentar inserir um registro que não tem relação na tabela “entidades”

Informe um código já registado na tabela “entidades_telefone”. Ele permite ter mais um telefone para cada entidade. Então não ocorre nenhuma mensagem de erro.

A criação de tabelas é a estrutura básica para utilizar uma base de dados, e um conhecimento que não pode ser ignorado. Uma base de dados pode viver sem Stored Procedures, Views, mas não pode viver sem uma tabela. Demonstrarei a criação de Views conforme o projecto for crescendo e necessitar de criação de Views. Esta estrutura de tabelas demonstradas nesse artigo é meramente ilustrativa, antes de iniciar o projecto irei definir a estrutura de tabela definitiva para o mesmo.

O pgAdmin é uma excelente ferramenta para administração, mas não tem muitos recursos para produção de códigos SQL (Stored Procedures, Views, Trigger), por isso no próximo artigo abordarei o tema “Ferramentas free de bases de dados para produção”, onde apresentarei e demonstrarei a parte básica de algumas ferramentas free para diversas bases de dados com a intenção de ajudar aqueles que precisam escrever códigos SQL nessas bases de dados.

Artigos relacionados: Projecto Pplware Open Source I