Database
BactImAs Database
In principle, you can use any database you like as long as you can connect (have a) JDBC driver for that database. For the time being, we're using sqlite database, since it is light, portable, multi-platform and easy to install. Below, you'll find both sqlite create database script, and Microsoft SQL Server database script. Starting from those, it's easy to adjust the script for any SQL database server.
Note that you should not bother with creating an empty sqlite database, there is one included in the installation package.
Database diagram
(Views are shown in gray.)\\
SqLite Create Database Script
CREATE TABLE Version (
version char(10) primary key
);
insert into Version(version) values ("v.0.7.");
CREATE TABLE [Bacteria] (
"idBacteria" integer PRIMARY KEY AUTOINCREMENT NOT NULL,
"idExperiment" integer,
"bactName" varchar(50) NOT NULL COLLATE NOCASE,
FOREIGN KEY ([idExperiment])
REFERENCES [Experiment]([idExperiment])
);
CREATE UNIQUE INDEX [Bacteria_unq_exp_bactName]
ON [Bacteria] ([idExperiment] DESC, [bactName] DESC);
CREATE TABLE [BacteriaSplit] (
"idBacteriaParent" integer NOT NULL,
"idBacteriaChildA" integer NOT NULL,
"idBacteriaChildB" integer NOT NULL,
"frameNo" integer NOT NULL,
PRIMARY KEY ([idBacteriaParent]),
FOREIGN KEY ([idBacteriaParent])
REFERENCES [Bacteria]([idBacteria]),
FOREIGN KEY ([idBacteriaChildA])
REFERENCES [Bacteria]([idBacteria]),
FOREIGN KEY ([idBacteriaChildB])
REFERENCES [Bacteria]([idBacteria])
);
CREATE TABLE [Measurement] (
"idMeasurement" integer PRIMARY KEY AUTOINCREMENT NOT NULL,
"measurementName" nvarchar(20) NOT NULL COLLATE NOCASE
);
CREATE UNIQUE INDEX [Measurement_unq_measurementName] ON [Measurement] ([measurementName] DESC);
CREATE TABLE [RoiType] (
"idRoiType" smallint NOT NULL,
"typeName" varchar(20) NOT NULL COLLATE NOCASE,
PRIMARY KEY ([idRoiType])
);
CREATE TABLE [Roi] (
"idBacteria" integer NOT NULL,
"frameNo" integer NOT NULL,
"idRoiType" smallint NOT NULL,
"roiBlob" blob,
"roiName" nvarchar(50) COLLATE NOCASE,
PRIMARY KEY ([idBacteria], [frameNo], [idRoiType]),
FOREIGN KEY ([idBacteria])
REFERENCES [Bacteria]([idBacteria]),
FOREIGN KEY ([idRoiType])
REFERENCES [RoiType]([idRoiType])
);
CREATE TABLE [Frame] (
"idFrame" integer PRIMARY KEY AUTOINCREMENT NOT NULL,
"idExperiment" integer,
"frameNo" integer NOT NULL,
"frameRedFileName" nvarchar(500) COLLATE NOCASE,
"frameGreenFileName" nvarchar(500) COLLATE NOCASE,
"frameBlueFileName" nvarchar(500) COLLATE NOCASE,
"transX" smallint DEFAULT 0,
"transY" smallint DEFAULT 0,
ignoreFrame char(1) default 'n',
bgRedMean double,
bgGreenMean double,
bgBlueMean double,
Algorithm varchar(500),
FOREIGN KEY ([idExperiment])
REFERENCES [Experiment]([idExperiment])
);
CREATE INDEX idx_frame_id_experiment_frame_no on frame(idExperiment, frameNo);
CREATE TABLE [ExperimentMeasurements] (
"idExperiment" integer NOT NULL,
"idMeasurement" smallint NOT NULL,
"idChannel" smallint, collarSize int default 0,
PRIMARY KEY ([idExperiment], [idMeasurement])
,
FOREIGN KEY ([idExperiment])
REFERENCES [Experiment]([idExperiment]),
FOREIGN KEY ([idMeasurement])
REFERENCES [Measurement]([idMeasurement])
);
CREATE TABLE [Channel] (
"idChannel" smallint NOT NULL,
"channelName" nvarchar(5) NOT NULL COLLATE NOCASE,
PRIMARY KEY ([idChannel])
);
CREATE TABLE [Palette] (
"idPalette" smallint NOT NULL,
"paletteName" nvarchar(30) NOT NULL COLLATE NOCASE,
PRIMARY KEY ([idPalette])
);
CREATE TABLE [PaletteColor] (
"idPalette" smallint NOT NULL,
"ordinal" smallint NOT NULL,
"red" smallint NOT NULL,
"green" smallint NOT NULL,
"blue" smallint NOT NULL,
PRIMARY KEY ([idPalette], [ordinal])
);
CREATE TABLE [Experiment] (
[idExperiment] integer PRIMARY KEY AUTOINCREMENT NOT NULL,
[expName] char(100) NULL,
[redMovieFileName] nvarchar(500) NULL,
[greenMovieFileName] nvarchar(500) NULL,
[blueMovieFileName] nvarchar(500) NOT NULL,
[movieSpf] INTEGER NULL,
[dateCreated] integer NULL,
pixelWidthMicron decimal(6,2),
pixelHeightMicron decimal(6,2),
pictureScale decimal(6,2)
);
CREATE TABLE [BacteriaMeasurement] (
"idBacteria" integer NOT NULL,
"frameNo" integer NOT NULL,
"idMeasurement" smallint NOT NULL,
"idRoiType" smallint NOT NULL,
"idChannel" smallint not null,
"value" float NOT NULL,
PRIMARY KEY ([idBacteria], [frameNo], [idMeasurement], [idRoiType], idChannel),
FOREIGN KEY ([idBacteria])
REFERENCES [Bacteria]([idBacteria]),
FOREIGN KEY ([idMeasurement])
REFERENCES [Measurement]([idMeasurement]),
FOREIGN KEY ([idChannel])
REFERENCES [Channel]([idChannel])
);
CREATE TABLE BacteriaState (
idState integer PRIMARY KEY,
stateName varchar(50) not null,
stateTag char(1) not null
);
CREATE TABLE BacteriaStateChange (
idBacteria integer ,
frameNo integer ,
idState integer ,
PRIMARY KEY (idBacteria, frameNo, idState),
FOREIGN KEY (idBacteria) REFERENCES Bacteria(idBacteria),
FOREIGN KEY (idState) REFERENCES BacteriaState(idState)
);
CREATE TABLE ExperimentEvent (
idExperiment integer NOT NULL,
frameNo integer NOT NULL,
eventDesc varchar(255) not null,
eventAbbr varchar(15) not null,
PRIMARY KEY (idExperiment, frameNo),
FOREIGN KEY (idExperiment) REFERENCES Experiment(idExperiment)
);
INSERT INTO Measurement(measurementName) VALUES ('area');
INSERT INTO Measurement(measurementName) VALUES ('mean');
INSERT INTO Measurement(measurementName) VALUES ('min');
INSERT INTO Measurement(measurementName) VALUES ('max');
INSERT INTO Measurement(measurementName) VALUES ('x');
INSERT INTO Measurement(measurementName) VALUES ('y');
INSERT INTO Measurement(measurementName) VALUES ('perim.');
INSERT INTO Measurement(measurementName) VALUES ('major');
INSERT INTO Measurement(measurementName) VALUES ('minor');
INSERT INTO Measurement(measurementName) VALUES ('angle');
INSERT INTO Measurement(measurementName) VALUES ('feret');
INSERT INTO Measurement(measurementName) VALUES ('kurt');
INSERT INTO Measurement(measurementName) VALUES ('slice');
INSERT INTO Measurement(measurementName) VALUES ('feretx');
INSERT INTO Measurement(measurementName) VALUES ('ferety');
INSERT INTO Measurement(measurementName) VALUES ('feretangle');
INSERT INTO Measurement(measurementName) VALUES ('minferet');
INSERT INTO Measurement(measurementName) VALUES ('length');
INSERT INTO Measurement(measurementName) VALUES ('width');
INSERT INTO Measurement(measurementName) VALUES ('rawintden');
INSERT INTO Measurement(measurementName) VALUES ('intden');
INSERT INTO Measurement(measurementName) VALUES ('stddev');
INSERT INTO Measurement(measurementName) VALUES ('height');
INSERT INTO Measurement(measurementName) VALUES ('median');
INSERT INTO Channel(idChannel, channelName) VALUES (1,'Red');
INSERT INTO Channel(idChannel, channelName) VALUES (2,'Green');
INSERT INTO Channel(idChannel, channelName) VALUES (3,'Blue');
insert into Palette(idPalette, paletteName) values (1, 'Default pallete');
INSERT INTO PaletteColor(idPalette, ordinal, red, green, blue) VALUES (1,1,0,0,0);
INSERT INTO PaletteColor(idPalette, ordinal, red, green, blue) VALUES (1,2,0,0,255);
INSERT INTO PaletteColor(idPalette, ordinal, red, green, blue) VALUES (1,3,0,255,0);
INSERT INTO PaletteColor(idPalette, ordinal, red, green, blue) VALUES (1,4,255,127,0);
INSERT INTO PaletteColor(idPalette, ordinal, red, green, blue) VALUES (1,5,255,0,0);
insert into BacteriaState values (1, 'Death', '*');
insert into BacteriaState values (2, 'Ignore', '#');
CREATE VIEW vBacteriaMeanMeasurement as
SELECT idBacteria, frameNo, channelName, AVG(value) as mean, count(*) as cnt -- if there are duplicate measuements on diff channels
FROM BacteriaMeasurement
JOIN Measurement
ON BacteriaMeasurement.idMeasurement = Measurement.idMeasurement
AND lower(measurementName) = 'mean'
JOIN Channel
ON BacteriaMeasurement.idChannel = Channel.idChannel
GROUP BY idBacteria, frameNo, channelName;
CREATE VIEW vBacteriaAreaMeasurement as
SELECT MArea.idBacteria, frameNo, pixelWidthMicron * pixelHeightMicron * AVG(value)/(pictureScale * pictureScale) as areaSquareMicron, AVG(value) as area, count(*) as cnt -- if there are duplicate measuements on diff channels
FROM BacteriaMeasurement AS MArea
JOIN Measurement
ON MArea.idMeasurement = measurement.idMeasurement
AND lower(measurementName) = 'area'
JOIN Bacteria
ON MArea.idBacteria = Bacteria.idBacteria
JOIN Experiment
ON Bacteria.idExperiment = Experiment.idExperiment
GROUP BY MArea.idBacteria, frameNo, pixelWidthMicron, pixelHeightMicron, pictureScale;
CREATE VIEW vBacteriaMeasurement AS
SELECT expName as exp_name,
Bacteria.idExperiment AS id_experiment,
DistBactMeasurement.frameNo AS frame_no,
DistBactMeasurement.frameNo * movieSpf AS time_s,
Bacteria.bactName AS bact_name,
Bacteria.idBacteria AS id_bacteria,
length (Bacteria.bactName) AS generation,
Birth.parentName AS parent,
CASE WHEN (Bacteria.bactName = Birth.childAName) THEN Birth.childBName
ELSE Birth.childAName
END AS sibling,
-- Division.childAName || ', ' || Division.childBName AS progeny,
Division.childAName AS progeny_a,
Division.childBName AS progeny_b,
(Birth.frameNo - 1) * movieSpf AS birth_time,
(Division.frameNo - 1) * movieSpf AS division_time,
(Division.frameNo - Birth.frameNo) * movieSpf AS interdivision_time,
1.0 * (DivisionArea.areaSquareMicron/BirthArea.areaSquareMicron)/((Division.frameNo - Birth.frameNo) * movieSpf) AS growth_rate,
1.0 * (DivisionArea.areaSquareMicron - BirthArea.areaSquareMicron)/((Division.frameNo - Birth.frameNo) * movieSpf) AS growth_velocity,
CurrArea.area,
CurrArea.areaSquareMicron AS area_square_microns,
CASE WHEN (PrevFrame.frameNo IS NULL) THEN 0
ELSE CurrArea.areaSquareMicron - PrevArea.areaSquareMicron
END AS area_square_microns_growth,
CurrRedMean.Mean AS red_mean,
currGreenMean.Mean AS green_mean,
currBlueMean.Mean AS blue_mean,
Frame.bgRedMean AS red_bg,
Frame.bgGreenMean AS green_bg,
Frame.bgBlueMean AS blue_bg,
(CurrRedMean.Mean - Frame.bgRedMean) AS red_mean_bg_cor,
(currGreenMean.Mean - Frame.bgGreenMean) AS green_mean_bg_cor,
(currBlueMean.Mean - Frame.bgBlueMean) AS blue_mean_bg_cor,
(CurrRedMean.Mean - Frame.bgRedMean) * CurrArea.areaSquareMicron AS red_ctcf,
(currGreenMean.Mean - Frame.bgGreenMean) * CurrArea.areaSquareMicron AS green_ctcf,
(currBlueMean.Mean - Frame.bgBlueMean) * CurrArea.areaSquareMicron AS blue_ctcf,
(CurrRedMean.Mean - Frame.bgRedMean) - (PrevRedMean.Mean - PrevFrame.bgRedMean) AS red_mean_bg_cor_change,
(CurrGreenMean.Mean - Frame.bgGreenMean) - (PrevGreenMean.Mean - PrevFrame.bgGreenMean) AS green_mean_bg_cor_change,
(CurrBlueMean.Mean - Frame.bgBlueMean) - (PrevBlueMean.Mean - PrevFrame.bgBlueMean) AS blue_mean_bg_cor_change,
(CurrRedMean.Mean - Frame.bgRedMean) * CurrArea.areaSquareMicron - (PrevRedMean.Mean - PrevFrame.bgRedMean) * PrevArea.areaSquareMicron AS red_ctcf_change,
(CurrGreenMean.Mean - Frame.bgGreenMean) * CurrArea.areaSquareMicron - (PrevGreenMean.Mean - PrevFrame.bgGreenMean) * PrevArea.areaSquareMicron AS green_ctcf_change,
(CurrBlueMean.Mean - Frame.bgBlueMean) * CurrArea.areaSquareMicron - (PrevBlueMean.Mean - PrevFrame.bgBlueMean) * PrevArea.areaSquareMicron AS blue_ctcf_change
FROM (SELECT DISTINCT idBacteria, frameNo FROM BacteriaMeasurement) AS DistBactMeasurement
JOIN Bacteria
ON DistBactMeasurement.idBacteria = bacteria.idBacteria
JOIN Experiment
ON Bacteria.idExperiment = Experiment.idExperiment
JOIN Frame
ON Bacteria.idExperiment = Frame.idExperiment
AND DistBactMeasurement.frameNo = Frame.frameNo
AND Frame.ignoreFrame = 'n'
LEFT JOIN Frame PrevFrame
ON PrevFrame.idExperiment = Bacteria.idExperiment
AND PrevFrame.frameNo = (SELECT MAX(frameNo) FROM Frame F WHERE F.idExperiment = PrevFrame.idExperiment AND F.frameNo < DistBactMeasurement.frameNo AND ignoreFrame = 'n')
LEFT JOIN (
SELECT BirthParent.bactName AS parentName, idBacteriaChildA, idBacteriaChildB, BirthChildA.bactName AS childAName, BirthChildB.bactName AS childBName, frameNo
FROM BacteriaSplit
JOIN Bacteria AS BirthParent ON BacteriaSplit.idBacteriaParent = BirthParent.idBacteria
JOIN Bacteria AS BirthChildA ON BacteriaSplit.idBacteriaChildA = BirthChildA.idBacteria
JOIN Bacteria AS BirthChildB ON BacteriaSplit.idBacteriaChildB = BirthChildB.idBacteria
) AS Birth
ON (DistBactMeasurement.idBacteria = Birth.idBacteriaChildA OR DistBactMeasurement.idBacteria = Birth.idBacteriaChildB)
LEFT JOIN (
SELECT idBacteriaParent, DivChildA.bactName AS childAName, DivChildB.bactName AS childBName, frameNo
FROM BacteriaSplit
JOIN Bacteria AS DivChildA ON BacteriaSplit.idBacteriaChildA = DivChildA.idBacteria
JOIN Bacteria AS DivChildB ON BacteriaSplit.idBacteriaChildB = DivChildB.idBacteria
) AS Division
ON (DistBactMeasurement.idBacteria = Division.idBacteriaParent)
LEFT JOIN vBacteriaAreaMeasurement AS BirthArea
ON Birth.frameNo = BirthArea.frameNo
AND DistBactMeasurement.idBacteria = BirthArea.idBacteria
LEFT JOIN vBacteriaAreaMeasurement AS DivisionArea
ON DivisionArea.frameNo = ((SELECT MAX(frameNo) FROM Frame F WHERE F.idExperiment = Experiment.idExperiment AND F.frameNo < Division.frameNo AND ignoreFrame = 'n'))
AND DivisionArea.idBacteria = DistBactMeasurement.idBacteria
LEFT JOIN vBacteriaAreaMeasurement AS PrevArea
ON PrevFrame.frameNo = PrevArea.frameNo
AND DistBactMeasurement.idBacteria = PrevArea.idBacteria
LEFT JOIN vBacteriaAreaMeasurement AS CurrArea
ON DistBactMeasurement.frameNo = CurrArea.frameNo
AND DistBactMeasurement.idBacteria = CurrArea.idBacteria
--// Prev mean
LEFT JOIN vBacteriaMeanMeasurement AS prevBlueMean
ON PrevFrame.frameNo = prevBlueMean.frameNo
AND DistBactMeasurement.idBacteria = prevBlueMean.idBacteria
AND prevBlueMean.channelName = 'Blue'
LEFT JOIN vBacteriaMeanMeasurement AS prevGreenMean
ON PrevFrame.frameNo = prevGreenMean.frameNo
AND DistBactMeasurement.idBacteria = prevGreenMean.idBacteria
AND prevGreenMean.channelName = 'Green'
LEFT JOIN vBacteriaMeanMeasurement AS PrevRedMean
ON PrevFrame.frameNo = PrevRedMean.frameNo
AND DistBactMeasurement.idBacteria = PrevRedMean.idBacteria
AND PrevRedMean.channelName = 'Red'
--// Curr mean
LEFT JOIN vBacteriaMeanMeasurement AS currBlueMean
ON DistBactMeasurement.frameNo = currBlueMean.frameNo
AND DistBactMeasurement.idBacteria = currBlueMean.idBacteria
AND currBlueMean.channelName = 'Blue'
LEFT JOIN vBacteriaMeanMeasurement AS currGreenMean
ON DistBactMeasurement.frameNo = currGreenMean.frameNo
AND DistBactMeasurement.idBacteria = currGreenMean.idBacteria
AND currGreenMean.channelName = 'Green'
LEFT JOIN vBacteriaMeanMeasurement AS CurrRedMean
ON DistBactMeasurement.frameNo = CurrRedMean.frameNo
AND DistBactMeasurement.idBacteria = CurrRedMean.idBacteria
AND CurrRedMean.channelName = 'Red';
MS Sql Server Create Database Script
CREATE TABLE Version (
version char(10) primary key
);
insert into Version(version) values ('v.0.7.');
CREATE TABLE [Experiment] (
[idExperiment] integer PRIMARY KEY identity(1000, 1) NOT NULL,
[expName] char(100) NULL,
[redMovieFileName] nvarchar(500) NULL,
[greenMovieFileName] nvarchar(500) NULL,
[blueMovieFileName] nvarchar(500) NOT NULL,
[movieSpf] INTEGER NULL,
[dateCreated] integer NULL,
pixelWidthMicron decimal(6,2),
pixelHeightMicron decimal(6,2),
pictureScale decimal(6,2)
);
CREATE TABLE [Bacteria] (
idBacteria integer PRIMARY KEY identity(1, 1) NOT NULL,
idExperiment integer,
bactName varchar(50) NOT NULL ,
FOREIGN KEY ([idExperiment])
REFERENCES [Experiment]([idExperiment])
);
CREATE UNIQUE INDEX [Bacteria_unq_exp_bactName]
ON [Bacteria] ([idExperiment] DESC, [bactName] DESC);
CREATE TABLE [BacteriaSplit] (
idBacteriaParent integer NOT NULL,
idBacteriaChildA integer NOT NULL,
idBacteriaChildB integer NOT NULL,
frameNo integer NOT NULL,
PRIMARY KEY ([idBacteriaParent]),
FOREIGN KEY ([idBacteriaParent])
REFERENCES [Bacteria]([idBacteria]),
FOREIGN KEY ([idBacteriaChildA])
REFERENCES [Bacteria]([idBacteria]),
FOREIGN KEY ([idBacteriaChildB])
REFERENCES [Bacteria]([idBacteria])
);
CREATE TABLE [Measurement] (
idMeasurement integer PRIMARY KEY identity NOT NULL,
measurementName nvarchar(20) NOT NULL
);
CREATE UNIQUE INDEX [Measurement_unq_measurementName] ON [Measurement] ([measurementName] DESC);
INSERT INTO Measurement(measurementName) VALUES ('area');
INSERT INTO Measurement(measurementName) VALUES ('mean');
INSERT INTO Measurement(measurementName) VALUES ('min');
INSERT INTO Measurement(measurementName) VALUES ('max');
INSERT INTO Measurement(measurementName) VALUES ('x');
INSERT INTO Measurement(measurementName) VALUES ('y');
INSERT INTO Measurement(measurementName) VALUES ('perim.');
INSERT INTO Measurement(measurementName) VALUES ('major');
INSERT INTO Measurement(measurementName) VALUES ('minor');
INSERT INTO Measurement(measurementName) VALUES ('angle');
INSERT INTO Measurement(measurementName) VALUES ('feret');
INSERT INTO Measurement(measurementName) VALUES ('kurt');
INSERT INTO Measurement(measurementName) VALUES ('slice');
INSERT INTO Measurement(measurementName) VALUES ('feretx');
INSERT INTO Measurement(measurementName) VALUES ('ferety');
INSERT INTO Measurement(measurementName) VALUES ('feretangle');
INSERT INTO Measurement(measurementName) VALUES ('minferet');
INSERT INTO Measurement(measurementName) VALUES ('length');
INSERT INTO Measurement(measurementName) VALUES ('width');
INSERT INTO Measurement(measurementName) VALUES ('rawintden');
INSERT INTO Measurement(measurementName) VALUES ('intden');
INSERT INTO Measurement(measurementName) VALUES ('stddev');
INSERT INTO Measurement(measurementName) VALUES ('height');
INSERT INTO Measurement(measurementName) VALUES ('median');
CREATE TABLE [RoiType] (
idRoiType smallint NOT NULL,
typeName varchar(20) NOT NULL ,
PRIMARY KEY ([idRoiType])
);
insert into RoiType values (1, 'Human')
insert into RoiType values (2, 'Computer')
CREATE TABLE [Roi] (
idBacteria integer NOT NULL,
frameNo integer NOT NULL,
idRoiType smallint NOT NULL,
roiBlob VARBINARY (MAX),
roiName nvarchar(50) ,
PRIMARY KEY ([idBacteria], [frameNo], [idRoiType]),
FOREIGN KEY ([idBacteria])
REFERENCES [Bacteria]([idBacteria]),
FOREIGN KEY ([idRoiType])
REFERENCES [RoiType]([idRoiType])
);
CREATE TABLE [Frame] (
idFrame integer PRIMARY KEY identity ,
idExperiment integer,
frameNo integer NOT NULL,
frameRedFileName nvarchar(500) ,
frameGreenFileName nvarchar(500) ,
frameBlueFileName nvarchar(500) ,
transX smallint DEFAULT 0,
transY smallint DEFAULT 0,
ignoreFrame char(1) default 'n',
bgRedMean float(53),
bgGreenMean float(53),
bgBlueMean float(53),
[Algorithm] varchar(500),
FOREIGN KEY ([idExperiment])
REFERENCES [Experiment]([idExperiment])
);
CREATE INDEX idx_frame_id_experiment_frame_no on frame(idExperiment, frameNo)
CREATE TABLE [ExperimentMeasurements] (
idExperiment integer NOT NULL,
idMeasurement integer NOT NULL,
idChannel smallint,
collarSize int default 0,
PRIMARY KEY ([idExperiment], [idMeasurement]),
FOREIGN KEY ([idExperiment])
REFERENCES [Experiment]([idExperiment]),
FOREIGN KEY ([idMeasurement])
REFERENCES [Measurement]([idMeasurement])
);
CREATE TABLE [Channel] (
idChannel smallint NOT NULL,
channelName nvarchar(5) NOT NULL ,
PRIMARY KEY ([idChannel])
);
INSERT INTO Channel(idChannel, channelName) VALUES (1,'Red');
INSERT INTO Channel(idChannel, channelName) VALUES (2,'Green');
INSERT INTO Channel(idChannel, channelName) VALUES (3,'Blue');
CREATE TABLE [Palette] (
idPalette smallint NOT NULL,
paletteName nvarchar(30) NOT NULL,
PRIMARY KEY ([idPalette])
);
insert into Palette(idPalette, paletteName) values (1, 'Default pallete')
CREATE TABLE [PaletteColor] (
idPalette smallint NOT NULL,
ordinal smallint NOT NULL,
red smallint NOT NULL,
green smallint NOT NULL,
blue smallint NOT NULL,
PRIMARY KEY ([idPalette], [ordinal])
FOREIGN KEY ([idPalette])
REFERENCES [Palette]([idPalette])
);
INSERT INTO PaletteColor(idPalette, ordinal, red, green, blue) VALUES (1,1,0,0,0);
INSERT INTO PaletteColor(idPalette, ordinal, red, green, blue) VALUES (1,2,0,0,255);
INSERT INTO PaletteColor(idPalette, ordinal, red, green, blue) VALUES (1,3,0,255,0);
INSERT INTO PaletteColor(idPalette, ordinal, red, green, blue) VALUES (1,4,255,127,0);
INSERT INTO PaletteColor(idPalette, ordinal, red, green, blue) VALUES (1,5,255,0,0);
CREATE TABLE [BacteriaMeasurement] (
idBacteria integer NOT NULL,
frameNo integer NOT NULL,
idMeasurement int NOT NULL,
idRoiType smallint NOT NULL,
idChannel smallint not null,
value float NOT NULL,
PRIMARY KEY ([idBacteria], [frameNo], [idMeasurement], [idRoiType], idChannel),
FOREIGN KEY ([idBacteria])
REFERENCES [Bacteria]([idBacteria]),
FOREIGN KEY ([idMeasurement])
REFERENCES [Measurement]([idMeasurement]),
FOREIGN KEY ([idChannel])
REFERENCES [Channel]([idChannel])
);
CREATE TABLE BacteriaState (
idState integer PRIMARY KEY,
stateName varchar(50) not null,
stateTag char(1) not null
);
insert into BacteriaState values (1, 'Death', '*');
insert into BacteriaState values (2, 'Ignore', '#');
CREATE TABLE BacteriaStateChange (
idBacteria integer ,
frameNo integer ,
idState integer ,
PRIMARY KEY (idBacteria, frameNo, idState),
FOREIGN KEY (idBacteria) REFERENCES Bacteria(idBacteria),
FOREIGN KEY (idState) REFERENCES BacteriaState(idState)
);
CREATE TABLE ExperimentEvent (
idExperiment integer NOT NULL,
frameNo integer NOT NULL,
eventDesc varchar(255) not null,
eventAbbr varchar(15) not null,
PRIMARY KEY (idExperiment, frameNo),
FOREIGN KEY (idExperiment) REFERENCES Experiment(idExperiment)
);
CREATE VIEW vBacteriaMeanMeasurement as
SELECT idBacteria, frameNo, channelName, AVG(value) as mean, count(*) as cnt -- if there are duplicate measuements on diff channels
FROM BacteriaMeasurement
JOIN Measurement
ON BacteriaMeasurement.idMeasurement = Measurement.idMeasurement
AND lower(measurementName) = 'mean'
JOIN Channel
ON BacteriaMeasurement.idChannel = Channel.idChannel
GROUP BY idBacteria, frameNo, channelName;
CREATE VIEW vBacteriaAreaMeasurement as
SELECT MArea.idBacteria, frameNo, pixelWidthMicron * pixelHeightMicron * AVG(value)/(pictureScale * pictureScale) as areaSquareMicron, AVG(value) as area, count(*) as cnt -- if there are duplicate measuements on diff channels
FROM BacteriaMeasurement AS MArea
JOIN Measurement
ON MArea.idMeasurement = measurement.idMeasurement
AND lower(measurementName) = 'area'
JOIN Bacteria
ON MArea.idBacteria = Bacteria.idBacteria
JOIN Experiment
ON Bacteria.idExperiment = Experiment.idExperiment
GROUP BY MArea.idBacteria, frameNo, pixelWidthMicron, pixelHeightMicron, pictureScale;
CREATE VIEW vBacteriaMeasurement AS
SELECT expName as exp_name,
Bacteria.idExperiment AS id_experiment,
DistBactMeasurement.frameNo AS frame_no,
DistBactMeasurement.frameNo * movieSpf AS time_s,
Bacteria.bactName AS bact_name,
Bacteria.idBacteria AS id_bacteria,
len (Bacteria.bactName) AS generation,
Birth.parentName AS parent,
CASE WHEN (Bacteria.bactName = Birth.childAName) THEN Birth.childBName
ELSE Birth.childAName
END AS sibling,
-- Division.childAName || ', ' || Division.childBName AS progeny,
Division.childAName AS progeny_a,
Division.childBName AS progeny_b,
(Birth.frameNo - 1) * movieSpf AS birth_time,
(Division.frameNo - 1) * movieSpf AS division_time,
(Division.frameNo - Birth.frameNo) * movieSpf AS interdivision_time,
1.0 * (DivisionArea.areaSquareMicron/BirthArea.areaSquareMicron)/((Division.frameNo - Birth.frameNo) * movieSpf) AS growth_rate,
1.0 * (DivisionArea.areaSquareMicron - BirthArea.areaSquareMicron)/((Division.frameNo - Birth.frameNo) * movieSpf) AS growth_velocity,
CurrArea.area,
CurrArea.areaSquareMicron AS area_square_microns,
CASE WHEN (PrevFrame.frameNo IS NULL) THEN 0
ELSE CurrArea.areaSquareMicron - PrevArea.areaSquareMicron
END AS area_square_microns_growth,
CurrRedMean.Mean AS red_mean,
currGreenMean.Mean AS green_mean,
currBlueMean.Mean AS blue_mean,
Frame.bgRedMean AS red_bg,
Frame.bgGreenMean AS green_bg,
Frame.bgBlueMean AS blue_bg,
(CurrRedMean.Mean - Frame.bgRedMean) AS red_mean_bg_cor,
(currGreenMean.Mean - Frame.bgGreenMean) AS green_mean_bg_cor,
(currBlueMean.Mean - Frame.bgBlueMean) AS blue_mean_bg_cor,
(CurrRedMean.Mean - Frame.bgRedMean) * CurrArea.areaSquareMicron AS red_ctcf,
(currGreenMean.Mean - Frame.bgGreenMean) * CurrArea.areaSquareMicron AS green_ctcf,
(currBlueMean.Mean - Frame.bgBlueMean) * CurrArea.areaSquareMicron AS blue_ctcf,
(CurrRedMean.Mean - Frame.bgRedMean) - (PrevRedMean.Mean - PrevFrame.bgRedMean) AS red_mean_bg_cor_change,
(CurrGreenMean.Mean - Frame.bgGreenMean) - (PrevGreenMean.Mean - PrevFrame.bgGreenMean) AS green_mean_bg_cor_change,
(CurrBlueMean.Mean - Frame.bgBlueMean) - (PrevBlueMean.Mean - PrevFrame.bgBlueMean) AS blue_mean_bg_cor_change,
(CurrRedMean.Mean - Frame.bgRedMean) * CurrArea.areaSquareMicron - (PrevRedMean.Mean - PrevFrame.bgRedMean) * PrevArea.areaSquareMicron AS red_ctcf_change,
(CurrGreenMean.Mean - Frame.bgGreenMean) * CurrArea.areaSquareMicron - (PrevGreenMean.Mean - PrevFrame.bgGreenMean) * PrevArea.areaSquareMicron AS green_ctcf_change,
(CurrBlueMean.Mean - Frame.bgBlueMean) * CurrArea.areaSquareMicron - (PrevBlueMean.Mean - PrevFrame.bgBlueMean) * PrevArea.areaSquareMicron AS blue_ctcf_change
FROM (SELECT DISTINCT idBacteria, frameNo FROM BacteriaMeasurement) AS DistBactMeasurement
JOIN Bacteria
ON DistBactMeasurement.idBacteria = bacteria.idBacteria
JOIN Experiment
ON Bacteria.idExperiment = Experiment.idExperiment
JOIN Frame
ON Bacteria.idExperiment = Frame.idExperiment
AND DistBactMeasurement.frameNo = Frame.frameNo
AND Frame.ignoreFrame = 'n'
LEFT JOIN Frame PrevFrame
ON PrevFrame.idExperiment = Bacteria.idExperiment
AND PrevFrame.frameNo = (SELECT MAX(frameNo) FROM Frame F WHERE F.idExperiment = PrevFrame.idExperiment AND F.frameNo < DistBactMeasurement.frameNo AND ignoreFrame = 'n')
LEFT JOIN (
SELECT BirthParent.bactName AS parentName, idBacteriaChildA, idBacteriaChildB, BirthChildA.bactName AS childAName, BirthChildB.bactName AS childBName, frameNo
FROM BacteriaSplit
JOIN Bacteria AS BirthParent ON BacteriaSplit.idBacteriaParent = BirthParent.idBacteria
JOIN Bacteria AS BirthChildA ON BacteriaSplit.idBacteriaChildA = BirthChildA.idBacteria
JOIN Bacteria AS BirthChildB ON BacteriaSplit.idBacteriaChildB = BirthChildB.idBacteria
) AS Birth
ON (DistBactMeasurement.idBacteria = Birth.idBacteriaChildA OR DistBactMeasurement.idBacteria = Birth.idBacteriaChildB)
LEFT JOIN (
SELECT idBacteriaParent, DivChildA.bactName AS childAName, DivChildB.bactName AS childBName, frameNo
FROM BacteriaSplit
JOIN Bacteria AS DivChildA ON BacteriaSplit.idBacteriaChildA = DivChildA.idBacteria
JOIN Bacteria AS DivChildB ON BacteriaSplit.idBacteriaChildB = DivChildB.idBacteria
) AS Division
ON (DistBactMeasurement.idBacteria = Division.idBacteriaParent)
LEFT JOIN vBacteriaAreaMeasurement AS BirthArea
ON Birth.frameNo = BirthArea.frameNo
AND DistBactMeasurement.idBacteria = BirthArea.idBacteria
LEFT JOIN vBacteriaAreaMeasurement AS DivisionArea
ON DivisionArea.frameNo = ((SELECT MAX(frameNo) FROM Frame F WHERE F.idExperiment = Experiment.idExperiment AND F.frameNo < Division.frameNo AND ignoreFrame = 'n'))
AND DivisionArea.idBacteria = DistBactMeasurement.idBacteria
LEFT JOIN vBacteriaAreaMeasurement AS PrevArea
ON PrevFrame.frameNo = PrevArea.frameNo
AND DistBactMeasurement.idBacteria = PrevArea.idBacteria
LEFT JOIN vBacteriaAreaMeasurement AS CurrArea
ON DistBactMeasurement.frameNo = CurrArea.frameNo
AND DistBactMeasurement.idBacteria = CurrArea.idBacteria
--// Prev mean
LEFT JOIN vBacteriaMeanMeasurement AS prevBlueMean
ON PrevFrame.frameNo = prevBlueMean.frameNo
AND DistBactMeasurement.idBacteria = prevBlueMean.idBacteria
AND prevBlueMean.channelName = 'Blue'
LEFT JOIN vBacteriaMeanMeasurement AS prevGreenMean
ON PrevFrame.frameNo = prevGreenMean.frameNo
AND DistBactMeasurement.idBacteria = prevGreenMean.idBacteria
AND prevGreenMean.channelName = 'Green'
LEFT JOIN vBacteriaMeanMeasurement AS PrevRedMean
ON PrevFrame.frameNo = PrevRedMean.frameNo
AND DistBactMeasurement.idBacteria = PrevRedMean.idBacteria
AND PrevRedMean.channelName = 'Red'
--// Curr mean
LEFT JOIN vBacteriaMeanMeasurement AS currBlueMean
ON DistBactMeasurement.frameNo = currBlueMean.frameNo
AND DistBactMeasurement.idBacteria = currBlueMean.idBacteria
AND currBlueMean.channelName = 'Blue'
LEFT JOIN vBacteriaMeanMeasurement AS currGreenMean
ON DistBactMeasurement.frameNo = currGreenMean.frameNo
AND DistBactMeasurement.idBacteria = currGreenMean.idBacteria
AND currGreenMean.channelName = 'Green'
LEFT JOIN vBacteriaMeanMeasurement AS CurrRedMean
ON DistBactMeasurement.frameNo = CurrRedMean.frameNo
AND DistBactMeasurement.idBacteria = CurrRedMean.idBacteria
AND CurrRedMean.channelName = 'Red';