Converting linear data into rate data in MySQL and PHP

This article discusses two different strategies for converting linear data into rate data using either MySQL or PHP.  When statistical data is collected from an SNMP agent or a log file, sometimes it gets stored in a database as linear data or as a counter that continually increments.  So, how do you find the rate or actual value between samples?  In this article, we show two different strategies for tackling this particular task.

The MySQL Way

I learned the MySQL way from the MySQL Cookbook.  The MySQL way calls for a SQL self-JOIN query whereby you use a read-ahead row and compare it to the current row.  This is accomplished in the self-JOIN by incrementing the row_id + 1 and calculating the difference in values to the current row_id.
SELECT IF(m2.total_count > m1.total_count, m2.total_count-m1.total_count,0),
   m2.other_stat,
   m2.yet_another_stat,
m2.stat_timestamp
FROM my_stat_table AS m1 INNER JOIN my_stat_table AS m2
   ON m1.stat_id+1 = m2.stat_id
WHERE DATE(m2.stat_timestamp) = DATE(NOW())
ORDER BY m1.stat_id DESC;
  • line 1: The IF() function is used to check to make sure that the next row is > than the current read row. If the next row is greater than the current row, then calculate the difference between the two, otherwise, set the total_count to 0.
  • lines 2-4: we include some other columns of data in our query, say for a graph.
  • lines 5-6: A self-join is performed on same table using the row_id. On one side of the join we increment the ID + 1 to create a "look ahead" row.
  • line 7: the WHERE clause is used to fetch only data that is relevant to the current date
  • line 8: the result set should be sorted by stat_id in descending order.
NOTE: The code above was used in a PHP page that graphed the data using JPGraph. I took that result set, and then reversed it using the PHP function array_reverse($resultset) and fed that data into the JPGraph module.

This all worked great until.....

There came a time that my graphing data schema had to change so that I could store similar data series for more than one entities. My schema had to be changed to support different different entity IDs. This meant that I couldn't guarantee that my SQL JOIN would work, b/c data for the same entity ID might not occur in successive rows. Truth be told, I didn't even test or try to fool with the MySQL way at this point. So, I can't even guarantee that it couldn't be made to work. I felt more comfortable in developing a work-around just in case.

The PHP Way

Ok, so now our data has introduced a new column that represented an entity ID, let's say a server_id. Using the PHP way, you simply get all the data columns "as is" and use a for loop in PHP to simulate the JOIN. The PHP way allows you to use PHP's array index as a pseudo ID for creating a "look ahead" row. Here's an example of the PHP way:

SELECT m.total_count,
             m.other_stat,
             m.yet_another_stat,
             m.stat_timestamp
FROM my_stat_table AS m
WHERE DATE(m.stat_timestamp) = DATE(NOW())
AND m.server_id = 34232 

In the SQL code above, we are querying rows out of the my_stat_table for data points that belong to the server entity whose server_id = 3432. We may or may not be so lucky as to get consecutive row or stat_ids with this query. Because of this, a different means of sorting and comparing is required. At this point, we have linear data that has not been converted into a rate. So, we use PHP to convert this data.

<?php
 
$res = $db->GetAll($sql);
 
$number_of_rows = count($res);
 
# initialize my storage array
$array1 = array();
$array2 = array();
$array3 = array();
 
