Выпуск 22. Декабрь 2014

Perl 6 XXI века | Содержание | Обзор CPAN за ноябрь 2014 г.

DBIx::Class. Сборник рецептов

Сборник рецептов на все случаи жизни

В данной статье собраны рецепты использования DBIx::Class по следующим темам:

  • создание простых запросов;
  • выборка строк с помощью where, distinct, group by, having;
  • выборка строк по первичному и уникальному ключам;
  • использование custom-методов для Result- и ResultSet-классов;
  • использование отношений между таблицами;
  • подзапросы;
  • ограничение результатов поиска с помощью limit;
  • CRUD для строк;
  • CRUD;
  • CRUD с поиском.

Для начала создадим БД для работы. Это будет информация о некой компании, ее служащих и об отделах, в которых они работают.

Загрузить SQL-файл с кодом создания и добавления данных, а также схему БД с подробным описанием отношений между таблицами в Result-классе каждой таблицы можно по ссылке.

Простые запросы

Рецепт 1. Получить список resultset-объектов для всей выборки с помощью метода all

Решение:

say $_->name for $department_rs->all;

Для получения столбца мы вызываем метод-аксессор с именем столбца.

Рецепт 2. Итерируем выборку с помощью метода next

Решение:

while (my $department = $department_rs->next) {
  say $department->name;
}

Данный вариант может быть использован для эффективного итерирования каждой записи в выборке (resultset-е).

Рецепт 3. Итерируем выборку с помощью курсора

Решение:

my $client_cursor = $client_rs->cursor;
while (my @client_row = $client_cursor->next) {
  say $client_row[1]; # client name
}

Для итерирования используется метод next класса DBIx::Class::Cursor, который возвращает следующую строку из курсора в виде массива значений столбцов (результат работы метода fetchrow_array из DBI), т.е. на каждой итерации получаем следующую структуру:

[
    [0] 1,
    [1] "Telco Inc",
    [2] "1 Collins St Melbourne",
    [3] "Fred Smith",
    [4] 95551234
]

Рецепт 4. Получить массив хешей со значениями таблицы

Решение:

my @department = $department_rs->search(undef,
    {result_class => 'DBIx::Class::ResultClass::HashRefInflator'})->all;
say 'Department name: ' . $department[0]{name};

На выходе получается следующая структура:

[
    [0] {
        departmentid   42,
        name           "Финансовый отдел"
    },
    [1] {
        departmentid   128,
        name           "Отдел проектировия"
    },
    ...
]

Рецепт 5. Итерируем набор хешей со значениями таблицы

Решение:

my $rs = $department_rs->search(undef,
    {result_class => 'DBIx::Class::ResultClass::HashRefInflator'});
while (my $hashref = $rs->next) {
    say $hashref->{name};
}

На каждой итерации получаем ссылку на хеш такого вида:

\ {
    departmentid   42,
    name           "Финансовый отдел"
}

Рецепт 6. Посчитать количество строк в таблице

Запрос, который мы хотим получить:

select count(departmentID) from department;

Решение:

my $department_cnt =
  $department_rs->get_column('departmentid')->func('count');
say "departmentid count = $department_cnt";

Здесь мы с помощью метода get_column() получаем объект класса DBIx::Class::ResultSetColumn, который позволяет работать с отдельными столбцами из результирующей выборки. Далее мы применили метод func из этого класса, который принимает имя SQL-функции и добавляет ее в select-запрос для выбранного столбца, в нашем случае departmentID.

Если нужно реализовать запрос вида:

select count(*) from department;

то можно написать следующим образом:

my $cnt = $department_rs->count;
say "department count = $cnt";

Рецепт 7. Посчитать максимальное значение departmentID

Запрос, который мы хотим получить:

select max(departmentID) from department;

Решение:

my $max = $department_rs->get_column('departmentid')->max;
say "department max = $max";

