[3.x][feature] Support for horizontal MySQL scaling

[TemaSM]

How about to use 2 or more MySQL connections for MySQL scaling?
I think it's must have feature. For example, if i want store Users in 1 DB, but Notes of these Users, i want store into 2 DB.

[panique]

The thing is that the project is basically "just" a pure implementation of a login system. All features should only be directly related to the login topic. The scaling thing is totally out of scope in my opinion, even Laravel etc. don't have this. I also think that this is something only hardcore users would use, and do they really use THIS project (I'm still unsure what the target group is) ? Hmm...

I would say: Good idea, and definitly important for larger projects, but let's keep the project as simple as possible. What do the others say ?

[sopitz]

I use this login-system in a larger project and one of the first things I implemented for me was to use more then one database for exactly the reason @TemaSM stated.
I think this project will be used by more "expert" devs then you might think (coz they understand how much work it's gonna save them).
I could/would help implement that feature.

[TemaSM]

I fully agree with @sopitz, cause this feature(in future) and this project saving a lot of time. I think will be good idea if you - @panique will create new repository with "php-login" files including extra features for big projects. And u really need to know, that most of who using your project - professionals =)
And we(professionals will donate u more than for mini versions).

[panique]

Wow! :) Okay, let's discuss this a little bit! @sopitz Can you give a short info on how this would look like, just a short estimation, maybe pseudocode or something. I've never worked with more than one DB server/connection and cannot really give useful info on that.

By the way: The 2.0 version is just out, so let's integrate this into 3.0 (maybe in early 2015) and work on a new branch.

[sopitz]

No problem. I'll do that later then, right?

[panique]

@sopitz Yeah, let's do this in July or August maybe, currently I'm busy with other stuff and setting up a roadmap for 3.0 will take some work.

[TemaSM]

Okay, @panique. Now i'm not so needed in this feature (and maybe other people), but i can help u with implementing this feature. Feel free, to contact me.

[sopitz]

[edit by panique / dec 2014]
Simon (sopitz) just commented that the code here works fine, but might cause a "too many mysql connections" error in high-traffic environments. Keep that in mind in case you use the lines below!

[original post by @sopitz ]
How I implemented it, its not even a lot to do.
First I added a param to the Database-Constructor. To make it downward compatible I added the default value (coz I basically was too lazy to change all the login-php classes for that).

<?php

/**
 * Class Database
 * Creates a PDO database connection. This connection will be passed into the models (so we use
 * the same connection for all models and prevent to open multiple connections at once)
 */
class Database extends PDO {
    /**
     * Construct this Database object, extending the PDO object
     * By the way, the PDO object is built into PHP by default
     */
    ***public function __construct($database = null)*** {
        /**
         * set the (optional) options of the PDO connection. in this case, we set the fetch mode to
         * "objects", which means all results will be objects, like this: $result->user_name !
         * For example, fetch mode FETCH_ASSOC would return results like this: $result["user_name] !
         *
         * @see http:https://www.php.net/manual/en/pdostatement.fetch.php
         */
        $options = array(PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ, PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING);

        /**
         * Generate a database connection, using the PDO connector
         *
         * @see http:https://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/
         * Also important: We include the charset, as leaving it out seems to be a security issue:
         * @see http:https://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers#Connecting_to_MySQL says:
         * "Adding the charset to the DSN is very important for security reasons,
         * most examples you'll see around leave it out. MAKE SURE TO INCLUDE THE CHARSET!"
         */

        if ($database != null) {
            parent::__construct(constant($database . "_DB_TYPE") . ':host=' . constant($database . "_DB_HOST") . ';dbname=' . constant($database . "_DB_NAME") . ';charset=utf8', constant($database . "_DB_USER"), constant($database . "_DB_PASS"), $options);
        } else {
            $database = Databases::LOGIN;
            parent::__construct(constant($database . "_DB_TYPE") . ':host=' . constant($database . "_DB_HOST") . ';dbname=' . constant($database . "_DB_NAME") . ';charset=utf8', constant($database . "_DB_USER"), constant($database . "_DB_PASS"), $options);
        }
    }
}

To make it easier for me to get my staging-infos into the code I added a 'few' more constants. For the database I created a class like this:

<?php

class Databases {
    const LOGIN = "login";
    const ACTIVITIES = "activities";
    const DATA = "data";
}

You can register all your databases in there. That makes it easier to use in your code as well (reference a certain database by Databases::LOGIN for example)

To manage all the different connections I created another class in the config area (which I also split up to user automated rollout-scripts that change configs, but thats probably another topic we could discuss).

<?php
if (ENVIRONMENT == Environments::DEV) {
    define(Databases::LOGIN . '_DB_TYPE', 'mysql');
    define(Databases::LOGIN . '_DB_HOST', '127.0.0.1');
    define(Databases::LOGIN . '_DB_NAME', APP_NAME . '_' . Databases::LOGIN);
    define(Databases::LOGIN . '_DB_USER', 'user');
    define(Databases::LOGIN . '_DB_PASS', 'pass');

    define(Databases::ACTIVITIES . '_DB_TYPE', 'mysql');
    define(Databases::ACTIVITIES . '_DB_HOST', '127.0.0.1');
    define(Databases::ACTIVITIES . '_DB_NAME', APP_NAME . '_' . Databases::ACTIVITIES);
    define(Databases::ACTIVITIES . '_DB_USER', 'user');
    define(Databases::ACTIVITIES . '_DB_PASS', 'pass');

    define(Databases::DATA . '_DB_TYPE', 'mysql');
    define(Databases::DATA . '_DB_HOST', '127.0.0.1');
    define(Databases::DATA . '_DB_NAME', APP_NAME . '_' . Databases::DATA);
    define(Databases::DATA . '_DB_USER', 'user');
    define(Databases::DATA . '_DB_PASS', 'pass');
} 

