deleteDeletedIds.sql

  1. /*
  2.   Deleted records
  3.  
  4.   Used in api/test/import_quiz.php
  5.  
  6.   The parameter inDeleteIds can be obtained from queryDeletedIds()
  7.  
  8.   Example:
  9.   DROP TABLE IF EXISTS zz;
  10.   CREATE TABLE zz AS SELECT number AS id FROM multipliers4096;
  11.   SELECT * FROM zz;
  12.   CALL deleteDeletedIds('zz', '1-3,5,11-2147483647');
  13.   SELECT * FROM zz;
  14. */
  15. DELIMITER $$
  16. DROP PROCEDURE IF EXISTS deleteDeletedIds$$
  17. CREATE PROCEDURE deleteDeletedIds(
  18. IN inTableName VARCHAR(64),
  19. IN inIdField VARCHAR(32),
  20. IN inDeletedIds TEXT
  21. ) READS SQL DATA
  22. BEGIN
  23. CALL insertTemporaryIds(inDeletedIds);
  24. SET inIdField := COALESCE(inIdField, 'id');
  25. SET @query := CONCAT('DELETE ', inTableName, ' FROM ', inTableName,
  26. ' INNER JOIN tmp_ids WHERE ', inIdField,' BETWEEN start_id AND final_id');
  27. PREPARE stmt FROM @query;
  28. EXECUTE stmt;
  29. DEALLOCATE PREPARE stmt;
  30. END$$
  31. DELIMITER ;
  32.  
  33. /*
  34.   Used in deleteDeletedIds()
  35.  
  36.   Example:
  37.   CALL insertTemporaryIds('18-20,78,238-240,282,289,294,316,350-351,396,472-474,507,521-2147483647');
  38.   SELECT * FROM tmp_ids;
  39. */
  40. DELIMITER $$
  41. DROP PROCEDURE IF EXISTS insertTemporaryIds$$
  42. CREATE PROCEDURE insertTemporaryIds(
  43. IN inIds TEXT
  44. ) READS SQL DATA
  45. BEGIN
  46. DECLARE myIndex, myCount INT;
  47. DECLARE myItem VARCHAR(32);
  48. DECLARE myStartId, myFinalId INT;
  49.  
  50. -- Create undeleted ids table
  51. CREATE /*TEMPORARY*/ TABLE IF NOT EXISTS tmp_ids (start_id INT, final_id INT);
  52. DELETE FROM tmp_ids;
  53.  
  54. -- Loop
  55. SET myIndex := 1;
  56. SET myCount := countItems(inIds, ',');
  57. WHILE myIndex <= myCount DO
  58. SET myItem := substringAt(inIds, ',', myIndex);
  59. SET myStartId := CAST(substringAt(myItem, '-', 1) AS UNSIGNED);
  60. IF countItems(myItem, '-') = 2 THEN
  61. SET myFinalId := CAST(substringAt(myItem, '-', 2) AS UNSIGNED);
  62. ELSE
  63. SET myFinalId := myStartId;
  64. END IF;
  65. INSERT INTO tmp_ids VALUES(myStartId, myFinalId);
  66. SET myIndex := myIndex + 1;
  67. END WHILE;
  68. END$$
  69. DELIMITER ;
  70.  

Proinf.net