Как и в прошлом рецепте, здесь используется метод max из класса DBIx::Class::ResultSetColumn, который подсчитывает максимальное значение заданного столбца. Метод max является алиасом для func('max').

Рецепт 8. Выбор отдельных столбцов

Запрос, который мы хотим получить:

select name, employeeID from employee;

Решение:

my $employee_name_and_id_rs =
  $employee_rs->search(undef, {columns => [qw/name employeeid/]});
while (my $name_and_id = $employee_name_and_id_rs->next) {
    say $name_and_id->name . ' | ' . $name_and_id->employeeid;
}

Рецепт 9. Создание псевдонимов для имен столбцов и таблиц

Запрос, который мы хотим получить:

select name as employeeName from employee;

Решение:

my $employee_name_rs =
  $employee_rs->search(undef, {select => 'name', as => 'employeeName'});
while (my $name = $employee_name_rs->next) {
    say $name->get_column('employeeName');
}

Следует обратить внимание, что когда создается алиас, отличный от имени столбца таблицы, то не создается метод-акссесор для этого столбца. Для этого необходимо вызывать метод get_column с именем алиаса.

Если алиас имеет такое же имя, что и значение в select (т.е. есть для него есть метод-аксессор), то можно использовать этот аксессор для столбца.

my $employee_name_count_max_rs = $employee_rs->search(
    undef,
    {
        select => [
            'name',
            {count => 'employeeid'},
            {max   => {char_length => 'name'}, -as => 'longest_name'}
        ],
        as => [
            qw/
              name
              employee_count
              max_name_length
              /
        ],
        group_by => ['name'],
    }
);

say "name\t\t| count | max name length";
while (my $name_count_max = $employee_name_count_max_rs->next) {
    say $name_count_max->name . "\t| "
      . $name_count_max->get_column('employee_count') . "\t| "
      . $name_count_max->get_column('max_name_length');
}

Выбор строк с помощью WHERE, DISTINCT, GROUP BY, HAVING

Рецепт 10. Выбор строк с помощью where

Запрос, который мы хотим получить:

select employeeID, name from employee where job = 'Программист';

Решение:

my $programmer_rs = $employee_rs->search({job => 'Программист'});
while (my $programmer = $programmer_rs->next) {
    say $programmer->employeeid . ' | ' . $programmer->name;
}

Рецепт 11. Удаление повторений с помощью distinct

Запрос, который мы хотим получить:

select distinct job from employee;

Решение:

my $job_rs = $employee_rs->search(undef, {columns => 'job', distinct => 1});
while (my $job = $job_rs->next) {
    say $job->job;
}

На самом деле, DBIC на выходе генерирует выражение group by, т.е. вместо вышеприведенного запроса будет сгенерирован следующий:

SELECT me.job FROM employee me GROUP BY me.job:

Для подсчета количества значений столбца job без учета повторений, можно реализовать такой запрос:

select count(distinct job) from employee;

Реализация с помощью DBIC:

my $count = $job_rs->count;
say "count: $count";

В данном случае DBIC генерирует следующий запрос:

SELECT COUNT( * ) FROM (SELECT me.job FROM employee me GROUP BY me.job) me;

Рецепт 12. Выбор групп с помощью having

Запрос, который мы хотим получить:

select count(*), job from employee group by job having count(*) = 1;

Решение:

my $employee_having_rs = $employee_rs->search(
    undef,
    {
        select => [{count => '*', -as => 'count_employee'}, 'job'],
        as     => [
            qw/
              count_employee
              job
              /
        ],
        group_by => ['job'],
        having   => {count_employee => 1}
    }
);

say 'count_employee | job';
while (my $employee = $employee_having_rs->next) {
    say $employee->get_column('count_employee') . ' | ' . $employee->job;
}

Выбор строк по первичному ключу

Рецепт 13. Поиск по первичному ключу (PK) с помощью метода find()

