Viewing file:  table.php (30.14 KB) -rw-r--r--
Select action/file-type:
 (+) |  (+) |  (+) | Code (+) | Session (+) |  (+) | SDB (+) |  (+) |  (+) |  (+) |  (+) |  (+) |
<?php
/**
* Abstract class, contains method common for both
* DataSourceProjectTable and DataSourceDBTable classes
*/
class DataSourceTable extends DataSource {
protected $cipherer;
function __construct( $name, $connection ) {
parent::__construct( $name );
$this->connection = $connection;
$this->cipherer = new RunnerCipherer( $name );
}
public function prepareSQL( $dc ) {
$this->buildWhereHaving( $dc );
$order = $this->getOrderClause( $dc );
$sql = $this->buildSQL( $dc, false );
return array(
"sql" => $sql,
"where" => $dc->_cache["where"],
"order" => $order
);
}
public function overrideSQL( $dc, $sql ) {
$dc->_cache["sql"] = $sql;
}
public function overrideWhere( $dc, $where, $having = "" ) {
// unset "sql" to build a new value with a correct WHERE clause
$dc->_cache["sql"] = NULL;
$dc->_cache["where"] = $where;
if( $dc->_cache["having"] )
$dc->_cache["having"] = $where;
}
public function overrideOrder( $dc, $orderby ) {
$dc->_cache["order"] = $orderby;
}
protected function getKeyFields() {
return array();
}
protected function buildWhereHaving( $dc ) {
if( isset( $dc->_cache["where"] ) ) {
return;
}
$filter = $dc->filter;
// when there are $dc->keys, just add them to the WHERE expression
if( $dc->keys ) {
$filter = DataCondition::_And( array(
$dc->filter,
DataCondition::FieldsEqual( $this->getKeyFields(), $dc->keys )
) );
}
$filters = $this->splitFilterWhereHaving( $dc, $filter );
$context = new DsFilterBuildContext;
$dc->_cache["where"] = $this->conditionToSQL( $filters["where"], $context );
$dc->_cache["having"] = $this->conditionToSQL( $filters["having"], $context );
$dc->_cache["context"] = $context;
}
protected function getWhereClause( $dc ) {
$this->buildWhereHaving( $dc );
return $dc->_cache["where"];
}
protected function getHavingClause( $dc ) {
$this->buildWhereHaving( $dc );
return $dc->_cache["having"];
}
protected function getOrderClause( $dc ) {
if( isset( $dc->_cache["order"] ) ) {
return $dc->_cache["order"];
}
$orderby = array();
foreach( $dc->order as $of ) {
if( $of["index"] ) {
$orderby[] = $of["index"] . " " . $of["dir"];
} else if( $of["column"] ) {
$extraColumn = $dc->findExtraColumn( $of["column"] );
if( $extraColumn ) {
$orderby[] = $this->connection->addFieldWrappers( $extraColumn->alias ) . " " . $of["dir"];
} else {
$orderby[] = $this->fieldExpression( $of["column"] ) . " " . $of["dir"];
}
} else {
$orderby[] = $of["expr"] . " " . $of["dir"];
}
}
$orderString = $orderby ? "ORDER BY " . implode( ", ", $orderby ) : "";
$dc->_cache["orderby"] = $orderString;
return $dc->_cache["orderby"];
}
protected function getGroupByFieldList() {
return array();
}
/**
* Split $filter into two conditions, one that will go into WHERE, another - into HAVING SQL clauses.
* select a, count(*) as b from c
* When search is specified, the query shoudl look like:
* select a, count(*) as b from c where a like 'a' having b like 'b'
*/
protected function splitFilterWhereHaving( $dc, $filter ) {
$ret = array(
"where" => DataCondition::_And( array() ),
"having" => DataCondition::_And( array() )
);
$grouppedFields = $this->getGroupByFieldList();
if( !$grouppedFields ) {
$ret["where"] = $filter;
return $ret;
}
$this->flattenANDs( $filter );
if( $filter->type !== dsopAND ) {
$ret["where"] = $filter;
return $ret;
}
foreach( $filter->operands as $oper ) {
if( $this->findField( $oper->value, $grouppedFields ) ) {
$ret["having"]->operands[] = $oper;
} else {
$ret["where"]->operands[] = $oper;
}
}
return $ret;
}
/**
* check if there is any field from the list in the condition
* @param DsCondition
* @param Array of strings
* @return Boolean
*/
protected function findField( $condition, $fields ) {
if( !$condition ) {
return false;
}
foreach( $condition->operands as $op ) {
if( $op->type === dsotFIELD ) {
if( array_search( $op->value, $fields ) !== false ) {
return true;
}
} else if( $op->type === dsotCONDITION ) {
if( $this->findField( $op->value, $fields ) ) {
return true;
}
}
}
return false;
}
protected function conditionToSQL( $dCondition, $context ) {
if( !$dCondition ) {
return "";
}
$op = $dCondition->operation;
if( $op == dsopAND || $op == dsopOR ) {
$sqlConditions = array();
foreach( $dCondition->operands as $o ) {
$sql = trim( $this->conditionToSQL( $o->value, $context ) );
if( $sql !== "" ) {
$sqlConditions[] = "( " . $sql . " )";
}
}
return implode( $op == dsopAND ? ' and ' : ' or ', $sqlConditions );
}
else if( $op == dsopSQL ) {
return trim( $dCondition->operands[ 0 ]->value );
}
else if( $op == dsopNOT ) {
$sql = trim( $this->conditionToSQL( $dCondition->operands[ 0 ]->value, $context ) );
if( $sql ) {
return "NOT ( " . $sql . " )";
}
return "";
}
else if( DataSource::basicFieldCondition( $op ) ) {
return $this->basicFieldConditionSQL( $dCondition, $context );
}
return "";
}
/**
* This function must be overridden
*/
protected function getFieldType( $field ) {
trigger_error("Unsupported datasource", E_USER_ERROR );
return 200;
}
/**
* @return Boolean
* field is encrypted with code-based cipher
*/
protected function valueNeedsEncrypted( $field ) {
return false;
}
// basic operations: < > = like isnull etc
protected function basicFieldConditionSQL( $dCondition, $context )
{
// take care of the field operand first
$operandType = $dCondition->operands[0]->type;
$field = $dCondition->operands[0]->value;
$modifier = $dCondition->operands[0]->modifier;
$fieldType = $this->getFieldType( $field );
// encryption stuff
// either field must be decrypted or value operand encrypted
$encryptValue = $this->valueNeedsEncrypted( $field );
if( $operandType == dsotFIELD )
$fieldExpr = $this->fieldExpression( $field, $modifier );
else if( $operandType == dsotSQL )
$fieldExpr = $field;
// process $field->joinData, apply criteria to a field from another table, like searching by Display field
if( !$encryptValue && $dCondition->operands[0]->joinData ) {
$newExpression = $this->createJoinedExpression( $field, $fieldExpr, $dCondition->operands[0], $context );
$fieldExpr = $newExpression ? $newExpression : $fieldExpr;
}
// forced conversion to char
if( $dCondition->operands[0]->tochar && !IsCharType( $fieldType ) ) {
$fieldExpr = $this->connection->field2char( $fieldExpr, $fieldType );
$fieldType = 200;
}
if( IsDateFieldType( $fieldType ) && $modifier > 0 ) {
$fieldType = 3; // integer
}
if( $encryptValue ) {
$values = array();
// only EMPTY and EQUALS operations can be done on PHP-encrypted fields
for( $i = 1; $i < count( $dCondition->operands); ++$i ) {
$values[] = $this->cipherer->EncryptField( $field, $dCondition->operands[$i]->value );
}
if( $dCondition->operation == dsopBETWEEN && count( $values ) == 2 ) {
return $fieldExpr . ' = ' . $values[0] . " or " . $fieldExpr . ' = ' . $values[1];
}
if( $dCondition->operation == dsopEMPTY ) {
$encryptedEmptyString = $this->cipherer->EncryptField( $field, $dCondition->operands[$i]->value );
return $fieldExpr . " is null or "
. $fieldExpr . " = '' or "
. $fieldExpr . " = " . $this->connection->prepareString( $encryptedEmptyString );
}
// all other operations turn into "="
return $fieldExpr . " = " . $this->connection->prepareString( $values[0] );
}
$op = $dCondition->operation;
if( $op == dsopEQUAL ) {
return $this->sqlExpressionEquals( $fieldType, $fieldExpr, $dCondition->operands[ 1 ]->value, $dCondition->caseInsensitive );
} else if( $op == dsopMORE || $op == dsopLESS ) {
return $this->sqlExpressionMore( $fieldType, $fieldExpr, $dCondition->operands[ 1 ]->value, $dCondition->caseInsensitive, $op == dsopMORE );
} else if( $op == dsopEMPTY ) {
return $this->sqlExpressionEmpty( $fieldType, $fieldExpr );
} else if( $op == dsopSTART || $op == dsopCONTAIN ) {
return $this->sqlExpressionLike( $fieldType, $fieldExpr, $dCondition->operands[ 1 ]->value,
$dCondition->caseInsensitive, $op == dsopSTART, $dCondition->operands[ 1 ]->likeWrapper );
} else if( $op == dsopBETWEEN ) {
return $this->sqlExpressionBetween( $fieldType, $fieldExpr, $dCondition->operands[ 1 ]->value, $dCondition->operands[ 2 ]->value, $dCondition->caseInsensitive );
} else if( $op == dsopSOME_IN_LIST || $op == dsopALL_IN_LIST ) {
return $this->sqlExpressionInList( $fieldType, $fieldExpr, $dCondition->operands[ 1 ]->value, $op == dsopALL_IN_LIST );
} else if( $op == dsopFALSE ) {
return $this->sqlExpressionFalse();
}
return "";
}
protected function sqlExpressionFalse() {
return "1=0";
}
protected function sqlExpressionEmpty( $fieldType, $fieldExpr ) {
$ret = $fieldExpr . ' is null';
if( IsDateFieldType( $fieldType ) && $this->connection->dbType == nDATABASE_MySQL ) {
// skip "zero" dates '0000-00-00', '0000-00-00 00:00:00'
return $ret
. " or " . $fieldExpr . " = '0000-00-00'"
. " or " . $fieldExpr . " = '0000-00-00 00:00:00'";
}
if( !IsCharType( $fieldType ) || $this->connection->dbType == nDATABASE_Oracle ) {
return $ret;
}
if( IsTextType( $fieldType ) && $this->connection->dbType == nDATABASE_MSSQLServer ) {
return $ret . " or " . $fieldExpr . " like ''";
} else {
return $ret . " or " . $fieldExpr . " = ''";
}
}
protected function sqlExpressionEquals( $fieldType, $fieldExpr, $value, $caseInsensitive ) {
if( !IsCharType( $fieldType ) ) {
return $fieldExpr . "=" . $this->prepareSQLValue( $fieldType, $value );
}
if( IsTextType( $fieldType ) && $this->connection->dbType != nDATABASE_MySQL ) {
$likeWord = ( $this->connection->dbType == nDATABASE_PostgreSQL && $caseInsensitive )
? 'ilike'
: 'like';
return $fieldExpr . ' ' . $likeWord . ' ' . $this->prepareSQLValue( $fieldType, $value );
}
return $this->connection->comparisonSQL( $fieldExpr, $this->prepareSQLValue( $fieldType, $value ), $caseInsensitive );
}
protected function sqlExpressionInList( $fieldType, $fieldExpr, $values, $all ) {
if( !IsCharType( $fieldType ) )
$fieldExpr = $this->connection->field2char( $fieldExpr, $fieldType );
$conditions = array();
foreach( $values as $v ) {
$valueConds = array();
$valueConds[] = $fieldExpr . " = " . $this->connection->prepareString( $v );
$vp = $this->connection->escapeLIKEpattern( $v );
$valueConds[] = $fieldExpr . " LIKE '%," . $v . "'";
$valueConds[] = $fieldExpr . " LIKE '" . $v . ",%'";
$valueConds[] = $fieldExpr . " LIKE '%," . $v . ",%'";
$conditions[] = "( " . implode( ' OR ', $valueConds ) . " )";
}
return implode( $all ? ' AND ' : ' OR ', $conditions );
}
protected function sqlExpressionMore( $fieldType, $fieldExpr, $value, $caseInsensitive, $more ) {
$operation = $more ? ' > ' : ' < ';
$valueExpression = $this->prepareSQLValue( $fieldType, $value );
if( IsCharType( $fieldType ) && $caseInsensitive ) {
if( !IsTextType( $fieldType ) || $this->connection->dbType == nDATABASE_MySQL ) {
$fieldExpr = $this->connection->upper( $fieldExpr );
// use database uppercase instead of PHP strtoupper because of character set issues
$valueExpression = $this->connection->upper( $valueExpression );
}
}
return $fieldExpr . $operation . $valueExpression;
}
protected function sqlExpressionLike( $fieldType, $fieldExpr, $value, $caseInsensitive, $starts, $likeWrapper ) {
if( !IsCharType( $fieldType ) )
$fieldExpr = $this->connection->field2char( $fieldExpr, $fieldType );
$likeWord = ( $this->connection->dbType == nDATABASE_PostgreSQL && $caseInsensitive )
? 'ilike'
: 'like';
$pattern = $this->connection->escapeLIKEpattern( $value )."%";
if( !$starts )
$pattern = "%".$pattern;
if( $likeWrapper ) {
$before = $this->connection->escapeLIKEpattern( $likeWrapper['before'] );
$after = $this->connection->escapeLIKEpattern( $likeWrapper['after'] );
if( !$starts && $before )
$before = "%".$before;
if( $after )
$after = $after."%";
$pattern = $before. $pattern .$after;
}
// file search [{ -->_{
if( $this->connection->dbType == nDATABASE_Access )
$pattern = str_replace( "[", "_", $pattern );
$valueExpression = $this->connection->prepareString( $pattern );
if( $caseInsensitive && $this->connection->dbType != nDATABASE_PostgreSQL ) {
$fieldExpr = $this->connection->upper( $fieldExpr );
// use database uppercase instead of PHP strtoupper because of character set issues
$valueExpression = $this->connection->upper( $valueExpression );
}
return $fieldExpr . ' ' . $likeWord . ' ' . $valueExpression;
}
protected function sqlExpressionBetween( $fieldType, $fieldExpr, $value1, $value2, $caseInsensitive ) {
$valueExpression1 = $this->prepareSQLValue( $fieldType, $value1 );
$valueExpression2 = $this->prepareSQLValue( $fieldType, $value2 );
if( IsCharType( $fieldType ) && $caseInsensitive ) {
if( !IsTextType( $fieldType ) || $this->connection->dbType == nDATABASE_MySQL ) {
$fieldExpr = $this->connection->upper( $fieldExpr );
// use database uppercase instead of PHP strtoupper because of character set issues
$valueExpression1 = $this->connection->upper( $valueExpression1 );
$valueExpression2 = $this->connection->upper( $valueExpression2 );
}
}
return $fieldExpr . ' BETWEEN ' . $valueExpression1 . ' AND ' . $valueExpression2;
}
/**
* add proper quotes, escape, convert to number etc
*/
protected function prepareSQLValue( $type, $value ) {
if( IsNumberType( $type ) ) {
return 0 + $value;
}
if( IsDateFieldType( $type ) ) {
// copied from add_db_quotes
global $locale_info;
$y = "(\d\d\d\d)";
$m = "(0?[1-9]|1[0-2])";
$d = "(0?[1-9]|[1-2][0-9]|3[0-1])";
$delim = "(-|".preg_quote($locale_info["LOCALE_SDATE"], "/").")";
$reg = "/".$d.$delim.$m.$delim.$y."|".$m.$delim.$d.$delim.$y."|".$y.$delim.$m.$delim.$d."/";
if( !preg_match($reg, $value, $matches) )
return "null";
return $this->connection->addDateQuotes( $value );
}
if( IsBinaryType($type) )
return $this->connection->addSlashesBinary( $value );
return $this->connection->prepareString( $value );
}
/**
* return record count
* @return number
*/
public function getCount( $dc ) {
$sql = $this->buildSQL( $dc, false );
return $this->connection->getFetchedRowsNumber( $sql );
}
/**
* returns recordset or array
*/
public function getList( $dc ) {
$sql = $this->buildSQL( $dc, true );
return $this->connection->limitedQuery( $sql, $dc->startRecord, $dc->reccount, true );
}
/**
* @return Array
* return array(
* "head" => $this->HeadToSql( $oneRecordMode ),
* "from" => $this->FromToSql(),
* "where" => $this->WhereToSql(),
* "groupby" => $this->GroupByToSql(),
* "having" => $this->Having()->toSql($this)
* );
*
*/
protected function getSQLComponents() {
trigger_error("Unsupported datasource", E_USER_ERROR );
return array();
}
/**
* @param DsCommand dc
* @param Boolean addOrder
* @param String replaceFieldList - when not empty replace everything between SELECT and FROM
* @return String
*/
protected function buildSQL( $dc, $addOrder, $replaceFieldList = "" ) {
if( !isset( $dc->_cache["sql"] ) ) {
$sqlParts = $this->getSQLComponents();
$whereClause = $this->getWhereClause( $dc );
$havingClause = $this->getHavingClause( $dc );
// add joins
$context = $dc->_cache["context"];
if( $context->joins ) {
$sqlParts["from"] .= ' ' . implode( ' ', $context->joins );
}
if( $replaceFieldList ) {
$sqlParts["head"] = "SELECT " . $replaceFieldList;
}
// add extra columns
if( $dc->extraColumns ) {
$extras = array();
foreach( $dc->extraColumns as $ec ) {
$extras[] = $this->extraColumnExpression( $ec );
}
$sqlParts["head"] .= ', ' . implode( ', ', $extras );
}
// extra columns from context
if( $context->extraColumnSQL ) {
$sqlParts["head"] .= ', ' . implode( ', ', $context->extraColumnSQL );
}
$dc->_cache["sql"] = SQLQuery::buildSQL(
$sqlParts,
array( $whereClause ),
array( $havingClause )
);
}
$sql = $dc->_cache["sql"];
if( $addOrder ) {
$sql .= $this->getOrderClause( $dc );
}
return $sql;
}
/**
* SQL expression for extra column
* @param DsFieldData
* @return String
*/
protected function extraColumnExpression( $column ) {
$expression = $column->sql;
if( !$expression ) {
$expression = $this->fieldExpression( $column->field, $column->modifier );
}
if( $column->alias ) {
return $expression . ' AS ' . $this->connection->addFieldWrappers( $column->alias );
} else {
return $expression;
}
}
/**
* Returns expression to use in SQL query
* It is overridden in DataSourceProjectTable to accomodate for encryption
*/
protected function fieldExpression( $field, $modifier = 0 ) {
$fieldExpr = $this->connection->addFieldWrappers( $field );
return $this->applyFieldModifier( $fieldExpr, $this->getFieldType($field), $modifier );
}
/**
* returns number of records in request with totals
* See DsCommand::totals description
* @return Integer
*/
public function getTotalCount( $dc ) {
$sql = "SELECT COUNT(*) from ( " . $this->getTotalsSQL( $dc, true ) . " ) a";
$result = $this->connection->query( $sql );
if( !$result ) {
return 0;
}
$data = $result->fetchNumeric();
if( !$data ) {
return 0;
}
return $data[0];
}
/**
* @param DsCommand
* @param Boolean
*/
protected function getTotalsSQL( $dc, $addOrder ) {
$baseSQL = $this->buildSQL( $dc, false );
$selectList = array();
$groupList = array();
$whereList = array();
$orderList = array();
foreach( $dc->totals as $idx => $t ) {
$f = $this->sqlTotalField( $dc, $t );
if( $f === false ) {
// don't do the query
return false;
}
$aliasClause = $t["alias"]
? (' AS ' . $this->connection->addFieldWrappers( $t["alias"] ) )
: "";
$selectList[] = $f . $aliasClause;
if( $t["direction"] ) {
$orderList[] = ($idx + 1) . ( $t["direction"] == "ASC" ? " ASC" : " DESC" );
}
if( !$t["total"] ) {
$groupList[] = $f;
}
if( $t["skipEmpty"] ) {
$whereList[] = "( NOT ( " . $this->sqlExpressionEmpty(
$this->getFieldType( $t["field"] ),
$this->connection->addFieldWrappers( $t["field"] )
) . " ) )";
}
}
$groupbyClause = implode( ',', $groupList );
if( count( $groupList ) ) {
$groupbyClause = " GROUP BY " . $groupbyClause;
}
$whereClause = implode( ' AND ', $whereList );
if( count( $whereList ) ) {
$whereClause = " WHERE " . $whereClause;
}
$distinct = $this->totalDistinct( $dc )
? "DISTINCT "
: "";
$sql = "SELECT " . $distinct . implode( ',', $selectList ) . ' FROM ( ' . $baseSQL . ' ) a ' . $whereClause . $groupbyClause;
if( $addOrder ) {
$order = implode( ", ", $orderList );
if( $order ) {
$sql .= " ORDER BY " . $order;
}
}
return $sql;
}
/**
* returns result with group totals
* See DsCommand::totals description
*/
public function getTotals( $dc ) {
$sql = $this->getTotalsSQL( $dc, true );
if( !$sql ) {
return new ArrayResult( array() );
}
return $this->connection->limitedQuery( $sql, $dc->startRecord, $dc->reccount, true );
}
/**
* Whether the column is COUNT, SUM, MIN, MAX, AVG in the original SQL query
* @return Boolean
*/
protected function isAggregateField( $field ) {
return false;
}
/**
* Check if the total query should include DISTINCT
*/
protected function totalDistinct( $dc ) {
foreach( $dc->totals as $total ) {
// all fields must be "distinct"
if( $total["total"] != "distinct" )
return false;
// some field types in some databases don't support DISTINCT
$fType = $this->getFieldType( $total["field"] );
if( IsTextType( $fType ) &&
( $this->connection->dbType == nDATABASE_MSSQLServer
|| $this->connection->dbType == nDATABASE_Access
|| $this->connection->dbType == nDATABASE_Oracle )
) {
return false;
}
// don't do search suggest for aggregated fields
if( $dc->skipAggregated && $total["total"] == "distinct" && $this->isAggregateField( $total[ "field" ] ) ) {
return false;
}
}
return true;
}
protected function sqlTotalField( $dc, $total ) {
if( $dc->skipAggregated && $total["total"] == "distinct" && $this->isAggregateField( $total[ "field" ] ) ) {
// don't do search suggest for aggregated fields
return false;
}
if( $total["time"] ) {
$expr = $this->connection->timeToSecWrapper( $total["field"] );
} else {
/**
* $total["field"] comes from subquery. $his->fieldExpression is not needed here
*/
$expr = $this->connection->addFieldWrappers( $total["field"] );
}
if( $total["total"] == "distinct" ) {
// substitute original field with the joined display field
$context = $dc->_cache["context"];
if( $context ) {
if( isset( $context->joinedAliases[ $total[ "field" ] ] ) ) {
$expr = $this->connection->addFieldWrappers( $context->joinedAliases[ $total[ "field" ] ] );
}
}
return $expr;
}
if( $total["total"] ) {
if( $total["total"] == 'count' && !$total["field"] ) {
$expr = '1';
}
if( $total["caseStatement"] ) {
$expr = $this->getCaseStatement( $total["caseStatement"] );
}
return strtoupper( $total["total"] ) . "( " . $expr . " )";
}
$ftype = $this->getFieldType( $total["field"] );
return $this->applyFieldModifier( $expr, $ftype, $total["modifier"] );
}
/**
* Build SQL for DsOperand used in totals
* @param DsOperand
* @return String
*/
protected function totalOperandSQL( $op ) {
if( $op ) {
if( $op->type == dsotFIELD )
return $this->connection->addFieldWrappers( $op->value );
else if( $op->type == dsotCONST )
return $op->value;
}
return 'NULL';
}
/**
* Build SQL CASE expression.
* @param DsCaseExpression
* @return String
*/
protected function getCaseStatement( $caseExpr ) {
$sqlCases = array();
$tail = "";
$context = new DsFilterBuildContext;
foreach( $caseExpr->conditions as $idx => $condition ) {
$sqlCondition = $this->conditionToSQL( $condition, $context );
$sqlValue = $this->totalOperandSQL( $caseExpr->values[ $idx ] );
if( $this->connection->dbType == nDATABASE_Access ) {
$sqlCases[] = "IIF( " . $sqlCondition . ", " . $sqlValue . ",";
$tail .= ")";
} else {
$sqlCases[] = "WHEN " . $sqlCondition . " THEN " . $sqlValue;
}
}
$sqlDefault = $this->totalOperandSQL( $caseExpr->defValue );
if( $this->connection->dbType == nDATABASE_Access ) {
return implode( ' ', $sqlCases ) . ' ' . $sqlDefault . $tail;
} else {
return "( CASE " . implode( ' ', $sqlCases ) . ' ELSE ' . $sqlDefault . ' END )';
}
}
protected function applyFieldModifier( $expr, $ftype, $modifier ) {
if( IsNumberType($ftype) ) {
$expr = $this->connection->intervalExpressionNumber( $expr, $modifier );
} else if( IsCharType( $ftype ) ) {
$expr = $this->connection->intervalExpressionString( $expr, $modifier );
} else if( IsDateFieldType( $ftype ) ) {
$expr = $this->connection->intervalExpressionDate( $expr, $modifier );
}
return $expr;
}
public function delete( $keys ) {
}
public function getSingle( $dc ) {
$sql = $this->buildSQL( $dc, true );
return $this->connection->limitedQuery( $sql, 0, 1, true );
}
protected function dbTableName() {
return $this->_name;
}
public function getNextPrevKeys($dc, &$data, $what = BOTH_RECORDS)
{
$nextPrevComponents = $this->getNextPrevComponents($dc, $data);
if (!$nextPrevComponents)
return array();
$baseSQL = $this->buildSQL($dc, false);
$keysFields = array();
$next = array();
$prev = array();
foreach ($this->getKeyFields() as $i => $k) {
$keysFields[] = $this->connection->addFieldWrappers($k);
}
$sql = "select " . implode(',', $keysFields) . " from ( " . $baseSQL . " ) a ";
// next record
if ($what == BOTH_RECORDS || $what == NEXT_RECORD) {
$strSQL = $sql;
if (strlen($nextPrevComponents["nextWhere"]))
$strSQL .= "where " . $nextPrevComponents["nextWhere"];
if (strlen($nextPrevComponents["nextOrder"]))
$strSQL .= "order by " . $nextPrevComponents["nextOrder"];
$rs = $this->connection->limitedQuery($strSQL, 0, 1, true);
if ($rs)
$next = $rs->fetchNumeric();
}
// prev record
if ($what == BOTH_RECORDS || $what == PREV_RECORD) {
$strSQL = $sql;
if (strlen($nextPrevComponents["prevWhere"]))
$strSQL .= "where " . $nextPrevComponents["prevWhere"];
if (strlen($nextPrevComponents["prevOrder"]))
$strSQL .= "order by " . $nextPrevComponents["prevOrder"];
$rs = $this->connection->limitedQuery($strSQL, 0, 1, true);
if ($rs)
$prev = $rs->fetchNumeric();
}
return array("next" => $next, "prev" => $prev);
}
protected function getNextPrevComponents($dc, &$data)
{
if ( !$dc->order )
return array();
$nextWhere = null;
$prevWhere = null;
$nextOrder = array();
$prevOrder = array();
for ($i = count($dc->order) - 1; $i >= 0; --$i) {
$of = $dc->order[$i];
$column = $of["column"];
$sqlColumn = $this->connection->addFieldWrappers($column);
$nextOrder[] = $sqlColumn . ' ' . $of["dir"];
$prevOrder[] = $sqlColumn . ' ' . ($of["dir"] == "ASC" ? "DESC" : "ASC");
// Build this sort of exporessions:
// field1 > x or field1 = x and ( field2 > y or field2=y and ( field3 ... ) )
if ($i < count($dc->order) - 1) {
$equal = DataCondition::FieldEquals($column, $data[$column]);
$nextWhere = $nextWhere ? DataCondition::_And(array($equal, $nextWhere)) : $equal;
$prevWhere = $prevWhere ? DataCondition::_And(array($equal, $prevWhere)) : $equal;
}
$next = DataCondition::FieldIs($column, $of["dir"] == "DESC" ? dsopLESS : dsopMORE, $data[$column]);
$prev = DataCondition::FieldIs($column, $of["dir"] == "DESC" ? dsopMORE : dsopLESS, $data[$column]);
$nextWhere = DataCondition::_Or(array($next, $nextWhere));
$prevWhere = DataCondition::_Or(array($prev, $prevWhere));
}
$context = new DsFilterBuildContext;
return array(
"nextWhere" => $this->conditionToSQL($nextWhere, $context),
"prevWhere" => $this->conditionToSQL($prevWhere, $context),
"nextOrder" => implode(", ", array_reverse($nextOrder)),
"prevOrder" => implode(", ", array_reverse($prevOrder))
);
}
/**
* @param String fieldExpression - original field expression
* @param DsOperand fieldOperand - parameter dsotFIELD type operand with non-empty joinData
* @param DsFilterBuildContext context
* @return String|Boolean - returns new field expression or false if nothing's changed
*/
protected function createJoinedExpression( $field, $fieldExpr, $fieldOperand, $context ) {
$jd = $fieldOperand->joinData;
if( !$jd->dataSource->tableBased() || $jd->dataSource->connection !== $this->connection ) {
return false;
}
$joinQuery = $jd->dataSource->getJoinedQuery( $fieldExpr, $fieldOperand );
if( !$joinQuery ) {
return false;
}
$context->joins[] = $joinQuery["join"];
if( $jd->displayAlias ) {
$context->extraColumnSQL[] = $joinQuery["column"] . " AS " . $this->connection->addFieldWrappers( $jd->displayAlias );
$context->joinedAliases[ $field ] = $jd->displayAlias;
}
return $joinQuery["column"];
}
protected function tableBased() {
return true;
}
/**
* @param String fieldExpression - original field expression
* @param DsOperand fieldOperand - parameter dsotFIELD type operand with non-empty joinData
* @return false | Array(
* "join" => LEFT JOIN expression
* "alias" => subquery alias
* "column" => expression to use in the WHERE clause
* )
*/
protected function getJoinedQuery( $fieldExpr, $fieldOperand ) {
$jd = $fieldOperand->joinData;
if( $jd->longList && !$this->connection->checkIfJoinSubqueriesOptimized() ) {
// too slow
return false;
}
$dc = new DsCommand;
$tableAlias = generateAlias();
$linkAlias = generateAlias();
$displayAlias = generateAlias();
if( $jd->displayExpression ) {
$displayExpression = $jd->displayExpression;
} else {
$displayExpression = $this->fieldExpression( $jd->displayField );
}
$linkExpression = $this->fieldExpression( $jd->linkField );
$selectList = array();
$selectList[] = $displayExpression . " AS " . $displayAlias;
$selectList[] = $linkExpression . " AS " . $linkAlias;
$column = $tableAlias.".".$displayAlias;
$sql = $this->buildSQL( $dc, false, implode( ', ', $selectList ) );
$join = "LEFT JOIN (" . $sql . ") ". $tableAlias
." ON " . $linkAlias . " = ".$fieldExpr;
return array(
"join" => $join,
"alias" => $alias,
"column" => $column
);
}
public function lastError() {
return $this->connection->lastError();
}
/**
* NOT USED! delete
* Get multiple field values,
* @param DsFieldData - field description
* @param DsCommand
* @return QueryResult
*/
public function getFieldValues( $field, $dc ) {
$sql = $this->buildSQL( $dc, false, $this->extraColumnExpression( $field ) );
return $this->connection->limitedQuery( $sql, $dc->startRecord, $dc->reccount, true );
}
}
class DsFilterBuildContext {
/**
* <join SQL clause>
* @var Array of strings
*/
public $joins = array();
/**
* additional columns, to be added as is to SQL query
* @var Array
*/
public $extraColumnSQL = array();
/**
* displayFieldAliases
* @var Array
* Associative array of display field aliases
* "<field>" => "<display field alias>"
*/
public $joinedAliases = array();
}
?>
|