Details
-
Type:
Bug
-
Status: Done
-
Resolution: Done
-
Fix Version/s: None
-
Component/s: butler
-
Labels:
-
Story Points:1
-
Epic Link:
-
Team:Data Release Production
Description
We had a few issues importing the Butler schema into Oracle that should be relatively easy correct.
Oracle does not have a boolean data type for table columns. The column actual in table Datasetconsumers is a boolean in SQLite. We suggest changing that to varchar2(1) and adding a "actual in ('T','F')"check constraint in Oracle.
"Size" is a reserved word in Oracle. The size column in table posixdatastorerecords will need to be renamed.
"Group" is a reserved word in Oracle. The group column in table detector will need to be renamed.
There is a data type mismatch in constraint Dataset_fk6 between column detector (number) in table detector and column detector (varchar) in table dataset. Data types must match in columns defined in FK/PK constraints.
Attachments
Activity
Jim Bosch I will confirm as soon as I familiarize myself with SQLAlchemy (never used it before) but it looks like booleans will be be converted to some number data type with an additional check constraint by default:
class sqlalchemy.types.Boolean(create_constraint=True, name=None, _create_events=True)
Bases: sqlalchemy.types.Emulated, sqlalchemy.types.TypeEngine, sqlalchemy.types.SchemaType
A bool datatype.
Boolean typically uses BOOLEAN or SMALLINT on the DDL side, and on the Python side deals in True or False.
The Boolean datatype currently has two levels of assertion that the values persisted are simple true/false values. For all backends, only the Python values None, True, False, 1 or 0 are accepted as parameter values. For those backends that don’t support a “native boolean” datatype, a CHECK constraint is also created on the target column. Production of the CHECK constraint can be disabled by passing the Boolean.create_constraint flag set to False.
Assuming that's the case, sticking with DDL generation through SQLAlchemy absolutely makes sense.
I'm to go ahead and get started on everything but the booleans, and just aim to get that done quickly. I figure we can open another issue if we need to do anything further with the booleans.
Michelle Gower, could you take this (hopefully trivial) review?
I'll leave it up to you whether you want to test whether these changes actually fix the issues with using the schema in Oracle, or just wait for them to land on master and open a new ticket later if they don't fix those issues.
Changes are mostly in daf_butler, with a very small change in obs_subaru due to the fact that I've changed "Detector.group" to "Detector.raft", and that changes the meaning as well as the name, making the old value in HSC (which doesn't have rafts) no longer applicable.
Changes are there for size, group, and detector data type. And the Boolean problem should be taken care of by SQLAlchemy.
I'm happy to just fix most of these in the schema YAML file in daf_butler, and will do so as soon as some other tickets land.
For booleans, Christopher Stephens [X], do you know (or could you find out) what SQlAlchemy does when you to tell it to create a table with a boolean in Oracle? If it does something reasonable, I'd like to do that so we can maintain the possibility of using it as a DBMS-abstraction layer in contexts where that's not a performance problem.