Fix event original_language via manual mass edit

[saerdnaer]

UPDATE events SET original_language = 'eng' WHERE original_language = 'en';
UPDATE events SET original_language = 'deu' WHERE original_language = 'de';

UPDATE recordings SET language = 'eng-deu' WHERE filename LIKE '%-en-de-%' AND language != 'eng-deu';
UPDATE recordings SET language = 'deu-eng' WHERE filename LIKE '%-de-en-%' AND language != 'deu-eng' AND language != 'deu-eng-gsw';
UPDATE recordings SET language = 'deu' WHERE filename LIKE '%-de-%' AND filename NOT LIKE '%-en-%' AND language != 'deu';

UPDATE events e SET original_language = substring(r.language from '[a-z]+')
FROM  recordings r 
WHERE e.id = r.event_id AND r.language LIKE '%-%' and e.original_language != substring(r.language from '[a-z]+')

For reference

SELECT e.title, e.original_language, r.language, substring(r.language from '[a-z]+') as first_language
FROM events e, recordings r 
WHERE e.id = r.event_id AND r.language LIKE '%-%' and e.original_language != substring(r.language from '[a-z]+')
GROUP BY e.id, r.language;

[saerdnaer]

Viele events haben ggf. noch die falsche original language.

SELECT e.id, e.title, e.original_language, r.language, r.filename
FROM events e, recordings r
WHERE e.id = r.event_id 
  AND (SELECT COUNT(*) FROM recordings r2 WHERE r2.event_id=e.id AND r2.language LIKE '%-%' ) = 0 
  AND r.mime_type != 'application/x-subrip'
  AND substring(r.language from '[a-z]+') != e.original_language
GROUP BY e.id, r.language, r.filename

zum fixen (bitte nochmal überprüfen ob die auch das richtige macht):

UPDATE events e SET original_language = substring(r.language from '[a-z]+')
FROM recordings r
WHERE e.id = r.event_id 
  AND (SELECT COUNT(*) FROM recordings r2 WHERE r2.event_id=e.id AND r2.language LIKE '%-%' ) = 0 
  AND r.mime_type != 'application/x-subrip'
  AND substring(r.language from '[a-z]+') != e.original_language

bitte nochmal überprüfen ob die auch das richtige macht, ich hatte in meiner test db trotzdem noch die eintraege hier, die sich nicht updaten liesen

1430;"Lightning Talks - Day 3";"eng";"deu";"26c3-3644-de-lightning_talks_-_day_3.webm"
1430;"Lightning Talks - Day 3";"eng";"deu";"26c3-3644-de-lightning_talks_-_day_3.mp4"
3008;"Installserver für den Hackerspace";"deu";"eng";"installserver.opus"
1434;"secuBT";"eng";"deu";"26c3-3515-de-secubt.ogg"

[saerdnaer]

@derpeter Hat noch einen netten Testfall gefunden: https://media.ccc.de/v/30C3_-_5210_-_de_-_saal_g_-_201312282030_-_bullshit_made_in_germany_-_linus_neumann

Links der aktuelle Stand auf media.ccc.de, rechts der Stand nachdem die obigen Queries ausgeführt wurden. Ergebnis: Bis auf die Audio-Only Releases passt das...

TODOs

• [ ] Update Queries mit weniger Heuristik bauen
• [ ] Sonderfall Audio-Only releases in nur einer Sprache wie Testfall 5210 berücksichtigen