Developers > Kernel Documentation > Database Management > Report.pm

Report.pm

ExSite::Report - general purpose tools for displaying database data

The Report class inherits from the DB and Auth classes, and is in turn inherited by the Form class. This help document describes only the methods that are unique to the Report class. Consult the docs for inherited classes to read about inherited methods. Code that requires write access to the database will typically work with the Form class, but read-only access can use a Report object instead.

Usage: my $db = new ExSite::Report;

By default, ExSite connects to its default database, which contains all the web content data. You can connect to an alternate database using a call like this:

my $db = new ExSite::Report(type=>"sql",name=>"mydb",map=>"mapname",user=>"username",pass=>"password");

Obtaining Meta-data for Tables

primary_column() - select the primary display column(s)

Usage: $db->primary_column($table)

From a reporting point of view, the primary display column is the column whose data best represents the record from a human point of view. This column will be used to make record titles, and hotlinks to the record.

This is not the same as the primary key, which is the column that best represents the record from the computers point of view. For example, in a ``person'' record, the computer's primary key will probably be a numeric record ID, whereas the human-readable primary display column will probably be the person's name. However, if no primary display column is defined, the primary key will be used by default.

More than one primary display column can be defined in the DBmap, in which case an array of column names will be returned, eg. (``first_name'',``last_name'').

title() - returns a title for a particular record

Usage: $db->title($table,$record,$rdata)

Given a table name ($table), a record ID ($record), and a hash ref pointing to the record data ($rdata), this routine will compose an appropriate title for the record, using the table's primary display column(s). The record title is typically:

TABLE_NAME ``PRIMARY_DISPLAY_COLUMN''

For example, Member ``John Doe'', or Website ``Acme Software, Inc.''.

record_label() - returns a label for a particular record

Usage: $db->record_label($table,$record,$rdata)

Same as title(), above, but returns only the quoted part of the title.

Reporting Data Values

show_data() - display individual database column values as HTML

The show_data() methods format database column values as HTML according to their datatype. Web-enabled datatypes (emails, URLs) are converted to links, machine-readable datatypes (timestamps, keys) are converted to human-readable form, and other datatypes are preprocessed for safe display in a browser.

Usage:

$db->show_data($table,$column,$data,$id,$datatype);
Standard version. Does authorization checks to validate that the user has permission to read the given data, and returns an error message if not. $id is only needed for file datatypes, and $datatype is only needed if overriding the datatype in the DBmap.)

$db->show_data_noauth($table,$column,$data,$id,$datatype);
This version skips the authorization checks. $id is only needed for file datatypes, and $datatype is only needed if overriding the datatype in the DBmap.)

$db->show_data_nomap($datatype,$data);

This version displays an arbitrary datum as a certain datatype, without any assumption that the data comes from the database.

Parameters:

$table
The table the datum comes from.

$column

The column the datum comes from.

$data
The datum value.

$id
The record ID of the record the datum comes from. This is needed for file (and bigfile) datatypes, which link back to the file itself.

$datatype
The datatype, if different from the default in the DBmap.

Reporting Records

report() - display record(s) from the database as HTML

report() displays one or more records from the database in a formatted table, automatically selecting which columns/values to display, formatting each value, hyperlinking active values appropriately, and providing options to edit, copy, delete, or view more detailed information. It returns the report as a string of formatted HTML.

Usage: $db->report($options);

$options is a hash reference containing any of the following parameters that are relevant to the report:

table

reference to a list of tables to report on

data
reference to a list of datahashes, or to a single matchhash

query
a query hash (see Queries, below)

sort
a column name to sort on, or a reference to a list of column names.

columns
Select which columns should be included in the report. This is normally determined automatically, but can be overriden manually if desired. Values of ``brief'', ``full'', or [ RE of column types to display ] are accepted.

max

the maximum number of records to display (default all).

start
starting record (default 0)

DB ops

Any record can be linked to a variety of database operations (DB ops) that the viewer can perform on the data. The operations that will be linked to are determined by the settings of the following DB ops flags. If the flag is true, the link to the operation will be given. If the flag is ``1'' the link will go to the default URL for handling DB ops (defined in $config{db_ops}), otherwise the flag is presumed to be a partial URL to link to for that operation. The following parameters will be appended to the URL:

    tab=TABLENAME&id=RECORD_ID

The DB ops that the system can perform are:

link, view
These link from a brief summary of the record to a full view of the record data.

edit

Links to a form to edit the record.

copy
Links to a form to insert a new record with the same data as the current record.

delete
Moves the record to the trash bin.

new
Links to a form to insert a new record (the form is initialized blank).

all_edit
Equivalent to edit=>1, copy=>1, delete=>1.

Queries

Some reports may include too many records for convenient display on a single web page. In this case, we break the report into pages. By default we show 20 records per page, but this value can be configured in exsite.conf. An index of the report pages is also shown at the bottom of the report.

A ``query'' (probably a misnomer) is a QUERY_STRING parameter that lets us keep track of where we are in a multi-page report, since we may have to re-generate the report several times as we page through it, and we need some context information to do that. The query also keeps track of which columns we are sorting on, so that we don't lose our position in the report if we resort somewhere in the middle.

All of this is handled automatically by ExSite if you use the report() method, in which case you should never have to interact with the query methods.

The query parameter is just an encoded hash containing the following values:

table
The table the records came from.

match
A reference to a match hash to select records from the above table.

sort
A reference to a list of column names to sort on.

start
The first record to display in the current view.

num

The number of records to display in the current view.

The query parameters are processed with the following calls:

$db->encode_query($query_hashref);
Converts a query hash to an string that can be embedded into a query string.

$db->decode_query($encoded_query_string);

