Mysql stored procedure and debugging

Yesterday I had this problem: I have a small application that logs when I switch my computer on and off. I have a webpage where I can change the times logged and also add remarks like what I did that day.  So a simple query can calculate my workhours for that day. But I should work 8 hours a day at my current job. So what if I make more or less? I want to be able to have an extra column that is the sum of the daily diff.

So I build the following solution. These are sketches. Do not expect these things to work out of the box. But if you know a little about Linux and MySQL you should have no problem pasting it together.

So the MySQL table looks like this:

CREATE TABLE `hours` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`workday` date DEFAULT NULL,
`start` time DEFAULT NULL,
`stop` time DEFAULT NULL,
`worked` time DEFAULT NULL,
`saldo` time DEFAULT NULL,
`som` time DEFAULT NULL,
`remark` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `workday` (`workday`)
)

The startup/shutdown registration script looks like this:

case “$1” in
start)
echo -n “Registering workhour start: ”
mysql -u workedhours -pworkedhours -e ‘insert into workedhours.hours set workday=curdate(),start=now();’
echo “OK”
;;
stop)
echo -n “Registering workhour end: ”
mysql -u workedhours -pworkedhours -e “use workedhours;update hours set stop=now() where workday=curdate(); update hours set worked=timediff(timediff(stop,start),’0:30:0′) where workday=curdate();update hours set saldo=timediff(worked,’8:00:00′) where workday=curdate();”
echo “OK”

Challenge

Now how can I calculate the accumulated time diffs? I am not a DBA but I guessed I should use what they call ‘cursors’. That is the only thing to loop through a set of records. But they can only be used in MySQL in a stored procedure. Ah, two things I have never used before 🙂

Anyway, the stored procedure is as follows:

DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`jbaten`@`localhost`*/ /*!50003 PROCEDURE `calcsum`()
BEGIN
declare l_proc_id varchar(100) default ‘test_calcsum’;
declare done INT DEFAULT 0;
DECLARE id1 INT DEFAULT 0;
DECLARE som time default “0:0:0″ ;
DECLARE timeworked TIME;
DECLARE cur1 CURSOR FOR SELECT id,saldo FROM workedhours.hours  order by workday;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
call debug.debug_on(l_proc_id);
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO id1,timeworked;
select ifnull(timeworked,”0:0:0”) into timeworked;
— if timeworked <> null then
call debug.debug_insert(l_proc_id,concat(“stap 1:id=”,id1,”->timeworked=”,timeworked,”som=”,som));
select addtime(som,timeworked) INTO som;
call debug.debug_insert(l_proc_id,concat(“stap 2:som->”,som));
— else
—  call debug.debug_insert(l_proc_id,concat(“timeworked=null->”,timeworked));
— end if;

IF done THEN
— call debug.debug_insert(l_proc_id,concat(“done:”,done));
LEAVE read_loop;
END IF;
— call debug.debug_insert(l_proc_id,concat(‘som=’,DATE_FORMAT(som, ‘%d-%b-%y %h:%i:%S %p’)));
update workedhours.hours set som=som where id=id1;
END LOOP;
CLOSE cur1;
— call debug.debug_insert(l_proc_id,’Ending procedure calcsum’);
— call debug.debug_off(l_proc_id);
END */;;
DELIMITER ;

More info about these ‘call debug’ things are found here. It helps you to debug stuff. Anyway, it all works. Have fun with it.

This entry was posted in Geen categorie. Bookmark the permalink.