SHARE
TWEET

Untitled

a guest Sep 13th, 2017 57 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DROP DATABASE IF EXISTS pairings_test;
  2. CREATE DATABASE pairings_test;
  3. USE pairings_test;
  4. CREATE TABLE parent_tbl (
  5.     parent_id INT PRIMARY KEY AUTO_INCREMENT,
  6.     child_counter INT NOT NULL DEFAULT 0,
  7.     child_avail INT NOT NULL DEFAULT 40,
  8.     child_busy INT NOT NULL DEFAULT 0
  9. );
  10.  
  11. CREATE TABLE dsthost_tbl (
  12.     dsthost_id INT PRIMARY KEY AUTO_INCREMENT,
  13.     dsthost_spec VARCHAR(24),
  14.     CONSTRAINT UNIQUE(dsthost_spec)
  15. );
  16.  
  17. CREATE TABLE child_tbl (
  18.     child_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  19.     parent_id INT NOT NULL,
  20.     dsthost_id INT NOT NULL,
  21.     FOREIGN KEY (parent_id) REFERENCES parent_tbl(parent_id),
  22.     FOREIGN KEY (dsthost_id) REFERENCES dsthost_tbl(dsthost_id)
  23. );
  24.  
  25. DELIMITER $$
  26. CREATE PROCEDURE new_child(IN dspec VARCHAR(24), IN priority INT)
  27. BEGIN
  28.     -- Map a spec to a dsthost_id
  29.     -- Normally, a given dsthost can only be associated once with a given
  30.     -- parent (though different dsthosts can be associated with different parents)
  31.     -- If priority is true, we ignore this restriction
  32.  
  33.     INSERT IGNORE INTO dsthost_tbl(dsthost_spec) VALUES(dspec);
  34.     SET @my_dstid = (SELECT dsthost_id FROM dsthost_tbl
  35.         WHERE dsthost_spec = dspec LIMIT 1);
  36.  
  37.     DROP TEMPORARY TABLE IF EXISTS tmp_tbl;
  38.     CREATE TEMPORARY TABLE tmp_tbl SELECT * FROM parent_tbl WHERE 0;
  39.    
  40.     IF priority THEN
  41.         SET @my_parent_id = (
  42.             SELECT parent_tbl.parent_id FROM
  43.             parent_tbl LEFT OUTER JOIN child_tbl
  44.             ON
  45.             parent_tbl.parent_id = child_tbl.parent_id
  46.             AND child_tbl.dsthost_id = @my_dstid
  47.             GROUP BY parent_tbl.parent_id, child_tbl.dsthost_id
  48.             ORDER BY
  49.             COUNT(child_tbl.child_id) ASC,
  50.             child_avail DESC,
  51.             child_counter ASC,
  52.             child_busy ASC
  53.             LIMIT 1
  54.         );
  55.     ELSE
  56.         SET @my_parent_id = (
  57.             SELECT parent_tbl.parent_id FROM parent_tbl LEFT OUTER JOIN child_tbl
  58.             ON
  59.             parent_tbl.parent_id = child_tbl.parent_id
  60.             AND child_tbl.dsthost_id = @my_dstid
  61.             WHERE (
  62.             child_tbl.dsthost_id IS NULL
  63.             AND parent_tbl.child_counter < 100
  64.             AND parent_tbl.child_avail > 0
  65.         )
  66.             ORDER BY
  67.             child_counter ASC,
  68.             child_busy ASC,
  69.             child_avail DESC
  70.             LIMIT 1
  71.         );
  72.     END IF;
  73.     IF @my_parent_id THEN
  74.         INSERT INTO tmp_tbl
  75.             (SELECT * FROM parent_tbl WHERE parent_id = @my_parent_id);
  76.         INSERT INTO child_tbl (dsthost_id, parent_id) VALUES (@my_dstid, @my_parent_id);
  77.         UPDATE parent_tbl SET
  78.             child_counter = child_counter + 1,
  79.             child_avail = child_avail - 1,
  80.             child_busy = child_busy + 1
  81.         WHERE parent_id = @my_parent_id;
  82.     END IF;
  83.     SELECT *, LAST_INSERT_ID() FROM tmp_tbl;
  84.     DROP TABLE IF EXISTS tmp_tbl;
  85. END $$
  86. DELIMITER ;
  87.  
  88. DELIMITER $$
  89. CREATE PROCEDURE mkrandrows()
  90. BEGIN
  91.     SET @n_inserted = 0;
  92.     insert_rows: REPEAT
  93.         INSERT INTO parent_tbl() VALUES();
  94.         SET @n_inserted = @n_inserted + 1;
  95.     UNTIL @n_inserted > 5
  96.     END REPEAT;
  97. END $$
  98.  
  99. DELIMITER ;
  100.  
  101. -- Generate random rows:
  102. CALL mkrandrows();
RAW Paste Data
Top