Denodo provides a stored procedure call “SOURCE_CHANGES” that detects the differences between the current schema of a base view and its underlying data source. The syntax is:
SOURCE_CHANGES ( base view name : text )
This stored procedure returns a row for each field of the view and also a row for each field that is present in the data source, but not in the base view. If a field is an array or a register, there will also be a row for each one of its subfields.
Sometimes, if the virtual data base has a lot of base views and the data source schemas are changing quite frequently, the manual execution of this stored procedure for every base view can be costly.
It is possible to combine the SOURCE_CHANGES and CATALOG_VIEWS stored procedures to detect which views have changed
- Create a derived view called ‘base_views‘ using the CATALOG_VIEWS stored procedure which returns the list of base views. This is the VQL code to create the view:
CREATE OR REPLACE VIEW base_views AS SELECT * FROM CATALOG_VIEWS() WHERE viewtype = 0;
The viewtype = 0 condition guarantees that the CATALOG_VIEWS stored procedure only returns views that are base views.
- Create a derived view ‘source_changes‘ from the SOURCE_CHANGES stored procedure. This is the VQL code to create the view:
CREATE OR REPLACE VIEW source_changes AS SELECT * FROM SOURCE_CHANGES();
Notice that to execute the view an input parameter is required, which is the name of the base view.
- Finally, create a nested join between the ‘base_view‘ derived view and the ‘source_changes‘ derived view. The join condition will be:
base_views.resultname = source_changes.tablename ;
In the join, the “base_views” view provides the base view names to execute the “source_changes” view.