X-Git-Url: http://git.kpe.io/?p=umlisp.git;a=blobdiff_plain;f=create-sql.lisp;h=04bc2b6bed3e0f676ddeb64761509a7360fd39e3;hp=15506cca76bdb539e6cf812f0452186d8a853823;hb=e6ef11f4c42de5a6754f73d9a714a0213b3a0133;hpb=eadf93ecf9164f5e0a1ec0be102624d1a383f677 diff --git a/create-sql.lisp b/create-sql.lisp index 15506cc..04bc2b6 100644 --- a/create-sql.lisp +++ b/create-sql.lisp @@ -10,7 +10,7 @@ ;;;; $Id$ ;;;; ;;;; This file, part of UMLisp, is -;;;; Copyright (c) 2000-2004 by Kevin M. Rosenberg, M.D. +;;;; Copyright (c) 2000-2006 by Kevin M. Rosenberg, M.D. ;;;; ;;;; UMLisp users are granted the rights to distribute and use this software ;;;; as governed by the terms of the GNU General Public License. @@ -20,8 +20,8 @@ (defun create-table-cmd (file) "Return sql command to create a table" - (let ((col-func - (lambda (c) + (let ((col-func + (lambda (c) (let ((sqltype (sqltype c))) (case *umls-sql-type* (:oracle @@ -30,7 +30,7 @@ (setq sqltype "VARCHAR2")) ((string-equal sqltype "BIGINT") (setq sqltype "VARCHAR2(20)"))))) - + (concatenate 'string (col c) " " @@ -38,25 +38,28 @@ (string-equal sqltype "CHAR")) (format nil "~a (~a)" sqltype (cmax c)) sqltype)))))) - (format nil "CREATE TABLE ~a (~{~a~^,~})~A" + (format nil "CREATE TABLE ~a (~{~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" - "")))) + "") + (if (eq *umls-sql-type* :mysql) + " TYPE=MYISAM CHARACTER SET utf8" + "")))) (defun create-custom-table-cmd (tablename sql-cmd) "Return SQL command to create a custom table" (format nil "CREATE TABLE ~a AS ~a;" tablename sql-cmd)) (defun insert-col-value (col value) - (if (null (parse-fun col)) + (if (null (parse-fun col)) value (format nil "~A" (funcall (parse-fun col) value)))) (defun insert-values-cmd (file values) - "Return sql insert command for a row of values" + "Return sql insert command for a row of values" (let ((insert-func (lambda (col value) (concatenate 'string (quote-str col) @@ -67,7 +70,7 @@ (table file) (fields file) (concat-separated-strings - "," + "," (mapcar insert-func (remove-custom-cols (ucols file)) values) (custom-col-values (custom-ucols-for-file file) values t))))) @@ -106,7 +109,7 @@ (defun noneng-lang-index-files () (remove-if-not (lambda (f) (and (> (length (fil f)) 4) - (string-equal (fil f) "MRXW_" :end1 5) + (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*)) @@ -117,7 +120,7 @@ "Return sql create index command" (format nil "CREATE INDEX ~a ON ~a (~a)" (concatenate 'string tablename "_" colname "_X") - tablename + tablename (case *umls-sql-type* (:mysql (concatenate 'string colname @@ -125,7 +128,7 @@ (format nil " (~d)" length) ""))) ((:postgresql :postgresql-socket) - ;; FIXME: incorrect syntax + ;; FIXME: incorrect syntax for postgresql? (if (integerp length) (format nil "substr((~A)::text,1,~D)" colname length) colname)) @@ -141,11 +144,11 @@ (defun sql-drop-tables (conn) "SQL Databases: drop all tables" (dolist (file *umls-files*) - (ignore-errors + (ignore-errors (sql-execute (format nil "DROP TABLE ~a" (table file)) conn)))) (defun sql-create-tables (conn) - "SQL Databases: create all tables" + "SQL Databases: create all tables" (dolist (file *umls-files*) (sql-execute (create-table-cmd file) conn))) @@ -166,10 +169,12 @@ (:oracle "NUMBER(2,0)") (t "INTEGER"))) :database conn) + ;; KCON deprecated by KPFENG field in MRCONSO + #+nil (dolist (tuple (query "select distinct cui from MRCONSO order by cui" :database conn)) (let ((cui (car tuple))) - (execute-command + (execute-command (format nil "INSERT into KCON VALUES (~D,'~A',~D)" cui (add-sql-quotes (pfstr-hash cui) ) @@ -181,14 +186,14 @@ ;;(sql-create-kcon-table conn) (dolist (ct +custom-tables+) (sql-execute (create-custom-table-cmd (car ct) (cadr ct)) conn))) - + (defun sql-insert-values (conn file) - "SQL Databases: inserts all values for a file" + "SQL Databases: inserts all values for a file" (with-umls-file (line (fil file)) (sql-execute (insert-values-cmd file line) conn))) (defun sql-insert-all-values (conn) - "SQL Databases: inserts all values for all files" + "SQL Databases: inserts all values for all files" (dolist (file *umls-files*) (sql-insert-values conn file))) @@ -203,11 +208,13 @@ (format nil "DROP INDEX ~a" (concatenate 'string tablename "_" colname "_X"))))) -(defun sql-create-indexes (conn &optional (indexes +index-cols+)) +(defun sql-create-indexes (conn &key (indexes +index-cols+) verbose) "SQL Databases: create all indexes" (dolist (idx indexes) + (when verbose (format t "UMLS Import: Creating index for column ~A on table ~A.~%" + (first idx) (second idx))) (ignore-errors (sql-execute (drop-index-cmd (car idx) (cadr idx)) conn)) - (sql-execute (create-index-cmd (car idx) (cadr idx) (caddr idx)) conn))) + (sql-execute (create-index-cmd (car idx) (cadr idx) (caddr idx)) conn))) (defun make-usrl (conn) (if (eql :mysql *umls-sql-type*) @@ -216,7 +223,7 @@ (sql-execute "create table USRL (sab varchar(80), srl integer)" conn) (dolist (tuple (mutex-sql-query "select distinct SAB,SRL from MRCONSO order by SAB asc")) - (sql-execute (format nil "insert into USRL (sab,srl) values ('~a',~d)" + (sql-execute (format nil "insert into USRL (sab,srl) values ('~a',~d)" (car tuple) (ensure-integer (cadr tuple))) conn))) @@ -224,7 +231,7 @@ (make-usrl conn) (make-ustats)) -(defun create-umls-db-by-insert () +(defun create-umls-db-by-insert (&key verbose) "SQL Databases: initializes entire database via SQL insert commands" (ensure-ucols+ufiles) (ensure-preparse) @@ -234,16 +241,18 @@ (sql-insert-all-values conn) (sql-create-indexes conn) (sql-create-custom-tables conn) - (sql-create-indexes conn +custom-index-cols+) + (sql-create-indexes conn :indexes +custom-index-cols+ :verbose verbose) (sql-create-special-tables conn))) -(defun create-umls-db (&key (extension ".trans") (skip-translation nil)) - "SQL Databases: initializes entire database via SQL copy commands. +(defun create-umls-db (&key (extension "-trans") (force-translation nil) (verbose nil)) + "SQL Databases: initializes entire database via SQL copy commands. This is much faster that using create-umls-db-insert." + (when verbose (format t "UMLS Import: Starting.~%")) (ensure-ucols+ufiles) + (when verbose (format t "UMLS Import: Preparsing files.~%")) (ensure-preparse) - (unless skip-translation - (translate-all-files extension)) + (when verbose (format t "UMLS Import: Converting text UMLS files to optimized format.~%")) + (translate-all-files :extension extension :verbose verbose :force force-translation) (let ((copy-cmd (ecase (umls-sql-type) (:mysql #'mysql-copy-cmd) @@ -253,37 +262,85 @@ This is much faster that using create-umls-db-insert." (sql-drop-tables conn) (sql-create-tables conn) (dolist (file *umls-files*) + (when verbose (format t "UMLS Import: Importing file ~A to SQL.~%" (fil file))) (sql-execute (funcall copy-cmd file extension) conn)) - (sql-create-indexes conn) + (When verbose (format t "UMLS Import: Creating SQL indices.~%")) + (sql-create-indexes conn :verbose verbose) + (When verbose (format t "UMLS Import: Creating custom tables.~%")) (sql-create-custom-tables conn) - (sql-create-indexes conn +custom-index-cols+) - (sql-create-special-tables conn)))) - -(defun translate-all-files (&optional (extension ".trans")) - "Copy translated files and return postgresql copy commands to import" - (make-noneng-index-file extension) + (When verbose (format t "UMLS Import: Creating custom indices.~%")) + (sql-create-indexes conn :indexes +custom-index-cols+ :verbose verbose) + (When verbose (format t "UMLS Import: Creating special tables.~%")) + (sql-create-special-tables conn))) + (When verbose (format t "UMLS Import: Completed.~%")) + t) + +(defun translate-all-files (&key (extension "-trans") verbose force) + "Translate all *umls-files* to optimized import format." + (when verbose (format t "UMLS Import: Translating file ~A.~%" (fil (find-ufile "MRXW_NONENG.RRF")))) + (make-noneng-index-file extension :force force) (dolist (f (remove "MRXW_NONENG.RRF" *umls-files* :test #'string= :key #'fil)) - (translate-umls-file f extension))) + (when verbose (format t "UMLS Import: Translating file ~A.~%" (fil f))) + (translate-umls-file f extension :force force))) -(defun translate-umls-file (file extension) +(defun translate-umls-file (file extension &key force) "Translate a umls file into a format suitable for sql copy cmd" - (translate-files file extension (list file))) + (translate-files file extension (list file) :force force)) -(defun make-noneng-index-file (extension) +(defun make-noneng-index-file (extension &key force) "Make non-english index file" (translate-files (find-ufile "MRXW_NONENG.RRF") - extension (noneng-lang-index-files))) - -(defun translate-files (out-ufile extension input-ufiles) + extension (noneng-lang-index-files) :force force)) + +(defun verify-translation-file (output-path input-ufiles) + "Returns t if translation file exists and is correct size. Warns and deletes incomplete translation file." + (when (probe-file output-path) + (let ((translated-lines 0) + (input-lines 0) + (eof (cons nil nil))) + (catch 'done-counting + (with-open-file (ts output-path :direction :input + #+(and clisp unicode) :external-format + #+(and clisp unicode) charset:utf-8) + (do () + ((eq (read-line ts nil eof) eof)) + (incf translated-lines))) + (dolist (input-ufile input-ufiles) + (with-umls-ufile (line input-ufile) + (incf input-lines) + (when (> input-lines translated-lines) + (throw 'done-counting 'incomplete))))) + (cond + ((< input-lines translated-lines) + (format t "Translated file ~A incomplete, deleting...~%" output-path) + (delete-file output-path) + nil) + ((eql input-lines translated-lines) + (format t "Translated file ~A already exists: skipping...~%" output-path) + t) + ((eql input-lines 0) + (warn "The number of input lines is 0 for output file ~A." output-path) + nil) + ((> translated-lines input-lines) + (error "Shouldn't happen. Translated lines of ~A is ~D, greater than input lines ~D" + output-path translated-lines input-lines) + (delete-file output-path) + nil))))) + +(defun translate-files (out-ufile extension input-ufiles &key force) "Translate a umls file into a format suitable for sql copy cmd" (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-ufile (line input-ufile) - (translate-line out-ufile line ostream) - (princ #\newline ostream))))))) + (when (and (not force) (verify-translation-file output-path input-ufiles)) + (return-from translate-files output-path)) + (with-open-file (ostream output-path :direction :output + :if-exists :overwrite + :if-does-not-exist :create + #+(and clisp unicode) :external-format + #+(and clisp unicode) charset:utf-8) + (dolist (input-ufile input-ufiles) + (with-umls-ufile (line input-ufile) + (translate-line out-ufile line ostream) + (princ #\newline ostream)))))) (defun translate-line (file line strm) "Translate a single line for sql output" @@ -295,25 +352,25 @@ This is much faster that using create-umls-db-insert." (write-to-string ui))) (escape-backslashes value)))) (print-separated-strings - strm "|" + strm "|" (mapcar #'col-value (remove-custom-cols (ucols file)) line) (custom-col-values (custom-ucols-for-file file) line nil)))) (defun pg-copy-cmd (file extension) - "Return postgresql copy statement for a file" + "Return postgresql copy statement for a file" (format nil "COPY ~a FROM '~a' using delimiters '|' with null as ''" (table file) (ufile-pathname file extension))) -(defun mysql-copy-cmd (file extension &key local-file) - "Return mysql copy statement for a file" +(defun mysql-copy-cmd (file extension &key (local-file t)) + "Return mysql copy statement for a file" (format nil "LOAD DATA ~AINFILE \"~a\" INTO TABLE ~a FIELDS TERMINATED BY \"|\"" (if local-file "LOCAL " "") - (ufile-pathname file extension) (table file))) + (namestring (ufile-pathname file extension)) (table file))) + - ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; ;;; ;;; Routines for analyzing cost of fixed size storage @@ -366,9 +423,9 @@ This is much faster that using create-umls-db-insert." (declare (optimize (speed 3) (space 0))) (ensure-ucols+ufiles) (let ((max 0)) - (declare (fixnum max)) + (declare (type (integer 0 1000000) max)) (dolist (ucol *umls-cols*) - (when (> (cmax ucol) max) + (when (> (the (integer 0 1000000) (cmax ucol)) max) (setq max (cmax ucol)))) max)) @@ -379,7 +436,11 @@ This is much faster that using create-umls-db-insert." (let ((rowsizes '())) (dolist (file *umls-files*) (let ((row 0)) + (declare (type (integer 0 1000000) row)) (dolist (ucol (ucols file)) - (incf row (1+ (cmax ucol)))) + (let* ((col-max (cmax ucol)) + (max-with-delim (1+ col-max))) + (declare (type (integer 0 1000000) col-max max-with-delim)) + (incf row max-with-delim))) (push row rowsizes))) (car (sort rowsizes #'>))))