- Create and Insert
- CONFIGURATION FILES
- LICENSE AND COPYRIGHT
db-browser - Browse
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 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).
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.
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
App::DBBrowser provide support for the DBI drivers
DBD::Pg. See 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 Term::TablePrint for more details.
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 (for example Choose Rows and Choose Columns from the input filter - see "Insert Into"); 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
Ctrl-SpaceBar inverts the made choices - so for example with no items chosen
Ctrl-SpaceBar chooses everything.
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.
Choosing a plugin leads to the Database Menu.
If there is only one plugin, the menu is not shown but the plugin is chosen automatically.
Choosing a database leads to the Schema Menu.
If there is only one database, the menu is not shown but the database is chosen automatically.
Choosing a schema leads to the Table Menu.
If there is not more than one schema, this menu is not shown but the schema is chosen automatically.
The menu entries of the table menu:
Selecting the prompt line (the database name) opens a menu with this entries:
Selecting CREATE Table opens a sub-menu with this entries:
Create a table.
- 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 Settings one can configure to how the data is parsed.
SQLiteonly: when ask for the column names the first entry is the
aicolumn if the option "Auto incr col name" is set. If
aiis set, an auto-increment column is added automatically. One can disable this per table by removing the value from the
See also "Insert Into".
- From File
Like Copy and Paste with the difference that the data is read from a chosen file.
Selecting Settings allows one to set the options described in option group "Create and Insert" for the current database.
Drop a table. The whole table is shown (if not empty) before the user confirms to drop the table.
Attache DB is available only for
SQLite databases. Attach DB can be used to attach databases to the current database.
This entry is available if a SQLite database has attached databases.
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
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).
Choosing a table leads to the SQL Menu.
Choosing this entry allows one to enter a sub-query instead of table name.
This entry is available only if "Subqueries as table" is enabled.
Combine the result from multiple SELECT statements.
See "DB Settings".
In this menu it can be formed the SQL query.
The SQL Menu has the following menu entries:
Selecting this prompt line (Customize:) opens a menu with these entries:
INSERT INTO are not available with
UNION statements (except
Three different ways to insert data into a table are available:
Insert data column by column.
- Copy and Paste
Multi line input: reads until the end of input. It is OS-depend how to indicate the end of input.
- From File
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 to 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
INSERT INTO statement is build without this first column.
If the option "Subqueries as SET value" is enabled, one can use the result of a sub-queries as the value of
Use Print TABLE to get the output from the formed statement.
To reset a SQL "sub-statement" (e.g
WHERE) re-enter into the respective menu and choose
If the option Subqueries in SELECT is enabled, it is possible to use a sub-query instead a column by selecting
Chosen aggregate functions are added automatically to the SELECT columns.
If the option Subqueries in WHERE/HAVING is enabled, one can use sub-queries on the left side of an operator by selecting
(Qand on the right side of an operator by selecting
- GROUP BY
GROUP BYcolumns are added automatically to the SELECT columns.
If the option Subqueries in WHERE/HAVING is enabled, one can use sub-queries on the right side of an operator by selecting
- ORDER BY
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:
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 clauses with numbers, take the non-truncated (original) value for the comparison.
Also to get a numeric sort in an
ORDER BY clause use the non-truncated (original) values for the ordering.
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
SQLite the function
Char_Length is a user-defined function which uses the Perl builtin
length to get the number of characters.
Changing the lock mode (
Lk1) resets the entire SQL.
The options menu is called with
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 "Tables Menu" the entry Settings to make database specific settings. If no database specific settings are set, these global (to the database plugin) 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.
Set which fields are required to connect to a database.
- 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.
- 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).
For the meaning of these driver specific attributes check the appropriate driver documentation.
- Reset DB
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.
Returnis pressed, the selected table row is printed with each column in its own line.
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.
MySQL the sensitive match is achieved by enabling the
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.
LIMIT automatically to Auto Limit. This can be overwritten by setting a SQL
LIMIT statement manually. Fetched table rows are kept in memory.
To disable the automatic limit set Auto Limit to
If at least one of the following sub-query options is enabled, the last 20 Print TABLE-statements are saved temporary.
Available sub-queries: the statements saved in the "SQ File" and the temporarily saved statements.
- Subqueries in SELECT
(Qto add a query as a SELECT column.
- Subqueries in WHERE/HAVING
(Qto use a query instead of a column in a
=(Qto use a query as a value in a
- Subqueries as SET value
=(Qto use a query as a
SETvalue in an
- Subqueries as table
SQin the Tables Menu to use a query instead of a table.
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.
Enable parentheses in
- 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.
- databases in SQL statements (
ATTACH DATABASE) are always quoted.
- if the current database has attached databases, the use of qualified table names is enabled automatically.
Enable write access - use with care.
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.
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.
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.
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.
To decode the files it is used the File encoding.
Allows to set different csv-related options.
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.
The values assigned to the IRS and the IFS are treated as regexps.
If Spreadsheet::Read is chosen, the default settings from
Spreadsheet::Readwill 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.
Set the input record separator (regexp).
Set the input field separator (regexp).
Expects a regex pattern. If set removes leading characters matching regexp from each field.
Expects a regex pattern. If set removes trailing characters matching regexp from each field.
How to decode text files.
Set how many input file names should be saved. A value of
0 disables the file history.
The default data type of the columns.
Set the auto-increment column name. If this option is set, a auto-increment column is added automatically to created tables.
To find out the location of the configuration files call
db-browser -h and choose Path. The data is saved in JSON format.
Requires Perl version 5.8.3 or greater.
db-browser expects decoded strings.
Non mappable characters will break the output.
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
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
Matthäus Kiem <email@example.com>
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.