I’m currently working on my Master degree thesis and I’m projecting a demo database about a Mammography archive. The purpose is providing an archive, whose clinical cases can be consulted on a portal page by a user interface.
I must create a database for a Liferay portal. Liferay is a very powerful opensouce CMS / web application framework written in Java that allows creating the database and the queries by an xml file.
Before developing the portlet I need to study the situation. I’m analyzing and evaluating the query formulations, as I will have to develop GUIs to consult the image (Echography) archive and upload new cases.
I’ve started with some sql scripts.
I’ve made a sql script to create and populate the Mammography database. Then I’ve made another script to add the archive zip file size of each clinical case and a stored procedure to update the volume table (grouping clinical cases of the same category) with the sum of the cases’ zip file size.
You can download the ER diagram clicking on the image above (powered by MySQLWorkbench!).
The script to create and populate the DB is:
/*Mammography demo database */
CREATE DATABASE `mammography`;
/* access database */
USE `mammography`;
CREATE TABLE scanner (
id_scanner INT(2) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
scanner_type VARCHAR(100) NOT NULL
);
CREATE TABLE volume (
id_volume INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
volume_name VARCHAR(100) NOT NULL,
cases INT(10), /* filled by a query */
total_size VARCHAR(10), /*filled by a query calculating the Gb*/
id_scanner INT(2) NOT NULL,
bits INT(10) NOT NULL,
resolution VARCHAR(50) NOT NULL, /*es. 42 microon , might be calculated*/
overview TEXT,
FOREIGN KEY(id_scanner) REFERENCES scanner (id_scanner)
);
/* 1:N* - one volume - n cases*/
CREATE TABLE case_archive (
id_case INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
case_name VARCHAR(100) NOT NULL,
id_volume INT(10) NOT NULL REFERENCES volume(id_volume) ,
ics_version VARCHAR(50) NOT NULL,
date_case DATE NOT NULL,
age_patient INT(3) NOT NULL,
film VARCHAR(50) ,
film_type VARCHAR(50) ,
density INT(10) NOT NULL,
digitizer_dba INT(10) NOT NULL,
notes TEXT,
zip_folder_link TEXT NOT NULL,
FOREIGN KEY (id_volume) REFERENCES volume(id_volume)
);
/* 1 image type : N images*/
CREATE TABLE image_type (
id_image_type INT(2) NOT NULL AUTO_INCREMENT PRIMARY KEY,
image_type VARCHAR(100)
);
/* 1:N* - one case - n images*/
CREATE TABLE image (
id_image INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
id_case INT(10) NOT NULL,
image_name VARCHAR(150),
image_type INT(2) NOT NULL,
link TEXT NOT NULL,
bits_pixel INT(10) NOT NULL, /*might be null*/
resolution VARCHAR(50) NOT NULL,
left_cc_lines int(10) NOT NULL,
pixels_per_line INT(10) NOT NULL,
/* the following column might null in normal cases - demo:only 2 columns */
total_abnormality INT(10),
abnormality INT(2),
FOREIGN KEY (id_case) REFERENCES case_archive (id_case),
FOREIGN KEY(image_type) REFERENCES image_type (id_image_type)
);
/*————————————————————————————*/
/* 3 scanner types */
insert into scanner(scanner_type) values('DBA');
insert into scanner(scanner_type) values('HOWTECK');
insert into scanner(scanner_type) values('LUMISYS');
/* 2 Volumes, 2 Cases X Volume*/
insert into volume (volume_name, id_scanner, bits, resolution, overview) values ('normal_01', 1, 16,'42 microns', 'overview notes ');
insert into volume (volume_name, id_scanner, bits, resolution, overview) values ('cancer_01', 3, 12, '50 microns', 'overview notes');
/* Image type*/
insert into image_type(image_type) values('Left_cc 0');
insert into image_type(image_type) values('Right_cc 1');
insert into image_type(image_type) values(' Left_mcl 2');
insert into image_type(image_type) values(' Right_mcl 3');
/* 2 Cases*/
insert into case_archive (case_name, id_volume, ics_version, date_case, age_patient, density, digitizer_dba, notes, zip_folder_link) values ('A-0002-1', 1, 1.0,'2008-06-13', 63, 2, 21,'n/a','/home/laura/project/case/normal1_case0001.zip' );
insert into case_archive (case_name, id_volume, ics_version, date_case, age_patient, density, digitizer_dba, notes, zip_folder_link) values ('A-0002-1', 1, 1.0,'2007-04-23', 43, 3, 23,'n/a','/home/laura/project/case/normal1_case0002.zip' );
insert into case_archive (case_name, id_volume, ics_version, date_case, age_patient, density, digitizer_dba, notes, zip_folder_link) values ('A-0002-1', 2, 1.0,'2009-07-12', 56, 2, 20,'n/a','/home/laura/project/case/cancer1_case0001.zip' );
insert into case_archive (case_name, id_volume, ics_version, date_case, age_patient, density, digitizer_dba, notes, zip_folder_link) values ('A-0002-1', 2, 1.0,'2007-03-12', 48, 3, 19,'n/a','/home/laura/project/case/cancer1_case0002.zip' );
/* 2 images x case*/
insert into image(id_case, image_name, image_type, link, bits_pixel, resolution, left_cc_lines, pixels_per_line, total_abnormality, abnormality) values ( 1, 'A_0002_1.LEFT_CC.LJPEG',1,'/home/laura/Arbeitsfläche/Project/case/normal1_case0001/A_0002_1.LEFT_CC.LJPEG',16,42,4349, 1979, 234, 1);
insert into image(id_case, image_name, image_type, link, bits_pixel, resolution, left_cc_lines, pixels_per_line, total_abnormality, abnormality) values ( 1, 'A_0002_1.RIGHT_CC.LJPEG',2,'/home/laura/Arbeitsfläche/Project/case/normal1_case0001/A_0002_1.RIGHT_CC.LJPEG',12,32,4229, 1959, 214, 2);
insert into image(id_case, image_name, image_type, link, bits_pixel, resolution, left_cc_lines, pixels_per_line, total_abnormality, abnormality) values ( 2, 'A_0003_1.LEFT_CC.LJPEG',1,'/home/laura/Arbeitsfläche/Project/case/normal1_case0002/A_0003_1.LEFT_CC.LJPEG',24,62,2359, 1629, 267, 1);
insert into image(id_case, image_name, image_type, link, bits_pixel, resolution, left_cc_lines, pixels_per_line, total_abnormality, abnormality) values ( 2, 'A_0003_1.RIGHT_CC.LJPEG',2,'/home/laura/Arbeitsfläche/Project/case/normal1_case0002/A_0003_1.RIGHT_CC.LJPEG',16,42,4349, 1979, 234, 1);
insert into image(id_case, image_name, image_type, link, bits_pixel, resolution, left_cc_lines, pixels_per_line, total_abnormality, abnormality) values ( 3, 'C_0001_1.LEFT_MLO.LJPEG',3,'/home/laura/Arbeitsfläche/Project/case/cancer1_case0001/C_0001_1.LEFT_MLO.LJPEG',24,46,3649, 2979, 734, 5);
insert into image(id_case, image_name, image_type, link, bits_pixel, resolution, left_cc_lines, pixels_per_line, total_abnormality, abnormality) values ( 3, 'C_0001_1.RIGHT_MLO.LJPEG',4,'/home/laura/Arbeitsfläche/Project/case/cancer1_case0001/C_0001_1.RIGHT_MLO.LJPEG',32,47,7349, 1363, 734, 1);
insert into image(id_case, image_name, image_type, link, bits_pixel, resolution, left_cc_lines, pixels_per_line, total_abnormality, abnormality) values ( 4, 'C_0001_2.LEFT_MLO.LJPEG',3,'/home/laura/Arbeitsfläche/Project/case/cancer1_case0002/C_0002_1.LEFT_CC.LJPEG',32,92,3549, 3779, 374, 1);
insert into image(id_case, image_name, image_type, link, bits_pixel, resolution, left_cc_lines, pixels_per_line, total_abnormality, abnormality) values ( 4, 'A_0001_2.LEFT_CC.LJPEG',4,'/home/laura/Arbeitsfläche/Project/case/cancer1_case0002/C_0002_1.RIGHT_CC.LJPEG',16,42,4349, 1979, 234, 1);
————————————————————————-
Then I’ve considered adding columns:
ALTER TABLE volume ADD COLUMN total_size VARCHAR(10);
ALTER TABLE case_archive ADD COLUMN size_zip_MB FLOAT NOT NULL (10);
And populate them:
UPDATE case_archive SET size_zip_MB=200.1 where id_case=1;
UPDATE case_archive SET size_zip_MB=460.2 where id_case=2;
UPDATE case_archive SET size_zip_MB=2154.5 where id_case=3;
UPDATE case_archive SET size_zip_MB=225.3 where id_case=4;
To calculate the sum of the zip files’ size of the cases for each row of the volume table I’ve made the following procedure:
DELIMITER //
CREATE PROCEDURE volume_total_size()
BEGIN
DECLARE i int(10);
DECLARE l int(10);
SET i=0;
set l= (SELECT COUNT(*) from volume);
WHILE i<l DO
SET i=i+1;
UPDATE volume SET total_size= (select IF(sum(case_archive.size_zip_MB)>1024.0, CONCAT(TRUNCATE(sum(case_archive.size_zip_MB)/1024,1),'GB' ),
CONCAT(sum(case_archive.size_zip_MB),'MB')) as total_size
from case_archive
where id_volume=i)
where id_volume=i;
SELECT * FROM volume;
END;
To invoke procedure type in the console:
MySQL> call volume_total_size();
You can also copy the command in a new *.sql file and launch the script.
I’ve tested the whole “project” with MySQL 5.5.
I have decided to share this because I have spent several hours trying to understand how to update the volume table with the sum of the file size.
Notice that in the ‘case_archive‘ table the column ‘size_zip_MB‘ type is INT(10) and you have to insert the size in MB, while in ‘volume‘ the ‘total_size‘ column type is VARCHAR(10). MySQL 5.5 casts the column type automatically 🙂
