PGYI DB Brainstorming

brainstorming.txt

-- API --

validate_upload(upload_id) --inserts from get_upload_violations

	exec delete_duplicates()upload_id -- iterate through eacj onValidate

	insert from get_upload_violations(upload_id) into violations -- query on all rules without insert


delete_upload()

commit_upload() -- iterate through each onCommit in order


-- TERMS --

new = data not in the database
duplicate = data aleady in the database

onValidate = pre-validate (upload_id implied)
onCommit = post-validate


-- ORDER --

         plot
        |   |
       |     |
  trees      plot_measurement
      |       |
       |     |
    trees_measurement

plot 
	onCommit:10 : delete from plot where not is_commited and key not in (select key from plot where is_commited)
	onCommit:11 : update from plot set is_commited = 'true' where upload_id = 'myid'

	plot_measurement x
		onValidate : delete from plot_measurement where not is_commited and key not in (select key from plot_measurement where is_commited)
		onCommit:4 : update from plot_measurement set is_commited = 'true' where upload_id = 'myid'

		trees_measurement  x
			onValidate : delete from trees_measurement where not is_commited and key not in (select key from trees_measurement where is_commited) --(should not be but in case)
			onCommit:1 : update from trees_measurement set is_commited = 'true' where upload_id = 'myid'

	trees x
		onCommit:2 : delete from trees where not is_commited and key not in (select key from trees where is_commited)
		onCommit:3 : update from trees set is_commited = 'true' where upload_id = 'myid'

		trees_measurement x
			onValidate : delete from trees_measurement where not is_commited and key not in (select key from trees_measurement where is_commited) --(should not be but in case)
			onCommit:1 : update from trees_measurement set is_commited = 'true' where upload_id = 'myid'

	treatment
		onValidate : delete from treatment where not is_commited and key not in (select key from treatment where is_commited)
		onCommit:5 : update from treatment set is_commited = 'true' where upload_id = 'myid'

	regeneration
		onValidate : delete from regeneration where not is_commited and key not in (select key from regeneration where is_commited) 
		onCommit:6 : update from regeneration set is_commited = 'true' where upload_id = 'myid'

	disturbance
		onValidate : delete from disturbance where not is_commited and key not in (select key from disturbance where is_commited) 
		onCommit:7 : update from disturbance set is_commited = 'true' where upload_id = 'myid'

	regeneration
		onValidate : delete from regeneration where not is_commited and key not in (select key from regeneration where is_commited) 
		onCommit:8 : update from regeneration set is_commited = 'true' where upload_id = 'myid'

	photo_avi_layer
		onValidate : delete from photo_avi_layer where not is_commited and key not in (select key from photo_avi_layer where is_commited) 
		onCommit:9 : update from photo_avi_layer set is_commited = 'true' where upload_id = 'myid'