Codeception · Naktibalda · Dec 3, 2022 · Dec 1, 2022 · Dec 2, 2022 · Dec 3, 2022
diff --git a/.gitattributes b/.gitattributes
@@ -5,3 +5,4 @@
 /Robofile.php    export-ignore
 /*.md            export-ignore
 /*.yml           export-ignore
+/*.Dockerfile    export-ignore
diff --git a/docker-compose.yml b/docker-compose.yml
@@ -0,0 +1,26 @@
+version: "3.9"
+
+services:
+  php81:
+    image: codeception-module-db-php81:2.2.0
+    build:
+      context: .
+      dockerfile: ./php81.Dockerfile
+    environment:
+      MYSQL_DSN: "mysql:host=host.docker.internal;port=3102;dbname=codeception"
+      MYSQL_USER: root
+      MYSQL_PASSWORD: codeception
+      XDEBUG_MODE: "debug"
+      XDEBUG_CONFIG: "client_host=host.docker.internal; client_port=9000; mode=debug; start_wih_request=1"
+      PHP_IDE_CONFIG: "serverName=codeception-module-db" # the name must be the same as in your PHP -> Server -> "name" field
+    volumes:
+      - ".:/var/www/html"
+
+  mariadb105:
+    image: mariadb:10.5
+    environment:
+      MARIADB_ROOT_PASSWORD: codeception
+      MARIADB_DATABASE: codeception
+    ports:
+      - "3102:3306"
+
diff --git a/php81.Dockerfile b/php81.Dockerfile
@@ -0,0 +1,33 @@
+FROM php:8.1-cli
+
+RUN apt-get update && \
+    apt-get install -y \
+        unzip \
+        wget \
+        git \
+        zlib1g-dev \
+        libzip-dev \
+		mariadb-client-10.5
+
+RUN docker-php-ext-install pdo pdo_mysql && docker-php-ext-enable pdo pdo_mysql
+RUN docker-php-ext-install mysqli && docker-php-ext-enable mysqli
+RUN docker-php-ext-install zip
+
+RUN pecl install xdebug-3.1.5 && \
+    echo zend_extension=xdebug.so > $PHP_INI_DIR/conf.d/xdebug.ini
+
+COPY --from=composer /usr/bin/composer /usr/bin/composer
+
+WORKDIR /var/www/html
+
+COPY composer.json .
+COPY composer.lock .
+
+RUN composer install --no-autoloader
+
+COPY . .
+
+RUN composer dump-autoload -o
+
+ENTRYPOINT ["tail"]
+CMD ["-f", "/dev/null"]
diff --git a/src/Codeception/Module/Db.php b/src/Codeception/Module/Db.php
@@ -529,7 +529,6 @@ private function readSql($databaseKey = null, $databaseConfig = null): void
     }
 
     /**
-     * @return bool|null|string|string[]
      * @throws ModuleConfigException
      */
     private function readSqlFile(string $filePath): ?string
