1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
|
#! /usr/bin/env guile
!#
(use-modules (srfi srfi-1)
(srfi srfi-26)
(ice-9 match)
(ice-9 regex)
(dump strings)
(dump sql)
(dump triples)
(dump special-forms))
(define %connection-settings
(call-with-input-file (list-ref (command-line) 1)
read))
;; One email ID in the Investigators table has spaces in it. This
;; function fixes that.
(define (fix-email-id email)
(string-delete #\space email))
(define (investigator-attributes->id first-name last-name email)
;; There is just one record corresponding to "Evan Williams" which
;; does not have an email ID. To accommodate that record, we
;; construct the investigator ID from not just the email ID, but
;; also the first and the last names. It would be preferable to just
;; find Evan Williams' email ID and insert it into the database.
(string->identifier "investigator"
(string-join
;; Add special case for Yohan Bossé whose name
;; has unprintable characters.
;; TODO: Fix Yohan Bossé's name in the database.
(let ((last-name (if (string=? first-name "Yohan")
"Bosse"
last-name)))
(list first-name last-name (fix-email-id email)))
"_")))
(define-dump dump-investigators
;; There are a few duplicate entries. We group by email to
;; deduplicate.
(tables (Investigators)
"GROUP BY Email")
(triples (investigator-attributes->id (field Investigators FirstName)
(field Investigators LastName)
(field Investigators Email))
(set rdf:type 'foaf:Person)
(set foaf:name (string-append (field Investigators FirstName) " "
(if (string=? (field Investigators FirstName) "Yohan")
"Bossé"
(field Investigators LastName))))
(set foaf:givenName
(field ("CAST(CONVERT(BINARY CONVERT(FirstName USING latin1) USING utf8) AS VARCHAR(100))" FirstName)))
(set foaf:familyName
(field ("CAST(CONVERT(BINARY CONVERT(LastName USING latin1) USING utf8) AS VARCHAR(100))" LastName)))
(set foaf:homepage (field Investigators Url))
(set v:adr (field Investigators Address))
(set v:locality (field Investigators City))
(set v:region (field Investigators State))
(set v:postal-code (field Investigators ZipCode))
(set v:country-name (field Investigators Country))))
(define-dump dump-info-files
(tables (InfoFiles
(left-join PublishFreeze "ON InfoFiles.InfoPageName = PublishFreeze.Name")
(left-join GenoFreeze "ON InfoFiles.InfoPageName = GenoFreeze.Name")
(left-join ProbeSetFreeze "ON InfoFiles.InfoPageName = ProbeSetFreeze.Name")
(left-join InbredSet "ON InfoFiles.InbredSetId = InbredSet.InbredSetId")
(left-join Species "ON InfoFiles.SpeciesId = Species.SpeciesId")
(left-join Datasets "USING (DatasetId)")
(left-join DatasetStatus "USING (DatasetStatusId)")
(left-join Tissue "USING (TissueId)")
(left-join Investigators "USING (InvestigatorId)")
(left-join AvgMethod "USING (AvgMethodId)")
(left-join Organizations "USING (OrganizationId)")
(left-join GeneChip "USING (GeneChipId)"))
"WHERE GN_AccesionId IS NOT NULL")
(schema-triples
(gnc:dataset rdf:type gdmt:Dataset)
(gnc:genotype rdfs:subClassOf gnc:dataset)
(gnc:phenotype rdfs:subClassOf gnc:dataset)
(gnt:belongsToInbredSet rdfs:domain gnc:dataset)
(gnt:belongsToInbredSet a owl:ObjectProperty)
(gnt:belongsToInbredSet skos:definition "The InbredSet this resource belongs to")
(gnt:hasTissue rdfs:domain gnc:dataset)
(gnt:hasTissue a owl:ObjectProperty)
(gnt:hasTissue skos:definition "Tissues this resource has")
(gnt:hasTissueInfo rdfs:domain gnc:dataset)
(gnt:hasTissueInfo a owl:ObjectProperty)
(gnt:hasTissueInfo skos:definition "Metadata about Tissue for this resource")
(gnt:usedNormalization rdfs:domain gnc:dataset)
(gnt:usedNormalization a owl:ObjectProperty)
(gnt:usedNormalization skos:definition "Normalization techniques this resource has")
(gnt:usedPlatform rdfs:domain gnc:dataset)
(gnt:usedPlatform a owl:ObjectProperty)
(gnt:usedPlatform skos:definition "The Platform this resource uses")
(gnt:hasGeoSeriesId rdfs:domain gnc:dataset)
(gnt:hasGeoSeriesId a owl:ObjectProperty)
(gnt:hasGeoSeriesId skos:definition "id of record in NCBI database")
(gnt:hasExperimentDesignInfo rdfs:domain gnc:dataset)
(gnt:hasExperimentDesignInfo rdfs:label "Experiment Design")
(gnt:hasExperimentDesignInfo a owl:ObjectProperty)
(gnt:hasExperimentDesignInfo skos:definition "Information about how the experiment was designed")
(gnt:hasNotes rdfs:domain gnc:dataset)
(gnt:hasNotes a owl:ObjectProperty)
(gnt:hasNotes rdfs:label "Notes")
(gnt:hasNotes skos:definition "Extra Notes about this dataset")
(gnt:hasDataProcessingInfo rdfs:domain gnc:dataset)
(gnt:hasDataProcessingInfo rdfs:label "About Data Processing")
(gnt:hasDataProcessingInfo a owl:ObjectProperty)
(gnt:hasDataProcessingInfo skos:definition "Information about how this dataset was processed")
(gnt:hasPlatformInfo rdfs:domain gnc:dataset)
(gnt:hasPlatformInfo a owl:ObjectProperty)
(gnt:hasPlatformInfo rdfs:label "About Platfoorm")
(gnt:hasPlatformInfo skos:definition "Information about the platform that was used with this dataset")
(gnt:hasCaseInfo rdfs:domain gnc:dataset)
(gnt:hasCaseInfo rdfs:label "About Case")
(gnt:hasCaseInfo a owl:ObjectProperty)
(gnt:hasCaseInfo skos:definition "Information about the cases used in this platform")
(gnt:hasAcknowledgement rdfs:domain gnc:dataset)
(gnt:hasAcknowledgement rdfs:label "Acknowledgement")
(gnt:hasAcknowledgement a owl:ObjectProperty)
(gnt:hasAcknowledgement skos:definition "People to acknowledge"))
(triples (string->identifier
"" (regexp-substitute/global #f "[^A-Za-z0-9:]"
(field InfoFiles InfoPageName)
'pre "_" 'post)
#:separator ""
#:proc string-capitalize-first)
(set rdf:type (string->symbol
(field ("IF(GenoFreeze.Id IS NOT NULL, 'gnc:genotype', IF(PublishFreeze.Id IS NOT NULL, 'gnc:phenotype', 'gnc:dataset'))"
rdfType))))
(set rdfs:label (regexp-substitute/global
#f "^[Nn]one$"
(field InfoFiles InfoPageName)
""))
(set skos:prefLabel
(field ("IFNULL(GenoFreeze.FullName, IFNULL(PublishFreeze.FullName, ''))"
DatasetFullName)))
(set skos:prefLabel (field Datasets DatasetName DatasetGroup))
(set gdmt:hasTitleInfo
(regexp-substitute/global
#f "^[Nn]one$"
(field InfoFiles InfoFileTitle)
""))
;; This is the published title
(set dct:title
(regexp-substitute/global
#f "^[Nn]one$"
(field Datasets PublicationTitle)
""))
(set dct:created
(field ("IFNULL(GenoFreeze.CreateTime, IFNULL(PublishFreeze.CreateTime, IFNULL(ProbeSetFreeze.CreateTime, '')))"
createTimeGenoFreeze)))
(set gdmt:hasCreatorInfo
(investigator-attributes->id (field Investigators FirstName)
(field Investigators LastName)
(field Investigators Email)))
(set gdmt:hasCreatorAffiliation
(field ("CAST(CONVERT(BINARY CONVERT(Organizations.OrganizationName USING latin1) USING utf8) AS VARCHAR(1500))" Organizations)))
(set gdmt:hasDatasetIdentifierSubType (format #f "GN~a" (field InfoFiles GN_AccesionId)))
(set gdmt:hasRightsInfo (string-downcase
(field DatasetStatus DatasetStatusName)))
(set gnt:belongsToInbredSet
(string->identifier "inbredSet" (field InbredSet Name InbredSetName)))
(set gnt:hasTissue (string->identifier "tissue"
(field Tissue Short_Name)))
(set gnt:usedNormalization
(string->identifier "avgmethod"
;; If AvgMethodName is NULL, assume N/A.
(if (string-blank? (field AvgMethod Name AvgMethodName))
"N/A" (field AvgMethod Name AvgMethodName))))
(set gnt:usedPlatform
(string->identifier "platform"
(field GeneChip Name GeneChip)))
(set gdmt:isDescribedBy
(sanitize-rdf-string (field Datasets Summary)))
(set gnt:hasGeoSeriesId
(let ((s
(string-match "GSE[0-9]*"
(field ("IFNULL(Datasets.GeoSeries, '')" GeoSeries)))))
(if s (ontology
'geoSeries: (match:substring s))
"")))
(set gnt:hasTissueInfo
(sanitize-rdf-string (field Datasets AboutTissue)))
(set gnt:hasContentInfo (sanitize-rdf-string (field InfoFiles Specifics)))
(set gnt:hasCaseInfo
(sanitize-rdf-string
(field ("CAST(CONVERT(BINARY CONVERT(Datasets.AboutCases USING latin1) USING utf8) AS VARCHAR(10000))" AboutCases))))
(set gnt:hasPlatformInfo
(sanitize-rdf-string
(field ("CAST(CONVERT(BINARY CONVERT(Datasets.AboutPlatform USING latin1) USING utf8) AS VARCHAR(1500))"
AboutPlatform))))
(set gnt:hasDataProcessingInfo
(sanitize-rdf-string
(field ("CAST(CONVERT(BINARY CONVERT(Datasets.AboutDataProcessing USING latin1) USING utf8) AS VARCHAR(1500))"
AboutDataProcessing))))
(set gnt:hasNotes
(sanitize-rdf-string
(field ("CAST(CONVERT(BINARY CONVERT(Datasets.Notes USING latin1) USING utf8) AS VARCHAR(1500))"
GNNotes))))
(set gnt:hasExperimentDesignInfo
(sanitize-rdf-string
(field ("CAST(CONVERT(BINARY CONVERT(Datasets.ExperimentDesign USING latin1) USING utf8) AS VARCHAR(1500))"
ExperimentDesign))))
(set gdmt:hasContributorInfo
(sanitize-rdf-string
(field ("CAST(CONVERT(BINARY CONVERT(Datasets.Contributors USING latin1) USING utf8) AS VARCHAR(1500))"
Contributors))))
(set gdmt:IsCitedBy
(sanitize-rdf-string
(regexp-substitute/global
#f "^[Nn]one$"
(field
("CAST(CONVERT(BINARY CONVERT(Datasets.Citation USING latin1) USING utf8) AS VARCHAR(1500))"
Citation))
"")))
(set gnt:hasAcknowledgement
(sanitize-rdf-string
(string-trim-both
(regexp-substitute/global
#f "^[Nn]one$"
(field ("CAST(CONVERT(BINARY CONVERT(InfoFiles.Data_Source_Acknowledge USING latin1) USING utf8) AS VARCHAR(1500))"
Data_Source_Acknowledge))
""))))
(set gnt:hasAcknowledgement (sanitize-rdf-string
(field Datasets Acknowledgment)))))
(dump-with-documentation
(name "Info files / Investigators Metadata")
(connection %connection-settings)
(table-metadata? #f)
(prefixes
'(("v:" "<http://www.w3.org/2006/vcard/ns#>")
("foaf:" "<http://xmlns.com/foaf/0.1/>")
("gdmt:" "<http://vocab.fairdatacollective.org/gdmt/>")
("skos:" "<http://www.w3.org/2004/02/skos/core#>")
("geoSeries:" "<http://www.ncbi.nlm.nih.gov/geo/query/acc.cgi?acc=>")
("gnt:" "<http://genenetwork.org/term/>")
("gn:" "<http://genenetwork.org/id/>")
("gnc:" "<http://genenetwork.org/category/>")
("rdf:" "<http://www.w3.org/1999/02/22-rdf-syntax-ns#>")
("owl:" "<http://www.w3.org/2002/07/owl#>")
("rdfs:" "<http://www.w3.org/2000/01/rdf-schema#>")
("taxon:" "<http://purl.uniprot.org/taxonomy/>")
("dct:" "<http://purl.org/dc/terms/>")))
(inputs
(list dump-info-files
dump-investigators))
(outputs
'(#:documentation "./docs/dump-info-pages.md"
#:rdf "./verified-data/dump-info-pages.ttl")))
|
