/**
* @expectedException \dokuwiki\plugin\struct\meta\StructException
*/
public function test_leftjoin_existing_alias_exception()
{
$qb = new QueryBuilder();
$qb->addTable('first', 'T1');
$qb->addTable('second', 'T2');
$qb->addLeftJoin('T2', 'third', 'T1', 'T2.bar = T1.bar');
}
/**
* Normalize tags before comparing
*
* @param QueryBuilder $QB
* @param string $tablealias
* @param string $colname
* @param string $comp
* @param string|string[] $value
* @param string $op
*/
public function filter(QueryBuilder $QB, $tablealias, $colname, $comp, $value, $op)
{
/** @var QueryBuilderWhere $add Where additionional queries are added to*/
if (is_array($value)) {
$add = $QB->filters()->where($op);
// sub where group
$op = 'OR';
} else {
$add = $QB->filters();
// main where clause
}
foreach ((array) $value as $item) {
$pl = $QB->addValue($item);
$add->where($op, "LOWER(REPLACE({$tablealias}.{$colname}, ' ', '')) {$comp} LOWER(REPLACE({$pl}, ' ', ''))");
}
}
/**
* Comparisons are done against the full string (including prefix/postfix)
*
* @param QueryBuilder $QB
* @param string $tablealias
* @param string $colname
* @param string $comp
* @param string|string[] $value
* @param string $op
*/
public function filter(QueryBuilder $QB, $tablealias, $colname, $comp, $value, $op)
{
/** @var QueryBuilderWhere $add Where additionional queries are added to */
if (is_array($value)) {
$add = $QB->filters()->where($op);
// sub where group
$op = 'OR';
} else {
$add = $QB->filters();
// main where clause
}
foreach ((array) $value as $item) {
$column = "{$tablealias}.{$colname}";
if ($this->config['prefix']) {
$pl = $QB->addValue($this->config['prefix']);
$column = "{$pl} || {$column}";
}
if ($this->config['postfix']) {
$pl = $QB->addValue($this->config['postfix']);
$column = "{$column} || {$pl}";
}
$pl = $QB->addValue($item);
$add->where($op, "{$column} {$comp} {$pl}");
}
}
/**
* Transform the set search parameters into a statement
*
* @return array ($sql, $opts) The SQL and parameters to execute
*/
public function getSQL()
{
if (!$this->columns) {
throw new StructException('nocolname');
}
$QB = new QueryBuilder();
// basic tables
$first_table = '';
foreach ($this->schemas as $schema) {
$datatable = 'data_' . $schema->getTable();
if ($first_table) {
// follow up tables
$QB->addLeftJoin($first_table, $datatable, $datatable, "{$first_table}.pid = {$datatable}.pid");
} else {
// first table
if (!$schema->isLookup()) {
$QB->addTable('schema_assignments');
$QB->filters()->whereAnd("{$datatable}.pid = schema_assignments.pid");
$QB->filters()->whereAnd("schema_assignments.tbl = '{$schema->getTable()}'");
$QB->filters()->whereAnd("schema_assignments.assigned = 1");
$QB->filters()->whereAnd("GETACCESSLEVEL({$datatable}.pid) > 0");
$QB->filters()->whereAnd("PAGEEXISTS({$datatable}.pid) = 1");
}
$QB->addTable($datatable);
$QB->addSelectColumn($datatable, 'pid', 'PID');
$QB->addGroupByColumn($datatable, 'pid');
$first_table = $datatable;
}
$QB->filters()->whereAnd("{$datatable}.latest = 1");
}
// columns to select, handling multis
$sep = self::CONCAT_SEPARATOR;
$n = 0;
foreach ($this->columns as $col) {
$CN = 'C' . $n++;
if ($col->isMulti()) {
$datatable = "data_{$col->getTable()}";
$multitable = "multi_{$col->getTable()}";
$MN = 'M' . $col->getColref();
$QB->addLeftJoin($datatable, $multitable, $MN, "{$datatable}.pid = {$MN}.pid AND\n {$datatable}.rev = {$MN}.rev AND\n {$MN}.colref = {$col->getColref()}");
$col->getType()->select($QB, $MN, 'value', $CN);
$sel = $QB->getSelectStatement($CN);
$QB->addSelectStatement("GROUP_CONCAT({$sel}, '{$sep}')", $CN);
} else {
$col->getType()->select($QB, 'data_' . $col->getTable(), $col->getColName(), $CN);
$QB->addGroupByStatement($CN);
}
}
// where clauses
foreach ($this->filter as $filter) {
list($col, $value, $comp, $op) = $filter;
$datatable = "data_{$col->getTable()}";
$multitable = "multi_{$col->getTable()}";
/** @var $col Column */
if ($col->isMulti()) {
$MN = 'MN' . $col->getColref();
// FIXME this joins a second time if the column was selected before
$QB->addLeftJoin($datatable, $multitable, $MN, "{$datatable}.pid = {$MN}.pid AND\n {$datatable}.rev = {$MN}.rev AND\n {$MN}.colref = {$col->getColref()}");
$coltbl = $MN;
$colnam = 'value';
} else {
$coltbl = $datatable;
$colnam = $col->getColName();
}
$col->getType()->filter($QB, $coltbl, $colnam, $comp, $value, $op);
// type based filter
}
// sorting - we always sort by the single val column
foreach ($this->sortby as $sort) {
list($col, $asc) = $sort;
/** @var $col Column */
$col->getType()->sort($QB, 'data_' . $col->getTable(), $col->getColName(false), $asc ? 'ASC' : 'DESC');
}
return $QB->getSQL();
}
public function fixPlaceholders($sql)
{
return parent::fixPlaceholders($sql);
}
/**
* @expectedException \dokuwiki\plugin\struct\meta\StructException
*/
public function test_missing_alias()
{
$qb = new QueryBuilder();
$qb->addTable('first', 'T1');
$qb->addSelectColumn('WrongAlias', 'colbar', 'colAlias');
}
/**
* Sort results by this type
*
* The default implementation should be good for nearly all types. However some
* types may need to do proper SQLite type casting to have the right order.
*
* Generally if you implemented @see select() you probably want to implement this,
* too.
*
* @param QueryBuilder $QB
* @param string $tablealias The table the currently saved value is stored in
* @param string $colname The column name on above table (always single column!)
* @param string $order either ASC or DESC
*/
public function sort(QueryBuilder $QB, $tablealias, $colname, $order)
{
$QB->addOrderBy("{$tablealias}.{$colname} {$order}");
}
/**
* @param QueryBuilder $QB
* @param string $tablealias
* @param string $colname
* @param string $comp
* @param string|\string[] $value
* @param string $op
*/
public function filter(QueryBuilder $QB, $tablealias, $colname, $comp, $value, $op)
{
// when accessing the revision column we need to convert from Unix timestamp
$col = "{$tablealias}.{$colname}";
if ($colname == 'rev') {
$col = "DATETIME({$col}, 'unixepoch', 'localtime')";
}
/** @var QueryBuilderWhere $add Where additionional queries are added to*/
if (is_array($value)) {
$add = $QB->filters()->where($op);
// sub where group
$op = 'OR';
} else {
$add = $QB->filters();
// main where clause
}
foreach ((array) $value as $item) {
$pl = $QB->addValue($item);
$add->where($op, "{$col} {$comp} {$pl}");
}
}