Transfer Existing Empherides Data to Dev DB

[acviana]

Now that we've build a new development database and populated the master_images table we can look at salvaging some of the data in the master_finders table of the original database dump I sent you.

As you showed in #114, not all of the filenames in the original database dumps are valid records in the new database (some of the them are old file formats we no longer use). So, write a query that will transfer the only the records in the production master_finders table to the development master_finders table if the parent record in production master_images table is in the dev master_images table.

[walyssonBarbosa]

Here's the query I used to migrate the data from the first dump to the new one:

mysql> insert into mtpipeline_dev.master_finders (id, object_name, master_images_id, ephem_x, ephem_y, version, jpl_ra, jpl_dec, diameter, magnitude) 
select mtpipe.master_finders.id, mtpipe.master_finders.object_name, mtpipe.master_finders.master_images_id, mtpipe.master_finders.ephem_x, mtpipe.master_finders.ephem_y, mtpipe.master_finders.version, mtpipe.master_finders.jpl_ra, mtpipe.master_finders.jpl_dec, mtpipe.master_finders.diameter, mtpipe.master_finders.magnitude 
from mtpipe.master_finders inner join mtpipeline_dev.master_images 
on mtpipe.master_finders.master_images_id = mtpipeline_dev.master_images.id;
Query OK, 281554 rows affected (3.06 sec)
Records: 281554  Duplicates: 0  Warnings: 0

[walyssonBarbosa]

This doesn't work. Although we don't have those files anymore, we still have a record with the same id for another file. So I will try to use name instead of id

[walyssonBarbosa]

Here's the new query I did:

mysql> INSERT INTO mtpipeline_dev.master_finders (
    id,
    object_name,
    master_images_id,
    ephem_x,
    ephem_y,
    version,
    jpl_ra,
    jpl_dec,
    diameter,
    magnitude)
SELECT mtpipe_test.master_finders.id,
    mtpipe_test.master_finders.object_name,
    mtpipe_test.master_finders.master_images_id,
    mtpipe_test.master_finders.ephem_x,
    mtpipe_test.master_finders.ephem_y,
    mtpipe_test.master_finders.version,
    mtpipe_test.master_finders.jpl_ra,
    mtpipe_test.master_finders.jpl_dec,
    mtpipe_test.master_finders.diameter,
    mtpipe_test.master_finders.magnitude
FROM mtpipe_test.master_finders, 
    mtpipe_test.master_images, 
    mtpipeline_dev.master_images 
WHERE mtpipe_test.master_finders.master_images_id = mtpipe_test.master_images.id 
AND mtpipe_test.master_images.name = mtpipeline_dev.master_images.name;
Query OK, 212022 rows affected (2.68 sec)
Records: 212022  Duplicates: 0  Warnings: 0

I checked the number of files that are not being used anymore and it's correct. There are 69532 records not being used anymore in master_finders that were not migrated to mtpipeline_dev.

[acviana]

:+1:

This is great!

[walyssonBarbosa]

So, I remigrated the old data from master_finders to our new database using this sql query:

INSERT INTO mtpipeline_dev.master_finders (
    id,
    object_name,
    master_images_id,
    ephem_x,
    ephem_y,
    version,
    jpl_ra,
    jpl_dec,
    diameter,
    magnitude)
SELECT mtpipe_test.master_finders.id,
    mtpipe_test.master_finders.object_name,
    mtpipeline_dev.master_images.id,  # changed this line
    mtpipe_test.master_finders.ephem_x,
    mtpipe_test.master_finders.ephem_y,
    mtpipe_test.master_finders.version,
    mtpipe_test.master_finders.jpl_ra,
    mtpipe_test.master_finders.jpl_dec,
    mtpipe_test.master_finders.diameter,
    mtpipe_test.master_finders.magnitude
FROM mtpipe_test.master_finders, 
    mtpipe_test.master_images, 
    mtpipeline_dev.master_images 
WHERE mtpipe_test.master_finders.master_images_id = mtpipe_test.master_images.id 
AND mtpipe_test.master_images.name = mtpipeline_dev.master_images.name;

It altered the same number of rows as before, I think it worked:

Query OK, 212022 rows affected (2.45 sec)
Records: 212022  Duplicates: 0  Warnings: 0

And I did this sql query to check if the data from mars was still mixed with neptune and it's not anymore:

mysql> select master_finders.object_name from master_finders, master_images where master_finders.master_images_id = master_images.id and master_images.fits_file = "u2j30302t_cr_c0m_center_single_sci.fits";
+-------------+
| object_name |
+-------------+
| triton      |
| nereid      |
| psamathe    |
| sao         |
| thalassa    |
| laomedeia   |
| naiad       |
| halimede    |
| neptune     |
| despina     |
| neso        |
| proteus     |
| galatea     |
| larissa     |
+-------------+
14 rows in set (0.00 sec)