Метод find производит поиск по первичным ключам, если их значения указаны в качестве списка значений данного PK в порядке, заданном в Result-классе таблицы. Также метод find может искать по уникальным ключам, заданным с помощью ссылки на хеш и установленном значении key в качестве второго аргумента метода find. Т.е. по сути find ищет либо по PK-ключам, указанном в виде списка значений этих ключей в том порядке, в котором они объявлены в Result-классе таблицы, либо по UK-ключам, при этом имя ключа указывается во втором аргументе метода find, а сами уникальные поля указываются в первом аргументе, например:

$rs->find({employeeid => 7513, clientid => 3}, {key => 'fk_employee'});

Если ключ не указывается, и используется поиск только по заданным столбцам без указания ключа или без использования списка PK-ключей, то результат непредсказуем и в следующих версиях может быть устаревшым. Что имеется в виду под непредсказуемостью: вы пишете вот такой код (PK(login), pass):

$rs->find({login => 'mylogin', pass => 'mypass'});
            ^                   ^
            |                   |
            PK            Обычное поле

на самом деле здесь будет поиск только по PK-ключу login, что может ввести в заблуждение.

Лучше здесь написать так:

# Получает список PK-ключей в порядке их определения
# в схеме БД, в данном случае у нас один элемент
$rs->find('mylogin');

С другой стороны, можно написать так (login, pass):

$rs->find({login => 'mylogin', pass=>'mypass'});
            ^                   ^
            |                   |
       Обычное поле        Обычное поле

здесь мы имеем 2 обычных поля (не PK и не UK), которые попадут в условие поиска. Результат тут непредсказуем — либо вернется одно значение и все ок, либо будет выдано предупреждение:

"DBIx::Class::Storage::DBI::select_single(): Query returned more thanone row.
SQL that returns multiple rows is DEPRECATED for ->find and ->single at",

которое сообщает, что возврат нескольких строк методами find и single является устаревшим и не рекомендуется к использованию. С другой стороны, если PK составной и в условии поиска find указано только одно поле, то такое предупреждение тоже может выдаться.

Рассмотрим поиск записи по первичному ключу (список PK из одного значения).

Запрос, который мы хотим получить:

select * from employee where employeeid = 6651;

Решение:

my $employee_find_pk = $employee_rs->find(6651);
say 'Employee name: ' . $employee_find_pk->name;

Рецепт 14. Поиск в таблице, содержащей составной PK-ключ.

Для PK ключей нужно указывать список значений ключей в том порядке, в котором они указаны в Result-классе заданной таблицы.

Запрос, который мы хотим получить:

select * from assignment where employeeid = 7513 and workdate = '2014-10-25'

Решение:

my $employee_find_by_many_pk = $assignment_rs->find(7513, '2014-10-25');
say $employee_find_by_many_pk->hours;

Рецепт 15. Поиск по уникальному ключу (UK)

Для того, чтобы произвести поиск по уникальному ключу, необходимо указать имя UK-ключа в параметре key метода find:

find({}, {key => 'unique_key'})

Более подробно о том, как работает метод find, смотрите в рецепте 13.

Пример запроса:

select * from assignment where employeeid = 7513 and workdate = '2014-10-25';

Решение:

my $assignment_find_clients =
  $assignment_rs->find({employeeid => 7513, workdate => '2014-10-25'},
    {key => 'fk_employee'});
say 'assignment find hours: ' . $assignment_find_clients->hours;
say 'assignment find client name: '
  . $assignment_find_clients->client->name;
say 'assignment find employee job: '
  . $assignment_find_clients->employee->job;

Обратите внимание, что метод find позволяет искать в связанных таблицах.

Использование custom-методов для Result- и ResultSet-классов

Рецепт 16. Вызов custom-метода из Result-класса

Вызываем custom-метод name_and_job из Result-класса Company::Schema::Result::Employee.

