SDA 2 Prototype (sda2p)

2009-10-24 11:10:15 am

guffaw

Quote from gia:

How many lines of code does this have? I dont feel many, but maybe I'm wrong, if you post the database design maybe people can add whatever you are missing, unless that's not your goal.

I'm not sure what to do about posting the source and db dump at the moment. One thing that is definitely the case is that I don't want to do all the work on this myself, so it would be nice to assemble a development team to work on it. However, I don't know if it's a good idea just to go ahead and make everything public; there are potential issues with doing that, and you can't put the proverbial cat back into the bag once it's done. I'm wondering what the other admins (particularly Radix) have to say about this.

As for code size, it's a few more than 5,000 lines at the moment. Some of the page layout code is quite complex, and of course it contains a BBcode parser implementation. There is an awful lot of commented out stuff in the source, though, so the real figure might be closer to 4,500. It certainly feels like there should be more than that, but I suppose a lot of stuff was repeatedly revised in a short period of time.

Quote from gia:

U+2934 ⤴ e2 a4 b4 ARROW POINTING RIGHTWARDS THEN CURVING UPWARDS
U+261B ☛ e2 98 9b BLACK RIGHT POINTING INDEX
U+261E ☞ e2 98 9e WHITE RIGHT POINTING INDEX
U+279A ➚ e2 9e 9a HEAVY NORTH EAST ARROW
U+2924 ⤤ e2 a4 a4 NORTH EAST ARROW WITH HOOK

They have promise as pointers, although the fingers are a bit corny.

Quote from gia:

U+2750 ❐ e2 9d 90 UPPER RIGHT DROP-SHADOWED WHITE SQUARE
U+2752 ❒ e2 9d 92 UPPER RIGHT SHADOWED WHITE SQUARE

I quite like those too; they convey something of the idea of linking to a video.

Quote from Arkarian:

rough idea for how drag and drop on run pages could be implemented.

It works better than I expected, although I only tried it in one browser. The playlist kind of gets in the way, though ... I definitely think I need to avoid displaying the playlist if there is only one video part. Not sure quite what to do about Flash in general though. It seems that Flash might be about to be made obsolete by the new <video> tag, which would eliminate the requirement to build up a separate XML playlist. This might be the way to go given that this project is a medium term rather than short term thing -- does anyone have any coding experience with <video> yet? It seems dumb to pour effort into getting the JW player to behave itself if it's about to be made obsolete. That's happened on this project several times already.

Quote from Manocheese:

Would it be possible to generate certain frequently used pages in advance? What I mean is that pages like the main game list and the runner list will be used fairly frequently, so instead of generating them each time a user asks for them, just generate them every time the site is updated. Then, when people request those pages, give them the pre-generated pages.

Yes, it's possible; this is in fact similar to how Enhasa's proposal would work (regenerate a static set of changed pages at update time). Even so, I think the problem with too much data being sent to the client applies to this sort of scheme as well, it's just less of a problem because providing the page to the browser requires less resources if it's just sending the thing over without having to build it up first. So I think pagination, or at least support for it, would eventually become desirable even under Radix's static HTML system.

The performance of various parts of the system under various conditions (specifically, the time taken for certain routines to execute) is being measured and stored in a database table as people test sda2p. On the whole, it seems to be providing more than adequate performance in the vast majority of cases, which is encouraging.

But I am hearing loud and clear the message that the current setup makes finding things difficult, so I will be looking at ways to improve this, and one of those ways will probably be an option to turn pagination off.

I'll wait a bit longer to give people more opportunity to express any opinions or ideas they may have, then I will start thinking about what the next step with this project might be.

[quote="gia"]
How many lines of code does this have? I dont feel many, but maybe I'm wrong, if you post the database design maybe people can add whatever you are missing, unless that's not your goal.[/quote]

I'm not sure what to do about posting the source and db dump at the moment. One thing that is definitely the case is that I don't want to do all the work on this myself, so it would be nice to assemble a development team to work on it. However, I don't know if it's a good idea just to go ahead and make everything public; there are potential issues with doing that, and you can't put the proverbial cat back into the bag once it's done. I'm wondering what the other admins (particularly Radix) have to say about this.

As for code size, it's a few more than 5,000 lines at the moment. Some of the page layout code is quite complex, and of course it contains a BBcode parser implementation. There is an awful lot of commented out stuff in the source, though, so the real figure might be closer to 4,500. It certainly feels like there should be more than that, but I suppose a lot of stuff was repeatedly revised in a short period of time.

