CakePHP switch database (using same datasource) on the fly?

Question 1

I have tried to build a small function that would be use in controller to switch database on the fly, i must use only one datasource.

On my database.php :

function __construct() {

    $server = Configure::read('Server');

    if(!empty($server['database'])) $this->local['database'] = $server['database'];
    $this->default = $this->{$server['datasource']};

}

Is used to switch database depending on server config. It is working great.

I tried to build up this :

/**
 * Connects to specified database
 *
 * @param array $config Server config to use {datasource:?, database:?}
 * @return array db->config on success, false on failure
 * @access public
 */
function dbConnect($config = array()) {
    ClassRegistry::init('ConnectionManager');
    //debug($config['datasource']);
    //$dbInstance =& ConnectionManager::getInstance();
    //$dbInstance->config->{$config['datasource']}['database'] = $config['database'];

    $db =& ConnectionManager::getDataSource($config['datasource']);
    $db->disconnect();
    $db->cacheSources = false;
    $db->config['database'] = $config['database'];
    $db->config['persistent'] = false;
    debug($db->config);
    $db->connect();
    if(!$db->isConnected()) {
        $this->error('!$db->isConnected()');
        return false;
    }
    return $db->config;
}

But sadly, everything seems to work but i alwas get data from the same DB using $this->Player->find('list') for instance. I tried $this->Player->cacheQueries = false; with no more success.

Question 2

Made it work using this (create a new connection on the fly) :

$newDbConfig = $this->dbConnect($serverConfig);
$this->Model->useDbConfig = $newDbConfig['name'];
$this->Model->cacheQueries = false;

With :

/**
 * Connects to specified database
 *
 * @param array $config Server config to use {datasource:?, database:?}
 * @return array db->config on success, false on failure
 * @access public
 */
function dbConnect($config = array()) {
    ClassRegistry::init('ConnectionManager');

    $nds = $config['datasource'] . '_' . $config['database'];
    $db =& ConnectionManager::getDataSource($config['datasource']);
    $db->setConfig(array('name' => $nds, 'database' => $config['database'], 'persistent' => false));
    if($ds = ConnectionManager::create($nds, $db->config)) return $db->config;
    return false;

}

Question 3

Based on the comments, I've created a hopefully acceptable jQuery solution.

See: http://jsfiddle.net/svRmL/

var $element = $('#cuisines');
var $elementWidth = $element.find(' > .checkbox').outerWidth(true),
    elementCount = $element.find(' > .checkbox').length,
    $boxes = $element.find(' > .checkbox');

/* just for debug */
$boxes.each(function(i) {
    $(this).find('label').html(i);
});

//set resize function
$(window).resize(function() {
    var perRow = Math.floor($element.width() / $elementWidth),
        i, j, $thisColumn, inc;

    $boxes.appendTo($element); //move elements out of columns from previous resize
    $('.tempColumn').remove(); //destroy old columns
    for (i = 0; i < perRow; i++) {
        $thisColumn = $('<div class="tempColumn" />').appendTo($element).css({
            width: $elementWidth,
            float: 'left'
        });
        inc = Math.ceil(elementCount / perRow);
        for (j = inc * i; j < inc * (i + 1); j++) {
            $boxes.eq(j).appendTo($thisColumn);
        }
    }
}).resize(); //trigger resize function immediately

Question 4

In the end the error was in my database user configuration. It seems I forgot to set CRUD permissions to the user I use to access the database and that's what gave me the error

Question 5

You should definitely use GraphAdapterBuilder.

As you said in the comment under @Braj 's answer,

workshift is set transient for a reason so that it won't serialize this object when serializing visit object. If its not marked as transient then the serialization falls into exception - by creating an unstoppable loop

This has a simple solution.

Workshift.java

public class Workshift {
    private final transient Context context;
    private final Visit visit;
    //for testing
    private String workshift_description;

    public Workshift(Context context,String id) {
        this.workshift_description=id;
        this.context = context;
        this.visit = new Visit(this);

    }
    public String getId() {
        return workshift_description;
    }

    public void setId(String id) {
        this.workshift_description = id;
    }
    public String toString() {
        return "[Workshift element => { WD: "+this.workshift_description+", VD : "+this.visit.getVisit_description()+"}";
    }
}

Visit.java

public class Visit {

    private final /* transient  */ Workshift workshift;

    public Visit(Workshift ws) {
        this.workshift = ws;

    }
    public String getVisit_description() {
        return "visit containing  "+ workshift.getId();
    }

}

The trick resides here:

GsonBuilder gsonBuilder = new GsonBuilder();
        new GraphAdapterBuilder()
        .addType(Visit.class)
        .addType(Workshift.class)
        .registerOn(gsonBuilder);

Putting all together,

public static void main(String[] args) {

        Workshift[] workshifts = new Workshift[10];
        for (int i = 0; i < workshifts.length; i++) {
            //Replace Context(i) for the real one
            workshifts[i] = new Workshift(new Context(i), "Workshift#"
                    + i);
        }
        System.out.println("Original Workshifts array:");
        for (int i = 0; i < workshifts.length; i++) {
            System.out.println(workshifts[i]);
        }
        System.out.println("===================================");

        GsonBuilder gsonBuilder = new GsonBuilder();
        new GraphAdapterBuilder()
        .addType(Visit.class)
        .addType(Workshift.class)
        .registerOn(gsonBuilder);

        Gson gson = gsonBuilder.setPrettyPrinting().create();
        String serialized = gson.toJson(workshifts);
        // System.out.println(serialized);
        Workshift[] w_array = gson.fromJson(serialized, Workshift[].class);
        // System.out.println(gson.toJson(w_array));

        System.out.println("Des-serialized Workshifts array:");
        for (int i = 0; i < w_array.length; i++) {
            System.out.println(w_array[i]);
        }
        System.out.println("===================================");

Output:

Original Workshifts array:
[Workshift element => { WD: Workshift#0, VD : visit containing  Workshift#0}
[Workshift element => { WD: Workshift#1, VD : visit containing  Workshift#1}
[Workshift element => { WD: Workshift#2, VD : visit containing  Workshift#2}
[Workshift element => { WD: Workshift#3, VD : visit containing  Workshift#3}
[Workshift element => { WD: Workshift#4, VD : visit containing  Workshift#4}
[Workshift element => { WD: Workshift#5, VD : visit containing  Workshift#5}
[Workshift element => { WD: Workshift#6, VD : visit containing  Workshift#6}
[Workshift element => { WD: Workshift#7, VD : visit containing  Workshift#7}
[Workshift element => { WD: Workshift#8, VD : visit containing  Workshift#8}
[Workshift element => { WD: Workshift#9, VD : visit containing  Workshift#9}
===================================
Des-serialized Workshifts array:
[Workshift element => { WD: Workshift#0, VD : visit containing  Workshift#0}
[Workshift element => { WD: Workshift#1, VD : visit containing  Workshift#1}
[Workshift element => { WD: Workshift#2, VD : visit containing  Workshift#2}
[Workshift element => { WD: Workshift#3, VD : visit containing  Workshift#3}
[Workshift element => { WD: Workshift#4, VD : visit containing  Workshift#4}
[Workshift element => { WD: Workshift#5, VD : visit containing  Workshift#5}
[Workshift element => { WD: Workshift#6, VD : visit containing  Workshift#6}
[Workshift element => { WD: Workshift#7, VD : visit containing  Workshift#7}
[Workshift element => { WD: Workshift#8, VD : visit containing  Workshift#8}
[Workshift element => { WD: Workshift#9, VD : visit containing  Workshift#9}
===================================

There's no error.

if you un-comment the line

// System.out.println(serialized);

The output would be like this:

[
  {
    "0x1": {
      "visit": "0x2",
      "workshift_description": "Workshift#0"
    },
    "0x2": {
      "workshift": "0x1"
    }
  },
  {
    "0x1": {
      "visit": "0x2",
      "workshift_description": "Workshift#1"
    },
    "0x2": {
      "workshift": "0x1"
    }
  },
  {
    "0x1": {
      "visit": "0x2",
      "workshift_description": "Workshift#2"
    },
    "0x2": {
      "workshift": "0x1"
    }
  },
  {
    "0x1": {
      "visit": "0x2",
      "workshift_description": "Workshift#3"
    },
    "0x2": {
      "workshift": "0x1"
    }
  },
  {
    "0x1": {
      "visit": "0x2",
      "workshift_description": "Workshift#4"
    },
    "0x2": {
      "workshift": "0x1"
    }
  },
  {
    "0x1": {
      "visit": "0x2",
      "workshift_description": "Workshift#5"
    },
    "0x2": {
      "workshift": "0x1"
    }
  },
  {
    "0x1": {
      "visit": "0x2",
      "workshift_description": "Workshift#6"
    },
    "0x2": {
      "workshift": "0x1"
    }
  },
  {
    "0x1": {
      "visit": "0x2",
      "workshift_description": "Workshift#7"
    },
    "0x2": {
      "workshift": "0x1"
    }
  },
  {
    "0x1": {
      "visit": "0x2",
      "workshift_description": "Workshift#8"
    },
    "0x2": {
      "workshift": "0x1"
    }
  },
  {
    "0x1": {
      "visit": "0x2",
      "workshift_description": "Workshift#9"
    },
    "0x2": {
      "workshift": "0x1"
    }
  }

]

That's because Gson is replacing your references, in order to avoid that exception. It's like emulating pointers

Hope it helps.

Note: Remember to copy the files GraphAdapterBuilder.java and change the line

private final ConstructorConstructor constructorConstructor = new ConstructorConstructor();

with

private final ConstructorConstructor constructorConstructor = new ConstructorConstructor(instanceCreators);

It won't compile otherwise. Maybe it's fixed right now.

Question 6

Try not to drop config, just alter the things you need.

For this task I successfully use

$dataSource = ConnectionManager::getDataSource('company_data');
$dataSource->config['schema'] = 'company_'.$id;

I don't know if database switching and mysql as engine is good pair. I use postgresql schemas for this purpose.

CakePHP switch database (using same datasource) on the fly?

Answers

php,database,cakephp,cakephp-1.3