Skip to content

Instantly share code, notes, and snippets.

@gkellogg
Created February 13, 2015 14:11
Show Gist options
  • Save gkellogg/738ccee76711b4432bb4 to your computer and use it in GitHub Desktop.
Save gkellogg/738ccee76711b4432bb4 to your computer and use it in GitHub Desktop.
virtual column pirate pad
From agenda,
https://www.w3.org/2013/csvw/wiki/F2F_Agenda_2015-02
09:00 - 10:30 Primary Keys & Cross References[edit]
Limit foreign key cross references to the same batch of processed resources
Should primary keys be skipped from cell level triple (or k/v pairs) generation?
Alternate structure in JSON mapping output graph if Primary Key is specified
Making `schema` property mandatory for table description objects & explicit identification of schemas
Composite primary keys and foreign key references
{
"@context": "http://www.w3.org/ns/csvw",
"dialect": {"trim": true},
"resources": [{
"url": "senior-roles.csv",
"tableSchema": "http://example.org/senior-roles.json"
}, {
"url": "junior-roles.csv",
"tableSchema": "http://example.org/junior-roles.json"
}]
}
-- senior roles
{
"@context": "http://www.w3.org/ns/csvw",
"columns": [{
"name": "ref",
"title": "Post Unique Reference"
}, {
"name": "name",
"title": "Name"
}, {
"name": "grade",
"title": "Grade"
}, {
"name": "job",
"title": "Job Title"
}, {
"name": "reportsTo",
"title": "Reports to Senior Post",
"propertyUrl": "http://example.org/reportsTo",
"valueUrl": "http://example.org/senior-roles.csv#post-{reportsTo}"
}],
"primaryKey": "ref",
"aboutUrl": "#post-{ref}",
"foreignKeys": [{
"columns": "reportsTo",
"reference": {
"schema": "http://example.org/senior-roles.json",
"columns": "ref"
}
}]
}
-- junior roles
{
"@context": "http://www.w3.org/ns/csvw",
"columns": [{
"name": "reportsToSenior",
"title": "Reporting Senior Post",
"propertyUrl": "http://example.org/reportsTo",
"valueUrl": "http://example.org/senior-roles.csv#post-{reportsToSenior}"
}],
"foreignKeys": [{
"columns": "reportsToSenior",
"reference": {
"schema": "http://example.org/senior-roles.json",
"columns": "ref"
}
}]
}
-- senior roles.csv
Post Unique Reference, Name,Grade, Job Title,Reports to Senior Post
90115, Steve Egan,SCS1A,Deputy Chief Executive, 90334
90250, David Sweeney,SCS1A, Director, 90334
90284, Heather Fry,SCS1A, Director, 90334
90334,Sir Alan Langlands, SCS4, Chief Executive, xx
-- junior roles.csv
Reporting Senior Post,Grade,Payscale Minimum (£),Payscale Maximum (£),Generic Job Title,Number of Posts in FTE, Profession
90284, 4, 17426, 20002, Administrator, 2,Operational Delivery
90284, 5, 19546, 22478, Administrator, 1,Operational Delivery
90115, 4, 17426, 20002, Administrator, 8.67,Operational Delivery
90115, 5, 19546, 22478, Administrator, 0.5,Operational Delivery
-- RDF results
@prefix csvw: <http://www.w3.org/ns/csvw#> .
@prefix dcat: <http://www.w3.org/ns/dcat#> .
@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#> .
[
csvw:table [
csvw:row [
<http://example.org/junior-roles.csv#Generic%20Job%20Title> "Administrator";
<http://example.org/junior-roles.csv#Grade> "4";
<http://example.org/junior-roles.csv#Number%20of%20Posts%20in%20FTE> "2";
<http://example.org/junior-roles.csv#Payscale%20Maximum%20(%C2%A3)> "20002";
<http://example.org/junior-roles.csv#Payscale%20Minimum%20(%C2%A3)> "17426";
<http://example.org/junior-roles.csv#Profession> "Operational Delivery";
<http://example.org/reportsTo> <http://example.org/senior-roles.csv#post-90284>;
csvw:rownum 2
], [
<http://example.org/junior-roles.csv#Generic%20Job%20Title> "Administrator";
<http://example.org/junior-roles.csv#Grade> "5";
<http://example.org/junior-roles.csv#Number%20of%20Posts%20in%20FTE> "1";
<http://example.org/junior-roles.csv#Payscale%20Maximum%20(%C2%A3)> "22478";
<http://example.org/junior-roles.csv#Payscale%20Minimum%20(%C2%A3)> "19546";
<http://example.org/junior-roles.csv#Profession> "Operational Delivery";
<http://example.org/reportsTo> <http://example.org/senior-roles.csv#post-90284>;
csvw:rownum 3
], [
<http://example.org/junior-roles.csv#Generic%20Job%20Title> "Administrator";
<http://example.org/junior-roles.csv#Grade> "4";
<http://example.org/junior-roles.csv#Number%20of%20Posts%20in%20FTE> "8.67";
<http://example.org/junior-roles.csv#Payscale%20Maximum%20(%C2%A3)> "20002";
<http://example.org/junior-roles.csv#Payscale%20Minimum%20(%C2%A3)> "17426";
<http://example.org/junior-roles.csv#Profession> "Operational Delivery";
<http://example.org/reportsTo> <http://example.org/senior-roles.csv#post-90115>;
csvw:rownum 4
], [
<http://example.org/junior-roles.csv#Generic%20Job%20Title> "Administrator";
<http://example.org/junior-roles.csv#Grade> "5";
<http://example.org/junior-roles.csv#Number%20of%20Posts%20in%20FTE> "0.5";
<http://example.org/junior-roles.csv#Payscale%20Maximum%20(%C2%A3)> "22478";
<http://example.org/junior-roles.csv#Payscale%20Minimum%20(%C2%A3)> "19546";
<http://example.org/junior-roles.csv#Profession> "Operational Delivery";
<http://example.org/reportsTo> <http://example.org/senior-roles.csv#post-90115>;
csvw:rownum 5
];
dcat:distribution [
a dcat:Distribution;
dcat:downloadURL <http://example.org/junior-roles.csv>
]
], [
csvw:row <http://example.org/senior-roles.csv#post-90115>,
<http://example.org/senior-roles.csv#post-90250>,
<http://example.org/senior-roles.csv#post-90284>,
<http://example.org/senior-roles.csv#post-90334>;
dcat:distribution [
a dcat:Distribution;
dcat:downloadURL <http://example.org/senior-roles.csv>
]
]
] .
<http://example.org/senior-roles.csv#post-90250> <http://example.org/reportsTo> <http://example.org/senior-roles.csv#post-90334>;
<http://example.org/senior-roles.csv#grade> "SCS1A";
<http://example.org/senior-roles.csv#job> "Director";
<http://example.org/senior-roles.csv#name> "David Sweeney";
<http://example.org/senior-roles.csv#ref> "90250";
csvw:rownum 3 .
<http://example.org/senior-roles.csv#post-90115> <http://example.org/reportsTo> <http://example.org/senior-roles.csv#post-90334>;
<http://example.org/senior-roles.csv#grade> "SCS1A";
<http://example.org/senior-roles.csv#job> "Deputy Chief Executive";
<http://example.org/senior-roles.csv#name> "Steve Egan";
<http://example.org/senior-roles.csv#ref> "90115";
csvw:rownum 2 .
<http://example.org/senior-roles.csv#post-90284> <http://example.org/reportsTo> <http://example.org/senior-roles.csv#post-90334>;
<http://example.org/senior-roles.csv#grade> "SCS1A";
<http://example.org/senior-roles.csv#job> "Director";
<http://example.org/senior-roles.csv#name> "Heather Fry";
<http://example.org/senior-roles.csv#ref> "90284";
csvw:rownum 4 .
<http://example.org/senior-roles.csv#post-90334> <http://example.org/reportsTo> <http://example.org/senior-roles.csv#post-xx>;
<http://example.org/senior-roles.csv#grade> "SCS4";
<http://example.org/senior-roles.csv#job> "Chief Executive";
<http://example.org/senior-roles.csv#name> "Sir Alan Langlands";
<http://example.org/senior-roles.csv#ref> "90334";
csvw:rownum 5 .
https://github.com/w3c/csvw/issues/16
Discussed at Feb F2F. Foreign key references are for validation purposes (there's an error if the referenced value doesn't exist, and it can only be used to link resources in a single table group). URL generation (eg `aboutUrl`, `propertyUrl`, `valueUrl`) provides for unvalidated (weak) links.
Editor action: Make clear the above and that validators should provide the user option to either do strict or lax validation. Converters should provide the option to not to do validation at all.
https://github.com/w3c/csvw/issues/130
Discussed at Feb F2F. We only want to define one kind of JSON output, not lots of possibilities (though the extension conversion hooks we provide should enable different outputs). An indexed output doesn't always make sense (eg when there's no primary key), and the `aboutUrl` role means that this becomes more complicated to do. So we're sticking with the current output which is an array of objects.
https://github.com/w3c/csvw/issues/66
Resolved at Feb F2F. If you want to match up columns that don't directly match up, you can't use `foreignKey`s, but you can use `aboutUrl` and `valueUrl` to create identifiers such that you get a linkage in any generated output.
https://github.com/w3c/csvw/issues/191
Resolved at Feb F2F. The order of processing is as described in https://github.com/w3c/csvw/issues/191#issuecomment-73497474. These properties are string properties, the URL template is expanded first. Any resolution (ie expanding prefixes & resolving against a base URL) is done after that expansion. Editor action to make this so.
https://github.com/w3c/csvw/issues/91
Resolved at Feb F2F. Link properties are resolved against the base url, maybe the @base from the context, or it may be the location of the metadata file, during normalization of the metadata file, and prior to merge. URL template properties are *expanded* (variable references replaced by values from a particular row) into a URL, which is then expanded to resolve prefixes, and against a base URL which is the absolute, resolved, table `url`.
https://github.com/w3c/csvw/issues/121
Resolved at Feb F2F. We will summarise the expansion processing that is necessary for our purposes, and say that it is intended to be consistent with JSON-LD IRI expansion. We do have some restrictions on what IRIs can be used, eg we don't allow blank node syntax.
https://github.com/w3c/csvw/issues/83
Resolved at F2F. `source` will be either `rdf` or `json`, referring to the in-memory representation of the result of the conversion; there's no serialisation going on.
https://github.com/w3c/csvw/issues/64
Resolved at F2F. We will introduce a `suppressOutput` property, on individual resources or on columns, which would mean that no output was generated from that table or from that column during a conversion. This is not an inherited property.
resolved: See http://www.w3.org/2015/02/13-csvw-irc#T13-07-20
example/possible def'n: - "this suppresses the conversion output from the thing that it is on".
time,A,B,C
00:00,1,2,3
_:obs1 a dc:Observation
:time 00:00:00 ;
:measure A
:value 1 .
_:obs2 a dc:Observation
:time 00:00:00 ;
:measure B
:value 2 .
_:obs3 a dc:Observation
:time 00:00:00 ;
:measure C
:value 3 .
---
aboutUrls are assigned as follow:
time _:obs1
A _:obs1
B _:obs2
C _:obs3
_:obs1
:time 00:00:00 ;
:value 1 .
_:obs2
:value 2 .
_:obs3
:value 3 .
---
_:obs
:time 00:00:00 ;
:A 1 ;
:B 2 ;
:C 3 .
---
[
name: location_name,
aboutUrl: "#location{,
propertyUrl: schema:name,
extras: [{
"aboutUrl": <#event>,x
"propertyUrl": schema:location,
"valueUrl": <#location>
}, {
"aboutUrl": <#location>
"propertyUrl": rdf:type
"valueUrl": schema:Place
}]
]
CSV was
Name, StartDate, location_name, location_address, ticket_url
B.B. King,2014-04-12T19:30,"Lupo’s Heartbreak Hotel", "79 Washington St., Providence, RI",https://www.etix.com/ticket/1771656
B.B. King,2014-04-13T20:00,"Lynn Auditorium","Lynn, MA, 01901",http://frontgatetickets.com/venue.php?id=11766
Target from 1st row:
_:b0 <http://schema.org/location> _:b1 .
_:b0 <http://schema.org/name> "B.B. King" .
_:b0 <http://schema.org/offers> _:b2 .
_:b0 <http://schema.org/startDate> "2014-04-12T19:30"^^<http://schema.org/Date> .
_:b0 <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://schema.org/MusicEvent> .
_:b1 <http://schema.org/address> "79 Washington St., Providence, RI" .
_:b1 <http://schema.org/name> "Lupo’s Heartbreak Hotel" .
_:b1 <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://schema.org/Place> .
_:b2 <http://schema.org/url> <https://www.etix.com/ticket/1771656> .
_:b2 <http://www.w3.org/1999/02/22-rdf-syntax-ns#type> <http://schema.org/Offer> .
new issue: (disagreement over triples per cell in case of array value)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment