Database versioning is needed

[jtniehof]

Work over the past few years has resulted in some small changes to the database structure. Right now there is code that allows everything to work with old or new databases, but at some point the structure should firm up and be assigned a version number, so that the structure of the database can be known (and eventually code to support old structures removed.)

Changes that I can recall:

• New columns in mission table to support explicit definition of code directory, inspector directory, and error directory
• New columns in productprocesslink to specify days in past/future of input products
• Something involving the newest_version flag; either getting rid of it or re-adding it, will need to look into it. (EDIT: Looks like it has been deleted in the latest codebase, so it is well and truly gone and should remain that way.)
• Looks like there used to be a not-null constraint on Inspector arguments which has been lifted.
• File unique (#6)
• Unix time table (#31). This has support in the migration script.
• Longer pathnames supported for rootdir, incoming_dir, codedir, inspectordir, errordir in Mission table (250 vs. 50 characters) (#78)
• Longer pathnames for relative_path in Product and Code tables (250 vs. 100 characters) (#78)
• (Not yet implemented) Other name lengths and similar (#107)

Closure condition

This issue should be closed when the database versioning has been defined (e.g. what constitutes an increment, what is current, where is the version stored) and code is available to migrate earlier versions of database to current version, compatible at least with postgres and sqlite.

[jtniehof]

I guess one question is whether we want to version the entire database, or table-by-table. I think the entire db is probably easier.

[balarsen]

whole thing works for me... way easier to keep track

[jtniehof]

Whole thing it seems to be. I think we can have an arg to the DButils __init__ specifying a minimum version and then have migration scripts...so once we define v.1, we have a script that migrates v. 0 to v. 1 and then the __init__ defaults to requiring v. 1, and we can rip out a lot of the peanut butter.

[jtniehof]

Migration scripts might also want to fix types on databases where sqlite doesn't enforce typing...there are a lot of booleans that were treated as 0/1 in the code at some point and that has almost certainly leaked into database. Similarly for empty string vs. NULL.