some pages would not load because PHP's maximum execution time was exceeded, caused...
authorAaron Dummer <aaron@dummer.info>
Mon, 30 Dec 2013 20:40:41 +0000 (12:40 -0800)
committerAaron Dummer <aaron@dummer.info>
Mon, 30 Dec 2013 20:40:41 +0000 (12:40 -0800)
add database triggers to the EntityLink table
add unit tests: EntityLinkTriggerTest, ObjectCircularReferenceTest, TagTreeCircularReferenceTest
getObjectContentsList: fix circular reference detection
getLocationChildrenList: new function which mimics getObjectContents list
getTagChildrenList: idem
commitLinkEntities: check for circular references
commitUpdateEntityLink: idem
commitUpdateTag: idem, new function replaces tableHandler call
getLocationTrail: exit at a depth of 20, there is probably a circular reference
renderRackspace: idem, when displaying the location tree
renderTagTreeEditor: add is_assignable hidden field to the 'fallen leaves' portlet, form submission failed even before the tableHandler replacement
renderDataIntegrityReport: report circular references for locations, objects and tags
linkObjects: new ophandler replaces tableHandler
updateTag: idem

12 files changed:
ChangeLog
tests/EntityLinkTriggerTest.php [new file with mode: 0644]
tests/ObjectCircularReferenceTest.php [new file with mode: 0644]
tests/TagTreeCircularReferenceTest.php [new file with mode: 0644]
wwwroot/inc/database.php
wwwroot/inc/functions.php
wwwroot/inc/install.php
wwwroot/inc/interface.php
wwwroot/inc/navigation.php
wwwroot/inc/ophandlers.php
wwwroot/inc/popup.php
wwwroot/inc/upgrade.php

index 9784395821fb0b18b1f85746bb04e4f0e0d63057..825702dc7311e0f225a8618ddee8c2812ae7f32e 100644 (file)
--- a/ChangeLog
+++ b/ChangeLog
@@ -2,6 +2,8 @@
        bugfix: IP tree expansion button was broken when MAX_UNFILTERED_ENTITIES was set
        bugfix: fix URL detection bug introduced in bugfix to #1023 (#1103)
        bugfix: browse objects page was broken if an object's container was unnamed (#1115)
