A Basic Guide for Newbie Yii Developers Part II

This is Part II of my introduction to Yii tutorials. If you haven't yet, take a look at Basic Guide for Newbie Yii Developers Part I.

Last week I covered how to create your first Yii project and put it under version control. In that tutorial, we created the project files for our first app: Yiicommerce. If you have been following along, great! If not, you can find the project files on Github. I have made a Git Tag representing the state of the project at the end of the last tutorial (don't worry, I'll show you how to do it too!), so download Tutorial-Part.I and follow along!

If you're using Git, you can also checkout the tag like this:

$ git clone git@github.com:brixican/Yii-commerce.git
$ git checkout tutorial-part.I

In part II of this series, we will begin designing our e-commerce application, starting at the database level. Today we'll build our database schema in MySQL, and use Yii's built-in Gii component to generate our initial Models, Views and Controllers. Before we get started, I think a brief introduction into some core Yii elements are in order.

An Overview of the Yii Framework

Models, Views, Controllers, Oh My!

If you have already begun looking into using Yii, or any PHP framework, no doubt you've been barraged by the term MVC. What is MVC? It's a design pattern. It's a best practice, if you will, for organizing your code and functionality. By separating core components of your application, such as the business logic, user interfaces, and the data, you (the developer) can focus on developing each component without worrying about breaking, or interfering with, the others. If you are absolutely new to the MVC design pattern, I would highly recommend taking a quick look at the official Yii MVC Fundamentals tutorial, as they do a great job at explaining what MVC is and how it works. I will assume you have a basic grasp of the concept. Rather than explain MVC, I will explain where various portions of a website fit into these designs.

Models

Models are the core of your application. They contain your business logic. They also dictate how data is accessed and stored. Models can hold any and all information in a website. For Pieces of Eight Costumes, models are used to represent each individual product in the site. My models store price information, product names and descriptions. They also store relational data, such as what sizes (small, medium, large, etc..) each product supports, the category a product falls under, and other important relations. Of course the models I am describing here store data in a database, but Yii also supports other types of models as well. Not all data handled by a website gets stored into a database. For example, a "Contact Us" form, that takes some user input and emails it somewhere does not necessarily need to store that information. Yii provides us with special models designed to temporarily store this data. It helps us organize, manipulate, and validate our input before deciding to either store it, email it, or display it.

Views

Views contain all of the logic to present your website to your audience. Not only will views be where you write your HTML, they will also be where you invoke CSS and Javascript, perform AJAX calls, render web forms, and create templates and layouts. Views also aid in gathering user input and passing it into a Model. For example, a contact form stores its data into a model, but gets passed into a view to be rendered. The same form can be rendered in a variety of ways simply using different views. In general you will have a view per section of your website, such as a "Contact Us" view, "About Us" view, a view for your home page. However many views are also very re-useable. For example for PiecesOfEightCostumes.com, each category uses the same view to render the data, we simply pass it different products to render. Also, the page to render a single product is a generic view that simply takes a Product model and renders it to the page. In Yii, views come with many very handy functions to help speed up the development of HTML.

Controllers

Controllers are essentially the glue of any application. They control the flow of the users experience: handing them to the different views on your website, passing data between views and models, and helping to select which business logic to invoke based on the current state of the application. Controllers are comprised of Actions. In the 'Contact Us' example, our action would instantiate and initialize our ContactForm model and pass it to the Contact view. Yii also maps actions to URLs, so we could setup a url such as www.website.com/contact to begin the process of rendering the view.

Yii Folder Structure

Right now, the folder structure for Yii-Commerce looks like this:

• yiicommerce/
• assets/
• css/
• images/
• protected/
• themes/
• index-test.php
• index.php
• README.md

We'll eventually explain the purpose of most of the files, but in this tutorial we are going to focus on the contents of protected/, as that is where the majority of the Yii-Commerce project files are:

• yiicommerce/protected/
• commands/
• components/
• config/
• controllers/
• data/
• extension/
• messages/
• migrations/
• models/
• runtime/
• tests/
• views/
• .htaccess

The amount of folders may seem a bit overwhelming at first, but don't worry! They are purely for organizational purposes. For example, all of our application's Models will be placed in the models/ directory. If you begin exploring these folders, you'll see our application already has some models an controllers and such. That's because the app we generated is already functional! We'll be deleting and editing files we don't need, so for now you can ignore the additional functionality in the application.

