Thursday, November 11, 2010

Yii CRUD: "Please fix the following input errors: {MODEL} cannot be blank"

Today I generated a CRUD scaffold for my 'User' model using Yii Framework's GUI code generation tool, Gii. When I attempted to insert some data to my User table with the Gii-generated CRUD scaffolding I got a rather unhelpful error message:

Please fix the following input errors:
* User cannot be blank

It didn't name a specific property/column so I didn't immediately know what the problem was. When I took a look at the 'User' model I found that the 'user_id' column's label alias was 'User'. That prompted me to look at my 'User' table in the database where I discovered that I forgot to set the 'user_id' primary key column to AUTOINCREMENT. I set that column to AUTOINCREMENT but there were still issues in my 'User' model.

The Yii code generator analyzes the database table metadata during model creation, and because my primary key column was not set to AUTOINCREMENT when I generated it the first time it figured this was a field that accepts user input and must therefore be validated. That's why my 'User' model had my primary key column set to 'required' in its 'rules' function. This validation rule is why I got an unhelpful error message; if it wasn't validating the presence of the auto-incrementing primary key prior to record insertion (which it should not be), it would have gone ahead and attempted to insert the record and I would have received a more useful error from MySQL like the following:

SQLSTATE[HY000]: General error: 1364 Field 'user_id' doesn't have a default value

After I changed the 'user_id' column to AUTOINCREMENT in the database I then used Gii again to regenerate the 'User' model and this time it did not create a 'required' validation rule for the 'user_id' column as expected.

Sunday, November 7, 2010

MySQL ERROR 1061: Duplicate key name 'my_tbl_fk'

I was creating a foreign key in MySQL Workbench 5.2 OSS when I got the following error:

ERROR 1061: Duplicate key name 'tbl1_tbl2_fk'

I checked the "Foreign Keys" tab for the table to which I was adding the FK and there was no key with that name. In fact, there were no foreign keys with that name anywhere.

However, when I checked the indexes on that table I found the problem: an index with the same name as the foreign key I was creating. I dropped that index and was able to create the foreign key without any issues.

What happened was I had previously created a foreign key with that name and then dropped it on the 'Foreign Keys' tab. The index backing this key was not dropped when I did that, so when I attempted to recreate the foreign key with that name it tried to duplicate the index.

Sunday, October 17, 2010

Yii Framework makeover and release schedule updates

I recently noticed that the Yii Framework website, http://www.yiiframework.com, got a new logo and design.  I think the changes look really nice and I hope it will help attract more potential developers to the framework.  I remember being impressed by the Cake PHP website when I was choosing my first PHP framework years ago, and I hope the new Yii design has a similar draw to interested developers.  Graphical design and marketing are not my forte, but as a consumer I know I've been quick to rule out products with poor design as it can be an indicator that the guts of the product were also poorly designed.  In the OSS world this probably isn't a very accurate indicator of the quality of the software, but subliminally I still find myself attracted to shiny things.

I also noticed some recent changes to the release and support schedule on the download page, found here.  I'm pretty sure the 1.1 release was previously only guaranteed support through December 2011 (not certain about that though) so it appears they've given it another year.  That's welcome news to those of us who are developing large projects with Yii.

A new note on the download page gives us some more information about the 2.0 release:
  • It will require PHP 5.3
  • Incompatible with previous releases
  • Earliest alpha may be coming out near the end of 2011
I guess it was inevitable that the PHP version requirement is increasing, but I know from personal experience that there are organizations out there who are unable to upgrade to the newer versions of PHP for a variety of reasons.  Hopefully there will be a considerable overlap between a stable 2.0 release and end of support for 1.1, given the backwards-incompatibility of the newer version.

Saturday, May 8, 2010

Are desktop applications dead (yet)?

I've been reading a lot of blogs recently and am feeling that I've been missing out on some important trends in the software industry. For as long as I have been interested in programming I have focused on learning and applying new skills to solve specific problems in the areas that interested me most, but haven't really spent much time paying attention to what the rest of the industry has been up to.

Some changes have been impossible to miss. Unless you've literally (yes, literally) had your head in the sand you've undoubtedly noticed the rise of the smart phone. Apple's iPhone and Google's Android platforms have captured the attention of developers and, more importantly, consumers. Prior to the release and popularity of these platforms I briefly investigated what effort would be required to write a game for a mobile device and was utterly frustrated by the multitude of incompatible platforms and devices, and the generally closed-to-outsiders attitude of the carriers.  I guess they didn't understand how unsatisfying it was for consumers to be straightjacketed into the overpriced and limited selection of proprietary applications they each made available, while Apple and Google understood the value of a healthy developer ecosystem and have made quick inroads in a very short period of time as a result.

