Connection to separate PDO databases - (multi-tenancy )

Question

I have the following password for my application: I have a PHP + Mysql application, with PDO connection, I need the application to be shared with all registered companies, but each company will have its database separately.

IMPLEMENTATION STRUCTURE

Connection:

1 - Con.class.php => Responsible for connecting to the database using PDO - Singleton

2 - Config.php => Via define(); takes the values to be passed to connection - (Host, User, DB...)

Central Database:

3 - TABLE Companies => Receives the companies that will use the application, containing the connection information - (Host, User, DB, Password)

Customer Databases (db1, bd2, db3.....):

4 - TABLE Users => Contains user information, including "ID_EMPRESA" to reference which company it belongs to in addition to login information- (user_login, user_password, user_email...)

How can I make each company log into its base it will be created when registering companies, besides not having the knowledge advanced in PHP, I couldn’t find a help on this by researching.

CODES

Con.class.php

<?php
class Conn {

    private static $Host = SIS_DB_HOST;
    private static $User = SIS_DB_USER;
    private static $Pass = SIS_DB_PASS;
    private static $Dbsa = SIS_DB_DBSA;

    private static $Connect = null;

    private static function Conectar() {
        try {
            if (self::$Connect == null):
                $dsn = 'mysql:host=' . self::$Host . ';dbname=' . self::$Dbsa;
                $options = [ PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES UTF8'];
                self::$Connect = new PDO($dsn, self::$User, self::$Pass, $options);
                self::$Connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            endif;
        } catch (PDOException $e) {
            PHPErro($e->getCode(), $e->getMessage(), $e->getFile(), $e->getLine());
            die;
        }

        return self::$Connect;
    }

    public static function getConn() {
        return self::Conectar();
    }

    private function __construct() {

    }

    private function __clone() {

    }

    private function __wakeup() {

    }

}

Config.php

if ($_SERVER['HTTP_HOST'] == $urlcentral):
    define('HOST', $linkBanco);
    define('USER', $userBanco);
    define('PASS', $senhaBanco);
    define('DBSA', $nomeBanco);
else:
    define('HOST_CLIENTE', $linkBancoCliente);
    define('USER_CLIENTE', $userBancoCliente);
    define('PASS_CLIENTE', $senhaBancoCliente);
    define('DBSA_CLIENTE', $nomeBancoCliente);
endif;

Is it possible to perform these actions? When the user enters the Login and Password, check which company it belongs to, and take the database connection information and it be connected in the database of the company to which it belongs. Thank you!

Answer 1

In your case, young man, I would reimplementate that connection. I believe that Singleton in this case (and in the majority, in my humble opinion) is more of a structure than helps.

I believe that a good option would be for you to have a list of connections saved in a array, separated each by an index, to indicate the application that each belongs to.

Then you can use the Multiton standard combined with Factory, to be able to generate these unique connections...

I made a sketch just to illustrate, but you can create it based on a structure similar to this:

class Connection

{
    protected $dsn;

    protected $user;

    protected $password;

    protected $options;

    public function __construct(array $options)
    {
        // monta as variáveis de acordo com o option

        //$this->dsn = $this->buildDsn($options); 

        // ...
    }

    public function getPdo()
    {
        if ($this->pdo === null)
        {
            $this->pdo = new \PDO($this->dsn, $this->user, $this->password, $this->options);
        }

        return $this->pdo;
    }
}

class ConnectionFactory
{
    protected static $connections = [
        'app_1' => [
            'host' => '...',
            'password' => '...',
        ],

        'app_2' => [
            'host' => '...',
            'password' => '...'
        ]
    ];

    protected static $instances = [];

    // implementação multiton

    public static function getConnection($name)
    {
        if (! isset(static::$instances[$name])) {

            static::$instances[$name] = new Connection(static::$connections[$name]);
        }

        return static::$instances[$name];
    }
}

Note now, that you could create the instance of connection to the database, normally according to the desired client.

Example:

  $db1 = ConnectionFactory::getConnection('app_1');

  $db2 = ConnectionFactory::getConnection('app_2');

In the above case, the calls could occur anywhere in the application, as the constructions of the instances are stored in a array and, if available, are not recreated, only reused.

In the above case, I combined Factory with Multiton (a Singleton based on parameters) to keep the structure similar to what you currently have (you must have a reason to be using English).