Limitations

This only deals with one database at a time.

new

Make a new report object.

This takes the following arguments:

database

The name of the SQLite database file. This is required.

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 gives a hash of table->column names.

join_cols

This covers simple joins of two tables, by providing the name of a commom column on which to join them. This is only used for presenting two tables separately in one report, not for a combined-table report; for that you are required to create a view.

Presenting two tables separately in one report is only done when only one row is being shown from the first table; then a second section shows the matching rows from the other table (if a second table has been asked for). This is mainly used for editing purposes (see SQLite::Work::CGI).

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:

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.

default_format

This contains the default format to use for the given columns in the given tables, when generating a row_template if a row_template has not been given. This is useful for things like CGI scripts where it isn't possible to know beforehand what sort of row_template is needed.

use_package

This contains an array of package names of packages to "use". This is mainly so that the {&funcname())} construct of the templates (see Text::NeatTemplate) can call functions within these packages (using their fully-qualified names).

do_connect

Connect to the database.

do_disconnect

Disconnect from the database.

do_report

    $rep->do_report(
        table=>$table,
        table2=>$table2,
        where=>\%where,
        not_where=>\%not_where,
        sort_by=>\@sort_by,
        show=>\@show,
        distinct=>0,
        headers=>\@headers,
        header_start=>1,
        groups=>\@groups,
        limit=>$limit,
        page=>$page,
        layout=>'table',
        row_template=>$row_template,
        outfile=>$outfile,
        report_style=>'full',
        table_border=>1,
        truncate_colnames=>0,
        title=>'',
    );

Select data from a table in the database, and make a HTML report.

Arguments are as follows (in alphabetical order):

distinct

If columns are given to show (see show), then this will ensure that rows with exactly the same values will not be repeated.

groups

