csummers.org

We're using PHP version 4.x in production at work, and I often find myself needing a function that has been implemented in PHP 5. A good example is the array_walk_recursive() function. I needed this in my framework library to recursively walk through an input array with a callback function to strip values of potentially harmful input. The recursive ability was key because the input array could be arbitrarily large, containing several nested arrays.

Now, I could have implemented my own function to do just this, but when we do move to PHP 5 in production, I'd want to recode to use the standard library function. To the rescue comes the PHP_Compat PEAR package, full of functions and constants to help bridge the gap between different PHP versions.

Thanks to Aidan Lister (the lead developer) and others who contributed to this package. It has saved me much coding, and I'm grateful!

I'm currently reading Programming Ruby (2nd edition) in my pursuit to learn Ruby. This book, known as the PickAxe because of the tool on the front cover, is part tutorial, part language reference, part best-Ruby-practices, and part API/Library reference.

The tendency with a reference book like this is to lump everything about a particular topic into long, boring sections. However, Dave Thomas and co-authors have managed not to do this. The book is good at switching gears frequently enough to keep my focus. Notes comparing Ruby to Perl, Java, and C++ add perspective if you are familiar with those languages. The code samples in this book also set it apart from similar programming books: the examples are concise–giving just enough information to illustrate the point in question.

As I read through the book, I find myself thinking: "Wow, it would've taken me so much more code to do that in Java (or C++ or …)!"

It's nice to see Oracle tipping it's hat to PHP over the last year or so. PHP is very good at meeting specific web needs in the enterprise, and Oracle acknowledges this by including it in their Oracle HTTP Server (Apache, really) package. See the PHP Developer Center for more Oracle/PHP goodness.

I've been learning the Ruby language lately. This language started in Japan, but I think it's catching on like wildfire over here now. It seems to have such a pure way about implementing its features.

Some helpful Ruby references:

• Programming Ruby - online book
• Why's (Poignant) Guide to Ruby - book with cartoon foxes!
• Things That Newcomers to Ruby Should Know - helpful tips
• Ruby-GNOME2 - GNOME/GTK bindings for Ruby
• Ruby Articles - I suggest reading Blocks and Closures and Dynamic Productivity

I've recently been reading Linux Kernel Development (2nd Edition) by Robert Love.

I'm not a kernel hacker, and it's been a while since I've coded in C, but I picked this up to learn more about the nitty gritty details of the operating system I use everyday. I like the special attention given to overall concepts and how certain aspects of the kernel came into being. A good read.

In my last sql post I showed a simple example of pivoting data in SQL when we know the values that we would like to pivot. Often, however, I run into the case where I must pivot data for which:

1. I don't know the values I need to pivot, or
2. there are so many possible values to pivot that I can't possibly give each its own column

A good example of this is summarizing Financial Aid awards by student for a particular term. The university may have thousands of different awards to give out, but a student usually has no more than 10 awards per term.

So, my source table looks something like this:

TERM    ID    AWARD    AMOUNT
----    --    -----    ------
FA04     1     PELL      4050
FA04     1   ESCHOL       500
FA04     1   WKSTDY      2000
FA04     2     PELL      3500
FA04     2   WKSTDY      2000
FA04     3     PELL      4000

Since I can't actually pivot the values into their own columns, I'm going to create AWARD columns 1..n containing the first n awards for each student per term. In order to do this, I need to be able to number the awards per student, and then pivot the number. Oracle's RANK functionality makes this easy:

SELECT
    TERM,
    ID,
    MAX(CASE WHEN RN = 1    THEN AWARD
             ELSE NULL END) AS   AWARD_1,
    MAX(CASE WHEN RN = 1    THEN AMOUNT
             ELSE NULL END) AS   AMOUNT_1,
    MAX(CASE WHEN RN = 2    THEN AWARD
             ELSE NULL END) AS   AWARD_2,
    MAX(CASE WHEN RN = 2    THEN AMOUNT
             ELSE NULL END) AS   AMOUNT_2,
    MAX(CASE WHEN RN = 3    THEN AWARD
             ELSE NULL END) AS   AWARD_3,
    MAX(CASE WHEN RN = 3    THEN AMOUNT
             ELSE NULL END) AS   AMOUNT_3,
FROM
    (SELECT
         RANK() OVER(PARTITION BY
                         TERM,
                         ID
                     ORDER BY
                         TERM,
                         ID,
                         AWARD)  AS RN,
         AWARD,
         AMOUNT
     FROM
         AWARD_TABLE)
GROUP BY
    TERM,
    ID)

And the results:

TERM  ID  AWARD_1  AMOUNT_1  AWARD_2  AMOUNT_2  AWARD_3  AMOUNT_3
----  --  -------  --------  -------  --------  -------  --------
FA04   1   ESCHOL       500     PELL      4050   WKSTDY      2000
FA04   2     PELL      3500   WKSTDY      2000
FA04   3     PELL      4000