;;;; *************************************************************************
;;;; 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.2 2003/05/07 22:53:36 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.
(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
(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"
(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"))
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"
- (ensure-init-umls)
- (init-hash-table)
+ (ensure-ucols+ufiles)
+ (ensure-preparse)
(with-sql-connection (conn)
(sql-drop-tables conn)
(sql-create-tables conn)
(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-init-umls)
- (init-hash-table)
- (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)))
+ (ensure-ucols+ufiles)
+ (ensure-preparse)
+ (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 *umls-files*) (translate-umls-file f extension)))
+ (dolist (f (remove "MRXW.NONENG" *umls-files* :test #'string= :key #'fil))
+ (translate-umls-file f extension)))
(defun translate-umls-file (file extension)
"Translate a umls file into a format suitable for sql copy cmd"
nil "COPY ~a FROM '~a' using delimiters '|' with null as ''"
(table file) (umls-pathname (fil 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 \"|\""
+ "LOAD DATA ~AINFILE \"~a\" INTO TABLE ~a FIELDS TERMINATED BY \"|\""
+ (if local-file "LOCAL " "")
(umls-pathname (fil file) extension) (table file)))
(nreverse avoidable) (nreverse unavoidable))))
(defun display-waste ()
- (ensure-init-umls)
+ (ensure-ucols+ufiles)
(multiple-value-bind (tw ta tu al ul) (umls-fixed-size-waste)
(format t "Total waste: ~d~%" tw)
(format t "Total avoidable: ~d~%" ta)
(defun max-umls-field ()
"Return length of longest field"
(declare (optimize (speed 3) (space 0)))
- (ensure-init-umls)
+ (ensure-ucols+ufiles)
(let ((max 0))
(declare (fixnum max))
(dolist (ucol *umls-cols*)
(defun max-umls-row ()
"Return length of longest row"
(declare (optimize (speed 3) (space 0)))
- (ensure-init-umls)
+ (ensure-ucols+ufiles)
(let ((rowsizes '()))
(dolist (file *umls-files*)
(let ((row 0))