2
\$\begingroup\$

I am working with 2 databases.

One for admins ('backoffice') and one for users.

Account managers: Use the 'backoffice' database to manage customer accounts.

Customers: Whenever a users connects and inserts his username password and company name, the code must connect to the backoffice database, validate his data, get his database host name (there are 5 customer databases), disconnect from 'backoffice' and connect to the customer personal account.

Is it "good practice" to create a general 'Database' class, a 'Backoffice_db' class (extending if with 'Database') and a 'Customer_db' class (extending it with Backoffice_db class)? or should i manage both in one 'Database' class?

Database.class.php:

<?php
/** 
 *  Database class 
 *  https://github.com/wickyaswal/indieteq-php-my-sql-pdo-database-class
 *  
 */

class Database
{
    # @object, The PDO object
    protected $pdo;

    # @object, PDO statement object
    protected $sQuery;

    # @array,  The database settings
    protected $ini;

    # @bool ,  Connected to the database
    protected $connected = false;

    # @object, Object for logging exceptions    
    protected $log;

    # @array, The parameters of the SQL query
    protected $parameters;

    # Create an instance 
    private

    /**
     *   Default Constructor 
     *
     *  1. Instantiate Log class.
     *  2. Creates the parameter array.
     */
    public function __construct()
    {

    }

    /**
     *   Get the PDO connection 
     *
     */
     public function getPDO() 
     {
           if ($this->pdo instanceof PDO) 
                return $this->pdo;
     }

    /**
     *  This method makes connection to the database.
     *  
     *  1. Reads the database settings from a ini file. 
     *  2. Puts  the ini content into the settings array.
     *  3. Tries to connect to the database.
     *  4. If connection failed, exception is displayed and a log file gets created.
     */
    protected function Connect($host, $user, $password, $dbname)
    {

        $dsn            = 'mysql:dbname=' . $dbname . ';host=' . $host . '';

        try {
            # Read settings from INI file, set UTF8
            $this->pdo = new PDO($dsn, $user, $password, array(
                PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"
            ));

            # We can now log any exceptions on Fatal error. 
            $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

            # Disable emulation of prepared statements, use REAL prepared statements instead.
            $this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

            # Connection succeeded, set the boolean to true.
            $this->connected = true;
        }
        catch (PDOException $e) {
            # Write into log
            // echo $this->ExceptionLog($e->getMessage());
            dbg( $this->ExceptionLog($e->getMessage()) );
            die();
        }
    }


    /*
     *   You can use this little method if you want to close the PDO connection
     *
     */
    public function CloseConnection()
    {
        # Set the PDO object to null to close the connection
        # http://www.php.net/manual/en/pdo.connections.php
        $this->pdo = null;
    }


    /*
     *   Gets db.ini data
     *
     */
    protected function get_db_ini()
    {
        if ( empty($this->ini) ) $this->load_db_ini();
    }


    /*
     *   Load db.ini file contents
     *
     */
    protected function load_db_ini()
    {
        $this->ini = parse_ini_file('/etc/optimizeit/db.ini', true);
    }


    /*
     *   Gets section from db.ini resultarray 
     *
     */
    protected function get_db_ini_section($section)
    {
        return isset( $this->ini[$section] ) ? $this->ini[$section] : Array();
    }


    /**
     *  Every method which needs to execute a SQL query uses this method.
     *  
     *  1. If not connected, connect to the database.
     *  2. Prepare Query.
     *  3. Parameterize Query.
     *  4. Execute Query.   
     *  5. On exception : Write Exception into the log + SQL query.
     *  6. Reset the Parameters.
     */
    protected function Init($query, $parameters = "")
    {
        # Connect to database
        if (!$this->connected) {
            $this->Connect();
        }
        try {
            # Prepare query
            $this->sQuery = $this->pdo->prepare($query);

            # Add parameters to the parameter array 
            $this->bindMore($parameters);

            # Bind parameters
            if (!empty($this->parameters)) {
                foreach ($this->parameters as $param => $value) {
                    if(is_int($value[1])) {
                        $type = PDO::PARAM_INT;
                    } else if(is_bool($value[1])) {
                        $type = PDO::PARAM_BOOL;
                    } else if(is_null($value[1])) {
                        $type = PDO::PARAM_NULL;
                    } else {
                        $type = PDO::PARAM_STR;
                    }
                    // Add type when binding the values to the column
                    $this->sQuery->bindValue($value[0], $value[1], $type);
                }
            }

            # Execute SQL 
            $this->sQuery->execute();
        }
        catch (PDOException $e) {
            # Write into log and display Exception
            // echo $this->ExceptionLog($e->getMessage(), $query);
            dbg( $this->ExceptionLog($e->getMessage(), $query) );
            die();
        }

        # Reset the parameters
        $this->parameters = array();
    }


