Construindo Banco de Dados

Dicas de SQL para criação e manipulação de bancos de dados
SQL: usando Having para agrupar com mais critério

Embora a linguagem SQL não seja um bicho de sete cabeças, muitos programadores têm dificuldades em usar recursos comuns desta linguagem e até mesmo deixam passar desapercebidas por anos algumas facilidades que estão ao seu alcance. Por esta razão, é sempre bom lembrar de alguns recursos da linguagem SQL que ficam esquecidos por muitos. Um destes recursos é a cláusula HAVING, que é usada em combinação com a cláusula GROUP BY.

A cláusula GROUP BY nos permite criar consultas que retornam alguns campos pelos quais queremos agrupar valores e também os totais resultantes do agrupamento. Para gerar os totais usamos funções agregadas da linguagem SQL, tais como SUM, MAX, MIN, AVG, COUNT e outras expressões aritméticas aceitas. A cláusula GROUP BY apenas cuida dos campos pelos quais será feito o agrupamento, mas não dos critérios de filtragem dos registros a serem considerados para o agrupamento. Os critérios para selecionar os registros a serem considerados são postos  na cláusula WHERE. Os critérios da cláusula WHERE são examinados antes de se dar o agrupamento. Há situações, porém, em que não só queremos poder filtrar os registros que serão considerados no agrupamento, mas também filtrar certos grupos baseando-nos nos valores dos totais gerados para cada grupo. Isto pode ser feito usando-se a cláusula HAVING. Esta cláusula nos permite informar critérios de filtragem baseados nos valores das próprias totalizações geradas para os grupos. Os critérios de filtragem da cláusula HAVING são aplicados após os agrupamentos terem sido feitos. A cláusula HAVING vem sempre após a cláusula GROUP BY e usa , em seus critérios, expressões que aparecem no SELECT para gerar os totais dos grupos. Veja um exemplo:

SELECT SUM(ValorTotaldoPedido) As BaseParaPremiacao, CodVendedor FROM Pedidos WHERE DataPedido BETWEEN #12/01/2002# AND #12/31/2002# GROUP BY CodVendedor HAVING SUM(ValorTotaldoPedido) > 10000.00

A consulta acima retorna um conjunto de registros relativos ao mes de dezembro de 2002 que contém o total dos pedidos para cada vendedor –  SUM(ValorTotaldoPedido) As BaseParaPremiacao – e mais o código do vendedor. Porém, como se trata de uma consulta para determinar quais vendedores merecem ser premiados, neste conjunto só estarão incluídos os vendedores que atendam ao critério para premiação. O critério para premiação é : vendedores com total de pedidos maior que R$ 10.000,00 e está expresso na cláusula HAVING na forma SUM(ValorTotaldoPedido) > 10000.00. A expressão SUM(ValorTotaldoPedido)  soma os valores totais de pedidos por CodVendedor e gera um total para cada CodVendedor. Mas, na cláusula HAVING estamos selecionando apenas os registros em que o total do vendedor seja maior que R$ 10.000,00.

Mais um truque com SQL

Há situações em que você deseja atualizar os registros de uma tabela mediante o uso de uma instrução UPDATE que filtre apenas os registros que satisfazem determinada condição. Isto é fácil de fazer usando-se a cláusula WHERE. Porém, há casos em que o filtro que você precisa não deve atuar apenas a nível de registro, mas o que você precisa é, num mesmo registro selecionado, atualizar alguns campos e outros apenas quando satisfizerem a determinada condição. Veja o caso de alguém que precisa atualizar uma massa de registros onde o campo QuantidadeTotal deve ser aumentado em 2 para todos os registros e o campo QuantidadeParcial deve ser aumentado em 2 somente se o campo IdProduto for igual a 1000. Se você tentar fazer isto usando a cláusula Where para restringir os registros àqueles onde o IdProduto seja igual a 1000, você deixará de atualizar os campos QuantidadeTotal dos registros que não sejam do produto 1000. Não é isto o que você quer aqui. A solução é usar um pequeno truque. Todos sabemos que, no Access, uma expressão booleana pode retornar 0 ou -1. Podemos jogar com este fato para criar uma consulta que atualiza o campo QuantidadeParcial somente se o valor que se quer acrescentar ao campo se mantiver igual após ser multiplicado pela condição que o registro deve satisfazer para ter o campo atualizado. Isto é feito da seguinte forma:

UPDATE Lancamentos Set QuantidadeTotal = QuantidadeTotal + 2, QuantidadeParcial = QuantidadeParcial + (2 * (IdProduto = 1000) * -1)

