Category: MySQL

Blog Tutorial with Fat-Free Framework

View the blog tutorial updated for version 3 here

I recently read an excellent tutorial on ‘Rapid Application Prototyping in PHP Using a Micro Framework‘, this used the Slim micro framework but one thing that bothered me was it required 5 packages before development could start.
These were: Slim (the micro framework), Slim Extras (additional resources for Slim), Twig (template engine), Idiorm (object-relational mapper) and Paris (Active Record implementation).
It struck me that if you need an extra 4 packages alongside Slim for a basic blog then maybe it is a little too skinny and I set out to find a micro framework that could do the same thing without these dependencies.

I found the Fat-Free Framework. It is condensed into a single 55KB file and has a host of features, you can find out about these on their web site so I won’t repeat it here. Instead I’ve reproduced the Slim tutorial to create a simple blog site, but using Fat-Free Framework instead.
You will need PHP 5.3 on your server, I used Ubuntu 11.04 for the tutorial as that version of PHP is easily installed. If you’re on RHEL or Centos then I’d suggest taking a look at the IUS Community Project for getting a recent PHP version.

Step 1: Setup

Download Fat-Free Framework (F3) from the website.
F3 works just as happily in the site root as from a subdirectory – I’ll assume you’re using a subdirectory here as it means you won’t have to set up a seperate site to do this tutorial.
Create a folder called blog and unzip the contents of the F3 download into that. It should look like this:
Move up one level in the directory heirarchy and set the permissions:

sudo chgrp -R www-data blog
sudo chmod -R 775 blog

The folder contents should look like this:
Folder Contents

Notice how much simpler the starting site is compared with Slim + extra packages.

If using Apache, mod_rewrite will need to be running. Edit .htaccess and adjust RewriteBase to add the blog subfolder that you are using, so it looks like RewriteBase /blog.

You can browse this site right away and get the F3 start page.
start page
(Once the site has been visited, F3 will create additional folders cache and temp – you don’t need to worry about these).

Step 2: Bootstrapping

As everything we need is already part of F3 you don’t need to do anything here!

You can however tidy up the site to remove the current home page and add a database connection.
Edit index.php
comment out the CACHE option and increase the DEBUG level to help while developing, then remove the only route command. It should look like this:

To setup a database connection add the following between the set and run commands:

F3::set('DB',
	new DB(
		'mysql:host=localhost;port=3306;dbname=YourDatabaseName',
		'YourUsername',
		'YourPassword'
	)
);

All the User Interface files will go in the ui directory, you can delete welcome.htm and style.css from here as they were just used by the default home page.

Step 3: Routing

Similar to Slim you need to tell F3 the route request method (GET, POST, PUT etc), the URI to respond to and how to respond.
Home page route:

F3::route('GET /',
	function () {
	//do something
	}
);

The anonymous function will contain the logic to populate the page.
To view a blog article:

F3::route('GET /view/@id',
	function () {
		$id = F3::get('PARAMS["id"]');
	}
);

This tells F3 to expect a URI parameter and assigns it to a PHP variable in the anonymous function.

Now the administration routes:

// Admin Home
F3::route('GET /admin',
	function () {	
	}
);

//Admin Add
F3::route('GET /admin/add',
	function() {
	}
);

//Admin Edit 
F3::route('GET /admin/edit/@id',
	function() {
		$id = F3::get('PARAMS["id"]');
	}
);

//Admin Add & Edit, Deal with Form Posts
F3::route('POST /admin/edit/@id','edit');
F3::route('POST /admin/add','edit');
	function edit() {

	}

//Admin Delete
F3::route('GET /admin/delete/@id',
	function() {
		$id = F3::get('PARAMS["id"]');
	}
);

Notice that we’re using the same function to process Add and Edit form posts so it isn’t anonymous but the function name is passed as a parameter to the route command.

Step 4: Models

