| 1 |
<HTML> |
| 2 |
<HEAD> |
| 3 |
<TITLE>selectall_xml</TITLE> |
| 4 |
<LINK REV="made" HREF="mailto:[email protected]"> |
| 5 |
</HEAD> |
| 6 |
|
| 7 |
<BODY> |
| 8 |
|
| 9 |
<A NAME="__index__"></A> |
| 10 |
<!-- INDEX BEGIN --> |
| 11 |
|
| 12 |
<UL> |
| 13 |
|
| 14 |
<LI><A HREF="#name">NAME</A></LI> |
| 15 |
<LI><A HREF="#synopsis">SYNOPSIS</A></LI> |
| 16 |
<LI><A HREF="#description">DESCRIPTION</A></LI> |
| 17 |
<UL> |
| 18 |
|
| 19 |
<LI><A HREF="#examples">EXAMPLES</A></LI> |
| 20 |
<LI><A HREF="#templates">TEMPLATES</A></LI> |
| 21 |
<LI><A HREF="#listing available templates for a db">LISTING AVAILABLE TEMPLATES FOR A DB</A></LI> |
| 22 |
<LI><A HREF="#listing variables for a template">LISTING VARIABLES FOR A TEMPLATE</A></LI> |
| 23 |
</UL> |
| 24 |
|
| 25 |
<LI><A HREF="#environment variables">ENVIRONMENT VARIABLES</A></LI> |
| 26 |
<LI><A HREF="#command line arguments">COMMAND LINE ARGUMENTS</A></LI> |
| 27 |
</UL> |
| 28 |
<!-- INDEX END --> |
| 29 |
|
| 30 |
<HR> |
| 31 |
<P> |
| 32 |
<H1><A NAME="name">NAME</A></H1> |
| 33 |
<P>selectall_xml.pl</P> |
| 34 |
<P> |
| 35 |
<HR> |
| 36 |
<H1><A NAME="synopsis">SYNOPSIS</A></H1> |
| 37 |
<PRE> |
| 38 |
selectall_xml.pl [-d <dbi>] [-f file of sql] [-nesting|n <nesting>] SQL</PRE> |
| 39 |
<P> |
| 40 |
<HR> |
| 41 |
<H1><A NAME="description">DESCRIPTION</A></H1> |
| 42 |
<P>This script will query a database using either SQL provided by the |
| 43 |
script user, or using an SQL templates; the query results will be |
| 44 |
turned into XML using the <A HREF="./DBIx/DBStag.html">the DBIx::DBStag manpage</A> module. The nesting of the |
| 45 |
XML can be controlled by the DBStag SQL extension ``USE NESTING...''</P> |
| 46 |
<P> |
| 47 |
<H2><A NAME="examples">EXAMPLES</A></H2> |
| 48 |
<PRE> |
| 49 |
selectall_xml.pl -d "dbi:Pg:dbname=mydb;host=localhost"\ |
| 50 |
"SELECT * FROM a NATURAL JOIN b"</PRE> |
| 51 |
<P> |
| 52 |
<H2><A NAME="templates">TEMPLATES</A></H2> |
| 53 |
<P>A parameterized SQL template (canned query) can be used instead of |
| 54 |
specifying the full SQL</P> |
| 55 |
<P>For example:</P> |
| 56 |
<PRE> |
| 57 |
selectall_xml.pl -d genedb /genedb-gene gene_symbol=Adh</PRE> |
| 58 |
<P>Or:</P> |
| 59 |
<PRE> |
| 60 |
selectall_xml.pl -d genedb /genedb-gene Adh</PRE> |
| 61 |
<P>A template is indicated by the syntactic shorthand of using a slash to |
| 62 |
precede the template name; in this case the template is called |
| 63 |
<STRONG>genedb-gene</STRONG>. the -t option can also be used.</P> |
| 64 |
<P>All the remaining arguments are passed in as SQL template |
| 65 |
parameters. They can be passed in as either name=value pairs, or as a |
| 66 |
simple list of arguments which get passed into the template in order</P> |
| 67 |
<P>To use templates, you should have the environment variable |
| 68 |
<STRONG>DBSTAG_TEMPLATE_DIRS</STRONG> set. See <STRONG>DBIx::DBStag</STRONG> for details.</P> |
| 69 |
<P> |
| 70 |
<H2><A NAME="listing available templates for a db">LISTING AVAILABLE TEMPLATES FOR A DB</A></H2> |
| 71 |
<PRE> |
| 72 |
selectall_xml.pl -d mydb -h</PRE> |
| 73 |
<P> |
| 74 |
<H2><A NAME="listing variables for a template">LISTING VARIABLES FOR A TEMPLATE</A></H2> |
| 75 |
<PRE> |
| 76 |
selectall_xml.pl /genedb-gene -h</PRE> |
| 77 |
<P> |
| 78 |
<HR> |
| 79 |
<H1><A NAME="environment variables">ENVIRONMENT VARIABLES</A></H1> |
| 80 |
<DL> |
| 81 |
<DT><STRONG><A NAME="item_DBSTAG_DBIMAP_FILE">DBSTAG_DBIMAP_FILE</A></STRONG><BR> |
| 82 |
<DD> |
| 83 |
A file containing configuration details for local databases |
| 84 |
<P></P> |
| 85 |
<DT><STRONG><A NAME="item_DBSTAG_TEMPLATE_DIRS">DBSTAG_TEMPLATE_DIRS</A></STRONG><BR> |
| 86 |
<DD> |
| 87 |
list of directories (seperated by <STRONG>:</STRONG>s) to be searched when templates |
| 88 |
are requested |
| 89 |
<P></P></DL> |
| 90 |
<P> |
| 91 |
<HR> |
| 92 |
<H1><A NAME="command line arguments">COMMAND LINE ARGUMENTS</A></H1> |
| 93 |
<DL> |
| 94 |
<DT><STRONG><A NAME="item_%2Dh%7Chelp">-h|help</A></STRONG><BR> |
| 95 |
<DD> |
| 96 |
shows this page if no other arguments are given |
| 97 |
<P>if a template is specified, gives template details</P> |
| 98 |
<P>if a db is specified, lists templates for that db</P> |
| 99 |
<P>use in conjunction with -v for full descriptions</P> |
| 100 |
<P></P> |
| 101 |
<DT><STRONG><A NAME="item_%2Dd%7Cdbname_DBNAME">-d|dbname DBNAME</A></STRONG><BR> |
| 102 |
<DD> |
| 103 |
this is either a full DBI locator string (eg |
| 104 |
<STRONG>dbi:Pg:dbname=mydb;host=localhost</STRONG>) or it can also be a shortened |
| 105 |
``nickname'', which is then looked up in the file pointed at by the |
| 106 |
environment variable <STRONG>DBSTAG_DBIMAP_FILE</STRONG> |
| 107 |
<P></P> |
| 108 |
<DT><STRONG><A NAME="item_%2Du%7Cuser_USER">-u|user USER</A></STRONG><BR> |
| 109 |
<DD> |
| 110 |
database user identity |
| 111 |
<P></P> |
| 112 |
<DT><STRONG><A NAME="item_%2Dp%7Cpassword_PASS">-p|password PASS</A></STRONG><BR> |
| 113 |
<DD> |
| 114 |
database password |
| 115 |
<P></P> |
| 116 |
<DT><STRONG><A NAME="item_%2Df%7Cfile_SQLFILE">-f|file SQLFILE</A></STRONG><BR> |
| 117 |
<DD> |
| 118 |
this is a path to a file containing SQL that will be executed, as an |
| 119 |
alternative to writing the SQL on the command line |
| 120 |
<P></P> |
| 121 |
<DT><STRONG><A NAME="item_%2Dn%7Cnesting_NESTING%2DEXPRESSIONS">-n|nesting NESTING-EXPRESSIONS</A></STRONG><BR> |
| 122 |
<DD> |
| 123 |
a bracketed expression indicating how to the resulting objects/XML |
| 124 |
should be nested. See <A HREF="./DBIx/DBStag.html">the DBIx::DBStag manpage</A> for details. |
| 125 |
<P></P> |
| 126 |
<DT><STRONG><A NAME="item_%2Dt%7Ctemplate_TEMPLATE%2DNAME">-t|template TEMPLATE-NAME</A></STRONG><BR> |
| 127 |
<DD> |
| 128 |
the name of a template; see above |
| 129 |
<P></P> |
| 130 |
<DT><STRONG><A NAME="item_%2Dwh%7Cwhere_WHERE%2DCLAUSE">-wh|where WHERE-CLAUSE</A></STRONG><BR> |
| 131 |
<DD> |
| 132 |
used to override the WHERE clause of the query; useful for combining |
| 133 |
with templates |
| 134 |
<P>You can append to an existing where clause by using the prefix <STRONG>+</STRONG></P> |
| 135 |
<P></P> |
| 136 |
<DT><STRONG><A NAME="item_%2Ds%7Cselect_SELECT%2DCOLS">-s|select SELECT-COLS</A></STRONG><BR> |
| 137 |
<DD> |
| 138 |
used to override the SELECT clause of the query; useful for combining |
| 139 |
with templates |
| 140 |
<P></P> |
| 141 |
<DT><STRONG><A NAME="item_%2Drows">-rows</A></STRONG><BR> |
| 142 |
<DD> |
| 143 |
sometimes it is preferable to return the results as a table rather |
| 144 |
than xml or a similar nested structure. specifying -rows will fetch a |
| 145 |
table, one line per row, and columns seperated by tabs |
| 146 |
<P></P> |
| 147 |
<DT><STRONG><A NAME="item_%2Do%7Cout_FILE">-o|out FILE</A></STRONG><BR> |
| 148 |
<DD> |
| 149 |
a file to output the results to |
| 150 |
<P></P> |
| 151 |
<DT><STRONG><A NAME="item_%2Dw%7Cwriter_WRITER">-w|writer WRITER</A></STRONG><BR> |
| 152 |
<DD> |
| 153 |
writer class; can be any perl class, or one of these |
| 154 |
<DL> |
| 155 |
<DT><STRONG><A NAME="item_xml_%5Bdefault%5D">xml [default]</A></STRONG><BR> |
| 156 |
<DD> |
| 157 |
<DT><STRONG><A NAME="item_sxpr">sxpr</A></STRONG><BR> |
| 158 |
<DD> |
| 159 |
lisp S-Expressions |
| 160 |
<P></P> |
| 161 |
<DT><STRONG><A NAME="item_itext">itext</A></STRONG><BR> |
| 162 |
<DD> |
| 163 |
indented text |
| 164 |
<P></P></DL> |
| 165 |
<DT><STRONG><A NAME="item_%2Dcolor">-color</A></STRONG><BR> |
| 166 |
<DD> |
| 167 |
shows results in color (sxpr and itext only) |
| 168 |
<P></P> |
| 169 |
<DT><STRONG><A NAME="item_%2Dshow">-show</A></STRONG><BR> |
| 170 |
<DD> |
| 171 |
will show the parse of the SQL statement |
| 172 |
<P></P></DL> |
| 173 |
|
| 174 |
</BODY> |
| 175 |
|
| 176 |
</HTML> |