Na consulta acima, todos os registros terão o campo QuantidadeTotal atualizado. Mas veja que você estará adicionando 0 a QuantidadeParcial sempre que a expressão (IdProduto = 1000) retornar 0 (False), pois o produto 2 * 0 * -1 é igual a 0. Adicionar 0 é o mesmo que não atualizar. Porém, se a expressão (IdProduto = 1000) retornar -1 (True), o produto 2 * – 1 *  -1 é igual a 2, ou seja, o valor que se quer adicionar nestes casos ao campo QuantidadeParcial.

Histórico de Objeto Composto

Criar históricos para os dados de uma aplicação é o mesmo que dar à aplicação uma dimensão nova: a dimensão do tempo. Informações de histórico não servem apenas para dirimir dúvidas sobre quem foi o responsável por este ou aquele erro, mas também é uma forma de acumular informações que poderão ser usadas para desenvolver aplicações que necessitem usar dados relativos a certos períodos ou  para melhor compreender o dia-a-dia das atividades da empresa. O trato das informações de histórico abre todo um conjunto novo de possibilidades para o planejamento baseado na experiência acumulada com o registro do dia-a-dia das atividades da empresa.

Porém … , gravar históricos não é tão simples como pode parecer à primeira vista. Não basta criar uma tabela de históricos para cada tabela do sistema e registrar nestas tabelas tudo que ocorre em cada operação no banco de dados. As complicações vêm do fato de que as tabelas são feitas para existirem em relação umas com as demais. Quase sempre temos que relacionar as informações de uma tabela com as de outras para que seus dados façam sentido.

E estes relacionamentos aparecem bem no modo como os programas têm de apresentar dados para os usuários. Ninguém está interessado em ver uma lista de pedidos sem que possa ver os itens dos pedidos, ou cadastrar um produto sem poder escolher a unidade de uma lista de unidades. O programador tem bastante trabalho para construir interfaces que ofereçam ao cliente a comodidade de ter tudo ao alcance de um click de mouse. A exibição de informações no estado presente é facilitado pelo fato de que o presente é único e compartilhado pelo último estado de todas as informações. O presente funciona como uma perpendicular à linha de progressão do tempo. Sobre a linha do presente temos o último estado de tudo que existe no momento atual. Basta percorrer esta linha para examinarmos o estado da totalidade das coisas. A exibição de informações do passado, no entanto, exige que lidemos com o problema de ter que descobrir que informações eram contemporâneas de quais quando algum evento ocorreu. As coisas se complicam quando queremos saber não apenas o estado de um objeto em determinado momento, mas também como estavam e quais eram os demais objetos relacionados a ele naquele momento.

O que nos falta nestes casos é a linha do presente tal como ela era no momento passado. Para exibir coerentemente um momento do passado é preciso reconstruir a linha do presente tal como ela era na ocasião. Não nos basta ter separadamente a história dos estados de cada objeto de uma totalidade, é preciso saber que estados compartilhavam a mesma linha do presente a cada  momento. Tabelas de históricos sempre contêm a data em que os históricos foram gerados, mas a data não é uma forma confiável para armazenar a ordem em que os eventos ocorrem num sistema de informações. Tanto pela sua resolução, que é baixa, como pela estabilidade, já que pode ser alterada pelo usuário ou pelo mau funcionamento do computador. Mas há também os campos identidade que podem ser de auto-numeração crescente. Estes sim servem para exibir a ordem real dos estados salvos na tabela de históricos. O problema porém é que esta ordem não está relacionada com a ordem dos históricos das demais tabelas. Por exemplo, não há nada que me informe com exatidão qual era o último histórico do cliente “A” quando o produto “B” sofreu alteração de preço. Não tenho nem como saber se o cliente já havia sido cadastrado.

Para resolver o problema exposto acima, apresento a seguir uma forma de armazenamento dos relacionamentos existentes entre as tabelas de históricos para que se possa ter um meio de reconstruir a linha do presente para qualquer que seja o momento examinado na história de um objeto. Usando esta técnica é sempre possível levantar, para qualquer momento, quais eram os objetos existentes , quais eram seus relacionamentos e suas propriedades. Devo adiantar que a técnica de armazenamento é bastante simples, mas a recuperação dos dados é suficientemente complexa e trabalhosa para justificar a criação de uma ferramenta que automatize a tarefa. É nisto que estou trabalhando atualmente. Mas vamos à técnica de armazenamento.

