queryUndeletedIds.sql

  1. /*
  2.   Get the undeleted ids of a table|view.
  3.   The rest of id should be deleted
  4.   The resulting table is "tmp_undeleted_ids"
  5.   Not used.
  6.  
  7.   Results:
  8.   - If table is empty:
  9.   [,]
  10.   - If has first ten consecutive ids:
  11.   [1,10]
  12.   - If has primes id until 19:
  13.   [2,3],[5,5],[7,7],[11,11],[13,13],[17,17],[19,19]
  14.  
  15.   Example 1:
  16.   CALL queryUndeletedIds('slots', 'id');
  17.   SELECT GROUP_CONCAT(IF(start_id=final_id,start_id,CONCAT(start_id,'-',final_id))) FROM tmp_undeleted_ids;
  18.   -- '1-2,5-6,8-10,13-25,28-37,40-45,47-65'
  19. */
  20. DELIMITER $$
  21. DROP PROCEDURE IF EXISTS queryUndeletedIds$$
  22. CREATE PROCEDURE queryUndeletedIds(
  23. IN inTableName VARCHAR(64),
  24. IN inIdField VARCHAR(32)
  25. ) READS SQL DATA
  26. BEGIN
  27. -- Variables
  28. DECLARE myId INT;
  29. DECLARE myStartId, myFinalId INT DEFAULT 0;
  30. DECLARE myCounter INT DEFAULT 0;
  31.  
  32. -- Cursor
  33. DECLARE myDone1 BOOLEAN DEFAULT FALSE;
  34. DECLARE myCursor CURSOR FOR SELECT id FROM view_ids ORDER BY id;
  35. DECLARE CONTINUE HANDLER FOR NOT FOUND SET myDone1 := TRUE;
  36.  
  37. -- Get all ids
  38. SET inIdField := COALESCE(inIdField, 'id');
  39. SET @query := CONCAT('CREATE OR REPLACE VIEW view_ids AS SELECT ', inIdField,
  40. ' AS id FROM ', inTableName, ' ORDER BY 1');
  41. PREPARE stmt FROM @query;
  42. EXECUTE stmt;
  43. DEALLOCATE PREPARE stmt;
  44.  
  45. -- Create undeleted ids
  46. CREATE /*TEMPORARY*/ TABLE IF NOT EXISTS tmp_undeleted_ids (start_id INT, final_id INT);
  47. DELETE FROM tmp_undeleted_ids;
  48.  
  49. -- Scan for undeleted ids
  50. OPEN myCursor;
  51. loop1: LOOP
  52. FETCH myCursor INTO myId;
  53. IF myDone1 THEN
  54. CLOSE myCursor;
  55. LEAVE loop1;
  56. END IF;
  57. IF myStartId = 0 THEN
  58. SET myStartId := myId;
  59. SET myFinalId := myId;
  60. ELSEIF myId = myFinalId+1 THEN
  61. SET myFinalId := myId;
  62. ELSE
  63. INSERT INTO tmp_undeleted_ids VALUES (myStartId, myFinalId);
  64. SET myStartId := myId;
  65. SET myFinalId := myId;
  66. SET myCounter := myCounter + 1;
  67. END IF;
  68. END LOOP;
  69. IF myStartId <> 0 THEN
  70. INSERT INTO tmp_undeleted_ids VALUES (myStartId, myFinalId);
  71. SET myCounter := myCounter + 1;
  72. END IF;
  73.  
  74. -- Cleaning
  75. DROP VIEW IF EXISTS view_ids;
  76.  
  77. -- Result
  78. SELECT start_id, final_id FROM tmp_undeleted_ids ORDER BY start_id;
  79. END$$
  80. DELIMITER ;
  81.  

Proinf.net