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";
} 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
}
//end foreach attrs chuck
/**
* Disconnects from the Oracle Matrix DB
*
* @return void
* @access private
*/
function _disconnectFromMatrixDatabase()
{
while (TRUE) {
try {
$conn_id = MatrixDAL::getCurrentDbId();
} catch (Exception $e) {
// run out of connections, that's it
break;
}
MatrixDAL::restoreDb();
MatrixDAL::dbClose($conn_id);
}
}
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
require_once $SYSTEM_ROOT . '/core/include/init.inc';
error_reporting(E_ALL);
if (ini_get('memory_limit') != '-1') {
ini_set('memory_limit', '-1');
}
$root_user =& $GLOBALS['SQ_SYSTEM']->am->getSystemAsset('root_user');
if (!$GLOBALS['SQ_SYSTEM']->setCurrentUser($root_user)) {
echo "ERROR: Failed logging in as root user\n";
exit;
}
//-- MAIN() --//
$script_start = time();
echo_headline(' GETTING ALL THE TRIGGERS INSTALLED ON THE SYSTEM');
// get trigger manager and all the triggers installed on the system
$tm =& $GLOBALS['SQ_SYSTEM']->am->getSystemAsset('trigger_manager');
$trigger_list = MatrixDAL::executeAll('core', 'getTriggerList');
foreach ($trigger_list as $index => $trigger_data) {
echo_headline(" REGENERATING TRIGGER " . $tm->id . ":" . $trigger_data['id']);
// load the trigger and regenerate it, same as clicking commit on the backend :)
$trigger = $tm->_loadTrigger($trigger_data['id']);
$result = $tm->_saveTrigger($trigger);
if (!$result) {
echo_headline(' ERROR OCCURED WHILE TRYING TO SAVE TRIGGER ' . $tm->id . ':' . $trigger_data['id']);
}
}
fwrite(STDERR, "\n");
echo_headline(' TREE ENTRIES CREATED');
$script_end = time();
$script_duration = $script_end - $script_start;
echo '-- Script Start : ', $script_start, ' Script End : ', $script_end, "\n";
echo '-- Script Duration: ' . floor($script_duration / 60) . ' mins ' . $script_duration % 60 . " seconds\n";
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)) {
/**
* 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;
}
foreach ($assets_id as $key => $value) {
$_tmp_assets_id = array_merge($_tmp_assets_id, array($key => $db->quote((string) $value)));
}
$asset_listing_ids = implode(',', $_tmp_assets_id);
} else {
$asset_listing_ids = "'" . $assets_id[0] . "'";
}
// get all the page_asset_listings with a custom attribute
$select = "select atv.assetid from sq_ast_attr at, sq_ast_attr_val atv where at.name like 'metadata_sort_type' and at.attrid = atv.attrid and atv.assetid in (" . $asset_listing_ids . ");";
$with_custom_attr_assets_id = array();
// PHP 5
if (version_compare(phpversion(), 5) >= 0) {
$result = NULL;
$query = $db->prepare($select);
try {
$result = MatrixDAL::executePdoAll($query);
} catch (Exception $e) {
throw new Exception('Could not get the ' . $e->getMessage());
}
//end
foreach ($result as $value) {
$with_custom_attr_assets_id = array_merge($with_custom_attr_assets_id, array($value['assetid']));
}
bam($result);
} else {
$result = $db->query($select);
assert_valid_db_result($result);
while (DB_OK === $result->fetchInto($row)) {
$with_custom_attr_assets_id = array_merge($with_custom_attr_assets_id, array($row['assetid']));
}
$result->free();
/**
* 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 update_form_submission_filepaths()
{
$root_user =& $GLOBALS['SQ_SYSTEM']->am->getSystemAsset('root_user');
$GLOBALS['SQ_SYSTEM']->setCurrentUser($root_user);
$GLOBALS['SQ_SYSTEM']->setRunLevel(SQ_RUN_LEVEL_FORCED);
// a quick query to find out all form submissions that needs update
$sql = "SELECT DISTINCT a.assetid FROM sq_ast a, sq_ast_attr_val v, sq_ast_attr t WHERE a.assetid = v.assetid AND t.attrid = v.attrid AND t.name = 'attributes' AND a.type_code = 'form_submission' AND v.custom_val like '%filesystem_path%' ";
$rows = MatrixDAL::executeSqlAll($sql);
$fields_to_check = array('temp_filesystem_path', 'filesystem_path');
foreach ($rows as $child_data) {
$child_id = $child_data['assetid'];
$asset = $GLOBALS['SQ_SYSTEM']->am->getAsset($child_id);
$data = $asset->attr('attributes');
if (isset($data['answers'])) {
foreach (array_keys($data['answers']) as $question_id) {
$extra_data = $asset->getExtraData($question_id);
$record_changed = FALSE;
foreach ($fields_to_check as $field) {
if (!empty($extra_data[$field])) {
$location = $extra_data[$field];
if (strpos($location, '/') === 0) {
if (strpos($location, SQ_SYSTEM_ROOT . '/') === 0) {
$location = str_replace(SQ_SYSTEM_ROOT . '/', '', $location);
$extra_data[$field] = $location;
$record_changed = TRUE;
} else {
echo "Absolute path found in form submission #{$child_id}, but can not be automatically converted. Location: {$location}\n";
}
}
}
}
if ($record_changed) {
if ($asset->setExtraData($question_id, $extra_data)) {
$asset->saveAttributes();
echo "Updated Form Submission ID: {$asset->id}\n";
} else {
echo "Failed to update Form Submission ID: {$asset->id}\n";
}
}
}
}
$GLOBALS['SQ_SYSTEM']->am->forgetAsset($asset, true);
unset($asset);
}
$GLOBALS['SQ_SYSTEM']->restoreRunLevel();
$GLOBALS['SQ_SYSTEM']->restoreCurrentUser();
}
/**
* Returns the table info of the rollback database tables
*
* @return array
* @access public
*/
function rollback_table_info()
{
$table_info = array();
$packages_installed = $GLOBALS['SQ_SYSTEM']->getInstalledPackages();
if (empty($packages_installed)) {
return array();
}
foreach ($packages_installed as $package_array) {
if ($package_array['code_name'] == '__core__') {
$table_file = SQ_CORE_PACKAGE_PATH . '/tables.xml';
} else {
$table_file = SQ_PACKAGES_PATH . '/' . $package_array['code_name'] . '/tables.xml';
}
if (!file_exists($table_file)) {
continue;
}
try {
$root = simplexml_load_string(file_get_contents($table_file), 'SimpleXMLElement', LIBXML_NOCDATA);
} catch (Exception $e) {
throw new Exception('Unable to parse table file : ' . $table_file . ' due to the following error: ' . $e->getMessage());
}
//end try catch
foreach ($root->children() as $child) {
$first_child_name = $child->getName();
break;
}
//end foreach
if ($root->getName() != 'schema' || $first_child_name != 'tables') {
trigger_error('Invalid table schema for file "' . $table_file . '"', E_USER_ERROR);
}
$table_root = $child;
foreach ($table_root->children() as $table_child) {
if ((string) $table_child->attributes()->require_rollback) {
$table_name = (string) $table_child->attributes()->name;
if (isset($table_child->keys) && count($table_child->keys->children()) > 0) {
foreach ($table_child->keys->children() as $table_key) {
$index_db_type = $table_key->attributes()->db;
if (!is_null($index_db_type) && (string) $index_db_type != MatrixDAL::getDbType()) {
continue;
}
// work out the columns in this key
$key_columns = array();
foreach ($table_key->column as $table_key_column) {
$col_name = (string) $table_key_column->attributes()->name;
$key_columns[] = $col_name;
// cache the primary key columns for this table
if ($table_key->getName() == 'primary_key') {
$table_info[$table_name]['primary_key'][] = $col_name;
}
if ($table_key->getName() == 'unique_key') {
$table_info[$table_name]['unique_key'][] = $col_name;
}
}
//end foreach
}
//end foreach
}
//end if
}
//end if
}
//end foreach
}
return $table_info;
}
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 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;
}
/**
* Get the type of database we dealing with
*
* @return string
* @access private
*/
private function _getDbType()
{
$dbtype = MatrixDAL::GetDbType();
if ($dbtype instanceof PDO) {
$dbtype = $dbtype->getAttribute(PDO::ATTR_DRIVER_NAME);
}
return strtolower($dbtype);
}
/**
* 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;
}
/**
* 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
}
/**
* 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";
}
}
/**
* 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;
}
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'];
}
/**
* 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;
}
/**
* 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();
}
$LIMIT_ROWS = 500;
// Last chance to stop from removing redundnet rollback entries
if ($DELETE_REDUNDANT_ENTRIES) {
echo "\nIMPORTANT: You have selected the option to remove all the redundant entries in the Rollback table.";
echo "\nThis will remove all the redundant entries for Cron Manager from the rollback tables.";
echo "\nAre you sure you want to proceed (Y/N)? ";
$choice = rtrim(fgets(STDIN, 4094));
if (strtolower($choice) != 'y') {
echo "\nScript aborted.\n";
exit;
}
echo "\n";
}
$GLOBALS['SQ_SYSTEM']->changeDatabaseConnection('db2');
$GLOBALS['SQ_SYSTEM']->doTransaction('BEGIN');
$db = MatrixDAL::getDb();
try {
if ($PURGE_FV_DATE) {
$affected_rows = purge_file_versioning($PURGE_FV_DATE);
if (!$QUIET) {
echo $affected_rows . ' FILE VERSIONING FILES AND ENTRIES DELETED' . "\n";
}
} else {
if ($RESET_ROLLBACK) {
// truncate toll back tables here then enable rollback
foreach ($tables as $table) {
truncate_rollback_entries($table);
if (!$QUIET) {
echo 'Rollback table sq_rb_' . $table . " truncated.\n";
}
}
/**
* Return old shadow links in the system
*
* @return array The array of old shadow links
*/
function get_old_shadow_links()
{
// Get all shadow links
$GLOBALS['SQ_SYSTEM']->changeDatabaseConnection('db');
$sql = 'SELECT linkid, link_type, majorid, minorid FROM sq_shdw_ast_lnk ORDER BY majorid, link_type, linkid';
$links = MatrixDAL::executeSqlAll($sql);
$GLOBALS['SQ_SYSTEM']->restoreDatabaseConnection();
// Search for old shadow links
$am = $GLOBALS['SQ_SYSTEM']->am;
$old_links = array();
foreach ($links as $link) {
$shadow_assetid = $link['minorid'];
$id_parts = explode(':', $shadow_assetid);
if (isset($id_parts[1])) {
$bridge_id = $id_parts[0];
$bridge = $am->getAsset($bridge_id, '', FALSE);
if (is_null($bridge) || !method_exists($bridge, 'getAsset')) {
$old_links[] = $link;
} else {
//getAsset() from bridge directly so we can set the fourth param $return_null to TRUE so we don't get dummy assets.
$shadow_asset = $bridge->getAsset($shadow_assetid, '', FALSE, TRUE);
if (is_null($shadow_asset)) {
$old_links[] = $link;
}
}
}
}
return $old_links;
}
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());
}
}
}
}
$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";
echo 'Are you sure you want to ' . ($DISALLOW_SETTING ? 'enable' : 'disable') . ' Disallow Password Login setting on these assets (Y/N)?';
/**
* 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;
}
/**
* 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;
}
trigger_error("You can only run this script from the command line\n", E_USER_ERROR);
}
$SYSTEM_ROOT = isset($_SERVER['argv'][1]) ? $_SERVER['argv'][1] : '';
if (empty($SYSTEM_ROOT)) {
echo "ERROR: You need to supply the path to the System Root as the first argument\n";
exit;
}
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';
require_once SQ_FUDGE_PATH . '/general/security.inc';
$algos = array('1', '2y', '6', '6o');
$sql = "SELECT v.custom_val FROM sq_ast_attr_val v INNER JOIN sq_ast_attr n ON n.attrid=v.attrid WHERE n.name='password' AND n.owning_type_code='user' AND v.contextid = 0";
$output = MatrixDAL::executeSqlAssoc($sql);
$results = array('1' => 0, '6o' => 0, '6' => 0, '2y' => 0);
foreach ($output as $o) {
$hash = $o['custom_val'];
if (strpos($hash, '$2y$') === 0) {
$results['2y']++;
} else {
if (strpos($hash, '$6$') === 0) {
$results['6']++;
} else {
if (strpos($hash, '$6o$') === 0) {
$results['6o']++;
} else {
$results['1']++;
}
}
/**
* 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;
}
}
/**
* 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);
}
