Aion-Query
4 results

 view release on metacpan or  search on metacpan

README.md  view on Meta::CPAN 

query_scalar "SELECT name FROM author WHERE id=2" # => Pushkin A.
```

## make_query_for_order ($order, $next)

Создает условие запроса страницы не по смещению, а по **пагинации курсора**.

Для этого он получает `$order` SQL-запроса и `$next` — ссылку на следующую страницу.

```perl
my ($select, $where, $order_sel) = make_query_for_order "name DESC, id ASC", undef;

$select     # => name || ',' || id
$where      # -> 1
$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};
$select     # => name || ',' || id
$where      # => (name < 'Pushkin A.'\nOR name = 'Pushkin A.' AND id >= '2')
$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)

lib/Aion/Query.md  view on Meta::CPAN 

query_scalar "SELECT name FROM author WHERE id=2" # => Pushkin A.
```

## make_query_for_order ($order, $next)

Создает условие запроса страницы не по смещению, а по **пагинации курсора**.

Для этого он получает `$order` SQL-запроса и `$next` — ссылку на следующую страницу.

```perl
my ($select, $where, $order_sel) = make_query_for_order "name DESC, id ASC", undef;

$select     # => name || ',' || id
$where      # -> 1
$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};
$select     # => name || ',' || id
$where      # => (name < 'Pushkin A.'\nOR name = 'Pushkin A.' AND id >= '2')
$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)

lib/Aion/Query.pm  view on Meta::CPAN 

	my ($dsn, $user, $password, $conn) = @_;
	my $base = DBI->connect($dsn, $user, $password, {
		RaiseError => 1,
		PrintError => 0,
		$dsn =~ /^DBI:mysql/i ? (mysql_enable_utf8 => 1): (),
	}) or die "Connect to db failed";

	$base->do($_) for @$conn;
	return $base unless wantarray;
	my ($base_connection_id) = $dsn =~ /^DBI:(mysql|mariadb)/i
		? $base->selectrow_array("SELECT connection_id()")
		: -1;
	return $base, $base_connection_id;
}

# Проверка коннекта и переконнект
sub connect_respavn {
	my ($base) = @_;
	$base->disconnect, undef $base if $base and !$base->ping;
	($_[0], $_[1]) = base_connect(default_connect_options) if !$base;
	return;

lib/Aion/Query.pm  view on Meta::CPAN 

	$query
}

# Выполняет sql-запрос
sub query_do($;$) {
	my ($query, $columns) = @_;
	sql_debug query => $query;
	connect_respavn($base, $base_connection_id);

	my $res = eval {
		if($query =~ /^\s*(select|show|desc(ribe)?)\b/in) {

			my $r = @_>1? do {
				my $sth = $base->prepare($query);
				$sth->execute;
				$_[1] = [@{$sth->{NAME}}];
				my $res = $sth->fetchall_arrayref({});
				$sth->finish;
				$res
			}: $base->selectall_arrayref($query, { Slice => {} });

			if(defined $r and BQ) {
				for my $row (@$r) {
					for my $k (keys %$row) {
						$row->{$k} =~ s/°([^\x7F]{1,7})\x7F/chr from_radix($1, 254)/ge if utf8::is_utf8($row->{$k});
					}
				}
			}
			$r
		} else {

lib/Aion/Query.pm  view on Meta::CPAN 

#
# ("concat(size,',',likes)", 1) = make_query_for_order "size desc, likes", ""
#
sub make_query_for_order(@) {
	my ($order, $next) = @_;

	my @orders = split /\s*,\s*/, $order;
	my @order_direct;
	my @order_sel = map { my $x=$_; push @order_direct, $x=~s/\s+(asc|desc)\s*$//ie ? lc $1: "asc"; $x } @orders;

	my $select = @order_sel==1? $order_sel[0]: 
		_check_drv($base, "mysql|mariadb")? 
			join("", "concat(", join(",',',", @order_sel), ")"):
			join " || ',' || ", @order_sel
	;

	return $select, 1 if $next eq "";

	my @next = split /,/, $next;
	$next[$#orders] //= "";
	@next = map quote($_), @next;
	my @op = map { /^a/ ? ">": "<" } @order_direct;

	# id -> id >= next[0]
	# id, update -> id > next[0] OR id = next[0] and
	my @whr;
	for(my $i=0; $i<@orders; $i++) {

lib/Aion/Query.pm  view on Meta::CPAN 

			} elsif($j != $#orders) {
				push @opr, "$order_sel[$j] $op[$j] $next[$j]";
			} else {
				push @opr, "$order_sel[$j] $op[$j]= $next[$j]";
			}
		}
		push @whr, join " AND ", @opr;
	}
	my $where = join "\nOR ", map "$_", @whr;

	return $select, "($where)", \@order_sel;
}

# Устанавливает или возвращает ключ из таблицы settings
sub settings($;$) {
	my ($id, $value) = @_;
	if(@_ == 1) {
		my $v = query_scalar("SELECT value FROM settings WHERE id=:id", id => $id);
		return defined($v)? Aion::Format::Json::from_json($v): $v;
	}

lib/Aion/Query.pm  view on Meta::CPAN 

Returns the first value. The query must return one row, otherwise it throws an exception.

	query_scalar "SELECT name FROM author WHERE id=2" # => Pushkin A.

=head2 make_query_for_order ($order, $next)

Creates a page request condition not by offset, but by B<cursor pagination>.

To do this, it receives C<$order> of the SQL query and C<$next> - a link to the next page.

	my ($select, $where, $order_sel) = make_query_for_order "name DESC, id ASC", undef;
	
	$select     # => name || ',' || id
	$where      # -> 1
	$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};
	$select     # => name || ',' || id
	$where      # => (name < 'Pushkin A.'\nOR name = 'Pushkin A.' AND id >= '2')
	$order_sel  # --> [qw/name id/]

See also:

=over

=item 1. Article [Paging pages on social networks
sch(https://habr.com/ru/articles/674714/).

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

::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)
#



( run in 1.428 second using v1.01-cache-2.11-cpan-49f99fa48dc )