+       bugfix: some pages would not load because PHP's maximum execution time was
+               exceeded, caused by a circular reference in the location tree (#1123)
        update: enable IP addressing for all object types unless specifically excluded
        update: SNMP support for 3Com 4510G-24, Cisco SF302-08MP, 
                Linksys SRW248G4 by Rafael Driutti,
diff --git a/tests/EntityLinkTriggerTest.php b/tests/EntityLinkTriggerTest.php
new file mode 100644 (file)
index 0000000..a69c528
--- /dev/null
@@ -0,0 +1,398 @@
+<?php
+
+// Test the effectiveness of the INSERT and UPDATE triggers on the EntityLink table
+//   - if parent and child entities are the same, parent_id != child_id
+//   - if both parent and child are objects, an ObjectParentCompat rule must exist
+//   - in some scenarios, only one-to-one links are allowed
+class EntityLinkTriggerTest extends PHPUnit_Framework_TestCase
+{
+       protected static $objtypea_id, $objtypeb_id, $objtypec_id;
+       protected static $objecta_id, $objectb_id, $objectc_id;
+       protected static $locationa_id, $locationb_id, $locationc_id;
+       protected static $rowa_id, $rowb_id;
+       protected static $racka_id, $rackb_id;
+
+       public static function setUpBeforeClass ()
+       {
+               // add sample data
+               usePreparedInsertBlade ('Dictionary', array ('chapter_id' => 1, 'dict_value' => 'unit test object type a'));
+               self::$objtypea_id = lastInsertID ();
+               usePreparedInsertBlade ('Dictionary', array ('chapter_id' => 1, 'dict_value' => 'unit test object type b'));
+               self::$objtypeb_id = lastInsertID ();
+               usePreparedInsertBlade ('Dictionary', array ('chapter_id' => 1, 'dict_value' => 'unit test object type c'));
+               self::$objtypec_id = lastInsertID ();
+               commitSupplementOPC (self::$objtypea_id, self::$objtypeb_id);
+               commitSupplementOPC (self::$objtypeb_id, self::$objtypea_id);
+               self::$objecta_id = commitAddObject ('unit test object a', NULL, self::$objtypea_id, NULL);
+               self::$objectb_id = commitAddObject ('unit test object b', NULL, self::$objtypeb_id, NULL);
+               self::$objectc_id = commitAddObject ('unit test object c', NULL, self::$objtypec_id, NULL);
+               self::$locationa_id = commitAddObject ('unit test location a', NULL, 1562, NULL);
+               self::$locationb_id = commitAddObject ('unit test location b', NULL, 1562, NULL);
+               self::$locationc_id = commitAddObject ('unit test location c', NULL, 1562, NULL);
+               self::$rowa_id = commitAddObject ('unit test row a', NULL, 1561, NULL);
+               self::$rowb_id = commitAddObject ('unit test row b', NULL, 1561, NULL);
+               self::$racka_id = commitAddObject ('unit test rack a', NULL, 1560, NULL);
+               self::$rackb_id = commitAddObject ('unit test rack b', NULL, 1560, NULL);
+       }
+
+       public static function tearDownAfterClass ()
+       {
+               // remove sample data
+               commitDeleteObject (self::$objecta_id);
+               commitDeleteObject (self::$objectb_id);
+               commitDeleteObject (self::$objectc_id);
+               commitReduceOPC (self::$objtypea_id, self::$objtypeb_id);
+               commitReduceOPC (self::$objtypeb_id, self::$objtypea_id);
+               usePreparedDeleteBlade ('Dictionary', array ('dict_key' => self::$objtypea_id));
+               usePreparedDeleteBlade ('Dictionary', array ('dict_key' => self::$objtypeb_id));
+               usePreparedDeleteBlade ('Dictionary', array ('dict_key' => self::$objtypec_id));
+               commitDeleteObject (self::$locationa_id);
+               commitDeleteObject (self::$locationb_id);
+               commitDeleteObject (self::$locationc_id);
+               commitDeleteObject (self::$rowa_id);
+               commitDeleteObject (self::$rowb_id);
+               commitDeleteObject (self::$racka_id);
+               commitDeleteObject (self::$rackb_id);
+       }
+
+       public function tearDown ()
+       {
+               // delete any links created during the test
+               usePreparedExecuteBlade
+               (
+                       "DELETE FROM EntityLink WHERE parent_entity_type='object' AND child_entity_type='object' " .
+                       'AND (parent_entity_id IN (?,?,?) OR child_entity_id IN (?,?,?))',
+                       array (self::$objecta_id, self::$objectb_id, self::$objectc_id, self::$objecta_id, self::$objectb_id, self::$objectc_id)
+               );
+               usePreparedExecuteBlade
+               (
+                       "DELETE FROM EntityLink WHERE parent_entity_type='location' AND child_entity_type='location' " .
+                       'AND (parent_entity_id IN (?,?,?) OR child_entity_id IN (?,?,?))',
+                       array (self::$locationa_id, self::$locationb_id, self::$locationc_id, self::$locationa_id, self::$locationb_id, self::$locationc_id)
+               );
+               usePreparedExecuteBlade
+               (
+                       "DELETE FROM EntityLink WHERE (child_entity_type='row' AND child_entity_id IN (?,?)) " .
+                       "OR (child_entity_type='rack' AND child_entity_id IN (?,?))",
+                       array (self::$rowa_id, self::$rowb_id, self::$racka_id, self::$rackb_id)
+               );
+       }
+
+       /**
+        * @expectedException PDOException
+        */
+       public function testLinkObjectToSelfByInsert ()
+       {
+               usePreparedInsertBlade
+               (
+                       'EntityLink',
+                       array
+                       (
+                               'parent_entity_type' => 'object', 'parent_entity_id' => self::$objecta_id,
+                               'child_entity_type' => 'object', 'child_entity_id' => self::$objecta_id
+                       )
+               );
+       }
+
+       /**
+        * @expectedException PDOException
+        */
+       public function testLinkObjectToSelfByUpdate ()
+       {
+               usePreparedInsertBlade
+               (
+                       'EntityLink',
+                       array
+                       (
+                               'parent_entity_type' => 'object', 'parent_entity_id' => self::$objecta_id,
+                               'child_entity_type' => 'object', 'child_entity_id' => self::$objectb_id
+                       )
+               );
+               usePreparedUpdateBlade
+               (
+                       'EntityLink',
+                       array ('child_entity_id' => self::$objecta_id),
+                       array ('id' => lastInsertID ())
+               );
+       }
+
+       public function testCreateLinkBetweenCompatibleObjects ()
+       {
+               usePreparedInsertBlade
+               (
+                       'EntityLink',
+                       array
+                       (
+                               'parent_entity_type' => 'object', 'parent_entity_id' => self::$objecta_id,
+                               'child_entity_type' => 'object', 'child_entity_id' => self::$objectb_id
+                       )
+               );
+               $this->assertTrue (TRUE);
+       }
+
+       public function testUpdateLinkBetweenCompatibleObjects ()
+       {
+               usePreparedInsertBlade
+               (
+                       'EntityLink',
+                       array
+                       (
+                               'parent_entity_type' => 'object', 'parent_entity_id' => self::$objecta_id,
+                               'child_entity_type' => 'object', 'child_entity_id' => self::$objectb_id
+                       )
+               );
+               usePreparedUpdateBlade
+               (
+                       'EntityLink',
+                       array
+                       (
+                               'parent_entity_id' => self::$objectb_id,
+                               'child_entity_id' => self::$objecta_id
+                       ),
+                       array ('id' => lastInsertID ())
+               );
+               $this->assertTrue (TRUE);
+       }
+
+       /**
+        * @expectedException PDOException
+        */
+       public function testCreateLinkBetweenIncompatibleObjects ()
+       {
+               usePreparedInsertBlade
+               (
+                       'EntityLink',
+                       array
+                       (
+                               'parent_entity_type' => 'object', 'parent_entity_id' => self::$objecta_id,
+                               'child_entity_type' => 'object', 'child_entity_id' => self::$objectc_id
+                       )
+               );
+       }
+
+       /**
+        * @expectedException PDOException
+        */
+       public function testUpdateLinkBetweenIncompatibleObjects ()
+       {
+               usePreparedInsertBlade
+               (
+                       'EntityLink',
+                       array
+                       (
+                               'parent_entity_type' => 'object', 'parent_entity_id' => self::$objecta_id,
+                               'child_entity_type' => 'object', 'child_entity_id' => self::$objectb_id
+                       )
+               );
+               usePreparedUpdateBlade
+               (
+                       'EntityLink',
+                       array ('child_entity_id' => self::$objectc_id),
+                       array ('id' => lastInsertID ())
+               );
+       }
+
+       public function testCreateLinkBetweenLocations ()
+       {
+               usePreparedInsertBlade
+               (
+                       'EntityLink',
+                       array
+                       (
+                               'parent_entity_type' => 'location', 'parent_entity_id' => self::$locationa_id,
+                               'child_entity_type' => 'location', 'child_entity_id' => self::$locationb_id
+                       )
+               );
+               $this->assertTrue (TRUE);
+       }
+
+       /**
+        * @expectedException PDOException
+        */
+       public function testLinkLocationToMultipleLocations ()
+       {
+               usePreparedInsertBlade
+               (
+                       'EntityLink',
+                       array
+                       (
+                               'parent_entity_type' => 'location', 'parent_entity_id' => self::$locationa_id,
+                               'child_entity_type' => 'location', 'child_entity_id' => self::$locationb_id
+                       )
+               );
+               usePreparedInsertBlade
+               (
+                       'EntityLink',
+                       array
+                       (
+                               'parent_entity_type' => 'location', 'parent_entity_id' => self::$locationc_id,
+                               'child_entity_type' => 'location', 'child_entity_id' => self::$locationb_id
+                       )
+               );
+       }
+
+       public function testUpdateLinkBetweenLocations ()
+       {
+               usePreparedInsertBlade
+               (
+                       'EntityLink',
+                       array
+                       (
+                               'parent_entity_type' => 'location', 'parent_entity_id' => self::$locationa_id,
+                               'child_entity_type' => 'location', 'child_entity_id' => self::$locationb_id
+                       )
+               );
+               usePreparedUpdateBlade
+               (
+                       'EntityLink',
+                       array
+                       (
+                               'parent_entity_id' => self::$locationb_id,
+                               'child_entity_id' => self::$locationa_id
+                       ),
+                       array ('id' => lastInsertID ())
+               );
+               $this->assertTrue (TRUE);
+       }
+
+       public function testLinkRowToLocation ()
+       {
+               usePreparedInsertBlade
+               (
+                       'EntityLink',
+                       array
+                       (
+                               'parent_entity_type' => 'location', 'parent_entity_id' => self::$locationa_id,
+                               'child_entity_type' => 'row', 'child_entity_id' => self::$rowa_id
+                       )
+               );
+               $this->assertTrue (TRUE);
+       }
+
+       /**
+        * @expectedException PDOException
+        */
+       public function testLinkRowToMultipleLocations ()
+       {
+               usePreparedInsertBlade
+               (
+                       'EntityLink',
+                       array
+                       (
+                               'parent_entity_type' => 'location', 'parent_entity_id' => self::$locationa_id,
+                               'child_entity_type' => 'row', 'child_entity_id' => self::$rowa_id
+                       )
+               );
+               usePreparedInsertBlade
+               (
+                       'EntityLink',
+                       array
+                       (
+                               'parent_entity_type' => 'location', 'parent_entity_id' => self::$locationb_id,
+                               'child_entity_type' => 'row', 'child_entity_id' => self::$rowa_id
+                       )
+               );
+       }
+
+       /**
+        * @expectedException PDOException
+        */
+       public function testInvalidateRowLink ()
+       {
+               usePreparedInsertBlade
+               (
+                       'EntityLink',
+                       array
+                       (
+                               'parent_entity_type' => 'location', 'parent_entity_id' => self::$locationa_id,
+                               'child_entity_type' => 'row', 'child_entity_id' => self::$rowa_id
+                       )
+               );
+               usePreparedInsertBlade
+               (
+                       'EntityLink',
+                       array
+                       (
+                               'parent_entity_type' => 'location', 'parent_entity_id' => self::$locationb_id,
+                               'child_entity_type' => 'row', 'child_entity_id' => self::$rowb_id
+                       )
+               );
+               usePreparedUpdateBlade
+               (
+                       'EntityLink',
+                       array ('child_entity_id' => self::$rowa_id),
+                       array ('id' => lastInsertID ())
+               );
+       }
+
+       public function testLinkRackToRow ()
+       {
+               usePreparedInsertBlade
+               (
+                       'EntityLink',
+                       array
+                       (
+                               'parent_entity_type' => 'row', 'parent_entity_id' => self::$rowa_id,
+                               'child_entity_type' => 'rack', 'child_entity_id' => self::$racka_id
+                       )
+               );
+               $this->assertTrue (TRUE);
+       }
+
+       /**
+        * @expectedException PDOException
+        */
+       public function testLinkRackToMultipleRows ()
+       {
+               usePreparedInsertBlade
+               (
+                       'EntityLink',
+                       array
+                       (
+                               'parent_entity_type' => 'row', 'parent_entity_id' => self::$rowa_id,
+                               'child_entity_type' => 'rack', 'child_entity_id' => self::$racka_id
+                       )
+               );
+               usePreparedInsertBlade
+               (
+                       'EntityLink',
+                       array
+                       (
+                               'parent_entity_type' => 'row', 'parent_entity_id' => self::$rowb_id,
+                               'child_entity_type' => 'rack', 'child_entity_id' => self::$racka_id
+                       )
+               );
+       }
+
+       /**
+        * @expectedException PDOException
+        */
+       public function testInvalidateRackLink ()
+       {
+               usePreparedInsertBlade
+               (
+                       'EntityLink',
+                       array
+                       (
+                               'parent_entity_type' => 'row', 'parent_entity_id' => self::$rowa_id,
+                               'child_entity_type' => 'rack', 'child_entity_id' => self::$racka_id
+                       )
+               );
+               usePreparedInsertBlade
+               (
+                       'EntityLink',
+                       array
+                       (
+                               'parent_entity_type' => 'row', 'parent_entity_id' => self::$rowb_id,
+                               'child_entity_type' => 'rack', 'child_entity_id' => self::$rackb_id
+                       )
+               );
+               usePreparedUpdateBlade
+               (
+                       'EntityLink',
+                       array ('child_entity_id' => self::$racka_id),
+                       array ('id' => lastInsertID ())
+               );
+       }
+}
+?>
diff --git a/tests/ObjectCircularReferenceTest.php b/tests/ObjectCircularReferenceTest.php
new file mode 100644 (file)
index 0000000..b5295e0
--- /dev/null
@@ -0,0 +1,120 @@
+<?php
+
+// An object's parent may not be one of its children.
+// The same principle applies to locations, which are stored in the DB as objects.
+// commitLinkEntities and commitUpdateEntityLink should each detect this and raise an exception
+class ObjectCircularReferenceTest extends PHPUnit_Framework_TestCase
+{
+       protected static $objtype_id;
+       protected static $objecta_id, $objectb_id, $objectc_id;
+       protected static $locationa_id, $locationb_id, $locationc_id;
+
+       public static function setUpBeforeClass ()
+       {
+               // add sample data
+               usePreparedInsertBlade ('Dictionary', array ('chapter_id' => 1, 'dict_value' => 'unit test object type'));
+               self::$objtype_id = lastInsertID ();
+               commitSupplementOPC (self::$objtype_id, self::$objtype_id);
+               self::$objecta_id = commitAddObject ('unit test object a', NULL, self::$objtype_id, NULL);
+               self::$objectb_id = commitAddObject ('unit test object b', NULL, self::$objtype_id, NULL);
+               self::$objectc_id = commitAddObject ('unit test object c', NULL, self::$objtype_id, NULL);
+               self::$locationa_id = commitAddObject ('unit test location a', NULL, 1562, NULL);
+               self::$locationb_id = commitAddObject ('unit test location b', NULL, 1562, NULL);
+               self::$locationc_id = commitAddObject ('unit test location c', NULL, 1562, NULL);
+       }
+
+       public static function tearDownAfterClass ()
+       {
+               // remove sample data
+               commitDeleteObject (self::$objecta_id);
+               commitDeleteObject (self::$objectb_id);
+               commitDeleteObject (self::$objectc_id);
+               commitReduceOPC (self::$objtype_id, self::$objtype_id);
+               usePreparedDeleteBlade ('Dictionary', array ('dict_key' => self::$objtype_id));
+               commitDeleteObject (self::$locationa_id);
+               commitDeleteObject (self::$locationb_id);
+               commitDeleteObject (self::$locationc_id);
+       }
+
+       public function tearDown ()
+       {
+               // delete any links created during the test
+               usePreparedExecuteBlade
+               (
+                       "DELETE FROM EntityLink WHERE parent_entity_type='location' AND child_entity_type='location' " .
+                       'AND (parent_entity_id IN (?,?,?) OR child_entity_id IN (?,?,?))',
+                       array
+                       (
+                               self::$locationa_id, self::$locationb_id, self::$locationc_id,
+                               self::$locationa_id, self::$locationb_id, self::$locationc_id
+                       )
+               );
+               usePreparedExecuteBlade
+               (
+                       "DELETE FROM EntityLink WHERE parent_entity_type='object' AND child_entity_type='object' " .
+                       'AND (parent_entity_id IN (?,?,?) OR child_entity_id IN (?,?,?))',
+                       array
+                       (
+                               self::$objecta_id, self::$objectb_id, self::$objectc_id,
+                               self::$objecta_id, self::$objectb_id, self::$objectc_id
+                       )
+               );
+       }
+
+       /**
+        * @expectedException RackTablesError
+        */
+       public function testCreateObjectCircularReference ()
+       {
+               // set A as the parent of B, and B as the parent of C
+               commitLinkEntities ('object', self::$objecta_id, 'object', self::$objectb_id);
+               commitLinkEntities ('object', self::$objectb_id, 'object', self::$objectc_id);
+               // setting C as the parent of A should fail
+               commitLinkEntities ('object', self::$objectc_id, 'object', self::$objecta_id);
+       }
+
+       /**
+        * @expectedException RackTablesError
+        */
+       public function testUpdateObjectCircularReference ()
+       {
+               // set A as the parent of B, and B as the parent of C
+               commitLinkEntities ('object', self::$objecta_id, 'object', self::$objectb_id);
+               commitLinkEntities ('object', self::$objectb_id, 'object', self::$objectc_id);
+               // reversing the link between B and C should fail
+               commitUpdateEntityLink
+               (
+                       'object', self::$objectb_id, 'object', self::$objectc_id,
+                       'object', self::$objectc_id, 'object', self::$objectb_id
+               );
+       }
+
+       /**
+        * @expectedException RackTablesError
+        */
+       public function testCreateLocationCircularReference ()
+       {
+               // set A as the parent of B, and B as the parent of C
+               commitLinkEntities ('location', self::$locationa_id, 'location', self::$locationb_id);
+               commitLinkEntities ('location', self::$locationb_id, 'location', self::$locationc_id);
+               // setting C as the parent of A should fail
+               commitLinkEntities ('location', self::$locationc_id, 'location', self::$locationa_id);
+       }
+
+       /**
+        * @expectedException RackTablesError
+        */
+       public function testUpdateLocationCircularReference ()
+       {
+               // set A as the parent of B, and B as the parent of C
+               commitLinkEntities ('location', self::$locationa_id, 'location', self::$locationb_id);
+               commitLinkEntities ('location', self::$locationb_id, 'location', self::$locationc_id);
+               // reversing the link between B and C should fail
+               commitUpdateEntityLink
+               (
+                       'location', self::$locationb_id, 'location', self::$locationc_id,
+                       'location', self::$locationc_id, 'location', self::$locationb_id
+               );
+       }
+}
+?>
diff --git a/tests/TagTreeCircularReferenceTest.php b/tests/TagTreeCircularReferenceTest.php
new file mode 100644 (file)
index 0000000..fb5e347
--- /dev/null
@@ -0,0 +1,45 @@
+<?php
+
+// a tag's parent may not be one of its children
+// commitUpdateTag should detect this and raise an exception
+class TagTreeCircularReferenceTest extends PHPUnit_Framework_TestCase
+{
+       protected $taga_id, $tagb_id, $tagc_id;
+
+       public function setUp ()
+       {
+               // add sample data
+               usePreparedInsertBlade ('TagTree', array ('tag' => 'unit test tag a'));
+               $this->taga_id = lastInsertID ();
+               usePreparedInsertBlade ('TagTree', array ('tag' => 'unit test tag b'));
+               $this->tagb_id = lastInsertID ();
+               usePreparedInsertBlade ('TagTree', array ('tag' => 'unit test tag c'));
+               $this->tagc_id = lastInsertID ();
+       }
+
+       public function tearDown ()
+       {
+               // remove sample data
+               usePreparedExecuteBlade
+               (
+                       'UPDATE TagTree SET parent_id = NULL WHERE id IN (?,?,?)',
+                       array ($this->taga_id, $this->tagb_id, $this->tagc_id)
+               );
+               usePreparedDeleteBlade ('TagTree', array ('id' => $this->taga_id));
+               usePreparedDeleteBlade ('TagTree', array ('id' => $this->tagb_id));
+               usePreparedDeleteBlade ('TagTree', array ('id' => $this->tagc_id));
+       }
+
+       /**
+        * @expectedException RackTablesError
+        */
+       public function testCreateCircularReference ()
+       {
+               // set A as the parent of B, and B as the parent of C
+               commitUpdateTag ($this->tagb_id, 'unit test tag b', $this->taga_id, 'yes');
+               commitUpdateTag ($this->tagc_id, 'unit test tag c', $this->tagb_id, 'yes');
+               // setting C as the parent of A should fail
+               commitUpdateTag ($this->taga_id, 'unit test tag a', $this->tagc_id, 'yes');
+       }
+}
+?>
index 8b5e98bcb06ff044ca9979f98e37b3c0208a6d50..bce0437cdc6d37ea964466f2e36476117354d69d 100644 (file)
@@ -1059,31 +1059,82 @@ function getEntityRelatives ($type, $entity_type, $entity_id)
        return $ret;
 }
 
