Many to Many relations btween to object types or table rows are usually saved to a database using a third table.
For example, if every server can have multiple services and each service can run on multiple computers, we need a third table to store the relations:
server table:
server_id | server_name
1 | hoellenhund.internal.company.com
2 | forellenfisch.internal.company.com
service table:
service_id | service_name
1 | tomcat
2 | dovecot
relation table:
service_id | server_id
1 | 1
2 | 2
2 | 1
Horde’s ORM Layer Horde_Rdo supports creating, checking and changing such relations but it’s not very prominently documented.
Let’s look at an example.
First, we need to create the database schema. Note that the relations table has no autoincrement key, only the two columns used for lookup
/usr/share/php5/PEAR/www/horde/hvview/migration # cat 1_hvview_base_tables.php
<?php
/**
* Create Hvview base tables.
*
* Copyright 2015-2015 B1 Systems GmbH (http://www.b1-systems.de/)
*
* See the enclosed file COPYING for license information (GPL). If you
* did not receive this file, see http://www.horde.org/licenses/gpl.
*
* @author Ralf Lang
* @package Hvview
*/
class HvviewBaseTables extends Horde_Db_Migration_Base
{
/**
* Upgrade
*/
public function up()
{
$t = $this->createTable('hvview_technical_landscapes', array('autoincrementKey' => 'landscape_id'));
$t->column('landscape_name', 'string', array('limit' => 255, 'null' => false));
$t->column('period_id', 'integer', array('limit' => 11, 'null' => false));
$t->end();
$t = $this->createTable('hvview_resource_pools', array('autoincrementKey' => 'resource_pool_id'));
$t->column('pool_name', 'string', array('limit' => 255, 'null' => false));
$t->column('landscape_id', 'integer', array('limit' => 11, 'null' => false));
$t->column('period_id', 'integer', array('limit' => 11, 'null' => false));
$t->end();
$t = $this->createTable('hvview_hardware_pools', array('autoincrementKey' => 'hardware_pool_id'));
$t->column('pool_name', 'string', array('limit' => 255, 'null' => false));
$t->column('landscape_id', 'integer', array('limit' => 11, 'null' => false)); /* possibly redundant, but may speed up things */
$t->column('period_id', 'integer', array('limit' => 11, 'null' => false));
$t->end();
/*Relations table*/
$t = $this->createTable('hvview_rp_hwps', array('autoincrementKey' => false));
$t->column('resource_pool_id', 'integer', array('limit' => 11, 'null' => false));
$t->column('hardware_pool_id', 'integer', array('limit' => 11, 'null' => false));
$t->end();
$t = $this->createTable('hvview_periods', array('autoincrementKey' => 'period_id'));
$t->column('period_ts', 'integer', array('limit' => 11, 'null' => false));
$t->end();
/* We collapse hypervisor and blade server objects into one for now - let`s see if this scales well */
$t = $this->createTable('hvview_servers', array('autoincrementKey' => 'server_id'));
$t->column('period_id', 'integer', array('limit' => 11, 'null' => false));
$t->column('hardware_pool_id', 'integer', array('limit' => 11, 'null' => false));
$t->column('hostname', 'string', array('limit' => 100, 'null' => false));
$t->column('state', 'string', array('limit' => 20, 'null' => true));
$t->column('os_release', 'string', array('limit' => 20, 'null' => true));
$t->column('comment', 'string', array('limit' => 255, 'null' => true));
$t->column('hv_free_vcpu', 'integer', array('limit' => 11, 'null' => true));
$t->column('hv_free_memory', 'integer', array('limit' => 11, 'null' => true));
$t->column('hv_free_disk', 'integer', array('limit' => 11, 'null' => true));
$t->column('hv_total_vcpu', 'integer', array('limit' => 11, 'null' => true));
$t->column('hv_total_memory', 'integer', array('limit' => 11, 'null' => true));
$t->column('hv_excluded', 'integer', array('limit' => 1, 'null' => true));
$t->column('hv_vm_count', 'integer', array('limit' => 3, 'null' => true));
$t->end();
// Indices not before we have an idea which of them we need most
// $this->addIndex('hvview_items', array('item_owner'));
}
/**
* Downgrade
*/
public function down()
{
$this->dropTable('hvview_technical_landscapes');
$this->dropTable('hvview_resource_pools');
$this->dropTable('hvview_hardware_pools');
$this->dropTable('hvview_periods');
$this->dropTable('hvview_servers');
$this->dropTable('hvview_rp_hwps');
}
}
The relations are defined in a Horde_Rdo_Mapper class which also knows how to spawn objects from the rows.
The Objects
/usr/share/php5/PEAR/www/horde/hvview/lib/Entity # cat ResourcePool.php
<?php
class Hvview_Entity_ResourcePool extends Horde_Rdo_Base {
}
/usr/share/php5/PEAR/www/horde/hvview/lib/Entity # cat HardwarePool.php
The Mappers:
/usr/share/php5/PEAR/www/horde/hvview/lib/Entity # cat ResourcePoolMapper.php
<?php
class Hvview_Entity_ResourcePoolMapper extends Horde_Rdo_Mapper
{
/**
* Inflector doesn't support Horde-style tables yet
*/
protected $_classname = 'Hvview_Entity_ResourcePool';
protected $_table = 'hvview_resource_pools';
protected $_lazyRelationships = array(
'hwps' => array('type' => Horde_Rdo::MANY_TO_MANY,
'through' => 'hvview_rp_hwps',
'mapper' => 'Hvview_Entity_HardwarePoolMapper')
);
}
/usr/share/php5/PEAR/www/horde/hvview/lib/Entity # cat HardwarePoolMapper.php
<?php
class Hvview_Entity_HardwarePoolMapper extends Horde_Rdo_Mapper
{
/**
* Inflector doesn't support Horde-style tables yet
*/
protected $_classname = 'Hvview_Entity_HardwarePool';
protected $_table = 'hvview_hardware_pools';
protected $_lazyRelationships = array(
'rps' => array('type' => Horde_Rdo::MANY_TO_MANY,
'through' => 'hvview_rp_hwps',
'mapper' => 'Hvview_Entity_ResourcePoolMapper')
);
}
The relation is defined in both direction and only loaded on-demand ("lazy") as opposed to upfront when the item is created from the database rows.
Now let's fetch two items and link them:
You can do this through the mapper or through one of the two partners
Adding a relation to an object using the object
// $rm is a ResourcePoolMapper instance
// $hm is a HardwarePoolMapper instance
$rp = $rm->findOne(); // In reality, you would not pick a random item but add some criteria
$hwp = $hm->findOne();
$rp->addRelation('hwps', $hwp);
Adding a relation to an object using the mapper
// $rm is a ResourcePoolMapper instance
// $hm is a HardwarePoolMapper instance
$rp = $rm->findOne(); // In reality, you would not pick a random item but add some criteria
$hwp = $hm->findOne();
$rm->addRelation('hwps', $rp, $hwp);