NonPublicSchemaTest.php

<?php

declare (strict_types=1);
namespace Drupal\Tests\pgsql\Kernel\pgsql;

use Drupal\Core\Database\Database;
use Drupal\Core\Database\Connection;
use Drupal\KernelTests\Core\Database\DatabaseTestSchemaDataTrait;
use Drupal\KernelTests\Core\Database\DatabaseTestSchemaInstallTrait;
use Drupal\KernelTests\Core\Database\DriverSpecificKernelTestBase;
// cSpell:ignore nspname schemaname upserting indexdef

/**
 * Tests schema API for non-public schema for the PostgreSQL driver.
 *
 * @group Database
 * @coversDefaultClass \Drupal\pgsql\Driver\Database\pgsql\Schema
 */
class NonPublicSchemaTest extends DriverSpecificKernelTestBase {
  use DatabaseTestSchemaDataTrait;
  use DatabaseTestSchemaInstallTrait;
  
  /**
   * The database connection for testing.
   *
   * @var \Drupal\Core\Database\Connection
   */
  protected Connection $testingFakeConnection;
  
  /**
   * {@inheritdoc}
   */
  protected function setUp() : void {
    parent::setUp();
    // Create a connection to the non-public schema.
    $info = Database::getConnectionInfo('default');
    $info['default']['schema'] = 'testing_fake';
    Database::getConnection()->query('CREATE SCHEMA IF NOT EXISTS testing_fake');
    Database::addConnectionInfo('default', 'testing_fake', $info['default']);
    $this->testingFakeConnection = Database::getConnection('testing_fake', 'default');
    $table_specification = [
      'description' => 'Schema table description may contain "quotes" and could be long—very long indeed.',
      'fields' => [
        'id' => [
          'type' => 'serial',
          'not null' => TRUE,
        ],
        'test_field' => [
          'type' => 'int',
          'not null' => TRUE,
        ],
      ],
      'primary key' => [
        'id',
      ],
    ];
    $this->testingFakeConnection
      ->schema()
      ->createTable('faking_table', $table_specification);
    $this->testingFakeConnection
      ->insert('faking_table')
      ->fields([
      'id' => '1',
      'test_field' => '123',
    ])
      ->execute();
    $this->testingFakeConnection
      ->insert('faking_table')
      ->fields([
      'id' => '2',
      'test_field' => '456',
    ])
      ->execute();
    $this->testingFakeConnection
      ->insert('faking_table')
      ->fields([
      'id' => '3',
      'test_field' => '789',
    ])
      ->execute();
  }
  
  /**
   * {@inheritdoc}
   */
  protected function tearDown() : void {
    // We overwrite this function because the regular teardown will not drop the
    // tables from a specified schema.
    $tables = $this->testingFakeConnection
      ->schema()
      ->findTables('%');
    foreach ($tables as $table) {
      if ($this->testingFakeConnection
        ->schema()
        ->dropTable($table)) {
        unset($tables[$table]);
      }
    }
    $this->assertEmpty($this->testingFakeConnection
      ->schema()
      ->findTables('%'));
    Database::removeConnection('testing_fake');
    parent::tearDown();
  }
  
  /**
   * @covers ::extensionExists
   * @covers ::tableExists
   */
  public function testExtensionExists() : void {
    // Check if PG_trgm extension is present.
    $this->assertTrue($this->testingFakeConnection
      ->schema()
      ->extensionExists('pg_trgm'));
    // Asserting that the Schema testing_fake exist in the database.
    $this->assertCount(1, \Drupal::database()->query("SELECT * FROM pg_catalog.pg_namespace WHERE nspname = 'testing_fake'")
      ->fetchAll());
    $this->assertTrue($this->testingFakeConnection
      ->schema()
      ->tableExists('faking_table'));
    // Hardcoded assertion that we created the table in the non-public schema.
    $this->assertCount(1, $this->testingFakeConnection
      ->query("SELECT * FROM pg_tables WHERE schemaname = 'testing_fake' AND tablename = :prefixedTable", [
      ':prefixedTable' => $this->testingFakeConnection
        ->getPrefix() . "faking_table",
    ])
      ->fetchAll());
  }
  
