Abstracting schemas for MySQL and Oracle with Yii Migrations

Yii migrations are an interesting way to keep track of your database changes.  I was originally looking for a tool that would abstract the schema SQL — with something like the Yii ActiveRecord — so I wouldn’t have to write duplicate code for Oracle and MySQL.  I mean, that’s kind of the point of going with the PDO abstraction.

So more than a few forum question/answers led me to Yii migration:

http://www.yiiframework.com/doc/guide/1.1/en/database.migration

I seriously have that page open 3 times in my tabs and I have read it top to bottom probably 20 times.  I keep going back to it over and over hoping it will have more information.  It’s just a little short on answers to all of my questions.

As I said, all I wanted was a way to abstract the schema, so the original premise of “migrations” was a little different than what I was looking for, but it being the only non-custom option, I went forward with it.  The point of migrations is to make sure the state of your database matches the revision of your code.  So it’s sort of like its own hokey versioning system.  As such, it doesn’t function as simply as I might like it.  It’s not easy to focus on one migration if it’s not the last migration.  Because the point of migrations is that you’re moving forward and editing a past migration would be like editing revision X without it effecting all of the future revisions.  So it makes sense for what it is, it’s just awkward.  For one the file structure HAS to include the timestamp.  And it has to include that timestamp at the front of the filename, so autocompleting these migration filenames is a pain in the ass.

One important thing they don’t talk about in the documentation is that the list of what’s been migrated is added automatically to your database via the “tbl_migrations” — and please remember the “s as it will insert it as lowercase, which could be annoying for Oracle users, though Yii seems to handle it “appropriately”.

The only major hurdle here is dealing with autoincrement. The way this was dealt with was I created an Autoincrement class that I plopped in the migrations directory:
protected/migrations/Autoincrement.php

This meant that I could just create the tables like so:

class m120402_194059_Quizes extends CDbMigration
{
	public function up()
	{

		$this->createTable('QUIZES', array(
			'ID' => 'pk',
			'COLLECTION_ID' => 'integer NOT NULL',
			'TITLE' => 'string NOT NULL',
			'DESCRIPTION' => 'string',
			'VISIBILITY' => "integer NOT NULL",
			'STATE' => 'string',
			'SHOW_FEEDBACK' => 'integer',
			'START_DATE' => 'datetime',
			'END_DATE' => 'datetime',
			'DATE_MODIFIED' => 'datetime',
			'DELETED' => "integer NOT NULL",
		));	

		Autoincrement::up('QUIZES', Yii::app()->db->driverName);


	}

	public function down()
	{

		Autoincrement::down('QUIZES', Yii::app()->db->driverName);

		$this->dropTable('QUIZES');

	}

Writing a Style Guide

I took a look at the style guides of all of the most popular frameworks.

Yii Style Guide
Codeigniter Style Guide
Zend Style Guide
Symfony Style Guide
CakePHP Style Guide

Yii is the simplest. They just tell you what should go in the Model, Controller and View. I actually believe that’s the best thing when thinking of a framework as a framework, since a framework shouldn’t be telling you how to write your code, and you should never be mixing your code within the framework. A framework is a tool, but it should be on the primary developer to decide on the style that’s right for them and their team. However, I do understand that most of these style guides are talking about their framework as a project, which of course it is

There are a lot of topics to consider in a style guide. A lot of things popped up as I looked at the different style guides that I hadn’t even considered.

File Names
Class Names
Method Names
Method Definition
Class and File Names Using Common Words
Database Table Names
Constants
Variable Names
Member Visibility (Private / Protected)
Indentation
Commenting
Control Structures
The PHP Tag
Comparing Return Values and Typecasting
Logical Operators
Debugging Code
PHP Errors
Whitespace
Linebreaks
Compatibility
One File per Class
Bracket and Parenthetic Spacing
Localized Text
One Statement per Line
Strings (When to use Single or Double quotes)
SQL Queries
Default Function Arguments
Associative Arrays
Unit Tests

Some of the style guides got so long I kind of got angry. Trying to enforce things like whitespace, logical operators or indentation is foolish. It’s just too much and it takes away from the things that are actually important.

So what’s actually important?

What’s important is what people are going to read. Nobody wants to read through a 30 page style guide, and they won’t. The best solution I’ve found is to have 2 style guides. The first, important style guide is to outline the very essential rules you want for your project. Try to keep it short enough to fit on one page so someone may actually read the whole thing. Then, if you choose, you can have a nitty gritty style guide that outlines how you want your variables named and if you’re an indent nazi. You can always refer back to the nitty gritty if someone is submitting code that you find repugnant.

My style guide for Quizmo
My nitty-gritty style guide for Quizmo

Yii fixtures with Foreign Keys

CDbException: CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 2292 OCIStmtExecute: ORA-02292: integrity constraint (QUIZMO_DEV.FK_ANSWERS_QUESTIONS) violated – child record found
(/web/quizmo/PDO_OCI-1.0/oci_statement.c:142). The SQL statement executed was: DELETE FROM “QUESTIONS”

Totally annoying. Now I’m not one who has a ton of experience with foreign keys as most places seem to have relations with tables but never explicitly link them as such.

So a FK just doesn’t let you add a record when the record it’s linking to doesn’t exist and won’t let you delete a record when there is some other record linking to it.

The problem is I don’t think Yii thought about these when they did fixtures. They built in a way to deal with it, but it’s a little annoying.

Let’s ignore the fact that all of my tables are in caps because I want this to work in Oracle without having to throw single quotes around everything all the time. Let’s take a look at the fixtures of a section of my tables in a project I’m working on right now.

fixtures/
USERS.php
COLLECTIONS.php
USERS_COLLECTIONS.php
QUIZZES.php
QUESTIONS.php
ANSWERS.php

First I need an init.php in the fixture directory. This is called from /yii/framework/test/CDbFixtureManager.php


