-- Name: tf_trg_lz_src_analysis_meta_id(); Type: FUNCTION; Schema: tm_lz; Owner: tm_lz
--

CREATE FUNCTION tm_lz.tf_trg_lz_src_analysis_meta_id() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin     
      if NEW.ETL_ID is null then
        select nextval('tm_lz.seq_etl_id') into NEW.ETL_ID ;       
      end if;       
    RETURN NEW;
end;
$$;


ALTER FUNCTION tm_lz.tf_trg_lz_src_analysis_meta_id() OWNER TO tm_lz;

--
-- Name: array_accum(anyelement); Type: AGGREGATE; Schema: public; Owner: postgres
--

CREATE AGGREGATE public.array_accum(anyelement) (
    SFUNC = array_append,
    STYPE = anyarray,
    INITCOND = '{}'
);


ALTER AGGREGATE public.array_accum(anyelement) OWNER TO postgres;

--
-- Name: median(anyelement); Type: AGGREGATE; Schema: tm_cz; Owner: tm_cz
--

CREATE AGGREGATE tm_cz.median(anyelement) (
    SFUNC = array_append,
    STYPE = anyarray,
    INITCOND = '{}',
    FINALFUNC = tm_cz._final_median
);


ALTER AGGREGATE tm_cz.median(anyelement) OWNER TO tm_cz;

--
-- Name: median(double precision); Type: AGGREGATE; Schema: tm_cz; Owner: tm_cz
--

CREATE AGGREGATE tm_cz.median(double precision) (
    SFUNC = array_append,
    STYPE = double precision[],
    INITCOND = '{}',
    FINALFUNC = tm_cz._final_median
);


ALTER AGGREGATE tm_cz.median(double precision) OWNER TO tm_cz;

--
-- Name: CAST (character varying AS numeric); Type: CAST; Schema: -; Owner: 
--

CREATE CAST (character varying AS numeric) WITH INOUT AS IMPLICIT;




--
-- Name: am_data_uid; Type: TABLE; Schema: amapp; Owner: amapp; Tablespace: transmart
--

CREATE TABLE amapp.am_data_uid (
    am_data_id bigint NOT NULL,
    unique_id character varying(300) NOT NULL,
    am_data_type character varying(100) NOT NULL
);


ALTER TABLE amapp.am_data_uid OWNER TO amapp;

--
-- Name: am_tag_association; Type: TABLE; Schema: amapp; Owner: amapp; Tablespace: transmart
--

CREATE TABLE amapp.am_tag_association (
    subject_uid character varying(300) NOT NULL,
    object_uid character varying(300) NOT NULL,
    object_type character varying(50),
    tag_item_id bigint
);


ALTER TABLE amapp.am_tag_association OWNER TO amapp;

--
-- Name: am_tag_item; Type: TABLE; Schema: amapp; Owner: amapp; Tablespace: transmart
--

CREATE TABLE amapp.am_tag_item (
    tag_template_id bigint NOT NULL,
    tag_item_id bigint NOT NULL,
    required character varying(1),
    display_order bigint,
    display_name character varying(200) NOT NULL,
    gui_handler character varying(200) NOT NULL,
    max_values bigint,
    code_type_name character varying(200),
    editable character varying(1),
    active_ind boolean NOT NULL,
    tag_item_uid character varying(300) NOT NULL,
    tag_item_attr character varying(300),
    tag_item_type character varying(200),
    view_in_grid smallint,
    tag_item_subtype character varying(200),
    view_in_child_grid smallint
);


ALTER TABLE amapp.am_tag_item OWNER TO amapp;

--
-- Name: am_tag_value; Type: TABLE; Schema: amapp; Owner: amapp; Tablespace: transmart
--

CREATE TABLE amapp.am_tag_value (
    tag_value_id bigint NOT NULL,
    value character varying(2000)
);


ALTER TABLE amapp.am_tag_value OWNER TO amapp;

--
-- Name: bio_assay_platform; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_assay_platform (
    bio_assay_platform_id bigint NOT NULL,
    platform_name character varying(200),
    platform_version character varying(200),
    platform_description character varying(2000),
    platform_array character varying(50),
    platform_accession character varying(20),
    platform_organism character varying(200),
    platform_vendor character varying(200),
    platform_type character varying(200),
    platform_technology character varying(200)
);


ALTER TABLE biomart.bio_assay_platform OWNER TO biomart;

--
-- Name: bio_compound; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_compound (
    bio_compound_id bigint NOT NULL,
    cnto_number character varying(200),
    jnj_number character varying(200),
    cas_registry character varying(400),
    code_name character varying(300),
    generic_name character varying(200),
    brand_name character varying(200),
    chemical_name character varying(1000),
    mechanism character varying(400),
    product_category character varying(200),
    description character varying(1000),
    etl_id_retired bigint,
    etl_id character varying(50),
    source_cd character varying(100)
);


ALTER TABLE biomart.bio_compound OWNER TO biomart;

--
-- Name: bio_concept_code; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_concept_code (
    bio_concept_code character varying(200),
    code_name character varying(200),
    code_description character varying(1000),
    code_type_name character varying(200),
    bio_concept_code_id bigint NOT NULL,
    filter_flag character(1) DEFAULT 0
);


ALTER TABLE biomart.bio_concept_code OWNER TO biomart;

--
-- Name: bio_data_uid; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_data_uid (
    bio_data_id bigint NOT NULL,
    unique_id character varying(300) NOT NULL,
    bio_data_type character varying(100) NOT NULL
);


ALTER TABLE biomart.bio_data_uid OWNER TO biomart;

--
-- Name: bio_disease; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_disease (
    bio_disease_id bigint NOT NULL,
    disease character varying(510) NOT NULL,
    ccs_category character varying(510),
    icd10_code character varying(510),
    mesh_code character varying(510),
    icd9_code character varying(510),
    prefered_name character varying(510),
    etl_id_retired bigint,
    primary_source_cd character varying(30),
    etl_id character varying(50)
);


ALTER TABLE biomart.bio_disease OWNER TO biomart;

--
-- Name: bio_marker; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_marker (
    bio_marker_id bigint NOT NULL,
    bio_marker_name character varying(200),
    bio_marker_description character varying(1000),
    organism character varying(200),
    primary_source_code character varying(200),
    primary_external_id character varying(200),
    bio_marker_type character varying(200) NOT NULL
);


ALTER TABLE biomart.bio_marker OWNER TO biomart;

--
-- Name: bio_observation; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_observation (
    bio_observation_id bigint NOT NULL,
    obs_name character varying(200),
    obs_code character varying(50),
    obs_descr character varying(1000),
    etl_id character varying(50),
    obs_type character varying(20),
    obs_code_source character varying(20)
);


ALTER TABLE biomart.bio_observation OWNER TO biomart;

--
-- Name: am_tag_display_vw; Type: VIEW; Schema: amapp; Owner: amapp
--

CREATE VIEW amapp.am_tag_display_vw AS
 SELECT DISTINCT tass.subject_uid,
    tass.tag_item_id,
    tval.value AS display_value,
    tass.object_type,
    tass.object_uid,
    obj_uid.am_data_id AS object_id
   FROM ((amapp.am_tag_association tass
     JOIN amapp.am_data_uid obj_uid ON (((tass.object_uid)::text = (obj_uid.unique_id)::text)))
     JOIN amapp.am_tag_value tval ON ((obj_uid.am_data_id = tval.tag_value_id)))
UNION
 SELECT DISTINCT tass.subject_uid,
    tass.tag_item_id,
    bio_val.code_name AS display_value,
    tass.object_type,
    tass.object_uid,
    obj_uid.bio_data_id AS object_id
   FROM ((amapp.am_tag_association tass
     JOIN biomart.bio_data_uid obj_uid ON (((tass.object_uid)::text = (obj_uid.unique_id)::text)))
     JOIN biomart.bio_concept_code bio_val ON ((obj_uid.bio_data_id = bio_val.bio_concept_code_id)))
UNION
 SELECT DISTINCT tass.subject_uid,
    tass.tag_item_id,
    bio_val.disease AS display_value,
    tass.object_type,
    tass.object_uid,
    obj_uid.bio_data_id AS object_id
   FROM ((amapp.am_tag_association tass
     JOIN biomart.bio_data_uid obj_uid ON (((tass.object_uid)::text = (obj_uid.unique_id)::text)))
     JOIN biomart.bio_disease bio_val ON ((obj_uid.bio_data_id = bio_val.bio_disease_id)))
UNION
 SELECT DISTINCT tass.subject_uid,
    tass.tag_item_id,
        CASE
            WHEN ((ati.code_type_name)::text = 'PLATFORM_NAME'::text) THEN (((((((bio_val.platform_type)::text || '/'::text) || (bio_val.platform_technology)::text) || '/'::text) || (bio_val.platform_vendor)::text) || '/'::text) || (bio_val.platform_name)::text)
            WHEN ((ati.code_type_name)::text = 'VENDOR'::text) THEN (bio_val.platform_vendor)::text
            WHEN ((ati.code_type_name)::text = 'MEASUREMENT_TYPE'::text) THEN (bio_val.platform_type)::text
            WHEN ((ati.code_type_name)::text = 'TECHNOLOGY'::text) THEN (bio_val.platform_technology)::text
            ELSE NULL::text
        END AS display_value,
    tass.object_type,
    tass.object_uid,
    obj_uid.bio_data_id AS object_id
   FROM (((amapp.am_tag_association tass
     JOIN biomart.bio_data_uid obj_uid ON (((tass.object_uid)::text = (obj_uid.unique_id)::text)))
     JOIN biomart.bio_assay_platform bio_val ON ((obj_uid.bio_data_id = bio_val.bio_assay_platform_id)))
     JOIN amapp.am_tag_item ati ON ((ati.tag_item_id = tass.tag_item_id)))
UNION
 SELECT DISTINCT tass.subject_uid,
    tass.tag_item_id,
    bio_val.code_name AS display_value,
    tass.object_type,
    tass.object_uid,
    obj_uid.bio_data_id AS object_id
   FROM ((amapp.am_tag_association tass
     JOIN biomart.bio_data_uid obj_uid ON (((tass.object_uid)::text = (obj_uid.unique_id)::text)))
     JOIN biomart.bio_compound bio_val ON ((obj_uid.bio_data_id = bio_val.bio_compound_id)))
UNION
 SELECT DISTINCT tass.subject_uid,
    tass.tag_item_id,
    bio_val.bio_marker_name AS display_value,
    tass.object_type,
    tass.object_uid,
    obj_uid.bio_data_id AS object_id
   FROM ((amapp.am_tag_association tass
     JOIN biomart.bio_data_uid obj_uid ON (((tass.object_uid)::text = (obj_uid.unique_id)::text)))
     JOIN biomart.bio_marker bio_val ON ((obj_uid.bio_data_id = bio_val.bio_marker_id)))
UNION
 SELECT DISTINCT tass.subject_uid,
    tass.tag_item_id,
    bio_val.obs_name AS display_value,
    tass.object_type,
    tass.object_uid,
    obj_uid.bio_data_id AS object_id
   FROM ((amapp.am_tag_association tass
     JOIN biomart.bio_data_uid obj_uid ON (((tass.object_uid)::text = (obj_uid.unique_id)::text)))
     JOIN biomart.bio_observation bio_val ON ((obj_uid.bio_data_id = bio_val.bio_observation_id)));


ALTER TABLE amapp.am_tag_display_vw OWNER TO amapp;

--
-- Name: am_tag_template; Type: TABLE; Schema: amapp; Owner: amapp; Tablespace: transmart
--

CREATE TABLE amapp.am_tag_template (
    tag_template_id bigint NOT NULL,
    tag_template_name character varying(200) NOT NULL,
    tag_template_type character varying(50) NOT NULL,
    tag_template_subtype character varying(50),
    active_ind boolean NOT NULL
);


ALTER TABLE amapp.am_tag_template OWNER TO amapp;

--
-- Name: am_tag_template_association; Type: TABLE; Schema: amapp; Owner: amapp; Tablespace: transmart
--

CREATE TABLE amapp.am_tag_template_association (
    tag_template_id bigint NOT NULL,
    object_uid character varying(300) NOT NULL,
    id bigint
);


ALTER TABLE amapp.am_tag_template_association OWNER TO amapp;

--
-- Name: seq_amapp_data_id; Type: SEQUENCE; Schema: amapp; Owner: amapp
--

CREATE SEQUENCE amapp.seq_amapp_data_id
    START WITH 1995735
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE amapp.seq_amapp_data_id OWNER TO amapp;

--
-- Name: annotation; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.annotation (
    platform character varying(50),
    probeset character varying(50),
    gene_descr character varying(4000),
    gene_symbol character varying(4000),
    gene_id character varying(50),
    organism character varying(200),
    id bigint NOT NULL
);


ALTER TABLE biomart.annotation OWNER TO biomart;

--
-- Name: assay_analysis_data; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.assay_analysis_data (
    bio_asy_analysis_data_id bigint NOT NULL,
    bio_experiment_id bigint,
    bio_assay_platform_id bigint,
    bio_assay_analysis_id bigint,
    bio_assay_feature_group_id bigint,
    feature_group_name character varying(100),
    tea_normalized_pvalue double precision,
    fold_change_ratio bigint,
    raw_pvalue double precision,
    adjusted_pvalue double precision,
    preferred_pvalue double precision
);


ALTER TABLE biomart.assay_analysis_data OWNER TO biomart;

--
-- Name: bio_ad_hoc_property; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_ad_hoc_property (
    ad_hoc_property_id bigint NOT NULL,
    bio_data_id bigint,
    property_key character varying(50),
    property_value character varying(2000)
);


ALTER TABLE biomart.bio_ad_hoc_property OWNER TO biomart;

--
-- Name: bio_analysis_attribute; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_analysis_attribute (
    study_id character varying(255),
    bio_assay_analysis_id bigint NOT NULL,
    term_id bigint,
    source_cd character varying(255),
    bio_analysis_attribute_id bigint NOT NULL
);


ALTER TABLE biomart.bio_analysis_attribute OWNER TO biomart;

--
-- Name: bio_analysis_attribute_lineage; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_analysis_attribute_lineage (
    bio_analysis_att_lineage_id bigint NOT NULL,
    bio_analysis_attribute_id bigint NOT NULL,
    ancestor_term_id bigint NOT NULL,
    ancestor_search_keyword_id bigint NOT NULL
);


ALTER TABLE biomart.bio_analysis_attribute_lineage OWNER TO biomart;

--
-- Name: bio_analysis_cohort_xref; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_analysis_cohort_xref (
    study_id character varying(255),
    cohort_id character varying(255),
    analysis_cd character varying(255),
    bio_assay_analysis_id bigint NOT NULL
);


ALTER TABLE biomart.bio_analysis_cohort_xref OWNER TO biomart;

--
-- Name: bio_assay; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_assay (
    bio_assay_id bigint NOT NULL,
    etl_id character varying(100) NOT NULL,
    study character varying(200),
    protocol character varying(200),
    description text,
    sample_type character varying(200),
    experiment_id bigint NOT NULL,
    test_date timestamp without time zone,
    sample_receive_date timestamp without time zone,
    requestor character varying(200),
    bio_assay_type character varying(200) NOT NULL,
    bio_assay_platform_id bigint,
    biosource character varying(200),
    measurement_type character varying(200),
    technology character varying(200),
    vendor character varying(200),
    platform_design character varying(200),
    biomarkers_studied character varying(200),
    biomarkers_type character varying(200)
);


ALTER TABLE biomart.bio_assay OWNER TO biomart;

--
-- Name: bio_assay_analysis; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_assay_analysis (
    analysis_name character varying(500),
    short_description character varying(510),
    analysis_create_date timestamp without time zone,
    analyst_id character varying(510),
    bio_assay_analysis_id bigint NOT NULL,
    analysis_version character varying(200),
    fold_change_cutoff double precision,
    pvalue_cutoff double precision,
    rvalue_cutoff double precision,
    bio_asy_analysis_pltfm_id bigint,
    bio_source_import_id bigint,
    analysis_type character varying(200),
    analyst_name character varying(250),
    analysis_method_cd character varying(50),
    bio_assay_data_type character varying(50),
    etl_id character varying(100),
    long_description character varying(4000),
    qa_criteria character varying(4000),
    data_count bigint,
    tea_data_count bigint,
    analysis_update_date date,
    lsmean_cutoff double precision,
    etl_id_source bigint
);


ALTER TABLE biomart.bio_assay_analysis OWNER TO biomart;

--
-- Name: bio_assay_analysis_data; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_assay_analysis_data (
    bio_asy_analysis_data_id bigint NOT NULL,
    fold_change_ratio bigint,
    raw_pvalue double precision,
    adjusted_pvalue double precision,
    r_value double precision,
    rho_value double precision,
    bio_assay_analysis_id bigint NOT NULL,
    adjusted_p_value_code character varying(100),
    feature_group_name character varying(100) NOT NULL,
    bio_experiment_id bigint,
    bio_assay_platform_id bigint,
    etl_id character varying(100),
    preferred_pvalue double precision,
    cut_value double precision,
    results_value character varying(100),
    numeric_value double precision,
    numeric_value_code character varying(50),
    tea_normalized_pvalue double precision,
    bio_assay_feature_group_id bigint,
    probeset_id bigint,
    lsmean1 double precision,
    lsmean2 double precision
);


ALTER TABLE biomart.bio_assay_analysis_data OWNER TO biomart;

--
-- Name: bio_assay_analysis_data_tea; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_assay_analysis_data_tea (
    bio_asy_analysis_data_id bigint NOT NULL,
    fold_change_ratio bigint,
    raw_pvalue double precision,
    adjusted_pvalue double precision,
    r_value double precision,
    rho_value double precision,
    bio_assay_analysis_id bigint NOT NULL,
    adjusted_p_value_code character varying(100),
    feature_group_name character varying(100) NOT NULL,
    bio_experiment_id bigint,
    bio_assay_platform_id bigint,
    etl_id character varying(100),
    preferred_pvalue double precision,
    cut_value double precision,
    results_value character varying(100),
    numeric_value double precision,
    numeric_value_code character varying(50),
    tea_normalized_pvalue double precision,
    bio_experiment_type character varying(50),
    bio_assay_feature_group_id bigint,
    tea_rank bigint,
    probeset_id bigint
);


ALTER TABLE biomart.bio_assay_analysis_data_tea OWNER TO biomart;

--
-- Name: bio_assay_analysis_eqtl; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_assay_analysis_eqtl (
    bio_asy_analysis_eqtl_id bigint NOT NULL,
    bio_assay_analysis_id bigint,
    rs_id character varying(50),
    gene character varying(50),
    p_value_char character varying(100),
    cis_trans character varying(10),
    distance_from_gene character varying(10),
    etl_id bigint,
    ext_data character varying(4000),
    p_value double precision,
    log_p_value double precision
);


ALTER TABLE biomart.bio_assay_analysis_eqtl OWNER TO biomart;

--
-- Name: bio_assay_analysis_ext; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_assay_analysis_ext (
    bio_assay_analysis_ext_id bigint NOT NULL,
    bio_assay_analysis_id bigint NOT NULL,
    vendor character varying(500),
    vendor_type character varying(500),
    genome_version character varying(500),
    tissue character varying(500),
    cell_type character varying(500),
    population character varying(500),
    research_unit character varying(500),
    sample_size character varying(500),
    model_name character varying(100),
    model_desc character varying(500),
    sensitive_flag integer,
    sensitive_desc character varying(500)
);


ALTER TABLE biomart.bio_assay_analysis_ext OWNER TO biomart;

--
-- Name: bio_assay_analysis_gwas; Type: TABLE; Schema: biomart; Owner: tm_cz; Tablespace: transmart
--

CREATE TABLE biomart.bio_assay_analysis_gwas (
    bio_asy_analysis_gwas_id bigint NOT NULL,
    bio_assay_analysis_id bigint,
    rs_id character varying(50),
    p_value_char character varying(100),
    etl_id bigint,
    ext_data character varying(4000),
    p_value double precision,
    log_p_value double precision
);


ALTER TABLE biomart.bio_assay_analysis_gwas OWNER TO tm_cz;

--
-- Name: bio_assay_cohort; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_assay_cohort (
    study_id character varying(500),
    cohort_id character varying(500),
    disease character varying(500),
    sample_type character varying(500),
    treatment character varying(500),
    organism character varying(500),
    pathology character varying(500),
    cohort_title character varying(500),
    short_desc character varying(500),
    long_desc character varying(500),
    import_date timestamp(6) without time zone NOT NULL,
    bio_assay_cohort_id bigint NOT NULL
);


ALTER TABLE biomart.bio_assay_cohort OWNER TO biomart;

--
-- Name: bio_assay_data; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_assay_data (
    bio_sample_id bigint,
    bio_assay_data_id bigint NOT NULL,
    log2_value double precision,
    log10_value double precision,
    numeric_value bigint,
    text_value character varying(200),
    float_value double precision,
    feature_group_name character varying(100) NOT NULL,
    bio_experiment_id bigint,
    bio_assay_dataset_id bigint,
    bio_assay_id bigint,
    etl_id bigint
);


ALTER TABLE biomart.bio_assay_data OWNER TO biomart;

--
-- Name: bio_assay_data_annotation; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_assay_data_annotation (
    bio_assay_feature_group_id bigint,
    bio_marker_id bigint NOT NULL,
    data_table character(5)
);


ALTER TABLE biomart.bio_assay_data_annotation OWNER TO biomart;

--
-- Name: bio_assay_data_stats; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_assay_data_stats (
    bio_assay_data_stats_id bigint NOT NULL,
    bio_sample_count bigint,
    quartile_1 double precision,
    quartile_2 double precision,
    quartile_3 double precision,
    max_value double precision,
    min_value double precision,
    bio_sample_id bigint,
    feature_group_name character varying(120),
    value_normalize_method character varying(50),
    bio_experiment_id bigint,
    mean_value double precision,
    std_dev_value double precision,
    bio_assay_dataset_id bigint,
    bio_assay_feature_group_id bigint NOT NULL
);


ALTER TABLE biomart.bio_assay_data_stats OWNER TO biomart;

--
-- Name: bio_assay_data_stats_seq; Type: SEQUENCE; Schema: biomart; Owner: biomart
--

CREATE SEQUENCE biomart.bio_assay_data_stats_seq
    START WITH 34497441
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 20;


ALTER TABLE biomart.bio_assay_data_stats_seq OWNER TO biomart;

--
-- Name: bio_assay_dataset; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_assay_dataset (
    bio_assay_dataset_id bigint NOT NULL,
    dataset_name character varying(400),
    dataset_description character varying(1000),
    dataset_criteria character varying(1000),
    create_date timestamp without time zone,
    bio_experiment_id bigint NOT NULL,
    bio_assay_id bigint,
    etl_id character varying(100),
    accession character varying(50)
);


ALTER TABLE biomart.bio_assay_dataset OWNER TO biomart;

--
-- Name: bio_assay_feature_group; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_assay_feature_group (
    bio_assay_feature_group_id bigint NOT NULL,
    feature_group_name character varying(100) NOT NULL,
    feature_group_type character varying(50) NOT NULL
);


ALTER TABLE biomart.bio_assay_feature_group OWNER TO biomart;

--
-- Name: bio_assay_sample; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_assay_sample (
    bio_assay_id bigint NOT NULL,
    bio_sample_id bigint NOT NULL,
    bio_clinic_trial_timepoint_id bigint NOT NULL
);


ALTER TABLE biomart.bio_assay_sample OWNER TO biomart;

--
-- Name: bio_asy_analysis_data_ext; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_asy_analysis_data_ext (
    bio_asy_analysis_data_id bigint NOT NULL,
    ext_type character varying(20) NOT NULL,
    ext_data character varying(4000) NOT NULL
);


ALTER TABLE biomart.bio_asy_analysis_data_ext OWNER TO biomart;

--
-- Name: bio_asy_analysis_data_idx; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_asy_analysis_data_idx (
    bio_asy_analysis_data_idx_id bigint NOT NULL,
    ext_type character varying(255) NOT NULL,
    field_idx numeric(10,0) NOT NULL,
    field_name character varying(255) NOT NULL,
    display_idx numeric(10,0) NOT NULL,
    display_name character varying(255) NOT NULL
);


ALTER TABLE biomart.bio_asy_analysis_data_idx OWNER TO biomart;

--
-- Name: bio_asy_analysis_dataset; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_asy_analysis_dataset (
    bio_assay_dataset_id bigint NOT NULL,
    bio_assay_analysis_id bigint NOT NULL
);


ALTER TABLE biomart.bio_asy_analysis_dataset OWNER TO biomart;

--
-- Name: bio_asy_analysis_eqtl_top50; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_asy_analysis_eqtl_top50 (
    bio_assay_analysis_id bigint,
    analysis character varying(500),
    chrom character varying(4),
    pos numeric(10,0),
    rsgene character varying(200),
    rsid character varying(50),
    pvalue double precision,
    logpvalue double precision,
    extdata character varying(4000),
    rnum bigint,
    intronexon character varying(10),
    regulome character varying(10),
    recombinationrate numeric(18,6)
);


ALTER TABLE biomart.bio_asy_analysis_eqtl_top50 OWNER TO biomart;

--
-- Name: bio_asy_analysis_gwas_top50; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_asy_analysis_gwas_top50 (
    bio_assay_analysis_id bigint,
    analysis character varying(500),
    chrom character varying(4),
    pos numeric(10,0),
    rsgene character varying(200),
    rsid character varying(50),
    pvalue double precision,
    logpvalue double precision,
    extdata character varying(4000),
    rnum bigint,
    intronexon character varying(10),
    regulome character varying(10),
    recombinationrate numeric(18,6)
);


ALTER TABLE biomart.bio_asy_analysis_gwas_top50 OWNER TO biomart;

--
-- Name: bio_asy_analysis_pltfm; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_asy_analysis_pltfm (
    bio_asy_analysis_pltfm_id bigint NOT NULL,
    platform_name character varying(200),
    platform_version character varying(200),
    platform_description character varying(1000)
);


ALTER TABLE biomart.bio_asy_analysis_pltfm OWNER TO biomart;

--
-- Name: bio_asy_data_stats_all; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_asy_data_stats_all (
    bio_assay_data_stats_id bigint NOT NULL,
    bio_sample_count bigint,
    quartile_1 double precision,
    quartile_2 double precision,
    quartile_3 double precision,
    max_value double precision,
    min_value double precision,
    bio_sample_id bigint,
    feature_group_name character varying(120),
    value_normalize_method character varying(50),
    bio_experiment_id bigint,
    mean_value double precision,
    std_dev_value double precision,
    bio_assay_dataset_id bigint
);


ALTER TABLE biomart.bio_asy_data_stats_all OWNER TO biomart;

--
-- Name: bio_cell_line; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_cell_line (
    disease character varying(510),
    primary_site character varying(510),
    metastatic_site character varying(510),
    species character varying(510),
    attc_number character varying(510),
    cell_line_name character varying(510),
    bio_cell_line_id bigint NOT NULL,
    bio_disease_id bigint,
    origin character varying(200),
    description character varying(500),
    disease_stage character varying(100),
    disease_subtype character varying(200),
    etl_reference_link character varying(300)
);


ALTER TABLE biomart.bio_cell_line OWNER TO biomart;

--
-- Name: bio_cgdcp_data; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_cgdcp_data (
    evidence_code character varying(200),
    negation_indicator character(1),
    cell_line_id bigint,
    nci_disease_concept_code character varying(200),
    nci_role_code character varying(200),
    nci_drug_concept_code character varying(200),
    bio_data_id bigint NOT NULL
);


ALTER TABLE biomart.bio_cgdcp_data OWNER TO biomart;

--
-- Name: bio_clinc_trial_attr; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_clinc_trial_attr (
    bio_clinc_trial_attr_id bigint NOT NULL,
    property_code character varying(200) NOT NULL,
    property_value character varying(200),
    bio_experiment_id bigint NOT NULL
);


ALTER TABLE biomart.bio_clinc_trial_attr OWNER TO biomart;

--
-- Name: bio_clinc_trial_pt_group; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_clinc_trial_pt_group (
    bio_experiment_id bigint NOT NULL,
    bio_clinical_trial_p_group_id bigint NOT NULL,
    name character varying(510),
    description character varying(1000),
    number_of_patients integer,
    patient_group_type_code character varying(200)
);


ALTER TABLE biomart.bio_clinc_trial_pt_group OWNER TO biomart;

--
-- Name: bio_clinc_trial_time_pt; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_clinc_trial_time_pt (
    bio_clinc_trial_tm_pt_id bigint NOT NULL,
    time_point character varying(200),
    time_point_code character varying(200),
    start_date timestamp without time zone,
    end_date timestamp without time zone,
    bio_experiment_id bigint NOT NULL
);


ALTER TABLE biomart.bio_clinc_trial_time_pt OWNER TO biomart;

--
-- Name: bio_clinical_trial; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_clinical_trial (
    trial_number character varying(510),
    study_owner character varying(510),
    study_phase character varying(100),
    blinding_procedure character varying(1000),
    studytype character varying(510),
    duration_of_study_weeks integer,
    number_of_patients integer,
    number_of_sites integer,
    route_of_administration character varying(510),
    dosing_regimen character varying(3500),
    group_assignment character varying(510),
    type_of_control character varying(510),
    completion_date timestamp without time zone,
    primary_end_points character varying(2000),
    secondary_end_points character varying(3500),
    inclusion_criteria text,
    exclusion_criteria text,
    subjects character varying(2000),
    gender_restriction_mfb character varying(510),
    min_age integer,
    max_age integer,
    secondary_ids character varying(510),
    bio_experiment_id bigint NOT NULL,
    development_partner character varying(100),
    geo_platform character varying(30),
    main_findings character varying(2000),
    platform_name character varying(200),
    search_area character varying(100)
);


ALTER TABLE biomart.bio_clinical_trial OWNER TO biomart;

