Posts Tagged ‘procedure’

PL/SQL Transactional single rows locking with cursors

Oracle already provides an automatic exclusive locking mechanism for the rows you want to update. However you can override it to customize the performance (for example, if you need consistent data and/or exclusive access for the duration of a whole and more complex transaction).

When your application requires consistent data for the duration of the transaction, not reflecting changes by other transactions, you can achieve transaction-level read consistency by using explicit locking, read-only transactions, serializable transactions, or by overriding default locking.

Row locking at transactional level can be achieved with “SELECT FOR UPDATE” statement. The lock is released after a commit or rollback only.

If you want to lock single rows in Oracle PL/SQL you can use cursors, pointing at the rows you want to lock.

The following example show you how to lock single rows:

CREATE OR REPLACE PROCEDURE LOCK_ORDER_ENTRY 
(id_number IN number, system_user IN varchar2)
IS
/*"order_row" is a variable to store the row found by the cursor select statement   */
order_row order_queue%rowtype;
cursor c1 is
    SELECT * 
    FROM order_queue 
    WHERE id=id_number
    FOR UPDATE NOWAIT;
BEGIN
/* first of all you need to open the cursor */
OPEN c1;
/* then you need to fetch the content in the variable */
   LOOP
      FETCH c1 INTO order_row;
/* the lock will be released after the commit */
      IF (c1%found) THEN
         UPDATE order_queue SET processed=1, user=system_user where CURRENT OF c1;
         COMMIT; 
      END IF;
 /* then you need close the cursor */
  END LOOP;
CLOSE c1;
END LOCK_ORDER_ENTRY;

The “select for update” statement has two possible variants
– FOR UPDATE NO WAIT, that triggers an error if the row is locked by another user.
– FOR UPDATE SKIP LOCKED, that fastens the execution by skipping the already locked rows

If you need to lock a single row in the database you don´t need a loop.

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 🙂

Categories
Links: