Allowing Sequences to be maintained using incremental //// CHANGE scripts

[jgarlick-adesa]

I'm trying to add a Sequence to an Oracle database. Oracle's SQL dialect for Sequences is very much like tables: CREATE SEQUENCE, ALTER SEQUENCE. Without this functionality, I don't know how to manage Sequences with Obevo, which could be a show-stopper for using Obevo.

Expected Behavior

Manage Oracle Sequences like Tables with CHANGE sections to CREATE and ALTER incrementally.

Actual Behavior

If I try to set a //// CHANGE section, Obevo complains that: Unexpected sections found: [CHANGE]; only expecting: [METADATA, BODY, DROP_COMMAND]

Obevo Version where this issue was observed

6.6.0

Steps to reproduce the behavior

• Initialize an Oracle database
• Initialize Obevo
• Create a SEQUENCE file with a CHANGE section
• Try to add an ALTER section inside

[shantstepanian]

We can add support to support sequences in an incremental manner. As of today, sequences are treated like other rerunnable objects like views/sps, which are "drop/create" or "create or replace"

It wouldn't be difficult to add this support, but in the interim, you can use the migration functionality - https://goldmansachs.github.io/obevo/db-project-structure.html#Ad-hoc_data_migrations

In the meantime, can you please send examples of the create/alter sequence operations that you do, for us to add to our test cases?

[jgarlick-adesa]

Hi there.

Thanks for getting back to me. I found that moving the SEQUENCE file to the table/ directory, Obevo thinks it’s a table and is creating it correctly. I’m not sure if I’ll keep it there (or how I'll migrate afterwards), but it is operating correctly for now.

Here’s an example of what I would expect:

File: sequence/PERSON_ID_SEQ.DDL

//// CHANGE name=init
CREATE SEQUENCE "PERSON_ID_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1
GO

//// CHANGE name=reset_increment
ALTER SEQUENCE PERSON_ID_SEQ INCREMENT BY 5
GO

Oracle doesn’t support the ‘CREATE OR REPLACE SEQUENCE’ command.

Thanks!

[shantstepanian]

Sounds good. This should suffice for you for now. You may run into issues if you need to drop this sequence permanently; I'll look to document the workaround for you in case that comes to pass (though I assume it would be less likely to happen)

Will keep this ticket open to account for 1) supporting sequences in an incremental fashion 2) publicly documenting the workaround to remove unneeded table files (your use case isn't the first time we've encountered this)

[jgarlick-adesa]

Something else I just tried is to add the SEQUENCE creation as part of the table it's for. Not my favorite because now we're mixing control of two objects into one file. But it's a little cleaner option than having sequences definition files mixed in with the table definition files in the tables/ directory.

[shantstepanian]

I do like this option a bit better. Still gets tricky on drops, but better than having it as a separate table.

Another option - manage it as a migration; note the "dependencies" attribute usage in MYTABLE. Here, you can manage it as a separate file. Only downside is that migration scripts can be dropped, so this is slightly less safe, but you will get the incremental behavior that you want (and easier to move to the strategic solution of having this in the /sequence folder once that functionality is ready)

/migration/PERSON_IQ_SEQ.sql

//// CHANGE name=init
CREATE SEQUENCE "PERSON_ID_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1
GO

//// CHANGE name=reset_increment
ALTER SEQUENCE PERSON_ID_SEQ INCREMENT BY 5
GO

/table/MYTABLE.sql

//// CHANGE name=init dependencies="PERSON_ID_SEQ.init"
CREATE TABLE MYTABLE ( ... using PERSON_ID_SEQ somehow ...)
GO

//// CHANGE name=reset_increment
ALTER TABLE MYTABLE ...
GO

[shantstepanian]

To confirm the requirements on this:

1. We should allow sequences to be maintained in an incremental fashion, using //// CHANGE entries

2. We should keep backwards-compatibility with existing deployments that already have sequence files defined

3. We should have an easy migration path for folks that want to convert their old rerunnable sequences to the incremental sequences, without having to run the INIT command or anything

4. It would be preferable to keep the incremental managed sequences in the same /sequences folder, but I can be flexible on this depending on the implementation difficulty. Note that keeping it in separate folders (akin to what we did with triggers) may not work as easily here