An array of group templates (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.

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:

header_start

At what level the headers should start. Default is 1 (H1).

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.

limit

The maximum number of rows to display per page. If this is zero, then all rows are displayed in one page.

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 the name is '-' then the output goes to STDOUT.

page

Select which page to generate, if limit is not zero.

report_style

The style of the report, especially as regards table layout.

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.

sort_by

An array of column names by which the result should be sorted. If the column name is prefixed with a "-", the sort order should be reversed for that column.

table

The table to report on. (required)

table2

A second table to report on. If this is given, and join_cols have been defined, and the result of the query on the first table returns only one row (either because there's only one row, or because limit was set to 1), then a second, simpler, sub-report will be done on this table, displaying all the rows which match the join-value in the first table.

This is only really useful when doing editing with a CGI script.

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.

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.

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.

do_multi_page_report

    $rep->do_multi_page_report(
        table=>$table,
        table2=>$table2,
        where=>\%where,
        not_where=>\%not_where,
        sort_by=>\@sort_by,
        show=>\@show,
        headers=>\@headers,
        groups=>\@groups,
        limit=>$limit,
        page=>$page,
        layout=>'table',
        row_template=>$row_template,
        prev_next_template=>$prev_next_template,
        multi_page_template=>$multi_page_template,
        outfile=>$outfile,
        table_border=>1,
        table_class=>'plain',
        truncate_colnames=>0,
        report_style=>'full',
        link_suffix=>'.html',
    );

Select data from a table in the database, and make a HTML file for EVERY page in the report.

If the limit is zero, or the number of rows is less than the limit, or the outfile is destined for STDOUT, then calls do_report to do a single-page report.

If no rows match the criteria, does nothing and returns false.

Otherwise, it uses the 'outfile' name as a base upon which to build the file-names for all pages in the report (basically appending the page-number to the name), and generates a report file for each of them, and an index-page file which is called the 'outfile' value.

The 'link_suffix' argument, if given, overrides the suffix given in links to the other pages in this 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.

See do_report for information about the rest of the arguments.

do_split_report

    $rep->do_split_report(
        table=>$table,
        split_col=>$colname,
        split_alpha=>$n,
        command=>'Select',
        table2=>$table2,
        where=>\%where,
        not_where=>\%not_where,
        sort_by=>\@sort_by,
        show=>\@show,
        headers=>\@headers,
        header_start=>1,
        groups=>\@groups,
        limit=>$limit,
        page=>$page,
        layout=>'table',
        row_template=>$row_template,
        outfile=>$outfile,
        table_border=>1,
        table_class=>'plain',
        truncate_colnames=>0,
        report_style=>'full',
        link_suffix=>'.html',
    );

Build up a multi-file report, splitting it into different pages for each distinct value of the 'split_col' column. (If the outfile is destined for STDOUT, then this will call do_report intead).

The filenames generated will use 'outfile' as a prefix, and the column name and values as the rest; this calls in turn do_multi_page_report to break those into multiple pages if need be. An index-page is also generated, which will be called outfile + colname + .html

If 'split_alpha' is also given and is not zero, then instead of splitting on each distinct value in the 'split_col' column, the split is done by the truncated values of that column; if 'split_alpha' is 1, then the split is by the first letter, if it is 2, by the first two letters, and so on.

The 'link_suffix' argument, if given, overrides the suffix given in links to the other pages in this 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.

See do_report for information about the rest of the arguments.

get_total_matching

    $rep->get_total_matching(
        table=>$table,
        where=>\%where,
        not_where=>\%not_where,
    );

Get the total number of rows which match the selection criteria.

See do_report for the meaning of the arguments.

update_one_row

    if ($rep->update_one_field(
        table=>$table,
        row_id=>$row_id,
        field=>$field,
        update_values=>\%values,
    ))
    {
        ...
    }

Update one row; either a single column, or the whole row. Returns 0 if failure, or the constructed update query if success (so that one can be informative).

Sets $rep->{message} with a success message if successful.

add_one_row

    if ($rep->add_one_row(
        table=>$table,
        add_values=>\%values)) { ...
    }

Add a row to a table.

Sets $rep->{message} with a success message if successful.

delete_one_row

    if ($rep->delete_one_row(
        table=>$table,
        row_id=>$row_id)) { ...
    }

Delete a single row.

Sets $rep->{message} with a success message if successful.

do_import_fv

    if ($rep->do_import_fv(
        table=>$table,
        datafile=>$filename,
        row_delim=>"=")) { ...
    }

Import a field:value file into the given table. Field names are taken from the table; rows not starting with a field name "Field:" are taken to be a continuation of the previous field value.

Rows are delimited by the given row_delim argument on a line by itself.

Returns the number of records imported.

print_message

$self->print_message($message); # error message

$self->print_message($message, 0); # non-error message

(here so that it can be overridden, say, for a CGI script)

make_selections

    my ($sth1, $sth2) = $rep->make_selections(%args);

Make the selection(s) for the matching table(s).

get_tables

my @tables = $self->get_tables(views=>0);

Get the names of the tables (and views) in the database.

get_colnames

my @columns = $self->get_colnames($table, do_rowid=>0);

Get the column names of the given table.

get_distinct_col

    @vals = $rep->get_distinct_col(
        table=>$table,
        colname=>$colname,
        where=>\%where,
        not_where=>\%not_where,
    );

Get all the distinct values for the given column (which match the selection criteria).

print_select

get_template

Get the given template (read if it's from a file)

get_id_colname

Get the name of the column which is used for row-identification. (Most of the time it is just 'rowid')

get_join_colname

Get the name of the column which is used to join these two tables.

col_is_int

Checks the column type of the given column in the given table; returns true if it is an integer type.

format_report

Construct a HTML result table

get_row_template

    $row_template = $self->get_row_template(
        table=>$table,
        row_template=>$rt,
        layout=>'table',
        columns=>\@columns,
        show_cols=>\%show_cols,
        nice_cols=>\%nice_cols,
    );

Get or set or create the row template.

set_nice_cols

    %nice_cols = $self->set_nice_cols(
        truncate_colnames=>0,
        columns=>\@columns);

start_section

Start a new table/para/list The 'table_border' option is the border-size of the table if using table style The 'table_class' option is the class of the table if using table style

end_section

End an old table/para/list

build_where_conditions

    @where = $self->build_where_conditions(where=>\%where,
        not_where=>\%not_where);