SQLite::Work - report on and update an SQLite database.

This describes version 0.1002 of SQLite::Work.

    use SQLite::Work;

    my $rep = SQLite::Work->new(%new_args);

    if ($rep->do_connect())
    {
	if ($simple_report)
	{
	    $rep->do_report(%report_args);
	}
	elsif ($multi_page_report)
	{
	    $rep->do_multi_page_report(%report_args);
	}
	elsif ($split_report)
	{
	    $rep->do_split_report(%report_args);
	}
	$rep->do_disconnect();
    }

SQLite::Work is a perl module for interfacing with an SQLite database. It can be used to:

• generate nice HTML (and non-HTML) reports, which
• • have nested headers
  • have grouped data which clusters under the headers
  • can be sorted on multiple columns
  • can be customized with templates (both headers and body) which include some simple formatting for column values, for example:
    
    

        simple HTMLize
        titles (Title,The becomes The Title)
        names (Nurk,Fred becomes Fred Nurk)
        month names
        truncation
    
    

    
    
    (see Text::NeatTemplate)
  • one can select the columns and their order even if one isn't using templates
  • default templates can be selected which present the data in tables, in paragraphs (Column:Value) or in lists.
  • can be split into multiple HTML pages, with automatic index-page generation; the split can be on the values of a given column, and/or by number of rows
• use a generic search CGI script ("show.cgi" using SQLite::Work::CGI) which
• • can search on all the fields in a table without having to hardcode the column names (it just gets them from the table information)
  • uses most of the power of the report engine to give nice search results
• update the database with a CGI script ("edit.cgi" using SQLite::Work::CGI)
• be able to mail reports to general addresses (such as a mailing list) or to specific addresses (such as sending notifications to individuals whose address is in the database). (using the sqlw_mail script)

This generates HTML (and non-HTML) reports from an SQLite database, taking care of the query-building and the report formatting. This also has methods for adding and updating the database.

The SQLite::Work::CGI module has extra methods which deal with CGI using the CGI module; the included "show.cgi" and "edit.cgi" are demonstration CGI scripts which use the SQLite::Work::CGI module. There is also the "show.epl" demonstration Embperl script which has the necessary alterations for using this with Embperl.

The sqlreport script uses SQLite::Work to generate reports from the command-line.

The sqlw_mail script uses SQLite::Work::Mail to email reports.

This only deals with single tables and views, and simple one-field, two-table joins. More complex joins should be dealt with by making a view.

This only deals with one database at a time.

my $rep = SQLite::Work->new( database=>$database_file, row_ids=>{ episodes=>'title_id', }, join_cols=>{ 'episodes+recordings'=>'title_id', } }, report_template=>$template, default_format=>{ 'episodes' => { 'title'=>'title', 'series_title'=>'title', } }, use_package=>[qw(File::Basename MyPackage)], );

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:

sqlr_title

Title (generally the table name).

sqlr_contents

The report itself.

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).

Methods in the SQLite::Work object interface

$rep->do_connect();

Connect to the database.

$rep->do_disconnect();

Disconnect from the database.

    $rep->do_report(
	table=>$table,
	table2=>$table2,
	where=>\%where,
	not_where=>\%not_where,
	sort_by=>\@sort_by,
	sort_reversed=>\%sort_reversed,
	show=>\@show,
	distinct=>0,
	headers=>\@headers,
	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:

{$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.

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.

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.

full

medium

compact

bare

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.

sort_reversed

A hash of column names where the sorting given in sort_by should be reversed.

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.

    $rep->do_multi_page_report(
	table=>$table,
	table2=>$table2,
	where=>\%where,
	not_where=>\%not_where,
	sort_by=>\@sort_by,
	sort_reversed=>\%sort_reversed,
	show=>\@show,
	headers=>\@headers,
	groups=>\@groups,
	limit=>$limit,
	page=>$page,
	layout=>'table',
	row_template=>$row_template,
	outfile=>$outfile,
	table_border=>1,
	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.

    $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,
	sort_reversed=>\%sort_reversed,
	show=>\@show,
	headers=>\@headers,
	groups=>\@groups,
	limit=>$limit,
	page=>$page,
	layout=>'table',
	row_template=>$row_template,
	outfile=>$outfile,
	table_border=>1,
	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.

    $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.

    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.

    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.

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

Delete a single row.

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

    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.

Lower-level methods, generally just called from other methods, but possibly suitable for other things.

Print an (error) message to the user.

$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)

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

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

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

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

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

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

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

Get the column names of the given table.

    @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 a selection result.

my $templ = $self->get_template($template);

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

$id_colname = $self->get_id_colname($table);

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

$join_col = $self->get_join_colname($table1, $table2);

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

my $res = $self->col_is_int(table=>$table, column=>$column);

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

$my report = $self->format_report( table=>$table, command=>'Search', columns=>\@columns, force_show_cols=>\%force_show_cols, sort_by=>\@sort_by, headers=>\@headers, table2=>$table2, layout=>'table', row_template=>$row_template, report_style=>'compact', table_header=>$thead, table_border=>1, truncate_colnames=>0, );

Construct a HTML result table

    $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.

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

$sect = $self->start_section(type=>'table', table_border=>$table_border);

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

$sect = $self->end_section(type=>'table');

End an old table/para/list

Take the %where, %not_where hashes and make an array of SQL conditions.

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

    DBI
    DBD::SQLite
    POSIX

    Test::More

    The CGI module requires:

    CGI

    Scripts require:

    Getopt::Long
    Pod::Usage
    Getopt::ArgvFile

To install this module, run the following commands:

    perl Build.PL
    ./Build
    ./Build test
    ./Build install

Or, if you're on a platform (like DOS or Windows) that doesn't like the "./" notation, you can do this:

   perl Build.PL
   perl Build
   perl Build test
   perl Build install

In order to install somewhere other than the default, such as in a directory under your home directory, like "/home/fred/perl" go

   perl Build.PL --install_base /home/fred/perl

as the first step instead.

This will install the files underneath /home/fred/perl.

You will then need to make sure that you alter the PERL5LIB variable to find the modules, and the PATH variable to find the script.

Therefore you will need to change: your path, to include /home/fred/perl/script (where the script will be)

	PATH=/home/fred/perl/script:${PATH}

the PERL5LIB variable to add /home/fred/perl/lib

	PERL5LIB=/home/fred/perl/lib:${PERL5LIB}

perl(1). DBI DBD::SQLite

Please report any bugs or feature requests to the author.

    Kathryn Andersen (RUBYKAT)
    perlkat AT katspace dot com
    http://www.katspace.com

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.