Cacade Delete for Timeseries Group?

[msweier]

Is it possible to get a cascade delete option for the timeseries group similar to the location group? Otherwise it's not possible to delete a timeseries category with CDA.

[rma-rripken]

Its not there b/c the pl/sql package routine for delete_ts_group didn't have the additional delete method flag like the delete_loc_group method had. Its an ugly work-around but you should be able to call an update for the ts_group with an empty assignment list and then delete the group after. I think you meant to write timeseries group in your last sentence. If you really meant timeseries category - there is a separate end-point for managing timeseries categories and that endpoint has a cascade-delete option - hopefully that works.

[rma-rripken]

We should change the pl/sql method to add the necessary flag for ts_group delete. In the meantime the cda ts_group delete method could check cascade-delete and if its present do the update then delete.

[krowvin]

I believe this issue is related to something we saw today in the CDA call.

We were note able to delete a timeseries group, or category via DELETE because it had TSID assigned to it.

The error was:
org.jooq.exception.DataAccessException: SQL [{ call "CWMS_20"."CWMS_TS"."DELETE_TS_CATEGORY" (?, ?, ?) }]; ORA-20998: ERROR: Cannot delete time series category monthly because it is not empty.

Attempting to POST back to the same timeseries category with an empty array also would not clear the assigned timeseries. (Vein attempt)

There does exist a cascade option in the timeseries category endpoint, but this only deletes the group of the category and does not go down into the assigned TS.

[rma-psmorris]

Per the 2026-01-06 CWMS DB meeting: CWMS Cascade delete support will be added in the database via a ticket to be named then supported here within CDA.

[rma-rripken]

It sounds like there are several issues being reported here

1. TimeSeriesCategory delete end-point with cascade-delete=true is failing "ERROR: Cannot delete time series category monthly because it is not empty."

2. TimeSeriesGroup delete does not have a cascade-delete option

3. " POST back to the same timeseries category with an empty array also would not clear the assigned timeseries"
   Looking at the code related to item 4 I think that if the ts_category provided to the create call includes ts_groups and assignments they are just ignored. Either we should create the embedded groups or we should throw an error if the user-provided body includes them. That way we won't give the impression that its handled if it isn't.

Possibly also 4) TimeSeriesCategory does not have an update method.

I still think we could have CDA implement a workaround for the missing cascade-delete options while we wait for the db changes.

[rma-rripken]

I've started a pr here HydrologicEngineeringCenter/cwms-database#101 the db build is failing but I haven't yet figured out why