--
-- Name: bio_clinical_trial_design; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_clinical_trial_design (
    ref_id character varying(4000),
    ref_record_id character varying(4000),
    ref_back_reference character varying(4000),
    ref_article_pmid character varying(4000),
    ref_protocol_id character varying(4000),
    ref_title character varying(4000),
    study_type character varying(4000),
    common_name character varying(4000),
    icd10 character varying(4000),
    mesh character varying(4000),
    disease_type character varying(4000),
    physiology_name character varying(4000),
    trial_status character varying(4000),
    trial_phase character varying(4000),
    nature_of_trial character varying(4000),
    randomization character varying(4000),
    blinded_trial character varying(4000),
    trial_type character varying(4000),
    run_n_period character varying(4000),
    treatment_period character varying(4000),
    washout_period character varying(4000),
    open_label_extension character varying(4000),
    sponsor character varying(4000),
    trial_nbr_of_patients_studied character varying(4000),
    source_type character varying(4000),
    trial_age character varying(4000),
    disease_severity character varying(4000),
    difficult_to_treat character varying(4000),
    asthma_diagnosis character varying(4000),
    inhaled_steroid_dose character varying(4000),
    laba character varying(4000),
    ocs character varying(4000),
    xolair character varying(4000),
    ltra_inhibitors character varying(4000),
    asthma_phenotype character varying(4000),
    fev1 character varying(4000),
    fev1_reversibility character varying(4000),
    tlc character varying(4000),
    fev1_fvc character varying(4000),
    fvc character varying(4000),
    dlco character varying(4000),
    sgrq character varying(4000),
    hrct character varying(4000),
    biopsy character varying(4000),
    dyspnea_on_exertion character varying(4000),
    concomitant_med character varying(4000),
    trial_smokers_pct character varying(4000),
    trial_former_smokers_pct character varying(4000),
    trial_never_smokers_pct character varying(4000),
    trial_pack_years character varying(4000),
    exclusion_criteria character varying(4000),
    minimal_symptoms character varying(4000),
    rescue_medication_use character varying(4000),
    control_details character varying(4000),
    blinding_procedure character varying(4000),
    number_of_arms character varying(4000),
    description character varying(4000),
    arm character varying(4000),
    arm_nbr_of_patients_studied character varying(4000),
    arm_classification_type character varying(4000),
    arm_classification_value character varying(4000),
    arm_asthma_duration character varying(4000),
    arm_geographic_region character varying(4000),
    arm_age character varying(4000),
    arm_gender character varying(4000),
    arm_smokers_pct character varying(4000),
    arm_former_smokers_pct character varying(4000),
    arm_never_smokers_pct character varying(4000),
    arm_pack_years character varying(4000),
    minority_participation character varying(4000),
    baseline_symptom_score character varying(4000),
    baseline_rescue_medication_use character varying(4000),
    clinical_variable character varying(4000),
    clinical_variable_pct character varying(4000),
    clinical_variable_value character varying(4000),
    prior_med_drug_name character varying(4000),
    prior_med_pct character varying(4000),
    prior_med_value character varying(4000),
    biomarker_name character varying(4000),
    biomarker_pct character varying(4000),
    biomarkervalue character varying(4000),
    cellinfo_type character varying(4000),
    cellinfo_count character varying(4000),
    cellinfo_source character varying(4000),
    pulmonary_pathology_name character varying(4000),
    pulmpath_patient_pct character varying(4000),
    pulmpath_value_unit character varying(4000),
    pulmpath_method character varying(4000),
    runin_ocs character varying(4000),
    runin_ics character varying(4000),
    runin_laba character varying(4000),
    runin_ltra character varying(4000),
    runin_corticosteroids character varying(4000),
    runin_anti_fibrotics character varying(4000),
    runin_immunosuppressive character varying(4000),
    runin_cytotoxic character varying(4000),
    runin_description character varying(4000),
    trtmt_ocs character varying(4000),
    trtmt_ics character varying(4000),
    trtmt_laba character varying(4000),
    trtmt_ltra character varying(4000),
    trtmt_corticosteroids character varying(4000),
    trtmt_anti_fibrotics character varying(4000),
    trtmt_immunosuppressive character varying(4000),
    trtmt_cytotoxic character varying(4000),
    trtmt_description character varying(4000),
    drug_inhibitor_common_name character varying(4000),
    drug_inhibitor_standard_name character varying(4000),
    drug_inhibitor_cas_id character varying(4000),
    drug_inhibitor_dose character varying(4000),
    drug_inhibitor_route_of_admin character varying(4000),
    drug_inhibitor_trtmt_regime character varying(4000),
    comparator_name character varying(4000),
    comparator_dose character varying(4000),
    comparator_time_period character varying(4000),
    comparator_route_of_admin character varying(4000),
    treatment_regime character varying(4000),
    placebo character varying(4000),
    experiment_description character varying(4000),
    primary_endpoint_type character varying(4000),
    primary_endpoint_definition character varying(4000),
    primary_endpoint_time_period character varying(4000),
    primary_endpoint_change character varying(4000),
    primary_endpoint_p_value character varying(4000),
    primary_endpoint_stat_test character varying(4000),
    secondary_type character varying(4000),
    secondary_type_definition character varying(4000),
    secondary_type_time_period character varying(4000),
    secondary_type_change character varying(4000),
    secondary_type_p_value character varying(4000),
    secondary_type_stat_test character varying(4000),
    clinical_variable_name character varying(4000),
    pct_change_from_baseline character varying(4000),
    abs_change_from_baseline character varying(4000),
    rate_of_change_from_baseline character varying(4000),
    average_over_treatment_period character varying(4000),
    within_group_changes character varying(4000),
    stat_measure_p_value character varying(4000),
    definition_of_the_event character varying(4000),
    number_of_events character varying(4000),
    event_rate character varying(4000),
    time_to_event character varying(4000),
    event_pct_reduction character varying(4000),
    event_p_value character varying(4000),
    event_description character varying(4000),
    discontinuation_rate character varying(4000),
    response_rate character varying(4000),
    downstream_signaling_effects character varying(4000),
    beneficial_effects character varying(4000),
    adverse_effects character varying(4000),
    pk_pd_parameter character varying(4000),
    pk_pd_value character varying(4000),
    effect_description character varying(4000),
    biomolecule_name character varying(4000),
    biomolecule_id character varying(4000),
    biomolecule_type character varying(4000),
    biomarker character varying(4000),
    biomarker_type character varying(4000),
    baseline_expr_pct character varying(4000),
    baseline_expr_number character varying(4000),
    baseline_expr_value_fold_mean character varying(4000),
    baseline_expr_sd character varying(4000),
    baseline_expr_sem character varying(4000),
    baseline_expr_unit character varying(4000),
    expr_after_trtmt_pct character varying(4000),
    expr_after_trtmt_number character varying(4000),
    expr_aftertrtmt_valuefold_mean character varying(4000),
    expr_after_trtmt_sd character varying(4000),
    expr_after_trtmt_sem character varying(4000),
    expr_after_trtmt_unit character varying(4000),
    expr_chg_source_type character varying(4000),
    expr_chg_technique character varying(4000),
    expr_chg_description character varying(4000),
    clinical_correlation character varying(4000),
    statistical_test character varying(4000),
    statistical_coefficient_value character varying(4000),
    statistical_test_p_value character varying(4000),
    statistical_test_description character varying(4000)
);


ALTER TABLE biomart.bio_clinical_trial_design OWNER TO biomart;

--
-- Name: bio_cohort_exp_xref; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_cohort_exp_xref (
    study_id character varying(255),
    cohort_id character varying(255),
    exp_id character varying(255)
);


ALTER TABLE biomart.bio_cohort_exp_xref OWNER TO biomart;

--
-- Name: bio_content; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_content (
    bio_file_content_id bigint NOT NULL,
    file_name character varying(1000),
    repository_id bigint,
    location character varying(400),
    title character varying(1000),
    abstract character varying(2000),
    file_type character varying(200) NOT NULL,
    etl_id bigint,
    etl_id_c character varying(30),
    study_name character varying(30),
    cel_location character varying(300),
    cel_file_suffix character varying(30)
);


ALTER TABLE biomart.bio_content OWNER TO biomart;

--
-- Name: bio_content_reference; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_content_reference (
    bio_content_reference_id bigint NOT NULL,
    bio_content_id bigint NOT NULL,
    bio_data_id bigint NOT NULL,
    content_reference_type character varying(200) NOT NULL,
    etl_id bigint,
    etl_id_c character varying(30)
);


ALTER TABLE biomart.bio_content_reference OWNER TO biomart;

--
-- Name: bio_content_repository; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_content_repository (
    bio_content_repo_id bigint NOT NULL,
    location character varying(510),
    active_y_n character(1),
    repository_type character varying(200) NOT NULL,
    location_type character varying(200)
);


ALTER TABLE biomart.bio_content_repository OWNER TO biomart;

--
-- Name: bio_curated_data; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_curated_data (
    statement text,
    statement_status character varying(200),
    bio_data_id bigint NOT NULL,
    bio_curation_dataset_id bigint NOT NULL,
    reference_id bigint,
    data_type character varying(200)
);


ALTER TABLE biomart.bio_curated_data OWNER TO biomart;

--
-- Name: bio_curation_dataset; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_curation_dataset (
    bio_curation_dataset_id bigint NOT NULL,
    bio_asy_analysis_pltfm_id bigint,
    bio_source_import_id bigint,
    bio_curation_type character varying(200) NOT NULL,
    create_date timestamp without time zone,
    creator bigint,
    bio_curation_name character varying(500),
    data_type character varying(100)
);


ALTER TABLE biomart.bio_curation_dataset OWNER TO biomart;

--
-- Name: bio_data_attribute; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_data_attribute (
    bio_data_attribute_id bigint NOT NULL,
    property_code character varying(200) NOT NULL,
    property_value character varying(200),
    bio_data_id bigint NOT NULL,
    property_unit character varying(100)
);


ALTER TABLE biomart.bio_data_attribute OWNER TO biomart;

--
-- Name: bio_data_compound; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_data_compound (
    bio_data_id bigint NOT NULL,
    bio_compound_id bigint NOT NULL,
    etl_source character varying(100)
);


ALTER TABLE biomart.bio_data_compound OWNER TO biomart;

--
-- Name: bio_data_correl_descr; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_data_correl_descr (
    bio_data_correl_descr_id bigint NOT NULL,
    correlation character varying(510),
    description character varying(1000),
    type_name character varying(200),
    status character varying(200),
    source character varying(100),
    source_code character varying(200)
);


ALTER TABLE biomart.bio_data_correl_descr OWNER TO biomart;

--
-- Name: bio_data_correlation; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_data_correlation (
    bio_data_id bigint NOT NULL,
    asso_bio_data_id bigint NOT NULL,
    bio_data_correl_descr_id bigint NOT NULL,
    bio_data_correl_id bigint NOT NULL
);


ALTER TABLE biomart.bio_data_correlation OWNER TO biomart;

--
-- Name: bio_data_disease; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_data_disease (
    bio_data_id bigint NOT NULL,
    bio_disease_id bigint NOT NULL,
    etl_source character varying(100)
);


ALTER TABLE biomart.bio_data_disease OWNER TO biomart;

--
-- Name: bio_data_ext_code; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_data_ext_code (
    bio_data_id bigint NOT NULL,
    code character varying(500) NOT NULL,
    code_source character varying(200),
    code_type character varying(200),
    bio_data_type character varying(100),
    bio_data_ext_code_id bigint NOT NULL,
    etl_id character varying(50)
);


ALTER TABLE biomart.bio_data_ext_code OWNER TO biomart;

--
-- Name: bio_data_literature; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_data_literature (
    bio_data_id bigint NOT NULL,
    bio_lit_ref_data_id bigint,
    bio_curation_dataset_id bigint NOT NULL,
    statement text,
    statement_status character varying(200),
    data_type character varying(200)
);


ALTER TABLE biomart.bio_data_literature OWNER TO biomart;

--
-- Name: bio_data_observation; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_data_observation (
    bio_data_id bigint,
    bio_observation_id bigint,
    etl_source character varying(100)
);


ALTER TABLE biomart.bio_data_observation OWNER TO biomart;

--
-- Name: bio_data_omic_marker; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_data_omic_marker (
    bio_data_id bigint,
    bio_marker_id bigint NOT NULL,
    data_table character varying(5)
);


ALTER TABLE biomart.bio_data_omic_marker OWNER TO biomart;

--
-- Name: bio_data_platform; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_data_platform (
    bio_data_id bigint,
    bio_assay_platform_id bigint,
    etl_source character varying(100)
);


ALTER TABLE biomart.bio_data_platform OWNER TO biomart;

--
-- Name: bio_data_taxonomy; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_data_taxonomy (
    bio_taxonomy_id bigint NOT NULL,
    bio_data_id bigint NOT NULL,
    etl_source character varying(100)
);


ALTER TABLE biomart.bio_data_taxonomy OWNER TO biomart;

--
-- Name: bio_experiment; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_experiment (
    bio_experiment_id bigint NOT NULL,
    bio_experiment_type character varying(200),
    title character varying(1000),
    description character varying(4000),
    design character varying(2000),
    start_date timestamp without time zone,
    completion_date timestamp without time zone,
    primary_investigator character varying(400),
    contact_field character varying(400),
    etl_id character varying(100),
    status character varying(100),
    overall_design character varying(2000),
    accession character varying(100),
    entrydt timestamp without time zone,
    updated timestamp without time zone,
    institution character varying(400),
    country character varying(1000),
    biomarker_type character varying(255),
    target character varying(255),
    access_type character varying(100)
);


ALTER TABLE biomart.bio_experiment OWNER TO biomart;

--
-- Name: bio_lit_alt_data; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_lit_alt_data (
    bio_lit_alt_data_id bigint NOT NULL,
    bio_lit_ref_data_id bigint NOT NULL,
    in_vivo_model_id bigint,
    in_vitro_model_id bigint,
    etl_id character varying(50),
    alteration_type character varying(50),
    control character varying(1000),
    effect character varying(500),
    description character varying(1000),
    techniques character varying(1000),
    patients_percent character varying(500),
    patients_number character varying(500),
    pop_number character varying(250),
    pop_inclusion_criteria character varying(1000),
    pop_exclusion_criteria character varying(1000),
    pop_description character varying(1000),
    pop_type character varying(250),
    pop_value character varying(250),
    pop_phase character varying(250),
    pop_status character varying(250),
    pop_experimental_model character varying(250),
    pop_tissue character varying(250),
    pop_body_substance character varying(250),
    pop_localization character varying(1000),
    pop_cell_type character varying(250),
    clin_submucosa_marker_type character varying(250),
    clin_submucosa_unit character varying(250),
    clin_submucosa_value character varying(250),
    clin_asm_marker_type character varying(250),
    clin_asm_unit character varying(250),
    clin_asm_value character varying(250),
    clin_cellular_source character varying(250),
    clin_cellular_type character varying(250),
    clin_cellular_count character varying(250),
    clin_prior_med_percent character varying(250),
    clin_prior_med_dose character varying(250),
    clin_prior_med_name character varying(250),
    clin_baseline_variable character varying(250),
    clin_baseline_percent character varying(250),
    clin_baseline_value character varying(250),
    clin_smoker character varying(250),
    clin_atopy character varying(250),
    control_exp_percent character varying(50),
    control_exp_number character varying(50),
    control_exp_value character varying(50),
    control_exp_sd character varying(50),
    control_exp_unit character varying(100),
    over_exp_percent character varying(50),
    over_exp_number character varying(50),
    over_exp_value character varying(50),
    over_exp_sd character varying(50),
    over_exp_unit character varying(100),
    loss_exp_percent character varying(50),
    loss_exp_number character varying(50),
    loss_exp_value character varying(50),
    loss_exp_sd character varying(50),
    loss_exp_unit character varying(100),
    total_exp_percent character varying(50),
    total_exp_number character varying(50),
    total_exp_value character varying(50),
    total_exp_sd character varying(50),
    total_exp_unit character varying(100),
    glc_control_percent character varying(250),
    glc_molecular_change character varying(250),
    glc_type character varying(50),
    glc_percent character varying(100),
    glc_number character varying(100),
    ptm_region character varying(250),
    ptm_type character varying(250),
    ptm_change character varying(250),
    loh_loci character varying(250),
    mutation_type character varying(250),
    mutation_change character varying(250),
    mutation_sites character varying(250),
    epigenetic_region character varying(250),
    epigenetic_type character varying(250)
);


ALTER TABLE biomart.bio_lit_alt_data OWNER TO biomart;

--
-- Name: bio_lit_amd_data; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_lit_amd_data (
    bio_lit_amd_data_id bigint NOT NULL,
    bio_lit_alt_data_id bigint NOT NULL,
    etl_id character varying(50),
    molecule character varying(50),
    molecule_type character varying(50),
    total_exp_percent character varying(50),
    total_exp_number character varying(100),
    total_exp_value character varying(100),
    total_exp_sd character varying(50),
    total_exp_unit character varying(50),
    over_exp_percent character varying(50),
    over_exp_number character varying(100),
    over_exp_value character varying(100),
    over_exp_sd character varying(50),
    over_exp_unit character varying(50),
    co_exp_percent character varying(50),
    co_exp_number character varying(100),
    co_exp_value character varying(100),
    co_exp_sd character varying(50),
    co_exp_unit character varying(50),
    mutation_type character varying(50),
    mutation_sites character varying(50),
    mutation_change character varying(50),
    mutation_percent character varying(50),
    mutation_number character varying(100),
    target_exp_percent character varying(50),
    target_exp_number character varying(100),
    target_exp_value character varying(100),
    target_exp_sd character varying(50),
    target_exp_unit character varying(50),
    target_over_exp_percent character varying(50),
    target_over_exp_number character varying(100),
    target_over_exp_value character varying(100),
    target_over_exp_sd character varying(50),
    target_over_exp_unit character varying(50),
    techniques character varying(250),
    description character varying(1000)
);


ALTER TABLE biomart.bio_lit_amd_data OWNER TO biomart;

--
-- Name: bio_lit_inh_data; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_lit_inh_data (
    bio_lit_inh_data_id bigint NOT NULL,
    bio_lit_ref_data_id bigint,
    etl_id character varying(50),
    trial_type character varying(250),
    trial_phase character varying(250),
    trial_status character varying(250),
    trial_experimental_model character varying(250),
    trial_tissue character varying(250),
    trial_body_substance character varying(250),
    trial_description character varying(1000),
    trial_designs character varying(250),
    trial_cell_line character varying(250),
    trial_cell_type character varying(250),
    trial_patients_number character varying(100),
    trial_inclusion_criteria character varying(2000),
    inhibitor character varying(250),
    inhibitor_standard_name character varying(250),
    casid character varying(250),
    description character varying(1000),
    concentration character varying(250),
    time_exposure character varying(500),
    administration character varying(250),
    treatment character varying(2000),
    techniques character varying(1000),
    effect_molecular character varying(250),
    effect_percent character varying(250),
    effect_number character varying(50),
    effect_value character varying(250),
    effect_sd character varying(250),
    effect_unit character varying(250),
    effect_response_rate character varying(250),
    effect_downstream character varying(2000),
    effect_beneficial character varying(2000),
    effect_adverse character varying(2000),
    effect_description character varying(2000),
    effect_pharmacos character varying(2000),
    effect_potentials character varying(2000)
);


ALTER TABLE biomart.bio_lit_inh_data OWNER TO biomart;

--
-- Name: bio_lit_int_data; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_lit_int_data (
    bio_lit_int_data_id bigint NOT NULL,
    bio_lit_ref_data_id bigint NOT NULL,
    in_vivo_model_id bigint,
    in_vitro_model_id bigint,
    etl_id character varying(50),
    source_component character varying(100),
    source_gene_id character varying(50),
    target_component character varying(100),
    target_gene_id character varying(50),
    interaction_mode character varying(250),
    regulation character varying(1000),
    mechanism character varying(250),
    effect character varying(500),
    localization character varying(500),
    region character varying(250),
    techniques character varying(1000)
);


ALTER TABLE biomart.bio_lit_int_data OWNER TO biomart;

--
-- Name: bio_lit_int_model_mv; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_lit_int_model_mv (
    bio_lit_int_data_id bigint,
    experimental_model character varying(250)
);


ALTER TABLE biomart.bio_lit_int_model_mv OWNER TO biomart;

--
-- Name: bio_lit_model_data; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_lit_model_data (
    bio_lit_model_data_id bigint NOT NULL,
    etl_id character varying(50),
    model_type character varying(50),
    description character varying(1000),
    stimulation character varying(1000),
    control_challenge character varying(500),
    challenge character varying(1000),
    sentization character varying(1000),
    zygosity character varying(250),
    experimental_model character varying(250),
    animal_wild_type character varying(250),
    tissue character varying(250),
    cell_type character varying(250),
    cell_line character varying(250),
    body_substance character varying(250),
    component character varying(250),
    gene_id character varying(250)
);


ALTER TABLE biomart.bio_lit_model_data OWNER TO biomart;

--
-- Name: bio_lit_int_model_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.bio_lit_int_model_view AS
 SELECT DISTINCT s.bio_lit_int_data_id,
    s.experimental_model
   FROM ( SELECT a.bio_lit_int_data_id,
            b.experimental_model
           FROM (biomart.bio_lit_int_data a
             JOIN biomart.bio_lit_model_data b ON ((a.in_vivo_model_id = b.bio_lit_model_data_id)))
          WHERE (b.experimental_model IS NOT NULL)
        UNION
         SELECT a.bio_lit_int_data_id,
            b.experimental_model
           FROM (biomart.bio_lit_int_data a
             JOIN biomart.bio_lit_model_data b ON ((a.in_vitro_model_id = b.bio_lit_model_data_id)))
          WHERE (b.experimental_model IS NOT NULL)) s;


ALTER TABLE biomart.bio_lit_int_model_view OWNER TO biomart;

--
-- Name: bio_lit_pe_data; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_lit_pe_data (
    bio_lit_pe_data_id bigint NOT NULL,
    bio_lit_ref_data_id bigint NOT NULL,
    in_vivo_model_id bigint,
    in_vitro_model_id bigint,
    etl_id character varying(50),
    description character varying(2000)
);


ALTER TABLE biomart.bio_lit_pe_data OWNER TO biomart;

--
-- Name: bio_lit_ref_data; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_lit_ref_data (
    bio_lit_ref_data_id bigint NOT NULL,
    etl_id character varying(50),
    component character varying(100),
    component_class character varying(250),
    gene_id character varying(50),
    molecule_type character varying(50),
    variant character varying(250),
    reference_type character varying(50),
    reference_id character varying(250),
    reference_title character varying(2000),
    back_references character varying(1000),
    study_type character varying(250),
    disease character varying(250),
    disease_icd10 character varying(250),
    disease_mesh character varying(250),
    disease_site character varying(250),
    disease_stage character varying(250),
    disease_grade character varying(250),
    disease_types character varying(250),
    disease_description character varying(1000),
    physiology character varying(250),
    stat_clinical character varying(500),
    stat_clinical_correlation character varying(250),
    stat_tests character varying(500),
    stat_coefficient character varying(500),
    stat_p_value character varying(100),
    stat_description character varying(1000)
);


ALTER TABLE biomart.bio_lit_ref_data OWNER TO biomart;

--
-- Name: bio_lit_sum_data; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_lit_sum_data (
    bio_lit_sum_data_id bigint NOT NULL,
    etl_id character varying(50),
    disease_site character varying(250),
    target character varying(50),
    variant character varying(50),
    data_type character varying(50),
    alteration_type character varying(100),
    total_frequency character varying(50),
    total_affected_cases character varying(50),
    summary character varying(1000)
);


ALTER TABLE biomart.bio_lit_sum_data OWNER TO biomart;

--
-- Name: bio_marker_correl_mv; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.bio_marker_correl_mv AS
 SELECT DISTINCT b.bio_marker_id,
    b.bio_marker_id AS asso_bio_marker_id,
    'GENE'::text AS correl_type,
    1 AS mv_id
   FROM biomart.bio_marker b
  WHERE ((b.bio_marker_type)::text = 'GENE'::text)
UNION
 SELECT DISTINCT b.bio_marker_id,
    b.bio_marker_id AS asso_bio_marker_id,
    'PROTEIN'::text AS correl_type,
    4 AS mv_id
   FROM biomart.bio_marker b
  WHERE ((b.bio_marker_type)::text = 'PROTEIN'::text)
UNION
 SELECT DISTINCT b.bio_marker_id,
    b.bio_marker_id AS asso_bio_marker_id,
    'MIRNA'::text AS correl_type,
    7 AS mv_id
   FROM biomart.bio_marker b
  WHERE ((b.bio_marker_type)::text = 'MIRNA'::text)
UNION
 SELECT DISTINCT c.bio_data_id AS bio_marker_id,
    c.asso_bio_data_id AS asso_bio_marker_id,
    'PATHWAY GENE'::text AS correl_type,
    2 AS mv_id
   FROM biomart.bio_marker b,
    biomart.bio_data_correlation c,
    biomart.bio_data_correl_descr d
  WHERE ((b.bio_marker_id = c.bio_data_id) AND (c.bio_data_correl_descr_id = d.bio_data_correl_descr_id) AND ((b.primary_source_code)::text <> 'ARIADNE'::text) AND ((d.correlation)::text = 'PATHWAY GENE'::text))
UNION
 SELECT DISTINCT c.bio_data_id AS bio_marker_id,
    c.asso_bio_data_id AS asso_bio_marker_id,
    'HOMOLOGENE_GENE'::text AS correl_type,
    3 AS mv_id
   FROM biomart.bio_marker b,
    biomart.bio_data_correlation c,
    biomart.bio_data_correl_descr d
  WHERE ((b.bio_marker_id = c.bio_data_id) AND (c.bio_data_correl_descr_id = d.bio_data_correl_descr_id) AND ((d.correlation)::text = 'HOMOLOGENE GENE'::text))
UNION
 SELECT DISTINCT c.bio_data_id AS bio_marker_id,
    c.asso_bio_data_id AS asso_bio_marker_id,
    'PROTEIN TO GENE'::text AS correl_type,
    5 AS mv_id
   FROM biomart.bio_marker b,
    biomart.bio_data_correlation c,
    biomart.bio_data_correl_descr d
  WHERE ((b.bio_marker_id = c.bio_data_id) AND (c.bio_data_correl_descr_id = d.bio_data_correl_descr_id) AND ((d.correlation)::text = 'PROTEIN TO GENE'::text))
UNION
 SELECT DISTINCT c.bio_data_id AS bio_marker_id,
    c.asso_bio_data_id AS asso_bio_marker_id,
    'GENE TO PROTEIN'::text AS correl_type,
    6 AS mv_id
   FROM biomart.bio_marker b,
    biomart.bio_data_correlation c,
    biomart.bio_data_correl_descr d
  WHERE ((b.bio_marker_id = c.bio_data_id) AND (c.bio_data_correl_descr_id = d.bio_data_correl_descr_id) AND ((d.correlation)::text = 'GENE TO PROTEIN'::text))
UNION
 SELECT DISTINCT c1.bio_data_id AS bio_marker_id,
    c2.asso_bio_data_id AS asso_bio_marker_id,
    'PATHWAY TO PROTEIN'::text AS correl_type,
    8 AS mv_id
   FROM (((biomart.bio_data_correlation c1
     JOIN biomart.bio_data_correlation c2 ON ((c1.asso_bio_data_id = c2.bio_data_id)))
     JOIN biomart.bio_data_correl_descr d1 ON ((c1.bio_data_correl_descr_id = d1.bio_data_correl_descr_id)))
     JOIN biomart.bio_data_correl_descr d2 ON ((c2.bio_data_correl_descr_id = d2.bio_data_correl_descr_id)))
  WHERE (((d1.correlation)::text = 'PATHWAY GENE'::text) AND ((d2.correlation)::text = 'GENE TO PROTEIN'::text))
UNION
 SELECT DISTINCT b.bio_marker_id,
    b.bio_marker_id AS asso_bio_marker_id,
    'METABOLITE'::text AS correl_type,
    9 AS mv_id
   FROM biomart.bio_marker b
  WHERE ((b.bio_marker_type)::text = 'METABOLITE'::text);


ALTER TABLE biomart.bio_marker_correl_mv OWNER TO biomart;

--
-- Name: bio_marker_correl_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.bio_marker_correl_view AS
 SELECT DISTINCT b.bio_marker_id,
    b.bio_marker_id AS asso_bio_marker_id,
    'GENE'::text AS correl_type,
    1 AS mv_id
   FROM biomart.bio_marker b
  WHERE ((b.bio_marker_type)::text = 'GENE'::text)
UNION
 SELECT DISTINCT c.bio_data_id AS bio_marker_id,
    c.asso_bio_data_id AS asso_bio_marker_id,
    'PATHWAY_GENE'::text AS correl_type,
    2 AS mv_id
   FROM biomart.bio_marker b,
    biomart.bio_data_correlation c,
    biomart.bio_data_correl_descr d
  WHERE ((b.bio_marker_id = c.bio_data_id) AND (c.bio_data_correl_descr_id = d.bio_data_correl_descr_id) AND ((b.primary_source_code)::text <> 'ARIADNE'::text) AND ((d.correlation)::text = 'PATHWAY GENE'::text))
UNION
 SELECT DISTINCT c.bio_data_id AS bio_marker_id,
    c.asso_bio_data_id AS asso_bio_marker_id,
    'HOMOLOGENE_GENE'::text AS correl_type,
    3 AS mv_id
   FROM biomart.bio_marker b,
    biomart.bio_data_correlation c,
    biomart.bio_data_correl_descr d
  WHERE ((b.bio_marker_id = c.bio_data_id) AND (c.bio_data_correl_descr_id = d.bio_data_correl_descr_id) AND ((d.correlation)::text = 'HOMOLOGENE GENE'::text));


ALTER TABLE biomart.bio_marker_correl_view OWNER TO biomart;

--
-- Name: bio_marker_exp_analysis_mv; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.bio_marker_exp_analysis_mv AS
 SELECT DISTINCT t3.bio_marker_id,
    t1.bio_experiment_id,
    t1.bio_assay_analysis_id,
    ((t1.bio_assay_analysis_id * 100) + t3.bio_marker_id) AS mv_id
   FROM biomart.bio_assay_analysis_data t1,
    biomart.bio_experiment t2,
    biomart.bio_marker t3,
    biomart.bio_assay_data_annotation t4
  WHERE ((t1.bio_experiment_id = t2.bio_experiment_id) AND ((t2.bio_experiment_type)::text = 'Experiment'::text) AND (t3.bio_marker_id = t4.bio_marker_id) AND (t1.bio_assay_feature_group_id = t4.bio_assay_feature_group_id));


ALTER TABLE biomart.bio_marker_exp_analysis_mv OWNER TO biomart;

--
-- Name: de_metabolite_annotation; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_metabolite_annotation (
    id bigint NOT NULL,
    gpl_id character varying(50) NOT NULL,
    biochemical_name character varying(200) NOT NULL,
    biomarker_id character varying(200),
    hmdb_id character varying(50)
);


ALTER TABLE deapp.de_metabolite_annotation OWNER TO deapp;

--
-- Name: de_metabolite_sub_pathways; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_metabolite_sub_pathways (
    id bigint NOT NULL,
    gpl_id character varying(50) NOT NULL,
    sub_pathway_name character varying(200) NOT NULL,
    super_pathway_id bigint
);


ALTER TABLE deapp.de_metabolite_sub_pathways OWNER TO deapp;

--
-- Name: de_metabolite_sub_pway_metab; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_metabolite_sub_pway_metab (
    metabolite_id bigint NOT NULL,
    sub_pathway_id bigint NOT NULL
);


ALTER TABLE deapp.de_metabolite_sub_pway_metab OWNER TO deapp;

--
-- Name: bio_metab_subpathway_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.bio_metab_subpathway_view AS
 SELECT sp.id AS subpathway_id,
    b.bio_marker_id AS asso_bio_marker_id,
    'SUBPATHWAY TO METABOLITE'::text AS correl_type
   FROM (((deapp.de_metabolite_sub_pathways sp
     JOIN deapp.de_metabolite_sub_pway_metab j ON ((sp.id = j.sub_pathway_id)))
     JOIN deapp.de_metabolite_annotation m ON ((m.id = j.metabolite_id)))
     JOIN biomart.bio_marker b ON ((((b.bio_marker_type)::text = 'METABOLITE'::text) AND ((b.primary_external_id)::text = (m.hmdb_id)::text))));


ALTER TABLE biomart.bio_metab_subpathway_view OWNER TO biomart;

--
-- Name: de_metabolite_super_pathways; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_metabolite_super_pathways (
    id bigint NOT NULL,
    gpl_id character varying(50) NOT NULL,
    super_pathway_name character varying(200)
);


ALTER TABLE deapp.de_metabolite_super_pathways OWNER TO deapp;

--
-- Name: bio_metab_superpathway_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.bio_metab_superpathway_view AS
 SELECT supp.id AS superpathway_id,
    b.bio_marker_id AS asso_bio_marker_id,
    'SUPERPATHWAY TO METABOLITE'::text AS correl_type
   FROM ((((deapp.de_metabolite_super_pathways supp
     JOIN deapp.de_metabolite_sub_pathways subp ON ((supp.id = subp.super_pathway_id)))
     JOIN deapp.de_metabolite_sub_pway_metab j ON ((subp.id = j.sub_pathway_id)))
     JOIN deapp.de_metabolite_annotation m ON ((m.id = j.metabolite_id)))
     JOIN biomart.bio_marker b ON ((((b.bio_marker_type)::text = 'METABOLITE'::text) AND ((b.primary_external_id)::text = (m.hmdb_id)::text))));