Unrelated, but around the same time, a couple frameworks were also released: Adobe's AIR and Microsoft's Silverlight. I completely ignored them when they were released because I did not see what purpose they served.  As a Java developer I felt comfortable in my ignorance because I couldn't imagine how Adobe or Microsoft would be able to get these new frameworks installed on enough computers to ever make them as widely available -- and, as a result, worth developing for -- as Windows, OSX, *NIX, or Java.  How naive was I?

I disliked Adobe's Flash because in its infancy it was generally used to create unusable websites.  It was very rare to find a Flash-based site that offered the same level of utility as a regular, vanilla HTML site.  It was frequently misused, but occasionally I'd stumble upon a creative individual's website and could appreciate that the technology did offer some capabilities which HTML and Javascript could not match.  But that seemed to be a niche that would not have any impact on me, since I generally preferred back-end development over creating user interfaces.  I viewed the WWW as a place to exchange data rather than as a place to use programs.  The disappointing life of Java Applets seemed to be proof that software should rightfully remain on the desktop.

I grudgingly accepted Flash's unexpected utility and dominance as a capable video player, but with the exception of large bricks-and-mortar corporations' websites it seemed like Flash was effectively limited to the domain of web cartoons and games.

An unexpected blow to Flash came from the least expected of sources: Javascript.  What previously ranked at the top of my list as least liked things about the WWW, Javascript miraculously rose from the dead on the back of AJAX and went on to become a viable programming language for web development due to the creation of robust cross-browser frameworks.  Combined with the browser DOM and CSS, Javascript was able to effectively do much of what Flash was being used for in many websites.

More recently, the evolution of HTML provides for additional built-in browser capabilities that make some people wonder if the end is indeed here for Flash.  I don't want to involve myself in that debate, but I speculate that even if HTML5 supplants Flash it will still be a slow death based on the amount of time it takes for all popular browsers to support new any standards reliably.

So when AIR was released, I was left wondering how Adobe possibly could have justified the expense in creating such a pointless technology.  Ditto for Silverlight; the only way I rationalized its existence was by assuming it was another "me too" from Microsoft not wanting to take the [remote] chance of missing out on the next big thing.

When I recently started thinking about writing a desktop application I had reason to pause.  During my lifetime as a computer user I've mostly associated software with the desktop, but I was shocked when I realized that I really don't use as many desktop applications as I imagined.  I live in FireFox, but beyond the occasional need to boot up OpenOffice I really don't use many full-fledged desktop applications.  There are an array of other tools I use during software development, such as NetBeans, Putty, Cygwin, TortoiseSVN and Notepad++, but when I'm not writing code I get by with very little.

I was further shocked when I contemplated how many Java desktop applications I've ever [to my knowledge] used.  Outside of Eclipse, JDeveloper, a handful of other programming IDEs, and the occasional Swing application I wrote for myself, I don't think I can name another desktop application I've ever used that was written in Java.  Now I know there are some out there, but it's definitely not as many as I obliviously assumed there were before I started looking.

As I looked around I found that most people seem to have written off Java when it comes to developing commercial desktop applications.  Some people still say it's too slow, others say Swing is too ugly, and the rest complain about a variety of other rough edges that Java's age has unfortunately failed to weather away.  I see a lot of C/C++ native applications and some healthy C#/.NET development for Windows.  What shocked me most was the discovery of several successful applications written in Adobe's AIR.  The poster-child of the Micro-ISV world seems to be a small company named Balsamiq, which has a wildly successful AIR application called Mockups.  Another notable application written in AIR is TweetDeck.

When I look at some of these AIR applications I am thoroughly impressed.  While Java may (for now) have a larger installation base, AIR applications are much less frustrating to deal with.  Installing an AIR application is surprisingly... easy.  Adobe has leveraged the ubiquity of Flash to not only push AIR out to the masses, but to enable the seamless and user-friendly installation of applications written in AIR.  I'm starting to feel now that AIR isn't the pointless framework I first thought it was.  It's not a stab in the dark by Adobe after all; instead, it's what, and where, Flash should have been in the first place.

In the past I at times fantasized about writing my own web browser replacement.  But instead of writing an HTML-parsing, W3 standards-compliant, ACID test-passing browser, I'd create something entirely different that felt less page-oriented and stateless, and more like a... desktop application.  I realize now that this is exactly what Adobe and Microsoft are attempting to do.

