Beginning cloud development with cloudControl – Part 2 – MySQL

Cloud Development with cloudControl on Malt Blue

In part one of the series we got a birds eye view of a great cloud development solution for PHPcloudControl. We looked at the concept of what it is, what you can do with it and ran through a basic deployment with a rather basic application. If you missed the first part, I strongly encourage you to read it before continuing on with part two. When you’re done, come on back and work through it here.

In this, part two of the series, things start to get more serious. In this part, we’re going to start to flesh out the application started in part one, adding in MySQL support – showing just how simple cloudControl makes this for us.

The application will be a simple CRUD application that will allow us to mange a set of users. We’ll be able to do the following operations by the time we’re done:

  • Add
  • Edit
  • Delete
  • List (or view)

Now initially I thought I’d write a pretty plain vanilla application. But as the work progressed, it appeared that that approach wouldn’t work well across multiple environments – which is one of the key benefits of cloudControl. So given that yet in the spirit of keeping things as simple as possible and professional, I decided to use a set of third party libraries to make it easier. These include:

There’s more code in there than I’d really planned to put in it, but it really helps to show you a good, if basic, application that will work across multiple environments. If you want, grab a copy of the code, available at https://github.com/maltblue/cloudcontrol-project and follow along in your local development environment.

Branches and Deployments

The application will have 4 branches: production, staging, testing and development. Production is a branch of the default, or master. For the 3 main branches there will be an accompanying cloudControl deployment.

Let’s create the application

Change to the project directory you created in part one and then run the following command:

# create (and checkout) a development branch from the master branch
git checkout -b development

Following this, you’ll need to do the following:

  1. Grab yourself a copy of the HTML 5 Boilerplate and Zend Framework libraries
  2. Extract the HTML 5 Boilerplate in to your project directory
  3. Create a directory, library, under the root of your project directory and extract the Zend Framework library in to it
  4. Under the root of the project create a new directory, config, and in there, create a file application.ini
  5. Also under the root of the project create a new directory, data/db, and in there, create a file called: dbload.sql
  6. In the css directory (created by the HTML 5 Boilerplate), create a new file main.css
  7. Create a file config.php in the root directory
  8. Create a new directory called includes and in there create two new files: template.footer.php and template.header.php

Adding MySQL Support

As we’re going to be using MySQL, then we’re going to be needing the cloudControl MySQL add-on. The MySQL add-on comes in a variety of flavours, from free, which we’ll be  using as well as in the following space options:

  • 512 MB
  • 20 GB
  • 250 GB
  • 1 TB
As you can see, there’s ample available options to suit your needs. You can read more about them on the add on page.

To add support for MySQL run the following command to add it to our development deployment:

cctrlapp maltbluedev/development addon.add mysql.free

To work with it, we’re going to need the configuration options for it. To get these, run the following command:

cctrlapp maltbluedev/default addon mysql.free

This will list all of the essential information as shown in the screenshot below:

cloudControl MySQL add on configuration optionsNow, in the application.ini file, add in the code below and adjust the details, where necessary, to match the values when you ran the above command.

[production]
; Database
database.adapter = PDO_MYSQL
database.params.host = 127.0.0.1
database.params.username = depe2yjxwjz
database.params.password = 2gVSZRDv5HReX3E
database.params.dbname = depe2yjxwjz

The Database Data

Ok, we’re going to need some real data to get us started. In data/db/dbload.sql, add the following which will load a default table, complete with 10 records for us:

-- database table
DROP TABLE IF EXISTS staff;

-- the staff table
CREATE TABLE staff (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
firstName VARCHAR(100) NOT NULL,
lastName VARCHAR(100),
emailAddress VARCHAR(150) NOT NULL,
occupation VARCHAR(50),
CONSTRAINT UNIQUE INDEX uniq_email(emailAddress)
) ENGINE = InnoDB, COMMENT = "Store list of staff members";

INSERT INTO staff (firstName, lastName, emailAddress, occupation)
VALUES ("Muhammad","Ali","mali@gmail.com","Boxer"),
("Lou","Ambers","lambers@gmail.com","Boxer"),
("Vito","Antuofermo","vantuofermo@gmail.com","Boxer"),
("Jorge","Arce","j.arce@gmail.com","Boxer"),
("Alexis","Arguello","a.arguello@gmail.com","Boxer"),
("Henry","Armstrong","h.armstrong@gmail.com","Boxer"),
("Abe","Attell","a.attell@gmail.com","Boxer"),
("Monte","Attell","m.attell@gmail.com","Boxer"),
("Yuri","Arbachakov","y.arbachakov@gmail.com","Boxer"),
("Satoshi","Aragaki","<a href="mailto:s.aragaki@gmail.com">s.aragaki@gmail.com</a>","Boxer");

Loading the Data in to the cloudControl Database

This is where it gets a bit interesting. In the interests of security, cloudControl provide a secure connection to your database available via an stunnel server. So to access it you’re going to need a copy of the stunnel client and create an encrypted connection from your development environment to the cloudControl servers. Grab and install a copy of stunnel from the stunnel website.

If you’re not familiar with stunnel, what it does is to create a secure connection between a local and a remote port. Through doing this, you can make a connection with an application to a local port which is then transparently redirected to a remote ip and port securely.

In the case of our application, we’re going to create a secure tunnel to the cloudControl mysql servers on the standard port or 3307 via the local port 4711. To do this, we run the command below:

stunnel -c -d 4711 -r mysql.cloudcontrolled.com:3307 -P /tmp/stunnel4.pid

Following this, we can use the MySQL clients as normal, but specifying port 4711 instead of the default 3307. Given that we’ve setup the tunnel, let’s import the data:

mysql -u depe2yjxwjz -p -h 127.0.0.1 -P 4711 depe2yjxwjz < data/db/dbload.sql

Following this, you’ll be prompted for the password as normal, so enter it and the database table will now be created and loaded up with our ten records.

The Templates

Now we need to lay the ground work for the user interface. In includes/template.footer.php, add the following code:

<!-- JavaScript at the bottom for fast page loading -->
<!-- Grab Google CDN's jQuery, with a protocol relative URL; fall back to local if offline -->
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.6.2/jquery.min.js"></script>
<script>window.jQuery || document.write('<script src="js/libs/jquery-1.6.2.min.js"><\/script>')</script>
<!-- scripts concatenated and minified via ant build script-->
<script defer src="js/plugins.js"></script>
<script defer src="js/script.js"></script>
<!-- end scripts-->
<!-- Prompt IE 6 users to install Chrome Frame. Remove this if you want to support IE 6.
chromium.org/developers/how-tos/chrome-frame-getting-started -->
<!--[if lt IE 7 ]>
<script src="//ajax.googleapis.com/ajax/libs/chrome-frame/1.0.3/CFInstall.min.js"></script>
<script>window.attachEvent('onload',function(){CFInstall.check({mode:'overlay'})})</script>
<![endif]-->
</body>
</html>

In includes/template.header.php, add the following code:

<!doctype html>
<!-- paulirish.com/2008/conditional-stylesheets-vs-css-hacks-answer-neither/ -->
<!--[if lt IE 7]> <html lang="en"> <![endif]-->
<!--[if IE 7]> <html lang="en"> <![endif]-->
<!--[if IE 8]> <html lang="en"> <![endif]-->
<!-- Consider adding an manifest.appcache: h5bp.com/d/Offline -->
<!--[if gt IE 8]><!--> <html lang="en"> <!--<![endif]-->
<head>
<meta charset="utf-8">
<!-- Use the .htaccess and remove these lines to avoid edge case issues.
More info: h5bp.com/b/378 -->
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<title>Malt Blue / cloudControl :: Staff Manager</title>
<meta name="description" content="">
<meta name="author" content="">

<!-- Mobile viewport optimized: j.mp/bplateviewport -->
<meta name="viewport" content="width=device-width,initial-scale=1">

<!-- Place favicon.ico and apple-touch-icon.png in the root directory: mathiasbynens.be/notes/touch-icons -->

<!-- CSS: implied media=all -->
<!-- CSS concatenated and minified via ant build script-->
<link rel="stylesheet" href="css/style.css">
<link rel="stylesheet" href="css/main.css">
<!-- end CSS-->

<!-- More ideas for your <head> here: h5bp.com/d/head-Tips -->

<!-- All JavaScript at the bottom, except for Modernizr / Respond.
Modernizr enables HTML5 elements & feature detects; Respond is a polyfill for min/max-width CSS3 Media Queries
For optimal performance, use a custom Modernizr build: www.modernizr.com/download/ -->
<script src="js/libs/modernizr-2.0.6.min.js"></script>
</head>
<body>

The core configuration

