Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages

  
 
  

home | help
SQLite::Work(3)	      User Contributed Perl Documentation      SQLite::Work(3)

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

VERSION
       version 0.1601

SYNOPSIS
	   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();
	   }

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

       o   generate nice HTML (and non-HTML) reports, which

	   o   have nested headers

	   o   have grouped data which clusters	under the headers

	   o   can be sorted on	multiple columns

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

	   o   one can select the columns and their order even if one isn't
	       using templates

	   o   default templates can be	selected which present the data	in
	       tables, in paragraphs (Column:Value) or in lists.

	   o   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

       o   use a generic search	CGI script ("show.cgi" using
	   SQLite::Work::CGI) which

	   o   can search on all the fields in a table without having to
	       hardcode	the column names (it just gets them from the table
	       information)

	   o   uses most of the	power of the report engine to give nice	search
	       results

       o   update the database with a CGI script ("edit.cgi" using
	   SQLite::Work::CGI)

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

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

CLASS METHODS
   new
       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).

OBJECT METHODS
       Methods in the SQLite::Work object interface

   do_connect
       $rep->do_connect();

       Connect to the database.

   do_disconnect
       $rep->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:

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

       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.

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

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

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

   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();

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

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

Private	Methods
   print_select
       Print a selection result.

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

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

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

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

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

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

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

       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
       $sect = $self->start_section(type=>'table',
	   table_border=>$table_border,
	   table_class=>$table_class);

       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
       $sect = $self->end_section(type=>'table');

       End an old table/para/list

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

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

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.32.1			  2021-08-27		       SQLite::Work(3)

NAME | VERSION | SYNOPSIS | DESCRIPTION | CLASS METHODS | OBJECT METHODS | Helper Methods | Private Methods | BUGS | AUTHOR | COPYRIGHT AND LICENCE

Want to link to this manual page? Use this URL:
<https://www.freebsd.org/cgi/man.cgi?query=SQLite::Work&sektion=3&manpath=FreeBSD+13.0-RELEASE+and+Ports>

home | help