4D551KA4

1Design Goals:
2As I’ve worked to get my various ROMs and systems working, it’s frustrated me that I have to keep re-scraping data that is already present, or should be. It’s unnecessarily time-consuming, and it probably wastes a lot of bandwidth for the sites being scraped. I believe you also mentioned something to this effect in one of your posts recently. I’ve tried to structure the database in a way that will allow maximum power and flexibility for the end user, while also providing a way for the community to more easily complete and “perfect” the data. This document is still very much a work in progress.
3
4I have not developed for the Pi before, so it’s possible (although I believe not likely) that the design specified below will be too much for the Pi to handle.
5
6Tables:
7Any field prefaced with a (?) indicates that I’m unsure if this field should be included, but I’m putting them here in order to foster thought and discussion.
8Systems Table:
9This table will contain data about all game systems. It can be fully pre-populated and will not require any scraping (by the end user), since EmulationStation/RetroArch/RetroPie support a limited number of systems, and this data should be fairly easy to acquire. We’ll also need to include art fields (system logo, system images, etc.) for them designers to reference.
10
11Fields:
12ID (integer; primary key)
13Name of system (text)
14Short name (text)
15Description (text)
16Year produced (integer)
17(?) # of games produced (integer)
18(?) # of units produced
19(?) System specs
20(?) etc...
21ROMs Table:
22This table will contain data about the ROMs in this particular collection (i.e. only the ROMs on the local disk(s)). This could become a very large table (I’ve got about 63,000 in my collection, and that’s growing. I’m sure others have much more than that.), so a redesign or optimization might be necessary.
23
24Fields:
25ID (integer; primary key)
26Hash (text or blob, foreign key to ROMData.Hash)
27This will uniquely identify all ROMs for all systems. It should be possible to tie this to game database sites and (with community and scraper assistance) uniquely catalog every ROM and variant ever produced.
28System (integer; foreign key to Systems.ID)
29Path (text)
30ROM fileName (text)
31Display name (text)
32(?) Parent ROM hash (text or blob)
33Could be useful for arcade roms and/or variants and modified roms
34Should this field go in the ROM metadata table instead?
35Tags Table:
36This will contain user-generated (and/or community-generated?) tags for individual games.
37
38Fields:
39ROM (text or blob, foreign key to ROMData.Hash)
40Tag (text) (might want to foreign key this or otherwise prevent things like case or formatting causing different tags: Racing vs racing, RPG vs RPGs, etc.)
41Enum_Category (integer, foreign key to Enumerable_Categories.ID)
42If this field is populated, it will tie an individual ROM to a user-editable tag category which can be easily filterable.
43
44Enumerable_Categories Table:
45This still requires some extra thought and design work, but this table comes from a sidebar discussion related to filtering. Essentially, in addition to tags, we’ll allow users to define enumerable tags/categories, so that when they add a tag, they can choose from one of the different options (and/or create new ones). This will also allow us to more easily define categories like genre. In addition, this table will be queried by the filtering system to automatically populate available filters based on user-created tags.
46
47Need to restrict this to that each key/value pair is unique, and duplicates are not allowed (i.e. if “Genre”, “Fighting” exists once, it is never inserted twice). Probably a good way to enforce this via SQL with multiple key columns. Will need to research. Same thing goes for the Tags table.
48
49Fields:
50ID (integer, primary key)
51Key (text)
52Value (text)
53
54
55ROM_Metadata Table:
56This table will ideally be contributed to by the community at large, and/or can be scraped from various websites and provided with the default install. It will contain metadata about each ROM. This will likely be (by far) the largest table, so further optimizations might be necessary (such as splitting out each system into its own table, or pulling out large text fields such as description into separate tables that can be queried and/or joined as necessary). Could also modify ES to update a remote database when a user fills in an empty field (would require user consent and cooperation with a games database).
57
58Fields:
59Hash (text or blob; primary key)
60Name (text)
61System (integer; foreign key to Systems.ID)
62Release date (integer?)
63Description (text)
64Genre (text)
65Might want to put genre in a table to use like an enum, and possibly have multiple genres allowable, such as Action and RPG, or Sci-Fi or Fantasy, etc. Could also achieve similar functionality with tags).
66Star_Rating (integer)
67This might be better in another table, but it’s worth thinking how to best handle a “community” star rating versus an individual system owner’s star rating. Keep both? Have one override the other?)
68Developer (integer?; foreign key to Developers.ID)
69Publisher (integer?; foreign key to Publisher.ID)
70Players (integer)
71Source (text)
72Where was the metadata scraped from?
73Source ID (text)
74Etag (or last updated, etc.)
75Play_History Table:
76This will contain a full log of every game played, and for how long. Useful for stats such as last played, total time played, etc. Since SQLite only has a few data types (http://www.sqlite.org/datatype3.html), I’m not sure what the most efficient way to handle DateTime type data is.
77
78Fields:
79ID (integer; primary key)
80ROM (foreign key to ROMS.ID or ROMS.Hash)
81System (integer; foreign key to Systems.ID)
82PlayStart (integer? text?)
83PlayEnd (integer? text?)
84(?) Duration (integer? real?)
85Media_Asset Table:
86This table will contain references (paths) to medial files (art and possibly audio/music) for ROMs (or game systems) on the local system. This will be referenced heavily by the theme and display system. Should also get input from theme designers as to what else they would like to have access to, and also look at various “scrape-ready” sites to see what is available. There might be many screenshots for a game, or many different fan art/backgrounds, and it could be nice to be able to open up a photo gallery to see them all. This talk, particularly what was done with the MegaMan collection might provide further context and ideas: https://youtu.be/HLWY7fCXUwE?t=1962 
87
88With regards to theming, I probably need to do some further thought into folder structure. Personally, I have all games for a system within a given folder, and all variants for a game within the same subfolder, so that I can easily see all variants. So I might have /snes/SuperMarioWorld/ and that folder might have several (or many) different versions. When I first drill into a particular game system from the root level, I’m only looking at folders, but when I drill into a particular folder, I see all ROMs for that particular game.
89
90Fields:
91ID (integer, primary key)
92Path (text)
93Originating URL (text)
94Etag (text)
95Asset type (integer)
96We’ll use the integer as an enum. Types can include:
97Screenshot
98Box art
99Media art (CD, cartridge, etc)
100Game logo
101Video
102Music
103Instruction manual
104Fan art
105Background
106Marquee
107Cabinet (for arcade machines)
108System logo
109Console
110Controller
111Poster
112Accessory
113
114Media_Association Table:
115This will allow us to do a many-to-many association between art assets and ROMs/Systems. Many ROMs might be able to share a set of screenshots, for example, but have different box arts (different regions). With this and the Media_Assets table, we can have galleries of all images associated with a particular ROM or system, sorted by category. See the MegaMan collection video above.
116
117Fields:
118Media_ID (integer)
119ROM_ID (integer)
120System_ID (integer)
121Developer_ID (integer)
122Publisher_ID (integer)
123Developer_Metadata Table:
124This table will contain metadata about particular game development studios (and publishers?). Useful for when someone wants to play all games by their favorite studio, or look at how the studio’s development style evolved over time. Need to do a bit more thinking about how to uniquely identify each developer/studio, such as different studios within Rockstar or Ubisoft, corporate mergers and acquisitions, etc. Should also have a Publisher table that will be essentially identical. Not sure if it’s best to combine or separate them, but I’m leaning towards having them be separate tables.
125
126Fields:
127ID (integer; primary key)
128Name (text)
129Date founded
130Date closed
131Description (text)
132(?) …
133
134Views:
135A view is essentially a stored SQL query that can be easily reused. The database will come pre-packaged with default views (one for every system, to preserve the current functionality), and it should be relatively straightforward to allow users to create their own queries/filters/etc and save those for easier access. Each view can be optionally displayed on the main system carousel screen (this will also affect theming), or just executed on command by accessing a menu somewhere.
136
137Filtering:
138So now that we’ve got the framework, what sorts of filtering options can/will we provide to the user, and how do we present them? Some users (myself included) use “GoodSets” that were generated using the GoodTools. The naming of the ROMs provides additional information: https://en.wikipedia.org/wiki/GoodTools#Good_codes. It would be nice to include all of these codes as default filters, or at least be able to create and save them as “frequently used filters”. I imagine the easiest way to add these filters is under the “select” menu where the current sort options are. This also raises the issue of subfolders. If I apply a filter (i.e. show only ROMs with a [!]), and a folder full of games does not include anything matching that filter, the two options I see are to display the folder, but then show “No ROMs match your filters” inside that folder (which I prefer, as it lets you know that your filter is the problem), or to simply not display the folder at all, which I think is less informative to the user, and is also a little more difficult from a development perspective.
139
140When searching and filtering, there should also the options of doing “contains”, “starts with”, and “ends with” searches. Case might also be a concern, but I’m thinking that searches should ignore case-sensitivity.
141
142And of course, we’ll be able to filter by genre, platform, date (made before, made after, made between), publisher, developer, etc. Basically any field in the ROM_Metadata table can be filtered individually. And since it’s just a SQL query, we can nest and sort them any way we want to. I’ve got a few ideas for how to present this in the UI so it’s intuitive, but we can discuss that later. The “easy mode” filters will be the big items previously discussed, while the “advanced mode” search can be something like “show me all NES or SNES platformer games that allow more than two players and have at least a 3 star rating, grouped by developer and then sorted in order of release date”.
143API info:
144We should probably have both a public API (which scraper authors can call into in order to create/edit/delete data) as well as a private API that EmulationStation will need in order to do filtering/searches/etc. This section is just a very rough sketch and needs a lot of fleshing out.
145Public API:
146CreateROM()
147Used when inserting data into the DB for a ROM that has never been scraped
148UpdateROM()
149Edit the the data for a ROM that already exists in the DB
150DeleteROM()
151Delete data for a ROM in the DB that no longer exists on disk
152ROMExists()
153Returns true if the ROM is in the DB
154ROMExistsOnDisk()
155Returns true of the ROM is on the file system
156CreatePublisher()
157CreateDeveloper()
158InsertMedia()
159Add media (art/video/music/etc) to the DB and filesystem
160UpdateMedia()
161Modify/update existing media
162DeleteMedia()
163Remove media from the DB and filesystem
164
165
166Private API:
167Needs thought...
168Additional things to consider:
169Data input/on-screen keyboard:
170I know many users would like an on-screen keyboard to be able to type in search terms. I also know at least one ES fork that has this (https://retropie.org.uk/forum/topic/3121/emulationstation-mod), but I have not personally tested it.
171
172Game manuals:
173I read a post on the forums that made an interesting point- what about game or system manuals? I haven’t seen a site that collects these and makes them available for scraping, but it would be nice to allow for this as well. The question is- what filetypes should be supported? Only images, or things like PDF as well? This should go in the Media_Asset table. Possible scrapable sites: http://www.replacementdocs.com/ , which seems to have mostly pdf files.
174
175Related Games:
176Some users on the forums have requested the ability to look at “related games”. I.e, the Mario games could be related to each other across systems, or could be related to Donkey Kong, or Sonic, etc. I’m not sure how best to handle this use case yet. Right now, I’m thinking this could be handled with tags.
177
178Kid-Friendly:
179I know Zigurana created the kid-friendly fork (https://github.com/retropie/retropie-setup/wiki/Child-friendly-EmulationStation), and it might be worth thinking about how to support that. Is a tag sufficient (i.e. “kid-friendly” or “adult-only”), or should the game metadata table also contain safety ratings from professional safety ratings agencies? If so, which one(s)? There are a variety of different safety rating systems (https://en.wikipedia.org/wiki/Video_game_content_rating_system) currently in place, and those systems have probably evolved considerably in the last few decades, when all of these “retro” games were first created. 
180
181Grid layout:
182Another thing to possibly consider is the grid layout theme (see link below for a fork containing this code, as well as a discussion about it). I know this is a popular request, but I have not really put any thought into how this will alter theming or what additional data or features will be required to make it work.
183https://retropie.org.uk/forum/topic/3566/grid-view-emulationstation-mod-ideas
184https://retropie.org.uk/forum/topic/3121/emulationstation-mod
185
186Dynamic Themes:
187Everything in this section is very much a “stretch goal” and might not be feasible. 
188
189One thing that I think would be nice to have is dynamic changes to UI elements. With the Art table above, we’ll have access to a much larger set of art assets than we currently do, so it might be nice to add some features/options for theme designers. Things such as 4-point-distort, translate, rotate, scale, fade in/out.
190
1914-point-distort: (i.e. take a rectangular image and do this with it: http://i.imgur.com/oISrXv4.gif?1, which allows for a square or rectangular screenshot image to be applied to an angled TV or arcade cabinet: http://www.bespoke-arcades.co.uk/wp-content/uploads/2014/03/black-evo-cabinet.jpg. Even better would be to be able to do this with video previews, but that might not be feasible given the limited power of the Pi. See also: https://www.codeproject.com/kb/graphics/ylscsfreetransform.aspx
192
193Other things including dynamic scaling (enlarge or shrink an image as the user scrolls through a game list), as well as dynamic rotation. With these three things, I think theme designers could simulate things like the popular AttractMode front-end. It would require some additional thought as to timing and speed of animations (and chaining multiple animations/effects together), and would certainly create a fair amount of complexity both in the codebase and the themes. Off the top of my head, we’d need triggers for “switch to”, “switch away”, and “select (or drill-in)”, and possibly “deselect (or drill-out)”. Each animation element would then have a “from” and “to”, with a simple linear interpolation between the two. More advanced interpolations could also be added.
194
195Also: https://retropie.org.uk/forum/topic/7156/variable-support-in-themes-in-emulationstation might be worth investigating, although I believe XML and XSLT already support variables to some extent.
196
197Overlays:
198See this thread: https://retropie.org.uk/forum/topic/7059/top-10-requested-arcade-games-for-overlays/41