Mercurial > hg > dbtune-site
view onto/doc/mapping.n3 @ 0:1e44d666ced1
Import site from parrot
| author | Chris Cannam |
|---|---|
| date | Fri, 13 Oct 2017 09:23:49 +0100 |
| parents | |
| children |
line wrap: on
line source
@prefix map: <file:///stdout#> . @prefix d2r: <http://sites.wiwiss.fu-berlin.de/suhl/bizer/d2r-server/config.rdf#> . @prefix rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> . @prefix rdfs: <http://www.w3.org/2000/01/rdf-schema#> . @prefix xsd: <http://www.w3.org/2001/XMLSchema#> . @prefix d2rq: <http://www.wiwiss.fu-berlin.de/suhl/bizer/D2RQ/0.1#> . @prefix xsd: <http://www.w3.org/2001/XMLSchema#>. @prefix foaf: <http://xmlns.com/foaf/0.1/>. @prefix vs: <http://www.w3.org/2003/06/sw-vocab-status/ns#>. @prefix tl: <http://purl.org/NET/c4dm/timeline.owl#>. @prefix event: <http://purl.org/NET/c4dm/event.owl#>. @prefix dc: <http://purl.org/dc/elements/1.1/>. @prefix geo: <http://www.w3.org/2003/01/geo/wgs84_pos#>. @prefix tags: <http://www.holygoat.co.uk/owl/redwood/0.1/tags/>. @prefix po: <http://purl.org/ontology/po/>. @prefix skos: <http://www.w3.org/2004/02/skos/core#>. @prefix owl: <http://www.w3.org/2002/07/owl#>. # # A D2R mapping for the /programmes database, # allowing to map this database to RDF designed # according to the Programme ontology available at # # http://purl.org/ontology/po/ # # This mapping is available under a Creative Commons # Attribution License (CCBY). # # Copyright (c) Yves Raimond, BBC, November 2007 # <> a d2r:Server; rdfs:label "PIPS RDF view"; d2r:port "2021"; d2r:documentMetadata [ rdfs:comment "This instance of a D2R server provides a RDF view of the PIPS database"; ]; . map:database a d2rq:Database; d2rq:jdbcDriver "com.mysql.jdbc.Driver"; d2rq:jdbcDSN "jdbc:mysql://localhost/pips"; d2rq:username "moustaki"; d2rq:resultSizeLimit "500"; # Just for my laptop to not die horribly . # Dropped from the database dump, for now: # * Versioning info # * Collections # * opt_out_url # * brands_occurence # * pip_formatid # * all ids (in URI) # * pip_genreid # * api_public_name # * is_publication_outlet # * pit_id # Mapping of the brands table # mysql> describe brands; # +-----------------+--------------+------+-----+---------+----------------+ # | Field | Type | Null | Key | Default | Extra | # +-----------------+--------------+------+-----+---------+----------------+ # | id | int(11) | NO | PRI | NULL | auto_increment | # | pid | varchar(8) | NO | UNI | | | # | title | varchar(255) | NO | | | | # | short_synopsis | text | YES | | NULL | | # | medium_synopsis | text | YES | | NULL | | # | long_synopsis | text | YES | | NULL | | # | occurrence | text | YES | | NULL | | # | service_id | int(11) | YES | MUL | NULL | | # | opt_out_url | varchar(255) | YES | | NULL | | # | created_at | datetime | NO | | | | # | updated_at | datetime | NO | | | | # +-----------------+--------------+------+-----+---------+----------------+ # To : # # po:Brand # rdfs:label # foaf:homepage # po:pid # dc:title # po:short_synopsis # po:medium_synopsis # po:long_synopsis # po:service # po:creation_date # po:update_date # map:brands a d2rq:ClassMap; d2rq:dataStorage map:database; d2rq:uriPattern "brand/@@brands.id@@"; d2rq:class po:Brand; . map:brands__label a d2rq:PropertyBridge; d2rq:belongsToClassMap map:brands; d2rq:property rdfs:label; d2rq:column "brands.title"; . map:brands_html a d2rq:PropertyBridge; d2rq:belongsToClassMap map:brands; d2rq:property foaf:homepage; d2rq:uriPattern "http://www.bbc.co.uk/programmes/@@brands.pid@@"; . map:brands_pid a d2rq:PropertyBridge; d2rq:belongsToClassMap map:brands; d2rq:property po:pid; d2rq:column "brands.pid"; . map:brands_title a d2rq:PropertyBridge; d2rq:belongsToClassMap map:brands; d2rq:property dc:title; d2rq:column "brands.title"; . map:brands_short_synopsis a d2rq:PropertyBridge; d2rq:belongsToClassMap map:brands; d2rq:property po:short_synopsis; d2rq:column "brands.short_synopsis"; . map:brands_medium_synopsis a d2rq:PropertyBridge; d2rq:belongsToClassMap map:brands; d2rq:property po:medium_synopsis; d2rq:column "brands.medium_synopsis"; . map:brands_long_synopsis a d2rq:PropertyBridge; d2rq:belongsToClassMap map:brands; d2rq:property po:long_synopsis; d2rq:column "brands.long_synopsis"; . map:brands_service_id a d2rq:PropertyBridge; d2rq:belongsToClassMap map:brands; d2rq:property po:service; d2rq:refersToClassMap map:services; d2rq:join "brands.service_id = services.id"; . map:brands_created_at a d2rq:PropertyBridge; d2rq:belongsToClassMap map:brands; d2rq:property po:creation_date; d2rq:column "brands.created_at"; d2rq:datatype xsd:dateTime; d2rq:condition "brands.created_at != '0000'"; . map:brands_updated_at a d2rq:PropertyBridge; d2rq:belongsToClassMap map:brands; d2rq:property po:update_date; d2rq:column "brands.updated_at"; d2rq:datatype xsd:dateTime; d2rq:condition "brands.updated_at != '0000'"; . # Mapping the table brand_titles # mysql> describe brand_titles; # +-------------+--------------+------+-----+---------+----------------+ # | Field | Type | Null | Key | Default | Extra | # +-------------+--------------+------+-----+---------+----------------+ # | id | int(11) | NO | PRI | NULL | auto_increment | # | brand_id | int(11) | NO | MUL | 0 | | # | title | varchar(255) | NO | | | | # | slice_title | varchar(255) | NO | MUL | | | # | letter | char(1) | NO | MUL | | | # +-------------+--------------+------+-----+---------+----------------+ # To: # # po:Brand # po:sort_title # po:slice_title # po:letter # map:brand_titles_title a d2rq:PropertyBridge; d2rq:belongsToClassMap map:brands; d2rq:property po:sort_title; d2rq:column "brand_titles.title"; d2rq:join "brand_titles.brand_id = brands.id"; . map:brand_titles_slice_title a d2rq:PropertyBridge; d2rq:belongsToClassMap map:brands; d2rq:property po:slice_title; d2rq:column "brand_titles.slice_title"; d2rq:join "brand_titles.brand_id = brands.id"; . map:brand_titles_letter a d2rq:PropertyBridge; d2rq:belongsToClassMap map:brands; d2rq:property po:letter; d2rq:column "brand_titles.letter"; d2rq:join "brand_titles.brand_id = brands.id"; . # Mapping the table collections # mysql> describe collections; # +----------------+---------+------+-----+---------+----------------+ # | Field | Type | Null | Key | Default | Extra | # +----------------+---------+------+-----+---------+----------------+ # | id | int(11) | NO | PRI | NULL | auto_increment | # | episode_id | int(11) | NO | UNI | 0 | | # | brand_id | int(11) | YES | MUL | NULL | | # | series_id | int(11) | YES | MUL | NULL | | # | base_series_id | int(11) | YES | MUL | NULL | | # +----------------+---------+------+-----+---------+----------------+ # To : # # po:Brand # po:episode # po:Series # po:episode # po:Brand # po:series # TODO: # * is that right to consider base_series_id as being implicit # in the series hierarchy? map:brand_collection_episode a d2rq:PropertyBridge; d2rq:belongsToClassMap map:brands; d2rq:property po:episode; d2rq:uriPattern "episode/@@collections.episode_id@@"; d2rq:join "collections.brand_id = brands.id"; . map:series_collection_episode a d2rq:PropertyBridge; d2rq:belongsToClassMap map:series; d2rq:property po:episode; d2rq:uriPattern "episode/@@collections.episode_id@@"; d2rq:join "collections.series_id = series.id"; . map:brands_collection_series a d2rq:PropertyBridge; d2rq:belongsToClassMap map:brands; d2rq:property po:series; d2rq:uriPattern "series/@@collections.series_id@@"; d2rq:join "collections.brand_id = brands.id"; . map:series_collection_baseseries a d2rq:PropertyBridge; d2rq:belongsToClassMap map:series; d2rq:property po:episode; d2rq:uriPattern "episode/@@collections.episode_id@@"; d2rq:join "collections.base_series_id = series.id"; . # Mapping the table broadcasts # mysql> describe broadcasts; # +---------------+--------------+------+-----+---------------------+----------------+ # | Field | Type | Null | Key | Default | Extra | # +---------------+--------------+------+-----+---------------------+----------------+ # | id | int(11) | NO | PRI | NULL | auto_increment | # | start | datetime | NO | MUL | 0000-00-00 00:00:00 | | # | end | datetime | NO | MUL | 0000-00-00 00:00:00 | | # | duration | int(11) | NO | | 0 | | # | is_repeat | tinyint(1) | YES | | NULL | | # | schedule_date | date | NO | | 0000-00-00 | | # | version_id | int(11) | NO | MUL | 0 | | # | service_id | int(11) | NO | MUL | 0 | | # | pid_imi | varchar(255) | NO | UNI | | | # | created_at | datetime | NO | | | | # | updated_at | datetime | NO | | | | # +---------------+--------------+------+-----+---------------------+----------------+ # To: # po:Broadcast # rdfs:label # event:time # po:schedule_date # po:broadcast_of # po:broadcasted_on # po:pid_imi # po:creation_date # po:update_date # tl:Interval # tl:start # tl:end # tl:duration # po:FirstBroadcast # po:RepeatBroadcast # TODO: # * Should the schedule date be a factor of the broadcast event? map:broadcasts a d2rq:ClassMap; d2rq:dataStorage map:database; d2rq:uriPattern "broadcast/@@broadcasts.id@@"; d2rq:class po:Broadcast; . map:broadcasts__label a d2rq:PropertyBridge; d2rq:belongsToClassMap map:broadcasts; d2rq:property rdfs:label; d2rq:pattern "broadcasts #@@broadcasts.id@@"; # <<<<<<<< . map:broadcast_event_time a d2rq:PropertyBridge; d2rq:belongsToClassMap map:broadcasts; d2rq:property event:time; d2rq:uriPattern "time/@@broadcasts.id@@"; # <<<<<<<<<<<<<< PROBLEM . map:broadcast_time a d2rq:ClassMap; d2rq:dataStorage map:database; d2rq:uriPattern "time/@@broadcasts.id@@"; d2rq:class tl:Interval; . map:broadcasts_start a d2rq:PropertyBridge; d2rq:belongsToClassMap map:broadcast_time; d2rq:property tl:start; d2rq:column "broadcasts.start"; d2rq:datatype xsd:dateTime; d2rq:condition "broadcasts.start != '0000'"; . map:broadcasts_end a d2rq:PropertyBridge; d2rq:belongsToClassMap map:broadcast_time; d2rq:property tl:end; d2rq:column "broadcasts.end"; d2rq:datatype xsd:dateTime; d2rq:condition "broadcasts.end != '0000'"; . map:broadcasts_duration a d2rq:PropertyBridge; d2rq:belongsToClassMap map:broadcast_time; d2rq:property tl:duration; d2rq:column "broadcasts.duration"; d2rq:datatype xsd:int; . map:first_broadcast a d2rq:ClassMap; d2rq:dataStorage map:database; d2rq:uriPattern "broadcast/@@broadcasts.id@@"; d2rq:class po:FirstBroadcast; d2rq:condition "broadcasts.is_repeat = '0'"; . map:repeated_broadcast a d2rq:ClassMap; d2rq:dataStorage map:database; d2rq:uriPattern "broadcast/@@broadcasts.id@@"; d2rq:class po:RepeatBroadcast; d2rq:condition "broadcasts.is_repeat = '1'"; . map:broadcasts_schedule_date a d2rq:PropertyBridge; d2rq:belongsToClassMap map:broadcasts; d2rq:property po:schedule_date; d2rq:column "broadcasts.schedule_date"; d2rq:datatype xsd:date; d2rq:condition "broadcasts.schedule_date != '0000'"; . map:broadcasts_version_id a d2rq:PropertyBridge; d2rq:belongsToClassMap map:broadcasts; d2rq:property po:broadcast_of; d2rq:refersToClassMap map:versions; d2rq:join "broadcasts.version_id = versions.id"; . map:broadcasts_service_id a d2rq:PropertyBridge; d2rq:belongsToClassMap map:broadcasts; d2rq:property po:broadcasted_on; d2rq:refersToClassMap map:services; d2rq:join "broadcasts.service_id = services.id"; . map:broadcasts_pid_imi a d2rq:PropertyBridge; d2rq:belongsToClassMap map:broadcasts; d2rq:property po:pid_imi; d2rq:column "broadcasts.pid_imi"; . map:broadcasts_created_at a d2rq:PropertyBridge; d2rq:belongsToClassMap map:broadcasts; d2rq:property po:creation_date; d2rq:column "broadcasts.created_at"; d2rq:datatype xsd:dateTime; d2rq:condition "broadcasts.created_at != '0000'"; . map:broadcasts_updated_at a d2rq:PropertyBridge; d2rq:belongsToClassMap map:broadcasts; d2rq:property po:update_date; d2rq:column "broadcasts.updated_at"; d2rq:datatype xsd:dateTime; d2rq:condition "broadcasts.updated_at != '0000'"; . # Mapping the table episodes # mysql> describe episodes; # +-----------------+--------------+------+-----+---------+----------------+ # | Field | Type | Null | Key | Default | Extra | # +-----------------+--------------+------+-----+---------+----------------+ # | id | int(11) | NO | PRI | NULL | auto_increment | # | title | varchar(255) | NO | | | | # | short_synopsis | text | YES | | NULL | | # | medium_synopsis | text | YES | | NULL | | # | long_synopsis | text | YES | | NULL | | # | pid | varchar(8) | NO | UNI | | | # | position | int(11) | YES | | NULL | | # | service_id | int(11) | YES | MUL | NULL | | # | opt_out_url | varchar(255) | YES | | NULL | | # | created_at | datetime | NO | | | | # | updated_at | datetime | NO | | | | # +-----------------+--------------+------+-----+---------+----------------+ # To : # po:Episode # rdfs:label # foaf:homepage # dc:title # po:short_synopsis # po:medium_synopsis # po:long_synopsis # po:pid # po:position # po:service # po:creation_date # po:update_date map:episodes a d2rq:ClassMap; d2rq:dataStorage map:database; d2rq:uriPattern "episode/@@episodes.id@@"; d2rq:class po:Episode; . map:episodes__label a d2rq:PropertyBridge; d2rq:belongsToClassMap map:episodes; d2rq:property rdfs:label; d2rq:column "episodes.title"; . map:episodes_html a d2rq:PropertyBridge; d2rq:belongsToClassMap map:episodes; d2rq:property foaf:homepage; d2rq:uriPattern "http://www.bbc.co.uk/programmes/@@episodes.pid@@"; . map:episodes_title a d2rq:PropertyBridge; d2rq:belongsToClassMap map:episodes; d2rq:property dc:title; d2rq:column "episodes.title"; . map:episodes_short_synopsis a d2rq:PropertyBridge; d2rq:belongsToClassMap map:episodes; d2rq:property po:short_synopsis; d2rq:column "episodes.short_synopsis"; . map:episodes_medium_synopsis a d2rq:PropertyBridge; d2rq:belongsToClassMap map:episodes; d2rq:property po:medium_synopsis; d2rq:column "episodes.medium_synopsis"; . map:episodes_long_synopsis a d2rq:PropertyBridge; d2rq:belongsToClassMap map:episodes; d2rq:property po:long_synopsis; d2rq:column "episodes.long_synopsis"; . map:episodes_pid a d2rq:PropertyBridge; d2rq:belongsToClassMap map:episodes; d2rq:property po:pid; d2rq:column "episodes.pid"; . map:episodes_position a d2rq:PropertyBridge; d2rq:belongsToClassMap map:episodes; d2rq:property po:position; d2rq:column "episodes.position"; d2rq:datatype xsd:int; . map:episodes_service_id a d2rq:PropertyBridge; d2rq:belongsToClassMap map:episodes; d2rq:property po:service; d2rq:refersToClassMap map:services; d2rq:join "episodes.service_id = services.id"; . map:episodes_created_at a d2rq:PropertyBridge; d2rq:belongsToClassMap map:episodes; d2rq:property po:creation_date; d2rq:column "episodes.created_at"; d2rq:datatype xsd:dateTime; d2rq:condition "episodes.created_at != '0000'"; . map:episodes_updated_at a d2rq:PropertyBridge; d2rq:belongsToClassMap map:episodes; d2rq:property po:update_date; d2rq:column "episodes.updated_at"; d2rq:datatype xsd:dateTime; d2rq:condition "episodes.updated_at != '0000'"; . # Mapping the table episode_title # mysql> describe episode_titles; # +-------------+--------------+------+-----+---------+----------------+ # | Field | Type | Null | Key | Default | Extra | # +-------------+--------------+------+-----+---------+----------------+ # | id | int(11) | NO | PRI | NULL | auto_increment | # | episode_id | int(11) | NO | MUL | 0 | | # | title | varchar(255) | NO | | | | # | slice_title | varchar(255) | NO | MUL | | | # | letter | char(1) | NO | MUL | | | # +-------------+--------------+------+-----+---------+----------------+ # To: # po:Episode # po:sort_title # po:slice_title # po:letter map:episode_titles_title a d2rq:PropertyBridge; d2rq:belongsToClassMap map:episodes; d2rq:property po:sort_title; d2rq:column "episode_titles.title"; d2rq:join "episode_titles.episode_id = episodes.id"; . map:episode_titles_slice_title a d2rq:PropertyBridge; d2rq:belongsToClassMap map:episodes; d2rq:property po:slice_title; d2rq:column "episode_titles.slice_title"; d2rq:join "episode_titles.episode_id = episodes.id"; . map:episode_titles_letter a d2rq:PropertyBridge; d2rq:belongsToClassMap map:episodes; d2rq:property po:letter; d2rq:column "episode_titles.letter"; d2rq:join "episode_titles.episode_id = episodes.id"; . # Mapping the table rpps_links (start/end date?) # mysql> describe rpps_links; # +-----------------+-------------+------+-----+---------+----------------+ # | Field | Type | Null | Key | Default | Extra | # +-----------------+-------------+------+-----+---------+----------------+ # | id | int(11) | NO | PRI | NULL | auto_increment | # | episode_id | int(11) | NO | UNI | | | # | rpps_id | int(11) | NO | UNI | | | # | network_key | varchar(32) | NO | MUL | | | # | file_key | varchar(64) | NO | | | | # | available_start | datetime | NO | | | | # | available_end | datetime | YES | | NULL | | # +-----------------+-------------+------+-----+---------+----------------+ # To : # po:Episode # po:available_as # TODO: # * Dealing with start/end date? # * It should surely be attached to a particular version!!! map:episode_rpps a d2rq:PropertyBridge; d2rq:belongsToClassMap map:episodes; d2rq:property po:available_as; d2rq:uriPattern "http://www.bbc.co.uk/radio/aod/@@rpps_links.network_key@@_aod.shtml?@@rpps_links.network_key@@/@@rpps_links.file_key@@"; d2rq:join "rpps_links.episode_id = episodes.id"; . # Mapping the table formats # mysql> describe formats; # +--------------+--------------+------+-----+---------+----------------+ # | Field | Type | Null | Key | Default | Extra | # +--------------+--------------+------+-----+---------+----------------+ # | id | int(11) | NO | PRI | NULL | auto_increment | # | title | varchar(255) | NO | | | | # | url_key | varchar(255) | NO | UNI | | | # | pip_formatid | varchar(8) | NO | UNI | | | # +--------------+--------------+------+-----+---------+----------------+ # To : # po:Format # rdfs:label # dc:title # foaf:homepage map:formats a d2rq:ClassMap; d2rq:dataStorage map:database; d2rq:uriPattern "format/@@formats.id@@"; d2rq:class po:Format; . map:formats__label a d2rq:PropertyBridge; d2rq:belongsToClassMap map:formats; d2rq:property rdfs:label; d2rq:column "formats.title"; . map:formats_title a d2rq:PropertyBridge; d2rq:belongsToClassMap map:formats; d2rq:property dc:title; d2rq:column "formats.title"; . map:formats_html a d2rq:PropertyBridge; d2rq:belongsToClassMap map:formats; d2rq:property foaf:homepage; d2rq:uriPattern "http://www.bbc.co.uk/programmes/formats/@@formats.url_key@@"; . # mapping the table formats_versions # mysql> describe formats_versions; # +------------+---------+------+-----+---------+-------+ # | Field | Type | Null | Key | Default | Extra | # +------------+---------+------+-----+---------+-------+ # | version_id | int(11) | NO | PRI | 0 | | # | format_id | int(11) | NO | PRI | 0 | | # +------------+---------+------+-----+---------+-------+ # To : # po:Version # po:format map:formats_versions a d2rq:PropertyBridge; d2rq:belongsToClassMap map:versions; d2rq:property po:format; d2rq:refersToClassMap map:formats; d2rq:join "formats.id = formats_versions.format_id"; d2rq:join "formats_versions.version_id = versions.id"; . # Mapping the table genres # mysql> describe genres; # +-----------------+--------------+------+-----+---------+----------------+ # | Field | Type | Null | Key | Default | Extra | # +-----------------+--------------+------+-----+---------+----------------+ # | id | int(11) | NO | PRI | NULL | auto_increment | # | parent_genre_id | int(11) | YES | MUL | NULL | | # | title | varchar(255) | NO | | | | # | url_key | varchar(255) | NO | MUL | | | # | pip_genreid | varchar(8) | NO | UNI | | | # +-----------------+--------------+------+-----+---------+----------------+ # To : # skos:Concept # rdfs:label # skos:inScheme # skos:narrower # dc:title # foaf:homepage map:genres a d2rq:ClassMap; d2rq:dataStorage map:database; d2rq:uriPattern "genre/@@genres.id@@"; d2rq:class skos:Concept; . map:genres_inscheme a d2rq:PropertyBridge; d2rq:belongsToClassMap map:genres; d2rq:property skos:inScheme; d2rq:uriPattern "http://purl.org/ontology/po/Genre"; . map:genres__label a d2rq:PropertyBridge; d2rq:belongsToClassMap map:genres; d2rq:property rdfs:label; d2rq:column "genres.title"; . map:genres_html a d2rq:PropertyBridge; d2rq:belongsToClassMap map:genres; d2rq:property foaf:homepage; d2rq:uriPattern "http://www.bbc.co.uk/programmes/genres/@@genres.url_key@@"; . map:genres_parent_genre_id a d2rq:PropertyBridge; d2rq:belongsToClassMap map:genres; d2rq:property skos:narrower; d2rq:refersToClassMap map:genres; d2rq:alias "genres AS genres__alias"; d2rq:join "genres__alias.parent_genre_id = genres.id"; . map:genres_title a d2rq:PropertyBridge; d2rq:belongsToClassMap map:genres; d2rq:property dc:title; d2rq:column "genres.title"; . # Mapping the table genres_versions # mysql> describe genres_versions; # +------------+---------+------+-----+---------+----------------+ # | Field | Type | Null | Key | Default | Extra | # +------------+---------+------+-----+---------+----------------+ # | id | int(11) | NO | PRI | NULL | auto_increment | # | version_id | int(11) | NO | MUL | 0 | | # | genre_id | int(11) | NO | MUL | 0 | | # | genre1_id | int(11) | NO | MUL | 0 | | # | genre2_id | int(11) | YES | MUL | NULL | | # | genre3_id | int(11) | YES | MUL | NULL | | # +------------+---------+------+-----+---------+----------------+ # To : # po:Version # po:genre # TODO: # * Different `genre' properties for each degree of association # * Should be linked to episode, instead of genre? map:genres_versions_genre_1 a d2rq:PropertyBridge; # <<<<< there should be different genre properties d2rq:belongsToClassMap map:versions; d2rq:property po:genre; d2rq:refersToClassMap map:genres; d2rq:join "genres_versions.genre_id = genres.id"; d2rq:join "genres_versions.version_id = versions.id"; . map:genres_versions_genre1_id a d2rq:PropertyBridge; d2rq:belongsToClassMap map:versions; d2rq:property po:genre; d2rq:refersToClassMap map:genres; d2rq:join "genres_versions.genre1_id = genres.id"; d2rq:join "genres_versions.version_id = versions.id"; . map:genres_versions_genre2_id a d2rq:PropertyBridge; d2rq:belongsToClassMap map:versions; d2rq:property po:genre; d2rq:refersToClassMap map:genres; d2rq:join "genres_versions.genre2_id = genres.id"; d2rq:join "genres_versions.version_id = versions.id"; . map:genres_versions_genre3_id a d2rq:PropertyBridge; d2rq:belongsToClassMap map:versions; d2rq:property po:genre; d2rq:refersToClassMap map:genres; d2rq:join "genres_versions.genre3_id = genres.id"; d2rq:join "genres_versions.version_id = versions.id"; . # Mapping the table series # mysql> describe series; # +------------------+--------------+------+-----+---------+----------------+ # | Field | Type | Null | Key | Default | Extra | # +------------------+--------------+------+-----+---------+----------------+ # | id | int(11) | NO | PRI | NULL | auto_increment | # | parent_series_id | int(11) | YES | MUL | NULL | | # | pid | varchar(8) | NO | UNI | | | # | title | varchar(255) | NO | | | | # | short_synopsis | text | YES | | NULL | | # | medium_synopsis | text | YES | | NULL | | # | long_synopsis | text | YES | | NULL | | # | position | int(11) | YES | | NULL | | # | service_id | int(11) | YES | MUL | NULL | | # | opt_out_url | varchar(255) | YES | | NULL | | # | created_at | datetime | NO | | | | # | updated_at | datetime | NO | | | | # +------------------+--------------+------+-----+---------+----------------+ # To : # po:Series # rdfs:label # po:series # po:pid # dc:title # po:short_synopsis # po:medium_synopsis # po:long_synopsis # po:position # po:service # po:creation_date # po:update_date # TODO: # * Filter out synopsis that do have an empty value (true for all other concepts # holding such synopsis) map:series a d2rq:ClassMap; d2rq:dataStorage map:database; d2rq:uriPattern "series/@@series.id@@"; d2rq:class po:Series; . map:series__label a d2rq:PropertyBridge; d2rq:belongsToClassMap map:series; d2rq:property rdfs:label; d2rq:column "series.title"; . map:series_html a d2rq:PropertyBridge; d2rq:belongsToClassMap map:series; d2rq:property foaf:homepage; d2rq:uriPattern "http://www.bbc.co.uk/programmes/@@series.pid@@"; . map:series_parent_series_id a d2rq:PropertyBridge; d2rq:belongsToClassMap map:series; d2rq:property po:series; d2rq:refersToClassMap map:series; d2rq:alias "series AS series__alias"; d2rq:join "series.id = series__alias.parent_series_id"; . map:series_pid a d2rq:PropertyBridge; d2rq:belongsToClassMap map:series; d2rq:property po:pid; d2rq:column "series.pid"; . map:series_title a d2rq:PropertyBridge; d2rq:belongsToClassMap map:series; d2rq:property dc:title; d2rq:column "series.title"; . map:series_short_synopsis a d2rq:PropertyBridge; d2rq:belongsToClassMap map:series; d2rq:property po:short_synopsis; d2rq:column "series.short_synopsis"; . map:series_medium_synopsis a d2rq:PropertyBridge; # <<< condition on existence d2rq:belongsToClassMap map:series; d2rq:property po:medium_synopsis; d2rq:column "series.medium_synopsis"; . map:series_long_synopsis a d2rq:PropertyBridge; d2rq:belongsToClassMap map:series; d2rq:property po:long_synopsis; d2rq:column "series.long_synopsis"; . map:series_position a d2rq:PropertyBridge; d2rq:belongsToClassMap map:series; d2rq:property po:position; d2rq:column "series.position"; d2rq:datatype xsd:int; . map:series_service_id a d2rq:PropertyBridge; d2rq:belongsToClassMap map:series; d2rq:property po:service; d2rq:refersToClassMap map:services; d2rq:join "series.service_id = services.id"; . map:series_created_at a d2rq:PropertyBridge; d2rq:belongsToClassMap map:series; d2rq:property po:creation_date; d2rq:column "series.created_at"; d2rq:datatype xsd:dateTime; d2rq:condition "series.created_at != '0000'"; . map:series_updated_at a d2rq:PropertyBridge; d2rq:belongsToClassMap map:series; d2rq:property po:update_date; d2rq:column "series.updated_at"; d2rq:datatype xsd:dateTime; d2rq:condition "series.updated_at != '0000'"; . # Mapping the table series_titles # mysql> describe series_titles; # +-------------+--------------+------+-----+---------+----------------+ # | Field | Type | Null | Key | Default | Extra | # +-------------+--------------+------+-----+---------+----------------+ # | id | int(11) | NO | PRI | NULL | auto_increment | # | series_id | int(11) | NO | MUL | 0 | | # | title | varchar(255) | NO | | | | # | slice_title | varchar(255) | NO | MUL | | | # | letter | char(1) | NO | MUL | | | # +-------------+--------------+------+-----+---------+----------------+ # To : # po:Series # po:sort_title # po:slice_title # po:letter map:series_titles_title a d2rq:PropertyBridge; d2rq:belongsToClassMap map:series; d2rq:property po:sort_title; d2rq:column "series_titles.title"; d2rq:join "series_titles.series_id = series.id"; . map:series_titles_slice_title a d2rq:PropertyBridge; d2rq:belongsToClassMap map:series; d2rq:property po:slice_title; d2rq:column "series_titles.slice_title"; d2rq:join "series_titles.series_id = series.id"; . map:series_titles_letter a d2rq:PropertyBridge; d2rq:belongsToClassMap map:series; d2rq:property po:letter; d2rq:column "series_titles.letter"; d2rq:join "series_titles.series_id = series.id"; . # Mapping the table services # mysql> describe services; # +-----------------------+-------------------------------------------------------------------------------------------+------+-----+---------+----------------+ # | Field | Type | Null | Key | Default | Extra | # +-----------------------+-------------------------------------------------------------------------------------------+------+-----+---------+----------------+ # | id | int(11) | NO | PRI | NULL | auto_increment | # | parent_service_id | int(11) | YES | MUL | NULL | | # | title | varchar(64) | NO | | | | # | api_public_name | varchar(32) | NO | UNI | | | # | url_key | varchar(32) | NO | MUL | | | # | outlet_url_key | varchar(32) | YES | MUL | NULL | | # | is_publication_outlet | tinyint(4) | NO | | 1 | | # | region | varchar(64) | YES | | NULL | | # | service_type | enum('Local Radio','National Radio','Regional Radio','TV','Web Only','Master Brand Only') | YES | | NULL | | # +-----------------------+-------------------------------------------------------------------------------------------+------+-----+---------+----------------+ # To: # po:Service # rdfs:label # po:parent_service # dc:title # po:location # po:TV # po:LocalRadio # po:RegionalRadio # po:NationalRadio # po:Web # po:MasterBrand # TODO: # * All services are self-contained - this might not be a real issue, though # * Sort out foaf:homepage map:services a d2rq:ClassMap; d2rq:dataStorage map:database; d2rq:uriPattern "service/@@services.id@@"; d2rq:class po:Service; . map:services__label a d2rq:PropertyBridge; d2rq:belongsToClassMap map:services; d2rq:property rdfs:label; d2rq:column "services.title"; . #map:services_html a d2rq:PropertyBridge; # d2rq:belongsToClassMap map:services; # d2rq:property foaf:homepage; # d2rq:uriPattern "http://www.bbc.co.uk/programmes/services/@@services.url_key@@"; # . map:services_parent_service_id a d2rq:PropertyBridge; # <<<<< Why are all services self-contained?? d2rq:belongsToClassMap map:services; d2rq:property po:parent_service; d2rq:refersToClassMap map:services; d2rq:alias "services AS services__alias"; d2rq:join "services.parent_service_id = services__alias.id"; . map:services_title a d2rq:PropertyBridge; d2rq:belongsToClassMap map:services; d2rq:property dc:title; d2rq:column "services.title"; . map:services_region a d2rq:PropertyBridge; # <<<<<<< d2rq:belongsToClassMap map:services; d2rq:property po:location; d2rq:translateWith map:location_table; d2rq:uriColumn "services.region"; . map:tv_service a d2rq:ClassMap; d2rq:dataStorage map:database; d2rq:uriPattern "service/@@services.id@@"; d2rq:class po:TV; d2rq:condition "services.service_type = 'TV'"; . map:local_service a d2rq:ClassMap; d2rq:dataStorage map:database; d2rq:uriPattern "service/@@services.id@@"; d2rq:class po:LocalRadio; d2rq:condition "services.service_type = 'Local Radio'"; . map:regional_service a d2rq:ClassMap; d2rq:dataStorage map:database; d2rq:uriPattern "service/@@services.id@@"; d2rq:class po:RegionalRadio; d2rq:condition "services.service_type = 'Regional Radio'"; . map:national_service a d2rq:ClassMap; d2rq:dataStorage map:database; d2rq:uriPattern "service/@@services.id@@"; d2rq:class po:NationalRadio; d2rq:condition "services.service_type = 'National Radio'"; . map:web_service a d2rq:ClassMap; d2rq:dataStorage map:database; d2rq:uriPattern "service/@@services.id@@"; d2rq:class po:Web; d2rq:condition "services.service_type = 'Web Only'"; . map:master_service a d2rq:ClassMap; # <<<< ? d2rq:dataStorage map:database; d2rq:uriPattern "service/@@services.id@@"; d2rq:class po:MasterBrand; d2rq:condition "services.service_type = 'Master Brand Only'"; . # Table for location mapping map:location_table a d2rq:TranslationTable; d2rq:translation [ d2rq:databaseValue "All regions"; d2rq:rdfValue <http://sws.geonames.org/2635167/>; ]; d2rq:translation [ d2rq:databaseValue "All English Regions"; d2rq:rdfValue <http://sws.geonames.org/2635167/>; #?? ]; d2rq:translation [ d2rq:databaseValue "Northern Ireland"; d2rq:rdfValue <http://sws.geonames.org/2635167/>; ]; d2rq:translation [ d2rq:databaseValue "Wales"; d2rq:rdfValue <http://sws.geonames.org/2635167/>; ]; d2rq:translation [ d2rq:databaseValue "South West"; d2rq:rdfValue <http://sws.geonames.org/2637294/>; #South West peninsula? ]; d2rq:translation [ d2rq:databaseValue "West Midlands"; d2rq:rdfValue <http://sws.geonames.org/2634343/>; ]; d2rq:translation [ d2rq:databaseValue "East Midlands"; d2rq:rdfValue <http://sws.geonames.org/6296613/>; ]; d2rq:translation [ d2rq:databaseValue "North East & Cumbria"; d2rq:rdfValue <http://sws.geonames.org/2651712/>; ]; d2rq:translation [ d2rq:databaseValue "Scotland"; d2rq:rdfValue <http://sws.geonames.org/2638360/>; ]; d2rq:translation [ d2rq:databaseValue "Yorkshire"; d2rq:rdfValue <http://sws.geonames.org/2641209/>; ]; d2rq:translation [ d2rq:databaseValue "Oxfordshire"; d2rq:rdfValue <http://sws.geonames.org/2640726/>; ]; d2rq:translation [ d2rq:databaseValue "South East"; d2rq:rdfValue <http://sws.geonames.org/2637438/>; ]; d2rq:translation [ d2rq:databaseValue "Cambridgeshire"; d2rq:rdfValue <http://sws.geonames.org/2653940/>; ]; d2rq:translation [ d2rq:databaseValue "Channel Islands"; d2rq:rdfValue <http://sws.geonames.org/3042400/>; ]; d2rq:translation [ d2rq:databaseValue "East Yorkshire & Lincolnshire"; d2rq:rdfValue <http://sws.geonames.org/2644486/>; ]; d2rq:translation [ d2rq:databaseValue "Northern Ireland"; d2rq:rdfValue <http://sws.geonames.org/2641364/>; ]; d2rq:translation [ d2rq:databaseValue "London"; d2rq:rdfValue <http://sws.geonames.org/2643744/>; ]; . # Mapping the table taggings # mysql> describe taggings; # +------------+----------+------+-----+---------------------+----------------+ # | Field | Type | Null | Key | Default | Extra | # +------------+----------+------+-----+---------------------+----------------+ # | id | int(11) | NO | PRI | NULL | auto_increment | # | episode_id | int(11) | NO | MUL | 0 | | # | tag_id | int(11) | NO | MUL | 0 | | # | created | datetime | NO | | 0000-00-00 00:00:00 | | # | pit_id | int(11) | NO | UNI | 0 | | # +------------+----------+------+-----+---------------------+----------------+ # To: # tags:Tagging # rdfs:label # tags:tag # tags:associatedTag # tags:taggedOn map:taggings a d2rq:ClassMap; d2rq:dataStorage map:database; d2rq:uriPattern "tagging/@@taggings.id@@"; d2rq:class tags:Tagging; . map:taggings__label a d2rq:PropertyBridge; d2rq:belongsToClassMap map:taggings; d2rq:property rdfs:label; d2rq:pattern "taggings #@@taggings.id@@"; . map:taggings_episode_id a d2rq:PropertyBridge; d2rq:belongsToClassMap map:episodes; d2rq:property tags:tag; d2rq:refersToClassMap map:taggings; d2rq:join "taggings.episode_id = episodes.id"; . map:taggings_tag_id a d2rq:PropertyBridge; d2rq:belongsToClassMap map:taggings; d2rq:property tags:associatedTag; d2rq:refersToClassMap map:tags; d2rq:join "taggings.tag_id = tags.id"; . map:taggings_created a d2rq:PropertyBridge; d2rq:belongsToClassMap map:taggings; d2rq:property tags:taggedOn; d2rq:column "taggings.created"; d2rq:datatype xsd:dateTime; d2rq:condition "taggings.created != '0000'"; . # Mapping the table tags # mysql> describe tags; # +---------+--------------+------+-----+---------+----------------+ # | Field | Type | Null | Key | Default | Extra | # +---------+--------------+------+-----+---------+----------------+ # | id | int(11) | NO | PRI | NULL | auto_increment | # | title | varchar(255) | NO | | | | # | url_key | varchar(255) | NO | MUL | | | # | pit_id | int(11) | NO | UNI | 0 | | # +---------+--------------+------+-----+---------+----------------+ # To : # tags:Tag # rdfs:label # dc:title # TODO: # * investigate the weird thing happening at http://www.bbc.co.uk/programmes/tags/john%20o'farrell # * most of the foaf:homepage point to non yet existing pages map:tags a d2rq:ClassMap; d2rq:dataStorage map:database; d2rq:uriPattern "tag/@@tags.id@@"; d2rq:class tags:Tag; . map:tags__label a d2rq:PropertyBridge; d2rq:belongsToClassMap map:tags; d2rq:property rdfs:label; d2rq:column "tags.title"; . map:taggings_html a d2rq:PropertyBridge; d2rq:belongsToClassMap map:tags; d2rq:property foaf:homepage; d2rq:uriPattern "http://www.bbc.co.uk/programmes/tags/@@tags.url_key@@"; . map:tags_title a d2rq:PropertyBridge; d2rq:belongsToClassMap map:tags; d2rq:property dc:title; d2rq:column "tags.title"; . # Mapping the table version_types # mysql> describe version_types; # +---------------------+--------------+------+-----+---------+----------------+ # | Field | Type | Null | Key | Default | Extra | # +---------------------+--------------+------+-----+---------+----------------+ # | id | int(11) | NO | PRI | NULL | auto_increment | # | pip_version_type_id | varchar(32) | NO | UNI | | | # | title | varchar(128) | YES | | | | # | synopsis | varchar(128) | YES | | NULL | | # +---------------------+--------------+------+-----+---------+----------------+ # And the table version_types_versions ... # mysql> describe version_types_versions; # +-----------------+---------+------+-----+---------+-------+ # | Field | Type | Null | Key | Default | Extra | # +-----------------+---------+------+-----+---------+-------+ # | version_id | int(11) | NO | PRI | 0 | | # | version_type_id | int(11) | NO | PRI | 0 | | # +-----------------+---------+------+-----+---------+-------+ # To : # po:OriginalVersion # po:AudioDescribedVersion # po:SignedVersion # po:ShortenedVersion map:original_version a d2rq:ClassMap; d2rq:dataStorage map:database; d2rq:uriPattern "version/@@versions.id@@"; d2rq:class po:OriginalVersion; d2rq:condition "versions.id = version_types_versions.version_id"; d2rq:condition "version_types_versions.version_type_id = version_types.id" ; d2rq:condition "version_types.pip_version_type_id = 'Original'"; . map:audiodesc_version a d2rq:ClassMap; d2rq:dataStorage map:database; d2rq:uriPattern "version/@@versions.id@@"; d2rq:class po:AudioDescribedVersion; d2rq:condition "versions.id = version_types_versions.version_id"; d2rq:condition "version_types_versions.version_type_id = version_types.id" ; d2rq:condition "version_types.pip_version_type_id = 'AudioDescribed'"; . map:signed_version a d2rq:ClassMap; d2rq:dataStorage map:database; d2rq:uriPattern "version/@@versions.id@@"; d2rq:class po:SignedVersion; d2rq:condition "versions.id = version_types_versions.version_id"; d2rq:condition "version_types_versions.version_type_id = version_types.id" ; d2rq:condition "version_types.pip_version_type_id = 'Signed'"; . map:shortened_version a d2rq:ClassMap; d2rq:dataStorage map:database; d2rq:uriPattern "version/@@versions.id@@"; d2rq:class po:ShortenedVersion; d2rq:condition "versions.id = version_types_versions.version_id"; d2rq:condition "version_types_versions.version_type_id = version_types.id" ; d2rq:condition "version_types.pip_version_type_id = 'Shortened'"; . # Mapping the table versions # mysql> describe versions; # +-----------------------+-----------------------+------+-----+---------+----------------+ # | Field | Type | Null | Key | Default | Extra | # +-----------------------+-----------------------+------+-----+---------+----------------+ # | id | int(11) | NO | PRI | NULL | auto_increment | # | pid | varchar(8) | NO | UNI | | | # | episode_id | int(11) | NO | MUL | 0 | | # | duration | int(11) | YES | | NULL | | # | aspect_ratio | varchar(16) | YES | | NULL | | # | sound_format | varchar(64) | YES | | NULL | | # | subtitle_type | enum('open','closed') | YES | | NULL | | # | subtitle_supplemental | tinyint(1) | YES | | NULL | | # | subtitle_language | varchar(8) | YES | | NULL | | # | created_at | datetime | NO | | | | # | updated_at | datetime | NO | | | | # +-----------------------+-----------------------+------+-----+---------+----------------+ # To: # po:Version # rdfs:label # po:pid # po:version # po:duration # po:aspect_ratio # po:sound_format # po:creation_date # po:update_date # po:subtitle # po:Subtitle # po:subtitle_type # po:subtitle_supplemental # po:subtitle_language map:versions a d2rq:ClassMap; d2rq:dataStorage map:database; d2rq:uriPattern "version/@@versions.id@@"; d2rq:class po:Version; . map:versions__label a d2rq:PropertyBridge; d2rq:belongsToClassMap map:versions; d2rq:property rdfs:label; d2rq:pattern "versions #@@versions.id@@"; . map:versions_pid a d2rq:PropertyBridge; d2rq:belongsToClassMap map:versions; d2rq:property po:pid; d2rq:column "versions.pid"; . map:versions_episode_id a d2rq:PropertyBridge; d2rq:belongsToClassMap map:episodes; d2rq:property po:version; d2rq:refersToClassMap map:versions; d2rq:join "versions.episode_id = episodes.id"; . map:versions_duration a d2rq:PropertyBridge; d2rq:belongsToClassMap map:versions; d2rq:property po:duration; d2rq:column "versions.duration"; d2rq:datatype xsd:int; . map:versions_aspect_ratio a d2rq:PropertyBridge; d2rq:belongsToClassMap map:versions; d2rq:property po:aspect_ratio; d2rq:column "versions.aspect_ratio"; . map:versions_sound_format a d2rq:PropertyBridge; d2rq:belongsToClassMap map:versions; d2rq:property po:sound_format; d2rq:column "versions.sound_format"; . map:versions_subtitle a d2rq:PropertyBridge; d2rq:belongsToClassMap map:versions; d2rq:property po:subtitle; d2rq:uriPattern "subtitle/@@versions.id@@"; . map:subtitle a d2rq:ClassMap; d2rq:dataStorage map:database; d2rq:uriPattern "subtitle/@@versions.id@@"; d2rq:class po:Subtitle; . map:versions_subtitle_type a d2rq:PropertyBridge; d2rq:belongsToClassMap map:subtitle; d2rq:property po:subtitle_type; d2rq:column "versions.subtitle_type"; . map:versions_subtitle_supplemental a d2rq:PropertyBridge; d2rq:belongsToClassMap map:subtitle; d2rq:property po:subtitle_supplemental; d2rq:column "versions.subtitle_supplemental"; . map:versions_subtitle_language a d2rq:PropertyBridge; d2rq:belongsToClassMap map:subtitle; d2rq:property po:subtitle_language; d2rq:column "versions.subtitle_language"; . map:versions_created_at a d2rq:PropertyBridge; d2rq:belongsToClassMap map:versions; d2rq:property po:creation_date; d2rq:column "versions.created_at"; d2rq:datatype xsd:dateTime; d2rq:condition "versions.created_at != '0000'"; . map:versions_updated_at a d2rq:PropertyBridge; d2rq:belongsToClassMap map:versions; d2rq:property po:update_date; d2rq:column "versions.updated_at"; d2rq:datatype xsd:dateTime; d2rq:condition "versions.updated_at != '0000'"; .