Fun with MySQL

[alexander]

So i figured we have talked about code and bash and all that, but we haven't covered any database languages, and they are fun to play with too, so lets go crazy with mysql :beer:

 

so this sniplet will display a list of uniqe values in a table and also the count of those unique values (here it's using hour of a datetime for the values that are to be counted)

 

select distinct hour(wakeup_time) as hour, (select count(*) from calls where hour(wakeup_time)=hour) from calls;

[Experiment Garden]

MySQL is a fun language. I haven't messed around too much with using nested select statements yet. Most of the web applications that I have programmed so far don't require anything more than basic SELECT, UPDATE, and INSERT statements.

 

But while we are on the subject of MySQL I have to share my favorite MySQL based cartoon. If you haven't experienced "little Bobby Tables" from xkcd then you are in for a treat:

 

 

The original comic is found at:

 

xkcd - A webcomic of romance, sarcasm, math, and language - By Randall Munroe

 

An explanation for non-coders can be found at:

 

How to Stop a Hacker - Don't Trust User Input - Experiment Garden

[alexander]

Yea that's a good one, and that cartoon has been around for a while, and it is very funny indeed :)

 

Procedures are pretty easy, sometimes you do have to get creative though, and currently mysql procedures lack a VERY important thing, that is the ability to raise errors from the procedure, which is something much needed and its something they are building into the next version, funny reading the bug/request track, and the engineers are like, "Yeaah, you would want that, huh".... anyways

 

DELIMITER $$
DROP PROCEDURE IF EXISTS do_stuff$$
# procedure to make sure that the time is entered and kept correctly in the database, regardless of what timezone is being used
CREATE PROCEDURE do_stuff(IN atime CHAR(5), IN atime_date CHAR(10), IN timezone CHAR(25))
BEGIN
DECLARE chk CHAR(16);
# make sure you pass me everything
IF (atime>'' and timezone>'') THEN
# say we are scheduling an event for the future, this will make sure that the date is not set in the past, note, if the date is not passed, the event is assumed to be in the next 24 hours
IF (atime_date='') THEN 
SET atime_date=CAST(CONVERT_TZ(NOW(),'UTC',timezone) as DATE); 
END IF;
# generates datetime in the remote time zone
SET adate = CONCAT(atime_date, ' ', atime, ':00');
# creates a date to check against
SET chk = CONVERT_TZ(NOW(),'UTC',timezone);
# this verifies that you didn't pass me a bunch of baloney timezone data
IF (chk>'') then
# checks for timestamp difference, just so noone sets wakeups in the past. this will set the wakeup call in the next 24 hours if you pass it a date that is before now
IF (TIMESTAMPDIFF(MINUTE, adate, chk)>=0) THEN
# this next check is necessary to make sure that if you say the time is 8 and you want an event for 10, it doesnt set the final date at 10 tomorrow
IF (TIMEDIFF(CAST(adate as TIME), CAST(chk as TIME))<=0) THEN 
SET adate = DATE_ADD(CONCAT(CAST(CONVERT_TZ(NOW(),'UTC',timezone) as DATE), ' ', atime, ':00'), INTERVAL 1 DAY);
ELSE
SET adate = CONCAT(CAST(CONVERT_TZ(NOW(),'UTC',timezone) as DATE), ' ', atime, ':00');
END IF;
END IF;
# convert the call time and date back to UTC
SET adate = CONVERT_TZ(adate,timezone,'UTC');
# generate a query
SET @query= CONCAT("INSERT INTO some_table (orig_time, event_date_time) VALUES ('", atime, "', '", adate, "'");
#prepare and execute the query
PREPARE qry FROM @query;
EXECUTE qry;
DEALLOCATE PREPARE qry;
END IF;
END IF;
END $$
DELIMITER ;

[freeztar]

IF EXISTS do_stuff$$

 

Cause it's all about money, money, money. :) :eek:

[alexander]

its all about the delimiter, delimiter, delimiter :)