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();
}
/**
* getIndexes
* Returns an array of indexes based on the type of db it's talking to
*
* @return Array Returns an array of index names
*/
function getIndexes()
{
$dbtype = _getDbType();
switch ($dbtype) {
case 'oci':
$sql = 'SELECT index_name FROM user_indexes';
break;
case 'pgsql':
$sql = 'SELECT indexname from pg_indexes where tablename like \'sq_%\' and indexdef not ilike \'create unique index %\'';
break;
}
$del_idx = array();
if ($sql !== false) {
$indexes = MatrixDAL::executeSqlAll($sql);
foreach ($indexes as $key => $value) {
$del_idx[] = strtolower($value[0]);
}
}
return $del_idx;
}
/**
* 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;
}
/**
* getIndexes
* Returns an array of indexes based on the type of db it's talking to
*
* @return Array Returns an array of index names
*/
function getIndexes()
{
global $db_conf;
$dbtype = _getDbType();
switch ($dbtype) {
case 'oci':
$sql = "SELECT u.table_name as tablename, u.index_name as indexname, DBMS_METADATA.GET_DDL('INDEX',u.index_name) AS indexdef, TRIM(u.degree) AS parallel, CASE WHEN c.constraint_name IS NULL THEN 0 ELSE 1 END AS constraint FROM USER_INDEXES u LEFT JOIN user_constraints c ON (u.index_name=c.constraint_name) WHERE u.TABLE_NAME LIKE 'SQ_%' ORDER BY u.table_name";
break;
case 'pgsql':
$sql = 'SELECT tablename, indexname, indexdef from pg_indexes where tablename like \'sq_%\'';
break;
}
$constraint_list = array();
$idx_list = array();
$parallel_list = array();
if ($sql !== false) {
$indexes = MatrixDAL::executeSqlAll($sql);
foreach ($indexes as $key => $value) {
$tablename = strtolower($value['tablename']);
if (!isset($idx_list[$tablename])) {
$idx_list[$tablename] = array();
}
switch ($dbtype) {
case 'oci':
$idx_def = $value['indexdef'];
$idx_columns = '';
$idx_name = $value['indexname'];
/**
* Indexes in oracle look like this
CREATE [UNIQUE] INDEX "username"."SQ_AST_PUBLISHED" ON "username"."SQ_AST" ("PUBLISHED") ..
*/
preg_match('/index "' . $db_conf['db']['user'] . '"."' . $idx_name . '" on "' . $db_conf['db']['user'] . '"."(.*?)" \\((.*?)\\)/i', $idx_def, $matches);
if (!empty($matches) && !empty($matches[2])) {
$idx_columns = str_replace(array(' ', '"'), '', strtolower($matches[2]));
}
$idx_name = strtolower($idx_name);
/**
* The way oracle handles unique indexes is to create a sub-index
* to enforce things. It's still an index but it has no columns.
*/
if (empty($idx_columns) === TRUE) {
break;
}
$idx_list[$tablename][$idx_name] = $idx_columns;
$parallel_list[$tablename][$idx_name] = $value['parallel'];
/**
* if there's a constraint in user_constraints
* remember that for later in case it needs to be dropped.
*/
if ($value['constraint'] == 1) {
$constraint_list[strtoupper($idx_name)] = 1;
}
break;
case 'pgsql':
$idx_def = $value['indexdef'];
$idx_columns = '';
/**
* All postgres indexes are btree indexes,
* we don't use any other types in matrix.
*
* The format of which is:
* CREATE [UNIQUE] INDEX thes_term_note_pk ON sq_thes_term_note USING btree (termid, name, thesid, value);
* ("UNIQUE" is optional)
*/
preg_match('/USING btree \\((.*?)\\)$/i', $idx_def, $matches);
if (!empty($matches[1])) {
// clean up special quoted names (like "type" in 8.1 dbs)
$idx_columns = str_replace(' ', '', str_replace('"', '', $matches[1]));
}
$idx_list[$tablename][strtolower($value['indexname'])] = $idx_columns;
break;
}
}
}
return array('index_list' => $idx_list, 'parallel_list' => $parallel_list, 'constraint_list' => $constraint_list);
}
