/**
* Converts a XML description of a SQL index into a full SQL type
*
* <index name="PRIMARY" type="primary">
* <column name="id" />
* </index>
* <index name="rate_chars">
* <column name="rate" />
* <column name="_mychars" nametype="namesuffix" size="8" ordering="DESC" />
* </index>
* <index name="myrate" type="unique" using="btree">
* <column name="rate" />
* </index>
*
* Returns: $fulltype: 'decimal(16,8) unsigned NULL DEFAULT NULL'
*
* @param SimpleXMLElement $index
* @param string $colNamePrefix Prefix to add to all column names
* @return string|boolean Full SQL creation type or NULL in case of no index/error
*/
protected function fullIndexType(SimpleXMLElement $index, $colNamePrefix)
{
$sqlIndexText = null;
if ($index->getName() == 'index') {
// first collect all columns of this index:
$indexColumns = array();
foreach ($index->children() as $column) {
if ($column->getName() == 'column') {
$colNamePrefixed = $this->prefixedName($column, $colNamePrefix);
$indexColText = $this->_db->NameQuote($colNamePrefixed);
if ($column->attributes('size')) {
$indexColText .= ' (' . (int) $column->attributes('size') . ')';
}
if ($column->attributes('ordering')) {
$indexColText .= ' ' . $this->_db->getEscaped($column->attributes('ordering'));
}
$indexColumns[] = $indexColText;
}
}
if (count($indexColumns) > 0) {
// then build the index creation SQL:
if ($index->attributes('type')) {
// PRIMARY, UNIQUE, FULLTEXT, SPATIAL:
$sqlIndexText .= $this->_db->getEscaped(strtoupper($index->attributes('type'))) . ' ';
}
$sqlIndexText .= 'KEY ';
if ($index->attributes('type') !== 'primary') {
$sqlIndexText .= $this->_db->NameQuote($this->prefixedName($index, $colNamePrefix)) . ' ';
}
if ($index->attributes('using')) {
// BTREE, HASH, RTREE:
$sqlIndexText .= 'USING ' . $this->_db->getEscaped($index->attributes('using')) . ' ';
}
$sqlIndexText .= '(' . implode(', ', $indexColumns) . ')';
}
}
return $sqlIndexText;
}
/**
* Treats recursively a <data> or <field> node and its children
*
* @access private
* @param SimpleXmlElement $data
* @return array|null|string
*/
protected function _composeSQLformula($data)
{
$this->_levelPush();
$dType = $data->attributes('type');
$formula = null;
// should not be used here
$moreFormulas = null;
if (!$this->_reverse) {
if ($dType == 'sql:field') {
$formula = $this->process_sql_field($data);
} elseif ($dType == 'sql:count') {
$formula = $this->process_sql_count($data);
}
}
$subFormula = array();
if (!$this->_reverse || $data->attributes('table') != $this->_table) {
//TBD LATER: shouldn't this be: || ( $data->attributes( 'table' ) && ( $data->attributes( 'table' ) != $this->_table ) ) ) {
if ($this->_reverse && $data->attributes('table') != $this->_table) {
$this->joinsNeededForCount = true;
}
/** @var $child SimpleXmlElement */
foreach ($data->children() as $child) {
switch ($child->getName()) {
case 'data':
// recurse to process bottom-up
if ($child->attributes('type') == 'sql:subquery') {
$childrenFormulas = $this->process_subquery($child, true);
} else {
$childrenFormulas = $this->_composeSQLformula($child);
}
if (is_array($childrenFormulas)) {
$childrenFormulas = implode(', ', $childrenFormulas);
}
$subFormula[] = $childrenFormulas;
if (!$this->_reverse && $child->attributes('select') == 'true') {
$moreFormulas .= ', ' . $childrenFormulas;
}
break;
case 'where':
$this->_levelPush();
$this->process_where($child);
$this->_levelPop();
break;
default:
break;
}
}
}
switch ($dType) {
case 'sql:count':
// count of related records in other table:
// if ( $this->_reverse ) {
// $formula = $this->process_sql_count( $data );
// }
break;
case 'sql:field':
// field value taken from related record in other table:
if ($this->_reverse) {
$formula = $this->process_reverse_sql_field($data, $subFormula);
}
break;
case 'sql:multiplerows':
// multiple field values taken from related record in other table:
// if ( $this->_reverse ) {
// $formula = $this->process_multiplerows( $data );
// }
break;
case 'sql:operator':
// any SQL operator between fields ( +, -, *, /, ...)
$cnt_name = $this->_db->getEscaped($data->attributes('name'));
$operator = $data->attributes('operator');
$formula = '( ' . implode(' ' . $operator . ' ', $subFormula) . ' )' . ($cnt_name ? ' AS `' . $cnt_name . '`' : '');
break;
case 'sql:function':
// any SQL function of fields ( SUM( f1, f2 ), AVG(...) )
$cnt_name = $this->_db->getEscaped($data->attributes('name'));
$operator = $data->attributes('operator');
$formula = $operator . '( ' . implode(', ', $subFormula) . ' ) ' . ($cnt_name ? 'AS `' . $cnt_name . '`' : '');
if ($operator == 'GROUP_CONCAT') {
// Normally GROUP_CONCAT is only 1 kB, increase it to maximum value:
$this->increaseGroupConcatMaxLen();
}
break;
case 'sql:formula':
// any SQL formula of fields ( GROUP_CONCAT( f1 f2 f3 ), SUM(...) )
$cnt_name = $this->_db->getEscaped($data->attributes('name'));
$operator = $data->attributes('operator');
$formula = $operator . '( ' . implode(' ', $subFormula) . ' ) ' . ($cnt_name ? 'AS `' . $cnt_name . '`' : '');
if ($operator == 'GROUP_CONCAT') {
// Normally GROUP_CONCAT is only 1 kB, increase it to maximum value:
$this->increaseGroupConcatMaxLen();
}
break;
case null:
// top-level probably, no type...
$formula = $subFormula;
// if not at top level, it will get imploded at level above.
break;
case 'param':
// a plugin parameter of type string
// a plugin parameter of type string
case 'param:string':
// a plugin parameter of type string
// a plugin parameter of type string
case 'param:int':
// a plugin parameter of type int
// a plugin parameter of type int
case 'param:float':
// a plugin parameter of type float
// a plugin parameter of type float
case 'param:datetime':
// a plugin parameter of type datetime
$formula = $this->process_param($data);
break;
default:
// 'const:string', 'const:int', 'const:float' and much more:
$name = $data->attributes('name');
if ($data->attributes('translate') == 'yes') {
$name = CBTxt::T($name);
}
$formula = $this->sqlCleanQuote($name, $dType);
if ($formula === false) {
trigger_error('SQLXML::_composeSQLformula: data type ' . $dType . ' is not implemented !', E_USER_NOTICE);
}
break;
}
$this->_levelPop();
if (is_array($formula)) {
if ($moreFormulas) {
$formula[] = $moreFormulas;
}
} else {
$formula .= $moreFormulas;
}
return $formula;
}