Designing our Database Schema

Finally we get to the coding! In my opinion, the best place to start designing an application for Yii is with the database. Being an e-commerce application, our number one concern is selling products. We need a way to represent a product in our database. Let's start off by making Products and Categories. We want products that have a name, price, and description. Each Product will belong to exactly one Category, and each Category can contain multiple products.

In your protected/data/ folder, create a file called yiicommerce.schema.sql with the following contents:

#-- Category : represents a Category in the database
#-- Category HasMany Product
CREATE TABLE yc_category
(
 #-- Key
 id  INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
 
 #-- Attributes
 name  VARCHAR(255) NOT NULL,
 
 #-- Constraints
 PRIMARY KEY (id),
 UNIQUE KEY fk_category_name(name)
 
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


#-- Product : a product to sell in our application
#-- Product BelongsTo Category
CREATE TABLE yc_product
(
    #-- Key
    id            INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,

    #-- Attributes
    name          VARCHAR(40) NOT NULL,
    price         DECIMAL(6, 2) NOT NULL,
    description   TEXT,
    
    #-- Relationships
    category_id   INTEGER UNSIGNED NOT NULL,

    #-- Constraints
    PRIMARY KEY (id),
    FOREIGN KEY (category_id) REFERENCES yc_category(id),
    UNIQUE KEY uk_product_name (name)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

#--
#-- Test Data
#--
INSERT INTO category (name)
VALUES
 ("Electronics"),
 ("Clothes"),
 ("Sporting Goods");

INSERT INTO yc_product (id, name, price, description, category_id)
VALUES
 (1, "Laptop", 1200.00, "A stunning little laptop.", 1),
 (2, "Smartphone", 299.99, "The most powerful smartphone yet.", 1),
 (3, "White Shirt", 12.50, "Comfortable, cotton, and plain.", 2),
 (4, "Football Helmet", 68.75, "Authentic NFL helmet.", 3);

Here, we have created the schema for our products. A product has Name, Price, and Description attributes. It uses an integer as the Primary Key inside of the database, and as a further constraint, no two products can have the same name. We also link Products and Categories together using the category_id attribute. By linking in this way, our Category models can each contain many Products, but our Product models can only belong to exactly one Category. We also created a few test categories and products, which we can always alter later.


Now that we've created the schema, it's time to make our tables. If you're using XAMPP or something similar, you probably already have phpMyAdmin installed. Go to http://localhost/phpmyadmin, and create a new database called yiicommerce. Once created, click the SQL tab, then copy and paste the contents of your schema file and hit the Go button. You will now see two tables, yc_category and yc_product.


Now we need to configure Yii to communicate with our database. Open the config file located at: /protected/config/main.php. Locate the 'components' key, and under that the 'db' key. You'll notice there are two entries, an sqlite string and a commented-out mysql string. Since we're not using sqlite, delete that one and uncomment the mysql string. In the 'connectionString', replace 'mysql:host=localhost;dbname=testdrive' with 'mysql:host=localhost;dbname=yiicommerce', and fill in your username and password to your database if necessary. Yii now has access to our database. 

Gii 

We're going to use Yii's built-in component called "Gii" to take our database schema, and generate our models, views, and controllers for us. While this is not a feature unique to Yii (Ruby on Rails, Symfony, CakePHP, etc.. all have a similar tool), it is nevertheless a very powerful tool that Yii provides us. 

In the config file, scroll up to the 'modules' key, and uncomment the Gii tool, as we will be using this next. Change the password to something simple, and remember to remove this section before ever deploying your Yii application.


Visit the following URL to login to the Gii tool (using the password in your config file): http://localhost/yiicommerce/index.php?r=gii, then click on the 'Model Generator' link. 


For the Table Prefix, put in the 'yc_' prefix that we added to all of our table names. Insert '*' under Table Name to have Gii generate all of our tables at once. Finally, click the preview button, and then the generate button. If Yii has an error writing the files, make sure your directories are writeable using the following chmod command:

$ chmod -R 766 protected/

Now that Yii has now created our models, let's make a controller and a view for it. Click the 'Controller Generator' link, and enter 'product' for the controller id field. We'll leave the Action IDs with just index for now. Again, click preview and generate to create our controller and view file. 


We now have the following files created in our protected/ folder:

• models/Product.php
• models/Category.php
• controllers/ProductController.php
• views/product/index.php

We can view our newly created content (although there's not much to see) by going to the following URL: http://localhost/yiicommerce/index.php?r=product

Committing our Changes

Now that we have altered our application, it's time to save our changes. The following Git commands will add our files into our repository, and commit changes to existing files:

$ git add -A
$ git commit -a -m "Added Product and Category models. Created controller and view for Products"
$ git push origin master

After entering your password, your changes will now be commited into your github repository. You'll notice I've been creating Tags for each tutorial. A Tag is essentially a snapshot of the project files. To create the Tag for this tutorial, I ran the following commands:

$ git tag -a tutorial-part.II -m "Tutorial Part II"
$ git push --tags origin master

While tags are not something you will create often, they work very well for tagging release versions of software.


Anyway, that's it for today! Next time we'll spruce up those controllers and views and start making some useful web functionality!

MySQL Comparing NULL values returns empty set

It has been a little while since I touched some MySQL. Long enough that I forgot about an issue with how MySQL treats NULL values.  What's the issue? You cannot use the typical SQL comparison operators when testing for a NULL value.  It even says so in the MySQL documentation:

http://dev.mysql.com/doc/refman/5.0/en/problems-with-null.html

It states two facts:
  * "In SQL, the NULL value is never true in comparison to any other value, even NULL."
 * "To look for NULL values, you must use the IS NULL test." 


In other words, MySql provides special operators when dealing with NULL values.  So if you were trying to perform a simple query like this:

SELECT id FROM users WHERE email = NULL

you would not get a syntax error. Instead, you just get an empty result returned by the query. Thanks, MySQL. (I sadly spent a good couple of hours tracking this exact problem).  Instead, performing the query like this:

SELECT id FROM users WHERE email IS NULL

will yield the expected results.  There is also IS NOT NULL, and a function IFNULL() to aid in your NULL comparisons.  The documentation is a pretty good read, as mysql handles null quite differently then other languages.

Hopefully this will help save some innocent keyboards being destroyed out of frustration!

5 MySQL Best Practices When Designing Schemas

As a follow up to my previous article on Common MySQL Performance Optimizations, I decided to post some of my research findings of MySQL best practices that are commonly suggested by MySQL experts in the community.

Following a set of established good practices can usually prevent common mistakes/errors or performances losses. Admittedly, some MySQL 'best practices' can be elusive, so it is nice to see a few all in one place. Here is a list of practices I try to stick to when creating database schemas:

1) Always specify NOT NULL if possible

For any field that does not need to hold a NULL value (usually most if not all of my fields in a table), specifically define that field as 'NOT NULL'. Each table that does not specify this will actually be one byte larger than it should be. If you had a table with five INTEGER fields, none of which specify NOT NULL, than you are looking at:

5 fields X (4-byte INTEGER size + 1-byte NULL option) == 25 bytes per record.

If you specified NOT NULL:

5 fields X 4-byte INTEGER size == 20 bytes per record.

That's a 20% decrease in record size! This can cause huge performance gains when you're talking potentially hundreds of thousands of records.

2) Specify the correct field types for the data you are storing.

If you have a field that will hold values between 1 and 10, don't specify that field as BIGINT (or even INT, for that matter). Store it in a TINYINT instead.  A Tinyint field can store values between -128 and 127 (0 and 256 if Unsigned), and is only 1-byte in size (as opposed to 4-byte Integer or 8-byte Bigint). If you are storing an IP address, consider using PHP (or your favorite language) to convert it to an integer, and store it in an INT type rather than a VARCHAR. If you know your username's or passwords cannot be longer than 40 characters, don't store them in a CHAR(256). Also, if you have a field that may store different string values such as this:

player_type      VARCHAR(32);   -- can be: 'Admin', 'Standard'

consider instead making player_type an ENUM value.  The nice thing about enum values is that you can still interface with the variable using strings, but the actual values are stored as Ints!

player_type      ENUM('Admin', 'Standard');   -- Yay!

For more information on MySQL data types, check out the official documentation.

3)  Always used UNSIGNED when possible

