r10633: define table type and charset for mysql

diff --git a/create-sql.lisp b/create-sql.lisp
index 15506cca76bdb539e6cf812f0452186d8a853823..4658a877ea7382c59a4ab2b892bb3ca8cd37f0f9 100644 (file)
--- 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