Now we’re starting to build up nicely. In config.php, add the following code, which I’ll step through and explain.

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

defined('APPLICATION_PATH')
|| define('APPLICATION_PATH', realpath(dirname(__FILE__) . '/'));

$applicationEnv = substr($_SERVER['HTTP_HOST'], 0, strpos($_SERVER['HTTP_HOST'], '.'));

// Define application environment
defined('APPLICATION_ENV')
|| define('APPLICATION_ENV', (getenv('APPLICATION_ENV') ? getenv('APPLICATION_ENV') : $applicationEnv));

Here, we define some environment settings, similar to how the standard Zend Framework bootstrap does it.

However, whereas Zend takes the APPLICATION_ENV setting from an environment variable set by the web server, I wasn’t able to find out how to replicate that for cloudControl. Given that, I’ve determined it by looking at the hostname up until the first period, i.e., the third-level domain. Now, I’m not saying that this is the best approach, but for the purposes of our application, it will work. But given that, we have to name our branches and deployments to match our configuration.
define('TABLE_STAFF', 'staff');
set_include_path(implode(PATH_SEPARATOR, array(
realpath(APPLICATION_PATH . '/library'),
get_include_path(),
)));
require_once('Zend/Loader/Autoloader.php');
$autoloader = Zend_Loader_Autoloader::getInstance();

Here, we ensure that the Zend libraries are on our include path and for convenience, set a constant for the users table, to avoid remembering it in our CRUD pages.

// database configuration
$config = new Zend_Config_Ini(
APPLICATION_PATH . '/config/application.ini',
APPLICATION_ENV
);

$db = Zend_Db::factory($config-&gt;database);
$db-&gt;getConnection();
$db-&gt;setFetchMode(Zend_<a href="db::FETCH_OBJ">Db::FETCH_OBJ</a>);
$view = new Zend_View();

Here, we load up a connection to our database through a Zend_Config_Ini object and our configuration file and create a view object, required to render forms later.

function getManageForm()
{
$firstName = new Zend_Form_Element_Text('firstName',
array('label' => 'first name: ', 'required' => true)
);
$lastName = new Zend_Form_Element_Text('lastName', array('label' => 'last name: '));
$emailAddress = new Zend_Form_Element_Text('emailAddress',
array('label' => 'email address: ', 'required' => true)
);
$occupation = new Zend_Form_Element_Text('occupation', array('label' => 'occupation: '));
$id = new Zend_Form_Element_Hidden('id', array('required' => true));
$submit = new Zend_Form_Element_Submit('submit',
array('label' => 'Create User', 'ignore' => true)
);

$form = new Zend_Form;
$form->setMethod('post')
->setAttrib('id', 'manage_user')
->addElement($firstName)
->addElement($lastName)
->addElement($emailAddress)
->addElement($occupation)
->addElement($submit)
->addElement($id);

return $form;
}

function getDeleteForm()
{
$id = new Zend_Form_Element_Text('id',
array('required' => true, 'readonly' => true, 'label' => 'user id: ')
);
$submit = new Zend_Form_Element_Submit('submit',
array('label' => 'Delete User', 'ignore' => true)
);

$form = new Zend_Form;
$form->setMethod('post')
->setAttrib('id', 'delete_user')
->addElement($id)
->addElement($submit);

return $form;
}

Here are two convenience methods for working with our forms. The first one will give us a form for adding and editing records, the second for deleting them.

The CRUD Forms

Add

The add user form

Grab a copy of the add form from the github repository and save it as add.php. In it you’ll see that we’ve included config.php and the header and footer templates. After that, we instantiate an add form, set the action and ensure that the id field is not required.

After that, we’ve put in a basic html shell to ensure the user knows the page that we’re on and a simple if/else that validates the form. If the page is not the result of a POST operation, then we just show the form.

If the page is the result of a POST, then we attempt to validate it. If it is valid, then we attempt to insert a new staff member. If successful, we tell the user and provide them a link to the view all staff page. If it wasn’t, we let them know and again provide them a link to the view all staff page.

If the form wasn’t successful, then we render it again and the form class will take care of repopulating it from the POST information and displaying the appropriate errors.

Edit

populated edit form

Grab a copy of the edit form from the github repository and save it as add.php. In it you’ll see that, as with the add form, we’ve included config.php and the header and footer templates. The code also retrieves the user, based on the GET id value and populates the form with the information retrieved from the database. With this information, we pre-populate the form data so it’s ready to update by the user.

