CREATE TABLE wb_rt_dp_columninfo (
    user_id                        NUMBER(11) default (sys_context('USERENV', 'CURRENT_USERID')) not null,
    invocation_id                  NUMBER(9,0),
    invocation_uoid                VARCHAR2(255),
    column_id                      NUMBER(9,0),
    column_name                    VARCHAR2(30),
    table_id                       NUMBER(9,0),
    table_name                     VARCHAR2(30),
    prftable_name                  VARCHAR2(30),
    datatype                       VARCHAR2(20),
    column_type                    NUMBER(1,0),
    basecolumn_id                  NUMBER(9,0),
    basecolumn_name                VARCHAR2(30)
);

CREATE INDEX IDX_COLUMNINFO_ID ON wb_rt_dp_columninfo(column_id,table_id);

CREATE TABLE wb_rt_dp_constraintinfo (
    user_id                        NUMBER(11) default (sys_context('USERENV', 'CURRENT_USERID')) not null,
    invocation_id                  NUMBER(9,0),
    invocation_uoid                VARCHAR2(255),
    constraint_id                  NUMBER(9,0),
    localcolumn_id                 NUMBER(9,0),
    localcolumn_name               VARCHAR2(30),
    localtable_id                  NUMBER(9,0),
    localtable_name                VARCHAR2(30),
    remotecolumn_id                NUMBER(9,0),
    remotecolumn_name              VARCHAR2(30),
    remotetable_id                 NUMBER(9,0),
    remotetable_name               VARCHAR2(30),
    group_id                       NUMBER(9),
    constraint_type                NUMBER(2)
);

CREATE TABLE wb_rt_dp_constraintstats (
    user_id                        NUMBER(11) default (sys_context('USERENV', 'CURRENT_USERID')) not null,
    invocation_id                  NUMBER(9,0),
    invocation_uoid                VARCHAR2(255),
    table_id                       NUMBER(9,0),
    constraint_id                  NUMBER(9,0),
    compliant                      NUMBER(1),
    error                          NUMBER
);

CREATE TABLE wb_rt_dp_bus_rule_stats (
    user_id                        NUMBER(11) default (sys_context('USERENV', 'CURRENT_USERID')) not null,
    invocation_id                  NUMBER(9,0),
    table_id                       NUMBER(9,0),
    table_uoid                     VARCHAR2(255),
    brusage_uoid                   VARCHAR2(255),
    rule_type                      NUMBER(9,0),
    bus_rule_param                 VARCHAR2(255),
    bus_rule_clob_value            CLOB
)
;
                                                                                
CREATE INDEX IDX_bus_rule_stats_ID ON wb_rt_dp_bus_rule_stats(brusage_uoid,table_id) ;

CREATE TABLE wb_rt_dp_bus_attr_maps (
    user_id                        NUMBER(11) default (sys_context('USERENV', 'CURRENT_USERID')) not null,
    invocation_id                  NUMBER(9,0),
    brusage_uoid                   VARCHAR2(255),
    attribute_name                 VARCHAR2(30),
    attribute_order                NUMBER(9,0),
    attribute_role                 VARCHAR2(255),
    attribute_type                 VARCHAR2(255),
    column_id                      NUMBER(9,0),
    column_name                    VARCHAR2(30),
    table_id                       NUMBER(9,0),
    table_uoid                     VARCHAR2(255)
);
                                                                                
CREATE INDEX IDX_BUS_ATTR_MAPS_ID ON wb_rt_dp_bus_attr_maps(brusage_uoid, column_id,table_id) ;


CREATE TABLE wb_rt_dp_columnstats (
    user_id                        NUMBER(11) default (sys_context('USERENV', 'CURRENT_USERID')) not null,
    invocation_id                  NUMBER(9,0),
    column_id                      NUMBER(9,0),
    column_name                    VARCHAR2(30),
    table_id                       NUMBER(9,0),
    num_nulls                      NUMBER(38,0),
    num_distinct                   NUMBER(38,0),
    min                            VARCHAR2(4000),
    max                            VARCHAR2(4000),
    avg                            NUMBER(38,0),
    stddev                         NUMBER(38,0),
    median                         NUMBER(38,0),
    common_format                  NUMBER(38,0),
    common_format_cnt              NUMBER(38,0),
    consensus_datatype             NUMBER(38,0),
    consensus_datatype_cnt         NUMBER(38,0), 
    consensus_pattern              VARCHAR2(4000),
    consensus_pattern_cnt          NUMBER(38,0),
    consensus_pattern1             VARCHAR2(4000),
    consensus_pattern1_cnt         NUMBER(38,0),
    consensus_length               NUMBER(38,0),
    consensus_length_cnt           NUMBER(38,0),
    max_length                     NUMBER(38,0),
    min_length                     NUMBER(38,0),
    consensus_precision            NUMBER(38,0),
    consensus_precision_cnt        NUMBER(38,0),
    max_precision                  NUMBER(38,0),
    min_precision                  NUMBER(38,0),
    consensus_scale                NUMBER(38,0),
    consensus_scale_cnt            NUMBER(38,0),
    max_scale                      NUMBER(38,0),
    min_scale                      NUMBER(38,0)
);

CREATE INDEX IDX_COLUMNSTATS_ID ON wb_rt_dp_columnstats(column_id,table_id) ;

CREATE TABLE wb_rt_dp_bus_table_stats (
    user_id                        NUMBER(11) default (sys_context('USERENV', 'CURRENT_USERID')) not null,
    invocation_id                  NUMBER(9,0),
    prfusage_uoid                  VARCHAR2(255),
    table_id                       NUMBER(9,0),
    brcnt                          NUMBER(38,0)
);


CREATE INDEX IDX_BUSTABSTATS_ID ON wb_rt_dp_bus_table_stats(prfusage_uoid,table_id) ;

CREATE TABLE wb_rt_dp_configstats (
    user_id                        NUMBER(11) default (sys_context('USERENV', 'CURRENT_USERID')) not null,
    invocation_id                  NUMBER(9,0),
    invocation_uoid                VARCHAR2(255),
    table_id                       NUMBER(9,0), 
    column_id                      NUMBER(9,0),
    property_name                  VARCHAR2(200),
    property_value                 VARCHAR2(4000),
    datatype                       VARCHAR2(30)
);
    
CREATE TABLE wb_rt_dp_domainvalues (
    user_id                        NUMBER(11) default (sys_context('USERENV', 'CURRENT_USERID')) not null,
    invocation_id                  NUMBER(9,0),
    table_id                       NUMBER(9,0),
    column_id                      NUMBER(9,0),
    value                          VARCHAR2(4000),
    num_occurs                     NUMBER(38,0),
    domain_type                    NUMBER(1)
);

CREATE TABLE wb_rt_dp_patternvalues (
    user_id                        NUMBER(11) default (sys_context('USERENV', 'CURRENT_USERID')) not null,
    invocation_id                  NUMBER(9,0),
    table_id                       NUMBER(9,0),
    column_id                      NUMBER(9,0),
    value                          VARCHAR2(4000),
    num_occurs                     NUMBER(38,0),
    pattern_type                   NUMBER(1),
    dominant                       NUMBER(1)
);
    
CREATE TABLE wb_rt_dp_fkconfig (
    user_id                        NUMBER(11) default (sys_context('USERENV', 'CURRENT_USERID')) not null,
    table_id                       NUMBER(9,0),
    difference                     NUMBER);

CREATE UNIQUE INDEX idx_fkconfig_id ON wb_rt_dp_fkconfig (
    table_id                        ASC);


CREATE TABLE wb_rt_dp_fkvalues (
    user_id                        NUMBER(11) default (sys_context('USERENV', 'CURRENT_USERID')) not null,
    invocation_id                  NUMBER(9,0),
    localtable_id                  NUMBER(9,0),
    localcolumn_id                 NUMBER(9,0),
    remotetable_id                 NUMBER(9,0),
    remotecolumn_id                NUMBER(9,0),
    num_orphans                    NUMBER(9,0),
    difference                     NUMBER(9,0),
    composite                      CHAR(1) default 0,
    composite_id                   VARCHAR2(1000)
);
    
