SIGHTING_CONTROLS

Each row records a step in the process of searching through the SokweDB tables to discover the places an individual was sighted. The content of the table controls what tables the build_sightings() function examines to determine whether an individual was sighted, and the order in which the tables are examined.

Each row of SIGHTING_CONTROLS has a Step and Source value. The Step value determines the order in which the SokweDB tables are searched. The Source value determines which table(s) are searched. The SIGHTING_CONTROLS row’s other columns control which rows are examined when searching.

For each individual, for each day, the build_sightings() function examines each SIGHTING_CONTROLS’ row, ordered by Step value, lowest number first.

Caution

The rows in SIGHTING_CONTROLS are referenced, by Step value, in the SIGHTINGS table. This connects the sighting of an individual, in SIGHTINGS, to the tables that record the individual’s sighting.

If SIGHTING_CONTROLS content is changed, then SIGHTINGS rows will no longer be connected to accurate information. There are no guard rails in place to prevent this from happening.

The recommended practice is to change SIGHTING_CONTROLS only when the SIGHTINGS table is empty. Alternately, begin a transaction, make the desired changes to SIGHTING_CONTROLS, use build_sightings() to rebuild the entire SIGHTINGS table, and commit the transaction.

The SIGHTING_CONTROLS table contains many columns that correspond with the columns of SokweDB’s tables. WType corresponds with the database’s WATCHES.Type column, Behavior corresponds with the database’s EVENTS.Behavior column, StartSource corresponds with the database’s COMM_MEMBS.StartSource column, etc. When one of these SIGHTING_CONTROLS columns contains a value, the corresponding in-database column must contain that same value in order for the individual to be recorded in SIGHTINGS. When WType is AGG, the value of the EVENTS.Behavior column must also be AGG. And so forth. When one of these SIGHTING_CONTROLS rows is NULL, the corresponding in-database column may contain any value whatsoever. So, NULL values in one of these SIGHTING_CONTROLS columns mean that the value of the in-database column does not effect the creation of a SIGHTINGS row.

Which tables and which rows of these tables are examined to determine whether an individual is sighted is therefore under the control of the person(s) responsible for maintaining SIGHTING_CONTROLS. Tables to examine can be added, omitted, or examined multiple times using different criteria each time.

The categories of database content available for searching is controlled by the Source column. The available values are:

COMM_MEMBS

The COMM_MEMBS table is searched.

The SIGHTING_CONTROLS columns used to control the search are:

• StartSource

• EndSource

NON_BREC_SIGHTING_SOURCES

The NON_BREC_SIGHTING_SOURCES table is searched.

The SIGHTING_CONTROLS column used to control the search is:

• SightingRecord

ROLES

The WATCHES, EVENTS, and ROLES tables are searched.

The SIGHTING_CONTROLS columns used to control the search are:

• WType

• Behavior

• Certainty

• Role

SWELLING_SOURCES

The SWELLING_SOURCES table is searched.

The SIGHTING_CONTROLS column used to control the search is:

• SightingRecord

A minimum, sensible, SIGHTING_CONTROLS table might look like (all omitted columns are NULL):

 step |          source           | certainty
------+---------------------------+-----------
    1 | ROLES                     | 1
    2 | SWELLING_SOURCES          |
    3 | NON_BREC_SIGHTING_SOURCES |
    4 | COMM_MEMBS                |

The above SIGHTING_CONTROLS content looks for individuals everywhere possible, but excludes all recorded events that are not certain. It excludes all EVENTS rows but those with an EVENTS.Certainty value of 1.

Notice that COMM_MEMBS is last. This makes sense because searching is done in Step order. If COMM_MEMBS was first it would almost always be where the presence of every individual is discovered, because COMM_MEMBS reports individuals present over vast swaths of time. This would result in SIGHTINGS.Step values that reveal very little detail in the way of when individuals were sighted.

On the other hand, putting COMM_MEMBS first does place the COMM_MEMBS.CommID value in the SIGHTINGS.CommID column. If COMM_MEMBS.CommID is more accurate than the community data stored elsewhere in the system this could be of benefit.

A SIGHTING_CONTROLS table that provides detail on exactly which behavior the sighted individual was involved with is (all omitted columns are NULL):

 step |          source           | behavior | certainty
------+---------------------------+----------+-----------
    1 | ROLES                     | PG       | 1
    2 | ROLES                     | AGG      | 1
    3 | ROLES                     | MATE     | 1
    4 | ROLES                     | GROOM    | 1
    5 | ROLES                     | GSCAN    | 1
    6 | ROLES                     | AGSCAN   | 1
    7 | ROLES                     | FOOD     | 1
    8 | ROLES                     | UTM      | 1
    9 | ROLES                     | PAPER    | 1
   10 | ROLES                     | ARR      | 1
   11 | ROLES                     | AARR     | 1
   12 | ROLES                     | COL      | 1
   13 | SWELLING_SOURCES          |          |
   14 | NON_BREC_SIGHTING_SOURCES |          |
   15 | COMM_MEMBS                |          |

Again, the Step value is significant. Lower numbers have a higher priority when it comes to what is recorded in SIGHTINGS.Step. Given the example values above, if an individual is observed both pantgrunting and mating the reported SIGHTINGS.Step value will be that of the pantgrunt.

Even more detail is possible. For example, a Source of ROLES, with a Behavior of AGG, and a Certainty of 1, and, finally, a Role of Actor singles out the aggressor in aggression events.

Step

A unique positive integer which serves both to identify the row and to prioritize the searching of a selected portion of SokweDB. This column may not be NULL.

Source

A code designating which broad category of database content is searched.

One of the values:

• COMM_MEMBS – The COMM_MEMBS table is searched.

• NON_BREC_SIGHTING_SOURCES – The NON_BREC_SIGHTING_SOURCES table is searched.

• ROLES – The WATCHES, EVENTS, and ROLES tables are searched.

• SWELLING_SOURCES – The SWELLING_SOURCES table is searched.

This column may not be NULL.

SightingRecord

A SIGHTING_RECORDS.Code value that must match for the search to succeed. Depending on the Source, it is either the codes found in the SWELLING_SOURCES.Source column or the NON_BREC_SIGHTING_SOURCES.Source column that must match.

When this column is NULL then every SIGHTING_RECORDS.Code value matches.

WType (Watches TYPE)

A WATCHES.Type value that must match for the search to succeed. When this column is NULL then every WATCHES.Type value matches.

Behavior

A EVENTS.Behavior value that must match for the search to succeed. When this column is NULL then every EVENTS.Behavior value matches.

The following behavior values are not associated with chimpanzees and may not be used in this column:

• BREC – B-Record note

• OS – Other species

Certainty

A EVENTS.Certainty value that must match for the search to succeed. When this column is NULL then every EVENTS.Certainty value matches.

Role

A ROLES.Role value that must match for the search to succeed. When this column is NULL then every ROLES.Role value matches.

StartSource

A COMM_MEMBS.StartSource value that must match for the search to succeed. When this column is NULL then every COMM_MEMBS.StartSource value matches.

EndSource

A COMM_MEMBS.EndSource value that must match for the search to succeed. When this column is NULL then every COMM_MEMBS.EndSource value matches.

Notes

Free form textual notes on the SIGHTINGS_CONTROL row. This column may be empty text. It need not contain characters, but it may not contain only whitespace characters. This column may not be NULL.

Page last generated: 2026-08-05 22:49:54 UTC