#
##  MySQL DB initialisation and tests
#

# by David Emschermann - 30.12.2005

##############################################################################

# list of tables created

DROP TABLE roctypes;
DROP TABLE robtypes;
DROP TABLE smpos;
DROP TABLE rocpos;
DROP TABLE robpos;
DROP TABLE dcspos;

#=============================================================================

DROP TABLE roctypes;

CREATE TABLE roctypes ( 
stack   ENUM('00','01','02','03','04'),
layer   ENUM('00','01','02','03','04','05'),
roctype ENUM('L0C0','L1C0','L2C0','L3C0','L4C0','L5C0','L0C1','L1C1','L2C1','L3C1','L4C1','L5C1'),
PRIMARY KEY(stack,layer)
);

INSERT INTO roctypes VALUES 
('00','05','L5C1'),('01','05','L5C1'),('02','05','L5C0'),('03','05','L5C1'),('04','05','L5C1'),
('00','04','L4C1'),('01','04','L4C1'),('02','04','L4C0'),('03','04','L4C1'),('04','04','L4C1'),
('00','03','L3C1'),('01','03','L3C1'),('02','03','L3C0'),('03','03','L3C1'),('04','03','L3C1'),
('00','02','L2C1'),('01','02','L2C1'),('02','02','L2C0'),('03','02','L2C1'),('04','02','L2C1'),
('00','01','L1C1'),('01','01','L1C1'),('02','01','L1C0'),('03','01','L1C1'),('04','01','L1C1'),
('00','00','L0C1'),('01','00','L0C1'),('02','00','L0C0'),('03','00','L0C1'),('04','00','L0C1');

SELECT * FROM roctypes;

#=============================================================================

DROP TABLE robtypes;

CREATE TABLE robtypes (
robtype  ENUM('1A','1B','2B','3A','3B','4A','4B')  NOT NULL,
position ENUM('0','1','2','3','4','5','6','7','8') NOT NULL,
PRIMARY KEY(position)
);

INSERT INTO robtypes VALUES 
('1A','0'),('1B','1'),
('1A','2'),('2B','3'),
('3A','4'),('3B','5'),
('4A','6'),('4B','7');

SELECT * FROM robtypes;

#=============================================================================

DROP TABLE smpos;

CREATE TABLE smpos ( 
position ENUM('00','01','02','03','04','05',
              '06','07','08','09','10','11',
              '12','13','14','15','16','17') UNIQUE,
smserial ENUM('01','02','03','04','05',
              '06','07','08','09','10','11',
              '12','13','14','15','16','17','18') NOT NULL UNIQUE,
date     DATE NOT NULL DEFAULT '0000-00-00',
editor   ENUM ('DE','KO','TD') NOT NULL,
comments TEXT,
PRIMARY KEY(smserial)
);

INSERT INTO smpos (position, smserial) VALUES ('08','01'),('17','02'),('00','03'),('09','04');
INSERT INTO smpos (position, smserial) VALUES ('08','05');
INSERT INTO smpos (position, smserial , date, editor, comments) VALUES ('06', '10', '2005-12-30', 'DE', 'This is SM no 10.');
SELECT * FROM smpos WHERE 1;

#=============================================================================

DROP TABLE rocpos;

CREATE TABLE rocpos ( 
smserial  ENUM('01','02','03','04','05',
              '06','07','08','09','10','11',
              '12','13','14','15','16','17','18') NOT NULL,
stack     ENUM('00','01','02','03','04'),
layer     ENUM('00','01','02','03','04','05'),
roctype   ENUM('L0C0','L1C0','L2C0','L3C0','L4C0','L5C0','L0C1','L1C1','L2C1','L3C1','L4C1','L5C1'),
rocserial INT NOT NULL,
date      DATE NOT NULL DEFAULT '0000-00-00',
editor    ENUM ('DE','KO','TD') NOT NULL,
comments  TEXT,
PRIMARY KEY(stack,layer,rocserial)
);

INSERT INTO rocpos (smserial, stack, layer, roctype, rocserial, date) VALUES 
('01','00','00','L0C1','0017',2005-12-20),
('01','01','00','L0C1','0018',2005-12-21),
('01','02','00','L0C0','0015',2005-12-22),
('01','03','00','L0C1','0019',2005-12-23),
('01','04','00','L0C1','0012',2005-12-28);

SELECT smserial, stack, layer, roctype, rocserial, date FROM rocpos WHERE 1;

#=============================================================================

DROP TABLE robpos;

CREATE TABLE robpos (
roctype   ENUM('L0C0','L1C0','L2C0','L3C0','L4C0','L5C0','L0C1','L1C1','L2C1','L3C1','L4C1','L5C1'),
rocserial INT NOT NULL,
position  ENUM('0','1','2','3','4','5','6','7','8') NOT NULL,
robserial INT NOT NULL UNIQUE,
date      DATE NOT NULL DEFAULT '0000-00-00',
editor    ENUM ('DE','KO','TD') NOT NULL,
comments  TEXT,
PRIMARY KEY(roctype,rocserial,position)
);

INSERT INTO robpos (roctype,rocserial,position,robserial,date) VALUES 
('L0C1','0017','0',48,2005-12-30),
('L0C1','0017','1',49,2006-01-09),
('L0C1','0017','2',52,2006-01-10),
('L0C1','0017','3',53,2006-01-11),
('L0C1','0017','4',54,2006-01-12),
('L0C1','0017','5',55,2006-01-13),
('L0C1','0017','6',56,2006-01-16),
('L0C1','0017','7',57,2006-01-17),
('L0C1','0018','0',50,2006-01-18),
('L0C1','0018','1',51,2006-01-20),
('L0C1','0018','2',58,2006-01-19),
('L0C1','0018','3',59,2006-01-21),
('L0C1','0018','4',60,2006-01-22),
('L0C1','0018','5',61,2006-01-23),
('L0C1','0018','6',62,2006-01-26),
('L0C1','0018','7',63,2006-01-27);

SELECT * FROM robpos WHERE 1;

#=============================================================================

DROP TABLE dcspos;

CREATE TABLE dcspos (
robserial INT NOT NULL UNIQUE,
dcsserial INT NOT NULL UNIQUE,
date      DATE NOT NULL DEFAULT '0000-00-00',
editor    ENUM ('DE','KO','TD') NOT NULL,
comments  TEXT,
PRIMARY KEY(robserial)
);

INSERT INTO dcspos (robserial,dcsserial,date) VALUES 
(53,'0045',2006-01-15),
(59,'0057',2006-01-23);

SELECT * FROM dcspos WHERE 1;

##############################################################################

#
## consistency checks
#

- TABLE dcspos: ROB type has to be 2B if DCS connected
- TABLE robpos: check ROBtype - position

#
## SELECT statements
#

SELECT smserial, rp.stack, rp.layer, roctype, rocserial, date, editor
FROM rocpos AS rp, roctypes AS rt 
WHERE rp.stack=rt.stack AND rp.layer=rt.layer;

##  find DCS boards on ROCs

SELECT rp.roctype,rp.rocserial,rp.position,rp.robserial,dp.dcsserial
FROM robpos AS rp, dcspos AS dp
WHERE rp.robserial=dp.robserial;

##  find ROBs in SM

SELECT roc.smserial, roc.stack, roc.layer, roc.roctype, roc.rocserial, rob.position, rob.robserial
FROM rocpos AS roc, robpos AS rob
WHERE roc.roctype=rob.roctype AND roc.rocserial=rob.rocserial AND roc.smserial='01'
ORDER BY rob.robserial;



