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