Contains one row for every row in INTERACT_DATA. Each row contains a column for the
actor and a column for the actee. The actor and actee are
retrieved from the PARTS table, when there
is no related parts row the actor or actee is NULL.
This view is somewhat useful for the maintenance and analysis of social interaction data.[262] It's primarily optimized for speed and so finds its best use when writing queries.
Figure 6.65. Query Defining the ACTOR_ACTEES View
SELECT interact_data.iid AS iid
, interact_data.sid AS sid
, interact_data.act AS act
, interact_data.date AS date
, interact_data.start AS start
, interact_data.stop AS stop
, interact_data.observer AS observer
, actor.partid AS actorid
, COALESCE(actor.sname, '998'::CHAR(3)) AS actor
, (SELECT actorms.grp
FROM members AS actorms
WHERE actorms.sname = actor.sname
AND actorms.date = interact_data.date) AS actor_grp
, actee.partid AS acteeid
, COALESCE(actee.sname, '998'::CHAR(3)) AS actee
, (SELECT acteems.grp
FROM members AS acteems
WHERE acteems.sname = actee.sname
AND acteems.date = interact_data.date) AS actee_grp
, interact_data.handwritten AS handwritten
, interact_data.exact_date AS exact_date
FROM interact_data
LEFT OUTER JOIN parts AS actor
ON (actor.iid = interact_data.iid AND actor.role = 'R')
LEFT OUTER JOIN parts AS actee
ON (actee.iid = interact_data.iid AND actee.role = 'E');
Table 6.30. Columns in the ACTOR_ACTEES View
| Column | From | Description |
|---|---|---|
| Iid | INTERACT_DATA.Iid | Identifier of the interaction. |
| Sid | INTERACT_DATA.Sid | Identifier of the sample, if any, during which the data was collected. |
| Act | INTERACT_DATA.Act | The kind of interaction. |
| Date | INTERACT_DATA.Date | The date of the interaction. |
| Start | INTERACT_DATA.Start | The time the interaction began. |
| Stop | INTERACT_DATA.Stop | The time the interaction ended. |
| Observer | INTERACT_DATA.Observer | The observer who recorded the interaction. |
| Actorid | PARTS.Partid | The Partid of the actor's PARTS row. |
| Actor | PARTS.Sname | The Sname of the
actor in the interaction, when there is a PARTS row for the actor. Otherwise, the
value 998. |
| Actor_Grp | MEMBERS.Grp | The Grp of the actor on the date of the interaction. |
| Acteeid | PARTS.Partid | The Partid of the actee's PARTS row. |
| Actee | PARTS.Sname | The Sname of the
actee in the interaction, when there is a PARTS row for the actee. Otherwise, the
value 998. |
| Actee_Grp | MEMBERS.Grp | The Grp of the actee on the date of the interaction. |
| Handwritten | INTERACT_DATA.Handwritten | Whether or not the interaction was recorded in handwritten records. |
| Exact_Date | INTERACT_DATA.Exact_Date | Whether this row's Date is the specific day
that the interaction occurred (TRUE) or only the
year and month of the interaction (FALSE). |
The Actor_Grp and Actee_Grp columns are computed.
Attempts to put a value into these columns raise an error if
the new value is not NULL or does not correspond to the
computed value.
Tip
Best practice is to omit computed columns from inserts and updates.
The Actor and Actee columns must not be NULL or
998 when inserting
into or updating this view.
When inserting or updating ACTOR_ACTEES the values of
the Actor_Grp and Actee_Grp columns must either be NULL or
match the group recorded for the individual for that day in
MEMBERS, if such a row exists. Inserting
and updating ACTOR_ACTEES cannot affect group
membership.
Tip
It is usually a good idea to leave the computed
columns unspecified (NULL) when maintaining social
interactions using this view.
Inserting a row into ACTOR_ACTEES inserts a row into INTERACT_DATA and two rows, one for the actor and one for the actee, into PARTS, as expected.
To insert an actor without an actee (or vice versa)
use a NULL value for the Actee (or Actor).
Tip
When entering a new social interaction it is
usually a good idea to leave Iid unspecified (or
specified as NULL). In this case Babase will compute
a new Iid and use it
appropriately in the new PARTS rows.
Likewise the Actorid and Acteeid columns are usually
best left NULL, in which case Babase will also create
appropriate values.
Updating a row in ACTOR_ACTEES updates the underlying columns in INTERACT_DATA and PARTS, as expected.
An actor or actees PARTS row can
be deleted or inserted by UPDATE of ACTOR_ACTEES. To
insert an new PARTS row supply a Sname for the actor or
actee where there was none. To delete a actor or or actee
set the Sname to NULL.
When deleting an actor or actee either the
corresponding Actorid/Acteeid value must be set to NULL,
or the corresponding Actorid/Acteeid value must be
unaltered.
Tip
The Actor and Actee cannot be switched with a update operation.[263] Delete the interaction and re-create it instead.
Deleting a row in ACTOR_ACTEES deletes the underlying row in INTERACT_DATA and the two underlying rows in PARTS, as expected.
Contains one row for every row in INTERACT_DATA. There is no difference between this view and the INTERACT_DATA table, other than the view extends the INTERACT_DATA table with additional date and time columns that transform the corresponding INTERACT_DATA columns in useful and interesting ways, and the view is sorted by Iid.
Figure 6.67. Query Defining the INTERACT View
SELECT iid AS iid
, interact_data.sid AS sid
, interact_data.act AS act
, acts.class AS class
, interact_data.date AS date
, julian(interact_data.date) AS jdate
, interact_data.start AS start
, spm(interact_data.start) AS startspm
, stop AS stop
, spm(interact_data.stop) AS stopspm
, interact_data.observer AS observer
, interact_data.handwritten AS handwritten
, interact_data.exact_date AS exact_date
FROM interact_data
JOIN acts
ON (acts.act = interact_data.act);
Table 6.31. Columns in the INTERACT View
| Column | From | Description |
|---|---|---|
| Iid | INTERACT_DATA.Iid | Identifier of the interaction. |
| Sid | INTERACT_DATA.Sid | Identifier of the point observation collection, if any, during which the data was collected. |
| Act | INTERACT_DATA.Act | The kind of interaction. |
| Date | INTERACT_DATA.Date | The date of the interaction. |
| Jdate | INTERACT_DATA.Date (computed) | The date of the interaction, in Julian date form. |
| Start | INTERACT_DATA.Start | The time the interaction began. |
| Startspm | INTERACT_DATA.Start (computed) | The time the interaction begin (Start), represented as the number of seconds past midnight. This is useful for computing and analyzing time intervals outside of the PostgreSQL environment. |
| Stop | INTERACT_DATA.Stop | The time the interaction ended. |
| Stopspm | INTERACT_DATA.Stop (computed) | The time the interaction ended (Stop), represented as the number of seconds past midnight. This is useful for computing and analyzing time intervals outside of the PostgreSQL environment. |
| Observer | INTERACT_DATA.Observer | The observer who recorded the interaction. |
| Handwritten | INTERACT_DATA.Handwritten | Whether or not the interaction was recorded in handwritten records. |
| Exact_Date | INTERACT_DATA.Exact_Date | Whether this row's Date is the specific day
that the interaction occurred
(TRUE) or only the year and month
of the interaction (FALSE). |
Warning
Any modifications to the Jdate, Startspm, or Stopspm columns are silently ignored.
Inserting a row into INTERACT inserts a row into INTERACT_DATA, as expected.
Updating a row in INTERACT updates the underlying columns in INTERACT_DATA, as expected.
Deleting a row in INTERACT deletes the underlying row in INTERACT_DATA.
Contains one row for every row in the INTERACT view. There is no difference between this view and the INTERACT view, other than that this view is sorted by Iid.
Figure 6.69. Query Defining the INTERACT_SORTED View
SELECT iid AS iid
, interact_data.sid AS sid
, interact_data.act AS act
, acts.class AS class
, interact_data.date AS date
, julian(interact_data.date) AS jdate
, interact_data.start AS start
, spm(interact_data.start) AS startspm
, interact_data.stop AS stop
, spm(interact_data.stop) AS stopspm
, interact_data.observer AS observer
, interact_data.handwritten AS handwritten
, interact_data.exact_date AS exact_date
FROM interact_data
JOIN acts
ON (acts.act = interact_data.act)
ORDER BY iid;
Table 6.32. Columns in the INTERACT_SORTED View
| Column | From | Description |
|---|---|---|
| Iid | INTERACT_DATA.Iid | Identifier of the interaction. |
| Sid | INTERACT_DATA.Sid | Identifier of the point observation collection, if any, during which the data was collected. |
| Act | INTERACT_DATA.Act | The kind of interaction. |
| Date | INTERACT_DATA.Date | The date of the interaction. |
| Jdate | INTERACT_DATA.Date (computed) | The date of the interaction, in Julian date form. |
| Start | INTERACT_DATA.Start | The time the interaction began. |
| Startspm | INTERACT_DATA.Start (computed) | The time the interaction begin (Start), represented as the number of seconds past midnight. This is useful for computing and analyzing time intervals outside of the PostgreSQL environment. |
| Stop | INTERACT_DATA.Stop | The time the interaction ended. |
| Stopspm | INTERACT_DATA.Stop (computed) | The time the interaction ended (Stop), represented as the number of seconds past midnight. This is useful for computing and analyzing time intervals outside of the PostgreSQL environment. |
| Observer | INTERACT_DATA.Observer | The observer who recorded the interaction. |
| Handwritten | INTERACT_DATA.Handwritten | Whether or not the interaction was recorded in handwritten records. |
| Exact_Date | INTERACT_DATA.Exact_Date | Whether this row's Date is the specific day
that the interaction occurred
(TRUE) or only the year and month
of the interaction (FALSE). |
The operations allowed are as described in the INTERACT view.
Contains one row for every row in MPI_DATA. Each row contains a column for the
actor and a column for the actee. The actor and actee are
retrieved from the MPI_PARTS table, when
there is no related parts row the actor or actee is NULL.
The MPIS table supplies the date of the
interaction and there are further self-joins to the MPI_DATA, MPIACTS, and MPI_PARTS tables to compute help request
circumstances and outcome.
This view is useful for the analysis of multiparty interaction data.[264].
Figure 6.71. Query Defining the MPI_EVENTS View
SELECT mpis.mpiid AS mpiid
, mpis.date AS date
, mpis.context_type AS context_type
, mpis.context AS context
, mpi_data.mpidid AS mpidid
, mpi_data.seq AS seq
, mpi_data.mpiact AS mpiact
, actor.mpipid AS actorid
, actor.sname AS actor
, actor.unksname AS unkactor
, actee.mpipid AS acteeid
, actee.sname AS actee
, actee.unksname AS unkactee
, CASE WHEN EXISTS(SELECT 1
FROM mpiacts
WHERE mpiacts.mpiact = mpi_data.mpiact
AND mpiacts.kind = 'H')
THEN
EXISTS(SELECT 1
FROM mpi_data AS request
, mpiacts
, mpi_parts AS requestor
, mpi_parts AS requestee
WHERE request.mpiid = mpi_data.mpiid
AND request.seq < mpi_data.seq
AND mpiacts.mpiact = request.mpiact
AND mpiacts.kind = 'R'
AND requestor.mpidid = request.mpidid
AND requestor.role = 'R'
AND requestor.sname = actee.sname
AND requestee.mpidid = request.mpidid
AND requestee.role = 'E'
AND requestee.sname = actor.sname)
ELSE
NULL
END AS solicited
, EXISTS(SELECT 1
FROM mpi_data AS initial,
mpiacts
WHERE initial.mpiid = mpi_data.mpiid
AND initial.seq = 1
AND mpiacts.mpiact = initial.mpiact
AND mpiacts.decided)
AS decided
, mpi_data.helped AS helped
, mpi_data.active AS active
FROM mpis
LEFT OUTER JOIN mpi_data ON (mpis.mpiid = mpi_data.mpiid)
LEFT OUTER JOIN mpi_parts AS actor
ON (actor.mpidid = mpi_data.mpidid AND actor.role = 'R')
LEFT OUTER JOIN mpi_parts AS actee
ON (actee.mpidid = mpi_data.mpidid AND actee.role = 'E');
Table 6.33. Columns in the MPI_EVENTS View
| Column | From | Description |
|---|---|---|
| Mpiid | MPIS.Mpiid | Identifier of the multiparty interaction collection. |
| Date | MPIS.Date | The date of the multiparty interaction collection. |
| Context_type | MPIS.Context_type | The context type of the multiparty interaction collection. |
| Context | MPIS.MPIS-Context | Text describing the context of the multiparty interaction collection. |
| Mpidid | MPI_DATA.Mpidid | Identifier of the dyadic interaction. |
| Seq | MPI_DATA.Seq | Number that orders the dyadic interaction in
time within the multiparty interaction collection.
The first dyadic interaction has a Seq value of
1, the second a Seq value of
2, etc. Identical Seq values
indicate interactions which occurred
simutainously. |
| Mpiact | MPI_DATA.MPIAct | The kind of dyadic interaction. |
| Actorid | MPI_PARTS.Mpipid | The Mpipid of the actor's MPI_PARTS row. |
| Actor | MPI_PARTS.Sname | The Sname of the actor in the interaction. |
| Unkactor | MPI_PARTS.Unksname | The PARTUNKS.Unksname code which denotes why the actor is unknown. |
| Acteeid | MPI_PARTS.Mpipid | The Mpipid of the actee's MPI_PARTS row. |
| Actee | MPI_PARTS.Sname | The Sname of the actee in the interaction. |
| Unkactee | MPI_PARTS.Unksname | The PARTUNKS.Unksname code which denotes why the actee is unknown. |
| Solicited | Computed | A boolean: TRUE or FALSE, or NULL.
NULL when the act is not an act of help (MPIACTS.MPIAct
is FALSE). Whether or not the help given was
solicited with a request for help -- whether MPIACTS.Kind
is R, from a
previous (having a smaller MPI_DATA.Seq)
MPI act's MPI_DATA.MPIAct value where the actor
and actee match. For more details see the Figure 6.71. |
| Decided | Computed | A boolean: TRUE or FALSE. Whether or not
the result of the MPI was decided when the event took
place -- obtained from the MPIACTS.Decided value of the MPI_DATA.MPIAct of the first (MPI_DATA.Seq =
1) interaction of the MPI. |
| Helped | MPI_DATA.Helped | Whether or not help was given in response to
the request for help. NULL when the event is not a
request for help. |
| Active | MPI_DATA.Active | Whether the help given was active or passive.
NULL when the event is not a request for help or no
help was forthcoming. |
Caution
Attempts to change the computed columns, Solicited and Decided, are silently ignored.
Tip
It is usually a good idea to leave the computed
columns, as well as the automatically assigned ID columns,
unspecified (NULL) when maintaining social interactions
using this view.
Inserting a row into MPI_EVENTs inserts a row into MPI_DATA and two rows, one for the actor and one for the actee, into MPI_PARTS, as expected. It also may insert a row into the MPIS table.
Warning
The presence or absence of a Mpiid value
determines whether or not a MPIS row
is created. When Mpiid is NULL (or the column is not
specified) a new MPIS row is created.
When a non-NULL value is supplied the given Mpiid
identifies the existing multiparty interaction
collection, a MPIS row, to which the
new dyadic interaction is added.
Warning
The value of the Date, Context_type, and MPIS-Context columns are ignored when the supplied Mpiid identifies an existing MPIS row.
Warning
The PostgreSQL nextval()
function cannot be part of an INSERT
expression which assigns a value to this view's Mpiid or
Mpidid columns.
Tip
When entering a new social interaction it is
usually a good idea to leave Mpidid unspecified (or
specified as NULL). In this case Babase will compute
a new Mpiid and use it
appropriately in the new MPI_PARTS
rows. Likewise the Actorid and Acteeid columns are
usually best left NULL, in which case Babase will also
create appropriate values.
Updating a row in MPI_EVENTS updates the underlying columns in MPIS, MPI_DATA, and MPI_PARTS as expected.
Tip
Because updates to the database occur one table at a time, and because Babase does not allow an interaction to have the same individual as both the actor and actee, it is impossible to switch the Actor and Actee with a update operation. Delete the interaction and re-create it instead.
Contains one row for every row in POINT_DATA. There is no difference between this view and the POINT_DATA table, other than the view contains additional columns that may be useful derivatives of the Ptime column.
Tip
Use this view instead of the POINT_DATA table.
Figure 6.73. Query Defining the POINTS View
SELECT pntid AS pntid
, sid AS sid
, activity AS activity
, posture AS posture
, foodcode AS foodcode
, ptime AS ptime
, spm(ptime) AS ptimespm
FROM point_data;
Table 6.34. Columns in the POINTS View
| Column | From | Description |
|---|---|---|
| Pntid | POINT_DATA.Pntid | Identifier of the point observation. |
| Sid | POINT_DATA.Sid | Identifier of the sample during which the data was collected. |
| Activity | POINT_DATA.Activity | The kind of activity the focal was engaged in. |
| Posture | POINT_DATA.Posture | The posture of the focal. |
| Foodcode | POINT_DATA.Foodcode | The food eaten, if any. |
| Ptime | POINT_DATA.Ptime | The time the observation was recorded, with a precision of 1 second. |
| Ptimespm | POINT_DATA.Ptime (computed) | The time the point observation was recorded (Ptime) represented as the number of seconds past midnight. This is useful for computing and analyzing time intervals outside of the PostgreSQL environment. |
The Ptimespm column is computed. Attempts to put a
value into the Ptimespm column raise an error if the new
value is not NULL or does not correspond to the computed
value.
Tip
Best practice is to omit computed columns from inserts and updates.
Inserting a row into POINTS inserts a row into POINT_DATA, as expected.
Updating a row in POINTS updates the underlying columns in POINT_DATA, as expected.
Deleting a row in POINTS deletes the underlying row in POINT_DATA.
Contains one row for every row in POINTS. There is no difference between this view and the POINTS view, other than this view is sorted by Sid, and within that by Ptime.
Figure 6.75. Query Defining the POINTS_SORTED View
SELECT pntid AS pntid
, sid AS sid
, activity AS activity
, posture AS posture
, foodcode AS foodcode
, ptime AS ptime
, ptimespm AS ptimespm
FROM points
ORDER BY sid, ptime;
Table 6.35. Columns in the POINTS_SORTED View
| Column | From | Description |
|---|---|---|
| Pntid | POINT_DATA.Pntid | Identifier of the point observation. |
| Sid | POINT_DATA.Sid | Identifier of the sample during which the data was collected. |
| Activity | POINT_DATA.Activity | The kind of activity the focal was engaged in. |
| Posture | POINT_DATA.Posture | The posture of the focal. |
| Foodcode | POINT_DATA.Foodcode | The food eaten, if any. |
| Ptime | POINT_DATA.Ptime | The time the observation was recorded, with a precision of 1 second. |
| Ptimespm | POINT_DATA.Ptime (computed) | The time the point observation was recorded (Ptime) represented as the number of seconds past midnight. This is useful for computing and analyzing time intervals outside of the PostgreSQL environment. |
The operations allowed are as described in the POINTS view.
Contains one row for every row in SAMPLES. This row is identical to the SAMPLES table except that it has an additional column Grp_of_focal which contains the group of the focal on the sampling date.[265]
Figure 6.77. Query Defining the SAMPLES_GOFF View
SELECT samples.sid AS sid
, samples.date AS date
, samples.stime AS stime
, samples.observer AS observer
, samples.stype AS stype
, samples.grp AS grp
, samples.sname AS sname
, samples.mins AS mins
, samples.minsis AS minsis
, samples.programid AS programid
, samples.setupid AS setupid
, samples.collection_system AS collection_system
, members.grp AS grp_of_focal
FROM members, samples
WHERE members.sname = samples.sname
AND members.date = CAST(samples.date AS DATE);
Table 6.36. Columns in the SAMPLES_GOFF View
| Column | From | Description |
|---|---|---|
| Sid | SAMPLES.Sid | Identifier of the sample. |
| Date | SAMPLES.Date | Date of sample collection. |
| STime | SAMPLES.Stime | Time of sample collection. |
| Observer | SAMPLES.Observer | Observer who collected the sample. |
| SType | SAMPLES.SType | A code indicating the nature of the focal individual and the data collection procedure used. |
| Grp | SAMPLES.Grp | The group the observation team sampled. |
| Sname | SAMPLES.Sname | Identifier of sampled individual. |
| Mins | SAMPLES.Mins | Sample duration in minutes, from start to finish. |
| Minsis | SAMPLES.Minsis | Number of minutes of sample data. |
| Programid | SAMPLES.Programid | Identifer of the software ("program", if any) used with this row's Collection_System to collect the focal sample. |
| Setupid | SAMPLES.Setupid | The configuration file (if any) used by this row's Programid to collect this sample's data. |
| Collection_System | SAMPLES.Collection_System | The device or hardware configuration used to collect the sample. |
| Grp_of_focal | MEMBERS.Grp | The group of the sampled individual. |
[262] Babase should contain one and exactly one actor and one and exactly one actee for every interaction. The ACTOR_ACTEES view does left outer joins of the INTERACT_DATA table with the PARTS table so that invalid data can still be maintained in the event the stated relationships do not exist. To ignore invalid data, e.g. for purposes of analysis, write a query that does regular joins instead of the left outer joins used by the view.
[263] This restriction is leftover from implementation limitations in older versions of PostgreSQL and could now be removed if so desired.
[264] Babase should contain one and exactly one actor and one and exactly one actee for every interaction. The MPI_EVENTS view does left outer joins of the MPI_DATA table with the MPI_PARTS table so that invalid data can be observed in the event the stated relationships do not exist. To ignore invalid data, e.g. for purposes of analysis, write a query that does regular joins instead of the left outer joins used by the view.
[265] This is expected to be the group that is sampled, but you never know.