Custom-метод для Result-класса применяется для каждой строки запроса. Работает по аналогии с методами из класса DBIx::Class::Row.

Решение:

Для одной строки запроса (с помощью метода single):

my $employee_custom = $employee_rs->single({employeeid => 6651});
say $employee_custom->name_and_job;

Для каждой строки результирующей выборки (с помощью метода next):

while (my $empl_custom = $employee_rs->next) {
    say $empl_custom->name_and_job;
}

Рецепт 17. Вызов custom-метода из ResultSet-класса

Вызываем custom-метод department_client_employee из ResultSet-класса Company::Schema::ResulSet::Client.

Custom-метод для ResultSet-класса применяется для результирующего набора и возвращает нужный результирующий набор (ResultSet). В данном случае мы получили с помощью метода нужные связи + с помощью search добавили ограничение на поиск клиента с именем Telco Inc.

Решение:

my $department_client =
  $client_rs->department_client_employee->search({name => 'Telco Inc'});
while (my $dep = $department_client->next) {
    say 'Address: ' . $dep->address;
}

Использовние отношений между таблицами (relationships)

Рецепт 18. Использование отношение один-к-одному (has_one).

Задача:

Определить, какие клиенты относятся к служащему 7513.

Запрос:

select c.*
from client c
  join assignment a on a.clientid = c.clientid
where a.employeeid = 7513;

Решение:

my $client_for_employee =
  $client_rs->search({employeeid => 7513}, {join => 'assignment'});
while (my $client = $client_for_employee->next) {
    say $client->name;
}

Рецепт 19. Определить, какой служащий имеет клиента 2 (The Bank)

Запрос:

select a.*
from assignment a
  join client c on c.clientid = a.clientid
where a.clientid = 2;

Решение:

my $employee_have_client =
  $assignment_rs->search({'me.clientid' => 2}, {join => 'client'});
my @employee_ids = $employee_have_client->first->id;
say "employeeid: $employee_ids[0]; workdate: $employee_ids[1]";

Обратите внимание, что аксессор id возвращает массив PK-ключей в порядке их определения в схеме.

Также следует обратить внимание на то, что если вы указываете в условии поиска поле, которое имеется в обеих таблицах, т.е. внешний ключ, то необходимо явно указывать, к какой таблице данное поле принадлежит, например:

# можно здесь указать me.clientid
my $employee_have_client =
  $assignment_rs->search({'client.clientid' => 2}, {join => 'client'});

а вот так уже не будет работать

# к какой таблице относится clientid?
my $employee_have_client =
  $assignment_rs->search({clientid => 2}, {join => 'client'});

и выдаст ошибку

DBI Exception: DBD::mysql::st execute failed: Column 'clientid' in where clause is ambiguous

т.е. это ошибка от MySQL, которая не поймет, к какой таблице относится данный clientid.

Рецепт 20. Получить имена служащих, еще не получавших внешних заданий, т.е. служащих, коды которых (employeeid) отсутствуют в таблице assignment

Запрос:

select e.*
from employee e
  left join assignment a on a.employeeid = e.employeeid
where clientid is null;

Решение:

my $employee_assignments =
  $employee_rs->search({clientid => undef}, {join => 'assignments'});
while (my $employee = $employee_assignments->next) {
    say $employee->id . ' | ' . $employee->name;
}

Рецепт 21. Пример отношений много-ко-многим (many_to_many)

Задача:

Получить все навыки для служащего 7513 (Нора Эдвардс).

Запрос:

# Сначала получаем служащего с id = 7513
select * from employee where employeeid = 7513;

# Затем если служащий найден, то получаем для него навыки
select * from employeeskills where employeeid = 7513;

Решение:

my $employee_7513_skills_rs = $employee_rs->find(7513)->employee_skills;
while (my $employee = $employee_7513_skills_rs->next) {
    say $employee->skill;
}

Подзапросы

