MIGRATE_DATE_TYPES¶
Description
The stored procedure MIGRATE_DATE_TYPES
automatically scans base views, interface views and complex types and updates them to change columns of type date
(deprecated) to one of the datetime types that was added in Denodo 8.0.
Syntax
GET_VIEWS (
input_database_name : text
, input_process_base_views : boolean
, input_process_interface_views : boolean
, input_process_complex_types : boolean
)
If you provide a non null value for
input_database_name
it will only scan for elements in that database. Please note that updating an element in a database may propagate changes to element in other databases. If you passnull
for this parameter it will update all elements in the server.You can set
input_process_base_views
,input_process_interface_views
andinput_process_complex_types
to false to skip processing that kind of element.The procedure changes the fields with type
date
to a new type in the following way:For JDBC base views, Virtual DataPort looks at the field “sourceTypeName” of the statement
CREATE WRAPPER JDBC
of the base view, and the adapter of the data source of the base view. With this information, it will export the date fields with one of the new types.For non-JDBC base views or derived views, it looks into the property “sourceTypeId” of the field of the
CREATE TABLE
statement:If the property “sourceTypeId” is
DATE
, the field is updated tolocaldate
, which is the name of the type in Denodo to represent date values without time (equivalent to type DATE of SQL).If it is
TIME
, the field is updated totime
.If it is
TIMESTAMP
, the field will remain asdate
(deprecated). There is not enough information to know if the field is timestamp or timestamptz.If the field does not have a source type, the field is remains as
date (deprecated)
.
If there is not enough information to export the field with one of the new date types, the field is exported as
date
. This will occur in non-JDBC base views whose fields do not have the source type id defined, or for fields of derived views that are the result of applying a function.
Each row returned by the procedure has these fields:
database_name
: name of database of the element described in the row.element_name
: name of the element being updated.element_type
: type of the element being updated. “BASE”, “INTERFACE” or “REGISTER”.column_name
: name of the field withdate
type being updated..column_new_type
: the new type assigned to the field.status
: a message describing the result of the process of migrating that field. For example it may just show “OK”, or list the views modified due to changes propagation, or show an error.
Privileges Required
The user that runs this procedure must have administration privileges on all the databases that will be processed.
Examples
Example 1
SELECT *
FROM MIGRATE_DATE_TYPES()
WHERE input_process_base_views = true AND input_process_interface_views = true AND input_process_complex_types = true
Scans all databases to search base views, interface views and complex types with fields with date
and updates them according to the rules described above. Returns a row per column with date
type.
Example 2
SELECT database_name, element_name, element_type, nest(column_name, column_new_type, status)
FROM MIGRATE_DATE_TYPES()
WHERE input_process_base_views = true AND input_process_interface_views = true AND input_process_complex_types = true
GROUP BY database_name, element_name, element_type
Same as before but returning only one row per element. Information about columns of each view / register will be nested in an array.