ALTER TABLE biomart.bio_metab_superpathway_view OWNER TO biomart;

--
-- Name: bio_patient; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_patient (
    bio_patient_id bigint NOT NULL,
    first_name character varying(200),
    last_name character varying(200),
    middle_name character varying(200),
    birth_date timestamp without time zone,
    birth_date_orig character varying(200),
    gender_code character varying(200),
    race_code character varying(200),
    ethnic_group_code character varying(200),
    address_zip_code character varying(200),
    country_code character varying(200),
    informed_consent_code character varying(200),
    bio_experiment_id bigint,
    bio_clinical_trial_p_group_id bigint
);


ALTER TABLE biomart.bio_patient OWNER TO biomart;

--
-- Name: bio_patient_event; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_patient_event (
    bio_patient_event_id bigint NOT NULL,
    bio_patient_id bigint NOT NULL,
    event_code character varying(200),
    event_type_code character varying(200),
    event_date timestamp without time zone,
    site character varying(400),
    bio_clinic_trial_timepoint_id bigint NOT NULL
);


ALTER TABLE biomart.bio_patient_event OWNER TO biomart;

--
-- Name: bio_patient_event_attr; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_patient_event_attr (
    bio_patient_attr_code character varying(200) NOT NULL,
    attribute_text_value character varying(200),
    attribute_numeric_value character varying(200),
    bio_clinic_trial_attr_id bigint NOT NULL,
    bio_patient_attribute_id bigint NOT NULL,
    bio_patient_event_id bigint NOT NULL
);


ALTER TABLE biomart.bio_patient_event_attr OWNER TO biomart;

--
-- Name: bio_recombination_rates; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_recombination_rates (
    chromosome character varying(20),
    "position" numeric(18,0),
    rate numeric(18,6),
    map numeric(18,6)
);


ALTER TABLE biomart.bio_recombination_rates OWNER TO biomart;

--
-- Name: bio_regulome_score; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_regulome_score (
    chromosome character varying(20),
    "position" numeric(18,0),
    rs_id character varying(100),
    score character varying(10)
);


ALTER TABLE biomart.bio_regulome_score OWNER TO biomart;

--
-- Name: bio_sample; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_sample (
    bio_sample_id bigint NOT NULL,
    bio_sample_type character varying(200) NOT NULL,
    characteristics character varying(1000),
    source_code character varying(200),
    experiment_id bigint,
    bio_subject_id bigint,
    source character varying(200),
    bio_bank_id bigint,
    bio_patient_event_id bigint,
    bio_cell_line_id bigint,
    bio_sample_name character varying(100)
);


ALTER TABLE biomart.bio_sample OWNER TO biomart;

--
-- Name: bio_species_organism; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_species_organism (
    id bigint,
    species character varying(200),
    organism character varying(200)
);


ALTER TABLE biomart.bio_species_organism OWNER TO biomart;

--
-- Name: bio_stats_exp_marker; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_stats_exp_marker (
    bio_marker_id bigint NOT NULL,
    bio_experiment_id bigint NOT NULL,
    bio_stats_exp_marker_id bigint
);


ALTER TABLE biomart.bio_stats_exp_marker OWNER TO biomart;

--
-- Name: bio_subject; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_subject (
    bio_subject_id bigint NOT NULL,
    site_subject_id bigint,
    source character varying(200),
    source_code character varying(200),
    status character varying(200),
    organism character varying(200),
    bio_subject_type character varying(200) NOT NULL
);


ALTER TABLE biomart.bio_subject OWNER TO biomart;

--
-- Name: bio_taxonomy; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.bio_taxonomy (
    bio_taxonomy_id bigint NOT NULL,
    taxon_name character varying(200) NOT NULL,
    taxon_label character varying(200) NOT NULL,
    ncbi_tax_id character varying(200)
);


ALTER TABLE biomart.bio_taxonomy OWNER TO biomart;

--
-- Name: biobank_sample; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.biobank_sample (
    sample_tube_id character varying(255) NOT NULL,
    accession_number character varying(255) NOT NULL,
    client_sample_tube_id character varying(255) NOT NULL,
    container_id character varying(255) NOT NULL,
    import_date timestamp without time zone,
    source_type character varying(255) NOT NULL
);


ALTER TABLE biomart.biobank_sample OWNER TO biomart;

--
-- Name: cta_results; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.cta_results (
    bio_assay_analysis_id bigint,
    search_keyword_id bigint,
    keyword character varying(400),
    bio_marker_id bigint,
    bio_marker_name character varying(200),
    gene_id character varying(200),
    probe_id character varying(100),
    fold_change double precision,
    preferred_pvalue double precision,
    organism character varying(100)
);


ALTER TABLE biomart.cta_results OWNER TO biomart;

--
-- Name: ctd2_clin_inhib_effect; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.ctd2_clin_inhib_effect (
    ctd_cie_seq bigint,
    ctd_study_id bigint,
    event_description_name character varying(4000),
    event_definition_name character varying(4000),
    adverse_effect_name character varying(4000),
    signal_effect_name character varying(4000),
    pharmaco_parameter_name character varying(500),
    discontinuation_rate_value character varying(250),
    beneficial_effect_name character varying(4000),
    drug_effect character varying(4000),
    clinical_variable_name character varying(250),
    qp_sm_percentage_change character varying(250),
    qp_sm_absolute_change character varying(250),
    qp_sm_rate_of_change character varying(250),
    qp_sm_treatment_period character varying(250),
    qp_sm_group_change character varying(250),
    qp_sm_p_value character varying(250),
    ce_sm_no character varying(250),
    ce_sm_event_rate character varying(250),
    ce_time_to_event character varying(250),
    ce_reduction character varying(250),
    ce_p_value character varying(250),
    clinical_correlation character varying(2000),
    coefficient_value character varying(250),
    statistics_p_value character varying(250),
    statistics_description character varying(4000),
    primary_endpoint_type character varying(250),
    primary_endpoint_definition character varying(4000),
    primary_endpoint_test_name character varying(2000),
    primary_endpoint_time_period character varying(2000),
    primary_endpoint_change character varying(2000),
    primary_endpoint_p_value character varying(2000),
    secondary_endpoint_type character varying(2000),
    secondary_endpoint_definition character varying(4000),
    secondary_endpoint_test_name character varying(2000),
    secondary_endpoint_time_period character varying(4000),
    secondary_endpoint_change character varying(4000),
    secondary_endpoint_p_value character varying(4000)
);


ALTER TABLE biomart.ctd2_clin_inhib_effect OWNER TO biomart;

--
-- Name: ctd2_disease; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.ctd2_disease (
    ctd_disease_seq bigint,
    ctd_study_id bigint,
    disease_type_name character varying(500),
    disease_common_name character varying(500),
    icd10_name character varying(250),
    mesh_name character varying(250),
    study_type_name character varying(2000),
    physiology_name character varying(500)
);


ALTER TABLE biomart.ctd2_disease OWNER TO biomart;

--
-- Name: ctd2_inhib_details; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.ctd2_inhib_details (
    ctd_inhib_seq bigint,
    ctd_study_id bigint,
    common_name_name character varying(500),
    standard_name_name character varying(500),
    experimental_detail_dose character varying(4000),
    exp_detail_exposure_period character varying(4000),
    exp_detail_treatment_name character varying(4000),
    exp_detail_admin_route character varying(4000),
    exp_detail_description character varying(4000),
    exp_detail_placebo character varying(250),
    comparator_name_name character varying(250),
    comp_treatment_name character varying(4000),
    comp_admin_route character varying(4000),
    comp_dose character varying(2000),
    comp_exposure_period character varying(2000)
);


ALTER TABLE biomart.ctd2_inhib_details OWNER TO biomart;

--
-- Name: ctd2_study; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.ctd2_study (
    ctd_study_id bigint,
    ref_article_protocol_id character varying(1000),
    reference_id integer NOT NULL,
    pubmed_id character varying(250),
    pubmed_title character varying(2000),
    protocol_id character varying(1000),
    protocol_title character varying(2000)
);


ALTER TABLE biomart.ctd2_study OWNER TO biomart;

--
-- Name: ctd2_trial_details; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.ctd2_trial_details (
    ctd_td_seq bigint,
    ctd_study_id bigint,
    control character varying(2000),
    blinding_procedure character varying(2000),
    no_of_arms character varying(2000),
    sponsor character varying(2000),
    patient_studied character varying(2000),
    source_type character varying(2000),
    trial_description character varying(4000),
    arm_name character varying(250),
    patient_study character varying(250),
    class_type character varying(250),
    class_value character varying(250),
    asthma_duration character varying(250),
    region character varying(250),
    age character varying(100),
    gender character varying(100),
    minor_participation character varying(100),
    symptom_score character varying(100),
    rescue_medication character varying(2000),
    therapeutic_intervention character varying(255),
    smokers character varying(255),
    former_smokers character varying(255),
    never_smokers character varying(255),
    smoking_pack_years character varying(255),
    pulm_path_name character varying(255),
    pulm_path_pct character varying(50),
    pulm_path_value character varying(50),
    pulm_path_method character varying(255),
    allow_med_therapy_ocs character varying(1000),
    allow_med_therapy_ics character varying(1000),
    allow_med_therapy_laba character varying(1000),
    allow_med_therapy_ltra character varying(1000),
    allow_med_therapy_desc character varying(4000),
    allow_med_therapy_cortster character varying(1000),
    allow_med_therapy_immuno character varying(1000),
    allow_med_therapy_cyto character varying(1000),
    allow_med_treat_ocs character varying(1000),
    allow_med_treat_ics character varying(1000),
    allow_med_treat_laba character varying(1000),
    allow_med_treat_ltra character varying(1000),
    allow_med_treat_desc character varying(4000),
    allow_med_treat_cortster character varying(1000),
    allow_med_treat_immuno character varying(1000),
    allow_med_treat_cyto character varying(1000),
    pat_char_base_clin_var character varying(500),
    pat_char_base_clin_var_pct character varying(250),
    pat_char_base_clin_var_value character varying(250),
    biomarker_name_name character varying(250),
    pat_char_biomarker_pct character varying(250),
    pat_char_biomarker_value character varying(250),
    pat_char_cellinfo_name character varying(250),
    pat_char_cellinfo_type character varying(250),
    pat_char_cellinfo_count character varying(250),
    pat_char_priormed_name character varying(250),
    pat_char_priormed_pct character varying(500),
    pat_char_priormed_dose character varying(250),
    disease_phenotype_name character varying(1000),
    disease_severity_name character varying(500),
    incl_age character varying(2000),
    incl_difficult_to_treat character varying(2000),
    incl_disease_diagnosis character varying(2000),
    incl_steroid_dose character varying(2000),
    incl_laba character varying(2000),
    incl_ocs character varying(2000),
    incl_xolair character varying(2000),
    incl_ltra_inhibitor character varying(2000),
    incl_fev1 character varying(2000),
    incl_fev1_reversibility character varying(2000),
    incl_smoking character varying(2000),
    incl_tlc character varying(2000),
    incl_fvc character varying(2000),
    incl_dlco character varying(2000),
    incl_sgrq character varying(2000),
    incl_hrct character varying(2000),
    incl_biopsy character varying(2000),
    incl_dypsnea_on_exertion character varying(2000),
    incl_concomitant_med character varying(2000),
    incl_former_smokers character varying(2000),
    incl_never_smokers character varying(2000),
    incl_smoking_pack_years character varying(2000),
    incl_fev_fvc character varying(2000),
    trial_des_minimal_symptom character varying(2000),
    trial_des_rescue_med character varying(4000),
    trial_des_exclusion_criteria character varying(4000),
    trial_des_open_label_status character varying(250),
    trial_des_random_status character varying(250),
    trial_des_nature_of_trial character varying(250),
    trial_des_blinded_status character varying(250),
    trial_des_run_in_period character varying(2000),
    trial_des_treatment character varying(2000),
    trial_des_washout_period character varying(2000),
    trial_status_name character varying(2000),
    trial_phase_name character varying(2000)
);


ALTER TABLE biomart.ctd2_trial_details OWNER TO biomart;

--
-- Name: ctd_allowed_meds_treatment; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.ctd_allowed_meds_treatment (
    ctd_study_id bigint,
    trtmt_ocs character varying(4000),
    trtmt_ics character varying(4000),
    trtmt_laba character varying(4000),
    trtmt_ltra character varying(4000),
    trtmt_corticosteroids character varying(4000),
    trtmt_anti_fibrotics character varying(4000),
    trtmt_immunosuppressive character varying(4000),
    trtmt_cytotoxic character varying(4000)
);


ALTER TABLE biomart.ctd_allowed_meds_treatment OWNER TO biomart;

--
-- Name: ctd_full; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.ctd_full (
    clinical_trial_design_id bigint,
    ref_article_protocol_id character varying(4000),
    ref_record_id character varying(4000),
    ref_back_reference character varying(4000),
    ref_article_pmid character varying(4000),
    ref_protocol_id character varying(4000),
    ref_title character varying(4000),
    study_type character varying(4000),
    common_name character varying(4000),
    icd10 character varying(4000),
    mesh character varying(4000),
    disease_type character varying(4000),
    physiology_name character varying(4000),
    trial_status character varying(4000),
    trial_phase character varying(4000),
    nature_of_trial character varying(4000),
    randomization character varying(4000),
    blinded_trial character varying(4000),
    trial_type character varying(4000),
    run_in_period character varying(4000),
    treatment_period character varying(4000),
    washout_period character varying(4000),
    open_label_extension character varying(4000),
    sponsor character varying(4000),
    trial_nbr_of_patients_studied character varying(4000),
    source_type character varying(4000),
    trial_age character varying(4000),
    disease_severity character varying(4000),
    difficult_to_treat character varying(4000),
    asthma_diagnosis character varying(4000),
    inhaled_steroid_dose character varying(4000),
    laba character varying(4000),
    ocs character varying(4000),
    xolair character varying(4000),
    ltra_inhibitors character varying(4000),
    asthma_phenotype character varying(4000),
    fev1 character varying(4000),
    fev1_reversibility character varying(4000),
    tlc character varying(4000),
    fev1_fvc character varying(4000),
    fvc character varying(4000),
    dlco character varying(4000),
    sgrq character varying(4000),
    hrct character varying(4000),
    biopsy character varying(4000),
    dyspnea_on_exertion character varying(4000),
    concomitant_med character varying(4000),
    trial_smokers_pct character varying(4000),
    trial_former_smokers_pct character varying(4000),
    trial_never_smokers_pct character varying(4000),
    trial_pack_years character varying(4000),
    exclusion_criteria text,
    minimal_symptoms character varying(4000),
    rescue_medication_use character varying(4000),
    control_details character varying(4000),
    blinding_procedure character varying(4000),
    number_of_arms character varying(4000),
    description text,
    arm character varying(4000),
    arm_nbr_of_patients_studied character varying(4000),
    arm_classification_type character varying(4000),
    arm_classification_value character varying(4000),
    arm_asthma_duration character varying(4000),
    arm_geographic_region character varying(4000),
    arm_age character varying(4000),
    arm_gender character varying(4000),
    arm_smokers_pct character varying(4000),
    arm_former_smokers_pct character varying(4000),
    arm_never_smokers_pct character varying(4000),
    arm_pack_years character varying(4000),
    minority_participation character varying(4000),
    baseline_symptom_score character varying(4000),
    baseline_rescue_medication_use character varying(4000),
    clinical_variable character varying(4000),
    clinical_variable_pct character varying(4000),
    clinical_variable_value character varying(4000),
    prior_med_drug_name character varying(4000),
    prior_med_pct character varying(4000),
    prior_med_value character varying(4000),
    biomarker_name character varying(4000),
    biomarker_pct character varying(4000),
    biomarker_value character varying(4000),
    cellinfo_type character varying(4000),
    cellinfo_count character varying(4000),
    cellinfo_source character varying(4000),
    pulmonary_pathology_name character varying(4000),
    pulmpath_patient_pct character varying(4000),
    pulmpath_value_unit character varying(4000),
    pulmpath_method character varying(4000),
    runin_ocs character varying(4000),
    runin_ics character varying(4000),
    runin_laba character varying(4000),
    runin_ltra character varying(4000),
    runin_corticosteroids character varying(4000),
    runin_anti_fibrotics character varying(4000),
    runin_immunosuppressive character varying(4000),
    runin_cytotoxic character varying(4000),
    runin_description character varying(4000),
    trtmt_ocs character varying(4000),
    trtmt_ics character varying(4000),
    trtmt_laba character varying(4000),
    trtmt_ltra character varying(4000),
    trtmt_corticosteroids character varying(4000),
    trtmt_anti_fibrotics character varying(4000),
    trtmt_immunosuppressive character varying(4000),
    trtmt_cytotoxic character varying(4000),
    trtmt_description character varying(4000),
    drug_inhibitor_common_name character varying(4000),
    drug_inhibitor_standard_name character varying(4000),
    drug_inhibitor_cas_id character varying(4000),
    drug_inhibitor_dose character varying(4000),
    drug_inhibitor_route_of_admin character varying(4000),
    drug_inhibitor_trtmt_regime character varying(4000),
    comparator_name character varying(4000),
    comparator_dose character varying(4000),
    comparator_time_period character varying(4000),
    comparator_route_of_admin character varying(4000),
    treatment_regime character varying(4000),
    placebo character varying(4000),
    experiment_description character varying(4000),
    primary_endpoint_type character varying(4000),
    primary_endpoint_definition character varying(4000),
    primary_endpoint_change character varying(4000),
    primary_endpoint_time_period character varying(4000),
    primary_endpoint_stat_test character varying(4000),
    primary_endpoint_p_value character varying(4000),
    secondary_type character varying(4000),
    secondary_type_definition character varying(4000),
    secondary_type_change character varying(4000),
    secondary_type_time_period character varying(4000),
    secondary_type_p_value character varying(4000),
    secondary_type_stat_test character varying(4000),
    clinical_variable_name character varying(4000),
    pct_change_from_baseline character varying(4000),
    abs_change_from_baseline character varying(4000),
    rate_of_change_from_baseline character varying(4000),
    average_over_treatment_period character varying(4000),
    within_group_changes character varying(4000),
    stat_measure_p_value character varying(4000),
    definition_of_the_event character varying(4000),
    number_of_events character varying(4000),
    event_rate character varying(4000),
    time_to_event character varying(4000),
    event_pct_reduction character varying(4000),
    event_p_value character varying(4000),
    event_description character varying(4000),
    discontinuation_rate character varying(4000),
    response_rate character varying(4000),
    downstream_signaling_effects character varying(4000),
    beneficial_effects character varying(4000),
    adverse_effects character varying(4000),
    pk_pd_parameter character varying(4000),
    pk_pd_value character varying(4000),
    effect_description character varying(4000),
    biomolecule_name character varying(4000),
    biomolecule_id character varying(4000),
    biomolecule_type character varying(4000),
    biomarker character varying(4000),
    biomarker_type character varying(4000),
    baseline_expr_pct character varying(4000),
    baseline_expr_number character varying(4000),
    baseline_expr_value_fold_mean character varying(4000),
    baseline_expr_sd character varying(4000),
    baseline_expr_sem character varying(4000),
    baseline_expr_unit character varying(4000),
    expr_after_trtmt_pct character varying(4000),
    expr_after_trtmt_number character varying(4000),
    expr_aftertrtmt_valuefold_mean character varying(4000),
    expr_after_trtmt_sd character varying(4000),
    expr_after_trtmt_sem character varying(4000),
    expr_after_trtmt_unit character varying(4000),
    expr_chg_source_type character varying(4000),
    expr_chg_technique character varying(4000),
    expr_chg_description character varying(4000),
    clinical_correlation character varying(4000),
    statistical_test character varying(4000),
    statistical_coefficient_value character varying(4000),
    statistical_test_p_value character varying(4000),
    statistical_test_description character varying(4000),
    drug_inhibitor_time_period character varying(4000)
);


ALTER TABLE biomart.ctd_full OWNER TO biomart;

--
-- Name: ctd_arm_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.ctd_arm_view AS
 SELECT row_number() OVER (ORDER BY v.ref_article_protocol_id, v.arm, v.arm_nbr_of_patients_studied) AS id,
    v.ref_article_protocol_id,
    v.arm,
    v.arm_nbr_of_patients_studied,
    v.arm_classification_type,
    v.arm_classification_value,
    v.arm_asthma_duration,
    v.arm_geographic_region,
    v.arm_age,
    v.arm_gender,
    v.arm_smokers_pct,
    v.arm_former_smokers_pct,
    v.arm_never_smokers_pct,
    v.arm_pack_years,
    v.minority_participation,
    v.baseline_symptom_score,
    v.baseline_rescue_medication_use
   FROM ( SELECT DISTINCT ctd_full.ref_article_protocol_id,
            ctd_full.arm,
            to_number((ctd_full.arm_nbr_of_patients_studied)::text, '999999999999999'::text) AS arm_nbr_of_patients_studied,
            ctd_full.arm_classification_type,
            ctd_full.arm_classification_value,
            ctd_full.arm_asthma_duration,
            ctd_full.arm_geographic_region,
            ctd_full.arm_age,
            ctd_full.arm_gender,
            ctd_full.arm_smokers_pct,
            ctd_full.arm_former_smokers_pct,
            ctd_full.arm_never_smokers_pct,
            ctd_full.arm_pack_years,
            ctd_full.minority_participation,
            ctd_full.baseline_symptom_score,
            ctd_full.baseline_rescue_medication_use
           FROM biomart.ctd_full
          WHERE ((ctd_full.arm IS NOT NULL) AND ((ctd_full.arm)::text <> ''::text))
          ORDER BY ctd_full.ref_article_protocol_id, ctd_full.arm, (to_number((ctd_full.arm_nbr_of_patients_studied)::text, '999999999999999'::text))) v;


ALTER TABLE biomart.ctd_arm_view OWNER TO biomart;

--
-- Name: ctd_biomarker; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.ctd_biomarker (
    ctd_study_id bigint,
    biomarker_name character varying(4000),
    biomarker_pct character varying(4000),
    biomarker_value character varying(4000)
);


ALTER TABLE biomart.ctd_biomarker OWNER TO biomart;

--
-- Name: ctd_biomarker_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.ctd_biomarker_view AS
 SELECT row_number() OVER (ORDER BY v.ref_article_protocol_id, v.biomarker_name) AS id,
    v.ref_article_protocol_id,
    v.biomarker_name,
    v.biomarker_pct,
    v.biomarker_value
   FROM ( SELECT DISTINCT ctd_full.ref_article_protocol_id,
            ctd_full.biomarker_name,
            ctd_full.biomarker_pct,
            ctd_full.biomarker_value
           FROM biomart.ctd_full
          WHERE ((ctd_full.biomarker_name IS NOT NULL) AND ((ctd_full.biomarker_name)::text <> ''::text))
          ORDER BY ctd_full.ref_article_protocol_id, ctd_full.biomarker_name) v;


ALTER TABLE biomart.ctd_biomarker_view OWNER TO biomart;

--
-- Name: ctd_cell_info_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.ctd_cell_info_view AS
 SELECT row_number() OVER (ORDER BY v.ref_article_protocol_id, v.cellinfo_type) AS id,
    v.ref_article_protocol_id,
    v.cellinfo_type,
    v.cellinfo_count,
    v.cellinfo_source
   FROM ( SELECT DISTINCT ctd_full.ref_article_protocol_id,
            ctd_full.cellinfo_type,
            ctd_full.cellinfo_count,
            ctd_full.cellinfo_source
           FROM biomart.ctd_full
          WHERE ((ctd_full.cellinfo_type IS NOT NULL) AND ((ctd_full.cellinfo_type)::text <> ''::text))
          ORDER BY ctd_full.ref_article_protocol_id, ctd_full.cellinfo_type) v;


ALTER TABLE biomart.ctd_cell_info_view OWNER TO biomart;

--
-- Name: ctd_clinical_chars_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.ctd_clinical_chars_view AS
 SELECT row_number() OVER (ORDER BY v.ref_article_protocol_id, v.clinical_variable) AS id,
    v.ref_article_protocol_id,
    v.clinical_variable,
    v.clinical_variable_pct,
    v.clinical_variable_value
   FROM ( SELECT DISTINCT ctd_full.ref_article_protocol_id,
            ctd_full.clinical_variable,
            ctd_full.clinical_variable_pct,
            ctd_full.clinical_variable_value
           FROM biomart.ctd_full
          WHERE ((ctd_full.clinical_variable IS NOT NULL) AND ((ctd_full.clinical_variable)::text <> ''::text))
          ORDER BY ctd_full.ref_article_protocol_id, ctd_full.clinical_variable) v;


ALTER TABLE biomart.ctd_clinical_chars_view OWNER TO biomart;

--
-- Name: ctd_disease; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.ctd_disease (
    ctd_study_id bigint,
    common_name character varying(4000),
    icd10 character varying(4000),
    mesh character varying(4000),
    disease_severity character varying(4000)
);


ALTER TABLE biomart.ctd_disease OWNER TO biomart;

--
-- Name: ctd_drug_effects_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.ctd_drug_effects_view AS
 SELECT row_number() OVER (ORDER BY v.ref_article_protocol_id, v.beneficial_effects, v.adverse_effects) AS id,
    v.ref_article_protocol_id,
    v.discontinuation_rate,
    v.response_rate,
    v.downstream_signaling_effects,
    v.beneficial_effects,
    v.adverse_effects,
    v.pk_pd_parameter,
    v.pk_pd_value,
    v.effect_description
   FROM ( SELECT DISTINCT ctd_full.ref_article_protocol_id,
            ctd_full.discontinuation_rate,
            ctd_full.response_rate,
            ctd_full.downstream_signaling_effects,
            ctd_full.beneficial_effects,
            ctd_full.adverse_effects,
            ctd_full.pk_pd_parameter,
            ctd_full.pk_pd_value,
            ctd_full.effect_description
           FROM biomart.ctd_full
          ORDER BY ctd_full.ref_article_protocol_id, ctd_full.beneficial_effects, ctd_full.adverse_effects) v;


ALTER TABLE biomart.ctd_drug_effects_view OWNER TO biomart;

--
-- Name: ctd_drug_inhib; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.ctd_drug_inhib (
    ctd_study_id bigint,
    drug_inhibitor_common_name character varying(4000),
    drug_inhibitor_standard_name character varying(4000),
    drug_inhibitor_cas_id character varying(4000)
);


ALTER TABLE biomart.ctd_drug_inhib OWNER TO biomart;

--
-- Name: ctd_drug_inhibitor_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.ctd_drug_inhibitor_view AS
 SELECT row_number() OVER (ORDER BY v.ref_article_protocol_id, v.drug_inhibitor_common_name) AS id,
    v.ref_article_protocol_id,
    v.drug_inhibitor_common_name,
    v.drug_inhibitor_standard_name,
    v.drug_inhibitor_cas_id
   FROM ( SELECT DISTINCT ctd_full.ref_article_protocol_id,
            ctd_full.drug_inhibitor_common_name,
            ctd_full.drug_inhibitor_standard_name,
            ctd_full.drug_inhibitor_cas_id
           FROM biomart.ctd_full
          ORDER BY ctd_full.ref_article_protocol_id, ctd_full.drug_inhibitor_common_name) v;


ALTER TABLE biomart.ctd_drug_inhibitor_view OWNER TO biomart;

--
-- Name: ctd_events_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.ctd_events_view AS
 SELECT row_number() OVER (ORDER BY v.ref_article_protocol_id, v.definition_of_the_event) AS id,
    v.ref_article_protocol_id,
    v.definition_of_the_event,
    v.number_of_events,
    v.event_rate,
    v.time_to_event,
    v.event_pct_reduction,
    v.event_p_value,
    v.event_description
   FROM ( SELECT DISTINCT ctd_full.ref_article_protocol_id,
            ctd_full.definition_of_the_event,
            ctd_full.number_of_events,
            ctd_full.event_rate,
            ctd_full.time_to_event,
            ctd_full.event_pct_reduction,
            ctd_full.event_p_value,
            ctd_full.event_description
           FROM biomart.ctd_full
          WHERE (((ctd_full.definition_of_the_event IS NOT NULL) AND ((ctd_full.definition_of_the_event)::text <> ''::text)) OR ((ctd_full.event_description IS NOT NULL) AND ((ctd_full.event_description)::text <> ''::text)))
          ORDER BY ctd_full.ref_article_protocol_id, ctd_full.definition_of_the_event) v;


ALTER TABLE biomart.ctd_events_view OWNER TO biomart;

--
-- Name: ctd_experiments_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.ctd_experiments_view AS
 SELECT row_number() OVER (ORDER BY v.ref_article_protocol_id, v.drug_inhibitor_common_name, v.drug_inhibitor_trtmt_regime) AS id,
    v.ref_article_protocol_id,
    v.drug_inhibitor_common_name,
    v.drug_inhibitor_dose,
    v.drug_inhibitor_time_period,
    v.drug_inhibitor_route_of_admin,
    v.drug_inhibitor_trtmt_regime,
    v.comparator_name,
    v.comparator_dose,
    v.comparator_time_period,
    v.comparator_route_of_admin,
    v.treatment_regime,
    v.placebo,
    v.experiment_description
   FROM ( SELECT DISTINCT ctd_full.ref_article_protocol_id,
            ctd_full.drug_inhibitor_common_name,
            ctd_full.drug_inhibitor_time_period,
            ctd_full.drug_inhibitor_dose,
            ctd_full.drug_inhibitor_route_of_admin,
            ctd_full.drug_inhibitor_trtmt_regime,
            ctd_full.comparator_name,
            ctd_full.comparator_dose,
            ctd_full.comparator_time_period,
            ctd_full.comparator_route_of_admin,
            ctd_full.treatment_regime,
            ctd_full.placebo,
            ctd_full.experiment_description
           FROM biomart.ctd_full
          ORDER BY ctd_full.ref_article_protocol_id, ctd_full.drug_inhibitor_common_name, ctd_full.drug_inhibitor_trtmt_regime) v;


ALTER TABLE biomart.ctd_experiments_view OWNER TO biomart;

--
-- Name: ctd_expr_after_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.ctd_expr_after_view AS
 SELECT row_number() OVER (ORDER BY v.ref_article_protocol_id, v.biomolecule_name) AS id,
    v.ref_article_protocol_id,
    v.biomolecule_name,
    v.expr_after_trtmt_pct,
    v.expr_after_trtmt_number,
    v.expr_aftertrtmt_valuefold_mean,
    v.expr_after_trtmt_sd,
    v.expr_after_trtmt_sem,
    v.expr_after_trtmt_unit
   FROM ( SELECT DISTINCT ctd_full.ref_article_protocol_id,
            ctd_full.biomolecule_name,
            ctd_full.expr_after_trtmt_pct,
            ctd_full.expr_after_trtmt_number,
            ctd_full.expr_aftertrtmt_valuefold_mean,
            ctd_full.expr_after_trtmt_sd,
            ctd_full.expr_after_trtmt_sem,
            ctd_full.expr_after_trtmt_unit
           FROM biomart.ctd_full
          WHERE (((ctd_full.biomolecule_name IS NOT NULL) AND ((ctd_full.biomolecule_name)::text <> ''::text)) OR ((ctd_full.expr_aftertrtmt_valuefold_mean IS NOT NULL) AND ((ctd_full.expr_aftertrtmt_valuefold_mean)::text <> ''::text)))
          ORDER BY ctd_full.ref_article_protocol_id, ctd_full.biomolecule_name) v;


ALTER TABLE biomart.ctd_expr_after_view OWNER TO biomart;

--
-- Name: ctd_expr_baseline_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.ctd_expr_baseline_view AS
 SELECT row_number() OVER (ORDER BY v.ref_article_protocol_id, v.biomolecule_name) AS id,
    v.ref_article_protocol_id,
    v.biomolecule_name,
    v.baseline_expr_pct,
    v.baseline_expr_number,
    v.baseline_expr_value_fold_mean,
    v.baseline_expr_sd,
    v.baseline_expr_sem,
    v.baseline_expr_unit
   FROM ( SELECT DISTINCT ctd_full.ref_article_protocol_id,
            ctd_full.biomolecule_name,
            ctd_full.baseline_expr_pct,
            ctd_full.baseline_expr_number,
            ctd_full.baseline_expr_value_fold_mean,
            ctd_full.baseline_expr_sd,
            ctd_full.baseline_expr_sem,
            ctd_full.baseline_expr_unit
           FROM biomart.ctd_full
          WHERE (((ctd_full.biomolecule_name IS NOT NULL) AND ((ctd_full.biomolecule_name)::text <> ''::text)) OR ((ctd_full.baseline_expr_value_fold_mean IS NOT NULL) AND ((ctd_full.baseline_expr_value_fold_mean)::text <> ''::text)))
          ORDER BY ctd_full.ref_article_protocol_id, ctd_full.biomolecule_name) v;


ALTER TABLE biomart.ctd_expr_baseline_view OWNER TO biomart;

--
-- Name: ctd_expr_bio_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.ctd_expr_bio_view AS
 SELECT row_number() OVER (ORDER BY v.ref_article_protocol_id, v.biomolecule_name) AS id,
    v.ref_article_protocol_id,
    v.biomolecule_name,
    v.biomolecule_id,
    v.biomolecule_type,
    v.biomarker,
    v.biomarker_type
   FROM ( SELECT DISTINCT ctd_full.ref_article_protocol_id,
            ctd_full.biomolecule_name,
            ctd_full.biomolecule_id,
            ctd_full.biomolecule_type,
            ctd_full.biomarker,
            ctd_full.biomarker_type
           FROM biomart.ctd_full
          WHERE (((ctd_full.biomolecule_name IS NOT NULL) AND ((ctd_full.biomolecule_name)::text <> ''::text)) OR ((ctd_full.biomolecule_id IS NOT NULL) AND ((ctd_full.biomolecule_id)::text <> ''::text)))
          ORDER BY ctd_full.ref_article_protocol_id, ctd_full.biomolecule_name) v;


ALTER TABLE biomart.ctd_expr_bio_view OWNER TO biomart;

--
-- Name: ctd_expr_source_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.ctd_expr_source_view AS
 SELECT row_number() OVER (ORDER BY v.ref_article_protocol_id, v.expr_chg_source_type) AS id,
    v.ref_article_protocol_id,
    v.expr_chg_source_type,
    v.expr_chg_technique,
    v.expr_chg_description
   FROM ( SELECT DISTINCT ctd_full.ref_article_protocol_id,
            ctd_full.expr_chg_source_type,
            ctd_full.expr_chg_technique,
            ctd_full.expr_chg_description
           FROM biomart.ctd_full
          WHERE (((ctd_full.expr_chg_source_type IS NOT NULL) AND ((ctd_full.expr_chg_source_type)::text <> ''::text)) OR ((ctd_full.expr_chg_description IS NOT NULL) AND ((ctd_full.expr_chg_description)::text <> ''::text)))
          ORDER BY ctd_full.ref_article_protocol_id, ctd_full.expr_chg_source_type) v;


ALTER TABLE biomart.ctd_expr_source_view OWNER TO biomart;

--
-- Name: ctd_full_clinical_endpts_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.ctd_full_clinical_endpts_view AS
 SELECT row_number() OVER (ORDER BY v.ref_article_protocol_id) AS id,
    v.ref_article_protocol_id,
    v.primary_endpoint_type,
    v.primary_endpoint_definition,
    v.primary_endpoint_change,
    v.primary_endpoint_time_period,
    v.primary_endpoint_p_value,
    v.primary_endpoint_stat_test,
    v.secondary_type,
    v.secondary_type_definition,
    v.secondary_type_change,
    v.secondary_type_time_period,
    v.secondary_type_p_value,
    v.secondary_type_stat_test
   FROM ( SELECT DISTINCT ctd_full.ref_article_protocol_id,
            ctd_full.primary_endpoint_type,
            ctd_full.primary_endpoint_definition,
            ctd_full.primary_endpoint_change,
            ctd_full.primary_endpoint_time_period,
            ctd_full.primary_endpoint_p_value,
            ctd_full.primary_endpoint_stat_test,
            ctd_full.secondary_type,
            ctd_full.secondary_type_definition,
            ctd_full.secondary_type_change,
            ctd_full.secondary_type_time_period,
            ctd_full.secondary_type_p_value,
            ctd_full.secondary_type_stat_test
           FROM biomart.ctd_full
          ORDER BY ctd_full.ref_article_protocol_id) v;


ALTER TABLE biomart.ctd_full_clinical_endpts_view OWNER TO biomart;

--
-- Name: ctd_full_search_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.ctd_full_search_view AS
 SELECT row_number() OVER (ORDER BY t.ref_article_protocol_id) AS fact_id,
    t.ref_article_protocol_id,
    t.mesh,
    t.common_name,
    t.drug_inhibitor_standard_name,
    t.primary_endpoint_type,
    t.secondary_type,
    t.biomarker_name,
    t.disease_severity,
    t.inhaled_steroid_dose,
    t.fev1,
    t.primary_endpoint_time_period,
    t.primary_endpoint_change,
    t.primary_endpoint_p_value
   FROM ( SELECT DISTINCT ctd_full.ref_article_protocol_id,
            ctd_full.mesh,
            ctd_full.common_name,
            ctd_full.drug_inhibitor_standard_name,
            ctd_full.primary_endpoint_type,
            ctd_full.secondary_type,
            ctd_full.biomarker_name,
            ctd_full.disease_severity,
            ctd_full.inhaled_steroid_dose,
            ctd_full.fev1,
            ctd_full.primary_endpoint_time_period,
            ctd_full.primary_endpoint_change,
            ctd_full.primary_endpoint_p_value
           FROM biomart.ctd_full) t;


ALTER TABLE biomart.ctd_full_search_view OWNER TO biomart;

--
-- Name: ctd_inclusion_criteria; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.ctd_inclusion_criteria (
    ctd_study_id bigint,
    inhaled_steroid_dose character varying(4000),
    laba character varying(4000),
    ocs character varying(4000),
    xolair character varying(4000),
    ltra_inhibitors character varying(4000),
    asthma_phenotype character varying(4000),
    fev1 character varying(4000)
);


ALTER TABLE biomart.ctd_inclusion_criteria OWNER TO biomart;

--
-- Name: ctd_primary_endpts; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.ctd_primary_endpts (
    ctd_study_id bigint,
    primary_type character varying(4000),
    primary_type_definition character varying(4000),
    primary_type_time_period character varying(4000),
    primary_type_change character varying(4000),
    primary_type_p_value character varying(4000),
    id bigint
);


ALTER TABLE biomart.ctd_primary_endpts OWNER TO biomart;

--
-- Name: ctd_primary_endpts_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.ctd_primary_endpts_view AS
 SELECT row_number() OVER (ORDER BY v.ref_article_protocol_id, v.primary_endpoint_type) AS id,
    v.ref_article_protocol_id,
    v.primary_endpoint_type,
    v.primary_endpoint_definition,
    v.primary_endpoint_change,
    v.primary_endpoint_time_period,
    v.primary_endpoint_p_value,
    v.primary_endpoint_stat_test
   FROM ( SELECT DISTINCT ctd_full.ref_article_protocol_id,
            ctd_full.primary_endpoint_type,
            ctd_full.primary_endpoint_definition,
            ctd_full.primary_endpoint_change,
            ctd_full.primary_endpoint_time_period,
            ctd_full.primary_endpoint_p_value,
            ctd_full.primary_endpoint_stat_test
           FROM biomart.ctd_full
          WHERE (((ctd_full.primary_endpoint_type IS NOT NULL) AND ((ctd_full.primary_endpoint_type)::text <> ''::text)) OR ((ctd_full.primary_endpoint_definition IS NOT NULL) AND ((ctd_full.primary_endpoint_definition)::text <> ''::text)))
          ORDER BY ctd_full.ref_article_protocol_id, ctd_full.primary_endpoint_type) v;


ALTER TABLE biomart.ctd_primary_endpts_view OWNER TO biomart;

--
-- Name: ctd_prior_med_use_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.ctd_prior_med_use_view AS
 SELECT row_number() OVER (ORDER BY v.ref_article_protocol_id, v.prior_med_drug_name) AS id,
    v.ref_article_protocol_id,
    v.prior_med_drug_name,
    v.prior_med_pct,
    v.prior_med_value
   FROM ( SELECT DISTINCT ctd_full.ref_article_protocol_id,
            ctd_full.prior_med_drug_name,
            ctd_full.prior_med_pct,
            ctd_full.prior_med_value
           FROM biomart.ctd_full
          WHERE ((ctd_full.prior_med_drug_name IS NOT NULL) AND ((ctd_full.prior_med_drug_name)::text <> ''::text))
          ORDER BY ctd_full.ref_article_protocol_id, ctd_full.prior_med_drug_name) v;


ALTER TABLE biomart.ctd_prior_med_use_view OWNER TO biomart;

--
-- Name: ctd_pulmonary_path_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.ctd_pulmonary_path_view AS
 SELECT row_number() OVER (ORDER BY v.ref_article_protocol_id, v.pulmonary_pathology_name) AS id,
    v.ref_article_protocol_id,
    v.pulmonary_pathology_name,
    v.pulmpath_patient_pct,
    v.pulmpath_value_unit,
    v.pulmpath_method
   FROM ( SELECT DISTINCT ctd_full.ref_article_protocol_id,
            ctd_full.pulmonary_pathology_name,
            ctd_full.pulmpath_patient_pct,
            ctd_full.pulmpath_value_unit,
            ctd_full.pulmpath_method
           FROM biomart.ctd_full
          WHERE ((ctd_full.pulmonary_pathology_name IS NOT NULL) AND ((ctd_full.pulmonary_pathology_name)::text <> ''::text))
          ORDER BY ctd_full.ref_article_protocol_id, ctd_full.pulmonary_pathology_name) v;


ALTER TABLE biomart.ctd_pulmonary_path_view OWNER TO biomart;

--
-- Name: ctd_quant_params_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.ctd_quant_params_view AS
 SELECT row_number() OVER (ORDER BY v.ref_article_protocol_id) AS id,
    v.ref_article_protocol_id,
    v.clinical_variable_name,
    v.pct_change_from_baseline,
    v.abs_change_from_baseline,
    v.rate_of_change_from_baseline,
    v.average_over_treatment_period,
    v.within_group_changes,
    v.stat_measure_p_value
   FROM ( SELECT DISTINCT ctd_full.ref_article_protocol_id,
            ctd_full.clinical_variable_name,
            ctd_full.pct_change_from_baseline,
            ctd_full.abs_change_from_baseline,
            ctd_full.rate_of_change_from_baseline,
            ctd_full.average_over_treatment_period,
            ctd_full.within_group_changes,
            ctd_full.stat_measure_p_value
           FROM biomart.ctd_full
          WHERE ((ctd_full.clinical_variable_name IS NOT NULL) AND ((ctd_full.clinical_variable_name)::text <> ''::text))
          ORDER BY ctd_full.ref_article_protocol_id) v;


ALTER TABLE biomart.ctd_quant_params_view OWNER TO biomart;

--
-- Name: ctd_reference_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.ctd_reference_view AS
 SELECT row_number() OVER (ORDER BY v.ref_article_protocol_id, v.ref_record_id) AS id,
    v.ref_article_protocol_id,
    v.ref_article_pmid,
    v.ref_protocol_id,
    v.ref_title,
    v.ref_record_id,
    v.ref_back_reference
   FROM ( SELECT DISTINCT ctd_full.ref_article_protocol_id,
            ctd_full.ref_article_pmid,
            ctd_full.ref_protocol_id,
            ctd_full.ref_title,
            ctd_full.ref_record_id,
            ctd_full.ref_back_reference
           FROM biomart.ctd_full
          ORDER BY ctd_full.ref_article_protocol_id, ctd_full.ref_record_id) v;


ALTER TABLE biomart.ctd_reference_view OWNER TO biomart;

--
-- Name: ctd_runin_therapies_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.ctd_runin_therapies_view AS
 SELECT row_number() OVER (ORDER BY v.ref_article_protocol_id, v.runin_description) AS id,
    v.ref_article_protocol_id,
    v.runin_ocs,
    v.runin_ics,
    v.runin_laba,
    v.runin_ltra,
    v.runin_corticosteroids,
    v.runin_anti_fibrotics,
    v.runin_immunosuppressive,
    v.runin_cytotoxic,
    v.runin_description
   FROM ( SELECT DISTINCT ctd_full.ref_article_protocol_id,
            ctd_full.runin_ocs,
            ctd_full.runin_ics,
            ctd_full.runin_laba,
            ctd_full.runin_ltra,
            ctd_full.runin_corticosteroids,
            ctd_full.runin_anti_fibrotics,
            ctd_full.runin_immunosuppressive,
            ctd_full.runin_cytotoxic,
            ctd_full.runin_description
           FROM biomart.ctd_full
          WHERE (((ctd_full.runin_ocs IS NOT NULL) AND ((ctd_full.runin_ocs)::text <> ''::text)) OR ((ctd_full.runin_description IS NOT NULL) AND ((ctd_full.runin_description)::text <> ''::text)) OR ((ctd_full.runin_immunosuppressive IS NOT NULL) AND ((ctd_full.runin_immunosuppressive)::text <> ''::text)))
          ORDER BY ctd_full.ref_article_protocol_id, ctd_full.runin_description) v;


ALTER TABLE biomart.ctd_runin_therapies_view OWNER TO biomart;

--
-- Name: ctd_sec_endpts; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.ctd_sec_endpts (
    ctd_study_id bigint,
    secondary_type character varying(4000),
    secondary_type_definition character varying(4000),
    secondary_type_time_period character varying(4000),
    secondary_type_change character varying(4000),
    secondary_type_p_value character varying(4000),
    id bigint
);


ALTER TABLE biomart.ctd_sec_endpts OWNER TO biomart;

--
-- Name: ctd_secondary_endpts_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.ctd_secondary_endpts_view AS
 SELECT row_number() OVER (ORDER BY v.ref_article_protocol_id, v.secondary_type) AS id,
    v.ref_article_protocol_id,
    v.secondary_type,
    v.secondary_type_definition,
    v.secondary_type_change,
    v.secondary_type_time_period,
    v.secondary_type_p_value,
    v.secondary_type_stat_test
   FROM ( SELECT DISTINCT ctd_full.ref_article_protocol_id,
            ctd_full.secondary_type,
            ctd_full.secondary_type_definition,
            ctd_full.secondary_type_change,
            ctd_full.secondary_type_time_period,
            ctd_full.secondary_type_p_value,
            ctd_full.secondary_type_stat_test
           FROM biomart.ctd_full
          WHERE (((ctd_full.secondary_type IS NOT NULL) AND ((ctd_full.secondary_type)::text <> ''::text)) OR ((ctd_full.secondary_type_definition IS NOT NULL) AND ((ctd_full.secondary_type_definition)::text <> ''::text)))
          ORDER BY ctd_full.ref_article_protocol_id, ctd_full.secondary_type) v;


ALTER TABLE biomart.ctd_secondary_endpts_view OWNER TO biomart;

--
-- Name: ctd_stats_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.ctd_stats_view AS
 SELECT row_number() OVER (ORDER BY v.ref_article_protocol_id, v.statistical_test) AS id,
    v.ref_article_protocol_id,
    v.clinical_correlation,
    v.statistical_test,
    v.statistical_coefficient_value,
    v.statistical_test_p_value,
    v.statistical_test_description
   FROM ( SELECT DISTINCT ctd_full.ref_article_protocol_id,
            ctd_full.clinical_correlation,
            ctd_full.statistical_test,
            ctd_full.statistical_coefficient_value,
            ctd_full.statistical_test_p_value,
            ctd_full.statistical_test_description
           FROM biomart.ctd_full
          WHERE (((ctd_full.statistical_test_description IS NOT NULL) AND ((ctd_full.statistical_test_description)::text <> ''::text)) OR ((ctd_full.statistical_test IS NOT NULL) AND ((ctd_full.statistical_test)::text <> ''::text)))
          ORDER BY ctd_full.ref_article_protocol_id, ctd_full.statistical_test) v;


ALTER TABLE biomart.ctd_stats_view OWNER TO biomart;

--
-- Name: ctd_study; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.ctd_study (
    ctd_study_id bigint,
    ref_article_protocol_id character varying(4000),
    ref_article_pmid character varying(4000),
    ref_protocol_id character varying(4000)
);


ALTER TABLE biomart.ctd_study OWNER TO biomart;

--
-- Name: ctd_study_details_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.ctd_study_details_view AS
 SELECT row_number() OVER (ORDER BY v.ref_article_protocol_id, v.common_name) AS id,
    v.ref_article_protocol_id,
    v.study_type,
    v.common_name,
    v.icd10,
    v.mesh,
    v.disease_type,
    v.physiology_name
   FROM ( SELECT DISTINCT ctd_full.ref_article_protocol_id,
            ctd_full.study_type,
            ctd_full.common_name,
            ctd_full.icd10,
            ctd_full.mesh,
            ctd_full.disease_type,
            ctd_full.physiology_name
           FROM biomart.ctd_full
          WHERE ((ctd_full.common_name IS NOT NULL) AND ((ctd_full.common_name)::text <> ''::text))
          ORDER BY ctd_full.ref_article_protocol_id, ctd_full.common_name) v;


ALTER TABLE biomart.ctd_study_details_view OWNER TO biomart;

--
-- Name: ctd_td_design_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.ctd_td_design_view AS
 SELECT row_number() OVER (ORDER BY v.ref_article_protocol_id, v.nature_of_trial, v.trial_type) AS id,
    v.ref_article_protocol_id,
    v.nature_of_trial,
    v.randomization,
    v.blinded_trial,
    v.trial_type,
    v.run_in_period,
    v.treatment_period,
    v.washout_period,
    v.open_label_extension
   FROM ( SELECT DISTINCT ctd_full.ref_article_protocol_id,
            ctd_full.nature_of_trial,
            ctd_full.randomization,
            ctd_full.blinded_trial,
            ctd_full.trial_type,
            ctd_full.run_in_period,
            ctd_full.treatment_period,
            ctd_full.washout_period,
            ctd_full.open_label_extension
           FROM biomart.ctd_full
          WHERE (((ctd_full.trial_type IS NOT NULL) AND ((ctd_full.trial_type)::text <> ''::text)) OR ((ctd_full.nature_of_trial IS NOT NULL) AND ((ctd_full.nature_of_trial)::text <> ''::text)))
          ORDER BY ctd_full.ref_article_protocol_id, ctd_full.nature_of_trial, ctd_full.trial_type) v;


ALTER TABLE biomart.ctd_td_design_view OWNER TO biomart;

--
-- Name: ctd_td_excl_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.ctd_td_excl_view AS
 SELECT row_number() OVER (ORDER BY v.ref_article_protocol_id) AS id,
    v.ref_article_protocol_id,
    v.exclusion_criteria1,
    v.exclusion_criteria2,
    v.minimal_symptoms,
    v.rescue_medication_use,
    v.control_details,
    v.blinding_procedure,
    v.number_of_arms,
    v.description1,
    v.description2
   FROM ( SELECT DISTINCT ctd_full.ref_article_protocol_id,
            "substring"(ctd_full.exclusion_criteria, 1, 4000) AS exclusion_criteria1,
            "substring"(ctd_full.exclusion_criteria, 4001, 2000) AS exclusion_criteria2,
            ctd_full.minimal_symptoms,
            ctd_full.rescue_medication_use,
            ctd_full.control_details,
            ctd_full.blinding_procedure,
            ctd_full.number_of_arms,
            "substring"(ctd_full.description, 1, 4000) AS description1,
            "substring"(ctd_full.description, 4001, 2000) AS description2
           FROM biomart.ctd_full
          WHERE (((ctd_full.blinding_procedure IS NOT NULL) AND ((ctd_full.blinding_procedure)::text <> ''::text)) OR ((ctd_full.number_of_arms IS NOT NULL) AND ((ctd_full.number_of_arms)::text <> ''::text)))
          ORDER BY ctd_full.ref_article_protocol_id) v;


ALTER TABLE biomart.ctd_td_excl_view OWNER TO biomart;

--
-- Name: ctd_td_inclusion_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.ctd_td_inclusion_view AS
 SELECT row_number() OVER (ORDER BY v.ref_article_protocol_id, v.disease_severity, v.fev1) AS id,
    v.ref_article_protocol_id,
    v.trial_age,
    v.disease_severity,
    v.difficult_to_treat,
    v.asthma_diagnosis,
    v.inhaled_steroid_dose,
    v.laba,
    v.ocs,
    v.xolair,
    v.ltra_inhibitors,
    v.asthma_phenotype,
    v.fev1,
    v.fev1_reversibility,
    v.tlc,
    v.fev1_fvc,
    v.fvc,
    v.dlco,
    v.sgrq,
    v.hrct,
    v.biopsy,
    v.dyspnea_on_exertion,
    v.concomitant_med
   FROM ( SELECT DISTINCT ctd_full.ref_article_protocol_id,
            ctd_full.trial_age,
            ctd_full.disease_severity,
            ctd_full.difficult_to_treat,
            ctd_full.asthma_diagnosis,
            ctd_full.inhaled_steroid_dose,
            ctd_full.laba,
            ctd_full.ocs,
            ctd_full.xolair,
            ctd_full.ltra_inhibitors,
            ctd_full.asthma_phenotype,
            ctd_full.fev1,
            ctd_full.fev1_reversibility,
            ctd_full.tlc,
            ctd_full.fev1_fvc,
            ctd_full.fvc,
            ctd_full.dlco,
            ctd_full.sgrq,
            ctd_full.hrct,
            ctd_full.biopsy,
            ctd_full.dyspnea_on_exertion,
            ctd_full.concomitant_med
           FROM biomart.ctd_full
          WHERE (((ctd_full.fev1 IS NOT NULL) AND ((ctd_full.fev1)::text <> ''::text)) OR ((ctd_full.disease_severity IS NOT NULL) AND ((ctd_full.disease_severity)::text <> ''::text)) OR ((ctd_full.trial_age IS NOT NULL) AND ((ctd_full.trial_age)::text <> ''::text)))
          ORDER BY ctd_full.ref_article_protocol_id, ctd_full.disease_severity, ctd_full.fev1) v;


ALTER TABLE biomart.ctd_td_inclusion_view OWNER TO biomart;

--
-- Name: ctd_td_smoker_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.ctd_td_smoker_view AS
 SELECT row_number() OVER (ORDER BY v.ref_article_protocol_id, v.trial_smokers_pct) AS id,
    v.ref_article_protocol_id,
    v.trial_smokers_pct,
    v.trial_former_smokers_pct,
    v.trial_never_smokers_pct,
    v.trial_pack_years
   FROM ( SELECT DISTINCT ctd_full.ref_article_protocol_id,
            ctd_full.trial_smokers_pct,
            ctd_full.trial_former_smokers_pct,
            ctd_full.trial_never_smokers_pct,
            ctd_full.trial_pack_years
           FROM biomart.ctd_full
          WHERE (((ctd_full.trial_smokers_pct IS NOT NULL) AND ((ctd_full.trial_smokers_pct)::text <> ''::text)) OR ((ctd_full.trial_never_smokers_pct IS NOT NULL) AND ((ctd_full.trial_never_smokers_pct)::text <> ''::text)))
          ORDER BY ctd_full.ref_article_protocol_id, ctd_full.trial_smokers_pct) v;


ALTER TABLE biomart.ctd_td_smoker_view OWNER TO biomart;

--
-- Name: ctd_td_sponsor_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.ctd_td_sponsor_view AS
 SELECT row_number() OVER (ORDER BY v.ref_article_protocol_id, v.sponsor, v.trial_nbr_of_patients_studied) AS id,
    v.ref_article_protocol_id,
    v.sponsor,
    v.trial_nbr_of_patients_studied,
    v.source_type
   FROM ( SELECT DISTINCT ctd_full.ref_article_protocol_id,
            ctd_full.sponsor,
            ctd_full.trial_nbr_of_patients_studied,
            ctd_full.source_type
           FROM biomart.ctd_full
          WHERE (((ctd_full.sponsor IS NOT NULL) AND ((ctd_full.sponsor)::text <> ''::text)) OR ((ctd_full.trial_nbr_of_patients_studied IS NOT NULL) AND ((ctd_full.trial_nbr_of_patients_studied)::text <> ''::text)))
          ORDER BY ctd_full.ref_article_protocol_id, ctd_full.sponsor, ctd_full.trial_nbr_of_patients_studied) v;


ALTER TABLE biomart.ctd_td_sponsor_view OWNER TO biomart;

--
-- Name: ctd_td_status_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.ctd_td_status_view AS
 SELECT row_number() OVER (ORDER BY v.ref_article_protocol_id) AS id,
    v.ref_article_protocol_id,
    v.trial_status,
    v.trial_phase
   FROM ( SELECT DISTINCT ctd_full.ref_article_protocol_id,
            ctd_full.trial_status,
            ctd_full.trial_phase
           FROM biomart.ctd_full
          WHERE (((ctd_full.trial_status IS NOT NULL) AND ((ctd_full.trial_status)::text <> ''::text)) OR ((ctd_full.trial_phase IS NOT NULL) AND ((ctd_full.trial_phase)::text <> ''::text)))
          ORDER BY ctd_full.ref_article_protocol_id) v;


ALTER TABLE biomart.ctd_td_status_view OWNER TO biomart;

--
-- Name: ctd_treatment_phases_view; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.ctd_treatment_phases_view AS
 SELECT row_number() OVER (ORDER BY v.ref_article_protocol_id, v.trtmt_description, v.trtmt_ocs) AS id,
    v.ref_article_protocol_id,
    v.trtmt_ocs,
    v.trtmt_ics,
    v.trtmt_laba,
    v.trtmt_ltra,
    v.trtmt_corticosteroids,
    v.trtmt_anti_fibrotics,
    v.trtmt_immunosuppressive,
    v.trtmt_cytotoxic,
    v.trtmt_description
   FROM ( SELECT DISTINCT ctd_full.ref_article_protocol_id,
            ctd_full.trtmt_ocs,
            ctd_full.trtmt_ics,
            ctd_full.trtmt_laba,
            ctd_full.trtmt_ltra,
            ctd_full.trtmt_corticosteroids,
            ctd_full.trtmt_anti_fibrotics,
            ctd_full.trtmt_immunosuppressive,
            ctd_full.trtmt_cytotoxic,
            ctd_full.trtmt_description
           FROM biomart.ctd_full
          WHERE (((ctd_full.trtmt_ocs IS NOT NULL) AND ((ctd_full.trtmt_ocs)::text <> ''::text)) OR ((ctd_full.trtmt_description IS NOT NULL) AND ((ctd_full.trtmt_description)::text <> ''::text)) OR ((ctd_full.trtmt_immunosuppressive IS NOT NULL) AND ((ctd_full.trtmt_immunosuppressive)::text <> ''::text)))
          ORDER BY ctd_full.ref_article_protocol_id, ctd_full.trtmt_description, ctd_full.trtmt_ocs) v;


ALTER TABLE biomart.ctd_treatment_phases_view OWNER TO biomart;

--
-- Name: dd; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.dd (
    drn character varying(20),
    drug character varying(100)
);


ALTER TABLE biomart.dd OWNER TO biomart;

--
-- Name: gene_info_test; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.gene_info_test (
    tax_id bigint,
    gene_id bigint,
    gene_symbol character varying(200),
    gene_descr character varying(4000)
);


ALTER TABLE biomart.gene_info_test OWNER TO biomart;

--
-- Name: gene_synonym_test; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.gene_synonym_test (
    tax_id bigint,
    gene_id bigint,
    gene_symbol character varying(200),
    gene_synonym character varying(200)
);


ALTER TABLE biomart.gene_synonym_test OWNER TO biomart;

--
-- Name: genego_gene_map; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.genego_gene_map (
    gene_symbol character varying(20),
    gene_id character varying(10),
    bio_marker_id bigint NOT NULL
);


ALTER TABLE biomart.genego_gene_map OWNER TO biomart;

--
-- Name: gse_analysis; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.gse_analysis (
    name character varying(100),
    platform character varying(100),
    test character varying(1000),
    data_ct bigint,
    fc_mean bigint,
    fc_stddev bigint,
    bio_experiment_id bigint,
    bio_assay_platform_id bigint,
    bio_assay_analysis_id bigint,
    analysis1 character varying(300),
    analysis2 character varying(300)
);


ALTER TABLE biomart.gse_analysis OWNER TO biomart;

--
-- Name: gse_probeset; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.gse_probeset (
    name character varying(100),
    platform character varying(100),
    probeset character varying(100)
);


ALTER TABLE biomart.gse_probeset OWNER TO biomart;

--
-- Name: heat_map_results; Type: TABLE; Schema: biomart; Owner: tm_cz; Tablespace: transmart
--

CREATE TABLE biomart.heat_map_results (
    subject_id character varying(50),
    log_intensity double precision,
    cohort_id character varying(255),
    probe_id character varying(100),
    bio_assay_feature_group_id bigint,
    fold_change_ratio double precision,
    tea_normalized_pvalue double precision,
    bio_marker_name character varying(400),
    bio_marker_id bigint,
    search_keyword_id bigint,
    bio_assay_analysis_id bigint,
    trial_name character varying(50),
    significant smallint,
    gene_id character varying(200),
    assay_id bigint,
    preferred_pvalue double precision
);


ALTER TABLE biomart.heat_map_results OWNER TO tm_cz;

--
-- Name: hibernate_sequence; Type: SEQUENCE; Schema: biomart; Owner: biomart
--

CREATE SEQUENCE biomart.hibernate_sequence
    START WITH 21270
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 20;


ALTER TABLE biomart.hibernate_sequence OWNER TO biomart;

--
-- Name: mesh; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.mesh (
    ui character varying(20) NOT NULL,
    mh character varying(200),
    mn character varying(200),
    ui_path character varying(500)
);


ALTER TABLE biomart.mesh OWNER TO biomart;

--
-- Name: mesh_copy; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.mesh_copy (
    ui character varying(20) NOT NULL,
    mh character varying(200),
    mn character varying(200)
);


ALTER TABLE biomart.mesh_copy OWNER TO biomart;

--
-- Name: mesh_descriptor; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.mesh_descriptor (
    mesh_descriptor_id character varying(15) NOT NULL,
    mesh_heading character varying(256),
    mesh_annotation character varying(2000)
);


ALTER TABLE biomart.mesh_descriptor OWNER TO biomart;

--
-- Name: mesh_entry; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.mesh_entry (
    mh character varying(200),
    entry character varying(200)
);


ALTER TABLE biomart.mesh_entry OWNER TO biomart;

--
-- Name: mesh_path; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.mesh_path (
    unique_id character varying(20) NOT NULL,
    mesh_name character varying(200),
    child_number character varying(200),
    parent_number character varying(200),
    id_path character varying(500)
);


ALTER TABLE biomart.mesh_path OWNER TO biomart;

--
-- Name: mesh_temp; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.mesh_temp (
    ui character varying(20),
    mh character varying(200),
    mn character varying(200)
);


ALTER TABLE biomart.mesh_temp OWNER TO biomart;

--
-- Name: mesh_with_parent; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.mesh_with_parent AS
 SELECT mesh.mn,
    mesh.ui,
    mesh.mh,
        CASE
            WHEN (biomart_user.instr(mesh.mn, '.'::character varying) = 0) THEN NULL::text
            ELSE substr((mesh.mn)::text, 1, (biomart_user.instr(mesh.mn, '.'::character varying, '-1'::integer) - 1))
        END AS pn
   FROM biomart.mesh
  ORDER BY mesh.mn;


ALTER TABLE biomart.mesh_with_parent OWNER TO biomart;

--
-- Name: VIEW mesh_with_parent; Type: COMMENT; Schema: biomart; Owner: biomart
--

COMMENT ON VIEW biomart.mesh_with_parent IS '-- mesh terms with parent term relation';


--
-- Name: mirna_bio_assay_data_annot; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.mirna_bio_assay_data_annot (
    bio_assay_feature_group_id numeric(18,0),
    bio_marker_id numeric(18,0) NOT NULL,
    data_table character(5)
);


ALTER TABLE biomart.mirna_bio_assay_data_annot OWNER TO biomart;

--
-- Name: seq_bio_data_id; Type: SEQUENCE; Schema: biomart; Owner: biomart
--

CREATE SEQUENCE biomart.seq_bio_data_id
    START WITH 391713417
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE biomart.seq_bio_data_id OWNER TO biomart;

--
-- Name: mirna_bio_assay_feature_group; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.mirna_bio_assay_feature_group (
    bio_assay_feature_group_id numeric(18,0) DEFAULT nextval('biomart.seq_bio_data_id'::regclass) NOT NULL,
    feature_group_name character varying(100) NOT NULL,
    feature_group_type character varying(50) NOT NULL
);


ALTER TABLE biomart.mirna_bio_assay_feature_group OWNER TO biomart;

--
-- Name: mirna_bio_marker; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.mirna_bio_marker (
    bio_marker_id bigint NOT NULL,
    bio_marker_name character varying(200),
    bio_marker_description character varying(1000),
    organism character varying(200),
    primary_source_code character varying(200),
    primary_external_id character varying(200),
    bio_marker_type character varying(200) NOT NULL
);


ALTER TABLE biomart.mirna_bio_marker OWNER TO biomart;

--
-- Name: project_info; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.project_info (
    file_id integer NOT NULL,
    id character varying(100),
    name character varying(100),
    file_name character varying(100),
    activedataname character varying(400),
    project_accession character varying(100),
    project_category text,
    project_contactaddress character varying(4000),
    project_contactcompany character varying(400),
    project_contactdepartment character varying(400),
    project_contactemail character varying(400),
    project_contactlaboratory character varying(400),
    project_contactname character varying(400),
    project_contactphone character varying(100),
    project_contactweblink character varying(1000),
    project_contributors character varying(4000),
    project_description text,
    project_design text,
    project_id character varying(100),
    project_keywords character varying(1000),
    project_organism character varying(400),
    project_tissue character varying(400),
    project_compound character varying(400),
    project_platform character varying(400),
    project_platformdescription character varying(4000),
    project_platformorganism character varying(400),
    project_platformprovider character varying(400),
    project_platformtechnology character varying(400),
    project_platformtype character varying(400),
    project_pubmed character varying(400),
    project_studytype character varying(1000),
    project_supplementaryfile character varying(400),
    project_title character varying(500),
    project_weblink character varying(1000),
    project_extractedfromcelfiles character varying(1000),
    project_contactorganization character varying(1000),
    project_contactfax character varying(100),
    project_outputfile character varying(500),
    project_datasource character varying(100),
    project_editors character varying(100),
    project_isprivate character varying(100),
    project_publishdate character varying(100),
    entrydt timestamp without time zone
);


ALTER TABLE biomart.project_info OWNER TO biomart;

--
-- Name: seq_bio_data_fact_id; Type: SEQUENCE; Schema: biomart; Owner: biomart
--

CREATE SEQUENCE biomart.seq_bio_data_fact_id
    START WITH 26518741
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 20;


ALTER TABLE biomart.seq_bio_data_fact_id OWNER TO biomart;

--
-- Name: seq_clinical_trial_design_id; Type: SEQUENCE; Schema: biomart; Owner: biomart
--

CREATE SEQUENCE biomart.seq_clinical_trial_design_id
    START WITH 24181
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 20;


ALTER TABLE biomart.seq_clinical_trial_design_id OWNER TO biomart;

--
-- Name: tests; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.tests (
    name character varying(100),
    platform character varying(100),
    id bigint,
    test character varying(1000),
    probeset character varying(100),
    raw_pvalue double precision,
    adjusted_pvalue double precision,
    estimate double precision,
    fold_change double precision,
    max_ls_mean double precision
);


ALTER TABLE biomart.tests OWNER TO biomart;

--
-- Name: tmp_bio_marker; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.tmp_bio_marker (
    id bigint,
    gene_id character varying(50),
    organism character varying(200)
);


ALTER TABLE biomart.tmp_bio_marker OWNER TO biomart;

--
-- Name: tmp_omicsoft_compound; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.tmp_omicsoft_compound (
    accession character varying(100),
    bio_compound_id bigint NOT NULL,
    cas_registry character varying(400),
    compound character varying(4000),
    bio_experiment_id bigint
);


ALTER TABLE biomart.tmp_omicsoft_compound OWNER TO biomart;

--
-- Name: tmp_omicsoft_disease_m1; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.tmp_omicsoft_disease_m1 (
    accession character varying(100),
    mesh_heading character varying(256),
    mesh_descriptor_id character varying(15) NOT NULL
);


ALTER TABLE biomart.tmp_omicsoft_disease_m1 OWNER TO biomart;

--
-- Name: tmp_project_category; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.tmp_project_category (
    file_id character varying(100),
    accession character varying(100),
    category character varying(4000)
);


ALTER TABLE biomart.tmp_project_category OWNER TO biomart;

--
-- Name: tmp_project_compound; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.tmp_project_compound (
    file_id character varying(100),
    accession character varying(100),
    compound character varying(4000)
);


ALTER TABLE biomart.tmp_project_compound OWNER TO biomart;

--
-- Name: tmp_project_disease; Type: TABLE; Schema: biomart; Owner: biomart; Tablespace: transmart
--

CREATE TABLE biomart.tmp_project_disease (
    file_id character varying(100),
    accession character varying(100),
    disease character varying(4000)
);


ALTER TABLE biomart.tmp_project_disease OWNER TO biomart;

--
-- Name: vw_faceted_search; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.vw_faceted_search AS
 SELECT ba.bio_assay_analysis_id AS analysis_id,
    be.bio_experiment_id AS study,
    be.bio_experiment_id AS study_id,
    ba.analysis_type AS analyses,
    ba.bio_assay_data_type AS data_type,
    bplat.platform_accession AS platform,
    bplat.platform_description,
    bplat.platform_vendor,
    baap.platform_name,
    ('OBS:'::text || (bpobs.obs_code)::text) AS observation,
    be.title AS study_title,
    be.description AS study_description,
    be.design AS study_design,
    be.primary_investigator AS study_primary_inv,
    be.contact_field AS study_contact_field,
    be.overall_design AS study_overall_design,
    be.institution AS study_institution,
    be.accession AS study_accession,
    be.country AS study_country,
    be.biomarker_type AS study_biomarker_type,
    be.target AS study_target,
    be.access_type AS study_access_type,
    ba.analysis_name,
    ba.short_description AS analysis_description_s,
    ba.long_description AS analysis_description_l,
    ba.analysis_type,
    ba.analyst_name AS analysis_analyst_name,
    ba.analysis_method_cd AS analysis_method,
    ba.bio_assay_data_type AS analysis_data_type,
    ba.qa_criteria AS analysis_qa_criteria,
    bae.model_name,
    bae.model_desc AS model_description,
    bae.research_unit,
    row_number() OVER (ORDER BY ba.bio_assay_analysis_id) AS facet_id
   FROM (((((((biomart.bio_assay_analysis ba
     JOIN biomart.bio_experiment be ON (((ba.etl_id)::text = (be.accession)::text)))
     LEFT JOIN biomart.bio_assay_analysis_ext bae ON ((bae.bio_assay_analysis_id = ba.bio_assay_analysis_id)))
     LEFT JOIN biomart.bio_data_platform bdplat ON ((ba.bio_assay_analysis_id = bdplat.bio_data_id)))
     LEFT JOIN biomart.bio_assay_platform bplat ON ((bdplat.bio_assay_platform_id = bplat.bio_assay_platform_id)))
     LEFT JOIN biomart.bio_data_observation bdpobs ON ((ba.bio_assay_analysis_id = bdpobs.bio_data_id)))
     LEFT JOIN biomart.bio_observation bpobs ON ((bdpobs.bio_observation_id = bpobs.bio_observation_id)))
     LEFT JOIN biomart.bio_asy_analysis_pltfm baap ON ((baap.bio_asy_analysis_pltfm_id = ba.bio_asy_analysis_pltfm_id)))
  WHERE (lower((be.bio_experiment_type)::text) = 'experiment'::text);


ALTER TABLE biomart.vw_faceted_search OWNER TO biomart;

--
-- Name: vw_faceted_search_disease; Type: VIEW; Schema: biomart; Owner: biomart
--

CREATE VIEW biomart.vw_faceted_search_disease AS
 SELECT z.bio_assay_analysis_id,
    replace(ltrim(z.solr_hierarchy, '/'::text), '//'::text, '/'::text) AS solr_hierarchy
   FROM ( SELECT y.bio_assay_analysis_id,
            string_agg(y.path, '/'::text ORDER BY (ROW(y.*::record, y.path))::text) AS solr_hierarchy
           FROM ( SELECT x.bio_assay_analysis_id,
                    x.top_node,
                    max(x.path) AS path
                   FROM ( SELECT DISTINCT bdd.bio_data_id AS bio_assay_analysis_id,
                            substr(mp.path, 2, 11) AS top_node,
                            mp.path
                           FROM biomart.bio_data_disease bdd,
                            biomart.bio_disease bd,
                            ( SELECT ('DIS:'::text || (mesh.ui)::text) AS path,
                                    mesh.ui AS unique_id
                                   FROM biomart.mesh) mp
                          WHERE ((bdd.bio_disease_id = bd.bio_disease_id) AND ((bd.mesh_code)::text = (mp.unique_id)::text))) x
                  GROUP BY x.bio_assay_analysis_id, x.top_node
                  ORDER BY x.bio_assay_analysis_id) y
          GROUP BY y.bio_assay_analysis_id) z
  ORDER BY z.bio_assay_analysis_id;


ALTER TABLE biomart.vw_faceted_search_disease OWNER TO biomart;

--
-- Name: fm_data_uid; Type: TABLE; Schema: fmapp; Owner: fmapp; Tablespace: transmart
--

CREATE TABLE fmapp.fm_data_uid (
    fm_data_id bigint NOT NULL,
    unique_id character varying(300) NOT NULL,
    fm_data_type character varying(100) NOT NULL
);


ALTER TABLE fmapp.fm_data_uid OWNER TO fmapp;

--
-- Name: fm_folder; Type: TABLE; Schema: fmapp; Owner: fmapp; Tablespace: transmart
--

CREATE TABLE fmapp.fm_folder (
    folder_id bigint NOT NULL,
    folder_name character varying(1000) NOT NULL,
    folder_full_name character varying(1000) NOT NULL,
    folder_level bigint NOT NULL,
    folder_type character varying(100) NOT NULL,
    folder_tag character varying(50),
    active_ind boolean NOT NULL,
    parent_id bigint,
    description character varying(2000)
);


ALTER TABLE fmapp.fm_folder OWNER TO fmapp;

--
-- Name: fm_folder_association; Type: TABLE; Schema: fmapp; Owner: fmapp; Tablespace: transmart
--

CREATE TABLE fmapp.fm_folder_association (
    folder_id bigint NOT NULL,
    object_uid character varying(300) NOT NULL,
    object_type character varying(100) NOT NULL
);


ALTER TABLE fmapp.fm_folder_association OWNER TO fmapp;

--
-- Name: browse_analyses_view; Type: VIEW; Schema: biomart_user; Owner: biomart_user
--

CREATE VIEW biomart_user.browse_analyses_view AS
 SELECT fd.unique_id AS id,
    baa.analysis_name AS title,
    baa.long_description AS description,
    string_agg((bap.platform_type)::text, '|'::text ORDER BY (bap.platform_type)::text) AS measurement_type,
    string_agg((bap.platform_name)::text, '|'::text ORDER BY (bap.platform_name)::text) AS platform_name,
    string_agg((bap.platform_vendor)::text, '|'::text ORDER BY (bap.platform_vendor)::text) AS vendor,
    string_agg((bap.platform_technology)::text, '|'::text ORDER BY (bap.platform_technology)::text) AS technology
   FROM (((((((biomart.bio_assay_analysis baa
     JOIN biomart.bio_data_uid bd ON ((baa.bio_assay_analysis_id = bd.bio_data_id)))
     JOIN fmapp.fm_folder_association fa ON (((fa.object_uid)::text = (bd.unique_id)::text)))
     JOIN fmapp.fm_data_uid fd ON ((fa.folder_id = fd.fm_data_id)))
     JOIN fmapp.fm_folder ff ON ((ff.folder_id = fa.folder_id)))
     LEFT JOIN amapp.am_tag_association ata ON (((fd.unique_id)::text = (ata.subject_uid)::text)))
     LEFT JOIN biomart.bio_data_uid bdu ON (((bdu.unique_id)::text = (ata.object_uid)::text)))
     LEFT JOIN biomart.bio_assay_platform bap ON ((bap.bio_assay_platform_id = bdu.bio_data_id)))
  WHERE (((ata.object_type)::text = 'BIO_ASSAY_PLATFORM'::text) AND ff.active_ind)
  GROUP BY fd.unique_id, baa.analysis_name, baa.long_description;


ALTER TABLE biomart_user.browse_analyses_view OWNER TO biomart_user;

--
-- Name: browse_assays_view; Type: VIEW; Schema: biomart_user; Owner: biomart_user
--

CREATE VIEW biomart_user.browse_assays_view AS
 SELECT DISTINCT fd.unique_id AS id,
    f.folder_name AS title,
    f.description,
    string_agg((bap.platform_type)::text, '|'::text ORDER BY (bap.platform_type)::text) AS measurement_type,
    string_agg((bap.platform_name)::text, '|'::text ORDER BY (bap.platform_name)::text) AS platform_name,
    string_agg((bap.platform_vendor)::text, '|'::text ORDER BY (bap.platform_vendor)::text) AS vendor,
    string_agg((bap.platform_technology)::text, '|'::text ORDER BY (bap.platform_technology)::text) AS technology,
    bio_markers.object_uids AS gene,
    bio_markers.object_uids AS mirna,
    biomarker_types.object_uids AS biomarker_type
   FROM ((((((fmapp.fm_folder f
     JOIN fmapp.fm_data_uid fd ON ((f.folder_id = fd.fm_data_id)))
     LEFT JOIN amapp.am_tag_association ata ON ((((fd.unique_id)::text = (ata.subject_uid)::text) AND ((ata.object_type)::text = 'BIO_ASSAY_PLATFORM'::text))))
     LEFT JOIN biomart.bio_data_uid bdu ON (((bdu.unique_id)::text = (ata.object_uid)::text)))
     LEFT JOIN biomart.bio_assay_platform bap ON ((bap.bio_assay_platform_id = bdu.bio_data_id)))
     LEFT JOIN ( SELECT fdu.unique_id AS id,
            string_agg((ata_1.object_uid)::text, '|'::text ORDER BY (ata_1.object_uid)::text) AS object_uids
           FROM ((fmapp.fm_folder ff
             JOIN fmapp.fm_data_uid fdu ON ((ff.folder_id = fdu.fm_data_id)))
             JOIN amapp.am_tag_association ata_1 ON (((fdu.unique_id)::text = (ata_1.subject_uid)::text)))
          WHERE (((ata_1.object_type)::text = 'BIO_MARKER'::text) AND ((ff.folder_type)::text = 'ASSAY'::text))
          GROUP BY fdu.unique_id) bio_markers ON (((bio_markers.id)::text = (fd.unique_id)::text)))
     LEFT JOIN ( SELECT fdu.unique_id AS id,
            string_agg((ata_1.object_uid)::text, '|'::text ORDER BY (ata_1.object_uid)::text) AS object_uids
           FROM (((fmapp.fm_folder ff
             JOIN fmapp.fm_data_uid fdu ON ((ff.folder_id = fdu.fm_data_id)))
             JOIN amapp.am_tag_association ata_1 ON (((fdu.unique_id)::text = (ata_1.subject_uid)::text)))
             JOIN amapp.am_tag_item ati ON ((ata_1.tag_item_id = ati.tag_item_id)))
          WHERE (((ata_1.object_type)::text = 'BIO_CONCEPT_CODE'::text) AND ((ati.code_type_name)::text = 'ASSAY_TYPE_OF_BM_STUDIED'::text) AND ((ff.folder_type)::text = 'ASSAY'::text))
          GROUP BY fdu.unique_id) biomarker_types ON (((biomarker_types.id)::text = (fd.unique_id)::text)))
  WHERE (((f.folder_type)::text = 'ASSAY'::text) AND f.active_ind)
  GROUP BY fd.unique_id, f.folder_name, f.description, bio_markers.object_uids, biomarker_types.object_uids;


ALTER TABLE biomart_user.browse_assays_view OWNER TO biomart_user;

--
-- Name: browse_folders_view; Type: VIEW; Schema: biomart_user; Owner: biomart_user
--

CREATE VIEW biomart_user.browse_folders_view AS
 SELECT fd.unique_id AS id,
    f.folder_name AS title,
    f.description,
    string_agg((ata.object_uid)::text, '|'::text ORDER BY (ata.object_uid)::text) AS file_type
   FROM ((fmapp.fm_folder f
     JOIN fmapp.fm_data_uid fd ON ((f.folder_id = fd.fm_data_id)))
     LEFT JOIN amapp.am_tag_association ata ON (((fd.unique_id)::text = (ata.subject_uid)::text)))
  WHERE (((f.folder_type)::text = 'FOLDER'::text) AND f.active_ind AND ((ata.object_type)::text = 'BIO_CONCEPT_CODE'::text) AND ((ata.object_uid)::text ~~ 'FILE_TYPE%'::text))
  GROUP BY fd.unique_id, f.folder_name, f.description;


ALTER TABLE biomart_user.browse_folders_view OWNER TO biomart_user;

--
-- Name: browse_programs_view; Type: VIEW; Schema: biomart_user; Owner: biomart_user
--

CREATE VIEW biomart_user.browse_programs_view AS
 SELECT fd.unique_id AS id,
    f.folder_name AS title,
    f.description,
    diseases.object_uids AS disease,
    observations.object_uids AS observation,
    pathways.object_uids AS pathway,
    genes.object_uids AS gene,
    therapeutic_domains.object_uids AS therapeutic_domain,
    institutions.object_uids AS institution,
    targets.object_uids AS target
   FROM ((((((((fmapp.fm_folder f
     JOIN fmapp.fm_data_uid fd ON ((f.folder_id = fd.fm_data_id)))
     LEFT JOIN ( SELECT fdu.unique_id AS id,
            string_agg((ata.object_uid)::text, '|'::text ORDER BY (ata.object_uid)::text) AS object_uids
           FROM ((((fmapp.fm_folder ff
             JOIN fmapp.fm_data_uid fdu ON ((ff.folder_id = fdu.fm_data_id)))
             JOIN amapp.am_tag_association ata ON (((fdu.unique_id)::text = (ata.subject_uid)::text)))
             JOIN biomart.bio_data_uid bdu ON (((bdu.unique_id)::text = (ata.object_uid)::text)))
             JOIN biomart.bio_disease bd ON ((bd.bio_disease_id = bdu.bio_data_id)))
          WHERE (((ata.object_type)::text = ANY (ARRAY[('BIO_DISEASE'::character varying)::text, ('PROGRAM_TARGET'::character varying)::text])) AND ((ff.folder_type)::text = 'PROGRAM'::text))
          GROUP BY fdu.unique_id) diseases ON (((diseases.id)::text = (fd.unique_id)::text)))
     LEFT JOIN ( SELECT fdu.unique_id AS id,
            string_agg((ata.object_uid)::text, '|'::text ORDER BY (ata.object_uid)::text) AS object_uids
           FROM ((((fmapp.fm_folder ff
             JOIN fmapp.fm_data_uid fdu ON ((ff.folder_id = fdu.fm_data_id)))
             JOIN amapp.am_tag_association ata ON (((fdu.unique_id)::text = (ata.subject_uid)::text)))
             JOIN biomart.bio_data_uid bdu ON (((bdu.unique_id)::text = (ata.object_uid)::text)))
             JOIN biomart.bio_observation bo ON ((bo.bio_observation_id = bdu.bio_data_id)))
          WHERE (((ata.object_type)::text = ANY (ARRAY[('BIO_OBSERVATION'::character varying)::text, ('PROGRAM_TARGET'::character varying)::text])) AND ((ff.folder_type)::text = 'PROGRAM'::text))
          GROUP BY fdu.unique_id) observations ON (((observations.id)::text = (fd.unique_id)::text)))
     LEFT JOIN ( SELECT fdu.unique_id AS id,
            string_agg((ata.object_uid)::text, '|'::text ORDER BY (ata.object_uid)::text) AS object_uids
           FROM ((((fmapp.fm_folder ff
             JOIN fmapp.fm_data_uid fdu ON ((ff.folder_id = fdu.fm_data_id)))
             JOIN amapp.am_tag_association ata ON (((fdu.unique_id)::text = (ata.subject_uid)::text)))
             JOIN biomart.bio_data_uid bdu ON (((bdu.unique_id)::text = (ata.object_uid)::text)))
             JOIN biomart.bio_marker bm ON ((bm.bio_marker_id = bdu.bio_data_id)))
          WHERE (((bm.bio_marker_type)::text = 'PATHWAY'::text) AND (((ata.object_type)::text = 'BIO_MARKER'::text) OR ((ata.object_type)::text = 'PROGRAM_TARGET'::text)) AND ((ff.folder_type)::text = 'PROGRAM'::text))
          GROUP BY fdu.unique_id) pathways ON (((pathways.id)::text = (fd.unique_id)::text)))
     LEFT JOIN ( SELECT fdu.unique_id AS id,
            string_agg((ata.object_uid)::text, '|'::text ORDER BY (ata.object_uid)::text) AS object_uids
           FROM ((((fmapp.fm_folder ff
             JOIN fmapp.fm_data_uid fdu ON ((ff.folder_id = fdu.fm_data_id)))
             JOIN amapp.am_tag_association ata ON (((fdu.unique_id)::text = (ata.subject_uid)::text)))
             JOIN biomart.bio_data_uid bdu ON (((bdu.unique_id)::text = (ata.object_uid)::text)))
             JOIN biomart.bio_marker bm ON ((bm.bio_marker_id = bdu.bio_data_id)))
          WHERE (((bm.bio_marker_type)::text = 'GENE'::text) AND (((ata.object_type)::text = 'BIO_MARKER'::text) OR ((ata.object_type)::text = 'PROGRAM_TARGET'::text)) AND ((ff.folder_type)::text = 'PROGRAM'::text))
          GROUP BY fdu.unique_id) genes ON (((genes.id)::text = (fd.unique_id)::text)))
     LEFT JOIN ( SELECT fdu.unique_id AS id,
            string_agg((ata.object_uid)::text, '|'::text ORDER BY (ata.object_uid)::text) AS object_uids
           FROM (((((fmapp.fm_folder ff
             JOIN fmapp.fm_data_uid fdu ON ((ff.folder_id = fdu.fm_data_id)))
             JOIN amapp.am_tag_association ata ON (((fdu.unique_id)::text = (ata.subject_uid)::text)))
             JOIN amapp.am_tag_item ati ON ((ata.tag_item_id = ati.tag_item_id)))
             JOIN biomart.bio_data_uid bdu ON (((bdu.unique_id)::text = (ata.object_uid)::text)))
             JOIN biomart.bio_concept_code bcc ON ((bcc.bio_concept_code_id = bdu.bio_data_id)))
          WHERE (((ata.object_type)::text = ANY (ARRAY[('BIO_CONCEPT_CODE'::character varying)::text, ('PROGRAM_TARGET'::character varying)::text])) AND ((ff.folder_type)::text = 'PROGRAM'::text) AND ((ati.code_type_name)::text = 'THERAPEUTIC_DOMAIN'::text))
          GROUP BY fdu.unique_id) therapeutic_domains ON (((therapeutic_domains.id)::text = (fd.unique_id)::text)))
     LEFT JOIN ( SELECT fdu.unique_id AS id,
            string_agg((ata.object_uid)::text, '|'::text ORDER BY (ata.object_uid)::text) AS object_uids
           FROM (((((fmapp.fm_folder ff
             JOIN fmapp.fm_data_uid fdu ON ((ff.folder_id = fdu.fm_data_id)))
             JOIN amapp.am_tag_association ata ON (((fdu.unique_id)::text = (ata.subject_uid)::text)))
             JOIN amapp.am_tag_item ati ON ((ata.tag_item_id = ati.tag_item_id)))
             JOIN biomart.bio_data_uid bdu ON (((bdu.unique_id)::text = (ata.object_uid)::text)))
             JOIN biomart.bio_concept_code bcc ON ((bcc.bio_concept_code_id = bdu.bio_data_id)))
          WHERE (((ata.object_type)::text = ANY (ARRAY[('BIO_CONCEPT_CODE'::character varying)::text, ('PROGRAM_TARGET'::character varying)::text])) AND ((ff.folder_type)::text = 'PROGRAM'::text) AND ((ati.code_type_name)::text = 'PROGRAM_INSTITUTION'::text))
          GROUP BY fdu.unique_id) institutions ON (((institutions.id)::text = (fd.unique_id)::text)))
     LEFT JOIN ( SELECT fdu.unique_id AS id,
            string_agg((ata.object_uid)::text, '|'::text ORDER BY (ata.object_uid)::text) AS object_uids
           FROM (((((fmapp.fm_folder ff
             JOIN fmapp.fm_data_uid fdu ON ((ff.folder_id = fdu.fm_data_id)))
             JOIN amapp.am_tag_association ata ON (((fdu.unique_id)::text = (ata.subject_uid)::text)))
             JOIN amapp.am_tag_item ati ON ((ata.tag_item_id = ati.tag_item_id)))
             JOIN biomart.bio_data_uid bdu ON (((bdu.unique_id)::text = (ata.object_uid)::text)))
             JOIN biomart.bio_concept_code bcc ON ((bcc.bio_concept_code_id = bdu.bio_data_id)))
          WHERE (((ata.object_type)::text = ANY (ARRAY[('BIO_CONCEPT_CODE'::character varying)::text, ('PROGRAM_TARGET'::character varying)::text])) AND ((ff.folder_type)::text = 'PROGRAM'::text) AND ((ati.code_type_name)::text = 'PROGRAM_TARGET_PATHWAY_PHENOTYPE'::text))
          GROUP BY fdu.unique_id) targets ON (((targets.id)::text = (fd.unique_id)::text)))
  WHERE (((f.folder_type)::text = 'PROGRAM'::text) AND f.active_ind);


ALTER TABLE biomart_user.browse_programs_view OWNER TO biomart_user;

--
-- Name: browse_studies_view; Type: VIEW; Schema: biomart_user; Owner: biomart_user
--

CREATE VIEW biomart_user.browse_studies_view AS
 SELECT fd.unique_id AS id,
    exp.title,
    exp.description,
    exp.design,
    exp.biomarker_type,
    exp.access_type,
    exp.accession,
    exp.institution,
    exp.country,
    diseases.object_uids AS disease,
    compounds.object_uids AS compound,
    study_objectives.object_uids AS study_objective,
    species.object_uids AS organism,
    phases.object_uids AS study_phase
   FROM (((((((((biomart.bio_experiment exp
     JOIN biomart.bio_data_uid bd ON ((exp.bio_experiment_id = bd.bio_data_id)))
     JOIN fmapp.fm_folder_association fa ON (((fa.object_uid)::text = (bd.unique_id)::text)))
     JOIN fmapp.fm_data_uid fd ON ((fa.folder_id = fd.fm_data_id)))
     JOIN fmapp.fm_folder ff ON ((ff.folder_id = fa.folder_id)))
     LEFT JOIN ( SELECT fdu.unique_id AS id,
            string_agg((ata.object_uid)::text, '|'::text ORDER BY (ata.object_uid)::text) AS object_uids
           FROM ((fmapp.fm_folder_association ffa
             JOIN fmapp.fm_data_uid fdu ON ((ffa.folder_id = fdu.fm_data_id)))
             JOIN amapp.am_tag_association ata ON (((fdu.unique_id)::text = (ata.subject_uid)::text)))
          WHERE ((ata.object_type)::text = 'BIO_DISEASE'::text)
          GROUP BY fdu.unique_id) diseases ON (((diseases.id)::text = (fd.unique_id)::text)))
     LEFT JOIN ( SELECT fdu.unique_id AS id,
            string_agg((ata.object_uid)::text, '|'::text ORDER BY (ata.object_uid)::text) AS object_uids
           FROM ((fmapp.fm_folder_association ffa
             JOIN fmapp.fm_data_uid fdu ON ((ffa.folder_id = fdu.fm_data_id)))
             JOIN amapp.am_tag_association ata ON (((fdu.unique_id)::text = (ata.subject_uid)::text)))
          WHERE ((ata.object_type)::text = 'BIO_COMPOUND'::text)
          GROUP BY fdu.unique_id) compounds ON (((compounds.id)::text = (fd.unique_id)::text)))
     LEFT JOIN ( SELECT fdu.unique_id AS id,
            string_agg((ata.object_uid)::text, '|'::text ORDER BY (ata.object_uid)::text) AS object_uids
           FROM (((fmapp.fm_folder_association ffa
             JOIN fmapp.fm_data_uid fdu ON ((ffa.folder_id = fdu.fm_data_id)))
             JOIN amapp.am_tag_association ata ON (((fdu.unique_id)::text = (ata.subject_uid)::text)))
             JOIN amapp.am_tag_item ati ON ((ata.tag_item_id = ati.tag_item_id)))
          WHERE (((ata.object_type)::text = 'BIO_CONCEPT_CODE'::text) AND ((ati.code_type_name)::text = 'STUDY_OBJECTIVE'::text))
          GROUP BY fdu.unique_id) study_objectives ON (((study_objectives.id)::text = (fd.unique_id)::text)))
     LEFT JOIN ( SELECT fdu.unique_id AS id,
            string_agg((ata.object_uid)::text, '|'::text ORDER BY (ata.object_uid)::text) AS object_uids
           FROM (((fmapp.fm_folder_association ffa
             JOIN fmapp.fm_data_uid fdu ON ((ffa.folder_id = fdu.fm_data_id)))
             JOIN amapp.am_tag_association ata ON (((fdu.unique_id)::text = (ata.subject_uid)::text)))
             JOIN amapp.am_tag_item ati ON ((ata.tag_item_id = ati.tag_item_id)))
          WHERE (((ata.object_type)::text = 'BIO_CONCEPT_CODE'::text) AND ((ati.code_type_name)::text = 'SPECIES'::text))
          GROUP BY fdu.unique_id) species ON (((species.id)::text = (fd.unique_id)::text)))
     LEFT JOIN ( SELECT fdu.unique_id AS id,
            string_agg((ata.object_uid)::text, '|'::text ORDER BY (ata.object_uid)::text) AS object_uids
           FROM (((fmapp.fm_folder_association ffa
             JOIN fmapp.fm_data_uid fdu ON ((ffa.folder_id = fdu.fm_data_id)))
             JOIN amapp.am_tag_association ata ON (((fdu.unique_id)::text = (ata.subject_uid)::text)))
             JOIN amapp.am_tag_item ati ON ((ata.tag_item_id = ati.tag_item_id)))
          WHERE (((ata.object_type)::text = 'BIO_CONCEPT_CODE'::text) AND ((ati.code_type_name)::text = 'STUDY_PHASE'::text))
          GROUP BY fdu.unique_id) phases ON (((phases.id)::text = (fd.unique_id)::text)))
  WHERE ff.active_ind;


ALTER TABLE biomart_user.browse_studies_view OWNER TO biomart_user;


--
-- Name: gene_ontology; Type: TABLE; Schema: biomart_user; Owner: biomart_user
--

CREATE TABLE biomart_user.gene_ontology (
    pathway character varying(100),
    descr character varying(500)
);


ALTER TABLE biomart_user.gene_ontology OWNER TO biomart_user;

--
-- Name: gene_ontology_data; Type: TABLE; Schema: biomart_user; Owner: biomart_user
--

CREATE TABLE biomart_user.gene_ontology_data (
    pathway character varying(100),
    gene_id character varying(20),
    gene_symbol character varying(200),
    organism character varying(100)
);


ALTER TABLE biomart_user.gene_ontology_data OWNER TO biomart_user;

--
-- Name: ioe_temp_obj_reg2; Type: TABLE; Schema: biomart_user; Owner: biomart_user
--

CREATE TABLE biomart_user.ioe_temp_obj_reg2 (
    object_schema character varying(32),
    object_name character varying(32),
    object_type character varying(64),
    kde_username character varying(64),
    kde_session character varying(255),
    object_created_timestamp timestamp(0) without time zone
);


ALTER TABLE biomart_user.ioe_temp_obj_reg2 OWNER TO biomart_user;

--
-- Name: kde_temp_obj_reg3; Type: TABLE; Schema: biomart_user; Owner: biomart_user
--

CREATE TABLE biomart_user.kde_temp_obj_reg3 (
    object_schema character varying(32),
    object_name character varying(32),
    object_type character varying(64),
    kde_username character varying(64),
    kde_session character varying(255),
    object_created_timestamp character(15)
);


ALTER TABLE biomart_user.kde_temp_obj_reg3 OWNER TO biomart_user;

--
-- Name: de_chromo_region_id_seq; Type: SEQUENCE; Schema: deapp; Owner: deapp
--

CREATE SEQUENCE deapp.de_chromo_region_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE deapp.de_chromo_region_id_seq OWNER TO deapp;


--
-- Name: de_chromosomal_region; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_chromosomal_region (
    region_id bigint DEFAULT nextval('deapp.de_chromo_region_id_seq'::regclass) NOT NULL,
    gpl_id character varying(50),
    chromosome character varying(2),
    start_bp bigint,
    end_bp bigint,
    num_probes integer,
    region_name character varying(100),
    cytoband character varying(100),
    gene_symbol character varying(100),
    gene_id bigint,
    organism character varying(200)
);


ALTER TABLE deapp.de_chromosomal_region OWNER TO deapp;

--
-- Name: de_concept_visit; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_concept_visit (
    concept_cd character varying(50) NOT NULL,
    visit_name character varying(100) NOT NULL,
    sourcesystem_cd character varying(50)
);


ALTER TABLE deapp.de_concept_visit OWNER TO deapp;

--
-- Name: de_encounter_level; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_encounter_level (
    study_id character varying(50),
    concept_id character varying(500),
    link_type character varying(50)
);


ALTER TABLE deapp.de_encounter_level OWNER TO deapp;

--
-- Name: de_encounter_type; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_encounter_type (
    study_id character varying(200),
    link_type character varying(50),
    link_value character varying(200),
    encounter_num numeric(38,0)
);


ALTER TABLE deapp.de_encounter_type OWNER TO deapp;

--
-- Name: de_gene_info; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_gene_info (
    gene_info_id integer NOT NULL,
    gene_source_id integer DEFAULT 1 NOT NULL,
    entrez_id integer,
    gene_symbol character varying(255) NOT NULL,
    gene_name character varying(255),
    chrom character varying(40),
    chrom_start integer,
    chrom_stop integer,
    strand smallint
);


ALTER TABLE deapp.de_gene_info OWNER TO deapp;

--
-- Name: de_gene_info_gene_info_id_seq; Type: SEQUENCE; Schema: deapp; Owner: deapp
--

CREATE SEQUENCE deapp.de_gene_info_gene_info_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE deapp.de_gene_info_gene_info_id_seq OWNER TO deapp;

--
-- Name: de_gene_info_gene_info_id_seq; Type: SEQUENCE OWNED BY; Schema: deapp; Owner: deapp
--

ALTER SEQUENCE deapp.de_gene_info_gene_info_id_seq OWNED BY deapp.de_gene_info.gene_info_id;


--
-- Name: de_gene_source; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_gene_source (
    gene_source_id integer NOT NULL,
    gene_source_name character varying(255) NOT NULL,
    version character varying(32),
    release_date timestamp without time zone,
    url character varying(255)
);


ALTER TABLE deapp.de_gene_source OWNER TO deapp;

--
-- Name: de_gpl_info; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_gpl_info (
    platform character varying(50) NOT NULL,
    title character varying(500),
    organism character varying(100),
    annotation_date timestamp without time zone,
    marker_type character varying(100),
    release_nbr character varying(50),
    genome_build character varying(20),
    gene_annotation_id character varying(50)
);


ALTER TABLE deapp.de_gpl_info OWNER TO deapp;

--
-- Name: de_mrna_annotation; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_mrna_annotation (
    gpl_id character varying(100),
    probe_id character varying(100),
    gene_symbol character varying(100),
    probeset_id bigint,
    gene_id bigint,
    organism character varying(200),
    de_mrna_annotation_id bigint NOT NULL
);


ALTER TABLE deapp.de_mrna_annotation OWNER TO deapp;

--
-- Name: de_obs_enroll_days; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_obs_enroll_days (
    encounter_num bigint,
    days_since_enroll double precision,
    study_id character varying(200)
);


ALTER TABLE deapp.de_obs_enroll_days OWNER TO deapp;

--
-- Name: de_parent_cd_seq; Type: SEQUENCE; Schema: deapp; Owner: deapp
--

CREATE SEQUENCE deapp.de_parent_cd_seq
    START WITH 3801
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 20;


ALTER TABLE deapp.de_parent_cd_seq OWNER TO deapp;

--
-- Name: de_pathway; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_pathway (
    name character varying(300),
    description character varying(510),
    id bigint NOT NULL,
    type character varying(100),
    source character varying(100),
    externalid character varying(100),
    pathway_uid character varying(200),
    user_id bigint
);


ALTER TABLE deapp.de_pathway OWNER TO deapp;

--
-- Name: de_pathway_gene; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_pathway_gene (
    id bigint NOT NULL,
    pathway_id numeric,
    gene_symbol character varying(200),
    gene_id character varying(200)
);


ALTER TABLE deapp.de_pathway_gene OWNER TO deapp;

--
-- Name: protein_annotation_id; Type: SEQUENCE; Schema: deapp; Owner: deapp
--

CREATE SEQUENCE deapp.protein_annotation_id
    START WITH 4742232
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE deapp.protein_annotation_id OWNER TO deapp;

--
-- Name: de_protein_annotation; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_protein_annotation (
    id bigint DEFAULT nextval('deapp.protein_annotation_id'::regclass) NOT NULL,
    gpl_id character varying(50) NOT NULL,
    peptide character varying(200) NOT NULL,
    uniprot_id character varying(200),
    biomarker_id character varying(200),
    organism character varying(200),
    uniprot_name character varying(200),
    chromosome character varying(5),
    start_bp bigint,
    end_bp bigint
);


ALTER TABLE deapp.de_protein_annotation OWNER TO deapp;

--
-- Name: de_qpcr_mirna_annotation; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_qpcr_mirna_annotation (
    id_ref character varying(100),
    probe_id character varying(100),
    mirna_symbol character varying(100),
    mirna_id character varying(100),
    probeset_id numeric(38,0) NOT NULL,
    organism character varying(200),
    gpl_id character varying(20)
);


ALTER TABLE deapp.de_qpcr_mirna_annotation OWNER TO deapp;

--
-- Name: de_rbm_annotation; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_rbm_annotation (
    id bigint NOT NULL,
    gpl_id character varying(50) NOT NULL,
    antigen_name character varying(200) NOT NULL,
    uniprot_id character varying(50),
    gene_symbol character varying(50),
    gene_id character varying(200),
    uniprot_name character varying(200)
);


ALTER TABLE deapp.de_rbm_annotation OWNER TO deapp;

--
-- Name: de_rbm_data_annotation_join; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_rbm_data_annotation_join (
    data_id bigint NOT NULL,
    annotation_id bigint NOT NULL
);


ALTER TABLE deapp.de_rbm_data_annotation_join OWNER TO deapp;

--
-- Name: de_rc_snp_info_seq; Type: SEQUENCE; Schema: deapp; Owner: deapp
--

CREATE SEQUENCE deapp.de_rc_snp_info_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE deapp.de_rc_snp_info_seq OWNER TO deapp;

--
-- Name: de_rc_snp_info; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_rc_snp_info (
    snp_info_id bigint DEFAULT nextval('deapp.de_rc_snp_info_seq'::regclass) NOT NULL,
    rs_id character varying(50),
    chrom character varying(4),
    pos bigint,
    ref character varying(1000),
    alt character varying(1000),
    gene_info character varying(1000),
    variation_class character varying(24),
    strand character varying(1),
    clinsig character varying(100),
    disease character varying(500),
    gmaf character varying(10),
    gene_biotype character varying(100),
    impact character varying(50),
    transcript_id character varying(100),
    functional_class character varying(100),
    effect character varying(100),
    exon_id character varying(100),
    amino_acid_change character varying(100),
    codon_change character varying(1000),
    hg_version character varying(10),
    gene_name character varying(50),
    entrez_id character varying(50),
    recombination_rate numeric(18,6),
    recombination_map numeric(18,6),
    regulome_score character varying(10),
    exon_intron character varying(50)
);


ALTER TABLE deapp.de_rc_snp_info OWNER TO deapp;

--
-- Name: de_rnaseq_annotation; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_rnaseq_annotation (
    gpl_id character varying(50),
    transcript_id character varying(50),
    gene_symbol character varying(50),
    gene_id character varying(50),
    organism character varying(30),
    probeset_id bigint
);


ALTER TABLE deapp.de_rnaseq_annotation OWNER TO deapp;

--
-- Name: de_sample_snp_data; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_sample_snp_data (
    sample_id character varying(50),
    snp_name character varying(100),
    snp_calls character varying(4),
    copy_number double precision
);


ALTER TABLE deapp.de_sample_snp_data OWNER TO deapp;

--
-- Name: de_saved_comparison; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_saved_comparison (
    comparison_id bigint NOT NULL,
    query_id1 bigint,
    query_id2 bigint
);


ALTER TABLE deapp.de_saved_comparison OWNER TO deapp;

--
-- Name: de_snp_calls_by_gsm; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_snp_calls_by_gsm (
    gsm_num character varying(100),
    trial_name character varying(20),
    patient_num bigint,
    snp_name character varying(100),
    snp_calls character varying(4)
);


ALTER TABLE deapp.de_snp_calls_by_gsm OWNER TO deapp;

--
-- Name: de_snp_copy_number; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_snp_copy_number (
    patient_num bigint,
    trial_name character varying(20),
    snp_name character varying(50),
    chrom character varying(2),
    chrom_pos bigint,
    copy_number smallint
);


ALTER TABLE deapp.de_snp_copy_number OWNER TO deapp;

--
-- Name: de_snp_data_by_patient; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_snp_data_by_patient (
    snp_data_by_patient_id bigint NOT NULL,
    snp_dataset_id bigint,
    trial_name character varying(255),
    patient_num bigint,
    chrom character varying(16),
    data_by_patient_chr text,
    ped_by_patient_chr text
);


ALTER TABLE deapp.de_snp_data_by_patient OWNER TO deapp;

--
-- Name: de_snp_data_by_probe; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_snp_data_by_probe (
    snp_data_by_probe_id bigint NOT NULL,
    probe_id bigint,
    probe_name character varying(255),
    snp_id bigint,
    snp_name character varying(255),
    trial_name character varying(255),
    data_by_probe text
);


ALTER TABLE deapp.de_snp_data_by_probe OWNER TO deapp;

--
-- Name: de_snp_data_dataset_loc; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_snp_data_dataset_loc (
    snp_data_dataset_loc_id bigint NOT NULL,
    trial_name character varying(255),
    snp_dataset_id bigint,
    location bigint
);


ALTER TABLE deapp.de_snp_data_dataset_loc OWNER TO deapp;

--
-- Name: de_snp_gene_map; Type: VIEW; Schema: deapp; Owner: deapp
--

CREATE VIEW deapp.de_snp_gene_map AS
 SELECT de_rc_snp_info.snp_info_id AS snp_id,
    de_rc_snp_info.rs_id AS snp_name,
    de_rc_snp_info.entrez_id AS entrez_gene_id,
    de_rc_snp_info.gene_name
   FROM deapp.de_rc_snp_info
  WHERE ((de_rc_snp_info.hg_version)::numeric = (19)::numeric);


ALTER TABLE deapp.de_snp_gene_map OWNER TO deapp;

--
-- Name: de_snp_info; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_snp_info (
    snp_info_id bigint NOT NULL,
    name character varying(255),
    chrom character varying(16),
    chrom_pos bigint
);


ALTER TABLE deapp.de_snp_info OWNER TO deapp;

--
-- Name: de_snp_probe; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_snp_probe (
    snp_probe_id bigint NOT NULL,
    probe_name character varying(255),
    snp_id bigint,
    snp_name character varying(255),
    vendor_name character varying(255)
);


ALTER TABLE deapp.de_snp_probe OWNER TO deapp;

--
-- Name: de_snp_probe_sorted_def; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_snp_probe_sorted_def (
    snp_probe_sorted_def_id bigint NOT NULL,
    platform_name character varying(255),
    num_probe bigint,
    chrom character varying(16),
    probe_def text,
    snp_id_def text
);


ALTER TABLE deapp.de_snp_probe_sorted_def OWNER TO deapp;

--
-- Name: de_snp_subject_sorted_def; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_snp_subject_sorted_def (
    snp_subject_sorted_def_id bigint NOT NULL,
    trial_name character varying(255),
    patient_position integer,
    patient_num bigint,
    subject_id character varying(255)
);


ALTER TABLE deapp.de_snp_subject_sorted_def OWNER TO deapp;

--
-- Name: de_subject_acgh_data; Type: TABLE; Schema: deapp; Owner: tm_cz; Tablespace: transmart
--

CREATE TABLE deapp.de_subject_acgh_data (
    trial_name character varying(50),
    region_id bigint NOT NULL,
    assay_id bigint NOT NULL,
    patient_id bigint,
    chip double precision,
    segmented double precision,
    flag smallint,
    probhomloss double precision,
    probloss double precision,
    probnorm double precision,
    probgain double precision,
    probamp double precision,
    partition_id numeric
);


ALTER TABLE deapp.de_subject_acgh_data OWNER TO tm_cz;

--
-- Name: COLUMN de_subject_acgh_data.chip; Type: COMMENT; Schema: deapp; Owner: tm_cz
--

COMMENT ON COLUMN deapp.de_subject_acgh_data.chip IS 'log2ratio';


--
-- Name: COLUMN de_subject_acgh_data.segmented; Type: COMMENT; Schema: deapp; Owner: tm_cz
--

COMMENT ON COLUMN deapp.de_subject_acgh_data.segmented IS 'segmented log2ratio';


--
-- Name: COLUMN de_subject_acgh_data.flag; Type: COMMENT; Schema: deapp; Owner: tm_cz
--

COMMENT ON COLUMN deapp.de_subject_acgh_data.flag IS 'call  -2:homloss, -1:loss, 0:normal, 1:gain, 2:amp';


--
-- Name: de_subject_metabolomics_data; Type: TABLE; Schema: deapp; Owner: tm_cz; Tablespace: transmart
--

CREATE TABLE deapp.de_subject_metabolomics_data (
    trial_source character varying(200),
    trial_name character varying(200),
    metabolite_annotation_id bigint,
    assay_id bigint,
    subject_id character varying(100),
    patient_id bigint,
    raw_intensity double precision NOT NULL,
    log_intensity double precision NOT NULL,
    zscore double precision NOT NULL,
    partition_id numeric
);


ALTER TABLE deapp.de_subject_metabolomics_data OWNER TO tm_cz;

--
-- Name: de_subject_microarray_data; Type: TABLE; Schema: deapp; Owner: tm_cz; Tablespace: transmart
--

CREATE TABLE deapp.de_subject_microarray_data (
    trial_name character varying(50),
    probeset_id bigint,
    assay_id bigint,
    patient_id bigint,
    sample_id bigint,
    subject_id character varying(50),
    raw_intensity double precision,
    log_intensity double precision,
    zscore double precision,
    new_raw double precision,
    new_log double precision,
    new_zscore double precision,
    trial_source character varying(200),
    partition_id numeric
);


ALTER TABLE deapp.de_subject_microarray_data OWNER TO tm_cz;

--
-- Name: de_subject_microarray_data_new; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_subject_microarray_data_new (
    trial_source character varying(200),
    trial_name character varying(50),
    probeset_id bigint,
    assay_id bigint,
    patient_id bigint,
    raw_intensity double precision,
    log_intensity double precision,
    zscore double precision
);


ALTER TABLE deapp.de_subject_microarray_data_new OWNER TO deapp;

--
-- Name: de_subject_microarray_logs; Type: TABLE; Schema: deapp; Owner: tm_cz; Tablespace: transmart
--

CREATE TABLE deapp.de_subject_microarray_logs (
    probeset character varying(50),
    raw_intensity numeric,
    pvalue double precision,
    refseq character varying(50),
    gene_symbol character varying(50),
    assay_id bigint,
    patient_id bigint,
    subject_id character varying(20),
    trial_name character varying(15),
    timepoint character varying(30),
    log_intensity numeric
);


ALTER TABLE deapp.de_subject_microarray_logs OWNER TO tm_cz;

--
-- Name: de_subject_microarray_med; Type: TABLE; Schema: deapp; Owner: tm_cz; Tablespace: transmart
--

CREATE TABLE deapp.de_subject_microarray_med (
    probeset character varying(50),
    raw_intensity numeric,
    log_intensity numeric,
    gene_symbol character varying(50),
    assay_id bigint,
    patient_id bigint,
    subject_id character varying(20),
    trial_name character varying(15),
    timepoint character varying(30),
    pvalue double precision,
    refseq character varying(50),
    mean_intensity numeric,
    stddev_intensity numeric,
    median_intensity numeric,
    zscore double precision
);


ALTER TABLE deapp.de_subject_microarray_med OWNER TO tm_cz;

--
-- Name: de_subject_mirna_data; Type: TABLE; Schema: deapp; Owner: tm_cz; Tablespace: transmart
--

CREATE TABLE deapp.de_subject_mirna_data (
    trial_source character varying(200),
    trial_name character varying(50),
    assay_id numeric(18,0) NOT NULL,
    patient_id numeric(18,0),
    raw_intensity numeric,
    log_intensity numeric,
    probeset_id numeric(38,0) NOT NULL,
    zscore numeric(18,9),
    partition_id numeric
);


ALTER TABLE deapp.de_subject_mirna_data OWNER TO tm_cz;

--
-- Name: de_subject_protein_data; Type: TABLE; Schema: deapp; Owner: tm_cz; Tablespace: transmart
--

CREATE TABLE deapp.de_subject_protein_data (
    trial_name character varying(50),
    protein_annotation_id bigint,
    component character varying(100),
    patient_id numeric(38,0),
    gene_symbol character varying(100),
    gene_id character varying(200),
    assay_id numeric,
    subject_id character varying(100),
    intensity numeric,
    zscore numeric,
    log_intensity numeric,
    partition_id numeric
);


ALTER TABLE deapp.de_subject_protein_data OWNER TO tm_cz;

--
-- Name: de_subject_proteomics_data; Type: TABLE; Schema: deapp; Owner: tm_cz; Tablespace: transmart
--

CREATE TABLE deapp.de_subject_proteomics_data (
    trial_name character varying(15),
    protein_annotation_id bigint,
    component character varying(100),
    patient_id bigint,
    gene_symbol character varying(100),
    gene_id bigint,
    assay_id bigint,
    subject_id character varying(100),
    intensity bigint,
    zscore bigint,
    partition_id numeric
);


ALTER TABLE deapp.de_subject_proteomics_data OWNER TO tm_cz;

--
-- Name: de_subject_rbm_data; Type: TABLE; Schema: deapp; Owner: tm_cz; Tablespace: transmart
--

CREATE TABLE deapp.de_subject_rbm_data (
    trial_name character varying(100),
    antigen_name character varying(100),
    n_value bigint,
    patient_id bigint,
    gene_symbol character varying(100),
    gene_id integer,
    assay_id bigint,
    normalized_value double precision,
    concept_cd character varying(100),
    timepoint character varying(100),
    data_uid character varying(100),
    value double precision,
    log_intensity numeric,
    mean_intensity numeric,
    stddev_intensity numeric,
    median_intensity numeric,
    zscore double precision,
    rbm_panel character varying(50),
    unit character varying(50),
    id bigint NOT NULL,
    partition_id numeric
);


ALTER TABLE deapp.de_subject_rbm_data OWNER TO tm_cz;

--
-- Name: de_subject_rbm_data_seq; Type: SEQUENCE; Schema: deapp; Owner: deapp
--

CREATE SEQUENCE deapp.de_subject_rbm_data_seq
    START WITH 1272564
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE deapp.de_subject_rbm_data_seq OWNER TO deapp;

--
-- Name: de_subject_rna_data; Type: TABLE; Schema: deapp; Owner: tm_cz; Tablespace: transmart
--

CREATE TABLE deapp.de_subject_rna_data (
    trial_source character varying(200),
    trial_name character varying(50),
    probeset_id character varying(200),
    assay_id bigint,
    patient_id bigint,
    raw_intensity double precision,
    log_intensity double precision,
    zscore double precision,
    partition_id numeric
);


ALTER TABLE deapp.de_subject_rna_data OWNER TO tm_cz;

--
-- Name: de_subject_rnaseq_data; Type: TABLE; Schema: deapp; Owner: tm_cz; Tablespace: transmart
--

CREATE TABLE deapp.de_subject_rnaseq_data (
    trial_source character varying(200),
    trial_name character varying(50),
    region_id bigint NOT NULL,
    assay_id bigint NOT NULL,
    patient_id bigint,
    readcount bigint,
    normalized_readcount double precision,
    log_normalized_readcount double precision,
    zscore double precision,
    partition_id bigint
);


ALTER TABLE deapp.de_subject_rnaseq_data OWNER TO tm_cz;

--
-- Name: de_subject_sample_mapping; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_subject_sample_mapping (
    patient_id bigint,
    site_id character varying(100),
    subject_id character varying(100),
    subject_type character varying(100),
    concept_code character varying(1000),
    assay_id bigint NOT NULL,
    patient_uid character varying(50),
    sample_type character varying(100),
    assay_uid character varying(100),
    trial_name character varying(30),
    timepoint character varying(100),
    timepoint_cd character varying(50),
    sample_type_cd character varying(50),
    tissue_type_cd character varying(50),
    platform character varying(50),
    platform_cd character varying(50),
    tissue_type character varying(100),
    data_uid character varying(100),
    gpl_id character varying(50),
    rbm_panel character varying(50),
    sample_id bigint,
    sample_cd character varying(200),
    category_cd character varying(1000),
    source_cd character varying(200),
    omic_source_study character varying(200),
    omic_patient_num bigint,
    omic_patient_id bigint,
    partition_id numeric
);


ALTER TABLE deapp.de_subject_sample_mapping OWNER TO deapp;

--
-- Name: de_subject_snp_dataset; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_subject_snp_dataset (
    subject_snp_dataset_id bigint NOT NULL,
    dataset_name character varying(255),
    concept_cd character varying(255),
    platform_name character varying(255),
    trial_name character varying(255),
    patient_num bigint,
    timepoint character varying(255),
    subject_id character varying(255),
    sample_type character varying(255),
    paired_dataset_id bigint,
    patient_gender character varying(1)
);


ALTER TABLE deapp.de_subject_snp_dataset OWNER TO deapp;

--
-- Name: de_two_region_event_seq; Type: SEQUENCE; Schema: deapp; Owner: deapp
--

CREATE SEQUENCE deapp.de_two_region_event_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE deapp.de_two_region_event_seq OWNER TO deapp;

--
-- Name: de_two_region_event; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_two_region_event (
    two_region_event_id bigint DEFAULT nextval('deapp.de_two_region_event_seq'::regclass) NOT NULL,
    cga_type character varying(500),
    soap_class character varying(500)
);


ALTER TABLE deapp.de_two_region_event OWNER TO deapp;

--
-- Name: COLUMN de_two_region_event.cga_type; Type: COMMENT; Schema: deapp; Owner: deapp
--

COMMENT ON COLUMN deapp.de_two_region_event.cga_type IS 'deletion, inversion, duplication,... Type from http://cgatools.sourceforge.net/docs/1.8.0/cgatools-command-line-reference.html#junctions2events';


--
-- Name: COLUMN de_two_region_event.soap_class; Type: COMMENT; Schema: deapp; Owner: deapp
--

COMMENT ON COLUMN deapp.de_two_region_event.soap_class IS 'inter/intra chromosomal inversion/translocation: http://sourceforge.net/p/soapfuse/wiki/classification-of-fusions.for.SOAPfuse/';


--
-- Name: de_two_region_event_gene_seq; Type: SEQUENCE; Schema: deapp; Owner: deapp
--

CREATE SEQUENCE deapp.de_two_region_event_gene_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE deapp.de_two_region_event_gene_seq OWNER TO deapp;

--
-- Name: de_two_region_event_gene; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_two_region_event_gene (
    two_region_event_gene_id bigint DEFAULT nextval('deapp.de_two_region_event_gene_seq'::regclass) NOT NULL,
    gene_id character varying(50) NOT NULL,
    event_id bigint NOT NULL,
    effect character varying(500)
);


ALTER TABLE deapp.de_two_region_event_gene OWNER TO deapp;

--
-- Name: COLUMN de_two_region_event_gene.gene_id; Type: COMMENT; Schema: deapp; Owner: deapp
--

COMMENT ON COLUMN deapp.de_two_region_event_gene.gene_id IS 'HUGO gene identifier';


--
-- Name: COLUMN de_two_region_event_gene.effect; Type: COMMENT; Schema: deapp; Owner: deapp
--

COMMENT ON COLUMN deapp.de_two_region_event_gene.effect IS 'effect of the event on the gene: FUSION, CONTAINED, DISRUPTED, ...';


--
-- Name: de_two_region_junction_seq; Type: SEQUENCE; Schema: deapp; Owner: deapp
--

CREATE SEQUENCE deapp.de_two_region_junction_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE deapp.de_two_region_junction_seq OWNER TO deapp;

--
-- Name: de_two_region_junction; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_two_region_junction (
    two_region_junction_id bigint DEFAULT nextval('deapp.de_two_region_junction_seq'::regclass) NOT NULL,
    up_end bigint NOT NULL,
    up_chr character varying(50) NOT NULL,
    up_pos bigint NOT NULL,
    up_strand character(1),
    down_end bigint NOT NULL,
    down_chr character varying(50) NOT NULL,
    down_pos bigint NOT NULL,
    down_strand character(1),
    is_in_frame boolean,
    external_id bigint,
    assay_id bigint
);


ALTER TABLE deapp.de_two_region_junction OWNER TO deapp;

--
-- Name: COLUMN de_two_region_junction.up_end; Type: COMMENT; Schema: deapp; Owner: deapp
--

COMMENT ON COLUMN deapp.de_two_region_junction.up_end IS 'end of up stream junction';


--
-- Name: COLUMN de_two_region_junction.up_chr; Type: COMMENT; Schema: deapp; Owner: deapp
--

COMMENT ON COLUMN deapp.de_two_region_junction.up_chr IS 'chromosome of up stream fusion partner';


--
-- Name: COLUMN de_two_region_junction.up_pos; Type: COMMENT; Schema: deapp; Owner: deapp
--

COMMENT ON COLUMN deapp.de_two_region_junction.up_pos IS 'location of up stream fusion partner''s junction point';


--
-- Name: COLUMN de_two_region_junction.up_strand; Type: COMMENT; Schema: deapp; Owner: deapp
--

COMMENT ON COLUMN deapp.de_two_region_junction.up_strand IS 'strand of up stream junction, 1 for +, 0 for -';


--
-- Name: COLUMN de_two_region_junction.down_end; Type: COMMENT; Schema: deapp; Owner: deapp
--

COMMENT ON COLUMN deapp.de_two_region_junction.down_end IS 'end of down stream junction';


--
-- Name: COLUMN de_two_region_junction.down_chr; Type: COMMENT; Schema: deapp; Owner: deapp
--

COMMENT ON COLUMN deapp.de_two_region_junction.down_chr IS 'chromosome of down stream fusion partner';


--
-- Name: COLUMN de_two_region_junction.down_pos; Type: COMMENT; Schema: deapp; Owner: deapp
--

COMMENT ON COLUMN deapp.de_two_region_junction.down_pos IS 'location of down stream junction point';


--
-- Name: COLUMN de_two_region_junction.down_strand; Type: COMMENT; Schema: deapp; Owner: deapp
--

COMMENT ON COLUMN deapp.de_two_region_junction.down_strand IS 'strand of down stream junction, 1 for +, 0 for -';


--
-- Name: COLUMN de_two_region_junction.is_in_frame; Type: COMMENT; Schema: deapp; Owner: deapp
--

COMMENT ON COLUMN deapp.de_two_region_junction.is_in_frame IS 'whether junction is frame-shift or in-frame-shift';


--
-- Name: de_two_rgn_junction_event_seq; Type: SEQUENCE; Schema: deapp; Owner: deapp
--

CREATE SEQUENCE deapp.de_two_rgn_junction_event_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE deapp.de_two_rgn_junction_event_seq OWNER TO deapp;

--
-- Name: de_two_region_junction_event; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_two_region_junction_event (
    two_region_junction_event_id bigint DEFAULT nextval('deapp.de_two_rgn_junction_event_seq'::regclass) NOT NULL,
    junction_id bigint,
    event_id bigint,
    reads_span integer,
    reads_junction integer,
    pairs_span integer,
    pairs_junction integer,
    pairs_end integer,
    reads_counter integer,
    base_freq double precision
);


ALTER TABLE deapp.de_two_region_junction_event OWNER TO deapp;

--
-- Name: COLUMN de_two_region_junction_event.reads_span; Type: COMMENT; Schema: deapp; Owner: deapp
--

COMMENT ON COLUMN deapp.de_two_region_junction_event.reads_span IS 'number of reads in the whole span';


--
-- Name: COLUMN de_two_region_junction_event.reads_junction; Type: COMMENT; Schema: deapp; Owner: deapp
--

COMMENT ON COLUMN deapp.de_two_region_junction_event.reads_junction IS 'number of reads spanning the junction';


--
-- Name: COLUMN de_two_region_junction_event.pairs_span; Type: COMMENT; Schema: deapp; Owner: deapp
--

COMMENT ON COLUMN deapp.de_two_region_junction_event.pairs_span IS 'number of spanning mate pairs ';


--
-- Name: COLUMN de_two_region_junction_event.pairs_junction; Type: COMMENT; Schema: deapp; Owner: deapp
--

COMMENT ON COLUMN deapp.de_two_region_junction_event.pairs_junction IS 'number of spanning mate pairs where one end spans a fusion ';


--
-- Name: COLUMN de_two_region_junction_event.pairs_end; Type: COMMENT; Schema: deapp; Owner: deapp
--

COMMENT ON COLUMN deapp.de_two_region_junction_event.pairs_end IS 'number of mate pairs that support the fusion and whose one end spans the fusion';


