title: How do I convert a relational hierarchy to a closure table in MySQL?
body: <p>I recently started using a <a href="http://www.slideshare.net/billkarwin/models-for-hierarchical-data" rel="nofollow">closure table</a>, and it was working fine when I had to convert simple parent-child relationships from person to lineage, but now I have to convert grandparent-parent-child and great-grandparent-parent-child relationships, and maybe even longer relationships than that into the closure table. This is the table I need to pull the relationships from:</p>
  
  <pre><code>CREATE TABLE `person` (
      `person` BIGINT UNSIGNED NOT NULL ,
      `parent` BIGINT UNSIGNED NULL ,
      PRIMARY KEY (`person`) ,
      INDEX `idx_person_has_parent` (`parent` ASC),
      CONSTRAINT `fk_person_has_parent`
          FOREIGN KEY (`parent`)
          REFERENCES `person` (`person`)
          ON DELETE NO ACTION
          ON UPDATE NO ACTION
  );
  </code></pre>
  
  <p>This is the table I need to convert to:</p>
  
  <pre><code>CREATE TABLE `lineage` (
      `ancestor` BIGINT UNSIGNED NOT NULL,
      `descendant` BIGINT UNSIGNED NOT NULL,
      `length` INT UNSIGNED NOT NULL,
      PRIMARY KEY (`ancestor`, descendant`),
      CONSTRAINT `fk_ancestor_has_descendant`
          FOREIGN KEY (`descendant`)
          REFERENCES `person` (`person`)
          ON DELETE NO ACTION
          ON UPDATE NO ACTION,
      CONSTRAINT `fk_descendant_has_ancestor`
          FOREIGN KEY (`ancestor`)
          REFERENCES `person` (`person`)
          ON DELETE NO ACTION
          ON UPDATE NO ACTION
  );
  </code></pre>
  
  <p>The biggest problem I'm having is saving the correct length for entries of grandparent-child, which should be length 2. Grandparent-parent and parent-child are both 1, and each of them has a length 0 when associated with themselves (child-child, parent-parent, etc.). Can I do this in a query or will it require some sort of program?</p>