Рецепт 22. Найти самое длинное имя служащего (подзапрос, возвращающий одно значение, + функция слева от оператора сравнения)

Запрос, который мы хотим получить:

select name from employee where char_length(name) = (select max(char_length(name)) from employee);

Если не знать, как правильно использвать DBIC, то можно написать такой некрасивый код:

my $employee_longest_name = $employee_rs->search(
    {
        'char_length(name)' => {
            '=' => $employee_rs->search(
                undef,
                {
                    select => [
                        {
                            max => {char_length => 'name'},
                            -as => 'longest_name'
                        }
                    ]
                }
            )->get_column('longest_name')->as_query
        }
    }
);

Но разобравшись больше с DBIC, можно найти более элегантное решение:

my $employee_longest_name = $employee_rs->search(
    {
        'CHAR_LENGTH(name)' => {
            '=' => $employee_rs->get_column('name')->func_rs('char_length')
              ->get_column('name')->max_rs->as_query,
        }
    }
);
say '--- longest name: ' . $employee_longest_name->single->name;

Здесь используются методы:

  • func_rs, который принимает имя SQL-функции, и добавляет ее в select-запрос для выбранного столбца;
  • max_rs, который является алиасом для func_rs('max').

Оба метода возвращают ResultSet-объект.

Рецепт 23. Определить, кто из программистов работал над выполнением внешних заданий (from (select…))

Запрос, который мы хотим получить:

select programmer.name
from (select employeeID, name from employee where job='Программист') as programmer, assignment
where programmer.employeeID = assignment.employeeID;

Чтобы все это дело заработало, нужно переделать запрос на вот такой:

SELECT employee.name
FROM (
  SELECT employee.name
  FROM assignment me
    JOIN employee employee ON employee.employeeid = me.employeeid
    WHERE ( job =  'Программист' )
) employee;

Решение:

my $assignment_prog_rs = $assignment_rs->search_related(
    'employee',
    {job     => 'Программист'},
    {columns => ['name']}
)->as_subselect_rs->search(undef, {columns => ['name']});

while (my $programmer = $assignment_prog_rs->next) {
    say 'programmer name: ' . $programmer->name;
}

Т.е. суть в том, что DBIC позволяет создать только такие встроенные представления (под встроенными представлениями понимается вложенный select в оператор from, т.к. по сути он (select) возвращает набор значений), в которых будут только эти самые встроенные представления. В этот же from не получится ничего добавить стандартными документированными способами. По крайней мере документации по атрибуту from я не нашел, хотя он (атрубут) используется внутри самого DBIC.

Хотя как по мне, так проку от такого запроса нет, т.к. можно просто опуситить внешний select, оставить только запрос внутри from и все будет работать так же. Если кто знает как реализовать на DBIC запрос вида:

select programmer.name
from (
        select employeeID, name
        from employee
        where job='Программист'
     ) as programmer,
     assignment
where programmer.employeeID = assignment.employeeID;

без преобразования для использования с join-ом и с двумя таблицами, просьба указать в комментариях.

Рецепт 24. Определить, кто из служащих потратил больше всех времени на выполнение задания (join + подазпрос, возвращающий одно значение)

Запрос, который мы хотим получить:

select e.employeeID, e.name
from employee e, assignment a
where e.employeeID = a.employeeID
and a.hours = (select max(hours) from assignment);

Данный запрос лучше переделать на следующий:

select e.employeeID, e.name
from employee e
  join assignment a on e.employeeID = a.employeeID
where a.hours = (select max(hours) from assignment);

Решение:

my $employee_max_busy_rs = $employee_rs->search(
    {
        hours => {
            '=' => $assignment_rs->get_column('hours')->max_rs->as_query,
        }
    },
    {join => 'assignments'}
);

while (my $employee = $employee_max_busy_rs->next) {
    say $employee->id . ' | ' . $employee->name;
}

Рецепт 25. Определить служащих, которые не имели внешних заданий (NOT IN)

