prompt table;
REM This DDL was reverse engineered by
REM Perl module DDL::Oracle, Version 1.11
REM
REM at:   chevre
REM from: gramene, an Oracle Release 9.0.1.0.0 instance
REM
REM on:   Fri Sep 19 16:40:22 2003
REM
REM Generating CREATE TABLE statements for:
REM
REM	RICELITE1.CHROMOSOME
REM	RICELITE1.CHROMOSOME_STAT
REM	RICELITE1.GENE
REM	RICELITE1.GENE_XREF
REM	RICELITE1.LANDMARK_MARKER
REM	RICELITE1.MAP_DENSITY
REM	RICELITE1.PLAN_TABLE
REM	RICELITE1.REPEAT
REM	RICELITE1.REPEAT_TYPES
REM	RICELITE1.SNP
REM	RICELITE1.TRANSCRIPT

PROMPT CREATE TABLE chromosome

CREATE TABLE chromosome
(
    chromosome_id                   NUMBER   (10)                   
  , name                            VARCHAR2 (40)                   
  , length                          NUMBER   (10)                   
)
ORGANIZATION        HEAP
NOMONITORING
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE TABLE chromosome_stat

CREATE TABLE chromosome_stat
(
    chromosome_id                   NUMBER   (10)                   
  , type                            VARCHAR2 (40)                   
  , value                           NUMBER   (10)                   
)
ORGANIZATION        HEAP
NOMONITORING
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE TABLE gene

CREATE TABLE gene
(
    id                              NUMBER   (10)                   NOT NULL
  , db                              VARCHAR2 (40)                   NOT NULL
  , analysis                        VARCHAR2 (40)                   NOT NULL
  , type                            VARCHAR2 (40)                   NOT NULL
  , gene_id                         NUMBER   (10)                   NOT NULL
  , gene_name                       VARCHAR2 (40)                   DEFAULT 'unknown'  NOT NULL
  , gene_version                    NUMBER   (5)                    NOT NULL
  , chr_name                        VARCHAR2 (20)                   NOT NULL
  , chr_start                       NUMBER   (10)                   NOT NULL
  , chr_end                         NUMBER   (10)                   NOT NULL
  , chr_strand                      NUMBER   (3)                    NOT NULL
  , description                     VARCHAR2 (4000)                 
  , external_db                     VARCHAR2 (40)                   
  , external_name                   VARCHAR2 (40)                   
  , external_status                 VARCHAR2 (10)                   
)
ORGANIZATION        HEAP
NOMONITORING
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE TABLE gene_xref

CREATE TABLE gene_xref
(
    db                              VARCHAR2 (40)                   NOT NULL
  , analysis                        VARCHAR2 (40)                   NOT NULL
  , type                            VARCHAR2 (40)                   NOT NULL
  , gene_id                         NUMBER   (10)                   NOT NULL
  , gene_name                       VARCHAR2 (40)                   DEFAULT 'unknown'  NOT NULL
  , gene_version                    NUMBER   (5)                    NOT NULL
  , transcript_id                   NUMBER   (10)                   NOT NULL
  , transcript_name                 VARCHAR2 (40)                   NOT NULL
  , transcript_version              NUMBER   (5)                    NOT NULL
  , translation_id                  NUMBER   (10)                   NOT NULL
  , translation_name                VARCHAR2 (40)                   NOT NULL
  , translation_version             NUMBER   (5)                    NOT NULL
  , external_db                     VARCHAR2 (40)                   NOT NULL
  , external_name                   VARCHAR2 (40)                   NOT NULL
  , external_status                 VARCHAR2 (10)                   NOT NULL
)
ORGANIZATION        HEAP
NOMONITORING
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE TABLE landmark_marker

