view release on metacpan or  search on metacpan

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

use DBI qw//;
use Scalar::Util qw//;
use List::Util qw//;

use Exporter qw/import/;
our @EXPORT = our @EXPORT_OK = grep {
	ref \$Aion::Query::{$_} eq "GLOB"
		&& *{$Aion::Query::{$_}}{CODE} && !/^(_|(NaN|import)\z)/n
} keys %Aion::Query::;

use config {
	DSN  => undef,
    DRV  => 'mysql',
    BASE => 'BASE',
    HOST => undef,
    PORT => undef,
    SOCK => undef,
    USER => 'root',
    PASS => 123,
    CONN => undef,
    DEBUG => 0,
	MAX_QUERY_ERROR => 1000,
	BQ => 1,
};

# Формирует DSN на основе конфига
our $DEFAULT_DSN;
sub default_dsn() {
	$DEFAULT_DSN //= do {
		if(defined DSN) {DSN}
		elsif(DRV =~ /mysql|mariadb/i) {
			my $sock = SOCK;
			$sock //= "/var/run/mysqld/mysqld.sock" if !defined HOST;

			"DBI:${\ DRV}:database=${\ BASE};${\
				(defined(HOST)?
					'host=' . HOST
					. (defined(PORT)? ':' . PORT: ())
					. ';': ()
				)
			}${\ (defined($sock)? 'mysql_socket=' . $sock: ()) }"
		}
		elsif(DRV =~ /sqlite/i) { "DBI:${\ DRV}:dbname=${\ BASE}" }
		else { die "Using DSN! DRV: ${\ DRV} is'nt supported." }
	}
}

my $CONN;
sub default_connect_options() {
    return default_dsn, USER, PASS, $CONN //= CONN // do {
		if(DRV =~ /mysql|mariadb/i) {[
			"SET NAMES utf8",
			"SET sql_mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'",
   		]}
		else {[]}
	};
}

# Коннект к базе и id коннекта
sub base_connect {
	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;
}

# Рестарт коннекта
sub connect_restart {
	my ($base, $base_connection_id) = @_;
	$base->disconnect if $base;
	($_[0], $_[1]) = base_connect(default_connect_options());
	return;
}


# Инициализация БД
our $base; our $base_connection_id;

END {
	$base->disconnect if $base;
}

# возможно выполняется запрос - нужно его убить
sub query_stop {
	return if $base_connection_id == -1;
	# вспомогательное подключение
	my $signal = base_connect(default_connect_options());
	$signal->do("KILL HARD " . ($base_connection_id + 0));
	$signal->disconnect;
	return;
}

# Запросы к базе

our @DEBUG;
sub sql_debug(@) {
	my ($fn, $query) = @_;
	my $msg = "$fn: " . (ref $query? np($query): $query);
	push @DEBUG, $msg;
	print STDERR $msg, "\n" if DEBUG;
}

# sub debug_html {
# 	join "", map { ("<p class='debug'>", to_html($_), "</p>\n") } @DEBUG;
# }

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

	(3, 'Kianu R.'),
	(2, 'Pushkin A.') ON CONFLICT DO UPDATE SET id = excluded.id, name = excluded.name";
	
	$Aion::Query::DEBUG[$#Aion::Query::DEBUG]  # -> $sql
	
	
	@authors = (
	    {id => 1, name => 'Pushkin A.S.'},
	    {id => 2, name => 'Pushkin A.'},
	    {id => 3, name => 'Kianu R.'},
	    {id => 5, name => 'Locatelli'},
	);
	
	query "SELECT * FROM author ORDER BY id" # --> \@authors

=head2 store ($tab, %params)

Saves data (updates or inserts) one row.

	store 'author', name => 'Bishop M.' # -> 1

=head2 touch ($tab, %params)

Super powerful function: returns the row identifier, and if it doesn't exist, creates or updates the row and returns anyway.

	touch 'author', name => 'Pushkin A.' # -> 2
	touch 'author', name => 'Pushkin X.' # -> 7

=head2 START_TRANSACTION()

Returns the variable on which the commit must be performed, otherwise a rollback occurs.

	my $transaction = START_TRANSACTION;
	
	query "UPDATE author SET name='Pushkin N.' where id=7"  # -> 1
	
	$transaction->commit;
	
	query_scalar "SELECT name FROM author where id=7"  # => Pushkin N.
	
	
	eval {
	    my $transaction = START_TRANSACTION;
	
	    query "UPDATE author SET name='Pushkin X.' where id=7" # -> 1
	
	    die "!";  # rollback
	    $transaction->commit;
	};
	
	query_scalar "SELECT name FROM author where id=7"  # => Pushkin N.

=head2 default_dsn()

Default DSN for C<< DBI-E<gt>connect >>.

	default_dsn  # => DBI:SQLite:dbname=test-base.sqlite

=head2 default_connect_options()

DSN, user, password and commands after connection.

	[default_connect_options]  # --> ['DBI:SQLite:dbname=test-base.sqlite', 'root', 123, []]

=head2 base_connect ($dsn, $user, $password, $conn)

We connect to the database and return the connection and identify it.

	my ($dbh, $connect_id) = base_connect("DBI:SQLite:dbname=base-2.sqlite", "toor", "toorpasswd", []);
	
	ref $dbh     # => DBI::db
	$connect_id  # -> -1

=head2 connect_respavn ($base)

Checking the connection and reconnecting.

	my $old_base = $Aion::Query::base;
	
	$old_base->ping  # -> 1
	connect_respavn $Aion::Query::base, $Aion::Query::base_connection_id;
	
	$old_base  # -> $Aion::Query::base

=head2 connect_restart ($base)

Restarting the connection.

	my $connection_id = $Aion::Query::base_connection_id;
	my $base = $Aion::Query::base;
	
	connect_restart $Aion::Query::base, $Aion::Query::base_connection_id;
	
	$base->ping  # -> 0
	$Aion::Query::base->ping  # -> 1

=head2 query_stop()

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

To do this, use C<$Aion::Query::base_connection_id>.

SQLite runs in the same process, so C<$Aion::Query::base_connection_id> has C<-1>. That is, for SQLite this method does nothing.

	my @x = query_stop;
	\@x  # --> []

=head2 sql_debug ($fn, $query)

Stores database queries in C<@Aion::Query::DEBUG>. Called from C<query_do>.

	sql_debug label => "SELECT 123";
	
	$Aion::Query::DEBUG[$#Aion::Query::DEBUG]  # => label: SELECT 123

=head1 AUTHOR

Yaroslav O. Kosmina LL<mailto:dart@cpan.org>

=head1 LICENSE

âš– B<GPLv3>

=head1 COPYRIGHT