Sto provando a progettare un builder SQL in PHP. Sto cercando di farlo in modo che il sistema possa funzionare con diversi sistemi di database (ad esempio Oracle, MySQL, MSSQL). Ecco cosa ho ottenuto finora.
In primo luogo, ho un'interfaccia denominata ISelectQuery
che definisce un contratto a cui tutti i costruttori di query di selezione devono aderire:
interface ISelectQuery{
public function addTables($tables);
public function addColumns($columns);
public function addWhere($column,$operator,$value,$bind = null);
public function addOrder($col,$dir);
public function validate();
public function toString();
public function getTables();
public function getColumns();
public function getWhere();
public function getOrder();
public function getBinds();
}
Successivamente, ho una classe denominata OracleSelectQuery
che è il mio generatore di query per Oracle. In futuro potrebbe esserci anche MysqlSelectQuery
.
class OracleSelectQuery implements ISelectQuery{
const EQUALS = '=';
const NOTEQUALS = '!=';
const LIKE = 'LIKE';
const ASC = 'ASC';
const DESC = 'DESC';
private $tables = array();
private $columns = array();
private $where = array();
private $order = array();
private $binds = array();
//add an array of tables to the query
public function addTables($tables){
$this->tables = array_merge($this->tables, $tables);
return $this;
}
//add an array of columns to the query
public function addColumns($columns){
$this->columns = array_merge($this->columns, $columns);
return $this;
}
//add a where clause to the query
public function addWhere($column,$operator,$value,$bind = null){
$where = array($column,$operator,$value,$bind);
$this->where[] = $where;
return $this;
}
//add some ordering to the query
public function addOrder($col,$dir){
$order = array($col,$dir);
$this->order[] = $order;
return $this;
}
public function validate(){
//can't be a valid query if tables aren't defined
if(empty($this->tables)){
throw new Exception('Tables not defined.');
}
//can't be a valid query if columns aren't defined
if(empty($this->columns)){
throw new Exception('Columns not defined.');
}
//search through each where clause to see if they are valid
foreach($this->where as $where){
if(!in_array($where[1],array($this::EQUALS, $this::NOTEQUALS, $this::LIKE))){
throw new Exception($where[1].' is not a valid WHERE clause');
}
}
//search through each order by clause to see if they are valid
foreach($this->order as $order){
if(!in_array($order[1],array($this::ASC, $this::DESC))){
throw new Exception($order[1].' is not a valid ORDER BY clause');
}
}
return $this;
}
public function toString(){
//add the columns to the query
$sql = 'select '.implode(', ',$this->columns).' from ';
//add the tables
$sql .= implode(', ',$this->tables).' ';
//add the where clauses (if where clauses were added)
if(!empty($this->where)){
$sql.= 'where ';
//loop through each where clause
foreach($this->where as $where){
//where[0] will be the column, where[1] will be the comparison
$sql.= $where[0].' '.$where[1].' ';
//if where[2] has a '?' in it, it must be a bind
if(strstr($where[2],'?') !== false){
//give the bind variable a unique name, by counting the number of binds already created
$bindName = ':bind'.count($this->binds);
//replace the '?' with the bind variable name
$sql .=' '.str_replace('?',$bindName,$where[2]);
//add the bind variable name and value to the list of binds - this can be used by the database connection
$this->binds[] = array($bindName,$where[3]);
}else{
//if it's not a bind variable, add the simle value
$sql .= $where[2];
}
//don't forget to add the and at the end
$sql .= ' and ';
}
//remove the last ' and '
$sql = substr($sql,0,-5);
}
//add the order clauses (if they exist)
if(!empty($this->order)){
$sql .= ' order by ';
//loop through each order clause and add it to the sql
foreach($this->order as $order){
$sql .= $order[0].' '.$order[1].', ';
}
//remove the last comma
$sql = substr($sql,0,-2);
}
//return the finished query to the client
return $sql;
}
//getters to allow client to inspect query later
public function getTables(){
return $this->tables;
}
public function getColumns(){
return $this->columns;
}
public function getWhere(){
return $this->where;
}
public function getOrder(){
return $this->order;
}
public function getBinds(){
return $this->binds;
}
}
È quindi possibile utilizzare il codice seguente per generare SQL:
//create a new select query
$select = new OracleSelectQuery();
$sql = $select->addTables(array('table1','table2'))
->addColumns(array('col1','col2'))
->addWhere('col1',OracleSelectQuery::LIKE,'?','value')
->addOrder('col1',OracleSelectQuery::ASC)
->validate()
->toString();
echo $sql;
La mia domanda è - questa è una buona implementazione del modello di builder? C'è un modo per migliorarlo?