User:Datumizer/List of role-playing video games
SPARQL
editI created these query scripts to help organize and update w:en:List of role-playing video games. The results are meant to be imported into Excel. Note that the queries need to be kept in sync with each other so that they always return the same number of values, and in the same sorting order. That means they need to be grouped by `?game` and ordered by `?dateYears` and `?gameENLabels`.
Query #1
editThe following query uses these:
- Items: Baldur's Gate III: The Black Hound (Q4850488) , Bob's Game (Q4931588) , Dunjonquest (Q5315330) , Deep Labyrinth (Q5250229) , Starflight (Q1462499) , God Wars: Future Past (Q22124593) , role-playing video game (Q744038) , tactical role-playing game (Q1529437) , action role-playing game (Q1422746) , roguelike (Q1143132) , video game (Q7889)
- Properties: genre (P136) , subclass of (P279) , instance of (P31) , country of origin (P495) , ISO 3166-1 alpha-2 code (P297) , revised Hepburn romanization (P2125) , Hanyu Pinyin transliteration (P1721) , publication date (P577)
# Script #1 # Should ideally print labels in all pertinent languanges, and include the language after the label. # E.g. "Super Mario Bros. (ENG)" SELECT # This section thanks to User:VIGNERON # (MIN(?dateYear) AS ?dateYears) # ( # CONCAT # ( # GROUP_CONCAT(DISTINCT ?gameENLabelTxt; separator = ", "), ", ", # GROUP_CONCAT(DISTINCT ?gameJALabelTxt; separator = ", "), ", ", # GROUP_CONCAT(DISTINCT ?gameHPLabelTxt; separator = ", "), ", ", # GROUP_CONCAT(DISTINCT ?gameZHLabelTxt; separator = ", "), ", ", # GROUP_CONCAT(DISTINCT ?gamePYLabelTxt; separator = ", ") # ) AS ?gameNames # ) # (GROUP_CONCAT(DISTINCT ?cooLabel; separator = ", ") AS ?cooLabels) # (?game AS ?dataLink) # This section is an alternate (MIN(?dateYear) AS ?dateYears) (GROUP_CONCAT(DISTINCT ?gameENLabel; separator = ", ") AS ?gameENLabels) # English label # (GROUP_CONCAT(DISTINCT ?gameNOLabel; separator = ", ") AS ?gameNOTxts) # All non-English labels, too many (GROUP_CONCAT(DISTINCT ?gameJALabel; separator = ", ") AS ?gameJALabels) # Japanese label (GROUP_CONCAT(DISTINCT ?gameHPLabel; separator = ", ") AS ?gameHPLabels) # Hepburn label # (GROUP_CONCAT(DISTINCT ?gameZHLabel; separator = ", ") AS ?gameZHLabels) # Mandarin label # (GROUP_CONCAT(DISTINCT ?gamePYLabel; separator = ", ") AS ?gamePYLabels) # Pinyin label (GROUP_CONCAT(DISTINCT ?cooLabel; separator = ", ") AS ?cooLabels) # (?game AS ?dataLink) WHERE { # hint:Query hint:optimizer "None". # test values # VALUES ?game # { # wd:Q4850488 # Baldur's Gate III: The Black Hound (should have "no value" due to being cancelled) # wd:Q4931588 # Bob's Game (should have "no value" due to not being released yet) # wd:Q5315330 # Dunjonquest (1979) # wd:Q5250229 # Deep Labyrinth (should have multiple dates) # wd:Q1462499 # Starflight (1986, has multiple genres) # wd:Q22124593 # God Wars: Beyond Time (should have blank date since the property does not exist) # } # types of item {?game wdt:P136 wd:Q744038} # regular RPGs UNION {?game wdt:P136 wd:Q1529437} # tactical RPGs UNION {?game wdt:P136 wd:Q1422746} # action RPGs UNION {?game wdt:P136 wd:Q1143132} # roguelikes # ?game wdt:P136/wdt:P279* wd:Q744038. # any class or subclass of role-playing video game, including MMORPGs ?game wdt:P31 wd:Q7889. # instance of video game #country of origin OPTIONAL { ?game wdt:P495 ?country. # country of origin ?country wdt:P297 ?cooLabel. # abbreviation } # English label OPTIONAL { ?game rdfs:label ?gameENLabel FILTER(LANG(?gameENLabel) = "en"). BIND(CONCAT(?gameENLabel, " (EN)") AS ?gameENLabelTxt) } # All non-English labels. Waaaay too many results! Many duplicates! # Ideally, we would only get non-English labels for games that originate from non-English speaking countries. And only get those languages, not others! # Only some dates are pertinent to en.wikipedia. # OPTIONAL # { # ?game rdfs:label ?gameNOLabel FILTER(LANG(?gameNOLabel) != "en"). # BIND(CONCAT(?gameNOLabel, " (", LANG(?gameNOLabel), ")") AS ?gameNOLabelTxt) # } # Japanese label OPTIONAL { ?game rdfs:label ?gameJALabel FILTER(LANG(?gameJALabel) = "ja") BIND(CONCAT(?gameJALabel, " (JA)") AS ?gameJALabelTxt) } # Japanese hepburn romanization OPTIONAL { ?game wdt:P2125 ?gameHPLabel BIND(CONCAT(?gameHPLabel, " (JA)") AS ?gameHPLabelTxt) } # Chinese label OPTIONAL { ?game rdfs:label ?gameZHLabel FILTER(LANG(?gameZHLabel) = "zh") BIND(CONCAT(?gameZHLabel, " (ZH)") AS ?gameZHLabelTxt) } # Chinese pinyin transliteration OPTIONAL { ?game wdt:P1721 ?gamePYLabel BIND(CONCAT(?gamePYLabel, " (ZH)") AS ?gamePYLabelTxt) } # release date (simple) OPTIONAL {?game wdt:P577 ?date BIND(YEAR(?date) AS ?dateYear)} # hepburn romanization and pinyin transliteration (is this still necessary?) # OPTIONAL {?game wdt:P2125 ?hepburn} # OPTIONAL {?game wdt:P1721 ?pinyin} # SERVICE wikibase:label # { # bd:serviceParam wikibase:language "en". # ?hepburn rdfs:label ?gameHPLabel. # ?pinyin rdfs:label ?gamePYLabel. # } } GROUP BY $game ORDER BY ASC (?dateYears) ASC (?gameENLabelTxts) #limit 100
Query #2
editThe following query uses these:
- Items: Baldur's Gate III: The Black Hound (Q4850488) , Bob's Game (Q4931588) , Dunjonquest (Q5315330) , Deep Labyrinth (Q5250229) , Starflight (Q1462499) , God Wars: Future Past (Q22124593) , role-playing video game (Q744038) , tactical role-playing game (Q1529437) , action role-playing game (Q1422746) , roguelike (Q1143132) , video game (Q7889) , video game genre (Q659563)
- Properties: genre (P136) , subclass of (P279) , instance of (P31) , publication date (P577) , developer (P178) , publisher (P123) , platform (P400) , part of the series (P179)
# Script #2 SELECT (MIN(?dateYear) AS ?dateYears) (GROUP_CONCAT(DISTINCT ?gameENLabelTxt; separator = ", ") AS ?gameENLabelTxts) # English label (GROUP_CONCAT(DISTINCT ?genreLabel; separator = ", ") AS ?gamGenreLabels) (GROUP_CONCAT(DISTINCT ?themeLabel; separator = ", ") AS ?ficGenreLabels) (GROUP_CONCAT(DISTINCT ?devLabel; separator = ", ") AS ?devLabels) (GROUP_CONCAT(DISTINCT ?pubLabel; separator = ", ") AS ?pubLabels) (GROUP_CONCAT(DISTINCT ?platLabel; separator = ", ") AS ?platLabels) (GROUP_CONCAT(DISTINCT ?seriesLabel; separator = ", ") AS ?seriesLabels) # (?game AS ?dataLink) WHERE { #hint:Query hint:optimizer "None". # test values # VALUES ?game # { # wd:Q4850488 # Baldur's Gate III: The Black Hound (should have "no value" due to being cancelled) # wd:Q4931588 # Bob's Game (should have "no value" due to not being released yet) # wd:Q5315330 # Dunjonquest (1979) # wd:Q5250229 # Deep Labyrinth (should have multiple dates) # wd:Q1462499 # Starflight (1986, has multiple genres) # wd:Q22124593 # God Wars: Beyond Time (should have blank date since the property does not exist) # } # types of item {?game wdt:P136 wd:Q744038} # regular RPGs UNION {?game wdt:P136 wd:Q1529437} # tactical RPGs UNION {?game wdt:P136 wd:Q1422746} # action RPGs UNION {?game wdt:P136 wd:Q1143132} # roguelikes # ?game wdt:P136/wdt:P279* wd:Q744038. # any class or subclass of role-playing video game, including MMORPGs ?game wdt:P31 wd:Q7889. # instance of video game # English label OPTIONAL { ?game rdfs:label ?gameENLabel FILTER(LANG(?gameENLabel) = "en"). BIND(CONCAT(?gameENLabel, " (EN)") AS ?gameENLabelTxt) } # release date (simple) OPTIONAL {?game wdt:P577 ?date BIND(YEAR(?date) AS ?dateYear)} # gameplay genres OPTIONAL { ?game wdt:P136 ?gameGenre. ?gameGenre wdt:P31 wd:Q659563. FILTER(?gameGenre != wd:Q744038). ?gameGenre rdfs:label ?gameplayGenreString. FILTER(LANG(?gameplayGenreString) = "en"). BIND(CONCAT(UCASE(SUBSTR(?gameplayGenreString, 1, 1)), SUBSTR(?gameplayGenreString, 2)) AS ?genreLabel). # makes the first character in the string upper case # BIND(STR(?gameplayGenreString) AS ?genreLabel). # faster substitute } # fiction genres OPTIONAL { ?game wdt:P136 ?otherGenre. MINUS {?otherGenre wdt:P31 wd:Q659563}. ?otherGenre rdfs:label ?fictionGenreString. FILTER(LANG(?fictionGenreString) = "en"). BIND(CONCAT(UCASE(SUBSTR(?fictionGenreString, 1, 1)), SUBSTR(?fictionGenreString, 2)) AS ?themeLabel). # makes the first character in the string upper case # BIND(STR(?fictionGenreString) AS ?themeLabel). # faster substitute } # developer, publisher, platform and series OPTIONAL {?game wdt:P178 ?developer} OPTIONAL {?game wdt:P123 ?publisher} OPTIONAL {?game wdt:P400 ?platform} OPTIONAL {?game wdt:P179 ?series} # labels SERVICE wikibase:label { bd:serviceParam wikibase:language "en". ?developer rdfs:label ?devLabel. ?publisher rdfs:label ?pubLabel. ?platform rdfs:label ?platLabel. ?series rdfs:label ?seriesLabel. } } GROUP BY $game ORDER BY ASC (?dateYears) ASC (?gameENLabelTxts) #limit 100
Query #3
editThe following query uses these:
- Items: Baldur's Gate III: The Black Hound (Q4850488) , Bob's Game (Q4931588) , Dunjonquest (Q5315330) , Deep Labyrinth (Q5250229) , Starflight (Q1462499) , God Wars: Future Past (Q22124593) , role-playing video game (Q744038) , tactical role-playing game (Q1529437) , action role-playing game (Q1422746) , roguelike (Q1143132) , video game (Q7889)
- Properties: genre (P136) , subclass of (P279) , instance of (P31) , publication date (P577) , official website (P856)
# Script #3 SELECT (MIN(?dateYear) AS ?dateYears) (GROUP_CONCAT(DISTINCT ?gameENLabelTxt; separator = ", ") AS ?gameENLabelTxts) # English label (GROUP_CONCAT(DISTINCT ?wikiName; separator = ", ") AS ?wikiNames) (GROUP_CONCAT(DISTINCT ?wikiHypr; separator = ", ") AS ?wikiHyprs) (GROUP_CONCAT(DISTINCT ?dataHypr; separator = ", ") AS ?dataHyprs) # (GROUP_CONCAT(DISTINCT ?websHypr; separator = ", ") AS ?websHyprs) # (?game AS ?dataLink) WHERE { # hint:Query hint:optimizer "None". # test values # VALUES ?game # { # wd:Q4850488 # Baldur's Gate III: The Black Hound (should have "no value" due to being cancelled) # wd:Q4931588 # Bob's Game (should have "no value" due to not being released yet) # wd:Q5315330 # Dunjonquest (1979) # wd:Q5250229 # Deep Labyrinth (should have multiple dates) # wd:Q1462499 # Starflight (1986, has multiple genres) # wd:Q22124593 # God Wars: Beyond Time (should have blank date since the property does not exist) # } # types of item {?game wdt:P136 wd:Q744038} # regular RPGs UNION {?game wdt:P136 wd:Q1529437} # tactical RPGs UNION {?game wdt:P136 wd:Q1422746} # action RPGs UNION {?game wdt:P136 wd:Q1143132} # roguelikes # ?game wdt:P136/wdt:P279* wd:Q744038. # any class or subclass of role-playing video game, including MMORPGs ?game wdt:P31 wd:Q7889. # instance of video game # English label OPTIONAL { ?game rdfs:label ?gameENLabel FILTER(LANG(?gameENLabel) = "en"). BIND(CONCAT(?gameENLabel, " (EN)") AS ?gameENLabelTxt) } # release date (simple) OPTIONAL {?game wdt:P577 ?date BIND(YEAR(?date) AS ?dateYear)} # wikidata link BIND(CONCAT("=hyperlink(\"", replace(replace(STR(?game), "entity", "wiki"), "http", "https"), "\")") AS ?dataHypr). # changes the target of the URL and creates a MS Excel compatible hyperlink # wikipedia link and wikidata name OPTIONAL { ?wikiURL schema:about ?game. ?wikiURL schema:isPartOf <https://en.wikipedia.org/>. ?wikiURL schema:name ?wikiName. BIND(CONCAT("=hyperlink(\"", STR(?wikiURL), "\")") AS ?wikiHypr). # creates a MS Excel compatible hyperlink #BIND(STR(?wikiURL) AS ?wikiHypr). # faster substitute } # offial website link OPTIONAL {?game wdt:P856 ?website BIND(CONCAT("=hyperlink(\"", STR(?website), "\")") AS ?websHypr)} } GROUP BY $game ORDER BY ASC (?dateYears) ASC (?gameENLabelTxts) #limit 100
Complex release date
editThe following query uses these:
- Properties: ISO 3166-1 alpha-2 code (P297) , publication date (P577) , place of publication (P291)
#release date (complex), thanks to User:Matěj Suchánek OPTIONAL { ?game p:P577 ?statement. ?statement ps:P577 ?date. OPTIONAL { ?statement pq:P291 ?place. # OPTIONAL {?place wdt:P297 ?place_label}. # can't do this, because there is no official two letter code for every region such as PAL OPTIONAL {?place rdfs:label ?place_label FILTER(LANG(?place_label) = 'en')}. }. BIND(STR(YEAR(?date)) AS ?dateYear). BIND(CONCAT(?dateYear, ' (', COALESCE(?place_label, '??'), ')') AS ?dateYear). }
Spreadsheet
editI've been using a spreadsheet to keep track of all the games. You can download it at this link. Note that it contains several macros to help clean the data.