Repository navigation
Database versioning is needed #7
Description
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 (Fix file table unique constraint #6)
- Unix time table (Speed up getFiles by start/stop time (Closes #23) #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) (add postgresql support and unit testing #78)
- Longer pathnames for relative_path in Product and Code tables (250 vs. 100 characters) (add postgresql support and unit testing #78)
- (Not yet implemented) Other name lengths and similar (Review database column types and sizes #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.
Activity
jtniehof commentedon Sep 29, 2020
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 commentedon Sep 29, 2020
whole thing works for me... way easier to keep track
jtniehof commentedon Oct 5, 2020
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 commentedon Jan 19, 2022
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.