It seems to me that a large number of casual software users' needs are and will continue to be met by webmail and social networking sites like Facebook, with lightweight frameworks like AIR and Silverlight either acting as browser replacement, or as a stopgap while we wait for our browsers to evolve enough to meet the needs of our dumb terminal desktops.

Saturday, April 10, 2010

Preparing a WAMP stack for Yii development - Part 2 of 2

If you don't have a working WAMP stack yet, you'll need to start with Part 1 of the tutorial. This continuation will walk through the process of installing the Yii Framework on your server and creating your first application.

First you need to download the Yii Framework itself from here: http://www.yiiframework.com/download/. I recommend getting the latest stable version. Once you've got the Yii Framework zip file you'll need to extract its contained files somewhere in close proximity to your Apache server's DocumentRoot directory. I wouldn't recommend leaving the Yii files inside your DocumentRoot, since Apache will never need to serve them up directly, but for the sake of simplicity I will put them there. In a production environment you would want to move them to a location not visible to Apache for security reasons.

Since my DocumentRoot is C:\yiiprojects\testproject I extracted the zip files into C:\yiiprojects\testproject, resulting in everything being placed in a folder at C:\yiiprojects\testproject\yii-1.1.0.r1700. The folder in the zip will be named based on your version, so if you get a different version of Yii it will be named slightly differently. For simplicity I renamed the extracted Yii framework folder from "yii-1.1.0.r1700" to "yii", so it now resides at C:\yiiprojects\testproject\yii. Inside the yii folder there are several folders -- demos, framework, requirements -- as well as the Yii LICENSE and README files among others. As you might have guessed the actual Yii php framework files reside in the framework folder and some demo applications exist in the demos folder.

The C:\yiiprojects\testproject\yii\requirements folder contains a script that will test if your system meets the Yii requirements. Since we've extracted these files inside of our DocumentRoot we can execute the requirement testing script by navigating to: http://localhost/yii/requirements/index.php

You should see something like the following if everything from the previous steps is working correctly:


You'll need to address any failures, but the warnings in the screenshot above are not critical.

Now that we've confirmed the Yii Framework requirements have been met we can create our first application. To do this we open up a command prompt, navigate to C:\yiiprojects\testproject\yii\framework and execute the following commands:

yiic webapp C:\yiiprojects\testproject

It will ask you to confirm whether you wish to create a new application. Type 'Yes' to proceed. It will generate a skeleton application for you in the specified folder.

You can now navigate to http://localhost and you should see a skeleton Yii application.

Next we need to create a database within MySQL for our project. To do this we will return to the command line and log back into MySQL:

mysql -u root -p

After you enter your MySQL root password, you'll need to issue the following command to create a database. I named my database 'myappdb' but you can pick something more appropriate for your application.

CREATE DATABASE myappdb;

Now that we've created our project database we need to configure our Yii application to point to it. To do this we need to edit the main application configuration file, located at C:\yiiprojects\testproject\protected\config\main.php

Here's the relevant section of your application's config/main.php file:

'db'=>array(
  'connectionString' => 'sqlite:protected/data/testdrive.db',
),

By default it is set up for SQLite. We'll need to update this so that it uses our MySQL database. Replace the above 'db' configuration with the following one, replacing the 'dbname' with the database you just created, and 'password' with your root password.