for($i=0; $i<$number_of_rows; $i++) {
    $j = $i+1;<
 
    if($res[$j][0]) {
        if($res[$j][0] > $res[$i][0]) {
        $difference = $res[$j][0] - $res[$i][0];
    }
    else {
        $difference = 0;
    }
 
    // load my arrays with result set data
    array_push($array1,$difference);
    array_push($array2,$res[$i][1]);
    array_push($array3,$res[$i][2]);
}
?>

Setting video resolution on a Sun U60

A Sun Microsystems Ultra 60 workstation with a Creator 3D card supports a myriad of different resolutions for video output.  This short article is for configuring a Sun U60 w/ a Creator 3D to work with a DELL flat panel 20" monitor.  First, find out what video resolutions the card can support with the following command:

# ffbconfig -prconf

Issuing the following command will yeild the following output:

--- Hardware Configuration for /dev/fbs/ffb0 ---
Type: double-buffered FFB2+ with Z-buffer
Board: rev 2 (Vertical)
PROM Information: @(#)ffb2p.fth 2.9 98/07/14
FBC: version 0x3241906d
DAC: Brooktree 9070, version 1 (Pac2)
3DRAM: Mitsubishi 130b, version 2
EDID Data: Available - EDID version 1 revision 3
Monitor Sense ID: 6  (Unknown 41x31cm RGB color monitor)
Monitor possible resolutions: 1024x768x60, 1024x768x75, 1280x1024x60,
        640x480x60, 1600x1200x60, 1600x1200x60d, 1280x1024x60c
Current resolution setting: 1600x1200x60 Hi-res

Note, that the resolution is set to 1600x1200x60 Hi-res.  That is my preferred video resolution when running x-windows and the various graphical user environments in Solaris.  I noticed that the monitor was incapable of displaying 1600x1200x60 at the console prompt, and I would have to wait for the system to boot before I would get any display.  To change the boot-time resolution, I typed the following:

eeprom output-device=screen:r1280x1024x60 

After the reboot command I was able to see the boot sequence and the console output.  To change resolution of the Frame Buffer, you can issue the following command(s):

# ffbconsole -res 1600x1200x60 now
# ffbconsole -res 1024x768x60 now
# ffbconsole -res 1280x1024x60 now

A good resource is http://www.sunhelp.org/faq/FrameBuffer.html

GeSHi syntax highligting in Joomla

I have successfully installed the latest version of the GeSHi mambot in my new copy of Joomla. It took me a while to get it configured and working, but I think I have it. I found this mambot at waltercedric.com click hereto download. Here are some examples of the mambot in use:

Example 1 - PHP code

<?php
$string_var = "this data-geshi-is data-geshi-a data-geshi-sample data-geshi-string";
$number_var = 12432312;
$array_var  = array( 
    1211--> "server1", 
    1212 => "server2", 
    1213 => "server3" 
);

print $string_var;
print $number_var;

foreach($array_var as $k => $v) {
    print "ID:" . $k . ":" . $v . "\n";
}
?>

Read more ...

Cross-site AJAX Solutions

Well, like so many people, I finally bumped into the issue where SAJAX and/or Prototypes AJAX.Request objects failed to work. It took me a few minutes, but after digging around and examining some remote website logs, I realized that the AJAX.Request that i was using was not hitting the remote server. I quickly found reference to the fact that AJAX tool kits have a security feature of not permitting you to make AJAX calls to remote or different servers than where the AJAX request originates. I was working on a mash-up web page for a project that needed to pull data from two different web servers. The purpose of this article is to show by example how I was able to solve this. Most of the web references indicate three ways of doing it. For the purposes of this article, I am only going to mention two.

  1. creating a proxy-page using PHP
  2. using Apache's mod_rewrite and mod_proxy modules

I started with the first one because it seemed the easier of the two at first. I hadn't compiled in mod_proxy and/or mod_proxy_http, and i didn't know how easy or difficult that would be.

PHP Proxy-page

Create an ajax_project.php page that acts as a "controller" page in the MVC model. In theory, this page would perform all AJAX request functions whether local and/or remote. If my main script has say four separte different AJAX requests, i would pass my ajax_project.php as the URL in the AJAX.Request constructor. Here is a sample of what the Javascript code would look like in my main script:

function getLocalData() {
    // create AJAX.Request here...
    new Ajax.Request ( "ajax_project.php", {
        parameters: funct: 'get_local_data', parm1: 'test1', parm2: 'test2',
        method: post,
        onCreate: showInProgress,
        onComplete: parseData,
        onFailure: showError
    });
}

function getRemoteData() {
    // create AJAX.Request here...
    new Ajax.Request ( "ajax_project.php", {
        parameters: funct: 'get_remote_data', parm1: 'test1', parm2: 'test2',
        method: post,
        onCreate: showInProgress,
        onComplete: parseData,
        onFailure: showError
    });
}

Note the URL in both calls is identical. Now, for the trickery. In both cases, i am going to POST three parameters and values to the same page. The first parameter, called "funct", is to let me know which function in the ajax_project.php page to run. That's the first clue. Now let's create the ajax_project.php page.

For this, we need to check for a POST var called 'funct', and if it's set, call the function that is named by that variable, and pass the remaining args to the function. Since, 'funct' can be one of two different values, i need to have two different functions in the ajax_project.php file to represent those calls. I will also make use of the PHP case or "switch" block in my logic to make the right call. See the sample ajax_project.php below:

<?php
 
if(isset($_POST['funct'])) {
    $funct   = isset($_POST['funct'])? $_POST['funct'] : '';
    $parm1 = isset($_POST['parm1'])? $_POST['parm1'] : '';
    $parm2 = isset($_POST['parm2'])? $_POST['parm2'] : '';
    switch($funct) {
    case 'get_local_data':
        get_local_data($parm1, $parm2);
        break;
    case 'get_remote_data':
        get_remote_data($parm1, $parm2);
        break;
    default:
        print "unknown function passed!";
    }
}
 
function get_local_data($parm1, $parm2) {
    // logic goes here to get data from local datasource
}
 
function get_remote_data($parm, $parm2) {
    // use file_get_contents to fetch data from remote site and return it
    $output = file_get_contents("http://www.remote-server.acme.com/some/url/index.php");
    print $output;
}
 
?>

The above PHP page, acts as a remote proxy page, when the $funct variable is set to 'get_remote_data'. This page calls the PHP function named get_remote_data(), which slurps the remote URL into a variable called $output on line 25. This is then printed back to the Ajax.Request object. Very slick. It was really easy and it works if you need to get things up and running really quick. The other method is to use Apache's mod_rewrite and mod_proxy to do the work.

Using Apache's mod_proxy and mod_rewrite

This was the second method that i tried to accomplish the task of getting cross-site Ajax requests to work. I didn't start here, because when I originally built Apache i compiled it with --enabled-mods-shared=most option. This command specifies that Apache be built with most, but NOT all modules. It builds Apache with a default set of modules that doesn't include any of the mod_proxy family of modules. To get this to work, you will specifically need mod_rewrite, mod_proxy, and mod_proxy_http. Since we already get mod_rewrite with our "default" set of modules, we should build the two mod_proxy modules.

To do this, i navigated to the machine with my source distribution, and typed the following commands:

cd modules/proxy
PATH=$PATH:/usr/local/apache2/bin
apxs -c mod_proxy.c
apxs -c mod_proxy_http.c
cd .libs
cp mod_proxy*.so /usr/local/apache2/module

Wasn't that easy? Alternatively, you could have gone the long route and re-built apache from scratch and typed --enable-mods-shared=all or you could have passed --enable-proxy at configure time.

Now, that the necessary modules are in place, its time to enable the configuration to support our problem. Edit the appropriate Apache conf file, httpd.conf OR ssl.conf. In my case, I needed this to be supported by my SSL server, because the entire site is behind the SSL site. Add the following to the body of the configuration:

RewriteEngine On
RewriteRule /proxy/(.*) http://www.remote-server.acme.com/some/url/$1 [P]

This snippet of code tells Apache to "intercept" or search for any URLs with the word proxy and send them to the URL specified on the right hand side of the rule. It also does a variable substitution at the end to allow us to use different web scripts. All that is left is to reconfigure our main script to use this facility. Below is the amended copy of the main index.php page that supports this configuration:

function getLocalData() {
    // create AJAX.Request here...
    new Ajax.Request (
        "ajax_project.php", {
    parameters: funct: 'get_local_data', parm1: 'test1', parm2: 'test2',
    method: post,
    onCreate: showInProgress,
    onComplete: parseData,
    onFailure:  showError
    });
}
 
function getRemoteData() {
    // create AJAX.Request here...
    new Ajax.Request (
        "proxy/someotherpage.asp", {
    parameters: parm1: 'test1', parm2: 'test2',
    method: post,
    onCreate: showInProgress,
    onComplete: parseData,
    onFailure:  showError
    });
}

Note the URL changed to proxy/someotherpage.asp. When my local Apache server sees this HTTP request, it is going to rewrite the URL to:

http://www.remote-server.acme.com/some/someotherpage.asp

The variables will still be delivered in the POST, and that server will respond but the response will be sent back to my Apache web server, that will relay it back to the originating client Ajax Requestor. You may also note, that the POST var 'funct' is no longer being passed. It is doubtful that the remote administrator of that site is using the same controller construct that we did in the first example, so it's not needed here.

In final, both worked. Both were easily configured. I was a bit scared with the first option b/c of slurping the output into a variable, if the remote site returns a lot of data, that could reach a maximum PHP core size and be inefficient. Of course, it would also implact the second solution, but i guess, i thought it would simply be more efficient in this case to do it from Apache.

Installing Apache 2 on Windows Vista

I have recently performed my first Windows Vista installation. I performed this installation on my Dell Inspiron 6400 laptop. I used the Microsoft Windows Vista Business upgrade edition to upgrade my Windows XP SP2 installation. When performing the installation, I selected the advanced option so that I could have a fresh "clean" installation of Vista. All that worked fine.

Now, for the article. My first application that I wanted to get installed on Vista was the apache web server. I choose to use pre-compiled windows x86 binaries that i was able to obtain from http://www.apachelounge.com/download/. They have made 2.2.8 available w/ OpenSSL 0.9.8g for Windows x86 software. Installation of the apache web server is not nearly as easy as it is on Windows XP. First, you have to deal with the UAC or User Account Control. This is a new security component in Windows Vista. UAC enables users to perform common tasks as non-administrators, called standard users in Windows Vista, and as administrators without having to switch users, log off, or use Run As. A standard user account is synonymous with a user account in Windows XP. User accounts that are members of the local Administrators group will run most applications as a standard user. By separating user and administrator functions while enabling productivity, UAC is an important enhancement for Windows Vista.

Here is a list of the steps that I followed for installing the software:

  1. Install the Microsoft runtime for Microsoft Visual C++ 2005 distributable. The reason is because that is how these binaries were built. You need this to run them.
  2. download the latest software from http://www.apachelounge.com/download/ to your downloads folder.
  3. create a directory called www in the root of your target drive (for me it was c:\www)
  4. extract the software from the zip file to this c:\www directory
  5. edit all configuration files to relfect your directory structure (mine was c:\www\Apache2)
  6. try to run the server by opening a DOS window and typing \www\Apache2\bin\httpd.exe
  7. if you don't have any configuration errors, test it using your web browser by going to http://localhost/

If that works, you have a working Apache installation. Next, you will want to install Apache as a service and set it to automatically start. This is where i ran into issues. The command for installing the Apache web as a service is performed form a Command Prompt:

cd \www\Apache2\bin
httpd.exe -k install 

When I attempted this command, I received the following error message:

Access is denied. : Failed to open the WinNT service manager

Well the trick is to start the command prompt using the "Run As Administrator" option. You can do this by right mouse clicking the icon for the Command Prompt. Select the "Run as administrator" option from the menu that appears. This will launch your command prompt as the system administrator and you can now execute the command to install the service.

cd \www\Apache2\bin
httpd.exe -k install 

That's it. That's what I had to do to get it running.

Applying the singleton pattern to database connections in PHP

The singleton object oriented pattern is a very useful design pattern that can be used to pass objects around in a consistent manner throughout a web-based application. The purpose of this article is to discuss the way in which database connection handles can be passed around a PHP web application by using the singleton design pattern. The pupose of the singleton design pattern can be described quite briefly as follows:

Ensure a class has only one instance and provide a global point of access to it.The singleton class achieves this by creating a new instance the first time it is refrenced, and thereafter it simply returns the handle to the existing instance.

What are some of the advantages of using the singleton class in processing a web page?

  • You have references to an object in multiple places, and you do not want the overhead of creating a new instance of that object for each reference. In my case, I wanted a way to make a sort of "global" database connection handle available to a PHP web application.
  • You may wish to pass the object's state from one reference to another rather than always starting from an initial state.

Read more ...