.\" Automatically generated by Pod::Man v1.37, Pod::Parser v1.32
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sh \" Subsection heading
.br
.if t .Sp
.ne 5
.PP
\fB\\$1\fR
.PP
..
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" Set up some character translations and predefined strings.  \*(-- will
.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
.\" double quote, and \*(R" will give a right double quote.  | will give a
.\" real vertical bar.  \*(C+ will give a nicer C++.  Capital omega is used to
.\" do unbreakable dashes and therefore won't be available.  \*(C` and \*(C'
.\" expand to `' in nroff, nothing in troff, for use with C<>.
.tr \(*W-|\(bv\*(Tr
.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
.ie n \{\
.    ds -- \(*W-
.    ds PI pi
.    if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
.    if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\"  diablo 12 pitch
.    ds L" ""
.    ds R" ""
.    ds C` ""
.    ds C' ""
'br\}
.el\{\
.    ds -- \|\(em\|
.    ds PI \(*p
.    ds L" ``
.    ds R" ''
'br\}
.\"
.\" If the F register is turned on, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.Sh), items (.Ip), and index
.\" entries marked with X<> in POD.  Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.if \nF \{\
.    de IX
.    tm Index:\\$1\t\\n%\t"\\$2"
..
.    nr % 0
.    rr F
.\}
.\"
.\" For nroff, turn off justification.  Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.hy 0
.if n .na
.\"
.\" Accent mark definitions (@(#)ms.acc 1.5 88/02/08 SMI; from UCB 4.2).
.\" Fear.  Run.  Save yourself.  No user-serviceable parts.
.    \" fudge factors for nroff and troff
.if n \{\
.    ds #H 0
.    ds #V .8m
.    ds #F .3m
.    ds #[ \f1
.    ds #] \fP
.\}
.if t \{\
.    ds #H ((1u-(\\\\n(.fu%2u))*.13m)
.    ds #V .6m
.    ds #F 0
.    ds #[ \&
.    ds #] \&
.\}
.    \" simple accents for nroff and troff
.if n \{\
.    ds ' \&
.    ds ` \&
.    ds ^ \&
.    ds , \&
.    ds ~ ~
.    ds /
.\}
.if t \{\
.    ds ' \\k:\h'-(\\n(.wu*8/10-\*(#H)'\'\h"|\\n:u"
.    ds ` \\k:\h'-(\\n(.wu*8/10-\*(#H)'\`\h'|\\n:u'
.    ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'^\h'|\\n:u'
.    ds , \\k:\h'-(\\n(.wu*8/10)',\h'|\\n:u'
.    ds ~ \\k:\h'-(\\n(.wu-\*(#H-.1m)'~\h'|\\n:u'
.    ds / \\k:\h'-(\\n(.wu*8/10-\*(#H)'\z\(sl\h'|\\n:u'
.\}
.    \" troff and (daisy-wheel) nroff accents
.ds : \\k:\h'-(\\n(.wu*8/10-\*(#H+.1m+\*(#F)'\v'-\*(#V'\z.\h'.2m+\*(#F'.\h'|\\n:u'\v'\*(#V'
.ds 8 \h'\*(#H'\(*b\h'-\*(#H'
.ds o \\k:\h'-(\\n(.wu+\w'\(de'u-\*(#H)/2u'\v'-.3n'\*(#[\z\(de\v'.3n'\h'|\\n:u'\*(#]
.ds d- \h'\*(#H'\(pd\h'-\w'~'u'\v'-.25m'\f2\(hy\fP\v'.25m'\h'-\*(#H'
.ds D- D\\k:\h'-\w'D'u'\v'-.11m'\z\(hy\v'.11m'\h'|\\n:u'
.ds th \*(#[\v'.3m'\s+1I\s-1\v'-.3m'\h'-(\w'I'u*2/3)'\s-1o\s+1\*(#]
.ds Th \*(#[\s+2I\s-2\h'-\w'I'u*3/5'\v'-.3m'o\v'.3m'\*(#]
.ds ae a\h'-(\w'a'u*4/10)'e
.ds Ae A\h'-(\w'A'u*4/10)'E
.    \" corrections for vroff
.if v .ds ~ \\k:\h'-(\\n(.wu*9/10-\*(#H)'\s-2\u~\d\s+2\h'|\\n:u'
.if v .ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'\v'-.4m'^\v'.4m'\h'|\\n:u'
.    \" for low resolution devices (crt and lpr)
.if \n(.H>23 .if \n(.V>19 \
\{\
.    ds : e
.    ds 8 ss
.    ds o a
.    ds d- d\h'-1'\(ga
.    ds D- D\h'-1'\(hy
.    ds th \o'bp'
.    ds Th \o'LP'
.    ds ae ae
.    ds Ae AE
.\}
.rm #[ #] #H #V #F C
.\" ========================================================================
.\"
.IX Title "SQLREPORT 1"
.TH SQLREPORT 1 "2008-01-01" "perl v5.8.8" "User Contributed Perl Documentation"
.SH "NAME"
sqlreport \- make reports on a table in an SQLite database
.SH "VERSION"
.IX Header "VERSION"
This describes version \fB0.1001\fR of sqlreport.
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
sqlreport \-\-help | \-\-manpage | \-\-version
.PP
sqlreport [ \-\-all_pages ] \-\-database \fIdatabase_file\fR
[ \-\-distinct ] { \-\-force_show \fIcolname\fR=1 }
{ \-\-groups \fItemplate\fR } { \-\-headers \fItemplate\fR }
[ \-\-index_template \fItemplate\fR ]
[ \-\-layout \fIstring\fR ] [ \-\-limit \fInumber\fR ] [ \-\-link_suffix \fIstring\fR ]
{ \-\-not_where \fIcolname\fR=1 } [ \-\-outfile \fIfilename\fR ]
[ \-\-page \fInumber\fR ] [ \-\-report_style \fIstring\fR ]
{ \-\-row_ids \fItable\fR=\fIcolname\fR } [ \-\-report_template \fItemplate\fR ]
[ \-\-row_template \fItemplate\fR ] { \-\-show \fIcolname\fR }
{ \-\-sort_by \fIcolname\fR } { \-\-sort_reversed \fIcolname\fR=1 }
[ \-\-split_col \fIcolname\fR [ \-\-split_alpha \fInumber\fR ] ]
\&\-\-table \fItable\fR [ \-\-table_border \fInumber\fR ]
[ \*(-- table_header \fIstring\fR ]
[ \-\-title \fIstring\fR ] [ \-\-total ] [ \-\-truncate_colnames \fInumber\fR ]
{ \-\-use_package \fIpkgname\fR } { \-\-where \fIcolname\fR=\fIstring\fR }
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
This makes a report in \s-1HTML\s0 format, of a single table from an
SQLite database.  One can also create a non-HTML report if one
gives a certain combination of options, but this is more oriented
towards \s-1HTML\s0 reports.
.SH "OPTIONS"
.IX Header "OPTIONS"
.IP "\-\-all_pages" 4
.IX Item "--all_pages"
Make a multi-page report, generating all pages, by page\-number.
The \-\-limit and \-\-outfile options are required for this.
.IP "\-\-database" 4
.IX Item "--database"
The name of the database file to use. (required)
.IP "\-\-distinct" 4
.IX Item "--distinct"
If columns are given to show (see show), then this will
ensure that rows with exactly the same values will not be
repeated.
.IP "\-\-force_show" 4
.IX Item "--force_show"
An set of columns to always show in a row, even if they've
already been shown in a header (see show).
.IP "\-\-groups" 4
.IX Item "--groups"
Group template(s) (or filenames of files containing
group templates).  A group template is a template for values
which are \*(L"grouped\*(R" under a corresponding header.  The first
group in the array is placed just after the first header in
the report, and so on.
.Sp
This argument can be repeated.
.Sp
See headers for more information.
.IP "\-\-headers" 4
.IX Item "--headers"
An array of header templates (or filenames of files containing header
templates).  A header template lays out what values should be put
into headers rather than the body of the report.  The first header
template is given a H1 header, the second a H2 header, and so on.
Headers are shown only when the value(s) they depend on change,
but they get their values from each row in the report.  Therefore
the columns used in the headers should match the columns used in the
sort_by array.
.Sp
The column names are the variable names in this template.  This has
a different format to the report_template; it is more sophisticated.
.Sp
The format is as follows:
.RS 4
.IP "{$colname}" 4
.IX Item "{$colname}"
A variable; will display the value of the column, or nothing if
that value is empty.
.IP "{?colname stuff [$colname] more stuff}" 4
.IX Item "{?colname stuff [$colname] more stuff}"
A conditional.  If the value of 'colname' is not empty, this will
display \*(L"stuff value-of-column more stuff\*(R"; otherwise it displays
nothing.
.Sp
.Vb 1
\&    {?col1 stuff [$col1] thing [$col2]}
.Ve
.Sp
This would use both the values of col1 and col2 if col1 is not
empty.
.IP "{?colname stuff [$colname] more stuff!!other stuff}" 4
.IX Item "{?colname stuff [$colname] more stuff!!other stuff}"
A conditional with \*(L"else\*(R".  If the value of 'colname' is not empty, this
will display \*(L"stuff value-of-column more stuff\*(R"; otherwise it displays
\&\*(L"other stuff\*(R".
.Sp
This version can likewise use multiple columns in its display parts.
.Sp
.Vb 1
\&    {?col1 stuff [$col1] thing [$col2]!![$col3]}
.Ve
.RE
.RS 4
.Sp
The same format is used for groups and row_template.
.RE
.IP "\-\-help" 4
.IX Item "--help"
Print help message and exit.
.IP "\-\-index_template" 4
.IX Item "--index_template"
Similar to the report_template, but this is used for the index-pages
in multi-page and split reports.  It has the same format, but it
can be useful to have them as two separate templates as one may wish
to change the way the title is treated for indexes versus actual
reports.
.IP "\-\-layout" 4
.IX Item "--layout"
The layout of the report.  This determines both how rows are grouped,
and what is in the generated row_template if no row_template is
given.
.RS 4
.IP "table" 4
.IX Item "table"
The report is a (group of) tables, each row of the report is a row in
the table; a new table occurs after the heading(s).
.IP "para" 4
.IX Item "para"
The report is in paragraphs, each row of the report is one paragraph.
.IP "list" 4
.IX Item "list"
The report is a (group of) lists, each row of the report is an item in
the list; a new list occurs after the heading(s).
.IP "fieldval" 4
.IX Item "fieldval"
The rows are not HTML\-formatted.  The generated row_template is made up
of Field:Value pairs, one on each line.
.IP "none" 4
.IX Item "none"
The rows are not HTML\-formatted.  The generated row_template is made up
of values, one on each line.
.RE
.RS 4
.RE
.IP "\-\-limit" 4
.IX Item "--limit"
The maximum number of rows to display per page.  If this is zero,
then all rows are displayed in one page.
.IP "\-\-link_suffix \fIstring\fR" 4
.IX Item "--link_suffix string"
The 'link_suffix' argument, if given, overrides the suffix given
in links to the other pages in a multi-page report; this is useful
if you're post-processing the files (and thus changing their extensions)
or are using something like Apache MultiViews to eliminate the need for
extensions in links.
.Sp
.Vb 1
\&    --link_suffix '.shtml'
.Ve
.Sp
.Vb 1
\&    --link_suffix ''
.Ve
.IP "\-\-manpage" 4
.IX Item "--manpage"
Print the full help documentation (manual page) and exit.
.IP "\-\-not_where" 4
.IX Item "--not_where"
A hash containing the column names where the selection criteria
in where should be negated.
.IP "\-\-outfile" 4
.IX Item "--outfile"
The name of the output file.  If this is not given, or the name is '\-'
then the output goes to \s-1STDOUT\s0.
.IP "\-\-page" 4
.IX Item "--page"
Select which page to generate, if limit is not zero.
.IP "\-\-report_style" 4
.IX Item "--report_style"
The style of the report, especially as regards table layout.
.RS 4
.IP "full" 4
.IX Item "full"
.PD 0
.IP "medium" 4
.IX Item "medium"
.IP "compact" 4
.IX Item "compact"
.IP "bare" 4
.IX Item "bare"
.RE
.RS 4
.RE
.IP "\-\-report_template" 4
.IX Item "--report_template"
.PD
Either a string containing a template, or string containing the name of
a template file.  The template variables are in the following format:
.Sp
<!\-\-sqlr_title\-\->
.Sp
The following variables are set for the report:
.RS 4
.IP "sqlr_title" 4
.IX Item "sqlr_title"
Title (generally the table name).
.IP "sqlr_contents" 4
.IX Item "sqlr_contents"
The report itself.
.RE
.RS 4
.RE
.IP "\-\-row_ids" 4
.IX Item "--row_ids"
The default column-name which identifies rows in SQLite is 'rowid', but
for tables which have a primary integer key, this doesn't work (even
though the documentation says it ought to).  Therefore it is necessary
to identify, for the given database, which tables need to use a
different column-name for this.  (This can be repeated)
.IP "\-\-row_template" 4
.IX Item "--row_template"
The template for each row.  This uses the same format as for headers.
If none is given, then a default row_template will be generated,
depending on what layout and which columns are going to be shown
(see show).
.Sp
Therefore it is important that if one provides a row_template, that
it matches the current layout.
.Sp
Also note that if a column is given in a header, it will not be
displayed in a row, even if it is put into the row_template.
.IP "\-\-show" 4
.IX Item "--show"
An array of columns to select; also the order in which they should
be shown when a row_template has not been given.
If this option is not used, all columns in the table will be shown.
.IP "\-\-sort_by" 4
.IX Item "--sort_by"
An array of column names by which the result should be sorted.
(Repeat the argument for each new value)
.IP "\-\-sort_reversed" 4
.IX Item "--sort_reversed"
A hash of column names where the sorting given in sort_by should
be reversed.
.IP "\-\-split_col" 4
.IX Item "--split_col"
Generate a multi-page report where pages are split by the value
of the given column (as well as by page-number if a limit is given)
.IP "\-\-split_alpha" 4
.IX Item "--split_alpha"
If one is generating a split_col report, giving the 'split_alpha' option
splits the report not by the distinct values of that column, but
by truncated values of the column; giving a split_alpha value
of 1 takes only the first letter, and so on.
.IP "\-\-table" 4
.IX Item "--table"
The table to report on. (required)
.IP "\-\-table_border" 4
.IX Item "--table_border"
For fine-tuning the report_style; if the layout is 'table',
then this overrides the default border-size of the table.
.IP "\-\-table_header" 4
.IX Item "--table_header"
When the report layout is 'table' and the report_style is not 'bare',
then this argument can be used to customize the table-header
of the report table.  This must either contain the contents
of the table\-header, or the name of a file which contains
the contents of the table\-header.
.Sp
If this argument is not given, the table-header will be constructed
from the column names of the columns to be shown.
.IP "\-\-title" 4
.IX Item "--title"
The title of the report; if this is empty, a title will be generated.
.IP "\-\-total" 4
.IX Item "--total"
Just print the total matching rows, then exit.
.IP "\-\-truncate_colnames" 4
.IX Item "--truncate_colnames"
For fine-tuning the report_style; this affects the length of
column names given in layouts which use them, that is, 'table'
(for all styles except 'bare') and 'para'.  If the value is zero,
the column names are not truncated at all; otherwise they are
truncated to that number of characters.
.IP "\-\-verbose" 4
.IX Item "--verbose"
Print informational messages.
.IP "\-\-version" 4
.IX Item "--version"
Print version information and exit.
.IP "\-\-where" 4
.IX Item "--where"
A hash containing selection criteria.  The keys are the column names
and the values are strings suitable for using in a \s-1GLOB\s0 condition;
that is, '*' is a multi-character wildcard, and '?' is a
single-character wildcard.  All the conditions will be ANDed together.
.Sp
Yes, this is limited and doesn't use the full power of \s-1SQL\s0, but it's
useful enough for most purposes.
.IP "\-\-use_package" 4
.IX Item "--use_package"
An array of package names of packages to \*(L"use\*(R".
This is mainly so that the {&\fIfuncname()\fR)} construct of
the templates (see SQLite::Work::Template) can call
functions within these packages (using their fully-qualified
names).
.SH "REQUIRES"
.IX Header "REQUIRES"
.Vb 4
\&    Getopt::Long
\&    Pod::Usage
\&    Getopt::ArgvFile
\&    SQLite::Work;
.Ve
.SH "SEE ALSO"
.IX Header "SEE ALSO"
\&\fIperl\fR\|(1)
Getopt::Long
Getopt::ArgvFile
Pod::Usage
.SH "BUGS"
.IX Header "BUGS"
Please report any bugs or feature requests to the author.
.SH "AUTHOR"
.IX Header "AUTHOR"
.Vb 3
\&    Kathryn Andersen (RUBYKAT)
\&    perlkat AT katspace dot com
\&    http://www.katspace.com
.Ve
.SH "COPYRIGHT AND LICENCE"
.IX Header "COPYRIGHT AND LICENCE"
Copyright (c) 2005 by Kathryn Andersen
.PP
This program is free software; you can redistribute it and/or modify it
under the same terms as Perl itself.