Bancos de dados relacionais, orientação a objetos e DBIx::Class, parte 2

Eden Cardim
Publicado em 01/03/2010

Bancos de dados relacionais, orientação a objetos e DBIx::Class, parte 2

No artigo de ontem, falamos sobre conceitos básicos de camadas de acesso a dados e os problemas inerentes. Abordamos os conceitos básicos de operação do DBIx::Class e criamos um esquema de exemplo para experimentar algumas consultas. Hoje iremos abordar inserções e atualizações, além de introduzir algumas técnicas avançadas como depuração e otimização.

Objetos Row - Registros

A forma básica de se criar um registro é instanciar um objeto da classe equivalente à tabela que será alterada:

    diff --git a/source/script/test2.pl b/source/script/test2.pl
    new file mode 100644
    index 0000000..098abdd
    --- /dev/null
    +++ b/source/script/test2.pl
    @@ -0,0 +1,7 @@
    +use warnings;
    +use strict;
    +use MySchema;
    +
    +my $schema = MySchema->connect('dbi:Pg:dbname=equinocio_students', 'edenc', '');
    +my $alunos = $schema->resultset('Aluno');
    +my $aluno = $alunos->new({ nome => 'José' });

O método new deve ser invocado sobre o result set onde se quer inserir o registro, nesse caso, no conjunto de todos os alunos. O objeto criado ainda não está persistido na base de dados.

    diff --git a/source/script/test2.pl b/source/script/test2.pl
    index 098abdd..8b4c989 100644
    --- a/source/script/test2.pl
    +++ b/source/script/test2.pl
    @@ -5,3 +5,4 @@ use MySchema;
     my $schema = MySchema->connect('dbi:Pg:dbname=equinocio_students', 'edenc', '');
     my $alunos = $schema->resultset('Aluno');
     my $aluno = $alunos->new({ nome => 'José' });
    +$aluno->insert;

O método insert persiste um objeto-registro na base de dados. Se o objeto já estiver persistido, esse método é um no-op. Para associar o novo aluno à matérias:

    diff --git a/source/script/test2.pl b/source/script/test2.pl
    index 50636f3..57ec631 100644
    --- a/source/script/test2.pl
    +++ b/source/script/test2.pl
    @@ -5,4 +5,11 @@ use MySchema;
     my $schema = MySchema->connect('dbi:Pg:dbname=equinocio_students', 'edenc', '');
     my $alunos = $schema->resultset('Aluno');
     my $aluno = $alunos->find({ nome => 'José' });
    -$aluno->insert;
    +my $jose_materias = $aluno->aluno_materias;
    +print 'Matérias estudadas por ' . $aluno->nome . ': '. $aluno->materias->count, "\n";
    +
    +$jose_materias->create({ materia => { nome => 'Geografia'} });
    +$jose_materias->create({ materia => { nome => 'Física'} });
    +
    +print 'Matérias estudadas por ' . $aluno->nome . ":\n";
    +print $_->nome, "\n" for $aluno->materias->all;

    perl -Ilib script/test2.pl

    Matérias estudadas por José: 0
    Matérias estudadas por José:
    Física
    Geografia

Por trás das cenas, aconteceram muito mais coisas do que é aparentado no código. O método create é um atalho para $resultset->new->insert. A matéria "Geografia" ainda não existe, então o DBIx::Class automaticamente criou um novo registro na tabela materia, obteve as colunas necessárias através dos meta-dados informados no relacionamento materia da classe AlunoMateria e criou o registro nessa tabela com a associação correta ao novo registro da tabela materia. No caso de "Física", o registro já existia, então ocorreu o mesmo processo, porém não foi criado um registro novo. Além disso, como $jose_materias é o sub-conjunto das matrículas em matérias do aluno "João", o DBIx::Class também inseriu a associação correta com o registro desse aluno. Vale a observação que o argumento materia é o nome do relacionamento, e não o nome de uma coluna, por isso o DBIx::Class conseguiu induzir os dados corretos para fazer a associação entre os registros.