-# This function is recursive and returns only object IDs.
-function getObjectContentsList ($object_id)
+// This function is recursive and returns only object IDs.
+function getObjectContentsList ($object_id, $children = array ())
 {
-       $ret = array();
+       $self = __FUNCTION__;
        $result = usePreparedSelectBlade
        (
                'SELECT child_entity_id FROM EntityLink ' .
                'WHERE parent_entity_type = "object" AND child_entity_type = "object" AND parent_entity_id = ?',
                array ($object_id)
        );
-       # Free this result before the called copy builds its one.
        $rows = $result->fetchAll (PDO::FETCH_ASSOC);
        unset ($result);
        foreach ($rows as $row)
        {
-               if (in_array ($row['child_entity_id'], $ret))
-                       throw new RackTablesError ("Cyclic dependency for object ${object_id}", RackTablesError::INTERNAL);
-               $ret[] = $row['child_entity_id'];
-               $ret = array_merge ($ret, call_user_func (__FUNCTION__, $row['child_entity_id']));
+               if (in_array ($row['child_entity_id'], $children))
+                       throw new RackTablesError ("Circular reference for object ${object_id}", RackTablesError::INTERNAL);
+               $children[] = $row['child_entity_id'];
+               $children = array_merge ($children, $self ($row['child_entity_id'], $children));
        }
-       return $ret;
+       return $children;
+}
+
+// This function is recursive and returns only location IDs.
+function getLocationChildrenList ($location_id, $children = array ())
+{
+       $self = __FUNCTION__;
+       $result = usePreparedSelectBlade ('SELECT id FROM Location WHERE parent_id = ?', array ($location_id));
+       $rows = $result->fetchAll (PDO::FETCH_ASSOC);
+       unset ($result);
+       foreach ($rows as $row)
+       {
+               if (in_array ($row['id'], $children))
+                       throw new RackTablesError ("Circular reference for location ${location_id}", RackTablesError::INTERNAL);
+               $children[] = $row['id'];
+               $children = array_merge ($children, $self ($row['id'], $children));
+       }
+       return $children;
+}
+
+// This function is recursive and returns only tag IDs.
+function getTagChildrenList ($tag_id, $children = array ())
+{
+       $self = __FUNCTION__;
+       $result = usePreparedSelectBlade ('SELECT id FROM TagTree WHERE parent_id = ?', array ($tag_id));
+       $rows = $result->fetchAll (PDO::FETCH_ASSOC);
+       unset ($result);
+       foreach ($rows as $row)
+       {
+               if (in_array ($row['id'], $children))
+                       throw new RackTablesError ("Circular reference for tag ${tag_id}", RackTablesError::INTERNAL);
+               $children[] = $row['id'];
+               $children = array_merge ($children, $self ($row['id'], $children));
+       }
+       return $children;
 }
 
 function commitLinkEntities ($parent_entity_type, $parent_entity_id, $child_entity_type, $child_entity_id)
 {
+       // a location's parent may not be one of its children
+       if
+       (
+               $parent_entity_type == 'location' and
+               $child_entity_type == 'location' and
+               in_array ($parent_entity_id, getLocationChildrenList ($child_entity_id))
+       )
+               throw new RackTablesError ("Circular reference for location ${parent_entity_id}", RackTablesError::INTERNAL);
+
+       // an object's container may not be one of its contained objects
+       if
+       (
+               $parent_entity_type == 'object' and
+               $child_entity_type == 'object' and
+               in_array ($parent_entity_id, getObjectContentsList ($child_entity_id))
+       )
+               throw new RackTablesError ("Circular reference for object ${parent_entity_id}", RackTablesError::INTERNAL);
+
        usePreparedInsertBlade
        (
                'EntityLink',
@@ -1103,14 +1154,40 @@ function commitUpdateEntityLink
        $new_parent_entity_type, $new_parent_entity_id, $new_child_entity_type, $new_child_entity_id
 )
 {
-       usePreparedExecuteBlade
+       // a location's parent may not be one of its children
+       if
        (
-               'UPDATE EntityLink SET parent_entity_type=?, parent_entity_id=?, child_entity_type=?, child_entity_id=? ' .
-               'WHERE parent_entity_type=? AND parent_entity_id=? AND child_entity_type=? AND child_entity_id=?',
+               $new_parent_entity_type == 'location' and
+               $new_child_entity_type == 'location' and
+               in_array ($new_parent_entity_id, getLocationChildrenList ($new_child_entity_id))
+       )
+               throw new RackTablesError ("Circular reference for location ${new_parent_entity_id}", RackTablesError::INTERNAL);
+
+       // an object's container may not be one of its contained objects
+       if
+       (
+               $new_parent_entity_type == 'object' and
+               $new_child_entity_type == 'object' and
+               in_array ($new_parent_entity_id, getObjectContentsList ($new_child_entity_id))
+       )
+               throw new RackTablesError ("Circular reference for object ${new_parent_entity_id}", RackTablesError::INTERNAL);
+
+       usePreparedUpdateBlade
+       (
+               'EntityLink',
                array
                (
-                       $new_parent_entity_type, $new_parent_entity_id, $new_child_entity_type, $new_child_entity_id,
-                       $old_parent_entity_type, $old_parent_entity_id, $old_child_entity_type, $old_child_entity_id
+                       'parent_entity_type' => $new_parent_entity_type,
+                       'parent_entity_id' => $new_parent_entity_id,
+                       'child_entity_type' => $new_child_entity_type,
+                       'child_entity_id' => $new_child_entity_id
+               ),
+               array
+               (
+                       'parent_entity_type' => $old_parent_entity_type,
+                       'parent_entity_id' => $old_parent_entity_id,
+                       'child_entity_type' => $old_child_entity_type,
+                       'child_entity_id' => $old_child_entity_id
                )
        );
 }
@@ -4049,6 +4126,25 @@ function deleteTagForEntity ($entity_realm, $entity_id, $tag_id)
        usePreparedDeleteBlade ('TagStorage', array ('entity_realm' => $entity_realm, 'entity_id' => $entity_id, 'tag_id' => $tag_id));
 }
 
