// Because DAL doesn't support row-by-row fetch, we'll do chunked fetch instead,
// 2000 at a time. If we don't get that many results, we'll do one last query
// in case anything had been added since - if we get zero results, we are done
$base_sql = 'SELECT l.majorid, l.linkid, l.minorid
FROM
sq_ast_lnk l
WHERE
' . db_extras_bitand(MatrixDAL::getDbType(), 'l.link_type', MatrixDAL::quote(SQ_SC_LINK_SIGNIFICANT)) . ' > 0
ORDER BY
l.sort_order, l.linkid, l.majorid, l.minorid';
$offset = 0;
$chunk_size = 2000;
$echo_i = 0;
$index = array();
while (TRUE) {
$sql = db_extras_modify_limit_clause($base_sql, MatrixDAL::getDbType(), $chunk_size, $offset);
$result = MatrixDAL::executeSqlAssoc($sql);
// If no further results, we're done.
if (count($result) == 0) {
break;
}
foreach ($result as $data) {
$majorid = $data['majorid'];
unset($data['majorid']);
if (!isset($index[$majorid])) {
$index[$majorid] = array();
}
$index[$majorid][] = $data;
$echo_i++;
if ($echo_i % 200 == 0) {
fwrite(STDERR, '.');
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());
}
}
}
/**
* 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;
}
MatrixDAL::execPdoQuery($query);
$sql = 'UPDATE sq_ast_lookup SET url = :to_url || SUBSTR(url, :from_url_length + 1) WHERE assetid IN (SELECT minorid FROM sq_ast_lnk WHERE linkid IN (SELECT linkid FROM sq_ast_lnk_tree t1 WHERE treeid LIKE (SELECT treeid || \'_%\' FROM sq_ast_lnk_tree t2 WHERE linkid IN (SELECT linkid FROM sq_ast_lnk WHERE minorid = :site_assetid) ' . $limit_clause . ')))
AND url LIKE :from_url || \'/__data/%\'';
$query = MatrixDAL::preparePdoQuery($sql);
MatrixDAL::bindValueToPdo($query, 'site_assetid', $from_site_assetid);
MatrixDAL::bindValueToPdo($query, 'from_url', $matching_from_roots[$x]);
MatrixDAL::bindValueToPdo($query, 'from_url_length', strlen($matching_from_roots[$x]));
MatrixDAL::bindValueToPdo($query, 'to_url', $matching_roots[$x]);
MatrixDAL::execPdoQuery($query);
}
// We have new URLs
// Going to pass on adding new lookups to this situation, because lookups are usually meant
// to be URL-based. How can we tell whether a lookup value is meant to be per-asset or per-URL?
for (; $x < count($matching_roots); $x++) {
$sql = 'INSERT INTO sq_ast_lookup (url, root_urlid, http, https, assetid) SELECT DISTINCT :to_url || SUBSTR(url, STRPOS(url, \'/__data/\')), 0, http, https, assetid FROM sq_ast_lookup WHERE url LIKE \'%/__data/%\' AND assetid IN (SELECT minorid FROM sq_ast_lnk WHERE linkid IN (SELECT linkid FROM sq_ast_lnk_tree t1 WHERE treeid LIKE (SELECT treeid || \'_%\' FROM sq_ast_lnk_tree t2 WHERE linkid IN (SELECT linkid FROM sq_ast_lnk WHERE minorid = :site_assetid) ' . $limit_clause . ')))';
if (MatrixDAL::getDbType() == 'oci') {
// String position function is called INSTR() in Oracle
$sql = str_replace('STRPOS(', 'INSTR(', $sql);
}
$query = MatrixDAL::preparePdoQuery($sql);
MatrixDAL::bindValueToPdo($query, 'site_assetid', $from_site_assetid);
MatrixDAL::bindValueToPdo($query, 'to_url', $matching_roots[$x]);
MatrixDAL::execPdoQuery($query);
}
//end for - remaining to URLs
// More URLs beforehand than what we have now = have to delete the rest
for (; $x < count($matching_from_roots); $x++) {
// Delete the lookup values first
$sql = 'DELETE FROM sq_ast_lookup_value WHERE url IN (SELECT url FROM sq_ast_lookup WHERE assetid IN (SELECT minorid FROM sq_ast_lnk WHERE linkid IN (SELECT linkid FROM sq_ast_lnk_tree t1 WHERE treeid LIKE (SELECT treeid || \'_%\' FROM sq_ast_lnk_tree t2 WHERE linkid IN (SELECT linkid FROM sq_ast_lnk WHERE minorid = :site_assetid) ' . $limit_clause . ')))
AND url LIKE :from_url || \'/__data/%\')';
$query = MatrixDAL::preparePdoQuery($sql);
/**
* 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";
}
}
}
require_once SQ_FUDGE_PATH . '/db_extras/db_extras.inc';
$GLOBALS['SQ_SYSTEM']->changeDatabaseConnection('db2');
$GLOBALS['SQ_SYSTEM']->doTransaction('BEGIN');
$db = $GLOBALS['SQ_SYSTEM']->db;
$sql = 'SELECT
a.assetid
FROM
(' . SQ_TABLE_RUNNING_PREFIX . 'ast a
JOIN ' . SQ_TABLE_RUNNING_PREFIX . 'ast_typ_inhd i ON a.type_code = i.type_code)
JOIN ' . SQ_TABLE_RUNNING_PREFIX . 'ast_lnk l
ON l.minorid = a.assetid';
$where = 'l.majorid IN (:assetid, :subfolder_assetid)
AND i.inhd_type_code = :inhd_type_code
AND a.created BETWEEN ' . db_extras_todate(MatrixDAL::getDbType(), ':created_from', FALSE) . '
AND ' . db_extras_todate(MatrixDAL::getDbType(), ':created_to', FALSE);
$where = $GLOBALS['SQ_SYSTEM']->constructRollbackWhereClause($where, 'a');
$where = $GLOBALS['SQ_SYSTEM']->constructRollbackWhereClause($where, 'l');
$sql = $sql . $where . ' ORDER BY a.created DESC';
$query = MatrixDAL::preparePdoQuery($sql);
MatrixDAL::bindValueToPdo($query, 'assetid', $asset->id);
MatrixDAL::bindValueToPdo($query, 'subfolder_assetid', $sub_folder->id);
MatrixDAL::bindValueToPdo($query, 'inhd_type_code', 'form_submission');
MatrixDAL::bindValueToPdo($query, 'created_from', $from_value);
MatrixDAL::bindValueToPdo($query, 'created_to', $to_value);
$assetids = MatrixDAL::executePdoAssoc($query, 0);
if (empty($assetids)) {
echo "No form submission found for '{$asset->name}' (#{$assetid}) within the specified date range\n";
exit;
}
echo 'Found ' . count($assetids) . " form submission(s) for '{$asset->name}' (#{$assetid})\n(Date range: {$from_value} to {$to_value})\n";
$sql = 'UPDATE sq_lock
SET lockid = ' . MatrixDAL::quote('asset.' . $new_dn) . '
WHERE lockid = ' . MatrixDAL::quote('asset.' . $old_dn);
$result = MatrixDAL::executeSql($sql);
$sql = 'UPDATE sq_lock
SET source_lockid = ' . MatrixDAL::quote('asset.' . $new_dn) . '
WHERE source_lockid = ' . MatrixDAL::quote('asset.' . $old_dn);
$result = MatrixDAL::executeSql($sql);
printActionStatus('OK');
// update the sq_ast_attr val table for all the attributes value of type "assetid", coz if the old dn is not going to be available
// henceforth then there is no point keeping it in our database
// see #5608 ldap_change_dn.php does not update running_as attribute for cron jobs
printActionName('Changing asset attributes ');
$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');
/**
* 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;
}
/**
* 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;
}
}
ini_set('memory_limit', '-1');
}
$toFix = getCLIArg('fix');
$toEmail = getCLIArg('email');
// asset is Live, Safe Editting, Approved To Go Live, Under Construction, Safe Edit Approved to Go Live but it has running workflow by mistake.
$where = MatrixDAL::getDbType() === 'oci' ? ' AND DBMS_LOB.GETLENGTH(w.wflow) > 0' : ' AND w.wflow IS NOT NULL';
$sql = 'SELECT a.assetid, a.status FROM sq_ast_wflow w, sq_ast a WHERE a.assetid = w.assetid AND a.status IN (2, 8, 16, 64, 256)' . $where;
try {
$query = MatrixDAL::preparePdoQuery($sql);
$assets_should_not_have_workflow = MatrixDAL::executePdoAssoc($query);
} catch (Exception $e) {
echo "ERROR: " . $e->getMessage() . "\n";
exit(1);
}
// asset is Pending Approval but it does't have running workflow by mistake.
$where = MatrixDAL::getDbType() === 'oci' ? ' AND (w.wflow IS NULL OR DBMS_LOB.GETLENGTH(w.wflow) = 0)' : ' AND w.wflow IS NULL';
$sql = 'SELECT a.assetid, a.status FROM sq_ast a LEFT JOIN sq_ast_wflow w ON a.assetid = w.assetid WHERE a.status IN (4)' . $where;
try {
$query = MatrixDAL::preparePdoQuery($sql);
$assets_should_have_workflow = MatrixDAL::executePdoAssoc($query);
} catch (Exception $e) {
echo "ERROR: " . $e->getMessage() . "\n";
exit(1);
}
// nothing to do
if (empty($assets_should_have_workflow) && empty($assets_should_not_have_workflow)) {
exit;
}
$workflows_to_update = array();
$assets_to_update = array();
ob_start();
/**
* Update the sq_file_vers_history table in database. Set to_time for old record and add new record
*
* @param File_Versioning $file_versioning the File_Versioning object
* @param string $fileid the fileid of the file to be updated
* @param string $real_file the real file path of the file
* @param int $version the new version of the file
* @param string $extra_info
* @return boolean return TRUE if success; otherwise, return FALSE
* @see _updateFile() in file_versioning.inc
*/
function _updateFileVersionHistory($file_versioning, $fileid, $real_file, $version, $extra_info = '')
{
$GLOBALS['SQ_SYSTEM']->changeDatabaseConnection('db2');
$GLOBALS['SQ_SYSTEM']->doTransaction('BEGIN');
try {
//If this version is already in version history table, but the to_date is set, report it.
//This case is not likely to happen, just to make sure there is no duplicate version is set
$db_version_info = $file_versioning->_getFileInfoAtVersion($fileid, $version);
if (!empty($db_version_info)) {
echo "ERROR: THIS FILE (FILEID = {$fileid}, VERSION = {$version}) IS NO LONGER USED SINCE {$db_version_info['to_date']}\n";
return FALSE;
}
$now = time();
$date = ts_iso8601($now);
/*if (MatrixDAL::getDbType() == 'oci') {
$date = db_extras_todate(MatrixDAL::getDbType(), $date);
}*/
$sql = 'UPDATE sq_file_vers_history
SET to_date = :to_date
WHERE fileid = :fileid
AND to_date IS NULL';
try {
if (MatrixDAL::getDbType() == 'oci') {
$sql = str_replace(':to_date', db_extras_todate(MatrixDAL::getDbType(), ':to_date', FALSE), $sql);
}
$query = MatrixDAL::preparePdoQuery($sql);
MatrixDAL::bindValueToPdo($query, 'fileid', $fileid);
MatrixDAL::bindValueToPdo($query, 'to_date', $date);
MatrixDAL::execPdoQuery($query);
} catch (Exception $e) {
throw new Exception('Unable to update version history for file ID ' . $fileid . ' due to database error: ' . $e->getMessage());
}
if (file_exists($real_file)) {
$file_size = filesize($real_file);
$md5 = md5_file($real_file);
$sha1 = sha1_file($real_file);
$removal = '0';
} else {
$file_size = 0;
$md5 = '';
$sha1 = '';
$removal = '1';
}
$sql = 'INSERT INTO sq_file_vers_history
(fileid, version, from_date, to_date, file_size, md5, sha1, removal, extra_info)
VALUES
(:fileid, :version, :from_date, :to_date, :file_size, :md5, :sha1, :removal, :extra_info)';
try {
if (MatrixDAL::getDbType() == 'oci') {
$sql = str_replace(':from_date', db_extras_todate(MatrixDAL::getDbType(), ':from_date', FALSE), $sql);
}
$query = MatrixDAL::preparePdoQuery($sql);
MatrixDAL::bindValueToPdo($query, 'fileid', $fileid);
MatrixDAL::bindValueToPdo($query, 'version', $version);
MatrixDAL::bindValueToPdo($query, 'from_date', $date);
MatrixDAL::bindValueToPdo($query, 'to_date', NULL);
MatrixDAL::bindValueToPdo($query, 'file_size', $file_size);
MatrixDAL::bindValueToPdo($query, 'md5', $md5);
MatrixDAL::bindValueToPdo($query, 'sha1', $sha1);
MatrixDAL::bindValueToPdo($query, 'removal', $removal);
MatrixDAL::bindValueToPdo($query, 'extra_info', $extra_info);
MatrixDAL::execPdoQuery($query);
} catch (Exception $e) {
throw new Exception('Unable to insert version history for file ID ' . $fileid . ' due to database error: ' . $e->getMessage());
}
$GLOBALS['SQ_SYSTEM']->doTransaction('COMMIT');
$GLOBALS['SQ_SYSTEM']->restoreDatabaseConnection();
} catch (Exception $e) {
echo "ERROR: " . $e->getMessage() . "\n";
$GLOBALS['SQ_SYSTEM']->doTransaction('ROLLBACK');
$GLOBALS['SQ_SYSTEM']->restoreDatabaseConnection();
return FALSE;
}
return TRUE;
}
<?php
$root_dir = dirname(dirname(dirname(dirname(__FILE__))));
require_once $root_dir . '/core/include/init.inc';
$GLOBALS['SQ_SYSTEM']->changeDatabaseConnection('db2');
$dbType = MatrixDAL::getDbType();
if ($dbType == 'pgsql') {
// Postgres.
// Remove old indexes and keys.
MatrixDAL::executeSql('ALTER TABLE ONLY sq_suite_product DROP CONSTRAINT suite_product_pk');
MatrixDAL::executeSql('DROP INDEX sq_suite_product_type');
// Create a new sequence.
// SELECT FROM information_schema.sequences won't work with postgres 8.1, so this is the only way to check
try {
$sequence = MatrixDAL::executeSqlAssoc("SELECT * from sq_suite_seq");
} catch (Exception $e) {
MatrixDAL::executeSql('CREATE SEQUENCE sq_suite_seq INCREMENT BY 1');
}
// Add new columns.
MatrixDAL::executeSql('ALTER TABLE sq_suite_product ADD COLUMN suiteid INTEGER');
MatrixDAL::executeSql('ALTER TABLE sq_suite_product ADD COLUMN url VARCHAR(2000)');
MatrixDAL::executeSql('ALTER TABLE sq_suite_product ADD COLUMN token VARCHAR(30)');
// Remove unused column.
MatrixDAL::executeSql('ALTER TABLE sq_suite_product DROP COLUMN knows_me_as');
// Populate data into not null columns.
MatrixDAL::executeSql("UPDATE sq_suite_product SET suiteid=nextval('sq_suite_seq')");
MatrixDAL::executeSql("UPDATE sq_suite_product SET url=''");
$products = MatrixDAL::executeSqlAssoc('SELECT suiteid, connection FROM sq_suite_product');
foreach ($products as $product) {
$suiteid = array_get_index($product, 'suiteid', NULL);
$connection = array_get_index($product, 'connection', NULL);
