7. Dezember 2015

by

In: horde

Kommentare deaktiviert für Horde_Rdo Many to Many relations and Horde DB Migrator

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
/**
* 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 

The Mappers:

/usr/share/php5/PEAR/www/horde/hvview/lib/Entity # cat ResourcePoolMapper.php 
 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 
 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);

Tags: , ,