About Adam

Adam Stout works in the technology field and is currently located in San Diego, CA.

Topics

Search

Connect

Recent Entries

ATOM Feed

I'm currently in the process of converting one of my web applications from static tables all generated on the server side to a YUI based implementation which uses a lot of JavaScript calls to get the data on the client side to display. This has some distinct advantages but also some disadvantages.

For instance, where I used to use this PHP function to convert a file size (in bytes) to a string that would display nicely:


/**
                     Format Bytes to a human readable format
                     * @param int $BYTES Size in bytes
                     * @param string $SIZE Format to use (k,m,g,t,best)
                     * @param int $DECIMALS Format string to use in
                     * @return string Formated string
                     * @todo Support k-t size
                     * @deprecated Use the MySQL UDF to do this in database
                     */
           function byteFormat($BYTES$SIZE="BEST"$DECIMALS=2){
                                    if(substr(strtolower($SIZE),0,1) == "b"){
                                        if($BYTES pow(1024,4)){
                                            $SIZE "t";
                                        } elseif($BYTES pow(1024,3)){
                                            $SIZE "g";
                                        } elseif($BYTES pow(1024,2)){
                                            $SIZE "m";
                                        } else {
                                            $SIZE "k";
                                        }
                                    }
                                    switch(substr(strtolower($SIZE),0,1)){
                                        case "k":
                                            $FORMATED_DATA number_format(($BYTES pow(1024,1)),$DECIMALS) . "kb";
                                            break;
                                        case "m":
                                            $FORMATED_DATA number_format(($BYTES pow(1024,2)),$DECIMALS) . "mb";
                                            break;
                                        case "g":
                                            $FORMATED_DATA number_format(($BYTES pow(1024,3)),$DECIMALS) . "gb";
                                            break;
                                        case "t":
                                            $FORMATED_DATA number_format(($BYTES pow(1024,4)),$DECIMALS) . "tb";
                                            break;
                                    }
                                    return $FORMATED_DATA;
           }
I need to now use a SQL function to do the processing. (I could still use PHP, but that would require processing the data more than I want to). I've been doing a lot of work in PL/SQL lately so I decided to write a MySQL UDF to accomplish this instead of a huge case statement. While there are a lot of examples for PL/SQL functions, I had a hard time finding a good example of what a MySQL UDF looked like so I thought I would post this in case anyone else is going through what I did.
DROP FUNCTION IF EXISTS byteFormat;
CREATE FUNCTION byteFormat (bytes INTEGER
                                       ,desiredSize VARCHAR(30)
                                       ,decimalPlaces INTEGER)
RETURNS VARCHAR(255)
BEGIN

    DECLARE formatedString VARCHAR(250) DEFAULT '';

    IF LOWER(SUBSTRING(desiredSize FROM 1 FOR 1)) = 'b' THEN
                                   SET desiredSize = CASE
                                                         WHEN (bytes POWER(1024,4)) THEN 't'
                                                         WHEN (bytes POWER(1024,3)) THEN 'g'
                                                         WHEN (bytes POWER(1024,2)) THEN 'm'
                                                         ELSE 'k'
                                                     END;
    END IF;
           SET formatedString = CASE
                                  WHEN LOWER(SUBSTRING(desiredSize FROM 1 FOR 1)) = 'k' THEN CONCAT((FORMAT(bytes POWER(1024,1),2)) , 'kb')
                                  WHEN LOWER(SUBSTRING(desiredSize FROM 1 FOR 1)) = 'm' THEN CONCAT((FORMAT(bytes POWER(1024,2),2)) , 'mb')
                                  WHEN LOWER(SUBSTRING(desiredSize FROM 1 FOR 1)) = 'g' THEN CONCAT((FORMAT(bytes POWER(1024,3),2)) , 'gb')
                                  WHEN LOWER(SUBSTRING(desiredSize FROM 1 FOR 1)) = 't' THEN CONCAT((FORMAT(bytes POWER(1024,4),2)) , 'tb')

           END;
    RETURN formatedString;
END;



BTW, in the MySQL documentation it shows the DECLARE statement but doesn't emphasize enough that the DEFAULT is mandatory. It took me a while to figure that out.

Comments:



Please log on to add your comments.
Previous
Next