REM * Set terminal output and command echoing on; log output of this script.
REM *
#set termout on
#set echo on

spool /usr/oracle/admin/sql/creatables.lst


REM * creation des numeros sequences. 

create sequence SEQ_DATASET;
create sequence SEQ_ETHERFILE;
create sequence SEQ_ETHER_APPLICABLE_SERVICE;
create sequence SEQ_ETHER_ATTRIBUTE;
create sequence SEQ_ETHER_CRITERIA;
create sequence SEQ_ETHER_DATA_RIGHT;
create sequence SEQ_ETHER_EXPERIMENT;
create sequence SEQ_ETHER_EXPERIMENT_RIGHT;
create sequence SEQ_ETHER_FILE;
create sequence SEQ_ETHER_GROUP;
create sequence SEQ_ETHER_ORDER;
create sequence SEQ_ETHER_SHOPPINGLIST;
create sequence SEQ_ETHER_USER;
create sequence SEQ_ETHER_SERVICE;
create sequence SEQ_ETHER_SERVICE_RIGHT;
create sequence SEQ_ETHER_GROUPUSER;
create sequence SEQ_ETHER_SERVICE_INTERACTIF;


REM * creation tables
create table ETHER_FILE  (
	 ETH_ID                  NUMBER(8) NOT NULL, 
	 ETH_ID_EXPERIMENT       NUMBER(8) NOT NULL,
	 ETH_PARAMETER           VARCHAR2(30),
	 ETH_SUBPARAMETER		 VARCHAR2(30),
	 ETH_DATASET_VERSION     VARCHAR2(15),
	 ETH_CEOS_LEVEL          VARCHAR2(15),
	 ETH_CEOS_SUBLEVEL       VARCHAR2(15),
	 ETH_ATMOSPHERIC_LEVEL   VARCHAR2(30),
	 ETH_FIRST_DATE          NUMBER(13),
	 ETH_LAST_DATE           NUMBER(13),
	 ETH_LON_MIN             NUMBER(6,3),
	 ETH_LON_MAX             NUMBER(6,3),
	 ETH_LAT_MIN             NUMBER(6,3),
	ETH_LAT_MAX             NUMBER(6,3),
	ETH_ALT_MIN             NUMBER(6,3),
	ETH_ALT_MAX             NUMBER(6,3),
	ETH_FORMAT              VARCHAR2(15),
	ETH_PATH                VARCHAR2(256),
	ETH_TAR_FILE            VARCHAR2(256),
	ETH_ON_LINE             NUMBER(1),
	ETH_SIZE                NUMBER(12),
	ETH_ID_DATASET		NUMBER(8)
)
PCTFREE 30
PCTUSED 50
tablespace SYSTEM
STORAGE (INITIAL 50K
		NEXT 500K
		MAXEXTENTS 100
		PCTINCREASE 25);

create table ETHER_DATASET ( 
	SET_ID			 NUMBER(8) NOT NULL,
	SET_ID_EXPERIMENT	 NUMBER(8) NOT NULL,
	SET_PARAMETER		 VARCHAR2(30),
	SET_SUBPARAMETER		 VARCHAR2(30),
	SET_CEOS_LEVEL		 VARCHAR2(15),
	SET_CEOS_SUBLEVEL	 VARCHAR2(15),
	SET_DATASET_VERSION	 VARCHAR2(15),
	SET_ATMOSPHERIC_LEVEL	 VARCHAR2(30),
	SET_FORMAT		 VARCHAR2(15),
	SET_FIRST_DATE		 NUMBER(13),
	SET_LAST_DATE		 NUMBER(13),
	SET_LON_MIN		 NUMBER(6,3),
	SET_LON_MAX		 NUMBER(6,3),
	SET_LAT_MIN		 NUMBER(6,3),
	SET_LAT_MAX		 NUMBER(6,3),
	SET_UPPER_ALTITUDE	 NUMBER(6,3),
	SET_LOWER_ALTITUDE	 NUMBER(6,3),
	SET_SIZE		 NUMBER(12),
	SET_NB_FILES		 NUMBER(5) 
)
PCTFREE 10
PCTUSED 40
tablespace SYSTEM
STORAGE (INITIAL 20K
                NEXT 500K
                MAXEXTENTS 100
                PCTINCREASE 25);

create table ETHER_ATTRIBUTE (
	ATT_ID			 NUMBER(5) NOT NULL,
	ATT_ID_EXPERIMENT	 NUMBER(5) NOT NULL,
	ATT_CODE		 NUMBER(2) ,
	ATT_NAME		 VARCHAR2(30),
	ATT_OPERATOR		 VARCHAR2(10),
	ATT_ORACLE_TYPE		 VARCHAR2(30),
	ATT_HTML_TYPE		 NUMBER(1),
	ATT_COLUMN_NAME		 VARCHAR2(50),
	ATT_USED		 NUMBER(1)
)
PCTFREE 10
PCTUSED 40
tablespace SYSTEM
STORAGE (INITIAL 10K
                NEXT 5K
                MAXEXTENTS 50
                PCTINCREASE 25);


