/** * delete remaps * * @params $remaps array * * @return null */ function deleteRemaps($remaps) { $count = 0; // chunck to small batches, so oracle can accept the sql foreach (array_chunk($remaps, 100) as $chunk) { $GLOBALS['SQ_SYSTEM']->changeDatabaseConnection('db2'); $GLOBALS['SQ_SYSTEM']->doTransaction('BEGIN'); $urls = array(); foreach ($chunk as $data) { $urls[] = MatrixDAL::quote($data['url']); } $remaps_sql = implode(', ', $urls); $sql = 'DELETE FROM sq_ast_lookup_remap WHERE url in (' . $remaps_sql . ')'; try { $count += MatrixDAL::executeSql($sql); } catch (Exception $e) { throw new Exception('Unable to delete remaps due to database error: ' . $e->getMessage()); } //end try-catch $GLOBALS['SQ_SYSTEM']->doTransaction('COMMIT'); $GLOBALS['SQ_SYSTEM']->restoreDatabaseConnection(); } return $count; }
/** * Delete old shadow links from an array of linkids in a PHP file * * @param $file The PHP file that stores an array of shadow linkids * @return void */ function delete_old_shadow_links($file) { // Require the file to get the $linkids array require $file; // Check if $linkids array is defined if (!isset($linkids) || !is_array($linkids)) { echo "\nERROR: The file {$file} is not in a correct format.\n\n"; return; } // Split the array into chunks of 100 elements $chunks = array_chunk($linkids, 100); // Declare total deleted links variable $total_deleted_links = 0; // Delete the old shadow links $GLOBALS['SQ_SYSTEM']->changeDatabaseConnection('db2'); $GLOBALS['SQ_SYSTEM']->doTransaction('BEGIN'); try { foreach ($chunks as $chunk) { $delete_linkids = array(); foreach ($chunk as $linkid) { $delete_linkids[] = MatrixDAL::quote($linkid); } $sql = 'DELETE FROM sq_shdw_ast_lnk WHERE linkid IN (' . implode(',', $delete_linkids) . ')'; $deleted_rows = MatrixDAL::executeSql($sql); $total_deleted_links += $deleted_rows; } } catch (DALException $e) { $GLOBALS['SQ_SYSTEM']->doTransaction('ROLLBACK'); $GLOBALS['SQ_SYSTEM']->restoreDatabaseConnection(); throw new Exception('Can not delete old shadow links due to the DB error: ' . $e->getMessage()); } $GLOBALS['SQ_SYSTEM']->doTransaction('COMMIT'); $GLOBALS['SQ_SYSTEM']->restoreDatabaseConnection(); echo "\n{$total_deleted_links} old shadow link(s) from {$file} were deleted successfully.\n\n"; }
/** * Execute the specified SQL/commands on the database. * * @param string $sql The SQL/command to send to the database. * * @return mixed string or array of returned data, or false on failure */ public function execute($sql) { $output = false; // Check/execute macros foreach ($this->_macros[$this->_db_type] as $pattern => $replacement) { $c = 0; $sql = str_replace($pattern, $replacement, $sql, $c); if ($c > 0) { break; } } // Strip semicolon from end if its Oracle if ($this->_db_type == 'oci') { $sql = mb_substr($sql, 0, mb_strlen($sql) - 1); } // Check what kind of query it is $query_type = $this->_getQueryType($sql); switch ($query_type) { case "SELECT": $output = MatrixDAL::executeSqlAssoc($sql); break; case "UPDATE": case "INSERT": $rows_affected = MatrixDAL::executeSql($sql); $output = $query_type . " " . $rows_affected; break; case "BEGIN": /* There is no return bool code, but according to PHP docs an exception will be thrown if the DB doesn't support transactions */ MatrixDAL::beginTransaction(); $output = $query_type; break; case "ROLLBACK": MatrixDAL::rollBack(); $output = $query_type; break; case "COMMIT": MatrixDAL::commit(); $output = $query_type; break; default: //echo "WARNING: Query type not recognised.\n"; $output = MatrixDAL::executeSqlAssoc($sql); break; } return $output; }
SET custom_val = ' . MatrixDAL::quote($new_dn) . ' WHERE ' . (MatrixDAL::getDbType() === 'oci' ? 'DBMS_LOB.SUBSTR(custom_val, 2000, 1)' : 'custom_val') . ' = ' . MatrixDAL::quote($old_dn) . ' AND attrid IN (SELECT attrid FROM sq_ast_attr WHERE type=\'assetid\')'; $result = MatrixDAL::executeSql($sql); printActionStatus('OK'); printActionName('Changing asset roles'); $sql = 'UPDATE sq_ast_role SET userid = ' . MatrixDAL::quote($new_dn) . ' WHERE userid = ' . MatrixDAL::quote($old_dn); $result = MatrixDAL::executeSql($sql); printActionStatus('OK'); printActionName('Changing asset roles (rollback)'); $sql = 'UPDATE sq_rb_ast_role SET userid = ' . MatrixDAL::quote($new_dn) . ' WHERE userid = ' . MatrixDAL::quote($old_dn); $result = MatrixDAL::executeSql($sql); printActionStatus('OK'); $GLOBALS['SQ_SYSTEM']->doTransaction('COMMIT'); if (!empty($existing_links)) { echo "Few links in sq_shdw_ast_lnk table were already updated prior to running script.\n"; echo "Links for old DN under following parents need to be fixed manually\n"; foreach ($existing_links as $index => $link) { echo "{$index}) {$link}\n"; } } /** * Prints the name of the action currently being performed as a padded string * * Pads action to 40 columns * * @param string $str the action being performed
MatrixDAL::executeSql($sql); echo "\tUpdating metadata value table ...\n"; $sql = 'DELETE FROM sq_ast_mdata_val WHERE ' . $in_assetid; MatrixDAL::executeSql($sql); echo "\tUpdating workflow table...\n"; $sql = 'DELETE FROM sq_ast_wflow WHERE ' . $in_assetid; MatrixDAL::executeSql($sql); echo "\tUpdating permissions table...\n"; $sql = 'DELETE FROM sq_ast_perm WHERE ' . $in_assetid; MatrixDAL::executeSql($sql); echo "\tUpdating roles table...\n"; $sql = 'DELETE FROM sq_ast_role WHERE ' . $in_assetid; MatrixDAL::executeSql($sql); echo "\tUpdating shadow link table...\n"; $sql = 'DELETE FROM sq_shdw_ast_lnk WHERE ' . $in_majorid; MatrixDAL::executeSql($sql); $GLOBALS['SQ_SYSTEM']->doTransaction('COMMIT'); $GLOBALS['SQ_SYSTEM']->restoreDatabaseConnection(); echo "\tDeleting asset data directories...\n"; require_once SQ_FUDGE_PATH . '/general/file_system.inc'; foreach ($unquoted_assetids as $sub_assetid) { $data_path_suffix = asset_data_path_suffix('form_submission', $sub_assetid); $data_path = SQ_DATA_PATH . '/private/' . $data_path_suffix; $data_path_public = SQ_DATA_PATH . '/public/' . $data_path_suffix; if (is_dir($data_path)) { if (!delete_directory($data_path)) { trigger_error("Could not delete private data directory for Form Submission (Id: #{$assetid})", E_USER_WARNING); } } if (is_dir($data_path_public)) { if (!delete_directory($data_path_public)) {
/** * 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; } }
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();
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";