    /**
     *  @void 
     *
     *  Add the parameter to the parameter array
     *  @param string $para  
     *  @param string $value 
     *  Updated 11.07.2018: https://github.com/wickyaswal/indieteq-php-my-sql-pdo-database-class/issues/83
     */
    public function bind($para, $value)
    {
        if (is_int($para)) {
          $this->parameters[sizeof($this->parameters)] = [++$para , $value];
        } else {
          $this->parameters[sizeof($this->parameters)] = [":" . $para , $value];
        }
    }

    /**
     *  @void
     *  
     *  Add more parameters to the parameter array
     *  @param array $parray
     */
    public function bindMore($parray)
    {
        if (empty($this->parameters) && is_array($parray)) {
            $columns = array_keys($parray);
            foreach ($columns as $i => &$column) {
                $this->bind($column, $parray[$column]);
            }
        }
    }


    /**
     *  If the SQL query  contains a SELECT or SHOW statement it returns an array containing all of the result set row
     *  If the SQL statement is a DELETE, INSERT, or UPDATE statement it returns the number of affected rows
     *
     *      @param  string $query
     *  @param  array  $params
     *  @param  int    $fetchmode
     *  @return mixed
     */
    public function query($query, $params = null, $fetchmode = PDO::FETCH_ASSOC)
    {
        $query = trim(str_replace("\r", " ", $query));

        $this->Init($query, $params);

        $rawStatement = explode(" ", preg_replace("/\s+|\t+|\n+/", " ", $query));

        # Which SQL statement is used 
        $statement = strtolower($rawStatement[0]);

        if ($statement === 'select' || $statement === 'show') {
            return $this->sQuery->fetchAll($fetchmode);
        } elseif ($statement === 'insert' || $statement === 'update' || $statement === 'delete') {
            return $this->sQuery->rowCount();
        } else {
            return NULL;
        }
    }


    /**
     *  Returns the last inserted id.
     *  @return string
     */
    public function lastInsertId()
    {
        return $this->pdo->lastInsertId();
    }


    /**
     * Starts the transaction
     * @return boolean, true on success or false on failure
     */
    public function beginTransaction()
    {
        return $this->pdo->beginTransaction();
    }


    /**
     *  Execute Transaction
     *  @return boolean, true on success or false on failure
     */
    public function executeTransaction()
    {
        return $this->pdo->commit();
    }


    /**
     *  Rollback of Transaction
     *  @return boolean, true on success or false on failure
     */
    public function rollBack()
    {
        return $this->pdo->rollBack();
    }


    /**
     *  Returns an array which represents a column from the result set 
     *
     *  @param  string $query
     *  @param  array  $params
     *  @return array
     */
    public function column($query, $params = null)
    {
        $this->Init($query, $params);
        $Columns = $this->sQuery->fetchAll(PDO::FETCH_NUM);

        $column = null;

        foreach ($Columns as $cells) {
            $column[] = $cells[0];
        }

        return $column;
    }


    /**
     *  Returns an array which represents a row from the result set 
     *
     *  @param  string $query
     *  @param  array  $params
     *  @param  int    $fetchmode
     *  @return array
     */
    public function row($query, $params = null, $fetchmode = PDO::FETCH_ASSOC)
    {
        $this->Init($query, $params);
        $result = $this->sQuery->fetch($fetchmode);
        $this->sQuery->closeCursor(); // Frees up the connection to the server so that other SQL statements may be issued,
        return $result;
    }