The ORMs in Fat-Free Framework do all the hard work for you – no directories, files or code required here.
Are you beginning to see how much simpler this is compared with Slim?

Here’s the SQL that will set you up with the 2 tables necessary for this tutorial:

CREATE DATABASE `blog` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
 
USE `blog`;
 
CREATE TABLE IF NOT EXISTS `article` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `timestamp` datetime NOT NULL,
  `title` varchar(128) NOT NULL,
  `summary` varchar(128) NOT NULL,
  `content` text NOT NULL,
  `author` varchar(128) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
 
INSERT INTO `article` (`id`, `timestamp`, `title`, `summary`, `content`, `author`) VALUES
(1, '2011-07-28 02:03:14', 'Hello World!', 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut ', 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.', 'Mr White'),
(2, '2011-07-28 02:03:14', 'More Hello World!', 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut ', 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.', 'Mr Green');

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `user` (`id`, `name`, `password`) VALUES
  ('1', 'admin', 'password');

Step 5: Application Front End

Like this Slim tutorial we’re going to keep this simple.
Instantiate an Axon object that interacts with the users table, call the afind method to return a simple array of results, finally the set command is used which will pass the variable between MVC components. F3 calls this a framework variable.

	$article=new Axon('article');
	$articles=$article->afind();
	F3::set('articles',$articles);

You could condense the final two lines together like F3::set('articles',$article->afind()); but I’m keeping this verbose to aid readability and help you figure out how it is all working.

To use templating you need a base layout file in the ui folder called layout.html



   
      {{@html_title}}

The F3 template engine uses {{@name}} to write out the value of a framework variable.
The F3:include directive will embed the contents of a file at the position where the directive is stated – in the example above we’re using a framework variable as the URL so that the content is dynamic.

Now lets create the first of those content files with the view for the homepage, called blog_home.html

Blog Titles


	
{{trim(@item['title'])}} by {{@item['author']}}

	
{{@item['summary']}}

The F3:repeat directive will loop through an array, setting item to the current array element. Within the loop item contains the array of data retrived from the database table and this is accessed using the column name as the array key.

Now that the view is in place we can complete the code in index.php to display it. Set the framework variable to tell the template which view to include, then tell F3 to serve the template.

	F3::set('content','blog_home.html');
	echo Template::serve('layout.html');

Serving the template also converts it to PHP code the first time it’s used and this optimised version is used thereafter which is great for performance.
The full code for this is:

F3::route('GET /',
	function () {
		F3::set('html_title','Home Page');
		$article=new Axon('article');
		F3::set('list',$article->afind());
		F3::set('content','blog_home.html');
		echo Template::serve('layout.html');	
	}
);

Now for the detail view, in index.php we need to get an Axon object, search for the id then send the data to the view/template. In this case we could have assigned each value individually e.g. F3::set('title',$article->title); but it is quicker to put all the values in the POST framework variable with the copyTo command.

F3::route('GET /view/@id',
	function () {
		$id = F3::get('PARAMS["id"]');
		//create Axon object and search for id
		$article=new Axon('article');
		$article->load("id='$id'");
		//set framework variables
		F3::set('html_title',$article->title);
		$article->copyTo('POST');
		//serve up the view
		F3::set('content','blog_detail.html');
		echo Template::serve('layout.html');
	}
);

And the view file itself called blog_detail.html accesses the individual data items from the POST framework variable:

{{@POST.title}}


Published: {{@POST.timestamp}} by {{@POST.author}}

{{@POST.content}}

Back to Homepage

Step 6: Application Back End

The admin home page just needs to list the blog articles and provide links, the code is similar to that for the homepage.

F3::route('GET /admin',
	function () {
		F3::set('html_title','My Blog Administration');
		$article=new Axon('article');
		$list=$article->afind();
		F3::set('list',$list);
		F3::set('content','admin_home.html');
		echo Template::serve('layout.html');	
	}
);

The view called admin_home.html and contains a table of the results. It looks like this:

