Dave Rowe’s Blog

I recently encountered a situation where I had a query that was built with quite a few joins (~8), and I found that the query was taking a bit longer than I expected, especially for the number of rows to look at / return.  With each join, you add in more complexity for MySQL to handle in how to best utilize indexes, etc.

(more…)

I use KDE as my main desktop environment. Recently I was rebuilding an installation, and saw my clock was set to the 24-hour style, instead of the American style of 12-hour with AMPM. Clicking through the settings on the clock widget itself, I found no settings for getting that changed back. I always find out where, but not for a couple hours. So, to post this here, such that I’ll find it next time, you simply go into System Settings -> Regional & Language -> Time & Dates (tab) and select pH:MM:SS AMPM from the select box.

You may need to logout / login to restart the clock and make the setting take effect.

Note to ArchLinux users: If you decide to rebuild an installation, and of course you’re going to use yaourt for community built packages, you need to remember to install ‘base-devel’. If you don’t install ‘base-devel’ you may receive vague messages like “Unable to read PKGBUILD for “. For me, the solution was a simple ‘pacman -S base-devel’

If you ask someone for an export of data, and you know the data is coming from SQL Server, be sure to clarify what encoding you’d like the export in (if they can configure it) – I spent a bit of time trying to figure out why I couldn’t reliably read a file, and by using a hexeditor, I found the leading bytes were the culprit. Comparing to a listing on Wikipedia, I found the file was in UTF-16, when I’m expecting simple UTF-8 or ASCII. Easy solution though if you’re on a *nix machine:

iconv -f UTF-16 -t UTF-8 input_file > output_file

And you’re done! Easy as pie…when you know what the problem is.

For a project, we have the need to create charts dynamically from data.  In another project, we’ve used ChartDirector for this.  It has worked great there, so we pulled it into this project as well.  Now, the type of charts I was working with in particular is a stacked percentage chart, which is kind of like a mash between a pie chart and a traditional bar chart.  An example:

Now, with dynamic data, you can’t predict what your data will look like, and your code needs to be flexible enough to handle any situation without causing headaches for the user.  With ChartDirector, you pass in datasets via arrays across the chart, so for example the above datasets would be created by:

$data0 = array(100, 125, 245, 147, 67);
$data1 = array(85, 156, 179, 211, 123);
$data2 = array(97, 87, 56, 267, 157);

Such that, the numbers line up in the arrays (vertically) in how they correspond to the resulting chart. In my situation and test data, I found I had a situation like the following:

$data0 = array(100, 125, 0, 147, 67);
$data1 = array(85, 156, 0, 211, 123);
$data2 = array(97, 87, 0, 267, 157);

So, the bar for the 3rd item in the chart would be distributed equally as 33.33% when, in fact, there was no data. I assumed the chart would display a blank spot for that bar. After searching the less than optimal support forums (to no fault of the creators / maintainers), I found I needed to instead use a constant defined in the ChartDirector code – ‘NoValue’, where I had…wait for it….no value. Putting a small check in my code to replace zeros with ‘NoValue’ proved to produce the results I was after.

Note to fellow developers: If you’re posting in a forum for assistance, please be more verbose in your subject line. It really helps with searching if the subject can provide some bit of context around what the problem is.

FYI – If you’re using jQuery in your application, and you’re trying to submit a form programmatically (ie, $(‘#myform’).submit(); ), you’ll want to make sure you don’t have a button with an ID/Name of ‘submit’ – the code fails silently, with no indication of why. This is something that caught me today, and was somewhat frustrating since it was such a basic concept of getting a form to fire.

Reference: http://api.jquery.com/submit/#comment-30950448 – Thanks Scotty!

You can now sign-in to Facebook Chat using your favorite XMPP/Jabber client (Pidgin, Adium, Kopete, etc). If you’re on Linux (Arch, specifically) you’ll need to install the cyrus-sasl package.

I love SSH tunnels. I use them as a cheap VPN solution when traveling, and if I need to get access to an internal web server on the inside of a network (assuming the network isn’t separated). As an example, I have 2 computers at home which I use daily for development, etc. When traveling, I have a laptop that I use. Well, I use VirtualBox at home, since the computers there have plenty of RAM to support it, where my laptop isn’t as VM friendly (its old, but has served me well, and will continue to do so until it croaks.), so I needed a way to access my applications running on the VM while on the go. Enter SSH tunnels. SSH tunnels work by opening a port over which traffic can flow to the remote location. Using ‘dynamic ports’, you get a SOCKS proxy.

You create SSH tunnels using:

ssh -D 8080 username@remote_server

Which opens port 8080 on the local machine. Then, you can configure your browser of choice to use a SOCKS v5 Proxy at 127.0.0.1:8080. Specifically in Firefox, make sure that none of your other proxy settings are set.

It should look like the following:

Now, you can check the IP address for your connection by visiting a site like: http://www.whatsmyip.org/

A quick shout-out to a great product. Concrete5 is an excellent CMS. With easy theming, and even easier setup, it’s a snap solution for some of the most particular of tastes.

It’s open-source, which I really like, but the ease of getting it setup, and the polished look and feel just make me happy to use it.

Great work guys!

At work, we’re developing an application that uses LDAP for authentication. Specifically, we’re using OpenLDAP. We use a VM for development, which allows each developer to have a copy of the ‘standard’ environment, to ensure we’re on the same version of libraries, compilers, databases, etc. As part of managing the VMs, we write maintenance scripts to keep everyones VM in line with each other. I wrote a script to install a baseline installation of OpenLDAP. I thought I’d covered my bases with permissions, but upon startup OpenLDAP created a new file which was owned by root, and had 0600 permissions, which meant no one but root could read or write to that file. I had configured OpenLDAP to run as ‘openldap’, so of course, it couldn’t read the file. Unfortunately, the error message is less than helpful:

'0x50 (Other (e.g., implementation specific) error): updating: <my DN, etc etc>'


So, checking to see the file permissions under /var/lib/ldap, I see a file objectClass.bdb owned by root. Changed it to openldap:openldap, and all is well.

Moral of the story: Always check file permissions. Especially after starting up the server.

Recently, I was tasked with creating a single-sign-on solution for phpBB, where the user would login to our application, and when clicking a link to take them to a support forum, they’d already be logged in. phpBB isn’t known for having a great API with which to integrate, but the code works, and the product works. The authentication works on the premise of providing credentials, logging in, which creates a session in the phpBB database. A cookie value is set, which ties the user to the server-side session. When you have the 2 disparate systems, the domains might be different, but on the same top-level domain. This means if we could get the session ID and set a cookie on the next domain level up, we could be logged in.

The implementation is fairly simple, upon login, we use cURL or something similar and generate a POST request, using the username and password. The remote script grabs the session ID and user ID and returns the values to the originating server. We then, set the cookie values.

Now, the interesting bit. phpBB has multiple layers by which it validates the session. Since our remote server is originating the request, we don’t have the same IP as the user. Second, it uses the User-Agent string of the browser to validate the session. Using cURL, we don’t have a browser. Now, with cURL, you can set various settings (User-Agent string, X-Forwarded-For header, etc) – but if you’d rather not depend on that, you can simply un-check those settings in phpBB.

Of course, I’d recommend using the cURL settings, but to get you started and ensure the connectivity is working.

I’m evaluating a DataGrid for use in a project which is using the Zend Framework, and I came across the ZFDataGrid project.  Fantastic work, and the grid works wonderfully.  It enables you to filter your data and export it in various formats (PDF, Doc, Docx, OpenOffice, etc).  The sample on the site works exactly like this.  The only issue is the manual doesn’t exactly explain how to enable the export functionality.  It doesn’t ‘just work’, but it was reasonably easy to find since the code for the sample site is in Google Code.  But, it isn’t in an intuitive place like the manual or on the site itself.  So, to hopefully save someone else some time, I’ll post the code here – it is from the sample SiteController, and not originally written by me.

$export = $this->getRequest ()->getParam ( 'export' );
 
switch ($export)
{
    case 'odt' :
        $grid = "Bvb_Grid_Deploy_Odt";
        break;
    case 'ods' :
        $grid = "Bvb_Grid_Deploy_Ods";
        break;
    case 'xml' :
        $grid = "Bvb_Grid_Deploy_Xml";
        break;
    case 'csv' :
        $grid = "Bvb_Grid_Deploy_Csv";
        break;
    case 'excel' :
        $grid = "Bvb_Grid_Deploy_Excel";
        break;
    case 'word' :
        $grid = "Bvb_Grid_Deploy_Word";
        break;
    case 'wordx' :
        $grid = "Bvb_Grid_Deploy_Wordx";
        break;
    case 'pdf' :
        $grid = "Bvb_Grid_Deploy_Pdf";
        break;
    case 'print' :
        $grid = "Bvb_Grid_Deploy_Print";
        break;
    default :
        $grid = "Bvb_Grid_Deploy_Table";
        break;
}
 
$grid = new $grid (false, 'DataGrid Example', '/tmp', array('download'));
$grid->setDataFromCsv(dirname(__FILE__).'/Detail_Limited.csv');
$grid->imagesUrl = '/images/';
 
$this->view->grid = $grid->deploy();

The code at the end is mine, which basically tells the DataGrid where to render/save the exported file, which is then immediately sent for download. I also am not using the Zend_Db stuff for the data. As a proof-of-concept, I’m using a simple dataset in CSV, which works amazingly well. The filters, sorting, and pagination still work with CSV.

I’m thinking about writing an adapter for Doctrine, such that one could construct a Doctrine query object, pass it into the DataGrid, and everything would work, as it does with the Zend_Db counterparts.

In my previous post, I used a key style that is open to debate and has been for many years amongst DB folks. The idea of every table having a surrogate key, regardless of the purpose of the table. This says, that for any record in the table I have a single column that acts as the primary key. Given a many-to-many relationship, using a surrogate key on the linking table allows me to describe the relationship in terms of objects and how they’re represented. As shown in the below diagram – each user may have many user_role instances, which are tied to a single role instance. This makes the lives of ORMs much easier since you can create objects for the linking table, which has a simple key to reference.

The ORM then has a User, UserRole, and Role object to use in accessing these tables and adding / removing relationships with ease, since it only needs to worry about the single surrogate ‘id’ key on each table.  In the linking table (as a design concern), one should place a Unique Index on the user_id/role_id column combination.

The other option is using a composite candidate key.  I may have the specific terminology wrong, but the idea is that instead of the single surrogate key to identify a unique record in the linking table, you use a design like the diagram below, which combines the columns that are foreign keys to their respective tables to create the primary key.  The combination of the columns creates a unique identifying key.  The difficulty emerges with ORMs attempting to create objects out this design, and attempting to correctly generate the SQL required to make updates / deletes, etc, using each member of the composite key.

Personally speaking, I’m a fan of the surrogate key approach, but I’ve worked with both.  I won’t discuss the performance impacts of either design, since I don’t have nearly the research base to accurately describe it.  But, using simple integer based keys, the difference should be low.

MySQL provides cascading updates / deletes with the relationships, but I tend not to use them, specifically because I want to control just how far these updates and deletes cascade!  But, given a situation where I have a design similar to this:

I would like to be able to remove a single Foo, without having to first remove all the associated data from the other 3 tables.  Or, I know the ID of the Foo I want to remove, so instead of running multiple queries to find the associated rows, lets just knock it out with a single, multi-table delete!

DELETE
  db.zap as z,
  db.baz as bz,
  db.bar as z,
  db.foo as f
FROM
  db.zap as b,
  db.baz as bz,
  db.bar as z,
  db.foo as f
WHERE
  b.baz_id = bz.id AND
  b.zap_id = z.id AND
  bz.foo_id = f.id AND
  z.foo_id = f.id AND
  f.id = ?

This will then remove the rows associated with the single Foo record I’ve referenced, in one fell swoop.

ICD9 data are the diagnosis and procedure codes used by insurance companies to categorize, well, diagnoses and procedures to be determined / performed by medical professionals. Typically, if you visit the doctor for an ailment and file insurance, your provider will list the reason for the visit, any diagnoses, and any procedures performed. This is then sent to the insurance company for processing. They can then use codes to indicate if the procedure is covered, as well as, (for example) determine if the diagnosis was a pre-existing condition. The data is a simple hierarchical structure which is shown in the following diagram.

ERD for storing ICD9 data

We see that diagnoses can have sub-diagnoses, etc. I used this simple structure, and added a qualifying column of ‘record_type’ to indicate if the code listed was an actual diagnosis, or a section header. Sections of diagnoses are part of the data, and can be used in searching the database. This diagram offers a simple and quick design to handle the data given.

Comments are welcome.

I’ve long neglected this blog, and the power behind it to discuss my feelings toward database design.  Proper database design is the backbone to a solid application.  Failing to correctly normalize tables and enforce business logic with foreign key relationships can cause undue headaches.

What I’ll be doing is going through some simple applications, and start modeling the tables and relationships, and intersperse some commentary where useful.  The discussion depends on interaction, and I’m of the opinion that a ‘good’ database designer can a) defend their design articulately and b) know when to concede a good point.

Database design is becoming organic.  Strict adherence to the normal forms isn’t required any longer.  Let me be clear though, denormalizing a table to make things easier for a developer is not a valid reason to denormalize.

The modeling tool I’ll be using for generating the images is MySQL Workbench.  Workbench is a very powerful tool, from the source of one of the most powerful (and used) databases today.  I cut my teeth on database design with DBDesigner4, whose creator went on to work for MySQL Workbench.

Stay tuned.

Stef and I recently switched phone providers, and thus, could take advantage of the latest deals. Well, Verizon had a buy one get one free offer on Blackberry phones. Step went with a Curve and I went with a Storm. Not having a physical keyboard takes some getting used to, but the Storm screen actually clicks, which makes it really feel like you’re typing. Compared to the typing I’ve done in the iphone, I really prefer the feedback.

I’m still looking for a calendar sync (online, but not Google) that I can setup on my own server. If anyone has an idea, please leave a comment.

This weekend, I watched (again) one of my childhood favorite movies, WarGames. This is a classic, and a must see for anyone involved in technology. Some of the concepts used, are accurate (war-dialing) while others, a little far-fetched (war-dialing into NORAD). Anyways, in one scene the WOPR  is running a simulation of a USA/Russia attack. One of the computer technicians views his screen and sees the continental US surrounded by 10 – 15 ‘red’ submarines. He turns to his colleague and says “Hope you like vodka…”. 

