view release on metacpan or  search on metacpan

t/aion/query.t  view on Meta::CPAN

::is_deeply scalar do {\%author}, scalar do {{"Pushkin A.S." => 1, "Pushkin A." => 2, "Alice" => 3}}, '\%author  # --> {"Pushkin A.S." => 1, "Pushkin A." => 2, "Alice" => 3}';

# 
# Если нужен хеш вида идентификатор – строка:
# 

my %author = query_slice id => {}, "SELECT id, name FROM author";

my $rows = {
    1 => {name => "Pushkin A.S.", id => 1},
    2 => {name => "Pushkin A.",   id => 2},
    3 => {name => "Alice",        id => 3},
};

::is_deeply scalar do {\%author}, scalar do {$rows}, '\%author  # --> $rows';

# 
# Если одному идентификатору соответствует несколько строк, то логично собрать их в массивы:
# 

query "CREATE TABLE book (
	id SERIAL PRIMARY KEY,
    author_id INT NOT NULL REFERENCES author(id),
    title TEXT NOT NULL
)";

stores book => [
    {author_id => 1, title => "Mir"},
    {author_id => 1, title => "Kiss in night"},
    {author_id => 3, title => "Mips as cpu"},
];

my %author = query_slice author_id => ["title"], "SELECT author_id, title FROM book ORDER BY title";

my $rows = {
    1 => ["Kiss in night", "Mir"],
    3 => ["Mips as cpu"],
};

::is_deeply scalar do {\%author}, scalar do {$rows}, '\%author  # --> $rows';

# 
# Ну и строки со всеми полями:
# 

my %author = query_slice author_id => [], "SELECT author_id, title FROM book ORDER BY title";

my $rows = {
    1 => [
        {title => "Kiss in night", author_id => 1},
        {title => "Mir",           author_id => 1},
    ],
    3 => [
        {title => "Mips as cpu",   author_id => 3}
    ],
};

::is_deeply scalar do {\%author}, scalar do {$rows}, '\%author  # --> $rows';

# 
# ## query_attach ($rows, $attach, $query, %kw)
# 
# Подсоединяет в результат запроса результат другого запроса.
# 
# `$attach` содержит три ключа через двоеточие: ключ для присоединяемых данных, столбец из `$rows` и столбец из `$query`. По столбцам происходит объеÐ...
# 
# Возвращает функция массив с результатом запроса (`$query`), в который можно приаттачить ещё что-то.
# 
done_testing; }; subtest 'query_attach ($rows, $attach, $query, %kw)' => sub { 
my $authors = query "SELECT id, name FROM author";

my $res = [
    {name => "Pushkin A.S.", id => 1},
    {name => "Pushkin A.",   id => 2},
    {name => "Alice",        id => 3},
];

::is_deeply scalar do {$authors}, scalar do {$res}, '$authors # --> $res';

my @books = query_attach $authors => "books:id:author_id" => "SELECT author_id, title FROM book ORDER BY title";

my $attaches = [
    {name => "Pushkin A.S.", id => 1, books => [
        {title => "Kiss in night", author_id => 1},
        {title => "Mir",           author_id => 1},
    ]},
    {name => "Pushkin A.",   id => 2, books => []},
    {name => "Alice",        id => 3, books => [
        {title => "Mips as cpu", author_id => 3},
    ]},
];

::is_deeply scalar do {$authors}, scalar do {$attaches}, '$authors # --> $attaches';

my $books = [
    {title => "Kiss in night", author_id => 1},
    {title => "Mips as cpu",   author_id => 3},
    {title => "Mir",           author_id => 1},
];

::is_deeply scalar do {\@books}, scalar do {$books}, '\@books  # --> $books';

# 
# ## query_col ($query, %params)
# 
# Возвращает один столбец.
# 
done_testing; }; subtest 'query_col ($query, %params)' => sub { 
::is_deeply scalar do {query_col "SELECT name FROM author ORDER BY name"}, scalar do {["Alice", "Pushkin A.", "Pushkin A.S."]}, 'query_col "SELECT name FROM author ORDER BY name" # --> ["Alice", "Pushkin A.", "Pushkin A.S."]';

::like scalar do {eval {query_col "SELECT id, name FROM author"}; $@}, qr!Only one column is acceptable\!!, 'eval {query_col "SELECT id, name FROM author"}; $@  # ~> Only one column is acceptable!';

# 
# ## query_row ($query, %params)
# 
# Возвращает одну строку.
# 
done_testing; }; subtest 'query_row ($query, %params)' => sub { 
::is_deeply scalar do {query_row "SELECT name FROM author WHERE id=2"}, scalar do {{name => "Pushkin A."}}, 'query_row "SELECT name FROM author WHERE id=2" # --> {name => "Pushkin A."}';

my ($id, $name) = query_row "SELECT id, name FROM author WHERE id=2";
::is scalar do {$id}, scalar do{2}, '$id    # -> 2';
::is scalar do {$name}, "Pushkin A.", '$name  # => Pushkin A.';

::like scalar do {eval { query_row "SELECT id, name FROM author" }; $@}, qr!A few lines\!!, 'eval { query_row "SELECT id, name FROM author" }; $@ # ~> A few lines!';

# 
# ## query_row_ref ($query, %params)
# 
# Как `query_row`, но всегда возвращает скаляр.
# 
done_testing; }; subtest 'query_row_ref ($query, %params)' => sub { 
my @x = query_row_ref "SELECT name FROM author WHERE id=2";
::is_deeply scalar do {\@x}, scalar do {[{name => "Pushkin A."}]}, '\@x # --> [{name => "Pushkin A."}]';

::like scalar do {eval {query_row_ref "SELECT name FROM author"}; $@}, qr!A few lines\!!, 'eval {query_row_ref "SELECT name FROM author"}; $@  # ~> A few lines!';

# 
# ## query_scalar ($query, %params)
# 
# Возвращает первое значение. Запрос должен возвращать одну строку, иначе – выбрасывает исключение.
# 
done_testing; }; subtest 'query_scalar ($query, %params)' => sub { 
::is scalar do {query_scalar "SELECT name FROM author WHERE id=2"}, "Pushkin A.", 'query_scalar "SELECT name FROM author WHERE id=2" # => Pushkin A.';

# 
# ## make_query_for_order ($order, $next)
# 
# Создает условие запроса страницы не по смещению, а по **пагинации курсора**.
# 
# Для этого он получает `$order` SQL-запроса и `$next` — ссылку на следующую страницу.
# 
done_testing; }; subtest 'make_query_for_order ($order, $next)' => sub {