+sub selectall_as_map {
+ $main::lxdebug->enter_sub(2);
+
+ my ($form, $dbh, $query, $key_col, $value_col) = splice(@_, 0, 5);
+
+ my $sth = prepare_execute_query($form, $dbh, $query, @_);
+
+ my %hash;
+ if ('' eq ref $value_col) {
+ while (my $ref = $sth->fetchrow_hashref()) {
+ $hash{$ref->{$key_col}} = $ref->{$value_col};
+ }
+ } else {
+ while (my $ref = $sth->fetchrow_hashref()) {
+ $hash{$ref->{$key_col}} = { map { $_ => $ref->{$_} } @{ $value_col } };
+ }
+ }
+
+ $sth->finish();
+
+ $main::lxdebug->leave_sub(2);
+
+ return %hash;
+}
+
+sub create_sort_spec {
+ $main::lxdebug->enter_sub(2);
+
+ my %params = @_;
+
+ # Safety check:
+ $params{defs} || die;
+ $params{default} || die;
+
+ # The definition of valid columns to sort by.
+ my $defs = $params{defs};
+
+ # The column name to sort by. Use the default column name if none was given.
+ my %result = ( 'column' => $params{column} || $params{default} );
+
+ # Overwrite the column name with the default column name if the other one is not valid.
+ $result{column} = $params{default} unless ($defs->{ $result{column} });
+
+ # The sort direction. true means 'sort ascending', false means 'sort descending'.
+ $result{dir} = defined $params{dir} ? $params{dir}
+ : defined $params{default_dir} ? $params{default_dir}
+ : 1;
+ $result{dir} = $result{dir} ? 1 : 0;
+ my $asc_desc = $result{dir} ? 'ASC' : 'DESC';
+
+ # Create the SQL code.
+ my $cols = $defs->{ $result{column} };
+ $result{sql} = join ', ', map { "${_} ${asc_desc}" } @{ ref $cols eq 'ARRAY' ? $cols : [ $cols ] };
+
+ $main::lxdebug->leave_sub(2);
+
+ return %result;
+}
+
+sub does_table_exist {
+ $main::lxdebug->enter_sub(2);
+
+ my $dbh = shift;
+ my $table = shift;
+
+ my $result = 0;
+
+ if ($dbh) {
+ my $sth = $dbh->table_info('', '', $table, 'TABLE');
+ if ($sth) {
+ $result = $sth->fetchrow_hashref();
+ $sth->finish();
+ }
+ }
+
+ $main::lxdebug->leave_sub(2);
+
+ return $result;
+}
+
+# add token to values.
+# usage:
+# add_token(
+# \@where_tokens,
+# \@where_values,
+# col => 'id',
+# val => [ 23, 34, 17 ]
+# esc => \&conf_i
+# )
+# will append to the given arrays:
+# -> 'id IN (?, ?, ?)'
+# -> (conv_i(23), conv_i(34), conv_i(17))
+#
+# features:
+# - don't care if one or multiple values are given. singlewill result in 'col = ?'
+# - pass escape routines
+# - expand for future method
+# - no need to type "push @where_tokens, 'id = ?'" over and over again
+sub add_token {
+ my $tokens = shift() || [];
+ my $values = shift() || [];
+ my %params = @_;
+ my $col = $params{col};
+ my $val = $params{val};
+ my $escape = $params{esc} || sub { $_ };
+ my $method = $params{esc} =~ /^start|end|substr$/ ? 'ILIKE' : $params{method} || '=';
+
+ $val = [ $val ] unless ref $val eq 'ARRAY';
+
+ my %escapes = (
+ id => \&conv_i,
+ bool => \&conv_b,
+ date => \&conv_date,
+ start => sub { $_[0] . '%' },
+ end => sub { '%' . $_[0] },
+ substr => sub { '%' . $_[0] . '%' },
+ );
+
+ my $_long_token = sub {
+ my $op = shift;
+ sub {
+ my $col = shift;
+ return scalar @_ ? join ' OR ', ("$col $op ?") x scalar @_,
+ : undef;
+ }
+ };
+
+ my %methods = (
+ '=' => sub {
+ my $col = shift;
+ return scalar @_ > 1 ? sprintf '%s IN (%s)', $col, join ', ', ("?") x scalar @_
+ : scalar @_ == 1 ? sprintf '%s = ?', $col
+ : undef;
+ },
+ map({ $_ => $_long_token->($_) } qw(LIKE ILIKE >= <= > <)),
+ );
+
+ $method = $methods{$method} || $method;
+ $escape = $escapes{$escape} || $escape;
+
+ my $token = $method->($col, @{ $val });
+ my @vals = map { $escape->($_) } @{ $val };
+
+ return unless $token;
+
+ push @{ $tokens }, $token;
+ push @{ $values }, @vals;
+
+ return ($token, @vals);
+}
+
projects / mfinanz.git / blobdiff