App-DBBrowser
view release on metacpan or search on metacpan
- Backwards incompatible changes:
Table names and column names are not quoted by default.
Saved subqueries: saved at plugin level instead at driver level.
Changes in how configuration data is saved.
- Bug fixes:
Bug fix in quoting attached databases.
Bug fixes in the Documentation.
DB2: decode only text columns.
Bugfixes in format functions.
Set the default for sqlite_string_mode to a valid value.
Bug fix in the window function frame clause.
Reset the where statement from delete/update.
Informix remove trailing spaces from table-type to fix empty table list in drop_table.
Informix: fixed system schema regexp.
- Updates:
Updated options menus.
Refactoring options.
Update plugins.
Refactoring scalar functions.
Removed comment from the App::DBBrowser::DB documentation.
Added 'tables_info' to the plugin documentation.
Drop table info: use select_statement_results from Table.pm the get the data.
Pg epoch_to_timestamp: removed timestamptz cast from to_timestamp.
Extensions.pm: replaced requires with uses.
Unlink empty config files.
- New:
ODBC: query the DBMS name and set the appropriate SQL dialect.
Added SQL dialects for MSSQL and DuckDB.
2.437_05 2025-12-17
- Bugfix: reset where statement from delete/update.
- Bugfix in window function frame clause.
- ODBC: removed option to set the appropriate SQL dialect.
- ODBC: query the dbms name and set the appropriate SQL dialect.
2.437_04 2025-12-15
- Bugfix default value sqlite_string_mode.
- ODBC: new option to set the appropriate SQL dialect.
- Code adjustments for MSSQL.
2.437_03 2025-12-04
- Added 'tables_info' to the plugin documentation.
lib/App/DBBrowser/Table/Extensions/WindowFunctions.pm view on Meta::CPAN
push @parts, $win_stmt;
$win_stmt = '';
}
if ( length $win_data->{'order_by_stmt'} ) {
$win_stmt .= $win_data->{'order_by_stmt'} . " ";
}
if ( $placeholder eq 'order_by_stmt' ) {
push @parts, $win_stmt;
$win_stmt = '';
}
if ( length $win_data->{'frame_mode'} ) {
$win_stmt .= $win_data->{'frame_mode'} . " ";
if ( length $win_data->{'frame_start'} || $placeholder eq 'frame_start' ) {
if ( length $win_data->{'frame_end'} || $placeholder eq 'frame_end' ) {
$win_stmt .= "BETWEEN ";
}
if ( length $win_data->{'frame_start'} ) {
$win_stmt .= $win_data->{'frame_start'} . " ";
}
if ( $placeholder eq 'frame_start' ) {
push @parts, $win_stmt;
$win_stmt = '';
}
}
if ( length $win_data->{'frame_end'} || $placeholder eq 'frame_end' ) {
if ( length $win_data->{'frame_start'} || $placeholder eq 'frame_start' ) {
$win_stmt .= "AND ";
}
if ( length $win_data->{'frame_end'} ) {
$win_stmt .= $win_data->{'frame_end'} . " ";
}
if ( $placeholder eq 'frame_end' ) {
push @parts, $win_stmt;
$win_stmt = '';
}
}
if ( length $win_data->{'frame_exclusion'} ) {
$win_stmt .= $win_data->{'frame_exclusion'};
}
if ( $placeholder eq 'frame_exclusion' ) {
push @parts, $win_stmt;
$win_stmt = '';
}
}
if ( $placeholder eq 'frame_mode' ) {
push @parts, $win_stmt;
$win_stmt = '';
}
if ( ! $placeholder ) {
push @parts, $win_stmt;
$win_stmt = '';
}
$parts[0] =~ s/\s+\z//;
$win_stmt =~ s/\s+\z//;
$win_stmt .= ')';
lib/App/DBBrowser/Table/Extensions/WindowFunctions.pm view on Meta::CPAN
if ( length $default_value && $default_value ne "''" ) {
$win_data->{'args_str'} .= ',' . $default_value;
#$r_data->[-1] = [ 'win', $sf->__get_win_func_stmt( $win_data ) ];
}
}
}
}
my $old_idx = 0;
WINDOW_DEFINITION: while( 1 ) {
my ( $partition_by, $order_by, $frame_clause ) = ( '- Partition by', '- Order by', '- Frame clause' );
my @pre = ( undef, $sf->{i}{confirm} );
my $menu = [ @pre, $partition_by, $order_by, $frame_clause ];
$r_data->[-1] = [ 'win', $sf->__get_win_func_stmt( $win_data ) ];
my $info = $info_sql . $ext->nested_func_info( $r_data );
# Choose
my $idx_wd = $tc->choose(
$menu,
{ %{$sf->{i}{lyt_v}}, info => $info, prompt => '', index => 1, default => $old_idx,
undef => $sf->{i}{back} }
);
if ( ! defined $idx_wd || ! defined $menu->[$idx_wd] ) {
if ( $win_data->{'partition_by_stmt'} || $win_data->{'order_by_stmt'} || $win_data->{'frame_mode'} ) {
$win_data = { func => $win_data->{'func'}, args_str => $win_data->{'args_str'} };
next WINDOW_DEFINITION;
}
if ( $func =~ /^(?:$rx_func_count_all|$rx_func_no_col)\z/ ) {
next WINDOW_FUNCTION;
}
next COLUMN;
}
if ( $sf->{o}{G}{menu_memory} ) {
if ( $old_idx == $idx_wd && ! $ENV{TC_RESET_AUTO_UP} ) {
lib/App/DBBrowser/Table/Extensions/WindowFunctions.pm view on Meta::CPAN
pop @$r_data;
my @parts = $sf->__get_win_func_stmt( $win_data );
return join '', @parts;
}
elsif ( $wd eq $partition_by ) {
$sf->__add_partition_by( $sql, $clause, $cols, $r_data, $win_data );
}
elsif ( $wd eq $order_by ) {
$sf->__add_order_by( $sql, $clause, $cols, $r_data, $win_data );
}
elsif ( $wd eq $frame_clause ) {
$sf->__add_frame_clause( $sql, $clause, $r_data, $win_data );
}
}
}
}
}
sub __add_partition_by {
my ( $sf, $sql, $clause, $cols, $r_data, $win_data ) = @_;
my $ax = App::DBBrowser::Auxil->new( $sf->{i}, $sf->{o}, $sf->{d} );
lib/App/DBBrowser/Table/Extensions/WindowFunctions.pm view on Meta::CPAN
pop @{$win_data->{'order_by_cols'}};
next ORDER_BY;
}
else {
$win_data->{'order_by_cols'}[-1] .= ' ' . $direction;
}
}
}
sub __add_frame_clause {
my ( $sf, $sql, $clause, $r_data, $win_data ) = @_;
my $ax = App::DBBrowser::Auxil->new( $sf->{i}, $sf->{o}, $sf->{d} );
my $ext = App::DBBrowser::Table::Extensions->new( $sf->{i}, $sf->{o}, $sf->{d} );
my $tc = Term::Choose->new( $sf->{i}{tc_default} );
my $dbms = $sf->{i}{dbms};
my @frame_clause_modes = ( 'ROWS', 'RANGE' );
if ( $dbms =~ /^(?:SQLite|Pg|DuckDB|Oracle)\z/ ) {
push @frame_clause_modes, 'GROUPS';
}
if ( ! length $win_data->{'frame_mode'} ) {
$r_data->[-1] = [ 'win', $sf->__get_win_func_stmt( $win_data, 'frame_mode' ) ];
}
my $info_sql = $ax->get_sql_info( $sql );
my $old_idx_fc = 0;
FRAME_CLAUSE: while ( 1 ) {
my $info = $info_sql . $ext->nested_func_info( $r_data );
my @pre = ( undef );
my $menu = [ @pre, map { '- ' . $_ } @frame_clause_modes ];
# Choose
my $idx_fc = $tc->choose(
$menu,
{ %{$sf->{i}{lyt_v}}, info => $info, index => 1, default => $old_idx_fc, prompt => 'Frame clause:', undef => ' <=' }
);
$ax->print_sql_info( $info );
if ( ! defined $idx_fc || ! defined $menu->[$idx_fc] ) {
return;
}
if ( $sf->{o}{G}{menu_memory} ) {
if ( $old_idx_fc == $idx_fc && ! $ENV{TC_RESET_AUTO_UP} ) {
$old_idx_fc = 0;
next FRAME_CLAUSE;
}
$old_idx_fc = $idx_fc;
}
my $frame_mode = $frame_clause_modes[$idx_fc-@pre];
$win_data->{'frame_mode'} = $frame_mode;
my $old_idx_fe = 0;
FRAME_END_AND_EXCLUSION: while ( 1 ) {
my $confirm = $sf->{i}{confirm};
my $back = $sf->{i}{back};
my @pre = ( undef, $confirm );
my ( $frame_start, $frame_end, $frame_exclusion ) = ( '- Add Frame start', '- Add Frame end', '- Add Frame exclusion' );
my $menu = [ @pre, $frame_start, $frame_end ];
if ( $dbms =~ /^(?:SQLite|Pg|DuckDB|Oracle)\z/ ) {
push @$menu, $frame_exclusion;
}
$r_data->[-1] = [ 'win', $sf->__get_win_func_stmt( $win_data, 'frame_mode' ) ];
my $info = $info_sql . $ext->nested_func_info( $r_data );
# Choose
my $idx_fe = $tc->choose(
$menu,
{ %{$sf->{i}{lyt_v}}, info => $info, index => 1, default => $old_idx_fe, prompt => 'Frame clause:', undef => $back }
);
$ax->print_sql_info( $info );
if ( ! defined $idx_fe || ! defined $menu->[$idx_fe] ) {
if ( $win_data->{'frame_start'} || $win_data->{'frame_end'} || $win_data->{'frame_exclusion'} ) {
delete @{$win_data}{qw(frame_start frame_end frame_exclusion)};
next FRAME_END_AND_EXCLUSION;
}
delete $win_data->{'frame_mode'};
next FRAME_CLAUSE;
}
if ( $sf->{o}{G}{menu_memory} ) {
if ( $old_idx_fe == $idx_fe && ! $ENV{TC_RESET_AUTO_UP} ) {
$old_idx_fe = 0;
next FRAME_END_AND_EXCLUSION;
}
$old_idx_fe = $idx_fe;
}
my $choice = $menu->[$idx_fe];
if ( $choice eq $confirm ) {
return 1;
}
if ( $choice eq $frame_start ) {
$sf->__add_frame_start_or_end( $sql, $clause, $r_data, $win_data, 'frame_start' );
}
elsif ( $choice eq $frame_end ) {
$sf->__add_frame_start_or_end( $sql, $clause, $r_data, $win_data, 'frame_end' );
}
else {
$sf->__add_frame_exclusion( $sql, $r_data, $win_data );
}
}
}
}
sub __add_frame_start_or_end {
my ( $sf, $sql, $clause, $r_data, $win_data, $pos ) = @_;
my $ax = App::DBBrowser::Auxil->new( $sf->{i}, $sf->{o}, $sf->{d} );
my $ext = App::DBBrowser::Table::Extensions->new( $sf->{i}, $sf->{o}, $sf->{d} );
my $tc = Term::Choose->new( $sf->{i}{tc_default} );
my $dbms = $sf->{i}{dbms};
my ( @frame_point_types, $prompt );
if ( $pos eq 'frame_start' ) {
if ( $win_data->{'frame_mode'} eq 'RANGE' && $dbms eq 'MSSQL' ) {
@frame_point_types = ( 'UNBOUNDED PRECEDING', 'CURRENT ROW' );
}
else {
@frame_point_types = ( 'UNBOUNDED PRECEDING', 'n PRECEDING', 'CURRENT ROW', 'n FOLLOWING' );
}
$prompt = 'Frame start:';
}
elsif ( $pos eq 'frame_end' ) {
if ( $win_data->{'frame_mode'} eq 'RANGE' && $dbms eq 'MSSQL' ) {
@frame_point_types = ( 'CURRENT ROW', 'UNBOUNDED FOLLOWING' );
}
else {
@frame_point_types = ( 'n PRECEDING', 'CURRENT ROW', 'n FOLLOWING', 'UNBOUNDED FOLLOWING' );
}
$prompt = 'Frame end:';
}
my $info_sql = $ax->get_sql_info( $sql );
FRAME_START: while ( 1 ) {
if ( ! length $win_data->{$pos} ) {
$r_data->[-1] = [ 'win', $sf->__get_win_func_stmt( $win_data, $pos ) ];
}
else {
$r_data->[-1] = [ 'win', $sf->__get_win_func_stmt( $win_data ) ];
}
my $info = $info_sql . $ext->nested_func_info( $r_data );
my $confirm = $sf->{i}{ok};
my @pre = ( undef, $confirm );
my $menu = [ @pre, map( '- ' . $_, @frame_point_types ) ];
# Choose
my $point = $tc->choose(
$menu,
{ %{$sf->{i}{lyt_v}}, info => $info, prompt => $prompt, undef => '<=' }
);
$ax->print_sql_info( $info );
if ( ! defined $point ) {
delete $win_data->{$pos};
return;
}
lib/App/DBBrowser/Table/Extensions/WindowFunctions.pm view on Meta::CPAN
next FRAME_START;
}
$point =~ s/^n/$offset/;
$win_data->{$pos} = $point;
}
}
}
}
sub __add_frame_exclusion {
my ( $sf, $sql, $r_data, $win_data ) = @_;
my $ax = App::DBBrowser::Auxil->new( $sf->{i}, $sf->{o}, $sf->{d} );
my $ext = App::DBBrowser::Table::Extensions->new( $sf->{i}, $sf->{o}, $sf->{d} );
my $tc = Term::Choose->new( $sf->{i}{tc_default} );
my @exclusion_types = ( 'EXCLUDE CURRENT ROW', 'EXCLUDE GROUP', 'EXCLUDE TIES', 'EXCLUDE NO OTHERS' );
my $info_sql = $ax->get_sql_info( $sql );
FRAME_EXCLUSION: while ( 1 ) {
if ( ! length $win_data->{'frame_exclusion'} ) {
$r_data->[-1] = [ 'win', $sf->__get_win_func_stmt( $win_data, 'frame_exclusion' ) ];
}
else {
$r_data->[-1] = [ 'win', $sf->__get_win_func_stmt( $win_data ) ];
}
my $info = $info_sql . $ext->nested_func_info( $r_data );
my $confirm = $sf->{i}{ok};
my @pre = ( undef, $confirm );
my $menu = [ @pre, map( '- ' . $_, @exclusion_types ) ];
# Choose
my $frame_exclusion = $tc->choose(
$menu,
{ %{$sf->{i}{lyt_v}}, info => $info, prompt => 'Frame exclusion:', undef => '<=' }
);
$ax->print_sql_info( $info );
if ( ! defined $frame_exclusion ) {
delete $win_data->{'frame_exclusion'};
return;
}
elsif ( $frame_exclusion eq $confirm ) {
return 1;
}
else {
$frame_exclusion =~ s/^-\s//;
$win_data->{'frame_exclusion'} = $frame_exclusion;
}
}
}
1;
__END__
( run in 0.501 second using v1.01-cache-2.11-cpan-e1769b4cff6 )