#!/usr/bin/env perl
use warnings;
use strict;
use 5.008003;
no warnings 'utf8';
our $VERSION = '2.014';
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 C<SQLite>/C<MySQL>/C<PostgreSQL> databases and their tables interactively.
=head1 VERSION
Version 2.014
=cut
=head1 SYNOPSIS
=head2 SQLite/MySQL/PostgreSQL
db-browser -h|--help
db-browser
db-browser [database-name, ...]
When the C<db-browser> is called with the argument C<-h|--help>, it shows a menu. The menu entry I<HELP> shows this
documentation - see L</OPTIONS>.
If C<db-browser> is called without arguments, the user can choose from the databases offered by the database plugin. The
database is chosen automatically if only one database is available. With the C<SQLite> driver in use, the option
I<Search directories> tells the database plugin where to search for C<SQLite> databases (defaults to the home directory).
If C<db-browser> is called with arguments, the arguments are used as the available databases.
=head2 SQLite
db-browser [-s|--search]
C<db-browser> called with C<-s|--search> causes a new search for C<SQLite> databases instead of using the cached data.
=head1 DESCRIPTION
Before using C<db-browser>, make sure you backed up your databases.
Search and read in SQL databases: one can browse databases and their tables interactively.
The database plugins bundled with C<App::DBBrowser> provide support for the DBI drivers C<DBD::SQLite>, C<DBD::mysql>
and C<DBD::Pg>. See L<App::DBBrowser::DB> how to write a database plugin.
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.
=head1 NAVIGATION
=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 (for example I<Choose Rows>
and I<Choose Columns> from the input filter - see L<Insert Into>); 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>. A C<Ctrl-SpaceBar> inverts the made choices
- so for example with no items chosen C<Ctrl-SpaceBar> chooses everything.
To leave a "readline" without entering anything enter the C<eof> control character (C<Ctrl-D> for *nix). Pressing only
the C<Enter> key means entering an empty string.
=head1 MENUS
=head2 Plugin Menu
Choosing a plugin leads to the I<Database Menu>.
If there is only one plugin, the menu is not shown but the plugin is chosen automatically.
=head2 Database Menu
Choosing a database leads to the I<Schema Menu>.
If there is only one database, the menu is not shown but the database is chosen automatically.
=head2 Schema Menu
Choosing a schema leads to the I<Table Menu>.
If there is not more than one schema, this menu is not shown but the schema is chosen automatically.
=head2 Table Menu
The menu entries of the table menu:
=head3 Prompt Line
Selecting the prompt line (the database name) opens a menu with this entries:
=head4 Create Table
Selecting I<CREATE Table> opens a sub-menu with this entries:
=over
=item Plain
Create a table.
=item Copy and Paste
Reads from STDIN until the end of input. Then it creates a table and inserts the records of fields parsed from the input
data. In I<Settings> one can configure to how the data is parsed.
C<SQLite> only: when ask for the column names the first entry is the C<ai> column if the option L<Auto incr col name> is
set. If C<ai> is set, an auto-increment column is added automatically. One can disable this per table by removing the
value from the C<ai> slot.
See also L<Insert Into>.
=item From File
Like I<Copy and Paste> with the difference that the data is read from a chosen file.
=item Settings
Selecting I<Settings> allows one to set the options described in option group L<Create and Insert> for the current
database.
=back
=head4 Drop table
Drop a table. The whole table is shown (if not empty) before the user confirms to drop the table.
=head4 Attache DB
I<Attache DB> is available only for C<SQLite> databases. I<Attach DB> can be used to attach databases to the current
database.
=head4 Detach DB
This entry is available if a SQLite database has attached databases.
=head4 SQ File
This menu entry is available if at least one of the sub-queries options is enabled.
Selecting this entry allow one to edit the file with the saved statements. It is possible to add, remove and
edit statements. Adding a statement can be done by entering a string via C<readline> or by choosing a statement from the
list of the temporary saved statements. (The last 20 statements from a printed table are saved in memory until the current
database goes out of scope).
=head3 Tables
Choosing a table leads to the I<SQL Menu>.
=head3 SQ
Choosing this entry allows one to enter a sub-query instead of table name.
This entry is available only if L<Subqueries as table> is enabled.
=head3 Join
Join tables.
=head3 Union
Combine the result from multiple SELECT statements.
=head3 Settings
See L<DB Settings>.
=head2 SQL Menu
In this menu it can be formed the SQL query.
The SQL Menu has the following menu entries:
=head3 Prompt Line
Selecting this prompt line (Customize:) opens a menu with these entries:
(C<DELETE>, C<UPDATE> or C<INSERT INTO> are not available with C<JOIN> and C<UNION> statements (except C<INSERT> with
C<mysql> and C<JOIN>))
=head4 Insert Into
Three different ways to insert data into a table are available:
=over
=item Plain
Insert data column by column.
=item Copy and Paste
Multi line input: reads until the end of input. It is OS-depend how to indicate the end of input.
=item From File
Read the input from am file. Supported file formats: text files and file formats supported by L<Spreadsheet::Read>.
=back
Setting the insert mode to I<From File> or to I<Copy and Paste> enables different filters: so it is possible to insert
only selected columns or selected rows.
SQLite: If the first column of a table is an auto-increment column, an C<INSERT INTO> statement is build without this
first column.
=head4 Update
Update records.
If the option L<Subqueries as SET value> is enabled, one can use the result of a sub-queries as the value of C<SET>.
=head4 Delete
Delete records.
=head3 The Sub Statements
Use I<Print TABLE> to get the output from the formed statement.
To reset a SQL "sub-statement" (e.g C<WHERE>) re-enter into the respective menu and choose C<-OK->.
=over
=item SELECT
If the option I<Subqueries in SELECT> is enabled, it is possible to use a sub-query instead a column by selecting C<(Q>.
=item AGGREGATE
Chosen aggregate functions are added automatically to the SELECT columns.
=item DISTINCT
=item WHERE
If the option I<Subqueries in WHERE/HAVING> is enabled, one can use sub-queries on the left side of an operator by
selecting C<(Q> and on the right side of an operator by selecting C<=(Q>.
=item GROUP BY
Chosen C<GROUP BY> columns are added automatically to the SELECT columns.
=item HAVING
If the option I<Subqueries in WHERE/HAVING> is enabled, one can use sub-queries on the right side of an operator by
selecting C<=(Q>.
=item ORDER BY
=item LIMIT
=back
=head3 Func
Opens the scalar functions menu.
To reset a modified a column select the column with the function a second time.
The available scalar functions are:
=head4 Epoch_to_DateTime
=head4 Epoch_to_Date
=head4 Truncate
With C<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> clauses with numbers, take the non-truncated (original) value for the
comparison.
Also to get a numeric sort in an C<ORDER BY> clause use the non-truncated (original) values for the ordering.
=head4 Bit_Length
With C<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 C<SQLite> the function C<Char_Length> is a user-defined function which uses the Perl builtin C<length> to get the
number of characters.
=head3 Lk1
Changing the I<lock> mode (C<Lk0>,C<Lk1>) resets the entire SQL.
=head1 OPTIONS
The options menu is called with C<db-browser -h>.
=head2 HELP
Show this Info.
=head2 Path
Shows the version and the path of the running C<db-browser> and the path of the application directory.
=head2 DB
=head3 DB Plugins
Choose the required database plugins.
=head3 DB Settings
These driver specific I<DB Settings> are used as the default database settings.
There is also in each L<Tables Menu> the entry I<Settings> to make database specific settings. If no database
specific settings are set, these global (to the database plugin) I<DB Settings> are used.
User defined database plugins: it depends on the plugin which items are offered to set in each option and whether the
selections made by the user are considered.
=over
=item Fields
Set which fields are required to connect to a database.
=item ENV Variables
The user can choose environment variables form a list of environment variables that should be used - if set - to connect
to the database.
=item Login Data
The entered login data is saved in a configuration file and used to connect to the database (the password can not be
saved).
=item Attributes
For the meaning of these driver specific attributes check the appropriate driver documentation.
=item Reset DB
Reset database specific parameter to the global I<DB Settings>.
=back
=head2 Menu
Set the behavior of the interactive menus.
=head3 Menu Memory
If enabled: saves the menu position while entering in a sub menu.
=head3 Table
Expand Rows:
=over
=item C<YES>
if C<Return> is pressed, the selected table row is printed with each column in its own line.
=item C<NO>
don't expand table rows.
=item C<YES fast back>
do not expand the first row if the cursor auto-jumped to the first row.
=back
=head3 Mouse Mode
Set the I<Mouse Mode> (see L<Term::Choose/mouse>).
=head2 SQL
=head3 Metadata
If I<Metadata> is enabled, system tables/schemas/databases are appended to the respective list.
=head3 Operators
Choose the required operators.
There are two regexp entries: C<REGEXP> matches case sensitive while C<REGEXP_i> matches case insensitive.
With C<MySQL> the sensitive match is achieved by enabling the C<BINARY> operator.
=head3 Lock
Set the default I<lock> value:
- C<Lk0>: Reset the SQL-statement after each time a table is printed.
- C<Lk1>: Reset the SQL-statement only when a table is selected.
=head3 Auto Limit
Sets C<LIMIT> automatically to I<Auto Limit>. This can be overwritten by setting a SQL C<LIMIT> statement manually.
Fetched table rows are kept in memory.
To disable the automatic limit set I<Auto Limit> to C<0>.
=head3 Subqueries
If at least one of the following sub-query options is enabled, the last 20 I<Print TABLE>-statements are saved
temporary.
Available sub-queries: the statements saved in the L<SQ File> and the temporarily saved statements.
=over
=item I<Subqueries in SELECT>
Choose C<(Q> to add a query as a SELECT column.
=item I<Subqueries in WHERE/HAVING>
Choose C<(Q> to use a query instead of a column in a C<WHERE> clause.
Choose C<=(Q> to use a query as a value in a C<WHERE> or C<HAVING> clause.
=item I<Subqueries as SET value>
Choose C<=(Q> to use a query as a C<SET> value in an C<UPDATE>.
=item I<Subqueries as table>
Choose C<SQ> in the I<Tables Menu> to use a query instead of a table.
=back
=head3 I<Alias for complex columns>
If enable, it is asked for an alias for columns with a function, for sub-queries, for joined tables and for unions. If
entered nothing, no alias is added except for joined tables and unions which get a default alias.
=head3 I<Parentheses>
Enable parentheses in C<WHERE> and/or C<HAVING> clauses.
=head3 Identifiers
- I<Qualified table names>: if set to C<YES> qualified table names are used in SQL statements.
- I<Quote identifiers>: if set to C<YES> SQL identifiers are quoted.
C<SQLite>:
- databases in SQL statements (C<ATTACH DATABASE>) are always quoted.
- if the current database has attached databases, the use of qualified table names is enabled automatically.
=head3 Write access
Enable write access - use with care.
=over
=item Insert records
=item Update records
=item Delete records
=item Create table
=item Drop table
=back
=head2 Output
=head3 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>.
=head3 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.
=head3 Tabwidth
Set the number of spaces between columns.
=head3 Grid
Separate the columns from each other and the header from the body with lines.
=head3 Keep Header
Show the table header on top of each page.
=head3 Undef
Set the string that will be shown on the screen instead of an undefined field.
On C<MSWin32> only single-byte character sets are supported when entering the setting I<Undef>, I<user>, I<host> or
I<port> with the C<db-browser>s readline. But it is possible to edit the entry in configuration files directly after the
entry was created with this options menu.
=head3 Binary binary
Setting the I<binary_filter> to C<1> means: print "BNRY" instead of arbitrary binary data. If 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.
=head2 Create and Insert
=head3 Parse Tool
Set how to parse text files or the "multi row" 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
To decode the files it is used the I<File encoding>.
Advantages:
=over
=item
Allows to set different csv-related options.
=back
=back
=over
=item
split
Reads to whole input at once and splits the input with the input record separator (IRS) to get the records (rows). Then
it splits the records with the input field separator (IFS) to get the fields (columns) of each record.
To decode the files it is used the I<File encoding>.
Advantages:
=over
=item
The values assigned to the IRS and the IFS are treated as regexps.
=back
=back
=over
=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.
Advantages:
=over
=item
Auto-detects the separation character for csv-files
=back
=back
=over
=back
=head3 Text::CSV config
Set different L<Text::CSV> options.
=head3 'split' config
=over
=item
Record separator
Set the input record separator (regexp).
=item
Field separator
Set the input field separator (regexp).
=item
Trim leading
Expects a regex pattern. If set removes leading characters matching regexp from each field.
=item
Trim trailing
Expects a regex pattern. If set removes trailing characters matching regexp from each field.
=back
=head3 File encoding
How to decode text files.
=head3 File History
Set how many input file names should be saved. A value of C<0> disables the file history.
=head3 Default data type
The default data type of the columns.
=head3 Auto incr col name
SQLite only:
Set the auto-increment column name. If this option is set, a auto-increment column is added automatically to created
tables.
=head1 CONFIGURATION FILES
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.
=head1 REQUIREMENTS
See L<Term::TablePrint/REQUIREMENTS>.
=head2 Perl version
Requires Perl version 5.8.3 or greater.
=head2 Decoded strings
C<db-browser> expects decoded strings.
Non mappable characters will break the output.
=head2 Terminal
It is required a terminal that uses a monospaced font which supports the printed characters.
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>.
=head2 Permissions
To be able to browse the database-, schema- and table-lists and the content of tables the user must have the database
privileges required for fetching the requested data.
The C<db-browser> expects an existing home directory with read and write permissions for the user of the C<db-browser>.
=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-2018 Matthäus Kiem.
THIS SOFTWARE IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE
IMPLIED WARRANTIES OF MERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE.
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