App-DBBrowser
view release on metacpan or search on metacpan
- Quote numbers if the data type is not numeric.
- Refactoring.
2.408_02 2024-04-01
- New: to_xxx scalar functions.
- Refactored ScalarFunctions.
- If chosen a deleted directory: warn and remove it from history.
2.408_01 2024-03-21
- Epoch_to_DateTime: bugfix and updates.
- New: Unix_Timestamp.
2.407 2024-03-11
- Bugfix: auto-increment primary key quoted twice.
2.406 2024-03-11
- New 'create table' options: 'table constraints' and 'table options'.
- Update option 'auto increment column'.
- Use storable to make deep copies.
- DB2, Oracle, Firebird: regex operator bugfix.
- DBD::SQLite minimum version '1.74'.
bin/db-browser view on Meta::CPAN
In the replacement, the variable C<$c> can be used as a counter. C<$c> is set to C<0> before each C<s///>.
By selecting the prompt line (I<Your choice:>), one can save I<search & replace> instructions for later use.
If a I<search & replace> has altered the first row of the input data, a menu entry named C<RESTORE header row> is
offered.
=item Convert DateTime
Convert a I<DateTime> to another format or to seconds since the Unix epoch.
id | date_time id | date_time
---|---------------------------- ---|--------------------
1 | Sat 13 Aug 2022 07:38:49 PM 1 | 2022-08-13 19:38:49
---|---------------------------- ---|--------------------
2 | Mon 15 Aug 2022 12:08:54 PM 2 | 2022-08-15 12:08:54
---|---------------------------- ---|--------------------
3 | Tue 16 Aug 2022 09:56:13 AM 3 | 2022-08-16 09:56:13
=item Split Table
lib/App/DBBrowser/Table/Extensions/ScalarFunctions.pm view on Meta::CPAN
my $str = 'STR';
my $strftime = 'STRFTIME';
my $strptime = 'STRPTIME';
my $str_to_date = 'STR_TO_DATE';
my $to_char = 'TO_CHAR';
my $to_date = 'TO_DATE';
my $to_epoch = 'TO_EPOCH';
my $to_number = 'TO_NUMBER';
my $to_timestamp = 'TO_TIMESTAMP';
my $to_timestamp_tz = 'TO_TIMESTAMP_TZ';
my $unixepoch = 'UNIXEPOCH';
my $cast = 'CAST';
my $coalesce = 'COALESCE';
sub new {
my ( $class, $info, $options, $d ) = @_;
bless {
i => $info,
o => $options,
lib/App/DBBrowser/Table/Extensions/ScalarFunctions.pm view on Meta::CPAN
$to_timestamp => [ undef , undef , undef , 'Pg' , 'DuckDB', undef , undef, undef , 'Oracle', undef ], # DB2 # DuckDB: epoch to timestamp
$to_timestamp_tz => [ undef , undef , undef , undef, undef , undef , undef, undef , 'Oracle', undef ],
$to_number => [ undef , undef , 'MariaDB', 'Pg' , undef , undef , 'DB2' , 'Informix', 'Oracle', undef ],
$to_epoch => [ 'SQLite', 'mysql', 'MariaDB', 'Pg' , 'DuckDB', 'Firebird', 'DB2' , undef , 'Oracle', 'MSSQL' ],
$str => [ undef , undef , undef , undef, undef , undef , undef, undef , undef , 'MSSQL' ],
$strftime => [ 'SQLite', undef , undef , undef, 'DuckDB', undef , undef, undef , undef , undef ],
$strptime => [ undef , undef , undef , undef, 'DuckDB', undef , undef, undef , undef , undef ],
$date_format => [ undef , 'mysql', 'MariaDB', undef, undef , undef , undef, undef , undef , undef ],
$format => [ undef , 'mysql', 'MariaDB', undef, undef , undef , undef, undef , undef , 'MSSQL' ], # DuckDB: construct formatted strings
$str_to_date => [ undef , 'mysql', 'MariaDB', undef, undef , undef , undef, undef , undef , undef ],
$unixepoch => [ 'SQLite', undef , undef , undef, undef , undef , undef, undef , undef , undef ],
},
other => {
$cast => [ 'SQLite', 'mysql', 'MariaDB', 'Pg' , 'DuckDB', 'Firebird', 'DB2' , 'Informix', 'Oracle', 'MSSQL' ],
$coalesce => [ 'SQLite', 'mysql', 'MariaDB', 'Pg' , 'DuckDB', 'Firebird', 'DB2' , 'Informix', 'Oracle', 'MSSQL' ],
},
};
my $dbms = $sf->{i}{dbms};
my $index = {
SQLite => 0, mysql => 1, MariaDB => 2, Pg => 3, DuckDB => 4, Firebird => 5, DB2 => 6, Informix => 7, Oracle => 8, MSSQL => 9,
lib/App/DBBrowser/Table/Extensions/ScalarFunctions/GetArguments.pm view on Meta::CPAN
'n seconds',
'n months',
'n years',
'[+-]YYYY-MM-DD HH:MM:SS.SSS',
'ceiling',
'floor',
'start of month',
'start of year',
'start of day',
'weekday n',
'unixepoch',
'julianday',
'auto',
'localtime',
'utc',
'subsec',
];
my @modifiers;
my ( $ok, $add_modifier ) = ( '-OK-', 'Add modifier' );
my @args_sofar = @{$r_data->[-1]};
lib/App/DBBrowser/Table/Extensions/ScalarFunctions/To.pm view on Meta::CPAN
return "TRUNC((CAST($col AT TIME ZONE 'UTC' AS DATE) - DATE '1970-01-01') * 86400)" if $column_type eq 'TIMESTAMP_TZ';
return "TRUNC((CAST(FROM_TZ($col,SESSIONTIMEZONE) AT TIME ZONE 'UTC' AS DATE) - DATE '1970-01-01') * 86400)" if $column_type eq 'TIMESTAMP';
return "TRUNC((CAST(FROM_TZ(CAST($col AS TIMESTAMP),SESSIONTIMEZONE) AT TIME ZONE 'UTC' AS DATE) - DATE '1970-01-01') * 86400)" if $column_type eq 'DATE';
}
elsif ( $dbms eq 'MSSQL' ) {
return "CAST(DATEDIFF(s,'1970-01-01 00:00:00',$col)AS BIGINT)";
}
}
sub function_unixepoch {
my ( $sf, $sql, $clause, $func, $cols, $r_data ) = @_;
my $ga = App::DBBrowser::Table::Extensions::ScalarFunctions::GetArguments->new( $sf->{i}, $sf->{o}, $sf->{d} );
my $col = $ga->choose_a_column( $sql, $clause, $cols, $r_data );
if ( ! defined $col ) {
return;
}
my $modifiers = $ga->sqlite_modifiers( $sql, $r_data );
return "$func($col,$modifiers)" if length $modifiers;
return "$func($col)";
}
lib/App/DBBrowser/Table/Extensions/ScalarFunctions/To/EpochTo.pm view on Meta::CPAN
}
}
sub __stmt_epoch_to_date {
my ( $sf, $col, $interval ) = @_;
#my $ax = App::DBBrowser::Auxil->new( $sf->{i}, $sf->{o}, $sf->{d} );
my $dbms = $sf->{i}{dbms};
if ( $dbms eq 'SQLite' ) {
return "DATE($col/$interval,'unixepoch','localtime')";
}
elsif ( $dbms =~ /^(?:mysql|MariaDB)\z/ ) {
return "FROM_UNIXTIME($col/$interval,'%Y-%m-%d')";
}
elsif ( $dbms =~ /^(?:Pg|DuckDB)\z/ ) { ##
return "TO_TIMESTAMP(${col}::bigint/$interval)::date";
}
elsif ( $dbms eq 'Firebird' ) {
#my $firebird_major_version = $ax->major_server_version();
my $firebird_major_version = 3; ##
lib/App/DBBrowser/Table/Extensions/ScalarFunctions/To/EpochTo.pm view on Meta::CPAN
#return "DATEADD(ns,$col%1000000000,DATEADD(s,$col/1000000000,CONVERT(datetime2(7),'1970-01-01 00:00:00.0000000'))) AT TIME ZONE 'UTC'"
}
}
sub __stmt_epoch_to_datetime {
my ( $sf, $col, $interval ) = @_;
#my $ax = App::DBBrowser::Auxil->new( $sf->{i}, $sf->{o}, $sf->{d} );
my $dbms = $sf->{i}{dbms};
if ( $dbms eq 'SQLite' ) {
return "DATETIME($col,'unixepoch','localtime')" if $interval == 1;
return "DATETIME($col/$interval.0,'unixepoch','localtime','subsec')";
}
elsif ( $dbms =~ /^(?:mysql|MariaDB)\z/ ) { # DATE_FORMAT and STR_TO_DATE ##
# mysql: FROM_UNIXTIME doesn't work with negative timestamps
return "FROM_UNIXTIME($col)" if $interval == 1;
return "FROM_UNIXTIME($col * 0.001)" if $interval == 1_000;
return "FROM_UNIXTIME($col * 0.000001)";
}
elsif ( $dbms eq 'Pg' ) {
return "TO_CHAR(TO_TIMESTAMP(${col}::bigint)::timestamp,'yyyy-mm-dd hh24:mi:ss')" if $interval == 1;
return "TO_CHAR(TO_TIMESTAMP(${col}::bigint/$interval.0)::timestamp,'yyyy-mm-dd hh24:mi:ss.ff3')" if $interval == 1_000;
( run in 0.980 second using v1.01-cache-2.11-cpan-64ef6c95b5d )