Objetos-registro e objetos-conjunto operam harmoniosamente, utilizando os meta-dados informados pelos relacionamentos. Por exemplo, poderíamos utilizar um objeto existente para fazer a associação com materia.

    diff --git a/source/script/test2.pl b/source/script/test2.pl
    index 57ec631..454f82a 100644
    --- a/source/script/test2.pl
    +++ b/source/script/test2.pl
    @@ -8,8 +8,8 @@ my $aluno = $alunos->find({ nome => 'José' });
     my $jose_materias = $aluno->aluno_materias;
     print 'Matérias estudadas por ' . $aluno->nome . ': '. $aluno->materias->count, "\n";

    -$jose_materias->create({ materia => { nome => 'Geografia'} });
    -$jose_materias->create({ materia => { nome => 'Física'} });
    +my $materia = $schema->resultset('Materia')->find({ nome => 'Biologia' });
    +$jose_materias->create({ materia => $materia });

     print 'Matérias estudadas por ' . $aluno->nome . ":\n";
     print $_->nome, "\n" for $aluno->materias->all;

Para ter uma ideia melhor do que acontece por trás das cenas, pode-se ativar a variável de ambiente DBIC_TRACE, que irá exibir o SQL gerado em STDERR:

    DBIC_TRACE=1 perl -Ilib script/test2.pl

    SELECT me.id, me.nome FROM aluno me WHERE ( me.nome = ? ): 'José'
    SELECT COUNT( * ) FROM aluno_materia me JOIN materia materia ON materia.id = me.materia_id WHERE ( me.aluno_id = ? ): '4'
    Matérias estudadas por José: 2
    SELECT me.id, me.nome FROM materia me WHERE ( me.nome = ? ): 'Biologia'
    INSERT INTO aluno_materia ( aluno_id, materia_id) VALUES ( ?, ? ): '4', '1'
    Matérias estudadas por José:
    SELECT materia.id, materia.nome FROM aluno_materia me JOIN materia materia ON materia.id = me.materia_id WHERE ( me.aluno_id = ? ): '4'
    Biologia
    Física
    Geografia

Uma outra forma de se criar registros em tabelas-relacionamento é utilizar um construtor especial que é criado pelo DBIx::Class quando se declara um relacionamento many_to_many, como exemplo, o caso de acrescentar um aluno a uma turma existente:

    diff --git a/source/script/test3.pl b/source/script/test3.pl
    new file mode 100644
    index 0000000..6ab33e4
    --- /dev/null
    +++ b/source/script/test3.pl
    @@ -0,0 +1,8 @@
    +use warnings;
    +use strict;
    +use MySchema;
    +
    +my $schema = MySchema->connect('dbi:Pg:dbname=equinocio_students', 'edenc', '');
    +my $aluno = $schema->resultset('Aluno')->find({ nome => 'José' });
    +my $turma = $schema->resultset('Turma')->find({ turma => '7ª Série'});
    +$turma->add_to_alunos($aluno);

    DBIC_TRACE=1 perl -Ilib script/test3.pl

    SELECT me.id, me.nome FROM aluno me WHERE ( me.nome = ? ): 'José'
    SELECT me.id, me.turma FROM turma me WHERE ( me.turma = ? ): '7ª Série'
    INSERT INTO turma_aluno ( aluno_id, turma_id) VALUES ( ?, ? ): '4', '1'

Pode-se usar o caso inverso também:

    $aluno->add_to_turmas($turma);

Quando a tabela-relacionamento tem atributos de relacionamento, como é o caso de aluno_materia. O método add_to_$rel aceita um hashref como argumento adicional, contendo os valores dos atributos em questão:

    diff --git a/source/script/test4.pl b/source/script/test4.pl
    new file mode 100644
    index 0000000..2d693c6
    --- /dev/null
    +++ b/source/script/test4.pl
    @@ -0,0 +1,8 @@
    +use warnings;
    +use strict;
    +use MySchema;
    +
    +my $schema = MySchema->connect('dbi:Pg:dbname=equinocio_students', 'edenc', '');
    +my $aluno = $schema->resultset('Aluno')->find({ nome => 'Maria' });
    +my $materia = $schema->resultset('Materia')->find({ nome => 'Física'});
    +$aluno->add_to_materias($materia, { nota => 8 });