If you have integer fields in your tables which cannot or should not contain negative values, store them as UNSIGNED.  Not only will this double the amount of values that can be held in the variable, but it also ensures data integrity be not allowing the field to contain erroneous values.

4) Use Indexes to your advantage

Properly defined indexes can be one of the biggest optimizations you can apply to your database.  When used incorrectly however, it can actually decrease performance.  The hardest thing about indexes is knowing when to use them, and when not to use them.  In general I think a good rule of thumb is if you are using a field in a WHERE clause, apply an index to it.  Doing so will help speed up searches, especially when performing joins. As always, be sure to utilize the EXPLAIN and SHOW aspects of MySQL in order to fully optimize your tables based on the queries used in your applications.

5) Use LIMIT 1 when grabbing unique rows from a table

While this does not actually fit into the design stage of a schema, it was still a MySQL best practice that I learned while researching the topic.  Using LIMIT 1 ensures that once your unique row is found, MySQL will stop searching and return the results.  This can potentially provide a small performance boost to your applications. 

There are certainly more established best-practices touted by other MySQL experts in the community, and this is not an exhaustive list by far.  These were simply some of my favorites that I try to stick to whenever I am creating a new schema.  If you know of other best practices that should be followed when using MySQL, let us know about it in the comments!

Common MySQL Database Performance Optimizations

