Create a Data Base clean up script/ set of funcitons

[mccabete]

It would be good to have a script or set of functions or both that round up records that need to be fixed. This could apply to:

files without input records, inputs with no file records, file-formats without files, dbfiles records that point to files that don’t exist etc.

This could just be some queries that puts records into a generic readable file that could be hand edited, and then re-fed into a function that would cull records that remain.

[mccabete]

@tonygardella What DB errors do we tend to get?

[ashiklom]

Let's make an editable list! I'll start a checklist here -- feel free to edit this comment (pencil button in the top right of the issue) to add stuff, or check stuff off that has been implemented.

• [ ] Orphan dbfiles records -- i.e. dbfiles records with no input, model, etc. records
• [ ] Childless input records -- i.e. input records that don't have corresponding dbfiles records
• [ ] formats records that don't have any matching dbfiles

[dlebauer]

• constraints can be used to prevent orphaned / childless records.
• There are a bunch of cleanup tasks based on constraint violations listed here: https://github.com/PecanProject/bety/issues?utf8=✓&q=is%3Aopen%20%22needed%20cleanup%22%20
• see also this function for combining duplicate records https://github.com/PecanProject/bety/issues/185#issuecomment-71534163

[ashiklom]

@ankurdesai suggestion -- Function for a given site and met product that would purge everything except original data download.

[mccabete]

Mike- A function that takes away old runs, and especially met made by old runs

• have certain provisions to prevent deleting met that can't be automatically recreated by PEcAn

[tonygardella]

It use to be that failed downloads of raw met data created a db record even though actual files are not there. For example if a site only had 2000- 2010 and you ran 1998-2002 it would update the input record to say 1998-2002 was there and only download 2000-2002. No sure how to fix this mismatch other than to delete old runs and their associated file as much as possible.

[ashiklom]

failed downloads of raw met data created a db record even though actual files are not there

Input records, especially met records, should have a start date, end date, and format specification, right? Perhaps we can use the format record and load_data functionality to get the actual bounds of the data, compare them to the start and end date, and if there is a mismatch, then flag the file for deletion? Similarly, would it be too aggressive to delete any input file that is missing any of these specifications? I.e. Are there circumstances where it's OK for an input to be missing a start date, end date, or format?

[dlebauer]

@ashiklom soil datasets often do not have a start and end date

[tonygardella]

@ashiklom I think that would work. We could update the records to reflect the data that is actually there or delete them. I think for this it would be best to just use the met format records.

[mccabete]

A checklist of functions to create. Also available here in a google doc.

A quick and dirty synthesis of github comments/issues turned into a list of functions:

Mass Execution functions

• [ ] write_out_list_of_records(results_from_sql_query, path)
  • Creates human-readable file with sql query results
  • [ ] cull_entries( human_readable_file, connection)
  • Parses file and deletes any remaining entries

Checks

• [ ] check_file_exists (input_record, connection)
• A check that the file associated with an input record is still there
• Maybe returns errors from load_data/ read.output?
• [ ] check_record_dates (input_record, connection)
• Checks that if an input record has a date range that matches that of its associated file

Find DB Entry Functions

• [x] find_formats_without_inputs(connection, date_range = NULL)
• Psql query of all formats without associated inputs
• [x] find_inputs_without_formats(connection, date_range = NULL)
• psql query of inputs without formats
• [ ] find_variables_without_formats (connection, date_range = NULL)
• Psql query of all variables without associated formats
• [ ] find_files_without_inputs(connection, date_range = NULL)
• Psql query of all files without an associated input
• [ ] find_inputs_without_files(connection, date_range = NULL)

Find Run Functions

• [ ] find_runs(connection, date-range, user, stage=NULL)
• psql query that Lists all failed runs from a date range and for a given user. Date-range and user are possibly overkill, but would hopefully prevent accidental deletion of production runs
• Could optionally list all runs that fail at a certain step. --Ie: all but runs that failed at model step
• [ ] find_in_progress_runs (connection, user=NULL)
• pqsl queryLists all runs still in progress

[github-actions[bot]]

This issue is stale because it has been open 365 days with no activity.