Outline of changes for joining 'on-the-fly' detection validity

[jkburges]

[WIP]
An outline of TODOs for this work.

Ref: #56 and #72

In no particular order:

• have a single detections table in the DB
• views corresponding to valid_detection and invalid_detection
• receiver download file reporting (e.g. valid, invalid counts)
• before and after:
  • number of (in)valid detections
  • size on disk
• drop receiver_valid_detection (I don't think this is used)

Questions:

• in app or in DB validation?
• migration or reload?
• make the corresponding change to ReceiverEvent?

[jkburges]

Before:

select count(*) from aatams.valid_detection - 60785805
select count(*) from aatams.invalid_detection - 37407543
postgres@3-aws-syd:/mnt/ebs/db/aatams$ du -sh . - 31G

[jkburges]

Stats:

https://gist.github.com/jkburges/a2aae361878e7db8924a

[jkburges]

Prod deploy:

1. (App) touch /tmp/detections_loading
2. (Jenkins) disable chef for 3-nsp-mel
3. (App) sudo service tomcat7_aatams stop
4. (DB) backup DB
5. (App -> DB) Copy CSV files to DB, unzip etc.
6. (DB) apt-get install dos2unix
7. ([JK]enkins) upgrade aatams_systest -> aatams_prod, in jenkins
8. (DB) Install and run load script (this took 172m on 3-aws-syd) (see https://raw.githubusercontent.com/aodn/aatams/master/grails-app/migrations/load_all_dets.sh)
9. (DB) apt-get purge dos2unix
10. (Jenkins) enable chef for 3-nsp-mel
11. (DB) remove load script
12. (DB) remove CSV files
13. (Pub) Celebrate

[jkburges]

For the duplicates work, here are the biggest receiver downloads:

count	receiver_download_id
4464919	117842635
3469427	5745659
3469427	7658857
3354258	39584261
2916572	113442245
2342303	7257676
1980629	93016360
1937174	102559144
1937174	100621931
1190168	71881452
1190168	66889894

Query:

select count(receiver_download_id), receiver_download_id
from (select * from detection) d
group by receiver_download_id 
order by count(receiver_download_id) desc
limit 50