Запрос, который мы хотим получить:

select name from employee
where employeeID not in (select employeeID from assignment);

Решение:

my $employee_not_have_assignment_rs = $employee_rs->search(
    {
        employeeid => {
            -not_in => $assignment_rs->get_column('employeeid')->as_query,
        }
    }
);

while (my $employee = $employee_not_have_assignment_rs->next) {
    say $employee->name;
}

Рецепт 26. Определить служащих, которые не входят в заданное множество (NOT IN)

Запрос, который мы хотим получить:

select name from employee where employeeID not in (6651, 1234);

Решение:

my $employee_not_in_set = $employee_rs->search(
    {
        employeeid => {
            -not_in => [qw/6651 1234/],
        }
    }
);

while (my $employee = $employee_not_in_set->next) {
    say $employee->name;
}

Рецепт 27. Получить список служащих, которые никогда не работали над внешними заданиями (NOT EXISTS)

Запрос, который мы хотим получить:

select e.name, e.employeeID
from employee e
where not exists (select * from assignment where employeeID = e.employeeID);

Решение:

my $employee_not_work_with_assignments = $employee_rs->search(
    {
        -not_exists => $assignment_rs->search(
            {
                'a.employeeid' => {-ident => 'e.employeeid'}
            },
            {
                alias   => 'a',
                columns => [qw/e.employeeid e.name/]
            }
        )->as_query,
    },
    {
        alias   => 'e',
        columns => [qw/e.employeeid e.name/],
    }
);

while (my $employee = $employee_not_work_with_assignments->next) {
    say $employee->name;
}

Вот что генерит DBIC:

SELECT e.employeeid, e.name
FROM employee e
WHERE ( (NOT EXISTS (SELECT e.employeeid, e.name FROM assignment a WHERE ( a.employeeid = e.employeeid ))) ):

Следует отметить, что нужно аккуратно использовать алиасы, т.к. по умолчанию все привыкли работать с me, поэтому для основного запроса можно не делать alias => 'e', а везде писать me.column_name. Здесь алиасы приведены только для примера.

Как можно видеть, DBIC позволяет писать как join-запросы, так и подзапросы. На мой взгяд, сила DBIC — в связях с помощью join, используя которые, можно не писать ключи в условиях where с помощью -ident (первый аргумент search(), например {id1 => {-ident => me.id2}}), а просто написать join => 'joining_table_name'.

Ограничение результатов поиска с помощью LIMIT

Рецепт 28. Ограничение LIMIT n;

Запрос, который мы хотим получить:

select * from employeeSkills limit 5;

Решение:

my $skills_limit = $employeeskill_rs->search(undef, {rows => 5});
while (my $employee = $skills_limit->next) {
    say $employee->skill;
}

Рецепт 29. Ограничение LIMIT n, m;

Запрос, который мы хотим получить:

select * from employeeSkills limit 5, 3;

Решение:

# здесь получается ($first, $last) включительно,
# т.е. у нас получается 3 записи: 5, 6, 7
my $skills_limit_with_slice = $employeeskill_rs->slice(5, 7);
while (my $employee = $skills_limit_with_slice->next) {
    say $employee->skill;
}

Рецепт 30. Разделение на страницы (pagination).

Формула расчета:

limit ($page-1)*$rows, $rows

Запрос:

select * from employeeSkills limit 4, 2;

Решение:

my $skills_limit_with_page =
  $employeeskill_rs->search(undef, {page => 3, rows => 2});
while (my $employee = $skills_limit_with_page->next) {
    say $employee->skill;
}

CRUD для строк

Здесь будут рассмотрены CRUD-операции над Result-объектами, которые возвращаются ResultSet-методами: create, find, next и all, т.е. по сути операции над отдельными строками.

Рецепт 31. insert

Запрос, который мы хотим получить:

insert into employee (departmentid, employeeid, job, name) values ('145', '9739', 'Главный программист', 'Билл Гейтс');

