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
|
#! /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")
(schema-triples
;; TODO: Are ranges required for FOAF predicates? Can they not be
;; obtained from the FOAF spec?
(foaf:name rdfs:range rdfs:Literal)
(foaf:givenName rdfs:range rdfs:Literal)
(foaf:familyName rdfs:range rdfs:Literal)
(foaf:homepage rdfs:range rdfs:Literal)
(gn:address rdfs:range rdfs:Literal)
(gn:city rdfs:range rdfs:Literal)
(gn:state rdfs:range rdfs:Literal)
(gn:zipCode rdfs:range rdfs:Literal)
(gn:country rdfs:range rdfs:Literal))
(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 gn:address (field Investigators Address))
(set gn:city (field Investigators City))
(set gn:state (field Investigators State))
(set gn:zipCode (field Investigators ZipCode))
(set gn:country (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
(gn:dataset rdfs:range rdfs:Literal)
(gn:datasetOfInvestigator rdfs:domain gn:dataset)
(gn:datasetOfOrganization rdfs:domain gn:dataset)
(gn:datasetOfInvestigator rdfs:range foaf:Person)
(gn:datasetOfInbredSet rdfs:domain gn:dataset)
(gn:datasetOfInbredSet rdfs:range gn:inbredSet)
(gn:datasetOfSpecies rdfs:domain gn:dataset)
(gn:datasetOfSpecies rdfs:range gn:inbredSet)
(gn:datasetOfTissue rdfs:domain gn:dataset)
(gn:datasetOfTissue rdfs:range gn:tissue)
(gn:normalization rdfs:domain gn:dataset)
(gn:normalization rdfs:range gn:avgMethod)
(gn:datasetOfPlatform rdfs:domain gn:dataset)
(gn:datasetOfPlatform rdfs:range gn:geneChip)
(gn:accessionId rdfs:range rdfs:Literal)
(gn:datasetStatusName rdfs:range rdfs:Literal)
(gn:summary rdfs:range rdfs:Literal)
(gn:aboutTissue rdfs:range rdfs:Literal)
(gn:geoSeries rdfs:range rdfs:Literal)
(gn:name rdfs:range rdfs:Literal)
(gn:title rdfs:range rdfs:Literal)
(gn:publicationTitle rdfs:range rdfs:Literal)
(gn:specifics rdfs:range rdfs:Literal)
(gn:datasetGroup rdfs:range rdfs:Literal)
(gn:aboutCases rdfs:range rdfs:Literal)
(gn:aboutPlatform rdfs:range rdfs:Literal)
(gn:aboutDataProcessing rdfs:range rdfs:Literal)
(gn:notes rdfs:range rdfs:Literal)
(gn:experimentDesign rdfs:range rdfs:Literal)
(gn:contributors rdfs:range rdfs:Literal)
(gn:citation rdfs:range rdfs:Literal)
(gn:acknowledgment rdfs:range rdfs:Literal))
(triples (ontology 'dataset:
(regexp-substitute/global #f "[^A-Za-z0-9:]"
(field InfoFiles InfoPageName)
'pre "_" 'post))
(set rdf:type (string->symbol
(field ("IF(GenoFreeze.Id IS NOT NULL, 'gn:genotypeDataset', IF(PublishFreeze.Id IS NOT NULL, 'gn:phenotypeDataset', 'gn:dataset'))"
rdfType))))
(set gn:name (regexp-substitute/global
#f "^[Nn]one$"
(field InfoFiles InfoPageName)
""))
(set gn:fullName
(field ("IFNULL(GenoFreeze.FullName, IFNULL(PublishFreeze.FullName, ''))"
DatasetFullName)))
(set dct:created
(field ("IFNULL(GenoFreeze.CreateTime, IFNULL(PublishFreeze.CreateTime, IFNULL(ProbeSetFreeze.CreateTime, '')))"
createTimeGenoFreeze)))
(set gn:datasetOfInvestigator
(investigator-attributes->id (field Investigators FirstName)
(field Investigators LastName)
(field Investigators Email)))
(set gn:datasetOfOrganization
(field ("CAST(CONVERT(BINARY CONVERT(Organizations.OrganizationName USING latin1) USING utf8) AS VARCHAR(1500))" Organizations)))
(set gn:accessionId (format #f "GN~a" (field InfoFiles GN_AccesionId)))
(set gn:datasetStatusName (string-downcase
(field DatasetStatus DatasetStatusName)))
(set gn:datasetOfInbredSet
(string->identifier "inbredSet" (field InbredSet Name InbredSetName)))
(set gn:datasetOfTissue (string->identifier "tissue"
(field Tissue Short_Name)))
(set gn:normalization
(string->identifier "avgmethod"
;; If AvgMethodName is NULL, assume N/A.
(if (string-blank? (field AvgMethod Name AvgMethodName))
"N/A" (field AvgMethod Name AvgMethodName))))
(set gn:datasetOfPlatform
(string->identifier "platform"
(field GeneChip Name GeneChip)))
(set gn:summary
(sanitize-rdf-string (field Datasets Summary)))
(set gn:aboutTissue
(sanitize-rdf-string (field Datasets AboutTissue)))
(set gn:geoSeries
(let ((s
(string-match "GSE[0-9]*"
(field ("IFNULL(Datasets.GeoSeries, '')" GeoSeries)))))
(if s (ontology
'geoSeries: (match:substring s))
"")))
(set gn:title
(regexp-substitute/global
#f "^[Nn]one$"
(field InfoFiles InfoFileTitle)
""))
(set gn:publicationTitle
(regexp-substitute/global
#f "^[Nn]one$"
(field Datasets PublicationTitle)
""))
(set gn:specifics (sanitize-rdf-string (field InfoFiles Specifics)))
(set gn:datasetGroup (field Datasets DatasetName DatasetGroup))
(set gn:aboutCases
(sanitize-rdf-string
(field ("CAST(CONVERT(BINARY CONVERT(Datasets.AboutCases USING latin1) USING utf8) AS VARCHAR(10000))" AboutCases))))
(set gn:aboutPlatform
(sanitize-rdf-string
(field ("CAST(CONVERT(BINARY CONVERT(Datasets.AboutPlatform USING latin1) USING utf8) AS VARCHAR(1500))"
AboutPlatform))))
(set gn:aboutDataProcessing
(sanitize-rdf-string
(field ("CAST(CONVERT(BINARY CONVERT(Datasets.AboutDataProcessing USING latin1) USING utf8) AS VARCHAR(1500))"
AboutDataProcessing))))
(set gn:notes
(sanitize-rdf-string
(field ("CAST(CONVERT(BINARY CONVERT(Datasets.Notes USING latin1) USING utf8) AS VARCHAR(1500))"
GNNotes))))
(set gn:experimentDesign
(sanitize-rdf-string
(field ("CAST(CONVERT(BINARY CONVERT(Datasets.ExperimentDesign USING latin1) USING utf8) AS VARCHAR(1500))"
ExperimentDesign))))
(set gn:contributors
(sanitize-rdf-string
(field ("CAST(CONVERT(BINARY CONVERT(Datasets.Contributors USING latin1) USING utf8) AS VARCHAR(1500))"
Contributors))))
(set gn:citation
(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 gn:dataSourceAcknowledgment
(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 gn:acknowledgment (sanitize-rdf-string
(field Datasets Acknowledgment)))))
(dump-with-documentation
(name "Info files / Investigators Metadata")
(connection %connection-settings)
(table-metadata? #f)
(prefixes
(("dct:" "<http://purl.org/dc/terms/>")
("geoSeries:" "<http://www.ncbi.nlm.nih.gov/geo/query/acc.cgi?acc=>")
("rdf:" "<http://www.w3.org/1999/02/22-rdf-syntax-ns#>")
("rdfs:" "<http://www.w3.org/2000/01/rdf-schema#>")
("gn:" "<http://genenetwork.org/terms/>")
("foaf:" "<http://xmlns.com/foaf/0.1/>")
("taxon:" "<http://purl.uniprot.org/taxonomy/>")
("dataset:" "<http://genenetwork.org/dataset/>")))
(inputs
(dump-info-files
dump-investigators))
(outputs
(#:documentation "./docs/dump-info-pages.md"
#:rdf "./verified-data/dump-info-pages.ttl")))
|
