protected function assertLastSql( $sqlText ) {
$this->assertEquals(
- $this->database->getLastSqls(),
- $sqlText
+ $sqlText,
+ $this->database->getLastSqls()
);
}
protected function assertLastSqlDb( $sqlText, $db ) {
- $this->assertEquals( $db->getLastSqls(), $sqlText );
+ $this->assertEquals( $sqlText, $db->getLastSqls() );
}
/**
$sql['conds'],
__METHOD__,
isset( $sql['insertOptions'] ) ? $sql['insertOptions'] : [],
- isset( $sql['selectOptions'] ) ? $sql['selectOptions'] : []
+ isset( $sql['selectOptions'] ) ? $sql['selectOptions'] : [],
+ isset( $sql['selectJoinConds'] ) ? $sql['selectJoinConds'] : []
);
$this->assertLastSql( $sqlTextNative );
$sql['conds'],
__METHOD__,
isset( $sql['insertOptions'] ) ? $sql['insertOptions'] : [],
- isset( $sql['selectOptions'] ) ? $sql['selectOptions'] : []
+ isset( $sql['selectOptions'] ) ? $sql['selectOptions'] : [],
+ isset( $sql['selectJoinConds'] ) ? $sql['selectJoinConds'] : []
);
$this->assertLastSqlDb( implode( '; ', [ $sqlSelect, $sqlInsert ] ), $dbWeb );
}
"INSERT INTO insert_table " .
"(field_insert,field) " .
"SELECT field_select,field2 " .
- "FROM select_table",
+ "FROM select_table WHERE *",
"SELECT field_select AS field_insert,field2 AS field " .
"FROM select_table WHERE * FOR UPDATE",
"INSERT INTO insert_table (field_insert,field) VALUES ('0','1')"
"FROM select_table WHERE field = '2' ORDER BY field FOR UPDATE",
"INSERT IGNORE INTO insert_table (field_insert,field) VALUES ('0','1')"
],
+ [
+ [
+ 'destTable' => 'insert_table',
+ 'srcTable' => [ 'select_table1', 'select_table2' ],
+ 'varMap' => [ 'field_insert' => 'field_select', 'field' => 'field2' ],
+ 'conds' => [ 'field' => 2 ],
+ 'selectOptions' => [ 'ORDER BY' => 'field', 'FORCE INDEX' => [ 'select_table1' => 'index1' ] ],
+ 'selectJoinConds' => [
+ 'select_table2' => [ 'LEFT JOIN', [ 'select_table1.foo = select_table2.bar' ] ],
+ ],
+ ],
+ "INSERT INTO insert_table " .
+ "(field_insert,field) " .
+ "SELECT field_select,field2 " .
+ "FROM select_table1 LEFT JOIN select_table2 ON ((select_table1.foo = select_table2.bar)) " .
+ "WHERE field = '2' " .
+ "ORDER BY field",
+ "SELECT field_select AS field_insert,field2 AS field " .
+ "FROM select_table1 LEFT JOIN select_table2 ON ((select_table1.foo = select_table2.bar)) " .
+ "WHERE field = '2' ORDER BY field FOR UPDATE",
+ "INSERT INTO insert_table (field_insert,field) VALUES ('0','1')"
+ ],
];
}
];
}
+ /**
+ * @dataProvider provideUnionConditionPermutations
+ * @covers Database::unionConditionPermutations
+ */
+ public function testUnionConditionPermutations( $params, $expect ) {
+ if ( isset( $params['unionSupportsOrderAndLimit'] ) ) {
+ $this->database->setUnionSupportsOrderAndLimit( $params['unionSupportsOrderAndLimit'] );
+ }
+
+ $sql = trim( $this->database->unionConditionPermutations(
+ $params['table'],
+ $params['vars'],
+ $params['permute_conds'],
+ isset( $params['extra_conds'] ) ? $params['extra_conds'] : '',
+ 'FNAME',
+ isset( $params['options'] ) ? $params['options'] : [],
+ isset( $params['join_conds'] ) ? $params['join_conds'] : []
+ ) );
+ $this->assertEquals( $expect, $sql );
+ }
+
+ public static function provideUnionConditionPermutations() {
+ return [
+ // @codingStandardsIgnoreStart Generic.Files.LineLength.TooLong
+ [
+ [
+ 'table' => [ 'table1', 'table2' ],
+ 'vars' => [ 'field1', 'alias' => 'field2' ],
+ 'permute_conds' => [
+ 'field3' => [ 1, 2, 3 ],
+ 'duplicates' => [ 4, 5, 4 ],
+ 'empty' => [],
+ 'single' => [ 0 ],
+ ],
+ 'extra_conds' => 'table2.bar > 23',
+ 'options' => [
+ 'ORDER BY' => [ 'field1', 'alias' ],
+ 'INNER ORDER BY' => [ 'field1', 'field2' ],
+ 'LIMIT' => 100,
+ ],
+ 'join_conds' => [
+ 'table2' => [ 'JOIN', 'table1.foo_id = table2.foo_id' ],
+ ],
+ ],
+ "(SELECT field1,field2 AS alias FROM table1 JOIN table2 ON ((table1.foo_id = table2.foo_id)) WHERE field3 = '1' AND duplicates = '4' AND single = '0' AND (table2.bar > 23) ORDER BY field1,field2 LIMIT 100 ) UNION ALL " .
+ "(SELECT field1,field2 AS alias FROM table1 JOIN table2 ON ((table1.foo_id = table2.foo_id)) WHERE field3 = '1' AND duplicates = '5' AND single = '0' AND (table2.bar > 23) ORDER BY field1,field2 LIMIT 100 ) UNION ALL " .
+ "(SELECT field1,field2 AS alias FROM table1 JOIN table2 ON ((table1.foo_id = table2.foo_id)) WHERE field3 = '2' AND duplicates = '4' AND single = '0' AND (table2.bar > 23) ORDER BY field1,field2 LIMIT 100 ) UNION ALL " .
+ "(SELECT field1,field2 AS alias FROM table1 JOIN table2 ON ((table1.foo_id = table2.foo_id)) WHERE field3 = '2' AND duplicates = '5' AND single = '0' AND (table2.bar > 23) ORDER BY field1,field2 LIMIT 100 ) UNION ALL " .
+ "(SELECT field1,field2 AS alias FROM table1 JOIN table2 ON ((table1.foo_id = table2.foo_id)) WHERE field3 = '3' AND duplicates = '4' AND single = '0' AND (table2.bar > 23) ORDER BY field1,field2 LIMIT 100 ) UNION ALL " .
+ "(SELECT field1,field2 AS alias FROM table1 JOIN table2 ON ((table1.foo_id = table2.foo_id)) WHERE field3 = '3' AND duplicates = '5' AND single = '0' AND (table2.bar > 23) ORDER BY field1,field2 LIMIT 100 ) " .
+ "ORDER BY field1,alias LIMIT 100"
+ ],
+ [
+ [
+ 'table' => 'foo',
+ 'vars' => [ 'foo_id' ],
+ 'permute_conds' => [
+ 'bar' => [ 1, 2, 3 ],
+ ],
+ 'extra_conds' => [ 'baz' => null ],
+ 'options' => [
+ 'NOTALL',
+ 'ORDER BY' => [ 'foo_id' ],
+ 'LIMIT' => 25,
+ ],
+ ],
+ "(SELECT foo_id FROM foo WHERE bar = '1' AND baz IS NULL ORDER BY foo_id LIMIT 25 ) UNION " .
+ "(SELECT foo_id FROM foo WHERE bar = '2' AND baz IS NULL ORDER BY foo_id LIMIT 25 ) UNION " .
+ "(SELECT foo_id FROM foo WHERE bar = '3' AND baz IS NULL ORDER BY foo_id LIMIT 25 ) " .
+ "ORDER BY foo_id LIMIT 25"
+ ],
+ [
+ [
+ 'table' => 'foo',
+ 'vars' => [ 'foo_id' ],
+ 'permute_conds' => [
+ 'bar' => [ 1, 2, 3 ],
+ ],
+ 'extra_conds' => [ 'baz' => null ],
+ 'options' => [
+ 'NOTALL' => true,
+ 'ORDER BY' => [ 'foo_id' ],
+ 'LIMIT' => 25,
+ ],
+ 'unionSupportsOrderAndLimit' => false,
+ ],
+ "(SELECT foo_id FROM foo WHERE bar = '1' AND baz IS NULL ) UNION " .
+ "(SELECT foo_id FROM foo WHERE bar = '2' AND baz IS NULL ) UNION " .
+ "(SELECT foo_id FROM foo WHERE bar = '3' AND baz IS NULL ) " .
+ "ORDER BY foo_id LIMIT 25"
+ ],
+ [
+ [
+ 'table' => 'foo',
+ 'vars' => [ 'foo_id' ],
+ 'permute_conds' => [],
+ 'extra_conds' => [ 'baz' => null ],
+ 'options' => [
+ 'ORDER BY' => [ 'foo_id' ],
+ 'LIMIT' => 25,
+ ],
+ ],
+ "SELECT foo_id FROM foo WHERE baz IS NULL ORDER BY foo_id LIMIT 25"
+ ],
+ [
+ [
+ 'table' => 'foo',
+ 'vars' => [ 'foo_id' ],
+ 'permute_conds' => [
+ 'bar' => [],
+ ],
+ 'extra_conds' => [ 'baz' => null ],
+ 'options' => [
+ 'ORDER BY' => [ 'foo_id' ],
+ 'LIMIT' => 25,
+ ],
+ ],
+ "SELECT foo_id FROM foo WHERE baz IS NULL ORDER BY foo_id LIMIT 25"
+ ],
+ [
+ [
+ 'table' => 'foo',
+ 'vars' => [ 'foo_id' ],
+ 'permute_conds' => [
+ 'bar' => [ 1 ],
+ ],
+ 'options' => [
+ 'ORDER BY' => [ 'foo_id' ],
+ 'LIMIT' => 25,
+ 'OFFSET' => 150,
+ ],
+ ],
+ "SELECT foo_id FROM foo WHERE bar = '1' ORDER BY foo_id LIMIT 150,25"
+ ],
+ [
+ [
+ 'table' => 'foo',
+ 'vars' => [ 'foo_id' ],
+ 'permute_conds' => [],
+ 'extra_conds' => [ 'baz' => null ],
+ 'options' => [
+ 'ORDER BY' => [ 'foo_id' ],
+ 'LIMIT' => 25,
+ 'OFFSET' => 150,
+ 'INNER ORDER BY' => [ 'bar_id' ],
+ ],
+ ],
+ "(SELECT foo_id FROM foo WHERE baz IS NULL ORDER BY bar_id LIMIT 175 ) ORDER BY foo_id LIMIT 150,25"
+ ],
+ [
+ [
+ 'table' => 'foo',
+ 'vars' => [ 'foo_id' ],
+ 'permute_conds' => [],
+ 'extra_conds' => [ 'baz' => null ],
+ 'options' => [
+ 'ORDER BY' => [ 'foo_id' ],
+ 'LIMIT' => 25,
+ 'OFFSET' => 150,
+ 'INNER ORDER BY' => [ 'bar_id' ],
+ ],
+ 'unionSupportsOrderAndLimit' => false,
+ ],
+ "SELECT foo_id FROM foo WHERE baz IS NULL ORDER BY foo_id LIMIT 150,25"
+ ],
+ // @codingStandardsIgnoreEnd
+ ];
+ }
+
/**
* @covers Database::commit
*/
dépôts / lhc / web / wiklou.git / blobdiff