X-Git-Url: http://git.kpe.io/?a=blobdiff_plain;f=create-sql.lisp;h=3b8b39ca912589b131958be15331f42f7794bcae;hb=62ce365967f05ec6a2945a20cd5c45efb7b809e5;hp=8632daab775a4d089aa0b3e900fcd0737c694310;hpb=9c0bccac3fb46e0cb8ab6d2a63b7fc5a92221002;p=umlisp.git diff --git a/create-sql.lisp b/create-sql.lisp index 8632daa..3b8b39c 100644 --- a/create-sql.lisp +++ b/create-sql.lisp @@ -2,15 +2,15 @@ ;;;; ************************************************************************* ;;;; FILE IDENTIFICATION ;;;; -;;;; Name: sql-create -;;;; Purpose: Create SQL database for UMLisp -;;;; Author: Kevin M. Rosenberg -;;;; Date Started: Apr 2000 +;;;; Name: sql-create +;;;; Purpose: Create SQL database for UMLisp +;;;; Author: Kevin M. Rosenberg +;;;; Created: Apr 2000 ;;;; -;;;; $Id: create-sql.lisp,v 1.5 2003/05/09 11:02:44 kevin Exp $ +;;;; $Id$ ;;;; ;;;; This file, part of UMLisp, is -;;;; Copyright (c) 2000-2002 by Kevin M. Rosenberg, M.D. +;;;; Copyright (c) 2000-2004 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. @@ -18,23 +18,33 @@ (in-package #:umlisp) -(eval-when (:compile-toplevel) - (declaim (optimize (speed 3) (safety 1) (compilation-speed 0) (debug 3)))) - (defun create-table-cmd (file) "Return sql command to create a table" (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" @@ -96,19 +106,31 @@ (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 (defun create-index-cmd (colname tablename length) "Return sql create index command" - (format nil "CREATE INDEX ~a ON ~a (~a ~a)" + (format nil "CREATE INDEX ~a ON ~a (~a)" (concatenate 'string tablename "_" colname "_X") - tablename colname - (if (integerp length) (format nil "(~d)" length) ""))) + tablename + (case *umls-sql-type* + (:mysql + (concatenate 'string colname + (if (integerp length) + (format nil " (~d)" length) + ""))) + ((:postgresql :postgresql-socket) + ;; FIXME: incorrect syntax + (if (integerp length) + (format nil "substr((~A)::text,1,~D)" colname length) + colname)) + (t + colname)))) (defun create-all-tables-cmdfile () "Return sql commands to create all tables. Not need for automated SQL import" @@ -127,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))) @@ -142,13 +192,27 @@ (dolist (file *umls-files*) (sql-insert-values conn file))) +(defun drop-index-cmd (colname tablename) + "Return sql create index command" + (case *umls-sql-type* + (:mysql + (format nil "DROP INDEX ~a ON ~a" + (concatenate 'string tablename "_" colname "_X") + tablename)) + (t + (format nil "DROP INDEX ~a" + (concatenate 'string tablename "_" colname "_X"))))) + (defun sql-create-indexes (conn &optional (indexes +index-cols+)) "SQL Databases: create all indexes" (dolist (idx indexes) + (ignore-errors (sql-execute (drop-index-cmd (car idx) (cadr idx)) conn)) (sql-execute (create-index-cmd (car idx) (cadr idx) (caddr idx)) conn))) (defun make-usrl (conn) - (sql-execute "drop table if exists USRL" conn) + (if (eql :mysql *umls-sql-type*) + (sql-execute "drop table if exists USRL" conn) + (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")) @@ -157,7 +221,8 @@ conn))) (defun sql-create-special-tables (conn) - (make-usrl conn)) + (make-usrl conn) + (make-ustats)) (defun create-umls-db-by-insert () "SQL Databases: initializes entire database via SQL insert commands" @@ -172,27 +237,32 @@ (sql-create-indexes conn +custom-index-cols+) (sql-create-special-tables conn))) -(defun create-umls-db (&optional (extension ".trans") - (copy-cmd #'mysql-copy-cmd)) +(defun create-umls-db (&key (extension ".trans") (skip-translation nil)) "SQL Databases: initializes entire database via SQL copy commands. This is much faster that using create-umls-db-insert." (ensure-ucols+ufiles) (ensure-preparse) - (translate-all-files extension) - (with-sql-connection (conn) - (sql-drop-tables conn) - (sql-create-tables conn) - (dolist (file *umls-files*) - (sql-execute (funcall copy-cmd file extension) conn)) - (sql-create-indexes conn) - (sql-create-custom-tables conn) - (sql-create-indexes conn +custom-index-cols+) - (sql-create-special-tables conn))) + (unless skip-translation + (translate-all-files extension)) + (let ((copy-cmd + (ecase (umls-sql-type) + (:mysql #'mysql-copy-cmd) + (:postgresql #'pg-copy-cmd)))) + (with-sql-connection (conn) + (clsql:truncate-database :database conn) + (sql-drop-tables conn) + (sql-create-tables conn) + (dolist (file *umls-files*) + (sql-execute (funcall copy-cmd file extension) conn)) + (sql-create-indexes conn) + (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) - (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) @@ -201,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))))))) @@ -233,14 +303,15 @@ 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) +(defun mysql-copy-cmd (file extension &key local-file) "Return mysql copy statement for a file" (format nil - "LOAD DATA LOCAL INFILE \"~a\" INTO TABLE ~a FIELDS TERMINATED BY \"|\"" - (umls-pathname (fil file) extension) (table file))) + "LOAD DATA ~AINFILE \"~a\" INTO TABLE ~a FIELDS TERMINATED BY \"|\"" + (if local-file "LOCAL " "") + (ufile-pathname file extension) (table file))) ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;