db-browser - Browse SQLite/MySQL/PostgreSQL databases and their tables interactively.
db-browser
Version 0.999
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
db-browser called without arguments uses its own method to find the available databases. If the SQLite driver is in use, the option Search directories determines where to search for SQLite databases (defaults to the home directory).
SQLite
If db-browser is called with arguments, the arguments are use as the available databases.
db-browser [-s|--search]
db-browser called with -s|--search causes a new search of SQLite databases instead of using the cached data.
-s|--search
Search and read in SQLite/MySQL/PostgreSQL databases. With the db-browser one can browse databases and their tables interactively. The supported DBI drivers are DBD::SQLite, DBD::mysql and DBD::Pg.
DBD::SQLite
DBD::mysql
DBD::Pg
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.
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.
To be able to use all the features of the db-browser some basic SQL knowledge is required.
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. Ctrl-SpaceBar (or Ctrl-@) inverts the made choices - marked items are unmarked and unmarked items are marked.
SpaceBar
Ctrl-SpaceBar
Ctrl-@
To move backwards in the menu hierarchy one can press the q key. When prompted for a string, try Ctrl-D instead of q.
q
Ctrl-D
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 entry and choose '- OK -'.
WHERE
'- OK -'
Changing the lock mode (Lk0,Lk1) resets the entire SQL.
Lk0
Lk1
To get to the DELETE, UPDATE or INSERT INTO statements select the prompt "Customize:" in the SQL menu and then select the prompt "Your choice:".
DELETE
UPDATE
INSERT INTO
The scalar functions can be reached in the main SQL menu and also in the DELETE and UPDATE SQL sub-menus by selecting the prompt "Customize:".
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 TO clauses with numbers, take the non-truncated (original) value for the comparison if sqlite_see_if_its_a_number is enabled (default).
HAVING TO
sqlite_see_if_its_a_number
Also to get a numeric comparison 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 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.
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.
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.
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.
- setting Print Table to "Expand" means: if Return is pressed, the selected table row is printed with each column in its own line.
- setting Table Header to "Each page" means: print the table header on top of each page.
Set the default lock value:
- Lk0: Reset the SQL-statement after each "PrintTable".
- Lk1: Reset the SQL-statement only when a table is selected.
Set the mouse mode (see "mouse" in Term::Choose).
Set the maximum number of fetched table rows. This can be overwritten by setting a SQL LIMIT statement.
LIMIT
The fetched table rows are kept in memory.
To disable the automatic limit set Max Rows to 0.
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 BINARY operator.
BINARY
Enable parentheses in WHERE and/or HAVING TO clauses.
- (YES: the position of "(" in the menu is before the column names.
(YES
- YES(: the position of "(" in the menu is after the column names.
YES(
Choose the required database plugins.
DB Settings are used as 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.
Determine how to gather the login data:
Ask
Ask for the data of a field when connecting to a database.
Use DBI_....
Use the environment variable to connect if available else ask the user for the required data.
The name of the environment variable:
$environment_variable = 'DBI_' . uc($name_of_the_field);
Don't set
If a field is not required for a database connection one can select the entry Don't set for that field. The field is then not used for the database connection. For example if the field is "port" Don't set could mean: form the DBI DSN without the "port" entry.
DBI
DSN
Possible fields could be "host", "port", "user", "password" and others.
It depends on the database plugin which fields are offered to set and whether the selections made by the user a considered.
The entered data for a field is saved with the field in a configuration file. If the configuration file contains a field with a defined value, the value is used for that field to connect instead to ask the user for the value.
All options in this sub-menu - apart the last one - are connection attributes. For the meaning of the different attributes see the documentation of the DBI database driver.
Setting binary_filter (the last item in the sub-menu) 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]/
It depends on the database plugin which connection attributes are offered to set and whether the selections made by the user a considered.
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.
It depends on the database plugin if this setting is considered.
Reset database specific parameter to the global DB Settings.
These Insert settings can also be set temporarily in the INSERT INTO sub-menu by selecting the prompt "Customize:".
INSERTing data into a tables - select the input modes:
INSERT
Cols
It is prompted for each column.
Rows
Enter a row at a time.
To parse the rows it is used Text::CSV.
Text::CSV
Multirow
Enter all rows at once.
File
Read the input from am file.
Supported file formats: CSV files and the spreadsheet formats supported by Spreadsheet::Read.
Set which module to use for parsing text files or for parsing the "Multirow" 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
The following csv options are used if Text::CSV is chosen.
To decode the file it is used the CSV file encoding
Text::ParseWords
With Text::ParseWords it is possible to use a regexp as delimiter - see the option T::PW: $delim.
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.
The following csv settings have no meaning when Spreadsheet::Read is in use.
How to decode csv files.
Set the csv sep_char.
Set the csv quote_char.
Set the csv escape_char.
Other different csv options.
Text::ParseWords: set the delimiter ($delim). Can be a regexp.
$delim
See Text::ParseWords for more information for the option $keep.
$keep
Set how many input file names should be saved. A value of 0 disables the file history.
The syntax of the configuration file names is "conf_${db_plugin_name}.json". To find out the location of the configuration files call db-browser -h and choose Path.
"conf_${db_plugin_name}.json"
db-browser -h
The data is saved in JSON format.
The global database settings are placed in the member called "*$db_plugin". Database specific settings have its own member named like the database itself. With the SQLite driver "database name" means the absolute path to the database file.
"*$db_plugin"
Sub-members (keys):
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)
¹ only with the SQLite driver: expects an array-reference as its value. db-browser searches for SQLite databases in the directories passed with this array-reference.
Examples
conf_SQLite.json: conf_mysql.json: { { "*SQLite" : { "*mysql" : { "binary_filter" : 0, "binary_filter" : 0, "directories_sqlite" : [ "host" : "localhost", "/home/my/Documents", "mysql_enable_utf8" : 1, "/home/my/databases" "port" : null, ], "user" : "name" "sqlite_see_if_its_a_number" : 1, }, "sqlite_unicode" : 1 "database1" : { }, "mysql_enable_utf8" : 0, "/home/my/databases/db1.sqlite" : { "host" : "my_host", "binary_filter" : 1, "user" : "user_5" "sqlite_unicode" : 0 }, } "database2" : { } "binary_filter" : 1 } }
See "REQUIREMENTS" in Term::TablePrint.
Requires Perl version 5.8.3 or greater.
db-browser expects decoded strings.
For a correct output it is required an appropriate encoding layer for STDOUT matching the terminal's character set.
It is required a terminal that uses a monospaced font which supports the printed characters.
Also 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.
App::DBBrowser
The terminal should have a width of at least 40 print columns.
DBI, DBD::SQLite, DBD::mysql, DBD::Pg.
Thanks to the Perl-Community.de and the people form stackoverflow for the help.
Matthäus Kiem <cuer2s@gmail.com>
Copyright 2012-2015 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.