+function commitUpdateTag ($tag_id, $tag_name, $parent_id, $is_assignable)
+{
+       // a tag's parent may not be one of its children
+       if ($parent_id > 0 && in_array ($parent_id, getTagChildrenList ($tag_id)))
+               throw new RackTablesError ("Circular reference for tag ${tag_id}", RackTablesError::INTERNAL);
+
+       usePreparedUpdateBlade
+       (
+               'TagTree',
+               array
+               (
+                       'tag' => $tag_name,
+                       'parent_id' => $parent_id == 0 ? NULL : $parent_id,
+                       'is_assignable' => $is_assignable
+               ),
+               array ('id' => $tag_id)
+       );
+}
+
 // Push a record into TagStorage unconditionally.
 function addTagForEntity ($realm, $entity_id, $tag_id)
 {
index 341f153c80032f33ba59886cc4c90485ae91b196..2fdbf680c878b590afcff6df27698f3172125ed7 100644 (file)
@@ -6065,13 +6065,20 @@ function getLocationTrail ($location_id, $link = TRUE, $spacer = ' : ')
        // prepend parent location(s) to given location string
        $name = '';
        $id = $location_id;
+       $locationIdx = 0;
        while (isset ($id))
        {
+               if ($locationIdx == 20)
+               {
+                       showWarning ('Warning: There is likely a circular reference in the location tree.');
+                       break;
+               }
                if ($link)
                        $name = mkA ($location_tree[$id]['name'], 'location', $id) . $spacer . $name;
                else
                        $name = $location_tree[$id]['name'] . $spacer . $name;
                $id = $location_tree[$id]['parent_id'];
+               $locationIdx++;
        }
        return substr ($name, 0, 0 - strlen ($spacer));
 }
