CVSTrac Reports

A report is simply a SQLite database query with special functions and output formatting appropriate for web presentation.

The basic idea is that a SQL query is run and each resulting row is formatted as a separate row in an HTML table. Table headers are determined dynamically from column names and, in a few cases, special columns get their own interpretations (to specify a row background color, or identify a column as containing linkable ticket numbers).

A report query can be written against most tables in the CVSTrac database and must contain a single SELECT statement. Subqueries are not allowed.

Viewing Reports

The Reports link brings up a list of all available reports. Each report can be viewed simply by selecting the title. The raw (unformatted) data can be seen (and downloaded) from within a report view using the Raw Data link.

The SQL used to generate the report can also be viewed at any time. This is useful if you wanted to write custom queries outside of CVSTrac against the SQLite database. Keep in mind, however, that some functions are only available inside CVSTrac. And, of course, you'll lose the meaning of some of the "special" columns.

Reports may also be embedded into wiki pages using the {report: rn} syntax, where rn is the report number as listed in the report URL (not the same number in the report list).

Creating a report

You can create a new report directly (following the appropriate link) or by making a copy of an existing report (particularly one that does almost what you need). In order to create or edit a report, a user must have the query permission set.

Each report should have a unique and self-explanatory title.

Tables

Most tables in the CVSTrac database are available for use in reports. However, keep in mind that the usual access restrictions are applied to all queries. In other words, users without checkout permissions will not be able to see the contents of the chng table.

Special Column Names

The wiki(), tkt() and chng() functions also give some control over column formatting.

Available SQL Functions

See the SQLite documentation for the standard functions.

  SELECT
    cn as 'Change #',
    ldate(date) as 'Time',
    message as 'Comment'
  FROM chng
  WHERE date>=now()-2592000 AND user=aux('User',user())
  ORDER BY date DESC

which allows a user to enter a userid and get back a list of check-ins made within the last 30 days.

  SELECT
    cn as 'Change #',
    ldate(date) as 'Time',
    message as 'Comment'
  FROM chng
  WHERE date>=now()-2592000
    AND user=option('User','SELECT id FROM user')
  ORDER BY date DESC

0 No sign of the word was found in the text
6 The word was found but not on a word boundry
8 The word was found with different capitalization
10 The word was found in the text exactly as given

  SELECT
    tkt(tn) AS 'Tkt',
    owner AS 'Owner',
  FROM ticket

  SELECT path(isdir, dir, base) AS 'filename' FROM file

  SELECT dirname('/path/to/dir/') => '/path/to/'
  SELECT dirname('/path/to/dir/file.c') => '/path/to/dir/'

  SELECT basename('/path/to/dir/') => 'dir'
  SELECT basename('/path/to/dir/file.c') => 'file.c'