Linux, SQL and a scoop of yogurt in Week #8

Find of the Week

Project Parfait

I’m a big yogurt fan, so there’s a decent chance I came across this while Googling recipes. Either way, it’s an incredible tool.

Supported only in Chrome at time of writing, this little app will allow you to upload a PSD, then slice and dice it for you, returning CSS, colors, fonts, etc. It’ll even pick apart individual layers or objects and export CSS rules to create them on the web.

I’m not sure I’ll use it for every project, but it definitely takes me once step closer to a less platform dependent development machine.

https://projectparfait.adobe.com

Linux

Time Zones

For a while, I had a server that for one reason or another, would restart every couple of months. No problem, it always happened at night, downtime was minimal and all services came back up.

A couple days after each restart, I’d get an email from one of my clients to the effect of, “It’s weird, all of a sudden the time on our orders is one hour off.”

“That is weird”, I’d reply, and immediately I knew that the server time zone was set improperly. So, I’d do the usual move my timezone file over to the /etc/localtime and voila, it was fixed.

I manage a few servers, so I clearly couldn’t remember that this same server was having the time zone reset every couple months. It finally hit me that it was something in a server config file.

A simple update to /etc/sysconfig/clock and I’m good to go!

Oh, that update was to set the ZONE= parameter in the clock config file to the proper time zone.

Secure Images not Working in some Email Clients

I had a really stupid and probably not-so-common issue with SSL certificates this week. I had installed a very basic Positive SSL certificate on one of my domains.

Everything checked out, except, when I viewed an email in Mozilla Thunderbird, it would not load the secure images from the server.  Any image at https:// simply wouldn’t load.

It ends up that Thunderbird didn’t like the order that I placed the SSL certificate chain files in!

This post brought the issue to light for me.

SQL

Getting the total row count when a limit is used

This is a really handy tip I learned this week. Typically when I want to return the number of matched rows when a LIMIT is used, I’ll do one of two things:

• Get the entire result set, then use my scripting language to get the count and return the limited results
• Run two queries (one to get the count, and another to get the limited result set).

Neither is really desirable. The first returns a complete resultset which could include thousands of rows. The second requires you to duplicate your WHERE condition, which could be complex, on the query to get the count.

It turns out that MySQL has an option called SQL_CALC_FOUND_ROWS and it’s definitely worth reading two paragraphs of info to find out what it does!

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

Inserting and Authorizing in a single query

A couple days ago I was putting my usual validation in place on a site, verifying that a user had ownership of an object before modifying it. Traditionally, I would run a query to see if the user had an editor role on an object, fail if they didn’t and proceed to run the insert or update query afterwards if the authorization passed.

It hit me that this could be done in a single query, saving a round trip to the database:

INSERT INTO list_account (l_id, a_id)
SELECT      1 as sl_id, 9 as sa_id
FROM        list
WHERE EXISTS (SELECT 1 FROM list WHERE l_id = 1 AND user_id = 6)
 AND  EXISTS (SELECT 1 FROM account_user 
              WHERE account_id = 9 AND owner_user_id = 5)