bgror

Double Shot #633 »

Created at: 25.01.2010 14:16, source: A Fresh Cup, tagged: Double Shot couchdb git jquery mysql PostgreSQL rails ruby ssl

It was a busy weekend - apparently for other people as well as for me.

• couch.io - Managed hosting and support for CouchDB.
• UTC vs Ruby, ActiveRecord, Sinatra, Heroku and Postgres - Some notes on time zones in ruby web apps.
• SSL Certificate Check - From DigiCert, but works for any SSL site. A fast way to tell whether you have things configured and installed correctly.
• Rapid Application Deployment with Git - Nice new workflow from from Webbynode. I need to dust off my Webbynode account and play with this.
• pgtune - Automatic configuration tuning utility for PostgreSQL.
• Active Record Query Interface 3.0 - Shiny new finders for Rails 3.
• jQuery QueryString Plugin - Came in handy for hooking up Rails controller methods and stuff inside the jQuery.
• Getting Up to Speed with Rails 3 - Detailed look at moving over an existing Rails application.
• jQuery.sheet - Excel-ish spreadsheet implemented in jQuery.
• Git Branch Auto-Tracking - Useful tip from Mike Champion.
• MySQL Tip: How To Check, Repair & Optimize All Tables in All Databases - I never knew about mysqlcheck, but now that I do I'll likely be using it again.
• murder - Deployment via BitTorrent, courtesy of Twitter. Cute idea, but the number of sites that need this is vanishingly small.

Double Shot #618 »

Created at: 04.01.2010 14:31, source: A Fresh Cup, tagged: Double Shot css javascript jquery mysql passenger rssowl sphinx textmate

Well, at least it's January.

• Primer - Paste in HTML, get back a CSS stylesheet with all the classes and IDs listed.
• jLinq - LINQ style querying in Javascript. This is getting out of hand.
• Save MySQL! - You know, if the MySQL founders wanted to exercise so much control over its future, maybe they shouldn't have sold the company.
• RSSOwl 2.0.2 - A new release of a desktop RSS reader. That's a rare occurence these days.
• hanoi - Some glue for automated jQuery testing.
• netrecorder - Record actual network traffic for later use with fakeweb.
• A Month in the Life of Thinking Sphinx - Lots of nice changes to this gem lately.
• phd - Push deployments for passenger.
• ReMate - TextMate plugin to disable automatic project tree refresh, making TM more useful against remote shares.

Double Shot #613 »

Created at: 28.12.2009 15:12, source: A Fresh Cup, tagged: Double Shot blueprints css formtastic holidays javascript mysql PostgreSQL rspec ruby

Last week of the year. This one can't end soon enough for me.

• Blueprints 0.4.0 - Another replacement for factories and fixtures with some nice syntactic sugar.
• Ruby 1.8.7-p248 released - Can't really tell how significant this is because reading Ruby's own changelogs is a mystery to me.
• holidays - Gem to extend the Ruby date class to make it holiday aware. Works well, once you figure out you have to require the individual holiday support for whatever region you're interested in.
• CoffeeScript - Rubyish language that compiles down to JavaScript.
• RMSforms - CSS styling framework especially for forms.
• formtastic-sass - Sass mixins to style formtastic forms. If only I liked sass.
• MySQL and Postgres command equivalents (mysql vs psql) - Handy if you switch back and forth.
• email-spec - Matchers and steps for testing email with RSpec and Cucumber.
• rspec-integration - A mind meld between regular Rails integration tests and RSpec. So far I like this much better than Cucumber.

Using BETWEEN for SQL comparisons »

Created at: 14.11.2009 22:55, source: Robby on Rails, tagged: programming PostgreSQL code sql development PostgreSQL mysql refactoring

Recently, Carlos, suggested that I should start sharing some basic SQL tips that help with performance and/or general usage. I recently came across some code that I didn’t like to read and/or write. For example, let’s take the following…

SELECT * FROM brochures WHERE published_at <= now() AND archived_at >= now()

Essentially, this is pulling back some data WHERE the the brochures are considered published. (We have a project that allows people to manage their brochure launch dates ahead of time.) In fact, in this project, we have no less than 6-8 dates in the database that we’re comparing data on and it’s easy to get lost in the logic when trying to understand it.

Now, there isn’t anything inheriently wrong with how this condition is constuctued. As a matter of personal taste, I find it annoying to mentally parse. Also, I find having to write now() more than once in a WHERE clause to feel like I’m repeating myself.

Read it outloud…

“WHERE the brochures published at date is less than and/or equal to right now AND the archived date is greater than and/or equal to now.”

Who talks like that?

Luckily, there is a better and in my opinion, a more readable way to express this is with the BETWEEN construct in SQL. (postgresql docs, mysql docs)

SELECT * FROM brochures WHERE now() BETWEEN published_at AND archived_at

Let’s read this outloud…

“WHERE the current date is between the published at and archived at dates.”

This sounds more natural to me.

Additionally, you can also do the inverse with NOT.

SELECT ... WHERE now() NOT BETWEEN brochures.published_at AND brochures.archive_at

Remember kids, “code is for humans first and computers second.”—Martin Fowler