Tabela:
RelacionamentosDeHistoricos
Layout:
StampId
StampDateTime
StampUser
StampAction
StampObjectType
StampObjectId
LastStampIdA
LastStampIdB
.
.
.
LastStampIdN

Na tabela RelacionamentosDeHistoricos temos um campo StampId, para servir de identificador único para cada registro da tabela; StampDateTime para conter a data e hora em que o registro foi criado; StampUser, para conter a identificação do usuário gerador da inclusão; StampAction, para conter o tipo de operação que causou a inclusão do registro; StampObjectType, para conter um valor convencionado na aplicação que identifique o tipo de objeto gerador do último histórico; StampObjectId, para conter o identificador único do objeto afetado pela operação e que é colhido da tabela em que os dados do objeto são armazenados. Os campos de nome prefixado por “LastStampId” são usados para conter o último StampId (identificador único) de cada tabela de históricos de objetos da composição. Para cada novo registro de histórico adicionado em uma destas tabelas, será também adicionado um registro na tabela RelacionamentosDeHistoricos. Quando da adição de um registro de RelacionamentosDeHistoricos, todos os valores para campos do tipo LastStampId serão mantidos iguais ao que eram quando da inclusão do último registro nesta tabela, exceto o campo LastStampId reservado para os identificadores únicos da tabela de históricos recém atualizada. Neste campo será armazenado o StampId do último registro incluído nesta tabela de históricos. No campo StampObjectType, será armazenado o tipo de objeto que teve sua tabela de históricos atualizada para gerar o registro de RelacionamentosDeHistoricos. Este valor é uma constante única que o programador cria para identificar cada tipo de objeto da composição. No campo StampObjectId, será armazenado o identificador do objeto afetado pela operação geradora do histórico. Estas últimas informações serão úteis na hora de recuperar os históricos da composição. Finalmente, no campo StampAction, será armazenada a ação que causou o histórico. Novamente teremos aí um valor correspondente a uma constante que o programador arbitrariamente cria para identificar cada tipo de ação: inclusão , alteração, exclusão, restauração.

Com as informações armazenadas na tabela RelacionamentosDeHistoricos, teremos o emparelhamento entre diferentes tabelas de históricos e também saberemos a ordem absoluta de criação dos históricos nestas tabelas. O campo StampId de RelacionamentosDeHistoricos nos dá esta ordem absoluta. Supondo que selecionemos um registro qualquer em uma tabela de históricos de algum tipo de objeto da composição. Chamemos o objeto gerador deste histórico de “A”. Para sabermos como estavam todas as demais tabelas de históricos de objetos relacionados a “A” no momento em que o registro foi gerado, pesquisamos, na tabela RelacionamentosDeHistoricos, o primeiro registro que contém o StampId do registro de histórico de “A” considerado. Teremos então, no mesmo registro de relacionamento, o último StampId de cada uma das demais tabelas de histórico no momento da criação daquele histórico. Com os valores de StampId de cada tabela de histórico, poderemos buscar, nestas tabelas relacionadas, o último histórico de cada objeto que participava da composição com o objeto “A” naquele momento. Vale lembrar que o objeto “A” pode ter qualquer relação de parentesco com os demais, ou seja, o estado total da composição pode sempre ser obtido para qualquer momento da história de qualquer dos seus componentes. Veja na ilustração abaixo como as tabelas se relacionam pelos registros de RelacionamentosDeHistoricos.

Seu banco de dados Access vive se corrompendo?

Viva Sem Bloquear Registros

Num ambiente multi-usuários o programador tem sempre que se preocupar com o problema do uso simultâneo das mesmas informações por vários usuários. O que muitas vezes se faz é recorrer ao bloqueio de registros, que pode ser feito só no momento da alteração do registro ou tão logo o usuário o acesse para iniciar uma atualização. Se partirmos para a segunda forma de bloqueio, vamos segurar um registro bloqueado por quanto tempo? E se o usuário que o está bloqueando sair para almoçar ou tomar um café? Seja qual for a forma de bloqueio nenhuma delas é boa. O bloqueio otimista, que só se dá no momento da atualização, não nos impede de acessar um registro, exibir suas informações na tela e, baseado nestas informações, realizar uma alteração que pode estar em total discordância com o estado atual do registro no momento em que o atualizamos. Enquanto exibimos os dados, um outro usuário pode acessar o mesmo registro e fazer uma alteração que cria uma condição completamente nova na base de dados. Além de desconhecermos esta alteração (o que poderia nos fazer mudar de idéia a respeito da nossa atualização), também poderemos estar enviando dados antigos para a base de dados e desfazendo o que o outro usuário acabou de fazer. Nesta situação estamos diante do que se chama de uma colisão de dados. Como então lidar com o problema da concorrência pelas mesmas informações num ambiente multi-usuários e tratar as colisões de dados?