    /**
     *  Returns the value of one single field/column
     *
     *  @param  string $query
     *  @param  array  $params
     *  @return string
     */
    public function single($query, $params = null)
    {
        $this->Init($query, $params);
        $result = $this->sQuery->fetchColumn();
        $this->sQuery->closeCursor(); // Frees up the connection to the server so that other SQL statements may be issued
        return $result;
    }


    /** 
     * Writes the log and returns the exception
     *
     * @param  string $message
     * @param  string $sql
     * @return string
     */
    protected function ExceptionLog($message, $sql = "")
    {
        $exception = "Unhandled Exception. <br /> \n";
        $exception .= $message;
        $exception .= "<br /> \n You can find the error back in the log.";

        if (!empty($sql)) {
            # Add the Raw SQL to the Log
            $message .= "\r\nRaw SQL : " . $sql;
        }
        # Write into log
        // $this->log->write($message);

        return $exception;
    }


    /**
     *  Generate a string for columns selection
     *  @param      Array   $app_name   An array of app name/s or preferable - comma separated sting 
     *  @throws     Array               An array of app_id, app_name, lhm_threshold, enable_evm, evm_type, evm_rate for each app.
     */
    private function select_columns(Array $colums_array)
    {
        return implode(",", $columns_array);
    }

}
?>

Backoffice_db.php:

<?php
/**
 *  Backoffice Database Connection class 
 *
 */
require("Database.class.php");

class Backoffice_db extends Database
{
    # @string, Database Host
    private $bo_host;

    # @string, Database Username
    protected $user     = 'root';

    # @string, Database Password
    protected $password = 'thisismyrealpassword';

    # @string, Database Name 
    private $bo_dbname  = 'backoffice';


    /**
     *   Default Constructor 
     *
     *  1. Instantiate Log class.
     *  2. Creates the parameter array.     
     *  3. Get backoffice variables from ini
     *  4. Connect to database.
     *
     */
    public function __construct()
    {
        $this->parameters = array();                                                    // Init params for queries. 
        if ( empty($this->ini) ) $this->get_db_ini();                                   // Make sure INI is fetched. 

        $this->get_bo_host();                                                           // Set global variables for the backoffice.
        $this->Connect($this->bo_host, $this->user, $this->password, $this->bo_dbname); // Connect with global vars. 
    }


    /**
     *   Get backoffice HOST for connection 
     *
     *  1. Get backoffice credentials.
     *  2. Set backoffice credentials.
     *
     */
    protected function get_bo_host()
    {
        $backoffice_credentials = $this->get_db_config($this->dbname);
        $this->bo_host          = $backoffice_credentials['host'];
    }


    /**
     *   Get db configurations via given $dbname 
     *
     *  1. Return 'main' array section (backoffice credentials)
     *
     */
    protected function get_db_config($dbname)
    {

        try 
        {
            $settings = $this->get_db_ini_section(md5($this->bo_dbname));

            if ( empty($settings) ) 
                throw new Exception('Cannot Find the customers name `' . $this->bo_dbname . '` in .ini file.');
        }
        catch (Exception $e)
        {
            // echo $this->ExceptionLog($e->getMessage());
            // dbg( $this->ExceptionLog($e->getMessage()) );
            err( $this->ExceptionLog($e->getMessage()) );
            die($ex->getMessage());
        }

        return $settings;
    }



}

Customer_db.php:

<?php
/**
 *  Customer Database Connection class 
 *
 */
require("Backoffice_db.class.php");

class Customer_db extends Backoffice_db
{

    # @string, Database Host
    private $host;

    # @string, Database Name 
    private $dbname;

    # The Single Instance
    private static $instance; 


    /*
     *  Get an instance of the Database
     *  @return Instance
    */
    public static function getInstance() {

        if ( !self::$instance ) {           // If no instance then make one
            self::$instance = new self();
        }

        return self::$instance;             // Return the Database obj
        // return self::$instance->getPDO();// Return the PDO connectoion instance from dabase obj 
    }