index 8974b06fd5568dd2a29b65588641f1e4d8728a86..3d151db189613ad93a30ef0b26e4afa40b2270d7 100644 (file)
@@ -1151,13 +1151,56 @@ function get_pseudo_file ($name)
   CONSTRAINT `VSEnabledPorts-FK-vs_id-proto-vport` FOREIGN KEY (`vs_id`, `proto`, `vport`) REFERENCES `VSPorts` (`vs_id`, `proto`, `vport`) ON DELETE CASCADE
 ) ENGINE=InnoDB";
 
+               $entitylink_trigger_body = <<<ENDOFTRIGGER
+EntityLinkTrigger:BEGIN
+  DECLARE parent_objtype, child_objtype, count INTEGER;
+
+  # forbid linking an entity to itself
+  IF NEW.parent_entity_type = NEW.child_entity_type AND NEW.parent_entity_id = NEW.child_entity_id THEN
+    SET NEW.parent_entity_id = NULL;
+    LEAVE EntityLinkTrigger;
+  END IF;
+
+  # in some scenarios, only one parent is allowed
+  CASE CONCAT(NEW.parent_entity_type, '.', NEW.child_entity_type)
+    WHEN 'location.location' THEN
+      SELECT COUNT(*) INTO count FROM EntityLink WHERE parent_entity_type = 'location' AND child_entity_type = 'location' AND child_entity_id = NEW.child_entity_id;
+    WHEN 'location.row' THEN
+      SELECT COUNT(*) INTO count FROM EntityLink WHERE parent_entity_type = 'location' AND child_entity_type = 'row' AND child_entity_id = NEW.child_entity_id;
+    WHEN 'row.rack' THEN
+      SELECT COUNT(*) INTO count FROM EntityLink WHERE parent_entity_type = 'row' AND child_entity_type = 'rack' AND child_entity_id = NEW.child_entity_id;
+    ELSE
+      # some other scenario, assume it is valid
+      SET count = 0;
+  END CASE; 
+  IF count > 0 THEN
+    SET NEW.parent_entity_id = NULL;
+    LEAVE EntityLinkTrigger;
+  END IF;
+
+  IF NEW.parent_entity_type = 'object' AND NEW.child_entity_type = 'object' THEN
+    # lock objects to prevent concurrent link establishment
+    SELECT objtype_id INTO parent_objtype FROM Object WHERE id = NEW.parent_entity_id FOR UPDATE;
+    SELECT objtype_id INTO child_objtype FROM Object WHERE id = NEW.child_entity_id FOR UPDATE;
+
+    # only permit the link if object types are compatibile
+    SELECT COUNT(*) INTO count FROM ObjectParentCompat WHERE parent_objtype_id = parent_objtype AND child_objtype_id = child_objtype;
+    IF count = 0 THEN
+      SET NEW.parent_entity_id = NULL;
+    END IF;
+  END IF;
+END;
+ENDOFTRIGGER;
+               $query[] = "CREATE TRIGGER `EntityLink-before-insert` BEFORE INSERT ON `EntityLink` FOR EACH ROW $entitylink_trigger_body";
+               $query[] = "CREATE TRIGGER `EntityLink-before-update` BEFORE UPDATE ON `EntityLink` FOR EACH ROW $entitylink_trigger_body";
                $link_trigger_body = <<<ENDOFTRIGGER