My Blog Administration


Add Article


    

  
    

      Title
      Date
      Author
      Actions
    

  
  
  

      {{@item['title']}}
      {{@item['timestamp']}}
      {{@item['author']}}
      Edit
      Delete

The output of this look like:
Admin Home Page
Now a form to add and edit articles, called admin_edit.html

Edit



  
    
{{ @message }}

I’ve kept this basic, apologies for the lack of styling but that’s not what this tutorial is about.
Note that there’s an area to display validation messages.

Now for the logic with the routes, add & edit both use the same view remember.

F3::route('GET /admin/add',
	function() {
		F3::set('html_title','My Blog Create');
		F3::set('content','admin_edit.html');
		echo Template::serve('layout.html');
	}
);

F3::route('GET /admin/edit/@id',
	function() {
		F3::set('html_title','My Blog Edit');
		$id = F3::get('PARAMS["id"]');
		$article=new Axon('article');
		$article->load("id='$id'");
		$article->copyTo('POST');
		F3::set('content','admin_edit.html');
		echo Template::serve('layout.html');
	}
);

Now we assigned a function for the POST routes and here’s the content:

	function edit() {
		// Reset previous error message, if any
		F3::clear('message');
		$id = F3::get('PARAMS["id"]');
		$article=new Axon('article');
		//load in the article, set new values then save
		//if we don't load it first Axon will do an insert instead of update when we use save command
		if ($id) $article->load("id='$id'");
		//overwrite with values just submitted
		$article->copyFrom('POST');
		//create a timestamp in MySQL format
		$article->timestamp=date("Y-m-d H:i:s");
		$article->save();
		// Return to admin home page, new blog entry should now be there
		F3::reroute('/admin');
	}

There’s a lot less coding required here than with Slim + extras.

Step 7: Using Middleware

This isn’t relevant to using the Fat-Free Framework.
Authentication using a database is built in so I’ll step through how to use it.
The following lines are added to the admin homepage route

//tell F3 the database table and fields for user id and password
F3::set('AUTH',array('table'=>'user','id'=>'name','pw'=>'password'));
//carry out authentication
$auth = Auth::basic('sql');
//if successful, set a framework variable
if ($auth) {
  //set the session so user stays logged in
  F3::set('SESSION.user',$auth->name);
  //display the admin view
  F3::set('content','admin_home.html');
} else {
  //login unsuccessful so display message
	F3::set('content','security.html');
}

security.html looks like this:

You must supply valid login details.

In the add & edit routes, add this line before Template::serve

if (!F3::get('SESSION.user')) F3::set('content','security.html');

That’s it (if you didn’t spot it in the SQL, my example uses admin & password for the login credentials).
I did get a bug with version 2.0.5, auth.php line 230 if you get this then that line should read self::HTTP_WebAuth instead of HTTP_WebAuth on its own.

You could instead choose to redirect back to the homepage with:

if (!F3::get('SESSION.user')) F3::reroute('/');

This would also work in the start of delete and POST routes and may make the application more secure, security.html wouldn’t be required.

Step 8: Summary

So here is another example of how to quickly get a prototype running using a PHP micro framework.
Is it any faster than with Slim? I’d like to think that it is, there’s less code, less complexity and you aren’t dependent on other packages which may end up changing in some way or not being maintained.
I’ve used F3 to create a times tables application for my son and found it was fun to build and made me more productive – I’ve now put it online at times-tables.willis-owen.co.uk for anyone to use.

I should add that Licensing of Fat-Free Framework means for academic and personal use it uses the GNU General Public License and is free, however for business or commercial gain you need to contact the developers.
Slim is released under the MIT Public License and you are free to use, modify and even sell it.

You can download the files used from here blog.zip.

I’ve done another tutorial on creating a near identical blog application to this but using CakePHP: Blog Tutorial with CakePHP Framework which may be interesting to compare with differences with using a micro-framework.

