Compare database tables and models

Hello,

I would like to know if there is a way to check the current database tables and if the CI4 models implements all columns correctly.

My team and I are create, update many tables and we would like to do tests that check if our models linked to a table match its state (same number and same names for the columns).


Any help is welcome !

See https://github.com/tattersoftware/codeigniter4-schemas

Simple CI 4 project for beginners codeigniter-expenses ( topic )

Interesting!

In my understanding, CI4 Model don't know table column names except $allowedFields.
The column names are often hard coded in Model code.
So it seems difficult to check all column names in Model.

You can list the columns in a table:
https://www.codeigniter.com/user_guide/d...in-a-table

ci-phpunit-test | CodeIgniter Testing Guide | CodeIgniter 3 to 4 Upgrade Helper

Thank you for your answers,

Finally I created a base class that I can inherit with each test file associated with a model.

Here is my (draft) code:

---

PHP Code:

<?php

namespace Tests\Support\Models;

use CodeIgniter\Model;
use CodeIgniter\Test\CIUnitTestCase;
use CodeIgniter\Test\DatabaseTestTrait;
use PHPUnit\Framework\Attributes\DataProvider;

use function PHPUnit\Framework\assertNotEmpty;
use function PHPUnit\Framework\assertNotFalse;
use function PHPUnit\Framework\assertTrue;

abstract class BaseModelTest extends CIUnitTestCase
{
    use DatabaseTestTrait;

    /** @var Model */
    protected $model = null;
    private $tableFields = null;

    /**
    * DataProvider for testModel, to override
    * 
    * @return array<array<...>> Arrays of arrays of args
    * */
    public static function modelProvider(): array
    {
        // Example of a returned array structure, needs to be overrided
        return  [
            [ 'value' ]
        ];
    }


    #[DataProvider('modelProvider')]
    public function testModel(string $model)
    {
        $this->model = new $model();
        $table = $this->getPrivateProperty($this->model, 'table');
        $this->tableFields = $this->model->db->getFieldData($table);
        $this->checkPrimaryKey();
        $this->checkFields();
        assert(true);
    }


    private function checkPrimaryKey(): void
    {
        // Assert PK is defined
        $primaryKey = $this->getPrivateProperty($this->model, 'primaryKey');
        assertNotEmpty($primaryKey, "primaryKey is not defined in model");
        // Assert PK exists in DB
        $fieldNames = array_column($this->tableFields, 'name');
        assertNotFalse(array_search($primaryKey, $fieldNames), "PK '$primaryKey' not found in DB.");
    }


    protected function checkFields(): void
    {
        $allowedFields = $this->getPrivateProperty($this->model, 'allowedFields');
        $fieldNames = array_column($this->tableFields, 'name');
        foreach ($allowedFields as $field) {
            assertNotFalse(array_search($field, $fieldNames), "column '$field' not found in DB.");
        }
    }
} 


---

And here the implementation sample :

PHP Code:

<?php

use App\Models\MyWonderfulModel;
use Tests\Support\Models\BaseModelTest;

class PublicationModelTest extends BaseModelTest
{
    #[\Override]
    public static function modelProvider(): array
    {
        return [
            [ MyWonderfulModel::class ]
        ];
    }

    // public function testFooNotBar(): void
    // {
    //    assert(false);
    // }
} 


NB: This code is not completely complete but it is a sort of proof of concept.