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

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:

Potential TODOs:

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

Examples:

~> ./select-to-json -L
sqlite3
mysql5
~> ./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...}"
		}]
}

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

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

Using database-specific pseudo-select commands:

~> ./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, ""]
]}