CREATE TABLE wb_rt_dp_fkcardinality (
    user_id                        NUMBER(11) default (sys_context('USERENV', 'CURRENT_USERID')) not null,
    invocation_id                  NUMBER(9,0),
    localtable_id                  NUMBER(9,0),
    localcolumn_id                 NUMBER(9,0),
    remotecolumn_id                NUMBER(9,0),
    minLocal                       NUMBER(9,0),
    maxLocal                       NUMBER(9,0),
    minRemote                      NUMBER(9,0),
    maxRemote                      NUMBER(9,0)
);

CREATE TABLE wb_rt_dp_rdntvalues (
    user_id                        NUMBER(11) default (sys_context('USERENV', 'CURRENT_USERID')) not null,
    invocation_id                  NUMBER(9,0),
    localtable_id                  NUMBER(9,0),
    localcolumn_id                 NUMBER(9,0),
    remotecolumn_id                NUMBER(9,0),
    rdntcolumn1_id                 NUMBER(9,0),
    rdntcolumn2_id                 NUMBER(9,0),
    threshold                      NUMBER
);
    
CREATE TABLE wb_rt_dp_tableinfo (
    user_id                        NUMBER(11) default (sys_context('USERENV', 'CURRENT_USERID')) not null,
    invocation_id                  NUMBER(9,0),
    invocation_uoid                VARCHAR2(255),
    table_id                       NUMBER(9,0),
    table_uoid                     VARCHAR2(255),
    sourcetable_name               VARCHAR2(30),
    table_name                     VARCHAR2(30),
    prftable_name                  VARCHAR2(30),
    map_prof                       VARCHAR2(255),
    nonplsqlmap_name               VARCHAR2(255),
    profile                        NUMBER(5,0) default 0,
    fkprofile                      NUMBER(1) default 0, 
    profile_seq                    NUMBER(9,0),
    profiled                       NUMBER(1) default 0,
    loaded                         NUMBER(1) default 0
);
    
CREATE TABLE wb_rt_dp_tablestats (
    user_id                        NUMBER(11) default (sys_context('USERENV', 'CURRENT_USERID')) not null,
    invocation_id                  NUMBER(9,0),
    table_id                       NUMBER(9,0),
    table_name                     VARCHAR2(30),
    prftable_name                  VARCHAR2(30),
    num_rows                       NUMBER(38,0)
);

CREATE TABLE wb_rt_dp_partition_metadata (
    user_id                        NUMBER(11) default (sys_context('USERENV', 'CURRENT_USERID')) not null,
    partition_id                   NUMBER,
    partition_name                 VARCHAR2(30),
    partition_colname              VARCHAR2(30),
    partition_cntname              VARCHAR2(30),
    partition_dtypename            VARCHAR2(30),
    partition_level                NUMBER,
    num_sets                       NUMBER,
    num_elems                      NUMBER,
    max_setcount                   NUMBER,
    num_nulls                      NUMBER,
    table_id                       NUMBER(9,0),
    invocation_id                  NUMBER(9,0)
);

CREATE TABLE wb_rt_dp_fdvalues (
    user_id                        NUMBER(11) default (sys_context('USERENV', 'CURRENT_USERID')) not null,
    lhs                            NUMBER(9,0),
    rhs                            NUMBER(9,0),
    key                            CHAR(1),
    group_id                       NUMBER,
    table_id                       NUMBER(9,0),
    invocation_id                  NUMBER(9,0),
    x_u_a_id                       NUMBER,
    x_id                           NUMBER,
    difference                     NUMBER,
    type                           NUMBER(1) default 0
);

CREATE TABLE wb_rt_dp_debug (
    user_id                        NUMBER(11) default (sys_context('USERENV', 'CURRENT_USERID')) not null,
    invocation_id                  NUMBER(9),
    logtime                        TIMESTAMP,
    msg                            VARCHAR2(4000) 
);

CREATE TABLE wb_rt_dp_errorhandler (
    user_id                        NUMBER(11) default (sys_context('USERENV', 'CURRENT_USERID')) not null,
    invocation_id                  NUMBER(9),
    table_id                       NUMBER(9),
    mask                           NUMBER(3),
    type                           NUMBER(1),
    constraint pk_id primary key (table_id,invocation_id)
);

BEGIN 
EXECUTE IMMEDIATE 'CREATE OR REPLACE FUNCTION wb_rt_dp_test RETURN VARCHAR2 AUTHID CURRENT_USER AS LANGUAGE JAVA NAME ''oracle.wh.service.impl.dataProfile.analysis.storedprocs.FunctionalDependency.test() return java.lang.String''; ' ; 
END;
/

BEGIN 
EXECUTE IMMEDIATE 'CREATE OR REPLACE PROCEDURE wb_rt_dp_fd(stopLevel IN NUMBER,numTuples IN NUMBER,numAttr IN NUMBER,fderror IN FLOAT,ukerror IN FLOAT,prfID IN NUMBER,tblID IN NUMBER,calculateFD IN NUMBER,calculateUK IN NUMBER,sampleTable IN VARCHAR2,isSample BOOLEAN) AUTHID CURRENT_USER AS LANGUAGE JAVA NAME ''oracle.wh.service.impl.dataProfile.analysis.storedprocs.FunctionalDependency.fd(int,int,int,double,double,int,int,int,int,java.lang.String,boolean)''; ' ; 
END;
/

BEGIN 
EXECUTE IMMEDIATE 'CREATE OR REPLACE PROCEDURE wb_rt_dp_create_fktables(prfID IN NUMBER,datatype IN VARCHAR2) AUTHID CURRENT_USER AS LANGUAGE JAVA NAME ''oracle.wh.service.impl.dataProfile.analysis.storedprocs.ForeignKey.createPartitionedTables(int,java.lang.String)''; ' ; 
END;
/

BEGIN 
EXECUTE IMMEDIATE 'CREATE OR REPLACE PROCEDURE wb_rt_dp_create_fkpartition(prfID IN NUMBER,datatype IN VARCHAR2) AUTHID CURRENT_USER AS LANGUAGE JAVA NAME ''oracle.wh.service.impl.dataProfile.analysis.storedprocs.ForeignKey.createFKPartition(int,java.lang.String)''; ' ; 
END;
/

BEGIN 
EXECUTE IMMEDIATE 'CREATE OR REPLACE PROCEDURE wb_rt_dp_fk(stopLevel IN NUMBER,prfID IN NUMBER,tblID IN NUMBER) AUTHID CURRENT_USER AS LANGUAGE JAVA NAME ''oracle.wh.service.impl.dataProfile.analysis.storedprocs.FunctionalDependency.fk(int,int,int)''; ' ; 
END;
/

BEGIN 
EXECUTE IMMEDIATE 'CREATE OR REPLACE FUNCTION wb_rt_dp_create_sample(tblID IN NUMBER,sampleSize IN NUMBER,type IN NUMBER) RETURN VARCHAR2 AUTHID CURRENT_USER AS LANGUAGE JAVA NAME ''oracle.wh.service.impl.dataProfile.analysis.storedprocs.FunctionalDependency.createSample(int,int,int) return java.lang.String''; ' ; 
END;
/
--  
-- Views implementing per-user slices on the DP tables.
--  
    

CREATE OR REPLACE VIEW wb_rtv_dp_columninfo
as
  select
    invocation_id,
    invocation_uoid,
    column_id,
    column_name,
    table_id,
    table_name,
    prftable_name,
    datatype,
    column_type,
    basecolumn_id,
    basecolumn_name
  from
    wb_rt_dp_columninfo
  where
    user_id = sys_context('USERENV', 'CURRENT_USERID');

