function remove_hipo_jobs() { $GLOBALS['SQ_SYSTEM']->changeDatabaseConnection('db2'); $GLOBALS['SQ_SYSTEM']->doTransaction('BEGIN'); $sql = 'DELETE FROM sq_hipo_job'; $query = MatrixDAL::preparePdoQuery($sql); $result = MatrixDAL::execPdoQuery($query); $GLOBALS['SQ_SYSTEM']->doTransaction('COMMIT'); $GLOBALS['SQ_SYSTEM']->restoreDatabaseConnection(); if ($result) { echo "Removed unfinished HIPO jobs.\n"; } }
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 *
/** * 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(); }
/** * 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; }
/** * 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"; } }
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()); } } }
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
$query = MatrixDAL::preparePdoQuery($sql); $result = MatrixDAL::execPdoQuery($query); if ($result) { echo "\n" . $result . " asset(s) fixed by removing running workflow state.\n"; } } if (!empty($assets_to_update)) { // Break up the assets into chunks of 1000 so that oracle does not complain $in_clauses = array(); foreach (array_chunk($assets_to_update, 999) as $chunk) { $in_clauses[] = ' assetid IN (' . implode(', ', $chunk) . ')'; } $where = '(' . implode(' OR ', $in_clauses) . ')'; $sql = 'UPDATE sq_ast SET status = 2 WHERE ' . $where; $query = MatrixDAL::preparePdoQuery($sql); $result = MatrixDAL::execPdoQuery($query); if ($result) { echo "\n" . $result . " asset(s) fixed by setting status to Under Construction.\n"; } } $GLOBALS['SQ_SYSTEM']->doTransaction('COMMIT'); $GLOBALS['SQ_SYSTEM']->restoreDatabaseConnection(); // Also flush the Deja Vu cache for the updated assets $deja_vu = $GLOBALS['SQ_SYSTEM']->getDejaVu(); if ($deja_vu->enabled()) { $assets_to_update = array_keys($assets_to_update); foreach ($assets_to_update as $assetid) { $deja_vu->forget('asset', $assetid); } //end foreach }
/** * Insert a file record info to sq_file_vers_file table if one does not exist * * @param File_Versioning $file_versioning the File_Versioning object * @param string $fileid the fileid of the file to be inserted * @param string $rep_file the repository file path of the file * @see add() in file_versioning.inc */ function _insertFileVersFileInfo($file_versioning, $fileid, $rep_file) { $sql = 'SELECT COUNT(*) FROM sq_file_vers_file WHERE fileid = :fileid'; $fileid_count = 0; try { $query = MatrixDAL::preparePdoQuery($sql); MatrixDAL::bindValueToPdo($query, 'fileid', $fileid); $fileid_count = MatrixDAL::executePdoOne($query); } catch (Exception $e) { echo "ERROR: " . $e->getMessage() . "\n"; return; } //the record info already exists, return without doing anything if ($fileid_count != 0) { return; } $GLOBALS['SQ_SYSTEM']->changeDatabaseConnection('db2'); $GLOBALS['SQ_SYSTEM']->doTransaction('BEGIN'); $sql = 'INSERT INTO sq_file_vers_file (fileid, path, filename) VALUES (:fileid, :path, :filename)'; try { $query = MatrixDAL::preparePdoQuery($sql); MatrixDAL::bindValueToPdo($query, 'fileid', $fileid); MatrixDAL::bindValueToPdo($query, 'path', dirname($rep_file)); MatrixDAL::bindValueToPdo($query, 'filename', basename($rep_file)); MatrixDAL::execPdoQuery($query); $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(); } }
} } /** * bytea fields from postgres are returned as resources * Convert them from resources into actual text content * * See http://www.php.net/manual/en/pdo.lobs.php */ if (is_resource($data_value)) { $stream = $data_value; $data_value = stream_get_contents($stream); fclose($stream); } MatrixDAL::bindValueToPdo($prepared_sql, $data_key, $data_value); } MatrixDAL::execPdoQuery($prepared_sql); $trans_count++; if ($trans_count % 10000 == 0) { MatrixDAL::commit(); MatrixDAL::beginTransaction(); $trans_count = 0; } } printName('Inserting Data (' . number_format($start) . ' - ' . number_format($start + $count) . ' rows)'); printUpdateStatus('OK', "\r"); MatrixDAL::commit(); /** * Switch to the source db connector to get the data.. */ MatrixDAL::changeDb($source_db); /**
$pg_version_query = 'SELECT version FROM version()'; $pg_vrs_qry = MatrixDAL::preparePdoQuery($pg_version_query); $pg_vrs_res = MatrixDAL::executePdoOne($pg_vrs_qry); $pg_vrs_matches = array(); preg_match('/[0-9\\.]+/', $pg_vrs_res, $pg_vrs_matches); if (version_compare($pg_vrs_matches[0], '9.0.0') >= 0) { $pg9_replication_slave_query = 'SELECT pg_is_in_recovery()'; $pg9_qry = MatrixDAL::preparePdoQuery($pg9_replication_slave_query); $pg9_is_slave = MatrixDAL::executePdoOne($pg9_qry); if ($pg9_is_slave) { $db_is_slave = TRUE; } } } if (!$db_is_slave) { $res = MatrixDAL::execPdoQuery($qry); } } } catch (Exception $e) { $res = '-1'; $return_code = '500'; } if ($res === NULL && ($dsn_name == 'db2' || $dsn_name == 'db3')) { $res = 1; } $output .= $res . ':' . ceil(time() - $start_time); /** * now we have run the main check query, run the extra queries as well. */ foreach ($all_db_queries as $qry) { $query = MatrixDAL::preparePdoQuery($qry);
} catch (Exception $e) { trigger_error('Unable to delete tree links for linkid: ' . $link['linkid'] . ' due to database error: ' . $e->getMessage(), E_USER_ERROR); } // Update sort orders of other children of this parent $sql = 'UPDATE sq_ast_lnk SET sort_order = sort_order - 1 WHERE majorid = :majorid AND sort_order > :sort_order'; $update_sort_order_query = MatrixDAL::preparePdoQuery($sql); try { MatrixDAL::bindValueToPdo($update_sort_order_query, 'majorid', $link['majorid']); MatrixDAL::bindValueToPdo($update_sort_order_query, 'sort_order', $link['sort_order']); MatrixDAL::execPdoQuery($update_sort_order_query); } catch (Exception $e) { trigger_error('Unable to update sort orders for majorid: ' . $link['majorid'] . ' due to database error: ' . $e->getMessage(), E_USER_ERROR); } // Delete from the link table try { $bind_vars = array('linkid' => $link['linkid'], 'majorid' => $link['majorid']); MatrixDAL::executeQuery('core', 'deleteLink', $bind_vars); } catch (Exception $e) { trigger_error('Unable to delete link with linkid: ' . $link['linkid'] . ' due to database error: ' . $e->getMessage(), E_USER_ERROR); } $GLOBALS['SQ_SYSTEM']->doTransaction('COMMIT'); echo "Deleted Link ID: " . $link['linkid'] . " with Major ID: " . $link['majorid'] . " and Minor ID: " . $link['minorid'] . "\n"; } // end foreach link $GLOBALS['SQ_SYSTEM']->restoreDatabaseConnection();