db-browser - Browse SQLite/MySQL/PostgreSQL databases and their tables interactively.
db-browser -h|--help db-browser db-browser [database-name, ...]
db-browser is called with the argument
-h|--help, it shows a menu. The menu entry HELP shows this documentation - see "OPTIONS".
db-browser called without arguments, the user can choose from the databases offered by the database plugin. The database is chosen automatically if it is available only one database. With the
SQLite driver is in use, the option Search directories tells the database plugin where to search for
SQLite databases (defaults to the home directory).
db-browser is called with arguments, the arguments are used as the available databases.
db-browser called with
-s|--search causes a new search for SQLite databases instead of using the cached data.
Search and read in SQL databases. With the
db-browser one can browse databases and their tables interactively.
The database plugins bundled with
App::DBBrowser provide support for the DBI drivers
DBD::Pg. See App::DBBrowser::DB how to write a database plugin.
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.
db-browser expects an existing home directory with read and write permissions for the user of the
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
h,j,k,l) to move up and down and to move to the right and to the left.
Ctrl-B) to go back one page, the
Ctrl-F) to go forward one page.
Ctrl-A) to jump to the beginning of the menu, the
Ctrl-E) to jump to the end of the menu.
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
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
To move backwards in the menu hierarchy one can press the
q key. When prompted for a string, try
Ctrl-D instead of
The SQL menu is the menu which opens after a table was selected.
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
GROUP BY statement.
To reset a SQL "sub-statement" (e.g
WHERE) re-enter into the respective menu entry and choose
'- OK -'.
Changing the lock mode (
Lk1) resets the entire SQL.
To get to the Delete/Update/Insert menu, navigate to the SQL menu and then select two times the prompt.
INSERT INTO are not available with
UNION statements (except
To reach the Create table/Drop table menu, select the prompt in the menu where the tables are selected.
To create a table without inserting anything, enter as the first and only row the column names and then choose "Use the first row as column names".
The scalar functions can be reached in the main SQL menu and also in the
UPDATE SQL sub-menus by selecting the prompt.
The available functions are:
With SQLite the function
TRUNCATE is a user-defined function which returns stringified values.
return sprintf "%.*f", $places, int( $number * 10 ** $places ) / 10 ** $places;
When comparing in
HAVING TO clauses with numbers, take the non-truncated (original) value for the comparison if
sqlite_see_if_its_a_number is enabled (default).
Also to get a numeric comparison in an
ORDER BY clause use the non-truncated (original) values for the ordering.
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
With SQLite the function
Char_Length is a user-defined function which uses the Perl builtin
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.
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.
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.
It depends on the database plugin which items are offered to set in each option and whether the selections made by the user a considered.
Set which fields are required to connect to a database.
Which environment variables should be used.
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 the different attributes (apart binary_filter) see the documentation of the DBI database driver.
The last entry of this sub-menu is binary_filter. Setting it 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.
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 "
. " menu entry. To confirm the made choices select "
= ". 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:
- setting Config Menus to "Memory" means: save the selected configuration menu position while entering in a config sub menu.
- setting SQL Menu to "Memory" means: save the selected SQL menu position while entering in a SQL sub menu.
- setting DB Menus to "Memory" means: save the selected menu position in the database/schema/table menus while entering in a sub menu.
- "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.
With MySQL the sensitive match is achieved by enabling the
Set the default lock value:
- Lk0: Reset the SQL-statement after each "PrintTable".
- Lk1: Reset the SQL-statement only when a table is selected.
Enable parentheses in
HAVING TO clauses.
Set the maximum number of fetched table rows. This can be overwritten by setting a SQL
The fetched table rows are kept in memory.
To disable the automatic limit set Max Rows to
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 setting Undef, user, host or port with the
db-browser. Edit the configuration files directly if multi-byte encoded characters are required for these settings on a machine with 'MSWin32' OS.
These Insert settings can also be set in the
INSERT INTO sub-menu by selecting the prompt.
INSERTing data into a table - choose the available input modes:
It is prompted for each column.
Enter a row at a time.
To parse the rows it is used
Enter many rows. Reads until the end of input. It is OS-depend how to indicate the end of input.
Read the input from am file.
Supported file formats: csv-files and the spreadsheet formats supported by Spreadsheet::Read.
Searching for input files: set the default directory.
Set how many input file names should be saved. A value of
0 disables the file history.
How to decode csv files.
Set how to parse text files or the "multi row" input. Files where
-T $filename returns true are considered text files. If a file is not a text file, then it is always used
Spreadsheet::Read to parse the file regardless of this setting.
Use Text::CSV to parse text files and the "multi row" input. To decode the files it is used the File encoding.
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 File encoding.
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.
Set different Text::CSV options.
Set the input record separator (regexp).
Set the input field separator (regexp).
If "Add auto increment primary key" is used, this sets the default name of the added column.
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.
The Database configuration file: the member of the global database settings is called
"*$db_plugin" whereas members of database specific settings are named like the database itself (for
SQLite: "database name" means the absolute path to the database file).
SQLite: mysql: Pg: sqlite_unicode (0,1) user user sqlite_see_if_its_a_number (0,1) host host binary_filter (0,1) port port directories_sqlite¹ mysql_enable_utf8 (0,1) pg_enable_utf8 (0,1,-1) binary_filter (0,1) binary_filter (0,1)
¹ directories passed as an array-reference
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
Matthäus Kiem <email@example.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.