NAME
db-browser - Browse SQLite/MySQL/PostgreSQL databases and their tables interactively.
VERSION
Version 0.028
SYNOPSIS
SQLite/MySQL/PostgreSQL
db-browser
db-browser -h|--helpWhen the db-browser is called with the argument -h|--help it shows a menu - see "OPTIONS".
SQLite
db-browser [-s|--search] [directories to be searched]If no directories are passed the home directory is searched for SQLite databases.
db-browser called with -s|--search causes a new search of SQLite databases instead of using the cached data.
DESCRIPTION
Search and read in SQLite/MySQL/PostgreSQL databases. With the db-browser one can browse databases and their tables interactively. The available DBI drivers are DBD::SQLite, DBD::mysql and DBD::Pg.
To be able to browse database-, schema- and table-lists and the content of tables one needs to have the database privileges required for fetching the respective data.
The db-browser expects an existing home directory with read and write permissions for the user of the db-browser.
It is recommended to work with an UTF encoding. Non mappable characters might break the output.
It is required a terminal with a width of at least 40 print columns.
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.
USAGE
The best way to find out how db-browser works is calling db-browser.
To be able to use all the features of the db-browser some basic SQL knowledge is required.
SQL menu
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.
To reset a SQL "sub-statement" (e.g WHERE) re-enter into the respective menu entry and choose '- OK -'.
Changing the lock mode (Lk0,Lk1) resets the entire SQL.
Keys to move around
the
Arrowkeys (orh,j,k,l) to move up and down and to move to the right and to the left,the
PageUpkey (orCtrl-B) to go back one page, thePageDownkey (orCtrl-F) to go forward one page.the
Homekey (orCtrl-A) to jump to the beginning of the menu, theEndkey (orCtrl-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 Return key.
When choosing columns for the SELECT and GROUP BY statement it is possible to select items with the SpaceBar key before Return is pressed. Then the list of items marked with the SpaceBar including the highlighted item are added to the chosen items. If a mouse mode is enabled it can be used the right mouse key instead of the SpaceBar. Ctrl-SpaceBar (or Ctrl-@) inverts the made choices - marked items are unmarked and unmarked items are marked.
To move backwards in the menu hierarchy one can press the q key . When prompted for a string use Ctrl-D instead of q.
OPTIONS
HELP
Show this Info.
Path
Shows the version and the path of the running db-browser and the path of the application directory.
Colwidth
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.
DB Defaults
Set Database defaults.
The DB Defaults can be overwritten for each Database with the Database menu entry "Database settings".
See if it's a number
If set to "YES" DBD::SQLite tries to see if the bind values are numbers or not, and does not quote if they are numbers.
See DBD::SQLite for details. This is a SQLite-only option.
Enable utf8
If enabled the utf8 flag will be turned on for character data coming from the database.
For a more driver specific explanation see the documentation of the respective Perl DBI driver.
With the Pg driver in use it is available - apart from the "YES/NO" choices - also an "AUTO" choice. If the DBD::Pg version is less than 3.0.0 "AUTO" is mapped to "YES". For the meaning of "AUTO" look in pg_enable_utf8 for the value -1.
"Binary Filter"
Print "BNRY" instead of arbitrary binary data.
If the data matches the repexp /[\x00-\x08\x0B-\x0C\x0E-\x1F]/ it is considered arbitrary binary data.
Printing arbitrary binary data could break the output.
DB Drivers
Choose the required database drivers.
DB Login
Determine when db-browser asks for the login data:
per-DB
log in data is asked once per database.
once
log in data is asked only once and then used for all connections.
This option has no meaning if the SQLite driver is in use.
ENV DBI
- use DBI_USER as username for all database logins.
- use DBI_PASS as password for all database logins.
This option has no meaning if the SQLite driver is in use.
Expand
Set the behavior of different menus.
For the menus "Choose Database", "Choose Schema" and "Choose Table" setting Expand to "Enchanted" means: save the menu position.
For "Print Table" "Enchanted" means: enable printing the chosen table row.
Lock
Set the default lock value:
- Lk0: Reset the SQL-statement after each "PrintTable".
- Lk1: Reset the SQL-statement only when a table is selected.
Max Rows
Set the maximum number of fetched table rows. This can be overwritten by setting a SQL LIMIT statement.
The fetched table rows are kept in memory.
To disable the automatic limit set Max Rows to 0.
Metadata
If enabled system tables/schemas/databases are appended to the respective list.
Mouse Mode
Set the mouse mode (see "mouse" in Term::Choose).
Operators
Choose the required operators.
Parentheses
Enable parentheses in WHERE and/or HAVING TO clauses.
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.
Regexp Case
If enabled REGEXP will match case sensitive.
With MySQL the sensitive match is achieved by enabling the BINARY operator.
Sssc Mode
With the Sssc mode "compat" enabled back-arrows are offered in the SQL menus entries. In the "simple" mode it can be used the q key instead of the back-arrows.
Tabwidth
Set the number of spaces between columns.
Undef
Set the string that will be shown on the screen instead of an undefined field.
HINTS
Scalar functions
There is a hidden menu entry: selecting the prompt line in the SQL menu - Customize: - makes available some scalar functions.
Epoch_to_DateTime
Epoch_to_Date
Truncate
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 WHERE or 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.
Bit_Length
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.
Char_Length
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.
SEE ALSO
DBI, DBD::SQLite, DBD::mysql, DBD::Pg.
CREDITS
Thanks to the Perl-Community.de and the people form stackoverflow for the help.
AUTHOR
Matthäus Kiem <cuer2s@gmail.com>
LICENSE AND COPYRIGHT
Copyright 2012-2014 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.