/*Описание синхронизируемых метаданных слоевых таблиц*/

DECLARE
  v_exists INTEGER;
BEGIN
 SELECT count(*) 
    INTO v_exists 
    FROM user_tables 
    WHERE UPPER(Table_Name)='LAYERTABLES';
 IF v_exists=0 THEN

/*Перечень таблиц*/
EXECUTE IMMEDIATE 'CREATE TABLE LayerTables (
    ID               NUMBER(10,0) NOT NULL,
    SYNCID           NUMBER(10,0) NOT NULL,
    TABLENAME        VARCHAR2(50) NOT NULL,
    NAME             VARCHAR2(50) NOT NULL,
    DESCR            VARCHAR2(1024) NOT NULL,
    TBTYPE           NUMBER(10,0),
    SYNC             NUMBER(5,0) NOT NULL,
    SYSTABLE         NUMBER(5,0) NOT NULL,
    URM              NUMBER(5,0) NOT NULL,
    CREATEDATE       DATE NOT NULL,
    CREATERUSER      VARCHAR2(30) NOT NULL,
    UPDATEDATE       DATE NOT NULL,
    UPDATERUSER      VARCHAR2(30) NOT NULL,
    
    CONSTRAINT "PK_LAYERTABLES" PRIMARY KEY (ID),
    CHECK ((ID<=2147483647)AND(ID>-2147483648)),
    CHECK ((SYNCID<=2147483647)AND(SYNCID>-2147483648)),
    CHECK ((TBTYPE<=2147483647)AND(TBTYPE>-2147483648)),
    CHECK ((SYNC<=2147483647)AND(SYNC>-2147483648)),
    CHECK ((SYSTABLE<=2147483647)AND(SYSTABLE>-2147483648)),
    CHECK ((URM<=2147483647)AND(URM>-2147483648))
)';


/*Поля таблиц*/
EXECUTE IMMEDIATE 'CREATE TABLE LAYERTABLESFIELDS (
    ID               NUMBER(10,0) NOT NULL,
    TABLEID          NUMBER(10,0) NOT NULL,
    DBNAME           VARCHAR2(255) NOT NULL,
    DESCR            VARCHAR2(1024),
    FIELDTYPE        NUMBER(10,0),
    FIELDSIZE        NUMBER(10,0),
    SYNC             NUMBER(5,0) NOT NULL,
    URM		           NUMBER(5,0) NOT NULL,
    REQUIRED         NUMBER(5,0) NOT NULL,
    DEFVALUE         VARCHAR2(255),
    SYNCVALUE        VARCHAR2(255),
    CREATEDATE       DATE NOT NULL,
    CREATERUSER      VARCHAR2(30) NOT NULL,
    UPDATEDATE       DATE NOT NULL,
    UPDATERUSER      VARCHAR2(30) NOT NULL,
    
    CONSTRAINT "PK_LAYERTABLESFIELDS" PRIMARY KEY (ID),
    CHECK ((ID<=2147483647)AND(ID>-2147483648)),
    CHECK ((TABLEID<=2147483647)AND(TABLEID>-2147483648)),
    CHECK ((FIELDTYPE<=2147483647)AND(FIELDTYPE>-2147483648)),
    CHECK ((FIELDSIZE<=2147483647)AND(FIELDSIZE>-2147483648)),
    CHECK ((SYNC<=2147483647)AND(SYNC>-2147483648)),
    CHECK ((URM<=2147483647)AND(URM>-2147483648)),
    CHECK ((REQUIRED<=2147483647)AND(REQUIRED>-2147483648))
)';

EXECUTE IMMEDIATE 'CREATE INDEX IDX_LTFIELDS_TABLEID ON LAYERTABLESFIELDS (TableID)';

