db-browser - Browse SQLite/MySQL/PostgreSQL databases and their tables interactively.
db-browser
SQLite
MySQL
PostgreSQL
Version 2.005
db-browser -h|--help db-browser db-browser [database-name, ...]
When the db-browser is called with the argument -h|--help, it shows a menu. The menu entry HELP shows this documentation - see "OPTIONS".
-h|--help
If 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 SQLite driver in use, the option Search directories tells the database plugin where to search for SQLite databases (defaults to the home directory).
If db-browser is called with arguments, the arguments are used as the available databases.
db-browser [-s|--search]
db-browser called with -s|--search causes a new search for SQLite databases instead of using the cached data.
-s|--search
Search and read in SQL databases: one can browse databases and their tables interactively.
The database plugins bundled with App::DBBrowser provide support for the DBI drivers DBD::SQLite, DBD::mysql and DBD::Pg. See App::DBBrowser::DB how to write a database plugin.
App::DBBrowser
DBD::SQLite
DBD::mysql
DBD::Pg
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 Term::TablePrint for more details.
Non mappable characters will break the output.
The best way to find out how db-browser works is calling db-browser.
the Arrow keys (or h,j,k,l) to move up and down and to move to the right and to the left.
Arrow
h,j,k,l
the PageUp key (or Ctrl-B) to go back one page, the PageDown key (or Ctrl-F) to go forward one page.
PageUp
Ctrl-B
PageDown
Ctrl-F
the Home key (or Ctrl-A) to jump to the beginning of the menu, the End key (or Ctrl-E) to jump to the end of the menu.
Home
Ctrl-A
End
Ctrl-E
With the option 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 Return key.
Return
In some sub-menus it is possible to select more then one item before Return is pressed; in such sub-menus the list of items marked with the SpaceBar key including the highlighted item are added to the chosen items when Return is pressed. If a mouse mode is enabled, it can be used the right mouse key instead of the SpaceBar.
SpaceBar
To leave a "readline" without entering anything enter the eof control character (Ctrl-D for *nix). Pressing only the Enter key means entering an empty string.
eof
Ctrl-D
Enter
The SQL menu is the menu which opens after a table was selected.
If AGGREGATE or GROUP BY is set, the SELECT statement is automatically formed; a previous user defined SELECT statement is reset. A user defined SELECT resets a previous set AGGREGATE or GROUP BY statement.
AGGREGATE
GROUP BY
SELECT
To reset a SQL "sub-statement" (e.g WHERE) re-enter into the respective menu and choose -OK-.
WHERE
-OK-
Changing the lock mode (Lk0,Lk1) resets the entire SQL.
Lk0
Lk1
See option Expert for the use of subqueries.
To get to the Delete/Update/Insert menu, navigate to the SQL menu and then select the prompt line.
Three different ways to insert data into a table are available:
Insert data column by column.
Multi line input: reads until the end of input. It is OS-depend how to indicate the end of input.
In this mode leading and trailing whitespaces of the fields/columns are removed.
Read the input from am file. Supported file formats: text-files and file formats supported by Spreadsheet::Read.
Setting the insert mode to from file or copy and paste enables different filters: it possible to choose the columns and rows which should be inserted.
DELETE, UPDATE or INSERT INTO are not available with JOIN and UNION statements (except INSERT with mysql and JOIN).
DELETE
UPDATE
INSERT INTO
JOIN
UNION
INSERT
mysql
To reach the Create table/Drop table menu, select the prompt in the menu where the tables are selected.
Drop table: the table is shown (here Auto Limit has no effect, the whole table is fetched) before the user confirms to drop the table.
For SQLite databases there are additional entries are available in this menu: Attach DB to attach databases to the current database and if the current database has attached databases also Detach DB.
The scalar functions can be reached in the main SQL menu and also in the DELETE and UPDATE SQL sub-menus by selecting the entry Func.
The available functions are:
With SQLite the function TRUNCATE is a user-defined function which returns stringified values.
TRUNCATE
return sprintf "%.*f", $places, int( $number * 10 ** $places ) / 10 ** $places;
When comparing in WHERE or HAVING clauses with numbers, take the non-truncated (original) value for the comparison.
HAVING
Also to get a numeric sort in an ORDER BY clause use the non-truncated (original) values for the ordering.
ORDER BY
With SQLite the function Bit_Length is a user-defined function which uses the Perl builtin length. To make length return the number of bytes the bytes pragma is used.
Bit_Length
length
bytes
use
With SQLite the function Char_Length is a user-defined function which uses the Perl builtin length to get the number of characters.
Char_Length
To reset a modified a column select the column with the function a second time.
The options menu is called with db-browser -h.
db-browser -h
Show this Info.
Shows the version and the path of the running db-browser and the path of the application directory.
Choose the required database plugins.
These driver specific DB Settings are used as the default database settings.
There is also in each database sub-menu the menu entry Database settings. If these database specific parameter are not set, the global (to the database plugin) DB Settings are use instead.
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.
Set which fields are required to connect to a database.
The user can choose environment variables form a list of environment variables that should be used to connect to the database.
The entered login data is saved in a configuration file and used to connect to the database (the password can not be saved).
For the meaning of these driver specific attributes check the appropriate driver documentation.
This is a SQLite-only option.
Sets the directories where db-browser searches for SQLite databases. Defaults to the home directory.
To move around in the directory tree select a directory and press Return to enter in the selected directory or choose .. to move upwards. To add the current working-directory to the list of chosen directories use the . (single dot) menu entry. Select = to confirm the made choices. The < menu entry resets the list of chosen directories if any. If the list of chosen directories is empty, < goes back without changing anything.
..
.
=
<
This setting can not be overwritten in a single database.
Reset database specific parameter to the global DB Settings.
Set the behavior of the interactive menus.
If enabled: saves the menu position while entering in a sub menu.
Expand Rows:
YES
if Return is pressed, the selected table row is printed with each column in its own line.
NO
don't expand table rows.
YES fast back
do not expand the first row if the cursor auto-jumped to the first row.
Set the Mouse Mode (see "mouse" in Term::Choose).
If Metadata is enabled, system tables/schemas/databases are appended to the respective list.
Choose the required operators.
There are two regexp entries: REGEXP matches case sensitive while REGEXP_i matches case insensitive.
REGEXP
REGEXP_i
With MySQL the sensitive match is achieved by enabling the BINARY operator.
BINARY
Set the default lock value:
- Lk0: Reset the SQL-statement after each time a table is printed.
- Lk1: Reset the SQL-statement only when a table is selected.
Enables aggregate function in the SELECT substatement &.
&
If this option is enabled, the last 20 queries are saved temporary. Saved queries can than be chosen as subqueries by selecting (Q. A query is saved when the table is printed. If a new database is selected the query-history is removed.
(Q
If enable, it is asked for an alias for columns with a function, for subqueries, for joined tables and for unions. If entered nothing, no alias is added except for joined tables and unions which get a default alias.
Enable parentheses in WHERE and/or HAVING clauses.
- Qualified table names: if set to YES qualified table names are used in SQL statements.
- Quote identifiers: if set to YES SQL identifiers are quoted.
SQLite:
- databases in SQL statements (ATTACH DATABASE) are always quoted.
ATTACH DATABASE
- if the current database has attached databases, the use of qualified table names is enabled automatically.
Sets LIMIT automatically to Max Rows. This can be overwritten by setting a SQL LIMIT statement manually. Fetched table rows are kept in memory.
LIMIT
To disable the automatic limit set Max Rows to 0.
0
Columns with a width below or equal Colwidth are only trimmed if it is still required to lower the row width despite all columns wider than Colwidth have been trimmed to Colwidth.
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.
Set the number of spaces between columns.
Separate the columns from each other and the header from the body with lines.
Show the table header on top of each page.
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 entering the setting Undef, user, host or port with the db-browsers readline. But it is possible to edit the entry in configuration files directly after the entry was created with this options menu.
MSWin32
Setting the binary_filter to 1 means: print "BNRY" instead of arbitrary binary data. If data matches the repexp /[\x00-\x08\x0B-\x0C\x0E-\x1F]/, it is considered arbitrary binary data. Printing arbitrary binary data could break the output.
1
/[\x00-\x08\x0B-\x0C\x0E-\x1F]/
These Insert settings can also be set in the INSERT INTO sub-statement menu.
Set how many input file names should be saved. A value of 0 disables the file history.
How to decode text files.
Set how to parse text files or the "multi row" input. Files where -T $filename returns true are considered text files.
-T $filename
If a file is not a text file, then it is always used Spreadsheet::Read to parse the file regardless of this setting.
Spreadsheet::Read
Text::CSV
To decode the files it is used the File encoding.
Advantages:
Allows to set different csv-related options.
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.
The values assigned to the IRS and the IFS are treated as regexps.
If Spreadsheet::Read is chosen, the default settings from Spreadsheet::Read are used.
Spreadsheet::Read will use the first line of the file to auto-detect the separation character if the file is a csv-file.
Auto-detects the separation character for csv-files
Set different Text::CSV options.
Record separator
Set the input record separator (regexp).
Field separator
Set the input field separator (regexp).
Trim leading
Expects a regex pattern. If set removes leading characters matching regexp from each field.
Trim trailing
Expects a regex pattern. If set removes trailing characters matching regexp from each field.
Default data type
The default data type of the columns.
To find out the location of the configuration files call db-browser -h and choose Path. The data is saved in JSON format.
See "REQUIREMENTS" in Term::TablePrint.
Requires Perl version 5.8.3 or greater.
db-browser expects decoded strings.
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 App::DBBrowser uses Win32::Console::ANSI which emulates an ANSI console for the db-browser.
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 db-browser expects an existing home directory with read and write permissions for the user of the db-browser.
Thanks to the Perl-Community.de and the people form stackoverflow for the help.
Matthäus Kiem <cuer2s@gmail.com>
Copyright 2012-2018 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.
To install App::DBBrowser, copy and paste the appropriate command in to your terminal.
cpanm
cpanm App::DBBrowser
CPAN shell
perl -MCPAN -e shell install App::DBBrowser
For more information on module installation, please visit the detailed CPAN module installation guide.