SQLREPORT(1) User Contributed Perl Documentation SQLREPORT(1)NAMEsqlreport - make reports on a table in an SQLite database
VERSION
version 0.16
SYNOPSISsqlreport--help | --manpage | --version
sqlreport [ --all_pages ] --database database_file [ --distinct ] {
--force_show colname=1 } { --groups template } { --headers template } {
--header_start num } [ --index_template template ] [ --filename_format
format ] [ --layout string ] [ --limit number ] [ --link_suffix string
] { --not_where colname=1 } [ --outfile filename ] [ --page number ] [
--report_style string ] { --row_ids table=colname } [ --report_template
template ] [ --row_template template ] [ --prev_next_template template
] [ --multi_page_template template ] [ --split_ind_template template ]
{ --show colname } { --sort_by colname } { --sort_reversed colname=1 }
[ --split_col colname [ --split_alpha number ] ] --table table [
--table_border number ] [ -- table_header string ] [ --title string ] [
--total ] [ --truncate_colnames number ] { --use_package pkgname } {
--where colname=string }
DESCRIPTION
This makes a report in HTML 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 HTML reports.
OPTIONS--all_pages
Make a multi-page report, generating all pages, by page-number.
The --limit and --outfile options are required for this.
--database
The name of the database file to use. (required)
--distinct
If columns are given to show (see show), then this will ensure that
rows with exactly the same values will not be repeated.
--force_show
An set of columns to always show in a row, even if they've already
been shown in a header (see show).
--groups
Group template(s) (or filenames of files containing group
templates). A group template is a template for values which are
"grouped" under a corresponding header. The first group in the
array is placed just after the first header in the report, and so
on.
This argument can be repeated.
See headers for more information.
--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.
The column names are the variable names in this template. This has
a different format to the report_template; it is more
sophisticated.
The format is as follows:
{$colname}
A variable; will display the value of the column, or nothing if
that value is empty.
{?colname stuff [$colname] more stuff}
A conditional. If the value of 'colname' is not empty, this
will display "stuff value-of-column more stuff"; otherwise it
displays nothing.
{?col1 stuff [$col1] thing [$col2]}
This would use both the values of col1 and col2 if col1 is not
empty.
{?colname stuff [$colname] more stuff!!other stuff}
A conditional with "else". If the value of 'colname' is not
empty, this will display "stuff value-of-column more stuff";
otherwise it displays "other stuff".
This version can likewise use multiple columns in its display
parts.
{?col1 stuff [$col1] thing [$col2]!![$col3]}
The same format is used for groups and row_template and
prev_next_template.
--help
Print help message and exit.
--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.
--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.
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).
para
The report is in paragraphs, each row of the report is one
paragraph.
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).
fieldval
The rows are not HTML-formatted. The generated row_template is
made up of Field:Value pairs, one on each line.
none
The rows are not HTML-formatted. The generated row_template is
made up of values, one on each line.
--limit
The maximum number of rows to display per page. If this is zero,
then all rows are displayed in one page.
--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.
--link_suffix '.shtml'
--link_suffix ''
--manpage
Print the full help documentation (manual page) and exit.
--not_where
A hash containing the column names where the selection criteria in
where should be negated.
--outfile
The name of the output file. If this is not given, or the name is
'-' then the output goes to STDOUT.
--page
Select which page to generate, if limit is not zero.
--prev_next_template
Template for previous and next links on multi-page reports.
--report_style
The style of the report, especially as regards table layout.
full
medium
compact
bare
--report_template
Either a string containing a template, or string containing the
name of a template file. The template variables are in the
following format:
<!--sqlr_title-->
The following variables are set for the report:
sqlr_title
Title (generally the table name).
sqlr_contents
The report itself.
--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)
--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).
Therefore it is important that if one provides a row_template, that
it matches the current layout.
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.
--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.
--sort_by
An array of column names by which the result should be sorted.
(Repeat the argument for each new value)
--sort_reversed
A hash of column names where the sorting given in sort_by should be
reversed.
--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)
--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.
--table
The table to report on. (required)
--table_border
For fine-tuning the report_style; if the layout is 'table', then
this overrides the default border-size of the table.
--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.
If this argument is not given, the table-header will be constructed
from the column names of the columns to be shown.
--title
The title of the report; if this is empty, a title will be
generated.
--total
Just print the total matching rows, then exit.
--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.
--verbose
Print informational messages.
--version
Print version information and exit.
--where
A hash containing selection criteria. The keys are the column
names and the values are strings suitable for using in a GLOB
condition; that is, '*' is a multi-character wildcard, and '?' is a
single-character wildcard. All the conditions will be ANDed
together.
Yes, this is limited and doesn't use the full power of SQL, but
it's useful enough for most purposes.
--use_package
An array of package names of packages to "use". This is mainly so
that the {&funcname())} construct of the templates (see
SQLite::Work::Template) can call functions within these packages
(using their fully-qualified names).
REQUIRES
Getopt::Long
Pod::Usage
Getopt::ArgvFile
SQLite::Work;
SEE ALSOperl(1) Getopt::Long Getopt::ArgvFile Pod::Usage
BUGS
Please report any bugs or feature requests to the author.
AUTHOR
Kathryn Andersen (RUBYKAT)
perlkat AT katspace dot com
http://www.katspace.com
COPYRIGHT AND LICENCE
Copyright (c) 2005 by Kathryn Andersen
This program is free software; you can redistribute it and/or modify it
under the same terms as Perl itself.
perl v5.20.2 2015-08-30 SQLREPORT(1)