-BEGIN
+LinkTrigger:BEGIN
   DECLARE tmp, porta_type, portb_type, count INTEGER;
 
   IF NEW.porta = NEW.portb THEN
     # forbid connecting a port to itself
     SET NEW.porta = NULL;
+    LEAVE LinkTrigger;
   ELSEIF NEW.porta > NEW.portb THEN
     # force porta < portb
     SET tmp = NEW.porta;
index 283a25290c34672bb82b6aa17a52cf4fc3de45f1..5fb4aeebf07a4396f1339f142ec50d6e349cf06b 100644 (file)
@@ -427,7 +427,9 @@ function renderRackspace ()
                        // Zero value effectively disables the limit.
                        $maxPerRow = getConfigVar ('RACKS_PER_ROW');
                        $order = 'odd';
-                       if (count ($rows))
+                       if (! count ($rows))
+                               echo "<h2>No rows found</h2>\n";
+                       else
                        {
                                echo '<table border=0 cellpadding=10 class=cooltable>';
                                echo '<tr><th class=tdleft>Location</th><th class=tdleft>Row</th><th class=tdleft>Racks</th></tr>';
@@ -445,22 +447,29 @@ function renderRackspace ()
                                                continue;
                                        $rackListIdx = 0;
                                        echo "<tr class=row_${order}><th class=tdleft>";
+                                       $locationIdx = 0;
                                        $locationTree = '';
                                        while ($location_id)
                                        {
-                                                       $parentLocation = spotEntity ('location', $location_id);
-                                                       $locationTree = "&raquo; <a href='" .
-                                                               makeHref(array('page'=>'location', 'location_id'=>$parentLocation['id'])) .
-                                                               "${cellfilter['urlextra']}'>${parentLocation['name']}</a> " .
-                                                               $locationTree;
-                                                       $location_id = $parentLocation['parent_id'];
+                                               if ($locationIdx == 20)
+                                               {
+                                                       showWarning ("Warning: There is likely a circular reference in the location tree.  Investigate location ${location_id}.");
+                                                       break;
+                                               }
+                                               $parentLocation = spotEntity ('location', $location_id);
+                                               $locationTree = "&raquo; <a href='" .
+                                                       makeHref(array('page'=>'location', 'location_id'=>$parentLocation['id'])) .
+                                                       "${cellfilter['urlextra']}'>${parentLocation['name']}</a> " .
+                                                       $locationTree;
+                                               $location_id = $parentLocation['parent_id'];
+                                               $locationIdx++;
                                        }
                                        $locationTree = substr ($locationTree, 8);
                                        echo $locationTree;
                                        echo "</th><th class=tdleft><a href='".makeHref(array('page'=>'row', 'row_id'=>$row_id))."${cellfilter['urlextra']}'>${row_name}</a></th>";
                                        echo "<th class=tdleft><table border=0 cellspacing=5><tr>";
-                                       if (!count ($rackList))
-                                               echo "<td>(empty row)</td>";
+                                       if (! count ($rackList))
+                                               echo '<td>(empty row)</td>';
                                        else
                                                foreach ($rackList as $rack)
                                                {
@@ -483,8 +492,6 @@ function renderRackspace ()
                                }
                                echo "</table>\n";
                        }
-                       else
-                               echo "<h2>No rows found</h2>\n";
                }
        }
        echo '</td><td class=pcright width="25%">';
@@ -965,7 +972,7 @@ function renderEditObjectForm()
                        echo "<th class=tdright>${label}</th><td class=tdleft>";
                        echo mkA ($parent_details['name'], 'object', $parent_details['entity_id']);
                        echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
-                       echo getOpLink (array('op'=>'unlinkEntities', 'link_id'=>$link_id), '', 'cut', 'Unlink container');
+                       echo getOpLink (array('op'=>'unlinkObjects', 'link_id'=>$link_id), '', 'cut', 'Unlink container');
                        echo "</td></tr>\n";
                        $label = '&nbsp;';
                }
@@ -4953,6 +4960,7 @@ END
                foreach ($otags as $taginfo)
                {
                        printOpFormIntro ('updateTag', array ('tag_id' => $taginfo['id'], 'tag_name' => $taginfo['tag']));
+                       echo "<input type=hidden name=is_assignable value=${taginfo['is_assignable']}>";
                        echo '<tr>';
                        echo '<td>' . $taginfo['tag'] . '</td>';
                        echo '<td>' . getSelect ($options, array ('name' => 'parent_id'), $taglist[$taginfo['id']]['parent_id']) . '</td>';
@@ -9489,6 +9497,114 @@ function renderDataIntegrityReport ()
                finishPortLet ();
        }
 
