BuildLinkTable

#CREATE PROCEDURE KeySpaces.BuildLinkTable (geneTable VARCHAR(30), targetTable VARCHAR(30), linkTable VARCHAR(30))
# Requires MySQL 5.0.17 or later, since uses SQL prepared statements
# Requires that there be a default temp table to scan.
# Change delimiter so can input this code.
DROP PROCEDURE IF EXISTS BuildLinkTable;
DELIMITER //

CREATE DEFINER = CURRENT_USER PROCEDURE BuildLinkTable (
      IN geneTable VARCHAR(30), IN targetTable VARCHAR(30), IN linkTable VARCHAR(30))
   LANGUAGE SQL
   DETERMINISTIC
   MODIFIES SQL DATA
   SQL SECURITY INVOKER
   COMMENT 'Routine to take a loaded temp link table, and find the actual links to put into the real link table'
BEGIN
   DECLARE done INT DEFAULT 0;
   DECLARE gName, tName VARCHAR(30);
   DECLARE selGeneStatement, selTargetStatement VARCHAR(80);
   DECLARE insertStatement VARCHAR(100);
#   DECLARE gID, tID INT;
   DECLARE mul ENUM('1-1', '1-M', 'M-1', 'M-M');
   DECLARE biDir BOOLEAN;
   DECLARE tempCursor CURSOR FOR SELECT geneName, targetName, mutiplicity, biDirectional FROM tempLinks;
   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
   SET @selGene = "";
   SET @selTarget = "";
   SET @doInsert = "";
   SET @gID = 0;
   SET @tID = 0;
   SET @geName = "";
   SET @taName = "";

   SET selGeneStatement = CONCAT("SELECT id FROM ", geneTable, " WHERE name = @geName INTO @gID");
   SET @selGene = selGeneStatement;
   PREPARE selGeneID FROM @selGene;

   SET selTargetStatement = CONCAT("SELECT id FROM ", targetTable, " WHERE name = @taName INTO @tID");
   SET @selTarget = selTargetStatement;
   PREPARE selTargetID FROM @selTarget;

   SET insertStatement = CONCAT("INSERT INTO ", linkTable, " VALUES (?, ?, '", targetTable, "', ?, ?)");
   SET @doInsert = insertStatement;
   PREPARE insertCom FROM @doInsert;

   OPEN tempCursor;

   REPEAT
      FETCH tempCursor INTO gName, tName, mul, biDir;
      IF !done THEN
         SET @geName = gName, @taName = tName;
         EXECUTE selGeneID;      # Get Gene ID from gName
         EXECUTE selTargetID;   # Get target ID from tName

         #Need error checking here.  What if couldn't find one of these?

         SET @mult = mul, @dir = biDir;
         EXECUTE insertCom USING @gID, @tID, @mult, @dir;
      END IF;
   UNTIL done END REPEAT;


   DEALLOCATE PREPARE selGeneID;
   DEALLOCATE PREPARE selTargetID;
   DEALLOCATE PREPARE insertCom;
END;
//

DELIMITER ;

-- Main.gregd - 26 Jun 2007