Uma vez que os conceito de objeto-conjunto (resultset), objeto-registro (row) e os relacionamentos eles são compreendidos, a interação entre esses objetos geralmente é bastante intuitiva e o DBIx::Class vai fazer "a coisa certa" na maior parte das vezes. As alternativas de interação são bastante vastas, experimentar é a melhor forma de aprender, nesse caso. A documentação também apresenta exemplos e casos mais comuns.

Para atualizar um registro, pode-se utilizar os métodos de acesso das colunas nos objetos-registro:

    diff --git a/source/script/test5.pl b/source/script/test5.pl
    new file mode 100644
    index 0000000..47379e5
    --- /dev/null
    +++ b/source/script/test5.pl
    @@ -0,0 +1,11 @@
    +use warnings;
    +use strict;
    +use MySchema;
    +
    +my $schema = MySchema->connect('dbi:Pg:dbname=equinocio_students', 'edenc', '');
    +my $aluno = $schema->resultset('Aluno')->find({ nome => 'Maria' });
    +my $aluno_materia = $aluno->aluno_materias
    +    ->search({'materia.nome' => 'Física'}, { join => ['materia']})
    +    ->first;
    +$aluno_materia->nota(7);
    +$aluno_materia->update;

    DBIC_TRACE=1 perl -Ilib script/test5.pl

    SELECT me.id, me.nome FROM aluno me WHERE ( me.nome = ? ): 'Maria'
    SELECT me.aluno_id, me.materia_id, me.nota FROM aluno_materia me JOIN
    materia materia ON materia.id = me.materia_id WHERE ( (
    materia.nome = ? AND me.aluno_id = ? ) ): 'Física', '2'
    UPDATE aluno_materia SET nota = ? WHERE ( ( aluno_id = ? AND materia_id = ?
    ) ): '7', '2', '3'

Alternativamente, pode-se atualizar o registro com uma única chamada:

    $aluno_materia->update({ nota => 7 });

Atualizações em massa são possíveis através de objetos resultset:

    my $aluno_materias = $schema->resultset('AlunoMateria');
    $aluno_materias->search({ nota => undef })->update({ nota => 5 });

Depuração e Otimização

Nem sempre o DBIx::Class vai acertar a melhor forma de se fazer a consulta, as vezes é necessário dar algumas "dicas" de otimização. Um dos exemplos de ontem apresentou a seguinte consulta:

    use warnings;
    use strict;
    use MySchema;

    my $schema = MySchema->connect('dbi:Pg:dbname=equinocio_students', 'edenc', '');
    my $turmas = $schema->resultset('Turma');
    foreach my $turma ($turmas->all) {
        print ref $turma, ' => ', $turma->turma, "\n";
        foreach my $aluno ($turma->alunos->all) {
            print "\t", ref $aluno, ' => ', $aluno->nome, "\n";
            foreach my $materia ($aluno->materias) {
                print "\t\t", ref $materia, ' => ', $materia->nome, "\n";
            }
        }
    }

Rodando novamente com DBIC_TRACE ativado:

    DBIC_TRACE=1 perl -Ilib script/test.pl

    SELECT me.id, me.turma FROM turma me:
    SELECT aluno.id, aluno.nome FROM turma_aluno me JOIN aluno aluno ON
    aluno.id = me.aluno_id WHERE ( me.turma_id = ? ): '1'
    SELECT materia.id, materia.nome FROM aluno_materia me JOIN materia materia
    ON materia.id = me.materia_id WHERE ( me.aluno_id = ? ): '1'
    SELECT materia.id, materia.nome FROM aluno_materia me JOIN materia materia
    ON materia.id = me.materia_id WHERE ( me.aluno_id = ? ): '4'
    SELECT aluno.id, aluno.nome FROM turma_aluno me JOIN aluno aluno ON
    aluno.id = me.aluno_id WHERE ( me.turma_id = ? ): '2'
    SELECT materia.id, materia.nome FROM aluno_materia me JOIN materia materia
    ON materia.id = me.materia_id WHERE ( me.aluno_id = ? ): '2'

