quarta-feira, 9 de janeiro de 2008

PostgreSQL - Inserindo múltiplas linhas só depois da versão 8.2

A batalha de hoje foi contra um velho costume (mal) adquirido do MySQL.

Criei uma tabela muito simples no PostgreSQL através do psql, na linha de comando mesmo.

CREATE TABLE pessoas (
id INT,
nome varchar(40),
sexo char(1)
);


Incluí um registro. Ótimo. Depois tentei incluir mais de um com um comando só:

INSERT INTO pessoas (id, nome, sexo) VALUES (2, 'José', 'M'), (3, 'Maria', 'F');


E ganhei um erro próximo dá vírgula.

...('M'), (3,...
^


Não ia nem com reza. Horas de pesquisa depois descobri algumas coisas interessantes.
1 - Esse tipo de construção não faz parte da norma SQL.
2 - Ela é popular devido ao popular MySQL (que disponibiliza essa construção como válida) e do seu fiel parceiro phpMyAdmin (que além de permitir a inclusão de múltiplos registros, ainda mostra o código SQL que foi executado, para todo mundo aprender como se faz!).
3 - Este tipo de construção é aceita pelo PostgreSQL 8.2 ou posterior. O server que tenho é o 8.1. E o manual que eu estava lendo do 8.3!
4 - Vou dar uma estudada em Java ou Shell Script para relaxar.

Té+
-----------
keepReading

segunda-feira, 7 de janeiro de 2008

PostgreSQL - instalando

A tempos atrás instalei o PostgreSQL 8.0 na minha máquina. Lembro que foi um tanto penoso para entender como proceder logo após a instalação dele no sistema, pensei até em escrever aqui mas devido à falta de tempo não escrevi nada, e é o que venho fazer agora.

O material é um tanto escasso em português, e as explicações geralmente confusas. Vou tentar dar um passo-a-passo.

Instalei no Ubuntu 6.06 pelo Synaptic (bem fácil), mas creio que o processo de compilação está bem descrito na documentação do PostgreSQL, então não vou me ater à este processo. O galho me veio após a instalação. Também recomendo que se instale o phppgAdmin, que dá uma interface mais agradável de trabalhar (nesse caso você precisará do Apache2 e do PHP5).

Primeiramente, saiba que quando se instala o PostgreSQL, assim como o Apache, o CUPS, é criado um usuário no sistema, para que este possa iniciar o servidor. É mais uma questão de organização e controle. Não se preocupe com isso, basta saber que o nome do usuário é postgres.

Além de criar este usuário, alguns comandos são adicionados ao sistema. São comandos administrativos que podem ser chamados direto pelo terminal.

Mais um detalhe importante: o PostgreSQL não permite que se crie e faça login com usuários com nomes sugestivos como admin, root, etc... Anotado? Então vamos lá, temos comandos para agir externamente e sabemos que não podemos criar nenhum usuário com nome sugestivo.

Abra um terminal e digite o comando:

$sudo su - postgres createuser seu_nome_de_usuario_do_linux

Se não funcionar dessa forma tente:

$sudo su - postgres
$createuser seu_nome_de_usuario_do_linux

A seguir serão feitas algumas perguntas (o novo usuário poderá criar um super-usuário? o novo usuário poderá criar bancos de dados?...poderá criar um novo usuário?). Responda a estas perguntas (sugiro um não-sim-não).

NOTA: o seu_nome_de_usuário_do_linux irá facilitar sua vida na hora de criar bancos de dados e fazer manutenção.

Temos um usuário criado, mas ele não tem senha ainda.
Faça assim:

$sudo su - postgres psql

Se não der certo tente:

$sudo su - postgres
$psql

Isso irá abrir um outro tipo de terminal, um terminal interativo com o servidor de banco de dados. Entre com a seguinte linha nele:

postgres=#ALTER ROLE seu_nome_de_usuário_do_linux WITH PASSWORD 'sua_senha';

Dê enter (aí você acaba de setar sua senha) e depois digite:

postgres=#\q

E dê enter novamente (para sair do terminal interativo do PostgreSQL).

Pronto. Agora você tem um usuário e senha do PostgreSQL com o qual poderá criar e editar seus bancos de dados no PostgreSQL.

Você pode criar um banco de dados mesmo sem senha com o comando:

$createdb nome_do_banco

Mas para logar pelo phppgAdmin a senha se faz necessária.

Agora vamos supor que você tenha criado um banco com o nome dummy. Para acessá-lo pelo terminal interativo do postgreSQL, basta fazer:

$psql -d dummy

E a partir daí enviar suas queries à este banco.

Espero que isso tenha ajudado a dar o ponta-pé inicial para o uso do PostgreSQL. Abaixo passo alguma informações extras.

Os comandos que podem ser usados direto pelo terminal são:
clusterdb -- cluster a PostgreSQL database
createdb -- create a new PostgreSQL database
createlang -- define a new PostgreSQL procedural language
createuser -- define a new PostgreSQL user account
dropdb -- remove a PostgreSQL database
droplang -- remove a PostgreSQL procedural language
dropuser -- remove a PostgreSQL user account
ecpg -- embedded SQL C preprocessor
pg_config -- retrieve information about the installed version of PostgreSQL
pg_dump -- extract a PostgreSQL database into a script file or other archive file
pg_dumpall -- extract a PostgreSQL database cluster into a script file
pg_restore -- restore a PostgreSQL database from an archive file created by pg_dump
psql -- PostgreSQL interactive terminal
reindexdb -- reindex a PostgreSQL database
vacuumdb -- garbage-collect and analyze a PostgreSQL database

-----------
keepReading

O MySQL não checa chaves estrangeiras!

Dando uma lida na documentação recentemente fiquei pasmo ao descobrir que as chaves estrangeiras não servem para absolutamente nada funcional no MySQL.

Sim, você leu direito. As chaves estrangeiras não servem para absolutamente nada no MySQL.

Ao definí-las, elas podem ser usadas como um lembrete, um aviso, etc... Mas os engines, com exceção ao InnoDB, não fazem checagem se o mesmo existe (na verdade eles não checam nem se a tabela onde é feita a referência!).

No InnoDB elas funcionam normalmente, mas nos outros a criação de chaves estrangeiras apenas criam uma coluna. Nem ao menos um índice é criado.

Uma vergonha para um banco com tantas funções, recursos, tipos de dados, extensões, conectores, etc...

A manual em questão é da versão 5.1 do MySQL, mais especificamente na seção 3.6.6.

Isso moçada: fiquem de olho.

-----------
keepReading

domingo, 6 de janeiro de 2008

Dicas de otimização e segurança

Olá, amigos. Depois de muito tempo away deste blog volto a publicar algumas informações coletadas e experimentadas.

Estudei bastante e fiz várias anotações durante os últimos meses. Além deste post se preparem para mais novidades legais em breve.

Neste reinício trago a vocês dicas sobre otimização e segurança. Algumas boas práticas que espero ser de valia.

Uma das decisões mais importantes durante o desenvolvimento de um sistema é onde implementar as regras do negócio. É comum deixar as regras ao encargo do linguagem de programação, mas quero mostrar que o melhor lugar para se fazer isso, sempre que possível, é no banco de dados.

Quando se implementa a lógica do negócio na linguagem de programação algumas coisas aumentam, como o tempo de desenvolvimento, o volume de códigos e a dificuldade em se fazer manutenção. Além de diminuir a portabilidade da aplicação (no caso de uma migração por exemplo).

Isso sem falar no ponto mais crítico: o aumento do tráfego e do processamento.

Vou exemplificar com um cenário comum - Apache 2, PHP 5 e MySQL 5.

Quando se tem a lógica implementada nos scripts/classes PHP, o fluxo segue mais ou menos como na figura:


Explicando... o cliente faz uma requisição ou envia um form. Estas informações são recebidas pelo servidor web que chamará o engine. O engine irá processar as requisições, se conectar com o banco de dados, enviar a(s) query(ies), processar a(s) resposta(s), fechar a conexão com o banco de dados, preparar a saída, repassá-la para o servidor e este por fim enviará para o cliente.

Isso é o processo que acontece geralmente em qualquer aplicação. Mas note que quando se deixa a regra dos negócios para o engine resolver, podem ser necessárias várias conexões e consultas ao banco de dados, e é aí onde o sistema perde performance - e muita.

A meta é diminuir este gargalo e aliviar este ponto crítico (a conversa entre o engine e o banco de dados). Deixando a lógica de dados implementada no banco de dados, o engine que trabalha com ele fará menos conexões, diminuirá a carga de processamento e melhorará o tempo de resposta, além de deixar o sistema mais robusto.

Assim, é altamente recomendável lançar mão de recursos como Views, Triggers, Procedures, Functions, e fazer um bom uso de índices e chaves disponíveis no banco de dados.

Digo isso porque tive uma experiência realmente dura em um sistema onde TODA a lógica dos dados estava implementada com o design pattern MVC. E para retornar um simples relatório com 50 linhas o sistema demorava uns 6 minutos - simplesmente crítico.

O problema é que haviam várias tabelas com relação Muitos-para-Muitos e a cada linha que se queria obter era necessário realizar uma série de queries e filtragens. Posso citar um dos casos onde para cada usuário era feita uma consulta numa tabela de relacionamento que retornava cerca de 8 chaves, que depois seriam pesquisadas em outra tabela, comparados, validados segundo sua data, organizados (aí já tinha apenas uns 2 ou 3 registros que realmente seriam úteis para o relatório), para depois fazer mais queries em duas ou três tabelas.

Ou seja para produzir uma linha, era necessário um volume de tráfego e processamento absurdo, sendo que isso poderia ser facilmente resolvido (ou pelo menos fortemente otimizado) com a criação de uma view e de uma function.

Aqui cabe um adendo: a diferença entre o modelo entidade-relacional dos bancos de dados e do que se pode implementar na programação orientada a objeto se chama impedância, e as boas práticas aqui citadas visam diminuir este problema. Eis um bom artigo aqui: http://www.linhadecodigo.com.br/ArtigoImpressao.aspx?id=70

Então a dica de otimização é essa: implemente a lógica de dados sempre que possível na própria base de dados.

Mas e a linguagem de programação seria útil para quê então?
Ora, para validar os dados enviados, fazer a conexão, enviar e receber dados do servidor, enviar e-mails, montar páginas dinamicamente... as mesmas coisas que antes. O que quero dizer é que ao invés de rechear seus scripts e classes com ifs, elses, switch cases e queries, você deve diminuir isso e usar apenas onde for realmente necessário.

Em relação à segurança, ela aumenta quando se adota esse procedimento e estes cuidados. Além disso você terá mais tempo livre para criar validações nos seus scripts server-side, e contará com mais uma camada de validação - a do servidor de bancos de dados.

É mais econômico para o sistema você checar se, por exemplo, um id existe numa determinada tabela do que fazer uma query select e depois uma comparação no script.

Algo assim: ao invés de fazer
<?
...
$valid_user = 0;
$sql = "SELECT 1 FROM users WHERE id = $posted_id";
$result = mysql_query($sql);
$x = mysql_fetch_assoc($result);
if ($x) {
$valid_user = 1;
}

if ($valid_user == 1) {
$sql_2 = "INSERT INTO...";
...
}
...

?>


Faça assim:
<?
$sql = INSERT INTO table_x VALUES ($x,$y,$z) WHERE $z INTO (SELECT id FROM users);
?>


Ou que você crie uma function no banco de dados para fazer isso.

Até mais.
-----------
keepReading