Skip to content

Instantly share code, notes, and snippets.

@seamuslee001

seamuslee001/db_dataobject.diff

Created January 2, 2020 08:52
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save seamuslee001/4053b2657ca6fa7ae181f884176863bd to your computer and use it in GitHub Desktop.
Save seamuslee001/4053b2657ca6fa7ae181f884176863bd to your computer and use it in GitHub Desktop.
Download ZIP
Raw
db_dataobject.diff
--- _ORIGINAL_/DB/DataObject.php 2020-01-02 17:57:17.933406472 +1100
+++ DB/DataObject.php 2020-01-02 17:57:17.933406472 +1100
@@ -43,7 +43,7 @@
*
* //Start and initialize...................... - dont forget the &
* $config = parse_ini_file('example.ini',true);
- * $options = &PEAR::getStaticProperty('DB_DataObject','options');
+ * $options = PEAR::getStaticProperty('DB_DataObject','options');
* $options = $config['DB_DataObject'];
*
* // example of a class (that does not use the 'auto generated tables data')
@@ -568,16 +568,14 @@
// note: we dont declare this to keep the print_r size down.
$_DB_DATAOBJECT['RESULTFIELDS'][$this->_DB_resultid]= array_flip(array_keys($array));
}
- $replace = array('.', ' ');
- foreach($array as $k=>$v) {
- // use strpos as str_replace is slow.
- $kk = (strpos($k, '.') === false && strpos($k, ' ') === false) ?
- $k : str_replace($replace, '_', $k);
-
+ $keys = str_replace(array("."," "), "_", array_keys($array));
+ $i = 0;
+ foreach($array as $val) {
+ $key = $keys[$i++];
if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
- $this->debug("$kk = ". $array[$k], "fetchrow LINE", 3);
+ $this->debug("$key = ". $val, "fetchrow LINE", 3);
}
- $this->$kk = $array[$k];
+ $this->$key = $val;
}
// set link flag
@@ -638,37 +636,36 @@
* @access public
* @return array format dependant on arguments, may be empty
*/
- function fetchAll($k= false, $v = false, $method = false)
- {
+ //function fetchAll($k= false, $v = false, $method = false)
+ //{
// should it even do this!!!?!?
- if ($k !== false &&
- ( // only do this is we have not been explicit..
- empty($this->_query['data_select']) ||
- ($this->_query['data_select'] == '*')
- )
- ) {
- $this->selectAdd();
- $this->selectAdd($k);
- if ($v !== false) {
- $this->selectAdd($v);
- }
- }
-
- $this->find();
- $ret = array();
- while ($this->fetch()) {
- if ($v !== false) {
- $ret[$this->$k] = $this->$v;
- continue;
- }
- $ret[] = $k === false ?
- ($method == false ? clone($this) : $this->$method())
- : $this->$k;
- }
- return $ret;
+ // if ($k !== false &&
+ // ( // only do this is we have not been explicit..
+ // empty($this->_query['data_select']) ||
+ // ($this->_query['data_select'] == '*')
+ // )
+ // ) {
+ // $this->selectAdd();
+ // $this->selectAdd($k);
+ // if ($v !== false) {
+ // $this->selectAdd($v);
+ // }
+ // }
- }
+ // $this->find();
+ // $ret = array();
+ // while ($this->fetch()) {
+ // if ($v !== false) {
+ // $ret[$this->$k] = $this->$v;
+ // continue;
+ // }
+ // $ret[] = $k === false ?
+ // ($method == false ? clone($this) : $this->$method())
+ // : $this->$k;
+ // }
+ // return $ret;
+ //}
/**
* Adds a condition to the WHERE statement, defaults to AND
@@ -694,7 +691,7 @@
}
if ($cond === false) {
- $r = $this->_query['condition'];
+ $r = isset($this->_query['condition']) ? $this->_query['condition'] : null;
$_query['condition'] = '';
$this->_query = $_query;
return preg_replace('/^\s+WHERE\s+/','',$r);
@@ -791,6 +788,16 @@
$this->_query['order_by'] .= " , {$order}";
}
+ /*
+ * Return affected rows for current connection.
+ * Override the mysql affectedRows w/ db object.
+ */
+ function affectedRows() {
+ global $_DB_DATAOBJECT;
+ $DB = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
+ return $DB->affectedRows();
+ }
+
/**
* Adds a group by condition
*
@@ -967,7 +974,7 @@
return false;
}
if ($k === null) {
- $old = $this->_query['data_select'];
+ $old = isset($this->_query['data_select']) ? $this->_query['data_select'] : null;
$this->_query['data_select'] = '';
return $old;
}
@@ -1134,11 +1141,6 @@
) {
continue;
}
- // dont insert data into mysql timestamps
- // use query() if you really want to do this!!!!
- if ($v & DB_DATAOBJECT_MYSQLTIMESTAMP) {
- continue;
- }
if ($leftq) {
$leftq .= ', ';
@@ -1147,6 +1149,7 @@
$leftq .= ($quoteIdentifiers ? ($DB->quoteIdentifier($k) . ' ') : "$k ");
+ /***
if (is_object($this->$k) && is_a($this->$k,'DB_DataObject_Cast')) {
$value = $this->$k->toString($v,$DB);
if (PEAR::isError($value)) {
@@ -1156,12 +1159,25 @@
$rightq .= $value;
continue;
}
-
+ **/
if (!($v & DB_DATAOBJECT_NOTNULL) && DB_DataObject::_is_null($this,$k)) {
$rightq .= " NULL ";
continue;
}
+ if (($v & DB_DATAOBJECT_DATE) || ($v & DB_DATAOBJECT_TIME) || $v & DB_DATAOBJECT_MYSQLTIMESTAMP) {
+ if (strpos($this->$k, '-') !== FALSE) {
+ /*
+ * per CRM-14986 we have been having ongoing problems with the format returned from $dao->find(TRUE) NOT
+ * being acceptable for an immediate save. This has resulted in the codebase being smattered with
+ * instances of CRM_Utils_Date::isoToMysql for date fields retrieved in this way
+ * which seems both risky (have to remember to do it for every field) & cludgey.
+ * doing it here should be safe as only fields with a '-' in them will be affected - if they are
+ * already formatted or empty then this line will not be hit
+ */
+ $this->$k = CRM_Utils_Date::isoToMysql($this->$k);
+ }
+ }
// DATE is empty... on a col. that can be null..
// note: this may be usefull for time as well..
if (!$this->$k &&
@@ -1183,6 +1199,17 @@
)) . " ";
continue;
}
+
+ if ($v & DB_DATAOBJECT_TXT) {
+ $rightq .= $this->_quote((string) $this->$k ) . ' ';
+ continue;
+ }
+
+ if ($v & DB_DATAOBJECT_BLOB) {
+ $rightq .= $this->_quote( $this->$k ) . ' ';
+ continue;
+ }
+
if (is_numeric($this->$k)) {
$rightq .=" {$this->$k} ";
continue;
@@ -1395,19 +1422,13 @@
continue;
}
- // dont insert data into mysql timestamps
- // use query() if you really want to do this!!!!
- if ($v & DB_DATAOBJECT_MYSQLTIMESTAMP) {
- continue;
- }
-
-
if ($settings) {
$settings .= ', ';
}
$kSql = ($quoteIdentifiers ? $DB->quoteIdentifier($k) : $k);
+ /***
if (is_object($this->$k) && is_a($this->$k,'DB_DataObject_Cast')) {
$value = $this->$k->toString($v,$DB);
if (PEAR::isError($value)) {
@@ -1417,12 +1438,26 @@
$settings .= "$kSql = $value ";
continue;
}
+ ***/
// special values ... at least null is handled...
if (!($v & DB_DATAOBJECT_NOTNULL) && DB_DataObject::_is_null($this,$k)) {
$settings .= "$kSql = NULL ";
continue;
}
+ if (($v & DB_DATAOBJECT_DATE) || ($v & DB_DATAOBJECT_TIME) || $v & DB_DATAOBJECT_MYSQLTIMESTAMP) {
+ if (strpos($this->$k, '-') !== FALSE) {
+ /*
+ * per CRM-14986 we have been having ongoing problems with the format returned from $dao->find(TRUE) NOT
+ * being acceptable for an immediate save. This has resulted in the codebase being smattered with
+ * instances of CRM_Utils_Date::isoToMysql for date fields retrieved in this way
+ * which seems both risky (have to remember to do it for every field) & cludgey.
+ * doing it here should be safe as only fields with a '-' in them will be affected - if they are
+ * already formatted or empty then this line will not be hit
+ */
+ $this->$k = CRM_Utils_Date::isoToMysql($this->$k);
+ }
+ }
// DATE is empty... on a col. that can be null..
// note: this may be usefull for time as well..
if (!$this->$k &&
@@ -1444,6 +1479,17 @@
)) . ' ';
continue;
}
+
+ if ($v & DB_DATAOBJECT_TXT) {
+ $settings .= "$kSql = " . $this->_quote((string) $this->$k ) . ' ';
+ continue;
+ }
+
+ if ($v & DB_DATAOBJECT_BLOB) {
+ $settings .= "$kSql = " . $this->_quote($this->$k ) . ' ';
+ continue;
+ }
+
if (is_numeric($this->$k)) {
$settings .= "$kSql = {$this->$k} ";
continue;
@@ -1500,10 +1546,12 @@
return true;
}
- $this->raiseError(
- "update: No Data specifed for query $settings , {$this->_query['condition']}",
- DB_DATAOBJECT_ERROR_NODATA);
- return false;
+ // $this->raiseError(
+ // "update: No Data specifed for query $settings , {$this->_query['condition']}",
+ // DB_DATAOBJECT_ERROR_NODATA);
+ // return false;
+ // we allow empty updates always [CiviCRM]
+ return true;
}
/**
@@ -1646,16 +1694,14 @@
$this->raiseError("fetchrow: No results available", DB_DATAOBJECT_ERROR_NODATA);
return false;
}
- $replace = array('.', ' ');
- foreach($array as $k => $v) {
- // use strpos as str_replace is slow.
- $kk = (strpos($k, '.') === false && strpos($k, ' ') === false) ?
- $k : str_replace($replace, '_', $k);
-
+ $keys = str_replace(array("."," "), "_", array_keys($array));
+ $i = 0;
+ foreach($array as $val) {
+ $key = $keys[$i++];
if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
- $this->debug("$kk = ". $array[$k], "fetchrow LINE", 3);
+ $this->debug("$key = ". $val, "fetchrow LINE", 3);
}
- $this->$kk = $array[$k];
+ $this->$key = $val;
}
if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
@@ -2511,6 +2557,10 @@
}
+ // change the connection and results charsets to UTF-8 if we're using MySQL 4.1+
+ $civicrmConfig = CRM_Core_Config::singleton();
+ $this->query("/*!40101 SET NAMES utf8 */");
+ $this->query("/*!50503 SET NAMES utf8mb4 */");
if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
$this->debug(print_r($_DB_DATAOBJECT['CONNECTIONS'],true), "CONNECT",5);
@@ -2557,6 +2607,8 @@
global $_DB_DATAOBJECT;
$this->_connect();
+ // Logging the query first makes sure it gets logged even if it fails.
+ CRM_Core_Error::debug_query($string);
$DB = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
@@ -2618,12 +2670,34 @@
$t= explode(' ',microtime());
$_DB_DATAOBJECT['QUERYENDTIME'] = $time = $t[0]+$t[1];
-
- for ($tries = 0;$tries < 3;$tries++) {
+ $maxTries = defined('CIVICRM_DEADLOCK_RETRIES') ? CIVICRM_DEADLOCK_RETRIES : 3;
+ for ($tries = 0;$tries < $maxTries;$tries++) {
if ($_DB_driver == 'DB') {
-
+ try {
$result = $DB->query($string);
+ }
+ catch (PEAR_Exception $e) {
+ if ($tries == 0) {
+ // The original sin was what triggered the retry. Sometimes the retry fails because mysql has done an internal rollback
+ // of previous queries in the transaction so it has essentially failed to recover from the deadlock. If we can't
+ // recover we should return the original error.
+ $firstError = $e;
+ }
+ // CRM-21489 If we have caught a DB lock - let it go around the loop until our tries limit is hit.
+ // else rethrow the exception. The 2 locks we are looking at are mysql code 1205 (lock) and
+ // 1213 (deadlock).
+ $dbErrorMessage = $e->getCause()->getUserInfo();
+ if (!stristr($dbErrorMessage, 'nativecode=1205') && !stristr($dbErrorMessage, 'nativecode=1213')) {
+ throw $firstError;
+ }
+ $message = (stristr($dbErrorMessage, 'nativecode=1213') ? 'Database deadlock encountered' : 'Database lock encountered');
+ if (($tries + 1) === $maxTries) {
+ throw new CRM_Core_Exception($message, 0, array('sql' => $string, 'trace' => $firstError->getTrace()));
+ }
+ CRM_Core_Error::debug_log_message("Retrying after $message hit on attempt " . ($tries + 1) . ' at query : ' . $string);
+ continue;
+ }
} else {
switch (strtolower(substr(trim($string),0,6))) {
@@ -2658,12 +2732,35 @@
$this->N = false;
return $this->raiseError($result);
}
- if (!empty($_DB_DATAOBJECT['CONFIG']['debug'])) {
- $t= explode(' ',microtime());
- $_DB_DATAOBJECT['QUERYENDTIME'] = $t[0]+$t[1];
- $this->debug('QUERY DONE IN '.($t[0]+$t[1]-$time)." seconds", 'query',1);
+
+ // Strip any prepended comments
+ $queryString = (substr($string, 0, 2) === '/*') ? substr($string, strpos($string, '*/') + 2) : $string;
+ $action = strtolower(substr(trim($queryString),0,6));
+
+ if (!empty($_DB_DATAOBJECT['CONFIG']['debug']) || defined('CIVICRM_DEBUG_LOG_QUERY')) {
+ $timeTaken = sprintf("%0.6f", microtime(TRUE) - $time);
+ $alertLevel = $this->getAlertLevel($timeTaken);
+ $message = "$alertLevel QUERY DONE IN $timeTaken seconds.";
+ if (in_array($action, array('insert', 'update', 'delete')) && $_DB_driver == 'DB') {
+ $message .= " " . $DB->affectedRows() . " row(s)s subject to $action action";
}
- switch (strtolower(substr(trim($string),0,6))) {
+ elseif (is_a($result, 'DB_result') && method_exists($result, 'numrows')) {
+ $message .= " Result is {$result->numRows()} rows by {$result->numCols()} columns. ";
+ }
+ elseif ($result === 1) {
+ $message .= " No further information is available for this type of query";
+ }
+ else {
+ echo $message .= " not quite sure why this query does not have more info";
+ }
+ if (defined('CIVICRM_DEBUG_LOG_QUERY')) {
+ CRM_Core_Error::debug_log_message($message, FALSE, 'sql_log');
+ }
+ else {
+ $this->debug($message, 'query', 1);
+ }
+ }
+ switch ($action) {
case 'insert':
case 'update':
case 'delete':
@@ -2750,7 +2847,7 @@
: "{$this->tableName()}.{$k}";
-
+ /***
if (is_object($this->$k) && is_a($this->$k,'DB_DataObject_Cast')) {
$dbtype = $DB->dsn["phptype"];
$value = $this->$k->toString($v,$DB);
@@ -2765,6 +2862,7 @@
$this->whereAdd(" $kSql = $value");
continue;
}
+ ***/
if (!($v & DB_DATAOBJECT_NOTNULL) && DB_DataObject::_is_null($this,$k)) {
$this->whereAdd(" $kSql IS NULL");
@@ -2772,7 +2870,8 @@
}
- if ($v & DB_DATAOBJECT_STR) {
+ if ($v & DB_DATAOBJECT_STR ||
+ $v & DB_DATAOBJECT_TXT) {
$this->whereAdd(" $kSql = " . $this->_quote((string) (
($v & DB_DATAOBJECT_BOOL) ?
// this is thanks to the braindead idea of postgres to
@@ -2814,7 +2913,7 @@
- static function factory($table = '')
+ function factory($table = '')
{
global $_DB_DATAOBJECT;
@@ -4248,12 +4347,11 @@
// the Validate will probably break!!... - rightly so! (your design is broken,
// so issuing a runtime error like PEAR_Error is probably not appropriate..
- switch (true) {
- // todo: date time.....
- case ($val & DB_DATAOBJECT_STR):
+ if ($val & DB_DATAOBJECT_STR) {
$ret[$key] = Validate::string($this->$key, VALIDATE_PUNCTUATION . VALIDATE_NAME);
continue;
- case ($val & DB_DATAOBJECT_INT):
+ }
+ if ($val & DB_DATAOBJECT_INT) {
$ret[$key] = Validate::number($this->$key, array('decimal'=>'.'));
continue;
}
@@ -4646,7 +4744,7 @@
* @access public
* @return none
*/
- static function debugLevel($v = null)
+ public static function debugLevel($v = null)
{
global $_DB_DATAOBJECT;
if (empty($_DB_DATAOBJECT['CONFIG'])) {
@@ -4689,7 +4787,7 @@
$behaviour = null;
}
- $error = &PEAR::getStaticProperty('DB_DataObject','lastError');
+ $error = PEAR::getStaticProperty('DB_DataObject','lastError');
// no checks for production here?....... - we log errors before we throw them.
@@ -4728,11 +4826,11 @@
* @access public
* @return object an error object
*/
- function _loadConfig()
+ public static function _loadConfig()
{
global $_DB_DATAOBJECT;
- $_DB_DATAOBJECT['CONFIG'] = &PEAR::getStaticProperty('DB_DataObject','options');
+ $_DB_DATAOBJECT['CONFIG'] = PEAR::getStaticProperty('DB_DataObject','options');
}
@@ -4812,7 +4910,8 @@
&& is_string($options['disable_null_strings'])
&& strtolower($options['disable_null_strings'] === 'full');
- if ( $null_strings && $isset && is_string($value) && (strtolower($value) === 'null') ) {
+ // CRM-15522 Ensure that Last name of Null is validly stored.
+ if ( $null_strings && $isset && is_string($value) && (strtolower($value) === 'null' && $value !== 'Null') ) {
return true;
}
@@ -4902,8 +5001,35 @@
function _get_table() { return $this->table(); }
function _get_keys() { return $this->keys(); }
+ /**
+ * Get a string to append to the query log depending on time taken.
+ *
+ * This is to allow easier grepping for slow queries.
+ *
+ * @param float $timeTaken
+ *
+ * @return string
+ */
+ public function getAlertLevel($timeTaken) {
+ if ($timeTaken >= 20) {
+ return '(very-very-very-slow)';
+ }
+ if ($timeTaken > 10) {
+ return '(very-very-slow)';
+ }
+ if ($timeTaken > 5) {
+ return '(very-slow)';
+ }
+ if ($timeTaken > 1) {
+ return '(slow)';
+ }
+ return '';
+ }
-
+ public function lastInsertId() {
+ $DB = $_DB_DATAOBJECT['CONNECTIONS'][$this->_database_dsn_md5];
+ return $DB->lastInsertId();
+ }
}
// technially 4.3.2RC1 was broken!!
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment