One ActiveRecord model for multiple tables and databases

Question

Currently I'm developing a new website/platform in the Yii2 framework.

The project exists out of multiple database's (different clients/my-sql user accounts), each database exists out of multiple tables with the same structure. I know this is against the guide-lines for relational databases, but for different other technical reasons (and not just laziness...) it's not possible to change the database setup. The database is integrated in other programs as well so it is not changeable... At this moment no writes need to be done to the databases.

So the database setup is as following:

db_100 --o-- tbl_A1  (tables all have the same structure)
         o-- tbl_B2 
         o-- tbl_C3

db_200 --o-- tbl_A1
         o-- tbl_B2
         o-- tbl_C3
         o-- tbl_D4

db_300 --o-- tbl_A1
          ...           

Database-names and tables names always have the same prefix, no defined maximum of databases or tables is given. The suffixes of the database- and table-names are not predictable. (Currently 40 db's with around 50 tables each, but still growing).

As every table has the same structure I thought it was a good Idea to use the ActiveRecord-class out of the Yii2 framework. However, the ActiveRecord-class uses static methods to get the database-connection and tableName. The static methods don't make it possible to create instances of the class while using different tables and db's for each instance.

getDb() (Yii2 Framework)

/**
* Returns the database connection used by this AR class.
* By default, the "db" application component is used as the database connection.
* You may override this method if you want to use a different database connection.
* @return Connection the database connection used by this AR class.
*/
public static function getDb()
{
    return Yii::$app->getDb();
}

tableName() (Yii2 Framework)

/**
* Declares the name of the database table associated with this AR class.
* By default this method returns the class name as the table name by calling [[Inflector::camel2id()]]
* with prefix [[Connection::tablePrefix]]. For example if [[Connection::tablePrefix]] is `tbl_`,
* `Customer` becomes `tbl_customer`, and `OrderItem` becomes `tbl_order_item`. You may override this method
* if the table is not named after this convention.
* @return string the table name
*/
public static function tableName()
{
    return '{{%' . Inflector::camel2id(StringHelper::basename(get_called_class()), '_') . '}}';
}

At this moment I made it work by using the get-values out of the request. So I can declare the tablename and db in an url, pretty easy http://...:8080/CustomActiveRecord/index?db=100&customTableName=A1

(simplified code)

public static function tableName() {
    //get base of tablename
        $customTblName = static::customTblName(); //-> Yii::$app->request->get('customTblName') ?: null;
    //throw exception if null
        if (is_null($customTblName )) {
            throw new \yii\web\HttpException(...);
        }
    //return the tablename
        return 'tbl_' . $customTblName;
}

I do something simular for the db-connections (I fill the parameter array with all database credentials, and set the db in the model also using the ...request->get(...) in the getDb() function.

This all works now in combination with gridview, listviews, kartik-Chartjs,... but only if the tableName and db is defined in the URL. This doesn't make it possible to use multiple models at once, which I need. (Comparing, statistics, ...)

Has anyone an idea how to use one ActiveRecord for more than one table/database? Ideally using a constructor so I can create an instance for each table?

$model = New CustomActiveRecord(['db' => '100', 'tbl' => 'A1']);

Answer 1

Recently I've came across the same problem.

I've used a non-pretty solution, but it ended up working.

First create a custom ActiveRecord class:

use Yii;
use yii\base\InvalidArgumentException;
use yii\base\InvalidCallException;
use yii\db\ActiveRecord;
use yii\db\Connection;

class ActiveRecordCustom extends ActiveRecord
{
    /**
     * @var Connection[]
     */
    protected static $_connections = [];

    /**
     * @var static[]
     */
    private static $_classes = [];

    private static function ensureConnection(string $db): void
    {
        if (!preg_match('/^[a-z0-9_]++$/i', $db)) throw new InvalidArgumentException('Argument $db is not a valid database name');

        if (array_key_exists($db, self::$_connections)) return;

        /* @var Connection $connection */
        $connection = clone Yii::$app->get('db');
        $connection->dsn = SomeHelperClass::GenerateDsn($db);

        self::$_connections[$db] = $connection;
    }

    /**
     * Creates a dynamic class (and caches it). The resulting class uses a specific DB on its connection string.
     * @param string $db
     * @return static
     */
    public static function classForDb(string $db)
    {
        $calledClass = static::class;
        if (!in_array(self::class, class_parents($calledClass))) throw new InvalidCallException('This function must be called from child classes only');

        self::ensureConnection($db);

        $classKey = "{$calledClass}_{$db}";
        if (!array_key_exists($classKey, self::$_classes)) {
            if (!UString::startsWith('\\', $calledClass)) $calledClass = "\\{$calledClass}";
            $generatedClassName = 'dynamic_' . UString::secureRandomHexString();
            $generatedClassCode = <<<HEREDOC
class {$generatedClassName} extends {$calledClass} {
    public static function tableName() {
        return {$calledClass}::tableName();
    }

    public static function getDb(): \yii\db\Connection {
        return self::\$_connections['{$db}'];
    }

    public static function getDbName(): string {
        return '{$db}';
    }
}
HEREDOC;
            eval($generatedClassCode);
            self::$_classes[$classKey] = $generatedClassName;
        }

        return self::$_classes[$classKey];
    }

    /**
     * Creates an instance of a dynamic class (and caches it). The resulting instance uses a specific DB on its connection string.
     * @param string $db
     * @return static
     */
    public static function instanceForDb(string $db)
    {
        $class = self::classForDb($db);
        return new $class;
    }
}

The UString class:

class UString {
    /**
     * Checks if the string $haystack starts with $needle
     * @param string $haystack The string to check if starts with $needle
     * @param string $needle The string used to check if $haystack starts with it
     * @return bool True if $haystack starts with $needle, otherwise, false
     */
    public static function startsWith($haystack, $needle) {
        $length = mb_strlen($needle);
        return (mb_substr($haystack, 0, $length) === $needle);
    }

    /**
     * Generates a random HEX string with a fixed length of 128 chars. Its guaranteed to be cryptographically secure.
     * @return string|bool The generated random HEX string, or false in case of failure
     */
    public static function secureRandomHexString() {
        $data = openssl_random_pseudo_bytes(64, $secure);
        return $secure ? bin2hex($data) : false;
    }
}

And finally a sample AR:

class MyARClass extends ActiveRecordCustom {...}

Now all that is left is to use the AR class as follows:

• For static method invocation: MyARClass::classForDb('some_database')::find()...

• For creating instances tied to a specific DB: $instanceTiedToDb = MyARClass::instanceForDb('some_database');

Even tough this specific code works only for a dynamic database connection, it can be easily extended to support tables too.