chh / sirel
Flexible SQL builder which builds an Abstract Syntax Tree of SQL and dumps it.
Requires
- php: >=5.4.0
Requires (Dev)
- chh/bob: ~1.0@dev
- doctrine/dbal: 2.*@stable
- phpunit/dbunit: ~1.2
- phpunit/phpunit: ~3.7
Suggests
- doctrine/dbal: For the Sirel\Visitor\DoctrinePlatform visitor.
This package is not auto-updated.
Last update: 2024-10-26 12:53:54 UTC
README
Sirel is a representation of the SQL Abstract Syntax Tree in PHP.
Sirel aims to be to PHP what Arel is for Ruby.
Install
Requirements:
- PHP 5.4
- Optional:
doctrine/dbal
for wider Database support
Install with Composer:
php composer.phar require 'chh/sirel:1.0.*@dev'
Then require vendor/autoload.php
in your app.
Sirel is under heavy Development and so the following shortcomings and bugs still have to be ironed out:
- Only tested with SQLite, most other DBMS implement a superset of SQLite's SQL, so may also work with MySQL though.
- No Generation of DBMS-specific SQL (will likely come as Doctrine DBAL-enabled Visitor)
- Only DML (use Doctrine DBAL if you want to run database independent DDL)
Now let's dive into a bird's eye overview of the things that work ;-).
Frequently Asked Questions
How do I protect myself from SQL injection when using Sirel?
I recommend using Prepared Statements. Make sure you mark
placeholders as raw SQL with Sirel::sql()
.
Example using PDO:
<?php use Sirel\Sirel; use Sirel\Table; $users = $u = new Table("users"); $select = $users->take(1)->where($u->id->eq(Sirel::sql(':id'))); $connSpec = "…"; $pdo = new \PDO($connSpec); $stmt = $pdo->prepare($select->toSql()); $stmt->bindParam(':id', $_GET['id']); $stmt->execute(); $user = $stmt->fetch();
Relations
The core of Sirel's Query Building API is the Table. The Table Object provides convenient access to attributes and Factory Methods for Queries.
You may call these methods on the Table Instance to start building a new query:
from
,project
,join
,where
,order
,group
,take
orskip
to start building a Select Queryinsert
to start building an Insert Queryupdate
to start building an Update Query- and
delete
to start building a Delete Query
The constructor takes one argument: the Table Name.
<?php use Sirel\Table; $users = new Table("users");
The Table Instance can additionally be accessed like an Array to get an Instance of an Attribute.
If no attribute is defined, then it returns a new instance of Sirel\Attribute\Attribute
.
<?php ... echo $users['username']; // -> users.username // If you don't like accessing arrays: assert($users['username'] === $users->username);
Joins
Joins look very similar to their SQL Counterparts. Joins are started by the join
operator. The Join's
ON
Expression is then set with the next call to on
. The call to on
expects one or more Expressions.
Example:
<?php use Sirel\Table; $profiles = new Table("profiles"); echo $profiles->join($users)->on($profiles['user_id']->eq($users['id'])); // -> SELECT * FROM profiles INNER JOIN users ON profiles.user_id = users.id
Left Joins can be created with the leftJoin
operator.
Example:
<?php use Sirel\Table; $profiles = new Table("profiles"); echo $profiles->leftJoin($users)->on($profiles['user_id']->eq($users['id'])); // -> SELECT * FROM profiles LEFT JOIN users ON profiles.user_id = users.id
Selections
Selections are done in Sirel with the where
operator. The where
operator takes one or more expressions as argument, which can be
created by an attribute. These expressions are then joined by the "AND" Operator.
Restrictions are created by calling the respective method on an attribute. The following restrictions are supported (which each correspond to their SQL equivalents):
- eq
- notEq
- gt
- gte
- lt
- lte
- like
- notLike
- in
- notIn
Examples:
<?php use Sirel\Table; $users = new Table("users"); echo $users->where($users['username']->eq("johnny"), $users['password']->eq('superSecretPass')); // -> SELECT * FROM users WHERE users.username = 'johnny' AND users.password = 'superSecretPass' echo $users->where($users['username']->like('a%')); // -> SELECT * FROM users WHERE users.username LIKE 'a%' echo $users->where($users['id']->in([3, 4, 10])); // -> SELECT * FROM users WHERE users.id IN (3, 4, 10)
Ordering
Ordering is done with the order
Operator. It receives either an Order Expression
or a combination of an Attribute Name and a Direction (\Sirel\Node\Order::ASC
or \Sirel\Node\Order::DESC
).
Additionally Attribute Instances provide asc
and desc
methods for creating Order Expressions.
<?php ... echo $users->order($users['username']->asc()); // -> SELECT * FROM users ORDER BY users.username ASC echo $users->order($users['username']->desc()); // -> SELECT * FROM users ORDER BY users.username DESC echo $users->order($users['username'], \Sirel\Node\Order::DESC); // -> SELECT * FROM users ORDER BY users.username DESC
Use reorder
to clear all current order operations:
<?php $select = $users->order($users->username->asc())->order($users->id->asc()); // Now let's reorder: echo $select->reorder($users->id->desc()); // -> SELECT * FROM users ORDER BY users.id DESC;
You can reverse the existing order with ->reverseOrder()
:
<?php $select = $users->order($users->username->asc()); echo $select->reverseOrder(); // -> SELECT * FROM users ORDER BY users.username DESC;
Reverse only the order of some attributes by passing a list of attributes
to ->reverseOrder()
:
<?php $select = $users->order($users->username->asc())->order($users->id->desc()); echo $select->reverseOrder([$users->id]); // -> SELECT * FROM users ORDER BY users.username ASC, users.id ASC;
Limit & Offset
Limit and Offset correspond to the take
and skip
Operators. These take the amount of rows as their sole argument.
<?php ... echo $users->take(5); // -> SELECT * FROM users LIMIT 5 echo $users->skip(4); // -> SELECT * FROM users OFFSET 4
Distinct
The 'distinct' operator is used for SELECT DISTINCT
queries:
<?php echo $users->project($users['id'])->distinct(); // -> SELECT DISTINCT id FROM users;
Chaining
The greatest benefit of using a Query Builder is the composability of queries. Therefore calls to the Manager's methods are not bound to any order and can be chained infinitely.
For Example:
<?php ... $query = $users->project($users['id']); $query->take(1)->where($users['username']->eq("johnny"))->where($users['password']->eq('foo')); $query->project($users['username']); echo $query; // -> SELECT users.id, users.username FROM users WHERE users.username = 'johnny' AND users.password = 'foo' LIMIT 1
Insert
Get an insert query manager by calling ->insert()
on the table, or
constructing a new Sirel\InsertManager
:
<?php $insert = $users->insert(); // is equivalent to $insert = new Sirel\InsertManager; $insert->into($users);
You can set a list of column-value pairs with the ->values()
method:
<?php $insert->values([ 'username' => 'jon', 'first_name' => 'John', 'last_name' => 'Doe' ]); echo $insert->toSql(); // -> INSERT INTO users (users.username, users.first_name, users.last_name) VALUES ('jon', 'John', 'Doe');
Update
Update queries can be created with the table's ->update()
method.
Values can be set with the ->set()
method.
Update queries feature most of the methods of Select queries, which work the same as their Select counterparts:
where
take
skip
order
Note: If you create an Sirel\UpdateManager
instance, you need to set the
table with the ->table()
method.
<?php $update = $users->update(); $update->where($users->id->eq(1))->set('last_name' => 'Foobar'); echo $update->toSql(); // -> UPDATE users SET users.last_name = 'Foobar' WHERE users.id = '1';
You can also compile an Update query from an existing Select query with
->compileUpdate()
:
<?php $select = $users->where($users->first_name->eq("James")) ->where($users->last_name->eq("Kirk")) ->take(1); $update = $select->compileUpdate()->set(['first_name' => 'Jim']); echo $update->toSql(); // -> UPDATE users SET users.first_name = 'Jim' WHERE users.first_name = 'James' AND users.last_name = 'Kirk' LIMIT 1;
Delete
Delete queries can be created by calling the table's ->delete()
method.
Delete queries understand these operations, which work exactly the same as their Select counterparts:
from
where
take
skip
order
<?php $delete = $users->delete() ->take(1) ->where($users->id->eq(1)); echo $delete->toSql(); // -> DELETE FROM users WHERE users.id = '1' LIMIT 1;
You can also compile an existing Select query to a Delete query:
<?php $select = $users->where($users->activated->eq(0))->take(10); $delete = $select->compileDelete(); echo $delete->toSql(); // -> DELETE FROM users WHERE users.activated = '0' LIMIT 10;