public function getAllColumns()
{
$selectOne = new Select();
$selectOne->columns(['column_name' => 'COLUMN_NAME', 'sort' => new Expression('IFNULL(sort, ORDINAL_POSITION)'), 'type' => new Expression('UCASE(C.DATA_TYPE)'), 'char_length' => 'CHARACTER_MAXIMUM_LENGTH', 'is_nullable' => 'IS_NULLABLE', 'default_value' => 'COLUMN_DEFAULT', 'comment' => new Expression('IFNULL(comment, COLUMN_COMMENT)'), 'column_type' => 'COLUMN_TYPE', 'column_key' => 'COLUMN_KEY']);
$selectOne->from(['C' => new TableIdentifier('COLUMNS', 'INFORMATION_SCHEMA')]);
$selectOne->join(['D' => 'directus_columns'], 'C.COLUMN_NAME = D.column_name AND C.TABLE_NAME = D.table_name', ['ui', 'hidden_list' => new Expression('IFNULL(hidden_list, 0)'), 'hidden_input' => new Expression('IFNULL(hidden_input, 0)'), 'relationship_type', 'related_table', 'junction_table', 'junction_key_left', 'junction_key_right', 'required' => new Expression('IFNULL(D.required, 0)')], $selectOne::JOIN_LEFT);
$selectOne->join(['T' => new TableIdentifier('TABLES', 'INFORMATION_SCHEMA')], 'C.TABLE_NAME = T.TABLE_NAME', ['table_name' => 'TABLE_NAME'], $selectOne::JOIN_LEFT);
$selectOne->where(['C.TABLE_SCHEMA' => $this->adapter->getCurrentSchema(), 'T.TABLE_SCHEMA' => $this->adapter->getCurrentSchema(), 'T.TABLE_TYPE' => 'BASE TABLE']);
$selectTwo = new Select();
$selectTwo->columns(['column_name', 'sort', 'type' => new Expression('UCASE(data_type)'), 'char_length' => new Expression('NULL'), 'is_nullable' => new Expression('"NO"'), 'default_value' => new Expression('NULL'), 'comment', 'column_type' => new Expression('NULL'), 'column_key' => new Expression('NULL'), 'ui', 'hidden_list', 'hidden_input', 'relationship_type', 'related_table', 'junction_table', 'junction_key_left', 'junction_key_right', 'required' => new Expression('IFNULL(required, 0)'), 'table_name']);
$selectTwo->from('directus_columns');
$where = new Where();
$where->addPredicate(new In('data_type', ['alias', 'MANYTOMANY', 'ONETOMANY']));
$selectTwo->where($where);
$selectOne->combine($selectTwo, $selectOne::COMBINE_UNION, 'ALL');
$selectOne->order('table_name');
$sql = new Sql($this->adapter);
$statement = $sql->prepareStatementForSqlObject($selectOne);
return iterator_to_array($statement->execute());
}
public function providerData()
{
// basic table
$select0 = new Select();
$select0->from('foo');
$sqlPrep0 = $sqlStr0 = 'SELECT "foo".* FROM "foo"';
$internalTests0 = array('processSelect' => array(array(array('"foo".*')), '"foo"'));
// table as TableIdentifier
$select1 = new Select();
$select1->from(new TableIdentifier('foo', 'bar'));
$sqlPrep1 = $sqlStr1 = 'SELECT "bar"."foo".* FROM "bar"."foo"';
$internalTests1 = array('processSelect' => array(array(array('"bar"."foo".*')), '"bar"."foo"'));
// table with alias
$select2 = new Select();
$select2->from(array('f' => 'foo'));
$sqlPrep2 = $sqlStr2 = 'SELECT "f".* FROM "foo" AS "f"';
$internalTests2 = array('processSelect' => array(array(array('"f".*')), '"foo" AS "f"'));
// table with alias with table as TableIdentifier
$select3 = new Select();
$select3->from(array('f' => new TableIdentifier('foo')));
$sqlPrep3 = $sqlStr3 = 'SELECT "f".* FROM "foo" AS "f"';
$internalTests3 = array('processSelect' => array(array(array('"f".*')), '"foo" AS "f"'));
// columns
$select4 = new Select();
$select4->from('foo')->columns(array('bar', 'baz'));
$sqlPrep4 = $sqlStr4 = 'SELECT "foo"."bar" AS "bar", "foo"."baz" AS "baz" FROM "foo"';
$internalTests4 = array('processSelect' => array(array(array('"foo"."bar"', '"bar"'), array('"foo"."baz"', '"baz"')), '"foo"'));
// columns with AS associative array
$select5 = new Select();
$select5->from('foo')->columns(array('bar' => 'baz'));
$sqlPrep5 = $sqlStr5 = 'SELECT "foo"."baz" AS "bar" FROM "foo"';
$internalTests5 = array('processSelect' => array(array(array('"foo"."baz"', '"bar"')), '"foo"'));
// columns with AS associative array mixed
$select6 = new Select();
$select6->from('foo')->columns(array('bar' => 'baz', 'bam'));
$sqlPrep6 = $sqlStr6 = 'SELECT "foo"."baz" AS "bar", "foo"."bam" AS "bam" FROM "foo"';
$internalTests6 = array('processSelect' => array(array(array('"foo"."baz"', '"bar"'), array('"foo"."bam"', '"bam"')), '"foo"'));
// columns where value is Expression, with AS
$select7 = new Select();
$select7->from('foo')->columns(array('bar' => new Expression('COUNT(some_column)')));
$sqlPrep7 = $sqlStr7 = 'SELECT COUNT(some_column) AS "bar" FROM "foo"';
$internalTests7 = array('processSelect' => array(array(array('COUNT(some_column)', '"bar"')), '"foo"'));
// columns where value is Expression
$select8 = new Select();
$select8->from('foo')->columns(array(new Expression('COUNT(some_column) AS bar')));
$sqlPrep8 = $sqlStr8 = 'SELECT COUNT(some_column) AS bar FROM "foo"';
$internalTests8 = array('processSelect' => array(array(array('COUNT(some_column) AS bar')), '"foo"'));
// columns where value is Expression with parameters
$select9 = new Select();
$select9->from('foo')->columns(array(new Expression('(COUNT(?) + ?) AS ?', array('some_column', 5, 'bar'), array(Expression::TYPE_IDENTIFIER, Expression::TYPE_VALUE, Expression::TYPE_IDENTIFIER))));
$sqlPrep9 = 'SELECT (COUNT("some_column") + ?) AS "bar" FROM "foo"';
$sqlStr9 = 'SELECT (COUNT("some_column") + \'5\') AS "bar" FROM "foo"';
$params9 = array('column1' => 5);
$internalTests9 = array('processSelect' => array(array(array('(COUNT("some_column") + ?) AS "bar"')), '"foo"'));
// joins (plain)
$select10 = new Select();
$select10->from('foo')->join('zac', 'm = n');
$sqlPrep10 = $sqlStr10 = 'SELECT "foo".*, "zac".* FROM "foo" INNER JOIN "zac" ON "m" = "n"';
$internalTests10 = array('processSelect' => array(array(array('"foo".*'), array('"zac".*')), '"foo"'), 'processJoins' => array(array(array('INNER', '"zac"', '"m" = "n"'))));
// join with columns
$select11 = new Select();
$select11->from('foo')->join('zac', 'm = n', array('bar', 'baz'));
$sqlPrep11 = $sqlStr11 = 'SELECT "foo".*, "zac"."bar" AS "bar", "zac"."baz" AS "baz" FROM "foo" INNER JOIN "zac" ON "m" = "n"';
$internalTests11 = array('processSelect' => array(array(array('"foo".*'), array('"zac"."bar"', '"bar"'), array('"zac"."baz"', '"baz"')), '"foo"'), 'processJoins' => array(array(array('INNER', '"zac"', '"m" = "n"'))));
// join with alternate type
$select12 = new Select();
$select12->from('foo')->join('zac', 'm = n', array('bar', 'baz'), Select::JOIN_OUTER);
$sqlPrep12 = $sqlStr12 = 'SELECT "foo".*, "zac"."bar" AS "bar", "zac"."baz" AS "baz" FROM "foo" OUTER JOIN "zac" ON "m" = "n"';
$internalTests12 = array('processSelect' => array(array(array('"foo".*'), array('"zac"."bar"', '"bar"'), array('"zac"."baz"', '"baz"')), '"foo"'), 'processJoins' => array(array(array('OUTER', '"zac"', '"m" = "n"'))));
// join with column aliases
$select13 = new Select();
$select13->from('foo')->join('zac', 'm = n', array('BAR' => 'bar', 'BAZ' => 'baz'));
$sqlPrep13 = $sqlStr13 = 'SELECT "foo".*, "zac"."bar" AS "BAR", "zac"."baz" AS "BAZ" FROM "foo" INNER JOIN "zac" ON "m" = "n"';
$internalTests13 = array('processSelect' => array(array(array('"foo".*'), array('"zac"."bar"', '"BAR"'), array('"zac"."baz"', '"BAZ"')), '"foo"'), 'processJoins' => array(array(array('INNER', '"zac"', '"m" = "n"'))));
// join with table aliases
$select14 = new Select();
$select14->from('foo')->join(array('b' => 'bar'), 'b.foo_id = foo.foo_id');
$sqlPrep14 = $sqlStr14 = 'SELECT "foo".*, "b".* FROM "foo" INNER JOIN "bar" AS "b" ON "b"."foo_id" = "foo"."foo_id"';
$internalTests14 = array('processSelect' => array(array(array('"foo".*'), array('"b".*')), '"foo"'), 'processJoins' => array(array(array('INNER', '"bar" AS "b"', '"b"."foo_id" = "foo"."foo_id"'))));
// where (simple string)
$select15 = new Select();
$select15->from('foo')->where('x = 5');
$sqlPrep15 = $sqlStr15 = 'SELECT "foo".* FROM "foo" WHERE x = 5';
$internalTests15 = array('processSelect' => array(array(array('"foo".*')), '"foo"'), 'processWhere' => array('x = 5'));
// where (returning parameters)
$select16 = new Select();
$select16->from('foo')->where(array('x = ?' => 5));
$sqlPrep16 = 'SELECT "foo".* FROM "foo" WHERE x = ?';
$sqlStr16 = 'SELECT "foo".* FROM "foo" WHERE x = \'5\'';
$params16 = array('where1' => 5);
$internalTests16 = array('processSelect' => array(array(array('"foo".*')), '"foo"'), 'processWhere' => array('x = ?'));
// group
$select17 = new Select();
$select17->from('foo')->group(array('col1', 'col2'));
$sqlPrep17 = $sqlStr17 = 'SELECT "foo".* FROM "foo" GROUP BY "col1", "col2"';
$internalTests17 = array('processSelect' => array(array(array('"foo".*')), '"foo"'), 'processGroup' => array(array('"col1"', '"col2"')));
$select18 = new Select();
$select18->from('foo')->group('col1')->group('col2');
$sqlPrep18 = $sqlStr18 = 'SELECT "foo".* FROM "foo" GROUP BY "col1", "col2"';
$internalTests18 = array('processSelect' => array(array(array('"foo".*')), '"foo"'), 'processGroup' => array(array('"col1"', '"col2"')));
$select19 = new Select();
$select19->from('foo')->group(new Expression('DAY(?)', array('col1'), array(Expression::TYPE_IDENTIFIER)));
$sqlPrep19 = $sqlStr19 = 'SELECT "foo".* FROM "foo" GROUP BY DAY("col1")';
$internalTests19 = array('processSelect' => array(array(array('"foo".*')), '"foo"'), 'processGroup' => array(array('DAY("col1")')));
// having (simple string)
$select20 = new Select();
$select20->from('foo')->having('x = 5');
$sqlPrep20 = $sqlStr20 = 'SELECT "foo".* FROM "foo" HAVING x = 5';
$internalTests20 = array('processSelect' => array(array(array('"foo".*')), '"foo"'), 'processHaving' => array('x = 5'));
// having (returning parameters)
$select21 = new Select();
$select21->from('foo')->having(array('x = ?' => 5));
$sqlPrep21 = 'SELECT "foo".* FROM "foo" HAVING x = ?';
$sqlStr21 = 'SELECT "foo".* FROM "foo" HAVING x = \'5\'';
$params21 = array('having1' => 5);
$internalTests21 = array('processSelect' => array(array(array('"foo".*')), '"foo"'), 'processHaving' => array('x = ?'));
// order
$select22 = new Select();
$select22->from('foo')->order('c1');
$sqlPrep22 = $sqlStr22 = 'SELECT "foo".* FROM "foo" ORDER BY "c1" ASC';
$internalTests22 = array('processSelect' => array(array(array('"foo".*')), '"foo"'), 'processOrder' => array(array(array('"c1"', Select::ORDER_ASCENDING))));
$select23 = new Select();
$select23->from('foo')->order(array('c1', 'c2'));
$sqlPrep23 = $sqlStr23 = 'SELECT "foo".* FROM "foo" ORDER BY "c1" ASC, "c2" ASC';
$internalTests23 = array('processSelect' => array(array(array('"foo".*')), '"foo"'), 'processOrder' => array(array(array('"c1"', Select::ORDER_ASCENDING), array('"c2"', Select::ORDER_ASCENDING))));
$select24 = new Select();
$select24->from('foo')->order(array('c1' => 'DESC', 'c2' => 'Asc'));
// notice partially lower case ASC
$sqlPrep24 = $sqlStr24 = 'SELECT "foo".* FROM "foo" ORDER BY "c1" DESC, "c2" ASC';
$internalTests24 = array('processSelect' => array(array(array('"foo".*')), '"foo"'), 'processOrder' => array(array(array('"c1"', Select::ORDER_DESCENDING), array('"c2"', Select::ORDER_ASCENDING))));
$select25 = new Select();
$select25->from('foo')->order(array('c1' => 'asc'))->order('c2 desc');
// notice partially lower case ASC
$sqlPrep25 = $sqlStr25 = 'SELECT "foo".* FROM "foo" ORDER BY "c1" ASC, "c2" DESC';
$internalTests25 = array('processSelect' => array(array(array('"foo".*')), '"foo"'), 'processOrder' => array(array(array('"c1"', Select::ORDER_ASCENDING), array('"c2"', Select::ORDER_DESCENDING))));
// limit
$select26 = new Select();
$select26->from('foo')->limit(5);
$sqlPrep26 = 'SELECT "foo".* FROM "foo" LIMIT ?';
$sqlStr26 = 'SELECT "foo".* FROM "foo" LIMIT \'5\'';
$params26 = array('limit' => 5);
$internalTests26 = array('processSelect' => array(array(array('"foo".*')), '"foo"'), 'processLimit' => array('?'));
// limit with offset
$select27 = new Select();
$select27->from('foo')->limit(5)->offset(10);
$sqlPrep27 = 'SELECT "foo".* FROM "foo" LIMIT ? OFFSET ?';
$sqlStr27 = 'SELECT "foo".* FROM "foo" LIMIT \'5\' OFFSET \'10\'';
$params27 = array('limit' => 5, 'offset' => 10);
$internalTests27 = array('processSelect' => array(array(array('"foo".*')), '"foo"'), 'processLimit' => array('?'), 'processOffset' => array('?'));
// joins with a few keywords in the on clause
$select28 = new Select();
$select28->from('foo')->join('zac', '(m = n AND c.x) BETWEEN x AND y.z OR (c.x < y.z AND c.x <= y.z AND c.x > y.z AND c.x >= y.z)');
$sqlPrep28 = $sqlStr28 = 'SELECT "foo".*, "zac".* FROM "foo" INNER JOIN "zac" ON ("m" = "n" AND "c"."x") BETWEEN "x" AND "y"."z" OR ("c"."x" < "y"."z" AND "c"."x" <= "y"."z" AND "c"."x" > "y"."z" AND "c"."x" >= "y"."z")';
$internalTests28 = array('processSelect' => array(array(array('"foo".*'), array('"zac".*')), '"foo"'), 'processJoins' => array(array(array('INNER', '"zac"', '("m" = "n" AND "c"."x") BETWEEN "x" AND "y"."z" OR ("c"."x" < "y"."z" AND "c"."x" <= "y"."z" AND "c"."x" > "y"."z" AND "c"."x" >= "y"."z")'))));
// order with compound name
$select29 = new Select();
$select29->from('foo')->order('c1.d2');
$sqlPrep29 = $sqlStr29 = 'SELECT "foo".* FROM "foo" ORDER BY "c1"."d2" ASC';
$internalTests29 = array('processSelect' => array(array(array('"foo".*')), '"foo"'), 'processOrder' => array(array(array('"c1"."d2"', Select::ORDER_ASCENDING))));
// group with compound name
$select30 = new Select();
$select30->from('foo')->group('c1.d2');
$sqlPrep30 = $sqlStr30 = 'SELECT "foo".* FROM "foo" GROUP BY "c1"."d2"';
$internalTests30 = array('processSelect' => array(array(array('"foo".*')), '"foo"'), 'processGroup' => array(array('"c1"."d2"')));
// join with expression in ON part
$select31 = new Select();
$select31->from('foo')->join('zac', new Expression('(m = n AND c.x) BETWEEN x AND y.z'));
$sqlPrep31 = $sqlStr31 = 'SELECT "foo".*, "zac".* FROM "foo" INNER JOIN "zac" ON (m = n AND c.x) BETWEEN x AND y.z';
$internalTests31 = array('processSelect' => array(array(array('"foo".*'), array('"zac".*')), '"foo"'), 'processJoins' => array(array(array('INNER', '"zac"', '(m = n AND c.x) BETWEEN x AND y.z'))));
$select32subselect = new Select();
$select32subselect->from('bar')->where->like('y', '%Foo%');
$select32 = new Select();
$select32->from(array('x' => $select32subselect));
$sqlPrep32 = 'SELECT "x".* FROM (SELECT "bar".* FROM "bar" WHERE "y" LIKE ?) AS "x"';
$sqlStr32 = 'SELECT "x".* FROM (SELECT "bar".* FROM "bar" WHERE "y" LIKE \'%Foo%\') AS "x"';
$internalTests32 = array('processSelect' => array(array(array('"x".*')), '(SELECT "bar".* FROM "bar" WHERE "y" LIKE ?) AS "x"'));
$select33 = new Select();
$select33->from('table')->columns(array('*'))->where(array('c1' => null, 'c2' => array(1, 2, 3), new \Zend\Db\Sql\Predicate\IsNotNull('c3')));
$sqlPrep33 = 'SELECT "table".* FROM "table" WHERE "c1" IS NULL AND "c2" IN (?, ?, ?) AND "c3" IS NOT NULL';
$sqlStr33 = 'SELECT "table".* FROM "table" WHERE "c1" IS NULL AND "c2" IN (\'1\', \'2\', \'3\') AND "c3" IS NOT NULL';
$internalTests33 = array('processSelect' => array(array(array('"table".*')), '"table"'), 'processWhere' => array('"c1" IS NULL AND "c2" IN (?, ?, ?) AND "c3" IS NOT NULL'));
// @author Demian Katz
$select34 = new Select();
$select34->from('table')->order(array(new Expression('isnull(?) DESC', array('name'), array(Expression::TYPE_IDENTIFIER)), 'name'));
$sqlPrep34 = 'SELECT "table".* FROM "table" ORDER BY isnull("name") DESC, "name" ASC';
$sqlStr34 = 'SELECT "table".* FROM "table" ORDER BY isnull("name") DESC, "name" ASC';
$internalTests34 = array('processOrder' => array(array(array('isnull("name") DESC'), array('"name"', Select::ORDER_ASCENDING))));
// join with Expression object in COLUMNS part (ZF2-514)
// @co-author Koen Pieters (kpieters)
$select35 = new Select();
$select35->from('foo')->columns(array())->join('bar', 'm = n', array('thecount' => new Expression("COUNT(*)")));
$sqlPrep35 = $sqlStr35 = 'SELECT COUNT(*) AS "thecount" FROM "foo" INNER JOIN "bar" ON "m" = "n"';
$internalTests35 = array('processSelect' => array(array(array('COUNT(*)', '"thecount"')), '"foo"'), 'processJoins' => array(array(array('INNER', '"bar"', '"m" = "n"'))));
// multiple joins with expressions
// reported by @jdolieslager
$select36 = new Select();
$select36->from('foo')->join('tableA', new Predicate\Operator('id', '=', 1))->join('tableB', new Predicate\Operator('id', '=', 2))->join('tableC', new Predicate\PredicateSet(array(new Predicate\Operator('id', '=', 3), new Predicate\Operator('number', '>', 20))));
$sqlPrep36 = 'SELECT "foo".*, "tableA".*, "tableB".*, "tableC".* FROM "foo"' . ' INNER JOIN "tableA" ON "id" = :join1part1 INNER JOIN "tableB" ON "id" = :join2part1 ' . 'INNER JOIN "tableC" ON "id" = :join3part1 AND "number" > :join3part2';
$sqlStr36 = 'SELECT "foo".*, "tableA".*, "tableB".*, "tableC".* FROM "foo" ' . 'INNER JOIN "tableA" ON "id" = \'1\' INNER JOIN "tableB" ON "id" = \'2\' ' . 'INNER JOIN "tableC" ON "id" = \'3\' AND "number" > \'20\'';
$internalTests36 = array();
$useNamedParams36 = true;
/**
* @author robertbasic
* @link https://github.com/zendframework/zf2/pull/2714
*/
$select37 = new Select();
$select37->from('foo')->columns(array('bar'), false);
$sqlPrep37 = $sqlStr37 = 'SELECT "bar" AS "bar" FROM "foo"';
$internalTests37 = array('processSelect' => array(array(array('"bar"', '"bar"')), '"foo"'));
// @link https://github.com/zendframework/zf2/issues/3294
// Test TableIdentifier In Joins
$select38 = new Select();
$select38->from('foo')->columns(array())->join(new TableIdentifier('bar', 'baz'), 'm = n', array('thecount' => new Expression("COUNT(*)")));
$sqlPrep38 = $sqlStr38 = 'SELECT COUNT(*) AS "thecount" FROM "foo" INNER JOIN "baz"."bar" ON "m" = "n"';
$internalTests38 = array('processSelect' => array(array(array('COUNT(*)', '"thecount"')), '"foo"'), 'processJoins' => array(array(array('INNER', '"baz"."bar"', '"m" = "n"'))));
// subselect in join
$select39subselect = new Select();
$select39subselect->from('bar')->where->like('y', '%Foo%');
$select39 = new Select();
$select39->from('foo')->join(array('z' => $select39subselect), 'z.foo = bar.id');
$sqlPrep39 = 'SELECT "foo".*, "z".* FROM "foo" INNER JOIN (SELECT "bar".* FROM "bar" WHERE "y" LIKE ?) AS "z" ON "z"."foo" = "bar"."id"';
$sqlStr39 = 'SELECT "foo".*, "z".* FROM "foo" INNER JOIN (SELECT "bar".* FROM "bar" WHERE "y" LIKE \'%Foo%\') AS "z" ON "z"."foo" = "bar"."id"';
$internalTests39 = array('processJoins' => array(array(array('INNER', '(SELECT "bar".* FROM "bar" WHERE "y" LIKE ?) AS "z"', '"z"."foo" = "bar"."id"'))));
// @link https://github.com/zendframework/zf2/issues/3294
// Test TableIdentifier In Joins, with multiple joins
$select40 = new Select();
$select40->from('foo')->join(array('a' => new TableIdentifier('another_foo', 'another_schema')), 'a.x = foo.foo_column')->join('bar', 'foo.colx = bar.colx');
$sqlPrep40 = $sqlStr40 = 'SELECT "foo".*, "a".*, "bar".* FROM "foo"' . ' INNER JOIN "another_schema"."another_foo" AS "a" ON "a"."x" = "foo"."foo_column"' . ' INNER JOIN "bar" ON "foo"."colx" = "bar"."colx"';
$internalTests40 = array('processSelect' => array(array(array('"foo".*'), array('"a".*'), array('"bar".*')), '"foo"'), 'processJoins' => array(array(array('INNER', '"another_schema"."another_foo" AS "a"', '"a"."x" = "foo"."foo_column"'), array('INNER', '"bar"', '"foo"."colx" = "bar"."colx"'))));
$select41 = new Select();
$select41->from('foo')->quantifier(Select::QUANTIFIER_DISTINCT);
$sqlPrep41 = $sqlStr41 = 'SELECT DISTINCT "foo".* FROM "foo"';
$internalTests41 = array('processSelect' => array(SELECT::QUANTIFIER_DISTINCT, array(array('"foo".*')), '"foo"'));
$select42 = new Select();
$select42->from('foo')->quantifier(new Expression('TOP ?', array(10)));
$sqlPrep42 = 'SELECT TOP ? "foo".* FROM "foo"';
$sqlStr42 = 'SELECT TOP \'10\' "foo".* FROM "foo"';
$internalTests42 = array('processSelect' => array('TOP ?', array(array('"foo".*')), '"foo"'));
$select43 = new Select();
$select43->from(array('x' => 'foo'))->columns(array('bar'), false);
$sqlPrep43 = 'SELECT "bar" AS "bar" FROM "foo" AS "x"';
$sqlStr43 = 'SELECT "bar" AS "bar" FROM "foo" AS "x"';
$internalTests43 = array('processSelect' => array(array(array('"bar"', '"bar"')), '"foo" AS "x"'));
$select44 = new Select();
$select44->from('foo')->where('a = b');
$select44b = new Select();
$select44b->from('bar')->where('c = d');
$select44->combine($select44b, Select::COMBINE_UNION, 'ALL');
$sqlPrep44 = $sqlStr44 = '( SELECT "foo".* FROM "foo" WHERE a = b ) UNION ALL ( SELECT "bar".* FROM "bar" WHERE c = d )';
$internalTests44 = array('processCombine' => array('UNION ALL', 'SELECT "bar".* FROM "bar" WHERE c = d'));
// limit with offset
$select45 = new Select();
$select45->from('foo')->limit("5")->offset("10");
$sqlPrep45 = 'SELECT "foo".* FROM "foo" LIMIT ? OFFSET ?';
$sqlStr45 = 'SELECT "foo".* FROM "foo" LIMIT \'5\' OFFSET \'10\'';
$params45 = array('limit' => 5, 'offset' => 10);
$internalTests45 = array('processSelect' => array(array(array('"foo".*')), '"foo"'), 'processLimit' => array('?'), 'processOffset' => array('?'));
// functions without table
$select46 = new Select();
$select46->columns(array(new Expression('SOME_DB_FUNCTION_ONE()'), 'foo' => new Expression('SOME_DB_FUNCTION_TWO()')));
$sqlPrep46 = 'SELECT SOME_DB_FUNCTION_ONE() AS Expression1, SOME_DB_FUNCTION_TWO() AS "foo"';
$sqlStr46 = 'SELECT SOME_DB_FUNCTION_ONE() AS Expression1, SOME_DB_FUNCTION_TWO() AS "foo"';
$params46 = array();
$internalTests46 = array();
// limit with big offset and limit
$select47 = new Select();
$select47->from('foo')->limit("10000000000000000000")->offset("10000000000000000000");
$sqlPrep47 = 'SELECT "foo".* FROM "foo" LIMIT ? OFFSET ?';
$sqlStr47 = 'SELECT "foo".* FROM "foo" LIMIT \'10000000000000000000\' OFFSET \'10000000000000000000\'';
$params47 = array('limit' => 1.0E+19, 'offset' => 1.0E+19);
$internalTests47 = array('processSelect' => array(array(array('"foo".*')), '"foo"'), 'processLimit' => array('?'), 'processOffset' => array('?'));
//combine and union with order at the end
$select48 = new Select();
$select48->from('foo')->where('a = b');
$select48b = new Select();
$select48b->from('bar')->where('c = d');
$select48->combine($select48b);
$select48combined = new Select();
$select48 = $select48combined->from(array('sub' => $select48))->order('id DESC');
$sqlPrep48 = $sqlStr48 = 'SELECT "sub".* FROM (( SELECT "foo".* FROM "foo" WHERE a = b ) UNION ( SELECT "bar".* FROM "bar" WHERE c = d )) AS "sub" ORDER BY "id" DESC';
$internalTests48 = array('processCombine' => null);
//Expression as joinName
$select49 = new Select();
$select49->from(new TableIdentifier('foo'))->join(array('bar' => new Expression('psql_function_which_returns_table')), 'foo.id = bar.fooid');
$sqlPrep49 = $sqlStr49 = 'SELECT "foo".*, "bar".* FROM "foo" INNER JOIN psql_function_which_returns_table AS "bar" ON "foo"."id" = "bar"."fooid"';
$internalTests49 = array('processSelect' => array(array(array('"foo".*'), array('"bar".*')), '"foo"'), 'processJoins' => array(array(array('INNER', 'psql_function_which_returns_table AS "bar"', '"foo"."id" = "bar"."fooid"'))));
/**
* $select = the select object
* $sqlPrep = the sql as a result of preparation
* $params = the param container contents result of preparation
* $sqlStr = the sql as a result of getting a string back
* $internalTests what the internal functions should return (safe-guarding extension)
*/
return array(array($select0, $sqlPrep0, array(), $sqlStr0, $internalTests0), array($select1, $sqlPrep1, array(), $sqlStr1, $internalTests1), array($select2, $sqlPrep2, array(), $sqlStr2, $internalTests2), array($select3, $sqlPrep3, array(), $sqlStr3, $internalTests3), array($select4, $sqlPrep4, array(), $sqlStr4, $internalTests4), array($select5, $sqlPrep5, array(), $sqlStr5, $internalTests5), array($select6, $sqlPrep6, array(), $sqlStr6, $internalTests6), array($select7, $sqlPrep7, array(), $sqlStr7, $internalTests7), array($select8, $sqlPrep8, array(), $sqlStr8, $internalTests8), array($select9, $sqlPrep9, $params9, $sqlStr9, $internalTests9), array($select10, $sqlPrep10, array(), $sqlStr10, $internalTests10), array($select11, $sqlPrep11, array(), $sqlStr11, $internalTests11), array($select12, $sqlPrep12, array(), $sqlStr12, $internalTests12), array($select13, $sqlPrep13, array(), $sqlStr13, $internalTests13), array($select14, $sqlPrep14, array(), $sqlStr14, $internalTests14), array($select15, $sqlPrep15, array(), $sqlStr15, $internalTests15), array($select16, $sqlPrep16, $params16, $sqlStr16, $internalTests16), array($select17, $sqlPrep17, array(), $sqlStr17, $internalTests17), array($select18, $sqlPrep18, array(), $sqlStr18, $internalTests18), array($select19, $sqlPrep19, array(), $sqlStr19, $internalTests19), array($select20, $sqlPrep20, array(), $sqlStr20, $internalTests20), array($select21, $sqlPrep21, $params21, $sqlStr21, $internalTests21), array($select22, $sqlPrep22, array(), $sqlStr22, $internalTests22), array($select23, $sqlPrep23, array(), $sqlStr23, $internalTests23), array($select24, $sqlPrep24, array(), $sqlStr24, $internalTests24), array($select25, $sqlPrep25, array(), $sqlStr25, $internalTests25), array($select26, $sqlPrep26, $params26, $sqlStr26, $internalTests26), array($select27, $sqlPrep27, $params27, $sqlStr27, $internalTests27), array($select28, $sqlPrep28, array(), $sqlStr28, $internalTests28), array($select29, $sqlPrep29, array(), $sqlStr29, $internalTests29), array($select30, $sqlPrep30, array(), $sqlStr30, $internalTests30), array($select31, $sqlPrep31, array(), $sqlStr31, $internalTests31), array($select32, $sqlPrep32, array(), $sqlStr32, $internalTests32), array($select33, $sqlPrep33, array(), $sqlStr33, $internalTests33), array($select34, $sqlPrep34, array(), $sqlStr34, $internalTests34), array($select35, $sqlPrep35, array(), $sqlStr35, $internalTests35), array($select36, $sqlPrep36, array(), $sqlStr36, $internalTests36, $useNamedParams36), array($select37, $sqlPrep37, array(), $sqlStr37, $internalTests37), array($select38, $sqlPrep38, array(), $sqlStr38, $internalTests38), array($select39, $sqlPrep39, array(), $sqlStr39, $internalTests39), array($select40, $sqlPrep40, array(), $sqlStr40, $internalTests40), array($select41, $sqlPrep41, array(), $sqlStr41, $internalTests41), array($select42, $sqlPrep42, array(), $sqlStr42, $internalTests42), array($select43, $sqlPrep43, array(), $sqlStr43, $internalTests43), array($select44, $sqlPrep44, array(), $sqlStr44, $internalTests44), array($select45, $sqlPrep45, $params45, $sqlStr45, $internalTests45), array($select46, $sqlPrep46, $params46, $sqlStr46, $internalTests46), array($select47, $sqlPrep47, $params47, $sqlStr47, $internalTests47), array($select48, $sqlPrep48, array(), $sqlStr48, $internalTests48), array($select49, $sqlPrep49, array(), $sqlStr49, $internalTests49));
}