Os DBA's de plantão perceberão imediatamente o que está acontecendo e que esta não é a forma ótima de realizar a consulta. Primeiro, obteve-se a lista de turmas, em seguida uma consulta com as tabela-relacionamentos unidas às tabelas aluno e materia, de onde vêm os dados para popular os respectivos objetos. Realizar uma consulta para cada registro na tabela turma e outra para cada registro da tabela aluno pode não ter muito impacto nesse exemplo, porém se, por exemplo, a tabela aluno crescer para, digamos, 500 registros (escolas com 500 alunos são bastante comuns) o overhead de realizar 500 consultas será perceptível. Para esse caso, utiliza-se o atributo prefetch:

    diff --git a/source/script/test.pl b/source/script/test.pl
    index 8977ab7..697ecc9 100644
    --- a/source/script/test.pl
    +++ b/source/script/test.pl
    @@ -3,7 +3,9 @@ use strict;
     use MySchema;

     my $schema = MySchema->connect('dbi:Pg:dbname=equinocio_students', 'edenc', '');
    -my $turmas = $schema->resultset('Turma');
    +my $turmas = $schema->resultset('Turma')->search({}, {
    +    prefetch => {turma_alunos => { aluno => { aluno_materias => 'materia' }}},
    +});
     foreach my $turma ($turmas->all) {
         print ref $turma, ' => ', $turma->turma, "\n";
         foreach my $aluno ($turma->alunos->all) {




    DBIC_TRACE=1 perl -Ilib script/
    test.pl
    SELECT me.id, me.turma, turma_alunos.turma_id, turma_alunos.aluno_id,
    aluno.id, aluno.nome, aluno_materias.aluno_id, aluno_materias.materia_id,
    aluno_materias.nota, materia.id, materia.nome FROM turma me LEFT JOIN
    turma_aluno turma_alunos ON turma_alunos.turma_id = me.id LEFT JOIN aluno
    aluno ON aluno.id = turma_alunos.aluno_id LEFT JOIN aluno_materia
    aluno_materias ON aluno_materias.aluno_id = aluno.id LEFT JOIN materia
    materia ON materia.id = aluno_materias.materia_id ORDER BY
    turma_alunos.turma_id, aluno_materias.aluno_id:
    MySchema::Result::Turma => 7ª Série
            MySchema::Result::Aluno => João
                    MySchema::Result::Materia => Biologia
                    MySchema::Result::Materia => Matemática
                    MySchema::Result::Materia => Física
            MySchema::Result::Aluno => José
                    MySchema::Result::Materia => Geografia
                    MySchema::Result::Materia => Física
                    MySchema::Result::Materia => Biologia
    MySchema::Result::Turma => 6ª Série
            MySchema::Result::Aluno => Maria
                    MySchema::Result::Materia => Biologia
                    MySchema::Result::Materia => Física

Com uma única consulta obtemos o mesmo resultado anterior, independente da quantidade de registros envolvidos. Uma observação importante é que essa otimização foi introduzida modificando apenas o conjunto-origem dos dados, o restante do acesso à API permaneceu idêntico. Isso só foi possível devido a outra coisa que aconteceu por trás das cenas, o DBIx::Class utilizou os meta-dados das tabelas e relacionamentos para realizar o colapso dos dados automaticamente.

Para ordenar os registros, utiliza-se o atributo order_by:

    diff --git a/source/script/test.pl b/source/script/test.pl
    index 8d090a4..579ffa2 100644
    --- a/source/script/test.pl
    +++ b/source/script/test.pl
    @@ -5,6 +5,7 @@ use MySchema;
     my $schema = MySchema->connect('dbi:Pg:dbname=equinocio_students', 'edenc', '');
     my $turmas = $schema->resultset('Turma')->search({}, {
         prefetch => {turma_alunos => { aluno => { aluno_materias => 'materia' }}},
    +    order_by => { '-asc' => [qw(me.turma aluno.nome materia.nome)]}
     });
     foreach my $turma ($turmas->all) {
         print ref $turma, ' => ', $turma->turma, "\n";

Views Virtuais e SQL Manual

Apesar de a arquitetura de consultas do DBIx::Class ser bastante expressiva e flexível, sempre existem os casos onde a melhor resolução é a escrita de SQL puro. Inclusive o sistema inteiro pode ser utilizado como um dicionário de consultas, dispensando o gerador de SQL interno mas preservando as funcionalidades de colapso, inserção, atualização e encadeamento. Uma view virtual funciona da mesma forma que uma classe-tabela porém a sua fonte de dados é proveniente de uma string de SQL. O restante das definições, como declarações de colunas, tuplas de unicidade e relacionamentos são idênticas às tabelas comuns. Por exemplo, seria desejável que a avaliação de desempenho dos alunos fosse consultada através de uma view virtual, já que pode envolver operações complexas como cálculo de pesos e medidas estatísticas como desvio-padrão. Uma forma simples de calcular a avaliação, em SQL:

    SELECT aluno_id,
           avg(nota) AS resultado_final,
           avg(nota) >= 6 AS foi_aprovado
      FROM aluno_materia group by aluno_id;

E a classe virtual view equivalente:

    diff --git a/source/lib/MySchema/Result/Avaliacao.pm b/source/lib/MySchema/Result/Avaliacao.pm
    new file mode 100644
    index 0000000..29039e2
    --- /dev/null
    +++ b/source/lib/MySchema/Result/Avaliacao.pm
    @@ -0,0 +1,26 @@
    +package MySchema::Result::Avaliacao;
    +
    +use strict;
    +use warnings;
    +
    +use base 'DBIx::Class';
    +
    +__PACKAGE__->load_components("Core");
    +__PACKAGE__->table_class('DBIx::Class::ResultSource::View');
    +__PACKAGE__->table('avaliacao');
    +__PACKAGE__->result_source_instance->is_virtual(1);
    +__PACKAGE__->result_source_instance->view_definition(q{
    +    SELECT aluno_id,
    +           avg(nota) AS resultado_final,
    +           avg(nota) >= 6 AS foi_aprovado
    +      FROM aluno_materia GROUP BY aluno_id
    +});
    +
    +__PACKAGE__->add_columns(qw(aluno_id resultado_final foi_aprovado));
    +
    +__PACKAGE__->belongs_to(
    +    aluno => 'MySchema::Result::Aluno'
    +          => { 'foreign.id' => 'self.aluno_id'}
    +);
    +
    +1;

Acréscimo do relacionamento na tabela aluno:

    diff --git a/source/lib/MySchema/Result/Aluno.pm b/source/lib/MySchema/Result/Aluno.pm
    index 4353455..2ac9b85 100644
    --- a/source/lib/MySchema/Result/Aluno.pm
    +++ b/source/lib/MySchema/Result/Aluno.pm
    @@ -42,6 +42,10 @@ __PACKAGE__->has_many(

     __PACKAGE__->many_to_many(materias => aluno_materias => 'materia');
     __PACKAGE__->many_to_many(turmas => turma_alunos => 'aluno');
    +__PACKAGE__->belongs_to(
    +    avaliacao => 'MySchema::Result::Avaliacao'
    +          => { 'foreign.aluno_id' => 'self.id' }
    +);

     # You can replace this text with custom content, and it will be preserved on regeneration
     1;




As alterações no script de teste para incluir a avaliação:

    diff --git a/source/script/test.pl b/source/script/test.pl
    index 579ffa2..c9b6939 100644
    --- a/source/script/test.pl
    +++ b/source/script/test.pl
    @@ -4,13 +4,15 @@ use MySchema;

     my $schema = MySchema->connect('dbi:Pg:dbname=equinocio_students', 'edenc', '');
     my $turmas = $schema->resultset('Turma')->search({}, {
    -    prefetch => {turma_alunos => { aluno => { aluno_materias => 'materia' }}},
    +    prefetch => {turma_alunos => { aluno => [{ aluno_materias => 'materia' }, 'avaliacao']}},
         order_by => { '-asc' => [qw(me.turma aluno.nome materia.nome)]}
     });
     foreach my $turma ($turmas->all) {
         print ref $turma, ' => ', $turma->turma, "\n";
         foreach my $aluno ($turma->alunos->all) {
             print "\t", ref $aluno, ' => ', $aluno->nome, "\n";
    +        print "\t\t", 'Resultado final: ', $aluno->avaliacao->resultado_final, "\n";
    +        print "\t\t", 'Status: ', ($aluno->avaliacao->foi_aprovado? 'aprovado' : 'reprovado'), "\n";
             foreach my $materia ($aluno->materias) {
                 print "\t\t", ref $materia, ' => ', $materia->nome, "\n";
             }




    MySchema::Result::Turma => 6ª Série
            MySchema::Result::Aluno => Maria
                    Resultado final: 6.0000000000000000
                    Status: aprovado
                    MySchema::Result::Materia => Biologia
                    MySchema::Result::Materia => Física
    MySchema::Result::Turma => 7ª Série
            MySchema::Result::Aluno => João
                    Resultado final: 5.0000000000000000
                    Status: reprovado
                    MySchema::Result::Materia => Biologia
                    MySchema::Result::Materia => Física
                    MySchema::Result::Materia => Matemática
            MySchema::Result::Aluno => José
                    Resultado final: 5.0000000000000000
                    Status: reprovado
                    MySchema::Result::Materia => Biologia
                    MySchema::Result::Materia => Física
                    MySchema::Result::Materia => Geografia

CONCLUSÃO

A filosofia básica do DBIx::Class é a mesma do perl: "coisas simples são fáceis e coisas complicadas possíveis". Existe uma variedade enorme de ferramentas disponíveis para realizar as tarefas básicas do cotidiano, porém, é bastante simples contornar o mecanismo básico e inserir customizações. Os conceitos apresentados nesse artigo e seu sucessor apresentaram alguns recursos na tentativa de "atiçar" a curiosidade dos leitores para investigar a gama completa de recursos de desenvolvimento. A partir daqui, é recomendavel a leitura minuciosa dos manuais do DBIx::Class. A lista de emails (dbix-class@lists.scsys.co.uk) e o canal de suporte do IRC (#dbix-class @ irc.perl.org ) são bastante movimentados e fornecem suporte em inglês. Alguns contribuidores do projeto DBIx::Class, inclusive o autor desse artigo frequentam a lista (saopaulo-pm@mail.pm.org) e o canal de IRC do São Paulo-PM (#sao-paulo.pm @ irc.prl.org), onde costumam disponibilizar suporte em português.

Happy Hacking!

AGRADECIMENTO

Thiago Rondon, que organizou o equinócio e conseguiu me convencer a passar horas escrevendo os artigos de ontem e hoje, além de contribuir com ideias e revisões. Nuba Princigalli, pela revisão dos artigos e bounce de ideias em geral. São Paulo Perl Mongers e Rio Perl Mongers pelos encontros sociais, eventos e fontes de procrastinação.

AUTOR

Eden Cardim <edencardim@gmail.com> é consultor de perl independente há 4 anos, trabalha com perl desde 1998 e é contribuidor dos projetos DBIx::Class, Catalyst e Reaction, além de contribuidor do CPAN. Atualmente, presta serviço para a ShadowCat Systems Ltd (shadowcat.co.uk).

blog comments powered by Disqus