
DROP TABLE nx_project;

DROP TABLE nx_module;

DROP TABLE nx_version;

DROP TABLE nx_translation;

DROP TABLE nx_node;

DROP TABLE nx_string;

DROP TABLE nx_attached_glossary;

DROP TABLE nx_otm_version;

DROP TABLE nx_access;

DROP TABLE nx_pjt_seq;

DROP TABLE nx_mdl_seq;

DROP TABLE nx_vrn_seq;

DROP TABLE nx_delimiter;



CREATE TABLE nx_project(
 project_id                      NUMBER      	NOT NULL,        
 name                            VARCHAR2(30)	NOT NULL,  
 language_code                   VARCHAR2(3),
 character_set                   VARCHAR2(30),
 description                     VARCHAR2(2000)
);

CREATE TABLE nx_module(
 module_id                       NUMBER		NOT NULL,        
 name                            VARCHAR2(30)	NOT NULL,  
 module_type                     VARCHAR2(3)	NOT NULL,   
 project_id                      NUMBER  	NOT NULL      
);

CREATE TABLE nx_version(
 version_id                      NUMBER		NOT NULL,        
 version_number                  VARCHAR2(30)	NOT NULL,  
 project_id                      NUMBER  	NOT NULL,     
 module_id                       NUMBER         NOT NULL
);

CREATE TABLE nx_translation(
 string_count                    NUMBER         NOT NULL,
 translated_by			 VARCHAR(30),
 translation_date		 DATE,
 pathname                        VARCHAR2(255),
 language_code                   VARCHAR2(3)    NOT NULL,
 character_set                   VARCHAR2(30)   NOT NULL,
 project_id                      NUMBER  	NOT NULL,     
 module_id                       NUMBER         NOT NULL,
 version_id		         NUMBER         NOT NULL
);

CREATE TABLE nx_node(
 node_id                         NUMBER		NOT NULL,        
 node_type                       VARCHAR2(1)	NOT NULL,
 context_code                    VARCHAR2(3)	NOT NULL,
 groupid                         NUMBER,
 stringid                        NUMBER,
 hint                            VARCHAR2(255),
 parent_id                       NUMBER		NOT NULL,
 version_id                      NUMBER         NOT NULL
);

CREATE TABLE nx_string(
 string_text                     RAW(250),
 string_text_char                VARCHAR2(2000),
 string_index                    NUMBER,
 string_sequence                 NUMBER         NOT NULL,
 geometry                        VARCHAR2(255),
 font                            VARCHAR2(255),
 justification                   VARCHAR2(30),
 translation_status              VARCHAR2(3),
 version_id	       	         NUMBER		NOT NULL,
 language_code                   VARCHAR2(3)    NOT NULL,   
 character_set                   VARCHAR2(30)   NOT NULL,
 node_id                         NUMBER         NOT NULL
);

CREATE TABLE nx_attached_glossary(
 project_id                      NUMBER         NOT NULL,
 sequence_no                     NUMBER         NOT NULL,
 connection                      VARCHAR(255)   NOT NULL,
 id                              NUMBER         NOT NULL,
 indicator                       NUMBER         NOT NULL
);

CREATE TABLE nx_otm_version(
 otm_version                     VARCHAR2(30)   NOT NULL
);

CREATE TABLE nx_access(
 project_id                      NUMBER,
 module_id                       NUMBER,
 version_id                      NUMBER,
 username                        VARCHAR2(30)   NOT NULL
);

CREATE TABLE nx_pjt_seq(
 pjt_seq                         NUMBER         NOT NULL
);

CREATE TABLE nx_mdl_seq(
 mdl_seq                         NUMBER         NOT NULL
);

CREATE TABLE nx_vrn_seq(
 vrn_seq                         NUMBER         NOT NULL
);
 
CREATE TABLE nx_delimiter( 
 delimiter_type                  VARCHAR2(1)	NOT NULL,   
 delimiter_begin                 VARCHAR2(50),  
 delimiter_end                   VARCHAR2(50),  
 version_id                      NUMBER         NOT NULL
);

CREATE UNIQUE INDEX nx_pjt_prim ON  nx_project
(
 project_id
);

CREATE UNIQUE INDEX nx_pjt_uniq ON  nx_project
(
 name
);

CREATE UNIQUE INDEX nx_mdl_prim ON  nx_module
(
 module_id
);

CREATE UNIQUE INDEX nx_mdl_uniq ON  nx_module
(
 project_id,
 module_type,
 name
);

CREATE UNIQUE INDEX nx_vrn_prim ON  nx_version
(
 version_id
);

CREATE UNIQUE INDEX nx_vrn_uniq ON  nx_version
(
 project_id,
 module_id,
 version_number
);

CREATE UNIQUE INDEX nx_trn_prim ON  nx_translation
(
 project_id,
 language_code,
 module_id,
 version_id,
 character_set
);

CREATE UNIQUE INDEX nx_nod_prim ON  nx_node
(
 version_id,
 node_id
);

CREATE UNIQUE INDEX nx_str_prim ON  nx_string
(
 language_code,
 version_id,
 node_id,
 string_sequence,
 character_set
);

CREATE INDEX nx_str_indx ON  nx_string
(
 string_index
);

CREATE INDEX nx_atg_indx ON  nx_attached_glossary
(
 project_id,
 sequence_no
);

CREATE INDEX nx_acc_indx ON nx_access
(
 project_id,
 module_id,
 version_id,
 username
);

CREATE INDEX nx_dlr_indx ON  nx_delimiter
( 
 version_id,
 delimiter_type
);


INSERT INTO nx_pjt_seq(pjt_seq) VALUES(1001);

INSERT INTO nx_mdl_seq(mdl_seq) VALUES(1001);

INSERT INTO nx_vrn_seq(vrn_seq) VALUES(1001);
 
INSERT INTO nx_otm_version VALUES('3.1.0.1.0');
