Difference between revisions of "DB Tables 2015"

From ecology
Jump to: navigation, search
(Created page with "== ''admin.''ee_project_limited == Each row represents a project. {| class="wikitable" ! COLUMN_NAME ! TYPE_NAME ! IS_NULLABLE ! DESCRIPTION |- |id||int8||NO||Unique identifier. ...")
 
Line 1: Line 1:
−
== ''admin.''ee_project_limited ==
+
== ''Leaf'' data ==
 +
 
 +
=== ''admin.''ee_project_limited ===
 
Each row represents a project.
 
Each row represents a project.
 
{| class="wikitable"
 
{| class="wikitable"
Line 30: Line 32:
 
|}
 
|}
  
−
== ''admin.''ee_user ==
+
=== ''admin.''ee_user ===
  
 
Each row represents a user.
 
Each row represents a user.
Line 61: Line 63:
 
|}
 
|}
  
−
== ''gps.'' ee_tracker ==
+
=== ''gps.'' ee_tracker ===
  
 
Each row represents a tracker.
 
Each row represents a tracker.
Line 100: Line 102:
 
|}
 
|}
  
 +
=== ''gps.''ee_individual ===
 +
 +
Each row represents a bird.
 +
 +
This table replaces gps.uva_individual from the old database.
 +
 +
{| class="wikitable"
 +
! 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.
 +
 +
{| class="wikitable"
 +
! 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 ==
+
=== ''admin.''ee_membership ===
  
 
Each row represents the fact that a user has a given role in a given project at the moment.
 
Each row represents the fact that a user has a given role in a given project at the moment.

Revision as of 10:35, 19 November 2014

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
email 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
Retrieved from "https://wiki.e-ecology.nl/index.php?title=DB_Tables_2015&oldid=1110"