X-Git-Url: http://git.kpe.io/?a=blobdiff_plain;f=create-sql.lisp;h=974087726b0dde19e71c699399e0311f15bcb7d4;hb=6c10979737dc10b0c510b1edfe070346bcac3ff5;hp=93c90d541af858522d8ec657aca23e6e15aa7b13;hpb=01888d11a058ecc8c1dcde23291f9cfbb3a307c0;p=umlisp.git diff --git a/create-sql.lisp b/create-sql.lisp index 93c90d5..9740877 100644 --- a/create-sql.lisp +++ b/create-sql.lisp @@ -7,10 +7,8 @@ ;;;; Author: Kevin M. Rosenberg ;;;; Created: Apr 2000 ;;;; -;;;; $Id$ -;;;; ;;;; This file, part of UMLisp, is -;;;; Copyright (c) 2000-2006 by Kevin M. Rosenberg, M.D. +;;;; Copyright (c) 2000-2010 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. @@ -46,7 +44,7 @@ " MAX_ROWS=200000000" "") (if (eq *umls-sql-type* :mysql) - " TYPE=MYISAM CHARACTER SET utf8" + " TYPE=MYISAM CHARACTER SET utf8 COLLATE utf8_bin" "")))) (defun create-custom-table-cmd (tablename sql-cmd) @@ -208,13 +206,56 @@ (format nil "DROP INDEX ~a" (concatenate 'string tablename "_" colname "_X"))))) +(defun sql-create-indexes-mysql (conn indexes verbose) + (let ((tables nil) + (table-cols nil)) + (dolist (idx indexes) + (pushnew (second idx) tables :test 'string-equal) + (let ((table-col (find (second idx) table-cols :key 'car :test 'string-equal))) + (if table-col + (vector-push-extend (cons (first idx) (third idx)) (second table-col)) + (push (list (second idx) (make-array (list 1) :initial-contents (list (cons (first idx) (third idx))) + :adjustable t :fill-pointer 1)) + table-cols)))) + (dolist (table tables) + (let ((table-col (find table table-cols :key 'car :test 'string-equal)) + (first t) + (str (format nil "ALTER TABLE ~A" table))) + (loop for col across (second table-col) + do + (let ((colname (car col)) + (length (cdr col))) + (ignore-errors (sql-execute (drop-index-cmd colname table) conn)) + (setq str (concatenate 'string + str + (if first + (progn + (setq first nil) + " ") + ", ") + (format nil "ADD INDEX ~A (~A)" + (concatenate 'string table "_" colname "_X") + (concatenate 'string + colname + (if (integerp length) + (format nil " (~d)" length) + ""))))))) + (when verbose + (format t "UMLS Import: Creating indexes for columns ~A on table ~A.~%" + (mapcar 'car (coerce (second table-col) 'list)) table)) + (when conn + (sql-execute str conn)) + )))) + (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))) + (if (eql :mysql *umls-sql-type*) + (sql-create-indexes-mysql conn indexes verbose) + (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)))) (defun make-usrl (conn) (if (eql :mysql *umls-sql-type*) @@ -264,15 +305,15 @@ This is much faster that using create-umls-db-insert." (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)) - (When verbose (format t "UMLS Import: Creating SQL indices.~%")) + (when verbose (format t "UMLS Import: Creating SQL indices.~%")) (sql-create-indexes conn :verbose verbose) - (When verbose (format t "UMLS Import: Creating custom tables.~%")) + (when verbose (format t "UMLS Import: Creating custom tables.~%")) (sql-create-custom-tables conn) - (When verbose (format t "UMLS Import: Creating custom indices.~%")) + (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.~%")) + (when verbose (format t "UMLS Import: Creating special tables.~%")) (sql-create-special-tables conn))) - (When verbose (format t "UMLS Import: Completed.~%")) + (when verbose (format t "UMLS Import: Completed.~%")) t) (defun translate-all-files (&key (extension "-trans") verbose force) @@ -298,18 +339,24 @@ This is much faster that using create-umls-db-insert." (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))))) + (with-open-file (ts output-path :direction :input + #+(and sbcl sb-unicode) :external-format + #+(and sbcl sb-unicode) :UTF-8 + #+(and allegro ics) :external-format + #+(and allegro ics) :UTF-8 + #+lispworks :external-format + #+lispworks :UTF-8 + #+(and clisp unicode) :external-format + #+(and clisp unicode) charset:utf-8) + (do ((c (read-char ts nil eof) (read-char ts nil eof))) + ((eq c eof)) + (when (eql c #\newline) + (incf translated-lines)))) + (dolist (input-ufile input-ufiles) + (with-umls-ufile (line input-ufile) + (incf input-lines) + (when (> input-lines translated-lines) + (return)))) (cond ((< input-lines translated-lines) (format t "Translated file ~A incomplete, deleting...~%" output-path) @@ -335,6 +382,12 @@ This is much faster that using create-umls-db-insert." (with-open-file (ostream output-path :direction :output :if-exists :overwrite :if-does-not-exist :create + #+(and sbcl sb-unicode) :external-format + #+(and sbcl sb-unicode) :UTF-8 + #+(and allegro ics) :external-format + #+(and allegro ics) :UTF-8 + #+lispworks :external-format + #+lispworks :UTF-8 #+(and clisp unicode) :external-format #+(and clisp unicode) charset:utf-8) (dolist (input-ufile input-ufiles) @@ -366,7 +419,7 @@ This is much faster that using create-umls-db-insert." "Return mysql copy statement for a file" (format nil - "LOAD DATA ~AINFILE \"~a\" INTO TABLE ~a FIELDS TERMINATED BY \"|\"" + "LOAD DATA ~AINFILE '~a' INTO TABLE ~a FIELDS TERMINATED BY '|'" (if local-file "LOCAL " "") (namestring (ufile-pathname file extension)) (table file)))