Posts Tagged ‘sql’

MySQL Stored Procedure for calculating total file_size in MByte or GByte

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 🙂

Perl and SQLite, free pdf download

You can download “Perl Scripting for SQLite”.
It’s a pdf of the slides I’ve made for the “Linux day 2011” in my hometown.
Have fun!

Perl script for a Hollywood Sqlite database

Let’s analyze the following perl scripta “cinema.pl”, to create and populate a simple database about  movies, and “query_hollywood.pl” to execute a simple SELECT-FROM-WHERE query on it.

—————————————cinema.pl————————————————————

#!/usr/bin/perl -w

use DBI;
use strict;

# CONFIG VARIABLES
my $platform = “SQLite”;
my $database = “hollywood.db”;
my $host = “localhost”;
my $port = “3306”;
my $user = “username”;
my $pw = “password”;

# DATA SOURCE NAME
my $dsn = “dbi:$platform:$database:$host:$port”;

# PERL DBI CONNECT
my $dbh = DBI->connect($dsn, $user, $pw) or die “Cannot connect: $DBI::errstr”;

# creating the “hollywood” database
$dbh->do(“CREATE TABLE IF NOT EXISTS actors(aid integer primary key, name text)”);

$dbh->do(“CREATE TABLE IF NOT EXISTS  movies(mid integer primary key, title text)”);

$dbh->do(“CREATE TABLE IF NOT EXISTS actors_movies(id integer primary key, mid integer, aid integer)”);

#populating “actors” table
$dbh->do(“INSERT INTO actors(name) VALUES(‘Philip Seymour Hofman’)”);
$dbh->do(“INSERT INTO actors(name) VALUES(‘Kate Shindle’)”);
$dbh->do(“INSERT INTO actors(name) VALUES (‘Kelci Stephenson’)”);
$dbh->do(“INSERT INTO actors(name) VALUES(‘Al Pacino’)”);
$dbh->do(“INSERT INTO actors(name) VALUES(‘Gabrielle Anwar’)”);
$dbh->do(“INSERT INTO actors(name) VALUES(‘Patricia Arquette’)”);
$dbh->do(“INSERT INTO actors(name) VALUES(‘Gabriel Byrne’)”);
$dbh->do(“INSERT INTO actors(name) VALUES(‘Max von Sydow’)”);
$dbh->do(“INSERT INTO actors(name) VALUES(‘Ellen Burstyn’)”);
$dbh->do(“INSERT INTO actors(name) VALUES(‘Jason Miller’)”);

#populating “movies” table

$dbh->do(“INSERT INTO movies VALUES(1,’Capote’)”);
$dbh->do(“INSERT INTO movies VALUES(2,’Scent of a woman’)”);
$dbh->do(“INSERT INTO movies VALUES(3,’Stigmata’)”);
$dbh->do(“INSERT INTO movies VALUES(4,’Exorcist’)”);
$dbh->do(“INSERT INTO movies VALUES(5,’Hamsun’)”);

#populating “actorsMovies” table
$dbh->do(“INSERT INTO actors_movies VALUES(1,1,1)”);
$dbh->do(“INSERT INTO actors_movies VALUES(2,2,1)”);
$dbh->do(“INSERT INTO actors_movies VALUES(3,3,1)”);
$dbh->do(“INSERT INTO actors_movies VALUES(4,4,2)”);
$dbh->do(“INSERT INTO actors_movies VALUES(5,5,2)”);
$dbh->do(“INSERT INTO actors_movies VALUES(6,6,3)”);
$dbh->do(“INSERT INTO actors_movies VALUES(7,7,3)”);
$dbh->do(“INSERT INTO actors_movies VALUES(8,8,4)”);
$dbh->do(“INSERT INTO actors_movies VALUES(9,9,4)”);
$dbh->do(“INSERT INTO actors_movies VALUES(10,10,4)”);
$dbh->do(“INSERT INTO actors_movies VALUES(11,8,5)”);

print qq{“Hollywood” database created! \n };

$dbh->disconnect;

—————————————————query_hollywood.pl———————————————

#!/usr/bin/perl -w

use DBI;
use strict;

# CONFIG VARIABLES
my $platform = “SQLite”;
my $database = “hollywood.db”;
my $host = “localhost”;
my $port = “3306”;
my $user = “username”;
my $pw = “password”;

# DATA SOURCE NAME
my $dsn = “dbi:$platform:$database:$host:$port”;

# PERL DBI CONNECT
my $dbh = DBI->connect($dsn, $user, $pw) or die “Cannot connect: $DBI::errstr”;

# EXECUTE THE QUERY
my $query = “SELECT actors.name , movies.title  FROM actors,movies,actors_movies WHERE actors.aid=actors_movies.aid and
movies.mid=actors_movies.mid”;

my $sth=$dbh->selectall_arrayref($query);

print “Actor                                                          Movie \n” ;
print “======================  ====================\n”;

foreach my $row (@$sth) {
my ($name, $title) = @$row;

### Print out the table metadata…
printf “%-23s %-23s \n”, $name, $title;

}

$dbh->disconnect;

—————————————————————————————–

Make the perl scripts executable like:
$ sudo chmod +x script.pl

