Cross Schema Dependencies

[IvanAtanasov]

Another issue we are having is cross schema dependencies. Reading the documentation this is supposed to work so could be user error.

For example

schema_one/table/my_table.sql

//// CHANGE name=create
CREATE TABLE my_table (
  col1  INT2 NOT NULL,
  col2 INT2 NOT NULL
)
GO

//// CHANGE name=foreign_key
ALTER TABLE my_table
  ADD CONSTRAINT foreign_key_example
FOREIGN KEY (col1)
REFERENCES schema_two.linked_table (col1)
MATCH SIMPLE
GO

schema_two/table/linked_table.sql

//// CHANGE name=create
CREATE TABLE linked_table (
  col1  INT2 NOT NULL,
  col2 INT2 NOT NULL
)
GO

I would expect when deploying this, obevo would find the dependency and therefore execute the linked_table creation first. This does not happen though.

Both schemas are included in the system-config.xml like this:

    <schemas>
        <schema name="schema_one"/>
        <schema name="schema_two"/>
    </schemas>

However it does seem to work when both tables are in the same schema.

Thanks, Ivan

[shantstepanian]

Hi Ivan, You are correct that the automatic discovery from text only works if it is in the same schema, and not across schemas.

For now, you can enforce the order using the "includeDependencies" attribute per the doc. Example below: //// CHANGE name=foreign_key includeDependencies=schema_two.linked_table.create

You can also depend on the object itself; for tables, this will default to the last change in the file: //// CHANGE name=foreign_key includeDependencies=schema_two.linked_table

Note - use the logical schema name when declaring the dependency, not the physical. i.e. if you map schema_one to schema_one_dev in one of your environments, your code should still refer to the dependency as schema_one.mytable

I will keep this issue open to track the feature for supporting dynamic cross schema dependencies.

[IvanAtanasov]

Hi,

Just got back to looking this and have tried your suggestion.

Unfortunately even explicitly adding the dependency as described does not work. The table with the dependency is still created before the other tables.

Any more ideas? Or could I be doing something wrong.

Thanks, Ivan

[shantstepanian]

I will try this on my side and get back to you

[shantstepanian]

The "includeDependencies" attribute should ideally work, but it looks like a bug on our side; I will look into that.

In the meantime, use the "dependencies" attribute

//// CHANGE name=foreign_key dependencies=schema_two.linked_table
ALTER TABLE my_table
  ADD CONSTRAINT foreign_key_example
FOREIGN KEY (col1)

[shantstepanian]

Updating the TODO for this ticket

• Implement this cross-schema dependency discovery, notably in TextDependencyExtractor
• Ensure that the explicit dependencies / includeDependencies have unit tests (notably for GraphEnricherImpl). I believe the includeDependencies issue in the last comment has been fixed; just need a double-check on the tests