  /**
   * @covers ::addField
   * @covers ::fieldExists
   * @covers ::dropField
   * @covers ::changeField
   */
  public function testField() : void {
    $this->testingFakeConnection
      ->schema()
      ->addField('faking_table', 'added_field', [
      'type' => 'int',
      'not null' => FALSE,
    ]);
    $this->assertTrue($this->testingFakeConnection
      ->schema()
      ->fieldExists('faking_table', 'added_field'));
    $this->testingFakeConnection
      ->schema()
      ->changeField('faking_table', 'added_field', 'changed_field', [
      'type' => 'int',
      'not null' => FALSE,
    ]);
    $this->assertFalse($this->testingFakeConnection
      ->schema()
      ->fieldExists('faking_table', 'added_field'));
    $this->assertTrue($this->testingFakeConnection
      ->schema()
      ->fieldExists('faking_table', 'changed_field'));
    $this->testingFakeConnection
      ->schema()
      ->dropField('faking_table', 'changed_field');
    $this->assertFalse($this->testingFakeConnection
      ->schema()
      ->fieldExists('faking_table', 'changed_field'));
  }
  
  /**
   * @covers \Drupal\Core\Database\Connection::insert
   * @covers \Drupal\Core\Database\Connection::select
   */
  public function testInsert() : void {
    $num_records_before = $this->testingFakeConnection
      ->query('SELECT COUNT(*) FROM {faking_table}')
      ->fetchField();
    $this->testingFakeConnection
      ->insert('faking_table')
      ->fields([
      'id' => '123',
      'test_field' => '55',
    ])
      ->execute();
    // Testing that the insert is correct.
    $results = $this->testingFakeConnection
      ->select('faking_table')
      ->fields('faking_table')
      ->condition('id', '123')
      ->execute()
      ->fetchAll();
    $this->assertIsArray($results);
    $num_records_after = $this->testingFakeConnection
      ->query('SELECT COUNT(*) FROM {faking_table}')
      ->fetchField();
    $this->assertEquals($num_records_before + 1, $num_records_after, 'Merge inserted properly.');
    $this->assertSame('123', $results[0]->id);
    $this->assertSame('55', $results[0]->test_field);
  }
  
  /**
   * @covers \Drupal\Core\Database\Connection::update
   */
  public function testUpdate() : void {
    $updated_record = $this->testingFakeConnection
      ->update('faking_table')
      ->fields([
      'test_field' => 321,
    ])
      ->condition('id', 1)
      ->execute();
    $this->assertSame(1, $updated_record, 'Updated 1 record.');
    $updated_results = $this->testingFakeConnection
      ->select('faking_table')
      ->fields('faking_table')
      ->condition('id', '1')
      ->execute()
      ->fetchAll();
    $this->assertSame('321', $updated_results[0]->test_field);
  }
  
  /**
   * @covers \Drupal\Core\Database\Connection::upsert
   */
  public function testUpsert() : void {
    $num_records_before = $this->testingFakeConnection
      ->query('SELECT COUNT(*) FROM {faking_table}')
      ->fetchField();
    $upsert = $this->testingFakeConnection
      ->upsert('faking_table')
      ->key('id')
      ->fields([
      'id',
      'test_field',
    ]);
    // Upserting a new row.
    $upsert->values([
      'id' => '456',
      'test_field' => '444',
    ]);
    // Upserting an existing row.
    $upsert->values([
      'id' => '1',
      'test_field' => '898',
    ]);
    $result = $upsert->execute();
    $this->assertSame(2, $result, 'The result of the upsert operation should report that at exactly two rows were affected.');
    $num_records_after = $this->testingFakeConnection
      ->query('SELECT COUNT(*) FROM {faking_table}')
      ->fetchField();
    $this->assertEquals($num_records_before + 1, $num_records_after, 'Merge inserted properly.');
    // Check if new row has been added with upsert.
    $result = $this->testingFakeConnection
      ->query('SELECT * FROM {faking_table} WHERE [id] = :id', [
      ':id' => '456',
    ])
      ->fetch();
    $this->assertEquals('456', $result->id, 'ID set correctly.');
    $this->assertEquals('444', $result->test_field, 'test_field set correctly.');
    // Check if new row has been edited with upsert.
    $result = $this->testingFakeConnection
      ->query('SELECT * FROM {faking_table} WHERE [id] = :id', [
      ':id' => '1',
    ])
      ->fetch();
    $this->assertEquals('1', $result->id, 'ID set correctly.');
    $this->assertEquals('898', $result->test_field, 'test_field set correctly.');
  }
  