CREATE TABLE landmark_marker
(
    marker                          VARCHAR2 (40)                   
  , name                            VARCHAR2 (40)                   
  , chr_start                       NUMBER   (17)                   DEFAULT 0  NOT NULL
  , chr_end                         NUMBER   (17)                   DEFAULT 0  NOT NULL
  , chr_strand                      NUMBER   (1)                    DEFAULT 0  NOT NULL
  , chr_name                        VARCHAR2 (20)                   
)
ORGANIZATION        HEAP
NOMONITORING
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE TABLE map_density

CREATE TABLE map_density
(
    chromosome_id                   NUMBER   (10)                   
  , chr_start                       NUMBER   (10)                   
  , chr_end                         NUMBER   (10)                   
  , type                            VARCHAR2 (40)                   
  , value                           NUMBER   (10)                   
)
ORGANIZATION        HEAP
NOMONITORING
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE TABLE plan_table

CREATE TABLE plan_table
(
    statement_id                    VARCHAR2 (30)                   
  , timestamp                       DATE                            
  , remarks                         VARCHAR2 (80)                   
  , operation                       VARCHAR2 (30)                   
  , options                         VARCHAR2 (255)                  
  , object_node                     VARCHAR2 (128)                  
  , object_owner                    VARCHAR2 (30)                   
  , object_name                     VARCHAR2 (30)                   
  , object_instance                 INTEGER                         
  , object_type                     VARCHAR2 (30)                   
  , optimizer                       VARCHAR2 (255)                  
  , search_columns                  NUMBER                          
  , id                              INTEGER                         
  , parent_id                       INTEGER                         
  , position                        INTEGER                         
  , cost                            INTEGER                         
  , cardinality                     INTEGER                         
  , bytes                           INTEGER                         
  , other_tag                       VARCHAR2 (255)                  
  , partition_start                 VARCHAR2 (255)                  
  , partition_stop                  VARCHAR2 (255)                  
  , partition_id                    INTEGER                         
  , other                           LONG                            
  , distribution                    VARCHAR2 (30)                   
  , cpu_cost                        INTEGER                         
  , io_cost                         INTEGER                         
  , temp_space                      INTEGER                         
)
ORGANIZATION        HEAP
NOMONITORING
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE TABLE repeat

CREATE TABLE repeat
(
    id                              NUMBER   (10)                   DEFAULT 0  NOT NULL
  , hid                             VARCHAR2 (255)                  
  , chr_name                        VARCHAR2 (40)                   
  , chr_start                       NUMBER   (17)                   DEFAULT 0  NOT NULL
  , chr_end                         NUMBER   (17)                   DEFAULT 0  NOT NULL
  , chr_strand                      NUMBER   (1)                    DEFAULT 0  NOT NULL
  , type                            VARCHAR2 (40)                   
)
ORGANIZATION        HEAP
NOMONITORING
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE TABLE repeat_types

CREATE TABLE repeat_types
(
    type                            VARCHAR2 (63)                   NOT NULL
)
ORGANIZATION        HEAP
NOMONITORING
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE TABLE snp

CREATE TABLE snp
(
    internal_id                     NUMBER   (15)                   DEFAULT 0  NOT NULL
  , chr_name                        VARCHAR2 (40)                   
  , chr_start                       NUMBER   (15)                   DEFAULT 0  NOT NULL
  , chr_end                         NUMBER   (15)                   
  , chr_strand                      NUMBER   (4)                    
  , snpclass                        VARCHAR2 (14)                   
  , validated                       VARCHAR2 (14)                   
  , alleles                         VARCHAR2 (4000)                 NOT NULL
  , mapweight                       NUMBER   (5)                    DEFAULT 0  NOT NULL
  , subsnpcount                     NUMBER   (5)                    
  , range_type                      VARCHAR2 (10)                   
  , id_refsnp                       NUMBER   (11)                   DEFAULT 0  NOT NULL
  , id_hgbase                       VARCHAR2 (50)                   DEFAULT ''
  
  , id_tsc                          VARCHAR2 (50)                   DEFAULT ''
  
  , id_ano                          VARCHAR2 (50)                   
  , id_wi                           VARCHAR2 (50)                   
  , type                            VARCHAR2 (12)                   
  , ambiguity                       VARCHAR2 (2)                    
  , source                          VARCHAR2 (15)                   
)
ORGANIZATION        HEAP
NOMONITORING
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE TABLE transcript

