correct way to use .alter to update element event.Event datatype

[trose-neuro]

Hi all,

As discussed on slack, I’m currently facing a quantization issue with the old Elements event.Event schema: the event_start_time and event_end_time attributes are quantized to ~10 ms for times exceeding 1000s because the datatype is float.

I see that decimal(10,4) was introduced in a later version of event.py.

We therefore have to change the datatype safely post-hoc in our rather large database. However, running on test data, I fail to get the .alter method to run.

What’s the correct way to use .alter() here? event_start_time is a primary key.

thanks,
Tobias

---

What I tried

I changed the event.Event schema definition from float to decimal(10,4):

@schema
class Event(dj.Imported):
    definition = """
    -> BehaviorRecording
    -> EventType
    event_start_time          : decimal(10, 4)
    ---
    event_end_time=null       : float
    """

Then loaded an old test database

schema = dj.Schema('tobiasrext_event')
schema.spawn_missing_classes()

Event.describe() shows float as it should:
'-> BehaviorRecording\n-> EventType\nevent_start_time : float # (second) relative to recording start\n---\nevent_end_time=null : float # (second) relative to recording start\n'

However, restarting the kernel and then using Event.alter() yields

---------------------------------------------------------------------------
NotImplementedError                       Traceback (most recent call last)
Cell In[9], [line 1](vscode-notebook-cell:?execution_count=9&line=1)
----> [1](vscode-notebook-cell:?execution_count=9&line=1) Event.alter()

File ~/miniconda3/envs/datajoint/lib/python3.8/site-packages/datajoint/table.py:135, in Table.alter(self, prompt, context)
    133     del frame
    134 old_definition = self.describe(context=context)
--> [135](https://vscode-remote+ssh-002dremote-002b7b22686f73744e616d65223a22425554227d.vscode-resource.vscode-cdn.net/home/backup_user/adamacs/notebooks/~/miniconda3/envs/datajoint/lib/python3.8/site-packages/datajoint/table.py:135) sql, external_stores = alter(self.definition, old_definition, context)
    136 if not sql:
    137     if prompt:

File ~/miniconda3/envs/datajoint/lib/python3.8/site-packages/datajoint/user_tables.py:105, in UserTable.definition(self)
    100 @property
    101 def definition(self):
    102     """
    103     :return: a string containing the table definition using the DataJoint DDL.
    104     """
--> [105](https://vscode-remote+ssh-002dremote-002b7b22686f73744e616d65223a22425554227d.vscode-resource.vscode-cdn.net/home/backup_user/adamacs/notebooks/~/miniconda3/envs/datajoint/lib/python3.8/site-packages/datajoint/user_tables.py:105)     raise NotImplementedError(
    106         'Subclasses of Table must implement the property "definition"'
    107     )

NotImplementedError: Subclasses of Table must implement the property "definition"

[dimitri-yatsenko]

Can you try it the other way around:

schema = dj.Schema('tobiasrext_event')
schema.spawn_missing_classes()

@schema
class Event(dj.Imported):
    definition = """
    -> BehaviorRecording
    -> EventType
    event_start_time          : decimal(10, 4)
    ---
    event_end_time=null       : double
    """

Event.alter()

[trose-neuro]

Great - this sequence works. Thanks - But see below.

[trose-neuro]

It only changed event_end_time, not the event_start_time - which is a primary key.
This is expected, I guess: https://docs.datajoint.com/core/datajoint-python/latest/design/alter/

What would be the way forward?

Create new schema with desired types, copy data over, drop Event table, rename new schema? Do this for all dependent tables (trial.TrialEvent etc)?

Or ALTER TABLE / MODIFY COLUMN in SQL directly?

[dimitri-yatsenko]

You can issue the SQL command directly. If there are tables below that depend on it, then you may have to first disable foreign keys, then alter all the tables that use the altered field, and then re-enable the foreign key constraint.

[trose-neuro]

Thanks. Will backup and also try on test data first. This will likely also take a while for .5 Billion events...

[trose-neuro]

Done. For future reference, this is what I did and where I initially struggled:

• make sure to take database offline and prevent any external access (from users and workers). Otherwise the conversion will be blocked.
• make sure to have enough headroom on database drive. Both for increased table size and temp data used during conversion (when using ALGORITHM=COPY)

-- Extend lock wait timeout so ALTER has time
SET SESSION innodb_lock_wait_timeout = 1800;

-- 1) Drop FK on child
ALTER TABLE `roselab_trial`.`_trial_event`
  DROP FOREIGN KEY `_trial_event_ibfk_2`;

-- 2) Alter parent table
ALTER TABLE `roselab_event`.`_event`
  MODIFY COLUMN `event_start_time` DECIMAL(11,5) NOT NULL,
  ALGORITHM=COPY;

-- 3) Alter child table
ALTER TABLE `roselab_trial`.`_trial_event`
  MODIFY COLUMN `event_start_time` DECIMAL(11,5) NOT NULL,
  ALGORITHM=COPY;

-- 4) Recreate FK
ALTER TABLE `roselab_trial`.`_trial_event`
  ADD CONSTRAINT `_trial_event_ibfk_2`
  FOREIGN KEY (`session_id`,`scan_id`,`event_type`,`event_start_time`)
  REFERENCES `roselab_event`.`_event`
             (`session_id`,`scan_id`,`event_type`,`event_start_time`)
  ON UPDATE CASCADE ON DELETE RESTRICT;

For the event_end_time I used datajoint's .alter() as above.

note: decimal(11,5) is overkill (11.57 d at 10us resolution) but was necessary since we had bursty lick events that led to key duplication at decimal(10,4).