GET_SOURCE_CHANGES

Description

The stored procedure GET_SOURCE_CHANGES detects the differences between the schema of a base view and its underlying data source.

For example, for a JDBC base view it detects the differences between the schema of the table in the database and the schema of the base view.

Syntax

GET_SOURCE_CHANGES (
      database_name : text
    , table_name : text
)
  • database_name: name of the Denodo database to which table_name belongs. If null, it looks for table_name in the current database.
  • table_name: name of the base view.

The procedure returns a row for each field of the view and also a row for each field that is present in the source, but not in the base view.

If a field is an array or a register, it also returns one row for each one of its subfields.

The output schema of this procedure has the following fields:

  • field: name of the field in the view. If the row represents a new field, it is the name of the field in the source.
  • type: new type of the field. If the field has not changed, this value is the same as “old_type”. If the field has been removed from the source, this value is empty.
  • old_type: old type of the field. If the type of the field has not changed, this value is the same as “type”. If the row represents a new field, this value is empty.
  • modification: If it is empty, the field has not changed. Otherwise, it has one of these values:
    • “New field”: when the row represents a field that has been added to the source.
    • “Deleted field”: when the row represents a field that has been deleted from the source.
    • “Type has changed”: the type of the field has changed.
    • “Properties have changed”: one or more “source type properties” of the field have changed.
    • “Compound structure has changed”: one of the subfields of this field has changed.
  • level: For first-level fields, the value is 1.

Remarks

  • By default, this procedure returns an error if it does not find the source. For example, it returns an error if table_name is a JDBC base view and the table/view was deleted from the underlying database. Or if table_name is a JSON base view and the JSON file does not exist, etc.

    If you do not want the procedure to fail when the source does not exist, execute the following statement from the VQL Shell (you need to be an administrator):

    SET 'com.denodo.vdb.contrib.storedprocedure.SourceChangesProcedure.errorsAsResults'='true'
    

    Restart the Virtual DataPort server to apply the change.

    With this change, the procedure will return only one row, instead of one per field, and the modification field will hold the error.

  • This procedure returns an error if table_name is not a base view.

  • This procedure return an error if database_name or table_name do not exist.

Privileges Required

Only users that have the Read privilege on the database of the view can execute this procedure. This means, the following users can execute this procedure:

  • Administrators or administrators of this database.
  • Users that have the Connect and Read privileges on this database.

Example

Let us suppose that internet_inc is a JDBC base view and that the current schema of its table in the database has changed since the base view was created:

  • The table has a new field CUSTOMER_ID
  • The field SPECIFIC_FIELD1 was removed from the table
  • The type of the field TTIME has changed to text
CALL SOURCE_CHANGES('internet_inc')

The result of invoking the previous query will be the following:

field type old_type modification depth
IINC_ID long long   1
SUMMARY text text   1
TTIME text date Type has changed 1
TAXID text text   1
SPECIFIC_FIEL D1   text Deleted Field 1
CUSTOMER_ID long   New Field 1