CamCOPS
CamCOPS – Table structure
Back to Documentation
www.camcops.org/documentation/tables.html

Overview of the logical system used by CamCOPS for data storage

The basics

Audit trail and preserve-and-delete function

The thought process behind the design runs as follows.

Transactional upload

The upload process is transactional: it either succeeds as a whole, or fails as a whole. (This is necessary because there may be arbitrary relationships between tables on the tablet, e.g. between the main table of a task and its sub-tables.)

Date/time storage

Databases like MySQL discard fractions of a second, and CamCOPS needs to store millisecond-accuracy timing information. Moreover, it is often helpful to work in local time zones. For consistency, therefore, all date and date/time fields are stored as TEXT fields in ISO 8601 format, specifically YYYY-MM-DDTHH:mm:ss.SSS+ZZ:ZZ. An example is 2013-04-22T14:35:07.381+01:00 – this means 22 April 2013 at 7.381 seconds after 2:35pm in a time zone 1 hour ahead of Coordinated Universal Time (UTC, GMT), such as British Summer Time (+01:00). (There are a few fields that are an exception to this rule and use DATETIME format, but these are only used by the webview and are not accessible to users.)

Tables in the CamCOPS database

Tables only visible to the server

The following tables are only used by the server. For details, see the source code or the detailed table structure.

_dirty_tables
_hl7_message_log
_hl7_run_log
_security_account_lockouts
_security_audit
_security_devices
_security_login_failures
_security_users
_security_webviewer_sessions
_server_storedvars
_special_notes

Fields added by the server to all tables uploaded by tablet devices

Server-side fields have an underscore prefix.

Field name Data type Nullable? Comment
_pk INTEGER NO (SERVER) Primary key (on the server)
_device VARCHAR(255) NO (SERVER) ID of the source tablet device
_era VARCHAR(32) NO (SERVER) 'NOW', or the date/time this row was preserved and removed from the source device (UTC ISO 8601)
_current BOOLEAN NO (SERVER) Is the row current (1) or not (0)?
_when_added_exact VARCHAR(32) YES (SERVER) Date/time this row was added (ISO 8601)
_when_added_batch_utc DATETIME YES (SERVER) Date/time of the upload batch that added this row (DATETIME in UTC)
_adding_user VARCHAR(255) YES (SERVER) User that added this row
_when_removed_exact VARCHAR(32) YES (SERVER) Date/time this row was removed, i.e. made not current (ISO 8601)
_when_removed_batch_utc DATETIME YES (SERVER) Date/time of the upload batch that removed this row (DATETIME in UTC)
_removing_user VARCHAR(255) YES (SERVER) User that removed this row
_preserving_user VARCHAR(255) YES (SERVER) User that preserved this row
_forcibly_preserved BOOLEAN YES (SERVER) Forcibly preserved by superuser (rather than normally preserved by tablet)?
_predecessor_pk INTEGER YES (SERVER) PK of predecessor record, prior to modification
_successor_pk INTEGER YES (SERVER) PK of successor record (after modification) or NULL (after deletion)
_manually_erased BOOLEAN YES (SERVER) Record manually erased (content destroyed)?
_manually_erased_at VARCHAR(32) YES (SERVER) Date/time of manual erasure (ISO 8601)
_manually_erasing_user VARCHAR(255) YES (SERVER) User that erased this row manually
_camcops_version REAL YES (SERVER) CamCOPS version number of the uploading device
_addition_pending BOOLEAN NO (SERVER) Addition pending?
_removal_pending BOOLEAN NO (SERVER) Removal pending?

Fields added by the client (tablet) to all tables uploaded by tablet devices

Field name Data type Nullable? Comment
_move_off_tablet BOOLEAN YES (SERVER/TABLET) Record-specific preservation pending?
when_last_modified TEXT / VARCHAR(32) YES (STANDARD) Date/time this row was last modified on the source tablet device (ISO 8601)