--
-- Name: COLUMN de_two_region_junction_event.reads_counter; Type: COMMENT; Schema: deapp; Owner: deapp
--

COMMENT ON COLUMN deapp.de_two_region_junction_event.reads_counter IS 'number of reads that contradict the fusion by mapping to only one of the chromosomes';


--
-- Name: COLUMN de_two_region_junction_event.base_freq; Type: COMMENT; Schema: deapp; Owner: deapp
--

COMMENT ON COLUMN deapp.de_two_region_junction_event.base_freq IS 'frequency in the baseline set of genomes for the junction';


--
-- Name: de_variant_dataset; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_variant_dataset (
    dataset_id character varying(50) NOT NULL,
    datasource_id character varying(200),
    etl_id character varying(20),
    etl_date date,
    genome character varying(50) NOT NULL,
    metadata_comment text,
    variant_dataset_type character varying(50),
    gpl_id character varying(50)
);


ALTER TABLE deapp.de_variant_dataset OWNER TO deapp;

--
-- Name: de_variant_metadata_seq; Type: SEQUENCE; Schema: deapp; Owner: deapp
--

CREATE SEQUENCE deapp.de_variant_metadata_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE deapp.de_variant_metadata_seq OWNER TO deapp;

--
-- Name: de_variant_metadata; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_variant_metadata (
    de_variant_metadata_id integer DEFAULT nextval('deapp.de_variant_metadata_seq'::regclass) NOT NULL,
    dataset_id character varying(50),
    key character varying(255),
    value text
);


ALTER TABLE deapp.de_variant_metadata OWNER TO deapp;

--
-- Name: de_variant_population_data_seq; Type: SEQUENCE; Schema: deapp; Owner: deapp
--

CREATE SEQUENCE deapp.de_variant_population_data_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE deapp.de_variant_population_data_seq OWNER TO deapp;

--
-- Name: de_variant_population_data; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_variant_population_data (
    variant_population_data_id bigint DEFAULT nextval('deapp.de_variant_population_data_seq'::regclass) NOT NULL,
    dataset_id character varying(50),
    chr character varying(50),
    pos bigint,
    info_name character varying(100),
    info_index integer DEFAULT 0,
    integer_value bigint,
    float_value double precision,
    text_value character varying(4000)
);


ALTER TABLE deapp.de_variant_population_data OWNER TO deapp;

--
-- Name: de_variant_population_info_seq; Type: SEQUENCE; Schema: deapp; Owner: deapp
--

CREATE SEQUENCE deapp.de_variant_population_info_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE deapp.de_variant_population_info_seq OWNER TO deapp;

--
-- Name: de_variant_population_info; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_variant_population_info (
    variant_population_info_id bigint DEFAULT nextval('deapp.de_variant_population_info_seq'::regclass) NOT NULL,
    dataset_id character varying(50),
    info_name character varying(100),
    description text,
    type character varying(30),
    number character varying(10)
);


ALTER TABLE deapp.de_variant_population_info OWNER TO deapp;

--
-- Name: de_variant_subject_detail_seq; Type: SEQUENCE; Schema: deapp; Owner: deapp
--

CREATE SEQUENCE deapp.de_variant_subject_detail_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE deapp.de_variant_subject_detail_seq OWNER TO deapp;

--
-- Name: de_variant_subject_detail; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_variant_subject_detail (
    variant_subject_detail_id bigint DEFAULT nextval('deapp.de_variant_subject_detail_seq'::regclass) NOT NULL,
    dataset_id character varying(50),
    chr character varying(50),
    pos bigint,
    rs_id character varying(50),
    ref character varying(500),
    alt character varying(500),
    qual character varying(100),
    filter character varying(50),
    info character varying(10000),
    format character varying(500),
    variant_value text
);


ALTER TABLE deapp.de_variant_subject_detail OWNER TO deapp;

--
-- Name: de_variant_subject_idx_seq; Type: SEQUENCE; Schema: deapp; Owner: deapp
--

CREATE SEQUENCE deapp.de_variant_subject_idx_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE deapp.de_variant_subject_idx_seq OWNER TO deapp;

--
-- Name: de_variant_subject_idx; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_variant_subject_idx (
    dataset_id character varying(50),
    subject_id character varying(50),
    "position" bigint,
    variant_subject_idx_id bigint DEFAULT nextval('deapp.de_variant_subject_idx_seq'::regclass) NOT NULL
);


ALTER TABLE deapp.de_variant_subject_idx OWNER TO deapp;

--
-- Name: de_variant_subject_summary_seq; Type: SEQUENCE; Schema: deapp; Owner: deapp
--

CREATE SEQUENCE deapp.de_variant_subject_summary_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE deapp.de_variant_subject_summary_seq OWNER TO deapp;

--
-- Name: de_variant_subject_summary; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_variant_subject_summary (
    variant_subject_summary_id bigint DEFAULT nextval('deapp.de_variant_subject_summary_seq'::regclass) NOT NULL,
    chr character varying(50),
    pos bigint,
    dataset_id character varying(50) NOT NULL,
    subject_id character varying(50) NOT NULL,
    rs_id character varying(500),
    variant character varying(1000),
    variant_format character varying(100),
    variant_type character varying(100),
    reference boolean,
    allele1 integer,
    allele2 integer,
    assay_id bigint
);


ALTER TABLE deapp.de_variant_subject_summary OWNER TO deapp;

--
-- Name: COLUMN de_variant_subject_summary.reference; Type: COMMENT; Schema: deapp; Owner: deapp
--

COMMENT ON COLUMN deapp.de_variant_subject_summary.reference IS 'This column contains a flag whether this subject has a reference value on this variant, or not.';


--
-- Name: COLUMN de_variant_subject_summary.assay_id; Type: COMMENT; Schema: deapp; Owner: deapp
--

COMMENT ON COLUMN deapp.de_variant_subject_summary.assay_id IS 'Reference to de_subject_sample_mapping';


--
-- Name: de_variant_summary_detail_gene; Type: VIEW; Schema: deapp; Owner: deapp
--

CREATE VIEW deapp.de_variant_summary_detail_gene AS
 SELECT summary.variant_subject_summary_id,
    summary.chr,
    summary.pos,
    summary.dataset_id,
    summary.subject_id,
    summary.rs_id,
    summary.variant,
    summary.variant_format,
    summary.variant_type,
    summary.reference,
    summary.allele1,
    summary.allele2,
    summary.assay_id,
    detail.ref,
    detail.alt,
    detail.qual,
    detail.filter,
    detail.info,
    detail.format,
    detail.variant_value,
    genesymbol.text_value AS gene_name,
    geneid.text_value AS gene_id
   FROM (((deapp.de_variant_subject_summary summary
     JOIN deapp.de_variant_subject_detail detail ON ((((detail.dataset_id)::text = (summary.dataset_id)::text) AND ((detail.chr)::text = (summary.chr)::text) AND (detail.pos = summary.pos) AND ((detail.rs_id)::text = (summary.rs_id)::text))))
     LEFT JOIN deapp.de_variant_population_data genesymbol ON ((((genesymbol.dataset_id)::text = (summary.dataset_id)::text) AND ((genesymbol.chr)::text = (summary.chr)::text) AND (genesymbol.pos = summary.pos) AND ((genesymbol.info_name)::text = 'GS'::text))))
     LEFT JOIN deapp.de_variant_population_data geneid ON ((((geneid.dataset_id)::text = (summary.dataset_id)::text) AND ((geneid.chr)::text = (summary.chr)::text) AND (geneid.pos = summary.pos) AND ((geneid.info_name)::text = 'GID'::text))));


ALTER TABLE deapp.de_variant_summary_detail_gene OWNER TO deapp;

--
-- Name: de_xtrial_child_map; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_xtrial_child_map (
    concept_cd character varying(50) NOT NULL,
    parent_cd bigint NOT NULL,
    manually_mapped bigint,
    study_id character varying(50)
);


ALTER TABLE deapp.de_xtrial_child_map OWNER TO deapp;

--
-- Name: de_xtrial_parent_names; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.de_xtrial_parent_names (
    parent_cd bigint NOT NULL,
    across_path character varying(500),
    manually_created bigint
);


ALTER TABLE deapp.de_xtrial_parent_names OWNER TO deapp;

--
-- Name: deapp_annotation; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.deapp_annotation (
    annotation_type character varying(50),
    annotation_value character varying(100),
    gene_id bigint,
    gene_symbol character varying(200)
);


ALTER TABLE deapp.deapp_annotation OWNER TO deapp;

--
-- Name: haploview_data; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.haploview_data (
    i2b2_id bigint,
    jnj_id character varying(30),
    father_id integer,
    mother_id integer,
    sex smallint,
    affection_status smallint,
    chromosome character varying(10),
    gene character varying(50),
    release smallint,
    release_date timestamp without time zone,
    trial_name character varying(50),
    snp_data text
);


ALTER TABLE deapp.haploview_data OWNER TO deapp;

--
-- Name: kegg; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.kegg (
    pathway character varying(20),
    gene_id character varying(20),
    gene_symbol character varying(20)
);


ALTER TABLE deapp.kegg OWNER TO deapp;

--
-- Name: kegg_data; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.kegg_data (
    pathway character varying(100),
    gene_id character varying(20),
    gene character varying(200)
);


ALTER TABLE deapp.kegg_data OWNER TO deapp;

--
-- Name: kegg_def; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.kegg_def (
    pathway character varying(100),
    descr character varying(500)
);


ALTER TABLE deapp.kegg_def OWNER TO deapp;

--
-- Name: metabolite_sub_pth_id; Type: SEQUENCE; Schema: deapp; Owner: deapp
--

CREATE SEQUENCE deapp.metabolite_sub_pth_id
    START WITH 1095
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE deapp.metabolite_sub_pth_id OWNER TO deapp;

--
-- Name: metabolite_sup_pth_id; Type: SEQUENCE; Schema: deapp; Owner: deapp
--

CREATE SEQUENCE deapp.metabolite_sup_pth_id
    START WITH 152
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE deapp.metabolite_sup_pth_id OWNER TO deapp;

--
-- Name: metabolomics_annot_id; Type: SEQUENCE; Schema: deapp; Owner: deapp
--

CREATE SEQUENCE deapp.metabolomics_annot_id
    START WITH 4479
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE deapp.metabolomics_annot_id OWNER TO deapp;

--
-- Name: rbm_annotation_id; Type: SEQUENCE; Schema: deapp; Owner: deapp
--

CREATE SEQUENCE deapp.rbm_annotation_id
    START WITH 105393
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE deapp.rbm_annotation_id OWNER TO deapp;

--
-- Name: ricerca; Type: TABLE; Schema: deapp; Owner: deapp; Tablespace: transmart
--

CREATE TABLE deapp.ricerca (
    subject_id character varying(20),
    sample_id character varying(100)
);


ALTER TABLE deapp.ricerca OWNER TO deapp;

--
-- Name: seq_assay_id; Type: SEQUENCE; Schema: deapp; Owner: deapp
--

CREATE SEQUENCE deapp.seq_assay_id
    START WITH 45741
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 20;


ALTER TABLE deapp.seq_assay_id OWNER TO deapp;

--
-- Name: seq_data_id; Type: SEQUENCE; Schema: deapp; Owner: deapp
--

CREATE SEQUENCE deapp.seq_data_id
    START WITH 11594011
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 20;


ALTER TABLE deapp.seq_data_id OWNER TO deapp;

--
-- Name: seq_de_mrna_annotation_id; Type: SEQUENCE; Schema: deapp; Owner: deapp
--

CREATE SEQUENCE deapp.seq_de_mrna_annotation_id
    START WITH 263161
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE deapp.seq_de_mrna_annotation_id OWNER TO deapp;

--
-- Name: seq_metabolomics_partition_id; Type: SEQUENCE; Schema: deapp; Owner: deapp
--

CREATE SEQUENCE deapp.seq_metabolomics_partition_id
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE deapp.seq_metabolomics_partition_id OWNER TO deapp;

--
-- Name: seq_mrna_partition_id; Type: SEQUENCE; Schema: deapp; Owner: deapp
--

CREATE SEQUENCE deapp.seq_mrna_partition_id
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE deapp.seq_mrna_partition_id OWNER TO deapp;

--
-- Name: seq_proteomics_partition_id; Type: SEQUENCE; Schema: deapp; Owner: deapp
--

CREATE SEQUENCE deapp.seq_proteomics_partition_id
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE deapp.seq_proteomics_partition_id OWNER TO deapp;

--
-- Name: seq_rbm_partition_id; Type: SEQUENCE; Schema: deapp; Owner: deapp
--

CREATE SEQUENCE deapp.seq_rbm_partition_id
    START WITH 29
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE deapp.seq_rbm_partition_id OWNER TO deapp;

--
-- Name: seq_rna_partition_id; Type: SEQUENCE; Schema: deapp; Owner: deapp
--

CREATE SEQUENCE deapp.seq_rna_partition_id
    START WITH 107
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE deapp.seq_rna_partition_id OWNER TO deapp;

--
-- Name: tt; Type: SEQUENCE; Schema: deapp; Owner: deapp
--

CREATE SEQUENCE deapp.tt
    START WITH 241
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE deapp.tt OWNER TO deapp;

--
-- Name: fm_file; Type: TABLE; Schema: fmapp; Owner: fmapp; Tablespace: transmart
--

CREATE TABLE fmapp.fm_file (
    file_id bigint NOT NULL,
    display_name character varying(1000) NOT NULL,
    original_name character varying(1000) NOT NULL,
    file_version numeric,
    file_type character varying(100),
    file_size numeric,
    filestore_location character varying(1000),
    filestore_name character varying(1000),
    link_url character varying(1000),
    active_ind boolean NOT NULL,
    create_date timestamp without time zone NOT NULL,
    update_date timestamp without time zone NOT NULL
);


ALTER TABLE fmapp.fm_file OWNER TO fmapp;

--
-- Name: fm_folder_file_association; Type: TABLE; Schema: fmapp; Owner: fmapp; Tablespace: transmart
--

CREATE TABLE fmapp.fm_folder_file_association (
    folder_id bigint NOT NULL,
    file_id bigint NOT NULL
);


ALTER TABLE fmapp.fm_folder_file_association OWNER TO fmapp;

--
-- Name: seq_fm_id; Type: SEQUENCE; Schema: fmapp; Owner: fmapp
--

CREATE SEQUENCE fmapp.seq_fm_id
    START WITH 1992447
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE fmapp.seq_fm_id OWNER TO fmapp;

--
-- Name: hibernate_id; Type: SEQUENCE; Schema: galaxy; Owner: galaxy
--

CREATE SEQUENCE galaxy.hibernate_id
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE galaxy.hibernate_id OWNER TO galaxy;

--
-- Name: status_of_export_job; Type: TABLE; Schema: galaxy; Owner: galaxy; Tablespace: transmart
--

CREATE TABLE galaxy.status_of_export_job (
    job_status character varying(200),
    last_export_name character varying(200),
    last_export_time date,
    job_name_id character varying(200),
    id bigint NOT NULL
);


ALTER TABLE galaxy.status_of_export_job OWNER TO galaxy;

--
-- Name: status_of_export_job_seq; Type: SEQUENCE; Schema: galaxy; Owner: galaxy
--

CREATE SEQUENCE galaxy.status_of_export_job_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE galaxy.status_of_export_job_seq OWNER TO galaxy;

--
-- Name: users_details_for_export_gal; Type: TABLE; Schema: galaxy; Owner: galaxy; Tablespace: transmart
--

CREATE TABLE galaxy.users_details_for_export_gal (
    id bigint NOT NULL,
    galaxy_key character varying(100) NOT NULL,
    mail_address character varying(200) NOT NULL,
    username character varying(200) NOT NULL
);


ALTER TABLE galaxy.users_details_for_export_gal OWNER TO galaxy;

--
-- Name: archive_observation_fact; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.archive_observation_fact (
    encounter_num numeric(38,0),
    patient_num numeric(38,0),
    concept_cd character varying(50),
    provider_id character varying(50),
    start_date timestamp without time zone,
    modifier_cd character varying(100),
    instance_num numeric(18,0),
    valtype_cd character varying(50),
    tval_char character varying(255),
    nval_num numeric(18,5),
    valueflag_cd character varying(50),
    quantity_num numeric(18,5),
    units_cd character varying(50),
    end_date timestamp without time zone,
    location_cd character varying(50),
    observation_blob text,
    confidence_num numeric(18,5),
    update_date timestamp without time zone,
    download_date timestamp without time zone,
    import_date timestamp without time zone,
    sourcesystem_cd character varying(50),
    upload_id numeric(38,0),
    archive_upload_id numeric(22,0)
);


ALTER TABLE i2b2demodata.archive_observation_fact OWNER TO i2b2demodata;

--
-- Name: async_job; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.async_job (
    id integer NOT NULL,
    job_name character varying(200),
    job_status character varying(200),
    run_time character varying(200),
    job_status_time timestamp(6) without time zone,
    last_run_on timestamp(6) without time zone,
    viewer_url character varying(4000),
    alt_viewer_url character varying(4000),
    job_results text,
    job_inputs_json text,
    job_type character varying(20)
);


ALTER TABLE i2b2demodata.async_job OWNER TO i2b2demodata;

--
-- Name: async_job_seq; Type: SEQUENCE; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE SEQUENCE i2b2demodata.async_job_seq
    START WITH 0
    INCREMENT BY 1
    MINVALUE 0
    NO MAXVALUE
    CACHE 1;


ALTER TABLE i2b2demodata.async_job_seq OWNER TO i2b2demodata;

--
-- Name: cd; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.cd (
    concept_path character varying(500),
    parent_concept_path character varying(500),
    patient_count bigint
);


ALTER TABLE i2b2demodata.cd OWNER TO i2b2demodata;

--
-- Name: code_lookup; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.code_lookup (
    table_cd character varying(100) NOT NULL,
    column_cd character varying(100) NOT NULL,
    code_cd character varying(50) NOT NULL,
    name_char character varying(650),
    lookup_blob text,
    upload_date timestamp without time zone,
    update_date timestamp without time zone,
    download_date timestamp without time zone,
    import_date timestamp without time zone,
    sourcesystem_cd character varying(50),
    upload_id numeric(38,0)
);


ALTER TABLE i2b2demodata.code_lookup OWNER TO i2b2demodata;

--
-- Name: concept_counts; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.concept_counts (
    concept_path character varying(500),
    parent_concept_path character varying(500),
    patient_count bigint
);


ALTER TABLE i2b2demodata.concept_counts OWNER TO i2b2demodata;

--
-- Name: concept_dimension; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.concept_dimension (
    concept_cd character varying(50) NOT NULL,
    concept_path character varying(700) NOT NULL,
    name_char character varying(2000),
    concept_blob text,
    update_date timestamp without time zone,
    download_date timestamp without time zone,
    import_date timestamp without time zone,
    sourcesystem_cd character varying(50),
    upload_id bigint,
    table_name character varying(255)
);


ALTER TABLE i2b2demodata.concept_dimension OWNER TO i2b2demodata;

--
-- Name: concept_id; Type: SEQUENCE; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE SEQUENCE i2b2demodata.concept_id
    START WITH 1341004
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE i2b2demodata.concept_id OWNER TO i2b2demodata;

--
-- Name: counter; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.counter (
    concept_cd character varying(50) NOT NULL,
    counts bigint
);


ALTER TABLE i2b2demodata.counter OWNER TO i2b2demodata;

--
-- Name: crc_db_lookup; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.crc_db_lookup (
    c_domain_id character varying(255),
    c_project_path character varying(255),
    c_owner_id character varying(255),
    c_db_fullschema character varying(255),
    c_db_datasource character varying(255),
    c_db_servertype character varying(255),
    c_db_nicename character varying(255),
    c_db_tooltip character varying(255),
    c_comment text,
    c_entry_date timestamp without time zone,
    c_change_date timestamp without time zone,
    c_status_cd character(1)
);


ALTER TABLE i2b2demodata.crc_db_lookup OWNER TO i2b2demodata;

--
-- Name: datamart_report; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.datamart_report (
    total_patient numeric(38,0),
    total_observationfact numeric(38,0),
    total_event numeric(38,0),
    report_date timestamp without time zone
);


ALTER TABLE i2b2demodata.datamart_report OWNER TO i2b2demodata;

--
-- Name: dimloader; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.dimloader (
    c_hlevel bigint,
    c_fullname character varying(900),
    c_name character varying(2000),
    c_synonym_cd character(1),
    c_visualattributes character(3),
    c_totalnum bigint,
    c_basecode character varying(50),
    c_metadataxml text,
    c_facttablecolumn character varying(50),
    c_tablename character varying(50),
    c_columnname character varying(50),
    c_columndatatype character varying(50),
    c_operator character varying(10),
    c_dimcode character varying(900),
    c_comment text,
    c_tooltip character varying(900),
    update_date timestamp without time zone,
    download_date timestamp without time zone,
    import_date timestamp without time zone,
    sourcesystem_cd character varying(50),
    valuetype_cd character varying(50)
);


ALTER TABLE i2b2demodata.dimloader OWNER TO i2b2demodata;

--
-- Name: encounter_mapping; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.encounter_mapping (
    encounter_ide character varying(200) NOT NULL,
    encounter_ide_source character varying(50) NOT NULL,
    encounter_num numeric(38,0) NOT NULL,
    patient_ide character varying(200),
    patient_ide_source character varying(50),
    encounter_ide_status character varying(50),
    upload_date timestamp without time zone,
    update_date timestamp without time zone,
    download_date timestamp without time zone,
    import_date timestamp without time zone,
    sourcesystem_cd character varying(50),
    upload_id numeric(38,0)
);


ALTER TABLE i2b2demodata.encounter_mapping OWNER TO i2b2demodata;

--
-- Name: hilosequences; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.hilosequences (
    sequencename character varying(50) NOT NULL,
    highvalues bigint NOT NULL
);


ALTER TABLE i2b2demodata.hilosequences OWNER TO i2b2demodata;

--
-- Name: jms_messages; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.jms_messages (
    messageid bigint NOT NULL,
    destination character varying(255) NOT NULL,
    txid bigint,
    txop character(1),
    messageblob text
);


ALTER TABLE i2b2demodata.jms_messages OWNER TO i2b2demodata;

--
-- Name: jms_roles; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.jms_roles (
    roleid character varying(32) NOT NULL,
    userid character varying(32) NOT NULL
);


ALTER TABLE i2b2demodata.jms_roles OWNER TO i2b2demodata;

--
-- Name: jms_subscriptions; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.jms_subscriptions (
    clientid character varying(128) NOT NULL,
    subname character varying(128) NOT NULL,
    topic character varying(255) NOT NULL,
    selector character varying(255)
);


ALTER TABLE i2b2demodata.jms_subscriptions OWNER TO i2b2demodata;

--
-- Name: jms_transactions; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.jms_transactions (
    txid bigint
);


ALTER TABLE i2b2demodata.jms_transactions OWNER TO i2b2demodata;

--
-- Name: jms_users; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.jms_users (
    userid character varying(32) NOT NULL,
    passwd character varying(32) NOT NULL,
    clientid character varying(128)
);


ALTER TABLE i2b2demodata.jms_users OWNER TO i2b2demodata;

--
-- Name: map; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.map (
    subject_id character varying(100),
    smaple_id character varying(100)
);


ALTER TABLE i2b2demodata.map OWNER TO i2b2demodata;

--
-- Name: modifier_dimension; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.modifier_dimension (
    modifier_path character varying(700) NOT NULL,
    modifier_cd character varying(50),
    name_char character varying(2000),
    modifier_blob text,
    update_date timestamp without time zone,
    download_date timestamp without time zone,
    import_date timestamp without time zone,
    sourcesystem_cd character varying(50),
    upload_id bigint,
    modifier_level bigint,
    modifier_node_type character varying(10)
);


ALTER TABLE i2b2demodata.modifier_dimension OWNER TO i2b2demodata;

--
-- Name: modifier_metadata; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.modifier_metadata (
    modifier_cd character varying(50),
    valtype_cd character varying(10),
    std_units character varying(50),
    visit_ind character(1)
);


ALTER TABLE i2b2demodata.modifier_metadata OWNER TO i2b2demodata;

--
-- Name: modifier_dimension_view; Type: VIEW; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE VIEW i2b2demodata.modifier_dimension_view AS
 SELECT md.modifier_path,
    md.modifier_cd,
    md.name_char,
    md.modifier_blob,
    md.update_date,
    md.download_date,
    md.import_date,
    md.sourcesystem_cd,
    md.upload_id,
    md.modifier_level,
    md.modifier_node_type,
    mm.valtype_cd,
    mm.std_units,
    mm.visit_ind
   FROM (i2b2demodata.modifier_dimension md
     LEFT JOIN i2b2demodata.modifier_metadata mm ON (((md.modifier_cd)::text = (mm.modifier_cd)::text)));


ALTER TABLE i2b2demodata.modifier_dimension_view OWNER TO i2b2demodata;

--
-- Name: news_updates; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.news_updates (
    newsid integer,
    ranbyuser character varying(200),
    rowsaffected integer,
    operation character varying(200),
    datasetname character varying(200),
    updatedate timestamp(6) without time zone,
    commentfield character varying(200)
);


ALTER TABLE i2b2demodata.news_updates OWNER TO i2b2demodata;

--
-- Name: observation_fact; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.observation_fact (
    encounter_num numeric(38,0),
    patient_num numeric(38,0) NOT NULL,
    concept_cd character varying(50) NOT NULL,
    provider_id character varying(50) NOT NULL,
    start_date timestamp without time zone,
    modifier_cd character varying(100) NOT NULL,
    instance_num numeric(18,0),
    valtype_cd character varying(50),
    tval_char character varying(255),
    nval_num numeric(18,5),
    valueflag_cd character varying(50),
    quantity_num numeric(18,5),
    units_cd character varying(50),
    end_date timestamp without time zone,
    location_cd character varying(50),
    observation_blob text,
    confidence_num numeric(18,5),
    update_date timestamp without time zone,
    download_date timestamp without time zone,
    import_date timestamp without time zone,
    sourcesystem_cd character varying(50),
    upload_id numeric(38,0),
    sample_cd character varying(200)
);


ALTER TABLE i2b2demodata.observation_fact OWNER TO i2b2demodata;

--
-- Name: patient_dimension; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.patient_dimension (
    patient_num numeric(38,0) NOT NULL,
    vital_status_cd character varying(50),
    birth_date timestamp without time zone,
    death_date timestamp without time zone,
    sex_cd character varying(50),
    age_in_years_num numeric(38,0),
    language_cd character varying(50),
    race_cd character varying(50),
    marital_status_cd character varying(50),
    religion_cd character varying(50),
    zip_cd character varying(50),
    statecityzip_path character varying(700),
    income_cd character varying(50),
    patient_blob text,
    update_date timestamp without time zone,
    download_date timestamp without time zone,
    import_date timestamp without time zone,
    sourcesystem_cd character varying(107),
    upload_id numeric(38,0)
);


ALTER TABLE i2b2demodata.patient_dimension OWNER TO i2b2demodata;

--
-- Name: patient_mapping; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.patient_mapping (
    patient_ide character varying(200) NOT NULL,
    patient_ide_source character varying(50) NOT NULL,
    patient_num numeric(38,0) NOT NULL,
    patient_ide_status character varying(50),
    upload_date timestamp without time zone,
    update_date timestamp without time zone,
    download_date timestamp without time zone,
    import_date timestamp without time zone,
    sourcesystem_cd character varying(50),
    upload_id numeric(38,0)
);


ALTER TABLE i2b2demodata.patient_mapping OWNER TO i2b2demodata;

--
-- Name: patient_trial; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.patient_trial (
    patient_num numeric,
    trial character varying(30),
    secure_obj_token character varying(50)
);


ALTER TABLE i2b2demodata.patient_trial OWNER TO i2b2demodata;

--
-- Name: protocol_id_seq; Type: SEQUENCE; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE SEQUENCE i2b2demodata.protocol_id_seq
    START WITH 215
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE i2b2demodata.protocol_id_seq OWNER TO i2b2demodata;

--
-- Name: provider_dimension; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.provider_dimension (
    provider_id character varying(50) NOT NULL,
    provider_path character varying(700) NOT NULL,
    name_char character varying(850),
    provider_blob text,
    update_date timestamp without time zone,
    download_date timestamp without time zone,
    import_date timestamp without time zone,
    sourcesystem_cd character varying(50),
    upload_id numeric(38,0)
);


ALTER TABLE i2b2demodata.provider_dimension OWNER TO i2b2demodata;

--
-- Name: qt_analysis_plugin; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.qt_analysis_plugin (
    plugin_id numeric(10,0) NOT NULL,
    plugin_name character varying(2000),
    description character varying(2000),
    version_cd character varying(50),
    parameter_info text,
    parameter_info_xsd text,
    command_line text,
    working_folder text,
    commandoption_cd text,
    plugin_icon text,
    status_cd character varying(50),
    user_id character varying(50),
    group_id character varying(50),
    create_date timestamp without time zone,
    update_date timestamp without time zone
);


ALTER TABLE i2b2demodata.qt_analysis_plugin OWNER TO i2b2demodata;

--
-- Name: qt_analysis_plugin_result_type; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.qt_analysis_plugin_result_type (
    plugin_id numeric(10,0) NOT NULL,
    result_type_id numeric(10,0) NOT NULL
);


ALTER TABLE i2b2demodata.qt_analysis_plugin_result_type OWNER TO i2b2demodata;

--
-- Name: qt_breakdown_path; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.qt_breakdown_path (
    name character varying(100),
    value character varying(2000),
    create_date timestamp without time zone,
    update_date timestamp without time zone,
    user_id character varying(50)
);


ALTER TABLE i2b2demodata.qt_breakdown_path OWNER TO i2b2demodata;

--
-- Name: qt_sq_qper_pecid; Type: SEQUENCE; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE SEQUENCE i2b2demodata.qt_sq_qper_pecid
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE i2b2demodata.qt_sq_qper_pecid OWNER TO i2b2demodata;

--
-- Name: qt_patient_enc_collection; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.qt_patient_enc_collection (
    patient_enc_coll_id numeric(10,0) DEFAULT nextval('i2b2demodata.qt_sq_qper_pecid'::regclass) NOT NULL,
    result_instance_id numeric(5,0),
    set_index numeric(10,0),
    patient_num numeric(10,0),
    encounter_num numeric(10,0)
);


ALTER TABLE i2b2demodata.qt_patient_enc_collection OWNER TO i2b2demodata;

--
-- Name: qt_patient_sample_collection; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.qt_patient_sample_collection (
    sample_id bigint,
    patient_id bigint,
    result_instance_id bigint
);


ALTER TABLE i2b2demodata.qt_patient_sample_collection OWNER TO i2b2demodata;

--
-- Name: qt_sq_qpr_pcid; Type: SEQUENCE; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE SEQUENCE i2b2demodata.qt_sq_qpr_pcid
    START WITH 4430157
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE i2b2demodata.qt_sq_qpr_pcid OWNER TO i2b2demodata;

--
-- Name: qt_patient_set_collection; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.qt_patient_set_collection (
    patient_set_coll_id numeric(10,0) DEFAULT nextval('i2b2demodata.qt_sq_qpr_pcid'::regclass) NOT NULL,
    result_instance_id numeric(5,0),
    set_index numeric(10,0),
    patient_num numeric(10,0)
);


ALTER TABLE i2b2demodata.qt_patient_set_collection OWNER TO i2b2demodata;

--
-- Name: qt_sq_pqm_qmid; Type: SEQUENCE; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE SEQUENCE i2b2demodata.qt_sq_pqm_qmid
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE i2b2demodata.qt_sq_pqm_qmid OWNER TO i2b2demodata;

