MTDDB/3d.sql at main · CMSROMA/MTDDB · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145


/*
        To be run as CMS_MTD_CORE_COND
*/

INSERT INTO CMS_MTD_CORE_COND.KINDS_OF_CONDITIONS (NAME, IS_RECORD_DELETED, EXTENSION_TABLE_NAME, COMMENT_DESCRIPTION) VALUES ('XTAL DIMENSIONS', 'F', 'XTAL_DIMENSIONS', '3D measurement of a crystal');
/*
	To be run as CMS_MTD_TMING_COND
*/

DROP TABLE CMS_MTD_TMING_COND.XTAL_DIMENSIONS CASCADE CONSTRAINTS;

CREATE TABLE CMS_MTD_TMING_COND.XTAL_DIMENSIONS
(
  RECORD_ID                NUMBER(38)	NOT NULL,
  CONDITION_DATA_SET_ID	   NUMBER(38)	NOT NULL,
  BARLENGTH	NUMBER,
  BARLENGTH_STD	NUMBER,
  LMAXVAR_LS	NUMBER,
  LMAXVAR_LN	NUMBER,
  LMAXVAR_LS_STD NUMBER,
  LMAXVAR_LN_STD NUMBER,
  L_MAX	NUMBER,
  L_MEAN	NUMBER,
  L_MEAN_STD	NUMBER,
  WMAXVAR_LO	NUMBER,
  WMAXVAR_LE	NUMBER,
  WMAXVAR_LO_STD    NUMBER,
  WMAXVAR_LE_STD    NUMBER,
  W_MAX	NUMBER,
  W_MEAN	NUMBER,
  W_MEAN_STD	NUMBER,
  TMAXVAR_FS	NUMBER,
  TMAXVAR_FS_STD	NUMBER,
  T_MAX	NUMBER,
  T_MEAN	NUMBER,
  T_MEAN_STD	NUMBER,
  L_MEAN_MITU	NUMBER,
  L_STD_MITU	NUMBER,
  W_MEAN_MITU	NUMBER,
  W_STD_MITU	NUMBER,
  T_MEAN_MITU	NUMBER,
  T_STD_MITU	NUMBER
)
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          10M
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE;

CREATE UNIQUE INDEX CMS_MTD_TMING_COND.XTL_DMNSNS_PK ON CMS_MTD_TMING_COND.XTAL_DIMENSIONS (RECORD_ID);

ALTER TABLE CMS_MTD_TMING_COND.XTAL_DIMENSIONS ADD (
  CONSTRAINT XTL_DMNSNS_PK
  PRIMARY KEY
  (RECORD_ID)
  USING INDEX CMS_MTD_TMING_COND.XTL_DMNSNS_PK
  ENABLE VALIDATE);


CREATE INDEX CMS_MTD_TMING_COND.XTL_DMNSNS_FK_I ON CMS_MTD_TMING_COND.XTAL_DIMENSIONS (CONDITION_DATA_SET_ID);

ALTER TABLE CMS_MTD_TMING_COND.XTAL_DIMENSIONS ADD (
  CONSTRAINT XTL_DMNSNS_FK
  FOREIGN KEY (CONDITION_DATA_SET_ID)
  REFERENCES CMS_MTD_CORE_COND.COND_DATA_SETS (CONDITION_DATA_SET_ID)
  ENABLE VALIDATE);

GRANT SELECT ON CMS_MTD_TMING_COND.XTAL_DIMENSIONS TO CMS_MTD_TMING_CONDITION_READER;

GRANT SELECT ON CMS_MTD_TMING_COND.XTAL_DIMENSIONS TO PUBLIC;

GRANT INSERT ON CMS_MTD_TMING_COND.XTAL_DIMENSIONS TO CMS_MTD_CORE_CONDITION_WRITER;
GRANT UPDATE ON CMS_MTD_TMING_COND.XTAL_DIMENSIONS TO CMS_MTD_CORE_CONDITION_WRITER;
GRANT INSERT ON CMS_MTD_TMING_COND.XTAL_DIMENSIONS TO CMS_MTD_PRTYP_TMING_WRT;
GRANT UPDATE ON CMS_MTD_TMING_COND.XTAL_DIMENSIONS TO CMS_MTD_PRTYP_TMING_WRT;

INSERT INTO CMS_MTD_CORE_COND.COND_TO_PART_RLTNSHPS
  (KIND_OF_PART_ID, KIND_OF_CONDITION_ID, DISPLAY_NAME, IS_RECORD_DELETED)
  VALUES ((SELECT KIND_OF_PART_ID FROM CMS_MTD_CORE_CONSTRUCT.KINDS_OF_PARTS WHERE
           DISPLAY_NAME = 'singleCrystal #1' AND IS_RECORD_DELETED = 'F'),
           (SELECT KIND_OF_CONDITION_ID FROM
           CMS_MTD_CORE_COND.KINDS_OF_CONDITIONS WHERE EXTENSION_TABLE_NAME = 'XTAL_DIMENSIONS' AND
           NAME = 'XTAL DIMENSIONS'), '3D measurement of a crystal for singleCrystal #1', 'F');
INSERT INTO CMS_MTD_CORE_COND.COND_TO_PART_RLTNSHPS
  (KIND_OF_PART_ID, KIND_OF_CONDITION_ID, DISPLAY_NAME, IS_RECORD_DELETED)
  VALUES ((SELECT KIND_OF_PART_ID FROM CMS_MTD_CORE_CONSTRUCT.KINDS_OF_PARTS WHERE
           DISPLAY_NAME = 'singleCrystal #2' AND IS_RECORD_DELETED = 'F'),
           (SELECT KIND_OF_CONDITION_ID FROM
           CMS_MTD_CORE_COND.KINDS_OF_CONDITIONS WHERE EXTENSION_TABLE_NAME = 'XTAL_DIMENSIONS' AND
           NAME = 'XTAL DIMENSIONS'), '3D measurement of a crystal for singleCrystal #2', 'F');