/*Триггеры таблиц*/
EXECUTE IMMEDIATE 'CREATE TABLE LAYERTABLESTRIGGERS (
    ID               NUMBER(10,0) NOT NULL,
    TABLEID          NUMBER(10,0) NOT NULL,
    DBNAME           VARCHAR2(255) NOT NULL,
    DESCR            VARCHAR2(1024),
    TRTEXT           VARCHAR2(4000) NOT NULL,
    TRTYPE           NUMBER(10,0),
    TRPOSITION       NUMBER(10,0),
    CREATEDATE       DATE NOT NULL,
    CREATERUSER      VARCHAR2(30) NOT NULL,
    UPDATEDATE       DATE NOT NULL,
    UPDATERUSER      VARCHAR2(30) NOT NULL,
    
    CONSTRAINT "PK_LAYERTABLESTRIGGERS" PRIMARY KEY (ID),
    CHECK ((ID<=2147483647)AND(ID>-2147483648)),
    CHECK ((TABLEID<=2147483647)AND(TABLEID>-2147483648)),
    CHECK ((TRTYPE<=2147483647)AND(TRTYPE>-2147483648)),
    CHECK ((TRPOSITION<=2147483647)AND(TRPOSITION>-2147483648))
)';

/*Индексы*/
EXECUTE IMMEDIATE 'CREATE TABLE LAYERTABLESIDX (
    ID               NUMBER(10,0) NOT NULL,
    TABLEID          NUMBER(10,0) NOT NULL,
    DBNAME           VARCHAR2(255) NOT NULL,
    DESCR            VARCHAR2(1024),
    FIELDS           VARCHAR2(512) NOT NULL,
    CREATEDATE       DATE NOT NULL,
    CREATERUSER      VARCHAR2(30) NOT NULL,
    UPDATEDATE       DATE NOT NULL,
    UPDATERUSER      VARCHAR2(30) NOT NULL,
    
    CONSTRAINT "PK_LAYERTABLESIDX" PRIMARY KEY (ID),
    CHECK ((ID<=2147483647)AND(ID>-2147483648)),
    CHECK ((TABLEID<=2147483647)AND(TABLEID>-2147483648))
)';

/*Внешние ключи*/
EXECUTE IMMEDIATE 'CREATE TABLE LAYERTABLESFK (
    ID               NUMBER(10,0) NOT NULL,
    TABLEID          NUMBER(10,0) NOT NULL,
    DBNAME           VARCHAR2(255) NOT NULL,
    DESCR            VARCHAR2(1024),
    FIELDS           VARCHAR2(512) NOT NULL,
    TABLENAME        VARCHAR2(150) NOT NULL,
    FIELDSREF        VARCHAR2(512) NOT NULL,
    CREATEDATE       DATE NOT NULL,
    CREATERUSER      VARCHAR2(30) NOT NULL,
    UPDATEDATE       DATE NOT NULL,
    UPDATERUSER      VARCHAR2(30) NOT NULL,
    
    CONSTRAINT "PK_LAYERTABLESFK" PRIMARY KEY (ID),
    CHECK ((ID<=2147483647)AND(ID>-2147483648)),
    CHECK ((TABLEID<=2147483647)AND(TABLEID>-2147483648))
)';

EXECUTE IMMEDIATE 'COMMIT WORK';

END IF;

END;
/





/* Trigger: TR_LAYERTABLES_BI */
CREATE OR REPLACE TRIGGER TR_LAYERTABLES_BI before insert ON LAYERTABLES for each row
BEGIN
  if (:new.id is null) then select G_SHARED.nextval into :new.Id from DUAL; end if;
  
  :NEW.CreateDate := SYSDATE;
  :NEW.UpdateDate := :NEW.CreateDate;
  :NEW.CreaterUser := USER;
  :NEW.UpdaterUser := USER;
END;
/
ALTER TRIGGER TR_LAYERTABLES_BI ENABLE;
/

/* Trigger: TR_LAYERTABLES_BU_500 */
CREATE OR REPLACE TRIGGER TR_LAYERTABLES_BU_500 before update ON LAYERTABLES for each row
begin
  :new.UpdateDate := SYSDATE;
  :new.UpdaterUser := User;
end;
/
ALTER TRIGGER TR_LAYERTABLES_BU_500 ENABLE;
/

/* Trigger: TR_LAYERTABLES_AD_500 */
CREATE OR REPLACE TRIGGER TR_LAYERTABLES_AD_500 after delete ON LAYERTABLES FOR EACH ROW
begin
    insert into DelRecIndexes(TableID, RecordId, DeleteDate) values (240, :old.ID, SYSDATE) ;
end;
/
ALTER TRIGGER TR_LAYERTABLES_AD_500 ENABLE;
/