After that, we’ve put in a basic html shell to ensure the user knows the page that we’re on and a simple if/else that validates the form. If the page is not the result of a POST operation, then we just show the form.

If the page is the result of a POST, then we attempt to validate it. If it is valid, then we attempt to update the details of the staff member. If successful, we tell the user and provide them a link to the view all staff page. If it wasn’t, we let them know and again provide them a link to the view all staff page.

If the form wasn’t successful, then we render it again and the form class will take care of repopulating it from the POST information and displaying the appropriate errors.

Delete

populated delete form

Grab a copy of the delete form from the github repository and save it as add.php. In it you’ll see that we’ve included config.php and the header and footer templates. After that, we instantiate an add form, set the form action and id value to use the id retrieved from the GET data.

After that, we’ve put in a basic html shell to ensure the user knows the page that we’re on and a simple if/else that validates the form. If the page is not the result of a POST operation, then we just show the form.

If the page is the result of a POST, then we attempt to validate it. If it is valid, then we attempt to delete the staff member. If successful, we tell the user and provide them a link to the view all staff page. If it wasn’t, we let them know and again provide them a link to the view all staff page.

If the form wasn’t successful, then we render it again and the form class will take care of repopulating it from the POST information and displaying the appropriate errors.

List

list users

Grab a copy of the list form from the github repository and save it as add.php. In it you’ll see that we’ve included config.php and the header and footer templates. After that, we retrieved the top 10 user records and in a simple table, have rendered them for the user to see. The table contains the first & last name, email address and their occupation along with links to edit and delete the user. Above the form, we also have a link to add a new staff member.

Now I’ve not focused too much on the structure of the application as this is a tutorial about cloudControl instead of creating an application in PHP. So if you have any questions, add a comment or email me – I’m more than happy to clarify anything here.

Now that the code’s in, workign and ready, we need to commit it to our local repository and deploy the changes out to our branch. So let’s do that with the following commands:

git add .
git commit -m “adding project content”
cctrlapp maltbluedev/development push
cctrlapp maltbluedev/development deploy

In under a minute, the code will be deployed to the development environment ready to test. So, in your browser, open up http://development.maltbluedev.cloudcontrolled.com/list.php.

Winding Up

Let’s stop a moment and consider just how easy that was. Let’s look at what we did and how simple it was to deploy. We just did the following:

  • Create an application
  • Commit it to a repository (which can be stored in multiple locations, including github)
  • Create a deployment that transparently works with the branch of the same name
  • Integrate MySQL in to our deployment and push out a database load script
  • Run through the application

Here’s what we didn’t have to know:

  • How to be a Systems Administration ninja (though I know a few)
  • How to be a MySQL Database Administration guru
  • Configure servers, setup and manage a VPS or dedicated box
  • Worry about how much load we need right now or later
  • Write complicated deployment management scripts

All we needed to do was to focus on the fundamental aspects that are important to us as developers: designing, building and testing our application

And that’s really how it should be for us as developers. It’s important for us to know how the components work, how our work impacts on and is impacted by the various components in N-tier application architectures. But we should be focused on the application itself – the systems administration is for either others, or other times.

Is cloudControl worthwhile?

I’m proud to say a big hell yes. Through the experience I’ve gained so far, developing the application for this tutorial, by using cloudControl, I’ve been able to get back in touch with the aspect of my craft that I love the most – software application design and development. Whilst I’m an avid Linux lover and part-time systems administrator, far and away my greater passion is for software.

The way that the cloudControl service has been constructed, from the simple command line interface, to the components that they offer, to how reliable it is – gives me the confidence to proudly tout their service to you as one that you should include either in your personal arsenal or in that of the company or organisation that you work for.

It really becomes quickly apparent that they were and are web developers and know what it takes to build a reliable hosting platform that just works. On top of that, whenever I’ve needed any assistance, the staff there have been all too willing to offer quality support. A big shout out to Henning.

Coming Up Next

In the next part of this tutorial, we branch away from MySQL and look at just how easy it is to use MongoDB as well – and just how easy it is to deploy it with cloudControl.

If you want more tips, ideas, hints and suggestions, jump on the mailing list. I’m keen to hear how you go with building the application, or a different application and deploying it with cloudControl. Let me know your thoughts in a comment or a tweet.

Links / Further Reading:

Cloud Development databases Intermediate PHP Tutorial