LumiDB/doc/hflumitables.sql

0001 /*=========================LUMIDATA=======================*/
0002 CREATE TABLE "LUMIDATA"
0003 (       "DATA_ID" NUMBER(20,0),
0004         "ENTRY_ID" NUMBER(20,0),
0005         "ENTRY_NAME" VARCHAR2(56),
0006         "SOURCE" VARCHAR2(128),
0007         "RUNNUM" NUMBER(10,0),
0008         "NOMINALEGEV" BINARY_FLOAT,
0009         "NCOLLIDINGBUNCHES" NUMBER(10,0),
0010         "STARTTIME" TIMESTAMP (6),
0011         "STOPTIME" TIMESTAMP (6),
0012         "NLS" NUMBER(10,0),
0013         CONSTRAINT "LUMIDATA_PK" PRIMARY KEY ("DATA_ID")
0014 )
0015 PARTITION BY RANGE(RUNNUM)
0016 (
0017 PARTITION YEAR2010 VALUES LESS THAN(153368),
0018 PARTITION YEAR2011 VALUES LESS THAN(183338),
0019 PARTITION THEREST VALUES LESS THAN(MAXVALUE)
0020 )
0021 ;
0022 GRANT SELECT ON "LUMIDATA" TO PUBLIC;
0023 GRANT SELECT,INSERT,DELETE,UPDATE ON "LUMIDATA" TO CMS_LUMI_WRITER;
0024 CREATE INDEX LUMIDATA_RUNNUM_IDX ON LUMIDATA(RUNNUM) LOCAL;
0025 /*=========================LUMIDATA_ID=======================*/
0026 CREATE TABLE "LUMIDATA_ID"
0027 (       "NEXTID" NUMBER(20,0),
0028          CONSTRAINT "LUMIDATA_ID_PK" PRIMARY KEY ("NEXTID")
0029 );
0030 INSERT INTO "LUMIDATA_ID"("NEXTID") VALUES(0);
0031 GRANT SELECT ON "LUMIDATA_ID" TO PUBLIC;
0032 GRANT SELECT,INSERT,DELETE,UPDATE ON "LUMIDATA_ID" TO CMS_LUMI_WRITER;
0033 /*=========================LUMIDATA_ENTRIES=======================*/
0034 CREATE TABLE "LUMIDATA_ENTRIES"
0035 (       "ENTRY_ID" NUMBER(20,0),
0036         "REVISION_ID" NUMBER(20,0),
0037         "NAME" VARCHAR2(56),
0038          CONSTRAINT "LUMIDATA_ENTRIES_PK" PRIMARY KEY ("ENTRY_ID")
0039 );
0040 GRANT SELECT ON "LUMIDATA_ENTRIES" TO PUBLIC;
0041 GRANT SELECT,INSERT,DELETE,UPDATE ON "LUMIDATA_ENTRIES" TO CMS_LUMI_WRITER;
0042 /*=========================LUMIDATA_ENTRIES_ID=======================*/
0043 CREATE TABLE "LUMIDATA_ENTRIES_ID"
0044 (       "NEXTID" NUMBER(20,0),
0045          CONSTRAINT "LUMIDATA_ENTRIES_ID_PK" PRIMARY KEY ("NEXTID")
0046 );
0047 INSERT INTO "LUMIDATA_ENTRIES_ID"("NEXTID") VALUES(0);
0048 GRANT SELECT ON "LUMIDATA_ENTRIES_ID" TO PUBLIC;
0049 GRANT SELECT,INSERT,DELETE,UPDATE ON "LUMIDATA_ENTRIES_ID" TO CMS_LUMI_WRITER;
0050 /*=========================LUMIDATA_REV=======================*/
0051 CREATE TABLE "LUMIDATA_REV"
0052 (       "DATA_ID" NUMBER(20,0),
0053         "REVISION_ID" NUMBER(20,0),
0054          CONSTRAINT "LUMIDATA_REV_PK" PRIMARY KEY("DATA_ID","REVISION_ID")
0055 );
0056 GRANT SELECT ON "LUMIDATA_REV" TO PUBLIC;
0057 GRANT SELECT,INSERT,DELETE,UPDATE ON "LUMIDATA_REV" TO CMS_LUMI_WRITER;
0058 /*=========================LUMISUMMARYV2=======================*/
0059 CREATE TABLE "LUMISUMMARYV2"
0060 ("DATA_ID" NUMBER(20,0),
0061 "RUNNUM" NUMBER(10,0),
0062 "LUMILSNUM" NUMBER(10,0),
0063 "CMSLSNUM" NUMBER(10,0),
0064 "INSTLUMI" BINARY_FLOAT,
0065 "INSTLUMIERROR" BINARY_FLOAT,
0066 "INSTLUMIQUALITY" NUMBER(5,0),
0067 "BEAMSTATUS" VARCHAR2(28),
0068 "BEAMENERGY" BINARY_FLOAT,
0069 "NUMORBIT" NUMBER(10,0),
0070 "STARTORBIT" NUMBER(10,0),
0071 "CMSBXINDEXBLOB" BLOB,
0072 "BEAMINTENSITYBLOB_1" BLOB,
0073 "BEAMINTENSITYBLOB_2" BLOB,
0074 "BXLUMIVALUE_OCC1" BLOB,
0075 "BXLUMIVALUE_OCC2" BLOB,
0076 "BXLUMIVALUE_ET" BLOB,
0077 "BXLUMIERROR_OCC1" BLOB,
0078 "BXLUMIERROR_OCC2" BLOB,
0079 "BXLUMIERROR_ET" BLOB,
0080 "BXLUMIQUALITY_OCC1" BLOB,
0081 "BXLUMIQUALITY_OCC2" BLOB,
0082 "BXLUMIQUALITY_ET" BLOB,
0083  CONSTRAINT "LUMISUMMARYV2_PK" PRIMARY KEY ("DATA_ID", "LUMILSNUM"))
0084 PARTITION BY RANGE(DATA_ID)(
0085 PARTITION LUMISUMMARYV2_P1 VALUES LESS THAN(500),
0086 PARTITION LUMISUMMARYV2_P2 VALUES LESS THAN(1000),
0087 PARTITION LUMISUMMARYV2_P3 VALUES LESS THAN(1500),
0088 PARTITION LUMISUMMARYV2_P4 VALUES LESS THAN(2000),
0089 PARTITION THEREST VALUES LESS THAN(MAXVALUE)
0090 );
0091 GRANT SELECT ON "LUMISUMMARYV2" TO PUBLIC;
0092 GRANT SELECT,INSERT,DELETE,UPDATE ON "LUMISUMMARYV2" TO CMS_LUMI_WRITER;