continue; } $field_metadata[$record['fieldid']][] = $record; } //end foreach $GLOBALS['SQ_SYSTEM']->changeDatabaseConnection('db2'); $GLOBALS['SQ_SYSTEM']->doTransaction('BEGIN'); $count = 0; foreach ($field_metadata as $fieldid => $field_data) { $field = $GLOBALS['SQ_SYSTEM']->am->getAsset($fieldid); if ($field->attr('is_contextable') && !$field instanceof Metadata_Field_Select) { foreach ($field_data as $record) { $sql = "UPDATE sq_ast_mdata_val SET use_default = '0' WHERE assetid=:aid AND fieldid=:fid AND contextid=:cid"; $query = MatrixDAL::preparePdoQuery($sql); MatrixDAL::bindValueToPdo($query, 'aid', $record['assetid']); MatrixDAL::bindValueToPdo($query, 'fid', $record['fieldid']); MatrixDAL::bindValueToPdo($query, 'cid', $record['contextid']); $success = MatrixDAL::execPdoQuery($query); echo ++$count % 50 ? '' : '.'; } //end foreach } //end if $GLOBALS['SQ_SYSTEM']->am->forgetAsset($field, TRUE); echo '.'; } //end foreach $GLOBALS['SQ_SYSTEM']->doTransaction('COMMIT'); $GLOBALS['SQ_SYSTEM']->restoreDatabaseConnection(); echo "\n" . $count . " asset metadata db entries were upgraded."; echo "\n";
} //end for - remaining to URLs // More URLs beforehand than what we have now = have to delete the rest for (; $x < count($matching_from_roots); $x++) { // Delete the lookup values first $sql = 'DELETE FROM sq_ast_lookup_value WHERE url IN (SELECT url FROM sq_ast_lookup WHERE assetid IN (SELECT minorid FROM sq_ast_lnk WHERE linkid IN (SELECT linkid FROM sq_ast_lnk_tree t1 WHERE treeid LIKE (SELECT treeid || \'_%\' FROM sq_ast_lnk_tree t2 WHERE linkid IN (SELECT linkid FROM sq_ast_lnk WHERE minorid = :site_assetid) ' . $limit_clause . '))) AND url LIKE :from_url || \'/__data/%\')'; $query = MatrixDAL::preparePdoQuery($sql); MatrixDAL::bindValueToPdo($query, 'site_assetid', $from_site_assetid); MatrixDAL::bindValueToPdo($query, 'from_url', $matching_from_roots[$x]); MatrixDAL::execPdoQuery($query); $sql = 'DELETE FROM sq_ast_lookup WHERE assetid IN (SELECT minorid FROM sq_ast_lnk WHERE linkid IN (SELECT linkid FROM sq_ast_lnk_tree t1 WHERE treeid LIKE (SELECT treeid || \'_%\' FROM sq_ast_lnk_tree t2 WHERE linkid IN (SELECT linkid FROM sq_ast_lnk WHERE minorid = :site_assetid) ' . $limit_clause . '))) AND url LIKE :from_url || \'/__data/%\''; $query = MatrixDAL::preparePdoQuery($sql); MatrixDAL::bindValueToPdo($query, 'site_assetid', $from_site_assetid); MatrixDAL::bindValueToPdo($query, 'from_url', $matching_from_roots[$x]); MatrixDAL::execPdoQuery($query); } //end for - remaining from URLs } //end if - no static root set pre_echo('LOOKUPS CHANGED FROM ' . $from_url . ' TO ' . $to_url); $GLOBALS['SQ_SYSTEM']->doTransaction('COMMIT'); $GLOBALS['SQ_SYSTEM']->restoreDatabaseConnection(); /** * Prints the specified prompt message and returns the line from stdin * * @param string $prompt the message to display to the user * * @return string * @access public
SET sort_order = sort_order - 1 WHERE majorid = :majorid AND sort_order > :sort_order'; $query = MatrixDAL::preparePdoQuery($sql); MatrixDAL::bindValueToPdo($query, 'majorid', $link['majorid']); MatrixDAL::bindValueToPdo($query, 'sort_order', $link['sort_order']); MatrixDAL::execPdoQuery($query); $sql = 'DELETE FROM sq_ast_lnk WHERE linkid = :linkid'; $query = MatrixDAL::preparePdoQuery($sql); MatrixDAL::bindValueToPdo($query, 'linkid', $link['linkid']); MatrixDAL::execPdoQuery($query); // tell the asset it has updated $asset =& $GLOBALS['SQ_SYSTEM']->am->getAsset($assetid, $type_code); $asset->linksUpdated(); $GLOBALS['SQ_SYSTEM']->doTransaction('COMMIT'); $GLOBALS['SQ_SYSTEM']->restoreDatabaseConnection(); unset($links[$linkid]); } //end if } //end foreach if ($errors) { // no links printUpdateStatus('FAILED'); continue;
JOIN ' . SQ_TABLE_RUNNING_PREFIX . 'ast_typ_inhd i ON a.type_code = i.type_code) JOIN ' . SQ_TABLE_RUNNING_PREFIX . 'ast_lnk l ON l.minorid = a.assetid'; $where = 'l.majorid IN (:assetid, :subfolder_assetid) AND i.inhd_type_code = :inhd_type_code AND a.created BETWEEN ' . db_extras_todate(MatrixDAL::getDbType(), ':created_from', FALSE) . ' AND ' . db_extras_todate(MatrixDAL::getDbType(), ':created_to', FALSE); $where = $GLOBALS['SQ_SYSTEM']->constructRollbackWhereClause($where, 'a'); $where = $GLOBALS['SQ_SYSTEM']->constructRollbackWhereClause($where, 'l'); $sql = $sql . $where . ' ORDER BY a.created DESC'; $query = MatrixDAL::preparePdoQuery($sql); MatrixDAL::bindValueToPdo($query, 'assetid', $asset->id); MatrixDAL::bindValueToPdo($query, 'subfolder_assetid', $sub_folder->id); MatrixDAL::bindValueToPdo($query, 'inhd_type_code', 'form_submission'); MatrixDAL::bindValueToPdo($query, 'created_from', $from_value); MatrixDAL::bindValueToPdo($query, 'created_to', $to_value); $assetids = MatrixDAL::executePdoAssoc($query, 0); if (empty($assetids)) { echo "No form submission found for '{$asset->name}' (#{$assetid}) within the specified date range\n"; exit; } echo 'Found ' . count($assetids) . " form submission(s) for '{$asset->name}' (#{$assetid})\n(Date range: {$from_value} to {$to_value})\n"; $unquoted_assetids = $assetids; // quote the assetids to be used in the IN clause foreach ($assetids as $key => $assetid) { $assetids[$key] = MatrixDAL::quote((string) $assetid); } // break up the assets into chunks of 1000 so that oracle does not complain $assetid_in = array(); foreach (array_chunk($assetids, 999) as $chunk) { $assetid_in[] = ' assetid IN (' . implode(', ', $chunk) . ')';
/** * Gets the children of the root nodes in the correct order from highest in the tree first to the * lowest. Taken from HIPO_Job_Update_Lookups->prepare(). * * @return array * @access public */ function getTreeSortedChildren($assetids) { $db = MatrixDAL::getDb(); $todo_normal = array(); $todo_shadows = array(); foreach ($assetids as $assetid) { // check if we are updating lookups for a shadow asset, or a bridge $id_parts = explode(':', $assetid); if (isset($id_parts[1])) { $todo_shadows = array_merge($todo_shadows, array_keys($GLOBALS['SQ_SYSTEM']->am->getChildren($assetid))); } else { $asset = $GLOBALS['SQ_SYSTEM']->am->getAsset($assetid); if ($asset instanceof Bridge) { if (!method_exists($asset, 'getChildren')) { trigger_localised_error('SYS0204', translate('Shadow asset handler "%s" can not get children'), E_USER_WARNING, $asset->name); } else { $todo_shadows = array_merge($todo_shadows, array_keys($asset->getChildren($assetid))); } } $where = 'l.minorid = :assetid'; $where = $GLOBALS['SQ_SYSTEM']->constructRollbackWhereClause($where, 't'); $where = $GLOBALS['SQ_SYSTEM']->constructRollbackWhereClause($where, 'l'); $sql = 'SELECT t.treeid FROM ' . SQ_TABLE_RUNNING_PREFIX . 'ast_lnk_tree t INNER JOIN ' . SQ_TABLE_RUNNING_PREFIX . 'ast_lnk l ON t.linkid = l.linkid ' . $where; $sql = db_extras_modify_limit_clause($sql, MatrixDAL::getDbType(), 1); try { $query = MatrixDAL::preparePdoQuery($sql); MatrixDAL::bindValueToPdo($query, 'assetid', $assetid); $treeid = MatrixDAL::executePdoOne($query); } catch (Exception $e) { throw new Exception('Unable to get treeid for minorid: ' . $assetid . ' due to database error: ' . $e->getMessage()); } $sql = 'SELECT l.minorid, MAX(LENGTH(t.treeid)) as length FROM ' . SQ_TABLE_RUNNING_PREFIX . 'ast_lnk_tree t INNER JOIN ' . SQ_TABLE_RUNNING_PREFIX . 'ast_lnk l ON t.linkid = l.linkid '; $where = 't.treeid LIKE :treeid GROUP BY l.minorid ORDER BY length'; $where = $GLOBALS['SQ_SYSTEM']->constructRollbackWhereClause($where, 't'); $where = $GLOBALS['SQ_SYSTEM']->constructRollbackWhereClause($where, 'l'); try { $query = MatrixDAL::preparePdoQuery($sql . $where); MatrixDAL::bindValueToPdo($query, 'treeid', $treeid . '%'); $new_assets = MatrixDAL::executePdoAssoc($query); } catch (Exception $e) { throw new Exception('Unable to get minorids for treeid: ' . $treeid[0]['treeid'] . ' due to database error: ' . $e->getMessage()); } $todo_normal = array_merge($todo_normal, $new_assets); } //end else } //end foreach // Make sure lower assets are done after higher ones usort($todo_normal, create_function('$a, $b', 'return $a[\'length\'] > $b[\'length\'];')); $todo_assetids = array(); foreach ($todo_normal as $asset_info) { $todo_assetids[] = $asset_info['minorid']; } $todo_assetids = array_unique(array_merge($todo_assetids, $todo_shadows)); return $todo_assetids; }
/** * Returns TRUE if all the comma seperate URLs are valid site URLs * * @param string $value * * @return boolean */ function _valid_site_url(&$value) { $urls = explode(',', $value); if (empty($urls)) { return FALSE; } $root_urls = explode("\n", SQ_CONF_SYSTEM_ROOT_URLS); $sql = 'SELECT url, http, https FROM sq_ast_url WHERE '; $bind_vars = array(); $protocol = array(); $valid_urls = array(); foreach ($urls as $index => $full_url) { $url_parts = explode('://', $full_url); if (count($url_parts) != 2) { continue; } if (in_array($url_parts[1], $root_urls) && ($url_parts[0] == 'http' || $url_parts[0] == 'https')) { $valid_urls[] = $full_url; } else { $protocol_field = $url_parts[0] == 'http' ? 'http' : 'https'; $sql .= ' (url = :url' . $index . ' AND ' . $protocol_field . ' = \'1\') OR'; $bind_vars['url' . $index] = $url_parts[1]; $protocol[$url_parts[1]] = $protocol_field; } } $sql = substr($sql, 0, strlen($sql) - 2); $query = MatrixDAL::preparePdoQuery($sql); foreach ($bind_vars as $var_name => $var_value) { MatrixDAL::bindValueToPdo($query, $var_name, $var_value); } $result = MatrixDAL::executePdoAssoc($query); foreach ($result as $row) { if ($row['http']) { $valid_urls[] = 'http://' . $row['url']; } //end if if ($row['https']) { $valid_urls[] = 'https://' . $row['url']; } //end if } //end forach protocols $invalid_sites = array_diff($urls, $valid_urls); if ($invalid_sites) { echo "ERROR: Following urls does not belongs to any site:\n" . implode("\n", $invalid_sites) . "\n"; return FALSE; } if (empty($valid_urls)) { return FALSE; } // Sort urls by length i.e. longer url on top uasort($valid_urls, function ($a, $b) { return strlen($a) < strlen($b); }); $value = $valid_urls; return TRUE; }
/** * Insert a file record info to sq_file_vers_file table if one does not exist * * @param File_Versioning $file_versioning the File_Versioning object * @param string $fileid the fileid of the file to be inserted * @param string $rep_file the repository file path of the file * @see add() in file_versioning.inc */ function _insertFileVersFileInfo($file_versioning, $fileid, $rep_file) { $sql = 'SELECT COUNT(*) FROM sq_file_vers_file WHERE fileid = :fileid'; $fileid_count = 0; try { $query = MatrixDAL::preparePdoQuery($sql); MatrixDAL::bindValueToPdo($query, 'fileid', $fileid); $fileid_count = MatrixDAL::executePdoOne($query); } catch (Exception $e) { echo "ERROR: " . $e->getMessage() . "\n"; return; } //the record info already exists, return without doing anything if ($fileid_count != 0) { return; } $GLOBALS['SQ_SYSTEM']->changeDatabaseConnection('db2'); $GLOBALS['SQ_SYSTEM']->doTransaction('BEGIN'); $sql = 'INSERT INTO sq_file_vers_file (fileid, path, filename) VALUES (:fileid, :path, :filename)'; try { $query = MatrixDAL::preparePdoQuery($sql); MatrixDAL::bindValueToPdo($query, 'fileid', $fileid); MatrixDAL::bindValueToPdo($query, 'path', dirname($rep_file)); MatrixDAL::bindValueToPdo($query, 'filename', basename($rep_file)); MatrixDAL::execPdoQuery($query); $GLOBALS['SQ_SYSTEM']->doTransaction('COMMIT'); $GLOBALS['SQ_SYSTEM']->restoreDatabaseConnection(); } catch (Exception $e) { echo "ERROR: " . $e->getMessage() . "\n"; $GLOBALS['SQ_SYSTEM']->doTransaction('ROLLBACK'); $GLOBALS['SQ_SYSTEM']->restoreDatabaseConnection(); } }
/** * Ensures the sort order is linear taking into account the existing sort_order * Begins from the provided root node and cleans all branches stemming from the provided root node * Note: This is based on Tom's Tool_Asset_Sorter - the difference: Tom's tool is not based on existing sort_order and does not recurse * * @param array $todo Parents to sort * @param array $done Parents done sorting * * @return boolean * @access public */ function sortAssets($todo, $done) { if (!empty($todo)) { $parentid = array_shift($todo); // order by existing sort_order // only concerned with TYPE_1 and TYPE_2 // retrieve minorids as well because we need them for the recursive behaviour implemented towards the end of this routine $sql = 'SELECT linkid, minorid FROM sq_ast_lnk WHERE majorid = :parentid AND link_type IN (' . MatrixDAL::quote(SQ_LINK_TYPE_1) . ', ' . MatrixDAL::quote(SQ_LINK_TYPE_2) . ') ORDER BY sort_order ASC'; try { $query = MatrixDAL::preparePdoQuery($sql); MatrixDAL::bindValueToPdo($query, 'parentid', $parentid); $results = MatrixDAL::executePdoAssoc($query); } catch (Exception $e) { throw new Exception('Unable to get linkids for parent: ' . $parentid . ' due to database error: ' . $e->getMessage()); } echo "\n" . '- Updating the sort order for kids of: #' . $parentid . '...'; // separate results $childids = $linkids = array(); foreach ($results as $row) { // linkids used to update the sort_order $linkids[] = $row['linkid']; // childids used to look for more parents $childids[] = $row['minorid']; } if (!empty($linkids)) { // there is a limit to CASE statement size in Oracle, that limits it to // 127 WHEN-THEN pairs (in theory), so limit to 127 at a time on Oracle $db_type = MatrixDAL::getDbType(); if ($db_type == 'oci') { $chunk_size = 127; } else { $chunk_size = 500; } $GLOBALS['SQ_SYSTEM']->doTransaction('BEGIN'); foreach (array_chunk($linkids, $chunk_size, TRUE) as $chunk) { $cases = ''; foreach ($chunk as $i => $linkid) { $cases .= 'WHEN (linkid = ' . $linkid . ') THEN ' . $i . ' '; } $sql = 'UPDATE sq_ast_lnk SET sort_order = CASE ' . $cases . ' ELSE sort_order END WHERE linkid IN (' . implode(', ', $chunk) . ')'; try { $result = MatrixDAL::executeSql($sql); } catch (Exception $e) { throw new Exception('Unable to update sort_order for parent: ' . $parentid . ' due to database error: ' . $e->getMessage()); $GLOBALS['SQ_SYSTEM']->doTransaction('ROLLBACK'); $GLOBALS['SQ_SYSTEM']->restoreDatabaseConnection(); } } $GLOBALS['SQ_SYSTEM']->doTransaction('COMMIT'); } // ensure we do not update this parent again if (!in_array($parentid, $done)) { $done[] = $parentid; } echo ' [done]'; // check each child of the parent to see if the parent is a grandparent (i.e. parent's children have children) // only examining 1 level deep at a time if (!empty($childids)) { echo "\n\t" . '- Searching immediate children of: #' . $parentid . ' for branches'; foreach ($childids as $assetid) { // check we have not processed it yet if (!in_array($assetid, $done)) { // these are the kids that we have already sorted // check to see if they are parents as well // shadow asset links are ignored $sql = 'SELECT minorid FROM sq_ast_lnk WHERE majorid = :assetid'; try { $query = MatrixDAL::preparePdoQuery($sql); MatrixDAL::bindValueToPdo($query, 'assetid', $assetid); $children = MatrixDAL::executePdoAssoc($query); } catch (Exception $e) { throw new Exception('Unable to check children of parent: ' . $parentid . ' due to database error: ' . $e->getMessage()); } if (!empty($children) && count($children) > 1) { // we have a potential new parent // check that the returned children contain at least one TYPE 1 or 2 linked asset // e.g. asset could just be tagged with a thesaurus term (shadow link), meaning it is not a valid parent $valid = FALSE; foreach ($children as $grandchild) { $link = $GLOBALS['SQ_SYSTEM']->am->getLink($grandchild['minorid'], NULL, '', TRUE, NULL, 'minor'); if (!empty($link) && ($link['link_type'] == SQ_LINK_TYPE_1 || $link['link_type'] == SQ_LINK_TYPE_2)) { $valid = TRUE; break; } } if ($valid) { echo "\n\t\t#" . $assetid . ' is a parent with kids that will be sorted'; $todo[] = $assetid; } } } } } echo "\n" . '* ' . count($todo) . ' items left to process' . "\n"; echo '* Using ' . round(memory_get_usage() / 1048576, 2) . ' MB' . "\n"; sortAssets($todo, $done); } else { // there are no more items to process return TRUE; } }
if ($source_dsn['type'] === 'oci' && $data_value === NULL) { $data_value = ''; } } /** * bytea fields from postgres are returned as resources * Convert them from resources into actual text content * * See http://www.php.net/manual/en/pdo.lobs.php */ if (is_resource($data_value)) { $stream = $data_value; $data_value = stream_get_contents($stream); fclose($stream); } MatrixDAL::bindValueToPdo($prepared_sql, $data_key, $data_value); } MatrixDAL::execPdoQuery($prepared_sql); $trans_count++; if ($trans_count % 10000 == 0) { MatrixDAL::commit(); MatrixDAL::beginTransaction(); $trans_count = 0; } } printName('Inserting Data (' . number_format($start) . ' - ' . number_format($start + $count) . ' rows)'); printUpdateStatus('OK', "\r"); MatrixDAL::commit(); /** * Switch to the source db connector to get the data.. */
/** * Searches the Matrix System for an existing direct child asset which matches the specified name (and optionally, asset type) * The asset IDs of matching assets are returned. * * @param int $parent_id The asset ID under which to search for direct matching child assets * @param string $asset_name The asset name to match * @param string $asset_type_code The asset type code to match (optional) * * @return array * @access public */ function searchExistingAsset($parent_id, $asset_name, $asset_type_code = '') { $db = MatrixDAL::getDb(); $sql = 'SELECT l.minorid, a.name ' . 'FROM sq_ast_lnk l, sq_ast a ' . 'WHERE l.majorid = :majorid '; if (!empty($asset_type_code)) { $sql .= 'AND a.type_code = :type_code '; } $sql .= 'AND a.assetid = l.minorid ' . 'AND a.name = :asset_name'; try { $query = MatrixDAL::preparePdoQuery($sql); MatrixDAL::bindValueToPdo($query, 'majorid', $parent_id); MatrixDAL::bindValueToPdo($query, 'asset_name', $asset_name); if (!empty($asset_type_code)) { MatrixDAL::bindValueToPdo($query, 'type_code', $asset_type_code); } $matching_assets = MatrixDAL::executePdoAssoc($query, 0); } catch (Exception $e) { throw new Exception('Unable to search for an existing ' . $asset_name . ' asset: ' . $e->getMessage()); } return $matching_assets; }
foreach ($products as $product) { $suiteid = array_get_index($product, 'suiteid', NULL); $connection = array_get_index($product, 'connection', NULL); if ($suiteid === NULL || $connection === NULL) { continue; } $connection = @unserialize($connection); if ($connection === FALSE) { continue; } $url = array_get_index($connection, 'url', NULL); if ($url === NULL) { continue; } unset($connection['url']); $query = MatrixDAL::preparePdoQuery('UPDATE sq_suite_product SET url=:url, connection=:connection WHERE suiteid=:id'); MatrixDAL::bindValueToPdo($query, 'url', $url); MatrixDAL::bindValueToPdo($query, 'connection', serialize($connection)); MatrixDAL::bindValueToPdo($query, 'id', $suiteid); MatrixDAL::execPdoQuery($query); } //end foreach // Set the not null constraint on columns. MatrixDAL::executeSql('ALTER TABLE sq_suite_product MODIFY suiteid NOT NULL'); MatrixDAL::executeSql('ALTER TABLE sq_suite_product MODIFY url NOT NULL'); // Set the new constraints and keys MatrixDAL::executeSql('ALTER TABLE sq_suite_product ADD CONSTRAINT suite_product_pk PRIMARY KEY (suiteid)'); MatrixDAL::executeSql('CREATE INDEX sq_suite_product_type ON sq_suite_product (systemid, type, status)'); } //end if $GLOBALS['SQ_SYSTEM']->restoreDatabaseConnection();
MatrixDAL::execPdoQuery($delete_tree_query); } catch (Exception $e) { trigger_error('Unable to delete tree links for linkid: ' . $link['linkid'] . ' due to database error: ' . $e->getMessage(), E_USER_ERROR); } // Update sort orders of other children of this parent $sql = 'UPDATE sq_ast_lnk SET sort_order = sort_order - 1 WHERE majorid = :majorid AND sort_order > :sort_order'; $update_sort_order_query = MatrixDAL::preparePdoQuery($sql); try { MatrixDAL::bindValueToPdo($update_sort_order_query, 'majorid', $link['majorid']); MatrixDAL::bindValueToPdo($update_sort_order_query, 'sort_order', $link['sort_order']); MatrixDAL::execPdoQuery($update_sort_order_query); } catch (Exception $e) { trigger_error('Unable to update sort orders for majorid: ' . $link['majorid'] . ' due to database error: ' . $e->getMessage(), E_USER_ERROR); } // Delete from the link table try { $bind_vars = array('linkid' => $link['linkid'], 'majorid' => $link['majorid']); MatrixDAL::executeQuery('core', 'deleteLink', $bind_vars); } catch (Exception $e) { trigger_error('Unable to delete link with linkid: ' . $link['linkid'] . ' due to database error: ' . $e->getMessage(), E_USER_ERROR); } $GLOBALS['SQ_SYSTEM']->doTransaction('COMMIT'); echo "Deleted Link ID: " . $link['linkid'] . " with Major ID: " . $link['majorid'] . " and Minor ID: " . $link['minorid'] . "\n"; } // end foreach link
if (!$value) { echo "\n CANNOT BE FIXED!"; } else { $fixed_count++; } if ($FIX_DB && $value) { // Update the db with the fixed serialsed data try { $sql = 'UPDATE ' . $table_prefix . 'ast_attr_val SET custom_val=:value ' . 'WHERE assetid=:assetid AND attrid=:attrid AND contextid=:contextid' . ($rollback ? ' AND sq_eff_from=:sq_eff_from' : ''); $update_sql = MatrixDAL::preparePdoQuery($sql); MatrixDAL::bindValueToPdo($update_sql, 'value', $value); MatrixDAL::bindValueToPdo($update_sql, 'assetid', $assetid); MatrixDAL::bindValueToPdo($update_sql, 'attrid', $attrid); MatrixDAL::bindValueToPdo($update_sql, 'contextid', $contextid); if ($rollback) { MatrixDAL::bindValueToPdo($update_sql, 'sq_eff_from', $eff_from); } $execute = MatrixDAL::execPdoQuery($update_sql); } catch (Exception $e) { echo "Unexpected error occured while updating database: " . $e->getMessage(); echo "\nNo database changes were made"; $GLOBALS['SQ_SYSTEM']->doTransaction('ROLLBACK'); exit(1); } } } //end if invalid serialsed data } //end foreach asset attr values } //end foreach result entries
// does it match with root matrix url $is_matrix_url = FALSE; foreach ($root_urls as $root_url) { if (strpos($url, $root_url . '/') === 0 || $url === $root_url) { $is_matrix_url = TRUE; break; } } // if it's not a Matrix URL, no need to show warning if ($is_matrix_url) { // if it's one of special url, no need to show warning if (strpos($url, '__data') === FALSE && strpos($url, '__lib') === FALSE && strpos($url, '__fudge') === FALSE) { $url_asset = $GLOBALS['SQ_SYSTEM']->am->getAssetFromURL($protocol, $url, TRUE, TRUE); $count_query = 'SELECT count(*) FROM sq_ast_lookup_remap WHERE url = :url '; $count_result = MatrixDAL::preparePdoQuery($count_query); MatrixDAL::bindValueToPdo($count_result, 'url', $url_info['remap_url']); $remap_url = MatrixDAL::executePdoOne($count_result); // if the new url is not an valid asset url, neither another redirect url, show warning if (empty($url_asset) && empty($remap_url)) { $affected_result[] = $url_info; } $GLOBALS['SQ_SYSTEM']->am->forgetAsset($url_asset); unset($url_asset); } } } } if (getCLIArg('execute') && !empty($affected_result)) { // delete them $count = deleteRemaps($affected_result); echo $count . " remaps deleted\n";
/** * Fix rollback table * * @param $table_name * @param $table_info * @param $entries * * @return boolean * @access public */ function fix_rollback_table($table_name, $table_info, $entries = array()) { $GLOBALS['SQ_SYSTEM']->changeDatabaseConnection('db2'); $GLOBALS['SQ_SYSTEM']->doTransaction('BEGIN'); $success = TRUE; switch ($table_name) { // TODO: If individual tables are required to be handled specifically then it should be done here default: // For all rollback tables, delete all the duplicates expect one with min(eff_from) i.e. oldest one $keys = $table_info['primary_key']; if (isset($table_info['unique_key'])) { $keys = array_unique(array_merge($keys, $table_info['unique_key'])); } if (empty($keys)) { echo "\nPrimary key fields not found the rollback table " . $table_name; $success = FALSE; break; } $where_sql = ''; foreach ($keys as $key) { $where_sql .= $key . ' = :' . $key . ' AND '; } $where_sql .= 'sq_eff_to = :sq_eff_to'; // Get the oldest 'eff_from' $sub_sql = 'SELECT min(sq_eff_from) FROM sq_rb_' . $table_name . ' WHERE ' . $where_sql; $sql = 'DELETE FROM sq_rb_' . $table_name . ' WHERE ' . $where_sql . ' AND sq_eff_from > (' . $sub_sql . ')'; foreach ($entries as $entry) { try { $update_sql = MatrixDAL::preparePdoQuery($sql); foreach ($entry as $row_name => $row_val) { if ($row_name == 'occ') { continue; } MatrixDAL::bindValueToPdo($update_sql, $row_name, $row_val); } $execute = MatrixDAL::executePdoAssoc($update_sql); } catch (Exception $e) { $success = FALSE; break; } } break; } //end switch $GLOBALS['SQ_SYSTEM']->doTransaction($success ? 'COMMIT' : 'ROLLBACK'); $GLOBALS['SQ_SYSTEM']->restoreDatabaseConnection(); if (!$success) { echo "\nUnexpected error occured while updating database: " . $e->getMessage(); } return $success; }
if (!is_dir($SYSTEM_ROOT) || !is_readable($SYSTEM_ROOT . '/core/include/init.inc')) { echo "ERROR: Path provided doesn't point to a Matrix installation's System Root. Please provide correct path and try again.\n"; exit; } require_once $SYSTEM_ROOT . '/core/include/init.inc'; $root_user =& $GLOBALS['SQ_SYSTEM']->am->getSystemAsset('root_user'); // log in as root if (!$GLOBALS['SQ_SYSTEM']->setCurrentUser($root_user)) { echo "ERROR: Failed login in as root user\n"; exit; } $GLOBALS['SQ_SYSTEM']->changeDatabaseConnection('db2'); $GLOBALS['SQ_SYSTEM']->doTransaction('BEGIN'); $old_date = date('Y-m-d', strtotime('-1 week')); $sql = "SELECT assetid\n\t\tFROM sq_ast_attr_val\n\t\tWHERE\n\t\t\tattrid IN\n\t\t\t\t(select attrid from sq_ast_attr where (type_code = 'cron_job' or owning_type_code = 'cron_job') and name='when')\n\t\t\t\tAND CAST(custom_val AS varchar2(255)) < :old_date"; $query = MatrixDAL::preparePdoQuery($sql); MatrixDAL::bindValueToPdo($query, 'old_date', 'OO=' . $old_date); $assetids = MatrixDAL::executePdoAssoc($query, 0); if (empty($assetids)) { echo "No old cron jobs found\n"; } else { echo 'Found ' . count($assetids) . ' old crons' . "\n"; $assetids_list = '(' . implode(', ', $assetids) . ')'; $res = MatrixDAL::executeSql('DELETE FROM sq_ast WHERE assetid IN ' . $assetids_list); $res = MatrixDAL::executeSql('DELETE FROM sq_ast_lnk WHERE minorid IN ' . $assetids_list); $res = MatrixDAL::executeSql('DELETE FROM sq_ast_lnk_tree WHERE linkid NOT IN (SELECT linkid FROM sq_ast_lnk)'); $res = MatrixDAL::executeSql('DELETE FROM sq_ast_attr_val WHERE assetid IN ' . $assetids_list); } $GLOBALS['SQ_SYSTEM']->doTransaction('COMMIT'); $GLOBALS['SQ_SYSTEM']->restoreDatabaseConnection(); echo "Done\n";
foreach ($assets_of_type as $index => $val) { $assets_of_type[$index] = MatrixDAL::quote($val); } if (!empty($assets_of_type)) { $asset_ids_set = '(' . implode(', ', $assets_of_type) . ')'; $sql = 'DELETE FROM sq_ast_attr_val WHERE assetid in ' . $asset_ids_set; $query = MatrixDAL::preparePdoQuery($sql); MatrixDAL::execPdoQuery($query); $sql = 'DELETE FROM sq_ast_lnk WHERE minorid in ' . $asset_ids_set; $query = MatrixDAL::preparePdoQuery($sql); MatrixDAL::execPdoQuery($query); $sql = 'DELETE FROM sq_ast WHERE type_code = :type_code'; $query = MatrixDAL::preparePdoQuery($sql); MatrixDAL::bindValueToPdo($query, 'type_code', $DELETING_ASSET_TYPE); MatrixDAL::execPdoQuery($query); } $sql = 'DELETE FROM sq_ast_attr WHERE type_code = :type_code OR owning_type_code = :owning_type_code'; $query = MatrixDAL::preparePdoQuery($sql); MatrixDAL::bindValueToPdo($query, 'type_code', $DELETING_ASSET_TYPE); MatrixDAL::bindValueToPdo($query, 'owning_type_code', $DELETING_ASSET_TYPE); MatrixDAL::execPdoQuery($query); $sql = 'DELETE FROM sq_ast_typ WHERE type_code = :type_code'; $query = MatrixDAL::preparePdoQuery($sql); MatrixDAL::bindValueToPdo($query, 'type_code', $DELETING_ASSET_TYPE); MatrixDAL::execPdoQuery($query); $sql = 'DELETE FROM sq_ast_typ_inhd WHERE type_code = :type_code'; $query = MatrixDAL::preparePdoQuery($sql); MatrixDAL::bindValueToPdo($query, 'type_code', $DELETING_ASSET_TYPE); MatrixDAL::execPdoQuery($query); assert_true(unlink(dirname(dirname(__FILE__)) . '/data/private/db/asset_types.inc'), 'failed removing asset_types.inc'); echo "\nDone\n";
printAssetName($asset); printUpdateStatus('OK'); // conserve memory and move on to the next perm $GLOBALS['SQ_SYSTEM']->am->forgetAsset($asset); continue; } // the asset doesn't exist $dummy_asset->id = $user_id; $dummy_asset->name = 'Unknown Asset'; printAssetName($dummy_asset); // open the transaction $GLOBALS['SQ_SYSTEM']->doTransaction('BEGIN'); try { $sql = 'DELETE FROM sq_ast_role WHERE userid = :userid'; $query = MatrixDAL::preparePdoQuery($sql); MatrixDAL::bindValueToPdo($query, 'userid', $user_id); MatrixDAL::execPdoQuery($query); // all good $GLOBALS['SQ_SYSTEM']->doTransaction('COMMIT'); printUpdateStatus('FIXED'); } catch (DALException $e) { // no good $GLOBALS['SQ_SYSTEM']->doTransaction('ROLLBACK'); printUpdateStatus('FAILED'); } } //end for /** * Prints the name of the Asset as a padded string * * Pads name to 40 columns
/** * Get the next sort order for the next asset under a parent asset * * @param $parent_assetid The parent asset to get its children' next sort order * @return int */ function getNextSortOrder($parent_assetid) { $GLOBALS['SQ_SYSTEM']->changeDatabaseConnection('db'); $sql = 'SELECT COUNT(*) as count, MAX(sort_order) as max FROM sq_ast_lnk WHERE majorid = :majorid'; try { $query = MatrixDAL::preparePdoQuery($sql); MatrixDAL::bindValueToPdo($query, 'majorid', $parent_assetid); $result = MatrixDAL::executePdoAll($query); $row = $result[0]; unset($result); } catch (Exception $e) { throw new Exception("Unable to get the last sort order of the parent asset #{$parent_assetid} , due to database error: " . $e->getMessage()); } $next_sort_order = $row['count'] > 0 ? max($row['count'], $row['max'] + 1) : 0; $GLOBALS['SQ_SYSTEM']->restoreDatabaseConnection(); return $next_sort_order; }
/** * Finds an existing asset matching the exact type with the metadata or attribute value supplied * * @return void * @access public */ function findAsset($root_asset_id, $asset_type_code, array $search) { // Begin uberquery! $db = MatrixDAL::getDb(); $search_type_attribute = isset($search['attribute']); $field_name = ''; $field_value = ''; if ($search_type_attribute) { $field_name = $search['attribute']['field']; $field_value = $search['attribute']['value']; } else { $field_name = $search['metadata']['field']; $field_value = $search['metadata']['value']; } $tree_id = ''; // Grab a single tree ID so we can search our entire root asset $sql = 'SELECT t.treeid FROM sq_ast_lnk_tree t, sq_ast_lnk l WHERE l.linkid = t.linkid AND l.minorid = :root_asset_id LIMIT 1'; try { $query = MatrixDAL::preparePdoQuery($sql); MatrixDAL::bindValueToPdo($query, 'root_asset_id', $root_asset_id); $tree_id = MatrixDAL::executePdoOne($query); } catch (Exception $e) { throw new Exception('Unable to search for an existing ' . $asset_type_code . ' asset: ' . $e->getMessage()); } if ($tree_id == '') { return array(); } // Query portion for restricting by attribute $attribute_sql_from = 'sq_ast_attr r, sq_ast_attr_val v '; // Query portion for restricting by metadata field value $metadata_sql_from = 'sq_ast_mdata_val m '; $sql = 'SELECT a.assetid, a.name ' . 'FROM sq_ast a, sq_ast_lnk l, sq_ast_lnk_tree t, ' . ($search_type_attribute ? $attribute_sql_from : $metadata_sql_from) . 'WHERE t.treeid LIKE :tree_id ' . 'AND l.linkid = t.linkid AND a.assetid = l.minorid '; if (!empty($asset_type_code)) { $sql .= 'AND a.type_code = :type_code '; } if ($search_type_attribute) { $sql .= ' AND v.assetid = a.assetid AND r.name = :field_name AND v.attrid = r.attrid AND v.custom_val = :field_val'; } else { $sql .= ' AND m.assetid = a.assetid AND m.fieldid = :field_name AND m.value = :field_val'; } try { $query = MatrixDAL::preparePdoQuery($sql); MatrixDAL::bindValueToPdo($query, 'tree_id', $tree_id . '%'); MatrixDAL::bindValueToPdo($query, 'field_name', $field_name); MatrixDAL::bindValueToPdo($query, 'field_val', $field_value); if (!empty($asset_type_code)) { MatrixDAL::bindValueToPdo($query, 'type_code', $asset_type_code); } $matching_assets = MatrixDAL::executePdoAssoc($query, 0); } catch (Exception $e) { throw new Exception('Unable to search for an existing ' . $asset_type_code . ' asset: ' . $e->getMessage()); } return $matching_assets; }
/** * Execute the write db query * * @param string $sql * * @return boolean|int */ function _executeSql($sql, $bind_vars) { try { $query = MatrixDAL::preparePdoQuery($sql); foreach ($bind_vars as $bind_var => $bind_value) { MatrixDAL::bindValueToPdo($query, $bind_var, $bind_value); } $count = MatrixDAL::execPdoQuery($query); } catch (Exception $e) { global $ERRORS; $error = 'DB Exception ' . $e->getMessage() . "\n" . "SQL: " . $sql; foreach ($bind_vars as $key => $val) { $error = str_replace(':' . $key, MatrixDAL::quote($val), $error); } $ERRORS[] = $error; return FALSE; } return $count; }
$purge_rootnode = isset($_SERVER['argv'][2]) ? $_SERVER['argv'][2] : 0; if (!empty($purge_rootnode)) { // do some checking to make sure there is a link to the trash folder $trash_folder = $GLOBALS['SQ_SYSTEM']->am->getSystemAsset('trash_folder'); $db = $GLOBALS['SQ_SYSTEM']->db; $sql = 'select linkid from sq_ast_lnk where minorid = :root_node and majorid = :trash_assetid'; $query = MatrixDAL::preparePdoQuery($sql); MatrixDAL::bindValueToPdo($query, 'root_node', $purge_rootnode); MatrixDAL::bindValueToPdo($query, 'trash_assetid', $trash_folder->id); $linkid = MatrixDAL::executePdoOne($query); if (!empty($linkid)) { // purge trash hipo will know what to do $vars['purge_root_linkid'] = $linkid; } else { printStdErr("Purge root node assetid id #" . $purge_rootnode . " not found\n"); exit(1); } } $hh = $GLOBALS['SQ_SYSTEM']->getHipoHerder(); $errors = $hh->freestyleHipo('hipo_job_purge_trash', $vars); if (!empty($errors)) { $error_msg = ''; foreach ($errors as $error) { $error_msg .= ' * ' . $error['message'];
/** * Actually perform the changes to the files * * @param string $root_node Asset ID of the root node to search for Files from * @param int $setting The unrestricted setting to change assets to * (0 = restricted, 1 = unrestricted) * @param array $file_assretids assetid of all the file type assets found under the root node * * @return void */ function do_set_unrestricted($root_node, $setting, $file_assetids) { $GLOBALS['SQ_SYSTEM']->changeDatabaseConnection('db2'); $GLOBALS['SQ_SYSTEM']->doTransaction('BEGIN'); $return = array('changed' => 0, 'failed' => 0); $root_asset = $GLOBALS['SQ_SYSTEM']->am->getAsset($root_node); $child_query = $GLOBALS['SQ_SYSTEM']->am->generateGetChildrenQuery($root_asset, 'file', FALSE); // Children query normally selects asset ID and type code. We don't want type code. $child_query['sql_array']['select'] = str_replace(', a.type_code', '', $child_query['sql_array']['select']); $child_query['sql_array']['union_select'] = str_replace(', null AS type_code', '', $child_query['sql_array']['union_select']); $sql = 'SELECT assetid FROM sq_ast_attr_val'; $where = ' WHERE assetid IN (' . implode(' ', $child_query['sql_array']) . ') AND attrid IN (SELECT attrid FROM sq_ast_attr WHERE type_code IN (SELECT type_code FROM sq_ast_typ_inhd WHERE inhd_type_code = :inhd_type_code) AND name = :attr_name) AND custom_val <> :setting'; $bind_vars = array('inhd_type_code' => 'file', 'attr_name' => 'allow_unrestricted', 'setting' => (int) $setting); // Get the assets (so we can update their lookups later)\ try { status_message_start('Finding files to change...'); $bind_vars = array_merge($bind_vars, $child_query['bind_vars']); $query = MatrixDAL::preparePdoQuery($sql . $where); foreach ($bind_vars as $bind_var => $bind_value) { MatrixDAL::bindValueToPdo($query, $bind_var, $bind_value); } $result = array_keys(MatrixDAL::executePdoGroupedAssoc($query)); } catch (Exception $e) { status_message_result('DB ERROR'); throw new Exception('Database error: ' . $e->getMessage()); } // bug fix #4649 set_files_unrestricted.php doesn't change any assets // since we have all the file type asset's assetid we will check and // see if any of them has the attributes not set $sql_query = 'SELECT assetid FROM sq_ast_attr_val l WHERE l.assetid IN (\'' . implode('\', \'', array_keys($file_assetids)) . '\') AND l.attrid IN (SELECT attrid FROM sq_ast_attr WHERE type_code IN (SELECT type_code FROM sq_ast_typ_inhd WHERE inhd_type_code = \'file\') AND name = \'allow_unrestricted\')'; $good_assets = MatrixDAL::executeSqlAssoc($sql_query); $additional_assets = array_keys($file_assetids); foreach ($good_assets as $good_asset) { foreach ($additional_assets as $index => $additional_asset) { if ($additional_assets[$index] == $good_asset['assetid']) { unset($additional_assets[$index]); } } } status_message_result(count($result) + count($additional_assets) . ' assets to update'); // If there were any assets, update them in one hit, and then update // the lookups if (count($result) + count($additional_assets) > 0) { status_message_start('Updating attributes...'); // update try { $update_sql = 'UPDATE sq_ast_attr_val SET custom_val = :new_setting'; $bind_vars['new_setting'] = (int) $setting; $query = MatrixDAL::preparePdoQuery($update_sql . $where); foreach ($bind_vars as $bind_var => $bind_value) { MatrixDAL::bindValueToPdo($query, $bind_var, $bind_value); } MatrixDAL::execPdoQuery($query); status_message_result('OK'); } catch (Exception $e) { status_message_result('DB ERROR'); throw new Exception('Database error: ' . $e->getMessage()); } // insert foreach ($additional_assets as $additional_asset) { $asset = $GLOBALS['SQ_SYSTEM']->am->getAsset($additional_asset); $GLOBALS['SQ_SYSTEM']->setRunLevel(SQ_RUN_LEVEL_FORCED); $asset->setAttrValue('allow_unrestricted', (int) $setting); $asset->saveAttributes(TRUE); $GLOBALS['SQ_SYSTEM']->restoreRunLevel(); } $assetids_to_update = array_merge($result, $additional_assets); $deja_vu = $GLOBALS['SQ_SYSTEM']->getDejaVu(); if ($deja_vu->enabled()) { foreach ($assetids_to_update as $assetid) { $deja_vu->forget('asset', $assetid); } //end foreach } //end if // Now update lookups status_message_start('Updating lookups...'); $hh = $GLOBALS['SQ_SYSTEM']->getHipoHerder(); $vars = array('assetids' => $assetids_to_update); $errors = $hh->freestyleHipo('hipo_job_update_lookups', $vars); if (empty($errors)) { status_message_result('OK'); } else { status_message_result('ERRORS'); pre_echo($errors); } } $GLOBALS['SQ_SYSTEM']->doTransaction('COMMIT'); $GLOBALS['SQ_SYSTEM']->restoreDatabaseConnection(); }
/** * Retrieve data from matrix in order to perform tasks * Data retrieved is relevant to the current run settings and gives the * worker functions the information required to process the request * * @return array * @access protected **/ protected function getAssetInfo() { $file = '/tmp/regenfs-assetdata-' . time() . '.tmp'; $retrievingTemplate = 'Retrieving Asset Data' . "\t\t\t" . '%s %s'; printf($retrievingTemplate, '....', "\r"); // Start a new child process $child = $this->pcntl->fork(); if ($child === true) { // We're the child, let's do some work $this->matrixSetup(); // Ensure Root node is a valid asset or die if (!$GLOBALS['SQ_SYSTEM']->am->assetExists($this->rootNode)) { trigger_error(sprintf('Root node \'%s\' does not exist!', $this->rootNode), E_USER_WARNING); posix_kill($this->pcntl->parentID, 9); exit(1); } // Get all contexts $contextids = array_keys($GLOBALS['SQ_SYSTEM']->getAllContexts()); $assets = array(); // If we're processing designs, include them on the list if ($this->processDesigns) { $assets['design'] = array_keys($GLOBALS['SQ_SYSTEM']->am->getChildren($this->rootNode, array('design', 'design_css'), true)); } // If we're processing metadata, include them on the list if ($this->processMetadata) { $assets['metadata'] = array(); $nodeassets = array_keys($GLOBALS['SQ_SYSTEM']->am->getChildren($this->rootNode)); $nodeassets = array_chunk($nodeassets, 100); foreach ($nodeassets as $k => $entries) { $keys = array_keys($entries); // Add binding character array_walk($keys, create_function('&$v,$k', '$v=\':a\'.$v;')); // Implode key array into sql statement for pdo processing $sql = 'select distinct assetid from sq_ast_mdata where assetid in (' . implode(',', $keys) . ')'; unset($keys); $query = MatrixDAL::preparePdoQuery($sql); // Bind keys to entry foreach ($entries as $kk => $entry) { MatrixDAL::bindValueToPdo($query, 'a' . $kk, $entry); } $metadata = MatrixDAL::executePdoAssoc($query); foreach ($metadata as $k => $row) { $assets['metadata'][] = $row['assetid']; } } } // If we're processing bodycopies, include them on the asset list if ($this->processBodycopies) { $content_type_assetids = array_keys($GLOBALS['SQ_SYSTEM']->am->getChildren($this->rootNode, 'content_type', false)); $assets['bodycopy'] = array(); foreach ($content_type_assetids as $assetid) { $bodycopy_container_link = $GLOBALS['SQ_SYSTEM']->am->getLinks($assetid, SQ_LINK_TYPE_2, array('bodycopy_container'), FALSE, 'minor'); if (isset($bodycopy_container_link[0]['majorid'])) { $assets['bodycopy'][] = $bodycopy_container_link[0]['majorid']; } } } $output = array(); $output['contextids'] = $contextids; $output['assets'] = $assets; // Output data to the data file file_put_contents($file, serialize($output)); exit(0); } else { if (is_numeric($child)) { // We're the parent, let's rest while the child is doing the chores while ($this->pcntl->childRunning($child) === true) { sleep(1); } // Child has done it's chores, check to see if the file it needs to create is there, then process it if (file_exists($file)) { // File is there, get the file contents and then delete the file $response = file_get_contents($file); unlink($file); $response = @unserialize($response); printf($retrievingTemplate, '[DONE]', "\n"); if (is_array($response)) { return $response; } else { return array(); } } } } }
function renameTerm($fieldids, $old_term, $new_term) { // Do the rename per asset and play nice with ORACLE $chunk_size = 1000; $field_chunks = array_chunk($fieldids, $chunk_size); foreach ($field_chunks as $field_chunk) { // Quoting Shakespeare foreach ($field_chunk as $index => $field_assetid) { $field_chunk[$index] = MatrixDAL::quote($field_assetid); } //end foreach $sql = "SELECT value, assetid, fieldid, contextid FROM sq_ast_mdata_val WHERE value like '{$old_term},%' OR value like '%,{$old_term}' OR value like '%,{$old_term},%'"; $results = MatrixDAL::executeSqlAssoc($sql); if (!empty($results)) { foreach ($results as $index => $result) { $asset_id = $result['assetid']; $value = $result['value']; $field_id = $result['fieldid']; $contextid = $result['contextid']; $pattern_1 = '/(.*)' . $old_term . '$/'; $pattern_2 = '/^' . $old_term . '(.*)/'; $pattern_3 = '/(.*)' . $old_term . '(.*)/'; $replacement_1 = '$1' . $new_term; $replacement_2 = $new_term . '$1'; $replacement_3 = '$1' . $new_term . '$2'; if (preg_match($pattern_2, $value)) { $new_value = preg_replace($pattern_2, $replacement_2, $value); } else { if (preg_match($pattern_1, $value)) { $new_value = preg_replace($pattern_1, $replacement_1, $value); } else { $new_value = preg_replace($pattern_3, $replacement_3, $value); } } // Run the Query against the current assetid if (MatrixDAL::getDbType() === 'oci') { $sql = 'UPDATE sq_ast_mdata_val SET value=:new_value WHERE TO_CHAR(value)=:old_value AND contextid=:contextid AND fieldid=:fieldid AND assetid=:assetid'; } else { $sql = 'UPDATE sq_ast_mdata_val SET value=:new_value WHERE value=:old_value AND contextid=:contextid AND fieldid=:fieldid AND assetid=:assetid'; } //end if try { $query = MatrixDAL::preparePdoQuery($sql); MatrixDAL::bindValueToPdo($query, 'new_value', $new_value); MatrixDAL::bindValueToPdo($query, 'old_value', $value); MatrixDAL::bindValueToPdo($query, 'contextid', $contextid); MatrixDAL::bindValueToPdo($query, 'fieldid', $field_id); MatrixDAL::bindValueToPdo($query, 'assetid', $asset_id); MatrixDAL::execPdoQuery($query); } catch (Exception $e) { throw new Exception('DB Error: ' . $e->getMessage()); } } } // Run the Query against the current assetid if (MatrixDAL::getDbType() === 'oci') { $sql = 'UPDATE sq_ast_mdata_val SET value=:new_term WHERE TO_CHAR(value)=:old_term AND fieldid IN (' . implode(',', $field_chunk) . ')'; } else { $sql = 'UPDATE sq_ast_mdata_val SET value=:new_term WHERE value=:old_term AND fieldid IN (' . implode(',', $field_chunk) . ')'; } //end if // Update EVERYTHING try { $query = MatrixDAL::preparePdoQuery($sql); MatrixDAL::bindValueToPdo($query, 'new_term', $new_term); MatrixDAL::bindValueToPdo($query, 'old_term', $old_term); MatrixDAL::execPdoQuery($query); } catch (Exception $e) { throw new Exception('DB Error: ' . $e->getMessage()); } } }
/** * Remove internal messages * * @param string $period The period to remove internal messages before * @param string $user_from The userid that the message is sent from * @param string $user_to The userid that the message is sent to * @param string $msg_type The type of internal message to remove, e.g. asset.linking.create, cron.* * @param string $msg_status The status of internal message to remove, e.g. U or D * @param array $assetids The asset id's to delete messages for. * * @return void * @access public */ function purge_internal_message($period, $user_from = '', $user_to = '', $msg_type = '', $msg_status = '', $assetids = array()) { global $db, $QUIET, $SHOW_QUERY_ONLY; $bind_vars = array(); $sql = 'DELETE FROM' . "\n"; $sql .= ' ' . SQ_TABLE_RUNNING_PREFIX . 'internal_msg' . "\n"; $sql .= 'WHERE' . "\n"; $sql .= ' sent <= :sent_before' . "\n"; $bind_vars['sent_before'] = $period; $userids = array(array('field_name' => 'userfrom', 'value' => (string) $user_from), array('field_name' => 'userto', 'value' => (string) $user_to)); foreach ($userids as $userid) { if (strlen(trim($userid['value'])) != 0) { if ($userid['value'] == 'all') { // All messages sent from/to users $sql .= ' AND ' . $userid['field_name'] . ' <> ' . MatrixDAL::quote('0') . "\n"; } else { if (strpos($userid['value'], ':') !== FALSE) { // Multiple userids found $ids = explode(':', $userid['value']); if (count($ids) >= 1) { $sql .= ' AND ('; foreach ($ids as $id) { if (strlen(trim($id)) == 0) { continue; } if (trim($id) == 'all') { usage(TRUE); } if (strpos($id, '*') !== FALSE && substr($id, -1) == '*') { $sql .= $userid['field_name'] . ' LIKE ' . MatrixDAL::quote(substr($id, 0, -1) . ':%') . ' OR '; } else { $sql .= $userid['field_name'] . ' = ' . MatrixDAL::quote($id) . ' OR '; } } $sql = substr($sql, 0, -4) . ')' . "\n"; } } else { // Single Userid found if (strpos($userid['value'], '*') !== FALSE && substr($userid['value'], -1) == '*') { $sql .= ' AND ' . $userid['field_name'] . ' LIKE ' . MatrixDAL::quote(substr($userid['value'], 0, -1) . ':%') . "\n"; } else { $sql .= ' AND ' . $userid['field_name'] . ' = ' . MatrixDAL::quote($userid['value']) . "\n"; } } } } } //end foreach userids // Type of message if (!empty($msg_type)) { if (strpos($msg_type, '*') !== FALSE && substr($msg_type, -1) == '*') { $sql .= ' AND type LIKE :msg_type' . "\n"; $bind_vars['msg_type'] = substr($msg_type, 0, -1) . '%'; } else { $sql .= ' AND type = :msg_type' . "\n"; $bind_vars['msg_type'] = $msg_type; } } // Message Status if (!empty($msg_status)) { if (strpos($msg_status, ':') !== FALSE) { $tmp = explode(':', $msg_status); $sql .= ' and status IN ('; foreach ($tmp as $token) { $sql .= MatrixDAL::quote($token) . ', '; } $sql = substr($sql, 0, -2) . ")\n"; } else { $sql .= ' AND status = :msg_status' . "\n"; $bind_vars['msg_status'] = $msg_status; } } if (!empty($assetids)) { $sql .= ' and assetid IN ('; foreach ($assetids as $_id => $assetid) { $sql .= MatrixDAL::quote($assetid) . ', '; } $sql = substr($sql, 0, -2) . ")\n"; } $query = MatrixDAL::preparePdoQuery($sql); foreach ($bind_vars as $bind_var => $bind_value) { MatrixDAL::bindValueToPdo($query, $bind_var, $bind_value); } MatrixDAL::execPdoQuery($query); $affected_rows = MatrixDAL::getDbType() == 'oci' ? oci_num_rows($query) : $query->rowCount(); if (!$QUIET) { echo "\n" . $affected_rows . ' INTERNAL MESSAGES ' . ($SHOW_QUERY_ONLY ? 'CAN BE ' : '') . 'DELETED' . "\n\n"; } if ($SHOW_QUERY_ONLY) { echo str_repeat('*', 50) . "\n"; echo '* Expected SQL query to run' . "\n"; echo str_repeat('*', 50) . "\n"; echo $sql; echo str_repeat('*', 50) . "\n"; } }
/** * Fixes the char encoding in the given tables in the database * * @param int $root_node Assetid of rootnode, all childern of rootnode will be processed for char replacement * @param array $tables DB tables and colunms info * @param boolean $rollback If TRUE process rollback tables, else process regular tables * * @return void */ function fix_db($root_node, $tables, $rollback) { global $reportOnly; $tables_info = get_tables_info(); // All the Matrix attribute types with serialised value $serialsed_attrs = array('option_list', 'email_format', 'parameter_map', 'serialise', 'http_request', 'oauth'); // Get the list of attrids of the type 'serialise' $sql = "SELECT attrid FROM sq_ast_attr WHERE type IN ('" . implode("','", $serialsed_attrs) . "')"; $serialise_attrids = array_keys(MatrixDAL::executeSqlGrouped($sql)); if ($root_node != 1) { // Get the targetted asset list $target_assetids = array_keys($GLOBALS['SQ_SYSTEM']->am->getChildren($root_node)); // Since we include the root node, target assetids will always contain atleast one asset id array_unshift($target_assetids, $root_node); echo "\n\nNumber of assets to look into : " . count($target_assetids) . " \n"; // Go through 50 assets at a time. Applicable to asset specific tables only $chunks = array_chunk($target_assetids, 50); $chunks_count = count($chunks); } $errors_count = 0; $warnings_count = 0; $records_fixed_count = 0; $invalid_asset_records = array(); // Assets that will require filesystem content regeneration $affected_assetids = array(); $GLOBALS['SQ_SYSTEM']->changeDatabaseConnection('db2'); // Counter to count the number of records accessed/processed $count = 0; foreach ($tables as $table_data) { $table_records_count = 0; $table = isset($table_data['table']) ? $table_data['table'] : ''; if (empty($table)) { continue; } $key_fields = isset($tables_info[$table]['primary_key']) ? $tables_info[$table]['primary_key'] : ''; if (empty($key_fields)) { echo "\n" . 'Ignoring table "' . $table . '". Table info for this table not found' . " \n"; continue; } $value_fields = isset($table_data['values']) ? $table_data['values'] : ''; if (empty($value_fields)) { // Nothing to check continue; } if ($rollback) { // Make sure table has rollback trigggers enabled, otherwise it will have rollback table if (isset($tables_info[$table]['rollback']) && $tables_info[$table]['rollback']) { // Add rollback table primary key field to the table's keys $key_fields[] = 'sq_eff_from'; } else { // This table does not has corresponding rollback table continue; } } // Prepend table prefix $table = !$rollback ? 'sq_' . $table : 'sq_rb_' . $table; $asste_specific_table = $table_data['asset_assoc']; $select_fields = array_merge($value_fields, $key_fields); if ($asste_specific_table && !in_array('assetid', $select_fields)) { $select_fields[] = 'assetid'; } if ($root_node == 1) { if ($asste_specific_table) { // When running system wide, get the asset list from the respective db table $sql = "SELECT DISTINCT assetid FROM " . $table; $target_assetids = array_keys(MatrixDAL::executeSqlGrouped($sql)); // Go through 50 assets at a time. Applicable to asset specific tables only $chunks = array_chunk($target_assetids, 50); } else { // Dummy assetids chuck just so that we can get into next loop $chunks = array(array()); } } echo "\nChecking " . $table . " ."; // For non-asset specific table, this loop will break at end of the very first iteration foreach ($chunks as $chunk_index => $assetids) { $sql = 'SELECT ' . implode(',', $select_fields) . ' FROM ' . $table; // For non-asset specific table, "where" condition not is required. We get the whole table in a single go if ($asste_specific_table) { $sql .= ' WHERE assetid IN (\'' . implode('\',\'', $assetids) . '\')'; } else { if ($table == 'sq_internal_msg') { // Special case for non-asset specific records for 'interal_msg' table // Internal message has 'assetid' field but messages not associated with the asset will have empty assetid $sql .= " WHERE assetid = '' OR assetid IS NULL"; } } $results = MatrixDAL::executeSqlAssoc($sql); foreach ($results as $record) { $table_records_count++; $count++; if ($count % 10000 == 0) { echo '.'; } // Asset ID associated with this record $assetid = $asste_specific_table ? $record['assetid'] : 'n/a'; // Key field data $key_values = array(); foreach ($key_fields as $key_field) { $temp_key_v = array_get_index($record, $key_field, NULL); if (is_null($temp_key_v)) { // Primary key field must be there continue 2; } $key_values[$key_field] = $temp_key_v; } //end foreach // Original value field data. // This is the one we need to check/fix $org_values = array(); foreach ($value_fields as $value_field) { $org_values[$value_field] = array_get_index($record, $value_field, ''); } //end foreach // If it's the same in the new and old encodings, that's good. foreach ($org_values as $value_field => $value) { $checked = @iconv(SYS_OLD_ENCODING, SYS_NEW_ENCODING . '//IGNORE', $value); if ($value === $checked) { // This field does not requires conversion/checking unset($org_values[$value_field]); } } //end foreach if (empty($org_values)) { // No field values to convert/check continue; } // Being here means this record contains invalid chars $invalid_asset_records[] = array('asset' => $assetid, 'table' => $table, 'keys' => $key_values, 'values' => $org_values); $converted_values = array(); foreach ($org_values as $value_field => $value) { // If not valid, convert the values without igonoring or interprating any chars if (!isValidValue($value)) { // Serialised fields needs to be handled here $serialised_value = FALSE; if ($table == 'sq_ast_attr_val' && $value_field == 'custom_val' && in_array($record['attrid'], $serialise_attrids)) { $serialised_value = TRUE; } if ($table == 'sq_trig' && $value_field == 'data') { $serialised_value = TRUE; } if ($serialised_value) { $us_value = @unserialize($value); if ($us_value === FALSE && serialize(FALSE) !== $value) { // This has invalid serialsed value, but fix it anyway $converted_value = @iconv(SYS_OLD_ENCODING, SYS_NEW_ENCODING . '//IGNORE', $value); // Put this error notice in the script log file $warnings_count++; $msg = 'Serialsed data field "' . $value_field . '" in the table "' . $table . '" ('; foreach ($key_values as $field_name => $value) { $msg .= $field_name . '=' . $value . '; '; } $msg = rtrim($msg, '; ') . ') does not contain unserialisable data. ' . ($reportOnly ? 'Data can still be converted.' : 'Data will be converted anyway.'); log_error_msg($msg); } else { if (is_array($us_value)) { array_walk_recursive($us_value, 'fix_char'); $converted_value = serialize($us_value); } else { if (is_scalar($us_value)) { $us_value = @iconv(SYS_OLD_ENCODING, SYS_NEW_ENCODING . '//IGNORE', $us_value); $converted_value = serialize($us_value); } else { $converted_value = $value; } } } } else { $converted_value = @iconv(SYS_OLD_ENCODING, SYS_NEW_ENCODING . '//IGNORE', $value); } // If the converted value is valid in current encoding then its good to go // otherwise we'll just not use this value if ($converted_value != $value && isValidValue($converted_value)) { $value = $converted_value; $converted_values[$value_field] = $value; } } else { // if it's a valid encoded value, but was convertable before with iconv using old encoding // it might be only because value is already properly encoded with new encoding. so use md_detect to double check $encoding = mb_detect_encoding($value); if (strtolower($encoding) === strtolower(SYS_NEW_ENCODING)) { unset($org_values[$value_field]); } } } //end foreach if (empty($org_values)) { // All good array_pop($invalid_asset_records); continue; } // If the successfully converted fields count is same as the invalid fields count, we can proceed with the update $update_required = count($org_values) == count($converted_values); if ($update_required) { if (!$reportOnly) { $GLOBALS['SQ_SYSTEM']->doTransaction('BEGIN'); // Generate update sql $bind_vars = array(); $set_sql = array(); foreach ($converted_values as $field_name => $value) { $set_sql[] = $field_name . '=:' . $field_name . '_v'; $bind_vars[$field_name . '_v'] = $value; } $where_sql = array(); foreach ($key_values as $field_name => $value) { $where_sql[] = $field_name . '=:' . $field_name . '_k'; $bind_vars[$field_name . '_k'] = $value; } try { $sql = 'UPDATE ' . $table . ' SET ' . implode(', ', $set_sql) . ' WHERE ' . implode(' AND ', $where_sql); $update_sql = MatrixDAL::preparePdoQuery($sql); foreach ($bind_vars as $var_name => $var_value) { MatrixDAL::bindValueToPdo($update_sql, $var_name, $var_value); } // Execute the update query $execute = MatrixDAL::executePdoAssoc($update_sql); if (count($execute) > 1) { foreach ($bind_vars as $var_name => $var_value) { $sql = str_replace(':' . $var_name, "'" . $var_value . "'", $sql); } $errors_count++; $msg = 'Executing query "' . $sql . '" will affect ' . count($execute) . ' records, instead of expected single record! Ignoring this sql.'; log_error_msg($msg); $GLOBALS['SQ_SYSTEM']->doTransaction('ROLLBACK'); } else { $GLOBALS['SQ_SYSTEM']->doTransaction('COMMIT'); $records_fixed_count++; $affected_assetids[$table][] = $assetid; } } catch (Exception $e) { $errors_count++; $msg = "Unexpected error occured while updating database: " . $e->getMessage(); log_error_msg($msg); $GLOBALS['SQ_SYSTEM']->doTransaction('ROLLBACK'); } } else { $records_fixed_count++; // For reporting purpose only $affected_assetids[$table][] = $assetid; } } else { // Trying to carryout charset conversion for this invalid value still resulted into invalid value // Hence record was not updated for this value conversion $errors_count++; $msg = 'Entry in the table "' . $table . '": ' . "\n"; foreach ($key_values as $field_name => $field_value) { $msg .= $field_name . '="' . $field_value . '"; '; } $msg .= "\n" . 'contains invalid char(s), which were not replaced because the charset conversion was not successful' . ($msg .= "\n" . 'Potentially invalid characters include:' . listProblematicCharacters($org_values)); log_error_msg($msg); } } //end foreach records if (!$asste_specific_table) { // We have processed all the entries for this non-asset specific table break; } } //end foreach assetids chunk echo " " . $table_records_count . " records"; } //end foreach tables $GLOBALS['SQ_SYSTEM']->restoreDatabaseConnection(); unset($target_assetids); unset($chunks); echo "\n"; $invalid_count = sizeof(array_keys($invalid_asset_records)); echo "Number of db records with invalid char(s): " . $invalid_count . "\n"; if ($invalid_count > 0) { foreach ($invalid_asset_records as $k => $details) { echo "\n\tAsset #" . $details['asset'] . " in table " . $details['table']; echo "\n\t" . 'Entry: '; foreach ($details['keys'] as $field_name => $field_value) { echo $field_name . '="' . $field_value . '"; '; } echo "\n\tPossibly problematic characters: " . listProblematicCharacters($details['values']) . "\n"; } echo "\n"; } return array('warning_count' => $warnings_count, 'error_count' => $errors_count, 'records_fixed_count' => $records_fixed_count, 'affected_assetids' => $affected_assetids); }