Proyectos de Subversion Moodle

<?php
// This file is part of Moodle - http://moodle.org/
//
// Moodle is free software: you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation, either version 3 of the License, or
// (at your option) any later version.
//
// Moodle is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with Moodle.  If not, see <http://www.gnu.org/licenses/>.

/**
 * Test specific features of the Postgres dml.
 *
 * @package core
 * @category test
 * @copyright 2020 Ruslan Kabalin
 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
 */

namespace core;

use stdClass, ReflectionClass;
use moodle_database, pgsql_native_moodle_database;
use xmldb_table;
use moodle_exception;

/**
 * Test specific features of the Postgres dml.
 *
 * @package core
 * @category test
 * @copyright 2020 Ruslan Kabalin
 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
 * @covers  \pgsql_native_moodle_database
 */
class pgsql_native_moodle_database_test extends \advanced_testcase {

    /**
     * Setup before class.
     */
    public static function setUpBeforeClass(): void {
        global $CFG;
        require_once($CFG->libdir.'/dml/pgsql_native_moodle_database.php');
    }

    /**
     * Set up.
     */
    public function setUp(): void {
        global $DB;
        parent::setUp();
        // Skip tests if not using Postgres.
        if (!($DB instanceof pgsql_native_moodle_database)) {
            $this->markTestSkipped('Postgres-only test');
        }
    }

    /**
     * Get a xmldb_table object for testing, deleting any existing table
     * of the same name, for example if one was left over from a previous test
     * run that crashed.
     *
     * @param string $suffix table name suffix, use if you need more test tables
     * @return xmldb_table the table object.
     */
    private function get_test_table($suffix = ''): xmldb_table {
        $tablename = "test_table";
        if ($suffix !== '') {
            $tablename .= $suffix;
        }

        $table = new xmldb_table($tablename);
        $table->setComment("This is a test'n drop table. You can drop it safely");
        return $table;
    }

    /**
     * Find out the current index used for unique SQL_PARAMS_NAMED.
     *
     * @return int
     */
    private function get_current_index(): int {
        global $DB;
        $reflector = new ReflectionClass($DB);
        $property = $reflector->getProperty('inorequaluniqueindex');
        return (int) $property->getValue($DB);
    }

    public function test_get_in_or_equal_below_limit(): void {
        global $DB;
        // Just less than 65535 values, expect fallback to parent method.
        $invalues = range(1, 65533);
        list($usql, $params) = $DB->get_in_or_equal($invalues);
        $this->assertSame('IN ('.implode(',', array_fill(0, count($invalues), '?')).')', $usql);
        $this->assertEquals(count($invalues), count($params));
        foreach ($params as $key => $value) {
            $this->assertSame($invalues[$key], $value);
        }
    }

    public function test_get_in_or_equal_single_array_value(): void {
        global $DB;
        // Single value (in an array), expect fallback to parent method.
        $invalues = array('value1');
        list($usql, $params) = $DB->get_in_or_equal($invalues);
        $this->assertEquals("= ?", $usql);
        $this->assertCount(1, $params);
        $this->assertEquals($invalues[0], $params[0]);
    }

    public function test_get_in_or_equal_single_scalar_value(): void {
        global $DB;
        // Single value (scalar), expect fallback to parent method.
        $invalue = 'value1';
        list($usql, $params) = $DB->get_in_or_equal($invalue);
        $this->assertEquals("= ?", $usql);
        $this->assertCount(1, $params);
        $this->assertEquals($invalue, $params[0]);
    }

    public function test_get_in_or_equal_multiple_int_value(): void {
        global $DB;
        // 65535 values, int.
        $invalues = range(1, 65535);
        list($usql, $params) = $DB->get_in_or_equal($invalues);
        $this->assertSame('IN (VALUES ('.implode('),(', array_fill(0, count($invalues), '?::bigint')).'))', $usql);
        $this->assertEquals($params, $invalues);
    }

    public function test_get_in_or_equal_multiple_int_value_not_equal(): void {
        global $DB;
        // 65535 values, not equal, int.
        $invalues = range(1, 65535);
        list($usql, $params) = $DB->get_in_or_equal($invalues, SQL_PARAMS_QM, 'param', false);
        $this->assertSame('NOT IN (VALUES ('.implode('),(', array_fill(0, count($invalues), '?::bigint')).'))', $usql);
        $this->assertEquals($params, $invalues);
    }

    public function test_get_in_or_equal_named_int_value_default_name(): void {
        global $DB;
        // 65535 values, int, SQL_PARAMS_NAMED.
        $index = $this->get_current_index();
        $invalues = range(1, 65535);
        list($usql, $params) = $DB->get_in_or_equal($invalues, SQL_PARAMS_NAMED);
        $regex = '/^'.
            preg_quote('IN (VALUES (:param'.$index.'::bigint),(:param'.++$index.'::bigint),(:param'.++$index.'::bigint)').'/';
        $this->assertMatchesRegularExpression($regex, $usql);
        foreach ($params as $value) {
            $this->assertEquals(current($invalues), $value);
            next($invalues);
        }
    }

    public function test_get_in_or_equal_named_int_value_specified_name(): void {
        global $DB;
        // 65535 values, int, SQL_PARAMS_NAMED, define param name.
        $index = $this->get_current_index();
        $invalues = range(1, 65535);
        list($usql, $params) = $DB->get_in_or_equal($invalues, SQL_PARAMS_NAMED, 'ppp');
        // We are in same DBI instance, expect uniqie param indexes.
        $regex = '/^'.
            preg_quote('IN (VALUES (:ppp'.$index.'::bigint),(:ppp'.++$index.'::bigint),(:ppp'.++$index.'::bigint)').'/';
        $this->assertMatchesRegularExpression($regex, $usql);
        foreach ($params as $value) {
            $this->assertEquals(current($invalues), $value);
            next($invalues);
        }
    }

    public function test_get_in_or_equal_named_scalar_value_specified_name(): void {
        global $DB;
        // 65535 values, string.
        $invalues = array_fill(1, 65535, 'abc');
        list($usql, $params) = $DB->get_in_or_equal($invalues);
        $this->assertMatchesRegularExpression('/^' . preg_quote('IN (VALUES (?::text),(?::text),(?::text)') . '/', $usql);
        foreach ($params as $value) {
            $this->assertEquals(current($invalues), $value);
            next($invalues);
        }
    }

    public function test_get_in_or_equal_query_use(): void {
        global $DB;
        $this->resetAfterTest();
        $dbman = $DB->get_manager();
        $table = $this->get_test_table();
        $tablename = $table->getName();

        $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
        $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
        $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null);
        $table->add_field('content', XMLDB_TYPE_TEXT, 'big', null, XMLDB_NOTNULL);
        $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id'));
        $dbman->create_table($table);

        $rec1 = ['course' => 3, 'content' => 'hello', 'name' => 'xyz'];
        $DB->insert_record($tablename, $rec1);
        $rec2 = ['course' => 3, 'content' => 'world', 'name' => 'abc'];
        $DB->insert_record($tablename, $rec2);
        $rec3 = ['course' => 5, 'content' => 'hello', 'name' => 'xyz'];
        $DB->insert_record($tablename, $rec3);
        $rec4 = ['course' => 6, 'content' => 'universe'];
        $DB->insert_record($tablename, $rec4);

        $currentcount = $DB->count_records($tablename);

        // Getting all 4.
        $values = range(1, 65535);
        list($insql, $inparams) = $DB->get_in_or_equal($values);
        $sql = "SELECT *
                  FROM {{$tablename}}
                 WHERE id $insql
              ORDER BY id ASC";
        $this->assertCount($currentcount, $DB->get_records_sql($sql, $inparams));

        // Getting 'hello' records (text).
        $values = array_fill(1, 65535, 'hello');
        list($insql, $inparams) = $DB->get_in_or_equal($values);
        $sql = "SELECT *
                  FROM {{$tablename}}
                 WHERE content $insql
              ORDER BY id ASC";
        $result = $DB->get_records_sql($sql, $inparams);
        $this->assertCount(2, $result);
        $this->assertEquals([1, 3], array_keys($result));

        // Getting NOT 'hello' records (text).
        $values = array_fill(1, 65535, 'hello');
        list($insql, $inparams) = $DB->get_in_or_equal($values, SQL_PARAMS_QM, 'param', false);
        $sql = "SELECT *
                  FROM {{$tablename}}
                 WHERE content $insql
              ORDER BY id ASC";
        $result = $DB->get_records_sql($sql, $inparams);
        $this->assertCount(2, $result);
        $this->assertEquals([2, 4], array_keys($result));

        // Getting 'xyz' records (char and NULL mix).
        $values = array_fill(1, 65535, 'xyz');
        list($insql, $inparams) = $DB->get_in_or_equal($values);
        $sql = "SELECT *
                  FROM {{$tablename}}
                 WHERE name $insql
              ORDER BY id ASC";
        $result = $DB->get_records_sql($sql, $inparams);
        $this->assertCount(2, $result);
        $this->assertEquals([1, 3], array_keys($result));

        // Getting NOT 'xyz' records (char and NULL mix).
        $values = array_fill(1, 65535, 'xyz');
        list($insql, $inparams) = $DB->get_in_or_equal($values, SQL_PARAMS_QM, 'param', false);
        $sql = "SELECT *
                  FROM {{$tablename}}
                 WHERE name $insql
              ORDER BY id ASC";
        $result = $DB->get_records_sql($sql, $inparams);
        // NULL will not be in result.
        $this->assertCount(1, $result);
        $this->assertEquals([2], array_keys($result));

        // Getting numbeic records.
        $values = array_fill(1, 65535, 3);
        list($insql, $inparams) = $DB->get_in_or_equal($values);
        $sql = "SELECT *
                  FROM {{$tablename}}
                 WHERE course $insql
              ORDER BY id ASC";
        $result = $DB->get_records_sql($sql, $inparams);
        $this->assertCount(2, $result);
        $this->assertEquals([1, 2], array_keys($result));