And run them liket:
$ ./script.pl

The result of the query is:

Actor                                   Movie
====================  ====================
Philip Seymour Hofman     Capote
Philip Seymour Hofman     Scent of a woman
Philip Seymour Hofman     Stigmata
Kate Shindle                      Exorcist
Kate Shindle                      Hamsun

 

Very nice script, isn’t it?
I guess the DBI deserves further attention…

Sqlite database backup: the .dump command

Let’s go on mastering our sqlite3 knowledge.
SQLite database is really just a file: a backup it’s as simple as copying one file.

The .dump command shows information about all the changes performed onto the database. Less pieces of information to the hidden file can be found in your home/user typing: $ ~/.sqlite_history.

$ sqlite3 test.db “.dump”

The result is:
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE n(id INTEGER PRIMARY KEY, f TEXT, l TEXT);
INSERT INTO “n” VALUES(1,’linus’,’torvalds’);
INSERT INTO “n” VALUES(2,’richard’,’stallman’);
COMMIT;

If you want to backup the database in a new file, you can specify a name (ex. “dbbackup”):
$ $ sqlite3 test.db ‘.dump’ > dbbackup

The contents of the backup can be modified.
For example you can filter and pipe it to another database. Below, table “n” is changed to “people” with the sed command, and it is piped into the “computer_pioneers” database.

$ sqlite3 test.db “.dump”|sed -e s/n/people/|sqlite3 computer_pioneers.db
The contect is the same:
$sqlite3 computer_pioneers.db “select * from people”;

First steps in Sqlite and Perl

Sqlite is useful to create a database (one file, .db extension) used to store configuration data, used Miicrosoft, Skype, Banshee… Smart Phone applications….

The file extension .db stands for a whole database: it’s made by the software C library called Sqlite

This is a great opportunity to learn about SQLITE and the Perl scripting languages… great, don’t you think?
Let’s kill two birds with a stone!
Install Sqlite3 and follow me:
On UBUNTU, DEBIAN, etc:
$ sudo apt-get install sqlite3
On OPEN-SUSE:
$ sudo zypper install sqlite3
On REDHAT, CentOS, or FEDORA:
$ yum install SQLite3

BASH EXAMPLE
Let’s create an example: a database called “test.db” by the (unix) shell by the following command:

:~$ sqlite3 test.db “create table if not exists user(id INTEGER PRIMARY KEY, name TEXT,  surname TEXT);”

Let’s fill it:
:~$ sqlite3 test.db “insert into user (name, surname) values (‘linus’,’torvalds’);”

:~$ sqlite3 test.db “insert into user(name,surname) values(‘richard’, ‘stallman’)”;

To check it out:
:~$sqlite3 test.db “select * from n”;

The result is:
1|linus|torvalds
2|richard|stallman

Alternatively you can create a database entering the sqlite3 enviroment
$ sqlite3 test.db
SQLite version 3.0.8
Enter “.help” for instructions
Enter SQL statements terminated with a “;”
sqlite>

In the sqlite3 enviroment you can use pure SQL statements to work with your database (in this case the test.db).
You can even change a few default settings to make the ouput of the commands look better. For example the column .mode and the .headers commands.They will last you exit the SQLite shell or change them to something else.
sqlite> .mode col
sqlite> .headers on

To see all the tables and views type:
sqlite> .tables

To see the databases that are currently open use the .databases command. It will show the main and temp databases and where they are on the system:
sqlite> .databases

To exit type .quit or .exit:
sqlite> .quit

Anyway it’s better to work in the shell prompt directly, that allows you to run bash scripts.
In this example the prompt is in your home/user directory. Check it out by the pwd command if you’re not sure…

PERL EXAMPLE

Make a file called test.pl :
$ touch test1.pl

Use an editor(ex. gedit under ubuntu and opensuse) or the cat test1.pl command to fill it with the following script:


#!/usr/bin/perl -w

use DBI;
use strict;

my $db = DBI->connect(“dbi:SQLite:test.db”, “”, “”) or die “couldn’t connect to db”.DBI->errstr;

$db->do(“CREATE TABLE IF NOT EXISTS user (id INTEGER PRIMARY KEY, name TEXT, surname TEXT)”);
$db->do(“INSERT INTO user\(name, surname) VALUES ( ‘linus’, ‘torvalds’)”);
$db->do(“INSERT INTO user\(name, surname) VALUES ( ‘richard’, ‘stallman’)”);

my $all = $db->selectall_arrayref(“SELECT * FROM USER”);

foreach my $row (@$all) {
my ($id, $name, $surname) = @$row;
print “$id|$name|$surname \n”;

}

$db->disconnect;

Alternatively you can make the file directly by the shell:
$ cat > test.pl
.. perl script content…

Type Ctlr+C to close the file and exit.
Now check the file content:
$> cat test.pl

Make the perl script executable:
$ sudo chmod +x test.pl
Then run the perl script simply:
$ ./test.pl
The result is:
1|linus|torvalds
2|richard|stallman

In the same folder where the script lies, you can find a file called “test.db”. That’s the Sqlite database. Just one file.

Yeah! So we’ve learned some Perl and SQLite, right?

Categories
Links: