/**
* 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";
