The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
App-DBBrowser-2.060
River stage zero No dependents
14 ++
/ db-browser

NAME

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

VERSION

Version 2.060

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

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.

NAVIGATION

  • 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 (for example Choose Rows and Choose Cols from the input filters; 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-D. 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.

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

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.

See also the option group "Output".

MENUS

Plugin Menu

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.

Database Menu

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.

Schema Menu

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.

Table Menu

The menu entries of the table menu:

Prompt Line

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

Create Table

See "WRITE ACCESS"

Drop Table

See "WRITE ACCESS"

Attach DB

Attache DB is available only for SQLite databases. Attach DB can be used to attach databases to the current database.

Detach DB

This entry is available if a SQLite database has attached databases.

Tables

Choosing a table 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 only if enabled in "Subqueries".

Join

Join tables.

Union

Combine the result from multiple SELECT statements.

Settings

See "DB Settings".

SQL Menu

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"

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

The Sub-Statements

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

See also "Extensions", "Parentheses", "Operators"

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

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.

HAVING

ORDER BY

LIMIT

Reset

Reset the SQL statement.

WRITE ACCESS

Create/Drop Table

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

Create Table

Selecting Create Table opens a sub-menu with these entries:

Plain

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 the Settings one can configure how the data is parsed.

From File

Like Copy and Paste with the difference that the data is read from a chosen file.

Settings

Selecting Settings allows one to set the options described in the option group "Get Data" for the current database.

Drop table

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

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.

Insert Into

Three different ways to insert data into a table are available:

Plain

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.

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 it is supported by the database plugin.

Input filters

Choose Cols

Use only selected columns of the imported data.

If the imported 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.

Choose Rows

Use only selected rows of the imported data.

If the input has rows with different numbers of columns, this filter sorts the rows in groups with the same number of columns. The user can then select the group of rows which should be used.

Range Rows

Use only a range of rows.

Add Col

Add an empty column: adds a column 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 columns, use first Choose Rows to select the rows to use.

Empty to NULL

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

With Plain and Copy & Paste this filter is enabled by default.

Reset

Reset the filters to the default values.

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
Merge Rows
     Month | Average | Average              Month | Average MinTemp | Average MaxTemp
    -------|---------|---------             ------|-----------------|----------------
           | MinTemp | MaxTemp              Jan   |               9 |              22
    -------|---------|---------             Feb   |              10 |              23
     Jan   |       9 |      22
    -------|---------|---------
     Feb   |      10 |      23
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
Split Col
     Item | length/width/heigh              Item | length | width | heigh
    ------|--------------------             -----|--------|-------|------
      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

Update

Update records.

See also "Extended SET" and "Extended WHERE"

Delete

Delete records.

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

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

Fields

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

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.

Set the behavior of the interactive menus.

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

Table

Expand table rows:

YES

if Return is pressed, the selected table row is printed with each column in its own line.

NO

don't expand table rows.

YES fast back

do not expand the first row if the cursor auto-jumped to the first row.

Info

Before records are deleted/updated or before a table is dropped the affected records are shown in an info-table.

Expand info tables rows:

YES

if Return is pressed, the selected table row is printed with each column in its own line.

NO

don't expand table rows.

YES fast back

do not expand the first row if the cursor auto-jumped to the first row.

Mouse Mode

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

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.

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 Auto Limit to 0.

Extensions

Select which menu extensions should be available:

Extensions SELECT

Values: None, Func, SQ and Func/SQ

Extensions WHERE

Values: None, Func, SQ and Func/SQ

Extensions HAVING

Values: None, Func, SQ and Func/SQ

Extensions SET

Values: None, Func, SQ, =N and Func/SQ/=N

Extensions Table

Values: None and SQ

Extensions Join

Values: None and SQ

Extensions Union

Values: None and SQ

If set to None, no menu extensions are available.

If set to Func, f() is shown as additionally menu entry. Selecting f() opens the "Functions" menu.

If set to SQ, SQ is shown as additionally menu entry. Selecting SQ opens the "Subqueries" menu. (In the table-, join- and union-menus Derived is used instead of SQ.)

If set to =N, =N is shown as additionally menu entry (available only after the SET in an UPDATE statement). Selecting =N sets the column to NULL.

If set to Func/SQ (or Func/SQ/=N), %% is shown as additionally menu entry. Selecting %% lets one choose between all available extensions.

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

Functions

Concatenate
Truncate
Bit_Length
Char_Length
Epoch_to_Date
Epoch_to_DateTime

Subqueries

Selecting a subquery 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.

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.

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.

Aggregate
Functions

Functions in the SELECT sub-statement.

Join
Subqueries

Derived Tables and subqueries in the SELECT sub-statement.

Union

Parentheses

Enable parentheses in WHERE and/or HAVING clauses.

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.

Write access

Enable write access - use with care.

Insert records
Update records
Delete records
Create table
Drop table

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.

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.

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.

Grid

Separate the columns from each other and the header from the body with lines.

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.

Keep Header

Show the table header on top of each page.

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.

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.

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.

Get Data

Parse Tool

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.

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

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

    Advantages:

    • Auto-detects the separation character for csv-files

Text::CSV config

Set different Text::CSV options.

'split' config

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

File encoding

How to decode text files.

File History

Set how many input file names and searched directories should be saved. A value of 0 disables the history.

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

See "REQUIREMENTS" in Term::TablePrint.

Perl version

Requires Perl version 5.8.3 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

Copyright 2012-2019 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.