NAME

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

VERSION

Version 2.287

SYNOPSIS

SQLite/MySQL/PostgreSQL

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

If db-browser is called without arguments, the user can choose from the databases offered by the database plugin.

If db-browser is called with arguments, the arguments are used as the available databases.

SQLite

    db-browser -s|--search

db-browser called with -s|--search causes a new search for SQLite databases instead of using the cached data.

DESCRIPTION

Before using 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::SQLite, DBD::mysql, DBD::MariaDB and DBD::Pg. See App::DBBrowser::DB how to write a database plugin.

Menus

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-P) to go to the previous page, the PageDown key (or Ctrl-N) to go to the next 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 (e.g. the input filters Choose Rows and Choose Cols); in such sub-menus the list of items marked with the SpaceBar 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. A Ctrl-SpaceBar inverts the made choices - so for example to select all but one select the one with the SpaceBar and then press Ctrl-SpaceBar.

Read-line

BackSpace or Strg-H to delete the character behind the cursor, Delete to delete the character at point.
Strg-U to delete the text backward from the cursor to the beginning of the line, Strg-K to delete the text from the cursor to the end of the line.
Right-Arrow to move forward a character, Left-Arrow to move back a character.
Up-Arrow to move back 10 characters, Down-Arrow to move forward 10 characters.
Home or Strg-A to move to the start of the line, End or Strg-E to move to the end of the line.

To leave a read-line without returning anything press Ctrl-X. When the input puffer is empty and an Enter is pressed, read-line returns an empty string.

OUTPUT

The elements are right-justified if they look like a number else they are left-justified.

If the option squash_spaces is enabled, leading and trailing spaces are removed from the elements and spaces are squashed to a single white-space.

Tab characters (\t) are replaces with a space.

Vertical spaces (\v) are squashed to two spaces

Control characters, code points of the surrogate ranges and non-characters are removed.

MENUS

Choosing a plugin leads to the Databases Menu.

(If there is only one plugin, the plugins menu is not shown but the plugin is chosen automatically.)

Choosing a database leads to the Schemas Menu.

(If there is only one database, the databases menu is not shown but the database is chosen automatically.)

Choosing a schema leads to the Tables Menu.

(If there is not more than one schema, the schemas menu is not shown but the schema is chosen automatically.)

The menu entries of the tables menu:

Prompt Line

Selecting the prompt line (the database name) opens a menu with these entries:

Create Table
Drop Table
Create View
Drop View
Attach DB
Detach DB

For Create Table, Drop Table, Create View and Drop View see "WRITE ACCESS". These entries are available if enabled in "Extensions".

Attache DB is available for SQLite databases. Attach DB can be used to attach databases to the current database. The menu entry Detach DB is available if a SQLite database has attached databases.

By selecting the prompt line of this menu (the database name) one can change the Source type of input data settings.

Tables Entries

Choosing one of the tables leads to the SQL Menu.

Derived

Choosing this entry allows one to enter a subquery and use it as a table.

This entry is available if enabled in "Extensions".

Join

Join tables.

This entry is available if enabled in "Extensions".

Union

Combine the result from multiple SELECT statements.

This entry is available if enabled in "Extensions".

Settings

See "DB Settings".

This entry is available if enabled in "Extensions".

In this menu it can be formed the SQL query.

The SQL Menu has the following menu entries:

Prompt Line

Selecting this prompt line (Customize:) opens a menu with these entries:

Insert Into
Update
Delete

See "WRITE ACCESS". These entries are available if enabled in "Extensions".

By selecting the prompt line of this menu (Choose SQL type:) one can change the Source type of input data settings.

Print TABLE

Select Print TABLE to show the result of the formed statement on the screen.

The Sub-Statements Entries

To reset a SQL "sub-statement" (e.g WHERE) re-enter into the respective menu and choose -OK-.

SELECT

Choose the required columns for the SELECT sub-statement.

It is possible to ad many columns at once by marking them with the SpaceBar key. If columns are marked with the SpaceBar, the highlighted column is not added to the chosen when Return is pressed.

If an AGGREGATE function or a GROUP BY columns is selected, the available choices of this sub-statement are the selected aggregate functions and the selected group-by columns.

AGGREGATE

AVG, COUNT, COUNT(*), GROUP_CONCAT/STRING_AGG, MAX, MIN, SUM

DISTINCT

WHERE

GROUP BY

HAVING

ORDER BY

LIMIT

Export

Export the result of the chosen SQL statement to a CSV-file.

WRITE ACCESS

Create/Drop Table/View

Create/Drop Table/View can be reached by selecting the prompt-line in the "Tables Menu" if the respective option is activated.

Selecting the prompt line of the Create/Drop Table/View sub-menu allows one to set the parse settings described in "Insert Data".

Create Table

If the option Source type of input data is set to menu, selecting Create Table opens a sub-menu where one can choose the type of the data source. If Source type of input data is set to plain or file, the set data source type is used automatically without showing a menu.

For the meaning of the different data source types see "Source type of input data".

After the data has been read the user can apply different input filters (See "Input filters"). When the input filters are confirmed the user is asked

- for the table name
- if the first data row should be used as header
- if an auto increment column should be added (if the option "Auto increment" is enabled)
- to edit the column names
- to edit the column data types
- to confirm the creating of the table

Drop table

Drop a table. The whole table is shown before the user confirms to drop the table.

Create View

Before creating a view: print the required select-statement to make it available in the subquery menu.

Drop View

Drop a view. The whole view is shown before the user confirms to drop the view.

Insert Into/Update/Delete

Insert Into, Update and Delete can be reached by selecting the prompt-line in the "SQL Menu" if the respective option is activated.

Delete, Update or Insert Into may not be available with JOIN and UNION statements.

Selecting the prompt line of the Insert Into/Update/Delete sub-menu allows one to set the parse settings described in "Insert Data".

Insert Into

If the option Source type of input data is set to menu, selecting Insert Into opens a sub-menu where one can choose the type of the data source. If Source type of input data is set to plain or file, the set data source type is used without showing a menu.

For the meaning of the different data source types see "Source type of input data".

After the data has been read the user can apply different input filters (See "Input filters").

If the first column of a table is an auto-increment column, the INSERT INTO statement is build without this first column. This feature is available if supported by the database plugin.

Update

Update records. The affected (and not yet updated) records are shown shown before the user confirms the update.

Delete

Delete records. The affected records are shown before the user confirms the deletion.

Data Input

Source type of input data

Plain

Insert the data cell by cell.

File

The data is read from a chosen file.

Supported file formats: text files and file formats supported by Spreadsheet::Read.

Selecting From File opens the Source file directory sub-menu where one can choose the directory which should be searched for files.

If "Directory history" is set to 1 and a directory has been saved, this menu is skipped and the directory is chosen automatically.

After a directory has been chosen a sub-menu is opened where one can choose the source file.

Selecting the prompt line of the source file sub-menu allows one to set the parse settings described in "Insert Data". If the previous sub-menu Source file directory is not shown due to the settings described before, additionally to the parse settings it is also possible the change the source file directory in this sub-menu by selecting NEW search.

See also the input filter "Reparse" in Input filters, which allows one to change the parse-settings and then re-read the data.

Input filters

Choose Cols

Use only selected columns of the imported data.

If the data has empty columns (--), the non-empty columns are preselected. Columns are regarded as empty if the values of all fields in the column - including the column header - have no length.

If the selected columns of a previous Choose Cols usage are a subset of the current available columns, this subset of columns is preselected. A RESET removes this preselection.

Columns added with the filter Append Col are empty and therefore not preselected.

If a column is not empty, but has no header name the header is named temporarily tmp.

Choose Rows

Use only selected rows of the imported data.

If the data has empty rows, the non-empty rows are preselected. Rows are regarded as empty if the values of all fields in the row have no length.

Range Rows

Use only a range of rows.

Row Groups

If the input has rows with different numbers of columns, Row Groups sorts rows with the same number of columns in a group. The user can then choose one or more groups of rows. There is only one group, if all rows have the same column count.

Remove Cell

Remove a cell from a chosen row.

Insert Cell

Insert a cell in a chosen row. The new cell is inserted in front of a chosen cell.

Append Col

Append an empty column. Appends a header element to the header row and adjusts the remaining rows to the length of the header row. If the input has rows with different numbers of cells, use the Row Group filter first to select the right row-group.

Split Column

     Item | length/width/height             Item | length | width | height
    ------|---------------------            -----|--------|-------|------
      423 |            40/30/25              423 |     40 |    30 |    25
    ------|---------------------              64 |     80 |    60 |    30
       64 |            80/60/30              705 |     50 |    50 |    40
    ------|---------------------
      705 |            50/50/40

Search & Replace

     y  | factor                            y  | factor
    ----|---------                          ---|-------
     62 | 8,975.1                           62 | 8975.1
    ----|---------                          63 | 9745.8
     63 | 9,745.8                           64 | 9818.2
    ----|---------
     64 | 9,818.2

In the replacement it can be used the variable $c as a counter. $c is set to 0 before each s///.

Available modifiers: g, i, e.

By selecting the prompt line (Choose:) one can save search & replace instructions for later use.

If a search & replace has altered the first row of the input data, a menu entry named RESTORE header row is offered.

Split Table

     y  | factor  | y  | factor             y  | factor
    ----|---------|----|---------           ---|--------
     62 | 8,975.1 | 64 | 9,986.5            62 | 8,975.1
    ----|---------|----|---------           63 | 9,745.8
     63 | 9,745.8 | 65 | 9,782.7            y  | factor
                                            64 | 9,818.2
                                            65 | 9,986.5

Merge Rows

     Month | Average | Average              Month | Average MinTemp | Average MaxTemp
    -------|---------|---------             ------|-----------------|----------------
           | MinTemp | MaxTemp              Jan   |               9 |              22
    -------|---------|---------             Feb   |              10 |              23
     Jan   |       9 |      22
    -------|---------|---------
     Feb   |      10 |      23

Merge Rows can be used to edit a row by selecting only one row.

Join Columns

     Item | length | width | height          Item | length/width/height
    ------|--------|-------|--------         -----|-------------------
      423 |     40 |    30 |     25           423 | 40/30/25
    ------|--------|-------|--------           64 | 80/60/30
       64 |     80 |    60 |     30           705 | 50/50/40
    ------|--------|-------|--------
      705 |     50 |    50 |     40

Join Columns can be used to edit a column by selecting only one column.

Fill up Rows

If the input table rows have different numbers of cells, this filter appends empty cells to the rows until the cell count of the rows are equal to the cell count of the row with highest cell count.

One can enter in the Row Group filter to see if the table has rows with different length. If Row Group shows only one group, all rows have the same length.

Cols to Rows

Transpose columns to rows.

     Year | 2000 | 2001 | 2002              Year | Max | Min
    ------|------|------|------             -----|-----|----
     Max  |   14 |   22 |   17              2000 |  14 |  11
    ------|------|------|------             2001 |  22 |  10
     Min  |   11 |   10 |    9              2002 |  17 |   9

Empty to NULL

Convert fields containing an empty string to NULL (undefined).

The default values for this filter can be set with the option "Empty to Null".

Reset

Reset the filters to the default values.

Reparse

Change the parse settings and reparse the data with the new settings.

OPTIONS

The options menu is called with db-browser -h.

HELP

Show this documentation.

Path

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

DB Plugins

Choose the required database plugins.

DB Settings

These driver specific DB Settings are used as the default database settings.

There is also in each "Tables Menu" the entry DB Settings (if enabled in "Extensions") 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 be set in each option and whether the selections made by the user are considered.

Fields: Set which fields are required to connect to a 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).
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.
Attributes: For the meaning of these driver specific attributes check the appropriate driver documentation.
Reset DB: Reset database specific parameter to the global DB Settings.

Behavior

Set the behavior of the interactive menus.

If enabled: saves the menu position while entering in a sub menu.

Expand table

Expand table rows:

YES: if Return is pressed, the selected table row is printed with each column in its own line. The first row is not expanded if the cursor auto-jumped to the first row.
NO: don't expand table rows.

Search

Set the search behavior (Ctrl-F).

disabled
case-insensitive search
case-sensitive search

Mouse mode

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

Extensions

Add menu entries to menus:

Derived (table)
Join
Union
DB settings

Derived (table)

Derived (table)
Union All

Substatements

Enable substatement additions for:

SELECT
WHERE
GROUP BY
ORDER BY
SET

If enabled, %% is shown as additionally menu entry. Selecting %% lets one choose between the available extensions:

Functions (f()). Selecting f() opens the "Functions" menu.
Subqueries (SQ). Selecting SQ opens the "Subqueries" menu.
Set column to NULL (=N). Available after the SET in an UPDATE statement. Selecting =N sets the column to NULL.

If these menu entries are prefixed with =, it is required to select the wanted operator after the extended menu entry has been chosen.

Functions

Selecting the prompt line ('Function:') allows one the enable the possibility to set the sign (+/-) for the precision in the rounding function.

Bit_Length
Char_Length
Concatenate
Epoch_to_Date
Epoch_to_DateTime
Replace
Round
Truncate

Subqueries

Selecting a subquery menu entry (or a Derived menu entry) opens a subquery sub-menu which allows one the enter a subquery (Read-Line).

If stored or temporarily stored subqueries are available, it is also possible to select one of those subqueries instead of entering a subquery.

The subqueries are automatically enclosed in parentheses if the subqueries do not contain any parentheses.

A subquery can be saved or removed in the sub-menu which opens by selecting the prompt line of a subquery menu (Choose SQ:).

The last subqueries entered in the subquery menu and the last statements printed with Print TABLE are stored temporarily.

Parentheses

Enable parentheses in WHERE and/or HAVING clauses.

Write access

Enable write access - use with care.

Insert Records
Update Records
Delete Records
Create Table
Drop Table
Create View
Drop View

SQL

Metadata

If Metadata is enabled, system tables/schemas/databases are appended to the respective list.

Operators

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.

Aliases

If enabled, it is asked for an alias. If entered nothing, no alias is added except for joined tables and unions and for derived tables which get then a default alias.

Functions/Subqueries in SELECT
AGGREGATE functions
Derived tables
JOIN
UNION

Identifiers

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.

SQLite:

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

View prefix

Enter a string which should be automatically put in front of all view names.

Auto increment

Setting Auto increment to a value which is not the empty string enables the option to set an auto increment column when creating a table. The value set here is then used as the default name for the auto increment column.

Col data types

If this option is enabled and a table is created, SQL::Type::Guess is used to detect the data types. These data types are then used as default values when the user is asked for the data types.

Max Rows

Sets LIMIT automatically to Max Rows. This can be overwritten by setting a SQL LIMIT statement manually. Fetched table rows are kept in memory.

To disable the automatic limit set Max Rows to 0.

Output

Truncate column threshold

Columns with a width below or equal Trunc col threshold are only trimmed if it is still required to lower the row width despite all columns wider than Trunc col threshold have been trimmed to Trunc col threshold.

Truncate fractions first

If the terminal width is not wide enough and Trunc fract first is enabled, the first step to reduce the width of the columns is to truncate the fraction part of numbers to 2 decimal places.

Progress bar

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.

Tab width

Set the number of spaces between columns.

Color

