Database Statistics Page

Purpose

This page offers the application admnistrator a way to gain some insight into the size and complexity of the database, and most importantly into its health and internal coherence. The role of this page is to provide information only, though. Not to provide the tools to fix any problem that might be detected.

This page is available only to users with administrator privileges.

Getting There...

From the Main Application Menu (or indeed almost any other Project Hirtius page), click on your name in the upper right corner of the header. Only for administrator accounts is the user ID a link.

Modes

The Database Statistics Page supports 10 different modes of operation:

Administration request selection (default)
Get statistics
Country breakdown
Location breakdown
Slides shot per year
Run sanity check
List empty categories
List orphaned objects
Identify weakly linked objects
Identify weakly documented objects
Find gaps in ID sequences

The purpose of each one will be detailed below.

Administration request selection

This mode is the default entry point when you first reach the Database Statistics Page.

In addition to providing a drop-down control to select the request to be executed, this page also provides high-level statistical information about the application configuration and the database, such as:

the name of the database that the application connects to and the server that hosts it
the database schema version
the date and time of the last modification that occurred in any database field
the tag level length (controls the breadth and depth of the INXtree)
whether debugging is currently active in the application, and if so which categories of debugging messages have been enabled

Get statistics

For each table in the database, and for some relevant subsets of the data, provides a record count and some statistics where applicable. Use this mode to find out, for instance:

How many digital vs analog Slide records you have
How many of your Event records are auto-events
How many Events are member of Sequences
How many male and female Individuals
The average number of INXlinks (= links to categories) from each fully-fledged object type
What's the total size used in the database by attachments
What's the largest attachment and its size
How many of the currently registered Weblinks are dead
etc...

Country breakdown

For each relevant object types (Slides and Events for now), gives you a record count per country, heaviest country first.

Location breakdown

For each relevant object types (Slides and Events for now), gives you a record count per location, heaviest location first (location, in this case, being location + country).

Slides shot per year

Sums the number of slides shot per calender year (based on the value of their 'shot_date' field). Only those years during which at least one slide has been shot will be reported in the resulting table.

Run sanity check

The sanity check feature will perform a series of tests against your 'hirtius' database to verify its internal coherence. Any potential problem it discovers will be listed (and displayed in bold red font), but the task of fixing them will be left to the local administrator (using the web-based application front-end whenever possible, or direct SQL queries against the back-end database otherwise).

Here is a short description of all the tests that will be performed:

Foreign key referential integrity: all references to foreign keys will be checked for existence. Where zero values are allowed by the application, an exception to the test will be made. Three different situations will be taken into account:
- Single field (e.g. the birth event in an Individual record has to point to an existing Event record).
- Double field (e.g. object ID and object type fields are both required to link an attachment record to its owner object, and that object ID must exist).
- INXtree structure: making sure that every inode has a father.
Out-of-bounds values: this test only applies to in-tray slot addresses for now.
Invalid object type or document type values: the contents of each of these fields is compared with a list of legal values.
Dates set in the future: only those fields where future dates are not considered to make sense will be checked.
Empty columns or NULL values: only specific columns will be checked.
Duplicate record detection in the following tables:
- see_also: both identical and mirror duplicates can be detected when present. Identical duplicates should not exist any more once on DB schema version 38 or above. Mirror duplicates can no longer be created as the code has been modified to prevent it.

At the bottom of the page, the total number of problems that have been identified will be reported (on a green background if no problem was found, on a yellow background otherwise).

Running the sanity check will generate a new entry in the Event Log, with a severity of 'notice' if no problem was identified, of 'critical' otherwise.

Use cases: When is it recommended that the Hirtius administrator run the sanity check?

Every once in a while: how often it should be run depends on the activity in the database (specially in the case of deletions).
After having performed an application upgrade that included a database schema upgrade.
Each time a transaction ends in error and the subsequent roll-back fails. This should never occur under normal circumstances, but if it does, an error message will be displayed asking for the sanity check to be run.
The first few times you run 3rd-party scripts that perform updates in the 'hirtius' database.
Any time you may have had the need to perform modifications in the database by executing direct SQL queries against it.

List empty categories

Lists those categories in the INXtree that are leaf nodes and don't have any object attached.

List orphaned objects

List all fully-fledged objects that are not attached to any category. The output is organised per object type.

In the case of Tiddlers, those records that are tagged 'UI' are ignored (their scope is limited to the management of the TiddlyWiki interface -- they are not considered an integral part of the Project).

Identify weakly linked objects

Not implemented yet.

Identify weakly documented objects

Not implemented yet.

Find gaps in ID sequences

Applies only to Slide records, as this is the only case where the ID will be assigned manually (slide_id field). Identifies any gap in the slide_id sequence. This might be OK if you create your Slide records out of order, but if not, it might indicate either a missing Slide or a mistake in the numbering.

Running the gap analysis will generate a new entry in the Event Log, with a severity of 'notice' if no problem was identified, of 'critical' otherwise.

Controls

In request selection mode

Administrative request drop-down

Select the action to perform in the list:

Get statistics
Country breakdown
Location breakdown
Run sanity check
List empty categories
List orphaned objects
Identify weakly linked objects
Identify weakly documented objects
Find gaps in ID sequences

Submit button

Submits your choice and reaches the next screen (the query result page).

Reset button

Resets all fields on the page to their default value. Mostly useless at this stage.

Related pages menu

At the bottom of the page, between the Administrative request drop-down and the page footer, a small secondary menu is located that will give you access to pages related to the task at hand or administration topics in general, pages that are not directly reachable through the header toolbar.

Here are the menu entries in question:

Event Log: opens the Event Log Viewer (this is a view-only page, even for application administrators).
Country Codes: opens the Country Codes Management Page (this is a view-only page, even for application administrators).

In request result mode

The control below is provided for every request result. Depending on the actual request, further links may be provided as part of the result set.

Return to the main admin page link: Located at the bottom of the page, just above the footer. Click on this link to return to the Database Statistics Page page in request selection mode.