system_integrity_foreign_keys.php

<?php
error_reporting(E_ALL);
if ((php_sapi_name() != 'cli')) {
    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();
}

$DELETING_ASSET_TYPE = (isset($_SERVER['argv'][2])) ? $_SERVER['argv'][2] : '';
require_once $SYSTEM_ROOT.'/core/include/init.inc';

$root_user =& $GLOBALS['SQ_SYSTEM']->am->getSystemAsset('root_user');

// log in as root
if (!$GLOBALS['SQ_SYSTEM']->setCurrentUser($root_user)) {
    echo "ERROR: Failed loggin in as root user\n";
    exit();
}

define('SQ_FOREIGN_KEY_INTEGRITY_CHECK_OUTPUT_WIDTH', 72);

class Foreign_Key_Integrity_Check
{


    var $fks = Array(
                'assetid'   => Array(
                                'table' => 'sq_ast',
                                'field' => 'assetid',
                                'match' => Array(
                                            'assetid',
                                            'created_userid',
                                            'updated_userid',
                                            'published_userid',
                                            'status_changed_userid',
                                            'majorid',
                                            'minorid',
                                            'schemaid',
                                            'userid',
                                            'roleid',
                                           ),
                               ),
                'treeid'    => Array(
                                'table' => 'sq_ast_lnk_tree',
                                'field' => 'treeid',
                                'match' => Array(
                                            'treeid',
                                           ),
                               ),
                'attrid'    => Array(
                                'table' => 'sq_ast_attr',
                                'field' => 'attrid',
                                'match' => Array(
                                            'attrid',
                                            'owning_attrid',
                                           ),
                               ),
                'linkid'    => Array(
                                'table' => 'sq_ast_lnk',
                                'field' => 'linkid',
                                'match' => Array(
                                            'linkid',
                                           ),

                               ),
                'urlid'     => Array(
                                'table' => 'sq_ast_url',
                                'field' => 'urlid',
                                'match' => Array(
                                            'urlid',
                                            'root_urlid',
                                           ),
                               ),
                'type_code' => Array(
                                'table' => 'sq_ast_typ',
                                'match' => Array(
                                            'type_code',
                                            'inhd_type_code',
                                           ),
                               ),
               );


    var $tables = Array();


    function Foreign_Key_Integrity_Check()
    {
        if (!$this->load()) {
            // todo: trigger_error
        }

    }//end Foreign_Key_Integrity_Check()


    function tables()
    {
        return $this->tables;

    }//end tables()


    function columns($table)
    {
        // if we don't know about this table, die
        if (!in_array($table, $this->tables())) {
            return Array();
        }

        $db =& $GLOBALS['SQ_SYSTEM']->db;
        $dbtype = $this->_getDbType();
        switch ($dbtype) {
            case 'oci':
                $sql = "SELECT column_name FROM user_tab_cols WHERE table_name = '".strtoupper($table)."'";
            break;
            default:
                $sql = "select column_name from information_schema.columns WHERE table_name='".$table."'";
            break;
        }//end switch
        $results = MatrixDAL::executeSqlAssoc($sql);
        $columns = Array();
        foreach ($results as $column) {
            $column_name = array_get_index($column, 'column_name', '');
            if (!empty($column_name)) {
                $columns[] = strtolower($column_name);
            }//end if
        }//end foreach

        return $columns;

    }//end columns()


    function fks($table)
    {
        $fks = Array();
        $columns = $this->columns($table);

        foreach ($this->fks as $fk => $fk_info) {
            // get all the names that this foreign key goes by
            $fk_columns = $fk_info['match'];

            // these are the defined fks we found in the specified table
            $match_fks = array_intersect($fk_columns, $columns);
            if (empty($match_fks)) continue;

            // build the information into expected format
            foreach ($match_fks as $fk) {
                $fks[$fk] = $fk_info;
            }
        }
        return $fks;

    }//end fks()


    function load()
    {
        $db =& $GLOBALS['SQ_SYSTEM']->db;
        $GLOBALS['SQ_SYSTEM']->changeDatabaseConnection('db');
        $dbtype = $this->_getDbType();

        // load our tables from the database
        switch ($dbtype) {
            case 'oci':
                $sql = "SELECT object_name AS table_name FROM user_objects WHERE object_type = 'TABLE'";
            break;
            default:
                $sql = "SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE'";
            break;
        }//end switch
        $results = MatrixDAL::executeSqlAssoc($sql);
        $tables = Array();
        foreach ($results as $table) {
            $table_name = array_get_index($table, 'table_name', '');
            if (!empty($table_name)) {
                $tables[] = strtolower($table_name);
            }//end if
        }//end foreach

        $this->tables = $tables;
        if (empty($this->tables)) {
            return FALSE;
        }
        return TRUE;

    }//end load()


    function checkDatabase()
    {
        // we're going to check each table in the database
        $tables = $this->tables();

        foreach ($tables as $table) {
            if (substr($table, 0, 6) != 'sq_ast') {
                continue;
            }
            $this->checkTable($table);
        }

    }//end checkDatabase()


    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";

    }//end checkTable()


    function printTable($table)
    {
        echo "> checking table $table...\n\n";

    }//end printTable()


    function printColumn($table, $column, $count)
    {
        $column_text = "$table.$column";
        $count_text = "$count found";

        echo $column_text;
        echo str_repeat(' ', (SQ_FOREIGN_KEY_INTEGRITY_CHECK_OUTPUT_WIDTH - strlen($column_text) - strlen($count_text)));
        echo $count_text."\n";

    }//end printColumn()


    function printResult($assetids)
    {
        echo "\t".'\''.implode('\', \'', $assetids).'\''."\n";

    }//end printResult


    private function _getDbType()
    {
        $dbtype = MatrixDAL::GetDbType();

        if ($dbtype instanceof PDO) {
            $dbtype = $dbtype->getAttribute(PDO::ATTR_DRIVER_NAME);
        }
        return strtolower($dbtype);

    }//end _getDbType()


}//end class


$foreign_key_checker = new Foreign_Key_Integrity_Check();
$foreign_key_checker->checkDatabase();

?>