/ 
App-DBBrowser-0.044_02
River stage zero No dependents
/ db-browser

NAME

db-browser - Browse SQLite/MySQL/PostgreSQL databases and their tables interactively.

VERSION

Version 0.044_02

SYNOPSIS

SQLite/MySQL/PostgreSQL

db-browser

db-browser -h|--help

When 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 supported 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.

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.

Legacy encodings

Non mappable characters will break the output.

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.

Keys to move around

  • the Arrow keys (or h,j,k,l) to move up and down and to move to the right and to the left,

  • the PageUp key (or Ctrl-B) to go back one page, the PageDown key (or Ctrl-F) to go forward one page.

  • 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.

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.

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.

To move backwards in the menu hierarchy one can press the q key. When prompted for a string, try Ctrl-D instead of q.

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.

Delete, Update and Insert

These features are experimental.

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:".

Scalar functions

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:

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.

OPTIONS

HELP

Show this Info.

Path

Shows the version and the path of the running db-browser and the path of the application directory.

Output

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.

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.

Tabwidth

Set the number of spaces between columns.

Undef

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.

Enchant

Set the behavior of different interactive menus:

- setting Menus config to "Memory" means: save the selected configuration menu position while entering in a config sub menu.

- setting Menu sql to "Memory" means: save the selected SQL menu position while entering in a SQL sub menu.

- setting Menus db 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: enable printing the chosen table row.

- setting Table Header to "Each page" means: print the table header on top of each page.

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.

Mouse Mode

Set the mouse mode (see "mouse" in Term::Choose).

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.

SQL

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 Metadata is enabled, system tables/schemas/databases are appended to the respective list.

Operators

Choose the required operators.

With this menu entry it is possible to mark items with the SpaceBar key.

Parentheses

Enable parentheses in WHERE and/or HAVING TO clauses.

- (YES: the position of "(" in the menu is before the column names.

- YES(: the position of "(" in the menu is after the column names.

Regexp Case

If Regexp Case is enabled, REGEXP will match case sensitive.

With MySQL the sensitive match is achieved by enabling the BINARY operator.

Database

DB Defaults

Set Database defaults.

The DB Defaults can be overwritten for each database with the database menu entry "Database settings".

Enable utf8

If Enable utf8 is 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.

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.

Default DB directories

Sets the default directories where db-browser searches for SQLite databases. This is a SQLite-only option.

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.

"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.

RESET

RESET resets the database settings.

DB Drivers

Choose the required database drivers.

DB Login

Determine when db-browser asks for the login data:

  • Ask host/port per DB

    If set to "YES" host and port data is asked once per database else the global settings are used if any.

  • Ask user/pass per DB

    If set to "YES" username and password data is asked once per database else only once.

This option has no meaning if the SQLite driver is in use.

ENV DBI

- use DBI_USER as username for all database logins if the environment variable exists and Ask user/pass is set to "Once".

- use DBI_PASS as password for all database logins if the environment variable exists and Ask user/pass is set to "Once".

- use DBI_HOST as host for all database connections if the environment variable exists and Ask host/port is set to "No".

- use DBI_PORT as port for all database connections if the environment variable exists and Ask host/port is set to "No".

This options have no meaning if the SQLite driver is in use.

Insert

These Insert settings can also be set temporarily in the INSERT INTO sub-menu by selecting the prompt "Customize:".

Insert mode

Choose how to enter the data, when inserting into a table.

  • "--"

    Offer the Insert mode menu each time when inserting into a table.

  • Cols

    It is prompted for each column.

  • Rows

    Enter a row at a time.

  • Multirow

    Enter all rows at once.

  • File

    Read the input from a file.

It is used Text::ParseWords to parse the input, if the input is entered with the input mode "Rows", "Multirow" or "File".

Encoding input file

When the input is read from a file, this option tells how to decode the read input.

Col-Row input filter

Enable the col-row input filter. If this option is enabled and the input mode is set to "Multirow" or "File", the user can choose which columns/rows from the input to use as insert values.

Delimiter

Set the delimiter. The delimiter is used to set the option $delim of the function parse_line from Text::ParseWords. parse_line is used to get the column values from a row (if the input mode is "Rows", "Multirow" or "File". The delimiter can be a regular expression.

Keep quotes an backslashes

See the $keep option in Text::ParseWords.

CONFIGURATION FILES

The syntax of the configuration file names is "conf_${driver_name}.json". To find out the location of the configuration files call db-browser -h and choose Path.

The data is saved in JSON format.

The global settings are placed in the member called "*$db_driver". This settings are used if ask per DB in DB Login is not enabled. If ask per DB is enabled each database uses is own member named like the database itself. With the SQLite driver "database name" means the absolute path to the database file.

Sub-members (keys):

SQLlite:                                    mysql:                             Pg:
    sqlite_unicode              (0,1)           user                              user
    sqlite_see_if_its_a_number  (0,1)           host                              host
    binary_filter               (0,1)           port                              port
    dirs_sqlite_search ¹                        mysql_enable_utf8  (0,1)          pg_enable_utf8  (0,1,-1)
                                                binary_filter      (0,1)          binary_filter   (0,1)

¹ only in "*SQLite": 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,
        "dirs_sqlite_search" : [                                    "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
                                                                }
                                                            }

REQUIREMENTS

See "REQUIREMENTS" in Term::TablePrint.

Perl version

Requires Perl version 5.10.0 or greater.

Decoded strings

db-browser expects decoded strings.

Encoding layer for STDOUT

For a correct output it is required an appropriate encoding layer for STDOUT matching the terminal's character set.

Terminal

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.

The terminal should have a width of at least 40 print columns.

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.