array(

'db'=>array(
    'class'=>'CDbConnection',
    'connectionString'=>'mysql:host=127.0.0.1;dbname=myappdb;port=3306',
    'username'=>'root',
    'password'=>'mysecretpassword',
    'emulatePrepare'=>true,  // needed by some MySQL installations
),

The 'connectionString' value defines your database type, the 'host' points to its location and the 'dbname' is the name of the actual database your application will use.

As you can see, I use '127.0.0.1' instead of 'localhost'. Using 'localhost' is fine in most cases but if that doesn't work for you, try 127.0.0.1 instead. You're not required to specify the port in your 'connectionString' if you use the default of 3306, but I did anyway.

Once you've completed these steps you should be ready to use your MySQL database from within your Yii application. The only thing we're lacking at this point is a table within our database. I'll create a table called 'user' where we can store registered users.

Log back into MySQL from the command line:

mysql -u root -p

After you enter your password, issue the following commands to select the database you created previously and add a new 'user' table to it:

USE myappdb

CREATE TABLE user (user_id int(10) unsigned NOT NULL AUTO_INCREMENT,
  name varchar(200) NOT NULL,
  password varchar(200) NOT NULL,
  PRIMARY KEY (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Now we have a 'user' table in our 'myappdb' database.

We now need to create a Yii Model to represent our user table. Close the MySQL command prompt and open a new command prompt.

Navigate to C:\yiiprojects\testproject\framework and open up the yiic shell:

yiic shell ../../index.php

If you get a PHP error/warning here related to the date() function not having a timezone setting it's because your PHP error settings are not ignoring warnings. We can get around this by opening up our C:\yiiprojects\testproject\index.php file and specifying our timezone at the top. I set mine to 'America/Chicago':

<?php

date_default_timezone_set('America/Chicago');

// change the following paths if necessary
$yii=dirname(__FILE__).'/yii/framework/yii.php';
$config=dirname(__FILE__).'/protected/config/main.php';

// remove the following line when in production mode
defined('YII_DEBUG') or define('YII_DEBUG',true);

require_once($yii);
Yii::createWebApplication($config)->run();

If you try to access the yiic shell again it should work now that we've fixed the warning.

Once we've logged into the yiic shell we can create our User model with the following command:

model User

If successful it should generate three files: models/User.php, fixtures/User.php, and unit/UserTest.php.

The last step is to allow Yii to create some scaffolding for us to generate basic CRUD controllers/views for our new User model:

crud User

This should generate a UserController.php and several views.

To test this we can now navigate to http://localhost/index.php?r=user/create and we will be presented with a form to create a new user.

In most cases we probably don't want to use the CRUD scaffolding, but it can be a good place to start. Take a look at the controller, model and views we just created to get an idea of how the Yii Framework is structured.

This concludes the two part tutorial on setting up a WAMP stack for Yii development. If you haven't yet, make sure you check out the documentation on the Yii site: http://www.yiiframework.com/doc/

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)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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.

Tuesday, February 23, 2010

Data validation musings

As I take on the responsibility of supporting more and more web applications I've come to realize that the most difficult and time consuming bugs to not only eradicate, but clean up after, are those which leak bad data into the backing databases.  As a result I find myself spending generous amounts of time working on data validation aspects in the new applications I create.

In web applications there are usually several layers of validation working in concert:

  1. Client validation.  Read: Javascript.  This form of validation became the norm in web applications over the past decade as Javascript has gained wider acceptance in the browser.  Since web applications are largely about the user experience in many ways, these weak checks can be quite important for a variety of reasons, the least of which relates to the basic integrity of a to-be-persisted piece of data.  From my perspective as data defender, I don't really consider Javascript validation to be, well, validation, at all.  It's 100% user experience in my view.  That's not to say that it isn't a key player in maintaining the correctness of data; after all, since users of web applications tend to interact with other players indirectly via the database, the means by which they see and interact with the data can have a serious impact on how they subsequently manipulate it.  AJAX helps web applications respond to and keep the users informed of events that have changed or which cannot be ascertained at the time of the initial request.
  2. Application code validation.  I've seen a lot of programs limp along with all validation being done at the server-side/application code level.  This is traditionally where most of the business logic lives.  And what are validations if not pure, codified business rules?
  3. Database validation.  This is where the edge cases are caught.  The place where the scenario of two people hitting a button at the exact same time is either caught and dealt with, or blissfully ignored at the cost of data integrity.  Functional tests can easily miss these cases, as they're usually the hardest to replicate.  Stress testing probably unearths more of them, but in a lot of production applications there are usually some cracks that, while small and unlikely to show themselves, exist, and which seem to be the most troublesome to debug.
The enumerations above are in practice not really as distinct as the numbered list would have you believe.  AJAX can blur the line between client-side validation and server-side validation, acting as a mere messenger rather than doing any work itself.  And the line between application code and database constraints is even more blurry, with transactions and vendor-specific features like stored procedures living in the fuzzy ether between the two.

While the validation of data is typically driven by business rules explicitly or implicitly, the method by which validations are written to enforce those rules varies quite a bit.  For example, a rule that users must enter their first and last names prior to registration is rather simple to enforce, as the presence or lack of values is itself enough information to determine whether the requirement has been met.  To contrast this, it becomes more complex when one must consult existing stored values in order to validate some new entry; as an example, a bank must check an account balance before determining whether a debit of $500 is allowed.  The difference between these two validations may seem minimal, especially when they sit side by side as brothers in a requirements document, but when the code hits the pavement the second example is many times more difficult to enforce absolutely.

In the first example we could simply slap a NOT NULL constraint on the relevant columns in the database and it would be enough to ensure the validity of our persistent data store.  It may not be handled in the most friendly manner, but there's no way that record will get inserted until values are provided.  If our application is the single point of access to the database and we enforce this requirement in every place in the application code which inserts or updates records, we could theoretically shift this check from the database to the application code and happily chug along for years in production with not a single problem.

The second example is not so simple.  Not only do we need to examine existing persisted data to determine whether the account has enough funds to cover the debit, we also need to serialize each transaction (overloaded term in this context) such that a situation cannot arise where simultaneous debits pass the balance check before either of them posts to the account, with the end result being a negative balance.  We cannot live in our own little bubble anymore; we must be fully aware of our environment and the other potential players.

To truly fulfill the basic integrity and correctness of data in modern day web applications, one typically must be competent in SQL, the vendor-specific quirks and features (such as PL/SQL or T-SQL) of the database being used, a server-side application programming language like C#, Java, Ruby, PHP or ASP, and most likely Javascript for the browser.  But simply "knowing" (whatever depth of comprehensive understanding that happens to convey) the appropriate languages is not enough.  One must be able to understand all the moving parts so that validations can be written in a way that all points at which these languages and tiers of the system interact with each other are conducted in such a fashion that things can't break regardless of the data being affected by simultaneous executions of the same code by other users and processes.

Saturday, February 13, 2010

Preparing a WAMP stack for Yii development - Part 1 of 2

This is the first part of a 2-part tutorial on setting up a WAMP stack for Yii Framework development. This part covers the installation of each individual component of a WAMP stack. The second part of the tutorial assumes you have your server set up, and picks up with the installation of the Yii Framework, configuration of your MySQL database, and the creation of a project.

Setting up a new development machine for Windows, Apache HTTP Server, MySQL and PHP + Yii Framework, i.e. WAMP-Yii, is a fairly easy process. WAMP being such a common configuration for web application development, some easy installation packages are out there which install and configure all these complementary components in one easy step.

I'm writing this walk-through to help those who don't have experience setting up a WAMP-Yii stack, in order to help them get up and running with the Yii Framework with minimal effort. I detest environment setup myself, and have wasted plenty of time trying to get various environments running the way I want them. It can be quite frustrating to start learning a new programming language or framework when you get stuck at the setup and configuration step.

As an aside: I recently had the experience of installing WampServer on a Windows XP machine and found it to be extremely user-friendly. If you're not comfortable installing each of the components separately, or if you just want to be up and running in a jiffy, you should definitely consider taking a look at one of the pre-configured installation packages. Unlike those I tried out several years ago, WampServer is a real treat in that it offers a nice GUI from which you can configure and manage your installation without having to make changes to your php.ini and httpd.conf files manually. For those who haven't administered PHP or Apache HTTP Server installations manually, these files, respectively, are where you do most of the tweaking to get things running to your specification. The Apache component is highly configurable, so it's common to make tweaks here and there as the need arises. If you wish to use WampServer instead of setting up each component separately, you can skip to Part 2 of the tutorial once you've got your server up and running.

I'm going to install each of these components separately, however, in the event that WampServer or another package doesn't fit your needs or isn't available for your particular environment or with the individual versions you desire.  Even if you do use a packaged one-click solution, it can be helpful to see the whole process so that you understand how each part interacts with the others, without having the ugly bits covered up by a GUI.  A warning, though: at the end of the process you'll probably wish you took the easy route ;)

Step 1: The first step is technically optional, but is quite important. As you're going to be downloading several installation packages from different websites, it's important to make sure you're getting what you expect. Most large open-source software packages are available from a series of mirrors rather than a single source, so verifying that the mirror served up the correct, unaltered file you were expecting is key from a security standpoint. In order to verify your downloads you need to get a checksum verification utility. Since you're using Windows I recommend Microsoft's File Checksum Integrity Verifier utility. Follow the instructions on the download page to install the "fciv.exe" checksum utility. It provides both md5 and sha1 checksum verification, which is what most vendors provide checksums for.

Step 2: Install MySQL Server. Select the desired MySQL installer version. I would recommend getting one of the MSI installers, as they're simple to install on a Windows machine. If you're on a 64 bit operating system you should opt for the 64 bit MSI installer. If you're on a 32 bit operating system, make sure you go for the 32 bit MSI installer, as the 64 bit version will not work. Once you've downloaded the installer, run the checksum utility you downloaded in Step 1 to verify the integrity of the file. If you downloaded the checksum utility to the same folder as the MySQL MSI file, it'll be a simple process. Open up the Windows command prompt, navigate to the download folder, and type fciv.exe <mysql-msi-file.msi> where <mysql-msi-file.msi> is the name of the MySQL msi file you downloaded (leave off the '<' and '>'). Compare the output of this command with the MD5 value provided on the download page. Currently MySQL provides MD5 checksums, but if they change to SHA1 you can type fciv.exe -sha1 <mysql-msi-file.msi> to get the SHA1 value instead of the default MD5. If the string of numbers and digits output matches the one on the download page, you can feel fairly comfortable that you have an unaltered file.  As an extra precaution you can scan the downloaded file with your Antivirus program.

Now run the MSI file to install MySQL on your machine.  You should be safe choosing the "Typical" setup type.


Continue with the wizard and hit the "Install" button.  Once complete it will pop up a window that says it finished installing.  Now make sure the "Configure the MySQL Server Now" box is ticked.
Next, make sure you select the "Detailed Configuration" radio button, so that we can make some tweaks to our MySQL Server installation.
Next, choose "Developer Machine".
The following option is up to you, but I recommend "Transactional Database only."  "Multifunction Database" is okay as well.  We're going to want to use the InnoDB storage engine for most web applications, as it supports transactions, so we need to make sure it is available.  You'll probably avoid MyISAM unless you are writing a web application that is used only for querying your database rather than issuing inserts or updates, and where speed is of the utmost importance.
 

You can leave the InnoDB Tablespace Setting as "Installation Path":



The next setting isn't terribly important, but I like to manually set a low number of concurrent connections since I don't typically have more than one or two open at a time during development:



The networking settings can be left as the defaults:



The character set is a decision you'll have to make based on your requirements.  I typically select UTF8:


We want our MySQL server to be running all the time on our machine as a Windows Service, rather than having to manually start it up every time, so we'll leave these boxes ticked.  The MySQL server doesn't use up to many resources, do don't fret about it hogging your RAM. You'll also want to tick the "Include Bin Directory in Windows PATH" option, as it will allow you to run the mysql utilities from the command line without having to prefix the installation directory path.



It now prompts you to enter a root password for your MySQL Server instance.  My screenshot may look a little different than what you see, because I've already set up MySQL with a password.  You'll probably see the same thing except there will be no "Current root password" field.  Just enter a root password in the "New root password" and "Confirm" fields.  Make sure you remember the password, as you'll need to know it later on; it can be difficult to reset this, so don't forget it!  Don't tick the root access on remote machines or the anonymous account options unless you have a reason to do so.


All done! You can test out your mysql server installation by opening up a command prompt. Note that you'll need to open a new one, as the PATH setting we enabled during setup will not be available on any existing command prompts you have open.

mysql -u root -p

Then you'll need to enter the root password you previously selected. If all goes well, you'll see this:


That's it for MySQL.  Now we'll move on to setting up Apache HTTP Server.

Step 3:  Download the Apache HTTP Server MSI.  According to Apache, version 1.3x was not meant to be installed as a Windows service, so they suggest version 2.x+.  I'm going to go with version 2.2, non-ssl version.  Again, run the checksum verifier to ensure the MD5 or SHA1 signatures match those provided by Apache.

After you accept the licensing agreement, you'll get to the Sever Information section.  Use localhost for the network domain and for the server name:


Next, choose the "Typical" installation.  The Apache HTTP Server will now be installed to your computer.  Once completed you should see the Apache Service Monitor icon on your toolbar.  If you right click and open it, you'll see the Apache server status:


If you need to make any alterations to the Apache HTTP Server settings in its httpd.conf file, or to the PHP settings in the php.ini file, you'll need to open up the Apache Service Monitor and restart Apache in order for them to take effect.

To test out your Apache installation, open a up browser and enter: http://localhost

If your browser gives you an error you've got a problem, but if you get the text "It works!" then everything was successful.
Now it's time to set up PHP.

Step 4:  We'll need to download a PHP installer.  If you're curious about the various different installers, here is a comparison of the thread safe and non-thread safe versions.  The decision between thread safe and non-thread safe comes down to whether we want to run PHP as a CGI or a module.

We're going to select the PHP 5.3.1 VC6 x86 Thread Safe installer.  Be careful and heed the warning on the download page not to install VC9 binaries in conjunction with the Apache HTTP Server binaries.

After accepting the license agreement you'll be prompted to select the web server.  Since we installed Apache HTTP 2.2, we'll opt to run PHP as a module:


After hitting next and selecting the install directory, we're prompted to select which extensions we want enabled.  The default selections should include, among other things, the MySQL and MySQLi extensions, as well as the PDO driver for MySQL.  IMPORTANT: If you downloaded the no-SSL version of Apache, you'll need to uncheck the OpenSSL extension.  Untick any extensions you don't think you'll need; you can always add them back in the future.  Be sure not to include any other databases than MySQL here or you may run into problems when you attempt to run php.



After clicking "Next" it will install PHP with the extensions we selected.  If you get an error message that says it had trouble accessing the httpd.conf file, you'll have to edit the Apache HTTP Server's httpd.conf file so that it can interpret PHP files.


If you get this error, don't worry: PHP probably installed successfully, it's just that your Apache HTTP Server doesn't realize it.

At this point you should open a new Command Prompt, navigate to the directory that you just installed PHP to, and try to run it by typing: php

If you get some errors about missing dll files, then you'll need to run the PHP MSI file again -- this time it will have a "Change" installation option -- and pay very careful attention to which extensions you're installing.  Uncheck MSSQL, PostgreSQL, and any other databases, as they create dependencies that your system may not be able to support.  Any extensions that have dependencies could cause problems.

If no errors occurred when you ran the "php" command you're probably in good shape.  Now you need to configure Apache so that it runs PHP as a module.

You need to navigate to your Apache 2.2 installation's conf folder.  It's probably located at C:\Program Files\Apache Software Foundation\Apache2.2\conf.  Once you locate it, open up the httpd.conf file within.  If the file is read-only, then you'll need to run your text editor as an administrator and then open the file.  To do this, locate your text editor's executable, e.g. Notepad (Start->Accessories->Notepad), right click on it and select "Run as administrator" and then go to File->Open, and then locate and open the httpd.conf file in the aforementioned location.

Search for this line in the httpd.conf file:

#LoadModule rewrite_module modules/mod_rewrite.so



And remove the '#' from the beginning of it in order to uncomment it. This will enable Mod Rewrite, the magical module that makes our URLs pretty.

At the end of all the LoadModule lines, add the following two lines (edit the path to your PHP install directory if applicable):

LoadModule php5_module "C:/Program Files/PHP/php5apache2_2.dll"

PHPIniDir "C:/Program Files/PHP"


Search for this line: <IfModule mime_module>

You need to add the following line between the opening <IfModule mime_module> and closing </IfModule> tags:

AddType application/x-httpd-php .php


Now search the file for the DocumentRoot tag... it probably looks like this:

DocumentRoot "C:/Program Files/Apache Software Foundation/Apache2.2/htdocs"


You'll want to change that directory path to the location where your web application will be developed, so that by navigating to http://localhost it will point to the root of your project folder.

Since my application files will reside at C:\yiiprojects\testproject I'll update this tag as follows:

DocumentRoot "C:/yiiprojects/testproject"


When you restart the Apache HTTP Server it will now start serving up files from that location.

A little farther down you'll see a tag with the original DocumentRoot; it has a comment preceding it stating, "This should be changed to whatever you set DocumentRoot to."  Make sure you copy the DocumentRoot value here as well.

For me it is:
<Directory C:/yiiprojects/testproject>

Now save and close the httpd.conf file.  Open up the Apache Service Monitor application that should still be running from before, or open it if not, and hit the "Restart" button (or "Start" if the server is not currently running).

If you get errors when you attempt to restart the Apache HTTP Server, go to the command line, navigate to your Apache installation's bin folder and try to run "httpd" from the command prompt.  It should display the errors there, which you can use to debug the problem.

If it restarts without any errors, navigate to the folder you specified as your DocumentRoot folder in the httpd.conf file, create a file called index.php with the following contents:
<?php
 phpinfo();
?>

Now open up a browser, navigate to http://localhost and if it prints out all the details of your PHP installation you've got PHP and Apache HTTP Server running together.

To get Yii Framework integrated with your new WAMP stack, proceed to Preparing a WAMP stack for Yii development - Part 2.

Saturday, January 2, 2010

Yii tip: CDbConnection failed to open the DB connection: SQLSTATE[HY000] [2005] Unknown MySQL server host 'localhost' (11004)' in ... CDbConnection.php:262

UPDATE:  I've been told by several people that they see this same exception but that their particular fixes differed from mine.  I think it's fair to say that this is a generic exception that gets thrown when there is an inability to connect to the MySQL server (for a variety of possible reasons).  I would suggest that if you see this exception you first check your database configuration to make sure the connection string is correct and, if it is, look at other potential causes for an inability to connect -- one of which is contained in the post below. If all fails, you may want to start your debugger up and/or put some debugging code in the framework code itself in order to see if you can get any clues as to the source of your exact problem.

I'm using Yii Framework 1.1 beta and I recently upgraded my MySQL version to 5.1. My development machine runs Windows Vista and I installed MySQL using the 5.1 msi file. When I attempted to configure my Yii application's connection to my database, located in MY_PROJECT_FOLDER/protected/config/main.php, and create a model using yiic shell, I got this error: CDbConnection failed to open the DB connection: SQLSTATE[HY000] [2005] Unknown MySQL server host 'localhost' (11004)' in ... CDbConnection.php:262

Here's my database configuration:
'db'=>array(
  'class' => 'CDbConnection',
  'connectionString'=>'mysql:dbname=mydb;host=localhost',
  'username' => 'mydbusr',
  'password' => 'mydbuserpassword',
)

Here's the full stack trace when I attempted to create the model using this database:
C:\MY_PROJECT_FOLDER\protected>yiic shell ../index.php
Yii Interactive Tool v1.0 (based on Yii v1.1rc)
Please type 'help' for help. Type 'exit' to quit.
>> model MyNewModel
exception 'CDbException' with message 'CDbConnection failed to open the DB connection: SQLSTATE[HY000] [2005] Unknown MySQL server host 'localhost' (11004)' in
C:\MY_PROJECT_FOLDER\yii-1.1rc.r1585\framework\db\CDbConnection.php:262
Stack trace:
#0 C:\MY_PROJECT_FOLDER\yii-1.1rc.r1585\framework\db\CDbConnection.php(237): CDbConnection->open()
#1 C:\MY_PROJECT_FOLDER\yii-1.1rc.r1585\framework\db\CDbConnection.php(216): CDbConnection->setActive(true)
#2 C:\MY_PROJECT_FOLDER\yii-1.1rc.r1585\framework\base\CModule.php(357): CDbConnection->init()
#3 C:\MY_PROJECT_FOLDER\yii-1.1rc.r1585\framework\base\CApplication.php(391): CModule->getComponent('db')
#4 C:\MY_PROJECT_FOLDER\yii-1.1rc.r1585\framework\cli\commands\shell\ModelCommand.php(260): CApplication->getDb()
#5 C:\MY_PROJECT_FOLDER\yii-1.1rc.r1585\framework\cli\commands\ShellCommand.php(144): ModelCommand->run(Array)
#6 C:\MY_PROJECT_FOLDER\yii-1.1rc.r1585\framework\cli\commands\ShellCommand.php(99): ShellCommand->runShell()
#7 C:\MY_PROJECT_FOLDER\yii-1.1rc.r1585\framework\console\CConsoleCommandRunner.php(62): ShellCommand->run(Array)
#8 C:\MY_PROJECT_FOLDER\yii-1.1rc.r1585\framework\console\CConsoleApplication.php(88): CConsoleCommandRunner->run(Array)
#9 C:\MY_PROJECT_FOLDER\yii-1.1rc.r1585\framework\base\CApplication.php(135): CConsoleApplication->processRequest()
#10 C:\MY_PROJECT_FOLDER\yii-1.1rc.r1585\framework\yiic.php(31): CApplication->run()
#11 C:\MY_PROJECT_FOLDER\protected\yiic.php(7): require_once('C:\MY_PROJECT_FOLDER\...')
#12 {main}

The problem baffled me for a while since I was able to connect to mysql via the command line without any issues. The resolution was a MySQL configuration. When I set up MySQL initially I locked it down as much as I could in order to prevent unauthorized people from being able to interact with my database instance. One of the steps I took, which caused problems for Yii, was disabling MySQL TCP/IP Networking -- this limited use of the database to local named pipe connections only.

In order to rectify this I ran the MySQL Instance Configuration utility (located at: MYSQL_DIR/bin/MySQLInstanceConfig.exe) and ticked the "Enable TCP/IP Networking" connection on the networking options screen (note that you need to select the "Detailed Configuration" path). If it freezes up when you attempt to execute the reconfiguration it may be a permissions issue. If you're running Vista you should right click on the MySQLInstanceConfig.exe file and select "Run As Administrator" and try the reconfiguration again.

I physically disconnected my development machine from the Internet so I'm not nearly as concerned about security. If your computer is connected to the net you may want to scrutinize changes to your configuration more carefully.