|
|
SQL Grouping SUM
01-04-2012
|
|
|
|
SELECTALL_XML(1p) User Contributed Perl Documentation SELECTALL_XML(1p) NAME
stag-selectall_xml - Query all elements from database with an XML output SYNOPSIS
stag-selectall_xml [-d <dbi>] [-f file of sql] [-nesting|n <nesting>] SQL DESCRIPTION
This script will query a database using either SQL provided by the script user, or using an SQL templates; the query results will be turned into XML using the DBIx::DBStag module. The nesting of the XML can be controlled by the DBStag SQL extension "USE NESTING..." EXAMPLES stag-selectall_xml -d "dbi:Pg:dbname=mydb;host=localhost" "SELECT * FROM a NATURAL JOIN b" TEMPLATES A parameterized SQL template (canned query) can be used instead of specifying the full SQL For example: stag-selectall_xml -d genedb /genedb-gene gene_symbol=Adh Or: stag-selectall_xml -d genedb /genedb-gene Adh Or: stag-selectall_xml -d genedb /genedb-gene gene_symbol@=Adh,dpp,bam,indy A template is indicated by the syntactic shorthand of using a slash to precede the template name; in this case the template is called genedb-gene. the -t option can also be used. All the remaining arguments are passed in as SQL template parameters. They can be passed in as either name=value pairs, or as a simple list of arguments which get passed into the template in order To use templates, you should have the environment variable DBSTAG_TEMPLATE_DIRS set. See DBIx::DBStag for details. LISTING AVAILABLE TEMPLATES FOR A DB stag-selectall_xml -d mydb -h LISTING VARIABLES FOR A TEMPLATE stag-selectall_xml /genedb-gene -h ENVIRONMENT VARIABLES
DBSTAG_DBIMAP_FILE A file containing configuration details for local databases DBSTAG_TEMPLATE_DIRS list of directories (separated by :s) to be searched when templates are requested COMMAND LINE ARGUMENTS
-h|help shows this page if no other arguments are given if a template is specified, gives template details if a db is specified, lists templates for that db use in conjunction with -v for full descriptions -d|dbname DBNAME this is either a full DBI locator string (eg dbi:Pg:dbname=mydb;host=localhost) or it can also be a shortened "nickname", which is then looked up in the file pointed at by the environment variable DBSTAG_DBIMAP_FILE -u|user USER database user identity -p|password PASS database password -f|file SQLFILE this is a path to a file containing SQL that will be executed, as an alternative to writing the SQL on the command line -n|nesting NESTING-EXPRESSIONS a bracketed expression indicating how to the resulting objects/XML should be nested. See DBIx::DBStag for details. -t|template TEMPLATE-NAME the name of a template; see above -wh|where WHERE-CLAUSE used to override the WHERE clause of the query; useful for combining with templates You can append to an existing where clause by using the prefix + -s|select SELECT-COLS used to override the SELECT clause of the query; useful for combining with templates -rows sometimes it is preferable to return the results as a table rather than xml or a similar nested structure. specifying -rows will fetch a table, one line per row, and columns separated by tabs -pre SQL a piece of SQL is that is executed immediately before the main query; e.g.: -pre "SET search_path=myschema,public" -o|out FILE a file to output the results to -w|writer WRITER writer class; can be any perl class, or one of these xml [default] sxpr lisp S-Expressions itext indented text -color shows results in color (sxpr and itext only) -show will show the parse of the SQL statement perl v5.12.4 2011-10-14 SELECTALL_XML(1p)