--
-- Name: qt_pdo_query_master; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.qt_pdo_query_master (
    query_master_id numeric(5,0) DEFAULT nextval('i2b2demodata.qt_sq_pqm_qmid'::regclass) NOT NULL,
    user_id character varying(50) NOT NULL,
    group_id character varying(50) NOT NULL,
    create_date timestamp without time zone NOT NULL,
    request_xml text,
    i2b2_request_xml text
);


ALTER TABLE i2b2demodata.qt_pdo_query_master OWNER TO i2b2demodata;

--
-- Name: qt_privilege; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.qt_privilege (
    protection_label_cd character varying(1500),
    dataprot_cd character varying(1000),
    hivemgmt_cd character varying(1000),
    plugin_id numeric(10,0)
);


ALTER TABLE i2b2demodata.qt_privilege OWNER TO i2b2demodata;

--
-- Name: qt_sq_qi_qiid; Type: SEQUENCE; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE SEQUENCE i2b2demodata.qt_sq_qi_qiid
    START WITH 28734
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE i2b2demodata.qt_sq_qi_qiid OWNER TO i2b2demodata;

--
-- Name: qt_query_instance; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.qt_query_instance (
    query_instance_id numeric(5,0) DEFAULT nextval('i2b2demodata.qt_sq_qi_qiid'::regclass) NOT NULL,
    query_master_id numeric(5,0),
    user_id character varying(50) NOT NULL,
    group_id character varying(50) NOT NULL,
    batch_mode character varying(50),
    start_date timestamp without time zone NOT NULL,
    end_date timestamp without time zone,
    delete_flag character varying(3),
    status_type_id numeric(5,0),
    message text
);


ALTER TABLE i2b2demodata.qt_query_instance OWNER TO i2b2demodata;

--
-- Name: qt_sq_qm_qmid; Type: SEQUENCE; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE SEQUENCE i2b2demodata.qt_sq_qm_qmid
    START WITH 28756
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE i2b2demodata.qt_sq_qm_qmid OWNER TO i2b2demodata;

--
-- Name: qt_query_master; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.qt_query_master (
    query_master_id numeric(5,0) DEFAULT nextval('i2b2demodata.qt_sq_qm_qmid'::regclass) NOT NULL,
    name character varying(250) NOT NULL,
    user_id character varying(50) NOT NULL,
    group_id character varying(50) NOT NULL,
    master_type_cd character varying(2000),
    plugin_id numeric(10,0),
    create_date timestamp without time zone NOT NULL,
    delete_date timestamp without time zone,
    delete_flag character varying(3),
    generated_sql text,
    request_xml text,
    i2b2_request_xml text
);


ALTER TABLE i2b2demodata.qt_query_master OWNER TO i2b2demodata;

--
-- Name: qt_sq_qri_qriid; Type: SEQUENCE; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE SEQUENCE i2b2demodata.qt_sq_qri_qriid
    START WITH 28714
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE i2b2demodata.qt_sq_qri_qriid OWNER TO i2b2demodata;

--
-- Name: qt_query_result_instance; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.qt_query_result_instance (
    result_instance_id numeric(5,0) DEFAULT nextval('i2b2demodata.qt_sq_qri_qriid'::regclass) NOT NULL,
    query_instance_id numeric(5,0),
    result_type_id numeric(3,0) NOT NULL,
    set_size numeric(10,0),
    start_date timestamp without time zone NOT NULL,
    end_date timestamp without time zone,
    delete_flag character varying(3),
    status_type_id numeric(3,0) NOT NULL,
    message text,
    description character varying(200),
    real_set_size numeric(10,0),
    obfusc_method character varying(500)
);


ALTER TABLE i2b2demodata.qt_query_result_instance OWNER TO i2b2demodata;

--
-- Name: qt_sq_qr_qrid; Type: SEQUENCE; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE SEQUENCE i2b2demodata.qt_sq_qr_qrid
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE i2b2demodata.qt_sq_qr_qrid OWNER TO i2b2demodata;

--
-- Name: qt_query_result_type; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.qt_query_result_type (
    result_type_id numeric(3,0) DEFAULT nextval('i2b2demodata.qt_sq_qr_qrid'::regclass) NOT NULL,
    name character varying(100),
    description character varying(200),
    display_type_id character varying(500),
    visual_attribute_type_id character varying(3)
);


ALTER TABLE i2b2demodata.qt_query_result_type OWNER TO i2b2demodata;

--
-- Name: qt_sq_qs_qsid; Type: SEQUENCE; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE SEQUENCE i2b2demodata.qt_sq_qs_qsid
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE i2b2demodata.qt_sq_qs_qsid OWNER TO i2b2demodata;

--
-- Name: qt_query_status_type; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.qt_query_status_type (
    status_type_id numeric(3,0) DEFAULT nextval('i2b2demodata.qt_sq_qs_qsid'::regclass) NOT NULL,
    name character varying(100),
    description character varying(200)
);


ALTER TABLE i2b2demodata.qt_query_status_type OWNER TO i2b2demodata;

--
-- Name: qt_sq_qxr_xrid; Type: SEQUENCE; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE SEQUENCE i2b2demodata.qt_sq_qxr_xrid
    START WITH 655
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE i2b2demodata.qt_sq_qxr_xrid OWNER TO i2b2demodata;

--
-- Name: qt_xml_result; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.qt_xml_result (
    xml_result_id numeric(5,0) DEFAULT nextval('i2b2demodata.qt_sq_qxr_xrid'::regclass) NOT NULL,
    result_instance_id numeric(5,0),
    xml_value character varying(4000)
);


ALTER TABLE i2b2demodata.qt_xml_result OWNER TO i2b2demodata;

--
-- Name: ricerca; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.ricerca (
    concept_cd character varying(50) NOT NULL,
    concept_path character varying(700) NOT NULL
);


ALTER TABLE i2b2demodata.ricerca OWNER TO i2b2demodata;

--
-- Name: sample_categories; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.sample_categories (
    trial_cd character varying(200),
    sample_id character varying(250),
    trial_name character varying(100),
    sample_cd character varying(200),
    field1 character varying(200),
    field2 character varying(200),
    field3 character varying(200),
    field4 character varying(200),
    field5 character varying(200),
    field6 character varying(200),
    field7 character varying(200),
    field8 character varying(200),
    field9 character varying(200),
    field10 character varying(200),
    field11 character varying(200),
    field12 character varying(200),
    field13 character varying(200),
    field14 character varying(200),
    field15 character varying(200),
    tissue_type character varying(2000),
    data_types character varying(2000),
    disease character varying(2000),
    tissue_state character varying(2000),
    biobank character varying(3),
    source_organism character varying(255),
    treatment character varying(255),
    sample_treatment character varying(2000),
    subject_treatment character varying(2000),
    timepoint character varying(250)
);


ALTER TABLE i2b2demodata.sample_categories OWNER TO i2b2demodata;

--
-- Name: sample_dimension; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.sample_dimension (
    sample_cd character varying(200) NOT NULL
);


ALTER TABLE i2b2demodata.sample_dimension OWNER TO i2b2demodata;

--
-- Name: seq_encounter_num; Type: SEQUENCE; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE SEQUENCE i2b2demodata.seq_encounter_num
    START WITH 49814595
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE i2b2demodata.seq_encounter_num OWNER TO i2b2demodata;

--
-- Name: seq_patient_num; Type: SEQUENCE; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE SEQUENCE i2b2demodata.seq_patient_num
    START WITH 1000384597
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE i2b2demodata.seq_patient_num OWNER TO i2b2demodata;

--
-- Name: seq_subject_reference; Type: SEQUENCE; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE SEQUENCE i2b2demodata.seq_subject_reference
    START WITH 743
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE i2b2demodata.seq_subject_reference OWNER TO i2b2demodata;

--
-- Name: sq_up_patdim_patientnum; Type: SEQUENCE; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE SEQUENCE i2b2demodata.sq_up_patdim_patientnum
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE i2b2demodata.sq_up_patdim_patientnum OWNER TO i2b2demodata;

--
-- Name: set_type; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.set_type (
    id integer DEFAULT nextval('i2b2demodata.sq_up_patdim_patientnum'::regclass) NOT NULL,
    name character varying(500),
    create_date timestamp without time zone
);


ALTER TABLE i2b2demodata.set_type OWNER TO i2b2demodata;

--
-- Name: set_upload_status; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.set_upload_status (
    upload_id numeric NOT NULL,
    set_type_id integer NOT NULL,
    source_cd character varying(50) NOT NULL,
    no_of_record numeric,
    loaded_record numeric,
    deleted_record numeric,
    load_date timestamp without time zone NOT NULL,
    end_date timestamp without time zone,
    load_status character varying(100),
    message text,
    input_file_name text,
    log_file_name text,
    transform_name character varying(500)
);


ALTER TABLE i2b2demodata.set_upload_status OWNER TO i2b2demodata;

--
-- Name: source_master; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.source_master (
    source_cd character varying(50) NOT NULL,
    description character varying(300),
    create_date timestamp without time zone
);


ALTER TABLE i2b2demodata.source_master OWNER TO i2b2demodata;

--
-- Name: sq_up_encdim_encounternum; Type: SEQUENCE; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE SEQUENCE i2b2demodata.sq_up_encdim_encounternum
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE i2b2demodata.sq_up_encdim_encounternum OWNER TO i2b2demodata;

--
-- Name: sq_uploadstatus_uploadid; Type: SEQUENCE; Schema: i2b2demodata; Owner: i2b2demodata
--

CREATE SEQUENCE i2b2demodata.sq_uploadstatus_uploadid
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE i2b2demodata.sq_uploadstatus_uploadid OWNER TO i2b2demodata;

--
-- Name: timers; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.timers (
    timerid character varying(80) NOT NULL,
    targetid character varying(250) NOT NULL,
    initialdate timestamp(6) without time zone NOT NULL,
    timerinterval bigint,
    instancepk text,
    info text
);


ALTER TABLE i2b2demodata.timers OWNER TO i2b2demodata;

--
-- Name: upload_status; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.upload_status (
    upload_id numeric(38,0) DEFAULT nextval('i2b2demodata.sq_uploadstatus_uploadid'::regclass) NOT NULL,
    upload_label character varying(500) NOT NULL,
    user_id character varying(100) NOT NULL,
    source_cd character varying(50) NOT NULL,
    no_of_record numeric,
    loaded_record numeric,
    deleted_record numeric,
    load_date timestamp without time zone NOT NULL,
    end_date timestamp without time zone,
    load_status character varying(100),
    message text,
    input_file_name text,
    log_file_name text,
    transform_name character varying(500)
);


ALTER TABLE i2b2demodata.upload_status OWNER TO i2b2demodata;

--
-- Name: visit_dimension; Type: TABLE; Schema: i2b2demodata; Owner: i2b2demodata; Tablespace: transmart
--

CREATE TABLE i2b2demodata.visit_dimension (
    encounter_num numeric(38,0) NOT NULL,
    patient_num numeric(38,0) NOT NULL,
    active_status_cd character varying(50),
    start_date timestamp without time zone,
    end_date timestamp without time zone,
    inout_cd character varying(50),
    location_cd character varying(50),
    location_path character varying(900),
    length_of_stay numeric(38,0),
    visit_blob text,
    update_date timestamp without time zone,
    download_date timestamp without time zone,
    import_date timestamp without time zone,
    sourcesystem_cd character varying(50),
    upload_id numeric(38,0)
);


ALTER TABLE i2b2demodata.visit_dimension OWNER TO i2b2demodata;

--
-- Name: custom_meta; Type: TABLE; Schema: i2b2metadata; Owner: i2b2metadata; Tablespace: transmart
--

CREATE TABLE i2b2metadata.custom_meta (
    c_hlevel bigint NOT NULL,
    c_fullname character varying(700) NOT NULL,
    c_name character varying(2000) NOT NULL,
    c_synonym_cd character(1) NOT NULL,
    c_visualattributes character(3) NOT NULL,
    c_totalnum bigint,
    c_basecode character varying(50),
    c_metadataxml text,
    c_facttablecolumn character varying(50) NOT NULL,
    c_tablename character varying(50) NOT NULL,
    c_columnname character varying(50) NOT NULL,
    c_columndatatype character varying(50) NOT NULL,
    c_operator character varying(10) NOT NULL,
    c_dimcode character varying(900) NOT NULL,
    c_comment text,
    c_tooltip character varying(900),
    update_date timestamp without time zone NOT NULL,
    download_date timestamp without time zone,
    import_date timestamp without time zone,
    sourcesystem_cd character varying(50),
    valuetype_cd character varying(50),
    m_applied_path character varying(700) NOT NULL,
    m_exclusion_cd character varying(25),
    c_path character varying(700),
    c_symbol character varying(50)
);


ALTER TABLE i2b2metadata.custom_meta OWNER TO i2b2metadata;

--
-- Name: i2b2; Type: TABLE; Schema: i2b2metadata; Owner: i2b2metadata; Tablespace: transmart
--

CREATE TABLE i2b2metadata.i2b2 (
    c_hlevel numeric(22,0) NOT NULL,
    c_fullname character varying(700) NOT NULL,
    c_name character varying(2000) NOT NULL,
    c_synonym_cd character(1) NOT NULL,
    c_visualattributes character(3) NOT NULL,
    c_totalnum numeric(22,0),
    c_basecode character varying(50),
    c_metadataxml text,
    c_facttablecolumn character varying(50) NOT NULL,
    c_tablename character varying(150) NOT NULL,
    c_columnname character varying(50) NOT NULL,
    c_columndatatype character varying(50) NOT NULL,
    c_operator character varying(10) NOT NULL,
    c_dimcode character varying(700) NOT NULL,
    c_comment text,
    c_tooltip character varying(900),
    m_applied_path character varying(700) NOT NULL,
    update_date timestamp without time zone NOT NULL,
    download_date timestamp without time zone,
    import_date timestamp without time zone,
    sourcesystem_cd character varying(50),
    valuetype_cd character varying(50),
    m_exclusion_cd character varying(25),
    c_path character varying(700),
    c_symbol character varying(50),
    i2b2_id bigint
);


ALTER TABLE i2b2metadata.i2b2 OWNER TO i2b2metadata;

--
-- Name: i2b2_id_seq; Type: SEQUENCE; Schema: i2b2metadata; Owner: i2b2metadata
--

CREATE SEQUENCE i2b2metadata.i2b2_id_seq
    START WITH 496244
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE i2b2metadata.i2b2_id_seq OWNER TO i2b2metadata;

--
-- Name: i2b2_secure; Type: TABLE; Schema: i2b2metadata; Owner: i2b2metadata; Tablespace: transmart
--

CREATE TABLE i2b2metadata.i2b2_secure (
    c_hlevel numeric(22,0),
    c_fullname character varying(900) NOT NULL,
    c_name character varying(2000),
    c_synonym_cd character(1),
    c_visualattributes character(3),
    c_totalnum numeric(22,0),
    c_basecode character varying(450),
    c_metadataxml text,
    c_facttablecolumn character varying(50),
    c_tablename character varying(150),
    c_columnname character varying(50),
    c_columndatatype character varying(50),
    c_operator character varying(10),
    c_dimcode character varying(900),
    c_comment text,
    c_tooltip character varying(900),
    m_applied_path character varying(700),
    update_date timestamp without time zone,
    download_date timestamp without time zone,
    import_date timestamp without time zone,
    sourcesystem_cd character varying(50),
    valuetype_cd character varying(50),
    m_exclusion_cd character varying(25),
    c_path character varying(900),
    c_symbol character varying(50),
    i2b2_id numeric(18,0),
    secure_obj_token character varying(50)
);


ALTER TABLE i2b2metadata.i2b2_secure OWNER TO i2b2metadata;

--
-- Name: i2b2_tags; Type: TABLE; Schema: i2b2metadata; Owner: i2b2metadata; Tablespace: transmart
--

CREATE TABLE i2b2metadata.i2b2_tags (
    tag_id integer NOT NULL,
    path character varying(400) NOT NULL,
    tag character varying(1000),
    tag_type character varying(400) NOT NULL,
    tags_idx integer NOT NULL
);


ALTER TABLE i2b2metadata.i2b2_tags OWNER TO i2b2metadata;

--
-- Name: i2b2_trial_nodes; Type: VIEW; Schema: i2b2metadata; Owner: i2b2metadata
--

CREATE VIEW i2b2metadata.i2b2_trial_nodes AS
 SELECT DISTINCT ON (i2b2.c_comment) i2b2.c_fullname,
    "substring"(i2b2.c_comment, 7) AS trial
   FROM i2b2metadata.i2b2
  WHERE (i2b2.c_comment IS NOT NULL)
  ORDER BY i2b2.c_comment, (char_length((i2b2.c_fullname)::text));


ALTER TABLE i2b2metadata.i2b2_trial_nodes OWNER TO i2b2metadata;

--
-- Name: ont_db_lookup; Type: TABLE; Schema: i2b2metadata; Owner: i2b2metadata; Tablespace: transmart
--

CREATE TABLE i2b2metadata.ont_db_lookup (
    c_domain_id character varying(255),
    c_project_path character varying(255),
    c_owner_id character varying(255),
    c_db_fullschema character varying(255),
    c_db_datasource character varying(255),
    c_db_servertype character varying(255),
    c_db_nicename character varying(255),
    c_db_tooltip character varying(255),
    c_comment text,
    c_entry_date timestamp without time zone,
    c_change_date timestamp without time zone,
    c_status_cd character(1)
);


ALTER TABLE i2b2metadata.ont_db_lookup OWNER TO i2b2metadata;

--
-- Name: ont_sq_ps_prid; Type: SEQUENCE; Schema: i2b2metadata; Owner: i2b2metadata
--

CREATE SEQUENCE i2b2metadata.ont_sq_ps_prid
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE i2b2metadata.ont_sq_ps_prid OWNER TO i2b2metadata;

--
-- Name: ont_process_status; Type: TABLE; Schema: i2b2metadata; Owner: i2b2metadata; Tablespace: transmart
--

CREATE TABLE i2b2metadata.ont_process_status (
    process_id numeric(5,0) DEFAULT nextval('i2b2metadata.ont_sq_ps_prid'::regclass) NOT NULL,
    process_type_cd character varying(50),
    start_date timestamp without time zone,
    end_date timestamp without time zone,
    process_step_cd character varying(50),
    process_status_cd character varying(50),
    crc_upload_id character varying(5),
    status_cd character varying(50),
    message character varying(2000),
    entry_date timestamp without time zone,
    change_date timestamp without time zone,
    changedby_char character(50)
);


ALTER TABLE i2b2metadata.ont_process_status OWNER TO i2b2metadata;

--
-- Name: schemes; Type: TABLE; Schema: i2b2metadata; Owner: i2b2metadata; Tablespace: transmart
--

CREATE TABLE i2b2metadata.schemes (
    c_key character varying(50) NOT NULL,
    c_name character varying(50) NOT NULL,
    c_description character varying(100)
);


ALTER TABLE i2b2metadata.schemes OWNER TO i2b2metadata;

--
-- Name: seq_concept_code; Type: SEQUENCE; Schema: i2b2metadata; Owner: i2b2metadata
--

CREATE SEQUENCE i2b2metadata.seq_concept_code
    START WITH 1000
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE i2b2metadata.seq_concept_code OWNER TO i2b2metadata;

--
-- Name: seq_i2b2_data_id; Type: SEQUENCE; Schema: i2b2metadata; Owner: i2b2metadata
--

CREATE SEQUENCE i2b2metadata.seq_i2b2_data_id
    START WITH 1789
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE i2b2metadata.seq_i2b2_data_id OWNER TO i2b2metadata;

--
-- Name: sq_i2b2_id; Type: SEQUENCE; Schema: i2b2metadata; Owner: i2b2metadata
--

CREATE SEQUENCE i2b2metadata.sq_i2b2_id
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE i2b2metadata.sq_i2b2_id OWNER TO i2b2metadata;

--
-- Name: sq_i2b2_tag_id; Type: SEQUENCE; Schema: i2b2metadata; Owner: i2b2metadata
--

CREATE SEQUENCE i2b2metadata.sq_i2b2_tag_id
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE i2b2metadata.sq_i2b2_tag_id OWNER TO i2b2metadata;

--
-- Name: table_access; Type: TABLE; Schema: i2b2metadata; Owner: i2b2metadata; Tablespace: transmart
--

CREATE TABLE i2b2metadata.table_access (
    c_table_cd character varying(65) NOT NULL,
    c_table_name character varying(50) NOT NULL,
    c_protected_access character(1),
    c_hlevel numeric(22,0) NOT NULL,
    c_fullname character varying(700) NOT NULL,
    c_name character varying(2000) NOT NULL,
    c_synonym_cd character(1) NOT NULL,
    c_visualattributes character(3) NOT NULL,
    c_totalnum numeric(22,0),
    c_basecode character varying(50),
    c_metadataxml text,
    c_facttablecolumn character varying(50) NOT NULL,
    c_dimtablename character varying(50) NOT NULL,
    c_columnname character varying(50) NOT NULL,
    c_columndatatype character varying(50) NOT NULL,
    c_operator character varying(10) NOT NULL,
    c_dimcode character varying(700) NOT NULL,
    c_comment text,
    c_tooltip character varying(900),
    c_entry_date timestamp without time zone,
    c_change_date timestamp without time zone,
    c_status_cd character(1),
    valuetype_cd character varying(50)
);


ALTER TABLE i2b2metadata.table_access OWNER TO i2b2metadata;

--
-- Name: tmp_secure_token; Type: TABLE; Schema: i2b2metadata; Owner: i2b2metadata; Tablespace: transmart
--

CREATE TABLE i2b2metadata.tmp_secure_token (
    sourcesystem_cd character varying(50),
    secure_obj_token character varying(50)
);


ALTER TABLE i2b2metadata.tmp_secure_token OWNER TO i2b2metadata;


--
-- Name: biomart_write_tables; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.biomart_write_tables (
    nschema text,
    ntable text
);


ALTER TABLE public.biomart_write_tables OWNER TO postgres;

--
-- Name: schemas_tables_funcs; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW public.schemas_tables_funcs AS
( SELECT quote_ident((c.relname)::text) AS name,
    c.relkind AS kind,
    r.rolname AS owner,
    c.relacl AS acl,
    n.nspname,
    (EXISTS ( SELECT 1
           FROM pg_depend
          WHERE ((pg_depend.refobjsubid <> 0) AND (pg_depend.deptype = 'a'::"char") AND (pg_depend.objid = c.oid))
         LIMIT 1)) AS change_owner_skip
   FROM ((pg_class c
     JOIN pg_namespace n ON ((c.relnamespace = n.oid)))
     JOIN pg_roles r ON ((c.relowner = r.oid)))
  WHERE (c.relkind = ANY (ARRAY['r'::"char", 'S'::"char", 'v'::"char"]))
  ORDER BY (c.relkind = 'S'::"char"))
UNION
 SELECT n.nspname AS name,
    's'::"char" AS kind,
    r.rolname AS owner,
    n.nspacl AS acl,
    n.nspname,
    false AS change_owner_skip
   FROM (pg_namespace n
     JOIN pg_roles r ON ((n.nspowner = r.oid)))
UNION
 SELECT quote_ident((pg_tablespace.spcname)::text) AS name,
    'T'::"char" AS kind,
    NULL::name AS owner,
    pg_tablespace.spcacl AS acl,
    NULL::name AS nspname,
    false AS change_owner_skip
   FROM pg_tablespace
  WHERE (pg_tablespace.spcname !~~ 'pg\_%'::text)
UNION
 SELECT (((quote_ident((p.proname)::text) || '('::text) || array_to_string(( SELECT public.array_accum(y.typname) AS array_accum
           FROM ( SELECT t.typname
                   FROM (( SELECT z.id,
                            p.proargtypes[z.id] AS proargtypes
                           FROM generate_subscripts(p.proargtypes, 1) z(id)) a(id, oid)
                     JOIN pg_type t ON ((t.oid = a.oid)))
                  GROUP BY a.id, t.typname
                  ORDER BY a.id) y), ', '::text)) || ')'::text) AS name,
    (
        CASE p.proisagg
            WHEN false THEN 'f'::text
            ELSE 'a'::text
        END)::"char" AS kind,
    r.rolname AS owner,
    p.proacl AS acl,
    n.nspname,
    false AS change_owner_skip
   FROM ((pg_proc p
     JOIN pg_namespace n ON ((p.pronamespace = n.oid)))
     JOIN pg_roles r ON ((p.proowner = r.oid)));


ALTER TABLE public.schemas_tables_funcs OWNER TO postgres;

--
-- Name: ts_default_permissions; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.ts_default_permissions (
    nschema text,
    nuser text,
    ntype text,
    nperm text
);


ALTER TABLE public.ts_default_permissions OWNER TO postgres;

--
-- Name: ts_misc_permissions; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.ts_misc_permissions (
    nschema text,
    nname text,
    ntype text,
    nuser text,
    nperm text
);


ALTER TABLE public.ts_misc_permissions OWNER TO postgres;

--
-- Name: hibernate_sequence; Type: SEQUENCE; Schema: searchapp; Owner: searchapp
--

CREATE SEQUENCE searchapp.hibernate_sequence
    START WITH 100442
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE searchapp.hibernate_sequence OWNER TO searchapp;


--
-- Name: search_gene_signature; Type: TABLE; Schema: searchapp; Owner: searchapp; Tablespace: transmart
--

CREATE TABLE searchapp.search_gene_signature (
    search_gene_signature_id bigint NOT NULL,
    name character varying(100) NOT NULL,
    description character varying(1000),
    unique_id character varying(50),
    create_date timestamp without time zone NOT NULL,
    created_by_auth_user_id bigint NOT NULL,
    last_modified_date timestamp without time zone,
    modified_by_auth_user_id bigint,
    version_number character varying(50),
    public_flag boolean DEFAULT false,
    deleted_flag boolean DEFAULT false,
    parent_gene_signature_id bigint,
    source_concept_id bigint,
    source_other character varying(255),
    owner_concept_id bigint,
    stimulus_description character varying(1000),
    stimulus_dosing character varying(255),
    treatment_description character varying(1000),
    treatment_dosing character varying(255),
    treatment_bio_compound_id bigint,
    treatment_protocol_number character varying(50),
    pmid_list character varying(255),
    species_concept_id bigint NOT NULL,
    species_mouse_src_concept_id bigint,
    species_mouse_detail character varying(255),
    tissue_type_concept_id bigint,
    experiment_type_concept_id bigint,
    experiment_type_in_vivo_descr character varying(255),
    experiment_type_atcc_ref character varying(255),
    analytic_cat_concept_id bigint,
    analytic_cat_other character varying(255),
    bio_assay_platform_id bigint NOT NULL,
    analyst_name character varying(100),
    norm_method_concept_id bigint,
    norm_method_other character varying(255),
    analysis_method_concept_id bigint,
    analysis_method_other character varying(255),
    multiple_testing_correction boolean,
    p_value_cutoff_concept_id bigint NOT NULL,
    upload_file character varying(255) NOT NULL,
    search_gene_sig_file_schema_id bigint DEFAULT 1 NOT NULL,
    fold_chg_metric_concept_id bigint NOT NULL,
    experiment_type_cell_line_id bigint,
    qc_performed numeric(1,0),
    qc_date date,
    qc_info character varying(255),
    data_source character varying(255),
    custom_value1 character varying(255),
    custom_name1 character varying(255),
    custom_value2 character varying(255),
    custom_name2 character varying(255),
    custom_value3 character varying(255),
    custom_name3 character varying(255),
    custom_value4 character varying(255),
    custom_name4 character varying(255),
    custom_value5 character varying(255),
    custom_name5 character varying(255),
    version character varying(255)
);


ALTER TABLE searchapp.search_gene_signature OWNER TO searchapp;

--
-- Name: search_gene_signature_item; Type: TABLE; Schema: searchapp; Owner: searchapp; Tablespace: transmart
--

CREATE TABLE searchapp.search_gene_signature_item (
    search_gene_signature_id bigint NOT NULL,
    bio_marker_id bigint,
    fold_chg_metric bigint,
    bio_data_unique_id character varying(200),
    id bigint NOT NULL,
    bio_assay_feature_group_id bigint,
    probeset_id bigint
);


ALTER TABLE searchapp.search_gene_signature_item OWNER TO searchapp;

--
-- Name: search_keyword; Type: TABLE; Schema: searchapp; Owner: searchapp; Tablespace: transmart
--

CREATE TABLE searchapp.search_keyword (
    keyword character varying(400),
    bio_data_id bigint,
    unique_id character varying(500) NOT NULL,
    search_keyword_id bigint NOT NULL,
    data_category character varying(200) NOT NULL,
    source_code character varying(100),
    display_data_category character varying(200),
    owner_auth_user_id bigint
);


ALTER TABLE searchapp.search_keyword OWNER TO searchapp;

--
-- Name: listsig_genes; Type: VIEW; Schema: searchapp; Owner: searchapp
--

CREATE VIEW searchapp.listsig_genes AS
 SELECT k_gsi.search_keyword_id AS gene_keyword_id,
    k_gs.search_keyword_id AS list_keyword_id
   FROM searchapp.search_keyword k_gs,
    searchapp.search_gene_signature gs,
    searchapp.search_gene_signature_item gsi,
    searchapp.search_keyword k_gsi
  WHERE ((k_gs.bio_data_id = gs.search_gene_signature_id) AND (gs.search_gene_signature_id = gsi.search_gene_signature_id) AND (gsi.bio_marker_id = k_gsi.bio_data_id));


ALTER TABLE searchapp.listsig_genes OWNER TO searchapp;

--
-- Name: pathway_genes; Type: VIEW; Schema: searchapp; Owner: searchapp
--

CREATE VIEW searchapp.pathway_genes AS
 SELECT k_gene.search_keyword_id AS gene_keyword_id,
    k_pathway.search_keyword_id AS pathway_keyword_id,
    b.asso_bio_marker_id AS gene_biomarker_id
   FROM searchapp.search_keyword k_pathway,
    biomart.bio_marker_correl_mv b,
    searchapp.search_keyword k_gene
  WHERE ((b.correl_type = 'PATHWAY_GENE'::text) AND (b.bio_marker_id = k_pathway.bio_data_id) AND ((k_pathway.data_category)::text = 'PATHWAY'::text) AND (b.asso_bio_marker_id = k_gene.bio_data_id) AND ((k_gene.data_category)::text = 'GENE'::text));


ALTER TABLE searchapp.pathway_genes OWNER TO searchapp;

--
-- Name: plugin; Type: TABLE; Schema: searchapp; Owner: searchapp; Tablespace: transmart
--

CREATE TABLE searchapp.plugin (
    plugin_seq bigint NOT NULL,
    name character varying(200) NOT NULL,
    plugin_name character varying(90) NOT NULL,
    has_modules character(1) DEFAULT 'N'::bpchar NOT NULL,
    has_form character(1) DEFAULT 'N'::bpchar NOT NULL,
    default_link character varying(70) NOT NULL,
    form_link character varying(70),
    form_page character varying(100),
    active character(1)
);


ALTER TABLE searchapp.plugin OWNER TO searchapp;

--
-- Name: plugin_module; Type: TABLE; Schema: searchapp; Owner: searchapp; Tablespace: transmart
--

CREATE TABLE searchapp.plugin_module (
    module_seq bigint NOT NULL,
    plugin_seq bigint NOT NULL,
    name character varying(70) NOT NULL,
    params text NOT NULL,
    version character varying(10) DEFAULT 0.1 NOT NULL,
    active character(1) DEFAULT 'Y'::bpchar NOT NULL,
    has_form character(1) DEFAULT 'N'::bpchar NOT NULL,
    form_link character varying(90),
    form_page character varying(90),
    module_name character varying(50) NOT NULL,
    category character varying(50)
);


ALTER TABLE searchapp.plugin_module OWNER TO searchapp;

--
