/**
* Get a list of the available columns on the specified table. Used for
* autocomplete.
*
* @param string $table Name of the table
*
* @return array List of column names
*/
public function getColumnNames($table)
{
$sql = '';
switch ($this->_db_type) {
case 'oci':
// Cheeky UNION here to allow tab completion to work for both all-upper OR
// all-lowercase table names (only for MatrixDAL/oci, so users can be lazy)
$sql = "SELECT column_name FROM all_tab_columns WHERE table_name = " . mb_strtoupper(MatrixDAL::quote($table)) . " UNION " . "SELECT LOWER(column_name) FROM all_tab_columns WHERE table_name = " . mb_strtoupper(MatrixDAL::quote($table));
break;
case 'pgsql':
$sql = <<<EOF
\t\t\t\t\t-- phpsqlc: tab-completion: column-names
\t\t\t\t\tSELECT a.attname FROM pg_catalog.pg_attribute a
\t\t\t\t\tWHERE a.attrelid IN (
\t\t\t\t\t SELECT c.oid
\t\t\t\t\t FROM pg_catalog.pg_class c
\t\t\t\t\t LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
\t\t\t\t\t WHERE c.relname = '{$table}' AND pg_catalog.pg_table_is_visible(c.oid)
\t\t\t\t\t) AND a.attnum > 0 AND NOT a.attisdropped;
EOF;
}
// We only know queries for pgsql and oci
if ($sql === '') {
return array();
}
try {
$names = MatrixDAL::executeSqlAssoc($sql, 0);
} catch (Exception $e) {
$names = array();
}
return $names;
}
exit(1);
}
$user_assetids = $GLOBALS['SQ_SYSTEM']->am->getChildren($ROOT_ASSETID, 'user', FALSE);
// if we are only interested in SAML/Oauth linked accounts, make sure we filter those don't
if ($LINKED_ONLY) {
foreach ($user_assetids as $id => $details) {
// filter SAML
$not_linked_saml = FALSE;
$sql = 'select count(*) from sq_saml_lnk where assetid = ' . MatrixDAL::quote($id);
$result = MatrixDAL::executeSqlAssoc($sql);
if (isset($result[0]['count']) && empty($result[0]['count'])) {
$not_linked_saml = TRUE;
}
// filter Oauth
$not_linked_oauth = FALSE;
$sql = 'select count(*) from sq_oauth_lnk where matrix_userid = ' . MatrixDAL::quote($id);
$result = MatrixDAL::executeSqlAssoc($sql);
if (isset($result[0]['count']) && empty($result[0]['count'])) {
$not_linked_oauth = TRUE;
}
if ($not_linked_saml && $not_linked_oauth) {
unset($user_assetids[$id]);
}
}
}
// add root node itself in if possible
$root_node_user = $GLOBALS['SQ_SYSTEM']->am->getAsset($ROOT_ASSETID);
if ($root_node_user instanceof User) {
$user_assetids[$ROOT_ASSETID] = array();
}
echo 'Found ' . count($user_assetids) . ' User asset(s) underneath asset ID #' . $ROOT_ASSETID . "\n";
/**
* 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;
}
exit;
}
// log in as root
$root_user = $GLOBALS['SQ_SYSTEM']->am->getSystemAsset('root_user');
if (!$GLOBALS['SQ_SYSTEM']->setCurrentUser($root_user)) {
echo "ERROR: Failed login in as root user\n";
exit;
}
// make some noiz
$db = $GLOBALS['SQ_SYSTEM']->db;
$sql = 'SELECT assetid FROM sq_ast WHERE type_code = :type_code';
$query = MatrixDAL::preparePdoQuery($sql);
MatrixDAL::bindValueToPdo($query, 'type_code', $DELETING_ASSET_TYPE);
$assets_of_type = MatrixDAL::executePdoAssoc($query, 0);
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';
/**
* Create SQL statements to delete old shadow links and store them in a file.
*
* @param array $links An array of old shadow links
* @param string $file The file to store SQL statements
* @return void
*/
function save_sql_statements($links, $file)
{
// Create a big SQL string with comments in it
$sql = '';
foreach ($links as $link) {
$sql .= 'DELETE FROM sq_shdw_ast_lnk WHERE linkid=' . MatrixDAL::quote($link['linkid']) . '; -- link_type=' . $link['link_type'] . ', majorid=' . $link['majorid'] . ', minorid=' . $link['minorid'] . "\n";
}
// Save the SQL string to file
if (file_put_contents($file, $sql) === FALSE) {
echo "\nERROR: Can not write SQL statements to {$file}\n\n";
} else {
echo "\nThe SQL statements to delete the old shadow links are saved to {$file}\n\n";
}
}
$assets_of_type[$index] = MatrixDAL::quote($asset_id);
}
$asset_ids_set = '(' . implode(', ', $assets_of_type) . ')';
$sql = 'SELECT linkid FROM sq_ast_lnk WHERE minorid in ' . $asset_ids_set . ' OR majorid in ' . $asset_ids_set;
$query = MatrixDAL::preparePdoQuery($sql);
// bugfix #3820
$res = MatrixDAL::executePdoAssoc($query);
// bugfix #3849 Script remove_assets_of_type.php leaves unwanted table entries
// while deleting the link make sure we call asset_manager function to do the same
// we should not be just deleting the links and treeids from the table as it can
// lead to unexpected behaviour. for more information see the Bug report
if (!empty($res)) {
$links_array = array();
foreach ($res as $value) {
$GLOBALS['SQ_SYSTEM']->am->deleteAssetLink($value['linkid']);
array_push($links_array, MatrixDAL::quote($value['linkid']));
}
$links_set = '(' . implode(', ', $links_array) . ')';
$sql = 'DELETE FROM sq_ast_lnk WHERE linkid in ' . $links_set;
$query = MatrixDAL::preparePdoQuery($sql);
MatrixDAL::execPdoQuery($query);
}
// we need to delete asset from sq_ast table after deleteAssetLink() call
// or else then function call will throw errors.
$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 WHERE type_code = :type_code';
$query = MatrixDAL::preparePdoQuery($sql);
MatrixDAL::bindValueToPdo($query, 'type_code', $DELETING_ASSET_TYPE);
MatrixDAL::execPdoQuery($query);
/**
* 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";
}
}
}
$workflows_to_update = array();
$assets_to_update = array();
ob_start();
if (!empty($assets_should_not_have_workflow)) {
echo "\nFollowing assets are found to be in running workflow state but not in workflow status\n";
foreach ($assets_should_not_have_workflow as $data) {
echo "#" . $data['assetid'] . " has status " . $data['status'] . "\n";
$workflows_to_update[$data['assetid']] = MatrixDAL::quote($data['assetid']);
}
}
if (!empty($assets_should_have_workflow)) {
echo "\nFollowing assets are found to be in workflow status but not in running workflow state\n";
foreach ($assets_should_have_workflow as $data) {
echo "#" . $data['assetid'] . " has status " . $data['status'] . "\n";
$assets_to_update[$data['assetid']] = MatrixDAL::quote($data['assetid']);
}
}
$content = ob_get_contents();
ob_end_clean();
if ($toFix) {
$GLOBALS['SQ_SYSTEM']->changeDatabaseConnection('db2');
$GLOBALS['SQ_SYSTEM']->doTransaction('BEGIN');
if (!empty($workflows_to_update)) {
// Break up the assets into chunks of 1000 so that oracle does not complain
$in_clauses = array();
foreach (array_chunk($workflows_to_update, 999) as $chunk) {
$in_clauses[] = ' assetid IN (' . implode(', ', $chunk) . ')';
}
$where = '(' . implode(' OR ', $in_clauses) . ')';
$sql = 'UPDATE sq_ast_wflow SET wflow = null WHERE ' . $where;
$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) . ')';
}
$in_assetid = '(' . implode(' OR ', $assetid_in) . ')';
$assetid_in = array();
foreach (array_chunk($assetids, 999) as $chunk) {
$assetid_in[] = ' minorid IN (' . implode(', ', $chunk) . ')';
}
$in_minorid = '(' . implode(' OR ', $assetid_in) . ')';
$assetid_in = array();
foreach (array_chunk($assetids, 999) as $chunk) {
$assetid_in[] = ' majorid IN (' . implode(', ', $chunk) . ')';
$sql = 'UPDATE sq_ast_attr_val
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
*
num_kids = num_kids - 1
WHERE
treeid in (' . $sub_sql . ')';
$query = MatrixDAL::preparePdoQuery($sql);
MatrixDAL::bindValueToPdo($query, 'linkid', $link['linkid']);
MatrixDAL::execPdoQuery($query);
// we can delete all the links under these nodes because it will be a clean start
// when we insert into the gap's we create below
$sub_sql = 'SELECT
ct.treeid
FROM
sq_ast_lnk_tree pt,
sq_ast_lnk_tree ct
WHERE
pt.linkid = :linkid
AND ct.treeid LIKE pt.treeid || ' . MatrixDAL::quote('%') . '
AND ct.treeid > pt.treeid';
$sql = 'DELETE FROM
sq_ast_lnk_tree
WHERE
treeid in (' . $sub_sql . ')';
$query = MatrixDAL::preparePdoQuery($sql);
MatrixDAL::bindValueToPdo($query, 'linkid', $link['linkid']);
MatrixDAL::execPdoQuery($query);
// we are going to set the treeid nodes that this link is associated
// with to zero so that we can find it as a gap when we createLink() later on
$sql = 'UPDATE
sq_ast_lnk_tree
SET
linkid = :linkid,
num_kids = :num_kids
if (!empty($ERRORS)) {
break 2;
}
}
}
//end foreach assetids chunk
}
//end foreach records
}
//end foreach tables
// Ok this bit is not quite related to rest of the script
// Get the all notice links and check if minor assetid is outside the site to keeep
$sql = 'SELECT majorid, minorid, value
FROM sq_ast_lnk
WHERE
link_type = ' . MatrixDAL::quote(SQ_LINK_NOTICE);
$results = MatrixDAL::executeSqlAssoc($sql);
foreach ($results as $row) {
$majorid = $row['majorid'];
$minorid = $row['minorid'];
$link_value = $row['value'];
// Report major ids that are in 'site to keep' but whose minor id is not
if (isset($KEEP_ASSETIDS[$majorid]) && !isset($KEEP_ASSETIDS[$minorid])) {
if (isset($DELETE_ASSETIDS[$minorid])) {
$REPORT['notice_links'][] = $majorid . ',' . $minorid . ',' . $link_value . ',1';
} else {
$REPORT['notice_links'][] = $majorid . ',' . $minorid . ',' . $link_value . ',0';
}
}
}
//end foreach
/**
* 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;
}
}
/**
* 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;
}
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());
}
}
}
/**
* Re-creates the link tree table starting from th
*
* @param string $majorid the ID of the asset whose children to recreate
* @param string $path the path so far
*
* @return void
* @access public
*/
function recurse_tree_create($majorid, $path)
{
global $db, $index, $echo_i, $pgdb, $no_ddl;
foreach ($index[$majorid] as $i => $data) {
$treeid = $path . asset_link_treeid_convert($i, true);
$num_kids = empty($index[$data['minorid']]) ? 0 : count($index[$data['minorid']]);
if ($pgdb && !$no_ddl) {
$sql = $treeid . "\t" . (string) $data['linkid'] . "\t" . (string) $num_kids;
} else {
$sql = 'INSERT INTO sq_ast_lnk_tree (treeid, linkid, num_kids) VALUES (' . MatrixDAL::quote($treeid) . ',' . MatrixDAL::quote((int) $data['linkid']) . ',' . MatrixDAL::quote($num_kids) . ');';
}
echo $sql, "\n";
$echo_i++;
if ($echo_i % 200 == 0) {
fwrite(STDERR, '.');
}
if ($num_kids) {
recurse_tree_create($data['minorid'], $treeid);
}
}
//end foreach
}
$exclude_user_string = getCLIArg('excludeuser');
$exclude_user_sql = '';
if (!empty($exclude_user_string)) {
$exclude_users = explode(',', $exclude_user_string);
foreach ($exclude_users as $key => $value) {
$exclude_users[$key] = MatrixDAL::quote($exclude_users[$key]);
}
$exclude_user_sql = ' AND userid NOT IN (' . implode(', ', $exclude_users) . ')';
}
// permission type condition
$type_string = getCLIArg('type');
$type_sql = '';
if (!empty($type_string)) {
$types = explode(',', $type_string);
foreach ($types as $key => $value) {
$types[$key] = MatrixDAL::quote($types[$key]);
}
$type_sql = ' AND permission IN (' . implode(', ', $types) . ')';
}
$sql = $sql . $in_clause_sql . $include_user_sql . $exclude_user_sql . $type_sql;
// confirmation to proceed
$message = "This script is about to remove ALL permissions for assets under root node:" . $root_nodes_string;
if (!empty($include_user_string)) {
$message .= " assigned to user:"******" excluding user:"******" with permission type:" . $type_string;
}