QueryGeneratorTest.php

<?php

require "./QueryGenerator.php";

/**
 * Designed to work with PHPUnit
 */
class QueryGeneratorTest extends PHPUnit\Framework\TestCase {
   public function testIncompleteQuery(): void {
      $incompleteQueryClauseSets = [
         'select' => [
            [],
            ['select'],
            ['from'],
         ],
         'insert' => [
            [],
            ['insert'],
            ['set'],
            ['insert', 'columns'],
            ['insert', 'values'],
            ['columns', 'values'],
         ],
         'replace' => [
            [],
            ['replace'],
            ['set'],
            ['replace', 'columns'],
            ['replace', 'values'],
            ['columns', 'values'],
         ],
         'update' => [
            [],
            ['update'],
            ['set'],
         ],
         'delete' => [
            [],
            ['delete'],
            ['from'],
         ],
      ];

      foreach ($incompleteQueryClauseSets as $clauseSets) {
         foreach ($clauseSets as $clauses) {
            $qGen = new QueryGenerator();
            foreach ($clauses as $clause) {
               $qGen->$clause('clause');
            }

            $build = function() use ($qGen): array {
               return $qGen->build();
            };
            $buildIncomplete = function() use ($qGen): array {
               return $qGen->skipValidation()->build();
            };

            $this->assertTrue((bool)$this->didThrowException($build));
            $this->assertFalse($this->didThrowException($buildIncomplete));
         }
      }
   }

   public function testCompleteQuery(): void {
      $completeQueryClauseSets = [
         'select' => [['select', 'from']],
         'insert' => [
            ['insert', 'set'],
            ['insert', 'columns', 'values'],
         ],
         'replace' => [
            ['replace', 'set'],
            ['replace', 'columns', 'values'],
         ],
         'update' => [['update', 'set']],
         'delete' => [['delete', 'from']],
      ];

      foreach ($completeQueryClauseSets as $clauseSets) {
         foreach ($clauseSets as $clauses) {
            $qGen = new QueryGenerator();
            foreach ($clauses as $clause) {
               $qGen->$clause('clause');
            }

            $build = function() use ($qGen): array {
               return $qGen->build();
            };
            $buildIncomplete = function() use ($qGen): array {
               return $qGen->skipValidation()->build();
            };

            $this->assertFalse($this->didThrowException($build));
            $this->assertFalse($this->didThrowException($buildIncomplete));
         }
      }
   }

   public function testSmallQuery(): void {
      $qGen = new QueryGenerator();
      $qGen->select(['field']);
      $qGen->from(['table']);

      $expectedQuery = <<<EOT
SELECT field
FROM table
EOT;
      $expectedParams = [];

      $this->assertQuery($qGen, $expectedQuery, $expectedParams);
   }

   public function testBigQuery(): void {
      $qGen = new QueryGenerator();
      $qGen->select(['field1', 'field2']);
      $qGen->from(['table1', 'table2']);
      $qGen->join([
         'INNER JOIN table3 USING (asdf)',
         'OUTER JOIN table4 ON foo = bar',
      ]);
      $qGen->where(['where1', 'where2 OR where3']);
      $qGen->group(['group1', 'group2']);
      $qGen->having(['having1 > ?', 'having2 < ?'], [0, 0]);
      $qGen->order(['order1', 'order2']);
      $qGen->limit([3]);
      $qGen->offset(['?'], [5]);

      $expectedQuery = <<<EOT
SELECT field1, field2
FROM table1, table2
INNER JOIN table3 USING (asdf)
OUTER JOIN table4 ON foo = bar
WHERE (where1) AND (where2 OR where3)
GROUP BY group1, group2
HAVING (having1 > ?) AND (having2 < ?)
ORDER BY order1, order2
LIMIT 3
OFFSET ?
EOT;
      $expectedParams = [0, 0, 5];

      $this->assertQuery($qGen, $expectedQuery, $expectedParams);
   }

   public function testUseOr(): void {
      $qGen = new QueryGenerator();
      $qGen->select('field')
           ->from('table')
           ->where('1')
           ->where('2')
           ->useOr();

      $expectedQuery = <<<EOT
SELECT field
FROM table
WHERE (1) OR (2)
EOT;
      $expectedParams = [];


      $this->assertQuery($qGen, $expectedQuery, $expectedParams);
   }

   public function testNestedGenerators(): void {
      $where = new QueryGenerator();
      $where->where('abcd', [1]);
      $qGen = new QueryGenerator();
      $qGen->select('field');
      $qGen->from('table');
      $qGen->where($where);

      $expectedQuery = <<<EOT
SELECT field
FROM table
WHERE ((abcd))
EOT;
      $expectedParams = [1];

      $this->assertQuery($qGen, $expectedQuery, $expectedParams);
   }

   public function testSingleArguments(): void {
      $qGen = new QueryGenerator();
      $qGen->select('field');
      $qGen->from('table');

      $expectedQuery = <<<EOT
SELECT field
FROM table
EOT;
      $expectedParams = [];

      $this->assertQuery($qGen, $expectedQuery, $expectedParams);
   }