CREATE TABLE transcript
(
    id                              NUMBER   (10)                   NOT NULL
  , db                              VARCHAR2 (40)                   NOT NULL
  , analysis                        VARCHAR2 (40)                   NOT NULL
  , type                            VARCHAR2 (40)                   NOT NULL
  , transcript_id                   NUMBER   (10)                   NOT NULL
  , transcript_name                 VARCHAR2 (40)                   NOT NULL
  , transcript_version              NUMBER   (5)                    NOT NULL
  , chr_name                        VARCHAR2 (20)                   NOT NULL
  , chr_start                       NUMBER   (10)                   NOT NULL
  , chr_end                         NUMBER   (10)                   NOT NULL
  , chr_strand                      NUMBER   (3)                    NOT NULL
  , coding_start                    NUMBER   (10)                   NOT NULL
  , coding_end                      NUMBER   (10)                   NOT NULL
  , translation_id                  NUMBER   (10)                   NOT NULL
  , translation_name                VARCHAR2 (40)                   NOT NULL
  , translation_version             NUMBER   (5)                    NOT NULL
  , gene_id                         NUMBER   (10)                   NOT NULL
  , gene_name                       VARCHAR2 (40)                   DEFAULT 'unknown'  NOT NULL
  , gene_version                    NUMBER   (5)                    NOT NULL
  , exon_structure                  VARCHAR2 (4000)                 
  , exon_ids                        VARCHAR2 (4000)                 
  , external_db                     VARCHAR2 (40)                   
  , external_name                   VARCHAR2 (40)                   
  , external_status                 VARCHAR2 (10)                   
)
ORGANIZATION        HEAP
NOMONITORING
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;


prompt index;
REM This DDL was reverse engineered by
REM Perl module DDL::Oracle, Version 1.11
REM
REM at:   chevre
REM from: gramene, an Oracle Release 9.0.1.0.0 instance
REM
REM on:   Fri Sep 19 16:40:29 2003
REM
REM Generating CREATE INDEX statements for:
REM
REM	RICELITE1.GENE_CHR_NAME
REM	RICELITE1.GENE_CHR_NAME_START
REM	RICELITE1.GENE_EGNAMES
REM	RICELITE1.GENE_GENE_ID
REM	RICELITE1.GENE_UK
REM	RICELITE1.GENE_XREF_DB
REM	RICELITE1.GENE_XREF_DB_2
REM	RICELITE1.GENE_XREF_EXTERNAL_NAME
REM	RICELITE1.GENE_XREF_GENE_ID
REM	RICELITE1.LANDMARK_MARKER_CHR_NAME
REM	RICELITE1.REPEAT_CHR_NAME
REM	RICELITE1.REPEAT_TYPE
REM	RICELITE1.SNP_UK
REM	RICELITE1.SYS_C0059454
REM	RICELITE1.SYS_C0064581
REM	RICELITE1.SYS_C0064609
REM	RICELITE1.TRANSCRIPT_CHR_NAME
REM	RICELITE1.TRANSCRIPT_DB_3
REM	RICELITE1.TRANSCRIPT_DB_4
REM	RICELITE1.TRANSCRIPT_DB_5
REM	RICELITE1.TRANSCRIPT_GENE_ID
REM	RICELITE1.TRANSCRIPT_GENE_NAME
REM	RICELITE1.TRANSCRIPT_TRANSCRIPT_DBNAME
REM	RICELITE1.TRANSCRIPT_TRANSCRIPT_ID
REM	RICELITE1.TRANSCRIPT_TRANSCRIPT_NAME
REM	RICELITE1.TRANSCRIPT_TRANSLATION_NAME
REM	RICELITE1.TRANSCRIPT_UK
REM	RICELITE1.UK_CHROMOSOME_ID
REM	RICELITE1.UK_CHROMOSOME_NAME
REM	RICELITE1.UK_CHROMOSOME_STAT_TI
REM	RICELITE1.UK_LITE_MAP_DENSITY