While developing the database schema used by Fireteams.net, I realized there were many performance improvements I could make upon my initial design.  After some research, I found that some of the performance boosts I applied were commonly asked questions on the internet. During this research I also learned some new tricks that I thought were very useful. While not a comprehensive list, I thought I would share some common performance optimizations and MySQL best practices that are commonly recommended amongst the MySQL community:



1) Normalize those Tables

It is highly recommended that you start your database designs in (at least) 3rd normal form. If you are unfamiliar with database normalization, the MySQL developer zone has a great introductory article on the subject. In general, normalizing your database involves creating tables such that every non-key element in the table is dependent upon a primary key, and only that key. Doing so increases data integrity by removing large amounts of data redundancy and (in my opinion) makes your database much easier to maintain. Normalization makes for a great starting point into further performance enhancements.



2) Choose the right database Engine.

In MySQL, the choice generally boils down to MyISAM vs. InnoDB.  While a google search here can give you more information than I can about these choices, I will point out some interesting information about the two.  First, is that MyISAM does not support the use of Foreign Keys.  Foreign keys can be very important for data integrity, so anyone wishing to use this functionality will most likely want to use InnoDB as their storage engine.  InnoDB also supports the use of Transactions, another invaluable tool for data integrity (and once again, something MyISAM tables do not support).


Something I learned recently about InnoDB regards its performance when counting rows in a table.  Basically, little metadata is kept about a table. If you wanted to count all rows in a table like this:

SELECT COUNT(*) from Users;

You will always end up iterating through each row in the table. This can cause a huge performance hit if you are running this query often on a table with hundreds of thousands of rows.  MyISAM tables do not have this problem, as the number of rows in the table is kept as metadata and is used directly by MySQL when calculating this value. Work around? Well, the generally accepted way is to create a separate table which stores the row counts. Of course this requires manually keeping a count of the rows, but if you need this data often then it may very well be worth the effort.

Another note about MyISAM is that it is geared towards tables that are heavy on reads, with very little writes. If you have a table with a high number of updates or inserts vs the amount of selects, you will most likely have better performance using InnoDB instead.  For a great reference on the different types of storage engines, check out Nilesh Pawar's blog entry on MySQL Storage Engines. He also has a bunch of other great articles as well.



3) Vertical Partitioning


The concepts of vertical partitioning were new to me. After some poking around however, it turns out that when done correctly it can lead to a substantial performance boost.  The concept is simple: split a large table such that some of its columns exist in one table, and the rest of its columns exist in another table. Initially I thought it seemed rather pointless. You would still need to join the data back together, so wouldn't it simply hurt performance? Not when done correctly... Consider the following example table:

Table User (
      user_id,
      username,
      password,
      email,
      alternate_email,
      date_registered,
      subscribed_to_newsletter
);

The types of these fields are not important (hence them being omitted in this table). What can we note about this table? First is that every time a user logs in, the username, password, and maybe email fields will be touched. The other fields will most likely only be touched in specific portions of the application. When MySQL is searching/reading/writing records, the entire record must be brought into memory. The larger the record (the more fields in the table), the longer is operation can take. In other words, if this table was smaller we might increase the performance of accessing common User fields. If the tables were split "Vertically" like this:

Table User (

      user_id,

      username,

      password,

      email

);

Table User_Profile (

      user_id,

      alternate_email,

      date_registered,

      subscribed_to_newsletter

);

Accessing username and password become much faster, since it no longer has to load each of the other fields into memory just to access them. When date_registered or alternate_email are required, a simple join would re-create the full table. For more information about how vertical partitioning can increase performance, I found this article to be quite helpful.

So, Vertical partitioning. You love it. You want to use it. How do you know which fields to split into another table?  There are many ways you can split a table, and it depends on the goals of your performance boost. Here are some common ways you could think about splitting your tables:


A) Split your read-heavy data from write-heavy data.

When you have to write to a table, on top of access times you also may invalidate the record in the cache, which means slower access times when you want to read it again. By splitting read-heavy from write-heavy data into separate tables, your read-heavy data will stay in the cache, and both sets of data will also benefit from quicker access times (due to each table being smaller).

B) Remove variable-length fields into separate tables.

MySQL makes a distinction between 'static' tables and 'variable-length' tables. Basically, if the table contains any fields which are not fixed in size (such as VARCHAR or TEXT fields) it is treated as variable-length. MySQL treats these tables slightly differently, and can iterate static tables faster than variable-length tables.  If you had a table such as the following:

Table Product (
      id                  INTEGER,
      cost               MEDIUMINT,
      weight          MEDIUMINT,
      description    TEXT
);

You may find that access times will increase if you created a new table called Product_Descriptions, which only held the description field of Products. This example (and Vertical Partitioning in general) will not yield a performance benefit if the majority of your reads to this table also required reading the product description, as now you will also have to perform a join on nearly every query. If you only sometimes need the description, but find yourself reading the cost and weight often, then this type of partitioning may yield a nice performance boost in your application.



4) Horizontal Partitioning


Horizontal partitioning is a type of partitioning supported by MySQL.  It involves logically splitting a table into different physical locations, generally based on some sort of sorting value/function/hash. MySQL supports many different types of functions to use when partitioning a table.  The benefit of horizontal partitioning is that you keep the same logical table, and MySQL will take care of the details behind the scenes.  Each partition has its own index table, so performing queries become much more efficient. As an example, take a look at the following table definition:

CREATE TABLE Log_Entry (
      id             INTEGER UNSIGNED NOT NULL PRIMARY KEY,
      type         TINYINT UNSIGNED NOT NULL
) PARTITION BY RANGE (type)
(
      PARTITION p0 VALUES LESS THAN (10),
      PARTITION p1 VALUES LESS THAN (100),
      PARTITION p2 VALUES LESS THAN MAXVALUE
);

Data will now be placed into one of the three physical partitions based on the value of 'type'.  The performance gain comes into play when performing queries on this table with 'type' in the WHERE clause. The MySQL Optimizer is very good at pruning partitions, so in this scenario the 'type' is in the where clause, MySQL will only search the partitions where the data could be placed in.  Naturally, this pruning prevents much of the data from being scanned, resulting in potentially large performance gains. Partitioning really shines when used on tables used primarily for storage, especially when that data may consist of hundreds of thousands to millions of records.

This sounds like an easy way to get performance gains out of any database, but there IS a catch.  Horizontal partitioning cannot be used on tables which use Foreign Keys, nor can you use Foreign Keys to reference a partitioned table.  That being said, partitioning can still be used very effectively to get large performance boosts, and I would definitely suggest looking into using it as a performance optimization for large tables.

While a quick google search will yield lots of information about horizontal partitioning in MySQL, here is a great success story where partitioning had a positive impact on a large database table.



5) Triggers for data integrity


While not necessarily a performance optimization, using Triggers can be a good practice in regards to ensuring data integrity.  Triggers can be defined as "Before Insert," "After Insert," "Before Delete," etc.. and thus can be used for all sort of great purposes.  Triggers can be used for logging, initializing or validating fields, performing certain operations or logic on other tables, the possibilities are quite endless. For a great in-depth tutorial on Triggers, check out this NetTut's tutorial.



There are many more ways to optimize for better performance, and this post really just scratches the surface as far as what is possible in MySQL.  What do you think of these optimizations?  Do you have a great performance boost that others (like myself) may benefit from knowing? Do share!