{Denodo}データソース・ラッパー・基本ビュー・インターフェイスビューの作成(MySQL)

https://community.denodo.com/docs/html/browse/8.0/jp/vdp/vql/generating_wrappers_and_data_sources/generating_wrappers_and_data_sources

前提:
Denodo Express インストール、起動済み
MySQL8.0.31インストール、起動済み
MySQL Connector/J 入手済み ※ ダウンロード時、ドロップダウンよりPlatform Independentを選択する必要あり

-- 1. 事前準備

-- 1.1 データベース作成
DROP DATABASE db01 cascade;

CREATE OR REPLACE DATABASE db01 'description of db01'
VCS OFF
CHARSET UNICODE
AUTHENTICATION LOCAL
ODBC AUTHENTICATION NORMAL
CHECK_VIEW_RESTRICTIONS ALWAYS
;

-- 1.2 フォルダ作成
CONNECT DATABASE db01;

CREATE FOLDER '/01ds' DESCRIPTION 'description of folder01';
CREATE FOLDER '/02bv' DESCRIPTION 'description of folder02';
CREATE FOLDER '/03iv' DESCRIPTION 'description of folder03';

-- 1.3 MySQL用JDBCドライバーのインポート

Design Studio で 'File -> Extension management'

Librariesタブを選択
「mysql-connector-j-8.0.32.jar」をImport
Resource type: jdbc
Version: mysql-5

-- 2. テスト用DBとテーブル、データの作成(MySQLデータベースで実施)

use test;

drop table tab1;
create table tab1(
col1 int not null
, col2 bigint not null
, col3 decimal not null
, col4 date
, col5 datetime
, col6 timestamp
, col7 char
, col8 varchar(10)
, col9 binary
, col10 varbinary(10)
, col11 blob
, col12 text
);
alter table tab1 add constraint tab1pk primary key(col1);

create index ind11 on tab1(col2);
create index ind12 on tab1(col3);

show create table tab1;

insert into tab1 values(1,2,3,now(),now(),now(),'a','a','a','a','a','a');
select * from tab1;

-- 3. データソース作成
DROP DATASOURCE JDBC IF EXISTS ds01 CASCADE;

CREATE DATASOURCE JDBC ds01
FOLDER = '/01ds'
DRIVERCLASSNAME = 'com.mysql.jdbc.Driver'
DATABASEURI = 'jdbc:mysql://localhost:3306/test'
USERNAME = 'root'
USERPASSWORD = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' ENCRYPTED
CLASSPATH = 'mysql-5'
DATABASENAME = 'mysql'
DATABASEVERSION = '5'
FETCHSIZE = 1000
VALIDATIONQUERY = 'Select 1'
INITIALSIZE = 4
MAXIDLE = -1
MINIDLE = 0
MAXACTIVE = 20
EXHAUSTEDACTION = 1
TESTONBORROW = true
TESTONRETURN = false
TESTWHILEIDLE = false
TIMEBETWEENEVICTION = -1
NUMTESTPEREVICTION = 3
MINEVICTABLETIME = 1800000
POOLPREPAREDSTATEMENTS = false
MAXOPENPREPAREDSTATEMENTS = -1
PROPERTIES ('vdp.streamRows'='yes')
DATA_LOAD_CONFIGURATION (
BATCHINSERTSIZE = 200
USEEXTERNALTABLES (
ONMOVEREAD = false,
ONMOVEWRITE = true
)
);

-- 4. ラッパー作成
DROP WRAPPER JDBC IF EXISTS b_tab1 CASCADE;

CREATE WRAPPER JDBC b_tab1
FOLDER = '/02bv'
DATASOURCENAME=ds01
CATALOGNAME='test'
RELATIONNAME='tab1'
OUTPUTSCHEMA (
col1 = 'col1' :'java.lang.Integer' (OPT) (sourcetyperadix='10', sourcetypesize='10', description='', sourcetypeid='4', sourcetypename='INT') NOT NULL SORTABLE,
col2 = 'col2' :'java.lang.Long' (OPT) (sourcetyperadix='10', sourcetypesize='19', description='', sourcetypeid='-5', sourcetypename='BIGINT') NOT NULL SORTABLE,
col3 = 'col3' :'java.lang.Long' (OPT) (sourcetypedecimals='0', sourcetyperadix='10', sourcetypesize='10', description='', sourcetypeid='3', sourcetypename='DECIMAL') NOT NULL SORTABLE,
col4 = 'col4' :'java.time.LocalDate' (OPT) (sourcetyperadix='10', sourcetypesize='10', description='', sourcetypeid='91', sourcetypename='DATE') SORTABLE,
col5 = 'col5' :'java.time.LocalDateTime' (OPT) (sourcetyperadix='10', sourcetypesize='19', description='', sourcetypeid='93', sourcetypename='DATETIME') SORTABLE,
col6 = 'col6' :'java.time.LocalDateTime' (OPT) (sourcetyperadix='10', sourcetypesize='19', description='', sourcetypeid='93', sourcetypename='TIMESTAMP') SORTABLE,
col7 = 'col7' :'java.lang.String' (OPT) (sourcetyperadix='10', sourcetypesize='1', description='', sourcetypeid='1', sourcetypename='CHAR') SORTABLE,
col8 = 'col8' :'java.lang.String' (OPT) (sourcetyperadix='10', sourcetypesize='10', description='', sourcetypeid='12', sourcetypename='VARCHAR') SORTABLE,
col9 = 'col9' :'java.sql.Blob' (OPT) (sourcetyperadix='10', sourcetypesize='1', description='', sourcetypeid='-2', sourcetypename='BINARY') NOT SORTABLE,
col10 = 'col10' :'java.sql.Blob' (OPT) (sourcetyperadix='10', sourcetypesize='10', description='', sourcetypeid='-3', sourcetypename='VARBINARY') NOT SORTABLE,
col11 = 'col11' :'java.sql.Blob' (OPT) (sourcetyperadix='10', sourcetypesize='65535', description='', sourcetypeid='-4', sourcetypename='BLOB') NOT SORTABLE,
col12 = 'col12' :'java.lang.String' (OPT) (sourcetyperadix='10', sourcetypesize='65535', description='', sourcetypeid='-1', sourcetypename='TEXT') SORTABLE
)
CONSTRAINT 'PRIMARY' PRIMARY KEY ( 'col1' )
INDEX 'PRIMARY' CLUSTER UNIQUE PRIMARY ( 'col1' )
INDEX 'ind11' OTHER ( 'col2' )
INDEX 'ind12' OTHER ( 'col3' );

