Schema change in modeled parameters

[kaseyLee123]

I currently am trying to make changes in the modeled parameters table: 1) removing value_error and replacing it with upper_error and lower_error 2) adding a model column

However, there has already been data ingested under this table so I get an error when running my script/loading the db since the data that has been ingested doesn't have the new changes.

Issue appeared in #518

[kaseyLee123]

error message:

Traceback (most recent call last):
  File "/opt/miniconda3/envs/simple-db/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context
    self.dialect.do_execute(
    ~~~~~~~~~~~~~~~~~~~~~~~^
        cursor, str_statement, effective_parameters, context
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/opt/miniconda3/envs/simple-db/lib/python3.13/site-packages/sqlalchemy/engine/default.py", line 942, in do_execute
    cursor.execute(statement, parameters)
    ~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^
sqlite3.IntegrityError: NOT NULL constraint failed: ModeledParameters.model

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Users/kasey/Documents/GitHub/SIMPLE-db/scripts/ingests/sanghi23/ingest_modeled_parameters.py", line 27, in <module>
    db = load_astrodb("SIMPLE.sqlite", recreatedb=RECREATE_DB, reference_tables=REFERENCE_TABLES, felis_schema=SCHEMA_PATH)
  File "/opt/miniconda3/envs/simple-db/lib/python3.13/site-packages/astrodb_utils/utils.py", line 82, in load_astrodb
    db.load_database(data_path)
    ~~~~~~~~~~~~~~~~^^^^^^^^^^^
  File "/opt/miniconda3/envs/simple-db/lib/python3.13/site-packages/astrodbkit/astrodb.py", line 1033, in load_database
    self.load_json(os.path.join(directory_of_sources, file))
    ~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/miniconda3/envs/simple-db/lib/python3.13/site-packages/astrodbkit/astrodb.py", line 975, in load_json
    conn.execute(self.metadata.tables[key].insert().values(temp_dict))
    ~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/miniconda3/envs/simple-db/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1416, in execute
    return meth(
        self,
        distilled_parameters,
        execution_options or NO_OPTIONS,
    )
  File "/opt/miniconda3/envs/simple-db/lib/python3.13/site-packages/sqlalchemy/sql/elements.py", line 516, in _execute_on_connection
    return connection._execute_clauseelement(
           ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
        self, distilled_params, execution_options
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/opt/miniconda3/envs/simple-db/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1638, in _execute_clauseelement
    ret = self._execute_context(
        dialect,
    ...<8 lines>...
        cache_hit=cache_hit,
    )
  File "/opt/miniconda3/envs/simple-db/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1843, in _execute_context
    return self._exec_single_context(
           ~~~~~~~~~~~~~~~~~~~~~~~~~^
        dialect, context, statement, parameters
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/opt/miniconda3/envs/simple-db/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1983, in _exec_single_context
    self._handle_dbapi_exception(
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
        e, str_statement, effective_parameters, cursor, context
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/opt/miniconda3/envs/simple-db/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 2352, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/opt/miniconda3/envs/simple-db/lib/python3.13/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context
    self.dialect.do_execute(
    ~~~~~~~~~~~~~~~~~~~~~~~^
        cursor, str_statement, effective_parameters, context
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/opt/miniconda3/envs/simple-db/lib/python3.13/site-packages/sqlalchemy/engine/default.py", line 942, in do_execute
    cursor.execute(statement, parameters)
    ~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) NOT NULL constraint failed: ModeledParameters.model
[SQL: INSERT INTO "ModeledParameters" (source, parameter, value, lower_error, upper_error, unit, comments, reference) VALUES (?, ?, ?, ?, ?, ?, ?, ?)]
[parameters: ('2MASS J09373487+2931409', 'T eff', 881.0, 74.0, 74.0, 'K', None, 'Fili15')]
(Background on this error at: https://sqlalche.me/e/20/gkpj)

[kaseyLee123]

For changes in value_error, I added some code in the load db function:

                    if "value_error" in temp_dict:
                        temp_dict["upper_error"] = temp_dict["value_error"]
                        temp_dict["lower_error"] = temp_dict["value_error"]
                        del temp_dict["value_error"]

which was an easy fix since the upper and lower would just be the same as whatever the value_error was but for model it would not be this easy since there were a good amount of sources ingested and I would not know which model is which

[kaseyLee123]

pr with my code:
#655

[kelle]

@dr-rodriguez , we need you for this. Not sure how to modify the schema now that we're using Felis.

[dr-rodriguez]

I'll take a look at the PR later when I get a chance, but the steps to modify schema are the same as they were pre-Felis:

1. Load the database and data as-is
2. Do ALTER TABLE commands to modify the tables/columns as needed
3. Save the database back to JSON
4. Make the modifications to the schema (yaml file this time)
5. Reload the database

There's an old example here: https://github.com/SIMPLE-AstroDB/SIMPLE-db/blob/main/scripts/updates/update_spectra_colnames.py

[kelle]

Thanks David!

Are there other ways to alter the tables/columns once the DB file is created? E.g. could one use the SQLLite Browser or dbWeaver or some other database tool? (It does look like the SQLite Browser has a Modify Table function.)

[kelle]

Also, open PR to add this information to the documentation. astrodbtoolkit/astrodb_utils#194

[kaseyLee123]

I'll take a look at the PR later when I get a chance, but the steps to modify schema are the same as they were pre-Felis:

1. Load the database and data as-is
2. Do ALTER TABLE commands to modify the tables/columns as needed
3. Save the database back to JSON
4. Make the modifications to the schema (yaml file this time)
5. Reload the database

There's an old example here: https://github.com/SIMPLE-AstroDB/SIMPLE-db/blob/main/scripts/updates/update_spectra_colnames.py

Thank you this worked!

[kelle]

There is now a section in the docs dedicated to this issue:
https://astrodb-utils.readthedocs.io/en/latest/pages/modifying/new_columns.html