/**
  * @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}");
     }
 }