robmorgan/phinx — pull request #145 by danielyule (2013-10-31 14:29:44+00:00)
Title: Added Views functionality

I've created a basic framework for adding views using Phinx. This may be outside the scope of what you want with Phinx, and if so, I'll simply have a fork.

Adding or replacing a view within a migration looks something like this:
```php
$this->view('MyView')
->tableName('Table1')
->addColumn('colA', 'Table1')
->addColumn('colB')
->setCondition($this->condition()
->aand()
->comparison("=")
->column("colA")
->column("colB", "Table1")
->comparison("<")
->column("colC", "Table1")
->column("colD"));
```
This will generate the following MySQL (or similar PostgreSQL):
```mysql
CREATE OR REPLACE VIEW `MyView` AS SELECT `Table1`.`colA`, `colB` FROM `Table1` WHERE `colA`=`Table1`.`colB` AND `Table1`.`colC`<`colD`;
```
Joins are supported with a similar notation.

As you can see, I've implemented WHERE expression syntax with a sort of post-fix function call notation, so that the series of function calls made to generate the WHERE clause form a kind of tree when indented. It's a bit strange, but I think it's easy enough to get the hang of from looking at an example, and much more elegant than something like ```new And(new Comparision('=', new Column('colA', 'Table1')...``` or some sort of array based syntax.

I've used functions called aand() and oor() for the AND and OR keywords in SQL, because 'and' and 'or' are restricted keywords in php, but I could be convinced to name them differently.

I've also added in a collection of unit tests for the functionality.

I haven't finished yet, as there are still some keywords (LIKE, BETWEEN) my system doesn't support, and more documentation and unit tests are needed. The API might also change slightly in the meantime

At this point, I am submitting this pull request to give you a chance to take a look at it, possibly give me some feedback and let me know if I should approach this as an addition to Phinx, or as a separate project.

Let me know,
-Daniel