== Structure of new ARSF Database ==

'''Things to change in database revision 2'''
* Move project_flights.area to flights.area ?
* Move project_flights.priority to flights.priority ?
* Remove sensor_status.id primary key and use the submit_time as primary key.




The database is made up of 9 tables, joined together by relations. See below for a diagram of the database tables and relations

The PI table is made up of the PI's name, address and their email address. The table has and internal field called 'id' which is filled in automatically and is referred to whenever a PI is linked to a project code. If you try and delete a PI, and that PI still has project_codes referring to the PI then the Database will prevent you deleting the PI.
||||||||||||= pi Table =||
||= Key =||= Field Name =||= Value Type =||= Constraints =||= Notes =||
|| Primary Key || id || Integer(4 Bytes) ||  Required  || Value is automatically generated. ||
||  || pi_name || Text (variable length) ||  Required  ||  ||
||  || address || Text (variable length) ||  || Not required as may not be known at time of entry. ||
||  || email || Text (variable length) ||  || Not required as may not be known at time of entry. ||

The projects table contains all the project codes and what PI relates to which project code. Many project codes can relate to one PI.
||||||||||||= projects Table =||
||= Key =||= Field Name =||= Value Type =||= Constraints =||= Notes =||
|| Primary Key || project_code || Text (variable length) ||  Required  ||  ||
|| Foreign Key || pi_id || Integer(4 Bytes) ||  Required  || The PI's ID from the pi table. ||
||  || summary || Text (variable length) ||  ||  ||
||  || eufar_code || Text (variable length) ||  ||  ||
||  || objective || Text (variable length) ||  ||  ||

The flights table contains the information about one flight. So if two flights were made in one day, then there would be two entries in the flight table. 
||||||||||||= flights Table =||
||= Key =||= Field Name =||= Value Type =||= Constraints =||= Notes =||
|| Primary Key || flight_id || Integer(4 Bytes) ||  Required  || Value is automatically generated. ||
||  || flight_date || Date || Required || The date that the flight took place ||
||  || primary_ticket || Integer(2 Bytes) || Required || Primary ticket id for trac ||
||  || kml_file_location || Text (variable length) ||  ||  ||
||  || data_location || Text (variable length) ||  ||  ||
||  || sortie || Text (variable length) || Required || The letter to differentiate multiple flights flown on one day ||
||  || notes || Text (variable length) ||  ||  ||
||  || secondary_tickets || Integer(2 Bytes) Array ||  ||  ||
||  || data_recieved_on || Date ||  ||  ||

The project_flights table (in database terms prevents a many-to-many relationship) holds data which allows for a single flight to have more than one project code, the table also contains the priorities for the different project codes and the area that was investigated.
||||||||||||= project_flights Table =||
||= Key =||= Field Name =||= Value Type =||= Constraints =||= Notes =||
|| Primary Key || flight_id || Integer(4 Bytes) ||  Required  || Value must be in the flights table. ||
|| Primary Key || project_code || Text (variable length) ||  Required  || Value must be in the projects table. ||
||  || priority || Text (variable length) ||  Required  || Value must be in the priorities table. ||
||  || area || Text (variable length) ||  Required  ||  ||

















== Structure of old ARSF ID-Key-Value database ==

For furture maintenance.

{{{
Indented sublevels mean the child has an "id" field matching the "value" field of the parent. 
If items are on the same level without a separating space, it means they share the same id. 
The flight_* items appear in separate groups, hence the abbreviation.

------------

piflight_pi_id
   pi_address
   pi_email
   pi_name
piflight_flight_id
   flight_area
   flight_data_location
   flight_date_received
   flight_jday
   flight_letter
   flight_notes
   flight_primary_ticket
   flight_year
   flight_kmlweblink

progress_change_dataset_id
   dataset_del_check_time
   dataset_flight_id
      flight_*
   dataset_number_of_lines
   dataset_priority
   dataset_priority_offset
   dataset_processing_progress
   dataset_sensor_id
      sensor_name
      sensor_type
   dataset_time_to_complete
progress_change_date
progress_change_notes
progress_change_progress
progress_change_user

projectflight_flight_id
   flight_*
projectflight_project_id
   project_code
   project_eufar_code
   project_objective
   project_priority
   project_summary

secondary_ticket_flight_id
   flight_*
secondary_ticket_number 

-----------

There is a 39th "kml_link" key but this only appears once, so I think it was replaced by flight_kmlweblink but not deleted. 

}}}

== Adding a new sensor ==

To add a new sensor to the status page all you need to do is to add two entries to the database:

|| ID || Type || Value ||
|| f855984c-b5d2-11e3-a2a4-1803732ee31c ||   sensor_name  ||  Fenix[[BR]] || 
|| f855984c-b5d2-11e3-a2a4-1803732ee31c ||   sensor_type ||   Hyperspectral || 

The IDs can be anything but they must match.