<commit_before>-- TODO for the moment the case sentences has an audio but no owner is not handle --
-- TODO for the moment we don't make the difference between shtooka / from user audio --
--      so a "user" audio can be kept even if a shtooka audio exist, but as no audio are removed --
--      we should be able to handle manually this --
Delimiter | 

DROP PROCEDURE IF EXISTS erase_and_relink_duplicate_sentence |

CREATE PROCEDURE erase_and_relink_duplicate_sentence(IN duplicate_text_id INT(11), IN duplicate_lang VARCHAR(4) )
BEGIN

  DECLARE duplicate_text VARBINARY(2500);

  DECLARE done INT DEFAULT 0;
  DECLARE temp_id INT;


  DECLARE curseur_ids CURSOR FOR
        select id  from sentences
        where text = (
            select text from sentences where id = duplicate_text_id limit 1
        ) AND lang = duplicate_lang ;

  DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET duplicate_text_id = duplicate_text_id;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;



  OPEN curseur_ids ;

  REPEAT
    FETCH curseur_ids INTO temp_id;
    IF done = 0 THEN        select duplicate_text_id ;

        -- mettre  jour la relation   phrase/traduction --
      select 'update text -> translation' ;
      update sentences_translations
      set    sentence_id = duplicate_text_id
      where  sentence_id = temp_id;
      

      select 'update translation -> text' ;
      update sentences_translations
      set    translation_id  = duplicate_text_id
      where  translation_id  = temp_id;

        -- mettre  jour sentence annotation --
      select 'update text annotations' ;
      update sentence_annotations
      set    sentence_id  = duplicate_text_id
      where  sentence_id  = temp_id;

    -- mettre  jour sentence_comments --
      select 'update text comments' ;
      update sentence_comments
      set    sentence_id  = duplicate_text_id
      where  sentence_id  = temp_id;

      IF done = 1 THEN
        SET done = 0;
      END IF;

    -- mettre  jour relation phrase -> liste --
     select 'update text lists' ;
      update sentences_sentences_lists
      set    sentence_id  = duplicate_text_id
      where  sentence_id  = temp_id;

      IF done = 1 THEN
        SET done = 0;
      END IF;

    -- mettre  jour relation phrase -> liste --
     select 'update tags' ;
      update tags_sentences
      set    sentence_id  = duplicate_text_id
      where  sentence_id  = temp_id;
<commit_msg>re #291 added more check<commit_after>-- TODO for the moment the case sentences has an audio but no owner is not handle --
-- TODO for the moment we don't make the difference between shtooka / from user audio --
--      so a "user" audio can be kept even if a shtooka audio exist, but as no audio are removed --
--      we should be able to handle manually this --
Delimiter | 

DROP PROCEDURE IF EXISTS erase_and_relink_duplicate_sentence |

CREATE PROCEDURE erase_and_relink_duplicate_sentence(IN duplicate_text_id INT(11), IN duplicate_lang VARCHAR(4) )
BEGIN

  DECLARE duplicate_text VARBINARY(2500);

  DECLARE done INT DEFAULT 0;
  DECLARE temp_id INT;


  DECLARE curseur_ids CURSOR FOR
        select id  from sentences
        where text = (
            select text from sentences where id = duplicate_text_id limit 1
        ) AND lang = duplicate_lang ;

  DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET duplicate_text_id = duplicate_text_id;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;



  OPEN curseur_ids ;

  REPEAT
    FETCH curseur_ids INTO temp_id;
    IF done = 0 THEN
    
      select duplicate_text_id ;

        -- mettre  jour la relation   phrase/traduction --
      select 'update text -> translation' ;
      update sentences_translations
      set    sentence_id = duplicate_text_id
      where  sentence_id = temp_id;
 
      IF done = 1 THEN
        SET done = 0;
      END IF;

      select 'update translation -> text' ;
      update sentences_translations
      set    translation_id  = duplicate_text_id
      where  translation_id  = temp_id;

      IF done = 1 THEN
        SET done = 0;
      END IF;

        -- mettre  jour sentence annotation --
      select 'update text annotations' ;
      update sentence_annotations
      set    sentence_id  = duplicate_text_id
      where  sentence_id  = temp_id;

      IF done = 1 THEN
        SET done = 0;
      END IF;


    -- mettre  jour sentence_comments --
      select 'update text comments' ;
      update sentence_comments
      set    sentence_id  = duplicate_text_id
      where  sentence_id  = temp_id;

      IF done = 1 THEN
        SET done = 0;
      END IF;

    -- mettre  jour relation phrase -> liste --
     select 'update text lists' ;
      update sentences_sentences_lists
      set    sentence_id  = duplicate_text_id
      where  sentence_id  = temp_id;

      IF done = 1 THEN
        SET done = 0;
      END IF;

    -- mettre  jour relation phrase -> liste --
     select 'update tags' ;
      update tags_sentences
      set    sentence_id  = duplicate_text_id
      where  sentence_id  = temp_id;

{'fraction_non_alphanumeric': 0.05334645669291339, 'fraction_numerical': 0.011614173228346457, 'mean_word_length': 2.3693633952254642, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 11, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4662311', 'n_tokens_mistral': 1592, 'n_tokens_neox': 1433, 'n_words': 527}