r9529: successful import of 2004aa

[umlisp.git] / create-sql.lisp

diff --git a/create-sql.lisp b/create-sql.lisp
index 6e50a0fe6f4bbd7cb359f6c3dbb32b152c5911a8..15506cca76bdb539e6cf812f0452186d8a853823 100644 (file)
--- a/create-sql.lisp
+++ b/create-sql.lisp
@@ -23,15 +23,28 @@
   (let ((col-func 
         (lambda (c) 
           (let ((sqltype (sqltype c)))
+            (case *umls-sql-type*
+              (:oracle
+               (cond
+                ((string-equal sqltype "VARCHAR")
+                 (setq sqltype "VARCHAR2"))
+                ((string-equal sqltype "BIGINT")
+                 (setq sqltype "VARCHAR2(20)")))))
+            
             (concatenate 'string
-                         (col c)
-                         " "
-                         (if (or (string-equal sqltype "VARCHAR")
-                                 (string-equal sqltype "CHAR"))
-                             (format nil "~a (~a)" sqltype (cmax c))
-                             sqltype))))))
-    (format nil "CREATE TABLE ~a (~{~a~^,~})" (table file)
-           (mapcar col-func (ucols file)))))
+              (col c)
+              " "
+              (if (or (string-equal sqltype "VARCHAR")
+                      (string-equal sqltype "CHAR"))
+                  (format nil "~a (~a)" sqltype (cmax c))
+                sqltype))))))
+    (format nil "CREATE TABLE ~a (~{~a~^,~})~A" 
+           (table file)
+           (mapcar col-func (ucols file))
+           (if (and (eq *umls-sql-type* :mysql)
+                    (string-equal (table file) "MRCXT"))
+               " MAX_ROWS=200000000"
+             ""))))
 
 (defun create-custom-table-cmd (tablename sql-cmd)
   "Return SQL command to create a custom table"
@@ -93,9 +106,9 @@
 (defun noneng-lang-index-files ()
   (remove-if-not
    (lambda (f) (and (> (length (fil f)) 4)
-                   (string-equal (fil f) "MRXW." :end1 5) 
-                   (not (string-equal (fil f) "MRXW.ENG"))
-                   (not (string-equal (fil f) "MRXW.NONENG"))))
+                   (string-equal (fil f) "MRXW_" :end1 5) 
+                   (not (string-equal (fil f) "MRXW_ENG.RRF"))
+                   (not (string-equal (fil f) "MRXW_NONENG.RRF"))))
    *umls-files*))
 
 ;;; SQL Command Functions
@@ -136,8 +149,36 @@
   (dolist (file *umls-files*)
     (sql-execute (create-table-cmd file) conn)))
 
+#+ignore
+(defun sql-create-kcon-table (conn)
+  "Create concept table, one row per concept."
+  (ignore-errors (execute-command "DROP TABLE KCON" :database conn))
+  (execute-command
+   (format nil "CREATE TABLE KCON (CUI INTEGER, STR ~A, LRL ~A)"
+          (case *umls-sql-type*
+            (:oracle
+             (format nil "VARCHAR2(~D)"
+                     (slot-value (find-ucol "STR" "MRCONSO.RRF") 'max)))
+            (t "TEXT"))
+          (case *umls-sql-type*
+            (:mysql "TINYINT")
+            ((:postgresql :postgresql-socket) "INT2")
+            (:oracle "NUMBER(2,0)")
+            (t "INTEGER")))
+   :database conn)
+  (dolist (tuple (query "select distinct cui from MRCONSO order by cui"
+                       :database conn))
+    (let ((cui (car tuple)))
+      (execute-command 
+       (format nil "INSERT into KCON VALUES (~D,'~A',~D)"
+              cui
+              (add-sql-quotes (pfstr-hash cui) )
+              (cui-lrl cui))
+       :database conn))))
+
 (defun sql-create-custom-tables (conn)
   "SQL Databases: create all custom tables"
+  ;;(sql-create-kcon-table conn)
   (dolist (ct +custom-tables+)
     (sql-execute (create-custom-table-cmd (car ct) (cadr ct)) conn)))
   
@@ -174,7 +215,7 @@
       (ignore-errors (sql-execute "drop table USRL" conn)))
   (sql-execute "create table USRL (sab varchar(80), srl integer)" conn)
   (dolist (tuple (mutex-sql-query
-                 "select distinct SAB,SRL from MRSO order by SAB asc"))
+                 "select distinct SAB,SRL from MRCONSO order by SAB asc"))
     (sql-execute (format nil "insert into USRL (sab,srl) values ('~a',~d)" 
                         (car tuple) (ensure-integer (cadr tuple)))
                 conn)))
@@ -221,7 +262,7 @@ This is much faster that using create-umls-db-insert."
 (defun translate-all-files (&optional (extension ".trans"))
   "Copy translated files and return postgresql copy commands to import"
   (make-noneng-index-file extension)
-  (dolist (f (remove "MRXW.NONENG" *umls-files* :test #'string= :key #'fil))
+  (dolist (f (remove "MRXW_NONENG.RRF" *umls-files* :test #'string= :key #'fil))
     (translate-umls-file f extension)))
 
 (defun translate-umls-file (file extension)
@@ -230,17 +271,17 @@ This is much faster that using create-umls-db-insert."
 
 (defun make-noneng-index-file (extension)
   "Make non-english index file"
-  (translate-files (find-ufile "MRXW.NONENG")
+  (translate-files (find-ufile "MRXW_NONENG.RRF")
                   extension (noneng-lang-index-files)))
 
 (defun translate-files (out-ufile extension input-ufiles)
   "Translate a umls file into a format suitable for sql copy cmd"
-  (let ((output-path (umls-pathname (fil out-ufile) extension)))
+  (let ((output-path (ufile-pathname out-ufile extension)))
     (if (probe-file output-path)
        (format t "File ~A already exists: skipping~%" output-path)
       (with-open-file (ostream output-path :direction :output)
        (dolist (input-ufile input-ufiles)
-         (with-umls-file (line (fil input-ufile))
+         (with-umls-ufile (line input-ufile)
            (translate-line out-ufile line ostream)
            (princ #\newline ostream)))))))
 
@@ -262,7 +303,7 @@ This is much faster that using create-umls-db-insert."
   "Return postgresql copy statement for a file"  
   (format
    nil "COPY ~a FROM '~a' using delimiters '|' with null as ''"
-   (table file) (umls-pathname (fil file) extension)))
+   (table file) (ufile-pathname file extension)))
 
 (defun mysql-copy-cmd (file extension &key local-file)
   "Return mysql copy statement for a file"  
@@ -270,7 +311,7 @@ This is much faster that using create-umls-db-insert."
    nil
    "LOAD DATA ~AINFILE \"~a\" INTO TABLE ~a FIELDS TERMINATED BY \"|\""
    (if local-file "LOCAL " "")
-   (umls-pathname (fil file) extension) (table file)))
+   (ufile-pathname file extension) (table file)))
 
    
 ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;