DB Tables 2015

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

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