@@ -729,7 +728,7 @@ public function _loadDump(string $databaseKey = null, array $databaseConfig = nu
         $databaseKey = empty($databaseKey) ?  self::DEFAULT_DATABASE : $databaseKey;
         $databaseConfig = empty($databaseConfig) ?  $this->config : $databaseConfig;
 
-        if ($databaseConfig['populator']) {
+        if (!empty($databaseConfig['populator'])) {
             $this->loadDumpUsingPopulator($databaseKey, $databaseConfig);
             return;
         }
@@ -759,8 +758,8 @@ protected function loadDumpUsingDriver(string $databaseKey): void
     }
 
     /**
-     * Inserts an SQL record into a database. This record will be erased after the test, 
-     * unless you've configured "skip_cleanup_if_failed", and the test fails. 
+     * Inserts an SQL record into a database. This record will be erased after the test,
+     * unless you've configured "skip_cleanup_if_failed", and the test fails.
      *
      * ```php
      * <?php
@@ -801,8 +800,15 @@ private function addInsertedRow(string $table, array $row, $id): void
         $primaryKey = $this->_getDriver()->getPrimaryKey($table);
         $primary = [];
         if ($primaryKey !== []) {
-            if ($id && count($primaryKey) === 1) {
-                $primary [$primaryKey[0]] = $id;
+            $filledKeys = array_intersect($primaryKey, array_keys($row));
+            $missingPrimaryKeyColumns = array_diff_key($primaryKey, $filledKeys);
+
+            if (count($missingPrimaryKeyColumns) === 0) {
+                $primary = array_intersect_key($row, array_flip($primaryKey));
+            } elseif (count($missingPrimaryKeyColumns) === 1) {
+                $primary = array_intersect_key($row, array_flip($primaryKey));
+                $missingColumn = reset($missingPrimaryKeyColumns);
+                $primary[$missingColumn] = $id;
             } else {
                 foreach ($primaryKey as $column) {
                     if (isset($row[$column])) {
@@ -949,6 +955,77 @@ public function grabFromDatabase(string $table, string $column, array $criteria
         return $this->proceedSeeInDatabase($table, $column, $criteria);
     }
 
+    /**
+     * Fetches a whole entry from a database.
+     * Make the test fail if the entry is not found.
+     * Provide table name, desired column and criteria.
+     *
+     * ``` php
+     * <?php
+     * $mail = $I->grabEntryFromDatabase('users', array('name' => 'Davert'));
+     * ```
+     * Comparison expressions can be used as well:
+     *
+     * ```php
+     * <?php
+     * $post = $I->grabEntryFromDatabase('posts', ['num_comments >=' => 100]);
+     * $user = $I->grabEntryFromDatabase('users', ['email like' => 'miles%']);
+     * ```
+     *
+     * Supported operators: `<`, `>`, `>=`, `<=`, `!=`, `like`.
+     *
+     * @return array Returns a single entry value
+     * @throws PDOException|Exception
+     */
+    public function grabEntryFromDatabase(string $table, array $criteria = []): array
+    {
+        $query      = $this->_getDriver()->select('*', $table, $criteria);
+        $parameters = array_values($criteria);
+        $this->debugSection('Query', $query);
+        $this->debugSection('Parameters', $parameters);
+        $sth = $this->_getDriver()->executeQuery($query, $parameters);
+
+        $result = $sth->fetch(PDO::FETCH_ASSOC, 0);
+
+        if ($result === false) {
+            throw new \AssertionError("No matching row found");
+        }
+
+        return $result;
+    }
+
+    /**
+     * Fetches a set of entries from a database.
+     * Provide table name and criteria.
+     *
+     * ``` php
+     * <?php
+     * $mail = $I->grabEntriesFromDatabase('users', array('name' => 'Davert'));
+     * ```
+     * Comparison expressions can be used as well:
+     *
+     * ```php
+     * <?php
+     * $post = $I->grabEntriesFromDatabase('posts', ['num_comments >=' => 100]);
+     * $user = $I->grabEntriesFromDatabase('users', ['email like' => 'miles%']);
+     * ```
+     *
+     * Supported operators: `<`, `>`, `>=`, `<=`, `!=`, `like`.
+     *
+     * @return array<array<string, mixed>> Returns an array of all matched rows
+     * @throws PDOException|Exception
+     */
+    public function grabEntriesFromDatabase(string $table, array $criteria = []): array
+    {
+        $query      = $this->_getDriver()->select('*', $table, $criteria);
+        $parameters = array_values($criteria);
+        $this->debugSection('Query', $query);
+        $this->debugSection('Parameters', $parameters);
+        $sth = $this->_getDriver()->executeQuery($query, $parameters);
+
+        return $sth->fetchAll(PDO::FETCH_ASSOC);
+    }
+
     /**
      * Returns the number of rows in a database
      *

diff --git a/tests/data/dumps/mysql.sql b/tests/data/dumps/mysql.sql
@@ -94,8 +94,25 @@ CREATE TABLE `no_pk` (
   `status` varchar(255) NOT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
+CREATE TABLE `auto_increment_not_on_pk` (
+    `id` int(11) NOT NULL,
+    `counter` int(11) NOT NULL,
+    PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE INDEX counter ON `auto_increment_not_on_pk` (counter);
+ALTER TABLE `auto_increment_not_on_pk`
+    MODIFY counter int AUTO_INCREMENT;
+
+
+CREATE TABLE `auto_increment_on_composite_pk` (
+    `id` int(11) NOT NULL,
+    `counter` int(11) AUTO_INCREMENT NOT NULL,
+PRIMARY KEY (`id`, `counter`)
+) ENGINE=MyISAM DEFAULT CHARSET=utf8;
+
 CREATE TABLE `empty_table` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `field` varchar(255),
   PRIMARY KEY(`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
diff --git a/tests/unit/Codeception/Module/Db/AbstractDbTest.php b/tests/unit/Codeception/Module/Db/AbstractDbTest.php
@@ -175,7 +175,7 @@ public function testLoadWithPopulator()
                 'cleanup'   => true,
             ]
         );
-        $this->module->_loadDump();
+        $this->module->_loadDump(null, $this->getConfig());
         $this->assertTrue($this->module->_isPopulated());
         $this->module->seeInDatabase('users', ['name' => 'davert']);
     }

diff --git a/tests/unit/Codeception/Module/Db/MySqlDbTest.php b/tests/unit/Codeception/Module/Db/MySqlDbTest.php
@@ -23,11 +23,13 @@ public function getPopulator(): string
     public function getConfig(): array
     {
         $host = getenv('MYSQL_HOST') ? getenv('MYSQL_HOST') : 'localhost';
+        $user = getenv('MYSQL_USER') ? getenv('MYSQL_USER') : 'root';
         $password = getenv('MYSQL_PASSWORD') ? getenv('MYSQL_PASSWORD') : '';
+        $dsn = getenv('MYSQL_DSN') ? getenv('MYSQL_DSN') : 'mysql:host='.$host.';dbname=codeception_test';
 
         return [
-            'dsn' => 'mysql:host='.$host.';dbname=codeception_test',
-            'user' => 'root',
+            'dsn' => $dsn,
+            'user' => $user,
             'password' => $password,
             'dump' => 'tests/data/dumps/mysql.sql',
             'reconnect' => true,
@@ -51,7 +53,7 @@ public function testConnectionIsResetOnEveryTestWhenReconnectIsTrue()
 
         // Simulate a test that runs
         $this->module->_before($testCase1);
-        
+
         $connection1 = $this->module->dbh->query('SELECT CONNECTION_ID()')->fetch(PDO::FETCH_COLUMN);
         $this->module->_after($testCase1);
 
@@ -83,14 +85,15 @@ public function testInitialQueriesAreExecuted()
         ];
         $this->module->_reconfigure($config);
         $this->module->_before(Stub::makeEmpty(TestInterface::class));
-        
+
         $usedDatabaseName = $this->module->dbh->query('SELECT DATABASE();')->fetch(PDO::FETCH_COLUMN);
 
         $this->assertSame($dbName, $usedDatabaseName);
     }
 
     public function testGrabColumnFromDatabase()
     {
+        $this->module->_beforeSuite();
         $emails = $this->module->grabColumnFromDatabase('users', 'email');
         $this->assertSame(
             [
@@ -101,4 +104,77 @@ public function testGrabColumnFromDatabase()
             ],
            $emails);
     }
+
+    public function testGrabEntryFromDatabaseShouldFailIfNotFound()
+    {
+        try {
+            $this->module->grabEntryFromDatabase('users', ['email' => 'doesnot@exist.info']);
+            $this->fail("should have thrown an exception");
+        } catch (\Throwable $t) {
+            $this->assertInstanceOf(AssertionError::class, $t);
+        }
+    }
+
+    public function testGrabEntryFromDatabaseShouldReturnASingleEntry()
+    {
+        $this->module->_beforeSuite();
+        $result = $this->module->grabEntryFromDatabase('users', ['is_active' => true]);
+
+        $this->assertArrayNotHasKey(0, $result);
+    }
+
+    public function testGrabEntryFromDatabaseShouldReturnAnAssocArray()
+    {
+        $this->module->_beforeSuite();
+        $result = $this->module->grabEntryFromDatabase('users', ['is_active' => true]);
+
+        $this->assertArrayHasKey('is_active', $result);
+    }
+
+    public function testGrabEntriesFromDatabaseShouldReturnAnEmptyArrayIfNoRowMatches()
+    {
+        $this->module->_beforeSuite();
+        $result = $this->module->grabEntriesFromDatabase('users', ['email' => 'doesnot@exist.info']);
+        $this->assertEquals([], $result);
+    }
+
+    public function testGrabEntriesFromDatabaseShouldReturnAllMatchedRows()
+    {
+        $this->module->_beforeSuite();
+        $result = $this->module->grabEntriesFromDatabase('users', ['is_active' => true]);
+
+        $this->assertCount(3, $result);
+    }
+
+    public function testGrabEntriesFromDatabaseShouldReturnASetOfAssocArray()
+    {
+        $this->module->_beforeSuite();
+        $result = $this->module->grabEntriesFromDatabase('users', ['is_active' => true]);
+
+        $this->assertEquals(true, array_key_exists('is_active', $result[0]));
+    }
+
+    public function testHaveInDatabaseAutoIncrementOnANonPrimaryKey()
+    {
+        $testData = [
+            'id' => 777,
+        ];
+        $this->module->haveInDatabase('auto_increment_not_on_pk', $testData);
+        $this->module->seeInDatabase('auto_increment_not_on_pk', $testData);
+        $this->module->_after(Stub::makeEmpty(TestInterface::class));
+
+        $this->module->dontSeeInDatabase('auto_increment_not_on_pk', $testData);
+    }
+
+    public function testHaveInDatabaseAutoIncrementOnCompositePrimaryKey()
+    {
+        $testData = [
+            'id' => 777,
+        ];
+        $this->module->haveInDatabase('auto_increment_on_composite_pk', $testData);
+        $this->module->seeInDatabase('auto_increment_on_composite_pk', $testData);
+        $this->module->_after(Stub::makeEmpty(TestInterface::class));
+
+        $this->module->dontSeeInDatabase('auto_increment_on_composite_pk', $testData);
+    }
 }