cson  select-to-json

ACHTUNG: THIS PAGE IS NOW MAINTAINED IN THE NEW WIKI: http://whiki.wanderinghorse.net/wikis/cson/?page=select-to-json

See also: cson_cpdo


select-to-json is a small application which uses cson_cpdo to convert database SELECT queries to JSON and send them to stdout. The cson project's main Makefile only builds this if either sqlite3 or mysql are found by the configuration process.

This application is mainly intended for testing cson_cpdo, but it surely has other uses as well. For example, it could be used to implement CGI shell/perl scripts which return JSON data for AJAX back-ends. (Or cson_cgi can be used to write such programs in C.)

Usage: select-to-json -d DSN [... options ...]

The -d option and one of the -s or -S options are required. All others are optional.

The options are:

  • -?|--help: shows the help text and lists the available database drivers.
  • -d DSN
    the database connection string (called a DSN). See the cpdo wiki for complete details.
  • -s SQL
    A complete, well-formed SQL SELECT statement (or database-specific pseudo-select, like MySQL's SHOW TABLES).
    Same as -s but reads the SQL from a file (use - for stdin). The file must contain only a single SQL statement, and not arbitrary SQL commands.
  • -u USER (default=$USER)
    User name for logging in to the db (if required)
  • -p PASSWORD (default=NULL)
    Database password (if required)
  • -f STRING
    Sets the output format name. Try one of "thin", "fat", and "aoo". The list of formats is shown in the help text. Default is "fat".
  • -t
    Alias for -f thin.
  • -# (default=1)
    Specifies the indentation level. 0 means no indentation, 1 means 1 tab/level, and any other positive number means to use that many spaces per level. e.g. -7 means to use 7 spaces of indentation per level.
  • -L (upper case 'L' to avoid confusion with the number '1')
    Lists the available database drivers and exits with code 0. This is intended to be used for shell scripting, to figure out whether or not the local copy of this tool supports a given database type.

Potential TODOs:

* Allow -s/-S to be specified multiple times and compound the combined results into an outer array.


~> ./select-to-json -L
~> ./select-to-json -d 'sqlite3:sessions.sqlite3' \
    -s "select * from cson_session where json not NULL limit 3" 
			"json":"{\"a\":1, \"hits\":13, ...snip...}"

The same as above, but in "thin" output format (add the -t option):

			"{\"a\":1, \"hits\":13, ...snip...}"

Using database-specific pseudo-select commands:

~> ./select-to-json -d 'mysql5:dbname=cpdo;host=localhost' \
    -t \
    -s 'show tables'

~> ./select-to-json -d 'mysql5:dbname=cpdo;host=localhost' \
    -t -0 \
    -s 'describe cson_session'

# output formatting here was done manually to optimize wiki screen space
{"columns":["Field", "Type", "Null", "Key", "Default", "Extra"],
  ["id", "varchar(50)", "NO", "PRI", null, ""],
  ["last_saved", "int(11)", "NO", "", "0", ""],
  ["json", "text", "YES", "", null, ""]