/* Trigger: TR_LAYERTABLES_AI_500 */
CREATE OR REPLACE TRIGGER TR_LAYERTABLES_AI_500 after insert ON LAYERTABLES FOR EACH ROW
begin
  delete from DelRecIndexes where (TableID = 240) and (RecordID = :new.ID);
end;
/
ALTER TRIGGER TR_LAYERTABLES_AI_500 ENABLE;
/

/* Trigger: TR_LAYERTABLES_AU_500 */
CREATE OR REPLACE TRIGGER TR_LAYERTABLES_AU_500 after update ON LAYERTABLES FOR EACH ROW
BEGIN
  IF (:NEW.ID<>:OLD.ID) THEN
    INSERT INTO DelRecIndexes(TableID, RecordId, DeleteDate) VALUES (240, :OLD.ID, SYSDATE);
    DELETE FROM DelRecIndexes WHERE (TableID=240) AND (RecordId=:NEW.ID);
  END IF;
END;
/
ALTER TRIGGER TR_LAYERTABLES_AU_500 ENABLE;
/

/* Trigger: TR_LAYERTABLES_BD */
CREATE OR REPLACE TRIGGER TR_LAYERTABLES_BD BEFORE DELETE ON LAYERTABLES FOR EACH ROW
BEGIN
    DELETE FROM LAYERTABLESFIELDS WHERE (TableID = :OLD.ID);
    DELETE FROM LAYERTABLESTRIGGERS WHERE (TableID = :OLD.ID);
    DELETE FROM LAYERTABLESIDX WHERE (TableID = :OLD.ID);
    DELETE FROM LAYERTABLESFK WHERE (TableID = :OLD.ID);
END;
/
ALTER TRIGGER TR_LAYERTABLES_BD ENABLE;
/


/* Trigger: TR_LAYERTABLESFIELDS_BI */
CREATE OR REPLACE TRIGGER TR_LAYERTABLESFIELDS_BI before insert ON LAYERTABLESFIELDS for each row
BEGIN
  if (:new.id is null) then select G_SHARED.nextval into :new.Id from DUAL; end if;
    
  :NEW.CreateDate := SYSDATE;
  :NEW.UpdateDate := :NEW.CreateDate;
  :NEW.CreaterUser := USER;
  :NEW.UpdaterUser := USER;
END;
/
ALTER TRIGGER TR_LAYERTABLESFIELDS_BI ENABLE;
/


/* Trigger: TR_LAYERTABLESFIELDS_BU_500 */
CREATE OR REPLACE TRIGGER TR_LAYERTABLESFIELDS_BU_500 before update ON LAYERTABLESFIELDS for each row
begin
  :new.UpdateDate := SYSDATE;
  :new.UpdaterUser := User;
end;
/
ALTER TRIGGER TR_LAYERTABLESFIELDS_BU_500 ENABLE;
/

/* Trigger: TR_LAYERTABLESFIELDS_AD_500 */
CREATE OR REPLACE TRIGGER TR_LAYERTABLESFIELDS_AD_500 after delete ON LAYERTABLESFIELDS FOR EACH ROW
begin
    insert into DelRecIndexes(TableID, RecordId, DeleteDate) values (241, :old.ID, SYSDATE) ;
end;
/
ALTER TRIGGER TR_LAYERTABLESFIELDS_AD_500 ENABLE;
/

/* Trigger: TR_LAYERTABLESFIELDS_AI_500 */
CREATE OR REPLACE TRIGGER TR_LAYERTABLESFIELDS_AI_500 after insert ON LAYERTABLESFIELDS FOR EACH ROW
begin
  delete from DelRecIndexes where (TableID = 241) and (RecordID = :new.ID);
end;
/
ALTER TRIGGER TR_LAYERTABLESFIELDS_AI_500 ENABLE;
/

/* Trigger: TR_LAYERTABLESFIELDS_AU_500 */
CREATE OR REPLACE TRIGGER TR_LAYERTABLESFIELDS_AU_500 after update ON LAYERTABLESFIELDS FOR EACH ROW
BEGIN
  IF (:NEW.ID<>:OLD.ID) THEN
    INSERT INTO DelRecIndexes(TableID, RecordId, DeleteDate) VALUES (241, :OLD.ID, SYSDATE);
    DELETE FROM DelRecIndexes WHERE (TableID=241) AND (RecordId=:NEW.ID);
  END IF;
END;
/
ALTER TRIGGER TR_LAYERTABLESFIELDS_AU_500 ENABLE;
/



