{Denodo}IFV作成

https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/defining_a_derived_view/defining_an_interface_view/defining_an_interface_view

-- 1. ソースDBでテストテーブル作成

drop table if exists tab1;

create table tab1(
  col1 BIGINT not null
, col2 DECIMAL(5,2)
, col3 CHAR(100)
, col4 VARCHAR(100)
, col5 DATETIME
, col6 BINARY(3)
, col7 VARBINARY(3)
);

-- 2. BV作成

select creation_vql 
from GENERATE_VQL_TO_CREATE_JDBC_BASE_VIEW()
where data_source_name = 'ds01'
and catalog_name = 'test'
and table_name = 'tab1'
and base_view_name = 'b_tab1'
and folder ='/02bv'
and database_name = 'db01'
;

CREATE OR REPLACE WRAPPER JDBC b_tab1
    FOLDER = '/02bv'
    DATASOURCENAME=db01.ds01
    CATALOGNAME='test' 
    RELATIONNAME='tab1' 
    OUTPUTSCHEMA (
        col1 = 'col1' :'java.lang.Long' (OPT) (sourcetyperadix='10', sourcetypesize='19', description='', sourcetypeid='-5', sourcetypename='BIGINT')  NOT NULL SORTABLE,
        col2 = 'col2' :'java.math.BigDecimal' (OPT) (sourcetypedecimals='2', sourcetyperadix='10', sourcetypesize='5', description='', sourcetypeid='3', sourcetypename='DECIMAL')  SORTABLE,
        col3 = 'col3' :'java.lang.String' (OPT) (sourcetyperadix='10', sourcetypesize='100', description='', sourcetypeid='1', sourcetypename='CHAR')  SORTABLE,
        col4 = 'col4' :'java.lang.String' (OPT) (sourcetyperadix='10', sourcetypesize='100', description='', sourcetypeid='12', sourcetypename='VARCHAR')  SORTABLE,
        col5 = 'col5' :'java.time.LocalDateTime' (OPT) (sourcetyperadix='10', sourcetypesize='19', description='', sourcetypeid='93', sourcetypename='DATETIME')  SORTABLE,
        col6 = 'col6' :'java.sql.Blob' (OPT) (sourcetyperadix='10', sourcetypesize='3', description='', sourcetypeid='-2', sourcetypename='BINARY')  NOT SORTABLE,
        col7 = 'col7' :'java.sql.Blob' (OPT) (sourcetyperadix='10', sourcetypesize='3', description='', sourcetypeid='-3', sourcetypename='VARBINARY')  NOT SORTABLE
    );
CREATE OR REPLACE TABLE b_tab1 I18N jp (
        col1:long (notnull, sourcetyperadix = '10', sourcetypesize = '19', description = '', sourcetypeid = '-5'),
        col2:decimal (sourcetypedecimals = '2', sourcetyperadix = '10', sourcetypesize = '5', description = '', sourcetypeid = '3'),
        col3:text (sourcetyperadix = '10', sourcetypesize = '100', description = '', sourcetypeid = '1'),
        col4:text (sourcetyperadix = '10', sourcetypesize = '100', description = '', sourcetypeid = '12'),
        col5:timestamp (sourcetyperadix = '10', sourcetypesize = '19', description = '', sourcetypeid = '93'),
        col6:blob (sourcetyperadix = '10', sourcetypesize = '3', description = '', sourcetypeid = '-2'),
        col7:blob (sourcetyperadix = '10', sourcetypesize = '3', description = '', sourcetypeid = '-3')
    )
    FOLDER = '/02bv'
    CACHE OFF
    TIMETOLIVEINCACHE DEFAULT
    ADD SEARCHMETHOD b_tab1(
        I18N jp
        CONSTRAINTS (
             ADD col1 (ANY) OPT ANY
             ADD col2 (ANY) OPT ANY
             ADD col3 (ANY) OPT ANY
             ADD col4 (ANY) OPT ANY
             ADD col5 (ANY) OPT ANY
             ADD col6 NOS ZERO ()
             ADD col7 NOS ZERO ()
        )
        OUTPUTLIST (col1, col2, col3, col4, col5, col6, col7
        )
        WRAPPER (jdbc b_tab1)
    );

-- 3. BVカラム情報取得

select
  database_name
, view_name
, ordinal_position
, column_name
, column_vdp_type
, column_sql_type
, column_sql_type_code
, column_size
, column_decimals
, column_radix
, column_is_primary_key
, column_is_nullable
, column_remarks
, column_is_autoincrement
, column_is_generated
, column_vdp_type_database
from GET_VIEW_COLUMNS('db01','b_tab1')
order by ordinal_position
;

-- 4. IFV作成

CONNECT DATABASE db01;

CREATE OR REPLACE FOLDER '/03iv';
CREATE OR REPLACE FOLDER '/03iv/111';
CREATE OR REPLACE FOLDER '/03iv/111/222';

CREATE OR REPLACE INTERFACE VIEW i_tab1 (
        col1:long ,
        col2:decimal ,
        col3:text ,
        col4:text ,
        col5:timestamp ,
        col6:blob ,
        col7:blob 
    )
    SET IMPLEMENTATION db01.b_tab1
    FOLDER = '/03iv/111/222';