DB Tables 2015
From ecology
Source data
admin.ee_project_limited
Each row represents a project.
| COLUMN_NAME | TYPE_NAME | IS_NULLABLE | DESCRIPTION |
|---|---|---|---|
| id | int8 | NO | Unique identifier. Primary key |
| description | text | YES | String for free description. Updatable by the user. |
| end_date | timestamp | NO | Date at which the project is not relevant any more |
| key_name | varchar | NO | Unique key name, of the form species_place |
| lft | int8 | NO | "Modified preorder tree traversal" field. Ignore it as a user. |
| rgt | int8 | NO | "Modified preorder tree traversal" field. Ignore it as a user. |
| start_date | timestamp | NO | Project start date |
| station_name | varchar | NO | Where the receiving station is |
| version | int4 | YES | Optimistic locking field. Ignore it as a user. |
| operator_user | int8 | NO | Reference to the user who last edited the project |
| parent | int8 | YES | Reference to the parent project. Only the root project has no parent. |
admin.ee_user
Each row represents a user.
| COLUMN_NAME | TYPE_NAME | IS_NULLABLE | DESCRIPTION |
|---|---|---|---|
| id | int8 | NO | Unique identifier. Primary key |
| address | varchar | YES | String for postal address |
| varchar | NO | e-mail address | |
| encrypted_password | varchar | NO | Encrypted password |
| login_name | varchar | NO | Login name |
| mobile_phone | varchar | YES | String for mobile phone number |
| name | varchar | NO | (first) name |
| surname | varchar | NO | Surname |
| version | int4 | YES | Optimistic locking field. Ignore it as a user. |
| operator_user | int8 | YES | Id of the user who last edited this user's data. |
gps. ee_tracker
Each row represents a tracker.
This table replaces the old gps.uva_device.
| COLUMN_NAME | TYPE_NAME | IS_NULLABLE | DESCRIPTION |
|---|---|---|---|
| id | int8 | NO | Unique identifier. Primary key. |
| device_info_serial | int8 | NO | Serial number of the tracker |
| end_date | timestamp | NO | Date at which the device is not relevant any more |
| firmware_version | varchar | YES | String for firmware version |
| mass | numeric(4,2) | YES | Mass (in grams) |
| start_date | timestamp | NO | Date before which the device is not relevant |
| version | int4 | YES | Optimistic locking field. Ignore it as a user. |
| x_o | numeric(30,6) | YES | Accelerometer calibration: offset on the x (surge) axis |
| x_s | numeric(30,6) | YES | Accelerometer calibration: sensitivity on the x (surge) axis |
| y_o | numeric(30,6) | YES | Accelerometer calibration: offset on the y (sway) axis |
| y_s | numeric(30,6) | YES | Accelerometer calibration: sensitivity on the y (sway) axis |
| z_o | numeric(30,6) | YES | Accelerometer calibration: offset on the z (heave) axis |
| z_s | numeric(30,6) | YES | Accelerometer calibration: sensitivity on the y (heave) axis |
gps.ee_individual
Each row represents a bird.
This table replaces gps.uva_individual from the old database.
| COLUMN_NAME | TYPE_NAME | IS_NULLABLE | DESCRIPTION |
|---|---|---|---|
| id | int8 | NO | Unique identifier. Primary key. |
| colour_ring | varchar | YES | String for ring colour |
| end_date | timestamp | NO | Date at which the bird is no longer relevant |
| mass | numeric(5,0) | YES | Mass (grams) |
| operated_date | timestamp | NO | Recording of the last time the row was edited |
| remarks | text | YES | Text for user remarks |
| ring_number | varchar | NO | Ring number |
| sex | varchar | NO | Sex |
| start_date | timestamp | NO | Date before which the bird is not relevant |
| version | int4 | YES | Optimistic locking field. Ignore it as user. |
| operator_user | int8 | NO | Reference to the user who last edited the row |
| species | int8 | NO | Reference to the species |
gps.ee_track_session
Each row represents a track session.
| COLUMN_NAME | TYPE_NAME | IS_NULLABLE | DESCRIPTION |
|---|---|---|---|
| id | int8 | NO | Unique identifier. Primary key. |
| end_date | timestamp | NO | Date at which the track session is not relevant any more |
| operated_date | timestamp | NO | Recording of the last time the row was edited |
| remarks | text | YES | Free text for comments |
| start_date | timestamp | NO | Date before which the track session is not relevant |
| start_latitude | numeric(11,8) | NO | Latitude where the bird was tagged |
| start_longitude | numeric(11,8) | NO | Longitude where the bird was tagged |
| version | int4 | YES | Optimistic locking field. Ignore it as a user. |
| device | int8 | NO | Reference to the tracker |
| individual | int8 | NO | Reference to the individual |
| operator_user | int8 | NO | Reference to the user who last edited the row |
| project | int8 | NO | Reference to the project this track session belongs to |
Relationship with projects
admin.ee_membership
Each row represents the fact that a user has a given role in a given project at the moment.
| COLUMN_NAME | TYPE_NAME | IS_NULLABLE | DESCRIPTION |
|---|---|---|---|
| id | int8 | NO | Unique identifier. Primary key. |
| comments | text | YES | Free text for comments |
| label | text | YES | Labels that Willem wants to have for people in projects |
| role | varchar | NO | Role that the user holds during this period |
| status | varchar | NO | Status that the row holds during that period. Look at the attached file to know the allowed transitions. |
| version | int4 | YES | Optimistic locking field. Ignore it as a user. |
| member_user | int8 | NO | Reference to the user that this membership applies to |
| operated_date | timestamp | NO | Date that the row was last modified |
| operator_user | int8 | NO | Reference to the user who last edited this row |
| project | int8 | NO | Reference to the project that this membership applies to |
admin.ee_tracker_ownership
Each row represents the fact that a tracker belongs to a project within a specified time period.
| COLUMN_NAME | TYPE_NAME | IS_NULLABLE | DESCRIPTION |
|---|---|---|---|
| id | int8 | NO | Unique identifier. Primary key. |
| end_date | timestamp | NO | Date at which the tracker ceases to belong to the project |
| operated_date | timestamp | NO | Recording of the last time the row was edited |
| start_date | timestamp | NO | Date at which the project begins to own the tracker |
| version | int4 | YES | Optimistic locking field. Ignore it as a user. |
| device | int8 | NO | Reference to the tracker |
| operator_user | int8 | NO | Reference to the user who last edited the row |
| project | int8 | NO | Reference to the project this tracker belongs to |
admin.ee_individual_ownership
Each row represents the fact that an individual belongs to a project within a specified time period.
| COLUMN_NAME | TYPE_NAME | IS_NULLABLE | DESCRIPTION |
|---|---|---|---|
| id | int8 | NO | Unique identifier. Primary key. |
| end_date | timestamp | NO | Date at which the individual ceases to belong to the project |
| operated_date | timestamp | NO | Recording of the last time the row was edited |
| start_date | timestamp | NO | Date at which the project begins to own the individual |
| version | int4 | YES | Optimistic locking field. Ignore it as a user. |
| individual | int8 | NO | Reference to the individual |
| operator_user | int8 | NO | Reference to the user who last edited the row |
| project | int8 | NO | Reference to the project this individual belongs to |
gps.ee_tracker_sharing
Each row represents the fact that a tracker's information within a specified period, can be accessed by users belonging to another project.
| COLUMN_NAME | TYPE_NAME | IS_NULLABLE | DESCRIPTION |
|---|---|---|---|
| id | int8 | NO | Unique identifier. Primary key. |
| end_date | timestamp | NO | Date at which the tracker information ceases to be available to the receiving project |
| operated_date | timestamp | NO | Recording of the last time the row was edited |
| start_date | timestamp | NO | Date at which the tracker information becomes available to the receiving project |
| version | int4 | YES | Optimistic locking field. Ignore it as a user. |
| operator_user | int8 | NO | Reference to the user who last edited the row |
| project | int8 | NO | Reference to the project that receives access to the tracker information |
| tracker_ownership | int8 | NO | Reference to the tracker ownership by virtue of which, the tracker information is shared |
Engineering data
gps.uva_trackingfile_parsing
Each row on this table represents the status of the last attempt to parse (process) the raw data file that came from a tracker.
It is heavily used by the dashboard to determine how a given file is represented there.
| COLUMN_NAME | TYPE_NAME | IS_NULLABLE | DESCRIPTION |
|---|---|---|---|
| id | int8 | NO | Unique identifier of the row |
| date_reported | date | NO | Date that the file arrived |
| filename | varchar | NO | Name of the file |
| last_modified_date | timestamp | NO | Date that the file was last modified on |
| device_info_serial | int4 | YES | Serial number of the tracker this file comes from |
| filesize | int8 | NO | Size of the file in KB |
| parsed_date | timestamp | YES | Date and time that the file was last attempted to be parsed on |
| version | int4 | NO | Optimistic locking field. Ignore it as a user. |
| last_found_date | timestamp | YES | Last time that a process that runs to look for new files saw the file |
| n_lines | int4 | YES | Count of lines found in the file last time it was processed |
| n_data_lines | int4 | YES | Count of actual data lines, out of the total lines |
| n_tracking_records | int4 | YES | Count of GPS fixes found during the parsing process |
| n_communication_records | int4 | YES | Count of communication records found during the parsing process |
| n_energy_records | int4 | YES | Count of energy records found during the parsing process |
| n_acceleration_records | int4 | YES | Count of acceleration records found during the parsing process |
| n_acceleration_start_records | int4 | YES | Count of acceleration start records found during the parsing process |
| n_errors | int4 | YES | Count of errors encountered during the parsing process |
| n_settings_records | int4 | YES | Count of settings records found during the parsing process |
| filepath | text | NO | Directory path of the file on the server |
gps.uva_trackingfile_messages
| COLUMN_NAME | TYPE_NAME | IS_NULLABLE | DESCRIPTION |
|---|---|---|---|
| id | serial | NO | Unique identifier of the row |
| tracking_bookkeeping_id | int4 | YES | Reference to the gps.uva_trackingfile_parsing row for this file |
| message_type | int4 | YES | Code for the type of information being told by the message. |
| message | text | YES | Textual message from the parsing process telling what it found. |
| message_code | int4 | YES | |
| line_number | int4 | YES | Line in the file where the message is reported |
gps.uva_warning102
Since firmware version 1.0.2.7 this table was added as catchall for events that do not occur very often (C10, C20) or errors that occur during parsing. For example all CRC failures can be selected with :
select * from gps.uva_warning102 where warning = 'CRC8 failure' order by device_info_serial;
| COLUMN_NAME | TYPE_NAME | IS_NULLABLE | DESCRIPTION |
|---|---|---|---|
| device_info_serial | int4 | NO | Serial number of the tracker |
| filename | text | NO | log file name |
| linenumber | integer | YES | line number in file |
| warning | text | YES | The warning message |
| line | text | YES | The actual line in the file |
gps.uva_comm101
Communication table indicates status and result of communications with ground stations. Only for newer firmware (v.1.0.1.4 and higher).
This table structure has not changed with respect to our previous database.
| COLUMN_NAME | TYPE_NAME | IS_NULLABLE | DESCRIPTION |
|---|---|---|---|
| device_info_serial | int4 | NO | Serial number of the tracker. |
| date_time | timestamp | NO | timestamp of start communication |
| date_time_end | timestamp | YES | timestamp of end communication |
| date_time_utc | timestamp | YES | date_time in utc (if available) |
| date_time_end_utc | timestamp | YES | date_time_end in utc (if available) |
| action | text | YES | JOINED / RESCHEDULE ?? |
| bytes_communicated | int4 | YES | Number of bytes communicated (total of all bytes per DATA line) |
| status | text | YES | READY, TIMEOUT, LOWPOWER, MISSING |
| network_join_time | int4 | YES | milliseconds |
| number_identical_lines | int4 | YES | Number identical lines in data block. Gives indication |
| non_ascii_char | int4 | YES | Number of non ascii characters (indicated by question-mark ?) |
| sts_time | timestamp | YES | derived from S0 hh,mi |
| timesynced | int2 | YES | TimeSynced (0=not sy:wnced, 1=user set, 2=Gps Synced) |
| devicemode | int2 | YES | DeviceMode (0-99). This describes whether the device is in a special operational mode. 0 = normal operational mode; 80 hex = Memory save mode (memory is almost full and all activity is drastically reduced so the circular memory probably won't be overwritten or, if it does, it will be kept to a minimum) |
| vbat | float8 | YES | Battery voltage in V (0-~4.200V) |
| logb | int4 | YES | logged bytes, waiting for download (0-4Mbyte) |
| logp | int2 | YES | Logged percentage (units 0.1%) of memory waiting to be downloaded (0-1000) |
| gpsii | int4 | YES | GpsIval, current (0-65535) |
| comii | int4 | YES | ComIval, current (0-65535) |
| accii | int4 | YES | AcceleroIval, current (0-65535) |
| swddd | int4 | YES | User switches, current (0-65535) |
| last_gps_fix | timestamp | YES | timestamp of last fix |
| latitude | float8 | YES | Latitude of last fix. Decimal degrees |
| longitude | float8 | YES | Longitude of last fix. Decimal degrees |
gps.uva_energy101
Only for newer firmware.
This table structure has not changed with respect to our previous database.
| COLUMN_NAME | TYPE_NAME | IS_NULLABLE | DESCRIPTION |
|---|---|---|---|
| device_info_serial | int4 | NO | Serial number of the tracker |
| date_time | timestamp | NO | Date and time of the reported data |
| timesynced | int2 | YES | TimeSynced (0=not synced, 1=user set, 2=Gps synced) |
| devicemode | int2 | YES | (0-99) |
| vsll | float8 | YES | Solar Voltage during charging in mV (0-~2500) |
| vbat | float8 | YES | Battery Voltage in V (0-~4.200) |
| ssw | int2 | YES | Solar Cell Switches ... (0-255) fw1022 and up |
| YES | extended fields if special user switch is on | ||
| vsoo | float8 | YES | Solar voltage open in V (0-~2.500) |
| vdrf | float8 | YES | reference voltage Dac in V (0-~2.435) |
| vchr | float8 | YES | charge current voltage |
| g | int2 | YES | Gps battery threshold OK |
| c | int2 | YES | Com battery threshold OK |
| m | int2 | YES | Charge Mode (0,1,2 Com battery threshold OK |
| pressure | int4 | YES | in pascal (1mBar = 100Pa, 1pressuremeter = ~12Pa) |
| temperature | float8 | YES | in celcius degrees |
gps.uva_settings101
Settings are either from C0-C1-C9 lines or parsed from uplink communication lines. If C0-C1 is parsed its values are compared with the previous value and if the settings are the same, then no new records need to be added. If uplink settings are parsed, then wait until the last setting has been seen (DL command or EOF); then store all the new settings in one record. Only for newer firmware.
This table structure has not changed from the old database.
| COLUMN_NAME | TYPE_NAME | IS_NULLABLE | DESCRIPTION |
|---|---|---|---|
| device_info_serial | int4 | NO | |
| date_time | timestamp | NO | Derived timestamp from Hh:mi fields in C0 combined with computer timestamp in JOINED line |
| timesynced | int2 | YES | |
| timezone_time | int2 | YES | |
| swddd | int4 | YES | SpecialUserSwitches |
| gpsnavmode | int2 | YES | GpsNavMode 0-8. This is the Kalman filter used for GPS navigation mode often called the "Dynamic platform model". The following codes are provided by the GPS chip manufacturer for the UBlox GPS chip LEAS4S: 1 = stationary, 2 = pedestrian, 3 = automotive, 4 = sea, 5 = airborne 1G, 6 = airborne 2G, 7 = airborne 3G. The UBlox GPS chip LEAS6S uses the following codes: 0 = portable, 2 = stationary, 3 = pedestrian, 4 = automotive, 5 = sea, 6 = airborne with < 1g acceleration, 7 = airborne with < 2g acceleration, 8 = airborne with < 4g acceleration |
| gpsreceivermode | int2 | YES | GpsReceiverNode 0-3 |
| gpsfixtime | int2 | YES | GpsFixTime 0-255 |
| gps_ivaln_on_h | int2 | YES | hours Gps IvalN on |
| gps_ivaln_on_m | int2 | YES | minutes Gps IvalN on |
| gps_ivaln | int4 | YES | GpsIvalN (0-65530) |
| gps_ivale_on_h | int2 | YES | hours Gps IvalE on |
| gps_ivale_on_m | int2 | YES | minutes GpS IvalE on |
| gps_ivale | int4 | YES | GpsIvalE |
| com_ivaln_on_h | int2 | YES | hours Com IvalN on |
| com_ivaln_on_m | int2 | YES | minutes Com IvalN on |
| com_ivaln | int4 | YES | ComIvalN (0-65530) |
| com_ivale_on_h | int2 | YES | hours Com IvalE on |
| com_ivale_on_m | int2 | YES | minutes Com IvalE on |
| com_ivale | int4 | YES | ComIvalE |
| accii | int4 | YES | Accelero measurement interval |
| accbn | int4 | YES | Accelero number of blocks |
| accsn | int4 | YES | Accelero number of samples (0-65536) |
| f | int2 | YES | Accelero sample frequency (0=20Hz, 1=10Hz, 2=5Hz, 3=1Hz) |
| pdp | int2 | YES | PdopMsk (3-250) |
| pac | int2 | YES | PaccMsk (10-100) |
| tdp | int2 | YES | TdopMsk (3-250) |
| tac | int2 | YES | TaccMsk (30-300) |
| ga | int2 | YES | Additional Fixes GpsIval mode (3-25) before interval fix is logged |
| aol | int2 | YES | AccMemOffLev (100 - 950) units 0.1%; if Memfull above this level all Accelero activity will be overruled and switched off untill MemFull < level - 5 |
| eventnum | text | YES | 12 eventnumbers (0-0xff) only printed if not zero, therfore converted to text |
