A quick migration script to convert the hierarchy stored in a badly-made path enumeration table to a closure table. Since there were no root nodes in the old table, I made some new ones to contain everything.

closure_migation.php

<?php

/*
* Convert the path enumeration of depts from dept_assign to closure table
*/

class Department {

    public $dept_code;

    // The old-style level as seen in dept_assign
    public $level;
    
    // A level 4 department will have 2 parents, a level 3 and a level 2
    public $parents = array(); 

    // A level 3 department will many children at level 4 through 7
    public $children = array();

    // The enumerated path
    public $path = array();

    public function __construct( $dept_code ){

        if( ! preg_match( '/[0-9]{6}/', $dept_code ) )
            die( 'Invalid department code' );

            $this->dept_code = $dept_code;

        $this->get_level();
        $this->get_parents();
        $this->get_children();

    }

    /*
    * Queries the DB and gets the LEVEL_X_DEPT_CD of each level
    * Sets $this->path to the result
    */
    public function get_dept_path(){

        global $DB;

        if( $this->dept_code != '000001' && $this->dept_code != '000002' ){

            $query = $DB->query( "select '000001' as level_0_dept_cd, '000002' as level_1_dept_cd, level_2_dept_cd, level_3_dept_cd, level_4_dept_cd, level_5_dept_cd, level_6_dept_cd, level_7_dept_cd
                                  from  dept_assign where dept_cd = {$this->dept_code} limit 1" );

            if( $query->num_rows == 0 )
                die( 'Department not found' );

            // The levels that appear empty are actually just a single space
            array_walk( $query->result[ 0 ], 'trim' );

            $results = $query->result[ 0 ];

            // Using numbers in quotes so I can just use array_keys later
            $this->path = array( '0' => $results[ 'level_0_dept_cd' ], 
                                 '1' => $results[ 'level_1_dept_cd' ], 
                                 '2' => $results[ 'level_2_dept_cd' ], 
                                 '3' => $results[ 'level_3_dept_cd' ], 
                                 '4' => $results[ 'level_4_dept_cd' ], 
                                 '5' => $results[ 'level_5_dept_cd' ], 
                                 '6' => $results[ 'level_6_dept_cd' ], 
                                 '7' => $results[ 'level_7_dept_cd' ] );

        } else {

            // Depts 000001 and 000002 encompass all the old depts, but 
            // aren't part of the old table.

            // University of California
            if( $this->dept_code == '000001' ){

                $this->path = array( '0' => '000001', 
                                 '1' => '', 
                                 '2' => '', 
                                 '3' => '', 
                                 '4' => '', 
                                 '5' => '', 
                                 '6' => '', 
                                 '7' => '' );

            }

            // University of California San Francisco
            if( $this->dept_code == '000002' ){

                $this->path = array( '0' => '000001', 
                                     '1' => '000002', 
                                     '2' => '', 
                                     '3' => '', 
                                     '4' => '', 
                                     '5' => '', 
                                     '6' => '', 
                                     '7' => '' );

            }

        }

    }

    /*
    * Gets the first occurence of the dept code in a path
    * Returns the key ( the level number )
    */
    public function get_level(){

        if( empty( $this->path ) )
            $this->get_dept_path();

        // This should never happen
        if( empty( $this->path ) )
            die( 'No path info found.' );

        $this->level = array_search( $this->dept_code, $this->path );

        if( empty( $this->level ) && $this->level !== 0 )
            $this->level = 2;       

    }

    /*
    * Get the parents above this department
    * Sets $this->parents to an array of the parent dept codes
    */
    public function get_parents(){

        if( empty( $this->path ) )
            $this->get_dept_path();

        $found = 0;

        foreach( $this->path as $dept_code ){

            if( $this->dept_code == $dept_code )
                $found = 1;

            if( $found == 0 )
                $this->parents[] = $dept_code;

        }

    }

    /*
    * Get the children of this department
    */
    public function get_children(){

        global $DB;

        if( $this->level > 6 )
            return array();
        if( $this->dept_code == '000001' || $this->dept_code == '000002' )
            $query = $DB->query( 'select dept_cd from dept_assign where length( level_2_dept_cd ) = 6 and dept_title not like "%NOT%"' );// Gets all valid depts
        else
            $query = $DB->query( "select dept_cd from dept_assign where level_{$this->level}_dept_cd = {$this->dept_code} and dept_cd != {$this->dept_code}");

        $this->children = array_map( function( $row ){ return $row[ 'dept_cd' ]; }, $query->result );

        // Create UC->UCSF parent->child relationhsip
        if( $this->dept_code == '000001' )
            array_unshift( $this->children, '000002' );

    }

    // Returns the difference in levels between two departments
    public function get_child_depth( $child_code ){

        $child = new Department( $child_code );

        return $child->level - $this->level;

    }

    // Insert relationships into the DB
    public function save_relationships(){

        global $DB;

        $rels = array();

        // Capture itself
        $rels[] = array( $this->dept_code, $this->dept_code, 0 );

        foreach( $this->children as $child_code ){

            $depth = $this->get_child_depth( $child_code );

            $child_check = $DB->query( "select code from departments where code = '{$child_code}'" );

            if( $child_check->num_rows > 0 )
                $rels[] = array( $this->dept_code, $child_code, $depth );

        }

        $rels_insert = '';

        foreach( $rels as $rel ){

            $rels_insert .= "( '{$rel[ 0 ]}', '{$rel[ 1 ]}', {$rel[ 2 ]} ),";

        }

        $rels_insert = rtrim( $rels_insert, ',' );

        $DB->query( "insert into department_relationships ( parent_code, child_code, depth ) values {$rels_insert}" );

    }

}

/*
* Client code
*/

// Get a list of departments from new dept table
$dept_query = $DB->query( 'select code from departments' );

$dept_array = array_filter( array_map( function( $row ){ 
    return $row[ 'code' ]; 
}, $dept_query->result ) );

// Create the relationships for each dept
foreach( $dept_array as $dept_code ){

    $dept = new Department( $dept_code );

    $dept->save_relationships();

}

die( 'Migration complete.' );

?>