
set timing on;

PROMPT Creating Table 'FITS'
CREATE TABLE FITS
 (VERSION VARCHAR2(20) NOT NULL
 ,BUNDLE_ID VARCHAR2(50) NOT NULL
 ,SIDE VARCHAR2(10) NOT NULL
 ,POSITION_ON_COLDEND NUMBER(38) NOT NULL
 ,PIXEL NUMBER(38) NOT NULL
 ,CREATE_DATE DATE NOT NULL
 ,CREATE_USER VARCHAR2(25) NOT NULL
 ,OBSOLETE NUMBER(38) NOT NULL
 ,GUESSED NUMBER(38)
 ,QE1 NUMBER
 ,QE2 NUMBER
 ,QE3 NUMBER
 ,QE4 NUMBER
 ,QE5 NUMBER
 ,QE6 NUMBER
 ,QECHI2 NUMBER
 ,Q_GUESSED NUMBER(2)
 ,G1 NUMBER
 ,G2 NUMBER
 ,GCHI2 NUMBER
 ,G_GUESSED NUMBER(2)
 ,DC1 NUMBER
 ,DC2 NUMBER
 ,DCCHI2 NUMBER
 ,DC_GUESSED NUMBER(2)
 ,EN1 NUMBER
 ,EN2 NUMBER
 ,ENCHI2 NUMBER
 ,EN_GUESSED NUMBER(2)
 ,GD NUMBER
 ,GX NUMBER
 ,R NUMBER
 ,GX_GUESSED NUMBER(2)
 ,R_GUESSED NUMBER(2)
 ,F NUMBER
 ,VREF NUMBER
 ,VMIN NUMBER
 ,VMAX NUMBER
 ,VBD NUMBER
 )
 PCTFREE 10
 STORAGE
 (
 INITIAL 120M
 NEXT 120M
 MINEXTENTS 1
 MAXEXTENTS 10
 )
 TABLESPACE FIBERT_DATA
/

COMMENT ON COLUMN FITS.VERSION IS 'Version of Parametrisation Program'
/

COMMENT ON COLUMN FITS.BUNDLE_ID IS 'Bundle Serial Number'
/

COMMENT ON COLUMN FITS.SIDE IS 'RH or LH'
/

COMMENT ON COLUMN FITS.POSITION_ON_COLDEND IS 'VLPC Position on Cold End (1 through 8)'
/

COMMENT ON COLUMN FITS.PIXEL IS 'Pixel Number'
/

COMMENT ON COLUMN FITS.CREATE_DATE IS 'Date record was inserted'
/

COMMENT ON COLUMN FITS.CREATE_USER IS 'User that create the entry'
/

COMMENT ON COLUMN FITS.OBSOLETE IS 'non-zero if obsolete'
/

COMMENT ON COLUMN FITS.GUESSED IS '0: all pars are fits to data, 1: some pars are guesses, 2: all pars are guesses'
/

COMMENT ON COLUMN FITS.QE1 IS 'Parameters for QE Fit'
/

COMMENT ON COLUMN FITS.Q_GUESSED IS 'QE pars are taken from neighbour'
/

COMMENT ON COLUMN FITS.G1 IS 'Parameters for Gain Fit'
/

COMMENT ON COLUMN FITS.G_GUESSED IS 'G pars are taken from neighbour'
/

COMMENT ON COLUMN FITS.DC1 IS 'Parameters for Dark Count Fit'
/

COMMENT ON COLUMN FITS.DC_GUESSED IS 'DC pars are taken from neighbour'
/

COMMENT ON COLUMN FITS.EN1 IS 'Parameters for Electronics Noise Fit'
/

COMMENT ON COLUMN FITS.EN_GUESSED IS 'EN pars are taken from neighbour'
/

COMMENT ON COLUMN FITS.GD IS 'Gain dispersion (in pe)'
/

COMMENT ON COLUMN FITS.GX IS 'Gain scaling factor (for noise spectrum)'
/

COMMENT ON COLUMN FITS.R IS 'Background Rate (single pe)'
/

COMMENT ON COLUMN FITS.GX_GUESSED IS 'GX is taken from neighbour'
/

COMMENT ON COLUMN FITS.R_GUESSED IS 'R is taken from neighbour'
/

COMMENT ON COLUMN FITS.F IS 'Background Pulser Frequency (MHz)'
/

COMMENT ON COLUMN FITS.VREF IS 'Voltage at which backgr. rate was determined'
/

COMMENT ON COLUMN FITS.VMIN IS 'Voltage Range in which the parametrisation is valid'
/

COMMENT ON COLUMN FITS.VBD IS 'Breakdown Voltage'
/

PROMPT Altering Table 'CASSETTE_PARAMETERS'
ALTER TABLE CASSETTE_PARAMETERS ADD (USE_FOR_PAR NUMBER(38))
/

COMMENT ON COLUMN CASSETTE_PARAMETERS.USE_FOR_PAR IS 'If non-zero, run is used for parametrisation'
/


PROMPT Altering Table 'CHANNELS'
COMMENT ON COLUMN CHANNELS.STATUS IS '0 for OK, 1 for dead, 2 for shorted, 3 for noisy'
/

PROMPT Creating Index 'FIT_CHANNEL_FK_I'
CREATE INDEX FIT_CHANNEL_FK_I ON FITS
 (BUNDLE_ID
 ,POSITION_ON_COLDEND
 ,SIDE
 ,PIXEL)
 PCTFREE 10
 STORAGE
 (
 INITIAL 50M
 NEXT 50M
 MINEXTENTS 1
 MAXEXTENTS 10
 )
 TABLESPACE FIBERT_IDX
/

PROMPT Creating Primary Key on 'FITS'
ALTER TABLE FITS
 ADD CONSTRAINT FIT_PK PRIMARY KEY 
  (VERSION
  ,POSITION_ON_COLDEND
  ,SIDE
  ,PIXEL
  ,BUNDLE_ID)
 USING INDEX 
 PCTFREE 10
 STORAGE
 (
 INITIAL 3M
 NEXT 3M
 MINEXTENTS 1
 MAXEXTENTS 10
 )
 TABLESPACE FIBERT_IDX
/

PROMPT Creating Foreign Keys on 'FITS'
ALTER TABLE FITS ADD CONSTRAINT
 FIT_CHANNEL_FK FOREIGN KEY 
  (POSITION_ON_COLDEND
  ,SIDE
  ,PIXEL
  ,BUNDLE_ID) REFERENCES CHANNELS
  (POSITION_ON_COLDEND
  ,SIDE
  ,PIXEL
  ,BUNDLE_ID)
/

PROMPT Creating Synonym 'FITS' 
CREATE PUBLIC SYNONYM FITS FOR FITS
/

GRANT DELETE, INSERT, SELECT, UPDATE ON FITS TO VLPC_CASSETTE_WRITE
/

GRANT SELECT ON FITS TO VLPC_CASSETTE_READ
/
