From: Kevin M. Rosenberg Date: Mon, 15 Aug 2005 03:23:22 +0000 (+0000) Subject: r10633: define table type and charset for mysql X-Git-Tag: v2006ac.2~73 X-Git-Url: http://git.kpe.io/?p=umlisp.git;a=commitdiff_plain;h=349464972190f5689f28e9894c666b712409f1fd;ds=sidebyside r10633: define table type and charset for mysql --- diff --git a/create-sql.lisp b/create-sql.lisp index 15506cc..4658a87 100644 --- a/create-sql.lisp +++ b/create-sql.lisp @@ -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 DEFAULT CHARACTER latin1" + "")))) (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 @@ -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))) @@ -169,7 +172,7 @@ (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 +184,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))) @@ -207,7 +210,7 @@ "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))) + (sql-execute (create-index-cmd (car idx) (cadr idx) (caddr idx)) conn))) (defun make-usrl (conn) (if (eql :mysql *umls-sql-type*) @@ -216,7 +219,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))) @@ -238,7 +241,7 @@ (sql-create-special-tables conn))) (defun create-umls-db (&key (extension ".trans") (skip-translation nil)) - "SQL Databases: initializes entire database via SQL copy commands. + "SQL Databases: initializes entire database via SQL copy commands. This is much faster that using create-umls-db-insert." (ensure-ucols+ufiles) (ensure-preparse) @@ -295,25 +298,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" + "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))) - + ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; ;;; ;;; Routines for analyzing cost of fixed size storage