<html><head><meta name="color-scheme" content="light dark"></head><body><pre style="word-wrap: break-word; white-space: pre-wrap;">[![Actions Status](https://github.com/darviarush/perl-aion-query/actions/workflows/test.yml/badge.svg)](https://github.com/darviarush/perl-aion-query/actions)
# NAME

Aion::Query - functional interface for accessing database mysql and mariadb

# VERSION

0.0.1

# SYNOPSIS

File .config.pm:
```perl
package config;

config_module Aion::Query =&gt; {
    DRV  =&gt; "SQLite",
    BASE =&gt; "test-base.sqlite",
    BQ =&gt; 0,
};

1;
```

```perl
use Aion::Query;

query "CREATE TABLE author (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE
)";

insert "author", name =&gt; "Pushkin A.S." # -&gt; 1

touch "author", name =&gt; "Pushkin A."    # -&gt; 2
touch "author", name =&gt; "Pushkin A.S."  # -&gt; 1
touch "author", name =&gt; "Pushkin A."    # -&gt; 2

query_scalar "SELECT count(*) FROM author"  # -&gt; 2

my @rows = query "SELECT *
FROM author
WHERE 1
    if_name&gt;&gt; AND name like :name
",
    if_name =&gt; Aion::Query::BQ == 0,
    name =&gt; "P%",
;

\@rows # --&gt; [{id =&gt; 1, name =&gt; "Pushkin A.S."}, {id =&gt; 2, name =&gt; "Pushkin A."}]

$Aion::Query::DEBUG[1]  # =&gt; query: INSERT INTO author (name) VALUES ('Pushkin A.S.')
```

# DESCRIPTION

When constructing queries, many disparate conditions are used, usually separated by different methods.

`Aion::Query` uses a different approach, which allows you to construct an SQL query in a query using a simple template engine.

The second problem is placing unicode characters into single-byte encodings, which reduces the size of the database. So far it has been solved only for the **cp1251** encoding. It is controlled by the parameter `BQ = 1`.

# SUBROUTINES

## query ($query, %params)

It provide SQL (DCL, DDL, DQL and DML) queries to DBMS with quoting params and .

```perl
query "SELECT * FROM author WHERE name=:name", name =&gt; 'Pushkin A.S.' # --&gt; [{id=&gt;1, name=&gt;"Pushkin A.S."}]
```

## LAST_INSERT_ID ()

Returns last insert id.

```perl
query "INSERT INTO author (name) VALUES (:name)", name =&gt; "Alice"  # -&gt; 1
#LAST_INSERT_ID  # -&gt; 3
```

## quote ($scalar)

Quoted scalar for SQL-query.

```perl
quote undef     # =&gt; NULL
quote "abc"     # =&gt; 'abc'
quote 123       # =&gt; 123
quote "123"     # =&gt; '123'
quote(0+"123")  # =&gt; 123
quote(123 . "") # =&gt; '123'
quote 123.0       # =&gt; 123.0
quote(0.0+"126")  # =&gt; 126
quote("127"+0.0)  # =&gt; 127
quote("128"-0.0)  # =&gt; 128
quote("129"+1.e-100)  # =&gt; 129.0

# use for insert formula: SELECT :x as summ 竍� x =&gt; \"xyz + 123"
quote \"without quote"  # =&gt; without quote

# use in: WHERE id in (:x)
quote [1,2,"5"] # =&gt; 1, 2, '5'

# use in: INSERT INTO author VALUES :x
quote [[1, 2], [3, "4"]]  # =&gt; (1, 2), (3, '4')

# use in multiupdate: UPDATE author SET name=CASE id :x ELSE null END
quote \[2=&gt;'Pushkin A.', 1=&gt;'Pushkin A.S.']  # =&gt; WHEN 2 THEN 'Pushkin A.' WHEN 1 THEN 'Pushkin A.S.'

# use for UPDATE SET :x or INSERT SET :x
quote {name =&gt; 'A.S.', id =&gt; 12}   # =&gt; id = 12, name = 'A.S.'

[map quote, -6, "-6", 1.5, "1.5"] # --&gt; [-6, "'-6'", 1.5, "'1.5'"]

```

## query_prepare ($query, %param)

Replace the parameters in `$query`. Parameters quotes by the `quote`.

```perl
query_prepare "INSERT author SET name = :name", name =&gt; "Alice"  # =&gt; INSERT author SET name = 'Alice'
```

## query_do ($query)

Execution query and returns it result.

```perl
query_do "SELECT count(*) as n FROM author"  # --&gt; [{n=&gt;3}]
query_do "SELECT id FROM author WHERE id=2"  # --&gt; [{id=&gt;2}]
```

## query_ref ($query, %kw)

As `query`, but always returns a reference.

```perl
my @res = query_ref "SELECT id FROM author WHERE id=:id", id =&gt; 2;
\@res  # --&gt; [[ {id=&gt;2} ]]
```

## query_sth ($query, %kw)

As `query`, but returns `$sth`.

```perl
my $sth = query_sth "SELECT * FROM author";
my @rows;
while(my $row = $sth-&gt;fetchrow_arrayref) {
    push @rows, $row;
}
$sth-&gt;finish;

0+@rows  # -&gt; 3
```

## query_slice ($key, $val, @args)

As query, plus converts the result into the desired data structure.

```perl
my %author = query_slice name =&gt; "id", "SELECT id, name FROM author";
\%author  # --&gt; {"Pushkin A.S." =&gt; 1, "Pushkin A." =&gt; 2, "Alice" =&gt; 3}
```

## query_col ($query, %params)

Returns one column.

```perl
query_col "SELECT name FROM author ORDER BY name" # --&gt; ["Alice", "Pushkin A.", "Pushkin A.S."]

eval {query_col "SELECT id, name FROM author"}; $@  # ~&gt; Only one column is acceptable!
```

## query_row ($query, %params)

Returns one row.

```perl
query_row "SELECT name FROM author WHERE id=2" # --&gt; {name =&gt; "Pushkin A."}

my ($id, $name) = query_row "SELECT id, name FROM author WHERE id=2";
$id    # -&gt; 2
$name  # =&gt; Pushkin A.
```

## query_row_ref ($query, %params)

As `query_row`, but retuns array reference always.

```perl
my @x = query_row_ref "SELECT name FROM author WHERE id=2";
\@x # --&gt; [{name =&gt; "Pushkin A."}]

eval {query_row_ref "SELECT name FROM author"}; $@  # ~&gt; A few lines!
```

## query_scalar ($query, %params)

Returns scalar.

```perl
query_scalar "SELECT name FROM author WHERE id=2" # =&gt; Pushkin A.
```

## make_query_for_order ($order, $next)

Creates a condition for requesting a page not by offset, but by **cursor pagination**.

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

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

$select     # =&gt; name || ',' || id
$where      # -&gt; 1
$order_sel  # -&gt; 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-&gt;{next};
$select     # =&gt; name || ',' || id
$where      # =&gt; (name &lt; 'Pushkin A.'\nOR name = 'Pushkin A.' AND id &gt;= '2')
$order_sel  # --&gt; [qw/name id/]
```

See also:
1. Article [Paging pages on social networks
](https://habr.com/ru/articles/674714/).
2. [SQL::SimpleOps-&gt;SelectCursor](https://metacpan.org/dist/SQL-SimpleOps/view/lib/SQL/SimpleOps.pod#SelectCursor)

## settings ($id, $value)

Sets or returns a key from a table `settings`.

```perl
query "CREATE TABLE settings(
    id TEXT PRIMARY KEY,
	value TEXT NOT NULL
)";

settings "x1"       # -&gt; undef
settings "x1", 10   # -&gt; 1
settings "x1"       # -&gt; 10
```

## load_by_id ($tab, $pk, $fields, @options)

Returns the entry by its id.

```perl
load_by_id author =&gt; 2  # --&gt; {id=&gt;2, name=&gt;"Pushkin A."}
load_by_id author =&gt; 2, "name as n"  # --&gt; {n=&gt;"Pushkin A."}
load_by_id author =&gt; 2, "id+:x as n", x =&gt; 10  # --&gt; {n=&gt;12}
```

## insert ($tab, %x)

Adds a record and returns its id.

```perl
insert 'author', name =&gt; 'Masha'  # -&gt; 4
```

## update ($tab, $id, %params)

Updates a record by its id, and returns this id.

```perl
update author =&gt; 3, name =&gt; 'Sasha'  # -&gt; 3
eval { update author =&gt; 5, name =&gt; 'Sasha' }; $@  # ~&gt; Row author.id=5 is not!
```

## remove ($tab, $id)

Remove row from table by it id, and returns this id.

```perl
remove "author", 4  # -&gt; 4
eval { remove author =&gt; 4 }; $@  # ~&gt; Row author.id=4 does not exist!
```

## query_id ($tab, %params)

Returns the id based on other fields.

```perl
query_id 'author', name =&gt; 'Pushkin A.' # -&gt; 2
```

## stores ($tab, $rows, %opt)

Saves data (update or insert). Returns count successful operations.

```perl
my @authors = (
    {id =&gt; 1, name =&gt; 'Pushkin A.S.'},
    {id =&gt; 2, name =&gt; 'Pushkin A.'},
    {id =&gt; 3, name =&gt; 'Sasha'},
);

query "SELECT * FROM author ORDER BY id" # --&gt; \@authors

my $rows = stores 'author', [
    {name =&gt; 'Locatelli'},
    {id =&gt; 3, name =&gt; 'Kianu R.'},
    {id =&gt; 2, name =&gt; 'Pushkin A.'},
];
$rows  # -&gt; 3

my $sql = "query: INSERT INTO author (id, name) VALUES (NULL, 'Locatelli'),
(3, 'Kianu R.'),
(2, 'Pushkin A.') ON CONFLICT DO UPDATE SET id = excluded.id, name = excluded.name";

$Aion::Query::DEBUG[$#Aion::Query::DEBUG]  # -&gt; $sql


@authors = (
    {id =&gt; 1, name =&gt; 'Pushkin A.S.'},
    {id =&gt; 2, name =&gt; 'Pushkin A.'},
    {id =&gt; 3, name =&gt; 'Kianu R.'},
    {id =&gt; 5, name =&gt; 'Locatelli'},
);

query "SELECT * FROM author ORDER BY id" # --&gt; \@authors
```

## store ($tab, %params)

Saves data (update or insert). But one row.

```perl
store 'author', name =&gt; 'Bishop M.' # -&gt; 1
```

## touch ($tab, %params)

Super-powerful function: returns id of row, and if it doesn窶冲 exist, creates or updates a row and still returns.

```perl
touch 'author', name =&gt; 'Pushkin A.' # -&gt; 2
touch 'author', name =&gt; 'Pushkin X.' # -&gt; 7
```

## START_TRANSACTION ()

Returns the variable on which to set commit, otherwise the rollback occurs.

```perl
my $transaction = START_TRANSACTION;

query "UPDATE author SET name='Pushkin N.' where id=7"  # -&gt; 1

$transaction-&gt;commit;

query_scalar "SELECT name FROM author where id=7"  # =&gt; Pushkin N.


eval {
    my $transaction = START_TRANSACTION;

    query "UPDATE author SET name='Pushkin X.' where id=7" # -&gt; 1

    die "!";  # rollback
    $transaction-&gt;commit;
};

query_scalar "SELECT name FROM author where id=7"  # =&gt; Pushkin N.
```

## default_dsn ()

Default DSN for `DBI-&gt;connect`.

```perl
default_dsn  # =&gt; DBI:SQLite:dbname=test-base.sqlite
```

## default_connect_options ()

DSN, USER, PASSWORD and commands after connect.

```perl
[default_connect_options]  # --&gt; ['DBI:SQLite:dbname=test-base.sqlite', 'root', 123, []]
```

## base_connect ($dsn, $user, $password, $conn)

Connect to base and returns connect and it identify.

```perl
my ($dbh, $connect_id) = base_connect("DBI:SQLite:dbname=base-2.sqlite", "toor", "toorpasswd", []);

ref $dbh     # =&gt; DBI::db
$connect_id  # -&gt; -1
```

## connect_respavn ($base)

Connection check and reconnection.

```perl
my $old_base = $Aion::Query::base;

$old_base-&gt;ping  # -&gt; 1
connect_respavn $Aion::Query::base, $Aion::Query::base_connection_id;

$old_base  # -&gt; $Aion::Query::base
```

## connect_restart ($base)

Connection restart.

```perl
my $connection_id = $Aion::Query::base_connection_id;
my $base = $Aion::Query::base;

connect_restart $Aion::Query::base, $Aion::Query::base_connection_id;

$base-&gt;ping  # -&gt; 0
$Aion::Query::base-&gt;ping  # -&gt; 1
```

## query_stop ()

A request may be running - you need to kill it.

Creates an additional connection to the base and kills the main one.

It using `$Aion::Query::base_connection_id` for this.

SQLite runs in the same process, so `$Aion::Query::base_connection_id` has `-1`. In this case, this method does nothing.

```perl
my @x = query_stop;
\@x  # --&gt; []
```

## sql_debug ($fn, $query)

Stores queries to the database in `@Aion::Query::DEBUG`. Called from `query_do`.

```perl
sql_debug label =&gt; "SELECT 123";

$Aion::Query::DEBUG[$#Aion::Query::DEBUG]  # =&gt; label: SELECT 123
```

# AUTHOR

Yaroslav O. Kosmina [dart@cpan.org](dart@cpan.org)

# LICENSE

笞� **GPLv3**

# COPYRIGHT

The Aion::Surf module is copyright ﾂｩ 2023 Yaroslav O. Kosmina. Rusland. All rights reserved.
</pre></body></html>