/* Trigger: TR_LAYERTABLESTRIGGERS_BI */
CREATE OR REPLACE TRIGGER TR_LAYERTABLESTRIGGERS_BI before insert ON LAYERTABLESTRIGGERS for each row
BEGIN
  if (:new.id is null) then select G_SHARED.nextval into :new.Id from DUAL; end if;
   
  :NEW.CreateDate := SYSDATE;
  :NEW.UpdateDate := :NEW.CreateDate;
  :NEW.CreaterUser := USER;
  :NEW.UpdaterUser := USER;
END;
/
ALTER TRIGGER TR_LAYERTABLESTRIGGERS_BI ENABLE;
/

/* Trigger: TR_LAYERTABLESTRIGGERS_BU_500 */
CREATE OR REPLACE TRIGGER TR_LAYERTABLESTRIGGERS_BU_500 before update ON LAYERTABLESTRIGGERS for each row
begin
  :new.UpdateDate := SYSDATE;
  :new.UpdaterUser := User;
end;
/
ALTER TRIGGER TR_LAYERTABLESTRIGGERS_BU_500 ENABLE;
/

/* Trigger: TR_LAYERTABLESTRIGGERS_AD_500 */
CREATE OR REPLACE TRIGGER TR_LAYERTABLESTRIGGERS_AD_500 after delete ON LAYERTABLESTRIGGERS FOR EACH ROW
begin
    insert into DelRecIndexes(TableID, RecordId, DeleteDate) values (242, :old.ID, SYSDATE) ;
end;
/
ALTER TRIGGER TR_LAYERTABLESTRIGGERS_AD_500 ENABLE;
/


/* Trigger: TR_LAYERTABLESTRIGGERS_AI_500 */
CREATE OR REPLACE TRIGGER TR_LAYERTABLESTRIGGERS_AI_500 after insert ON LAYERTABLESTRIGGERS FOR EACH ROW
begin
  delete from DelRecIndexes where (TableID = 242) and (RecordID = :new.ID);
end;
/
ALTER TRIGGER TR_LAYERTABLESTRIGGERS_AI_500 ENABLE;
/

/* Trigger: TR_LAYERTABLESTRIGGERS_AU_500 */
CREATE OR REPLACE TRIGGER TR_LAYERTABLESTRIGGERS_AU_500 after update ON LAYERTABLESTRIGGERS FOR EACH ROW
BEGIN
  IF (:NEW.ID<>:OLD.ID) THEN
    INSERT INTO DelRecIndexes(TableID, RecordId, DeleteDate) VALUES (242, :OLD.ID, SYSDATE);
    DELETE FROM DelRecIndexes WHERE (TableID=242) AND (RecordId=:NEW.ID);
  END IF;
END;
/
ALTER TRIGGER TR_LAYERTABLESTRIGGERS_AU_500 ENABLE;
/



/* Trigger: TR_LAYERTABLESIDX_BI */
CREATE OR REPLACE TRIGGER TR_LAYERTABLESIDX_BI before insert ON LAYERTABLESIDX for each row
BEGIN
  if (:new.id is null) then select G_SHARED.nextval into :new.Id from DUAL; end if;
    
  :NEW.CreateDate := SYSDATE;
  :NEW.UpdateDate := :NEW.CreateDate;
  :NEW.CreaterUser := USER;
  :NEW.UpdaterUser := USER;
END;
/
ALTER TRIGGER TR_LAYERTABLESIDX_BI ENABLE;
/

/* Trigger: TR_LAYERTABLESTRIGGERS_BU_500 */
CREATE OR REPLACE TRIGGER TR_LAYERTABLESIDX_BU_500 before update ON LAYERTABLESIDX for each row
begin
  :new.UpdateDate := SYSDATE;
  :new.UpdaterUser := User;
end;
/
ALTER TRIGGER TR_LAYERTABLESIDX_BU_500 ENABLE;
/

/* Trigger: TR_LAYERTABLESIDX_AD_500 */
CREATE OR REPLACE TRIGGER TR_LAYERTABLESIDX_AD_500 after delete ON LAYERTABLESIDX FOR EACH ROW
begin
    insert into DelRecIndexes(TableID, RecordId, DeleteDate) values (243, :old.ID, SYSDATE) ;