CREATE OR REPLACE VIEW wb_rtv_dp_constraintinfo
as
  select
    invocation_id,
    invocation_uoid,
    constraint_id,
    localcolumn_id,
    localcolumn_name,
    localtable_id,
    localtable_name,
    remotecolumn_id,
    remotecolumn_name,
    remotetable_id,
    remotetable_name,
    group_id,
    constraint_type
  from
    wb_rt_dp_constraintinfo
  where
    user_id = sys_context('USERENV', 'CURRENT_USERID');

CREATE OR REPLACE VIEW wb_rtv_dp_constraintstats
as
  select
    invocation_id,
    invocation_uoid,
    table_id,
    constraint_id,
    compliant,
    error
  from
    wb_rt_dp_constraintstats
  where
    user_id = sys_context('USERENV', 'CURRENT_USERID');

CREATE OR REPLACE VIEW wb_rtv_dp_bus_rule_stats
as
  select
    user_id,
    invocation_id,
    table_id,
    table_uoid,
    brusage_uoid,
    rule_type,
    bus_rule_param,
    bus_rule_clob_value
  from
    wb_rt_dp_bus_rule_stats
  where
    user_id = sys_context('USERENV', 'CURRENT_USERID');

CREATE OR REPLACE VIEW wb_rtv_dp_bus_attr_maps
as
  select
    user_id,
    invocation_id,
    brusage_uoid,
    attribute_name,
    attribute_order,
    attribute_role,
    attribute_type,
    column_id,
    column_name,
    table_id,
    table_uoid
  from
    wb_rt_dp_bus_attr_maps
  where
    user_id = sys_context('USERENV', 'CURRENT_USERID');

CREATE OR REPLACE VIEW wb_rtv_dp_columnstats
as
  select
    invocation_id,
    column_id,
    column_name,
    table_id,
    num_nulls,
    num_distinct,
    min,
    max,
    avg,
    stddev,
    median,
    common_format,
    common_format_cnt,
    consensus_datatype,
    consensus_datatype_cnt,
    consensus_pattern,
    consensus_pattern_cnt,
    consensus_pattern1,
    consensus_pattern1_cnt,
    consensus_length,
    consensus_length_cnt,
    max_length,
    min_length,
    consensus_precision,
    consensus_precision_cnt,
    max_precision,
    min_precision,
    consensus_scale,
    consensus_scale_cnt,
    max_scale,
    min_scale
  from
    wb_rt_dp_columnstats
  where
    user_id = sys_context('USERENV', 'CURRENT_USERID');

CREATE OR REPLACE VIEW wb_rtv_dp_bus_table_stats
as
  select
    invocation_id,
    prfusage_uoid,
    table_id,
    brcnt
  from
    wb_rt_dp_bus_table_stats
  where
    user_id = sys_context('USERENV', 'CURRENT_USERID');

CREATE OR REPLACE VIEW wb_rtv_dp_configstats
as
  select
    invocation_id,
    invocation_uoid,
    table_id,
    column_id,
    property_name,
    property_value,
    datatype
  from
    wb_rt_dp_configstats
  where
    user_id = sys_context('USERENV', 'CURRENT_USERID');

CREATE OR REPLACE VIEW wb_rtv_dp_domainvalues
as
  select
    invocation_id,
    table_id,
    column_id,
    value,
    num_occurs,
    domain_type
  from
    wb_rt_dp_domainvalues
  where
    user_id = sys_context('USERENV', 'CURRENT_USERID');

CREATE OR REPLACE VIEW wb_rtv_dp_patternvalues
as
  select
    invocation_id,
    table_id,
    column_id,
    value,
    num_occurs,
    pattern_type,
    dominant
  from
    wb_rt_dp_patternvalues
  where
    user_id = sys_context('USERENV', 'CURRENT_USERID');

CREATE OR REPLACE VIEW wb_rtv_dp_fkconfig
as
  select
    table_id,
    difference
  from
    wb_rt_dp_fkconfig
  where
    user_id = sys_context('USERENV', 'CURRENT_USERID');