        // Getting numbeic records with NOT condition.
        $values = array_fill(1, 65535, 3);
        list($insql, $inparams) = $DB->get_in_or_equal($values, SQL_PARAMS_QM, 'param', false);
        $sql = "SELECT *
                  FROM {{$tablename}}
                 WHERE course $insql
              ORDER BY id ASC";
        $result = $DB->get_records_sql($sql, $inparams);
        $this->assertCount(2, $result);
        $this->assertEquals([3, 4], array_keys($result));
    }

    public function test_get_in_or_equal_big_table_query(): void {
        global $DB;
        $this->resetAfterTest();
        $dbman = $DB->get_manager();

        $table = $this->get_test_table();
        $tablename = $table->getName();

        $table->add_field('id', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, XMLDB_SEQUENCE, null);
        $table->add_field('course', XMLDB_TYPE_INTEGER, '10', null, XMLDB_NOTNULL, null, '0');
        $table->add_field('oneint', XMLDB_TYPE_INTEGER, '10', null, null, null, 100);
        $table->add_field('onenum', XMLDB_TYPE_NUMBER, '10,2', null, null, null, 200);
        $table->add_field('onechar', XMLDB_TYPE_CHAR, '100', null, null, null, 'onestring');
        $table->add_field('onetext', XMLDB_TYPE_TEXT, 'big', null, null, null);
        $table->add_key('primary', XMLDB_KEY_PRIMARY, ['id']);
        $dbman->create_table($table);

        $record = new stdClass();
        $record->course = 1;
        $record->oneint = null;
        $record->onenum = 1.0;
        $record->onechar = 'a';
        $record->onetext = 'aaa';

        $records = [];
        for ($i = 1; $i <= 65535; $i++) {
            $rec = clone($record);
            $rec->oneint = $i;
            $records[$i] = $rec;
        }
        // Populate table with 65535 records.
        $DB->insert_records($tablename, $records);
        // And one more record.
        $record->oneint = -1;
        $DB->insert_record($tablename, $record);

        // Check we can fetch all.
        $values = range(1, 65535);
        list($insql, $inparams) = $DB->get_in_or_equal($values);
        $sql = "SELECT *
                  FROM {{$tablename}}
                 WHERE oneint $insql
              ORDER BY id ASC";
        $stored = $DB->get_records_sql($sql, $inparams);

        // Check we got correct set of records.
        $this->assertCount(65535, $stored);
        $oneint = array_column($stored, 'oneint');
        $this->assertEquals($values, $oneint);

        // Check we can fetch all, SQL_PARAMS_NAMED.
        $values = range(1, 65535);
        list($insql, $inparams) = $DB->get_in_or_equal($values, SQL_PARAMS_NAMED);
        $sql = "SELECT *
                  FROM {{$tablename}}
                 WHERE oneint $insql
              ORDER BY id ASC";
        $stored = $DB->get_records_sql($sql, $inparams);

        // Check we got correct set of records.
        $this->assertCount(65535, $stored);
        $oneint = array_column($stored, 'oneint');
        $this->assertEquals($values, $oneint);

        // Check we can fetch one using NOT IN.
        list($insql, $inparams) = $DB->get_in_or_equal($values, SQL_PARAMS_QM, 'param', false);
        $sql = "SELECT *
                  FROM {{$tablename}}
                 WHERE oneint $insql
              ORDER BY id ASC";
        $stored = $DB->get_records_sql($sql, $inparams);

        // Check we got correct set of records.
        $this->assertCount(1, $stored);
        $oneint = array_column($stored, 'oneint');
        $this->assertEquals([-1], $oneint);
    }

    /**
     * SSL connection helper.
     *
     * @param mixed $ssl
     * @return resource|PgSql\Connection
     * @throws moodle_exception
     */
    public function new_connection($ssl) {
        global $DB;

        // Open new connection.
        $cfg = $DB->export_dbconfig();
        if (!isset($cfg->dboptions)) {
            $cfg->dboptions = [];
        }

        $cfg->dboptions['ssl'] = $ssl;

        // Get a separate disposable db connection handle with guaranteed 'readonly' config.
        $db2 = moodle_database::get_driver_instance($cfg->dbtype, $cfg->dblibrary);
        $db2->raw_connect($cfg->dbhost, $cfg->dbuser, $cfg->dbpass, $cfg->dbname, $cfg->prefix, $cfg->dboptions);

        $reflector = new ReflectionClass($db2);
        $rp = $reflector->getProperty('pgsql');
        return $rp->getValue($db2);
    }

    /**
     * Test SSL connection.
     *
     * @return void
     * @covers ::raw_connect
     */
    public function test_ssl_connection(): void {
        $pgconnerr = 'pg_connect(): Unable to connect to PostgreSQL server:';

        try {
            $pgsql = $this->new_connection('require');
            // Either connect ...
            $this->assertNotNull($pgsql);
        } catch (moodle_exception $e) {
            // ... or fail with SSL not supported.
            $this->assertStringContainsString($pgconnerr, $e->debuginfo);
            $this->assertStringContainsString('server does not support SSL', $e->debuginfo);
            $this->markTestSkipped('Postgres server does not support SSL. Unable to complete the test.');
            return;
        }

        try {
            $pgsql = $this->new_connection('verify-full');
            // Either connect ...
            $this->assertNotNull($pgsql);
        } catch (moodle_exception $e) {
            // ... or fail with invalid cert.
            $this->assertStringContainsString($pgconnerr, $e->debuginfo);
            $this->assertStringContainsString('change sslmode to disable server certificate verification', $e->debuginfo);
        }

        $this->expectException(moodle_exception::class);
        $this->new_connection('invalid-mode');
    }
}