Core tables present on the tablet devices

patient

In addition to the standard fields:

Field name Data type Nullable? Comment
id INTEGER NO Primary key (patient ID) on the source tablet device
forename TEXT / VARCHAR(255) NO Forename
surname TEXT / VARCHAR(255) NO Surname
dob TEXT / VARCHAR(32) NO Date of birth (YYYY-MM-DD)
sex VARCHAR(1) NO Sex (M, F, X)
idnum1 BIGINT UNSIGNED YES First ID number (meaning depends on device's storedvars idDescription1, idShortDescription1)
idnum2 BIGINT UNSIGNED YES Second ID number (meaning depends on device's storedvars idDescription2, idShortDescription2)
idnum3 BIGINT UNSIGNED YES Third ID number (meaning depends on device's storedvars idDescription3, idShortDescription3)
idnum4 BIGINT UNSIGNED YES Fourth ID number (meaning depends on device's storedvars idDescription4, idShortDescription4)
idnum5 BIGINT UNSIGNED YES Fifth ID number (meaning depends on device's storedvars idDescription5, idShortDescription5)
idnum6 BIGINT UNSIGNED YES Sixth ID number (meaning depends on device's storedvars idDescription6, idShortDescription6)
idnum7 BIGINT UNSIGNED YES Seventh ID number (meaning depends on device's storedvars idDescription7, idShortDescription7)
idnum8 BIGINT UNSIGNED YES Eighth ID number (meaning depends on device's storedvars idDescription8, idShortDescription8)
address TEXT YES Address
gp TEXT YES General practitioner (GP)
other TEXT YES Other details

storedvars

This table stores global variables, such as the server name and the font size preferences.

In addition to the standard fields:

Field name Data type Nullable? Comment
id INTEGER NO Arbitrary numerical primary key on the source tablet device
name VARCHAR(255) NO Variable name (effectively the actual primary key on the source tablet device)
type VARCHAR(255) NO Variable type ('integer', 'real', 'text')
valueInteger INTEGER YES Value of an integer variable
valueText TEXT YES Value of a text variable
valueReal REAL YES Value of a real (floating-point) variable

blobs

On the client devices, BLOBs (binary large objects) are stored in the device’s filesystem, not in the database. On the server, BLOBs are stored in the database.

In addition to the standard fields:

Field name Data type Nullable? Comment
id INTEGER NO BLOB (binary large object) primary key on the source tablet device
tablename TEXT NO Name of the table referring to this BLOB
tablepk INTEGER NO Primary key (id field) of the row referring to this BLOB
fieldname TEXT NO Field name of the field referring to this BLOB by ID
filename TEXT YES Filename of the BLOB on the source tablet device
theblob LONGBLOB YES The BLOB itself, a binary object containing arbitrary information (such as a picture)

Task tables

In addition to the standard fields, all task tables include these fields:

Field name Data type Nullable? Comment
id INTEGER NO (TASK) Primary key (task ID) on the tablet device
patient_id INTEGER NO (TASK) Foreign key to patient.id for this device
when_created TEXT NO (TASK) Date/time this task instance was created
when_firstexit TEXT YES (TASK) Date/time of the first exit from this task
firstexit_is_finish BOOLEAN YES (TASK) Was the first exit from the task because it was finished (1)?
firstexit_is_abort BOOLEAN YES (TASK) Was the first exit from the task because it was aborted (1)?

Ancillary tables

In addition to the standard fields, all task tables include these fields:

Field name Data type Nullable? Comment
id INTEGER NO (ANCILLARY) Primary key on the tablet device

Example of a task table

Let’s extract key information about the phq9 table. We can run this SQL on the server’s database. First, we log in:

mysql -u root -p
use camcops; /* database name */

Then the main SQL:

SELECT c.table_name, c.column_name, c.data_type, c.is_nullable, c.column_comment
FROM information_schema.columns c
    INNER JOIN information_schema.tables t
    ON c.table_schema = t.table_schema
    AND c.table_name = t.table_name
WHERE t.table_type='BASE TABLE'
AND c.table_schema='camcops' /* database name */
AND c.table_name='phq9';

That gives:

+------------+-------------------------+-----------+-------------+--------------------------------------------------------------------------------------------------+
| table_name | column_name             | data_type | is_nullable | column_comment                                                                                   |
+------------+-------------------------+-----------+-------------+--------------------------------------------------------------------------------------------------+
| phq9       | _pk                     | int       | NO          | (SERVER) Primary key (on the server)                                                             |
| phq9       | _device                 | varchar   | NO          | (SERVER) ID of the source tablet device                                                          |
| phq9       | _era                    | varchar   | NO          | (SERVER) 'NOW', or when this row was preserved and removed from the source device (UTC ISO 8601) |
| phq9       | _current                | tinyint   | NO          | (SERVER) Is the row current (1) or not (0)?                                                      |
| phq9       | _when_added_exact       | varchar   | YES         | (SERVER) Date/time this row was added (ISO 8601)                                                 |
| phq9       | _when_added_batch_utc   | datetime  | YES         | (SERVER) Date/time of the upload batch that added this row (DATETIME in UTC)                     |
| phq9       | _adding_user            | varchar   | YES         | (SERVER) User that added this row                                                                |
| phq9       | _when_removed_exact     | varchar   | YES         | (SERVER) Date/time this row was removed, i.e. made not current (ISO 8601)                        |
| phq9       | _when_removed_batch_utc | datetime  | YES         | (SERVER) Date/time of the upload batch that removed this row (DATETIME in UTC)                   |
| phq9       | _removing_user          | varchar   | YES         | (SERVER) User that removed this row                                                              |
| phq9       | _preserving_user        | varchar   | YES         | (SERVER) User that preserved this row                                                            |
| phq9       | _predecessor_pk         | int       | YES         | (SERVER) PK of predecessor record, prior to modification                                         |
| phq9       | _successor_pk           | int       | YES         | (SERVER) PK of successor record  (after modification) or NULL (after deletion)                   |
| phq9       | _camcops_version        | double    | YES         | (SERVER) CamCOPS version number of the uploading device                                          |
| phq9       | _addition_pending       | tinyint   | NO          | (SERVER) Addition pending?                                                                       |
| phq9       | _removal_pending        | tinyint   | NO          | (SERVER) Removal pending?                                                                        |
| phq9       | _move_off_tablet        | tinyint   | YES         | (SERVER/TABLET) Record-specific preservation pending?                                            |
| phq9       | when_last_modified      | varchar   | YES         | (STANDARD) Date/time this row was last modified on the source tablet device (ISO 8601)           |
| phq9       | id                      | int       | NO          | (TASK) Primary key (task ID) on the tablet device                                                |
| phq9       | patient_id              | int       | NO          | (TASK) Foreign key to patient.id for this device                                                 |
| phq9       | when_created            | varchar   | NO          | (TASK) Date/time this task instance was created (ISO 8601)                                       |
| phq9       | when_firstexit          | varchar   | YES         | (TASK) Date/time of the first exit from this task (ISO 8601)                                     |
| phq9       | firstexit_is_finish     | tinyint   | YES         | (TASK) Was the first exit from the task because it was finished (1)?                             |
| phq9       | firstexit_is_abort      | tinyint   | YES         | (TASK) Was the first exit from this task because it was aborted (1)?                             |
| phq9       | q1                      | int       | YES         |                                                                                                  |
| phq9       | q2                      | int       | YES         |                                                                                                  |
| phq9       | q3                      | int       | YES         |                                                                                                  |
| phq9       | q4                      | int       | YES         |                                                                                                  |
| phq9       | q5                      | int       | YES         |                                                                                                  |
| phq9       | q6                      | int       | YES         |                                                                                                  |
| phq9       | q7                      | int       | YES         |                                                                                                  |
| phq9       | q8                      | int       | YES         |                                                                                                  |
| phq9       | q9                      | int       | YES         |                                                                                                  |
| phq9       | q10                     | int       | YES         |                                                                                                  |
+------------+-------------------------+-----------+-------------+--------------------------------------------------------------------------------------------------+

You can see the PHQ9 fields (Q1–Q9 and extra Q10), the standard client-side field, the standard task fields, and the fields added by the server.

Predefined views

Records are created on the tablet devices, with appropriate and simple key relationships. Once they’re on the server, things are more complicated, as the server keeps track of multiple tablet devices (and, moreover, multiple copies across time of information from those tablet devices).

The following views are therefore pre-created to make life easier. They (a) restrict queries to current information, and (b) provide the appropriate server PK (for example, _patient_server_pk to link to the server’s patient._pk field).

Any table X present on the client devices has a corresponding view named X_current. For simple top-level tables (patient, storedvars, blobs), these are defined as follows:

CREATE OR REPLACE VIEW X_current AS
SELECT *
FROM X
WHERE _current
;

For a task table Y, the view Y_current is defined as:

CREATE OR REPLACE VIEW Y_current AS
SELECT Y.*, patient._pk AS _patient_server_pk
FROM Y
INNER JOIN patient
    ON Y.patient_id = patient.id
    AND Y._device = patient._device
    AND Y._era = patient._era
WHERE
    Y._current
    AND patient._current
;

In addition, a view Y_current_withpt, linking in current patient information, is defined as:

CREATE OR REPLACE VIEW Y_current_withpt AS
SELECT
    Y_current.*
    , patient_current.forename AS patient_forename
    , patient_current.surname AS patient_surname
    , patient_current.dob AS patient_dob
    , patient_current.sex AS patient_sex
    , patient_current.idnum1 AS patient_idnum1
    , patient_current.idnum2 AS patient_idnum2
    , patient_current.idnum3 AS patient_idnum3
    , patient_current.idnum4 AS patient_idnum4
    , patient_current.idnum5 AS patient_idnum5
    , patient_current.idnum6 AS patient_idnum6
    , patient_current.idnum7 AS patient_idnum7
    , patient_current.idnum8 AS patient_idnum8
    , patient_current.address AS patient_address
    , patient_current.gp AS patient_gp
    , patient_current.other AS patient_other
FROM Y_current
INNER JOIN patient_current
    ON Y_current._patient_server_pk = patient_current._pk
;

For an ancillary table Z (used by task Y, with a foreign key named Y_id to Y.id), the view Z_current is defined as:

CREATE OR REPLACE VIEW Z_current AS
SELECT Z, Y._pk AS _task_server_pk
FROM Z
INNER JOIN Y
    ON Z.Y_id = Y.id
    AND Z._device = Y._device
    AND Z._era = Y._era
WHERE
    Z._current
    AND Y._current
;

You are encouraged to use the *_current and *_current_withpt views.

Identical *_current and *_current_withpt views are created for all summary tables (in this example, phq9_SUMMARY_TEMP_current and phq9_SUMMARY_TEMP_current_withpt).

As a simple example, to attach patient information to all current instances of a PHQ9 task (mimicking the phq9_current_withpt view), you can run:

SELECT * FROM phq9_current INNER JOIN patient_current ON phq9_current._patient_server_pk = patient_current._pk;

All fields

Code to generate this:

SELECT c.table_name, c.column_name, c.data_type, c.is_nullable, c.column_comment
FROM information_schema.columns c
    INNER JOIN information_schema.tables t
    ON c.table_schema = t.table_schema
    AND c.table_name = t.table_name
WHERE t.table_type='BASE TABLE'
AND c.table_schema='camcops' /* database name */
;

With slightly fancier output, we get some detailed table structure.

Valid HTML 4.01 Transitional
Valid CSS