Saturday, March 20, 2010

Inserting a list of countries into a database

It's pretty common to need a list of countries for a web application. Here's a method one could use to populate the 'country' table in a MySQL database. The only other tool necessary is a text editor with regular expression capabilities such as Notepad++.

First you create the 'country' table in your database. Below I have the 'name' column with 200 characters length, but if you want to save a few bytes you could trim that down quite a bit.

CREATE TABLE country (country_id int(10) unsigned NOT NULL AUTO_INCREMENT,
  name varchar(200) NOT NULL,
  PRIMARY KEY (country_id)

Next you find a list of countries. One such list is the ISO 3166-1 country list. According to the site this list is provided free for non-commercial use. The data in the text file version is formatted with each country name followed by a semicolon and then the ISO 3166-1-alpha-2 code for each country.

Since some countries have apostrophes in their names you need to do a search for them and replace each occurrence with \' to escape them.

In order to prepare the escaped data for insertion you need to do another replace, but this time using the search string ^(.+);[A-Z][A-Z]$ and the replace string INSERT INTO country (name) VALUES ('\1');. Take a look at the Notepad++ regular expression documentation for more details.

If you want to insert the country codes into another column you could use the search string ^(.+);([A-Z][A-Z])$ and the replace string INSERT INTO country (name, code) VALUES ('\1', '\2');

Saturday, March 6, 2010

Speeding up development with TODOs

I feel that one of my weaknesses has been a desire to perfect each piece of an application before moving on to the next part.  While this may be a good quality in some ways, I've found that it can really dampen my motivation to work on certain projects.  It's tough to get excited about the application I'm crafting when I spend big blocks of time working on the parts that I do not excel at or enjoy.

As I complete each project, the accumulated experience tends to instill more caution and a desire to do more planning for those that follow; I learn to predict all the boring, but necessary, chores that one must complete when writing a solid application.  That means an up-front realization that there's a lot of uninteresting aspects that need to be tackled, and less time to be creative in the way that I enjoy.

I'm not talking about the clever algorithms or the killer features that will set this application apart from all others -- these are the challenging things that draw us to the industry.  And it isn't the low level features that most frameworks take care of out of the box, like validation and logging facilities.  The things I'm referring to require some thought and considerable customization for each particular application.  It's the stuff that is too specific for a framework to include in its stock configuration, but which I either dread because it's a labor-intensive, semi-reinvention of the wheel, or it's something I just don't find interesting.  Some examples are designing views, ensuring that HTML validates and is accessible, various forms of authentication/authorization, and other distributed aspects that don't really contribute to the core functions of the application but which need to be there before deployment.

If one looks around in the supplemental or unofficial libraries that spring up around languages and frameworks, there usually exist a variety of plug-ins/helpers/classes that help ease the burden of writing some of these things from scratch.  But you typically have to spend a fair amount of time familiarizing yourself with the library and/or taking a leap of faith in using it.  And as far as the visual design elements are concerned, you're on your own for the most part.

I really like the way the Yii Framework handles almost all of the low-level stuff, and I'm finding that it even provides out of the box solutions to a lot of the mid-level stuff that I've previously written from scratch for each application.  As an example, it's exceptionally easy to configure controller/action authentication and authorization with very minor configuration.

Even with a great framework, there are still plenty of boring housekeeping tasks to check off in any new application.  As I said before, my method up until recently has been to complete each isolated task before hooking it into the application and moving on.  But now I've been forcing myself to concentrate on the interesting things, returning only afterward to complete and polish the ancillary portions.

I think the cause of my frustrations may be the reason why test-driven development (TDD) isn't nearly as boring as it seems like it should be.  Boilerplate code is boring.  Writing tests, on the other hand, allows a developer to explore their design ideas in depth before committing all the necessary time to writing the frame that will hold the interesting bits together.  It's like building the mold for the concepts and features contained within the application, with the actual code being poured into place somewhat unceremoniously at the end.

These ideas obviously aren't new nor the technique sophisticated, but I've been really surprised by how liberating it can be to leave a wake of "TODO" comments in the first draft of my code.  Instead of spending hours consumed with floating HTML divs in such a way that they display similarly in each browser, I just throw everything I need on the page and move on without the slightest bit of worry about how it will look in the end.  My views (as in MVC) serve the sole purpose of exposing the interesting functions I enjoy spending the majority of time working on.

That's not to say that I don't find value in sketching out screens and doing some minor visual design up front.  It's fun to imagine how things will end up, especially during early planning phases.  But I've learned through experience that if I spend too much time working on the boring code and the things that I consider chores (visual design), that I'll be less productive and may even end up compromising on key features as I struggle to figure out how to wire them all together.

My way of tackling a project differs from others in the industry.  Obviously, for people who are more drawn to the visual aspects of web application design, the interests would probably be flipped.  And then there are the people who excel at both equally, such as Matthew Inman, who in his case study of how he built a successful web application in 66.5 hours explains his method of balancing each aspect of development:
I didn't outline every single page on Mingle2 in one go, I stopped and switched to working on the visual parts of the site often throughout the planning phase.  I interspersed designing the logo and visuals in between to keep me motivated.  It's important not to dive head first into writing code or playing around in photoshop, but don't feel obligated to map out every piece of your site before you start playing around.   Switch up tasks frequently, it'll make you enjoy it more.
A takeaway here is that graphic designers and HCI folks provide lots of value not only to the projects they work on but also to the programmers they work with.