Duplicate SQL records

[krisvanneste]

Claudio,

I'm experiencing a strange problem with SQLite output. I need to run sourcespec again for a number of events. As I am using the same run_id, I expected the previous results to be overwritten, but instead I now have duplicate entries, as you can see in the screenshot:

However, the code to delete existing records for a given event_id, (station) and run_id is still there, and it should generate an error message if it fails, and exit, but none of that has happened.

Do you have an idea how this is possible? I did manually set the user_version of the database, added the new 'Er_is_outlier' column, and reordered the columns, but I don't understand why this would affect deletion of records, but not insertion...

[krisvanneste]

I failed to notice there has been a change in ssp_sqlite_output.py. I reverted to the commit before, and it seems to work again now. It is possible the problem occurs because my database does not have the primary keys, as it was created before this change.

[claudiodsf]

Hi Kris,

yes, the new code uses primary keys and INSERT OR REPLACE construct to avoid pre-deleting table rows.

I just made commit 8e3442dc97f097ed00386b6af3e20040cf740c5b, which introduces a new command:

 source_spec --updatedb

Could you try using it to migrate an old database (DB_VERSION=1) to a new one?

[krisvanneste]

I used the update_db_file function to update my database file, and it worked. The Stations and Events tables have primary keys now.

[claudiodsf]

Great! Guess I can close this issue, then.

[krisvanneste]

Yes!