INSERT INTO CMS_MTD_CORE_COND.COND_TO_PART_RLTNSHPS
  (KIND_OF_PART_ID, KIND_OF_CONDITION_ID, DISPLAY_NAME, IS_RECORD_DELETED)
  VALUES ((SELECT KIND_OF_PART_ID FROM CMS_MTD_CORE_CONSTRUCT.KINDS_OF_PARTS WHERE
           DISPLAY_NAME = 'singleCrystal #3' AND IS_RECORD_DELETED = 'F'),
           (SELECT KIND_OF_CONDITION_ID FROM
           CMS_MTD_CORE_COND.KINDS_OF_CONDITIONS WHERE EXTENSION_TABLE_NAME = 'XTAL_DIMENSIONS' AND
           NAME = 'XTAL DIMENSIONS'), '3D measurement of a crystal for singleCrystal #3', 'F');
INSERT INTO CMS_MTD_CORE_COND.COND_TO_PART_RLTNSHPS
  (KIND_OF_PART_ID, KIND_OF_CONDITION_ID, DISPLAY_NAME, IS_RECORD_DELETED)
  VALUES ((SELECT KIND_OF_PART_ID FROM CMS_MTD_CORE_CONSTRUCT.KINDS_OF_PARTS WHERE
           DISPLAY_NAME = 'singleCrystal #1' AND IS_RECORD_DELETED = 'F'),
           (SELECT KIND_OF_CONDITION_ID FROM
           CMS_MTD_CORE_COND.KINDS_OF_CONDITIONS WHERE EXTENSION_TABLE_NAME = 'XTAL_DIMENSIONS' AND
           NAME = 'XTAL DIMENSIONS'), '3D measurement of a crystal for singleCrystal #1', 'F');
INSERT INTO CMS_MTD_CORE_COND.COND_TO_PART_RLTNSHPS
  (KIND_OF_PART_ID, KIND_OF_CONDITION_ID, DISPLAY_NAME, IS_RECORD_DELETED)
  VALUES ((SELECT KIND_OF_PART_ID FROM CMS_MTD_CORE_CONSTRUCT.KINDS_OF_PARTS WHERE
           DISPLAY_NAME = 'singleBarCrystal' AND IS_RECORD_DELETED = 'F'),
           (SELECT KIND_OF_CONDITION_ID FROM
           CMS_MTD_CORE_COND.KINDS_OF_CONDITIONS WHERE EXTENSION_TABLE_NAME = 'XTAL_DIMENSIONS' AND
           NAME = 'XTAL DIMENSIONS'), '3D measurement of a crystal for singleBarCrystal', 'F');
INSERT INTO CMS_MTD_CORE_COND.COND_TO_PART_RLTNSHPS
  (KIND_OF_PART_ID, KIND_OF_CONDITION_ID, DISPLAY_NAME, IS_RECORD_DELETED)
  VALUES ((SELECT KIND_OF_PART_ID FROM CMS_MTD_CORE_CONSTRUCT.KINDS_OF_PARTS WHERE
           DISPLAY_NAME = 'LYSOMatrix #3' AND IS_RECORD_DELETED = 'F'),
           (SELECT KIND_OF_CONDITION_ID FROM
           CMS_MTD_CORE_COND.KINDS_OF_CONDITIONS WHERE EXTENSION_TABLE_NAME = 'XTAL_DIMENSIONS' AND
           NAME = 'XTAL DIMENSIONS'), '3D measurement of a crystal for LYSOMatrix #3', 'F');
INSERT INTO CMS_MTD_CORE_COND.COND_TO_PART_RLTNSHPS
  (KIND_OF_PART_ID, KIND_OF_CONDITION_ID, DISPLAY_NAME, IS_RECORD_DELETED)
  VALUES ((SELECT KIND_OF_PART_ID FROM CMS_MTD_CORE_CONSTRUCT.KINDS_OF_PARTS WHERE
           DISPLAY_NAME = 'LYSOMatrix #2' AND IS_RECORD_DELETED = 'F'),
           (SELECT KIND_OF_CONDITION_ID FROM
           CMS_MTD_CORE_COND.KINDS_OF_CONDITIONS WHERE EXTENSION_TABLE_NAME = 'XTAL_DIMENSIONS' AND
           NAME = 'XTAL DIMENSIONS'), '3D measurement of a crystal for LYSOMatrix #2', 'F');
INSERT INTO CMS_MTD_CORE_COND.COND_TO_PART_RLTNSHPS
  (KIND_OF_PART_ID, KIND_OF_CONDITION_ID, DISPLAY_NAME, IS_RECORD_DELETED)
  VALUES ((SELECT KIND_OF_PART_ID FROM CMS_MTD_CORE_CONSTRUCT.KINDS_OF_PARTS WHERE
           DISPLAY_NAME = 'LYSOMatrix #1' AND IS_RECORD_DELETED = 'F'),
           (SELECT KIND_OF_CONDITION_ID FROM
           CMS_MTD_CORE_COND.KINDS_OF_CONDITIONS WHERE EXTENSION_TABLE_NAME = 'XTAL_DIMENSIONS' AND
           NAME = 'XTAL DIMENSIONS'), '3D measurement of a crystal for LYSOMatrix #1', 'F');