From 9c8a302515c660178f2cb3de7a0b03e2cb7624a4 Mon Sep 17 00:00:00 2001
From: Patrick Figel <pfigel@greenpeace.org>
Date: Sun, 13 Jan 2019 19:11:41 +0100
Subject: [PATCH] CRM/Logging - Fix various bugs in schema parsing

This fixes a couple of bugs in the schema parsing methods used by
Civi's extended logging feature:

- CRM_Logging_Schema::getIndexesForTable only queried for constraints,
  not returning any indexes.
- CRM_Logging_Schema::getIndexesForTable returned an array in the form
  [0 => ['constraint_name' => 'foo']] rather than the expected array
  of index names (i.e. ['foo']).
- CRM_Logging_Schema::columnSpecsOf contained an off-by-one error and
  a wrongly used substr parameter causing column lengths to include
  surrounding parenthesis. This would result in a "varchar(42)"
  column returning a length of "(42)" instead of "42".
---
 CRM/Logging/Schema.php                   | 22 +++++++++----
 tests/phpunit/CRM/Logging/SchemaTest.php | 42 ++++++++++++++++++++++++
 2 files changed, 58 insertions(+), 6 deletions(-)

diff --git a/CRM/Logging/Schema.php b/CRM/Logging/Schema.php
index 1e259ed0e7..53560c22cd 100644
--- a/CRM/Logging/Schema.php
+++ b/CRM/Logging/Schema.php
@@ -363,12 +363,22 @@ AND    (TABLE_NAME LIKE 'log_civicrm_%' $nonStandardTableNameString )
    * @return array
    */
   public function getIndexesForTable($table) {
-    return CRM_Core_DAO::executeQuery("
-      SELECT constraint_name
-      FROM information_schema.key_column_usage
-      WHERE table_schema = %2 AND table_name = %1",
+    $indexes = [];
+    $result = CRM_Core_DAO::executeQuery("
+        SELECT constraint_name AS index_name
+        FROM information_schema.key_column_usage
+        WHERE table_schema = %2 AND table_name = %1
+      UNION
+        SELECT index_name AS index_name
+        FROM information_schema.statistics
+        WHERE table_schema = %2 AND table_name = %1
+      ",
       array(1 => array($table, 'String'), 2 => array($this->db, 'String'))
-    )->fetchAll();
+    );
+    while ($result->fetch()) {
+      $indexes[] = $result->index_name;
+    }
+    return $indexes;
   }
 
   /**
@@ -598,7 +608,7 @@ WHERE  table_schema IN ('{$this->db}', '{$civiDB}')";
         );
         if (($first = strpos($dao->COLUMN_TYPE, '(')) != 0) {
           \Civi::$statics[__CLASS__]['columnSpecs'][$dao->TABLE_NAME][$dao->COLUMN_NAME]['LENGTH'] = substr(
-            $dao->COLUMN_TYPE, $first, strpos($dao->COLUMN_TYPE, ')')
+            $dao->COLUMN_TYPE, $first + 1, strpos($dao->COLUMN_TYPE, ')') - $first - 1
           );
         }
       }
diff --git a/tests/phpunit/CRM/Logging/SchemaTest.php b/tests/phpunit/CRM/Logging/SchemaTest.php
index 948c61704b..f6cb6f30e1 100644
--- a/tests/phpunit/CRM/Logging/SchemaTest.php
+++ b/tests/phpunit/CRM/Logging/SchemaTest.php
@@ -16,6 +16,7 @@ class CRM_Logging_SchemaTest extends CiviUnitTestCase {
     $schema->disableLogging();
     $schema->dropAllLogTables();
     CRM_Core_DAO::executeQuery("DROP TABLE IF EXISTS civicrm_test_table");
+    CRM_Core_DAO::executeQuery("DROP TABLE IF EXISTS civicrm_test_column_info");
   }
 
   public function queryExamples() {
@@ -123,4 +124,45 @@ class CRM_Logging_SchemaTest extends CiviUnitTestCase {
     }
   }
 
+  public function testColumnInfo() {
+    CRM_Core_DAO::executeQuery("CREATE TABLE `civicrm_test_column_info` (
+      test_id  int(10) unsigned NOT NULL AUTO_INCREMENT,
+      test_varchar varchar(42) NOT NULL,
+      test_integer int(8) NULL,
+      test_integer_default int(8) DEFAULT 42,
+      test_date date DEFAULT NULL,
+      PRIMARY KEY (`test_id`)
+    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci");
+    $schema = new CRM_Logging_Schema();
+    $schema->enableLogging();
+    $schema->updateLogTableSchema();
+    $ci = \Civi::$statics['CRM_Logging_Schema']['columnSpecs']['civicrm_test_column_info'];
+
+    $this->assertEquals('test_id', $ci['test_id']['COLUMN_NAME']);
+    $this->assertEquals('int', $ci['test_id']['DATA_TYPE']);
+    $this->assertEquals('NO', $ci['test_id']['IS_NULLABLE']);
+    $this->assertEquals('auto_increment', $ci['test_id']['EXTRA']);
+    $this->assertEquals('10', $ci['test_id']['LENGTH']);
+
+    $this->assertEquals('varchar', $ci['test_varchar']['DATA_TYPE']);
+    $this->assertEquals('42', $ci['test_varchar']['LENGTH']);
+
+    $this->assertEquals('int', $ci['test_integer']['DATA_TYPE']);
+    $this->assertEquals('8', $ci['test_integer']['LENGTH']);
+    $this->assertEquals('YES', $ci['test_integer']['IS_NULLABLE']);
+
+    $this->assertEquals('42', $ci['test_integer_default']['COLUMN_DEFAULT']);
+
+    $this->assertEquals('date', $ci['test_date']['DATA_TYPE']);
+  }
+
+  public function testIndexes() {
+    $schema = new CRM_Logging_Schema();
+    $indexes = $schema->getIndexesForTable('civicrm_contact');
+    $this->assertContains('PRIMARY', $indexes);
+    $this->assertContains('UI_external_identifier', $indexes);
+    $this->assertContains('FK_civicrm_contact_employer_id', $indexes);
+    $this->assertContains('index_sort_name', $indexes);
+  }
+
 }
-- 
2.25.1