PDO-QUERY(1) Shell Utilities PDO-QUERY(1)
NAME
pdo-query - the universal RDBMS client / CSV dumper
SYNOPSIS
pdo-query [-a..] [-c..] [-d..] [-e..] [-q/t..]
pdo-query [-s..] [-u..] [-t..] [-d..] [-e..] [-q/t..]
pdo-query -h
pdo-query -s sqlite:/tmp/meta.sq3 -q 'SELECT * FROM my_data'
DESCRIPTION
The pdo-query utility eases querying common RDBMS systems and dumping
the results as CSV data. Beside the possibility to specifiy CSV for‐
matting options, the tool provides a flexible way to pre-configure and
alias connections and credentials. This allows users and script authors
to refer to any ressource by name, whilst e.g. the administrator can
create and update the configuration at any time.
OPTIONS
-a alias, --alias ...
Use the configured alias, as described in the CONFIGURATION sec‐
tion. This allows to store various connection credentials -
without the need to remember each detail.
-c configuration, --configuration ..., --config ...
Register the given configuration file to be used in addition to
the default files, as described in the CONFIGURATION section.
-d delimiter, --delimiter ..., --delim ...
Use the given delimiter to separate the fields in the CSV out‐
put, if any. This option defaults to a comma.
-e enclosure, --enclosure ..., --enc ...
Use the given enclosure to quote the fields in the CSV output,
if any. This option defaults to a double quote.
-f fields, --fields ...
Comma-separated list of field names, only these fields will be
included in the output.
-h, --help, -?
Print a help message and exit.
-n, --no-header, --omit-header
Omit the first line (containing the column names) from the CSV
output, if any.
-p password, --password ..., --pw ...
Use the given password for authentication when initializing the
PDO connection.
-q query, --query ...
Send the given query to obtain the data to dump as CSV, if any.
-s source, --source ..., --dsn ...
Use the given source as data source name (DSN) to initialize the
PDO connection. Please refer to the PDO documentation at
http://www.php.net/manual/en/pdo.construct.php for further
information.
-t table, --table ...
Dump the entire data of the given table instead of using an
explicit query. This is a shortcut for using --query to 'SELECT
* FROM table'.
-u username, --username ..., --user ...
Use the given username for authentication when initializing the
PDO connection.
CONFIGURATION
The --dsn, --user and --pw options can be stored in configuration
files, for later reference via --alias. The order in which these files
are searched for and processed is as follows:
1. System-wide configuration: /etc/pdo-query.ini
2. User-specific configuration: $HOME/.pdo-query.ini
3. Requested configuration (via e.g. --config)
The syntax of the configuration files is a pretty self-explanatory
INI-file dialect. The following examples demonstrate the basic use:
; [alias]
; dsn = driver:driver-specific-options
; user = username
; pw = password
[sqlite-example]
dsn = "sqlite:/tmp/my-database.sq3"
[postgresql-example]
dsn = "pgsql:host=postgres.example.com;dbname=example"
user = "john-doe"
pw = "c0nf1d3nt1@l"
[mysql-example]
dsn = "mysql:host=mysql.example.com"
user = "jane-doe"
pw = "s3cr3t"
Note that directives in configuration files can override each other
when using the same alias. Also, the --dsn, --user and --pw options
override the configuration when passed to pdo-query AFTER the --alias
directive (and vice-versa).
EXAMPLES
The following examples assume that there is a database alias named
example (e.g. an SQLite database as demonstrated in the previous sec‐
tion) configured and running:
pdo-query -a example <<<'CREATE TABLE data (key TEXT, value TEXT)'
pdo-query -a example -q 'INSERT INTO data VALUES ("foo", "bar")'
pdo-query -a example -q 'SELECT * FROM data'
key,value
foo,bar
pdo-query -s sqlite:/tmp/my-database.sq3 -t data
key,value
foo,bar
pdo-query -a example -t data -n
foo,bar
pdo-query -d \; -a example -t data
key;value
foo;bar
Note that in case there's no data at all the column names aren't writ‐
ten either:
pdo-query -a example -q 'DELETE FROM data'
pdo-query -a example -t data
SUPPORTED SYSTEMS
See http://www.php.net/manual/en/pdo.drivers.php
BUGS
Probably infinite.
SEE ALSO
sqlite(1), psql(1), mysql(1), php(1)
COPYING
Mathias J. Hennig wrote this script and its manual page. As long as you
retain this notice you can do whatever you want with this stuff. If we
meet some day, and you think this stuff is worth it, you can buy me a
beer in return.
Matze's Toolbox February 2013 PDO-QUERY(1)