end;
/
ALTER TRIGGER TR_LAYERTABLESIDX_AD_500 ENABLE;
/

/* Trigger: TR_LAYERTABLESTRIGGERS_AI_500 */
CREATE OR REPLACE TRIGGER TR_LAYERTABLESIDX_AI_500 after insert ON LAYERTABLESIDX FOR EACH ROW
begin
  delete from DelRecIndexes where (TableID = 243) and (RecordID = :new.ID);
end;
/
ALTER TRIGGER TR_LAYERTABLESIDX_AI_500 ENABLE;
/

/* Trigger: TR_LAYERTABLESTRIGGERS_AU_500 */
CREATE OR REPLACE TRIGGER TR_LAYERTABLESIDX_AU_500 after update ON LAYERTABLESIDX FOR EACH ROW
BEGIN
  IF (:NEW.ID<>:OLD.ID) THEN
    INSERT INTO DelRecIndexes(TableID, RecordId, DeleteDate) VALUES (243, :OLD.ID, SYSDATE);
    DELETE FROM DelRecIndexes WHERE (TableID=243) AND (RecordId=:NEW.ID);
  END IF;
END;
/
ALTER TRIGGER TR_LAYERTABLESIDX_AU_500 ENABLE;
/



/* Trigger: TR_LAYERTABLESIDX_BI */
CREATE OR REPLACE TRIGGER TR_LAYERTABLESFK_BI before insert ON LAYERTABLESFK for each row
BEGIN
  if (:new.id is null) then select G_SHARED.nextval into :new.Id from DUAL; end if;
   
  :NEW.CreateDate := SYSDATE;
  :NEW.UpdateDate := :NEW.CreateDate;
  :NEW.CreaterUser := USER;
  :NEW.UpdaterUser := USER;
END;
/
ALTER TRIGGER TR_LAYERTABLESFK_BI ENABLE;
/

/* Trigger: TR_LAYERTABLESTRIGGERS_BU_500 */
CREATE OR REPLACE TRIGGER TR_LAYERTABLESFK_BU_500 before update ON LAYERTABLESFK for each row
begin
  :new.UpdateDate := SYSDATE;
  :new.UpdaterUser := User;
end;
/
ALTER TRIGGER TR_LAYERTABLESFK_BU_500 ENABLE;
/

/* Trigger: TR_LAYERTABLESFK_AD_500 */
CREATE OR REPLACE TRIGGER TR_LAYERTABLESFK_AD_500 after delete ON LAYERTABLESFK FOR EACH ROW
begin
    insert into DelRecIndexes(TableID, RecordId, DeleteDate) values (244, :old.ID, SYSDATE) ;
end;
/
ALTER TRIGGER TR_LAYERTABLESFK_AD_500 ENABLE;
/

/* Trigger: TR_LAYERTABLESTRIGGERS_AI_500 */
CREATE OR REPLACE TRIGGER TR_LAYERTABLESFK_AI_500 after insert ON LAYERTABLESFK FOR EACH ROW
begin
  delete from DelRecIndexes where (TableID = 244) and (RecordID = :new.ID);
end;
/
ALTER TRIGGER TR_LAYERTABLESFK_AI_500 ENABLE;
/

/* Trigger: TR_LAYERTABLESTRIGGERS_AU_500 */
CREATE OR REPLACE TRIGGER TR_LAYERTABLESFK_AU_500 after update ON LAYERTABLESFK FOR EACH ROW
BEGIN
  IF (:NEW.ID<>:OLD.ID) THEN
    INSERT INTO DelRecIndexes(TableID, RecordId, DeleteDate) VALUES (244, :OLD.ID, SYSDATE);
    DELETE FROM DelRecIndexes WHERE (TableID=244) AND (RecordId=:NEW.ID);
  END IF;
END;
/
ALTER TRIGGER TR_LAYERTABLESFK_AU_500 ENABLE;
/



GRANT ALL ON LAYERTABLES TO PUBLIC;
/
GRANT ALL ON LAYERTABLESFIELDS TO PUBLIC;
/
GRANT ALL ON LAYERTABLESTRIGGERS TO PUBLIC;
/
GRANT ALL ON LAYERTABLESIDX TO PUBLIC;
/
GRANT ALL ON LAYERTABLESFK TO PUBLIC;
/

COMMIT WORK;

