前提:
Denodo Express インストール、起動済み
PostgreSQL15インストール、起動済み
-- 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';
-- 2. テスト用テーブル、データの作成(PostgreSQLデータベースで実施)
drop table tab1;
create table tab1(
col1 int not null
, col2 bigint not null
, col3 numeric(20,5) not null
, col4 timestamp
, col5 char(10)
, col6 varchar(10)
, col7 text
, col8 bytea
);
alter table tab1 add constraint tab1pk primary key(col1);
create index ind11 on tab1(col2);
create index ind12 on tab1(col3);
\d tab1
insert into tab1 values(1,2,3,clock_timestamp(),'a','a','a','a');
select * from tab1;
-- 3. データソース作成
CREATE OR REPLACE DATASOURCE JDBC ds01
FOLDER = '/01ds'
DRIVERCLASSNAME = 'org.postgresql.Driver'
DATABASEURI = 'jdbc:postgresql://192.168.137.55:5432/test'
USERNAME = 'postgres'
USERPASSWORD = 'iluNriSuEKBhRoNJqGg/rF9zht4d41nfKSdzG02AifCsFGr4EzMS8+wo5H1ypT/TZ1MnUCnXf6DMXoqzYwb0k6CB43AGmrm6e/XXDAk3MTXLxuxLWGt0SrxlTynP1/b+' ENCRYPTED
CLASSPATH = 'postgresql-12'
DATABASENAME = 'postgresql'
DATABASEVERSION = '12'
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
DATA_LOAD_CONFIGURATION (
BATCHINSERTSIZE = 200
);
-- 4. ラッパー作成
CREATE OR REPLACE WRAPPER JDBC b_tab1
FOLDER = '/02bv'
DATASOURCENAME=ds01
SCHEMANAME='public' ESCAPE
RELATIONNAME='tab1'
OUTPUTSCHEMA (
col1 = 'col1' :'java.lang.Integer' (OPT) (sourcetypedecimals='0', sourcetyperadix='10', sourcetypesize='10', sourcetypeid='4', sourcetypename='int4') NOT NULL SORTABLE,
col2 = 'col2' :'java.lang.Long' (OPT) (sourcetypedecimals='0', sourcetyperadix='10', sourcetypesize='19', sourcetypeid='-5', sourcetypename='int8') NOT NULL SORTABLE,
col3 = 'col3' :'java.math.BigDecimal' (OPT) (sourcetypedecimals='5', sourcetyperadix='10', sourcetypesize='20', sourcetypeid='2', sourcetypename='numeric') NOT NULL SORTABLE,
col4 = 'col4' :'java.time.LocalDateTime' (OPT) (sourcetypedecimals='6', sourcetyperadix='10', sourcetypesize='29', sourcetypeid='93', sourcetypename='timestamp') SORTABLE,
col5 = 'col5' :'java.lang.String' (OPT) (sourcetypedecimals='0', sourcetyperadix='10', sourcetypesize='10', sourcetypeid='1', sourcetypename='bpchar') SORTABLE,
col6 = 'col6' :'java.lang.String' (OPT) (sourcetypedecimals='0', sourcetyperadix='10', sourcetypesize='10', sourcetypeid='12', sourcetypename='varchar') SORTABLE,
col7 = 'col7' :'java.lang.String' (OPT) (sourcetypedecimals='0', sourcetyperadix='10', sourcetypesize='2147483647', sourcetypeid='12', sourcetypename='text') SORTABLE,
col8 = 'col8' :'java.sql.Blob' (OPT) (sourcetypedecimals='0', sourcetyperadix='10', sourcetypesize='2147483647', sourcetypeid='-2', sourcetypename='bytea') NOT SORTABLE
)
CONSTRAINT 'tab1pk' PRIMARY KEY ( 'col1' )
INDEX 'tab1pk' OTHER UNIQUE PRIMARY ( 'col1' )
INDEX 'ind11' OTHER ( 'col2' )
INDEX 'ind12' OTHER ( 'col3' );
-- 5. 基本ビュー作成
CREATE OR REPLACE TABLE b_tab1 I18N jp (
col1:int (notnull, sourcetypeid = '4', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '10'),
col2:long (notnull, sourcetypeid = '-5', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '19'),
col3:decimal (notnull, sourcetypeid = '2', sourcetyperadix = '10', sourcetypedecimals = '5', sourcetypesize = '20'),
col4:timestamp (sourcetypeid = '93', sourcetyperadix = '10', sourcetypedecimals = '6', sourcetypesize = '29'),
col5:text (sourcetypeid = '1', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '10'),
col6:text (sourcetypeid = '12', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '10'),
col7:text (sourcetypeid = '12', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '2147483647'),
col8:blob (sourcetypeid = '-2', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '2147483647')
)
FOLDER = '/02bv'
CONSTRAINT 'tab1pk' 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 NOS ZERO ()
)
OUTPUTLIST (col1, col2, col3, col4, col5, col6, col7, col8
)
WRAPPER (jdbc b_tab1)
);
-- 6. インターフェイスビュー作成
CREATE OR REPLACE INTERFACE VIEW i_tab1 (
col1:int (sourcetypedecimals = '0', sourcetyperadix = '10', sourcetypesize = '10', sourcetypeid = '4'),
col2:long (sourcetypedecimals = '0', sourcetyperadix = '10', sourcetypesize = '19', sourcetypeid = '-5'),
col3:decimal (sourcetypedecimals = '5', sourcetyperadix = '10', sourcetypesize = '20', sourcetypeid = '2'),
col4:timestamp (sourcetypedecimals = '6', sourcetyperadix = '10', sourcetypesize = '29', sourcetypeid = '93'),
col5:text (sourcetypedecimals = '0', sourcetyperadix = '10', sourcetypesize = '10', sourcetypeid = '1'),
col6:text (sourcetypedecimals = '0', sourcetyperadix = '10', sourcetypesize = '10', sourcetypeid = '12'),
col7:text (sourcetypedecimals = '0', sourcetyperadix = '10', sourcetypesize = '2147483647', sourcetypeid = '12'),
col8:blob (sourcetypedecimals = '0', sourcetyperadix = '10', sourcetypesize = '2147483647', sourcetypeid = '-2', contenttype = 'autodetect')
)
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;
