[SD] Correct broadcast_text_id in script_texts.

[newsbot]

Автор: ssj17vegeta

[SD] Correct broadcast_text_id in script_texts.



This commit "links" many of the script_texts entries to their matching text in broadcast_text, by setting broadcast_text_id, bumping the 332 previously set entries to 4420 (out of 4744).



It essentially fixes non-translated NPC dialogues and missing sounds, since translations are now stored in broadcast_text_locale and shouldn***39;t be set in content_locX fields anymore.



This was done by two ways :



1. Comparing script_texts.content_default and broadcast_text.Text, while stripping and replacing any punctuation differences (~90%) with queries like this :

```

SELECT entry, script_texts.content_default, broadcast_text.`Text`, broadcast_text.Id, broadcast_text_id FROM script_texts

INNER JOIN broadcast_text ON

LOWER(TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE  (REPLACE(REPLACE(LOWER(content_default),",", ***39;***39;), ***39; ***39;, ***39;***39;), ***39;.***39;, ***39;***39;), ***39;!***39;, ***39;***39;), ***39;\n***39;, ***39;***39;), "***39;ll", ***39;will***39;), ***39;-***39;, ***39;***39;))) =

LOWER(TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE  (REPLACE(REPLACE(LOWER(broadcast_text.Text),",", ***39;***39;), ***39; ***39;, ***39;***39;), ***39;.***39;, ***39;***39;), ***39;!***39;, ***39;***39;), ***39;\n***39;, ***39;***39;), "***39;ll", ***39;will***39;), ***39;-***39;, ***39;***39;)))

WHERE broadcast_text_id = 0 AND content_default <> ***39;***39; AND content_default NOT IN(***39;REUSE ME***39;, ***39;REUSE_ME***39;);

```



2. Finding manually (because of typos or missing words).



Values I didn***39;t find (324 of them) are left with broadcast_text_id = 0, for now.



WOTLK-DB only.

Подробнее...