SQLBuilder focuses on providing a simple syntax for building SQL statements.
When switching database backend, you can simplely change the driver type of query builder, and it will generate the proper SQL for your backend, you don't have to modify the code to support different backend.
For example, pgsql support returning
statement, this kind of syntax will only
be built when this feature is supported.
- Simple.
- Fast & Powerful.
- Ability to change SQL style, question-mark style, named-placeholder style.
- Ability to change quote style, table name quoting, column name quoting..etc.
- Configurable escaper and quoter.
- No package dependency.
$ pear channe-discover pear.corneltek.com
$ pear install corneltek/SQLBuilder
{
"require": {
"c9s/sqlbuilder": "*"
}
}
$driver = new Driver('mysql');
$b = new SQLBuilder\QueryBuilder($driver,'Member');
$b->select('*');
$b->where()
->equal( 'a' , 'bar' );
$sql = $b->build();
// SELECT * FROM Member where a = 'bar'
Get your SQL driver
$driver = new SQLBuilder\Driver('pgsql');
$driver = SQLBuilder\Driver::getInstance();
$driver = SQLBuilder\Driver::create('pgsql');
string quote/escape handler:
$driver->configure('escape',array($pg,'escape'));
$driver->configure('quoter',array($pdo,'quote'));
$driver->escaper = 'addslashes';
$driver->quoter = function($string) {
return '\'' . $string . '\'';
};
$driver->configure('driver','pgsql');
Trim spaces for SQL ?
$driver->configure('trim',true);
SQLBuilder supports two placeholder styles:
- named parameter by PDO
- question-mark paramter by mysql, PDO.
$driver->configure('placeholder','named');
This generates SQL with named-parameter for PDO:
INSERT INTO table (foo ,bar ) values (:foo, :bar);
If you pass variables to build SQL with named parameters, query
builder converts named parameters for you, to get variables, you
can use getVars
method:
$vars = $sb->getVars();
Which returns:
array(
':name' => 'Foo',
':phone' => 'Bar',
);
Or to use question-mark style:
$driver->configure('placeholder',true);
This generates:
INSERT INTO table (foo ,bar ) values (?,?);
Build SQL query for table 'Member':
$builder = new SQLBuilder\QueryBuilder($driver);
$builder->table('Member');
$builder->select('*','column1','column2');
$builder->select(array(
'column1' => 'as1',
'column2' => 'as2',
));
Build Select SQL
$sql = $builder->table('Member')->select('*')
->where()
->equal( 'a' , 'bar' ) // a = 'bar'
->notEqual( 'a' , 'bar' ) // a != 'bar'
->is( 'a' , 'null' ) // a is null
->isNot( 'a' , 'null' ) // a is not equal
->greater( 'a' , '2011-01-01' );
->in( 'a', array(1,2,3,4,5) )
->greater( 'a' , array('date(2011-01-01)') ); // do not escape
->or()->less( 'a' , 123 )
->and()->like( 'content' , '%content%' );
->group() // AND ( a = 123 AND b != 123 )
->is( 'a' , 123 )
->isNot( 'b', 123 )
->ungroup()
->back() // back to sql builder
->build();
The where()
returns SQLBuilder\Expression
object.
Condition->back()
returns QueryBuilder object
$builder->select('*')->table('items')
->groupBy('name')
->limit(10)->offset(100);
?>
For PostgreSQL, which generates:
SELECT * FROM items OFFSET 100 LIMIT 10;
For MySql, which generates:
SELECT * FROM items LIMIT 100,10;
$query->select('*')->table('items')
->where()
->between('created_on', '2011-01-01' , '2011-02-01' );
SELECT * FROM items WHERE created_on BETWEEN '2011-01-01' AND '2011-02-01'
$query->select('*')->table('items')
->where()
->in('a', array(1,2,3,4));
SELECT * FROM items WHERE a IN (1,2,3,4);
$query->select('*')->table('City')
->where()
->in('name', array('Taipei','France','Japan'));
SELECT * FROM City WHERE name IN ('Taipei','France','Japan');
Insertion:
$builder->insert(array(
// placeholder => 'value'
'foo' => 'foo',
'bar' => 'bar',
));
For question-mark style SQL, you might need this:
$builder->insert(array(
'foo',
'bar',
));
The last thing, build the SQL statement:
$sql = $builder->build();
$driver = new Driver;
$driver->configure('driver','mysql');
$driver->configure('placeholder','named');
$sb = new QueryBuilder('member',$driver);
$sb->update( array( 'set1' => 'value1') );
$sb->whereFromArgs(array(
'cond1' => ':blah', // is equal to where()->equal('cond1',':blah')
));
$sql = $sb->build(); // UPDATE member SET set1 = 'value1' WHERE cond1 = :cond1
$sb = new QueryBuilder($driver,'Member');
$sb->alias('m')
->join('table_name')
->alias('t')
->on()->equal( 't.zzz', array('m.ccc') ) // not to escape string (with array())
->back() // return to join expression object
->on()->equal( 'a.foo', 'string' ) // treat as string, escape string
->back() // go back to SqlBuilder object.
->toSql();
$driver = new Driver;
$driver->configure('driver','mysql');
$driver->configure('trim',true);
$sb = new QueryBuilder($driver,'member');
$sb->delete();
$sb->whereFromArgs(array( 'foo' => '123' ));
$sb->where()->equal('foo',123);
$sql = $sb->build(); // DELETE FROM member WHERE foo = 123
$builder = new SQLBuilder\MigrationBuilder( $driver );
$sql = $builder->addColumn( 'members' ,
SQLBuilder\Column::create('price')
->integer()
->notNull()
->default(100)
);
// ALTER TABLE members ADD COLUMN price integer DEFAULT 100 NOT NULL
$sql = $builder->addColumn( 'members' ,
SQLBuilder\Column::create('email')
->varchar(64)
);
// ALTER TABLE members ADD COLUMN email varchar(64)
$sql = $builder->createIndex( 'members', 'email_index', 'email' ); // create index email_index on members (email);
$sql = $builder->dropIndex( 'members', 'email_index' );
PHPUnit_TestMore
is needed.
$ pear channel-discover pear.corneltek.com
$ pear install corneltek/PHPUnit_TestMore
Install Universal package for the classloader:
curl -s http://install.onionphp.org/ | sh
onion -d install
Copy the phpunit.xml
file for your local configuration:
phpunit -c your-phpunit.xml tests
- http://dev.mysql.com/doc/refman/5.0/en/sql-syntax.html
- http://www.postgresql.org/docs/8.2/static/sql-syntax.html
- http://www.sqlite.org/optoverview.html
Yo-An Lin (c9s) cornelius.howl@gmail.com