  /**
   * @covers \Drupal\Core\Database\Connection::merge
   */
  public function testMerge() : void {
    $num_records_before = $this->testingFakeConnection
      ->query('SELECT COUNT(*) FROM {faking_table}')
      ->fetchField();
    $this->testingFakeConnection
      ->merge('faking_table')
      ->key('id', '789')
      ->fields([
      'test_field' => 343,
    ])
      ->execute();
    $num_records_after = $this->testingFakeConnection
      ->query('SELECT COUNT(*) FROM {faking_table}')
      ->fetchField();
    $this->assertEquals($num_records_before + 1, $num_records_after, 'Merge inserted properly.');
    $merge_results = $this->testingFakeConnection
      ->select('faking_table')
      ->fields('faking_table')
      ->condition('id', '789')
      ->execute()
      ->fetchAll();
    $this->assertSame('789', $merge_results[0]->id);
    $this->assertSame('343', $merge_results[0]->test_field);
  }
  
  /**
   * @covers \Drupal\Core\Database\Connection::delete
   * @covers \Drupal\Core\Database\Connection::truncate
   */
  public function testDelete() : void {
    $num_records_before = $this->testingFakeConnection
      ->query('SELECT COUNT(*) FROM {faking_table}')
      ->fetchField();
    $num_deleted = $this->testingFakeConnection
      ->delete('faking_table')
      ->condition('id', 3)
      ->execute();
    $this->assertSame(1, $num_deleted, 'Deleted 1 record.');
    $num_records_after = $this->testingFakeConnection
      ->query('SELECT COUNT(*) FROM {faking_table}')
      ->fetchField();
    $this->assertEquals($num_records_before, $num_records_after + $num_deleted, 'Deletion adds up.');
    $num_records_before = $this->testingFakeConnection
      ->query("SELECT COUNT(*) FROM {faking_table}")
      ->fetchField();
    $this->assertNotEmpty($num_records_before);
    $this->testingFakeConnection
      ->truncate('faking_table')
      ->execute();
    $num_records_after = $this->testingFakeConnection
      ->query("SELECT COUNT(*) FROM {faking_table}")
      ->fetchField();
    $this->assertEquals(0, $num_records_after, 'Truncate really deletes everything.');
  }
  
  /**
   * @covers ::addIndex
   * @covers ::indexExists
   * @covers ::dropIndex
   */
  public function testIndex() : void {
    $this->testingFakeConnection
      ->schema()
      ->addIndex('faking_table', 'test_field', [
      'test_field',
    ], []);
    $this->assertTrue($this->testingFakeConnection
      ->schema()
      ->indexExists('faking_table', 'test_field'));
    $results = $this->testingFakeConnection
      ->query("SELECT * FROM pg_indexes WHERE indexname = :indexname", [
      ':indexname' => $this->testingFakeConnection
        ->getPrefix() . 'faking_table__test_field__idx',
    ])
      ->fetchAll();
    $this->assertCount(1, $results);
    $this->assertSame('testing_fake', $results[0]->schemaname);
    $this->assertSame($this->testingFakeConnection
      ->getPrefix() . 'faking_table', $results[0]->tablename);
    $this->assertStringContainsString('USING btree (test_field)', $results[0]->indexdef);
    $this->testingFakeConnection
      ->schema()
      ->dropIndex('faking_table', 'test_field');
    $this->assertFalse($this->testingFakeConnection
      ->schema()
      ->indexExists('faking_table', 'test_field'));
  }
  
  /**
   * @covers ::addUniqueKey
   * @covers ::indexExists
   * @covers ::dropUniqueKey
   */
  public function testUniqueKey() : void {
    $this->testingFakeConnection
      ->schema()
      ->addUniqueKey('faking_table', 'test_field', [
      'test_field',
    ]);
    // This should work, but currently indexExist() only searches for keys that
    // end with idx.
    // @todo remove comments when:
    //   https://www.drupal.org/project/drupal/issues/3325358 is committed.
    // phpcs:ignore
    // $this->assertTrue($this->testingFakeConnection->schema()->indexExists('faking_table', 'test_field'));
    $results = $this->testingFakeConnection
      ->query("SELECT * FROM pg_indexes WHERE indexname = :indexname", [
      ':indexname' => $this->testingFakeConnection
        ->getPrefix() . 'faking_table__test_field__key',
    ])
      ->fetchAll();
    // Check the unique key columns.
    $this->assertCount(1, $results);
    $this->assertSame('testing_fake', $results[0]->schemaname);
    $this->assertSame($this->testingFakeConnection
      ->getPrefix() . 'faking_table', $results[0]->tablename);
    $this->assertStringContainsString('USING btree (test_field)', $results[0]->indexdef);
    $this->testingFakeConnection
      ->schema()
      ->dropUniqueKey('faking_table', 'test_field');
    // This function will not work due to a the fact that indexExist() does not
    // search for keys without idx tag.
    // @todo remove comments when:
    //   https://www.drupal.org/project/drupal/issues/3325358 is committed.
    // phpcs:ignore
    // $this->assertFalse($this->testingFakeConnection->schema()->indexExists('faking_table', 'test_field'));
  }
  
  /**
   * @covers ::addPrimaryKey
   * @covers ::dropPrimaryKey
   */
  public function testPrimaryKey() : void {
    $this->testingFakeConnection
      ->schema()
      ->dropPrimaryKey('faking_table');
    $results = $this->testingFakeConnection
      ->query("SELECT * FROM pg_indexes WHERE schemaname = 'testing_fake'")
      ->fetchAll();
    $this->assertCount(0, $results);
    $this->testingFakeConnection
      ->schema()
      ->addPrimaryKey('faking_table', [
      'id',
    ]);
    $results = $this->testingFakeConnection
      ->query("SELECT * FROM pg_indexes WHERE schemaname = 'testing_fake'")
      ->fetchAll();
    $this->assertCount(1, $results);
    $this->assertSame('testing_fake', $results[0]->schemaname);
    $this->assertSame($this->testingFakeConnection
      ->getPrefix() . 'faking_table', $results[0]->tablename);
    $this->assertStringContainsString('USING btree (id)', $results[0]->indexdef);
    $find_primary_keys_columns = new \ReflectionMethod(get_class($this->testingFakeConnection
      ->schema()), 'findPrimaryKeyColumns');
    $results = $find_primary_keys_columns->invoke($this->testingFakeConnection
      ->schema(), 'faking_table');
    $this->assertCount(1, $results);
    $this->assertSame('id', $results[0]);
  }
  
  /**
   * @covers ::renameTable
   * @covers ::tableExists
   * @covers ::findTables
   * @covers ::dropTable
   */
  public function testTable() : void {
    $this->testingFakeConnection
      ->schema()
      ->renameTable('faking_table', 'new_faking_table');
    $tables = $this->testingFakeConnection
      ->schema()
      ->findTables('%');
    $result = $this->testingFakeConnection
      ->query("SELECT * FROM information_schema.tables WHERE table_schema = 'testing_fake'")
      ->fetchAll();
    $this->assertFalse($this->testingFakeConnection
      ->schema()
      ->tableExists('faking_table'));
    $this->assertTrue($this->testingFakeConnection
      ->schema()
      ->tableExists('new_faking_table'));
    $this->assertEquals($this->testingFakeConnection
      ->getPrefix() . 'new_faking_table', $result[0]->table_name);
    $this->assertEquals('testing_fake', $result[0]->table_schema);
    sort($tables);
    $this->assertEquals([
      'new_faking_table',
    ], $tables);
    $this->testingFakeConnection
      ->schema()
      ->dropTable('new_faking_table');
    $this->assertFalse($this->testingFakeConnection
      ->schema()
      ->tableExists('new_faking_table'));
    $this->assertCount(0, $this->testingFakeConnection
      ->query("SELECT * FROM information_schema.tables WHERE table_schema = 'testing_fake'")
      ->fetchAll());
  }

}