2 ;;; Lisp Routines for parsing UMLS files
3 ;;; and inserting into SQL databases
5 ;;; Copyright (c) 2001 Kevin M. Rosenberg, M.D.
6 ;;; $Id: parse-2002.lisp,v 1.1 2002/10/05 20:17:14 kevin Exp $
10 ;;; Pre-read data for custom fields into hash tables
11 (defvar *parse-hash-init?* nil)
13 (eval-when (:compile-toplevel :load-toplevel :execute)
14 (let ((pfstr-hash nil) ;;; Preferred concept strings by CUI
15 (cui-lrl-hash nil) ;;; LRL by CUI
16 (lui-lrl-hash nil) ;;; LRL by LUI
17 (cuisui-lrl-hash nil) ;;; LRL by CUISUI
18 (sab-srl-hash nil)) ;;; SRL by SAB
20 (defun make-parse-hash-table ()
24 (clrhash cui-lrl-hash)
25 (clrhash lui-lrl-hash)
26 (clrhash cuisui-lrl-hash)
27 (clrhash sab-srl-hash))
29 pfstr-hash (make-hash-table :size 800000)
30 cui-lrl-hash (make-hash-table :size 800000)
31 lui-lrl-hash (make-hash-table :size 1500000)
32 cuisui-lrl-hash (make-hash-table :size 1800000)
33 sab-srl-hash (make-hash-table :size 100 :test 'equal))))
35 (defun binit-hash-table (&optional (force-read nil))
36 (when (or force-read (not *parse-hash-init?*))
37 (make-parse-hash-table)
38 (setq *parse-hash-init?* t))
39 (with-buffered-umls-file (line "MRCON")
40 (let ((cui (parse-ui (aref line 0)))
41 (lui (parse-ui (nth 3 line)))
42 (sui (parse-ui (nth 5 line)))
43 (lrl (parse-integer (nth 7 line))))
44 (unless (gethash cui pfstr-hash) ;; if haven't stored pfstr for cui
45 (if (and (string-equal (aref line 1) "ENG") ; LAT
46 (string-equal (aref line 2) "P") ; ts
47 (string-equal (aref line 4) "PF")) ; stt
48 (setf (gethash cui pfstr-hash) (aref line 6))))
49 (set-lrl-hash cui lrl cui-lrl-hash)
50 (set-lrl-hash lui lrl lui-lrl-hash)
51 (set-lrl-hash (make-cuisui cui sui) lrl cuisui-lrl-hash)))
52 (with-buffered-umls-file (line "MRSO")
53 (let ((sab (aref 3 line)))
54 (unless (gethash sab sab-srl-hash) ;; if haven't stored
55 (setf (gethash sab sab-srl-hash) (aref 6 line))))))
57 (defun init-hash-table (&optional (force-read nil))
58 (when (or force-read (not *parse-hash-init?*))
59 (make-parse-hash-table)
60 (setq *parse-hash-init?* t))
61 (with-umls-file (line "MRCON")
62 (let ((cui (parse-ui (nth 0 line)))
63 (lui (parse-ui (nth 3 line)))
64 (sui (parse-ui (nth 5 line)))
65 (lrl (parse-integer (nth 7 line))))
66 (unless (gethash cui pfstr-hash) ;; if haven't stored pfstr for cui
67 (if (and (string-equal (nth 1 line) "ENG") ; LAT
68 (string-equal (nth 2 line) "P") ; ts
69 (string-equal (nth 4 line) "PF")) ; stt
70 (setf (gethash cui pfstr-hash) (nth 6 line))))
71 (set-lrl-hash cui lrl cui-lrl-hash)
72 (set-lrl-hash lui lrl lui-lrl-hash)
73 (set-lrl-hash (make-cuisui cui sui) lrl cuisui-lrl-hash)))
74 (with-umls-file (line "MRSO")
75 (let ((sab (nth 3 line)))
76 (multiple-value-bind (val found) (gethash sab sab-srl-hash)
77 (declare (ignore val))
79 (setf (gethash sab sab-srl-hash) (parse-integer (nth 6 line))))))))
81 (defun pfstr-hash (cui)
82 (gethash cui pfstr-hash))
85 (gethash cui cui-lrl-hash))
88 (gethash lui lui-lrl-hash))
90 (defun cuisui-lrl (cuisui)
91 (gethash cuisui cuisui-lrl-hash))
94 (gu:aif (gethash sab sab-srl-hash) gu::it 0))
97 (defun set-lrl-hash (key lrl hash)
98 "Set the least restrictive level in hash table"
99 (multiple-value-bind (hash-lrl found) (gethash key hash)
100 (if (or (not found) (< lrl hash-lrl))
101 (setf (gethash key hash) lrl))))
103 ;; UMLS file and column structures
105 (defstruct (umls-file)
106 "Record for each UMLS File"
107 fil table des fmt cls rws bts fields colstructs)
109 (defstruct (umls-col)
110 "Record for each UMLS Column in each file"
111 col des ref min av max fil sqltype
112 dty ;; new in 2002 umls: suggested SQL datatype
113 parsefunc quotechar datatype custom-value-func)
115 ;;; SQL datatypes symbols
116 ;;; sql-u - Unique identifier
117 ;;; sql-s - Small integer (16-bit)
118 ;;; sql-i - Integer (32-bit)
119 ;;; sql-l - Big integer (64-bit)
120 ;;; sql-f - Floating point
122 (defconstant +col-datatypes+
123 '(("AV" sql-f) ("BTS" sql-i) ("CLS" sql-i) ("COF" sql-i) ("CUI1" sql-u)
124 ("CUI2" sql-u) ("CUI" sql-u) ("CXN" sql-s) ("FR" sql-i) ("LRL" sql-s)
125 ("LUI" sql-u) ("MAX" sql-s) ("MIN" sql-s) ("RANK" sql-s) ("REF" sql-s)
126 ("RNK" sql-s) ("RWS" sql-i) ("SRL" sql-s) ("SUI" sql-u) ("TUI" sql-u)
128 ("KCUISUI" sql-l) ("KCUILUI" sql-l) ("KCUILRL" sql-i) ("KLUILRL" sql-i)
129 ("KSRL" sql-i) ("KLRL" sql-i)
131 ("EUI" sql-u) ("EUI2" sql-u)
132 ;;; Semantic net columns
133 ("UI" sql-u) ("UI2" sql-u) ("UI3" sql-u))
134 "SQL data types for each non-string column")
136 (defconstant +custom-tables+
139 '(("MRCONSO" "SELECT m.CUI, m.LAT, m.TS, m.LUI, m.STT, m.SUI, m.STR, m.LRL, s.SAB, s.TTY, s.SCD, s.SRL FROM MRCON m, MRSO s WHERE m.CUI=s.CUI AND m.LUI=s.LUI AND m.SUI=s.SUI")
140 ("MRCONFULL" "SELECT m.CUI, m.LAT, m.TS, m.LUI, m.STT, m.SUI, m.STR, m.LRL, s.SAB, s.TTY, s.SCD, s.SRL, t.TUI FROM MRCON m, MRSO s, MRSTY t WHERE m.CUI=s.CUI AND m.LUI=s.LUI AND m.SUI=s.SUI AND m.CUI=t.CUI AND s.CUI=t.CUI"))
141 "Custom tables to create")
143 (defconstant +custom-cols+
144 '(("MRCON" "KPFSTR" "TEXT" 1024
145 (lambda (x) (pfstr-hash (parse-ui (nth 0 x)))))
146 ("MRCON" "KCUISUI" "BIGINT" 0
147 (lambda (x) (format nil "~d" (make-cuisui (parse-ui (nth 0 x)) (parse-ui (nth 5 x))))))
148 ("MRCON" "KCUILUI" "BIGINT" 0
149 (lambda (x) (format nil "~d" (make-cuilui (parse-ui (nth 0 x)) (parse-ui (nth 3 x))))))
150 ("MRCON" "KCUILRL" "INTEGER" 0
151 (lambda (x) (format nil "~d" (cui-lrl (parse-ui (nth 0 x))))))
152 ("MRCON" "KLUILRL" "INTEGER" 0
153 (lambda (x) (format nil "~d" (lui-lrl (parse-ui (nth 3 x))))))
154 ("MRLO" "KLRL" "INTEGER" 0
155 (lambda (x) (format nil "~d"
156 (if (zerop (length (nth 4 x)))
157 (cui-lrl (parse-ui (nth 0 x)))
158 (cuisui-lrl (make-cuisui (parse-ui (nth 0 x)) (parse-ui (nth 4 x))))))))
159 ("MRSTY" "KLRL" "INTEGER" 0
160 (lambda (x) (format nil "~d" (cui-lrl (parse-ui (nth 0 x))))))
161 ("MRCOC" "KLRL" "INTEGER" 0
162 (lambda (x) (format nil "~d"
163 (max (cui-lrl (parse-ui (nth 0 x)))
164 (gu:aif (cui-lrl (parse-ui (nth 1 x))) gu::it 0)))))
165 ("MRSAT" "KSRL" "INTEGER" 0
166 (lambda (x) (format nil "~d" (sab-srl (nth 5 x)))))
167 ("MRREL" "KSRL" "INTEGER" 0
168 (lambda (x) (format nil "~d" (sab-srl (nth 4 x)))))
169 ("MRRANK" "KSRL" "INTEGER" 0
170 (lambda (x) (format nil "~d" (sab-srl (nth 1 x)))))
171 ("MRDEF" "KSRL" "INTEGER" 0
172 (lambda (x) (format nil "~d" (sab-srl (nth 1 x)))))
173 ("MRCXT" "KSRL" "INTEGER" 0
174 (lambda (x) (format nil "~d" (sab-srl (nth 2 x)))))
175 ("MRATX" "KSRL" "INTEGER" 0
176 (lambda (x) (format nil "~d" (sab-srl (nth 1 x)))))
177 ("MRXW.ENG" "KLRL" "INTEGER" 0
178 (lambda (x) (format nil "~d" (cuisui-lrl (make-cuisui
180 (parse-ui (nth 4 x)))))))
181 ("MRXW.NONENG" "KLRL" "INTEGER" 0
182 (lambda (x) (format nil "~d" (cuisui-lrl (make-cuisui
184 (parse-ui (nth 4 x)))))))
185 ("MRXNW.ENG" "KLRL" "INTEGER" 0
186 (lambda (x) (format nil "~d" (cuisui-lrl (make-cuisui
188 (parse-ui (nth 4 x)))))))
189 ("MRXNS.ENG" "KLRL" "INTEGER" 0
190 (lambda (x) (format nil "~d" (cuisui-lrl (make-cuisui
192 (parse-ui (nth 4 x)))))))
193 ("MRREL" "KPFSTR2" "TEXT" 1024
194 (lambda (x) (pfstr-hash (parse-ui (nth 2 x)))))
195 ("MRCOC" "KPFSTR2" "TEXT" 1024
196 (lambda (x) (pfstr-hash (parse-ui (nth 1 x)))))
197 ("MRCXT" "KCUISUI" "BIGINT" 0
198 (lambda (x) (format nil "~d" (make-cuisui (parse-ui (nth 0 x)) (parse-ui (nth 1 x))))))
199 ("MRSAT" "KCUILUI" "BIGINT" 0
200 (lambda (x) (format nil "~d" (make-cuilui (parse-ui (nth 0 x)) (parse-ui (nth 1 x))))))
201 ("MRSAT" "KCUISUI" "BIGINT" 0
202 (lambda (x) (format nil "~d" (make-cuisui (parse-ui (nth 0 x)) (parse-ui (nth 2 x))))))
203 ("MRSO" "KCUISUI" "BIGINT" 0
204 (lambda (x) (format nil "~d" (make-cuisui (parse-ui (nth 0 x)) (parse-ui (nth 2 x))))))
205 ("MRXW.ENG" "KCUISUI" "BIGINT" 0
206 (lambda (x) (format nil "~d" (make-cuisui (parse-ui (nth 2 x)) (parse-ui (nth 4 x))))))
207 ("MRXNW.ENG" "KCUISUI" "BIGINT" 0
208 (lambda (x) (format nil "~d" (make-cuisui (parse-ui (nth 2 x)) (parse-ui (nth 4 x))))))
209 ("MRXNS.ENG" "KCUISUI" "BIGINT" 0
210 (lambda (x) (format nil "~d" (make-cuisui (parse-ui (nth 2 x)) (parse-ui (nth 4 x))))))
211 ("MRXW.NONENG" "LAT" "CHAR" 3 (lambda (x) (nth 0 x)))
212 ("MRXW.NONENG" "WD" "CHAR" 200 (lambda (x) (nth 1 x)))
213 ("MRXW.NONENG" "CUI" "INTEGER" 0 (lambda (x) (nth 2 x)))
214 ("MRXW.NONENG" "LUI" "INTEGER" 0 (lambda (x) (nth 3 x)))
215 ("MRXW.NONENG" "SUI" "INTEGER" 0 (lambda (x) (nth 4 x)))
216 ("MRXW.NONENG" "KCUISUI" "BIGINT" 0
217 (lambda (x) (format nil "~d" (make-cuisui (parse-ui (nth 2 x)) (parse-ui (nth 4 x)))))))
218 "Custom columns to create.(filename, col, sqltype, value-func).")
220 (defconstant +index-cols+
221 '(("CUI" "MRATX") ("CUI1" "MRCOC") ("CUI" "MRCON") ("LUI" "MRCON")
223 ("SUI" "MRCON") ("CUI" "MRCXT") ("CUI" "MRDEF") ("CUI" "MRLO")
224 ("CUI1" "MRREL") ("CUI" "MRSAT") ("LUI" "MRSAT") ("SUI" "MRSAT")
225 ("CUI" "MRSO") ("SAB" "MRSO") ("SRL" "MRSO") ("CUI" "MRSTY")
226 ("TUI" "MRSTY") ("CUI" "MRXNS_ENG") ("NSTR" "MRXNS_ENG" 10)
227 ("CUI" "MRXNW_ENG") ("NWD" "MRXNW_ENG") ("WD" "MRXW_ENG")
228 ("KCUISUI" "MRCON") ("KCUILUI" "MRCON") ("KCUILRL" "MRCON")
229 ("KLUILRL" "MRCON") ("KCUISUI" "MRCXT")
230 ("KCUISUI" "MRSO") ("KCUISUI" "MRSAT") ("KCUILUI" "MRSAT")
231 ("KCUISUI" "MRXW_ENG") ("KCUISUI" "MRXNW_ENG")
232 ("KCUISUI" "MRXNS_ENG") ("KCUISUI" "MRXW_NONENG")
233 ("KSRL" "MRATX") ("KSRL" "MRCXT") ("KSRL" "MRDEF") ("KSRL" "MRRANK")
234 ("KSRL" "MRREL") ("KSRL" "MRSAT") ("KLRL" "MRCOC")
235 ("KLRL" "MRLO") ("KLRL" "MRSTY") ("KLRL" "MRXW_ENG") ("KLRL" "MRXNW_ENG")
236 ("KLRL" "MRXNS_ENG") ("KLRL" "MRXW_NONENG")
238 ("EUI" "LRABR") ("EUI2" "LRABR") ("EUI" "LRAGR") ("EUI" "LRCMP") ("EUI" "LRMOD")
239 ("EUI" "LRNOM") ("EUI2" "LRNOM") ("EUI" "LRPRN") ("EUI" "LRPRP") ("EUI" "LRSPL")
240 ("EUI" "LRTRM") ("EUI" "LRTYP") ("EUI" "LRWD") ("WRD" "LRWD")
242 ;; Semantic NET indices
243 ("UI" "SRSTRE1") ("UI2" "SRSTRE1") ("UI3" "SRSTRE1")
244 ("STY_RL" "SRDEF") ("RT" "SRDEF") ("STY_RL" "SRSTR") ("STY_RL2" "SRSTR")
246 "Columns in files to index")
249 (defconstant +custom-index-cols+
252 '(("CUI" "MRCONFULL") ("SAB" "MRCONFULL") ("TUI" "MRCONFULL"))
253 "Indexes to custom tables")
255 ;; File & Column functions
257 (defun init-umls (&optional (alwaysclear nil))
258 "Initialize all UMLS file and column structures if not already initialized"
259 (when (or alwaysclear (null *umls-files*))
262 (init-field-lengths)))
264 (defun init-umls-cols ()
265 (setq *umls-cols* (append
268 (init-generic-cols "LRFLD")
269 (init-generic-cols "SRFLD"))))
271 (defun init-meta-cols ()
272 "Initialize all umls columns"
274 (with-umls-file (line "MRCOLS")
275 (destructuring-bind (col des ref min av max fil dty) line
276 (let ((c (make-umls-col
280 :min (parse-integer min)
281 :av (read-from-string av)
282 :max (parse-integer max)
284 :dty dty ;; new in 2002 UMLS
285 :sqltype "VARCHAR" ; default data type
286 :parsefunc #'add-sql-quotes
287 :custom-value-func nil
289 (add-datatype-to-col c (datatype-for-col col))
293 (defun init-custom-cols ()
294 "Initialize umls columns for custom columns"
296 (dolist (customcol +custom-cols+)
297 (let ((c (make-umls-col :col (nth 1 customcol)
301 :max (nth 3 customcol)
304 :fil (nth 0 customcol)
305 :sqltype (nth 2 customcol)
306 :parsefunc #'add-sql-quotes
307 :custom-value-func (nth 4 customcol)
309 (add-datatype-to-col c (datatype-for-col (nth 1 customcol)))
313 (defun escape-column-name (name)
314 (substitute #\_ #\/ name))
316 (defun init-generic-cols (col-filename)
317 "Initialize for generic (LEX/NET) columns"
319 (with-umls-file (line col-filename)
320 (destructuring-bind (nam des ref fil) line
321 (setq nam (escape-column-name nam))
322 (dolist (file (delimited-string-to-list fil #\,))
323 (let ((c (make-umls-col
332 :sqltype "VARCHAR" ; default data type
333 :parsefunc #'add-sql-quotes
334 :custom-value-func nil
336 (add-datatype-to-col c (datatype-for-col nam))
340 (defun init-umls-files ()
341 (setq *umls-files* (append
342 (init-generic-files "MRFILES")
343 (init-generic-files "LRFIL")
344 (init-generic-files "SRFIL")))
345 ;; need to separate this since init-custom-files depends on *umls-files*
346 (setq *umls-files* (append *umls-files* (init-custom-files))))
349 (defun umls-field-string-to-list (fmt)
350 "Converts a comma delimited list of fields into a list of field names. Will
351 append a unique number (starting at 2) onto a column name that is repeated in the list"
352 (let ((field-list (delimited-string-to-list (escape-column-name fmt) #\,))
353 (col-count (make-hash-table :test 'equal)))
354 (dotimes (i (length field-list))
356 (let ((col (nth i field-list)))
357 (multiple-value-bind (key found) (gethash col col-count)
359 (let ((next-id (1+ key)))
360 (setf (nth i field-list) (concatenate 'string
362 (format nil "~D" next-id)))
363 (setf (gethash col col-count) next-id))
364 (setf (gethash col col-count) 1)))))
367 (defun init-generic-files (files-filename)
368 "Initialize all LEX file structures"
370 (with-umls-file (line files-filename)
371 (destructuring-bind (fil des fmt cls rws bts) line
372 (let ((f (make-umls-file
374 :table (substitute #\_ #\. fil)
376 :fmt (escape-column-name fmt)
377 :cls (parse-integer cls)
378 :rws (parse-integer rws)
379 :bts (parse-integer bts)
380 :fields (concatenate 'list
381 (umls-field-string-to-list fmt)
382 (custom-colnames-for-filename fil)))))
383 (setf (umls-file-colstructs f) (umls-cols-for-umls-file f))
387 (defun init-custom-files ()
388 (let ((ffile (make-umls-file :fil "MRXW.NONENG"
389 :des "Custom NonEnglish Index"
394 :fields (umls-file-fields (find-umls-file "MRXW.ENG")))))
395 (setf (umls-file-colstructs ffile)
396 (umls-cols-for-umls-file ffile))
399 (defun datatype-for-col (colname)
400 "Return datatype for column name"
401 (car (cdr (find colname +col-datatypes+ :key #'car :test #'string-equal))))
403 (defun add-datatype-to-col (col datatype)
404 "Add data type information to column"
405 (setf (umls-col-datatype col) datatype)
407 (sql-u (setf (umls-col-sqltype col) "INTEGER"
408 (umls-col-parsefunc col) #'parse-ui
409 (umls-col-quotechar col) ""))
410 (sql-s (setf (umls-col-sqltype col) "SMALLINT"
411 (umls-col-parsefunc col) #'parse-integer
412 (umls-col-quotechar col) ""))
413 (sql-l (setf (umls-col-sqltype col) "BIGINT"
414 (umls-col-parsefunc col) #'parse-integer
415 (umls-col-quotechar col) ""))
416 (sql-i (setf (umls-col-sqltype col) "INTEGER"
417 (umls-col-parsefunc col) #'parse-integer
418 (umls-col-quotechar col) ""))
419 (sql-f (setf (umls-col-sqltype col) "NUMERIC"
420 (umls-col-parsefunc col) #'read-from-string
421 (umls-col-quotechar col) ""))
422 (t ; Default column type, optimized text storage
423 (setf (umls-col-parsefunc col) #'add-sql-quotes
424 (umls-col-quotechar col) "'")
425 (when (and (umls-col-max col) (umls-col-av col))
426 (if (> (umls-col-max col) 255)
427 (setf (umls-col-sqltype col) "TEXT")
428 (if (< (- (umls-col-max col) (umls-col-av col)) 4)
429 (setf (umls-col-sqltype col) "CHAR") ; if average bytes wasted < 4
430 (setf (umls-col-sqltype col) "VARCHAR")))))))