source: main/trunk/model-sites-dev/eurovision-lod/collect/eurovision/prepare/errata-lod/local--countries-in-esc-by-year-in-1956--with-errata.sparql@ 35057

prefix skos:   <http://www.w3.org/2004/02/skos/core#>
prefix dct:    <http://purl.org/dc/terms/>
prefix xsd:    <http://www.w3.org/2001/XMLSchema#>

PREFIX rdf:    <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs:   <http://www.w3.org/2000/01/rdf-schema#>

prefix dbc:    <http://dbpedia.org/resource/Category:>
prefix dbr:    <http://dbpedia.org/resource/>
prefix dbp:    <http://dbpedia.org/property/>

PREFIX dbo:    <http://dbpedia.org/ontology/>

PREFIX prov:   <http://www.w3.org/ns/prov#>


SELECT ?countries_in_esc_by_year ?country_in_year (?year AS ?Year) (?country AS ?Country)
       ?entrant_robust (?entrant_label AS ?Creator) ?song_robust (?song_label AS ?Title)
       (?country_in_year_abstract_en AS ?Abstract_en)
       (?song_abstract_en AS ?TitleAbstract_en) (?entrant_abstract_en AS ?CreatorAbstract_en) 
       (?was_derived_from AS ?WikipediaURL)
WHERE {
  SERVICE <https://dbpedia.org/sparql> {
    ?countries_in_esc_by_year skos:broader dbc:Countries_in_the_Eurovision_Song_Contest_by_year.

    ?country_in_year dct:subject ?countries_in_esc_by_year.
## Useful example    
#       FILTER (?country_in_year = dbr:Bosnia_and_Herzegovina_in_the_Eurovision_Song_Contest_2010).
## Useful example    
#       FILTER (?country_in_year = dbr:Germany_in_the_Eurovision_Song_Contest_2010).
     
    bind( REPLACE(str(?country_in_year), ".*(\\d{4})", "$1") AS ?year).
    FILTER ( xsd:integer(?year) >= 1956 && xsd:integer(?year) <= 1956).

    ?country_in_year dbp:country ?country.

    ?country_in_year dbp:entrant ?entrant
      FILTER (?entrant != ""@en && ?entrant != dbr:Eurovision_Song_Contest_1985).
  }

  OPTIONAL {
    ?entrant_errata_uri dct:source ?entrant.
  }

  BIND (IF(isIRI(?entrant),?entrant,?entrant_errata_uri) AS ?entrant_robust).

  SERVICE <https://dbpedia.org/sparql> {

    ?country_in_year dbp:song ?song
      FILTER (?song != ""@en).
  }

  OPTIONAL {
    ?song_errata_uri dct:source ?song.
  }

  BIND (IF(isIRI(?song),?song,?song_errata_uri) AS ?song_robust).

  # Additional 1956 check to avoid cross-product
  SERVICE <https://dbpedia.org/sparql> {
    ?song_robust dbp:artist ?entrant_robust.
  }
  
  # Create more DL friendly entrant-title metadata from Wikipedia entrant-uri/label
  BIND(REPLACE(REPLACE(str(?entrant_robust), "^.*/(.*?)(_+\\(.*(singer|duo|band|group|musician)\\))?$", "$1"),"_"," ") AS ?entrant_label).

  # Create more DL friendly song-title metadata from Wikipedia song-uri/label
  BIND(REPLACE(REPLACE(str(?song_robust), "^.*/(.*?)(_+\\(.*song\\))?$", "$1"),"_"," ") AS ?song_label).

# DBpedia incorrectly maps some songs/entrants to integer numbers
# Most of these can be fixed with the usual errata approach,
# however there are a few examples that map to identical
# integer vals, and therefore are not unique.
#
# The chosen solution to address this is to include some
# bespoke filtering to explicitly rule in the song+entrant combination
# we want (i.e., the filter has the affect of ruling out the
# (incorrect) combination we don't want.

#dbr:Macedonia_in_the_Eurovision_Song_Contest_2000
# song: "100"^^xsd:integer
#   dbr:100%25_te_ljubam 
#   dbr:XXL_\(Macedonian_band\))
#
#Luxembourg_in_the_Eurovision_Song_Contest_1984
# song: "100"^^xsd:integer
#   dbr:100%25_d%27amour
#   dbr:Sophie_Carle

#dbr:Belarus_in_the_Eurovision_Song_Contest_2010
# entrant: "3"^^xsd:integer
#   dbr:3%2B2_\(band\)
#   dbr:Butterflies_\(3%2B2_song\)
#
#dbr:Netherlands_in_the_Eurovision_Song_Contest_2011
# entrant: "3"^^xsd:integer
#   dbr:3JS
#   dbr:Je_vecht_nooit_alleen
    
  FILTER(
    (?song != "100"^^xsd:integer && ?entrant != "3"^^xsd:integer)
    ||
    (?song = "100"^^xsd:integer
      && ?song_robust = dbr:100%25_te_ljubam && ?entrant = dbr:XXL_\(Macedonian_band\))
    ||
    (?song = "100"^^xsd:integer
      && ?song_robust = dbr:100%25_d%27amour && ?entrant = dbr:Sophie_Carle)
    ||
    (?entrant = "3"^^xsd:integer
      && ?entrant_robust = dbr:3\+2_\(band\) && ?song_robust = dbr:Butterflies_\(3\+2_song\))
    ||
    (?entrant = "3"^^xsd:integer
      && ?entrant_robust = dbr:3JS && ?song_robust = dbr:Je_vecht_nooit_alleen)
  ).

#      && ?entrant_robust = dbr:3%2B2_\(band\) && ?song_robust = dbr:Butterflies_\(3%2B2_song\))


  SERVICE <https://dbpedia.org/sparql> {

    OPTIONAL {
      ?song_robust prov:wasDerivedFrom ?was_derived_from.

      ?country_in_year dbo:abstract ?country_in_year_abstract_en
        FILTER (lang(?country_in_year_abstract_en) = 'en').

      ?entrant_robust dbo:abstract ?entrant_abstract_en
        FILTER (lang(?entrant_abstract_en) = 'en').

      ?song_robust dbo:abstract ?song_abstract_en
        FILTER (lang(?song_abstract_en) = 'en').

    }
  }
}
ORDER BY ASC(?countries_in_esc_by_year) ?country