I used a pattern for me to be able to run multiple instances on one server. Therefore I added an APP_NAME as prefix to my databases, you can change that or manually type the names. I always prefer to store this stuff in constants (probably due to my recent Java-coding).

After you added this to your code you can simply call the Database-Constructor with a param (e. g. like this new Database(Databases::ACTIVITIES);). On that object you can run the normal PDO-statements (e. g. db->prepare()).

Open issues for me:

• have tables stored in constants, linked to the databases for easier use within model-scripts
• extend the debugging capabilities (for now its not clear which connections fails at first sight)

One additional feature to that (combined with the staging and rollout) I implemented is the use of http:https://phinx.org/ to maintain my databases on the different environments. It can be easily configured and nicely used in rollout-scripts.

[panique]

Fantastic stuff! Out of curiosity: Can you give some demo informations about how your Environments-class looks like ?

[nerdalertdk]

Uhh can't what for this upgrade, this opens up for SAAS 👍

[sopitz]

@panique the Environments.php is another "enum".

class Environments {
    const DEV = "development";
    const TEST = "test";
    const PROD = "production";
}

but actually it works together with the Stages.php ("enum" as well).

<?php

class Stages {
    const LOCAL = "local";
    const ONLINE = "online";
    const UNITTEST = "no";
}

This is useful in two ways.
First of all you can enable or disable certain behavior for different environments. My dev environment for example logs a lot more and puts more details into the log files. In production I send myself emails if there are certain errors popping up (That required to overwrite default error handling and implement it manually).
Second good thing is, that I load certain scripts (jQuery, my IssueTracker-Script, ...) from the www usually. That wont work with slow (EDGE) internet in trains for example. Therefore I disabled it with the stage "local". It wont try and load those anymore.
The UNITTEST tag was needed for autoloading coz this kinda messed up with paths when running scripts outside of an apache.
I hope that helps, when there is the need to implement these things, let me know.

edit: thats how you would use it in your scripts then:

if (ENVIRONMENT == Environments::DEV && STAGE != Stages::LOCAL) {
    doSomething();
}

[sopitz]

@panique is there a timeline when the branch for 3.0 will be created? Im thinking about extending my version a bit and would like to give that back to 3.0 then.

[panique]

Hey @sopitz , good question! To be honest, there's currently summer here in Europe, so I wanted to move the work on the 3.0 branch to autumn & winter, maybe in October 2014. Maybe it's a good idea to create the 3.0 develop branch right now :)

I'll do this and comment again when done...

[sopitz]

haha @panique. Good point. I will get bored sometimes tho and take my machine to the lakes ;) thanks a lot!

[panique]

Okay @sopitz , I've just created a final 2.0(.1) tag for the 2.0 branch, so from now we simply use the develop branch for the 3.0 version :)
I'll just merge and document the latest changes in develop and then push this.

Potential urgent fixes for 2.0 will be made in feature-branches of master branch.

[sopitz]

I found a huge issue with that approach after using it for a while. Seems like my script creates too many connections without reusing or closing old ones. So please DONT use the approach described above.
I'm still trying to figure out what causes that problem but I'll provide a fixed solution once I figured it out.

[panique]

He everybody, the new HUGE has now a DatabaseFactory class:
https://github.com/panique/huge/blob/master/application/core/DatabaseFactory.php
which allows quite complex database connection creation.

Currently it's only used in a very simple way, like here in a model class:

$database = DatabaseFactory::getFactory()->getConnection();
$sql = "....";
$query = $database->prepare($sql);

[JFJanssen]

Hi All,

Anyone still toying with this idea? At least I can see use for a "simple", barebones structure for adding more than one DB, with Load Balancing. However, I, too, see the need for a clean framework that focuses on a -well- implemented, secure login system.

In another GitHub-issue I read a proposqal (by TemaSM?, I'm not sure) to create an extra part, like a module maybe, that allows for expansion of functionality beyond the scope of PHP-Login. I think doing that would make PHP-Login the unifying Twitter Bootstrap-version of site backends. Worth a philosofical discussion, maybe.

[panique]

He everybody, the current DatabaseFactory allows exactly that (the Factory design pattern is basically able to "produce" new things, like in this case database connections to additional servers).

I got this solution from http:https://stackoverflow.com/questions/130878/global-or-singleton-for-database-connection, maybe somebody could an implementation of connecting to multiple databases / servers.

Btw personal experience: Maxing out the main (cloud-based) database server totally does the job for 99.99% of companies I would say. Currently DB servers from mainstream providers (Rackspace etc) can use 384 GB of RAM and 16-32 cores, that's insane and to be honest not even the biggest company i've ever seen from the inside doesn't even needed that. I think somebody who really needs this setup would use this quite tiny framework...

Anyway, if somebody can add horizontal database scaling feature, then this would be awesome!

[panique]

Hey, I'm currently "cleaning" the project a little bit and moving feature-requests like this to an own list inside the readme file (find it under the "future features" point). I hope you are okay with it, as most tickets here are new features and not really bugs or so.

My idea is just to avoid this project from getting oversized by too many features, so I'm closing the ticket, but for sure linking it from the readme in case somebody wants to implement this.

I hope you are all okay with this. :)