	public function prepare()
	{
		$initFile=$this->basePath . DIRECTORY_SEPARATOR . $this->initScript;

		$this->checkIntegrity(false);

		if(is_file($initFile))
			require($initFile);
		else
		{
			foreach($this->getFixtures() as $tableName=>$fixturePath)
			{
				$this->resetTable($tableName);
				$this->loadFixture($tableName);
			}
		}
		$this->checkIntegrity(true);
	}

So the init file just needs to do this:

foreach($this->getFixtures() as $tableName=>$fixturePath)
			{
				$this->resetTable($tableName);
				$this->loadFixture($tableName);
			}

The issue is the resets and loads need to be done in the right order. So the way I went about this is I put in an array of the tables, using that and a reversed version of the array.

$reset_order = array(
	'USERS_COLLECTIONS',
	'ANSWERS',
	'QUESTIONS',
	'QUIZES',
	'USERS',
	'COLLECTIONS',
);

$load_order = array_reverse($reset_order);

foreach($this->getFixtures() as $tableName=>$fixturePath){
	if(!in_array($tableName, $reset_order)){
		throw new CException("Table '$tableName' is not in the reset_order.");
	}
	if(!in_array($tableName, $load_order)){
		throw new CException("Table '$tableName' is not in the load_order.");
	}
}

foreach($reset_order as $tableName){
	//echo("resetting $tableName\n");
	// this runs the TABLE.init.php if it exists
	// otherwise it just does a $this->truncateTable($tableName);
	$this->resetTable($tableName);
}
foreach($load_order as $tableName){
	//echo("loading $tableName\n");
	$this->loadFixture($tableName);
}

That’s not all though, because EVERY table needs a TABLE.init.php file that will be run when “resetTable()” is run in the above script.

fixtures/
init.php
USERS.php
USERS.init.php
COLLECTIONS.php
COLLECTIONS.init.php
USERS_COLLECTIONS.php
USERS_COLLECTIONS.init.php
QUIZZES.php
QUIZZES.init.php
QUESTIONS.php
QUESTIONS.init.php
ANSWERS.php
ANSWERS.init.php

Without these scripts, each table will just DELETE FROM MYTABLE when resetTable is called. The problem with this is if you DELETE FROM COLLECTIONS without deleting the QUIZZES first, you get an integrity constraint violation. So a table init file needs to truncate all tables that are “children” of that table in the order of smallest to largest. COLLECTIONS.init.php looks like this:

truncateTable('USERS_COLLECTIONS');
$this->truncateTable('ANSWERS');
$this->truncateTable('QUESTIONS');
$this->truncateTable('QUIZES');
$this->truncateTable('COLLECTIONS');
?>

Yii handling “getLastInsertId” with Oracle

With MySQL or SQLite, when you insert something with an auto_increment field, it will automatically deal with the ActiveRecord by putting the last inserted id into $model->id. Because Oracle needs sequences and triggers to deal with that, neither the PDO driver nor the Yii PDO code felt it necessary to deal with that. Most people are probably fine with commiting to a database and throwing in Oracle specific sequence_name.nextval.

I just added a class in the models to extend CActiveRecord. The only thing you need to do with this is have your model extend this instead of CActiveRecord and add the variable schemaName to the model:

class User extends QActiveRecord
{
	public $sequenceName = 'USERS_SEQ';	
...

Yii Migrations: Dealing with auto_increment for Oracle and MySQL

Oracle deals with auto_increment by using sequences and triggers:

create sequence test_seq
start with 1
increment by 1
nomaxvalue;

create trigger test_trigger
before insert on test
for each row
begin
select test_seq.nextval into :new.id from dual;
end;
/

And MySQL is just an auto_increment keyword on the end of a column declaration:

CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
)
So I created a simple Autoincrement class:

/protected/migrations/Autoincrement.php

Which I included in the migration file for a schema.  This class will handle checking the driver and executing the appropriate sql.
Autoincrement::up('USERS', Yii::app()->db->driverName);
Autoincrement::down('USERS', Yii::app()->db->driverName);

What needs to be remembered here is to add a “sequenceName” to the model class, that is of the form “TABLENAME_SEQ”.

Using Facebook authentication with Yii (PHP)

Note: There many ways to implement this, but this seemed to make the most sense to me at the time.

A login can either come from someone clicking a login link and being sent to a login page or we can force the login and not allow guests.  The login/logout button is easy enough, just modify the distributed site controller’s login and logout “action” methods.  In order to force the login, the best wat to do this is to implement a behavior.  Please see Larry Ullman’s blog for more information on that.

Then we get to the IdentityFactory.  Yii has a nice configuration system in place for its components, so I did some Identity components extending the standard Yii UserIdentity that was included with the Yii distribution. I have the login entry point call on the identity factory which checks a param in the config and returns an instance of the appropriate object.

These objects are where all authentication type specifics happen.  Lets take a look at a simple flow diagram:

Facebook auth flow

So the IdentityFactory chooses which identity we’re using based on the config.  It will send the request over to the FacebookIdentity, which gets all the info needed for the UserIdentity class to query the database, update information there, and then set the user session.

The more interesting part is the connection between FacebookIdentity and the Facebook SDK.  For this I made use of a Yii extension.  I used yii-facebook-opengraph, which, while not the most mature of facebook connect extensions, is the most actively developed and the closest to functional.  (Last year Facebook made a huge change to their SDK which is not at all backwards compatible so they broke most extensions that exist and most developers did not make updates to their extensions.)  This extension only needed one method added to help deal with the Facebook problem with access tokens.


// had to add this function to deal with php's poor handling of expired access tokens
public function setAccessToken($access_token){
return $this->_getFacebook()->setAccessToken($access_token);
}

 

Problems with the Facebook PHP SDK

There were a few reasons I wanted to add Facebook authentication to the new iteration of Quizmo.
  • I wanted a second form of authentication to help in abstracting authentication.
  • I needed a second form of authentication because the intention is to open source this project, and I need to provide something other than iSites authentication if I want people to use it.
  • The process is very similar to Harvard’s PIN authentication, which I have done before, but probably won’t be necessary for Quizmo as it will be available through iSites.
  • “Everyone” uses Facebook so it’s probably something worth knowing.
The first minor problem was there is no proxy support.  The Facebook SDK takes you to a Facebook login page to authenticate, but before it sends you there, it checks if you’re already logged in.  This is done through a curl request.  The problem comes if you’re working in a development environment which doesn’t have a direct outlet to the internets.  The curl options are set as

public static $CURL_OPTS = array(
CURLOPT_CONNECTTIMEOUT => 10,
CURLOPT_RETURNTRANSFER => true,
CURLOPT_TIMEOUT => 60,
CURLOPT_USERAGENT => 'facebook-php-3.1',
);

So this is stored as a static variable in the abstract BaseFacebook class – which is absurd because that means it can’t be overridden.  As such the following line has to be added to the BaseFacebook class:

CURLOPT_PROXY => "my.proxy.address:myport",

The larger problem with the Facebook PHP SDK is its inability to handle token errors.  The tokens are set server side when someone authenticates the first time.  These tokens then expire and the SDK is unable to handle errors that occur because of this.  The weirdest thing about this error is that the developers have a solution, they just expect people using their SDK to implement it on their end.  Regardless, their code did not work for me as advertised and had to be tweaked as such:
private function checkAccessToken(){

$app_id = ;
$app_secret = ;
$my_url = ;

// known valid access token stored in a database
$access_token = ->getAccessToken();

$code = @$_REQUEST["code"];

// If we get a code, it means that we have re-authed the user
//and can get a valid access_token.
if (isset($code)) {
$token_url="https://graph.facebook.com/oauth/access_token?client_id="
. $app_id . "&redirect_uri=" . urlencode($my_url)
. "&client_secret=" . $app_secret
. "&code=" . $code . "&display=popup";

// Now all file stream functions can use this context.
$response = $this->curl_get_file_contents($token_url);
$params = null;
parse_str($response, $params);
$access_token = @$params['access_token'];
}

// Attempt to query the graph:
$graph_url = "https://graph.facebook.com/me?"
. "access_token=" . $access_token;
$response = $this->curl_get_file_contents($graph_url);
$decoded_response = json_decode($response);

//Check for errors
if (@$decoded_response->error) {
// check to see if this is an oAuth error:
if ($decoded_response->error->type== "OAuthException") {
// Retrieving a valid access token.
$dialog_url= "https://www.facebook.com/dialog/oauth?"
. "client_id=" . $app_id
. "&redirect_uri=" . urlencode($my_url);
error_log("top.location.href='" . $dialog_url);
}
else {
error_log("other error has happened");
}
}
else {
// success
//error_log("success" . $decoded_response->name);
//error_log($access_token);
}

return $access_token;

}