Connection in separate databases PDO - (multi-tenancy)

Question

Connection in separate databases PDO - (multi-tenancy)

Navigation

#1 by (2 votes)

3

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

APPLICATION STRUCTURE

Connection:

1 - Con.class.php = > Responsible for connecting to the bank using PDO - SingleTon

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

Central Database:

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

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

4 - Users TABLE = > Contains user information, including "ID_EMPRESA" to refer to which company the same belongs to the login information (user_login, user_pass, user_email ...)

How can I make each company log on its base that will be created when registering the companies, besides not having the knowledge advanced in PHP, I did not find a help on this by searching.

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 this action? When the user enters the Login and Password, check which company it belongs to, and take the database connection information and it is connected to the database of the company to which it belongs. Thanks!

php mysql pdo multi-tenancy

asked by anonymous 28.06.2018 / 03:40

1 answer

Proper Naming of User Groups in a System How to set var JS value for html field?

score 2 · Answer 1

In your case, young man, I would reimplement that connection. I believe that Singleton in this case (and most, in my humble opinion) engages more structure than it helps.

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

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

I made a sketch just for an example, but you can create it based on a structure similar to that:

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];
    }
}

Now, you could create the instance of connection to the database, usually according to the desired client.

Example:

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

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

In the above case, calls could occur anywhere in the application, because the constructs of the instances are stored in array and if they exist, they are not recreated, only reused.

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