DB Tables 2015
From ecology
Contents
Leaf 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 |
