== Structure of new ARSF Database == '''Things to change in database revision 3''' * make flights ondelete cascade. * make sensor_status delete cascade from flight sensor id * DOCUMENT SQL QUERIES * change sensor_status.flight_id to sensor_status.flight_sensor_id * need trigger for when last status is gone clear up flight sensors table. * fix spelling boresite -> boresight * priorities and progress tables, make fields unique? * ~~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. * ~~In sensors add a boolean to add the logic for "display on status page" therefore in theory we could store other sensor data and it would not be added to another column on the status page.~~ * ~~Rename sensor_status.sensor_id to Rename sensor_status.flight_id~~ * ~~Make primary ticket nullable~~ * Make sensor_status.username nullable? check data first * Go through and look for reasonable defaults for things. * order/order.php page should be created too, this gives simple overview of order in which things should be done when they are not required. * ~~Add a boolean to the flights table so it can be marked as a boresite, for sorting purposes.~~ * ~~Add a blocked text field to flight status so that a message can be recorded when a status is marked as blocked.~~ * See if progress view / ~~order view is necessary~~. * Determine search stuff properly. Do once progress view is sorted, probably use get variables. * Make objects for things like PIs, Flights etc... 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. If one tries to delete a PI and they have a project code related to them, the database will say nah. ||||||||||||= 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. If a flight is deleted then a row in this table is automatically deleted, if you try to delete a PI that is linked to flights the database will prevent you from doing so. ||||||||||||= 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 || || The flight_sensors table contains a row for every sensor that is used on the flight. Store information about how many flight-lines are flown for that sensor and contains the time it has taken to complete the latest delivery check, and time it has taken to complete the processing of the flight sensor data. A row in the flight sensors table will be automatically deleted if the flight or sensor it linked to is deleted. ||||||||||||= flight_sensors Table =|| ||= Key =||= Field Name =||= Value Type =||= Constraints =||= Notes =|| || Primary Key || id || Integer(4 Bytes) || Required || Value is automatically generated. || || || flight_id || Integer(4 Bytes) || Required || Value is the flight id from the flights table. || || || sensor_name || || Required || Name of the sensor used, must be in the sensors table || || || priority || || Required || Processing priority of the data, must be in the processing table. || || || priority_offset || Integer(2 Bytes) || || Not sure what this is used for || || || number_of_flightlines || Integer(2 Bytes) || || || || || completion_time || Real (variable precision 4 Bytes) || || || || || delivery_check_time || Real (variable precision 4 Bytes) || || || The sensor_status page contains all the information about the processing status of the data. Essentially it is kind of an audit trail sort of showing who's done what with the data. ||||||||||||= sensor_status Table =|| ||= Key =||= Field Name =||= Value Type =||= Constraints =||= Notes =|| || Primary Key || id || Integer(4 Bytes) || Required || Value is automatically generated. || || || sensor_id || Integer(4 Bytes) || Required || Value is the flight_id from the flight_sensors table. || || || username || Text (variable length) || Required || || || || submit_time || Timestamp || Required || Should be omitted from the insert query or explicitly told to use the DEFAULT value as this is the current time / insert time. || || || progress || Text (variable length) || Required || Value must be in the progress table. || || || notes || Text (variable length) || || || The priorities table contains the priority name, the numeric value of the priority and the friendly description of the priority. If one tried to delete a priority that is in use the database will just say "nah mate not happening". ||||||||||||= priorities Table =|| ||= Key =||= Field Name =||= Value Type =||= Constraints =||= Notes =|| || Primary Key || priority_name || Text (variable length) || Required || Value for priority. (There should be a valid CSS class to match this) || || || value || Integer(2 Bytes) || Required || Integer value for the priority. Not sure what this is used for. || || || description || Text (variable length) || Required || Friendly description of the priority. || The progress table contains the progress name, the numeric value of the progress and the friendly description of the progress. If a flight status uses a progress value deleting it will not work. ||||||||||||= progress Table =|| ||= Key =||= Field Name =||= Value Type =||= Constraints =||= Notes =|| || Primary Key || progress_name || Text (variable length) || Required || Value for progress. (There should be a valid CSS class to match this) || || || value || Integer(2 Bytes) || Required || Integer value for the progress. Not sure what this is used for. || || || description || Text (variable length) || Required || Friendly description of the progress. || The sensors table contains the sensor and the type of sensor it is. E.g the sensor name would be "Fenix" and type would be "Hyperspectral". ||||||||||||= sensors Table =|| ||= Key =||= Field Name =||= Value Type =||= Constraints =||= Notes =|| || Primary Key || sensor_name || Text (variable length) || Required || || || || sensor_type || Text (variable length) || Required || || A some views have been created for commonly used queries. (A view is basically a "virtual" table based on an SQL query.) A view called status_page displays all the data for the status page. That view uses another view called flight_progress which is a query that joins a few table and then joins that to a pivot table (basically a query that converts rows to columns). The flight_progress pivot table had to be hard coded so whenever a new sensor is added the view will need to be updates, I intend to automate recreating the view but in case it can't be done it shall at least be documented. == 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.