I then thought to myself, what an interesting state of mind to be in. Growing up, and even today, I would laugh at the idea of another country attacking us, and having any sort of success.  But, I realize there was/is a time in our history (and today) where we have vulnerabilities, and we’re not necessarily the huge powerhouse I grew up thinking we were.  That said, it was interesting to see the characters in the movie, playing technicians in one of the safest places in the world, and they assume the Russians would clean up.

My boss used an analogy today, which struck me as quite accurate for the position we’re currently in.  We’re moving at a fairly rapid pace, quick releases, and plenty of improvements / changes.  Along with this, there is new business, new ideas, and new potential sources of revenue that we’re working on.

Assuming that the speed of the vehicle is constant, and the winding is fairly unknown, how do you best prevent hitting the walls?  This part of the analogy is talking about software quality.  While no one is perfect, reducing the amount of churn on bugs and enhancements allows for time better spent on other things, be it additional bugs, new enhancements, or time to re-design the credit card processing module to be more efficient.

In keeping with the analogy, I tried to think of various methods to implement, which prevent hitting the wall.  The first, posed by my direct superior was to ‘increase the accuracy of the driver’.  This is the ideal solution, all things the same.  But, what other factors contribute?

Part 1 – Size of the car

Reducing the size of the car will generally improve the accuracy of the car.  Now, with this analogy, I’m thinking in terms of relative extremes, and I won’t delve into the nit-picks of which 2-door sport coup is the best for suspension.  So, consider 2 vehicles.  1 – a smaller 4-door sedan, low-profile, but consistent acceleration / braking.  2 – an eighteen wheeler – hauls, but reaction time is limited, and said reactions are greatly exaggerated.

A smaller car here represents a focused small unit of developers.  These developers are typically the medium-high to high performers, able to deliver on quality in terms of fixing an issue, and in terms of overall design and architecture.  They work closely together in a nice cohesive environment, with limited distractions.  This allows for quick reactions to change, more rapid context-switches between projects, and good performance.  But, the amount of total ‘fixing’ they can do is limited (they’re in a sedan, for petes sake!).

A big rig here represents a bigger team, with a wider range of skillsets.  You have the higher performers, but also the medium-lows (no lows here, that’s just not pleasant).  Likewise, the medium-lows can deliver, and the solutions work, but they may not be fully thought out, they may not consider some design flaws in their solution which could impact maintainability down the line, but they _can_ deliver a solution.  This team moves along quickly, making a lot of changes, but with unexpected turns ahead, the reactions and turns cause turmoil and disruption.  But, at the end of the day, there is quite a bit of things being fixed, the worry though is, is it being _really_ fixed?

Is the amount of items being fixed the goal?  Or, can you afford to delay a while, but in the end deliver a more solid product?  Over time, the lines will converge, and the products will more than likely appear similar, and operate fine, but did your reputation suffer along the way?  Are customers willing to wait, if they know the problem will be resolved to their complete satisfaction?  Or, do customers accept a partial solution, and potential headaches, but know that eventually the problem will be fully resolved?