User:Datumizer/List of role-playing video games

SPARQL

I 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

The 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)   , 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
  

Try it!

Query #2

The 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
  

Try it!

Query #3

The 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
  

Try it!

Complex release date

The 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).
  		}
  

Try it!

Spreadsheet

I'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.