PROMPT CREATE INDEX gene_chr_name ON gene

CREATE INDEX gene_chr_name ON gene
(
    chr_name
  , chr_start
  , chr_end
)
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE INDEX gene_chr_name_start ON gene

CREATE INDEX gene_chr_name_start ON gene
(
    db
  , chr_name
  , chr_start
)
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
COMPRESS            3
;

PROMPT CREATE INDEX gene_egnames ON gene

CREATE INDEX gene_egnames ON gene
(
    gene_name
  , external_name
  , db
)
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
COMPRESS            3
;

PROMPT CREATE INDEX gene_gene_id ON gene

CREATE INDEX gene_gene_id ON gene
(
    gene_id
)
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE UNIQUE INDEX gene_uk ON gene

CREATE UNIQUE INDEX gene_uk ON gene
(
    db
  , gene_name
)
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
COMPRESS            1
;

PROMPT CREATE INDEX gene_xref_db ON gene_xref

CREATE INDEX gene_xref_db ON gene_xref
(
    db
  , gene_id
)
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE INDEX gene_xref_db_2 ON gene_xref

CREATE INDEX gene_xref_db_2 ON gene_xref
(
    db
  , external_db
  , external_name
)
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE INDEX gene_xref_external_name ON gene_xref

CREATE INDEX gene_xref_external_name ON gene_xref
(
    external_name
)
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE INDEX gene_xref_gene_id ON gene_xref

CREATE INDEX gene_xref_gene_id ON gene_xref
(
    gene_id
  , transcript_id
)
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE INDEX landmark_marker_chr_name ON landmark_marker

CREATE INDEX landmark_marker_chr_name ON landmark_marker
(
    chr_name
  , chr_start
)
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE INDEX repeat_chr_name ON repeat

CREATE INDEX repeat_chr_name ON repeat
(
    chr_name
  , chr_start
)
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE INDEX repeat_type ON repeat

CREATE INDEX repeat_type ON repeat
(
    type
  , chr_name
  , chr_start
)
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE UNIQUE INDEX snp_uk ON snp

CREATE UNIQUE INDEX snp_uk ON snp
(
    chr_name
  , chr_start
)
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE UNIQUE INDEX sys_c0059454 ON repeat_types

CREATE UNIQUE INDEX sys_c0059454 ON repeat_types
(
    type
)
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE UNIQUE INDEX sys_c0064581 ON transcript

CREATE UNIQUE INDEX sys_c0064581 ON transcript
(
    id
)
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE UNIQUE INDEX sys_c0064609 ON gene

CREATE UNIQUE INDEX sys_c0064609 ON gene
(
    id
)
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE INDEX transcript_chr_name ON transcript

CREATE INDEX transcript_chr_name ON transcript
(
    chr_name
  , chr_start
)
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE INDEX transcript_db_3 ON transcript

CREATE INDEX transcript_db_3 ON transcript
(
    db
  , chr_name
  , chr_start
)
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE INDEX transcript_db_4 ON transcript

CREATE INDEX transcript_db_4 ON transcript
(
    db
  , gene_id
)
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE INDEX transcript_db_5 ON transcript

CREATE INDEX transcript_db_5 ON transcript
(
    db
  , gene_name
)
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE INDEX transcript_gene_id ON transcript

CREATE INDEX transcript_gene_id ON transcript
(
    gene_id
)
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE INDEX transcript_gene_name ON transcript

CREATE INDEX transcript_gene_name ON transcript
(
    gene_name
)
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE INDEX transcript_transcript_dbname ON transcript

CREATE INDEX transcript_transcript_dbname ON transcript
(
    db
  , transcript_name
)
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE INDEX transcript_transcript_id ON transcript

CREATE INDEX transcript_transcript_id ON transcript
(
    transcript_id
)
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE INDEX transcript_transcript_name ON transcript

CREATE INDEX transcript_transcript_name ON transcript
(
    transcript_name
)
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE INDEX transcript_translation_name ON transcript

CREATE INDEX transcript_translation_name ON transcript
(
    translation_name
)
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE UNIQUE INDEX transcript_uk ON transcript

CREATE UNIQUE INDEX transcript_uk ON transcript
(
    db
  , transcript_id
)
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE UNIQUE INDEX uk_chromosome_id ON chromosome

CREATE UNIQUE INDEX uk_chromosome_id ON chromosome
(
    chromosome_id
)
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE UNIQUE INDEX uk_chromosome_name ON chromosome

CREATE UNIQUE INDEX uk_chromosome_name ON chromosome
(
    name
)
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE UNIQUE INDEX uk_chromosome_stat_ti ON chromosome_stat

CREATE UNIQUE INDEX uk_chromosome_stat_ti ON chromosome_stat
(
    type
  , chromosome_id
)
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;

PROMPT CREATE UNIQUE INDEX uk_lite_map_density ON map_density

CREATE UNIQUE INDEX uk_lite_map_density ON map_density
(
    type
  , chromosome_id
  , chr_start
)
PARALLEL
(
  DEGREE            1
  INSTANCES         1
)
;


prompt sequence;
REM This DDL was reverse engineered by
REM Perl module DDL::Oracle, Version 1.11
REM
REM at:   chevre
REM from: gramene, an Oracle Release 9.0.1.0.0 instance
REM
REM on:   Fri Sep 19 16:40:48 2003
REM
REM Generating CREATE SEQUENCE statements for:
REM
REM	RICELITE1.GENE_ID_SQ
REM	RICELITE1.TRANSCRIPT_ID_SQ

PROMPT CREATE SEQUENCE gene_id_sq

CREATE SEQUENCE gene_id_sq
   START WITH       81061
   INCREMENT BY     1
   MINVALUE         1
   NOMAXVALUE
   CACHE            20
   NOCYCLE
   NOORDER
;

PROMPT CREATE SEQUENCE transcript_id_sq

CREATE SEQUENCE transcript_id_sq
   START WITH       81061
   INCREMENT BY     1
   MINVALUE         1
   NOMAXVALUE
   CACHE            20
   NOCYCLE
   NOORDER
;


prompt trigger;
REM This DDL was reverse engineered by
REM Perl module DDL::Oracle, Version 1.11
REM
REM at:   chevre
REM from: gramene, an Oracle Release 9.0.1.0.0 instance
REM
REM on:   Fri Sep 19 16:40:48 2003
REM
REM Generating CREATE TRIGGER statements for:
REM
REM	RICELITE1.GENE_ID_AI
REM	RICELITE1.TRANSCRIPT_ID_AI

PROMPT CREATE OR REPLACE TRIGGER gene_id_ai

CREATE OR REPLACE TRIGGER gene_id_ai
BEFORE INSERT ON gene
REFERENCING new AS NEW old AS OLD
FOR EACH ROW
WHEN (new.id IS NULL OR new.id = 0)
BEGIN
   SELECT gene_id_sq.nextval INTO :new.id FROM dual;
   mysql.last_insert_id := :new.id;
END;
/

PROMPT CREATE OR REPLACE TRIGGER transcript_id_ai

CREATE OR REPLACE TRIGGER transcript_id_ai
BEFORE INSERT ON transcript
REFERENCING new AS NEW old AS OLD
FOR EACH ROW
WHEN (new.id IS NULL OR new.id = 0)
BEGIN
   SELECT transcript_id_sq.nextval INTO :new.id FROM dual;
   mysql.last_insert_id := :new.id;
END;
/