-- 5. 基本ビュー作成
DROP VIEW IF EXISTS b_tab1 CASCADE;

CREATE TABLE b_tab1 I18N jp (
col1:int (notnull, sourcetypeid = '4', sourcetyperadix = '10', sourcetypesize = '10', description = ''),
col2:long (notnull, sourcetypeid = '-5', sourcetyperadix = '10', sourcetypesize = '19', description = ''),
col3:long (notnull, sourcetypeid = '-5', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '10', description = ''),
col4:localdate (sourcetypeid = '91', sourcetyperadix = '10', sourcetypesize = '10', description = ''),
col5:timestamp (sourcetypeid = '93', sourcetyperadix = '10', sourcetypesize = '19', description = ''),
col6:timestamp (sourcetypeid = '93', sourcetyperadix = '10', sourcetypesize = '19', description = ''),
col7:text (sourcetypeid = '1', sourcetyperadix = '10', sourcetypesize = '1', description = ''),
col8:text (sourcetypeid = '12', sourcetyperadix = '10', sourcetypesize = '10', description = ''),
col9:blob (sourcetypeid = '-2', sourcetyperadix = '10', sourcetypesize = '1', description = ''),
col10:blob (sourcetypeid = '-3', sourcetyperadix = '10', sourcetypesize = '10', description = ''),
col11:blob (sourcetypeid = '2004', sourcetyperadix = '10', sourcetypesize = '65535', description = ''),
col12:text (sourcetypeid = '-1', sourcetyperadix = '10', sourcetypesize = '65535', description = '')
)
FOLDER = '/02bv'
CONSTRAINT 'PRIMARY' PRIMARY KEY ( 'col1' )
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 (any) OPT ANY
ADD col7 (any) OPT ANY
ADD col8 (any) OPT ANY
ADD col12 (any) OPT ANY
ADD col9 NOS ZERO ()
ADD col10 NOS ZERO ()
ADD col11 NOS ZERO ()
)
OUTPUTLIST (col1, col10, col11, col12, col2, col3, col4, col5, col6, col7, col8, col9
)
WRAPPER (jdbc b_tab1)
);

-- 6. インターフェイスビュー作成

DROP INTERFACE VIEW IF EXISTS i_tab1 CASCADE;

CREATE INTERFACE VIEW i_tab1 (
col1:int (sourcetyperadix = '10', sourcetypesize = '10', sourcetypeid = '4'),
col2:long (sourcetyperadix = '10', sourcetypesize = '19', sourcetypeid = '-5'),
col3:long (sourcetypedecimals = '0', sourcetyperadix = '10', sourcetypesize = '10', sourcetypeid = '-5'),
col4:localdate (sourcetyperadix = '10', sourcetypesize = '10', sourcetypeid = '91'),
col5:timestamp (sourcetyperadix = '10', sourcetypesize = '19', sourcetypeid = '93'),
col6:timestamp (sourcetyperadix = '10', sourcetypesize = '19', sourcetypeid = '93'),
col7:text (sourcetyperadix = '10', sourcetypesize = '1', sourcetypeid = '1'),
col8:text (sourcetyperadix = '10', sourcetypesize = '10', sourcetypeid = '12'),
col9:blob (sourcetyperadix = '10', sourcetypesize = '1', sourcetypeid = '-2', contenttype = 'autodetect'),
col10:blob (sourcetyperadix = '10', sourcetypesize = '10', sourcetypeid = '-3', contenttype = 'autodetect'),
col11:blob (sourcetyperadix = '10', sourcetypesize = '65535', sourcetypeid = '2004', contenttype = 'autodetect'),
col12:text (sourcetyperadix = '10', sourcetypesize = '65535', sourcetypeid = '-1')
)
SET IMPLEMENTATION b_tab1
FOLDER = '/03iv';

-- 7. 動作確認

SELECT * FROM db01.b_tab1 CONTEXT('cache_wait_for_load' = 'true') TRACE;

SELECT * FROM db01.i_tab1 CONTEXT('cache_wait_for_load' = 'true') TRACE;

insert into db01.b_tab1 values(11,12,13,CURRENT_DATE,LOCALTIMESTAMP,LOCALTIMESTAMP ,'b','b','b','b','b','b');

insert into db01.i_tab1 values(21,22,23,CURRENT_DATE,LOCALTIMESTAMP,LOCALTIMESTAMP ,'c','c','c','c','c','c');

MySQL側にデータが追加されていることを確認
select * from tab1;