DROP TABLE MG_CONTROL CASCADE CONSTRAINTS;

-- MG_CONTROL  (Table) 
--
CREATE TABLE MG_CONTROL (
  FDO_UNIQUE_ID                INTEGER          NOT NULL,
  FDO_ORA_OWNER                VARCHAR2(64 BYTE) NOT NULL,
  FDO_ORA_NAME                 VARCHAR2(64 BYTE) NOT NULL,
  FDO_ORA_GEOMCOLUMN           VARCHAR2(1024 BYTE) NOT NULL,
  FDO_SPATIALTABLE_OWNER       VARCHAR2(64 BYTE)     NULL,
  FDO_SPATIALTABLE_NAME        VARCHAR2(64 BYTE)     NULL,
  FDO_SPATIALTABLE_GEOMCOLUMN  VARCHAR2(1024 BYTE)     NULL,
  FDO_CLASS_NAME               VARCHAR2(256 BYTE) NOT NULL,
  FDO_SRID                     NUMBER           NOT NULL,
  FDO_DIMINFO                  MDSYS.SDO_DIM_ARRAY     NULL,
  FDO_CS_NAME                  VARCHAR2(256 BYTE)     NULL,
  FDO_WKTEXT                   VARCHAR2(2046 BYTE)     NULL,
  FDO_LAYER_GTYPE              VARCHAR2(64 BYTE)     NULL,
  FDO_SEQUENCE_NAME            VARCHAR2(64 BYTE)     NULL,
  FDO_IDENTITY                 VARCHAR2(1024 BYTE) NOT NULL,
  FDO_SDO_ROOT_MBR             MDSYS.SDO_GEOMETRY     NULL,
  FDO_POINT_X_COLUMN           VARCHAR2(128 BYTE)     NULL,
  FDO_POINT_Y_COLUMN           VARCHAR2(128 BYTE)     NULL,
  FDO_POINT_Z_COLUMN           VARCHAR2(128 BYTE)     NULL,
  FDO_SPATIAL_CONTEXT          VARCHAR2(128 BYTE)     NULL
)
LOGGING 
NOCOMPRESS 
NOPARALLEL
MONITORING;

COMMENT ON COLUMN MG_CONTROL.FDO_UNIQUE_ID IS 'Unique number for record';
COMMENT ON COLUMN MG_CONTROL.FDO_ORA_OWNER IS 'Schema name of the database object to expose' ;
COMMENT ON COLUMN MG_CONTROL.FDO_ORA_NAME IS 'Database object that is associated to the Mapguide layer' ;
COMMENT ON COLUMN MG_CONTROL.FDO_ORA_GEOMCOLUMN IS 'Geometry column' ;
COMMENT ON COLUMN MG_CONTROL.FDO_CLASS_NAME IS 'Name to be exposed to mapguide for the object' ;
COMMENT ON COLUMN MG_CONTROL.FDO_SRID IS 'SRID of the geometry data.' ;
COMMENT ON COLUMN MG_CONTROL.FDO_IDENTITY IS 'Primary key in source object' ;

DROP SEQUENCE MG_CONTROL_SEQ;

-- MG_CONTROL_SEQ  (Sequence) 
--
CREATE SEQUENCE MG_CONTROL_SEQ START WITH 1 MAXVALUE 99999999999 MINVALUE 1 NOCYCLE NOCACHE NOORDER;

-- MG_CONTROL_TRG  (Trigger) 
--
--  Dependencies: 
--   MG_CONTROL (Table)
--
CREATE OR REPLACE TRIGGER MG_CONTROL_TRG
BEFORE INSERT ON MG_CONTROL FOR EACH ROW
WHEN (
new.FDO_UNIQUE_ID IS NULL
      )
BEGIN
  -- insert the next primary key value and the current date/time
  SELECT MG_CONTROL_SEQ.NEXTVAL
  INTO   :new.FDO_UNIQUE_ID
  FROM   dual;
END;
/