App-DBBrowser
view release on metacpan or search on metacpan
bin/db-browser view on Meta::CPAN
=head1 NAME
C<db-browser> - Browse SQL databases and their tables interactively.
=head1 VERSION
Version 2.440
=head1 BACKWARD INCOMPATIBLE CHANGES
=over
=item
Table names and column names are now unquoted by default (see L</Identifiers>).
=item
Saved subqueries are saved now at plugin level instead at driver level.
=item
How plugin and database configuration data is saved has changed.
=back
=head1 SYNOPSIS
db-browser -h|--help
db-browser
db-browser database, ...
When the C<db-browser> is called with the argument C<-h|--help>, it displays 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.
If C<db-browser> is called with arguments, those arguments are treated as the list of available databases (or as
available ODBC data sources when using the DBD::ODBC driver).
=head2 SQLite/Firebird
db-browser -s|--search
Calling C<db-browser> with C<-s|--search> initiates a new search for databases instead of using cached data.
=head1 DESCRIPTION
Before using C<db-browser>, ensure you have backed up your databases.
Search and read in SQL databases: one can interactively browse databases and their tables.
C<App::DBBrowser> provides and supports plugins for the DBI drivers C<DBD::SQLite>, C<DBD::mysql>, C<DBD::MariaDB>,
C<DBD::Pg>, C<DBD::Firebird>, C<DBD::DB2>, C<DBD::Informix>, C<DBD::Oracle> and C<DBD::ODBC>. Refer to
L<App::DBBrowser::DB> for guidance on writing a database plugin.
=head1 NAVIGATION
=head2 Menus
=over
=item *
Use the C<Arrow> keys (or C<h,j,k,l>) to move up and down and to move right or left.
=item *
Press the C<PageUp> key (or C<Ctrl-P>) to go to the previous page and the C<PageDown> key (or C<Ctrl-N>) to go to the
next page.
=item *
Press the C<Home> key (or C<Ctrl-A>) to jump to the beginning of the menu and the C<End> key (or C<Ctrl-E>) to jump to
the end of the menu.
=back
When the I<mouse> option is enabled, you can use the left mouse key to navigate through the menus.
To confirm a chosen menu item, use the C<Return> key.
In some submenus, it is possible to select more than one item before pressing C<Return> (e.g., the input filters
I<Choose Rows> and I<Choose Cols>); in such submenus, the list of items marked with the C<SpaceBar> is added to the
chosen items when C<Return> is pressed. If the mouse mode is enabled, you can use the right mouse key instead of the
C<SpaceBar>. Pressing C<Ctrl-SpaceBar> inverts the made choices - for example, to select all but one, select the one
with the C<SpaceBar> and then press C<Ctrl-SpaceBar>.
=head2 Read-line
=over
=item *
Use C<BackSpace> or C<Strg-H> to delete the character behind the cursor and C<Delete> to delete the character at the
cursor.
=item *
Press C<Strg-U> to delete the text backward from the cursor to the beginning of the line and C<Strg-K> to delete the
text from the cursor to the end of the line.
=item *
Use C<Right-Arrow> to move forward a character and C<Left-Arrow> to move back a character.
=item *
Press C<Page-Up> to move back 10 characters and C<Page-Down> to move forward 10 characters.
=item *
Use C<Home> or C<Strg-A> to move to the start of the line and C<End> or C<Strg-E> to move to the end of the line.
=back
Some C<read-line>s have predefined values. You can access these values with the C<Up-Arrow> and C<Down-Arrow> keys.
To exit a C<read-line> without returning anything, press C<Ctrl-X>. When the input buffer is empty and C<Enter> is
pressed, C<read-line> returns an empty string.
=head1 OUTPUT
The elements are right-justified if they look like a number; otherwise, they are left-justified.
If the option I<squash_spaces> is enabled, leading and trailing spaces are removed from the elements, and spaces are
squashed to a single white space.
Tab characters (C<\t>) are replaced with a space.
Vertical spaces (C<\v>) are squashed to two spaces.
Control characters, code points of the surrogate ranges and non-characters are removed.
See also the option group L</Output>.
C<Ctrl-F> opens a prompt. A regular expression is expected as input. This enables one to display only rows where at
least one column matches the entered pattern. See option L</Search>.
=head1 MENUS
=head2 Plugins Menu
Choosing a plugin leads to the I<Databases Menu>.
(If there is only one plugin, the plugins menu is not shown, but the plugin is chosen automatically.)
=head2 Databases Menu
Choosing a database leads to the I<Schemas Menu>.
(If there is only one database, the databases menu is not shown, but the database is chosen automatically.)
=head2 Schemas Menu
Choosing a schema leads to the I<Tables Menu>.
(If there is not more than one schema, the schemas menu is not shown, but the schema is chosen automatically.)
=head2 Tables Menu
The menu entries of the tables menu:
=head3 Prompt Line
Selecting the prompt line (the database name) opens a menu with these entries:
=over
=item
Create Table
=item
Drop Table
=item
Create View
=item
Drop View
=item
Attach DB
=item
Detach DB
=back
For I<Create Table>, I<Drop Table>, I<Create View> and I<Drop View>, see L</WRITE ACCESS>.
These entries are available if enabled in L</Extensions>.
I<Attach DB> is available for C<SQLite> databases. I<Attach DB> can be used to attach databases to the current database.
=head3 Tables Entries
Choosing one of the tables leads to the I<SQL Menu>.
=head3 Subquery
I<Subquery> is available if enabled in L</Extensions>.
Enter a subquery and use it as a table.
The query can be created in different ways:
=over
=item SQL Menu
Build the query with the SQL Menu.
Before returning the subquery, the result of the subquery can be displayed by selecting the prompt line of the menu.
This does not work if the subquery is a correlated subquery.
=item Readline
Write the query in the prompt.
=item Stored Queries
If stored queries are available, it is possible to select one of those queries.
There are three different types of stored queries:
- permanently saved queries
- previously entered queries
- queries printed with I<Print TABLE>
Queries can be saved permanently by selecting the prompt line (I<Choose:>) in the I<Subquery> menu.
=back
=head3 CTE
Common table expression, available if enabled in L</Extensions>.
=over
=item New CTE
The user is asked for the CTE query and the CTE name.
The syntax of the CTE name is:
[RECURSIVE] cte_name [(col_name [, col_name] ...)]
L<Subquery> above describes how to build/enter the query.
=item Available CTEs
Select from the built CTEs.
=item Remove
Remove CTEs.
=back
=head3 Join
Join tables.
This entry is available if enabled in L</Extensions>.
=head3 Union
Combine the result from multiple C<SELECT> statements with C<Union>, C<Intersect> and C<Except> (C<Firebird>: Union).
This entry is available if enabled in L</Extensions>.
=head2 SQL Menu
SQL statements are created in this menu.
The SQL Menu has the following menu entries:
=head3 Prompt Line
Selecting this prompt line (I<Customize:>) opens a menu with these entries:
=over
=item
Insert
=item
Update
=item
Delete
=back
See L</WRITE ACCESS>. These entries are available if enabled in L</Extensions>.
=head3 BACK
If the user has set substatements, selecting C<BACK> will reset all changes made.
If no changes have been made or if the changes have been reset, selecting C<BACK> will exit the SQL menu.
=head3 Print TABLE
Select I<Print TABLE> to show the result of the formed statement on the screen.
=head3 The Sub-Statements Entries
In a SQL submenu (e.g., C<WHERE>), the back arrow resets the subquery step by step. With no items left, the SQL submenu
is left when the back arrow is chosen.
To activate the aggregate mode, enter the GROUP BY submenu and press C<-OK-> (with or without selecting GROUP BY
columns).
See also L</Extensions> and L</Operators>
=head4 SELECT
Choose the required columns for the C<SELECT> substatement.
It is possible to add many columns at once by marking them with the C<SpaceBar> key. If columns are marked with the
C<SpaceBar>, the highlighted column is not added to the chosen when C<Return> is pressed.
When aggregate mode is active, the user can select from the available aggregate functions C<AVG>, C<COUNT>, C<COUNT(*)>,
C<GROUP_CONCAT>, C<MAX>, C<MIN>, C<SUM> and the added GROUP BY columns.
=head4 DISTINCT
=head4 WHERE
=head4 GROUP BY
Selected GROUP BY columns are added automatically to the selected columns.
=head4 HAVING
=head4 ORDER BY
=head4 LIMIT
=head4 OFFSET
=head3 Export
Export the result of the chosen SQL statement to a C<CSV>-file. See options L</Export Data>.
=head1 WRITE ACCESS
=head2 Create/Drop Table/View
I<Create/Drop Table/View> can be accessed by selecting the prompt-line in the L</Tables Menu> if the respective option
is activated.
Selecting the prompt line of the I<Create/Drop Table/View> submenu allows you to set the parse settings described in
L</Import Data>.
=head3 Create Table
If the option I<Source Type> is set to I<menu>, selecting I<Create Table> opens a submenu where you can choose the type
of the data source. If I<Source Type> is set to I<plain> or I<file>, the set data source type is automatically used
without displaying a menu. For more information, see L</Data Input>.
You will be prompted:
=over
=item
for the table name
=item
if the first data row should be used as a header
Selecting the prompt line of this menu allows you to set the 'create table' options.
=item
if an auto increment column should be added (if the option is enabled in L</Create-Table>)
=item
to edit the column names
=item
to edit the column data types
=item
to confirm the creation of the table
=back
=head3 Drop Table
Dropping a table displays the entire table before confirming the deletion.
=head3 Create View
Before creating a view, print the required select statement to make it available in the subquery menu.
=head3 Drop View
Drop a view. The entire view is shown before the user confirms dropping it.
=head2 Insert/Update/Delete
I<Insert>, I<Update> and I<Delete> can be accessed by selecting the prompt-line in the L</SQL Menu> if the respective
option is activated.
I<Delete>, I<Update>, or I<Insert> may not be available with C<Join> and C<Union>/C<Intersect>/C<Except> statements.
=head3 Insert
If the option I<Source Type> is set to I<menu>, selecting I<Insert> opens a submenu where one can choose the type of
the data source. If I<Source Type> is set to I<plain> or I<file>, the set data source type is used without showing a
menu. For more information, see L</Data Input>.
If the first column of a table is an auto-increment column, the C<INSERT INTO> statement is built without this first
column. This feature is available if supported by the database plugin.
=head3 Update
Update records. The affected (and not yet updated) records are shown before the user confirms the update.
=head3 Delete
Delete records. The affected records are shown before the user confirms the deletion.
=head2 Data Input
=head3 Data Source
=over
=item plain
Insert the data cell by cell.
=item file
The data is read from a chosen file.
Supported file formats: text files and file formats supported by L<Spreadsheet::Read>. C<Spreadsheet::Read> uses
different parser modules for the different file formats - see L<Spreadsheet::Read/DESCRIPTION>. The parser modules are not
installed automatically. It is up to the user to install the required parser modules.
Selecting I<file> opens the I<Source directory> submenu where one can choose the directory which should be
searched for files.
If the L</Directory History> is set to C<1>, the I<Source directory> submenu is skipped and the directory is chosen
automatically.
After a directory has been chosen, a submenu is opened where one can choose the source file.
Selecting the prompt line of the I<Files> submenu (I<Choose a File:>) allows one to set the parse settings described in
L</Import Data>.
After the data has been read, the user can apply different input filters (See L</Input Filters>).
=back
=head3 Input Filters
=over
=item Choose Cols
bin/db-browser view on Meta::CPAN
Feb | 10 | 23
I<Merge Rows> can be used to edit a row by selecting only one row.
=item Join Columns
Item | length | width | height Item | length/width/height
------|--------|-------|-------- -----|-------------------
423 | 40 | 30 | 25 423 | 40/30/25
------|--------|-------|-------- -----|-------------------
64 | 80 | 60 | 30 64 | 80/60/30
------|--------|-------|-------- -----|-------------------
705 | 50 | 50 | 40 705 | 50/50/40
I<Join Columns> can be used to edit a column by selecting only one column.
=item Fill up Rows
If the input table rows have different numbers of cells, this filter appends empty cells to the rows until the cell
count of each row is equal to the cell count of the row with the highest cell count.
You can enter the I<Row Group> filter to check if the table has rows with different lengths. If I<Row Group> shows only
one group, all rows have the same length.
=item Cols to Rows
Transpose columns to rows.
Year | 2000 | 2001 | 2002 Year | Max | Min
------|------|------|------ -----|-----|----
Max | 14 | 22 | 17 2000 | 14 | 11
------|------|------|------ -----|-----|----
Min | 11 | 10 | 9 2001 | 22 | 10
-----|-----|----
2002 | 17 | 9
=item Empty to NULL
Convert fields containing an empty string to NULL (undefined).
The default values for this filter can be set using the option L</Empty to Null>.
=item Reset
Reset the filters.
=item Reparse
Change the parse settings and reparse the data with the new settings.
=back
=head1 GLOABL SETTINGS
=head2 Select plugins
Choose the required database plugins.
=head2 Gloabl settings
=head3 Menu Memory
If enabled, saves the menu position while entering a submenu.
=head3 Mouse Mode
Set the I<Mouse mode> (see L<Term::Choose/mouse>).
=head1 OPTIONS
The options menu is accessed with C<db-browser -h>.
Select the database plugin and then configure the following options.
=head2 Connect data
By selecting the prompt line of the plugin settings menu (Configure plugin-name), you can set the connect data at
database level.
=over
=item Fields
Set which fields are required to connect to a database.
=item Login Data
The entered login data is saved in a configuration file and used to connect to the database (the password cannot be
saved).
=item ENV Variables
The user can choose environment variables from a list of environment variables that should be used - if set - to connect
to the database.
=item Attributes
For the meaning of these driver-specific attributes, refer to the appropriate driver documentation.
=back
=head2 Extensions
Add menu entries to menus:
=head3 Tables Menu
=over
=item Subquery
=item CTE
=item Join
=item Union
=back
=head3 Join Menu
=over
=item Subquery
=item CTE
=back
=head3 Union Menu
=over
=item Subquery
=item CTE
=item Where
=item Parentheses
=back
Note: Parentheses are not available in C<SQLite> and C<Firebird>.
These settings are also valid when C<Intersect> or C<Except> is used.
=head3 Columns and Values
=over
=item
Extended Columns
If enabled, C<%%> is shown as an additional menu entry in the column menus. Selecting C<%%> lets one choose between the
available extensions.
=item
Extended Values
If enabled, then - in addition to constants - subqueries, functions and more (see the following list) are available for
the right side of operators. Places where extended values are available: C<WHERE>, C<HAVING>, C<SET> in update, and
C<WHEN>, C<THEN> and C<ELSE> in case expressions.
=item
Extended Arguments
If enabled, subqueries, functions and more (see the following list) are available for entering arguments in addition to
constants in the scalar and window functions.
This option can also be enabled later in the functions menu by selecting the prompt line.
=back
=head4 Column/Value/Argument Extensions
Not all of these extensions are available everywhere.
=over
=item Value
Enter a constant value.
If the data type is numeric or undefined and the literal looks like a number, it is not enclosed in quotation marks.
In all other cases, the literal is enclosed in quotation marks.
bin/db-browser view on Meta::CPAN
=back
Not all of these settings are available for any alias type.
=head3 Aliases in Clauses
Use aliases in
=over
=item
GROUP BY
=item
HAVING
=item
ORDER BY
=back
Not all RDBMSs support aliases in GROUP BY and HAVING.
=head3 Identifiers
=over
=item
Qualified Table Names
=item
Quote Table Names
=item
Quote Column Names and Aliases
=back
C<SQLite>: If the current database has attached databases, the use of qualified table names is enabled automatically.
C<SQLite>: Database names in SQL statements (C<ATTACH DATABASE>) are always quoted.
C<Informix>: Set the C<DELIMIDENT> environment variable to allow quoted identifiers.
=head3 View Prefix
Enter a string that should be automatically placed in front of all view names.
=head3 Other
=over
=item
Subqueries created with the 'SQL Menu': Allow editing.
When enabled, subqueries created using the 'SQL Menu' can be edited before they are used.
=item
Pg: Convert to C<text> automatically when required.
If the driver is C<Pg>: Convert columns in string functions automatically to C<text> if the datatype of the column is
not a char like datatype. If the datatype is unknown and the value looks like a number, it is also converted to C<text>.
=back
=head2 Create-Table
=head3 Enable Options
=over
=item
Auto Increment
Enabling I<Auto Increment> makes the 'auto-increment primary key' option available when creating a table.
=item
Data Type Guessing
When this option is enabled and a table is created, C<SQL::Type::Guess> is used to guess the data types. These data types
are then used as defaults when the user is prompted for data types.
=item
Encode data for type guessing
If enabled, the data passed to C<SQL::Type::Guess> is encoded.
The setting of this option can affect the size of C<max> in C<varchar(max)> if strings contain multibyte characters.
=back
=head3 Add Form Fields
=over
=item
Table Constraint Fields
Specify the number of fields available for entering table constraints (0-9).
=item
Table Option Fields
Specify the number of fields available for entering table options (0-9).
=back
=head3 Auto Increment Column Name
Set the default value for the auto-increment primary key column name.
( run in 0.762 second using v1.01-cache-2.11-cpan-39bf76dae61 )