Converts a the encoded string back to a hash. (Returns a reference to this hash.)

$db->do_query($query_hashref);
Returns an array of datahashes corresponding to the ``slice'' of data specified by the query hashref.

$db->sizeof_query();

Returns the total number of records matching the last query processed by do_query() (not just the slice that was returned).

report_row() - display a single row in verbose format

This displays all viewable columns of a selected record. The results are formatted in a two-column table, with labels (user-friendly column names) on the left, and values on the right. The report is returned as a string of HTML.

Usage: $db->report_row($table,$row,%opt);

$table
The table the record (row) is taken from.

$row
The record ID or a reference to the record datahash.

%opt

Options to be passed to report(), if desired. For example (edit=>1).

report_db() - display top-level database view

This lists all viewable tables in the database, with options to view the contents of the table, search the table, or add a new record to the table.

Usage: $db->report_db($options);

$options is a hash containing the following parameters:

class
select sub-category of tables to show, if the DBmap defines table classes.

count

count

display record counts for each table, if true.

title
include title/intro text.

[op]

optional db_ops settings, if you want to modify the allowed operations.

report_relational() - displays records and their relations

This method generates contextual views of the database. A viewer may be looking at a specific record, a table, or nothing.

In the case of nothing, report_db() is called.

In the case of a table, report() is called.

In the case of a specific record, we call report_row() on that record, and then recursively generate summary reports for every child record of this record. Each of these child records can in turn be visited to generate new relational reports from that context. Parent records can be visited by following the links in the the record's data. This is the usual method for navigating/browsing the database.

b $db->report_relational($options);

HMTL for the combined report is returned in a string.

$options is a hash containing the following parameters, all of which are optional:

tab,table
the primary table to report on. If none specified, all tables are listed.

id
the key of a particular record in the above table. Without this parameter, all members of the table are listed for selection. With this parameter, the associated members of the subtables are listed, with options to edit, delete, or add to the table.

stab
the subtable(s) to manage. By default, all known subtables are shown. This parameter can be used to restrict which subtables are shown.

ptab
pid

(Both of these must be specified at once.) These parameters select which members of the table are listed. By default, all members of the table are shown.

other...
All remaining options are passed to the other report() methods.

report_links() - show DB ops links in a report

This is an internal method used to generate the DB ops links in the above reports. Links will use graphic buttons, if those are found where they are expected, but will default to text links if not. report_links() can be invoked for a table or record to generate all of the appropriate links for that entity.

Usage: $db->report_links($table,$id,%options)

$table

The table that is being operated on.

$id
The record ID that is being operated on. (If none, then it is assumed we are operating on the table as a whole.)

%options
Flags for the DB ops that should be included. The DB ops that this method supports are:

link, view
These link from a brief summary of the record to a full view of the record data.

edit
Links to a form to edit the record.

copy

Links to a form to insert a new record with the same data as the current record.

delete
Moves the record to the trash bin.

new
Links to a form to insert a new record (the form is initialized blank).

list
Links to a summary report of all records in a table.

search
Links to a form to search for matching records in a table.

all_edit

Equivalent to edit=>1, copy=>1, delete=>1.

If the flag is set to 1, the option will be linked to the default URL defined in $config{db_ops}. Otherwise, you can set the flag to your own URL to perform that operation. The following parameters will be appended to your URL:

    tab=TABLENAME&id=RECORD_ID

link() - generate a single db ops link

Usage: $db->link($op,$arg,$script)

$op is the db operation (eg. ``view'', ``edit'', ``search'', etc.). $arg is an explicit query string to include, or a hash ref to a set of parameters to convert to a query string.

$script is a CGI program name to use in place of the default db_ops CGI program.

link() returns links in one of two formats, depending on the value of $config{report}{linkmode}. If the linkmode is ``exsite'', the current URL is edited to include the new parameters, on the assumption that an appropriate plug-in will act on those parameters. Otherwise if the linkmode is ``cgi'', the link URL will be taken from $config{db_ops}.

helplink() - display help tops for DB fields

This method generates help links for DB fields in forms. Help data is found in dbmap/help/$table/$column.

Usage: $db->helplink($table,$column,$anchor,$css)

$table and $column refer to the DB column that help is needed for. $anchor is the anchor text for the help link.

$css is a boolean flag that makes us use CSS help popups (you must be using the ExSite stylesheet or equivalent); otherwise we use JavaScript popups. It will generate a popup window if help is available. There is no output if the help files are not found.

Bulk Data Imports and Exports

Bulk imports/exports are typically done to/from some spreadsheet format. Exports can be done to tab-delimted text or Excel formats. Imports are done from tab-delimited text files.

export()

Usage: $db->export($options)

$options is a hash ref containing:

table
the table to export.

column
ref to a list of columns to export.

format
``text'' or ``excel'' (default is text)

outfile
output filename or ``-'' for stdout (default is stdout)

header

set this to ``label'' to get friendly column headings in the export. Otherwise the column headings default to the database column name.

heading
hash of headings for columns (default is taken from dbmap)

data
ref to a list of datahashes of preselected records.

match
ref to a match hash to select records.

owner
export all records owned by this UID

group

export all records owned by this GID

readable_fkey
If true, convert foreign keys to a readable form (ie. replace numeric foreign keys with a descriptive string indicating the foreign record)

By default, all records in a table are exported, unless one of data, match, owner, or group is given.

import()

Usage: $db->import($options);

$options is a hash ref containing:

table
the table to import into.

delim

the field delimiter (TAB by default).

file
input file name.

method
insert or update. Update can be used if the imported file is from a previous export, and includes appropriate primary keys indicating which records are being updated. Insert should be used for new data.

Topics