Infrastructure to produce .db files

[humnaawan]

Is there any functionality to save a modified version of the input sqlite database (specifically, e.g., just the Summary table)? I haven't found anything aside from the lsst sims_operations afterburner, which isnt runnable by MAF users. The only way to go about saving a modified .db file is to either get sql queries to work (which I couldn't .. ) or use csv/pandas to do intermediate steps (which probably isnt the best way).

[rhiannonlynne]

Something to save just the summary table, after some stackers have been run to add additional columns? Not really, but I agree it could be useful.
I'm not sure what you mean about getting sql queries to work? Can you explain?

[humnaawan]

Yes, so the stackers add columns on the fly but you cant really save the effectively-updated database as a whole. Essentially, what I am thinking is a way to create .db files with modified version of the OpSim database, which can be read like a normal OpSim output.

What I tried to do with sql was to create a copy of the opsim ouput (sqlite_file_copy), read it in, and try to add columns using sql, doing something like:
conn = sqlite3.connect(sqlite_file_copy)
c = conn.cursor()
for i, obsID in enumerate(simdata['obsHistID']): c.execute('UPDATE Summary SET newCol= %s'%(newVal, obsID))
conn.commit()
conn.close()

I tried using executemany but it wasnt doing anything at all and execute was taking too long (as I had to loop over all the obsHistIDs; it was changing values though).

Please let me know if I am not explaining things well.

[rhiannonlynne]

We'll put this on the list as well.
It does raise one issue however, of how to track the provenance for the new information -- the details of how the extra data was added. Also, how to keep track of what version of a database run is what (i.e. if you run a stacker two different times with different parameters and save the output -- which one did your collaborator receive?).

[humnaawan]

Great, thank you!

It does raise one issue however, of how to track the provenance for the new information -- the details of how the extra data was added. Also, how to keep track of what version of a database run is what (i.e. if you run a stacker two different times with different parameters and save the output -- which one did your collaborator receive?).

Yeah, that's true. Can we add a table to the sqlite database? I am thinking perhaps something similar to the Summary table, but with version number etc. That way, a summary of the added changes could be required by the user before the new database can be saved?