/************************************************************************
 * Name     :create_db_objects.sql                                      *
 * Issue    :03-09-2003 - H. Korte - Intevation - Osnabrueck            *
 * Purpose  :create seq.'s, func.'s, triggers and tables in spec. db    *
 * Parameter:                                                           *
 * Changes  :                                                           *
 * [ Date   ] - [ Name ] - [ Action ]                                   *
 ************************************************************************/
 
/********************************************************
 * 1 - GROUP_SEQ                                        *
 ********************************************************
 *                                                      *
 * CREATE [ TEMPORARY | TEMP ] SEQUENCE seqname         *
 *    [ INCREMENT [ BY ] increment ]                    *
 *        [ MINVALUE minvalue | NO MINVALUE ]           *
 *    [ MAXVALUE maxvalue | NO MAXVALUE ]               *
 *        [ START [ WITH ] start ]                      *
 *    [ CACHE cache ]                                   *
 *    [ [ NO ] CYCLE ]                                  *
 *                                                      *
 * increment - The default value is 1                   *
 *         positive value -> ascending sequence         *
 *         negative value -> descending sequence        *
 *                                                      *
 * MINVALUE  - NO MINVALUE is default                   *
 *                                                      *
 * MAXVALUE  - NO MAXVALUE is default                   *
 *                                                      *
 * START     - default starting value is minvalue for   *
 *         ascending sequences and maxvalue for         *
 *         descending                                   *
 *                                                      *
 * CACHE     - Hope, no cache is default                *
 *                                                      *
 * CYCLE     - No CYCLE is default                      *
 ********************************************************/

CREATE SEQUENCE GROUP_SEQ MINVALUE 1 START 100;
 
/********************************************************
 * 2 - PRHASE_SEQ 					*
 ********************************************************/
 
CREATE SEQUENCE PHRASE_SEQ MINVALUE 1 START 100;


/********************************************************
 * 3 - BIN_OBJ_SEQ					*
 ********************************************************/

CREATE SEQUENCE BIN_OBJ_SEQ MINVALUE 1 START 200; 


/********************************************************
 * 4 - SUBST_SEQ					*
 ********************************************************/

CREATE SEQUENCE SUBST_SEQ MINVALUE 1 START 100;


/********************************************************
 * 5 - SUBST_DET_SEQ					*
 ********************************************************/

CREATE SEQUENCE SUBST_DET_SEQ MINVALUE 1 START 100;


/********************************************************
 * 6 - ENV_SEQ						*
 ********************************************************/

CREATE SEQUENCE ENV_SEQ MINVALUE 1 START 100;


/********************************************************
 * 7 - ENV_DET_SEQ					*
 ********************************************************/

CREATE SEQUENCE ENV_DET_SEQ MINVALUE 1 START 100;


/********************************************************
 * 8 - MARKET_SEQ					*
 ********************************************************/

CREATE SEQUENCE MARKET_SEQ MINVALUE 1 START 100;


/********************************************************
 * 9 - MOD_SEQ						*
 ********************************************************/

CREATE SEQUENCE MOD_SEQ MINVALUE 1 START 100;


/********************************************************
 * 10 - MOD_DET_SEQ					*
 ********************************************************/

CREATE SEQUENCE MOD_DET_SEQ MINVALUE 1 START 100;


/********************************************************
 * 11 - RES_SEQ						*
 ********************************************************/

CREATE SEQUENCE RES_SEQ MINVALUE 1 START 100;


/********************************************************
 * 12 - RES_STRETCH_SEQ					*
 ********************************************************/

CREATE SEQUENCE RES_STRETCH_SEQ MINVALUE 1 START 100;


/********************************************************
 * 13 - RES_DISCH_SEQ					*
 ********************************************************/

CREATE SEQUENCE RES_DISCH_SEQ MINVALUE 1 START 100;


/********************************************************
 * 14 - SESS_SEQ					*
 ********************************************************/

CREATE SEQUENCE SESS_SEQ MINVALUE 1 START 100;


/********************************************************
 * 15 - SESS_DISCH_SEQ					*
 ********************************************************/

CREATE SEQUENCE SESS_DISCH_SEQ MINVALUE 1 START 100;

/********************************************************
 * 16 - CATCH_SEQ					*
 ********************************************************/

CREATE SEQUENCE CATCH_SEQ MINVALUE 1 START 100;

/********************************************************
 * 17 - BIN_OBJ_SEARCH_TAB 				*
 ********************************************************/

CREATE TABLE BIN_OBJ_SEARCH_TAB ( 
  BIN_OBJ_ID  NUMERIC (26)     NOT NULL, 
  KEY_WORD    VARCHAR (255)  NOT NULL);
 
CREATE INDEX BIN_OBJ_I1_SEARCH_IDX ON 
  BIN_OBJ_SEARCH_TAB(BIN_OBJ_ID);

CREATE INDEX BIN_OBJ_I2_SEARCH_IDX ON 
  BIN_OBJ_SEARCH_TAB(KEY_WORD);

/********************************************************
 * 18 - BIN_OBJ_TAB 					*
 ********************************************************/

CREATE TABLE BIN_OBJ_TAB ( 
  BIN_OBJ_ID    NUMERIC (26)   DEFAULT nextval('BIN_OBJ_SEQ'), 
  OBJECT_ID     NUMERIC (26)    NOT NULL, 
  NAME          VARCHAR (255)   NOT NULL, 
  USER_ID       VARCHAR (30)    NOT NULL, 
  PRIVS         NUMERIC (1)     NOT NULL, 
  OBJ_TYPE      VARCHAR (30)    NOT NULL, 
  OBJ_SUB_TYPE  VARCHAR (30)    NOT NULL, 
  FILE_TYPE     VARCHAR (3)     NOT NULL, 
  FILE_OBJ      OID             NOT NULL, 
  FILE_SIZE     NUMERIC (26)    NOT NULL, 
  REMARK        VARCHAR (2000)          , 
  CRE_DATE      DATE            NOT NULL, 
  MOD_DATE      DATE            NOT NULL, 
  CONSTRAINT BIN_OBJ_U1_IDX
  PRIMARY KEY ( BIN_OBJ_ID ) 
  );

CREATE INDEX BIN_OBJ_I1_IDX ON 
  BIN_OBJ_TAB(OBJECT_ID); 

CREATE INDEX BIN_OBJ_I2_IDX ON 
  BIN_OBJ_TAB(NAME); 

