LumiDB/doc/lumidb2schema.txt

0001 1.
0002 create table revisions(
0003      revision_id uint64 PK,
0004      branch_id uint64 not null,
0005      branch_name string,  (56)
0006      name string, unique  (56)
0007      comment string,   (1024)
0008      ctime   timestamp(6)
0009 )
0010 create table revisions_id(     #id helper table
0011      nextid uint64 PK
0012 )
0013 create index revisions_branch_id on revision(branchid)
0014
0015 2.
0016 create table luminorms(
0017      data_id  uint64 PK
0018      entry_id    uint64 not null references luminorms_entries(entry_id),
0019      entry_name  string,  (56)
0020      amodetag string,     (28)
0021      norm_1     float,
0022      egev_1     uint32,
0023      norm_2     float,
0024      egev_2     uint32
0025 )
0026
0027 create table luminorms_entries(   #versionable entity helper table
0028      entry_id uint64 PK,
0029      revision_id uint64 not null references revisions(revision_id),
0030      name    string  (56)
0031 )
0032
0033 create table luminorms_id(          #id helper table
0034      nextid uint64 PK
0035 )
0036 create table luminorms_entries_id(    #id helper table
0037      nextid uint64 PK
0038 )
0039 create table luminorms_rev(
0040     data_id uint64 not null references luminorms(data_id),
0041     revision_id uint64 not null references revisions(revision_id)
0042    (data_id,revision_id) PK
0043 )
0044 3.
0045 create table lumidata(             #versionable data table
0046      data_id uint64 PK,
0047      entry_id    uint64 not null references lumidata_entries(entry_id),
0048      entry_name string,  (56)
0049      source     string,  (128)
0050      runnum     uint32,
0051      nominalegev float
0052 )
0053 create index lumidata_entry_id on lumidata(entry_id);
0054
0055 create table lumidata_id(          #id helper table
0056      nextid uint64 PK
0057 )
0058 create table lumidata_entries(       #versionable entity table
0059      entry_id     uint64 not null   ,
0060      revision_id  uint64 not null references revision(revision_id),
0061      name    string      (56)
0062 )
0063 create table lumidata_rev(
0064     data_id uint64 not null references lumidata(data_id),
0065     revision_id uint64 not null references revision(revision_id)
0066    (data_id,revision_id) PK
0067 )
0068 constraint lumidata_rev_uc unique(data_id,revision_id)
0069 create index lumidata_rev_revision_id on lumidata_rev(revision_id);
0070
0071 create table lumidata_entries_id(
0072      nextid uint64 PK
0073 )
0074
0075 4.
0076 create table cmsrunsummary(        #data table
0077      runnum uint32 PK,
0078      hltkey string not null, (128)
0079      l1key  string,          (128)
0080      fillnum uint32,
0081      sequence    string,     (56)
0082      starttime   timestamp(6),
0083      stoptime    timestamp(6),
0084      egev uint32,
0085      amodetag string      (28),
0086      fillscheme string(128),
0087      ncollidingbunches uint32
0088 )
0089 5.
0090 create table lumisummaryv2(         #data table
0091      data_id  uint64 not null PK references lumidata(data_id),
0092      runnum      uint32,  (redundant)
0093      cmslsnum    uint32,
0094      lumilsnum   uint32,
0095      instlumi     float,
0096      instlumierror  float,
0097      instlumiquality  short,
0098      beamstatus   string,    (28)
0099      beamenergy float,
0100      cmsbxindexblob blob,
0101      beamintensityblob_1 blob,
0102      beamintensityblob_2 blob,
0103      numorbit            uint32,
0104      startorbit          uint32,
0105      bxlumivalue_occ1    blob,
0106      bxlumivalue_occ2    blob,
0107      bxlumivalue_et      blob,
0108      bxlumierr_occ1      blob,
0109      bxlumierr_occ2      blob,
0110      bxlumierr_et        blob,
0111      bxlumiquality_occ1  blob,
0112      bxlumiquality_occ2  blob,
0113      bxlumiquality_et    blob
0114 )
0115 6.
0116 create table trgdata(                #verionable data table
0117      data_id uint64 PK,
0118      entry_id  uint64 not null references trgdata_entries(entry_id),
0119      entry_name string, (56)
0120      source string,     (128)
0121      runnum   uint32,
0122      bitzeroname string, (56)
0123      bitnameclob clob
0124 )
0125 create table trgdata_rev(
0126     data_id uint64 not null references trgdata(data_id),
0127     revision_id uint64 not null references revisions(revision_id)
0128     (data_id,revision_id) PK
0129 )
0130 constraint trgdata_rev_uc unique(data_id,revision_id)
0131 create table trgdata_entries(
0132      entry_id    uint64 not null             ,
0133      revision_id uint64 not null references revisions(revision_id),
0134      name    string  (56)
0135 )
0136 create table trgdata_id(             #id helper table
0137      nextid uint64 PK
0138 )
0139 create table trgdata_entries_id(       #id helper table
0140      nextid uint64 PK
0141 )
0142 7.
0143 create table lstrg(          #data table
0144      data_id uint64 not null references trgdata(data_id),
0145      runnum    uint32,   (redundant)
0146      cmslsnum  uint32,
0147      deadtimecount uint64,
0148      bitzerocount uint32,
0149      bitzeroprescale uint32,
0150      deadfrac float,
0151      pescalesblob   blob,
0152      trgcountblob   blob
0153 )
0154 8.
0155 create table hltdata(                #vesionable data table
0156     data_id uint64 PK,
0157     entry_id    uint64 not null references hltdata_entries(entry_id),
0158     entry_name string, (56)
0159     runnum    uint32,
0160     source    string,  (128)
0161     npath    uint32,
0162     pathnameclob clob
0163 )
0164 create table hltdata_entries(
0165      entry_id    uint64 PK,
0166      revision_id uint64 not null references revisions(revision_id) ,
0167      name    string (56)
0168 )
0169 create table hltdata_id(
0170      nextid uint64 PK
0171 )
0172 create table hltdata_entries_id(
0173      nextid uint64 PK
0174 )
0175 create table hltdata_rev(
0176     data_id uint64 not null references hltdata(data_id),
0177     revision_id uint64 not null references revisions(revision_id)
0178     (data_id,revision_id) PK
0179 )
0180 9.
0181 create table lshlt(
0182    data_id uint64 not null references hltdata(data_id),
0183    runnum   uint32 not null,
0184    cmslsnum uint32 not null,
0185    prescaleblob blob,
0186    hltcountblob    blob,
0187    hltacceptblob     blob
0188    (data_id,cmslsnum) PK
0189 )
0190 10.
0191 create table trghltmap(
0192    hltkey string(128) not null,
0193    hltpathname string(256) not null,
0194    l1seed      string(1024) not null
0195    (hltkey,hltpathname,l1seed) PK
0196 )
0197 11.
0198 create table lumivalidation(
0199    runnum uint32 not null,
0200    cmslsnum uint32 not null,
0201    flag string, (28)
0202    comment string (1024)
0203 )
0204 12
0205 create table FILLSCHEME (
0206    fillscheme_id(uint64),        PK
0207    fillschemepattern(string),    NOT NULL (128)
0208    correctionfactor(float),      NOT NULL
0209 )
0210
0211 new applications:
0212
0213 lumiNorm create -name a -defaultnorm value -normbyenergy "[{energy1:norm1},{energy2:norm2}]" -minrun 1234 -maxrun 5678 -m comment
0214 lumiNorm describe -name a
0215 lumiNorm list --verbose
0216 lumiNorm modify -name a -normbyenergy "[{energy1:norm1},{energy2:norm2}]" -minrun 1234  -maxrun 6789 -m comment
0217
0218 lumiTagCommit tagname -m comment
0219 lumiTagCopy -sourcetag a -desttag b -m comment
0220 lumiTagDescribe tagname -datatype 'lumi,trg,hlt,norm' --verbose
0221
0222 Other tables:
0223
0224 create table INTGLUMI(
0225   intglumi_id uint64 PK,
0226   runnum uint32 not null,
0227   startrun uint32,
0228   intglumi float
0229 )
0230 create table INTGLUMI(RUNNUM NUMBER(10),STARTRUN NUMBER(10), INTGLUMI BINARY_FLOAT , constraint PK_INTGLUMI primary key(RUNNUM,STARTRUN));