view release on metacpan or  search on metacpan

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

=encoding utf-8

=head1 NAME

Aion::Query - a functional interface for accessing SQL databases (MySQL, MariaDB, Postgres and SQLite)

=head1 VERSION

0.0.6

=head1 SYNOPSIS

File .config.pm:

	package config;
	
	config_module Aion::Query => {
	    DRV  => "SQLite",
	    BASE => "test-base.sqlite",
	    BQ => 0,
	};
	
	1;



	use Aion::Query;
	
	query "CREATE TABLE author (
	    id INTEGER PRIMARY KEY AUTOINCREMENT,
	    name TEXT NOT NULL UNIQUE
	)";
	
	insert "author", name => "Pushkin A.S." # -> 1
	
	touch "author", name => "Pushkin A."    # -> 2
	touch "author", name => "Pushkin A.S."  # -> 1
	touch "author", name => "Pushkin A."    # -> 2
	
	query_scalar "SELECT count(*) FROM author"  # -> 2
	
	my @rows = query "SELECT *
	FROM author
	WHERE 1
	    if_name>> AND name like :name
	",
	    if_name => Aion::Query::BQ == 0,
	    name => "P%",
	;
	
	\@rows # --> [{id => 1, name => "Pushkin A.S."}, {id => 2, name => "Pushkin A."}]
	
	$Aion::Query::DEBUG[1]  # => query: INSERT INTO author (name) VALUES ('Pushkin A.S.')

=head1 DESCRIPTION

C<Aion::Query> allows you to build an SQL query using a simple template mechanism.

Typically, SQL queries are built using conditions, which loads the code.

The second problem is placing Unicode characters in single-byte encodings, which reduces the size of the database. So far the problem has been resolved only for the B<cp1251> encoding. This is controlled by the C<< use config BQ =E<gt> 1 >> parameter...

=head1 SUBROUTINES

=head2 query ($query, %params)

Provides SQL queries (DCL, DDL, DQL and DML) to the DBMS with parameter quotas.

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

=head2 LAST_INSERT_ID()

Returns the ID of the last insert.

	query "INSERT INTO author (name) VALUES (:name)", name => "Alice"  # -> 1
	LAST_INSERT_ID  # -> 3

=head2 quote ($scalar)

Quotes a scalar for an SQL query.

	quote undef     # => NULL
	quote "abc"     # => 'abc'
	quote 123       # => 123
	quote "123"     # => '123'
	quote(0+"123")  # => 123
	quote(123 . "") # => '123'
	quote 123.0       # => 123.0
	quote(0.0+"126")  # => 126
	quote("127"+0.0)  # => 127
	quote("128"-0.0)  # => 128
	quote("129"+1.e-100)  # => 129.0
	
	# use for insert formula: SELECT :x as summ ⇒ x => \"xyz + 123"
	quote \"without quote"  # => without quote
	
	# use in: WHERE id in (:x)
	quote [1,2,"5"] # => 1, 2, '5'
	
	# use in: INSERT INTO author VALUES :x
	quote [[1, 2], [3, "4"]]  # => (1, 2), (3, '4')
	
	# use in multiupdate: UPDATE author SET name=CASE id :x ELSE null END
	quote \[2=>'Pushkin A.', 1=>'Pushkin A.S.']  # => WHEN 2 THEN 'Pushkin A.' WHEN 1 THEN 'Pushkin A.S.'
	
	# use for UPDATE SET :x or INSERT SET :x
	quote {name => 'A.S.', id => 12}   # => id = 12, name = 'A.S.'
	
	[map quote, -6, "-6", 1.5, "1.5"] # --> [-6, "'-6'", 1.5, "'1.5'"]
	

=head2 query_prepare ($query, %param)

Replaces the parameters (C<%param>) in a query (C<$query>) and returns it. Parameters are enclosed in quotes via the C<quote> routine.

Parameters of the form C<:x> will be quoted taking into account the scalar flags, which indicate whether it contains a string, an integer or a floating point number.

To explicitly indicate the type of a scalar, use the prefixes: C<:^x> – integer, C<:.x> – string, C<:~x> – floating.

	query_prepare "INSERT author SET name IN (:name)", name => ["Alice", 1, 1.0]  # => INSERT author SET name IN ('Alice', 1, 1.0)