From 40c8b8293bc5311c0a521f801002a80be8883519 Mon Sep 17 00:00:00 2001 From: Tim Otten Date: Thu, 14 Jun 2018 15:51:25 -0700 Subject: [PATCH] (dev/core#183) Add more robust TempTable generator --- CRM/Core/Config.php | 1 + CRM/Utils/SQL/TempTable.php | 266 ++++++++++++++++++++++++++++++++++++ 2 files changed, 267 insertions(+) create mode 100644 CRM/Utils/SQL/TempTable.php diff --git a/CRM/Core/Config.php b/CRM/Core/Config.php index 349c672b83..a1b3eb7f30 100644 --- a/CRM/Core/Config.php +++ b/CRM/Core/Config.php @@ -392,6 +392,7 @@ class CRM_Core_Config extends CRM_Core_Config_MagicMerge { OR TABLE_NAME LIKE 'civicrm_export_temp%' OR TABLE_NAME LIKE 'civicrm_task_action_temp%' OR TABLE_NAME LIKE 'civicrm_report_temp%' + OR TABLE_NAME LIKE 'civicrm_tmp_d%' ) "; if ($timeInterval) { diff --git a/CRM/Utils/SQL/TempTable.php b/CRM/Utils/SQL/TempTable.php new file mode 100644 index 0000000000..8e419789b4 --- /dev/null +++ b/CRM/Utils/SQL/TempTable.php @@ -0,0 +1,266 @@ +getName(); + * $name = CRM_Utils_SQL_TempTable::build()->setDurable()->getName(); + * + * Example 2: Create a temp table using the results of a SELECT query. + * + * $tmpTbl = CRM_Utils_SQL_TempTable::build()->createWithQuery('SELECT id, display_name FROM civicrm_contact'); + * $tmpTbl = CRM_Utils_SQL_TempTable::build()->createWithQuery(CRM_Utils_SQL_Select::from('civicrm_contact')->select('display_name')); + * + * Example 3: Create an empty temp table with list of columns. + * + * $tmpTbl = CRM_Utils_SQL_TempTable::build()->setDurable()->setUtf8()->createWithColumns('id int(10, name varchar(64)'); + * + * Example 4: Drop a table that you previously created. + * + * $tmpTbl->drop(); + * + * Example 5: Auto-drop a temp table when $tmpTbl falls out of scope + * + * $tmpTbl->setAutodrop(); + * + */ +class CRM_Utils_SQL_TempTable { + + const UTF8 = 'DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci'; + const CATEGORY_LENGTH = 12; + const CATEGORY_REGEXP = ';^[a-zA-Z0-9]+$;'; + const ID_LENGTH = 37; // MAX{64} - CATEGORY_LENGTH{12} - CONST_LENGHTH{15} = 37 + const ID_REGEXP = ';^[a-zA-Z0-9_]+$;'; + + /** + * @var bool + */ + protected $durable, $utf8; + + protected $category; + + protected $id; + + protected $autodrop; + + /** + * @return CRM_Utils_SQL_TempTable + */ + public static function build() { + $t = new CRM_Utils_SQL_TempTable(); + $t->category = NULL; + $t->id = md5(uniqid('', TRUE)); + // The constant CIVICRM_TEMP_FORCE_DURABLE is for local debugging. + $t->durable = CRM_Utils_Constant::value('CIVICRM_TEMP_FORCE_DURABLE', FALSE); + // I suspect it would be better to just say utf8=true, but a lot of existing queries don't do the utf8 bit. + $t->utf8 = CRM_Utils_Constant::value('CIVICRM_TEMP_FORCE_UTF8', FALSE); + $t->autodrop = FALSE; + return $t; + } + + public function __destruct() { + if ($this->autodrop) { + $this->drop(); + } + } + + /** + * Determine the full table name. + * + * @return string + * Ex: 'civicrm_tmp_d_foo_abcd1234abcd1234' + */ + public function getName() { + $parts = ['civicrm', 'tmp']; + $parts[] = ($this->durable ? 'd' : 'e'); + $parts[] = $this->category ? $this->category : 'dflt'; + $parts[] = $this->id ? $this->id : 'dflt'; + return implode('_', $parts); + } + + /** + * Create the table using results from a SELECT query. + * + * @param string|CRM_Utils_SQL_Select $selectQuery + * @return CRM_Utils_SQL_TempTable + */ + public function createWithQuery($selectQuery) { + $sql = sprintf('%s %s AS %s', + $this->toSQL('CREATE'), + $this->utf8 ? self::UTF8 : '', + ($selectQuery instanceof CRM_Utils_SQL_Select ? $selectQuery->toSQL() : $selectQuery) + ); + CRM_Core_DAO::executeQuery($sql, array(), TRUE, NULL, TRUE, FALSE); + return $this; + } + + /** + * Create the empty table. + * + * @parma string $columns + * SQL column listing. + * Ex: 'id int(10), name varchar(64)'. + * @return CRM_Utils_SQL_TempTable + */ + public function createWithColumns($columns) { + $sql = sprintf('%s (%s) %s', + $this->toSQL('CREATE'), + $columns, + $this->utf8 ? self::UTF8 : '' + ); + CRM_Core_DAO::executeQuery($sql, array(), TRUE, NULL, TRUE, FALSE); + return $this; + } + + /** + * Drop the table. + * + * @return CRM_Utils_SQL_TempTable + */ + public function drop() { + $sql = $this->toSQL('DROP', 'IF EXISTS'); + CRM_Core_DAO::executeQuery($sql, array(), TRUE, NULL, TRUE, FALSE); + return $this; + } + + /** + * @param string $action + * Ex: 'CREATE', 'DROP' + * @param string|NULL $ifne + * Ex: 'IF EXISTS', 'IF NOT EXISTS'. + * @return string + * Ex: 'CREATE TEMPORARY TABLE `civicrm_tmp_e_foo_abcd1234`' + * Ex: 'CREATE TABLE IF NOT EXISTS `civicrm_tmp_d_foo_abcd1234`' + */ + private function toSQL($action, $ifne = NULL) { + $parts = []; + $parts[] = $action; + if (!$this->durable) { + $parts[] = 'TEMPORARY'; + } + $parts[] = 'TABLE'; + if ($ifne) { + $parts[] = $ifne; + } + $parts[] = '`' . $this->getName() . '`'; + return implode(' ', $parts); + } + + /** + * @return string|NULL + */ + public function getCategory() { + return $this->category; + } + + /** + * @return string|NULL + */ + public function getId() { + return $this->id; + } + + /** + * @return bool + */ + public function isAutodrop() { + return $this->autodrop; + } + + /** + * @return bool + */ + public function isDurable() { + return $this->durable; + } + + /** + * @return bool + */ + public function isUtf8() { + return $this->utf8; + } + + /** + * @param bool $autodrop + * @return CRM_Utils_SQL_TempTable + */ + public function setAutodrop($autodrop = TRUE) { + $this->autodrop = $autodrop; + return $this; + } + + /** + * @param string|NULL $category + * @return CRM_Utils_SQL_TempTable + */ + public function setCategory($category) { + if ($category && !preg_match(self::CATEGORY_REGEXP, $category) || strlen($category) > self::CATEGORY_LENGTH) { + throw new \RuntimeException("Malformed temp table category"); + } + $this->category = $category; + return $this; + } + + /** + * @parma bool $value + * @return CRM_Utils_SQL_TempTable + */ + public function setDurable($durable = TRUE) { + $this->durable = $durable; + return $this; + } + + /** + * @param mixed $id + * @return CRM_Utils_SQL_TempTable + */ + public function setId($id) { + if ($id && !preg_match(self::ID_REGEXP, $id) || strlen($id) > self::ID_LENGTH) { + throw new \RuntimeException("Malformed temp table id"); + } + $this->id = $id; + return $this; + } + + public function setUtf8($value = TRUE) { + $this->utf8 = $value; + return $this; + } + +} -- 2.25.1