   public function testModifiers(): void {
      $qGen = new QueryGenerator();
      $qGen->insert('table');
      $qGen->set('field = ?', 0);
      $qGen->modify('IGNORE');

      $expectedQuery = <<<EOT
INSERT IGNORE INTO table
SET field = ?
EOT;
      $expectedParams = [0];

      $this->assertQuery($qGen, $expectedQuery, $expectedParams);
   }

   public function testForUpdate(): void {
      $qGen = new QueryGenerator();
      $qGen->select('a');
      $qGen->from('b');
      $qGen->forUpdate();

      $expectedQuery = <<<EOT
SELECT a
FROM b
FOR UPDATE
EOT;

      $this->assertQuery($qGen, $expectedQuery, []);
   }

   public function testOnDuplicate(): void {
      $qGen = new QueryGenerator();
      $qGen->insert('a');
      $qGen->as('row');
      $qGen->set('b = 1');
      $qGen->set('c = 1');
      $qGen->duplicate('b = 2');

      $expectedQuery = <<<EOT
INSERT INTO a
SET b = 1, c = 1
AS row
ON DUPLICATE KEY UPDATE b = 2
EOT;

      $this->assertQuery($qGen, $expectedQuery, []);
   }

   public function testUnion(): void {
      $other = new QueryGenerator();
      $other->select('field');
      $other->from('table2');

      $qGen = new QueryGenerator();
      $qGen->select('field');
      $qGen->from('table');
      $qGen->union($other);

      $expectedQuery = <<<EOT
SELECT field
FROM table
UNION
SELECT field
FROM table2
EOT;

      $this->assertQuery($qGen, $expectedQuery, []);
   }

   public function testUnionAll(): void {
      $other = new QueryGenerator();
      $other->select('field');
      $other->from('table2');

      $qGen = new QueryGenerator();
      $qGen->select('field');
      $qGen->from('table');
      $qGen = $qGen->unionAll($other);

      $expectedQuery = <<<EOT
SELECT field
FROM table
UNION ALL
SELECT field
FROM table2
EOT;

      $this->assertQuery($qGen, $expectedQuery, []);
   }

   public function testMixedUnions(): void {
      $qGen = new QueryGenerator();
      $qGen->select('a');
      $qGen->from('b');
      $qGen->union('SELECT a FROM c');
      $qGen->unionAll('SELECT a FROM d');

      $expectedQuery = <<<EOT
SELECT a
FROM b
UNION
SELECT a FROM c
UNION ALL
SELECT a FROM d
EOT;

      $this->assertQuery($qGen, $expectedQuery, []);
   }

   public function testUnionStringOperand(): void {
      $qGen = new QueryGenerator();
      $qGen->select('id');
      $qGen->from('live');
      $qGen->union('SELECT id FROM archive WHERE x = ?', 5);

      $expectedQuery = <<<EOT
SELECT id
FROM live
UNION
SELECT id FROM archive WHERE x = ?
EOT;

      $this->assertQuery($qGen, $expectedQuery, [5]);
   }

   public function testUnionParamOrdering(): void {
      $other = new QueryGenerator();
      $other->select('a');
      $other->from('c');
      $other->where('y = ?', 2);

      $qGen = new QueryGenerator();
      $qGen->select('a');
      $qGen->from('b');
      $qGen->where('x = ?', 1);
      $qGen->union($other);
      $qGen->order('a');
      $qGen->limit(10);
      $qGen->offset('?', 5);

      $expectedQuery = <<<EOT
SELECT a
FROM b
WHERE (x = ?)
UNION
SELECT a
FROM c
WHERE (y = ?)
ORDER BY a
LIMIT 10
OFFSET ?
EOT;

      $this->assertQuery($qGen, $expectedQuery, [1, 2, 5]);
   }

   public function testUnionGeneratorOperandWithParamsThrows(): void {
      $other = new QueryGenerator();
      $other->select('a');
      $other->from('c');

      $qGen = new QueryGenerator();
      $qGen->select('a');
      $qGen->from('b');

      $this->expectException(Exception::class);
      $qGen->union($other, [1]);
   }

   public function testUnionOperandValidation(): void {
      $incomplete = new QueryGenerator();
      $incomplete->select('a');

      $qGen = new QueryGenerator();
      $qGen->select('a');
      $qGen->from('b');

      $union = function() use ($qGen, $incomplete): QueryGenerator {
         return $qGen->union($incomplete);
      };

      $this->assertTrue((bool)$this->didThrowException($union));
      $incomplete->skipValidation();
      $this->assertFalse($this->didThrowException($union));
   }

   public function assertQuery(QueryGenerator $qGen, string $expectedQuery, array $expectedParams): void {
      [$actualQuery, $actualParams] = $qGen->build();
      $this->assertEquals($expectedQuery, $actualQuery);
      $this->assertEquals($expectedParams, $actualParams);

      [$actualQuery, $actualParams] = $qGen->skipValidation()->build();
      $this->assertEquals($expectedQuery, $actualQuery);
      $this->assertEquals($expectedParams, $actualParams);
   }

   /**
    * @param callable $callback
    * @return non-falsy-string|bool
    */
   public function didThrowException($callback): string|bool {
      try {
         $callback();
         return false;
      } catch (MissingClauseException $e) {
         return $e->getMessage() ?: true;
      }
   }
}