Hi all, I have been quiet for a number of weeks now for a couple of reason namely I have been working on the conceptual design for a PDO that is Proceedural Database Objectivity for XOOPS 2.5.0
It will be working on the following concept already in play in the Persitant Objects..
See the idea of PDO is to have a control class that constructs proceedurally the SQL for XOOPS to run.. This is easily wrapped into the Persitent objects supplied orginally with 2.3.x
This means XOOPS will be able to work with Oracle, MSDE, MSSQL, Firebird, Postgress, and many other database systems.
This is the example of the control class, there is a wrapping class similar to criteria that goes around this object to generate the arrays in it.. But this at base level is what PDO will look like:
class XoopsMysqlSql extends XoopsSQLHandler {
function XoopsMysqlSql(){
}
function Select($fields = '*', $from, $where, $orderby, $having, $limit)
{
$ret = array();
if (is_array($fields))
{
foreach($fields as $field)
$ret['fields'] .= $this->FieldQuote() . $field . $this->FieldQuote() . ', ';
$ret['fields'] = substr($ret['fields'],0, strlen($ret['fields'])-2);
} elseif ($fields)
$ret['fields'] = $fields;
if (is_array($orderby))
{
foreach($orderby as $field => $order)
$ret['orderby'] .= $this->FieldQuote() . $field . $this->FieldQuote() . ' ' . (isset($order)?$order:'DESC') ', ';
$ret['orderby'] = substr($ret['orderby'],0, strlen($ret['orderby'])-2);
} elseif ($orderby)
$ret['orderby'] = $orderby;
if (is_array($limit))
{
if ($limit['limit'])
$ret['limit'] .= intval($limit['start']) . ', ' . intval($limit['limit']);
} elseif ($limit)
$ret['limit'] = $limit;
if (is_array($where))
{
foreach($where['fields'] as $field => $value) {
$jj++;
if ($jj>1)
if (is_array($value))
$ret['where'] .= (isset($where['type'][$field])?$where['type'][$field]:'AND ') . $this->FieldQuote() . $field . $this->FieldQuote() . ' ' . (isset($where['comparison'][$field])?$where['comparison'][$field]:'IN') . ' ('' implode($this->Quote() . ', ' . $this->Quote(), $value).'')' . ' ';
else
$ret['where'] .= (isset($where['type'][$field])?$where['type'][$field]:'AND ') . $this->FieldQuote() . $field . $this->FieldQuote() . ' ' . (isset($where['comparison'][$field])?$where['comparison'][$field]:'=') . ' ' . $this->Quote() . $value . $this->Quote() . ' ';
else
if (is_array($value))
$ret['where'] .= $this->FieldQuote() . $field . $this->FieldQuote() . ' ' . (isset($where['comparison'][$field])?$where['comparison'][$field]:'IN') . ' ('' implode($this->Quote() . ', ' . $this->Quote(), $value).'')' . ' ';
else
$ret['where'] .= $this->FieldQuote() . $field . $this->FieldQuote() . ' = ' . $this->Quote() . $value . $this->Quote() . ' ';
}
$ret['where'] = substr($ret['where'],0, strlen($ret['where'])-1);
} elseif ($where)
$ret['where'] = $where;
if (is_array($having))
{
foreach($having['fields'] as $field => $value) {
$jj++;
if ($jj>1)
if (is_array($value))
$ret['having'] .= (isset($having['type'][$field])?$having['type'][$field]:'AND ') . $this->FieldQuote() . $field . $this->FieldQuote() . ' ' . (isset($having['comparison'][$field])?$having['comparison'][$field]:'IN') . ' ('' implode($this->Quote() . ', ' . $this->Quote(), $value).'')' . ' ';
else
$ret['having'] .= (isset($having['type'][$field])?$having['type'][$field]:'AND ') . $this->FieldQuote() . $field . $this->FieldQuote() . ' ' . (isset($having['comparison'][$field])?$having['comparison'][$field]:'=') . ' ' . $this->Quote() . $value . $this->Quote() . ' ';
else
if (is_array($value))
$ret['having'] .= $this->FieldQuote() . $field . $this->FieldQuote() . ' ' . (isset($having['comparison'][$field])?$having['comparison'][$field]:'IN') . ' ('' implode($this->Quote() . ', ' . $this->Quote(), $value).'')' . ' ';
else
$ret['having'] .= $this->FieldQuote() . $field . $this->FieldQuote() . ' = ' . $this->Quote() . $value . $this->Quote() . ' ';
}
$ret['having'] = substr($ret['having'],0, strlen($ret['having'])-1);
} elseif ($having)
$ret['having'] = $having;
if (is_array($from))
{
if (isset($from['joins'])) {
foreach($from['joins'] as $join) {
$jin++;
foreach($join as $table => $ons) {
switch (strtolower($ons['type']) {
default:
case "left":
case "left join":
case "inner":
case "inner join:"
$join[$jin] .= $this->Innerjoin;
break;
case "right":
case "right join":
case "outer":
case "outer join:"
$join[$jin] .= $this->Outerjoin;
break;
}
$join[$jin] .= $this->FieldQuote() . $table . $this->FieldQuote() . (isset($ons['alias'])?' ' .$this->As($ons['alias']):'') . ' ON ';
foreach($ons as $var_a => $var_b) {
$join[$jin] .= $var_a ' = ' $var_b . ', '
}
$join[$jin] = substr($join[$jin],0, strlen($join[$jin])-2) . ' ';
}
}
$ret['from'] .= $from['pivot'] . ' ';
foreach($join as $jin)
$ret['from'] .= $jin;
} else {
foreach($from as $table => $alias)
$ret['from'] .= $this->FieldQuote() . $table . $this->FieldQuote() . (isset($alias)?' ' .$this->As($alias):'')', ';
$ret['from'] = substr($ret['from'],0, strlen($ret['from'])-2) . ' ';
}
} elseif ($from)
$ret['from'] = $from;
if (isset($ret['fields'])
$ret['sql'] .= $this->Select($ret['fields']);
else
$ret['sql'] .= $this->Select('*')
if (isset($ret['from'])
$ret['sql'] .= $this->From($ret['from']);
if (isset($ret['where'])
$ret['sql'] .= $this->Where($ret['where']);
if (isset($ret['orderby'])
$ret['sql'] .= $this->Orderby($ret['orderby']);
if (isset($ret['having'])
$ret['sql'] .= $this->Having($ret['having']);
if (isset($ret['limit'])
$ret['sql'] .= $this->Limit($ret['limit']);
return $ret['sql'];
}
function Update($table, $set, $where)
{
$ret = array();
if (is_array($where))
{
foreach($where['fields'] as $field => $value) {
$jj++;
if ($jj>1)
if (is_array($value))
$ret['where'] .= (isset($where['type'][$field])?$where['type'][$field]:'AND ') . $this->FieldQuote() . $field . $this->FieldQuote() . ' ' . (isset($where['comparison'][$field])?$where['comparison'][$field]:'IN') . ' ('' implode($this->Quote() . ', ' . $this->Quote(), $value).'')' . ' ';
else
$ret['where'] .= (isset($where['type'][$field])?$where['type'][$field]:'AND ') . $this->FieldQuote() . $field . $this->FieldQuote() . ' ' . (isset($where['comparison'][$field])?$where['comparison'][$field]:'=') . ' ' . $this->Quote() . $value . $this->Quote() . ' ';
else
if (is_array($value))
$ret['where'] .= $this->FieldQuote() . $field . $this->FieldQuote() . ' ' . (isset($where['comparison'][$field])?$where['comparison'][$field]:'IN') . ' ('' implode($this->Quote() . ', ' . $this->Quote(), $value).'')' . ' ';
else
$ret['where'] .= $this->FieldQuote() . $field . $this->FieldQuote() . ' = ' . $this->Quote() . $value . $this->Quote() . ' ';
}
$ret['where'] = substr($ret['where'],0, strlen($ret['where'])-1);
} elseif ($where)
$ret['where'] = $where;
if (is_array($set))
{
foreach($set as $field => $value)
$ret['set'] .= $this->FieldQuote() . $field . $this->FieldQuote() . ' = ' . $this->Quote() . $value . $this->Quote() . ', ';
$ret['set'] = substr($ret['set'],0, strlen($ret['set'])-2);
} elseif ($set)
$ret['set'] = $set;
$ret['sql'] = $this->Update . $this->FieldQuote() . $table . $this->FieldQuote();
if (isset($ret['set'])
$ret['sql'] .= $this->Set($ret['set']);
if (isset($ret['where'])
$ret['sql'] .= $this->Where($ret['where']);
return $ret['sql'];
}
function FieldQuote()
{
return '`';
}
function Quote()
{
return ''';
}
function As($name) {
return "AS " . $this->FieldQuote() . $name . $this->FieldQuote();
}
function Innerjoin()
{
return "INNER JOIN ";
}
function Outerjoin()
{
return "OUTER JOIN ";
}
function Insert()
{
return 'INSERT ';
}
function Alter()
{
return 'ALTER ';
}
function Drop($table)
{
if (strlen($table)>0)
return 'DROP ' . $table;
}
function Truncate($table)
{
if (strlen($table)>0)
return 'TRUNCATE ' . $table;
}
function From($clause)
{
if (strlen($clause)>0)
return 'FROM ' . $clause;
}
function Where($clause)
{
if (strlen($clause)>0)
return 'WHERE ' . $clause;
}
function OrderBy($order = '')
{
if (strlen($order)>0)
return 'ORDER BY ' . $order;
}
function Limit($limit)
{
if (strlen($limit)>0)
return 'LIMIT ' . $limit;
}
function OrderBy($clause)
{
if (strlen($clause)>0)
return 'ORDER BY '.$clause;
}
function Set($clause)
{
if (strlen($clause)>0)
return 'SET '.$clause;
}
}
This class alone is still not complete and will probably change it naming convention. But essentially you can see how to generate a SELECT statement you call the PDO function
XoopsMysqlSql::Select
I have yet to start the interface objectivity between this class and the criteria object where I will also be making some changes possibly so we can take advantage of Joins, Having etc.
For example MySQL has quiet an extended SQL as well, I am going to be making some method to use commands like COMPRESS, DECOMPRESS, AES_ENCRYPT, AES_DECRYPT etc as well as things like MD5 and so on. This will probably be an extension of SQLFactory that interacts with the xoopsObject:Init but i am unsure if this is the best method of this I will wait till I am fitting it.
IN estimation this will probably take a few more weeks to perfect and have completely researched as I have to go through all online documentation for Field Types and Command to write similar classes for those type. But as trabis promised some form of multiple database support may be implemented in 2.5.0
