Banco de dados
Índice
- Diagrama entidade-relacionamento
- Limpeza do banco de dados
- Versionamento do banco de dados
- Atualização do banco para o PostgreSQL 8.3
- Solucionando problemas
- Links úteis
O banco de dados do i-Educar pode é composto por 330 tabelas, 9 views e 100 stored procedures. Essa grande quantidade de objetos já torna o trabalho com o banco complexo, some a isso a integridade referencial e uma normalização estranha (ruim em muitos pontos).
Desde a estabilização do projeto com a definição da versão 1.0.0, o banco passou por poucas mas importantes alterações.
Essa página documenta essas alterações além de conter outras referências úteis.
Diagrama entidade-relacionamento
Existe um diagrama entidade-relacionamento gerado através de engenharia reversa pelo software Aqua Data Studio, da AquaFold, que doou uma licença "Open-source developer" para o contribuidor Eriksen Costa.
O diagrama está disponível nos formatos png (imagem) e xed (xml interpretado pelo AquaFold Data Studio) na seção arquivos da comunidade i-Educar (pasta Desenvolvimento/DER) e correspondem ao banco de dados na revisão r698.
Limpeza do banco de dados
Antes da definição da versão 1.0.0, o i-Educar era distribuído como um pacote zip sem sincronia com os fontes do repositório. Um arquivo sql com o schema do banco de dados era distribuído com muitos registros de teste, contendo escolas, alunos, servidores e notas cadastradas.
Isso dificultava a implantação da aplicação pois não havia uma forma simples de apagar todos os dados pré-cadastrados.
O banco de dados foi "limpo" (mais detalhes no ticket 37) e então versionado.
Versionamento do banco de dados
A grande dificuldade de se trabalhar com banco de dados é torná-los evolutivos junto com o código-fonte. Geralmente o schema de um banco de dados é alterado ao longo do tempo, diversas vezes, para acomodar-se com as alterações do código-fonte, seja por conta do desenvolvimento de novas features ou por reescrita de partes da aplicação.
O grande problema, porém, é que essas alterações não são versionadas. O código-fonte evolui mas a representação do banco de dados não o acompanha, tornando difícil, por exemplo, retornar uma aplicação ao exato ponto para onde um bug foi introduzido ou até mesmo visualizar a evolução do schema.
A evolução do banco de dados no i-Educar é gerenciada através das técnicas de padrão industrial documentadas pelo DbDeploy, uma ferramenta de gerenciamento de mudança de banco de dados open-source.
O DbDeploy é usado no i-Educar através de uma tarefa Phing (ferramenta de build usada para a criação de novas releases). O processo é bastante simples e consiste na criação de arquivos de migração, ou arquivos deltas. No i-Educar, esses arquivos deltas estão localizados no diretório misc/database/deltas (disponível somente através de um checkout do svn ou clone do repositório git).
Arquivo delta
Um arquivo delta é um simples arquivo texto com duas seções, a seção de deploy e a seção de rollback. A seção de deploy contém todo o código SQL necessário para efetuar uma alteração no banco de dados, sejam eles comandos DDL ou DML. A seção rollback pode conter o mesmo tipo de código, sendo que este código será executado para desfazer as alterações efetuadas pela seção deploy. Um exemplo de um arquivo delta é listado a seguir:
-- // -- -- Cria o schema para módulos "modules". -- -- @author Eriksen Costa Paixão <eriksen.paixao_bs@cobra.com.br> -- @license @@license@@ -- @version $Id$ -- CREATE SCHEMA "modules"; -- //@UNDO DROP SCHEMA "modules"; -- //
O arquivo da lista é o misc/database/deltas/6_cria_schema_modules.sql. Note que o arquivo é delimitado em duas seções. A primeira seção contém o código SQL para criar um novo "SCHEMA" no banco de dados chamado modules e a segunda seção, delimitada pelo label @UNDO contém o código SQL para remover o "SCHEMA" modules.
O ponto chave do versionamento com o DbDeploy está na nomenclatura dos arquivos deltas. Note que o nome do arquivo delta inicia com um número. Cada arquivo de delta deve possuir um número sequencial que é verificado pelo tarefa DbDeploy do Phing para que este identifique quantos arquivos deltas precisam ser aplicados no banco de dados durante o build do i-Educar.
Aplicando os deltas no banco de dados com o Phing
Após criar seu arquivo delta, você precisa aplicá-lo no banco de dados. Para isso, é necessário executar a ferramenta Phing com o arquivo de build do i-Educar. A instalação do Phing é bastante simples caso você tenha a ferramenta PEAR (PHP Extension and Application Repository) do PHP instalada no seu ambiente.
Observação: o processo de execução da aplicação dos arquivos deltas no banco será nomeado migração na documentação do i-Educar para ter consistência com a nomenclatura usada em outros projetos open-source como o Ruby on Rails e o Doctrine ORM.
Usuários Debian/Ubuntu? podem instalar o PEAR e o Phing seguindo os passos da listagem a seguir:
$ sudo aptitude install php-pear $ pear channel-discover pear.phing.info $ sudo pear install phing/phing-beta
Após a instalação do Phing, você poderá executar de forma automatizada os processos para criação de uma nova release ou a de migração de banco de dados. Para ver os comandos disponíveis no arquivo de build do i-Educar, execute o comando a seguir:
$ cd /caminho/para/ieducar $ phing -l -f misc/build.xml
A saída do comando deverá ser parecida com a seguinte:
Sistema de gestão escolar Default target: ------------------------------------------------------------------------------- release Main targets: ------------------------------------------------------------------------------- migrate Migração de BD Subtargets: ------------------------------------------------------------------------------- build gzip prepare release zip
O comando necessário para executar a migração de banco de dados é o migrate. Antes de executar o comando migrate, é necessário definir os dados de conexão com o banco de dados no arquivo build.xml. Procure pelo trecho seguinte e altere para que o Phing e o DbDeploy possam conectar com o banco de dados com sucesso e aplicar as migrações dos arquivos deltas.
<!-- Configuração de acesso ao banco de dados para o DbDeployTask --> <property name="project.conf.dbdeploy.hostname" value="localhost" /> <property name="project.conf.dbdeploy.username" value="ieducar" /> <property name="project.conf.dbdeploy.dbname" value="ieducar" /> <property name="project.conf.dbdeploy.password" value="" />
Com o arquivo de build devidamente configurado, execute o seguinte comando:
$ phing -f misc/build.xml migrate
A saída do comando devererá ser parecida com a seguinte:
i-Educar > migrate:
[dbdeploy] Getting applied changed numbers from DB: pgsql:host=localhost;dbname=ieducar
[dbdeploy] Current db revision: 20
[exec] Executing command: /bin/psql -U ieducaruser development < deploy/deploy.sql 2>&1
BUILD FINISHED
Total time: 1.7139 second
Note que a tarefa DbDeploy do Phing verificou que a revisão do banco de dados está atualmente no número 20. Esse comando foi executado em uma cópia do i-Educar do repositório svn na revisão r698 e, portanto, não houve nenhum delta novo a executar.
Para ver o DbDeploy em ação, vamos executar a tarefa migrate com um arquivo delta de teste. Crie um arquivo com o nome 21_delta_teste.sql com o seguinte conteúdo:
-- // CREATE SCHEMA "teste"; -- //@UNDO DROP SCHEMA "teste"; -- //
Execute novamente o comando Phing:
$ phing -f misc/build.xml migrate
A saída do comando será idêntica. A diferença é que um novo schema foi criado no banco de dados:
ieducar=# \dn
List of schemas
Name | Owner
--------------------+-------------
acesso | ieducaruser
alimentos | ieducaruser
cadastro | ieducaruser
consistenciacao | ieducaruser
historico | ieducaruser
information_schema | postgres
modules | ieducaruser
pg_catalog | postgres
pg_toast | postgres
pmiacoes | ieducaruser
pmicontrolesis | ieducaruser
pmidrh | ieducaruser
pmieducar | ieducaruser
pmiotopic | ieducaruser
portal | ieducaruser
public | postgres
teste | ieducaruser
urbano | ieducaruser
(18 rows)
Internamente, o DbDeploy verifica a revisão atual do banco de dados na tabela public.changelog:
ieducar=# SELECT change_number, applied_by, description FROM changelog;
change_number | applied_by | description
---------------+------------+-------------------------------------------------------------------------------------------------------------
2 | dbdeploy | 2_popula_dados_basicos.sql
1 | dbdeploy | 1_cria_schema_inicial.sql
3 | dbdeploy | 3_corrige_sequences.sql
4 | dbdeploy | 4_permissoes_usuario_tipo_biblioteca.sql
5 | dbdeploy | 5_corrige_triggers_constrainst.sql
6 | dbdeploy | 6_cria_schema_modules.sql
7 | dbdeploy | 7_cria_tabelas_modulo_regra_avaliacao.sql
8 | dbdeploy | 8_remove_campos_pmieducar_serie.sql
9 | dbdeploy | 9_atualiza_foreign_key_de_pmieducar_escola_serie_disciplina.sql
10 | dbdeploy | 10_remove_campos_pmieducar_curso.sql
11 | dbdeploy | 11_adiciona_foreign_key_entre_pmieducar_serie_e_modules_regra_avaliacao.sql
12 | dbdeploy | 12_cria_tabelas_modulo_avaliacao.sql
13 | dbdeploy | 13_adiciona_campo_carga_horaria_pmieducar_escola_serie_disciplina_modules_componente_curricular_ano_escolar
14 | dbdeploy | 14_adiciona_campo_etapa_modules_nota_componente_curricular_media.sql
15 | dbdeploy | 15_atualiza_foreign_key_modules_falta_geral.sql
16 | dbdeploy | 16_permissoes_para_modules_e_correcao_menus.sql
17 | dbdeploy | 17_adiciona_campo_curso_componente_ano_escolar_e_atualiza_constraint_servidor_disciplina.sql
18 | dbdeploy | 18_cria_tabelas_de_parecer_descritivo.sql
19 | dbdeploy | 19_atualiza_nome_de_relatorio_no_menu_e_permissoes.sql
20 | dbdeploy | 20_remove_permissao_e_menu_diario_avaliacao_temp.sql
21 | dbdeploy | 21_delta_teste.sql
(21 rows)
Pronto! Isso é o suficiente para que você consiga criar novos arquivos deltas para alterar de o banco de dados de forma evolutiva e sincronizada com o código-fonte.
Observação: Embora o processo seja simples, você só deve usá-lo caso você pense em compartilhar a sua implementação com o time de desenvolvimento do i-Educar. Como o DbDeploy usa números sequenciais para manter o rastro das alterações, você colocaria em risco a sincronização da sua instância do i-Educar com o lançamento das futuras versões.
Desafio
O desafio é conseguir levar a forma simples de manutenção de banco de dados proposto pelo DbDeploy para outras áreas do i-Educar, como a API de módulos além do instalador (e da interface de atualização de versão) propostos para o projeto.
Atualização do banco para o PostgreSQL 8.3
Existe uma proposta em andamento para a atualização do banco de dados do i-Educar para a versão 8.3 do PostgreSQL. Os motivos da incompatibilidade (e por consequência, dependência da versão 8.2) estão documentadas no ticket 52.
Para facilitar o rastreamento dessa tarefa, foram extraídas todas as stored procedures do arquivo misc/database/ieducar.sql para arquivos individuais, permitindo a revisão de cada função para as chamadas de typecasting explícitos.
Para isso, foi criado um repositório no GitHub como forma de facilitar a revisão do SQL por desenvolvedores interessados. Caso faça alguma atualização em um dos arquivos, envie um Pull Request através de seu fork para que os desenvolvedores do i-Educar analisem o seu código e façam um merge caso as alterações estejam corretas. Consulte a documentação do GitHub para saber como criar forks de um repositório git.
Solucionando problemas
Caso apareça a seguinte mensagem ao tentar usar o comando:
$ phing -f misc/build.xml migrate
Execution of target "migrate" failed for the following reason: /var/www/misc/build.xml:142:16: [wrapped: pgsql is not supported by dbdeploy task.]
BUILD FAILED /var/www/misc/build.xml:142:16: [wrapped: pgsql is not supported by dbdeploy task.] Total time: 0.2560 seconds
Para solucionar crie o seguinte arquivo chamado DbmsSyntaxPgSql.php no diretorio $ /usr/share/php/phing/tasks/ext/dbdeploy
com o seguinte contéudo:
<?php
class DbmsSyntaxPgsql extends DbmsSyntax
{
public function generateTimestamp()
{
return "current_timestamp";
}
}
?>
depois edite o arquivo $/usr/share/php/phing/tasks/ext/dbdeploy/DbmsSyntaxFactory.php e altere a seguinte função:
public function getDbmsSyntax()
{
switch ($this->dbms){
case('sqlite') :
require_once 'phing/tasks/ext/dbdeploy/DbmsSyntaxSQLite.php';
return new DbmsSyntaxSQLite();
case('mysql'):
require_once 'phing/tasks/ext/dbdeploy/DbmsSyntaxMysql.php';
return new DbmsSyntaxMysql();
case('mssql'):
require_once 'phing/tasks/ext/dbdeploy/DbmsSyntaxMsSql.php';
return new DbmsSyntaxMsSql();
default:
throw new Exception($this->dbms . ' is not supported by dbdeploy task.');
}
}
acrescentando a seguinte linha:
case('pgsql'):
require_once 'phing/tasks/ext/dbdeploy/DbmsSyntaxPgSql.php';
return new DbmsSyntaxPgsql();
ficando assim:
public function getDbmsSyntax()
{
switch ($this->dbms){
case('sqlite') :
require_once 'phing/tasks/ext/dbdeploy/DbmsSyntaxSQLite.php';
return new DbmsSyntaxSQLite();
case('mysql'):
require_once 'phing/tasks/ext/dbdeploy/DbmsSyntaxMysql.php';
return new DbmsSyntaxMysql();
case('mssql'):
require_once 'phing/tasks/ext/dbdeploy/DbmsSyntaxMsSql.php';
return new DbmsSyntaxMsSql();
case('pgsql'):
require_once 'phing/tasks/ext/dbdeploy/DbmsSyntaxPgSql.php';
return new DbmsSyntaxPgsql();
default:
throw new Exception($this->dbms . ' is not supported by dbdeploy task.');
}
}
depois rode o comando:
$ phing -f misc/build.xml migrate
e veja se dessa vez foi sem erro.
Links úteis
- Documentação oficial do Phing (em inglês)
- Documentação oficial do DbDeploy (em inglês)