Enables the support for color and text formatting escape sequences (SGR) if set to Enable. Before the output it is added a reset (\e[0m) at the end of each row.

Binary binary

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.

Squash spaces

If squash_spaces is enabled, consecutive spaces are squashed to one space and leading and trailing spaces are removed.

Indentation

Set the indentation width for the SQL substatements.

Set string

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

Decimal separator

Set the decimal separator. Numbers with a decimal separator are formatted as number if this option is set to the right value.

Allowed values: a character with a print width of 1. If an invalid value is passed, decimal_separator falls back to the default value (dot).

Warnings

Enable/disable File::Find warnings when searching for SQLite databases.

Insert Data

Parse tool for 'file'

Set how to parse text files. 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.

Text::CSV

To decode the files it is used the "File encoding".

Advantages:
- Allows to set different csv-related options.

split

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

Advantages:
- The values assigned to the IRS and the IFS are treated as regexps.

Template

Uses unpack with the A character to break up input lines.

unpack used with A strips trailing whitespace. For leading whitespace to user has the option to remove it or not. Any remaining column separators can be removed with the Search & Replace filter.

If the row length is greater than the screen width, make notes of the number of columns and the length of each column before using Template.

Advantages:
- Good for input with fixed-columns-length.

Spreadsheet::Read

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.

Spreadsheet::Read::rows fills up uneven rows automatically so the filter "Fill up Rows" and "Row Groups" have no meaning if Spreadsheet::Read is used.

Advantages:
- Auto-detects the separation character for csv-files

csv settings-a

Set the Text::CSV options

sep_char
quote_char
escape_char
eol

csv settings-b

Set Text::CSV options not mentioned in csv settings-a:

For the meaning of the different options see Text::CSV.

'split' settings

Record separator

Set the input record separator (regexp).
Trim record left

Expects a regex pattern. If set, removes leading characters matching regexp from each record.
Trim record right

Expects a regex pattern. If set, removes trailing characters matching regexp from each record.
Field separator

Set the input field separator (regexp).
Trim field left

Expects a regex pattern. If set, removes leading characters matching regexp from each field.
Trim field right

Expects a regex pattern. If set, removes trailing characters matching regexp from each field.

Enable input filter

Enable the input filter menu.

Enabling input filter will cause a second copy of the data to be kept in memory.

Empty to Null

Set the empty_to_null defaults for the different data source types.

File encoding

How to decode text files.

Source type of input data

Set the data source type to plain, file or menu for Insert Into and for Create Table.

If this option is set to menu, a menu is offered where the user can choose the type of the data source.

Directory history

Set how many searched directories should be saved in the directory history.

A value of 0 disables the history.

If Directory history is set to 1 and a directory has been saved, the directory is chosen automatically without showing the history menu.

File filter

This option expects as its value a string. The string is used as a glob pattern.

If file is selected as the data source type, only files matching this glob pattern are part of the available files.

Show hidden files

If file is selected as the data source type, this setting determines if hidden files are part of the available files or not.

Export to CSV

Destination folder

Set the folder where the data to be exported will be saved as a CSV file.

Auto-File-Extension

If this option is enabled, the file extension csv is added automatically to the file-name.

Encoding to CSV

Set the Text::CSV::csv-option encoding.

CONFIGURATION FILES

To find out the location of the configuration files call db-browser -h and choose Path. The data is saved in JSON format.

REQUIREMENTS

Perl version

Requires Perl version 5.10.1 or greater.

Decoded strings

db-browser expects decoded strings.

Non mappable characters will break the output.

Terminal

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 db-browser.

Permissions

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.

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

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.

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.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

VERSION

SYNOPSIS

SQLite/MySQL/PostgreSQL

SQLite

DESCRIPTION

NAVIGATION

Menus

Read-line

OUTPUT

MENUS

Plugins Menu

Databases Menu

Schemas Menu

Tables Menu

Prompt Line

Tables Entries

Derived

Join

Union

Settings

SQL Menu

Prompt Line

Print TABLE

The Sub-Statements Entries

SELECT

AGGREGATE

DISTINCT

WHERE

GROUP BY

HAVING

ORDER BY

LIMIT

Export

WRITE ACCESS

Create/Drop Table/View

Create Table

Drop table

Create View

Drop View

Insert Into/Update/Delete

Insert Into

Update

Delete

Data Input

Source type of input data

Input filters

OPTIONS

HELP

Path

DB Plugins

DB Settings

Behavior

Menu memory

Expand table

Search

Mouse mode

Extensions

Tables menu

Join menu

Union menu

Substatements

Functions

Subqueries

Parentheses

Write access

SQL

Metadata

Operators

Aliases

Identifiers

View prefix

Auto increment

Col data types

Max Rows

Output

Truncate column threshold

Truncate fractions first

Progress bar

Tab width