create table ETHER_USER (
	ID        NUMBER(8) NOT NULL,
	LOGIN      VARCHAR2(30) NOT NULL,
	NAME       VARCHAR2(30),
	FIRST_NAME  VARCHAR2(30),
	PASSWORD   VARCHAR2(30),
	EMAIL      VARCHAR2(50),
	IP_ADDRESS       VARCHAR2(15),
	LABORATORY_NAME    VARCHAR2(30),
	ADDRESS        VARCHAR2(250),
	TELEPHONE  VARCHAR2(20),
	REPOSITORY_PATH        VARCHAR2(256),
	NETWORK_QUOTA     NUMBER(3),
	REPOSITORY_MAX_SIZE     NUMBER(3),
	ORDER_LIFETIME       NUMBER(3),
	MEDIA_ID     NUMBER(3),
	ROLE     VARCHAR2(1) 
)
PCTFREE 10
PCTUSED 40
tablespace SYSTEM
STORAGE (INITIAL 5K
                NEXT 5K
                MAXEXTENTS 5
                PCTINCREASE 10);

create table ETHER_GROUP (
        GRP_ID       NUMBER(5) NOT NULL,
        GRP_GROUPNAME     VARCHAR2(25)
)
PCTFREE 10
PCTUSED 40
tablespace SYSTEM
STORAGE (INITIAL 5K
                NEXT 5K
                MAXEXTENTS 5
                PCTINCREASE 10);

create table ETHER_GROUP_USER (
        ID       NUMBER(8) NOT NULL,
        GROUP_ID     NUMBER(8),
	USER_ID		NUMBER(8)
)
PCTFREE 10
PCTUSED 40
tablespace SYSTEM
STORAGE (INITIAL 5K
                NEXT 5K
                MAXEXTENTS 5
                PCTINCREASE 10);

create table ETHER_EXPERIMENT (
	EXP_ID		NUMBER(5) NOT NULL,
	EXP_NAME        VARCHAR2(50) NOT NULL,
	EXP_MISSION     VARCHAR2(30),
	EXP_URLINFO     VARCHAR2(50),
	EXP_LON_MIN     NUMBER(6,3),
	EXP_LON_MAX     NUMBER(6,3),
	EXP_LAT_MIN     NUMBER(6,3),
	EXP_LAT_MAX     NUMBER(6,3),
	EXP_ALT_MIN     NUMBER(6,3),
	EXP_ALT_MAX     NUMBER(6,3),
	EXP_FIRST_DATE  DATE,
	EXP_LAST_DATE   DATE,
	EXP_PUBLIC      NUMBER(1),
	EXP_READ	    VARCHAR2(256),
	EXP_RIGHT	    VARCHAR2(256)
)
PCTFREE 10
PCTUSED 40
tablespace SYSTEM
STORAGE (INITIAL 5K
                NEXT 5K
                MAXEXTENTS 5
                PCTINCREASE 10);

create table ETHER_EXPERIMENT_RIGHT (
	ERI_ID         		NUMBER(5) NOT NULL,
	ERI_EXPERIMENT_ID   NUMBER(5),
	ERI_GROUP_ID        NUMBER(5)
)
PCTFREE 10
PCTUSED 40
tablespace SYSTEM
STORAGE (INITIAL 5K
                NEXT 5K
                MAXEXTENTS 5
                PCTINCREASE 10);

create table ETHER_SERVICE_RIGHT (
        SRI_ID       NUMBER(8) NOT NULL,
	SRI_SERVICE_ID     NUMBER(8),
        SRI_GROUP_ID         NUMBER(8)
)
PCTFREE 10
PCTUSED 40
tablespace SYSTEM
STORAGE (INITIAL 5K
                NEXT 5K
                MAXEXTENTS 5
                PCTINCREASE 10);

create table ETHER_DATA_RIGHT (
        DRI_ID       NUMBER(5) NOT NULL,
	DRI_GROUP_ID       NUMBER(5),
	DRI_DATASET_ID       NUMBER(5),
	DRI_LON_MIN             NUMBER(6,3),
        DRI_LON_MAX             NUMBER(6,3),
        DRI_LAT_MIN             NUMBER(6,3),
        DRI_LAT_MAX             NUMBER(6,3),
        DRI_ALT_MIN             NUMBER(6,3),
        DRI_ALT_MAX             NUMBER(6,3),
	DRI_FIRST_DATE		NUMBER(13),
	DRI_LAST_DATE		NUMBER(13)
)
PCTFREE 10
PCTUSED 40
tablespace SYSTEM
STORAGE (INITIAL 5K
                NEXT 5K
                MAXEXTENTS 5
                PCTINCREASE 10);

create table ETHER_SHOPPINGLIST (
	SHO_ID       NUMBER(5) NOT NULL,
	SHO_USERID   NUMBER(5),
	SHO_NAME     VARCHAR2(25),
	SHO_NBFILES  NUMBER(9),
	SHO_VOLUME   NUMBER(9)
)
PCTFREE 10
PCTUSED 40
tablespace SYSTEM
STORAGE (INITIAL 5
                NEXT 5K
                MAXEXTENTS 10
                PCTINCREASE 10);

