queryDeletedIdsV1.sql

  1. /*
  2.   Obsoleta versión 1
  3.  
  4.   Obtiene los ids de una tabla o vista.
  5.   Obtiene los saltos en la secuencia de ids.
  6.   Retorna el resultado de "tmp_deleted_ids"
  7.   Usado para borrar filas de las tablas clónicas
  8.  
  9.   Resultados:
  10.   - Si la tabla está vacía:
  11.   [1, 2147483647]
  12.   - Si tiene los primeros 10 ids consecutivos:
  13.   [11, 2147483647]
  14.   - Si tiene ids primos hasta 19:
  15.   [1,1],[4,4],[6,6],[8,10],[12,12],[14,16],[18,18],[20,2147483647]
  16.  
  17.   Ejemplo:
  18.   CALL queryDeletedIds('nombre_tabla','campo_id');
  19.   SELECT GROUP_CONCAT(IF(start_id=final_id,start_id,CONCAT(start_id,'-',final_id))) FROM tmp_deleted_ids;
  20.  
  21. */
  22. DELIMITER $$
  23. DROP PROCEDURE IF EXISTS queryDeletedIdsV1$$
  24. CREATE PROCEDURE queryDeletedIdsV1(
  25. IN inTableName VARCHAR(64),
  26. IN inIdField VARCHAR(32)
  27. ) READS SQL DATA
  28. BEGIN
  29. -- Variables
  30. DECLARE myId INT;
  31. DECLARE myStartId, myFinalId INT DEFAULT 1;
  32. DECLARE myCounter INT DEFAULT 0;
  33.  
  34. -- Cursor
  35. DECLARE myDone1 BOOLEAN DEFAULT FALSE;
  36. DECLARE myCursor CURSOR FOR SELECT id FROM view_ids ORDER BY id;
  37. DECLARE CONTINUE HANDLER FOR NOT FOUND SET myDone1 := TRUE;
  38.  
  39. -- Get all ids
  40. SET inIdField := COALESCE(inIdField, 'id');
  41. SET @query := CONCAT('CREATE OR REPLACE VIEW view_ids AS SELECT ', inIdField,
  42. ' AS id FROM ', inTableName, ' ORDER BY 1');
  43. PREPARE stmt FROM @query;
  44. EXECUTE stmt;
  45. DEALLOCATE PREPARE stmt;
  46.  
  47. -- Create undeleted ids table
  48. CREATE /*TEMPORARY*/ TABLE IF NOT EXISTS tmp_deleted_ids (start_id INT, final_id INT);
  49. DELETE FROM tmp_deleted_ids;
  50.  
  51. -- Scan for undeleted ids
  52. OPEN myCursor;
  53. loop1: LOOP
  54. FETCH myCursor INTO myId;
  55. IF myDone1 THEN
  56. SET myStartId := COALESCE(myId,0)+1;
  57. SET myFinalId := POW(2,31)-1; -- max signed int
  58. INSERT INTO tmp_deleted_ids VALUES (myStartId, myFinalId);
  59. CLOSE myCursor;
  60. LEAVE loop1;
  61. END IF;
  62. IF myStartId = myId THEN
  63. SET myStartId := myId+1;
  64. ELSEIF myId > 0 THEN
  65. SET myFinalId := myId-1;
  66. INSERT INTO tmp_deleted_ids VALUES (myStartId, myFinalId);
  67. SET myStartId := myId+1;
  68. END IF;
  69. END LOOP;
  70.  
  71. -- Cleaning
  72. DROP VIEW IF EXISTS view_ids;
  73.  
  74. -- Result
  75. SELECT start_id, final_id FROM tmp_deleted_ids ORDER BY start_id;
  76. END$$
  77. DELIMITER ;
  78.  

Proinf.net