X-Git-Url: http://git.kpe.io/?a=blobdiff_plain;f=create-sql.lisp;h=e212536b79bd5a1ca58445c7bf66937a6b5e7e44;hb=aeade16272b79115d3f307906c7a3e9597137e97;hp=e81440d1c51be43f40025f82598bdd06c9830ade;hpb=3199369942d2e5ab4f5b060c2c6b655caf505944;p=umlisp.git diff --git a/create-sql.lisp b/create-sql.lisp index e81440d..e212536 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.1 2003/05/07 21:57:06 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,9 +18,6 @@ (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 @@ -49,8 +46,9 @@ "Return sql insert command for a row of values" (let ((insert-func (lambda (col value) - (let ((q (quotechar col))) - (concatenate 'string q (insert-col-value col value) q))))) + (concatenate 'string (quote-str col) + (insert-col-value col value) + (quote-str col))))) (format nil "INSERT INTO ~a (~{~a~^,~}) VALUES (~A)" (table file) @@ -65,8 +63,9 @@ (let ((custom-value (funcall (custom-value-fun col) values))) (if custom-value (if doquote - (let ((q (quotechar col))) - (concatenate 'string q (escape-backslashes custom-value) q)) + (concatenate 'string (quote-str col) + (escape-backslashes custom-value) + (quote-str col)) (escape-backslashes custom-value)) ""))) @@ -103,10 +102,21 @@ (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) + (if (integerp length) + (format nil "substr(~A,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" @@ -146,7 +156,9 @@ (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")) @@ -155,12 +167,13 @@ 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) @@ -170,27 +183,38 @@ (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 (&optional (extension ".trans")) "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) + (ignore-errors + (clsql:destroy-database (list *umls-sql-host* (lookup-db-name *umls-sql-db*) + *umls-sql-user* *umls-sql-passwd*) + :database-type *umls-sql-type*)) + (clsql:create-database (list *umls-sql-host* (lookup-db-name *umls-sql-db*) + *umls-sql-user* *umls-sql-passwd*) + :database-type *umls-sql-type*) + (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))) + (let ((copy-cmd + (ecase (umls-sql-type) + (:mysql #'mysql-copy-cmd) + (:postgresql #'pg-copy-cmd)))) + (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)))) (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" @@ -274,7 +298,7 @@ This is much faster that using create-umls-db-insert." (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) @@ -290,7 +314,7 @@ This is much faster that using create-umls-db-insert." (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*) @@ -301,7 +325,7 @@ This is much faster that using create-umls-db-insert." (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))