    /**
     *   Default Constructor 
     *  1. Sets given dbnmae
     *  2. Connects to the backoffice
     *  3. Get customer data from backoffice db
     *  4. Disconnects from backoffice db
     *  5. Connect to customers database if active account
     */
    public function __construct($dbname)
    {
        self::$instance = $this;

        $this->dbname                               = $dbname;

        $bo_conn                                    = new Backoffice_db();

        list($this->host, $this->dbname, $status)   = $this->get_customer_conn_info($bo_conn);

        $bo_conn->CloseConnection();                                                               // Disconnect from backoffice and connect to customer

        try
        {
            if ( ($status === 'Deleted') || ($status === 'Inactive') ) 
                throw new Exception('Customer ('. $dbname .') account is "Deleted" or "Inactive".'); 

            $this->Connect($this->host, $this->user, $this->password, $this->dbname);
        }
        catch(Exception $e)
        {
            err($this->ExceptionLog($e->getMessage()));
            die($e->getMessage());
        }

    }


    /**
     *   Get user HOST, DBNAME & STATUS from backoffice=>customers table
     *
     *  1. Run a query on backoffice to fetch users host and db name
     *  2. return 2 separate vairbles of host and db name 
     *
     */
    protected function get_customer_conn_info($bo_conn){

        $customer_data = $bo_conn->row("SELECT db_host, db_name, assessment_status FROM customers WHERE customer_name = :customer_name", array("customer_name" => $this->dbname));
        return array($customer_data['db_host'], $customer_data['db_name'], $customer_data['assessment_status']);
    }

}
\$\endgroup\$
  • \$\begingroup\$ This isn't an answer to your specific question but I have to wonder why there are multiple databases involved in the first place. Seems to be overly complicating things for no apparent reason (at least not one that is able to be discerned from what you posted). To your question, I would use a single class for database interaction. \$\endgroup\$ – Dave Jul 22 '18 at 12:47
  • \$\begingroup\$ @Dave this is the exact question i asked when i started the job. Currently this cant change.. so i have to work with what i have :( ... I started writing my db class like this, and then now i started thinking maybe i had to make my database as one class and not 2.. thanks for you answer Dave. \$\endgroup\$ – Kar19 Jul 22 '18 at 12:50
  • \$\begingroup\$ @Dave multi-tentant applications are always were a thing \$\endgroup\$ – Your Common Sense Jul 22 '18 at 19:02
1
\$\begingroup\$

There are several areas of improvement

Architecture

First of all, I don't see why these classes should inherit to each other. To me, there is no difference between these classes and I don't see why can't you just instantiate two objects of the same class. Of course if you fix another issue:

There is at least one principal principle violated in your classes - a single responsibility principle. Why a database class should look somewhere in the filesystem for its credentials? Why a filename with credentials is hardcoded?

You must create a distinct class for the configuration and provide its instance as a parameter when instantiating/initializing your database class.

PDO wrapper

Unfortunately, you choose a very bad example to model your class after. It even could be used as a showcase for bad practices, featuring every single one of them. Consider taking a look at my article on database wrappers' common mistakes. In brief, your class

  • implements an inflexible error reporting (this is also a code duplication and another violation of the single responsibility principle. It makes no sense for a database class to have an error logger of its own. whatever logging should be done elsewhere (notably core PHP is already excel in it))
  • stateful
  • duplicates PDO's functionality
  • at the same time limits it, offering you less features than exists in vanilla PDO
  • overall untidy with a lot of duplicated or unnecessary code.

What I would do is just a single class that extends PDO with addition of 3 new functions:

  • init($config) - called once, initializing a connection
  • run($query, $params) to run a query with parameters
  • getInstance() get the instance

You can take a look at such a class in my other article, make sure to check the usage examples below. You'd be surprised to find most of your code unnecessary, such as for telling a select query from insert

And one class to supplement it, a class to get configuration options, with two methods, one to get from a file and one to get from a database.

\$\endgroup\$
  • \$\begingroup\$ I think you're my favorite user here <3 \$\endgroup\$ – Kar19 Jul 23 '18 at 7:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.