Hard- and softsubs in *.ogm. [DONE]

[zaufany]

We have to do something with hard- and softsubs in *.ogm files. Some people hate hardsubs. It would be very nice to inform them that they are able to put subtitles off. I have few solutions.

Solution 0:
Let people find this information themselves.

Solution 1 [denied]:
It was about adding some additional languages: none/eng or eng(soft). It is too clumsy.

Solution 2:
Use 'Descr' field. It needn't any modification in the AniDB. Unfortunately it is clumsy too. Am I wrong?

Solution 3:
Add new kind files: '*.ogm softsub'. Isn't it clumsy too?

Solution 4:
Add a new field to the record: 'softsub'. This information should be displayed next to the flags. I have an icon for it.

[Elberet]

Solution 5: Change the database to create a n:n relation between languages and files. That way, each file can have as many dub/sub flags associated with it, and adding a flag/icon/whatever to indicate whether the subtitles are encoded into the video stream or are softsubs can easily be tracked in the relation table.

<random thoughts>
- remove the dub/sub fields from the files.
- new table dub-languages, containing int id, char name.
- new table file-dub-rel, containing int fid (file-id), int lid (language-id), enum type (dub,sub), enum mode (hard,soft).
- select ... from files as f left join file-dub-rel as r on f.id=r.fid left join dub-languages as l on r.lid=l.id where f.id=X order by r.type asc, r.mode, l.name;
</random thoughts>

[exp]

hm,

Elberet, your random thoughts would mean 300+ sql queries for some of the animes on anidb if the user clicks on expand all link.
i somehow dislike that thought }:o)
However i do see that it would allow some nice new options.

BYe!
EXP

[alaureijs]

or maybe dedicate one page explaining the fileformats presented in the dB. Like a quickguide to viewing pleasure or AniDB for dummies... Whichever You prefer.

Solution 0 suits me fine.

[Iceman[grrrr]]

I guess solution 2 is still better than 0...
People who care will put it there... well if they really care!

[Elberet]

Elberet, your random thoughts would mean 300+ sql queries for some of the animes on anidb if the user clicks on expand all link.

Hmmm... no.

One query joining files, episodes and languages can give you all languages for all files for all episodes of an anime. Just "order by episode.number asc, files.size desc, files.id asc". Then, load the first returned row. If the next row has the same file id, take it's language information and ignore the rest. If the file id differs, output the current file and continue to the next. The ordering in the SQL statement should make sure that nothing bad happens, even if two files have the exact same size (or whatever sorting criterium the user has selected).

For example, the DB query would return

Code: Select all

 files.id | ... | lang.type | lang.short
----------+-----+-----------+------------
 123      |     | dub       | jap
 123      |     | dub       | eng
 123      |     | sub       | eng
 456      |     | dub       | ger
 789      |     | dub       | jap
 789      |     | dub       | eng
 789      |     | dub       | ger
 789      |     | sub       | eng
 789      |     | sub       | ger

Instead of or additionally to lang.short, you can store the URL to an icon image, alt="" tag or label, a flag whether to use the icon image or a text tag, etc.

The query will use more memory, but shouldn't decrease performance noteably since the new languages table is not very complex and all columns involved in the join are unique indices.