view release on metacpan or  search on metacpan

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

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 { 
my ($select, $where, $order_sel) = make_query_for_order "name DESC, id ASC", undef;

::is scalar do {$select}, "name || ',' || id", '$select     # => name || \',\' || id';
::is scalar do {$where}, scalar do{1}, '$where      # -> 1';
::is scalar do {$order_sel}, scalar do{undef}, '$order_sel  # -> undef';

my @rows = query "SELECT $select as next FROM author WHERE $where LIMIT 2";

my $last = pop @rows;

($select, $where, $order_sel) = make_query_for_order "name DESC, id ASC", $last->{next};
::is scalar do {$select}, "name || ',' || id", '$select     # => name || \',\' || id';
::is scalar do {$where}, "(name < 'Pushkin A.'\nOR name = 'Pushkin A.' AND id >= '2')", '$where      # => (name < \'Pushkin A.\'\nOR name = \'Pushkin A.\' AND id >= \'2\')';
::is_deeply scalar do {$order_sel}, scalar do {[qw/name id/]}, '$order_sel  # --> [qw/name id/]';

# 
# Смотрите также:
# 
# 1. Article [Paging pages on social networks
# ](https://habr.com/ru/articles/674714/).
# 2. [SQL::SimpleOps->SelectCursor](https://metacpan.org/dist/SQL-SimpleOps/view/lib/SQL/SimpleOps.pod#SelectCursor)
# 
# ## settings ($id, $value)
# 
# Устанавливает или возвращает ключ из таблицы `settings`.
# 
done_testing; }; subtest 'settings ($id, $value)' => sub { 
query "CREATE TABLE settings(
    id TEXT PRIMARY KEY,
	value TEXT NOT NULL
)";

::is scalar do {settings "x1"}, scalar do{undef}, 'settings "x1"       # -> undef';
::is scalar do {settings "x1", 10}, scalar do{1}, 'settings "x1", 10   # -> 1';
::is scalar do {settings "x1"}, scalar do{10}, 'settings "x1"       # -> 10';

# 
# ## load_by_id ($tab, $pk, $fields, @options)
# 
# Возвращает запись по ее идентификатору.
# 
done_testing; }; subtest 'load_by_id ($tab, $pk, $fields, @options)' => sub { 
::is_deeply scalar do {load_by_id author => 2}, scalar do {{id=>2, name=>"Pushkin A."}}, 'load_by_id author => 2  # --> {id=>2, name=>"Pushkin A."}';
::is_deeply scalar do {load_by_id author => 2, "name as n"}, scalar do {{n=>"Pushkin A."}}, 'load_by_id author => 2, "name as n"  # --> {n=>"Pushkin A."}';
::is_deeply scalar do {load_by_id author => 2, "id+:x as n", x => 10}, scalar do {{n=>12}}, 'load_by_id author => 2, "id+:x as n", x => 10  # --> {n=>12}';

# 
# ## insert ($tab, %x)
# 
# Добавляет запись и возвращает ее идентификатор.
# 
done_testing; }; subtest 'insert ($tab, %x)' => sub { 
::is scalar do {insert 'author', name => 'Masha'}, scalar do{4}, 'insert \'author\', name => \'Masha\'  # -> 4';

# 
# ## update ($tab, $id, %params)
# 
# Обновляет запись по её идентификатору и возвращает этот идентификатор.
# 
done_testing; }; subtest 'update ($tab, $id, %params)' => sub { 
::is scalar do {update author => 3, name => 'Sasha'}, scalar do{3}, 'update author => 3, name => \'Sasha\'  # -> 3';
::like scalar do {eval { update author => 5, name => 'Sasha' }; $@}, qr!Row author.id=5 is not\!!, 'eval { update author => 5, name => \'Sasha\' }; $@  # ~> Row author.id=5 is not!';

# 
# ## remove ($tab, $id)
# 
# Удалить строку из таблицы по её идентификатору и вернуть этот идентификатор.
# 
done_testing; }; subtest 'remove ($tab, $id)' => sub { 
::is scalar do {remove "author", 4}, scalar do{4}, 'remove "author", 4  # -> 4';
::like scalar do {eval { remove author => 4 }; $@}, qr!Row author.id=4 does not exist\!!, 'eval { remove author => 4 }; $@  # ~> Row author.id=4 does not exist!';