Uploaded image for project: 'Data Management'
  1. Data Management
  2. DM-16810

Butler schema changes to run on Oracle

    XMLWordPrintable

Details

    • Bug
    • Status: Done
    • Resolution: Done
    • None
    • butler

    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

          jbosch Jim Bosch added a comment -

          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, cs2018, 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.

          jbosch Jim Bosch added a comment - 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, cs2018 , 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.

          jbosch 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=Truename=None, _create_events=True)

          Bases: sqlalchemy.types.Emulatedsqlalchemy.types.TypeEnginesqlalchemy.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 NoneTrueFalse1 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.

           

          cs2018 Christopher Stephens [X] (Inactive) added a comment - jbosch  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.  
          jbosch Jim Bosch added a comment -

          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.

          jbosch Jim Bosch added a comment - 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.
          jbosch Jim Bosch added a comment -

          mgower, 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.

          jbosch Jim Bosch added a comment - mgower , 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.

          mgower Michelle Gower added a comment - Changes are there for size, group, and detector data type.   And the Boolean problem should be taken care of by SQLAlchemy.

          People

            jbosch Jim Bosch
            cs2018 Christopher Stephens [X] (Inactive)
            Michelle Gower
            Christopher Pond [X] (Inactive), Christopher Stephens [X] (Inactive), Jim Bosch, Michelle Gower
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Jenkins

                No builds found.