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