#!/usr/bin/env perl
use warnings;
use strict;
use 5.010000;
no warnings 'utf8';
our $VERSION = '0.048';
use Encode::Locale qw();
use App::DBBrowser;
use if $^O eq 'MSWin32', 'Win32::Console::ANSI';
print "\e(U" if $^O eq 'MSWin32';
binmode STDIN, ':encoding(console_in)';
binmode STDOUT, ':encoding(console_out)';
binmode STDERR, ':encoding(console_out)';
my $db_browser = App::DBBrowser->new();
$db_browser->run();
__END__
=pod
=encoding UTF-8
=head1 NAME
C<db-browser> - Browse SQLite/MySQL/PostgreSQL databases and their tables interactively.
=head1 VERSION
Version 0.048
=cut
=head1 SYNOPSIS
=head2 SQLite/MySQL/PostgreSQL
db-browser
db-browser -h|--help
When the C<db-browser> is called with the argument C<-h|--help>, it shows a menu - see L</OPTIONS>.
=head2 SQLite
db-browser [-s|--search] [directories to be searched]
If no directories are passed, the home directory is searched for SQLite databases.
C<db-browser> called with C<-s|--search> causes a new search of SQLite databases instead of using the cached data.
=head1 DESCRIPTION
Search and read in SQLite/MySQL/PostgreSQL databases. With the C<db-browser> one can browse databases and their tables
interactively. The supported DBI drivers are C<DBD::SQLite>, C<DBD::mysql> and C<DBD::Pg>.
To be able to browse database-, schema- and table-lists and the content of tables one needs to have the database
privileges required for fetching the respective data.
The C<db-browser> expects an existing home directory with read and write permissions for the user of the C<db-browser>.
Before the output leading and trailing spaces are removed from the elements and spaces are squashed to a single
white-space.
The elements in a column are right-justified if one or more elements of that column do not look like a number, else they
are left-justified.
See L<Term::TablePrint|Term::TablePrint/DESCRIPTION> for more details.
=head3 Legacy encodings
Non mappable characters will break the output.
=head1 USAGE
The best way to find out how C<db-browser> works is calling C<db-browser>.
To be able to use all the features of the C<db-browser> some basic SQL knowledge is required.
=head3 Keys to move around
=over
=item *
the C<Arrow> keys (or C<h,j,k,l>) to move up and down and to move to the right and to the left.
=item *
the C<PageUp> key (or C<Ctrl-B>) to go back one page, the C<PageDown> key (or C<Ctrl-F>) to go forward one page.
=item *
the C<Home> key (or C<Ctrl-A>) to jump to the beginning of the menu, the C<End> key (or C<Ctrl-E>) to jump to the end
of the menu.
=back
With the option I<mouse> enabled it can be used the mouse with the left mouse key to navigate through the menus.
To confirm a chosen menu item use the C<Return> key.
In some sub-menus it is possible to select more then one item before C<Return> is pressed; in such sub-menus the list of
items marked with the C<SpaceBar> key including the highlighted item are added to the chosen items when C<Return>
is pressed. If a mouse mode is enabled, it can be used the right mouse key instead of the C<SpaceBar>. C<Ctrl-SpaceBar>
(or C<Ctrl-@>) inverts the made choices - marked items are unmarked and unmarked items are marked.
To move backwards in the menu hierarchy one can press the C<q> key. When prompted for a string, try C<Ctrl-D> instead
of C<q>.
=head3 SQL menu
The SQL menu is the menu which opens after a table was selected.
If C<AGGREGATE> or C<GROUP BY> is set, the C<SELECT> statement is automatically formed; a previous user defined
C<SELECT> statement is reset. A user defined C<SELECT> resets a previous set C<AGGREGATE> or C<GROUP BY> statement.
To reset a SQL "sub-statement" (e.g C<WHERE>) re-enter into the respective menu entry and choose C<'- OK -'>.
Changing the I<lock> mode (C<Lk0>,C<Lk1>) resets the entire SQL.
=head3 Delete, Update and Insert
To get to the C<DELETE>, C<UPDATE> or C<INSERT INTO> statements select the prompt "I<Customize:>" in the SQL menu
and then select the prompt "I<Your choice:>".
=head3 Scalar functions
The scalar functions can be reached in the main SQL menu and also in the C<DELETE> and C<UPDATE> SQL sub-menus by
selecting the prompt "I<Customize:>".
The available functions are:
=head4 Epoch_to_DateTime
=head4 Epoch_to_Date
=head4 Truncate
With SQLite the function C<TRUNCATE> is a user-defined function which returns stringified values.
return sprintf "%.*f", $places, int( $number * 10 ** $places ) / 10 ** $places;
When comparing in C<WHERE> or C<HAVING TO> clauses with numbers, take the non-truncated (original) value for the
comparison if C<sqlite_see_if_its_a_number> is enabled (default).
Also to get a numeric comparison in an C<ORDER BY> clause use the non-truncated (original) values for the ordering.
=head4 Bit_Length
With SQLite the function C<Bit_Length> is a user-defined function which uses the Perl builtin C<length>. To make
C<length> return the number of bytes the C<bytes> pragma is C<use>d.
=head4 Char_Length
With SQLite the function C<Char_Length> is a user-defined function which uses the Perl builtin C<length> to get the
number of characters.
To remove a chosen scalar function from a column select the column with the function a second time.
=head2 OPTIONS
=head3 HELP
Show this Info.
=head3 Path
Shows the version and the path of the running C<db-browser> and the path of the application directory.
=head3 Output
=head4 Colwidth
Columns with a width below or equal I<Colwidth> are only trimmed if it is still required to lower the row width despite
all columns wider than I<Colwidth> have been trimmed to I<Colwidth>.
=head4 ProgressBar
Set the progress bar threshold. If the number of fields (rows x columns) is higher than the threshold, a progress bar is
shown while preparing the data for the output.
=head4 Tabwidth
Set the number of spaces between columns.
=head4 Undef
Set the string that will be shown on the screen instead of an undefined field.
On MSWin32 only single-byte character sets are supported when setting I<Undef>, I<user>, I<host> or I<port> with the
C<db-browser>. Edit the configuration files directly if multi-byte encoded characters are required for these settings on
a machine with 'MSWin32' OS.
=head4 Debug
If I<Debug> is enabled, it is shown from where an error message was called.
=head3 Menu
=head4 Enchant
Set the behavior of different interactive menus:
- setting I<Menus config> to "Memory" means: save the selected configuration menu position while entering in a config
sub menu.
- setting I<Menu sql> to "Memory" means: save the selected SQL menu position while entering in a SQL sub menu.
- setting I<Menus db> to "Memory" means: save the selected menu position in the database/schema/table menus while
entering in a sub menu.
- setting I<Print Table> to "Expand" means: enable printing the chosen table row.
- setting I<Table Header> to "Each page" means: print the table header on top of each page.
=head4 Lock
Set the default I<lock> value:
- Lk0: Reset the SQL-statement after each "PrintTable".
- Lk1: Reset the SQL-statement only when a table is selected.
=head4 Mouse Mode
Set the I<mouse mode> (see L<Term::Choose/mouse>).
=head4 Sssc Mode
With the I<Sssc> mode "compat" enabled back-arrows are offered in the SQL menus entries. In the "simple" mode it can
be used the C<q> key instead of the back-arrows.
=head3 SQL
=head4 Max Rows
Set the maximum number of fetched table rows. This can be overwritten by setting a SQL C<LIMIT> statement.
The fetched table rows are kept in memory.
To disable the automatic limit set I<Max Rows> to 0.
=head4 Metadata
If I<Metadata> is enabled, system tables/schemas/databases are appended to the respective list.
=head4 Operators
Choose the required operators.
With this menu entry it is possible to mark items with the C<SpaceBar> key.
=head4 Parentheses
Enable parentheses in C<WHERE> and/or C<HAVING TO> clauses.
- C<(YES>: the position of "(" in the menu is before the column names.
- C<YES(>: the position of "(" in the menu is after the column names.
=head4 Regexp Case
If I<Regexp Case> is enabled, C<REGEXP> will match case sensitive.
With MySQL the sensitive match is achieved by enabling the C<BINARY> operator.
=head3 Database
=head4 DB Defaults
Set Database defaults.
The I<DB Defaults> can be overwritten for each database with the database menu entry "Database settings".
=over
=item Enable utf8
If I<Enable utf8> is enabled, the utf8 flag will be turned on for character data coming from the database.
For a more driver specific explanation see the documentation of the respective Perl DBI driver.
With the C<Pg> driver in use it is available - apart from the "YES/NO" choices - also an "AUTO" choice. If the
L<DBD::Pg> version is less than 3.0.0, "AUTO" is mapped to "YES". For the meaning of "AUTO" look in
L<pg_enable_utf8|https://metacpan.org/pod/DBD::Pg#pg_enable_utf8-integer> for the value C<-1>.
=item See if it's a number
If set to "YES", C<DBD::SQLite> tries to see if the bind values are numbers or not, and does not quote if they are
numbers.
See L<DBD::SQLite> for details. This is a SQLite-only option.
=item Default DB directories
Sets the default directories where C<db-browser> searches for SQLite databases. This is a SQLite-only option.
To move around in the directory tree select a directory and press C<Return> to enter in the selected directory or choose
"C< .. >" to move upwards. To add the current working-directory to the list of chosen directories use the "C< . >" menu
entry. To confirm the made choices select "C< = >". The ( "C< < >" ) menu entry resets the list of chosen directories if
any. If the list of chosen directories is empty, "C< < >" goes back without changing anything.
This setting can not be overwritten in a single database.
=item "Binary Filter"
Print "BNRY" instead of arbitrary binary data.
If the data matches the repexp C</[\x00-\x08\x0B-\x0C\x0E-\x1F]/>, it is considered arbitrary binary data.
Printing arbitrary binary data could break the output.
=item RESET
I<RESET> resets the database settings.
=back
=head4 DB Drivers
Choose the required database drivers.
=head4 DB Login
Determine when C<db-browser> asks for the login data:
=over
=item
Ask host/port per DB
If set to "YES" host and port data is asked once per database else the global settings are used if any.
=item
Ask user/pass per DB
If set to "YES" username and password data is asked once per database else only once.
=back
This option has no meaning if the SQLite driver is in use.
=head4 ENV DBI
- use C<DBI_USER> as username for all database logins if the environment variable exists and I<Ask user/pass> is set to
"Once".
- use C<DBI_PASS> as password for all database logins if the environment variable exists and I<Ask user/pass> is set to
"Once".
- use C<DBI_HOST> as host for all database connections if the environment variable exists and I<Ask host/port> is set to
"No".
- use C<DBI_PORT> as port for all database connections if the environment variable exists and I<Ask host/port> is set to
"No".
This options have no meaning if the SQLite driver is in use.
=head3 Insert
These I<Insert> settings can also be set temporarily in the C<INSERT INTO> sub-menu by selecting the prompt
"I<Customize:>".
=head4 Input modes
Choose how to enter the data, when inserting into a table.
=over
=item
Cols
It is prompted for each column.
=item
Rows
Enter a row at a time.
To parse the rows it is used C<Text::CSV>.
=item
Multirow
Enter all rows at once.
=item
File
Read the input from am file.
Supported file formats: CSV files and the spreadsheet formats supported by L<Spreadsheet::Read>.
=back
=head4 Input filter
Enable the col-row input filter. If this option is enabled and the input mode is set to "Multirow" or "File", the user
can choose which columns/rows from the input to use as insert values.
=head4 CSV parse module
Set which module to use for parsing text files or for parsing the "Multirow" input.
Files where C<-T $filename> returns true are considered text files.
If a file is not a text file, then it is always used C<Spreadsheet::Read> to parse the file regardless of this setting.
=over
=item
Text::CSV
The following csv options are used if L<Text::CSV> is chosen.
To decode the file it is used the I<CSV file encoding>
=item
Text::ParseWords
With L<Text::ParseWords> it is possible to use a regexp as delimiter - see the option I<T::PW: $delim>.
To decode the file it is used the I<CSV file encoding>
=item
Spreadsheet::Read
If L<Spreadsheet::Read> is chosen, the default settings from C<Spreadsheet::Read> are used.
C<Spreadsheet::Read> will use the first line of the file to auto-detect the separation character if the file is a CSV
file.
The following csv settings have no meaning when L<Spreadsheet::Read> is in use.
=back
=head4 CSV file encoding
How to decode csv files.
=head4 csv sep_char
Set the csv sep_char.
=head4 csv quote_char
Set the csv quote_char.
=head4 csv escape_char
Set the csv escape_char.
=head4 csv various
Other different csv options.
=head4 T::PW: $delim
C<Text::ParseWords>: set the delimiter (C<$delim>). Can be a regexp.
=head4 T::PW: $keep
See L<Text::ParseWords> for more information for the option C<$keep>.
=head4 File history
Set how many input file names should be saved. A value of 0 disables the file history.
=head2 CONFIGURATION FILES
The syntax of the configuration file names is C<"conf_${driver_name}.json">. To find out the location of the
configuration files call C<db-browser -h> and choose I<Path>.
The data is saved in JSON format.
The global settings are placed in the member called C<"*$db_driver">. This settings are used if I<ask per DB> in
I<DB Login> is not enabled. If I<ask per DB> is enabled each database uses is own member named like the database itself.
With the C<SQLite> driver "database name" means the absolute path to the database file.
Sub-members (keys):
SQLlite: mysql: Pg:
sqlite_unicode (0,1) user user
sqlite_see_if_its_a_number (0,1) host host
binary_filter (0,1) port port
dirs_sqlite_search ¹ mysql_enable_utf8 (0,1) pg_enable_utf8 (0,1,-1)
binary_filter (0,1) binary_filter (0,1)
¹ only in C<"*SQLite">: expects an array-reference as its value. C<db-browser> searches for SQLite databases in the
directories passed with this array-reference.
Examples
conf_SQLite.json: conf_mysql.json:
{ {
"*SQLite" : { "*mysql" : {
"binary_filter" : 0, "binary_filter" : 0,
"dirs_sqlite_search" : [ "host" : "localhost",
"/home/my/Documents", "mysql_enable_utf8" : 1,
"/home/my/databases" "port" : null,
], "user" : "name"
"sqlite_see_if_its_a_number" : 1, },
"sqlite_unicode" : 1 "database1" : {
}, "mysql_enable_utf8" : 0,
"/home/my/databases/db1.sqlite" : { "host" : "my_host",
"binary_filter" : 1, "user" : "user_5"
"sqlite_unicode" : 0 },
} "database2" : {
} "binary_filter" : 1
}
}
=head1 REQUIREMENTS
See L<Term::TablePrint/REQUIREMENTS>.
=head2 Perl version
Requires Perl version 5.10.0 or greater.
=head2 Decoded strings
C<db-browser> expects decoded strings.
=head2 Encoding layer for STDOUT
For a correct output it is required an appropriate encoding layer for STDOUT matching the terminal's character set.
=head2 Terminal
It is required a terminal that uses a monospaced font which supports the printed characters.
Also the terminal has to understand ANSI escape sequences. If the OS is MSWin32 C<App::DBBrowser> uses
L<Win32::Console::ANSI> which emulates an ANSI console for the C<db-browser>.
The terminal should have a width of at least 40 print columns.
=head1 SEE ALSO
L<DBI>, L<DBD::SQLite>, L<DBD::mysql>, L<DBD::Pg>.
=head1 CREDITS
Thanks to the L<Perl-Community.de|http://www.perl-community.de> and the people form
L<stackoverflow|http://stackoverflow.com> for the help.
=head1 AUTHOR
Matthäus Kiem <cuer2s@gmail.com>
=head1 LICENSE AND COPYRIGHT
Copyright 2012-2014 Matthäus Kiem.
This program is free software; you can redistribute it and/or modify it under the same terms as Perl 5.10.0. For
details, see the full text of the licenses in the file LICENSE.
=cut