Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
Change database not from config/db.php
  • sosekisoseki
    Dear sir,

    Is it possible to change the connection string from one to the other in the model?

    I know that I had better set connection string in config/db.php.

    However, I need to do it manually for my system.
  • HarroHarro
    Orm model? Crud model?

    Do you want to set it dynamically, at runtime, of you do want to be able to configure it per model?
  • sosekisoseki
    Dear Harro,

    thank you for your reply.

    I am taking about orm model and want to change database configuration dynamically.

    Is that possible?
  • HarroHarro
    An ORM model object has two connection properties:

    /**
    * @var  string  connection to use
    */
    // protected static $_connection = null;

    /**
    * @var  string  write connection to use
    */
    // protected static $_write_connection = null;

    They are ment to be set statically in your model. If you want to change it dynamically, you need to add a method to your model (or use a base model or a trait if you need this in multiple models) that can set these values, as they are protected.

    If you don't have a master/slave database system with different read and write servers, you don't have to do anything with  $_write_connection, you only have to set $_connection.
  • sosekisoseki
    Dear Harro,

    Thank you for your answer.

    Dose that $_connection can store database connection string?

    For example, hostname, db name, port number, id and password can be set?

  • HarroHarro
    No, it stores a database definition string, as defined in your db.php.

    BTW, I now see (never used it myself!) that the model already has a "set_connection()" method, so you don't need to add that.
  • sosekisoseki
    Yes, that is what I thought.

    Also, when I look at orm/model.php, I found following.

    /**
     * Sets the connection to use for this model.
     * @param string $connection
     */
    public static function set_connection($connection)
    {
    static::$_connection = $connection;
    }

    It looks like it only set $_connection, not storing whole connection string.

    Am I right?

    So, possibly I may have to create one for changing database for my project.

    The reason why I need to implement this is that the connection string ,database hostname, id, password, db name and so on, stored in the database.
  • HarroHarro
    Accepted Answer
    You can create a definition at runtime:

    // make sure the db config is loaded
    \Config::get('db', true);

    // dynamically create a new db config based on 'default'
    $mydb = \Config::get('db.default');

    // set a new DSN
    $mydb['connection']['dsn'] = "mysql:host=127.0.0.1;dbname=MyDynamicDb";

    // store it
    \Config::set('db.MyDynamicDb', $mydb);

    After this, you can use this  in your model:

    /**
    * @var  string  connection to use
    */
    protected static $_connection = 'MyDynamicDb';

  • HarroHarro
    Accepted Answer
    edit: You don't need to explicitly load the db config if you first query the database for the correct DSN, in that case the config is already loaded.

    You can do this in your app bootstrap.php, after the call to \Fuel::init(), so the db config is populated correctly when your app starts.
  • sosekisoseki
    Hello,

    Thank you very much for your big help.

    I will try it out later this week.

    Thank you :)
  • sosekisoseki
    I just did following and now I can dynamically load config.


    Model_Something::find('all',array('where'=>array('delete_flag'=>0)));  
    $mydb = \Config::get('db.default');
    foreach($model as $m){ 
      $mydb['connection']['dsn'] = $m->db_type.':host='.preg_replace('/:[0-9]*/','',$m->db_hostname).';dbname='.$m->db_name;
      $mydb['connection']['username'] = $m->db_user_id;
      $mydb['connection']['password'] = $m->db_password;
      // Store it
      \Config::set('db.'.$m->hostname.':'.$m->db_name, $mydb);
    }

    Thanks a lot :)
  • sosekisoseki
    Sorry for asking the same question.

    I have another question regarding this.

    I have following source code and it is dynamically changing database, but, not always.

    $srv = \Model_Servers::find('all',array('where'=>array('delete_flag'=>0)));  

    foreach($srv as $s)
    {
    $conn_str = $s->hostname.':'.$s->db_name; 
    \Model_User::set_connection($conn_str);
    $model = \Model_User::find('all',array('where'=>array(array('id','!=',1))));
    print_r($model);
    }
  • HarroHarro
    Every database object has an internal connection object, which is persistent. These database objects are stored by name (in your code, $conn_str). As written before, this name must match a database configuration entry. From the code I've seen, you're doing it correctly.

    I'll have a look in the code for any issues.
  • HarroHarro
    I've done a quick scan of the Orm query code, and as far as I can see, it uses the defined connection anywere.

    Could you enable the profiler (in your config.php), en enable database profiling on all your database definitions ( add $mydb['profiling'] = true to your dynamic code ) and check exactly which queries go wrong? 

    The profiler will show you exactly which db connection name is used for every query run.
  • sosekisoseki
    Thank you for your suggestions. 

    In fact, the profiler is already enabled to see what is wrong with my program. 

    The db configuration was set correctly. 

     But, the data fetched from orm have some issue.

    One issue is that there was duplicated data. 

    Of course, this duplicated data have exactly the same primary key. 

    So, I thought there is something wrong with database cache because of fetching from exactly the same database scheme of table and/or with my program. 

    Secondly, some data were not fetched from the database. 

    So, I was thinking what to do last night and I did set primary keys. 

    First one is id and second one is shopid.
     

    They are unique so I set them as primary key and now it is working okay..
     

    But, I wonder why this happen.

    They are not defined as multiple key in the database. 

     But, now it is working.
  • HarroHarro
    Accepted Answer
    The ORM caches objects on modelname + primary keys. So yes, if you switch connections, you may bump into cached items if the databases contain records with the same key values.

    You might have to add a method to your model to clear the cache when you switch:

    public static function clear_cache()
    {
        unset(static::$_cached_objects[get_called_class()]);
    }

    then you can use

    Model_User::clear_cache();

    which would clear all cached objects of type Model_User.
  • sosekisoseki
    Hello there,

    Now I can fetch all data from all databases without duplicate and losing data.

    Thank you so much.
  • HarroHarro
    You're welcome. Happy coding! ;-)
Add a Comment

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership

Categories

In this Discussion