CREATE OR REPLACE VIEW wb_rtv_dp_fkvalues
as
  select
    invocation_id,
    localtable_id,
    localcolumn_id,
    remotetable_id,
    remotecolumn_id,
    num_orphans,
    difference,
    composite,
    composite_id
  from
    wb_rt_dp_fkvalues
  where
    user_id = sys_context('USERENV', 'CURRENT_USERID');

CREATE OR REPLACE VIEW wb_rtv_dp_fkcardinality 
as
  select
    invocation_id,
    localtable_id,
    localcolumn_id,
    remotecolumn_id,
    minLocal,
    maxLocal,
    minRemote,
    maxRemote
  from
    wb_rt_dp_fkcardinality
  where
    user_id = sys_context('USERENV', 'CURRENT_USERID');

CREATE OR REPLACE VIEW wb_rtv_dp_fkvalues_v (
    localtable_id,
    localcolumn_id,
    remotetable_id,
    remotecolumn_id,
    invocation_id,
    num_orphans,
    difference,
    composite,
    composite_id)
AS
select f1.localtable_id,f1.localcolumn_id,
    f1.remotetable_id,f1.remotecolumn_id,f1.invocation_id,
    f1.num_orphans,f1.difference,f1.composite,f1.composite_id from
    wb_rt_dp_fkvalues f1,wb_rt_dp_fkconfig f2 where f1.localtable_id = f2.table_id
  and f1.difference >= f2.difference
  and f1.user_id = sys_context('USERENV', 'CURRENT_USERID')
  and f2.user_id = sys_context('USERENV', 'CURRENT_USERID');

CREATE OR REPLACE VIEW wb_rtv_dp_rdntvalues
as
  select
    invocation_id,
    localtable_id,
    localcolumn_id,
    remotecolumn_id,
    rdntcolumn1_id,
    rdntcolumn2_id,
    threshold
  from
    wb_rt_dp_rdntvalues
  where
    user_id = sys_context('USERENV', 'CURRENT_USERID');

CREATE OR REPLACE VIEW wb_rtv_dp_tableinfo
as
  select
    invocation_id,
    invocation_uoid,
    table_id,
    table_uoid,
    sourcetable_name,
    table_name,
    prftable_name,
    map_prof,
    nonplsqlmap_name,
    profile,
    fkprofile,
    profile_seq,
    profiled,
    loaded
  from
    wb_rt_dp_tableinfo
  where
    user_id = sys_context('USERENV', 'CURRENT_USERID');

CREATE OR REPLACE VIEW wb_rtv_dp_tablestats
as
  select
    invocation_id,
    table_id,
    table_name,
    prftable_name,
    num_rows
  from
    wb_rt_dp_tablestats
  where
    user_id = sys_context('USERENV', 'CURRENT_USERID');

CREATE OR REPLACE VIEW wb_rtv_dp_partition_metadata
as
  select
    partition_id,
    partition_name,
    partition_colname,
    partition_cntname,
    partition_dtypename,
    partition_level,
    num_sets,
    num_elems,
    max_setcount,
    num_nulls,
    table_id,
    invocation_id
  from
    wb_rt_dp_partition_metadata
  where
    user_id = sys_context('USERENV', 'CURRENT_USERID');

CREATE OR REPLACE VIEW wb_rtv_dp_fdvalues
as
  select
    lhs,
    rhs,
    key,
    group_id,
    table_id,
    invocation_id,
    x_u_a_id,
    x_id,
    difference,
    type
  from
    wb_rt_dp_fdvalues
  where
    user_id = sys_context('USERENV', 'CURRENT_USERID');

CREATE OR REPLACE VIEW wb_rtv_dp_debug 
as
  select
    invocation_id,
    logtime,
    msg
  from
    wb_rt_dp_debug
  where
    user_id = sys_context('USERENV', 'CURRENT_USERID');

CREATE OR REPLACE VIEW wb_rtv_dp_errorhandler 
as
  select
    invocation_id,
    table_id,
    mask,
    type
  from
    wb_rt_dp_errorhandler
  where
    user_id = sys_context('USERENV', 'CURRENT_USERID');

CREATE SEQUENCE wb_rt_dp_profile_seq start with 1 increment by 1 order;