コード例 #1
0
    /**
     * 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;
    }
コード例 #2
0
// 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, '.');
        }
コード例 #3
0
 /**
  * Peforms a foreign key integrity check on the specified table
  *
  * @param string	$table	the name of the table to perform the check on
  *
  * @return boolean
  * @access public
  */
 function checkTable($table)
 {
     $db =& $GLOBALS['SQ_SYSTEM']->db;
     $dbtype = $this->_getDbType();
     // we're going to check these keys
     $fks = $this->fks($table);
     if (empty($fks)) {
         return TRUE;
     }
     $this->printTable($table);
     foreach ($fks as $fk_field => $fk_info) {
         // query db for invalid foreign keys
         switch ($fk_field) {
             case 'treeid':
             case 'type_code':
             case 'inhd_type_code':
                 switch ($dbtype) {
                     case 'oci':
                         $where_cond = $fk_field . " > ''";
                         break;
                     default:
                         $where_cond = $fk_field . "::text > ''";
                 }
                 //end switch
                 break;
             default:
                 switch ($dbtype) {
                     case 'oci':
                         $where_cond = 'CAST (' . $fk_field . " as int) > 0";
                         break;
                     default:
                         $where_cond = $fk_field . "::int > 0";
                 }
                 //end switch
         }
         //end switch
         $sub_sql = 'SELECT ' . $fk_field . ' FROM ' . $fk_info['table'];
         $sql = 'SELECT ' . $fk_field . ' FROM ' . $table . ' WHERE ' . $where_cond . ' AND ' . $fk_field . ' NOT IN (' . $sub_sql . ')';
         $assetids = MatrixDAL::executeSqlAssoc($sql, 0);
         $broken_count = count($assetids);
         if (!is_array($assetids)) {
             $broken_count = 0;
         }
         $this->printColumn($fk_info['table'], $fk_field, $broken_count);
         if (is_array($assetids) && !empty($assetids)) {
             $this->printResult($assetids);
         }
     }
     //end foreach
     echo "\n";
 }
    } catch (DALException $e) {
        // no good
        $GLOBALS['SQ_SYSTEM']->doTransaction('ROLLBACK');
        printUpdateStatus('FAILED');
    }
}
//end for
//--        CLEANING ROLES        --//
// Note that userid of 0 is legitimate as this indicates a global role
echo 'Cleaning up roles...' . "\n";
$sql = 'SELECT
			DISTINCT userid
		FROM sq_ast_role
		WHERE userid <> 0
		ORDER BY userid';
$user_ids = MatrixDAL::executeSqlAssoc($sql, 0);
foreach ($user_ids as $user_id) {
    $id_parts = explode(':', $user_id);
    if (isset($id_parts[1])) {
        $real_assetid = $id_parts[0];
        $bridge = $GLOBALS['SQ_SYSTEM']->am->getAsset($real_assetid, '', TRUE);
        if (is_null($bridge)) {
            // bridge is unknown, we cannot return anything from it
            $asset = NULL;
        } else {
            $asset = $bridge->getAsset($user_id, '', TRUE, TRUE);
        }
        $GLOBALS['SQ_SYSTEM']->am->forgetAsset($bridge);
    } else {
        $asset =& $GLOBALS['SQ_SYSTEM']->am->getAsset($user_id, '', TRUE);
    }
コード例 #5
0
     $sql = 'UPDATE sq_ast_path SET path = :new_path WHERE path = :old_path AND assetid = :assetid';
     $records_count = _executeSql($sql, array('new_path' => $new_path, 'old_path' => $old_path, 'assetid' => $assetid));
     $summary['ast_path'] += $records_count ? $records_count : 0;
     if ($records_count === FALSE) {
         break 2;
     }
 }
 // Add the remap entry for the url change
 if (!$data_url_pos) {
     foreach (array('http', 'https') as $protocol) {
         if ($protocol == 'http' && !$http || $protocol == 'https' && !$https) {
             continue;
         }
         // If the URL is already remapped, update it
         $sql = 'SELECT url FROM sq_ast_lookup_remap WHERE url = ' . MatrixDAL::quote($protocol . '://' . $url);
         if (MatrixDAL::executeSqlAssoc($sql, 'url')) {
             $sql = 'UPDATE sq_ast_lookup_remap SET remap_url = :remap_url, auto_remap = :auto_remap WHERE url = :url';
         } else {
             $sql = 'INSERT INTO sq_ast_lookup_remap(url, remap_url, auto_remap) VALUES (:url, :remap_url, :auto_remap)';
         }
         $bind_vars = array('url' => $protocol . '://' . $url, 'remap_url' => $protocol . '://' . $updated_url, 'auto_remap' => '1');
         $records_count = _executeSql($sql, $bind_vars);
         $summary['ast_lookup_remap'] += $records_count ? $records_count : 0;
         if ($records_count === FALSE) {
             break 2;
         }
     }
     //end foreach protocol
 }
 //end if non-unrestricted url
 // Update ast_lookup_value table
コード例 #6
0
    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']++;
            }
        }
コード例 #7
0
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());
        }
    }
}
コード例 #8
0
/**
* 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();
}
    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';
echo "Upgrading asset metadata db entris ";
$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;
}
// Get all the non-default context metadata entries in the system
$sql = "SELECT assetid, fieldid, contextid FROM sq_ast_mdata_val WHERE contextid <> '0'";
$records = MatrixDAL::executeSqlAssoc($sql);
// Group by metadata field assetid to limit the am->getAsset() call
$field_metadata = array();
foreach ($records as $count => $record) {
    if (!$record['assetid'] || !$record['fieldid'] || !$record['contextid']) {
        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) {
コード例 #10
0
			SET published_userid = ' . MatrixDAL::quote($new_dn) . '
			WHERE published_userid = ' . MatrixDAL::quote($old_dn);
$result = MatrixDAL::executeSql($sql);
$sql = 'UPDATE sq_rb_ast
			SET status_changed_userid = ' . MatrixDAL::quote($new_dn) . '
			WHERE status_changed_userid = ' . MatrixDAL::quote($old_dn);
$result = MatrixDAL::executeSql($sql);
$sql = 'UPDATE sq_rb_ast_lnk
			SET updated_userid = ' . MatrixDAL::quote($new_dn) . '
			WHERE updated_userid = ' . MatrixDAL::quote($old_dn);
$result = MatrixDAL::executeSql($sql);
printActionStatus('OK');
printActionName('Changing shadow links');
// find out any links that has already be re-created..possibly from backend
$sql = 'SELECT majorid FROM sq_shdw_ast_lnk where minorid = ' . MatrixDAL::quote($new_dn);
$existing_links = MatrixDAL::executeSqlAssoc($sql);
foreach ($existing_links as $index => $existing_link) {
    $existing_links[] = $existing_links[$index]['majorid'];
    unset($existing_links[$index]);
}
// update the dn here but be sure to not to try to insert
// duplicate entries incase the updated ldap user is already
// linked to target asset from backend for more info see bug
// 5686 LDAP update script fails if unique constraint violated
$sql = 'UPDATE sq_shdw_ast_lnk
			SET minorid = ' . MatrixDAL::quote($new_dn) . '
			WHERE minorid = ' . MatrixDAL::quote($old_dn) . '
			AND majorid NOT IN (SELECT s.majorid FROM sq_shdw_ast_lnk s where s.minorid = ' . MatrixDAL::quote($new_dn) . ')';
$result = MatrixDAL::executeSql($sql);
MatrixDAL::executeQuery('core', 'changeShadowLinkUpdatedDateUser', $bind_vars);
printActionStatus('OK');
コード例 #11
0
 MatrixDAL::executeSql('DROP INDEX sq_suite_product_type');
 // Create a new sequence.
 $sequence = MatrixDAL::executeSqlAssoc("SELECT sequence_name FROM user_sequences WHERE sequence_name='SQ_SUITE_SEQ'");
 if (empty($sequence)) {
     MatrixDAL::executeSql('CREATE SEQUENCE sq_suite_seq INCREMENT BY 1');
 }
 // Add new columns.
 MatrixDAL::executeSql('ALTER TABLE sq_suite_product ADD suiteid INTEGER');
 MatrixDAL::executeSql('ALTER TABLE sq_suite_product ADD url VARCHAR2(2000)');
 MatrixDAL::executeSql('ALTER TABLE sq_suite_product ADD token VARCHAR2(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=sq_suite_seq.nextVal");
 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);
     if ($suiteid === NULL || $connection === NULL) {
         continue;
     }
     $connection = @unserialize($connection);
     if ($connection === FALSE) {
         continue;
     }
     $url = array_get_index($connection, 'url', NULL);
     if ($url === NULL) {
         continue;
     }
     unset($connection['url']);
コード例 #12
0
    $package_list = get_console_list($options[0]);
}
// get the list of functions used during install
require_once $SYSTEM_ROOT . '/install/install.inc';
$GLOBALS['SQ_SYSTEM']->changeDatabaseConnection('db2');
$GLOBALS['SQ_SYSTEM']->doTransaction('BEGIN');
// firstly let's check that we are OK for the version
if (version_compare(PHP_VERSION, SQ_REQUIRED_PHP_VERSION, '<')) {
    trigger_error('<i>' . SQ_SYSTEM_LONG_NAME . '</i> requires PHP Version ' . SQ_REQUIRED_PHP_VERSION . '.<br/> You may need to upgrade.<br/> Your current version is ' . PHP_VERSION, E_USER_ERROR);
}
$old_path = ini_get('include_path');
ini_set('include_path', SQ_LIB_PATH);
require_once SQ_LIB_PATH . '/MatrixDAL/MatrixDALBaker.inc';
$packages = $GLOBALS['SQ_SYSTEM']->getInstalledPackages();
$asset_sql = 'SELECT type_code FROM sq_ast_typ';
$asset_types = MatrixDAL::executeSqlAssoc($asset_sql, 0);
print_status_name('Installing queries for MySource Matrix core...');
try {
    MatrixDALBaker::addCoreQueries();
    MatrixDALBaker::bakeQueriesFile('core');
    print_status_result('OK');
} catch (Exception $e) {
    print_status_result('FAILED');
    echo '(Exception: ' . $e->getMessage() . ')' . "\n";
    exit(1);
}
if (count($packages) === 0) {
    print_status_name('No packages currently installed...');
    print_status_result('SKIPPED');
} else {
    print_status_name('Installing queries for installed packages (' . count($packages) . ' packages)...');
コード例 #13
-2
/**
 * 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);
}