Решение:

my $new_row_for_employee = $employee_rs->new(
    {
        employeeid   => 9739,
        name         => 'Билл Гейтс',
        job          => 'Главный программист',
        departmentid => 145
    }
);

my $insert_new_row_to_employee = $new_row_for_employee->insert;

Рецепт 32. delete

Запрос:

# Ищем служащего с id = 9789
select * from employee where employeeid = 9739;

# Если нашли, то удаляем
delete from employee where employeeid = 9739;

Решение:

my $delete_employee_row = $employee_rs->find(9739)->delete;

Рецепт 33. update

Запрос:

begin work
update client set name = 'Telco Inc' where clientid = 1;
commit

Решение:

my $update_client_row = $client_rs->find(1)->update({name => 'Telco Inc'});

Рецепт 34. update_or_insert | insert_or_update

Обновляет объект, если он есть в базе (на основе in_storage-метода), иначе заносит его в базу.

Запросы:

--- update_or_insert: update
select * from department where departmentid = 130;
update department
set name = 'Отдел управления и маркетинга'
where departmentid = 130;

--- update_or_insert: update
select * from department where me.departmentid = 130;
update department
set name = 'Отдел маркетинга'
where departmentid = 130;

--- update_or_insert: insert
select * from department where me.departmentid = 155;
insert into department (departmentid, name)
values ('155', 'Отдел информационных технологий');

Решение:

say '--- update_or_insert: update';
my $new_row_for_department =
  $department_rs->find_or_new({departmentid => 130});
$new_row_for_department->name('Отдел управления и маркетинга');
$new_row_for_department->update_or_insert;    # update

say '--- update_or_insert: update';
$new_row_for_department =
  $department_rs->find_or_new({departmentid => 130});
$new_row_for_department->name('Отдел маркетинга');
$new_row_for_department->update_or_insert;    # update

say '--- update_or_insert: insert';
$new_row_for_department =
  $department_rs->find_or_new({departmentid => 155});
$new_row_for_department->name('Отдел информационных технологий');
$new_row_for_department->update_or_insert;    # insert

Метод insert_or_update является алиасом для update_or_insert.

CRUD

Рецепт 35. create

Запросы:

insert into client ( address, contactnumber, contactperson, name)
values ('ул. Кандаурова, 25/3', '123456789777', 'Наталья Ветлицкая', 'Азовский рынок');

Решение:

my $create_client = $client_rs->create(
    {
        name          => 'Горбушка',
        address       => 'ул. Барклая, 8',
        contactperson => 'Наталья Ветлицкая',
        contactnumber => '123456789777'
    }
);

Рецепт 36. delete

Запрос:

delete from client where name = 'Горбушка';

Решение:

my $delete_client =
  $client_rs->search({name => 'Горбушка'})->delete;

Рецепт 37. populate

Метод вставляет набор записей в рамках одной транзакции.

Так будет работать в непустом (non-void) (скалярном или списковом) контексте:

BEGIN WORK
INSERT INTO department ( departmentid, name) VALUES ('132', 'Инженерный отдел');
INSERT INTO department ( departmentid, name) VALUES ('134', 'Отдел легкой промышленности');
INSERT INTO department ( departmentid, name) VALUES ('135', 'Отдел тяжелой промышленности');
COMMIT

т.е. это обычная обертка над методом create с добавлением транзакций.

Решение:

my $populate_client = $department_rs->populate(
    [
        [qw/departmentid name/],
        [132, 'Инженерный отдел'],
        [134, 'Отдел легкой промышленности'],
        [135, 'Отдел тяжелой промышленности'],
    ]
);

А так будет работать в пустом (void) контексте:

BEGIN WORK
INSERT INTO department ( departmentid, name) VALUES ( ?, ? ): '__BULK_INSERT__'
COMMIT

Здесь используется метод execute_for_fetch из DBI, что позволяет ускорить вставку данных за счет непосредственной заливки данных в БД. Кроме того, есть и недостаток этого подхода — если используются генерируемые с помощью DBIC столбцы, то данные для этих столбцов не будут генерироваться. Если это необходимо, то лучше использовать вызов метода в скалярном или в списковом контекстах.

Решение:

$department_rs->populate(
    [
        [qw/departmentid name/],
        [137, 'Маркетинговый отдел'],
        [139, 'Отдел электроники'],
        [141, 'Отдел бытовой техники'],
    ]
);

Рецепт 38. delete_all

Вызывает поиск нужных значений и удаление в рамках одной транзакции:

BEGIN WORK
SELECT me.departmentid, me.name FROM department me WHERE departmentid IN ( '132', '134', '135');
DELETE FROM department WHERE departmentid = '132';
DELETE FROM department WHERE departmentid = '134';
DELETE FROM department WHERE departmentid = '135';
COMMIT

Решение:

my $delete_all_department = $department_rs->search(
    {
        departmentid => {-in => [qw/132 134 135 137 139 141 150 155/]}
    }
)->delete_all;

Рецепт 39. update

Обновляет данные для всего ResultSet-а

Решение:

my $update_employee =
  $employee_rs->search({departmentid => 128})->update({vacation => 'Yes'});

DBIC выдает следующий код:

UPDATE employee SET vacation = 'Yes' WHERE ( departmentid = '128');

CRUD с поиском

Рецепт 40. find_or_new

Ищет существующую запись из результирующего набора с помощью find. Если запись не существует, создается и возвращается новый объект. Объект не будет сохранен в БД, если не будет вызван метод insert из DBIx::Class::Row. Пример использования см. в рецепте update_or_insert.

Рецепт 41. find_or_create

Пытается найти запись по primary key или unique. Если запись не существует, данные заносятся в БД. Следует обратить внимание на то, что можно и нужно в качестве аргумента указать все поля, нужные для занесения, а не только PK или UK. Т.е. find работает как обычно — в получаемом хеше обрабатывает только PK либо UK (если во втором аргументе указан key), если запись не найдена, то заданный в качестве аргумента хеш записывается в БД. Также следует учесть, что в данном случае не используются транзакции, поэтому нужно самим позаботится о создании транзации во избежание ситуации гонки, т.е. когда данные в БД обновились после find, но перед create.

Решение:

my $department_create = $department_rs->find_or_create(
    {
        departmentid => 153,
        name =>
          'Отдел информационных технологий'
    }
);

Рецепт 42. update_or_new

Решение:

my $department_it = $department_rs->update_or_new(
    {
        departmentid => 150,
        name         => 'Отдел IT'
    }
);

# если запись не найдена, то заносим ее в БД
$department_it->insert unless $department_it->in_storage;

По аналогии с find_or_new, но если строка найдена, то она немедленно обновляется через $found_row->update(\%col_data).

Рецепт 43. update_or_create

Решение:

my $department_update_it = $department_rs->update_or_create(
    {
        departmentid => 153,
        name => 'Отдел программирования станков с ЧПУ'
    }
);

По аналогии с find_or_create, но если строка найдена, то она немедленно обновляется с помощью $found_row->update(\%col_data).

Заключение

В заключение хочется обратить внимание на достоинства и недостатки DBIx::Class.

Достоинства:

  • активная разработка;
  • расширяемость;
  • большое количество расширений, хелперов.

Недостатки:

  • нет возможности использовать DISTINCT, т.к. DBIC генерирует GROUP BY;
  • не документирована возможность создания сложных подзапросов с несколькими вложенными select в оператор from с добавлением других таблиц.

Вячеслав Коваль


Perl 6 XXI века | Содержание | Обзор CPAN за ноябрь 2014 г.
Нас уже 1393. Больше подписчиков — лучше выпуски!

Комментарии к статье