cson  Artifact Content

Artifact 102b68220c523c8efb8a04cdca10fd479320c3f1:

Wiki page [select-to-json] by stephan 2011-05-09 17:58:46.
D 2011-05-09T17:58:46.453
L select-to-json
P 2797e9bf7c45f55461a10bf2cb0297c88eabf349
U stephan
W 4027
<strong>ACHTUNG: THIS PAGE IS NOW MAINTAINED IN THE NEW WIKI:</strong> [http://whiki.wanderinghorse.net/wikis/cson/?page=select-to-json]


See also: [cson_cpdo]

<h1>select-to-json</h1>

<tt>select-to-json</tt> is a small application which uses [cson_cpdo] to convert database <tt>SELECT</tt> 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: <tt><nowiki>select-to-json -d DSN [... options ...]</nowiki></tt>

The <tt>-d</tt> option and <em>one</em> of the <tt>-s</tt> or <tt>-S</tt> options are required. All others are optional.

The options are:

   *  <tt>-?|--help</tt>: shows the help text and lists the available database drivers.
   *  <tt>-d DSN</tt><br/>the database connection string (called a <em>DSN</em>). See [http://fossil.wanderinghorse.net/repos/cpdo/index.cgi/wiki?name=DSN|the cpdo wiki] for complete details.
   *  <tt>-s SQL</tt><br/>A complete, well-formed SQL <tt>SELECT</tt> statement (or database-specific pseudo-select, like MySQL's <tt>SHOW TABLES</tt>).
   *  <tt>-S SQL_FILE</tt><br/>Same as <tt>-s</tt> but reads the SQL from a file (use <tt>-</tt> for stdin). The file must contain only a single SQL statement, and not arbitrary SQL commands.
   *  <tt>-u USER</tt> (default=<tt>$USER</tt>)<br/>User name for logging in to the db (if required)
   *  <tt>-p PASSWORD</tt> (default=<tt>NULL</tt>)<br/>Database password (if required)
   *  <tt>-f STRING</tt><br/>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".
   *  <tt>-t</tt><br/>Alias for <tt>-f thin</tt>.
   *  <tt>-#</tt> (default=1) <br/>Specifies the indentation level. 0 means no indentation, 1 means 1 <em>tab</em>/level, and any other positive number means to use that many <em>spaces</em> per level. e.g. <tt>-7</tt> means to use 7 spaces of indentation per level.
   *  <tt>-L</tt> (upper case 'L' to avoid confusion with the number '1')<br/>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 <tt>-s/-S</tt> to be specified multiple times and compound the combined results into an outer array.


Examples:

<verbatim>
~> ./select-to-json -L
sqlite3
mysql5
</verbatim>

<verbatim>
~> ./select-to-json -d 'sqlite3:sessions.sqlite3' \
    -s "select * from cson_session where json not NULL limit 3" 
{
	"columns":[
		"id",
		"last_saved",
		"json"
	],
	"rows":[{
			"id":"abacab",
			"last_saved":1302907618,
			"json":"{\"a\":1, \"hits\":13, ...snip...}"
		}]
}
</verbatim>

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

<verbatim>
{
	"columns":[
		"id",
		"last_saved",
		"json"
	],
	"rows":[[
			"abacab",
			1302907618,
			"{\"a\":1, \"hits\":13, ...snip...}"
		]]
}
</verbatim>

Using database-specific pseudo-select commands:

<verbatim>
~> ./select-to-json -d 'mysql5:dbname=cpdo;host=localhost' \
    -u MYNAME -p PASSWORD \
    -t \
    -s 'show tables'
{
	"columns":["Tables_in_cpdo"],
	"rows":[
		["cson_session"],
		["t"]
	]
}

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

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

Z 7322365c303c1ae7369f11055b873f6e