CRM/Upgrade/Snapshot.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   6  | This work is published under the GNU AGPLv3 license with some      |
   7  | permitted exceptions and without any warranty. For full license    |
   8  | and copyright information, see https://civicrm.org/licensing       |
   9  +--------------------------------------------------------------------+
  10  */
  11
  12 /**
  13  * Provide helpers for recording data snapshots during an upgrade.
  14  */
  15 class CRM_Upgrade_Snapshot {
  16
  17   public static $pageSize = 50 * 1000;
  18
  19   /**
  20    * How long should we retain old snapshots?
  21    *
  22    * Time is measured in terms of MINOR versions - eg "4" means "retain for 4 MINOR versions".
  23    * Thus, on v5.60, you could delete any snapshots predating 5.56.
  24    *
  25    * @var int
  26    */
  27   public static $cleanupAfter = 4;
  28
  29   /**
  30    * List of reasons why the snapshots are not running.
  31    *
  32    * @var array|null
  33    */
  34   private static $activationIssues;
  35
  36   /**
  37    * Get a list of reasons why the snapshots should not run.
  38    * @return array
  39    *   List of printable messages.
  40    */
  41   public static function getActivationIssues(): array {
  42     if (static::$activationIssues === NULL) {
  43       $policy = CRM_Utils_Constant::value('CIVICRM_UPGRADE_SNAPSHOT', 'auto');
  44       if ($policy === TRUE) {
  45         return [];
  46       }
  47
  48       $limits = [
  49         'civicrm_contact' => 200 * 1000,
  50         'civicrm_contribution' => 200 * 1000,
  51         'civicrm_activity' => 200 * 1000,
  52         'civicrm_case' => 200 * 1000,
  53         'civicrm_mailing' => 200 * 1000,
  54         'civicrm_event' => 200 * 1000,
  55       ];
  56
  57       static::$activationIssues = [];
  58       foreach ($limits as $table => $limit) {
  59         try {
  60           $count = CRM_Core_DAO::singleValueQuery("SELECT count(*) FROM `{$table}`");
  61         }
  62         catch (\Exception $e) {
  63           $count = 0;
  64         }
  65         if ($count > $limit) {
  66           static::$activationIssues["count_{$table}"] = ts('Table "%1" has a large number of records (%2 > %3).', [
  67             1 => $table,
  68             2 => $count,
  69             3 => $limit,
  70           ]);
  71         }
  72       }
  73
  74       if (CRM_Core_I18n::isMultilingual()) {
  75         static::$activationIssues['multilingual'] = ts('Multilingual snapshots have not been implemented.');
  76       }
  77
  78       if ($policy === FALSE) {
  79         static::$activationIssues['override'] = ts('Snapshots disabled by override (CIVICRM_UPGRADE_SNAPSHOT).');
  80       }
  81     }
  82
  83     return static::$activationIssues;
  84   }
  85
  86   /**
  87    * Create the name of a MySQL snapshot table.
  88    *
  89    * @param string $owner
  90    *   Name of the component/module/extension that owns the snapshot.
  91    *   Ex: 'civicrm', 'sequentialcreditnotes', 'oauth_client'
  92    * @param string $version
  93    *   Ex: '5.50'
  94    * @param string $name
  95    *   Ex: 'dates'
  96    * @return string
  97    *   Ex: 'snap_civicrm_v5_50_dates'
  98    * @throws \CRM_Core_Exception
  99    *   If the resulting table name would be invalid, then this throws an exception.
 100    */
 101   public static function createTableName(string $owner, string $version, string $name): string {
 102     $versionParts = explode('.', $version);
 103     if (count($versionParts) !== 2) {
 104       throw new \CRM_Core_Exception("Snapshot support is currently only defined for two-part version (MAJOR.MINOR). Found ($version).");
 105       // If you change this, be sure to consider `cleanupTask()` as well.
 106       // One reason you might change it -- if you were going to track with the internal schema-numbers from an extension.
 107       // Of course, you could get similar effect with "0.{$schemaNumber}" eg "5002" ==> "0.5002"
 108     }
 109     $versionExpr = ($versionParts[0] . '_' . $versionParts[1]);
 110
 111     $table = sprintf('snap_%s_v%s_%s', $owner, $versionExpr, $name);
 112     if (!preg_match(';^[a-z0-9_]+$;', $table)) {
 113       throw new CRM_Core_Exception("Malformed snapshot name ($table)");
 114     }
 115     if (strlen($table) > 64) {
 116       throw new CRM_Core_Exception("Snapshot name is too long ($table)");
 117     }
 118
 119     return $table;
 120   }
 121
 122   /**
 123    * Build a set of queueable tasks which will store a snapshot.
 124    *
 125    * @param string $owner
 126    *   Name of the component/module/extension that owns the snapshot.
 127    *   Ex: 'civicrm', 'sequentialcreditnotes', 'oauth_client'
 128    * @param string $version
 129    *   Ex: '5.50'
 130    * @param string $name
 131    * @param \CRM_Utils_SQL_Select $select
 132    * @throws \CRM_Core_Exception
 133    */
 134   public static function createTasks(string $owner, string $version, string $name, CRM_Utils_SQL_Select $select): iterable {
 135     $destTable = static::createTableName($owner, $version, $name);
 136     $srcTable = \Civi\Test\Invasive::get([$select, 'from']);
 137
 138     // Sometimes, backups fail and people rollback and try again. Reset prior snapshots.
 139     CRM_Core_DAO::executeQuery("DROP TABLE IF EXISTS `{$destTable}`");
 140
 141     $maxId = CRM_Core_DAO::singleValueQuery("SELECT MAX(id) FROM `{$srcTable}`");
 142     $pageSize = CRM_Upgrade_Snapshot::$pageSize;
 143     for ($offset = 0; $offset <= $maxId; $offset += $pageSize) {
 144       $title = ts('Create snapshot from "%1" (%2: %3 => %4)', [
 145         1 => $srcTable,
 146         2 => $name,
 147         3 => $offset,
 148         4 => $offset + $pageSize,
 149       ]);
 150       $pageSelect = $select->copy()->where('id >= #MIN AND id < #MAX', [
 151         'MIN' => $offset,
 152         'MAX' => $offset + $pageSize,
 153       ]);
 154       $sqlAction = ($offset === 0) ? "CREATE TABLE {$destTable} ROW_FORMAT=COMPRESSED AS " : "INSERT INTO {$destTable} ";
 155       // Note: 'CREATE TABLE AS' implicitly preserves the character-set of the source-material, so we don't set that explicitly.
 156       yield new CRM_Queue_Task(
 157         [static::class, 'insertSnapshotTask'],
 158         [$sqlAction . $pageSelect->toSQL()],
 159         $title
 160       );
 161     }
 162   }
 163
 164   /**
 165    * @param \CRM_Queue_TaskContext $ctx
 166    * @param string $sql
 167    * @return bool
 168    */
 169   public static function insertSnapshotTask(CRM_Queue_TaskContext $ctx, string $sql): bool {
 170     CRM_Core_DAO::executeQuery($sql);
 171     // If anyone works on multilingual support, you might need to set $i18nRewrite. But doesn't matter since skip ML completely.
 172     return TRUE;
 173   }
 174
 175   /**
 176    * Cleanup any old snapshot tables.
 177    *
 178    * @param CRM_Queue_TaskContext|null $ctx
 179    * @param string $owner
 180    *   Ex: 'civicrm', 'sequentialcreditnotes', 'oauth_client'
 181    * @param string|null $version
 182    *   The current version of CiviCRM.
 183    * @param int|null $cleanupAfter
 184    *   How long should we retain old snapshots?
 185    *   Time is measured in terms of MINOR versions - eg "4" means "retain for 4 MINOR versions".
 186    *   Thus, on v5.60, you could delete any snapshots predating 5.56.
 187    * @return bool
 188    */
 189   public static function cleanupTask(?CRM_Queue_TaskContext $ctx = NULL, string $owner = 'civicrm', ?string $version = NULL, ?int $cleanupAfter = NULL): bool {
 190     $version = $version ?: CRM_Core_BAO_Domain::version();
 191     $cleanupAfter = $cleanupAfter ?: static::$cleanupAfter;
 192
 193     [$major, $minor] = explode('.', $version);
 194     $cutoff = $major . '.' . max(0, $minor - $cleanupAfter);
 195
 196     $dao = new CRM_Core_DAO();
 197     $query = "
 198       SELECT TABLE_NAME as tableName
 199       FROM   INFORMATION_SCHEMA.TABLES
 200       WHERE  TABLE_SCHEMA = %1
 201       AND TABLE_NAME LIKE %2
 202     ";
 203     $tables = CRM_Core_DAO::executeQuery($query, [
 204       1 => [$dao->database(), 'String'],
 205       2 => ["snap_{$owner}_v%", 'String'],
 206     ])->fetchMap('tableName', 'tableName');
 207
 208     $oldTables = array_filter($tables, function($table) use ($owner, $cutoff) {
 209       if (preg_match(";^snap_{$owner}_v(\d+)_(\d+)_;", $table, $m)) {
 210         $generatedVer = $m[1] . '.' . $m[2];
 211         return (bool) version_compare($generatedVer, $cutoff, '<');
 212       }
 213       return FALSE;
 214     });
 215
 216     array_map(['CRM_Core_BAO_SchemaHandler', 'dropTable'], $oldTables);
 217     return TRUE;
 218   }
 219
 220 }