+       // check 10: circular references
+       //     - all affected members of the tree are displayed
+       //     - it would be beneficial to only display the offending records
+       // check 10.1: locations
+       $invalids = array ();
+       $locations = listCells ('location');
+       foreach ($locations as $location)
+       {
+               try
+               {
+                       $children = getLocationChildrenList ($location['id']);
+               }
+               catch (RackTablesError $e)
+               {
+                       $invalids[] = $location;
+               }
+       }
+       if (count ($invalids))
+       {
+               $violations = TRUE;
+               startPortlet ('Locations: Tree Contains Circular References (' . count ($invalids) . ')');
+               echo "<table cellpadding=5 cellspacing=0 align=center class=cooltable>\n";
+               echo "<tr><th>Child ID</th><th>Child Location</th><th>Parent ID</th><th>Parent Location</th></tr>\n";
+               $order = 'odd';
+               foreach ($invalids as $invalid)
+               {
+                       echo "<tr class=row_${order}>";
+                       echo "<td>${invalid['id']}</td>";
+                       echo "<td>${invalid['name']}</td>";
+                       echo "<td>${invalid['parent_id']}</td>";
+                       echo "<td>${invalid['parent_name']}</td>";
+                       echo "</tr>\n";
+                       $order = $nextorder[$order];
+               }
+               echo "</table>\n";
+               finishPortLet ();
+       }
+
+       // check 10.2: objects
+       $invalids = array ();
+       $objects = listCells ('object');
+       foreach ($objects as $object)
+       {
+               try
+               {
+                       $children = getObjectContentsList ($object['id']);
+               }
+               catch (RackTablesError $e)
+               {
+                       $invalids[] = $object;
+               }
+       }
+       if (count ($invalids))
+       {
+               $violations = TRUE;
+               startPortlet ('Objects: Tree Contains Circular References (' . count ($invalids) . ')');
+               echo "<table cellpadding=5 cellspacing=0 align=center class=cooltable>\n";
+               echo "<tr><th>Contained ID</th><th>Contained Object</th><th>Container ID</th><th>Container Object</th></tr>\n";
+               $order = 'odd';
+               foreach ($invalids as $invalid)
+               {
+                       echo "<tr class=row_${order}>";
+                       echo "<td>${invalid['id']}</td>";
+                       echo "<td>${invalid['name']}</td>";
+                       echo "<td>${invalid['container_id']}</td>";
+                       echo "<td>${invalid['container_name']}</td>";
+                       echo "</tr>\n";
+                       $order = $nextorder[$order];
+               }
+               echo "</table>\n";
+               finishPortLet ();
+       }
+
+       // check 10.3: tags
+       $invalids = array ();
+       $tags = getTagList ();
+       foreach ($tags as $tag)
+       {
+               try
+               {
+                       $children = getTagChildrenList ($tag['id']);
+               }
+               catch (RackTablesError $e)
+               {
+                       $invalids[] = $tag;
+               }
+       }
+       if (count ($invalids))
+       {
+               $violations = TRUE;
+               startPortlet ('Tags: Tree Contains Circular References (' . count ($invalids) . ')');
+               echo "<table cellpadding=5 cellspacing=0 align=center class=cooltable>\n";
+               echo "<tr><th>Child ID</th><th>Child Tag</th><th>Parent ID</th><th>Parent Tag</th></tr>\n";
+               $order = 'odd';
+               foreach ($invalids as $invalid)
+               {
+                       echo "<tr class=row_${order}>";
+                       echo "<td>${invalid['id']}</td>";
+                       echo "<td>${invalid['tag']}</td>";
+                       echo "<td>${invalid['parent_id']}</td>";
+                       printf('<td>%s</td>', $tags[$invalid['parent_id']]['tag']);
+                       echo "</tr>\n";
+                       $order = $nextorder[$order];
+               }
+               echo "</table>\n";
+               finishPortLet ();
+       }
+
        if (! $violations)
                echo '<h2>No integrity violations found</h2>';
 }
index b67106294badacbaf66ef3f447928d1bf3de5b66..c32649a8df4406eb4eab6ccc1728752bd61d9ef1 100644 (file)
@@ -214,8 +214,8 @@ $trigger['object']['8021qsync'] = 'trigger_object_8021qsync';
 $trigger['object']['cacti'] = 'triggerCactiGraphs';
 $trigger['object']['munin'] = 'triggerMuninGraphs';
 $trigger['object']['ucs'] = 'trigger_ucs';
-$ophandler['object']['edit']['linkEntities'] = 'tableHandler';
-$ophandler['object']['edit']['unlinkEntities'] = 'tableHandler';
+$ophandler['object']['edit']['linkObjects'] = 'linkObjects';
+$ophandler['object']['edit']['unlinkObjects'] = 'tableHandler';
 $ophandler['object']['rackspace']['updateObjectAllocation'] = 'updateObjectAllocation';
 $ophandler['object']['ports']['addPort'] = 'addPortForObject';
 $ophandler['object']['ports']['editPort'] = 'editPortForObject';
@@ -604,7 +604,7 @@ $tabhandler['tagtree']['default'] = 'renderTagTree';
 $tabhandler['tagtree']['edit'] = 'renderTagTreeEditor';
 $ophandler['tagtree']['edit']['createTag'] = 'tableHandler';
 $ophandler['tagtree']['edit']['destroyTag'] = 'tableHandler';
-$ophandler['tagtree']['edit']['updateTag'] = 'tableHandler';
+$ophandler['tagtree']['edit']['updateTag'] = 'updateTag';
 
 $page['myaccount']['title'] = 'My account';
 $page['myaccount']['parent'] = 'config';
index 5946cd92fb5f695f18ae05011401ce002ef8fcb9..82615521793a731d1a3268e8819f42bd56df868a 100644 (file)
@@ -53,19 +53,7 @@ $msgcode = array();
 global $opspec_list;
 $opspec_list = array();
 
-$opspec_list['object-edit-linkEntities'] = array
-(
-       'table' => 'EntityLink',
-       'action' => 'INSERT',
-       'arglist' => array
-       (
-               array ('url_argname' => 'parent_entity_type', 'assertion' => 'string'), # FIXME enum
-               array ('url_argname' => 'parent_entity_id', 'assertion' => 'uint'),
-               array ('url_argname' => 'child_entity_type', 'assertion' => 'string'), # FIXME enum
-               array ('url_argname' => 'child_entity_id', 'assertion' => 'uint'),
-       ),
-);
-$opspec_list['object-edit-unlinkEntities'] = array
+$opspec_list['object-edit-unlinkObjects'] = array
 (
        'table' => 'EntityLink',
        'action' => 'DELETE',
@@ -443,21 +431,6 @@ $opspec_list['tagtree-edit-destroyTag'] = array
                array ('url_argname' => 'tag_id', 'table_colname' => 'id', 'assertion' => 'uint'),
        ),
 );
