resources/sql/autopatches/20180418.alamanc.interface.unique.php

   1 <?php
   2
   3 $interface_table = new AlmanacInterface();
   4 $binding_table = new AlmanacBinding();
   5 $interface_conn = $interface_table->establishConnection('w');
   6
   7 queryfx(
   8   $interface_conn,
   9   'LOCK TABLES %T WRITE, %T WRITE',
  10   $interface_table->getTableName(),
  11   $binding_table->getTableName());
  12
  13 $seen = array();
  14 foreach (new LiskMigrationIterator($interface_table) as $interface) {
  15   $device = $interface->getDevicePHID();
  16   $network = $interface->getNetworkPHID();
  17   $address = $interface->getAddress();
  18   $port = $interface->getPort();
  19   $key = "{$device}/{$network}/{$address}/{$port}";
  20
  21   // If this is the first copy of this row we've seen, mark it as seen and
  22   // move on.
  23   if (empty($seen[$key])) {
  24     $seen[$key] = $interface->getID();
  25     continue;
  26   }
  27
  28   $survivor = queryfx_one(
  29     $interface_conn,
  30     'SELECT * FROM %T WHERE id = %d',
  31     $interface_table->getTableName(),
  32     $seen[$key]);
  33
  34   $bindings = queryfx_all(
  35     $interface_conn,
  36     'SELECT * FROM %T WHERE interfacePHID = %s',
  37     $binding_table->getTableName(),
  38     $interface->getPHID());
  39
  40   // Repoint bindings to the survivor.
  41   foreach ($bindings as $binding) {
  42     // Check if there's already a binding to the survivor.
  43     $existing = queryfx_one(
  44       $interface_conn,
  45       'SELECT * FROM %T WHERE interfacePHID = %s and devicePHID = %s and '.
  46       'servicePHID = %s',
  47       $binding_table->getTableName(),
  48       $survivor['phid'],
  49       $binding['devicePHID'],
  50       $binding['servicePHID']);
  51
  52     if (!$existing) {
  53       // Reattach this binding to the survivor.
  54       queryfx(
  55         $interface_conn,
  56         'UPDATE %T SET interfacePHID = %s WHERE id = %d',
  57         $binding_table->getTableName(),
  58         $survivor['phid'],
  59         $binding['id']);
  60     } else {
  61       // Binding to survivor already exists. Remove this now-redundant binding.
  62       queryfx(
  63         $interface_conn,
  64         'DELETE FROM %T WHERE id = %d',
  65         $binding_table->getTableName(),
  66         $binding['id']);
  67     }
  68   }
  69
  70   queryfx(
  71     $interface_conn,
  72     'DELETE FROM %T WHERE id = %d',
  73     $interface_table->getTableName(),
  74     $interface->getID());
  75 }
  76
  77 queryfx(
  78   $interface_conn,
  79   'ALTER TABLE %T ADD UNIQUE KEY `key_unique` '.
  80   '(devicePHID, networkPHID, address, port)',
  81   $interface_table->getTableName());
  82
  83 queryfx(
  84   $interface_conn,
  85   'UNLOCK TABLES');