getSql() public method

Retrieve the SQL statement
Since: 9.1
public getSql ( )
Example #1
0
 /**
  *@covers ::getEntitiesRestrictRequest
  *@covers ::getEntitiesRestrictCriteria
  */
 public function testGetEntityRestrict()
 {
     $this->Login();
     // See all, really all
     $_SESSION['glpishowallentities'] = 1;
     // will be restored by setEntity call
     $this->assertEmpty(getEntitiesRestrictRequest('AND', 'glpi_computers'));
     $it = new DBmysqlIterator(NULL, 'glpi_computers', getEntitiesRestrictCriteria('glpi_computers'));
     $this->assertEquals('SELECT * FROM `glpi_computers`', $it->getSql());
     // See all
     $this->setEntity('_test_root_entity', true);
     $this->assertEquals("WHERE ( `glpi_computers`.`entities_id` IN ('1', '2', '3')  ) ", getEntitiesRestrictRequest('WHERE', 'glpi_computers'));
     $it = new DBmysqlIterator(NULL, 'glpi_computers', getEntitiesRestrictCriteria('glpi_computers'));
     $this->assertEquals('SELECT * FROM `glpi_computers` WHERE `glpi_computers`.`entities_id` IN (1, 2, 3)', $it->getSql());
     // Root entity
     $this->setEntity('_test_root_entity', false);
     $this->assertEquals("WHERE ( `glpi_computers`.`entities_id` IN ('1')  ) ", getEntitiesRestrictRequest('WHERE', 'glpi_computers'));
     $it = new DBmysqlIterator(NULL, 'glpi_computers', getEntitiesRestrictCriteria('glpi_computers'));
     $this->assertEquals('SELECT * FROM `glpi_computers` WHERE `glpi_computers`.`entities_id` IN (1)', $it->getSql());
     // Child
     $this->setEntity('_test_child_1', false);
     $this->assertEquals("WHERE ( `glpi_computers`.`entities_id` IN ('2')  ) ", getEntitiesRestrictRequest('WHERE', 'glpi_computers'));
     $it = new DBmysqlIterator(NULL, 'glpi_computers', getEntitiesRestrictCriteria('glpi_computers'));
     $this->assertEquals('SELECT * FROM `glpi_computers` WHERE `glpi_computers`.`entities_id` IN (2)', $it->getSql());
     // Child without table
     $this->assertEquals("WHERE ( `entities_id` IN ('2')  ) ", getEntitiesRestrictRequest('WHERE'));
     $it = new DBmysqlIterator(NULL, 'glpi_computers', getEntitiesRestrictCriteria());
     $this->assertEquals('SELECT * FROM `glpi_computers` WHERE `entities_id` IN (2)', $it->getSql());
     // Child + parent
     $this->setEntity('_test_child_2', false);
     $this->assertEquals("WHERE ( `glpi_computers`.`entities_id` IN ('3')  OR (`glpi_computers`.`is_recursive`='1' AND `glpi_computers`.`entities_id` IN ('0','1')) ) ", getEntitiesRestrictRequest('WHERE', 'glpi_computers', '', '', true));
     $it = new DBmysqlIterator(NULL, 'glpi_computers', getEntitiesRestrictCriteria('glpi_computers', '', '', true));
     $this->assertEquals('SELECT * FROM `glpi_computers` WHERE (`glpi_computers`.`entities_id` IN (3) OR (`glpi_computers`.`is_recursive` = 1 AND `glpi_computers`.`entities_id` IN (0, 1)))', $it->getSql());
     //Child + parent on glpi_entities
     $it = new DBmysqlIterator(NULL, 'glpi_entities', getEntitiesRestrictCriteria('glpi_entities', '', '', true));
     $this->assertEquals('SELECT * FROM `glpi_entities` WHERE (`glpi_entities`.`id` IN (3, 0, 1))', $it->getSql());
     //Child + parent -- automatic recusrivity detection
     $it = new DBmysqlIterator(NULL, 'glpi_computers', getEntitiesRestrictCriteria('glpi_computers', '', '', 'auto'));
     $this->assertEquals('SELECT * FROM `glpi_computers` WHERE (`glpi_computers`.`entities_id` IN (3) OR (`glpi_computers`.`is_recursive` = 1 AND `glpi_computers`.`entities_id` IN (0, 1)))', $it->getSql());
     // Child + parent without table
     $this->assertEquals("WHERE ( `entities_id` IN ('3')  OR (`is_recursive`='1' AND `entities_id` IN ('0','1')) ) ", getEntitiesRestrictRequest('WHERE', '', '', '', true));
     $it = new DBmysqlIterator(NULL, 'glpi_computers', getEntitiesRestrictCriteria('', '', '', true));
     $this->assertEquals('SELECT * FROM `glpi_computers` WHERE (`entities_id` IN (3) OR (`is_recursive` = 1 AND `entities_id` IN (0, 1)))', $it->getSql());
     $it = new DBmysqlIterator(NULL, 'glpi_entities', getEntitiesRestrictCriteria('glpi_entities', '', 3, true));
     $this->assertEquals('SELECT * FROM `glpi_entities` WHERE (`glpi_entities`.`id` IN (3, 0, 1))', $it->getSql());
     $it = new DBmysqlIterator(NULL, 'glpi_entities', getEntitiesRestrictCriteria('glpi_entities', '', 7, true));
     $this->assertEquals('SELECT * FROM `glpi_entities` WHERE `glpi_entities`.`id` = 7', $it->getSql());
 }
 public function testModern()
 {
     $req = ['SELECT' => ['a', 'b'], 'FROM' => 'foo', 'WHERE' => ['c' => 1]];
     $sql = "SELECT `a`, `b` FROM `foo` WHERE `c` = 1";
     $it = new DBmysqlIterator(NULL, $req);
     $this->assertEquals($sql, $it->getSql(), 'Mondern syntax');
 }
Example #3
0
 public function testLogical()
 {
     $it = new DBmysqlIterator(NULL, ['foo'], [['a' => 1, 'b' => 2]]);
     $this->assertEquals('SELECT * FROM `foo` WHERE (`a` = 1 AND `b` = 2)', $it->getSql(), 'Logical implicit AND');
     $it = new DBmysqlIterator(NULL, ['foo'], ['AND' => ['a' => 1, 'b' => 2]]);
     $this->assertEquals('SELECT * FROM `foo` WHERE (`a` = 1 AND `b` = 2)', $it->getSql(), 'Logical AND');
     $it = new DBmysqlIterator(NULL, ['foo'], ['OR' => ['a' => 1, 'b' => 2]]);
     $this->assertEquals('SELECT * FROM `foo` WHERE (`a` = 1 OR `b` = 2)', $it->getSql(), 'Logical OR');
     $it = new DBmysqlIterator(NULL, ['foo'], ['NOT' => ['a' => 1, 'b' => 2]]);
     $this->assertEquals('SELECT * FROM `foo` WHERE NOT (`a` = 1 AND `b` = 2)', $it->getSql(), 'Logical NOT');
     $crit = ['WHERE' => ['a' => 1, 'OR' => ['b' => 2, 'NOT' => ['c' => [2, 3], 'AND' => ['d' => 4, 'e' => 5]]]]];
     $it = new DBmysqlIterator(NULL, ['foo'], $crit);
     $this->assertEquals("SELECT * FROM `foo` WHERE `a` = 1 AND (`b` = 2 OR NOT (`c` IN (2, 3) AND (`d` = 4 AND `e` = 5)))", $it->getSql(), 'Complex case');
 }