-$opspec_list['tagtree-edit-updateTag'] = array
-(
-       'table' => 'TagTree',
-       'action' => 'UPDATE',
-       'set_arglist' => array
-       (
-               array ('url_argname' => 'tag_name', 'table_colname' => 'tag', 'assertion' => 'tag'),
-               array ('url_argname' => 'parent_id', 'assertion' => 'uint0', 'if_empty' => 'NULL'),
-               array ('url_argname' => 'is_assignable', 'assertion' => 'enum/yesno'),
-       ),
-       'where_arglist' => array
-       (
-               array ('url_argname' => 'tag_id', 'table_colname' => 'id', 'assertion' => 'uint'),
-       ),
-);
 $opspec_list['8021q-vstlist-add'] = array
 (
        'table' => 'VLANSwitchTemplate',
@@ -1331,6 +1304,23 @@ function addLotOfObjects()
        }
 }
 
+function linkObjects ()
+{
+       assertStringArg ('parent_entity_type');
+       assertUIntArg ('parent_entity_id');
+       assertStringArg ('child_entity_type');
+       assertUIntArg ('child_entity_id');
+
+       commitLinkEntities
+       (
+               $_REQUEST['parent_entity_type'],
+               $_REQUEST['parent_entity_id'],
+               $_REQUEST['child_entity_type'],
+               $_REQUEST['child_entity_id']
+       );
+       showSuccess ('Container set successfully');
+}
+
 $msgcode['deleteObject']['OK'] = 7;
 function deleteObject ()
 {
@@ -2057,6 +2047,16 @@ function generateAutoPorts ()
        return buildRedirectURL (NULL, 'ports');
 }
 
+function updateTag ()
+{
+       assertUIntArg ('tag_id');
+       genericAssertion ('tag_name', 'tag');
+       assertUIntArg ('parent_id', TRUE);
+       genericAssertion ('is_assignable', 'enum/yesno');
+       commitUpdateTag ($_REQUEST['tag_id'], $_REQUEST['tag_name'], $_REQUEST['parent_id'], $_REQUEST['is_assignable']);
+       showSuccess ('Tag updated successfully');
+}
+
 $msgcode['saveEntityTags']['OK'] = 43;
 function saveEntityTags ()
 {
index 166cb70a8360a2edbed3642bef44850fc1d91bf2..c6bf0e9193193ddbacc404e458215f1060b2d68f 100644 (file)
@@ -237,7 +237,7 @@ function renderPopupObjectSelector()
        echo '<br>';
        echo "<input type=submit value='Proceed' onclick='".
                "if (getElementById(\"parents\").value != \"\") {".
-               "       opener.location=\"?module=redirect&page=object&tab=edit&op=linkEntities&object_id=${object_id}&child_entity_type=object&child_entity_id=${object_id}&parent_entity_type=object&parent_entity_id=\"+getElementById(\"parents\").value; ".
+               "       opener.location=\"?module=redirect&page=object&tab=edit&op=linkObjects&object_id=${object_id}&child_entity_type=object&child_entity_id=${object_id}&parent_entity_type=object&parent_entity_id=\"+getElementById(\"parents\").value; ".
                "       window.close();}'>";
        echo '</form></div>';
 }
index 0f60b6dd123d702485800df33bb7df5d47188e15..34cad3020dfca6f6dc7055ceafe90b40eaeb1eab 100644 (file)
@@ -1379,13 +1379,64 @@ CREATE TABLE `VSEnabledPorts` (
                                die;
                        }
 
+                       // for the UNIQUE key to work, portb needs to be > porta
+                       $result = $dbxlink->query ('SELECT porta, portb FROM `Link` WHERE porta > portb');
+                       $links = $result->fetchAll (PDO::FETCH_ASSOC);
+                       unset ($result);
+                       foreach ($links as $link)
+                               $query[] = "UPDATE `Link` SET `porta`=${link['portb']}, `portb`=${link['porta']} WHERE `porta`=${link['porta']} AND `portb`=${link['portb']}";
+
+                       // add triggers
+                       $entitylink_trigger_body = <<<ENDOFTRIGGER
+EntityLinkTrigger:BEGIN
+  DECLARE parent_objtype, child_objtype, count INTEGER;
+
+  # forbid linking an entity to itself
+  IF NEW.parent_entity_type = NEW.child_entity_type AND NEW.parent_entity_id = NEW.child_entity_id THEN
+    SET NEW.parent_entity_id = NULL;
+    LEAVE EntityLinkTrigger;
+  END IF;
+
+  # in some scenarios, only one parent is allowed
+  CASE CONCAT(NEW.parent_entity_type, '.', NEW.child_entity_type)
+    WHEN 'location.location' THEN
+      SELECT COUNT(*) INTO count FROM EntityLink WHERE parent_entity_type = 'location' AND child_entity_type = 'location' AND child_entity_id = NEW.child_entity_id;
+    WHEN 'location.row' THEN
+      SELECT COUNT(*) INTO count FROM EntityLink WHERE parent_entity_type = 'location' AND child_entity_type = 'row' AND child_entity_id = NEW.child_entity_id;
+    WHEN 'row.rack' THEN
+      SELECT COUNT(*) INTO count FROM EntityLink WHERE parent_entity_type = 'row' AND child_entity_type = 'rack' AND child_entity_id = NEW.child_entity_id;
+    ELSE
+      # some other scenario, assume it is valid
+      SET count = 0;
+  END CASE; 
+  IF count > 0 THEN
+    SET NEW.parent_entity_id = NULL;
+    LEAVE EntityLinkTrigger;
+  END IF;
+
+  IF NEW.parent_entity_type = 'object' AND NEW.child_entity_type = 'object' THEN
+    # lock objects to prevent concurrent link establishment
+    SELECT objtype_id INTO parent_objtype FROM Object WHERE id = NEW.parent_entity_id FOR UPDATE;
+    SELECT objtype_id INTO child_objtype FROM Object WHERE id = NEW.child_entity_id FOR UPDATE;
+
+    # only permit the link if object types are compatibile
+    SELECT COUNT(*) INTO count FROM ObjectParentCompat WHERE parent_objtype_id = parent_objtype AND child_objtype_id = child_objtype;
+    IF count = 0 THEN
+      SET NEW.parent_entity_id = NULL;
+    END IF;
+  END IF;
+END;
+ENDOFTRIGGER;
+                       $query[] = "CREATE TRIGGER `EntityLink-before-insert` BEFORE INSERT ON `EntityLink` FOR EACH ROW $entitylink_trigger_body";
+                       $query[] = "CREATE TRIGGER `EntityLink-before-update` BEFORE UPDATE ON `EntityLink` FOR EACH ROW $entitylink_trigger_body";
                        $link_trigger_body = <<<ENDOFTRIGGER
-BEGIN
+LinkTrigger:BEGIN
   DECLARE tmp, porta_type, portb_type, count INTEGER;
 
   IF NEW.porta = NEW.portb THEN
     # forbid connecting a port to itself
     SET NEW.porta = NULL;
+    LEAVE LinkTrigger;
   ELSEIF NEW.porta > NEW.portb THEN
     # force porta < portb
     SET tmp = NEW.porta;