/********************************************************
 * 19 - REPLACE() - RETURNS VARCHAR			*
 ********************************************************/
-- by Jonathan Ellis (jbellis ( at ) hotmail ( dot ) com)
-- licensed under the GPL
-- emailing me improvements is appreciated but not required
create function replace (varchar, varchar, varchar) returns varchar as '
declare
    string alias for $1;
    sub alias for $2;
    replacement alias for $3;
    match integer;
    end_before integer;
    start_after integer;
    string_replaced varchar;
    string_remainder varchar;
begin
    string_remainder := string;
    string_replaced := '''';
    match := position(sub in string_remainder);

    while match > 0 loop
        end_before := match - 1;
        start_after := match + length(sub);
        string_replaced := string_replaced || substr(string_remainder, 1, end_before) || replacement;
        string_remainder := substr(string_remainder, start_after);
        match := position(sub in string_remainder);
    end loop;
    string_replaced := string_replaced || string_remainder;

    return string_replaced;
end;
' LANGUAGE 'plpgsql';


/********************************************************
 * 20 - BIN_OBJ_PROC() - RETURNS OPAQUE -> 'nothing'	*
 ********************************************************/

CREATE FUNCTION BIN_OBJ_PROCF () RETURNS TRIGGER AS '
DECLARE
  updateAction VARCHAR (10);
  remarkString VARCHAR (2001);
  separatorPos INT;
BEGIN
  updateAction := ''nothing'';

  IF TG_OP=''UPDATE'' THEN
    IF (OLD.REMARK IS NOT NULL OR NEW.REMARK IS NOT NULL) THEN
      IF NEW.REMARK IS NULL OR LENGTH(NEW.REMARK) = 0 THEN
        updateAction := ''delete'';
      ELSIF OLD.REMARK IS NULL OR LENGTH(OLD.REMARK) = 0 THEN
        updateAction := ''insert'';
      ELSIF OLD.REMARK != NEW.REMARK THEN
        updateAction := ''rebuild'';
      END IF;
    END IF;
  END IF;
  IF (TG_OP=''DELETE'' OR updateAction = ''delete'' OR updateAction = ''rebuild'') THEN
    DELETE FROM BIN_OBJ_SEARCH_TAB WHERE BIN_OBJ_ID = OLD.BIN_OBJ_ID;
    RETURN OLD;
  END IF;
  IF ((TG_OP=''INSERT'' AND NEW.REMARK IS NOT NULL) OR updateAction = ''insert'' OR updateAction = ''rebuild'') THEN
    remarkString := NEW.REMARK;
    remarkString := UPPER(remarkString);
    remarkString := REPLACE(remarkString, chr(39), '''');
    remarkString := REPLACE(remarkString, chr(96), '''');
    remarkString := REPLACE(remarkString, chr(180), '''');
    remarkString := REPLACE(remarkString, '' '', '';'');
    remarkString := REPLACE(remarkString, '','', '';'');
    remarkString := REPLACE(remarkString, ''.'', '';'');
    remarkString := REPLACE(remarkString, '':'', '';'');
    remarkString := REPLACE(remarkString, ''('', '';'');
    remarkString := REPLACE(remarkString, '')'', '';'');
    remarkString := LTRIM(remarkString, '';'');
    remarkString := RTRIM(remarkString, '';'');
    remarkString := remarkString || '';'';
    WHILE (LENGTH(remarkString) > 0) LOOP
      separatorPos := strpos(remarkString, '';'');
      INSERT INTO BIN_OBJ_SEARCH_TAB
             (BIN_OBJ_ID, KEY_WORD)
      VALUES (NEW.BIN_OBJ_ID, substr(remarkString, 1, separatorPos - 1));
      remarkString := substr(remarkString, separatorPos,
                              LENGTH (remarkString) - separatorPos + 1);
      remarkString := LTRIM (remarkString, '';'');
    END LOOP;
  END IF;
  RETURN NEW;
END;
' LANGUAGE 'plpgsql';

/********************************************************
 * 21 - BIN_OBJ_TRG					*
 ********************************************************/

CREATE TRIGGER BIN_OBJ_TRG
 AFTER UPDATE OR DELETE OR INSERT
 ON BIN_OBJ_TAB
FOR EACH ROW
EXECUTE PROCEDURE BIN_OBJ_PROCF();



/********************************************************
 * 22 - GROUP_TAB					*
 *							*
 *	Bei Insert auf die Tabelle GROUP_TAB		*
 *	wird die GROUP_ID automatisch durch die 	*
 *	Sequenz GROUP_SEQ vergeben.			*
 *	GROUP_SEQ wird beim anlegen der Tabelle		*
 *	initialisiert.					*
 *							*
 ********************************************************/

CREATE TABLE GROUP_TAB ( 
  GROUP_ID  NUMERIC (26) DEFAULT nextval('GROUP_SEQ'), 
  NAME      VARCHAR (255)  NOT NULL, 
  ADMIN     VARCHAR (1)    NOT NULL, 
  CRE_DATE  DATE            NOT NULL, 
  MOD_DATE  DATE            NOT NULL, 
  STATUS    VARCHAR (30)           , 
  REMARK    VARCHAR (2000)         , 
  CONSTRAINT GROUP_U1_IDX
  PRIMARY KEY ( GROUP_ID ) 
  );

CREATE INDEX GROUP_I1_IDX ON 
  GROUP_TAB(NAME); 

/********************************************************
 * 23 - MESSAGE_TAB					*
 ********************************************************/

CREATE TABLE MESSAGE_TAB ( 
  MESSAGE_ID  Decimal (26) NOT NULL, 
  LANGUAGE    VARCHAR (1)  NOT NULL, 
  TYPE        VARCHAR (3)  NOT NULL, 
  MESSAGE     VARCHAR (255)        , 
  CONSTRAINT MESSAGE_U1_IDX
  PRIMARY KEY ( MESSAGE_ID ) 
  );

CREATE INDEX MESSAGE_I1_IDX ON 
  MESSAGE_TAB(MESSAGE);

/********************************************************
 * 24 - PHRASE_TAB					*
 ********************************************************/

CREATE TABLE PHRASE_TAB ( 
  PHRASE_ID      NUMERIC (26) DEFAULT nextval('PHRASE_SEQ'), 
  PHRASE_GRP_ID  NUMERIC (26)     NOT NULL, 
  LANGUAGE       VARCHAR (1)    NOT NULL, 
  SORT           NUMERIC (3)      NOT NULL, 
  VALUE_PT       VARCHAR (255)          , 
  REMARK         VARCHAR (2000)         , 
  CONSTRAINT PHRASE_U1_IDX
  PRIMARY KEY ( PHRASE_ID ) 
  );

CREATE INDEX PHRASE_I1_IDX ON 
  PHRASE_TAB(VALUE_PT);


/********************************************************
 * 25 - PARA_TREE_DEF_TAB				*
 ********************************************************/

CREATE TABLE PARA_TREE_DEF_TAB ( 
  FIELD_ID     VARCHAR (30)  NOT NULL, 
  BLOCK_ID     VARCHAR (30)  NOT NULL, 
  GROUP_ID     VARCHAR (30)  NOT NULL, 
  OBJ_TYPE     VARCHAR (30)  NOT NULL, 
  VAR_TYPE     VARCHAR (30)  NOT NULL, 
  TREE_LEVEL   NUMERIC (1)     NOT NULL, 
  FIELD_SORT   NUMERIC (3)     NOT NULL, 
  BLOCK_SORT   NUMERIC (3)     NOT NULL, 
  GROUP_SORT   NUMERIC (3)     NOT NULL, 
  LABEL        VARCHAR (255)         , 
  DEFAULT_VAL  VARCHAR (255)         , 
  FORM_MASK    VARCHAR (255)         , 
  UNIT         VARCHAR (30)          , 
  W_RANGE      VARCHAR (255)         , 
  E_RANGE      VARCHAR (255)         , 
  REMARK       VARCHAR (2000)        , 
  CONSTRAINT PARA_TREE_U1_IDX
  PRIMARY KEY ( GROUP_ID, BLOCK_ID, FIELD_ID ) 
  );

CREATE INDEX PARA_TREE_I1_IDX ON 
  PARA_TREE_DEF_TAB(LABEL);

/********************************************************
 * 26 - USER_TAB					*
 ********************************************************/

CREATE TABLE USER_TAB ( 
  USER_ID   VARCHAR (30)  NOT NULL, 
  GROUP_ID  NUMERIC (26)    NOT NULL, 
  CRE_DATE  DATE           NOT NULL, 
  MOD_DATE  DATE           NOT NULL, 
  STATUS    VARCHAR (30)  NOT NULL, 
  REMARK    VARCHAR (2000)        , 
  CONSTRAINT USER_U1_IDX
  PRIMARY KEY ( USER_ID ) 
  );


/********************************************************
 * 27 - CATCH_TAB					*
 ********************************************************/

CREATE TABLE CATCH_TAB ( 
  CATCH_ID  NUMERIC (26) DEFAULT nextval('CATCH_SEQ'), 
  USER_ID   VARCHAR (30)  NOT NULL, 
  CRE_DATE  DATE           NOT NULL, 
  MOD_DATE  DATE           NOT NULL, 
  STATUS    VARCHAR (30)          , 
  PRIVS     NUMERIC (1)     NOT NULL, 
  NAME      VARCHAR (255)         , 
  REMARK    VARCHAR (2000)        , 
  CONSTRAINT CATCH_U1_IDX
  PRIMARY KEY ( CATCH_ID ) 
  );

CREATE UNIQUE INDEX CATCH_U2_IDX ON 
  CATCH_TAB(NAME); 

/********************************************************
 * 28 - CATCH_PROC() - RETURNS OPAQUE -> 'nothing'	*
 ********************************************************/

CREATE FUNCTION CATCH_PROCF () RETURNS TRIGGER AS '
    BEGIN
 	IF TG_OP=''UPDATE'' THEN
	  IF OLD.CATCH_ID  != NEW.CATCH_ID THEN
	      UPDATE  BIN_OBJ_TAB
	      SET     OBJECT_ID    = NEW.CATCH_ID
	      WHERE   OBJECT_ID    = OLD.CATCH_ID
	      AND     OBJ_TYPE     = ''CATCHMENT'';
	  END IF;
	END IF;
	IF TG_OP=''DELETE'' THEN
	    DELETE FROM BIN_OBJ_TAB
	    WHERE   OBJECT_ID    = OLD.CATCH_ID
	    AND     OBJ_TYPE     = ''CATCHMENT'';
	END IF;
        RETURN NEW;
    END;
' LANGUAGE 'plpgsql';


/********************************************************
 * 29 - CATCH_TRG					*
 ********************************************************/

CREATE TRIGGER CATCH_TRG
 AFTER UPDATE OR DELETE    
 ON CATCH_TAB
FOR EACH ROW
EXECUTE PROCEDURE CATCH_PROCF();


/********************************************************
 * 30 - BACKGRD_JOIN_TAB				*
 ********************************************************/

CREATE TABLE BACKGRD_JOIN_TAB ( 
  CATCH_ID    NUMERIC (26)   NOT NULL, 
  BIN_OBJ_ID  NUMERIC (26)   NOT NULL, 
  CONSTRAINT BACKGRD_U1_IDX
  PRIMARY KEY ( CATCH_ID, BIN_OBJ_ID ) 
  );
  

/********************************************************
 * 31 - DISCH_CLASS_TAB					*
 ********************************************************/

CREATE TABLE DISCH_CLASS_TAB ( 
  DISCH_CLASS_ID   NUMERIC (26)   NOT NULL, 
  NAME             VARCHAR (255)        , 
  WWTP_TYPE        VARCHAR (255)        , 
  CAPACITY         VARCHAR (40)         , 
  AER_TYPE         VARCHAR (40)         , 
  H_PRIM           VARCHAR (40)         , 
  V_OVERFLOW_PRIM  VARCHAR (40)         , 
  R_SS_PRIM_MAX    VARCHAR (40)         , 
  H_SEC            VARCHAR (40)         , 
  V_OVERFLOW_SEC   VARCHAR (40)         , 
  R_SS_SEC_MAX     VARCHAR (40)         , 
  SS_ML            VARCHAR (40)         , 
  RECYCLE          VARCHAR (40)         , 
  A_BIOMASS        VARCHAR (40)         , 
  H_ACT_SLUDGE     VARCHAR (40)         , 
  B_X              VARCHAR (40)         , 
  Y                VARCHAR (40)         , 
  DO_              VARCHAR (40)         , 
  F_AEROBIC        VARCHAR (40)         , 
  F_ANOXIC         VARCHAR (40)         , 
  F_ANAEROBIC      VARCHAR (40)         , 
  EPS_N            VARCHAR (40)         , 
  EPS_DN           VARCHAR (40)         , 
  REMARK           VARCHAR (2000)       , 
  CONSTRAINT DISCH_CLASS_U1_IDX
  PRIMARY KEY ( DISCH_CLASS_ID ) 
  );

CREATE INDEX DISCH_CLASS_I1_IDX ON 
  DISCH_CLASS_TAB(NAME);


 
/********************************************************
 * 32 - STRETCH_CLASS_TAB				*
 ********************************************************/

CREATE TABLE STRETCH_CLASS_TAB ( 
  STRETCH_CLASS_ID  NUMERIC (26)   NOT NULL, 
  NAME              VARCHAR (255)        , 
  SS_VAL            VARCHAR (255)        , 
  SS_DIST           VARCHAR (11)         , 
  F_OC              VARCHAR (40)         , 
  PH_VAL            VARCHAR (255)        , 
  PH_DIST           VARCHAR (11)         , 
  K_Z               VARCHAR (40)         , 
  X                 VARCHAR (40)         , 
  DO_VAL            VARCHAR (255)        , 
  DO_DIST           VARCHAR (11)         , 
  TEMP_WATER_VAL    VARCHAR (255)        , 
  TEMP_WATER_DIST   VARCHAR (11)         , 
  TEMP_AIR_VAL      VARCHAR (255)        , 
  TEMP_AIR_DIST     VARCHAR (11)         , 
  V_SED             VARCHAR (40)         , 
  EPS_SED           VARCHAR (40)         , 
  RHO_SED           VARCHAR (40)         , 
  V_WIND10_VAL      VARCHAR (255)        , 
  V_WIND10_DIST     VARCHAR (11)         , 
  REMARK            VARCHAR (2000)       , 
  CONSTRAINT STRETCH_CLASS_U1_IDX
  PRIMARY KEY ( STRETCH_CLASS_ID ) 
  ); 

CREATE INDEX STRETCH_CLASS_I1_IDX ON 
  STRETCH_CLASS_TAB(NAME);


 
/********************************************************
 * 33 - SUBST_TAB					*
 ********************************************************/

CREATE TABLE SUBST_TAB ( 
  SUBST_ID  NUMERIC (26) DEFAULT nextval('SUBST_SEQ'), 
  NAME      VARCHAR (255)  NOT NULL, 
  USER_ID   VARCHAR (30)   NOT NULL, 
  CRE_DATE  DATE            NOT NULL, 
  MOD_DATE  DATE            NOT NULL, 
  STATUS    VARCHAR (30)   NOT NULL, 
  PRIVS     NUMERIC (1)      NOT NULL, 
  IS_TEMPL  VARCHAR (1)    NOT NULL, 
  REMARK    VARCHAR (2000)         , 
  CONSTRAINT SUBST_U1_IDX
  PRIMARY KEY ( SUBST_ID ) 
  );

CREATE INDEX SUBST_I1_IDX ON 
  SUBST_TAB(NAME);

 
/********************************************************
 * 34 - SUBST_DET_TAB					*
 ********************************************************/

CREATE TABLE SUBST_DET_TAB ( 
  SUBST_DET_ID  NUMERIC (26) DEFAULT nextval('SUBST_DET_SEQ'), 
  SUBST_ID      NUMERIC (26)    NOT NULL, 
  FIELD_ID      VARCHAR (30)  NOT NULL, 
  BLOCK_ID      VARCHAR (30)  NOT NULL, 
  BLOCK_RECNO   NUMERIC (3)     NOT NULL, 
  VALUE_SDT     VARCHAR (255)         , 
  DIST_TYPE     VARCHAR (11)          , 
  REMARK        VARCHAR (2000)        , 
  CONSTRAINT SUBST_DET_U1_IDX
  PRIMARY KEY ( SUBST_DET_ID ) 
  );

CREATE INDEX SUBST_DET_I1_IDX ON 
  SUBST_DET_TAB(VALUE_SDT); 


/********************************************************
 * 35 - ENV_TAB						*
 ********************************************************/

CREATE TABLE ENV_TAB ( 
  ENV_ID    NUMERIC (26) DEFAULT nextval('ENV_SEQ'), 
  NAME      VARCHAR (255)  NOT NULL, 
  USER_ID   VARCHAR (30)   NOT NULL, 
  CRE_DATE  DATE            NOT NULL, 
  MOD_DATE  DATE            NOT NULL, 
  STATUS    VARCHAR (30)   NOT NULL, 
  PRIVS     NUMERIC (1)      NOT NULL, 
  IS_TEMPL  VARCHAR (1)    NOT NULL, 
  REMARK    VARCHAR (2000)         , 
  CONSTRAINT ENV_U1_IDX
  PRIMARY KEY ( ENV_ID ) 
  );

CREATE INDEX ENV_I1_IDX ON 
  ENV_TAB(NAME);


/********************************************************
 * 36 - ENV_DET_TAB					*
 ********************************************************/

CREATE TABLE ENV_DET_TAB ( 
  ENV_DET_ID   NUMERIC (26)	DEFAULT nextval('ENV_DET_SEQ'), 
  ENV_ID       NUMERIC (26)    NOT NULL, 
  FIELD_ID     VARCHAR (30)  NOT NULL, 
  BLOCK_ID     VARCHAR (30)  NOT NULL, 
  BLOCK_RECNO  NUMERIC (3)     NOT NULL, 
  VALUE_EDT    VARCHAR (255)         , 
  DIST_TYPE    VARCHAR (11)          , 
  REMARK       VARCHAR (2000)        , 
  CONSTRAINT ENV_DET_U1_IDX
  PRIMARY KEY ( ENV_DET_ID ) 
  );

CREATE INDEX ENV_DET_I1_IDX ON 
  ENV_DET_TAB(VALUE_EDT);


/********************************************************
 * 37 - STRETCH_TAB					*
 ********************************************************/

CREATE TABLE STRETCH_TAB ( 
  STRETCH_ID        NUMERIC (26)       NOT NULL, 
  CATCH_ID          NUMERIC (26)       NOT NULL, 
  STRETCH_CLASS_ID  NUMERIC (26)       NOT NULL, 
  UP1_ID            NUMERIC (26)               , 
  UP2_ID            NUMERIC (26)               , 
  DOWN1_ID          NUMERIC (26)               , 
  DOWN2_ID          NUMERIC (26)               , 
  NAME              VARCHAR (255)            , 
  TYPE              VARCHAR (11)     NOT NULL, 
  Q_VAL             VARCHAR (255)    NOT NULL, 
  Q_DIST            VARCHAR (11)             , 
  V_VAL             VARCHAR (255)    NOT NULL, 
  V_DIST            VARCHAR (11)             , 
  L                 VARCHAR (40)     NOT NULL, 
  D_VAL             VARCHAR (255)    NOT NULL, 
  D_DIST            VARCHAR (11)             , 
  CONSTRAINT STRETCH_U1_IDX
  PRIMARY KEY ( CATCH_ID, STRETCH_ID ) 
  ); 

CREATE INDEX STRETCH_I1_IDX ON 
  STRETCH_TAB(NAME);


/********************************************************
 * 38 - DISCH_TAB					*
 ********************************************************/

CREATE TABLE DISCH_TAB ( 
  DISCH_ID        NUMERIC (26)   NOT NULL, 
  STRETCH_ID      NUMERIC (26)   NOT NULL, 
  CATCH_ID        NUMERIC (26)   NOT NULL, 
  DISCH_CLASS_ID  NUMERIC (26)   NOT NULL, 
  NAME            VARCHAR (255)        , 
  POP             VARCHAR (40)         , 
  DWF             VARCHAR (40)         , 
  FLOW_VAL        VARCHAR (255)        , 
  FLOW_DIST       VARCHAR (11)         , 
  FLOW_DOM        VARCHAR (40)         , 
  FLOW_NONDOM     VARCHAR (40)         , 
  FLOW_RUNOFF     VARCHAR (40)         , 
  TREATED         VARCHAR (40)         , 
  R_WWTP          VARCHAR (40)         , 
  REMARK          VARCHAR (2000)        , 
  CONSTRAINT DISCH_U1_IDX
  PRIMARY KEY ( CATCH_ID, DISCH_ID ) 
  );

CREATE INDEX DISCH_I1_IDX ON 
  DISCH_TAB(NAME); 
 
 
/********************************************************
 * 39 - MARKET_TAB					*
 ********************************************************/

CREATE TABLE MARKET_TAB ( 
  MARKET_ID    NUMERIC (26) DEFAULT nextval('MARKET_SEQ'), 
  DISCH_ID     NUMERIC (26)     NOT NULL, 
  CATCH_ID     NUMERIC (26)     NOT NULL, 
  NAME         VARCHAR (255)  NOT NULL, 
  USER_ID      VARCHAR (30)   NOT NULL, 
  CRE_DATE     DATE            NOT NULL, 
  MOD_DATE     DATE            NOT NULL, 
  STATUS       VARCHAR (30)   NOT NULL, 
  PRIVS        NUMERIC (1)      NOT NULL, 
  CONSUMPTION  VARCHAR (40)           , 
  FLUX_NONDOM  VARCHAR (40)           , 
  FLUX_RUNOFF  VARCHAR (40)           , 
  REMARK       VARCHAR (2000)         , 
  CONSTRAINT MARKET_U1_IDX
  PRIMARY KEY ( CATCH_ID, DISCH_ID, MARKET_ID ) 
  );


/********************************************************
 * 40 - MOD_TAB						*
 ********************************************************/

CREATE TABLE MOD_TAB ( 
  MOD_ID    NUMERIC (26) DEFAULT nextval('MOD_SEQ'), 
  NAME      VARCHAR (255)  NOT NULL, 
  USER_ID   VARCHAR (30)   NOT NULL, 
  CRE_DATE  DATE            NOT NULL, 
  MOD_DATE  DATE            NOT NULL, 
  STATUS    VARCHAR (30)   NOT NULL, 
  PRIVS     NUMERIC (1)      NOT NULL, 
  REMARK    VARCHAR (2000)         , 
  CONSTRAINT MOD_U1_IDX
  PRIMARY KEY ( MOD_ID ) 
  );

CREATE INDEX MOD_I1_IDX ON 
  MOD_TAB(NAME);

 
/********************************************************
 * 41 - MOD_DET_TAB					*
 ********************************************************/

CREATE TABLE MOD_DET_TAB ( 
  MOD_DET_ID   NUMERIC (26) DEFAULT nextval('MOD_DET_SEQ'), 
  MOD_ID       NUMERIC (26)    NOT NULL, 
  FIELD_ID     VARCHAR (30)  NOT NULL, 
  BLOCK_ID     VARCHAR (30)  NOT NULL, 
  BLOCK_RECNO  NUMERIC (3)     NOT NULL, 
  VALUE_MDT    VARCHAR (255)         , 
  DIST_TYPE    VARCHAR (11)          , 
  REMARK       VARCHAR (2000)        ,  
  CONSTRAINT MOD_DET_U1_IDX
  PRIMARY KEY ( MOD_DET_ID ) 
  );

CREATE INDEX MOD_DET_I1_IDX ON 
  MOD_DET_TAB(VALUE_MDT);


/********************************************************
 *  42 - RES_TAB					*
 ********************************************************/

CREATE TABLE RES_TAB ( 
  RES_ID        NUMERIC (26) DEFAULT nextval('RES_SEQ'), 
  RIVER_FACTOR  VARCHAR (40)   NOT NULL, 
  RIVER_UNIT    VARCHAR (255)  NOT NULL, 
  STP_FACTOR    VARCHAR (40)   NOT NULL, 
  STP_UNIT      VARCHAR (255)  NOT NULL, 
  PEC_INITIAL   VARCHAR (2000) NOT NULL, 
  PEC_CATCH     VARCHAR (2000) NOT NULL, 
  REMARK        VARCHAR (2000)         , 
  CONSTRAINT RES_U1_IDX
  PRIMARY KEY ( RES_ID ) 
  );


/********************************************************
 * 43 - RES_PROC() - RETURNS OPAQUE -> 'nothing'	*
 ********************************************************/

CREATE FUNCTION RES_PROCF() RETURNS TRIGGER AS '
BEGIN
  IF TG_OP=''UPDATE'' THEN
    IF OLD.RES_ID  != NEW.RES_ID THEN
      UPDATE  BIN_OBJ_TAB
      SET     OBJECT_ID    = NEW.RES_ID
      WHERE   OBJECT_ID    = OLD.RES_ID
      AND     OBJ_TYPE     = ''RESULT''
      AND (   OBJ_SUB_TYPE = ''DISCH''
           OR OBJ_SUB_TYPE = ''STRETCH'');
    END IF;
  END IF;
  IF TG_OP=''DELETE'' THEN
    DELETE FROM BIN_OBJ_TAB
    WHERE   OBJECT_ID    = OLD.RES_ID
    AND     OBJ_TYPE     = ''RESULT''
    AND (   OBJ_SUB_TYPE = ''DISCH''
         OR OBJ_SUB_TYPE = ''STRETCH'');
  END IF;
  RETURN NEW;
END;
' LANGUAGE 'plpgsql';


/********************************************************
 * 44 - RES_TRG						*
 ********************************************************/

CREATE TRIGGER RES_TRG
 AFTER UPDATE OR DELETE 
 ON RES_TAB
FOR EACH ROW
EXECUTE PROCEDURE RES_PROCF();


/********************************************************
 * 45 - RES_DISCH_TAB					*
 ********************************************************/

CREATE TABLE RES_DISCH_TAB ( 
  RES_DISCH_ID  NUMERIC (26) DEFAULT nextval('RES_DISCH_SEQ'), 
  RES_ID        NUMERIC (26)    NOT NULL, 
  DISCH_ID      NUMERIC (26)    NOT NULL, 
  FIELD_ID      VARCHAR (30)  NOT NULL, 
  BLOCK_ID      VARCHAR (30)  NOT NULL, 
  BLOCK_RECNO   NUMERIC (3)     NOT NULL, 
  VALUE_RDT     VARCHAR (255)         , 
  DIST_TYPE     VARCHAR (11)          , 
  REMARK        VARCHAR (2000)        , 
  CONSTRAINT RES_DISCH_U1_IDX
  PRIMARY KEY ( RES_DISCH_ID ) 
  );

CREATE INDEX RES_DISCH_I1_IDX ON 
  RES_DISCH_TAB(VALUE_RDT);

 
/********************************************************
 * 46 - RES_STRETCH_TAB					*
 ********************************************************/

CREATE TABLE RES_STRETCH_TAB ( 
  RES_STRETCH_ID  NUMERIC (26) DEFAULT nextval('RES_STRETCH_SEQ'), 
  RES_ID          NUMERIC (26)    NOT NULL, 
  STRETCH_ID      NUMERIC (26)    NOT NULL, 
  FIELD_ID        VARCHAR (30)  NOT NULL, 
  BLOCK_ID        VARCHAR (30)  NOT NULL, 
  BLOCK_RECNO     NUMERIC (3)     NOT NULL, 
  VALUE_RST       VARCHAR (255)         , 
  DIST_TYPE       VARCHAR (11)          , 
  REMARK          VARCHAR (2000)        , 
  CONSTRAINT RES_STRETCH_U1_IDX
  PRIMARY KEY ( RES_STRETCH_ID ) 
  );

CREATE INDEX RES_STRETCH_I1_IDX ON 
  RES_STRETCH_TAB(VALUE_RST);

 
/********************************************************
 * 47 - SESS_TAB					*
 ********************************************************/

CREATE TABLE SESS_TAB ( 
  SESS_ID           NUMERIC (26) DEFAULT nextval('SESS_SEQ'), 
  CATCH_ID          NUMERIC (26)            , 
  SUBST_ID          NUMERIC (26)            , 
  ENV_ID            NUMERIC (26)            , 
  MARKET_ID         NUMERIC (26)            , 
  MOD_ID            NUMERIC (26)            , 
  RES_ID            NUMERIC (26)            , 
  NAME              VARCHAR (255) NOT NULL, 
  USER_ID           VARCHAR (30)  NOT NULL, 
  CRE_DATE          DATE           NOT NULL, 
  MOD_DATE          DATE           NOT NULL, 
  STATUS            VARCHAR (30)  NOT NULL, 
  PRIVS             NUMERIC (1)     NOT NULL, 
  IS_TEMP_SESS      VARCHAR (1)   NOT NULL, 
  TEMP_SESS_SRC_ID  NUMERIC (26)            , 
  TEMP_SESS_DB_SID  NUMERIC (26)            , 
  REMARK            VARCHAR (2000)          , 
  SESS_DISCH_ID     NUMERIC (26)            , 
  CONSTRAINT SESS_U1_IDX
  PRIMARY KEY ( SESS_ID ) 
  );

CREATE INDEX SESS_I1_IDX ON 
  SESS_TAB(NAME); 

CREATE INDEX SESS_I2_IDX ON 
  SESS_TAB(MARKET_ID); 

CREATE INDEX SESS_I3_IDX ON 
  SESS_TAB(IS_TEMP_SESS); 

CREATE INDEX SESS_I4_IDX ON 
  SESS_TAB(RES_ID);

 
/********************************************************
 * 48 - SESS_DISCH_TAB					*
 ********************************************************/

CREATE TABLE SESS_DISCH_TAB ( 
  SESS_DISCH_ID   NUMERIC (26) DEFAULT nextval('SESS_DISCH_SEQ'), 
  DISCH_ID        NUMERIC (26)     NOT NULL, 
  STRETCH_ID      NUMERIC (26)     NOT NULL, 
  CATCH_ID        NUMERIC (26)     NOT NULL, 
  DISCH_CLASS_ID  NUMERIC (26)     NOT NULL, 
  NAME            VARCHAR (255)  NOT NULL, 
  USER_ID         VARCHAR (30)   NOT NULL, 
  CRE_DATE        DATE            NOT NULL, 
  MOD_DATE        DATE            NOT NULL, 
  STATUS          VARCHAR (30)   NOT NULL, 
  PRIVS           NUMERIC (1)      NOT NULL, 
  POP             VARCHAR (40)           , 
  DWF             VARCHAR (40)           ,  
  FLOW_VAL        VARCHAR (255)          , 
  FLOW_DIST       VARCHAR (11)           , 
  FLOW_DOM        VARCHAR (40)           , 
  FLOW_NONDOM     VARCHAR (40)           , 
  FLOW_RUNOFF     VARCHAR (40)           , 
  TREATED         VARCHAR (40)           , 
  R_WWTP          VARCHAR (40)           , 
  REMARK          VARCHAR (2000)         , 
  CONSTRAINT SESS_DISCH_U1_IDX
  PRIMARY KEY ( CATCH_ID, DISCH_ID, SESS_DISCH_ID ) 
  );

CREATE INDEX SESS_DISCH_I1_IDX ON 
  SESS_DISCH_TAB(NAME);

/********************************************************
 * 49 - instr() -> function, that operates on Strings	*
 ********************************************************/

 -- 3 alternatives
 -- instr functions that mimic Oracle's counterpart
 -- Syntax: instr(string1,string2,[n],[m]) where [] denotes optional params.
 -- 
 -- Searches string1 beginning at the nth character for the mth
 -- occurrence of string2. If n is negative, search backwards. If m is
 -- not passed, assume 1 (search starts at first character).
 --
 -- by Roberto Mello (rmello@fslc.usu.edu)
 -- modified by Robert Gaszewski (graszew@poland.com)
 -- Licensed under the GPL v2 or later.
 --
 
 CREATE FUNCTION instr(VARCHAR,VARCHAR) RETURNS INTEGER AS '
 DECLARE
     pos integer;
 BEGIN
     pos:= instr($1,$2,1);
     RETURN pos;
 END;
 ' LANGUAGE 'plpgsql';
 
 
 CREATE FUNCTION instr(VARCHAR,VARCHAR,INTEGER) RETURNS INTEGER AS '
 DECLARE
     string ALIAS FOR $1;
     string_to_search ALIAS FOR $2;
     beg_index ALIAS FOR $3;
     pos integer NOT NULL DEFAULT 0;
     temp_str VARCHAR;
     beg INTEGER;
     length INTEGER;
     ss_length INTEGER;
 BEGIN
     IF beg_index > 0 THEN
 
        temp_str := substring(string FROM beg_index);
        pos := position(string_to_search IN temp_str);
 
        IF pos = 0 THEN
          RETURN 0;
      ELSE
          RETURN pos + beg_index - 1;
      END IF;
     ELSE
        ss_length := char_length(string_to_search);
        length := char_length(string);
        beg := length + beg_index - ss_length + 2;
 
        WHILE beg > 0 LOOP
            temp_str := substring(string FROM beg FOR ss_length);
          pos := position(string_to_search IN temp_str);
 
          IF pos > 0 THEN
            RETURN beg;
          END IF;
 
          beg := beg - 1;
        END LOOP;
        RETURN 0;
     END IF;
 END;
 ' LANGUAGE 'plpgsql';
 
 --
 -- Written by Robert Gaszewski (graszew@poland.com)
 -- Licensed under the GPL v2 or later.
 --
 CREATE FUNCTION instr(VARCHAR,VARCHAR,INTEGER,INTEGER) RETURNS INTEGER AS '
 DECLARE
     string ALIAS FOR $1;
     string_to_search ALIAS FOR $2;
     beg_index ALIAS FOR $3;
     occur_index ALIAS FOR $4;
     pos integer NOT NULL DEFAULT 0;
     occur_number INTEGER NOT NULL DEFAULT 0;
     temp_str VARCHAR;
     beg INTEGER;
     i INTEGER;
     length INTEGER;
     ss_length INTEGER;
 BEGIN
     IF beg_index > 0 THEN
         beg := beg_index;
         temp_str := substring(string FROM beg_index);
 
         FOR i IN 1..occur_index LOOP
             pos := position(string_to_search IN temp_str);
 
             IF i = 1 THEN
                 beg := beg + pos - 1;
             ELSE
                 beg := beg + pos;
             END IF;
 
             temp_str := substring(string FROM beg + 1);
         END LOOP;
 
         IF pos = 0 THEN
             RETURN 0;
         ELSE
             RETURN beg;
         END IF;
     ELSE
         ss_length := char_length(string_to_search);
         length := char_length(string);
         beg := length + beg_index - ss_length + 2;
 
         WHILE beg > 0 LOOP
             temp_str := substring(string FROM beg FOR ss_length);
             pos := position(string_to_search IN temp_str);
 
             IF pos > 0 THEN
                 occur_number := occur_number + 1;
 
                 IF occur_number = occur_index THEN
                     RETURN beg;
                 END IF;
             END IF;
 
             beg := beg - 1;
         END LOOP;
 
         RETURN 0;
     END IF;
 END;
 ' LANGUAGE 'plpgsql';


/********************************************************
 * 50 - dbms_init_seq()					*
 * initializes the sequences with the biggest allocated *
 * id that is higher than the MINVALUE of the seq.	*
 ********************************************************/
CREATE FUNCTION dbms_init_seq() 
RETURNS int AS'
	DECLARE
	  v_diff   NUMERIC(26);			-- tmp Variables 
	  v_curr   NUMERIC(26);
	  v_max    NUMERIC(26);
	BEGIN					-- get max id of table 
						-- and current seq value (csv)
	  SELECT coalesce(MAX(ENV_DET_ID),1)      	INTO v_max   FROM ENV_DET_TAB;
	  SELECT nextval(''ENV_DET_SEQ'')		INTO v_curr;

	  IF v_max >= v_curr THEN		-- if csv lower than max id 
	     v_diff := v_max - v_curr;          -- set seq to first possible val 
	     SELECT setval(''ENV_DET_SEQ'',v_diff + 1);
	  END IF;

	  SELECT coalesce(MAX(BIN_OBJ_ID),1)      	INTO v_max   FROM BIN_OBJ_TAB;
	  SELECT nextval(''BIN_OBJ_SEQ'')		INTO v_curr;

	  IF v_max >= v_curr THEN		-- if csv lower than max id 
	     v_diff := v_max - v_curr;          -- set seq to first possible val 
	     SELECT setval(''BIN_OBJ_SEQ'',v_diff + 1);
	  END IF;

	  SELECT coalesce(MAX(ENV_ID),1)      		INTO v_max   FROM ENV_TAB;
	  SELECT nextval(''ENV_SEQ'')			INTO v_curr;

	  IF v_max >= v_curr THEN		-- if csv lower than max id 
	     v_diff := v_max - v_curr;          -- set seq to first possible val 
	     SELECT setval(''ENV_SEQ'',v_diff + 1);
	  END IF;

	  SELECT coalesce(MAX(GROUP_ID),1)      	INTO v_max   FROM GROUP_TAB;
	  SELECT nextval(''GROUP_SEQ'')			INTO v_curr;

	  IF v_max >= v_curr THEN		-- if csv lower than max id 
	     v_diff := v_max - v_curr;          -- set seq to first possible val 
	     SELECT setval(''GROUP_SEQ'',v_diff + 1);
	  END IF;

	  SELECT coalesce(MAX(MARKET_ID),1)      	INTO v_max   FROM MARKET_TAB;
	  SELECT nextval(''MARKET_SEQ'')		INTO v_curr;

	  IF v_max >= v_curr THEN		-- if csv lower than max id 
	     v_diff := v_max - v_curr;          -- set seq to first possible val 
	     SELECT setval(''MARKET_SEQ'',v_diff + 1);
	  END IF;

	  SELECT coalesce(MAX(MOD_DET_ID),1)      	INTO v_max   FROM MOD_DET_TAB;
	  SELECT nextval(''MOD_DET_SEQ'')		INTO v_curr;

	  IF v_max >= v_curr THEN		-- if csv lower than max id 
	     v_diff := v_max - v_curr;          -- set seq to first possible val 
	     SELECT setval(''MOD_DET_SEQ'',v_diff + 1);
	  END IF;

	  SELECT coalesce(MAX(MOD_ID),1)	      	INTO v_max   FROM MOD_TAB;
	  SELECT nextval(''MOD_SEQ'')			INTO v_curr;

	  IF v_max >= v_curr THEN		-- if csv lower than max id 
	     v_diff := v_max - v_curr;          -- set seq to first possible val 
	     SELECT setval(''MOD_SEQ'',v_diff + 1);
	  END IF;

	  SELECT coalesce(MAX(PHRASE_ID),1)      	INTO v_max   FROM PHRASE_TAB;
	  SELECT nextval(''PHRASE_SEQ'')		INTO v_curr;

	  IF v_max >= v_curr THEN		-- if csv lower than max id 
	     v_diff := v_max - v_curr;          -- set seq to first possible val 
	     SELECT setval(''PHRASE_SEQ'',v_diff + 1);
	  END IF;

	  SELECT coalesce(MAX(CATCH_ID),1)      	INTO v_max   FROM CATCH_TAB;
	  SELECT nextval(''CATCH_SEQ'')			INTO v_curr;

	  IF v_max >= v_curr THEN		-- if csv lower than max id 
	     v_diff := v_max - v_curr;          -- set seq to first possible val 
	     SELECT setval(''CATCH_SEQ'',v_diff + 1);
	  END IF;

	  SELECT coalesce(MAX(RES_DISCH_ID),1)      	INTO v_max   FROM RES_DISCH_TAB;
	  SELECT nextval(''RES_DISCH_SEQ'')		INTO v_curr;

	  IF v_max >= v_curr THEN		-- if csv lower than max id 
	     v_diff := v_max - v_curr;          -- set seq to first possible val 
	     SELECT setval(''RES_DISCH_SEQ'',v_diff + 1);
	  END IF;

	  SELECT coalesce(MAX(RES_ID),1)      		INTO v_max   FROM RES_TAB;
	  SELECT nextval(''RES_SEQ'')			INTO v_curr;

	  IF v_max >= v_curr THEN		-- if csv lower than max id 
	     v_diff := v_max - v_curr;          -- set seq to first possible val 
	     SELECT setval(''RES_SEQ'',v_diff + 1);
	  END IF;

  	  SELECT coalesce(MAX(SUBST_DET_ID),1)      	INTO v_max   FROM SUBST_DET_TAB;
	  SELECT nextval(''SUBST_DET_SEQ'')		INTO v_curr;

	  IF v_max >= v_curr THEN		-- if csv lower than max id 
	     v_diff := v_max - v_curr;          -- set seq to first possible val 
	     SELECT setval(''SUBST_DET_SEQ'',v_diff + 1);
	  END IF;

	  SELECT coalesce(MAX(SUBST_ID),1)      	INTO v_max   FROM SUBST_TAB;
	  SELECT nextval(''SUBST_SEQ'')			INTO v_curr;

	  IF v_max >= v_curr THEN		-- if csv lower than max id 
	     v_diff := v_max - v_curr;          -- set seq to first possible val 
	     SELECT setval(''SUBST_SEQ'',v_diff + 1);
	  END IF;

	  SELECT coalesce(MAX(SESS_DISCH_ID),1)      	INTO v_max   FROM SESS_DISCH_TAB;
	  SELECT nextval(''SESS_DISCH_SEQ'')		INTO v_curr;

	  IF v_max >= v_curr THEN		-- if csv lower than max id 
	     v_diff := v_max - v_curr;          -- set seq to first possible val 
	     SELECT setval(''SESS_DISCH_SEQ'',v_diff + 1);
	  END IF;

	  SELECT coalesce(MAX(SESS_ID),1)      		INTO v_max   FROM SESS_TAB;
	  SELECT nextval(''SESS_SEQ'')			INTO v_curr;

	  IF v_max >= v_curr THEN		-- if csv lower than max id 
	     v_diff := v_max - v_curr;          -- set seq to first possible val 
	     SELECT setval(''SESS_SEQ'',v_diff + 1);
	  END IF;

	  SELECT coalesce(MAX(RES_STRETCH_ID),1)      	INTO v_max   FROM RES_STRETCH_TAB;
	  SELECT nextval(''RES_STRETCH_SEQ'')		INTO v_curr;

	  IF v_max >= v_curr THEN		-- if csv lower than max id 
	     v_diff := v_max - v_curr;          -- set seq to first possible val 
	     SELECT setval(''RES_STRETCH_SEQ'',v_diff + 1);
	  END IF;
	  RETURN 1;
	END;
'LANGUAGE 'plpgsql';


/********************************************************
 * 51 - pg_decode(numeric, numeric, numeric, numeric)   *
 *    -> function, that operates Conditional            *
 *       Expressions on numerics.                       *
 *                                                      *
 * Example: pg_decode(var1, -1, 0, var1)                *
 * Falls var1 == -1 ist, dann gib 0 zurueck, sonst var1  *
 ********************************************************/

CREATE FUNCTION pg_decode( numeric, numeric, numeric, numeric ) RETURNS
numeric AS '
DECLARE 
    output_value numeric;
BEGIN
    SELECT CASE WHEN $1=$2 THEN $3
                        ELSE $4
END
   INTO output_value;
   return output_value;
END;
' LANGUAGE 'plpgsql';