Há duas soluções que se pode usar. Uma é usar o conceito de “versão de registro”. Uma idéia bem simples e que dá maior liberdade e segurança no acesso às informações. A idéia é ter um identificador de versão para cada registro em cada tabela que será atualizada por múltiplos usuários. A versão pode ser implementada criando-se um campo numérico inteiro no layout  de registro e com valor padrão 0. Toda vez que um registro é acessado para  ser modificado, trazemos com seus dados também o campo que contém a identificação de versão do registro. Após reunirmos os dados para fazer a atualização, criamos uma instrução “UPDATE” da linguagem SQL onde condicionamos na cláusula WHERE que o registro somente será  atualizado se o seu identificador de versão atual for igual àquele que obtivemos no momento que acessamos seus dados. Se esta condição estiver satisfeita, atualizaremos o registro acrescentando 1 ao valor atual do identificador de versão do registro. Exemplo:

“UPDATE Veiculos SET PLACA = ‘AAA1234’, CHASSI = ‘1234567890WERTYU’, IdentificadorDeVersao = IdentificadorDeVersao + 1 WHERE CodVeiculo = 1000 AND IdentificadorDeVersao = ” & lngUltimoIdentificadorDeVersaoObtido

Desta forma, se um outro usuário estiver visualizando o registro e quiser enviar uma atualização, seu identificador de versão não mais será igual àquele que ele recebeu no momento que leu o registro anteriormente. E qualquer tentativa de atualizar o registro não se cumprirá, porque a condição de estar trabalhando com a versão mais atual do registro não será satisfeita na sua instrução “UPDATE”.

Neste caso então, o programa deve verificar o número de registros afetados pela operação (neste caso será 0) e buscar na base de dados os valores atuais para os campos do registro. Comparará campo a campo os seus dados com os atuais e mostrará uma mensagem para o usuário informando o que mudou e perguntando se deve continuar. Caso o usuário concorde em atualizar o registro, uma nova tentativa de envio dos  dados será feita, mas só que agora informando o identificador de versão mais atual para não haver novamente o conflito. Caso um outro usuário tenha feito uma nova alteração neste intervalo de tempo, o identificador de versão terá sido modificado e  o conflito se repetirá. O mesmo procedimento será seguido até que as versões de registros confiram ou o usuário cancele a atualização.

O inconveniente sério da abordagem acima é que o seu programa pode não ser o único a acessar a mesma base de dados. E como garantir então que os demais programas seguirão a mesma técnica nas atualizações de registros? Não há como garantir isto. Para simplificar as coisas ainda mais e evitar o inconveniente acima, podemos fazer o mesmo que o ADO faz quando chamamos o método UpdateBatch de um recordset. Quando se chama o método UpdateBatch, o ADO cria uma consulta de ação para cada registro que precisa ser incluído, atualizado ou excluído. Em uma atualização, a consulta de ação é criada usando o critério da cláusula WHERE para comparar os valores de todos os campos do registro que será atualizado com os valores que estes campos tinham no momento que foram lidos. Se houver alguma diferença, é porque outro usuário já atualizou o registro ou o excluiu, e o número de registros afetados pela operação será zero. Verificando o número de registros afetados pela consulta e a não ocorrência de erros, o programa atualizador tem como saber se houve uma colisão de dados. Nestes casos, é feita uma consulta para recuperar o estado atual do registro e retorná-lo para o usuário com as devidas comparações. Isto nos livra de ter que usar um campo em cada tabela para indicar a versão de registros e também nos tira do risco de uma outra aplicação produzir inconsistências no nosso modo de trabalhar. O inconveniente aqui é o de ter de criar consultas tão longas quantos forem os campos da tabela a ser atualizada. Você pode perguntar: E por que não usar logo o método UpdateBatch do recordset ADO? Bem, se você estiver trabalhando em três camadas, irá descobrir o inconveniente de não conseguir retornar o valor da propriedade UnderlyingValue dos campos de um recordset desconectado. Esta propriedade é usada pelo método Resync que você chamaria para tratar colisões de dados após um UpdateBatch. O método Resync, adequadamente chamado, colocaria nesta propriedade os valores atuais dos campos do registro conflitante para que você os comparasse com os valores da propriedade OriginalValue de cada campo. Como os valores de UnderlyingValue não são retornados para o cliente, você teria o trabalho de montar um esquema de retorno destes dados feito à parte. Além disto, o automatismo de UpdateBatch pode dificultar o seu controle sobre a operação, principalmente se você estiver trabalhando com Access. Você pode, por exemplo, querer gravar históricos para as operações que são feitas em cada registro. Vale lembrar também que, para trabalhar com os métodos UpdateBatch e Resync, você precisará ter no recordset os campos chave primária de qualquer tabela fornecedora de campos para a consulta  geradora do recordset. O ADO precisa destas informações para montar suas consultas internamente.
Talvez esta dica não resolva o seu problema, mas uma das causas mais comuns para repetidas corrupções de bases de dados Access é o não fechamento e destruição explícitos de objetos Recordset e Database (quando se usa DAO). Sempre que crio uma aplicação usando Access, crio rotinas de fechamento de recordsets e databases para usar inclusíve dentro de tratamentos de erros. Estas rotinas são criadas assim:
Public Sub FechaRecordset ( rs as Recordset )

      On Error Resume Next

     rs.Close

     Set rs = Nothing

