Changes between Version 30 and Version 31 of statusdatabase


Ignore:
Timestamp:
Oct 16, 2014, 11:17:42 AM (10 years ago)
Author:
tec
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • statusdatabase

    v30 v31  
     1= ARSF Documentation =
    12== ARSF Status Page Documentation ==
    23Documentation for web files can be generated using PHPDoc and JSDoc as they contain comments which can be converted into HTML documentation.
    … …  
    1112}}}
    1213
     14== ARSF Status Database =
     15Ok I will have hopefully attached an up-to-date entity relationship diagram to show the layout of the database, don't believe it wholeheartedly, check the actual database (pgsql.npm.ac.uk AKA hodgson.npm.ac.uk) for actual column names etc...
    1316
     17The majority of the database is self explanatory. PIs are associated with projects, a flight can be related to many projects (as defined in project_flights). A flight can have flight_sensors (basically what sensor was used on that flight) and each flight sensor can have many sensor_statuses (this keeps track of the progress, I like to think of it as an audit trail, keeps track of what everyone has done). That is basically it. Now '''do not touch my database unless you are sure you know what you are doing!'''
    1418
     19The following info is only useful to those already fluent in SQL
    1520
     21=== SQL Explanation ===
     22This explanation might not always be up-to-date but should be rather close.
    1623
    17 
    18 
    19 
    20 
    21 
    22 
    23 
    24 
    25 
    26 == Structure of new ARSF Database ==
    27 
    28 '''Things to change in database revision 3'''
    29 * MOVE Prioirty to flights from projects :(
    30 * Search add exclude project option
    31 * DOCUMENT SQL QUERIES
    32 * need trigger for when last status is gone clear up flight sensors table.
    33 * 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.
    34 * Determine search stuff properly. Do once progress view is sorted, probably use get variables.
    35 * Make objects for things like PIs, Flights etc...
    36 * ~~priorities and progress tables, make fields unique?~~
    37 * ~~Remove sensor_status.id primary key and use the submit_time as primary key.~~
    38 * ~~Make sensor_status.username nullable? check data first~~
    39 * ~~Go through and look for reasonable defaults for things.~~
    40 * ~~See if progress view / order view is necessary~~.
    41 * ~~make flights ondelete cascade.~~
    42 * ~~make sensor_status delete cascade from flight sensor id~~
    43 * ~~change sensor_status.flight_id to sensor_status.flight_sensor_id~~
    44 * ~~fix spelling boresite -> boresight~~
    45 * ~~Move project_flights.area to flights.area~~
    46 * ~~Move project_flights.priority to flights.priority~~
    47 * ~~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.~~
    48 * ~~Rename sensor_status.sensor_id to Rename sensor_status.flight_id~~
    49 * ~~Make primary ticket nullable~~
    50 * ~~Add a boolean to the flights table so it can be marked as a boresite, for sorting purposes.~~
    51 * ~~Add a blocked text field to flight status so that a message can be recorded when a status is marked as blocked.~~
    52 
    53 
    54 
    55 
    56 
    57 The database is made up of 9 tables, joined together by relations. See below for a diagram of the database tables and relations
    58 
    59 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.
    60 ||||||||||||= pi Table =||
    61 ||= Key =||= Field Name =||= Value Type =||= Constraints =||= Notes =||
    62 || Primary Key || id || Integer(4 Bytes) ||  Required  || Value is automatically generated. ||
    63 ||  || pi_name || Text (variable length) ||  Required  ||  ||
    64 ||  || address || Text (variable length) ||  || Not required as may not be known at time of entry. ||
    65 ||  || email || Text (variable length) ||  || Not required as may not be known at time of entry. ||
    66 
    67 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.
    68 ||||||||||||= projects Table =||
    69 ||= Key =||= Field Name =||= Value Type =||= Constraints =||= Notes =||
    70 || Primary Key || project_code || Text (variable length) ||  Required  ||  ||
    71 || Foreign Key || pi_id || Integer(4 Bytes) ||  Required  || The PI's ID from the pi table. ||
    72 ||  || summary || Text (variable length) ||  ||  ||
    73 ||  || eufar_code || Text (variable length) ||  ||  ||
    74 ||  || objective || Text (variable length) ||  ||  ||
    75 
    76 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.
    77 ||||||||||||= flights Table =||
    78 ||= Key =||= Field Name =||= Value Type =||= Constraints =||= Notes =||
    79 || Primary Key || flight_id || Integer(4 Bytes) ||  Required  || Value is automatically generated. ||
    80 ||  || flight_date || Date || Required || The date that the flight took place ||
    81 ||  || primary_ticket || Integer(2 Bytes) || Required || Primary ticket id for trac ||
    82 ||  || kml_file_location || Text (variable length) ||  ||  ||
    83 ||  || data_location || Text (variable length) ||  ||  ||
    84 ||  || sortie || Text (variable length) || Required || The letter to differentiate multiple flights flown on one day ||
    85 ||  || notes || Text (variable length) ||  ||  ||
    86 ||  || secondary_tickets || Integer(2 Bytes) Array ||  ||  ||
    87 ||  || data_recieved_on || Date ||  ||  ||
    88 
    89 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.
    90 ||||||||||||= project_flights Table =||
    91 ||= Key =||= Field Name =||= Value Type =||= Constraints =||= Notes =||
    92 || Primary Key || flight_id || Integer(4 Bytes) ||  Required  || Value must be in the flights table. ||
    93 || Primary Key || project_code || Text (variable length) ||  Required  || Value must be in the projects table. ||
    94 ||  || priority || Text (variable length) ||  Required  || Value must be in the priorities table. ||
    95 ||  || area || Text (variable length) ||  Required  ||  ||
    96 
    97 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.
    98 ||||||||||||= flight_sensors Table =||
    99 ||= Key =||= Field Name =||= Value Type =||= Constraints =||= Notes =||
    100 || Primary Key || id || Integer(4 Bytes) ||  Required  || Value is automatically generated. ||
    101 ||  || flight_id || Integer(4 Bytes) ||  Required  || Value is the flight id from the flights table. ||
    102 ||  || sensor_name ||  ||  Required  || Name of the sensor used, must be in the sensors table ||
    103 ||  || priority ||  ||  Required  || Processing priority of the data, must be in the processing table. ||
    104 ||  || priority_offset || Integer(2 Bytes) ||    || Not sure what this is used for ||
    105 ||  || number_of_flightlines || Integer(2 Bytes) ||    ||  ||
    106 ||  || completion_time || Real (variable precision 4 Bytes) ||    ||  ||
    107 ||  || delivery_check_time || Real (variable precision 4 Bytes) ||    ||  ||
    108 
    109 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.
    110 ||||||||||||= sensor_status Table =||
    111 ||= Key =||= Field Name =||= Value Type =||= Constraints =||= Notes =||
    112 || Primary Key || id || Integer(4 Bytes) ||  Required  || Value is automatically generated. ||
    113 ||  || sensor_id || Integer(4 Bytes) ||  Required  || Value is the flight_id from the flight_sensors table. ||
    114 ||  || username || Text (variable length) ||  Required  ||  ||
    115 ||  || 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. ||
    116 ||  || progress || Text (variable length) ||  Required  || Value must be in the progress table. ||
    117 ||  || notes || Text (variable length) ||    ||  ||
    118 
    119 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".
    120 ||||||||||||= priorities Table =||
    121 ||= Key =||= Field Name =||= Value Type =||= Constraints =||= Notes =||
    122 || Primary Key || priority_name || Text (variable length) ||  Required  || Value for priority. (There should be a valid CSS class to match this) ||
    123 ||  || value || Integer(2 Bytes) ||  Required  || Integer value for the priority. Not sure what this is used for. ||
    124 ||  || description || Text (variable length) ||  Required  || Friendly description of the priority. ||
    125 
    126 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.
    127 ||||||||||||= progress Table =||
    128 ||= Key =||= Field Name =||= Value Type =||= Constraints =||= Notes =||
    129 || Primary Key || progress_name || Text (variable length) ||  Required  || Value for progress. (There should be a valid CSS class to match this) ||
    130 ||  || value || Integer(2 Bytes) ||  Required  || Integer value for the progress. Not sure what this is used for. ||
    131 ||  || description || Text (variable length) ||  Required  || Friendly description of the progress. ||
    132 
    133 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".
    134 ||||||||||||= sensors Table =||
    135 ||= Key =||= Field Name =||= Value Type =||= Constraints =||= Notes =||
    136 || Primary Key || sensor_name || Text (variable length) ||  Required  ||  ||
    137 ||  || sensor_type || Text (variable length) ||  Required  ||  ||
    138 
    139 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.
    140 
    141 
    142 
    143 
    144 
    145 
    146 
    147 
    148 
    149 
    150 
    151 
    152 
    153 
    154 
    155 
    156 
    157 
    158 
    159 == Structure of old ARSF ID-Key-Value database ==
    160 
    161 For furture maintenance.
    162 
     24This needs to be run as the superuser on the database, normally `postgres`
    16325{{{
    164 Indented sublevels mean the child has an "id" field matching the "value" field of the parent.
    165 If items are on the same level without a separating space, it means they share the same id.
    166 The flight_* items appear in separate groups, hence the abbreviation.
    167 
    168 ------------
    169 
    170 piflight_pi_id
    171    pi_address
    172    pi_email
    173    pi_name
    174 piflight_flight_id
    175    flight_area
    176    flight_data_location
    177    flight_date_received
    178    flight_jday
    179    flight_letter
    180    flight_notes
    181    flight_primary_ticket
    182    flight_year
    183    flight_kmlweblink
    184 
    185 progress_change_dataset_id
    186    dataset_del_check_time
    187    dataset_flight_id
    188       flight_*
    189    dataset_number_of_lines
    190    dataset_priority
    191    dataset_priority_offset
    192    dataset_processing_progress
    193    dataset_sensor_id
    194       sensor_name
    195       sensor_type
    196    dataset_time_to_complete
    197 progress_change_date
    198 progress_change_notes
    199 progress_change_progress
    200 progress_change_user
    201 
    202 projectflight_flight_id
    203    flight_*
    204 projectflight_project_id
    205    project_code
    206    project_eufar_code
    207    project_objective
    208    project_priority
    209    project_summary
    210 
    211 secondary_ticket_flight_id
    212    flight_*
    213 secondary_ticket_number
    214 
    215 -----------
    216 
    217 There is a 39th "kml_link" key but this only appears once, so I think it was replaced by flight_kmlweblink but not deleted.
    218 
     26#!SQL
     27CREATE EXTENSION tablefunc;
    21928}}}
    220 
    221 == Adding a new sensor ==
    222 
    223 To add a new sensor to the status page all you need to do is to add two entries to the database:
    224 
    225 || ID || Type || Value ||
    226 || f855984c-b5d2-11e3-a2a4-1803732ee31c ||   sensor_name  ||  Fenix[[BR]] ||
    227 || f855984c-b5d2-11e3-a2a4-1803732ee31c ||   sensor_type ||   Hyperspectral ||
    228 
    229 The IDs can be anything but they must match.
    230