MySQL 5.1 Partitioning and the Key Cache

User-defined partitioning looks like it is set to be a useful feature in MySQL. It allows you to distribute portions of individual tables across a file system according to rules which you can set largely as needed.

I was looking into it to overcome the obstacles of running OPTIMIZE against large tables, because when optimizing you need more free disk space than the size of the table to optimize it, otherwise MySQL will just sit there idling, not completing the operation but not failing with an error. It just sits there, locking the table and idles (this is a reported bug).

By partitioning, I could split a large table into several pieces, and then optimize each of these separately and not need as much free space on the disk.
Partitioning can also make some queries run faster because MySQL can tell which partition the data is sitting on and only search that one rather that having to search the entire set of data.

However – tucked away at the bottom of one of the last manual pages is a very important limitation: Key caches are not supported for partitioned tables.
The key cache helps to minimize disk I/O by putting table indexes into memory, so by using a partitioned table, each time a query is executed the index files are either accessed from the native file system buffering (provided by the operating system) or it has to be read from disk. I’m not sure I want to try that on a table with several million rows.

Key caching for partitioned tables was fixed by bug #39637 but only available in MySQL 5.5, which isn’t yet suitable for production.

For now, I’m going to create a merge table based on a collection of small tables and optimize these separately when necessary.

IUS MySQL – how to enable the Archive storage engine

IUS is a great way of getting more recent versions of PHP and MySQL onto a Red Hat Enterprise Linux (RHEL) or Centos server than are normally available – without the trouble of having to compile them yourself.

Here is how to add the Archive storage engine to IUS MySql. This applies to MySQL 5.1.41 but will probably be the same for subsequent versions.

  1. Install the Archive storage engine as a plugin:
    yum install mysql51-plugins-archive*
  2. Tell MySQL to include the plugin via a client program:
    INSTALL PLUGIN ARCHIVE SONAME ‘ha_archive.so’

That’s all there is to it.

The blackhole, example, federated and innodb (beta) plugins are also available – you can view the list here.

UPDATE 15/07/10

This isn’t required any more (version 5.1.48-2) the Archive storage engine is now built in.

MySQL Transactions will automatically rollback if not committed

Just a quick note on this one, because although it seems obvious, I couldn’t find this explicitly mentioned anywhere in the MySQL manual entry for transaction syntax.

However if you issue an SQL ‘START TRANSACTION’ statement, anything you do after that will not affect the tables unless the ‘COMMIT’ statement is issued (assuming they are InnoDB tables). This is great because if your server crashed or you lost your connection to it, your data is protected.

Here’s an article from weberdev.com with more information: Using transactions in MySQL

Watch out for calculations with MySQL unsigned integers

MySQL

Unsigned integers are a useful column type in MySQL if you want to make your tables as small as possible. Making tables small is a good idea because at the end of the day, database access is limited by hard drive access speed and if there is less data to read, you will access it faster.

A signed MEDIUMINT has the range -8388608 to 8388607, whereas an UNSIGNED MEDIUMINT goes from 0 to 16777215. If you are sure your number is NEVER going to be negative, then you can save 1 Byte per row by using an UNSIGNED MEDIUMINT instead of an INT.

However – do make sure that any calculations you do in your SQL never involve negative numbers.

Lets set up a simple example, a table with signed and unsigned columns:

CREATE TABLE  `temp` (
`id` int(10) unsigned NOT NULL auto_increment,
`myUnsigned` mediumint(9) NOT NULL,
`mySigned` mediumint(8) unsigned NOT NULL,
PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `temp` (myUnsigned,mySigned) VALUES (1,1);

Now here’s a simple calculation:

SELECT myUnsigned – 10, mySigned – 10 FROM `temp`;

And the results:

myUnsigned  – 10 = -9
mySigned – 10 = 18446744073709551607

I would prefer it if MySQL could issue some kind of error and fail, but instead it produces this stupidly high number. Watch Out!