#!/usr/bin/perl
Hide Show 49 lines of Pod
package
pssql;
no warnings qw(uninitialized);
our $VERSION = ( split( ' ', '$Revision: 4688 $' ) )[1];
$Data::Dumper::Sortkeys = $Data::Dumper::Useqq = $Data::Dumper::Indent = $Data::Dumper::Terse = 1;
our ( %work, );
our (%config);
*config = *main::config;
*work = *main::work;
*stat = *main::stat;
our $AUTOLOAD;
my ( $tq, $rq, $vq );
my ( $roworder, $tableorder, );
our ( %row, %default );
$config{ 'log_' . $_ } = 0 for grep { !exists $config{ 'log_' . $_ } } qw(trace dmpbef);
sub row {
my $row = shift @_;
return {
%{ ( defined $config{'row'} ? $config{'row'}{$row} : undef ) || $row{$row} || {} }, %{ $config{'row_all'} || {} },
'order' => --$roworder,
@_
};
}
sub table {
my $table = shift @_;
return @_;
}
BEGIN {
%row = (
'time' => {
'type' => 'INT',
'unsigned' => 1,
'default' => 0,
'date_time' => 1,
},
'uint' => { 'type' => 'INTEGER', 'unsigned' => 1, 'default' => 0, },
'uint16' => { 'type' => 'SMALLINT', 'unsigned' => 1, 'default' => 0, },
'uint64' => { 'type' => 'BIGINT', 'unsigned' => 1, 'default' => 0, },
'text' => { 'type' => 'VARCHAR', 'index' => 10, 'default' => '', },
'stem' => {
'type' => 'VARCHAR',
'fulltext' => 'stemi',
'default' => '',
'not null' => 1,
'stem_index' => 1,
},
);
$row{'id'} ||= row( 'uint', 'auto_increment' => 1, 'primary' => 1 ),
$row{'added'} ||= row( 'time', 'default_insert' => int( time() ), 'no_insert_update' => 1, );
$row{'year'} ||= row('uint16');
$row{'size'} ||= row('uint64');
%default = (
'null' => { 'do' => sub { }, 'query' => sub { wantarray ? () : [] }, 'line' => sub { {} }, },
'sqlite' => {
'dbi' => 'SQLite',
'params' => [qw(dbname)],
'dbname' => $config{'root_path'} . 'sqlite.db',
'no_update_limit' => 1,
'table quote' => '"',
'row quote' => '"',
'value quote' => "'",
'IF NOT EXISTS' => 'IF NOT EXISTS',
'index_IF NOT EXISTS' => 'IF NOT EXISTS',
'IF EXISTS' => 'IF EXISTS',
'REPLACE' => 'REPLACE',
'AUTO_INCREMENT' => 'AUTOINCREMENT',
'ANALYZE' => 'ANALYZE',
'err_ignore' => [qw( 1 )],
'error_type' => sub {
my $self = shift;
my ( $err, $errstr ) = @_;
return 'install' if $errstr =~ /no such table:|unable to open database file/i;
return 'syntax' if $errstr =~ /syntax|unrecognized token/i or $errstr =~ /misuse of aggregate/;
return 'retry' if $errstr =~ /database is locked/i;
return 'upgrade' if $errstr =~ /no such column/i;
return undef;
},
'pragma' => {map {$_=>$_} 'synchronous = OFF', 'auto_vacuum = FULL'},
'on_connect' => sub {
my $self = shift;
$self->do("PRAGMA $_;") for keys %{$self->{'pragma'}||{}};
},
'no_dbirows' => 1,
},
'pgpp' => {
'dbi' => 'PgPP',
'user' => ( $^O =~ /^(?:(ms)?(dos|win(32|nt)?))/i ? 'postgres' : 'pgsql' ),
'IF EXISTS' => 'IF EXISTS', 'CREATE TABLE' => 'CREATE TABLE', 'OFFSET' => 'OFFSET',
'UNSIGNED' => '',
'no_delete_limit' => 1,
'table quote' => '"',
'row quote' => '"',
'value quote' => "'",
'index_name_table' => 1,
'REPLACE' => 'INSERT',
'EXPLAIN' => 'EXPLAIN ANALYZE',
'CASCADE' => 'CASCADE',
'SET NAMES' => 'SET client_encoding = ',
'fulltext_config' => 'pg_catalog.simple',
'params' => [qw(dbname host port path debug)],
'err_ignore' => [qw( 1 7)],
'error_type' => sub {
my $self = shift, my ( $err, $errstr ) = @_;
return 'install_db' if $errstr =~ /FATAL:\s*database ".*?" does not exist/i;
return 'fatal' if $errstr =~ /fatal/i;
return 'syntax' if $errstr =~ /syntax/i;
return 'connection' if $errstr =~ /connect|Unknown message type: ''/i;
return 'install' if $errstr =~ /ERROR:\s*(?:relation \S+ does not exist)/i;
return 'retry' if $errstr =~ /ERROR: database ".*?" is being accessed by other users/i;
return 'ignore'
if $errstr =~
/(?:duplicate key violates unique constraint)|(?:duplicate key value violates unique constraint)|(?:ERROR:\s*(?:database ".*?" already exists)|(?:relation ".*?" already exists)|(?:invalid byte sequence for encoding)|(?:function .*? does not exist)|(?:null value in column .*? violates not-null constraint))/i;
return undef;
},
'on_connect' => sub {
my $self = shift;
$self->set_names();
$self->do("select set_curcfg('default');") if $self->{'use_fulltext'} and $self->{'old_fulltext'};
},
'no_dbirows' => 1,
'cp1251' => 'win1251',
'fulltext_word_glue' => '&',
},
'sphinx' => {
'dbi' => 'mysql',
'user' => 'root',
'port' => 9306,
'params' => [qw(host port )],
'sphinx' => 1,
'value quote' => "'",
'no_dbirows' => 1,
'no_column_prepend_table' => 1,
'no_join' => 1,
'OPTION' => 'OPTION',
'option' => { 'max_query_time' => 20000, 'cutoff' => 1000 },
},
'mysql5' => {
'dbi' => 'mysql',
'user' => 'root',
'use_drh' => 1,
'mysql_enable_utf8' => 1,
'varchar_max' => 65530,
'unique_max' => 1000,
'primary_max' => 999,
'fulltext_max' => 1000,
'err_connection' => [qw( 1 1040 1053 1129 1213 1226 2002 2003 2006 2013 )],
'err_fatal' => [qw( 1016 1046 1251 )],
'err_syntax' => [qw( 1060 1064 1065 1067 1071 1096 1103 1118 1148 1191 1364 1366 1406 1439)],
'err_repair' => [qw( 126 130 144 145 1034 1062 1194 1582 )],
'err_retry' => [qw( 1317 )],
'err_install' => [qw( 1146 )],
'err_install_db' => [qw( 1049 )],
'err_upgrade' => [qw( 1054 )],
'err_ignore ' => [qw( 2 1264 )],
'error_type' => sub {
my $self = shift, my ( $err, $errstr ) = @_;
for my $errtype (qw(connection retry syntax fatal repair install install_db upgrade)) {
return $errtype if grep { $err eq $_ } @{ $self->{ 'err_' . $errtype } };
}
return undef;
},
'table quote' => "`",
'row quote' => "`",
'value quote' => "'",
'quote_slash' => 1,
'index in create table' => 1,
'utf-8' => 'utf8',
'koi8-r' => 'koi8r',
'table options' => 'ENGINE = MYISAM DELAY_KEY_WRITE=1',
'IF NOT EXISTS' => 'IF NOT EXISTS',
'IF EXISTS' => 'IF EXISTS',
'IGNORE' => 'IGNORE',
'REPLACE' => 'REPLACE',
'INSERT' => 'INSERT',
'HIGH_PRIORITY' => 'HIGH_PRIORITY',
'SET NAMES' => 'SET NAMES',
'DEFAULT CHARACTER SET' => 'DEFAULT CHARACTER SET',
'USE_FRM' => 'USE_FRM',
'EXTENDED' => 'EXTENDED',
'QUICK' => 'QUICK',
'ON DUPLICATE KEY UPDATE' => 'ON DUPLICATE KEY UPDATE',
'UNSIGNED' => 'UNSIGNED',
'UNLOCK TABLES' => 'UNLOCK TABLES',
'LOCK TABLES' => 'LOCK TABLES',
'OPTIMIZE' => 'OPTIMIZE TABLE',
'ANALYZE' => 'ANALYZE TABLE',
'CHECK' => 'CHECK TABLE',
'FLUSH' => 'FLUSH TABLE',
'LOW_PRIORITY' => 'LOW_PRIORITY',
'on_connect' => sub {
my $self = shift;
$self->{'db_id'} = $self->{'dbh'}->{'mysql_thread_id'};
$self->set_names() if !( $ENV{'MOD_PERL'} || $ENV{'FCGI_ROLE'} );
},
'on_user' => sub {
my $self = shift;
$self->set_names() if $ENV{'MOD_PERL'} || $ENV{'FCGI_ROLE'};
},
'params' => [
qw(host port database mysql_client_found_rows mysql_compression mysql_connect_timeout mysql_read_default_file mysql_read_default_group mysql_socket
mysql_ssl mysql_ssl_client_key mysql_ssl_client_cert mysql_ssl_ca_file mysql_ssl_ca_path mysql_ssl_cipher
mysql_local_infile mysql_embedded_options mysql_embedded_groups mysql_enable_utf8)
],
'insert_by' => 1000, ( !$ENV{'SERVER_PORT'} ? ( 'auto_check' => 1 ) : () ), 'unique name' => 1,
'match' => sub {
my $self = shift;
my ( $param, $param_num, $table, $search_str, $search_str_stem ) = @_;
my ( $ask, $glue );
local %_;
map { $_{ $self->{'table'}{$table}{$_}{'fulltext'} } = 1 }
grep { $self->{'table'}{$table}{$_}{'fulltext'} or ( $self->{'sphinx'} and $self->{'table'}{$table}{$_}{'sphinx'} ) }
keys %{ $self->{'table'}{$table} };
for my $index ( keys %_ ) {
if (
$_ = join( ' , ',
map { "$rq$_$rq" }
sort { $self->{'table'}{$table}{$b}{'order'} <=> $self->{'table'}{$table}{$a}{'order'} }
grep { $self->{'table'}{$table}{$_}{'fulltext'} eq $index } keys %{ $self->{'table'}{$table} } )
)
{
my $stem =
grep { $self->{'table'}{$table}{$_}{'fulltext'} eq $index and $self->{'table'}{$table}{$_}{'stem_index'} }
keys %{ $self->{'table'}{$table} };
$self->{'accurate'} = 1, next,
if ($stem
and length $search_str_stem
and $self->{'auto_accurate_on_slow'}
and $search_str_stem =~ /\b\w{$self->{'auto_accurate_on_slow'}}\b/ );
my $double =
grep { $self->{'table'}{$table}{$_}{'fulltext'} and $self->{'table'}{$table}{$_}{'stem'} }
keys %{ $self->{'table'}{$table} };
next if $double and ( $self->{'accurate'} xor !$stem );
my $match;
if ( $self->{'sphinx'} ) { $match = ' MATCH (' . $self->squotes( $stem ? $search_str_stem : $search_str ) . ')' }
else {
$match = ' MATCH (' . $_ . ')' . ' AGAINST (' . $self->squotes( $stem ? $search_str_stem : $search_str ) . (
( !$self->{'no_boolean'} and $param->{ 'adv_query' . $param_num } eq 'on' )
? 'IN BOOLEAN MODE'
: $self->{'fulltext_extra'}
) . ') ';
}
$ask .= " $glue " . $match;
$work{'what_relevance'}{$table} ||= $match . " AS $rq" . "relev$rq"
if $self->{'select_relevance'}
or $self->{'table_param'}{$table}{'select_relevance'};
}
$glue = $self->{'fulltext_glue'};
}
return $ask;
},
},
);
}
sub new {
my $self = bless( {}, shift );
$self->init(@_);
$self->psconn::init(@_);
return $self;
}
sub cmd {
my $self = shift;
my $cmd = shift;
$self->log( 'trace', "pssql::$cmd [$self->{'dbh'}]", @_ ) if $cmd ne 'log';
$self->{'handler_bef'}{$cmd}->( $self, \@_ ) if $self->{'handler_bef'}{$cmd};
my @ret =
ref( $self->{$cmd} ) eq 'CODE'
? ( wantarray ? ( $self->{$cmd}->( $self, @_ ) ) : scalar $self->{$cmd}->( $self, @_ ) )
: (
exists $self->{$cmd}
? ( ( defined( $_[0] ) ? ( $self->{$cmd} = $_[0] ) : ( $self->{$cmd} ) ) )
: (!$self->{'dbh'} ? ()
: $self->{'dbh'}->can($cmd) ? $self->{'dbh'}->$cmd(@_)
: exists $self->{'dbh'}{$cmd} ? ( ( defined( $_[0] ) ? ( $self->{'dbh'}->{$cmd} = $_[0] ) : ( $self->{'dbh'}->{$cmd} ) ) )
: undef )
);
$self->{'handler'}{$cmd}->( $self, \@_, \@ret ) if $self->{'handler'}{$cmd};
return wantarray ? @ret : $ret[0];
}
sub AUTOLOAD {
my $self = shift;
my $type = ref($self) or return;
my $name = $AUTOLOAD;
$name =~ s/.*://;
return $self->cmd( $name, @_ );
}
sub _disconnect {
my $self = shift;
$self->log( 'trace', 'pssql::_diconnect', "dbh=$self->{'dbh'}" );
$self->flush_insert() unless $self->{'in_disconnect'};
$self->{'in_disconnect'} = 1;
return 0;
}
sub _dropconnect {
my $self = shift;
$self->log( 'trace', 'pssql::_dropconnect' );
$self->{'in_disconnect'} = 1;
$self->{'sth'}->finish() if $self->{'sth'};
$self->{'dbh'}->disconnect(), $self->{'dbh'} = undef if $self->{'dbh'} and keys %{ $self->{'dbh'} };
delete $self->{'in_disconnect'};
return 0;
}
sub _check {
my $self = shift;
return 1 if !$self->{'dbh'} or !$self->{'connected'};
return !$self->{'dbh'}->ping();
}
sub init {
my $self = shift;
local %_ = (
'log' => sub (@) {
shift;
psmisc::printlog(@_);
},
'driver' => 'mysql5',
'host' => ( $^O eq 'cygwin' ? '127.0.0.1' : 'localhost' ),
'database' => 'pssqldef',
'error_sleep' => ( $ENV{'SERVER_PORT'} ? 1 : 3600 ),
'error_tries' => ( $ENV{'SERVER_PORT'} ? 1 : 1000 ),
'error_chain_tries' => ( $ENV{'SERVER_PORT'} ? 1 : 100 ),
'connect_tries' => ( $ENV{'SERVER_PORT'} ? 1 : 0 ),
'connect_chain_tries' => 0,
'connect_auto' => 0,
'connect_params' => {
'RaiseError' => 0,
'AutoCommit' => 1,
'PrintError' => 0,
'PrintWarn' => 0,
'HandleError' => sub {
$self->log( 'dev', 'HandleError', @_, $DBI::err, $DBI::errstr );
},
},
( $ENV{'SERVER_PORT'} ? () : ( 'auto_repair' => 10 ) ),
'auto_repair_selected' => 0,
'auto_install' => 1, 'auto_install_db' => 1, 'err_retry_unknown' => 0,
'codepage' => 'utf-8',
'index_postfix' => '_i', 'limit_max' => 1000, 'limit_default' => 100,
'page_min' => 1, 'page_default' => 1,
'varchar_max' => 65535,
'row_max' => 65535,
'primary_max' => 65535,
'fulltext_max' => 65535,
'AUTO_INCREMENT' => 'AUTO_INCREMENT',
'EXPLAIN' => 'EXPLAIN',
'statable' => { 'queries' => 1, 'connect_tried' => 1, 'connects' => 1, 'inserts' => 1 },
'statable_time' => { 'queries_time' => 1, 'queries_avg' => 1, },
'param_trans_int' => { 'on_page' => 'limit', 'show_from' => 'limit_offset', 'page' => 'page', 'accurate' => 'accurate' },
'connect_cached' => 1,
'char_type' => 'VARCHAR',
'true' => 1,
'fulltext_glue' => 'OR',
'retry_vars' => [qw(auto_repair connect_tries connect_chain_tries error_sleep error_tries auto_check)],
'err' => 0,
'insert_cached_time' => 60,
'stat_every' => 60,
'auto_repairs_max' => 2,
@_,
);
@{$self}{ keys %_ } = values %_;
$self->{'database'} = $self->{'dbname'} if $self->{'dbname'};
$self->{'dbname'} ||= $self->{'database'};
$self->calc();
$self->functions();
( $tq, $rq, $vq ) = $self->quotes();
DBI->trace( $self->{'trace_level'}, $self->{'trace'} ) if $self->{'trace_level'} and $self->{'trace'};
return 0;
}
sub calc {
my $self = shift;
$self->{'default'} ||= \%default;
$self->{'default'}{'pgpp'}{'match'} = sub {
my $self = shift;
return undef unless $self->{'use_fulltext'};
my ( $param, $param_num, $table, $search_str, $search_str_stem ) = @_;
my ( $ask, $glue );
s/(?:^\s+)|(?:\s+$)//, s/\s+/$self->{'fulltext_word_glue'}/g for ( $search_str, $search_str_stem );
local %_;
map { $_{ $self->{'table'}{$table}{$_}{'fulltext'} } = 1 }
grep { $self->{'table'}{$table}{$_}{'fulltext'} } keys %{ $self->{'table'}{$table} };
for my $index ( keys %_ ) {
my $stem =
grep { $self->{'table'}{$table}{$_}{'fulltext'} eq $index and $self->{'table'}{$table}{$_}{'stem_index'} }
keys %{ $self->{'table'}{$table} };
my $double =
grep { $self->{'table'}{$table}{$_}{'fulltext'} and $self->{'table'}{$table}{$_}{'stem'} }
keys %{ $self->{'table'}{$table} };
next if $double and ( $self->{'accurate'} xor !$stem );
$ask .= " $glue $index @@ to_tsquery( ${vq}$self->{'fulltext_config'}${vq}, "
. $self->squotes( $stem ? $search_str_stem : $search_str ) . ")";
$glue ||= $self->{'fulltext_glue'};
}
return $ask;
}
if $self->{'use_fulltext'};
%{ $self->{'default'}{'mysql6'} } = %{ $self->{'default'}{'mysql5'} };
%{ $self->{'default'}{'mysql4'} } = %{ $self->{'default'}{'mysql5'} };
$self->{'default'}{'mysql4'}{'SET NAMES'} = $self->{'default'}{'mysql4'}{'DEFAULT CHARACTER SET'} =
$self->{'default'}{'mysql4'}{'ON DUPLICATE KEY UPDATE'} = '';
$self->{'default'}{'mysql4'}{'varchar_max'} = 255;
%{ $self->{'default'}{'mysql3'} } = %{ $self->{'default'}{'mysql4'} };
$self->{'default'}{'mysql3'}{'table options'} = '';
$self->{'default'}{'mysql3'}{'USE_FRM'} = '';
$self->{'default'}{'mysql3'}{'no_boolean'} = 1;
$self->{'default'}{'pgpp'}{'fulltext_config'} = 'default' if $self->{'old_fulltext'};
%{ $self->{'default'}{'pg'} } = %{ $self->{'default'}{'pgpp'} };
$self->{'default'}{'pg'}{'dbi'} = 'Pg';
$self->{'default'}{'pg'}{'params'} = [qw(host port options tty dbname user password)];
%{ $self->{'default'}{'mysqlpp'} } = %{ $self->{'default'}{'mysql5'} };
$self->{'default'}{'mysqlpp'}{'dbi'} = 'mysqlPP';
$self->{'default'}{'sphinx'}{'match'} = $self->{'default'}{'mysql5'}{'match'};
$self->{'driver'} ||= 'mysql5';
$self->{'driver'} = 'mysql5' if $self->{'driver'} eq 'mysql';
$self->{$_} //= $self->{'default'}{ $self->{'driver'} }{$_} for keys %{ $self->{'default'}{ $self->{'driver'} } };
$self->{'dbi'} ||= $self->{'driver'}, $self->{'dbi'} =~ s/\d+$//i unless $self->{'dbi'};
$self->{'codepage'} = psmisc::cp_normalize( $self->{'codepage'} );
local $_ = $self->{ $self->{'codepage'} } || $self->{'codepage'};
$self->{'cp'} = $_;
$self->{'cp_set_names'} ||= $_;
$self->{'cp_int'} ||= 'utf-8';
$self->cp_client( $self->{'codepage'} );
}
sub _connect {
my $self = shift;
Hide Show 18 lines of Pod
local @_ = (
"dbi:$self->{'dbi'}:"
. join( ';', map( { $_ . '=' . $self->{$_} } grep { defined( $self->{$_} ) } @{ $self->{'params'} } ) ),
$self->{'user'}, $self->{'pass'}, $self->{'connect_params'}
);
$self->{'dbh'} = ( $self->{'connect_cached'} ? DBI->connect_cached(@_) : DBI->connect(@_) );
local $_ = $self->err_parse( \'Connection', $DBI::err, $DBI::errstr );
return $_;
}
sub sleep {
my $self = shift;
return psmisc::sleeper(@_);
}
sub functions {
my $self = shift;
$self->{'user_params'} ||= sub {
my $self = shift;
( $tq, $rq, $vq ) = $self->quotes();
my $param = { map { %$_ } @_ };
for my $from ( keys %{ $self->{'param_trans_int'} } ) {
my $to = $self->{'param_trans_int'}{$from} || $from;
$param->{$from} = 1 if $param->{$from} eq 'on';
$self->{$to} =
psmisc::check_int( $param->{$from}, ( $self->{ $to . '_min' } ), $self->{ $to . '_max' }, $self->{ $to . '_default' } );
}
$self->cp_client( $work{'codepage'} || $param->{'codepage'} || $config{'codepage'} );
};
$self->{'dump'} ||= sub {
my $self = shift;
$self->log( 'dmp', caller, ':=', join( ':', %$self ) );
return 0;
};
$self->{'quotes'} ||= sub {
my $self = shift;
$self->{'tq'} ||= $self->{'table quote'};
$self->{'rq'} ||= $self->{'row quote'};
$self->{'vq'} ||= $self->{'value quote'};
return (
$self->{'table quote'},
$self->{'row quote'},
$self->{'value quote'},
);
};
$self->{'sleep'} ||= sub {
my $self = shift;
$self->log( 'dev', 'sql_sleeper', @_ );
return psmisc::sleeper(@_);
};
$self->{'drh_init'} ||= sub {
my $self = shift;
$self->{'drh'} ||= DBI->install_driver( $self->{'dbi'} );
return 0;
};
$self->{'repair'} ||= sub {
my $self = shift;
my $tim = psmisc::timer();
@_ = sort keys %{ $self->{'table'} } unless @_;
@_ = grep { $_ and $self->{'table'}{$_} } @_;
$self->log( 'info', 'Repairing table...', @_ );
$self->flush() unless $self->{'no_repair_flush'};
local $self->{'error_tries'} = 0;
$self->query_log( "REPAIR TABLE "
. join( ',', map( $self->tquote("$self->{'table_prefix'}$_"), @_ ) )
. ( $self->{'rep_quick'} ? ' ' . $self->{'QUICK'} : '' )
. ( $self->{'rep_ext'} ? ' ' . $self->{'EXTENDED'} : '' )
. ( $self->{'rep_frm'} ? ' ' . $self->{'USE_FRM'} : '' ) );
$self->flush();
$self->log( 'time', 'Repair per', psmisc::human( 'time_period', $tim->() ) );
return 0;
};
$self->{'query_time'} ||= sub {
my $self = shift;
++$self->{'queries'};
$self->{'queries_time'} += $_[0];
$self->{'queries_avg'} = $self->{'queries_time'} / $self->{'queries'} || 1;
};
$self->{'do'} ||= sub {
my $self = shift;
my $ret;
return $ret if $self->keep();
$self->err(0);
for my $cmd (@_) {
next unless $cmd;
do {
{
$self->log( 'dmpbef', 'do(' . $self->{database} . '):[', $cmd, '] ' );
my $tim = psmisc::timer();
$ret += $self->{'dbh'}->do($cmd) if $self->{'dbh'};
$self->log(
'dmp', 'do(' . $self->{database} . '):[',
$cmd, '] = ', $ret, ' per', psmisc::human( 'time_period', $tim->() ),
'rps', psmisc::human( 'float', $ret / ( $tim->() || 1 ) )
);
$self->query_time( $tim->() );
}
} while ( $self->can_query() and $self->err_parse( \$cmd, $DBI::err, $DBI::errstr ) );
}
return $ret;
};
$self->{'can_query'} ||= sub {
my $self = shift;
return
!( $work{'die'} or $self->{'die'} or $self->{'fatal'} )
&& ( !$self->{'error_chain_tries'} or $self->{'errors_chain'} < $self->{'error_chain_tries'} )
&& ( !$self->{'error_tries'} or $self->{'errors'} < $self->{'error_tries'} );
};
$self->{'prepare'} ||= sub {
my $self = shift;
my ($query) = @_;
return 1 if $self->keep();
$self->log( 'dmpbef', "prepare query {$query}" );
return 2 unless $query;
$self->err(0);
my $ret;
my $tim = psmisc::timer();
do {
{
next unless $self->{'dbh'};
$self->{'sth'}->finish() if $self->{'sth'};
$self->{'sth'} = $self->{'dbh'}->prepare($query);
redo if $self->can_query() and $self->err_parse( \$query, $DBI::err, $DBI::errstr, 1 );
last unless $self->{'sth'};
$ret = $self->{'sth'}->execute();
}
} while ( $self->can_query() and $self->err_parse( \$query, $DBI::err, $DBI::errstr ) );
$self->query_time( $tim->() );
return 3 if $DBI::err;
$self->{'dbirows'} = 0 if ( $self->{'dbirows'} = $DBI::rows ) == 4294967294;
$self->{'dbirows'} = $self->{'limit'} if $self->{'no_dbirows'};
return (($self->{'no_dbirows'} && $ret) ? undef : !int $ret);
};
$self->{'line'} ||= sub {
my $self = shift;
return {} if @_ and $self->prepare(@_);
return {} if !$self->{'sth'} or $self->{'sth'}->err;
my $tim = psmisc::timer();
local $_ =
scalar( psmisc::cp_trans_hash( $self->{'codepage'}, $self->{'cp_out'}, ( $self->{'sth'}->fetchrow_hashref() || {} ) ) );
$self->{'queries_time'} += $tim->();
$self->log(
'dmp', 'line(' . $self->{database} . '):[',
@_, '] = ', scalar keys %$_,
' per', psmisc::human( 'time_period', $tim->() ),
'err=', $self->err(),
) if ( caller(2) )[0] ne 'pssql';
return $_;
};
$self->{'query'} ||= sub {
my $self = shift;
my $tim = psmisc::timer();
my @hash;
for my $query (@_) {
next unless $query;
local $self->{'explain'} = 0, $self->query_log( $self->{'EXPLAIN'} . ' ' . $query )
if $self->{'explain'} and $self->{'EXPLAIN'};
local $_ = $self->line($query);
next unless keys %$_;
push( @hash, $_ );
next unless $self->{'sth'} and keys %$_;
my $tim = psmisc::timer();
push( @hash, scalar psmisc::cp_trans_hash( $self->{'codepage'}, $self->{'cp_out'}, $_ ) ),
while ( $_ = $self->{'sth'}->fetchrow_hashref() );
$self->{'queries_time'} += $tim->();
}
$self->log(
'dmp', 'query(' . $self->{database} . '):[',
@_, '] = ', scalar @hash, ' per', psmisc::human( 'time_period', $tim->() ),
'rps', psmisc::human( 'float', ( scalar @hash ) / ( $tim->() || 1 ) ),
'err=', $self->err()
);
$self->{'dbirows'} = scalar @hash if $self->{'no_dbirows'} or $self->{'dbirows'} <= 0;
if ( $self->{'codepage'} eq 'utf-8' ) {
for (@hash) { utf8::decode $_ for %$_; }
}
return wantarray ? @hash : \@hash;
};
$self->{'query_log'} ||= sub {
my $self = shift;
my @ret;
for (@_) { push( @ret, $self->query_print( $self->query($_) ) ); }
return wantarray ? @ret : \@ret;
};
$self->{'query_print'} ||= sub {
my $self = shift;
my @hash = @_;
return unless @hash and %{ $hash[0] };
$self->log( 'dbg', 'sql query', $_ );
$self->log( 'dbg', '|', join "\t|", keys %{ $hash[0] } ) if keys %{ $hash[0] };
$self->log( 'dbg', '|', join( "\t|", values %{$_} ) ) for @hash;
return wantarray ? @_ : \@_;
};
$self->{'quote'} ||= sub {
my $self = shift;
my ( $s, $q, $qmask ) = @_;
return $s if $self->{'no_quote_null'} and $s =~ /^null$/i;
return $self->{'dbh'}->quote( defined $s ? $s : '' ) if $self->{'dbh'} and !$q;
$q ||= "'";
if ( $self->{'quote_slash'} ) { $s =~ s/($q|\\)/\\$1/g; }
else { $s =~ s/($q)/$1$1/g; }
return $q . $s . $q;
};
$self->{'squotes'} ||= sub {
my $self = shift;
return ' ' . $self->quote(@_) . ' ';
};
$self->{'tquote'} ||= sub {
my $self = shift;
return $self->{'tq'} . $_[0] . $self->{'tq'};
};
$self->{'rquote'} ||= sub {
my $self = shift;
return $self->{'rq'} . $_[0] . $self->{'rq'};
};
$self->{'vquote'} ||= $self->{'quote'};
$self->{'filter_row'} ||= sub {
my $self = shift;
my ( $table, $filter, $values ) = @_;
local %_;
map { $_{$_} = $values->{$_} } grep { $self->{'table'}{$table}{$_}{$filter} } keys %{ $self->{'table'}{$table} };
return wantarray ? %_ : \%_;
};
$self->{'err_parse'} ||= sub {
my $self = shift;
my ( $cmd, $err, $errstr, $sth ) = @_;
$err ||= $DBI::err;
$errstr ||= $DBI::errstr;
my $state = $self->{'dbh'}->state if $self->{'dbh'};
my $errtype = $self->error_type( $err, $errstr );
$errtype ||= 'connection' unless $self->{'dbh'};
$self->{'fatal'} = 1 if $errtype eq 'fatal';
Hide Show 16 lines of Pod
$self->log(
'dev', "err_parse st0 ret1 ",
'wdi=', $work{'die'}, 'di=', $self->{'die'}, 'fa=', $self->{'fatal'}, 'er=',
( $self->{'errors'} >= $self->{'error_tries'} ),
$self->{'errors'}, $self->{'error_tries'},
$errtype, $state
),
CORE::sleep(1), return $self->err(1)
if $work{'die'}
or $self->{'die'}
or $self->{'fatal'}
or ( $self->{'error_tries'} and $self->{'errors'} > $self->{'error_tries'} )
or ( $self->{'error_chain_tries'} and $self->{'errors_chain'} > $self->{'error_chain_tries'} );
$self->log( 'err', 'err_parse: IMPOSIBLE! !$err and !$self->{sth}' ), $self->err('sth'), return 0
if $sth and ( !$err and !$self->{'sth'} );
$self->{'errors_chain'} = 0, return $self->err(0) if !$err and $self->{'dbh'};
++$self->{'errors_chain'};
++$self->{'errors'};
$self->log( 'err',
"SQL: error[$err,$errstr,$errtype,$state] on executing {$$cmd} [sleep:$self->{'error_sleep'}] dbh=[$self->{'dbh'}]" );
$self->log( 'dev', "err_parse st3 ret0 fatal=$errtype" ), $self->err($errtype), return (0)
if $errtype and grep { $errtype eq $_ } qw(fatal syntax ignore);
$self->log( 'dev', "err_parse sleep($self->{'error_sleep'}), ret1 ", );
$self->sleep( $self->{'error_sleep'}, 'sql_parse' ) if $self->{'error_sleep'};
$self->log( 'dev', "err_parse st3 ret1 fatal=$errtype" ), return $self->err($errtype)
if $errtype and grep { $errtype eq $_ } qw(retry);
if ( $errtype eq 'install_db' and $self->{'auto_install_db'}-- > 0 ) {
$self->log( 'info', "SQL: trying automatic install db" );
$self->create_databases(@_);
return $self->err($errtype);
}
$self->log( 'info', "SQL: trying reconnect[$self->{'connected'}]" ), $self->reconnect(), return $self->err('dbh')
if !$self->{'dbh'};
if ( $errtype eq 'install' or $errtype eq 'upgrade' ) {
if ( $self->{'auto_install'}-- > 0 ) {
$self->log( 'dev', "SQL:install err " );
$self->log( 'info', "SQL: trying automatic install" );
$self->$errtype();
return $self->err($errtype);
} else {
$self->log( 'dev', "SQL:NOinstall err " );
$self->err($errtype);
return (0);
}
}
$self->log( 'err', "SQL: connection error, trying reconnect and retry last query" ), $self->dropconnect(),
$self->reconnect(), return $self->err($errtype)
if $errtype eq 'connection';
if ( $self->{'auto_repair'} and $errtype eq 'repair' ) {
my ($repair) = $errstr =~ /'(?:.*[\\\/])*(\w+)(?:\.my\w)?'/i;
$repair = $self->{'current_table'} unless %{ $self->{'table'}{$repair} || {} };
if ( $self->{'auto_repairs'}{$repair} < $self->{'auto_repairs_max'} ) {
my $sl = int( rand( $self->{'auto_repair'} + 1 ) );
$self->log( 'info', 'pre repair sleeping', $sl );
$self->sleep($sl);
if ( $sl == 0 or $self->{'force_repair'} ) {
$self->log( 'info', 'denied repair', $repair ), return $self->err(1)
if $self->{'auto_repair_selected'}
and ( !$repair or $self->{'auto_repair_selected'} and $self->{'table_param'}{$repair}{'no_auto_repair'} );
++$self->{'auto_repairs'}{$repair};
$self->log( 'info', "SQL: trying automatic repair", $repair );
$self->repair($repair);
$self->{'rep_ext'} = $self->{'rep_frm'} = 1;
$self->{'rep_quick'} = 0;
return $self->err($errtype);
}
}
}
$self->log( 'dev', "err_parse st2 ret1 no dbh", $err, $errstr ), return $self->err('dbh') if !$self->{'dbh'};
$self->log( 'dev', "err_parse unknown error ret($self->{'err_retry_unknown'}), end: [$err], [$errstr], [$errtype]" );
return $self->err( $self->{'err_retry_unknown'} );
};
$self->{'install'} ||= sub {
my $self = shift;
return $self->create_databases(@_) + $self->create_tables();
};
$self->{'create_database'} ||= sub {
my $self = shift;
my $ret;
local $_;
local @_ = ( $self->{'database'} ) unless @_;
$self->drh_init() if ( $self->{'use_drh'} );
for my $db (@_) {
if ( $self->{'use_drh'} ) {
$ret += $_ = $self->{'drh'}->func( 'createdb', $db, $self->{'host'}, $self->{'user'}, $self->{'pass'}, 'admin' );
} elsif ( $self->{'driver'} =~ /pg/i ) {
{
my $db = $self->{'dbname'};
local $self->{'dbname'} = 'postgres';
local $self->{'in_connect'} = undef;
$self->do("CREATE DATABASE $rq$db$rq WITH ENCODING $vq$self->{'cp'}$vq");
}
$self->reconnect();
}
$self->log( 'info', 'install database ', $db, '=', $ret );
}
return $ret;
};
$self->{'create_databases'} ||= sub {
my $self = shift;
return $self->create_database( $self->{'database'} );
};
$self->{'create_tables'} ||= sub {
my $self = shift;
my (%table) = %{ $self->{'table'} or {} };
my @ret;
for my $tab ( sort keys %table ) {
$self->log( 'dev', 'creating table', $tab );
push( @ret, $self->{'create_table'}->( $self, $tab, $table{$tab} ) );
push( @ret, $self->{'create_index'}->( $self, $tab, $table{$tab} ) ) unless $self->{'index in create table'};
}
return @ret;
};
$self->{'create_table'} ||= sub {
my $self = shift;
my ( $tab, $table ) = @_;
my ( @subq, @ret );
return undef if $tab =~ /^\W/;
my ( @primary, %unique, %fulltext, @do );
for my $row ( sort { $table->{$b}{'order'} <=> $table->{$a}{'order'} } keys %$table ) {
push( @primary, $rq . $row . $rq ) if $table->{$row}{'primary'}
;
push( @{ $fulltext{ $table->{$row}{'fulltext'} } }, $rq . $row . $rq ) if $table->{$row}{'fulltext'};
push( @{ $unique{ $table->{$row}{'unique'} } }, $rq . $row . $rq )
if $table->{$row}{'unique'} and $table->{$row}{'unique'} =~ /\D/;
}
if ( $self->{'driver'} =~ /pg/i and $self->{'use_fulltext'} ) {
1 || $self->{'fulltext_trigger'}
? push(
@do,
"DROP TRIGGER $self->{'IF EXISTS'} ${tab}_update_$_ ON $tab",
$self->{'old_fulltext'}
? ( "CREATE TRIGGER ${tab}_update_$_ BEFORE UPDATE OR INSERT ON $tab FOR EACH ROW EXECUTE PROCEDURE tsearch2($rq$_$rq, "
. ( join( ', ', @{ $fulltext{$_} || [] } ) )
. ")" )
: (
"CREATE TRIGGER ${tab}_update_$_ BEFORE UPDATE OR INSERT ON $tab FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger($rq$_$rq, ${vq}$self->{'fulltext_config'}${vq}, "
. ( join( ', ', @{ $fulltext{$_} || [] } ) )
. ")" )
)
: (),
$table->{$_} = { 'order' => -9999, 'type' => 'tsvector', } for keys %fulltext;
}
for my $row ( grep { keys %{ $table->{$_} } } keys %$table ) {
$table->{$row}{'varchar'} = 1 if $table->{$row}{'type'} =~ /^varchar$/i;
}
for my $row ( sort { $table->{$b}{'order'} <=> $table->{$a}{'order'} } grep { keys %{ $table->{$_} } } keys %$table ) {
next if $row =~ /^\W/;
$table->{$row}{'length'} = psmisc::min( $self->{'varchar_max'}, $table->{$row}{'length'} );
my $length = $table->{$row}{'length'};
if ( !defined $length ) {
{
my ( @types, @maxs, );
push @types, 'primary' if $table->{$row}{'primary'} and $table->{$row}{'type'} =~ /char/i;
push @types, 'fulltext' if $table->{$row}{'fulltext'};
push @types, 'unique' if $table->{$row}{'unique'};
push( @types, 'varchar' ) if $table->{$row}{'varchar'};
last unless @types;
for my $type (@types) {
my $max;
$max = $self->{ $type . '_max' };
$max /= 3 if $self->{'codepage'} eq 'utf-8' and $self->{'driver'} =~ /mysql/;
my $same;
my $nowtotal;
for (
grep {
$_
} keys %{$table}
)
{
$nowtotal += 2 if $type eq 'varchar' and $table->{$_}{'type'} =~ /^smallint$/i;
$nowtotal += 4 if $type eq 'varchar' and $table->{$_}{'type'} =~ /^int$/i;
$nowtotal += 8 if $type eq 'varchar' and $table->{$_}{'type'} =~ /^bigint$/i;
next unless $table->{$_}{$type} eq $table->{$row}{$type};
next if !( $table->{$_}{$type} and $_ ne $row );
$nowtotal += $table->{$_}{'length'};
++$same,
if !( $table->{$_}{'length'} );
}
$max -= $nowtotal;
my $want = $max / ( $same + 1 );
$nowtotal = 0;
for (
grep {
$table->{$_}{$type}
and $_ ne $row
and $table->{$_}{$type} eq $table->{$row}{$type}
and !$table->{$_}{'length'}
} keys %{$table}
)
{
--$same,
$max -= $table->{$_}{'length_max'}, $nowtotal += $table->{$_}{'length_max'},
if $table->{$_}{'length_max'} and $table->{$_}{'length_max'} < $want;
}
$max /= $same + 1 if $same;
$max = int($max);
push @maxs, $max;
}
push @maxs, $table->{$row}{'length_max'} if $table->{$row}{'length_max'};
push @maxs, $self->{'varchar_max'} if $table->{$row}{'type'} =~ /^varchar$/i;
push @maxs, 1000 / 3
if $table->{$row}{'type'} =~ /^varchar$/i
and $table->{$row}{'primary'}
and $self->{'codepage'} eq 'utf-8';
$length = psmisc::min( grep { $_ > 0 } @maxs );
$table->{$row}{'length'} ||= $length;
Hide Show 19 lines of Pod
$length = int($length);
}
}
push(
@subq,
$rq
. $row
. $rq
. " $table->{$row}{'type'} "
. ( $length ? "($length) " : '' )
. ( ( $table->{$row}{'unsigned'} and $self->{'UNSIGNED'} ) ? ' ' . $self->{'UNSIGNED'} : '' )
. ( (
!$table->{$row}{'auto_increment'}
)
? ( ( $table->{$row}{'not null'} ) ? ' NOT NULL ' : '' )
: ''
)
. (
( defined( $table->{$row}{'default'} ) and !$table->{$row}{'auto_increment'} )
? " DEFAULT " . ( $table->{$row}{'default'} eq 'NULL' ? 'NULL' : "$vq$table->{$row}{'default'}$vq" ) . " "
: ''
)
. ( ( $table->{$row}{'unique'} and $table->{$row}{'unique'} =~ /^\d+$/ ) ? ' UNIQUE ' : '' )
. ( (
$table->{$row}{'auto_increment'} and (
1
)
)
? ' '
. $self->{'AUTO_INCREMENT'} . ' '
: ''
)
. "$table->{$row}{'param'}"
);
}
push( @subq, "PRIMARY KEY (" . join( ',', @primary ) . ")" ) if @primary;
for my $row ( sort { $table->{$b}{'order'} <=> $table->{$a}{'order'} } keys %$table ) {
push(
@subq,
"INDEX "
. $rq
. $row
. $self->{'index_postfix'}
. $rq . " ("
. $rq
. $row
. $rq
. (
( $table->{$row}{'index'} > 1 and $table->{$row}{'index'} < $table->{$row}{'length'} )
? '(' . $table->{$row}{'index'} . ')'
: ''
)
. ")"
) if $table->{$row}{'index'} and $self->{'index in create table'};
Hide Show 17 lines of Pod
push( @primary, $rq . $row . $rq ) if $table->{$row}{'primary'};
}
push( @subq, "UNIQUE " . ( $self->{'unique name'} ? $rq . $_ . $rq : '' ) . " (" . join( ',', @{ $unique{$_} } ) . ")" )
for grep @{ $unique{$_} }, keys %unique;
if ( $self->{'index in create table'} ) {
push( @subq, "FULLTEXT $rq$_$rq (" . join( ',', @{ $fulltext{$_} } ) . ")" ) for grep @{ $fulltext{$_} }, keys %fulltext;
}
return
map { $self->do($_) }
grep { $_ }
( !@subq
? ()
: 'CREATE TABLE '
. $self->{'IF NOT EXISTS'}
. " $tq$self->{'table_prefix'}$tab$tq ("
. join( ",", @subq )
. ( join ' ', '', grep { $_ } $self->{'table_constraint'}, $self->{'table_param'}{$tab}{'table_constraint'} ) . ") "
. $self->{'table options'} . ' '
. $self->{'table_param'}{$tab}{'table options'}
. ( $self->{'cp'} && $self->{'DEFAULT CHARACTER SET'} ? " $self->{'DEFAULT CHARACTER SET'} $vq$self->{'cp'}$vq " : '' )
. ';' ), @do;
};
$self->{'create_index'} ||= sub {
my $self = shift;
my @ret;
my ( $tab, $table ) = @_;
my (@subq);
for my $row ( sort { $table->{$b}{'order'} <=> $table->{$a}{'order'} } keys %$table ) {
next if $row =~ /^\W/;
push( @ret,
'CREATE INDEX '
. $self->{'index_IF NOT EXISTS'} . ' '
. $rq
. $row
. ( $self->{'index_name_table'} ? '_' . $tab : '' )
. $self->{'index_postfix'}
. $rq . ' ON '
. " $tq$self->{'table_prefix'}$tab$tq ( $rq$row$rq )" )
if $table->{$row}{'index'};
}
return $self->do(@ret);
};
$self->{'create_indexes'} ||= sub {
$self->create_index( $_, $self->{'table'}{$_} ) for keys %{ $self->{'table'} };
};
$self->{'drop_table'} ||= sub {
my $self = shift;
my @ret;
for my $tab (@_) {
my ($sql);
next if $tab =~ /^\W/ or $tab !~ /\w/;
$sql .= "DROP TABLE " . $self->{'IF EXISTS'} . " $tq$self->{'table_prefix'}$tab$tq $self->{'CASCADE'}";
push( @ret, $sql );
}
return $self->do(@ret);
};
$self->{'drop_database'} ||= sub {
my $self = shift;
my @ret;
@_ = $self->{'database'} if !@_;
my $rec = 1 if $self->{'driver'} =~ /pg/i and grep { $self->{'database'} eq $_ } @_;
if ($rec) {
local $self->{'dbname'} = undef;
local $self->{'database'} = undef;
$self->{'dbname'} = $self->{'database'} = 'postgres' if $self->{'driver'} =~ /pg/i;
$self->reconnect();
}
for my $tab (@_) {
my ($sql);
next if $tab =~ /^\W/ or $tab !~ /\w/;
$sql .= "DROP DATABASE " . $self->{'IF EXISTS'} . " $tq$self->{'table_prefix'}$tab$tq";
push( @ret, $sql );
}
@ret = $self->do(@ret);
if ($rec) { $self->reconnect(); }
return @ret;
};
$self->{'drop_tables'} ||= sub {
my $self = shift;
@_ = keys %{ $self->{'table'} or {} } if !@_;
return $self->drop_table(@_);
};
$self->{'insert_fields'} ||= sub {
my $self = shift;
my $table = shift || $self->{'current_table'};
return grep {
$self->{'table'}{$table}{$_}{'array_insert'}
} keys %{ $self->{'table'}{$table} };
};
$self->{'insert_order'} ||= sub {
my $self = shift;
my $table = shift || $self->{'current_table'};
return sort { $self->{'table'}{$table}{$b}{'order'} <=> $self->{'table'}{$table}{$a}{'order'} } $self->insert_fields($table)
};
$self->{'insert_cached'} ||= sub {
my $self = shift;
my $table = shift;
my $table_insert = $table || $self->{'current_table'};
my @dummy;
++$self->{'inserts'}, ++$self->{'table_updated'}{$table_insert}, push( @{ $self->{'insert_buffer'}{$table_insert} }, \@_ )
if $table_insert and @_;
for my $table ( $table ? ($table) : ( keys %{ $self->{'insert_buffer'} } ) ) {
$self->{'insert_block'}{$table} //= $self->{'table_param'}{$table}{'insert_by'} || $self->{'insert_by'};
if (
$self->{'insert_block'}{$table}-- <= 1
or !scalar(@_)
or time() - ( $self->{'insert_buffer_time'}{$table} ||= time() ) > $self->{'insert_cached_time'}
)
{
$self->{'stat'}{'time'}{'count'} += scalar @{ $self->{'insert_buffer'}{$table_insert} || [] };
$self->{'insert_buffer_time'}{$table} = time();
$self->{'current_table'} = $table;
$self->do(
join(
'',
( $self->{'ON DUPLICATE KEY UPDATE'} ? $self->{'INSERT'} : $self->{'REPLACE'} )
. " $self->{$self->{'insert_options'}} INTO $tq$self->{'table_prefix'}$table$tq (",
join( ',', map { $rq . $_ . $rq } $self->insert_order($table) ),
") VALUES\n",
join(
",\n",
map {
join(
'', '(',
join(
',',
map { $self->quote( scalar psmisc::cp_trans( $self->{'cp_in'}, $self->{'codepage'}, $$_ ) ) }
@{$_}[ 0 .. scalar( $self->insert_fields($table) ) - 1 ],
@dummy =
( map { \$self->{'table'}{$table}{$_}{'default'} } $self->insert_order($table) )
[ scalar( @{$_} ) .. scalar( $self->insert_fields($table) ) - 1 ]
),
')'
)
} @{ $self->{'insert_buffer'}{$table} }
), (
!$self->{'ON DUPLICATE KEY UPDATE'} ? '' : " \n" . $self->{'ON DUPLICATE KEY UPDATE'} . ' ' . join(
',',
map {
$rq . $_ . $rq . '=VALUES(' . $rq . $_ . $rq . ')'
} sort {
$self->{'table'}{$table}{$b}{'order'} <=> $self->{'table'}{$table}{$a}{'order'}
} grep {
$self->{'table'}{$table}{$_}{'array_insert'}
and !$self->{'table'}{$table}{$_}{'no_insert_update'}
and !$self->{'table'}{$table}{$_}{'added'}
} keys %{ $self->{'table'}{$table} }
)
),
';'
)
),
delete $self->{'insert_buffer'}{$table}
if scalar @{ $self->{'insert_buffer'}{$table} || [] };
$self->{'insert_block'}{$table} = $self->{'table_param'}{$table}{'insert_by'} || $self->{'insert_by'};
$self->{'stat'}{'time'}{'time'} += time - $self->{'insert_buffer_time'}{$table};
psmisc::schedule(
[ $self->{'stat_every'}, $self->{'stat_every'} ],
sub {
$self->log(
'time',
'inserts',
$self->{'stat'}{'time'}{'count'},
'per',
psmisc::human( 'time_period', $self->{'stat'}{'time'}{'time'} ),
'at',
psmisc::human( 'float', $self->{'stat'}{'time'}{'count'} / ( $self->{'stat'}{'time'}{'time'} || 1 ) ),
'rps',
'full',
psmisc::human( 'float', $self->{'stat'}{'time'}{'count'} / ( time - $self->{'stat'}{'time'}{'full'} or 1 ) ),
'rps'
);
$self->{'stat'}{'time'} = { 'full' => time };
}
) if $self->{'stat_every'};
}
}
return undef;
};
$self->{'flush_insert'} ||= sub {
my $self = shift;
$self->insert_cached(@_);
if ( 0 and $self->{'driver'} =~ /pg/i and $self->{'use_fulltext'} ) {
for my $tablen ( grep { $_ and $self->{'table_updated'}{$_} } keys %{ $self->{'table_updated'} || {} } ) {
my $table = $self->{'table'}{$tablen};
my (%fulltext);
for my $row ( sort { $table->{$b}{'order'} <=> $table->{$a}{'order'} } keys %$table ) {
push( @{ $fulltext{ $table->{$row}{'fulltext'} } }, $rq . $row . $rq ) if $table->{$row}{'fulltext'};
}
my @do;
push @do,
"SELECT tsvector_update_trigger($rq$_$rq, ${vq}$self->{'fulltext_config'}${vq}, "
. ( join( ', ', @{ $fulltext{$_} || [] } ) )
. ") FROM $tq$tablen$tq"
for keys %fulltext;
$self->do(@do);
$self->{'table_updated'}{$tablen} = 0;
}
}
};
$self->{'insert'} ||= sub {
my $self = shift;
my @ret = $self->insert_cached(@_);
$self->flush_insert( $_[0] ) if scalar @_ > 1;
return @ret;
};
$self->{'update'} ||= sub {
my $self = shift;
my $table = ( shift or $self->{'current_table'} );
my ( $by, $values, $where, $set, $setignore, $whereignore ) = @_;
return unless %{ $self->{'table'}{$table} or {} };
$self->{'current_table'} = $table;
next
if ref $self->{'table_param'}{$table}{'filter'} eq 'CODE'
and $self->{'table_param'}{$table}{'filter'}->( $self, $values );
$self->{'handler_insert'}->( $table, $values ) if ref $self->{'handler_insert'} eq 'CODE';
$self->stem_insert( $table, $values );
local $self->{'handler_insert'} = undef;
local $self->{'stem_insert'} = sub { };
local @_;
$by ||=
[ grep { $self->{'table'}{$table}{$_}{'primary'} or $self->{'table'}{$table}{$_}{'unique'} }
keys %{ $self->{'table'}{$table} || {} } ];
my $bymask = '^(' . join( ')|(', @$by ) . ')$';
my $bywhere = join(
' AND ',
map ( "$rq$_$rq=" . $self->quote( $values->{$_} ),
grep {
%{ $self->{'table'}{$table}{$_} || {} }
and ( $self->{'table'}{$table}{$_}{'primary'} or $self->{'table'}{$table}{$_}{'unique'} )
and $self->{'table'}{$table}{$_}{'type'} ne 'serial'
and !$self->{'table'}{$table}{$_}{'auto_increment'}
} @$by )
);
$set ||= join(
', ', (
map {
$rq . $_ . $rq . "=" . $self->quote(
$self->cut( $values->{$_}, $self->{'table'}{$table}{$_}{'length'} )
)
} (
@_ = grep( ( ( $_ !~ $bymask ) and $_ and %{ $self->{'table'}{$table}{$_} || {} } and defined( $values->{$_} ) ),
keys %$values ), (
@_ ? () : grep {
$_ and %{ $self->{'table'}{$table}{$_} or {} } and defined( $values->{$_} )
} keys %$values
)
)
)
);
$set = 'SET ' . $set if $set;
my $lwhere = $where;
$where = '' if $where eq 1;
$where = ' AND ' . $where if $where and $bywhere;
$whereignore = ' AND ' . $whereignore if $whereignore and ( $where or $bywhere );
local $_;
$_ = $self->do(
"UPDATE $self->{$self->{'update_options'}} $self->{'IGNORE'} $tq$self->{'table_prefix'}$table$tq $set $setignore WHERE $bywhere $where $whereignore"
)
if ( $set or $lwhere or !$self->{'ON DUPLICATE KEY UPDATE'} )
and ( $bywhere or $where or $whereignore );
$self->insert_data( $table, $values ),
$self->flush_insert($table) if ( !$set or !int($_) ) and !$lwhere;
return undef;
};
$self->{'insert_hash'} ||= sub {
my $self = shift;
return $self->insert_data(@_) unless $self->{'driver'} =~ /pg/i;
my $table = shift || $self->{'current_table'};
my $ret;
for (@_) {
$ret += $self->update( $table, undef, $_ );
}
return $ret;
};
$self->{'cut'} ||= sub {
my $self = shift;
return $_[0] unless $_[1];
return $_[0] = substr( $_[0], 0, $_[1] );
};
$self->{'insert_data'} ||= sub {
my $self = shift;
my $table = ( shift or $self->{'current_table'} );
for my $hash (@_) {
next if !$hash;
$hash->{$_} = (
$self->{'table'}{$table}{$_}{'default_insert'}
or ( $self->{'table'}{$table}{$_}{'array_insert'} ? $self->{'table'}{$table}{$_}{'default'} : undef )
),
for grep { !defined $hash->{$_} }
keys %{ $self->{'table'}{$table} };
next if
grep { $self->{'table'}{$table}{$_}{'insert_min'} and !$hash->{$_} } keys %{ $self->{'table'}{$table} };
$self->handler_insert0( $table, $hash );
next
if ref $self->{'table_param'}{$table}{'filter'} eq 'CODE'
and $self->{'table_param'}{$table}{'filter'}->( $self, $hash );
$self->handler_insert( $table, $hash );
$self->stem_insert( $table, $hash );
$self->cut( $hash->{$_}, $self->{'table'}{$table}{$_}{'length'} )
for grep {
( $self->{'table'}{$table}{$_}{'type'} eq $self->{'char_type'} )
and $self->{'table'}{$table}{$_}{'length'}
and length( $hash->{$_} ) >
( $self->{'table'}{$table}{$_}{'length'} )
} keys %{ $self->{'table'}{$table} };
local $self->{'table'}{$table} = $self->{'table'}{$table};
my $chanded;
(
++$chanded == 1
? (
$self->flush_insert($table)
)
: ()
),
$self->{'table'}{$table}{$_}{'array_insert'} = 1
for grep {
defined $hash->{$_}
and length $hash->{$_}
and keys %{ $self->{'table'}{$table}{$_} } and !$self->{'table'}{$table}{$_}{'array_insert'}
} keys %{ $self->{'table'}{$table} };
$self->insert_cached(
$table,
\@{$hash}{
$self->insert_order($table)
}
);
$self->handler_insert2( $table, $hash );
}
return undef;
};
$self->{'insert_hash_hash'} ||= sub {
my $self = shift;
my $table = ( shift or $self->{'current_table'} );
for my $hash (@_) { $self->insert_hash( $table, values %$hash ); }
return undef;
};
$self->{'q_file'} ||= sub {
my $self = shift;
my $table = shift || $self->{'current_table'};
my $search_str = shift;
my %tparam;
if ( $self->{'table'}{$table}{'name'}
and $self->{'table'}{$table}{'ext'}
and $search_str =~ m{^([^/|"]+[^\s/|"])\.([^/\."|]+)$} ) #"
{
%tparam = ( 'name' => $1, 'ext' => $2 );
} elsif ( $self->{'table'}{$table}{'tiger'} and $search_str =~ /^\s*([A-Z0-9]{39})\s*$/i ) {
%tparam = ( 'tiger' => uc $1 );
}
return %tparam;
};
$self->{'where_body'} ||= sub {
my $self = shift;
my ( $param_orig, $param_num, $table, $after ) = @_;
my $param = { %{ $param_orig || {} } };
$table ||= $self->{'current_table'};
my ( $search_str_add, $ask, $close );
my $questions = 0;
map ++$questions, grep defined( $param->{ $_ . $param_num } ),
@{ $config{'user_param_founded'} || [ 'q', keys %{ $self->{'table'}{$table} } ] };
return if ( $param_num and !$questions ) or ++$self->{'rec_stop'} > 20;
my $first = 1;
my $local_cond = 0;
while ( defined( $param->{ 'q' . $param_num } ) and $param->{ 'q' . $param_num } =~ s/(\w+\S?[=:](?:".+?"|\S+))// ) {
local $_ = $1;
s/^(\S+):/$1=/;
my $lparam = get_params_one( undef, $_ );
$lparam->{$_} =~ s/^"|"$//g, $param->{$_} = $lparam->{$_} for keys %$lparam;
}
for my $preset ( $param->{ 'q' . $param_num } =~ /:(\S+)/g ) {
for my $sets ( keys %{ $config{'preset'} } ) {
if ( $config{'preset'}{$sets}{$preset} ) {
$param->{ 'q' . $param_num } =~ s/:$preset//;
for ( keys %{ $config{'preset'}{$sets}{$preset}{'set'} } ) {
$param->{ $_ . $param_num } .=
( $param->{ $_ . $param_num } ? ' ' : '' ) . $config{'preset'}{$sets}{$preset}{'set'}{$_};
}
}
}
}
my $search_str = $param->{ 'q' . $param_num };
my $glueg = $param->{ 'glueg' . $param_num } eq 'or' ? ' OR ' : ' AND ';
my $gluel = $param->{ 'gluel' . $param_num } eq 'or' ? ' OR ' : ' AND ';
$glueg = ' XOR ' if $self->{'enable_xor_query'} and $param->{ 'glueg' . $param_num } eq 'xor';
$gluel = ' XOR ' if $self->{'enable_xor_query'} and $param->{ 'gluel' . $param_num } eq 'xor';
if ( my ($days) = $param->{ 'search_days' . $param_num } =~ /(\d+)/ and $1 and %{ $self->{'table'}{$table}{'time'} or {} } )
{
$ask .= " " . ( $self->{'no_column_prepend_table'} ? () : "$tq$self->{'table_prefix'}$table$tq." ) . "$rq" . "time$rq ";
if ( $param->{ 'search_days_mode' . $param_num } eq 'l' ) { $ask .= '<'; }
else { $ask .= '>'; }
$days = int( time() ) - $days * 24 * 60 * 60;
$ask .= '= ' . ( $self->{'sphinx'} ? $days : $self->squotes($days) );
}
if ( !$self->{'no_online'} and defined( $param->{ 'online' . $param_num } ) ) {
if ( $param->{ 'online' . $param_num } eq 'on' ) { $param->{ 'online' . $param_num } = $config{'online_minutes'}; }
if ( $param->{ 'online' . $param_num } > 0 ) {
$param->{ 'live' . $param_num } = int( time() ) + $self->{'timediff'} - int( $param->{ 'online' . $param_num } ) * 60;
$param->{ 'live_mode' . $param_num } = 'g';
}
}
if (
$self->{'path_complete'}
and $param->{ 'path' . $param_num }
and !( $param->{ 'path' . $param_num } =~ /^[ !\/\*]/ )
and ( $param->{ 'path' . $param_num } ne 'EMPTY' )
and !( (
!$self->{'no_regex'}
and
( $param->{ 'path' . $param_num } =~ /^\s*reg?e?x?p?:\s*/i or $param->{ 'path' . '_mode' . $param_num } =~ /[r~]/i )
)
)
)
{
$search_str_add .= ' /' . $param->{ 'path' . $param_num } . '/ ';
delete $param->{ 'path' . $param_num };
}
for my $item ( (
sort {
$self->{'table'}{$table}{$b}{'weight'} <=> $self->{'table'}{$table}{$a}{'weight'}
|| $self->{'table'}{$table}{$b}{'order'} <=> $self->{'table'}{$table}{$a}{'order'}
} grep {
$self->{'nav_all'}
or $self->{'table'}{$table}{$_}{'nav_num_field'}
or $self->{'table'}{$table}{$_}{'nav_field'}
or $self->{'table'}{$table}{$_}{'nav_hide'}
} keys %{ $self->{'table'}{$table} }
),
@{ $self->{'table_param'}{$table}{'join_fields'} }
)
{
next
if $self->{'no_index'}
or $self->{'ignore_index'}
or $self->{'table_param'}{$table}{'no_index'}
or $self->{'table_param'}{$table}{'ignore_index'}
or $param->{ $item . $param_num } !~ /\S/;
my $lask;
++$local_cond, $lask .= $gluel if $ask;
my $pib = $param->{ $item . $param_num };
$pib =~ s/^\s*|\s*$//g;
my ( $group_not, $group_not_close );
$pib =~ s/\:$_(\W|$)/$config{$item}{$_}{'to'}$1/g and ++$group_not
for grep { defined $config{$item}{$_}{'to'} } keys %{ ref $config{$item} eq 'HASH' ? $config{$item} : {} };
next if $pib eq '';
my ( $brstr, $space );
if ( $self->{'table'}{$table}{$item}{'no_split_space'}
or ( !$self->{'no_regex'} and ( $pib =~ /\s*reg?e?x?p?:\s*/ or $param->{ $item . '_mode' . $param_num } =~ /[r~]/i ) ) )
{
$space = '\s+';
} else {
$brstr = '|\s+';
}
$brstr = $space . '\&+' . $space . '|' . $space . '\|+' . $space . '|(\s+AND\s+)|\s+OR\s+' . $brstr;
my $num_cond = 0;
my $next_cond;
my $llask;
do {
my ( $pi, $cond );
$cond = $next_cond;
if ( $pib =~ /($brstr)/ ) { ( $pib, $pi, $next_cond ) = ( $', $`, $1 ); }
else { $pi = $pib, $pib = ''; }
if ( $num_cond++ ) {
if ( $cond =~ /(and)|\&+/i ) { $llask .= ' AND '; }
elsif ( $self->{'enable_xor_query'} and $cond =~ /(xor)/i ) { $llask .= ' XOR '; }
elsif ( $cond =~ /(or)|\|+|\s+|^$/i ) { $llask .= ' OR '; }
}
my $not = 1 if ( !$self->{'no_slow'} or $self->{'table'}{$table}{$item}{'fast_not'} ) and ( $pi =~ s/^\s*[\!\-]\s*//g );
$llask .= ' NOT ' . ( $group_not ? ( ++$group_not_close, ' ( ' ) : '' ) if $not;
if ( $self->{'table_param'}{$table}{'name_to_base'}{$item} ) {
$llask .= ' ' . $self->{'table_param'}{$table}{'name_to_base'}{$item} . ' ';
} else {
$llask .=
" " . ( $self->{'no_column_prepend_table'} ? () : "$tq$self->{'table_prefix'}$table$tq." ) . "$rq$item" . "$rq ";
}
my ($dequote_);
if ( !$self->{'no_regex'}
and ( $pi =~ s/^\s*reg?e?x?p?:\s*//ig or $param->{ $item . '_mode' . $param_num } =~ /[r~]/i ) )
{
$llask .= ' REGEXP ';
} elsif ( !$self->{'no_soundex'}
and ( $pi =~ s/^\s*sou?n?d?e?x?:\s*//ig or $param->{ $item . '_mode' . $param_num } =~ /[s@]/i ) )
{
$llask .= ' SOUNDS LIKE ';
} elsif ( $pi =~ /[*?]/ ) {
$pi =~ s/%/\\%/g;
$pi =~ s/_/\\_/g and ++$dequote_;
$pi =~ tr/*?/%_/;
next if $self->{'no_empty'} and ( $pi !~ /\S/ or $pi =~ /^\s*[%_]+\s*$/ );
$llask .= ' LIKE ';
}
elsif ( $param->{ $item . '_mode' . $param_num } =~ /notnull/i ) { $llask .= 'IS NOT NULL'; next; }
elsif ( $param->{ $item . '_mode' . $param_num } =~ /null/i ) { $llask .= 'IS NULL'; next; }
elsif ( $param->{ $item . '_mode' . $param_num } =~ /[g>]/i ) { $llask .= ( $not ? '<' : '>' ) . '= '; }
elsif ( $param->{ $item . '_mode' . $param_num } =~ /[l<]/i ) { $llask .= ( $not ? '>' : '<' ) . '= '; }
else { $llask .= '= '; }
$pi =~ s/(^\s*)|(\s*$)//g;
$pi = psmisc::human( 'number_k', $pi ) if $item eq 'size';
$work{ 'bold_' . $item } .= ' ' . $pi;
if ( !( $self->{'sphinx'} and $self->{'table'}{$table}{$item}{'nav_num_field'} and $pi =~ /^\d+$/ ) ) {
$pi = ( $pi ne 'EMPTY' ? $self->squotes($pi) : $self->squotes('') );
}
$pi =~ s|\\_|\_|g if $dequote_;
$llask .= $pi;
} while ( $pib and $num_cond < 50 );
$llask .= " ) " x $group_not_close;
$group_not_close = 0;
$lask .= ( $num_cond > 1 ? ' ( ' : '' ) . $llask . ( $num_cond > 1 ? ' ) ' : '' );
$ask .=
( ( !$self->{'no_slow'} or $self->{'table'}{$table}{$item}{'fast_not'} )
and $param->{ $item . '_mode' . $param_num } =~ /[n!]/i ? ' NOT ' : ' ' )
. $lask;
}
$work{'search_str'} .= ' ' . $search_str . ' ' . $search_str_add;
if ( $search_str =~ /\S/ or $search_str_add ) {
unless ( $param->{'page'} > 1 or $param->{'order'} or $param->{'no_querystat'} ) {
++$work{'query'}{$search_str};
map { ++$work{'word'}{$_} } grep $_, split /[\W_]+/, $search_str;
}
++$local_cond, $ask .= $gluel if $ask;
$param->{ 'adv_query' . $param_num } = 'on'
if $search_str =~ /\S+\*+\s*/
or $search_str =~ /(^|\s+)(([+\-><~]+\()|\")[^"()]*\S+\s+\S+[^"()]*[\"\)]($|\s+)/
or $search_str =~ /(^|\s+)[\~\+\-\<\>]\S+/;
$search_str =~ s/(\S+)/\+$1/g
if $param->{ 'adv_query' . $param_num } eq 'on'
and !( $search_str =~ /((^|\s)\W+\S)|\S\W+(\s|$)/ )
and $search_str =~ /\s/;
$ask .= ( $search_str =~ s/^\s*\!\s*// ? ' NOT ' : '' );
if ( !$self->{'use_q_file_fallback'} and my %tparam = $self->q_file( $table, $search_str ) ) {
$ask .= ' ( ' . $self->where_body( \%tparam, undef, $table ) . ' ) ';
} elsif ( !$self->{'sphinx'}
and !$self->{'no_slow'}
and $search_str =~ /^\s*\*+\S+/
and $self->{'table'}{$table}{'path'}
and $self->{'table'}{$table}{'name'}
and $self->{'table'}{$table}{'ext'} )
{
my %tparam = ( 'path' => '/' . $search_str, 'name' => $search_str, 'ext' => $search_str, 'gluel' => 'or' );
$ask .= ' ( ' . $self->where_body( \%tparam, undef, $table ) . ' ) ';
} else {
$search_str .= $search_str_add;
$self->{'handler_search_str'}->( $table, \$search_str ) if ref $self->{'handler_search_str'} eq 'CODE';
my $search_str_stem = $self->stem($search_str)
if grep { $self->{'table'}{$table}{$_}{'stem'} } keys %{ $self->{'table'}{$table} };
local $param->{ 'adv_query' . $param_num } = 'on'
if $self->{'ignore_index'}
or $self->{'table_param'}{$table}{'ignore_index'};
if ( (
!$param->{ 'adv_query' . $param_num } and (
$self->{'ignore_index_fulltext'} or !grep {
$self->{'table'}{$table}{$_}{'fulltext'}
or ( $self->{'sphinx'} and $self->{'table'}{$table}{$_}{'sphinx'} )
} keys %{ $self->{'table'}{$table} }
)
)
or !$self->{'match'}
)
{
$_ = join(
' OR ',
map {
"$rq$_$rq LIKE "
. $self->squotes( ( (
!$self->{'no_slow'}
and $self->{'table'}{$table}{$_}{'like_bef'}
|| $self->{'table_param'}{$table}{'like_bef'}
|| $self->{'like_bef'}
) ? '%' : ''
)
. $search_str . '%'
)
} grep {
$self->{'table'}{$table}{$_}{'q'} || $self->{'table'}{$table}{$_}{'nav_field'}
and !$self->{'table'}{$table}{$_}{'q_skip'}
} keys %{ $self->{'table'}{$table} }
);
$ask .= ' ( ' . $_ . ' ) ' if $_;
} else {
$ask .= $self->match( $param, $param_num, $table, $search_str, $search_str_stem );
}
}
}
if ( !$self->{'sphinx'} and $local_cond > 1 ) { $ask = ' ( ' . $ask . ' ) '; }
$ask = $glueg . $ask if $after and $ask;
return
$ask
. ( $ask and $close ? ' ) ' x $close : '' )
. $self->where_body( $param, $param_num + ( defined($param_num) ? 1 : ( $param->{'search_prev'} ? 0 : 1 ) ),
$table, ( $ask ? 1 : 0 ) );
};
$self->{'where'} ||= sub {
my $self = shift;
my ( $param, undef, $table ) = @_;
$self->{'rec_stop'} = 0;
my $where = $self->where_body(@_);
if ( $self->{'table_param'}{$table}{'where_extra'} ) {
$where .= (' AND ') if length $where;
$where .= $self->{'table_param'}{$table}{'where_extra'};
}
return ' WHERE ' . $where if $where;
return undef;
};
$self->{'count'} ||= sub {
my $self = shift;
my ( $param, $table ) = @_;
$self->limit_calc( $self, $param, $table );
return undef
if $self->{'query_count'}{$table}++
or $self->{'ignore_index'}
or $self->{'table_param'}{$table}{'ignore_index'};
my @ask;
$param->{'count_f'} = 'on' if $self->{'page'} eq 'rnd';
push( @ask, ' COUNT(*) ' ) if $param->{'count_f'} eq 'on';
push( @ask, " SUM($tq$table$tq.$rq$_$rq) " )
for grep(
( ( $self->{'allow_count_all'} or $self->{'table'}{$table}{$_}{'allow_count'} ) and $param->{ 'count_' . $_ } eq 'on' ),
sort keys %{ $self->{'table'}{$table} } );
if (@ask) {
my %tmp_para = %$param;
local $self->{'dbirows'};
delete $tmp_para{'online'};
my $where = $self->where( \%tmp_para, undef, $table );
return unless $self->{'allow_null_count'} or $where;
my $from = join ' ', $tq . $self->{'table_prefix'} . $table . $tq, $self->join_what( undef, $param, $table );
my $req = ' SELECT ' . join( ' , ', @ask ) . " FROM $from $where ";
psmisc::flush();
@ask = values %{ $self->query($req)->[0] };
$self->{'stat'}{'found'}{'files'} = pop(@ask) if $param->{'count_f'} eq 'on';
for (
grep( ( $self->{'table'}{$table}{$_}{'allow_count'} and $param->{ 'count_' . $_ } eq 'on' ),
sort keys %{ $self->{'table'}{$table} } )
)
{
my $t = pop(@ask);
$self->{'stat'}{'found'}{$_} = $t if $t;
}
}
$self->{'calc_count'}->( $self, $param, $table );
return undef;
};
$self->{'can_select'} ||= sub {
my $self = shift;
my ( $param, $table, ) = @_;
my $where = $self->where( $param, undef, $table );
return $where if $where;
return '0E0' if $self->{'use_sphinx'} and $self->{'sphinx_dbi'} and length $param->{'q'};
};
$self->{'select'} ||= sub {
my $self = shift;
my ( $table, $param, $opt ) = @_;
$opt ||= {};
$self->{'current_table'} = $table;
my $select;
my $ids = [];
my %id;
my $ret = [];
$self->{'founded_max'} = 0;
my @fail;
my @selects;
my $file_fallback;
my $n;
my $do_select = sub {
my $count;
for my $s (@_) {
my ($select);
my $count_add;
( $select, undef, $count_add ) = $s->() if psmisc::is_code $s;
$count += $count_add;
( $select, ) = $s if psmisc::is_hash $s;
local $self->{'limit_body'} = sub { }
if psmisc::is_array_size $ids;
if ( psmisc::is_hash($select) ) {
for my $s ( sort keys %$select ) {
my $r;
$r =
$self->{'shard_dbis'}{ $select->{$s} }
->query( scalar psmisc::cp_trans( $self->{'cp_in'}, $self->{'codepage'}, $self->select_body( $s, $param ) ) )
if $s;
next unless $r;
map { $_->{id} //= psmisc::join_url($_) } @$r;
$r = [ grep { !$id{ $_->{id} }++ } @$r ];
$count += scalar @$r;
$opt->{row}->(@$r), psmisc::code_run( $opt->{flush} ), next if psmisc::is_code $opt->{row};
push @$ret, @$r;
}
} else {
for my $select ( psmisc::array $select) {
my $r;
$r = $self->query(
scalar psmisc::cp_trans( $self->{'cp_in'}, $self->{'codepage'}, $self->select_body( $select, $param ) ) )
if $select;
next unless $r;
map { $_->{id} //= psmisc::join_url($_) } @$r;
$r = [ grep { !$id{ $_->{id} }++ } @$r ];
$count += scalar @$r;
push @$ret, @$r;
}
}
last if $count >= $self->{'limit'};
} continue {
++$n;
}
return $count;
};
push @selects, sub {
my $ask;
my $search_str = $param->{'q'};
if ( my %tparam = $self->q_file( $table, $search_str ) ) {
$ask .= ' ( ' . $self->where_body( \%tparam, undef, $table ) . ' ) ';
}
}
if $self->{'use_q_file_fallback'} and !$self->{'sphinx'};
push @selects, sub {
my %id;
if ( $self->{'use_sphinx'}
and $self->{'sphinx_dbi'}
and length $param->{'q'}
and ( $file_fallback or !$self->q_file( $table, $param->{'q'} ) ) )
{
( $tq, $rq, $vq ) = $self->quotes();
local $self->{'sphinx_dbi'}->{'option'}{'max_query_time'} = 2000 if $config{'client_bot'};
my $idsl = [];
push @$idsl, grep { !$id{ $_->{id} }++ } @{ $self->{'sphinx_dbi'}->select( $table, $param ) };
$self->{'founded_max'} = $self->{'sphinx_dbi'}{'option'}{'cutoff'};
if (
( @$ids + @$idsl < $self->{'limit'} )
)
{
++$work{'fulltext_fail'} unless @$ids;
local $param->{'q'} = $param->{'q'};
for my $func ( sub { $_[0] =~ s/^\s*"\s*// and $_[0] =~ s/\s*"\s*$// }, sub { $_[0] =~ s/(\w\s+)(\w)/$1 | $2/g }, ) {
if ( $func->( $param->{'q'} ) ) {
local $param->{'no_querystat'} = 1;
my $ids_add = $self->{'sphinx_dbi'}->select( $table, $param );
$self->{'founded_max'} = $self->{'sphinx_dbi'}{'option'}{'cutoff'};
push @fail, { 'n' => scalar @$ids + scalar @$idsl, 'q' => $param->{'q'} } if @$ids_add;
unless (@$ids_add) { ++$work{'fulltext_fail_or'}; }
push @$idsl, grep { !$id{ $_->{id} }++ } @$ids_add;
}
last if @$ids + @$idsl >= $self->{'limit'};
}
}
if (@$idsl) {
my $wheregen = sub {
@_ = psmisc::array @_;
return " WHERE ${rq}id${rq} IN (" . ( join ',', map { $_->{'id'} } @_ ) . ')' if @_;
};
if ( !$self->{'sphinx'} and $self->{'shard'} ) {
my %ids;
for my $r (@$idsl) {
for my $from ( reverse sort keys %{ $self->{'shard_dbis'} } ) {
if ( $r->{id} >= $from ) {
push @{ $ids{$from} ||= [] }, $r;
last;
}
}
}
$select = {};
for my $from ( keys %{ $self->{'shard_dbis'} } ) {
my $w = $ids{$from} || next;
$select->{ $wheregen->($w) } = $from;
}
} else {
$select = $wheregen->($idsl);
}
push @$ids, @$idsl;
}
}
local $self->{'limit_body'} = sub { }
if @$ids;
( $tq, $rq, $vq ) = $self->quotes();
my $count;
if ( !$select and ( !$self->{'use_sphinx'} or !$config{'client_bot'} ) ) {
if ( !$self->{'use_sphinx'} or !$self->{'no_sphinx_like'} ) {
if ( !$self->{'sphinx'} and $self->{'shard'} ) {
for my $from ( sort keys %{ $self->{'shard_dbis'} } ) {
local $self->{'limit_from'} = $self->{'limit_offset'} + $count if $count;
local $self->{'limit_minus'} = $count;
$select = {};
$select->{ "/* $from */" . $self->where( $param, undef, $table ) } = $from;
$count += $do_select->($select);
$select = undef;
last if $count >= $self->{limit};
}
} else {
$select = $self->where( $param, undef, $table );
}
}
$self->{'founded_max'} = 0;
}
return $select, $ids, $count;
};
my $count = $do_select->(@selects);
if ( $self->{'use_sphinx'} and @$ids ) {
my $n = 0;
my %ids = map { $_->{'id'} => ++$n } @$ids;
@$ret = sort { $ids{ $a->{'id'} } <=> $ids{ $b->{'id'} } } @$ret;
$ret->[ $_->{'n'} ]{'__fulltext_fail'} = $_->{'q'} for @fail;
}
$self->{'dbirows'} ||= $count;
return wantarray ? @$ret : $ret;
};
$self->{'select_log'} ||= sub {
my $self = shift;
my ( $table, $param, ) = @_;
return $self->query_log( $self->select_body( $self->where( $param, undef, $table ), $param, $table ) );
};
$self->{'join_what'} ||= sub {
my $self = shift;
my ( $where, $param, $table ) = @_;
$table ||= $self->{'current_table'};
my @join;
for my $jt ( keys %{ $self->{'table_join'}{$table} } ) {
local @_ = (
grep { $_ and $self->{'table'}{$jt}{$_} } keys %{ $self->{'table_join'}{$table}{$jt}{'on'} }
);
push @join, " LEFT JOIN " . $tq . $self->{'table_prefix'} . $jt . $tq . ' ON ' . '(' . join(
', ',
map {
$tq
. $self->{'table_prefix'}
. $table
. $tq . '.'
. $rq
. $self->{'table_join'}{$table}{$jt}{'on'}{$_}
. $rq . ' = '
. $tq
. $self->{'table_prefix'}
. $jt
. $tq . '.'
. $rq
. $_
. $rq
} @_
)
. ')'
if @_;
unless (@_) {
@_ = (
grep { $_ and $self->{'table'}{$jt}{$_} } keys %{ $self->{'table_join'}{$table}{$jt}{'using'} }
);
@_ = ( grep { $self->{'table'}{$jt}{$_}{'primary'} } keys %{ $self->{'table'}{$jt} } ) unless @_;
push @join,
" LEFT JOIN "
. $tq
. $self->{'table_prefix'}
. $jt
. $tq
. ' USING ' . '('
. join( ', ', map { $rq . $_ . $rq } @_ ) . ')'
if @_;
}
}
return join( ' ', @join );
};
$self->{'join_where'} ||= sub {
my $self = shift;
my ( $where, $param, $table ) = @_;
my @what;
$table ||= $self->{'current_table'};
for my $jt ( sort keys %{ $self->{'table_join'}{$table} } ) {
local $_ = join ', ', map {
$tq
. $self->{'table_prefix'}
. $jt
. $tq . '.'
. $rq
. $self->{'table_join'}{$table}{$jt}{'fields'}{$_}
. $rq . ' AS '
. $rq
. $_
. $rq
} grep {
$self->{'table'}{$jt}{ $self->{'table_join'}{$table}{$jt}{'fields'}{$_} }
} sort keys %{ $self->{'table_join'}{$table}{$jt}{'fields'} };
$_ ||= "$tq$self->{'table_prefix'}$jt" . "$tq.*";
push( @what, $_ );
}
return join( ', ', grep { $_ } @what );
};
for my $by (qw(order group)) {
$self->{ $by . 'by' } ||= sub {
my $self = shift;
my ( $param, $table ) = @_;
$table ||= $self->{'current_table'};
my $sql;
my %order;
for my $ordern ( '', 0 .. 10 ) {
my $order = ( $param->{ $by . $ordern } or next );
last if ( $self->{'ignore_index'} or $self->{'table_param'}{$table}{'ignore_index'} );
my $min_data;
++$min_data
for grep { $self->{'table'}{$table}{$_}{'sort_min'} and defined( $param->{$_} ) } keys %{ $self->{'table'}{$table} };
last if $self->{'no_slow'} and !$min_data;
for my $join (
grep { $order eq $_ } (
grep { $self->{'table'}{$table}{$_}{'sort'} or !$self->{'table'}{$table}{$_}{'no_order'} }
keys %{ $self->{'table'}{$table} }
),
@{ $self->{'table_param'}{$table}{'join_fields'} }
)
{
my ($intable) = grep { keys %{ $self->{'table'}{$_}{$join} } } $table, keys %{ $config{'sql'}{'table_join'}{$table} };
$order{ ( $self->{'no_column_prepend_table'} ? '' : $tq . $intable . $tq . '.' )
. $rq
. $join
. $rq
. ( ( $param->{ $by . '_mode' . $ordern } ) ? ' DESC ' : ' ASC' ) }
=
$ordern;
}
}
if ( keys %order ) {
$sql .= ' ' . uc($by) . ' BY ' . join ', ', sort { $order{$a} <=> $order{$b} } keys %order;
}
return $sql;
};
}
$self->{'select_body'} ||= sub {
my $self = shift;
my ( $where, $param, $table ) = @_;
$table ||= $self->{'current_table'};
( $tq, $rq, $vq ) = $self->quotes();
$self->limit_calc( $param, $table );
if ( ( $self->{'ignore_index'} or $self->{'table_param'}{$table}{'ignore_index'} )
and !( $self->{'no_index'} or $self->{'table_param'}{$table}{'no_index'} ) )
{
local @_ = ();
local %_ = ();
for ( keys %{ $self->{'table'}{$table} } ) {
++$_{ $self->{'table'}{$table}{$_}{'fulltext'} } if $self->{'table'}{$table}{$_}{'fulltext'};
push( @_, $_ ) if $self->{'table'}{$table}{$_}{'index'};
}
push( @_, keys %_ ) unless $self->{'ignore_index_fulltext'} and $self->{'table_param'}{$table}{'ignore_index_fulltext'};
$work{'sql_select_index'} = 'IGNORE INDEX (' . join( ',', @_ ) . ')';
}
my $from;
if ($table) {
if ( $self->{'sphinx'} and $self->{'table_param'}{$table}{'stemmed_index'} and !$param->{'accurate'} ) {
$from .= "$tq$self->{'table_param'}{$table}{'stemmed_index'}$tq ";
} else {
$from .= "$tq$self->{'table_prefix'}$table$tq ";
}
}
unless ( $self->{'no_join'} ) { $from .= $work{'sql_select_index'} . ' ' . $self->join_what( $where, $param, $table ); }
$from = "FROM " . $from if $from;
my $sql = $from . ' ' . $where;
my @what = (
( ( $table and !$self->{'no_column_prepend_table'} ) ? $tq . $self->{'table_prefix'} . $table . $tq . '.' : '' ) . '*',
$work{'what_relevance'}{$table},
$self->{'table_param'}{$table}{'what_extra'}
);
if ( defined( $self->{'table'}{$table}{ $param->{'distinct'} } ) ) {
@what = ( "DISTINCT $rq$param->{'distinct'}$rq", $self->{'table_param'}{$table}{'what_extra'} );
} else {
unless ( $self->{'no_join'} ) { @what = ( $self->join_where( $where, $param, $table ), @what ); }
}
$sql = join( ', ', grep { $_ } @what, ) . ' ' . $sql;
my $priority;
$priority = $self->{'HIGH_PRIORITY'} unless $config{'client_bot'};
$sql = " SELECT $self->{'SELECT_FLAGS'} $priority " . $sql;
$sql .= $self->groupby( $param, $table );
$sql .= $self->orderby( $param, $table );
$sql .= $self->limit_body();
if ( $self->{'OPTION'} and psmisc::is_hash $self->{'option'} ) {
$sql .= $self->{'OPTION'} . ' ' . join ', ', map { "$_=$self->{'option'}{$_}" } keys %{ $self->{'option'} };
}
$sql .= $self->{'select_append'};
return $sql;
};
$self->{'limit_body'} ||= sub {
my $self = shift;
return unless $self->{'limit_offset'} or $self->{'limit'};
return
' LIMIT '
. ( $self->{'limit_offset'} && !$self->{'OFFSET'} ? $self->{'limit_offset'} . ',' : '' )
. $self->{'limit'}
. ( $self->{'OFFSET'} && $self->{'limit_offset'} ? ' ' . $self->{'OFFSET'} . ' ' . $self->{'limit_offset'} : '' ) . ' ';
return '';
};
$self->{'calc_count'} ||= sub {
my $self = shift;
my ( $param, $table, $count ) = @_;
return if $work{'calc_count'}{$table}++;
$self->{'founded'} = $count
|| ( ( $self->{'dbirows'} > $self->{'stat'}{'found'}{'files'} and $self->{'dbirows'} < $self->{'limit'} )
? $self->{'dbirows'} + $self->{'limit_offset'}
: $self->{'stat'}{'found'}{'files'} );
$self->{'founded'} = 0 if $self->{'founded'} < 0 or !$self->{'founded'};
$self->{'page_last'} =
$self->{'limit'} > 0
? ( int( $self->{'founded'} / ( $self->{'limit'} or 1 ) ) + ( $self->{'founded'} % ( $self->{'limit'} or 1 ) ? 1 : 0 ) )
: 0;
$self->{'page'} = int( rand( $self->{'page_last'} ) ) if $self->{'page'} eq 'rnd' and $param->{'count_f'} eq 'on';
};
$self->{'limit_calc'} ||= sub {
my $self = shift;
my ($param) = @_;
$self->{'limit_offset'} =
int( $self->{'page'} > 0 ? $self->{'limit'} * ( $self->{'page'} - 1 ) : ( ( $param->{'show_from'} ) or 0 ) );
$self->{'limit_offset'} -= $self->{'limit_from'} - $self->{'limit_minus'} if $self->{'limit_offset'};
$self->{'limit'} -= $self->{'limit_minus'};
return undef;
};
$self->{'lock_tables'} ||= sub {
my $self = shift;
return $self->do( $self->{'LOCK TABLES'} . ' ' . join ' ', @_ ) if $self->{'LOCK TABLES'};
};
$self->{'unlock_tables'} ||= sub {
my $self = shift;
return $self->do( $self->{'UNLOCK TABLES'} . ' ' . join ' ', @_ ) if $self->{'UNLOCK TABLES'};
};
$self->{'stat_string'} ||= sub {
my $self = shift;
return 'sqlstat: '
. join(
' ',
( map { "$_=$self->{$_};" } grep { $self->{$_} } ( @_ or sort keys %{ $self->{'statable'} } ) ),
(
map { "$_=" . psmisc::human( 'time_period', $self->{$_} ) . ';' }
grep { $self->{$_} } ( @_ or sort keys %{ $self->{'statable_time'} } )
)
);
};
$self->{'log_stat'} ||= sub {
my $self = shift;
$self->log( 'stat', $self->stat_string(@_) );
};
$self->{'check_data'} ||= sub {
my $self = shift;
local @_ = sort grep { $_ } keys %{ $self->{'table'} };
return 0 unless @_;
return 0;
return $self->query( 'SELECT * FROM ' . ( join ',', map { "$tq$_$tq" } @_ ) . ' WHERE 1 LIMIT 1' );
};
$self->{'check_data_every_table'} ||= sub {
my $self = shift;
local @_ = sort grep { $_ } keys %{ $self->{'table'} };
return 0 unless @_;
for my $table (@_) {
$self->query_log("SELECT * FROM $tq$table$tq LIMIT 1");
}
};
$self->{'on_connect1'} ||= sub {
my $self = shift;
$self->check_data() if $self->{'auto_check'};
};
$self->{'table_stat'} ||= sub {
my $self = shift;
$self->log( 'info', 'totals:', @_,
map { ( $_, '=', values %{ $self->line("SELECT COUNT(*) FROM $rq$self->{'table_prefix'}$_$rq ") } ) }
grep { $_ } ( @_ or keys %{ $self->{'table'} } ) );
};
$self->{'next_user_prepare'} ||= sub {
my $self = shift;
$self->{$_} = 0 for qw(founded queries queries_time errors_chain errors connect_tried);
$self->{'stat'}{'found'} = {};
$self->{ 'on_user' . $_ }->($self) for grep { ref $self->{ 'on_user' . $_ } eq 'CODE' } ( '', 1 .. 5 );
};
$self->{'next_user'} ||= sub {
my $self = shift;
$self->user_params(@_);
$self->next_user_prepare(@_);
$self->{'sphinx_dbi'}->next_user(@_) if $self->{'sphinx_dbi'};
};
Hide Show 8 lines of Pod
$self->{'stem'} ||= sub {
my $self = shift;
local $_ = lc( scalar psmisc::cp_trans( $self->{'cp_in'}, $self->{'cp_int'}, $_[0] ) );
$self->{'stem_version'} = 4 if $self->{'stem_version'} <= 1;
if ( $self->{'stem_version'} == 2 ) {
s/(\d)(\D)/$1 $2/g;
s/(\D)(\d)/$1 $2/g;
tr/А-Я/а-я/;
s/[ъь]//g;
s/kn/n/g;
tr/абвгдеёжзийклмнопрстуфхцчшщыэюя/abvgdeejsiiklmnoprstufhccssieua/;
tr/ekouw/acaav/;
s/'//g;
s/\W/ /g if $_[1];
s/_/ /g;
s/(?:rd|nd)\b/d/g;
s/ay\b/y/g;
s/\B[aeisuo]\b//g;
s/av/af/g;
s/sch/s/g;
s/ph/f/g;
s/\s+/ /g;
s/(\w)\1+/$1/g;
} elsif ( $self->{'stem_version'} == 3 ) {
s/(\d)(\D)/$1 $2/g;
s/(\D)(\d)/$1 $2/g;
tr/А-Я/а-я/;
s/[ъь]//g;
s/kn/n/g;
tr/абвгдеёжзийклмнопрстуфхцчшщыэюя/abvgdeejsiiklmnoprstufhccssieua/;
s/ks/x/g;
tr/kw/cv/;
s/'//g;
s/\W/ /g if $_[1];
s/_/ /g;
s/(?:rd|nd)\b/d/g;
s/ay\b/y/g;
s/\B[aeisuo]\b//g;
s/av/af/g;
s/sch/s/g;
s/ph/f/g;
s/\s+/ /g;
s/(?:(?!xxx)|(?=xxxx))(\w)\1+(?:(?<!xxx)|(?<=xxxx))/$1/g;
} elsif ( $self->{'stem_version'} == 4 ) {
s/(\d)(\D)/$1 $2/g;
s/(\D)(\d)/$1 $2/g;
tr/А-Я/а-я/;
s/kn/n/g;
s/[ъь]//g;
tr{абвгдеёжзийклмнопрстуфхцчшщыэюя}
{abvgdeejziiklmnoprstufhccssieua};
s/ks/x/g;
tr/kw/cv/;
s/'//g;
s/\W/ /g if $_[1];
s/_/ /g;
s/(?:rd|nd)\b/d/g;
s/ay\b/y/g;
s/\B[aeisuo]\b//g;
s/av/af/g;
s/sch/s/g;
s/ph/f/g;
s/\s+/ /g;
s/(?:(?!xxx)|(?=xxxx))(\w)\1+(?:(?<!xxx)|(?<=xxxx))/$1/g;
}
return scalar psmisc::cp_trans( $self->{'cp_int'}, $self->{'cp_in'}, $_ );
};
$self->{'stem_insert'} ||= sub {
my $self = shift;
my ( $table, $col ) = @_;
return 1 unless ref $self->{'stem'} eq 'CODE';
$col->{'stem'} = join ' ',
map { $self->stem( $col->{$_}, 1 ) } grep { $self->{'table'}{$table}{$_}{'stem'} and $col->{$_} } keys %$col;
return undef;
};
$self->{'last_insert_id'} ||= sub {
my $self = shift;
my $table = shift || $self->{'current_table'};
if ( $^O eq 'MSWin32' and $self->{'driver'} eq 'pgpp' ) {
my ($field) =
grep { $self->{'table'}{$table}{$_}{'type'} eq 'serial' or $self->{'table'}{$table}{$_}{'auto_increment'} }
keys %{ $self->{'table'}{$table} };
return $self->line("SELECT currval('${table}_${field}_seq') as lastid")->{'lastid'};
} else {
return $self->{dbh}->last_insert_id( undef, undef, $table, undef );
}
};
$self->{'dump_cp'} ||= sub {
$self->log( 'dev', map { "$_ = $self->{$_}; " } qw(codepage cp cp_in cp_out cp_int cp_set_names) );
};
$self->{'cp_client'} ||= sub {
shift;
$self->{'cp_in'} = $_[0] if $_[0];
$self->{'cp_out'} = $_[1] || $self->{'cp_in'} if $_[1] or $_[0];
return ( $self->{'cp_in'}, $self->{'cp_out'} );
};
$self->{'index_disable'} ||= sub {
my $self = shift;
my $tim = psmisc::timer();
$self->log( 'info', 'Disabling indexes on', @_ );
$self->log( 'err', 'ALTER TABLE ... DISABLE KEYS available in mysql >= 4' ), return
if $self->{'driver'} eq 'mysql3'
or $self->{'driver'} !~ /mysql/;
$self->
do("ALTER TABLE $tq$config{'table_prefix'}$_$tq DISABLE KEYS") for @_;
$self->log( 'time', "Disable index per", psmisc::human( 'time_period', $tim->() ), "sec" );
};
$self->{'index_enable'} ||= sub {
my $self = shift;
my $tim = psmisc::timer();
$self->log( 'info', 'Enabling indexes on', @_ );
$self->log( 'err', 'ALTER TABLE ... DISABLE KEYS available in mysql >= 4' ), return
if $self->{'driver'} eq 'mysql3'
or $self->{'driver'} !~ /mysql/;
$self->
do("ALTER TABLE $tq$config{'table_prefix'}$_$tq ENABLE KEYS") for @_;
$self->log( 'time', 'Enable index per ', psmisc::human( 'time_period', $tim->() ) );
};
for my $action (qw(optimize analyze check flush)) {
$self->{$action} ||= sub {
my $self = shift;
@_ = sort keys %{ $self->{'table'} } unless @_;
@_ = grep { $_ and $self->{'table'}{$_} } @_;
$self->log( 'err', 'not defined action', $action, ), return unless $self->{ uc $action };
$self->log( 'info', $action, @_ );
my $tim = psmisc::timer();
for ( $self->{'bulk_service'} ? \@_ : @_ ) {
$self->query_log(
$self->{ uc $action } . ' ' . join( ',', map( $self->tquote("$self->{'table_prefix'}$_"), psmisc::array $_ ) ) );
}
$self->log( 'time', $action, 'per ', psmisc::human( 'time_period', $tim->() ) );
};
}
Hide Show 15 lines of Pod
$self->{'retry_off'} ||= sub {
my $self = shift;
return if %{ $self->{'retry_save'} || {} };
$self->{'retry_save'}{$_} = $self->{$_}, $self->{$_} = 0 for @{ $self->{'retry_vars'} };
};
$self->{'retry_on'} ||= sub {
my $self = shift;
return unless %{ $self->{'retry_save'} || {} };
$self->{$_} = $self->{'retry_save'}{$_} for @{ $self->{'retry_vars'} };
$self->{'retry_save'} = {};
};
$self->{'set_names'} ||= sub {
my $self = shift;
local $_ = $_[0] || $self->{'cp_set_names'};
$self->do( $self->{'SET NAMES'} . " $vq$_$vq" ) if $_ and $self->{'SET NAMES'};
};
}
1;