create table ETHER_SHOPPINGLISTCONTENT (
	SHO_ID        NUMBER(5) NOT NULL,
	SHO_IDFILE    NUMBER(9),
	SHO_TYPEFILE  NUMBER(1)
)
PCTFREE 30
PCTUSED 60
tablespace SYSTEM
STORAGE (INITIAL 10K
                NEXT 10K
                MAXEXTENTS 20
                PCTINCREASE 20);



create table ETHER_ORDER (
	ORD_ID              NUMBER(5) NOT NULL,
	ORD_USERID          NUMBER(5),
	ORD_SHOPPINGLISTID  NUMBER(5),
	ORD_MEDIA           NUMBER(5),
	ORD_VOLUME          NUMBER(9),
	ORD_STATUS          NUMBER(5),
	ORD_DATE_START      DATE,
	ORD_DATE_FINISH     DATE
)
PCTFREE 10
PCTUSED 40
tablespace SYSTEM
STORAGE (INITIAL 5K
                NEXT 5K
                MAXEXTENTS 10
                PCTINCREASE 10);

create table ETHER_SERVICE (
	SER_ID			 NUMBER(5) NOT NULL,
	SER_NAME		 VARCHAR2(25)NOT NULL,
	SER_QUOTA		 NUMBER(5),
	SER_VERSION		 VARCHAR2(25),
	SER_STATE		 VARCHAR2(25),
	SER_CODING		 VARCHAR2(25),
	SER_DESIGNER		 VARCHAR2(25),
	SER_INFO		 VARCHAR2(256),
	SER_DESCRIPTION		 VARCHAR2(256),
	SER_HOST		 VARCHAR2(25),
	SER_SOURCE_PATH		 VARCHAR2(256),
	SER_EXEC_PATH		 VARCHAR2(256),
	SER_EXEC_NAME		 VARCHAR2(256),
	SER_PARAM_FILE		 VARCHAR2(256),
	SER_KEEP_ORIGINAL	 VARCHAR2(1),
	SER_INTERACTIF		 VARCHAR2(1),
	SER_ACTIVATION_URL	 VARCHAR2(256),
	SER_OUT_ORDER		 VARCHAR2(1),
	SER_PARAMETER_PAGE	 VARCHAR2(256),
	SER_COEFFICIENT		 NUMBER(9)
)
PCTFREE 10
PCTUSED 40
tablespace SYSTEM
STORAGE (INITIAL 5K
                NEXT 5K
                MAXEXTENTS 10
                PCTINCREASE 10);


create table ETHER_APPLICABLE_SERVICE (
        ID                   NUMBER(8) NOT NULL,
        SERVICE_ID                 NUMBER(8),
        DATASET_ID              NUMBER(8)
)
PCTFREE 10
PCTUSED 40
tablespace SYSTEM
STORAGE (INITIAL 5K
                NEXT 5K
                MAXEXTENTS 10
                PCTINCREASE 10);

REM * creation des indexes

create index order_idx on ETHER_ORDER (ORD_ID, ORD_USERID)
	TABLESPACE INDX
	STORAGE (INITIAL 20K
	NEXT 20K
	PCTINCREASE 25)
	PCTFREE 0;

create index shoppinlist_idx on ETHER_SHOPPINGLIST (sho_id, sho_userid)
	TABLESPACE INDX
	STORAGE (INITIAL 20K
	NEXT 20K
	PCTINCREASE 25)
	PCTFREE 0;

create index service_idx on ETHER_SERVICE (ser_id, ser_interactif)
	TABLESPACE INDX
        STORAGE (INITIAL 20K
        NEXT 20K
        PCTINCREASE 25)
        PCTFREE 0;

create index shoppintlistcontent_idx on ETHER_SHOPPINGLISTCONTENT (sho_id)
	TABLESPACE INDX
        STORAGE (INITIAL 20K
        NEXT 20K
        PCTINCREASE 25)
        PCTFREE 0;

create index fileexp_idx on ETHER_FILE (eth_id_experiment)
	TABLESPACE INDX
        STORAGE (INITIAL 20K
        NEXT 20K
        PCTINCREASE 25)
        PCTFREE 0;

create index datasetexp_idx on ETHER_DATASET (set_id_experiment )
	TABLESPACE INDX
        STORAGE (INITIAL 20K
        NEXT 20K
        PCTINCREASE 25)
        PCTFREE 0;

create index attexpe_idx on ETHER_ATTRIBUTE (ATT_id_experiment)
TABLESPACE INDX
        STORAGE (INITIAL 20K
        NEXT 20K
        PCTINCREASE 25)
        PCTFREE 0;

create index rightdata_idx on ETHER_DATA_RIGHT (dri_datasetid, dri_group_id)
	TABLESPACE INDX
        STORAGE (INITIAL 20K
        NEXT 20K
        PCTINCREASE 25)
        PCTFREE 0;



exit
spoot off