End Sub

Public Sub FechaDatabase (db as Database )

      On Error Resume Next

     db.Close

     Set db = Nothing

End Sub 

Onde quer que você declare uma variável como Recordset, seja a nível de módulo ou de procedimento, certifique-se de chamar a rotina de fechamento explícito dos recordsets ao sair do escopo em questão. Atente especialmente para colocar uma rotina de tratamento de erros dentro de cada procedimento que declarar variável do tipo Recordset. Como você não tem como saber se um erro terá ocorrido antes ou depois de instanciar objetos Recordset nas variáveis declaradas, e como em tratamentos de erros não há como usar a cláusula On Error, é necessário ter uma rotina que pode tratar internamente o erro que ocorreria ao se chamar o método Close para um objeto que nem tenha sido instanciado ainda. Seja dentro dos tratamentos de erros ou em qualquer parte, chame sempre FechaRecordset para todas as variáveis do tipo Recordset que estiverem saindo do escopo. Se estiver usando DAO, faça o mesmo com os objetos Database quando não mais for usá-los ou ao encerrar sua aplicação. Se estiver usando ADO, faça o mesmo com as conexões quando não mais precisar usá-las ou ao encerrar sua aplicação.

Conheço inúmeros relatos de problemas recorrentes de corrupção de bancos de dados Access que foram resolvidos com esta medida simples e  que deve se tornar um hábito de programação.

Usando DDL  

A SQL a esta altura já deve ser uma velha conhecida sua (pelo menos deveria). Vamos recordar alguns comandos SQL básicos mas essenciais para gerenciar banco de dados e tabelas com SQL:

Criando indices com CREATE INDEX

Depois que você criou a tabela você pode criar índices usando a cláusula CREATE INDEX.

Sintaxe: CREATE INDEX nome_indice ON nome_tabela(nome do campo)

Exemplos :

1- CREATE INDEX nome ON Clientes(nome) – cria o índice chamado nome na tabela Clientes no campo nome.

2- CREATE UNIQUE INDEX PrimaryKey ON Clientes(Codigo) WITH PRIMARY – define o campo codigo como chave primária na tabela Clientes

Apagando tabelas e índices com DROP

Para apagar elementos do banco de dados usamos a cláusula DROP.

Sintaxe : DROP Table/Index Nome_Tabela

Exemplos:

1- DROP TABLE Clientes – apaga a tabela Clientes

2- DROP INDEX PrimaryKey ON Clientes – remove o índice PrimaryKey na tabela Clientes

Modificando uma Tabela com ALTER

Para alterar a condição de um campo em uma tabela podemos usar a cláusula ALTER.

Exemplos:

1- ALTER TABLE Clientes ADD COLUMN Endereco String – inclui a coluna Endereco na tabela Clientes

2- ALTER TABLE Clientes DROP COLUMN Endereco – remove a coluna Endereco da tabela Clientes

Adicionando Restrições à uma tabela

Para criar uma restrição usamos a cláusula CONSTRAINT com dois parâmetros : o nome do índice e o nome do campo

Exemplos:

1- CREATE TABLE Teste ( Nome Text (40) , Endereco Text (40)), CONSTRAINT iNome UNIQUE (Nome) – define com campo exclusivo

2- CREATE TABLE Teste ( Nome Text (40) , Endereco Text (40)), CONSTRAINT iNome PRIMARY KEY (nome) – Cria tabela com chave primária.

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s