[quote="gia"]
U+2934	⤴	e2 a4 b4	ARROW POINTING RIGHTWARDS THEN CURVING UPWARDS
U+261B	☛	e2 98 9b	BLACK RIGHT POINTING INDEX
U+261E	☞	e2 98 9e	WHITE RIGHT POINTING INDEX
U+279A	➚	e2 9e 9a	HEAVY NORTH EAST ARROW
U+2924	⤤	e2 a4 a4	NORTH EAST ARROW WITH HOOK
[/quote]

They have promise as pointers, although the fingers are a bit corny.

[quote="gia"]
U+2750	❐	e2 9d 90	UPPER RIGHT DROP-SHADOWED WHITE SQUARE
U+2752	❒	e2 9d 92	UPPER RIGHT SHADOWED WHITE SQUARE
[/quote]

I quite like those too; they convey something of the idea of linking to a video.

[quote="Arkarian"]
[url=http://samuel.arkarian.org/sda2p/run.html]rough idea for how drag and drop on run pages could be implemented.[/url]
[/quote]

It works better than I expected, although I only tried it in one browser. The playlist kind of gets in the way, though ... I definitely think I need to avoid displaying the playlist if there is only one video part. Not sure quite what to do about Flash in general though. It seems that Flash might be about to be made obsolete by the new <video> tag, which would eliminate the requirement to build up a separate XML playlist. This might be the way to go given that this project is a medium term rather than short term thing -- does anyone have any coding experience with <video> yet? It seems dumb to pour effort into getting the JW player to behave itself if it's about to be made obsolete. That's happened on this project several times already.

[quote="Manocheese"]
Would it be possible to generate certain frequently used pages in advance? What I mean is that pages like the main game list and the runner list will be used fairly frequently, so instead of generating them each time a user asks for them, just generate them every time the site is updated. Then, when people request those pages, give them the pre-generated pages.[/quote]

Yes, it's possible; this is in fact similar to how Enhasa's proposal would work (regenerate a static set of changed pages at update time). Even so, I think the problem with too much data being sent to the client applies to this sort of scheme as well, it's just less of a problem because providing the page to the browser requires less resources if it's just sending the thing over without having to build it up first. So I think pagination, or at least support for it, would eventually become desirable even under Radix's static HTML system.

The performance of various parts of the system under various conditions (specifically, the time taken for certain routines to execute) is being measured and stored in a database table as people test sda2p. On the whole, it seems to be providing more than adequate performance in the vast majority of cases, which is encouraging.

But I am hearing loud and clear the message that the current setup makes finding things difficult, so I will be looking at ways to improve this, and one of those ways will probably be an option to turn pagination off.

I'll wait a bit longer to give people more opportunity to express any opinions or ideas they may have, then I will start thinking about what the next step with this project might be.

Kabuto

2009-10-24 01:15:58 pm

Your script is broken: http://speeddemosarchive.com/sda2p/gamelist.php?sysfilter=on&start=0&system_id=19&sort=0 outputs a nice error message that gives some insight into your prototype :-) AFAIK you cannot use column aliases in the WHERE part of your statement. If you don't need the systemid_multi why not use something like this:

Code:

select
  games.game_id,
  games.catalogue_name_en_us,
  games.release_date,
  DATE_FORMAT(games.release_date,'%M %Y') as release_date_fmt,
  games.name_sort_order,
  games.gl_category_override,
  games.dup_time_format,
  games.dup_time_ms,
  game_systems.system_id,
  ( SELECT
      group_concat(systems.system_name separator ', ')
    FROM
      systems
      JOIN game_systems using (system_id)
    WHERE
      game_systems.game_id = games.game_id and
      games.update_id = 0 and
      game_systems.update_id  = 0
  ) as systemname_multi,
from
  games
  JOIN game_systems using (game_id)
where
  system_id = ? and
  games.update_id = 0 and
  game_systems.update_id = 0
order by
  name_sort_order
limit
  ?,?

Your script is broken: http://speeddemosarchive.com/sda2p/gamelist.php?sysfilter=on&start=0&system_id=19&sort=0 outputs a nice error message that gives some insight into your prototype :-) AFAIK you cannot use column aliases in the WHERE part of your statement. If you don't need the systemid_multi why not use something like this: [spoiler][code]select
  games.game_id,
  games.catalogue_name_en_us,
  games.release_date,
  DATE_FORMAT(games.release_date,'%M %Y') as release_date_fmt,
  games.name_sort_order,
  games.gl_category_override,
  games.dup_time_format,
  games.dup_time_ms,
  game_systems.system_id,
  ( SELECT
      group_concat(systems.system_name separator ', ')
    FROM
      systems
      JOIN game_systems using (system_id)
    WHERE
      game_systems.game_id = games.game_id and
      games.update_id = 0 and
      game_systems.update_id  = 0
  ) as systemname_multi,
from
  games
  JOIN game_systems using (game_id)
where
  system_id = ? and
  games.update_id = 0 and
  game_systems.update_id = 0
order by
  name_sort_order
limit
  ?,?[/code][/spoiler]

DJGrenola

2009-10-24 02:19:09 pm

guffaw

Quote from Kabuto:

Your script is broken: http://speeddemosarchive.com/sda2p/gamelist.php?sysfilter=on&start=0&system_id=19&sort=0 outputs a nice error message that gives some insight into your prototype :-) AFAIK you cannot use column aliases in the WHERE part of your statement. If you don't need the systemid_multi why not use something like this:

Code:

select
  games.game_id,
  games.catalogue_name_en_us,
  games.release_date,
  DATE_FORMAT(games.release_date,'%M %Y') as release_date_fmt,
  games.name_sort_order,
  games.gl_category_override,
  games.dup_time_format,
  games.dup_time_ms,
  game_systems.system_id,
  ( SELECT
      group_concat(systems.system_name separator ', ')
    FROM
      systems
      JOIN game_systems using (system_id)
    WHERE
      game_systems.game_id = games.game_id and
      games.update_id = 0 and
      game_systems.update_id  = 0
  ) as systemname_multi,
from
  games
  JOIN game_systems using (game_id)
where
  system_id = ? and
  games.update_id = 0 and
  game_systems.update_id = 0
order by
  name_sort_order
limit
  ?,?

This was already reported -- it was something that broke when I modified some of the database code, and then I didn't bother to test it afterwards. I know it leaks SQL to the user but I don't particularly care at this point. Thanks for reporting it though, it will be fixed whenever the next release is.

[quote="Kabuto"]
Your script is broken: http://speeddemosarchive.com/sda2p/gamelist.php?sysfilter=on&start=0&system_id=19&sort=0 outputs a nice error message that gives some insight into your prototype :-) AFAIK you cannot use column aliases in the WHERE part of your statement. If you don't need the systemid_multi why not use something like this: [spoiler][code]select
  games.game_id,
  games.catalogue_name_en_us,
  games.release_date,
  DATE_FORMAT(games.release_date,'%M %Y') as release_date_fmt,
  games.name_sort_order,
  games.gl_category_override,
  games.dup_time_format,
  games.dup_time_ms,
  game_systems.system_id,
  ( SELECT
      group_concat(systems.system_name separator ', ')
    FROM
      systems
      JOIN game_systems using (system_id)
    WHERE
      game_systems.game_id = games.game_id and
      games.update_id = 0 and
      game_systems.update_id  = 0
  ) as systemname_multi,
from
  games
  JOIN game_systems using (game_id)
where
  system_id = ? and
  games.update_id = 0 and
  game_systems.update_id = 0
order by
  name_sort_order
limit
  ?,?[/code][/spoiler]
[/quote]

This was already reported -- it was something that broke when I modified some of the database code, and then I didn't bother to test it afterwards. I know it leaks SQL to the user but I don't particularly care at this point. Thanks for reporting it though, it will be fixed whenever the next release is.

WMJ

2009-10-24 02:58:27 pm

Quote:

While I'm on the subject of the runner list: I know that I have the two Wouter Jansens listed in the database as a single person. If someone could clarify which runs were done by which Wouter, I will get this fixed.

My name is always listed as "Wouter M. "WMJ" Jansen" while for the other Wouter it's just "Wouter Jansen". I've done the speedruns for Star Wars Episode 1: Racer, Shadow Man European version and Wipeout 64.

arkarian

2009-10-24 03:02:28 pm

gamelogs.org

Quote from DJGrenola:

does anyone have any coding experience with <video> yet? It seems dumb to pour effort into getting the JW player to behave itself if it's about to be made obsolete. That's happened on this project several times already.

<video> will of course replace flash eventually, but i don't think it will be soon enough to not worry about getting jw player to work. iirc microsoft hasn't said anything about supporting the tag, and other browsers only support .ogg (which might or might not be a problem for sda, dunno). i think it'll be at least another 6 months to a year before <video> is widely supported.

other than the lack of support, it's great.

DJGrenola

2009-10-24 05:06:44 pm

guffaw

no mp4 then? lame.

arkarian

2009-10-24 07:10:36 pm

gamelogs.org

looks like apple and some others are supporting h.264 which would mean .mp4 could be used, but for now at least mozilla is only backing ogg theora (since it's free). i suspect they'll all start supporting one or the other soon but not soon enough for the purpose of sda2p.

DJGrenola

Edit history:

DJGrenola: 2009-10-28 09:20:03 am

2009-10-28 09:00:20 am

guffaw

It seems logical to concentrate on developing the admin interface that will allow sda2p to be updated, rather than on improving this demo. However, one of the ideas behind exhibiting the demo was to try to identify changes that would need to be made to the way the data is represented internally, and other low-level sda2p functions, since these changes will obviously have an impact on the implementation of the update interface.

Because of this, I think priority at this time will be given to the items in the list that have a significant impact on "core machinery". I haven't decided precisely how to proceed yet, but I'm leaning towards perhaps releasing an updated sda2p-r2 once these issues have been tackled, and then moving on to the admin interface. Purely cosmetic bugs are still not very important.

Anyway, here's my current bug list.

1. Bring data up to date.
2. Introduce support for cookies.
3. Improve CSS consistency; offer selectable stylesheets (SDA classic, and also a black-on-white alternative). Make inverted version of banner for the former.
4. Decide on target HTML/XHTML version, attempt to make bbcode parser W3C compliant.
5. [?] Implement fuzzy sdaname lookups via URL parameters, to make linking to things easier.
6. Improve debug mode.
7. Implement pictures; this can be done now the site is hosted on m2k2.
8. Finalise design for implementation of update mechanism and history mechanisms.
9. Abolish release systems. (Delete from db?)
10. Solve the no surname / no handle class of problems; allow sorting by surname or handle with fallback to the other if not present. Probably requires schema alterations and data duplication.
11. Make various X_from_db_row() functions into methods on the relevant classes; move the data classes into a separate include file.
12. Improve filename naming convention.
13. Improve PHP sanity checks.
14. Improve database column naming.
15. Clarify strategy for db localisation support.
16. Add runner bio display code.
17. Revise metacategories; patch data accordingly.
18. Introduce a fourth way of finding SDA content: listing by metacategory.
19. Investigate alternatives to "(go ->)" links.
20. Standardise time formatting.
21. Implement user-selectable date display format via cookie.
22. Add database fields to store f, d, bitrate and codec for all vidfiles.
23. Develop software to recover all f, d, bitrate, codec information from FDC, archive.org for all vidfiles on sda2p. See #22.
24. Improve main / supplementary part naming, remove unnecessary fold-up code for supp parts.
25. Add persistent option to disable flash player.
26. [?] Delete FLV from database.
27. Fix vertical spacing bugs in download lists.
28. Search SDA and archive for any missing auxiliary parts.
29. Allow creation of vidfiles that are not attached to a run.
30. Implement [vf] parser tag for vidfiles; see also #29, this will allow the IL levelset ZIP problem to be solved.
31. Implement caching mechanism for bbcode parse.
32. Improve IL table category extraction mechanism such that Max Payne 2 IL displays correctly.
33. Replace e-mail obfuscation hack with better solution.
34. Implement BitTorrent linking, including database fields that will be required for auto-rebuilding of torrents from run parts (for full runs) or IL category tuple (for IL runs). Consider to what degree an override mechanism may also be required for these tasks (IL in particular).
35. Decide upon IL playlist strategy, implement it.
36. Implement audio commentary.
37. Improve pagination strategy on systems.php, gamelist.php, person.php.
38. Investigate improved tooltip CSS.
39. Game titles on run pages should link to game pages.
40. Add "<- pick another level" link to IL run pages.
41. Only display playlist for runs with multiple parts (or IL, if IL category playlists are implemented -- see #35).
42. Try to improve the layout of run.php such that scrollable comments are "less lame". Consider in-page user control of the way comments are displayed (inline or scrollable, make scroll box size user-selectable? other options? fix flash player on right?)
43. Try to make all tooltips display "upwards", i.e. with their bottom edge relative to the tooltip position.
44. "Runner commentary" is confusing.
45. Make video flavour tooltips less confusing; see #22 and #23.
46. Fix OoT run system (any%, 100% -> GCN).
47. List mp4 before avi -- any other changes needed to the order in which flavours are displayed? schema change (sort_order)?
48. Review what is expanded by default and what is collapsed by default under various circumstances on run.php.
49. Fix "<- back" controls.
50. Find less verbose strategy for IL lists on system.php; size-dependent? Consider what to do about listing / linking to runs on this page.
51. Wouter Jansens.
52. LeCoureur SM64 70-star run.
53. Add some links to VLC somewhere.
54. Improve naming within source code; see also #14.
55. Investigate possibly incorrect system.php ordering (Zelda).
56. Write proper descriptions for global categories (there are currently seven of these: SS, 100%, death abuse, save warping, EU version, JP version, and scripts).

It seems logical to concentrate on developing the admin interface that will allow sda2p to be updated, rather than on improving this demo. However, one of the ideas behind exhibiting the demo was to try to identify changes that would need to be made to the way the data is represented internally, and other low-level sda2p functions, since these changes will obviously have an impact on the implementation of the update interface.

Because of this, I think priority at this time will be given to the items in the list that have a significant impact on "core machinery". I haven't decided precisely how to proceed yet, but I'm leaning towards perhaps releasing an updated sda2p-r2 once these issues have been tackled, and then moving on to the admin interface. Purely cosmetic bugs are still not very important.

Anyway, here's my current bug list.

1. Bring data up to date.
2. Introduce support for cookies.
3. Improve CSS consistency; offer selectable stylesheets (SDA classic, and also a black-on-white alternative). Make inverted version of banner for the former.
4. Decide on target HTML/XHTML version, attempt to make bbcode parser W3C compliant.
5. [?] Implement fuzzy sdaname lookups via URL parameters, to make linking to things easier.
6. Improve debug mode.
7. Implement pictures; this can be done now the site is hosted on m2k2.
8. Finalise design for implementation of update mechanism and history mechanisms.
9. Abolish release systems. (Delete from db?)
10. Solve the no surname / no handle class of problems; allow sorting by surname or handle with fallback to the other if not present. Probably requires schema alterations and data duplication.
11. Make various X_from_db_row() functions into methods on the relevant classes; move the data classes into a separate include file.
12. Improve filename naming convention.
13. Improve PHP sanity checks.
14. Improve database column naming.
15. Clarify strategy for db localisation support.
16. Add runner bio display code.
17. Revise metacategories; patch data accordingly.
18. Introduce a fourth way of finding SDA content: listing by metacategory.
19. Investigate alternatives to "(go ->)" links.
20. Standardise time formatting.
21. Implement user-selectable date display format via cookie.
22. Add database fields to store f, d, bitrate and codec for all vidfiles.
23. Develop software to recover all f, d, bitrate, codec information from FDC, archive.org for all vidfiles on sda2p. See #22.
24. Improve main / supplementary part naming, remove unnecessary fold-up code for supp parts.
25. Add persistent option to disable flash player.
26. [?] Delete FLV from database.
27. Fix vertical spacing bugs in download lists.
28. Search SDA and archive for any missing auxiliary parts.
29. Allow creation of vidfiles that are not attached to a run.
30. Implement [vf] parser tag for vidfiles; see also #29, this will allow the IL levelset ZIP problem to be solved.
31. Implement caching mechanism for bbcode parse.
32. Improve IL table category extraction mechanism such that Max Payne 2 IL displays correctly.
33. Replace e-mail obfuscation hack with better solution.
34. Implement BitTorrent linking, including database fields that will be required for auto-rebuilding of torrents from run parts (for full runs) or IL category tuple (for IL runs). Consider to what degree an override mechanism may also be required for these tasks (IL in particular).
35. Decide upon IL playlist strategy, implement it.
36. Implement audio commentary.
37. Improve pagination strategy on systems.php, gamelist.php, person.php.
38. Investigate improved tooltip CSS.
39. Game titles on run pages should link to game pages.
40. Add "<- pick another level" link to IL run pages.
41. Only display playlist for runs with multiple parts (or IL, if IL category playlists are implemented -- see #35).
42. Try to improve the layout of run.php such that scrollable comments are "less lame". Consider in-page user control of the way comments are displayed (inline or scrollable, make scroll box size user-selectable? other options? fix flash player on right?)
43. Try to make all tooltips display "upwards", i.e. with their bottom edge relative to the tooltip position.
44. "Runner commentary" is confusing.
45. Make video flavour tooltips less confusing; see #22 and #23.
46. Fix OoT run system (any%, 100% -> GCN).
47. List mp4 before avi -- any other changes needed to the order in which flavours are displayed? schema change (sort_order)?
48. Review what is expanded by default and what is collapsed by default under various circumstances on run.php.
49. Fix "<- back" controls.
50. Find less verbose strategy for IL lists on system.php; size-dependent? Consider what to do about listing / linking to runs on this page.
51. Wouter Jansens.
52. LeCoureur SM64 70-star run.
53. Add some links to VLC somewhere.
54. Improve naming within source code; see also #14.
55. Investigate possibly incorrect system.php ordering (Zelda).
56. Write proper descriptions for global categories (there are currently seven of these: SS, 100%, death abuse, save warping, EU version, JP version, and scripts).

DJGrenola

2009-10-28 07:13:03 pm

guffaw

I've just noticed that none of the HQ/IQ/LQ links are pointing to the correct files -- they all seem to point to the MQ. This used to work but it must have got broken, so I'll add that to the list ...

nate

2009-10-28 08:26:22 pm

you might spam the staff channel with stuff while you're working on this, especially involving the data updating. if i see that stuff and i have anything to comment on then i can catch you sometime and go over that. i have a lot of experience in that area now and i've been getting a lot more over the past few weeks. basically i've been thinking that it should be possible to construct a single application that brings in all the data starting from scratch. this way we can build/rebuild new structures at will. it shouldn't take three hours to run every time like the one i'm working on now either - we can use rsync to keep a copy of the site up to date on a fast volume (not m2k2) somewhere and then it shouldn't be but like 1-2 seconds to run. the only reason i'm not talking about just going ahead and doing this now is that it sounds like the way you conceptualize the data may change in the short term and so i'd rather start this with that already there if you know what i mean.

also, don't forget that fork may know something, as well.

you might spam the staff channel with stuff while you're working on this, especially involving the data updating. if i see that stuff and i have anything to comment on then i can catch you sometime and go over that. i have a lot of experience in that area now and i've been getting a lot more over the past few weeks. basically i've been thinking that it should be possible to construct a single application that brings in all the data starting from scratch. this way we can build/rebuild new structures at will. it shouldn't take three hours to run every time like the one i'm working on now either - we can use rsync to keep a copy of the site up to date on a fast volume (not m2k2) somewhere and then it shouldn't be but like 1-2 seconds to run. the only reason i'm not talking about just going ahead and doing this now is that it sounds like the way you conceptualize the data may change in the short term and so i'd rather start this with that already there if you know what i mean.

also, don't forget that fork may know something, as well.

DJGrenola

2009-10-29 04:43:23 pm

guffaw

It was done that way originally -- I had a local copy of SDA and the data was derived from that. It was easier to do it that way since I could patch certain brokennesses in SDA's HTML rather than having to come up with too many horrible hacks in the mining script.

Trouble is the categories -- there's really no way to work out from the HTML what the categories are, and things like what their metacategory should be, so the 380-odd categories all had to be added by manual inspection.

You could go through all the HTML and add some  or  style comments to everything, but nobody wants that job, do they?

moooh

2009-10-30 01:02:59 am

Exoray

You could spread that job out over several people. Just assign volunteers 10 pages each and I'm sure you'll have the job done in no time.
Of course you'd be needing some good way of distributing and collecting the pages as well as a clear definition of what id's and or category spelling to be used for consistency.

TheVoid

2009-10-30 02:51:55 am

Quote from DJGrenola:

but nobody wants that job, do they?

I volunteer. If you want a tedious job done, you can always ask me Wink

DJGrenola

Edit history:

DJGrenola: 2009-11-01 11:48:18 am

2009-11-01 11:11:24 am

guffaw

There'll be plenty of tedium for everyone later, I promise, but I'm not sure that adding categories to all the HTML is necessarily the way to go at this stage (especially as it would require cooperation from dex and Enhasa, which I probably won't get).

In order to try to get the database to 1.0, I am going to prioritise the following issues from the list. There are also a few other important things that I haven't written down yet.

8. Finalise design for implementation of update mechanism and history mechanisms.
9. Abolish release systems. (Delete from db?)
- actually, it would be worth keeping this data for a very few cases, namely those cases where a game is completely different from one system to another. I'd imagine there has been a game called "Batman" on just about every system ever released, for example. In this case, the release system becomes vital for unique identification of the game.
14. Improve database column naming.
15. Clarify strategy for db localisation support.
22. Add database fields to store f, d, bitrate and codec for all vidfiles.
23. Develop software to recover all f, d, bitrate, codec information from FDC, archive.org for all vidfiles on sda2p. See #22.
26. [?] Delete FLV from database.
29. Allow creation of vidfiles that are not attached to a run.
31. Implement caching mechanism for bbcode parse.
34. Implement BitTorrent linking, including database fields that will be required for auto-rebuilding of torrents from run parts (for full runs) or IL category tuple (for IL runs). Consider to what degree an override mechanism may also be required for these tasks (IL in particular).
47. List mp4 before avi -- any other changes needed to the order in which flavours are displayed? schema change (sort_order)?
55. Investigate possibly incorrect system.php ordering (Zelda).

These are the items that will have significant impact on the provisional 1.0 database, so I intend to tackle them first. I have been looking at #22 and #23 in particular since they are something of an auxiliary project that allows me time to think about how to tackle the other items. I have now recovered resolution, codec, framerate and bitrate information for something like 18,000 video files in sda2p's database, but some minority of these figures are clearly incorrect, so I'm not quite there with it yet. There is also the question of how (say) 20 potentially different entries for the vidfile data fields in a run's video flavour should be amalgamated together into one set of fields to display in the flavour tooltip. That will probably boil down to using mean, median, mode or range depending on the field, since displaying f/d/codec/abr/vbr independently for every single file almost certainly falls into the "too much information" class of problem.

I should add that the process of attempting to pull in all the codec information for all the files has provided an effective test of the link generating code, and a number of bugs have been fixed in my core stuff as a result of 404s and so on thrown up during the course of this work.

There'll be plenty of tedium for everyone later, I promise, but I'm not sure that adding categories to all the HTML is necessarily the way to go at this stage (especially as it would require cooperation from dex and Enhasa, which I probably won't get).

In order to try to get the database to 1.0, I am going to prioritise the following issues from the list. There are also a few other important things that I haven't written down yet.

8. Finalise design for implementation of update mechanism and history mechanisms.
9. Abolish release systems. (Delete from db?)
 - actually, it would be worth keeping this data for a very few cases, namely those cases where a game is completely different from one system to another. I'd imagine there has been a game called "Batman" on just about every system ever released, for example. In this case, the release system becomes vital for unique identification of the game.
14. Improve database column naming.
15. Clarify strategy for db localisation support.
22. Add database fields to store f, d, bitrate and codec for all vidfiles.
23. Develop software to recover all f, d, bitrate, codec information from FDC, archive.org for all vidfiles on sda2p. See #22.
26. [?] Delete FLV from database.
29. Allow creation of vidfiles that are not attached to a run.
31. Implement caching mechanism for bbcode parse.
34. Implement BitTorrent linking, including database fields that will be required for auto-rebuilding of torrents from run parts (for full runs) or IL category tuple (for IL runs). Consider to what degree an override mechanism may also be required for these tasks (IL in particular).
47. List mp4 before avi -- any other changes needed to the order in which flavours are displayed? schema change (sort_order)?
55. Investigate possibly incorrect system.php ordering (Zelda).

These are the items that will have significant impact on the provisional 1.0 database, so I intend to tackle them first. I have been looking at #22 and #23 in particular since they are something of an auxiliary project that allows me time to think about how to tackle the other items. I have now recovered resolution, codec, framerate and bitrate information for something like 18,000 video files in sda2p's database, but some minority of these figures are clearly incorrect, so I'm not quite there with it yet. There is also the question of how (say) 20 potentially different entries for the vidfile data fields in a run's video flavour should be amalgamated together into one set of fields to display in the flavour tooltip. That will probably boil down to using mean, median, mode or range depending on the field, since displaying f/d/codec/abr/vbr independently for every single file almost certainly falls into the "too much information" class of problem.

I should add that the process of attempting to pull in all the codec information for all the files has provided an effective test of the link generating code, and a number of bugs have been fixed in my core stuff as a result of 404s and so on thrown up during the course of this work.

DJGrenola

Edit history:

DJGrenola: 2009-11-03 07:49:28 pm

2009-11-03 07:30:17 pm

guffaw

In my quest to get accurate bitrate and other metadata from mp4 files stored on HTTP servers, I have written a tool called mp4www. Note that this is a PHP command-line application intended to be run using "php -f" and not a web application.

Given the URL of an mp4 video file, mp4www will use HTTP Keep-Alive and Range features to download only the sections of the mp4 file it needs to recover bitrate, width, height, creation and modification times, and frame rate.

Unfortunately, even though there is a "btrt" atom in some mp4 files which provides bitrate information, this cannot be relied upon to be correct. Hence the only way to get accurate rate data is to download and parse the "stsz" sample size tables, which are several megabytes in size for the very largest video files, and usually a few hundred kilobytes even for small ones. The tool is slower than I would have liked as a result, but still useful.

Since I didn't bother to write more than a very bare-bones HTTP client implementation, this may not work with some HTTP servers, particularly those which use chunked transfer-encoding. The software also makes various assumptions about the structure of the MP4 file which may well be incorrect in some cases. There are many other things here that could be improved a lot, but I think it is good enough for my purposes now.

If you encounter problems you might try increasing the default fragment size (currently 2K); there are also some other options in the source you can fiddle with.

mp4www-0.1.php.gz is attached; sample output follows.

Hope someone finds this useful. Patches are welcome.

---

Code:

$ php -f mp4www-0.1.php
mp4www by Alastair Campbell, v0.1
reads metadata from mp4 files over HTTP
Usage:
  php -f mp4www.php <url to mp4> [max tracks]

$ php -f mp4www-0.1.php http://www.archive.org/download/MegaManX8_SS_116/MegaManX8_SS_116_HQ.mp4
track 0:
  created: Tue Nov 21 20:22:10 2006
  modified: Thu Nov 23 00:09:12 2006
  bitrate: 2,047,976 bits per second
  type: video
  width: 704
  height: 576
  fps: 50.000
track 1:
  created: Thu Nov 23 00:09:01 2006
  modified: Thu Nov 23 00:09:12 2006
  bitrate: 129,187 bits per second
  type: audio
$

In my quest to get accurate bitrate and other metadata from mp4 files stored on HTTP servers, I have written a tool called mp4www. Note that this is a PHP [i]command-line[/i] application intended to be run using "php -f" and not a web application.

Given the URL of an mp4 video file, mp4www will use HTTP Keep-Alive and Range features to download only the sections of the mp4 file it needs to recover bitrate, width, height, creation and modification times, and frame rate.

Unfortunately, even though there is a "btrt" atom in some mp4 files which provides bitrate information, this cannot be relied upon to be correct. Hence the only way to get accurate rate data is to download and parse the "stsz" sample size tables, which are several megabytes in size for the very largest video files, and usually a few hundred kilobytes even for small ones. The tool is slower than I would have liked as a result, but still useful.

Since I didn't bother to write more than a very bare-bones HTTP client implementation, this may not work with some HTTP servers, particularly those which use chunked transfer-encoding. The software also makes various assumptions about the structure of the MP4 file which may well be incorrect in some cases. There are many other things here that could be improved a lot, but I think it is good enough for my purposes now.

If you encounter problems you might try increasing the default fragment size (currently 2K); there are also some other options in the source you can fiddle with.

mp4www-0.1.php.gz is attached; sample output follows.

Hope someone finds this useful. Patches are welcome.

---

[code]$ php -f mp4www-0.1.php
mp4www by Alastair Campbell, v0.1
reads metadata from mp4 files over HTTP
Usage:
  php -f mp4www.php <url to mp4> [max tracks]

$ php -f mp4www-0.1.php http://www.archive.org/download/MegaManX8_SS_116/MegaManX8_SS_116_HQ.mp4
track 0:
  created: Tue Nov 21 20:22:10 2006
  modified: Thu Nov 23 00:09:12 2006
  bitrate: 2,047,976 bits per second
  type: video
  width: 704
  height: 576
  fps: 50.000
track 1:
  created: Thu Nov 23 00:09:01 2006
  modified: Thu Nov 23 00:09:12 2006
  bitrate: 129,187 bits per second
  type: audio
$[/code]

Attachment:

mp4www-0.1.php.gz

Mercury.Com

2009-11-03 07:59:23 pm

Sea of Green

Makes me wish I knew PHP instead of Java so I could make something else useful with it. Are you going to use the program to populate a database with the video information? Because if so, I don't know how many lines of code it would take to write to a database table as well as outputting the information to the console. Could probably just do a simple transaction... but again, I'm thinking from a Java perspective, not PHP, so I'm not sure how you'd go about that.

arkarian

2009-11-04 01:23:23 am

gamelogs.org

Quote from MercuryZelda:

Makes me wish I knew PHP instead of Java so I could make something else useful with it.

i guess i don't understand this. just learn some php; it'll take you a few hours if that.

Mercury.Com

2009-11-04 05:02:40 am

Sea of Green

I should have written "as well as", not "instead of", but that's my fault. Anyway, learning PHP would be nice, but I don't have a lot of time to learn a new programming language what with working and also stuff I have to do at home. Maybe if I get free time some weekend I'll look into it, but it's not exactly high on my list of priorities.

Lag.Com

2009-11-04 07:52:30 am

sda loyalist

Don't worry, DJ is quite able to glom this information into his tables. Sweet script, DJ. So hacky. Smiley

DJGrenola

2010-01-11 06:22:55 pm

guffaw

This is just a note to say that no, I have by no means forgotten about this, but also no, I haven't done much more on it since November.

Things are a bit up in the air for me at the moment for various reasons, but most significantly that I am being turfed out of my current accommodation sometime in the next six months and therefore need to buy a house ASAP -- other stuff has to take second place. I hope to get back to work on sda2p soon, though.