Improve special table generation

diff --git a/create-sql.lisp b/create-sql.lisp
index 71195c3962b0381cdb4cec0a114e5cf4c2953949..27f65de0b431681ea6c3a5148b6cfea2a922d13c 100644 (file)
--- a/create-sql.lisp
+++ b/create-sql.lisp
@@ -261,16 +261,56 @@
   (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)
+  (sql-execute
+   (concatenate 'string
+                "create table USRL (sab varchar(80), srl integer)"
+                (if (eq *umls-sql-type* :mysql)
+                    " ENGINE=MYISAM CHARACTER SET utf8 COLLATE utf8_bin"
+                    ""))
+   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)"
                          (car tuple) (ensure-integer (cadr tuple)))
                  conn)))
 
+(defun make-ustats (conn)
+  (ignore-errors (sql-execute "drop table USTATS" conn))
+  (sql-execute
+   (concatenate 'string"create table USTATS (NAME varchar(160), COUNT bigint, SRL integer)"
+                (if (eq *umls-sql-type* :mysql)
+                    " ENGINE=MYISAM CHARACTER SET utf8 COLLATE utf8_bin"
+                    ""))
+   conn)
+
+  (dolist (srl '(0 1 2 3 4 9))
+    (insert-ustats-count conn "Concept Count" "MRCONSO" "distinct CUI" "KCUILRL" srl)
+    (insert-ustats-count conn "Term Count" "MRCONSO" "distinct KCUILUI" "KCUILRL" srl)
+    (insert-ustats-count conn "Distinct Term Count" "MRCONSO" "distinct LUI" "KLUILRL" srl)
+    (insert-ustats-count conn "String Count" "MRCONSO" "*" "KSUILRL" srl)
+    (insert-ustats-count conn "Distinct String Count" "MRCONSO" "distinct SUI" "KSUILRL" srl)
+    (insert-ustats-count conn "Hierarchcy" "MRHIER" "*" "KSRL" srl)
+    (insert-ustats-count conn "Mappings" "MRMAP" "*" "KSRL" srl)
+    (insert-ustats-count conn "Simple Mappings" "MRSMAP" "*" "KSRL" srl)
+    (insert-ustats-count conn "Co-occuring Concept Count" "MRCOC" "*" "KLRL" srl)
+    (insert-ustats-count conn "Definition Count" "MRDEF" "*" "KSRL" srl)
+    (insert-ustats-count conn "Rank Count" "MRRANK" "*" "KSRL" srl)
+    (insert-ustats-count conn "Relationship Count" "MRREL" "*" "KSRL" srl)
+    (insert-ustats-count conn "Semantic Type Count" "MRSTY" "*" "KLRL" srl)
+    (insert-ustats-count conn "Simple Attribute Count" "MRSAT" "*" "KSRL" srl)
+    (insert-ustats-count conn "Source Abbreviation Count" "MRSAB" "*" "SRL" srl)
+    (insert-ustats-count conn "Word Index Count" "MRXW_ENG" "*" "KLRL" srl)
+    (insert-ustats-count conn "Normalized Word Index Count" "MRXNW_ENG" "*" "KLRL" srl)
+    (insert-ustats-count conn "Normalized String Index Count" "MRXNS_ENG" "*" "KLRL" srl))
+  (sql-execute "create index USTATS_SRL on USTATS (SRL)" conn)
+  (find-ustats-all))
+
+(defun insert-ustats-count (conn name table count-variable srl-control srl)
+  (insert-ustats conn name (find-count-table conn table srl count-variable srl-control) srl))
+
 (defun sql-create-special-tables (conn)
   (make-usrl conn)
-  (make-ustats))
+  (make-ustats conn))
 
 (defun create-umls-db-by-insert (&key verbose)
   "SQL Databases: initializes entire database via SQL insert commands"

diff --git a/sql-classes.lisp b/sql-classes.lisp
index df23c492e224aae5812ddd15314655f4642534a1..7736eb6ccc4107bca8f8321d4fa4141bfa75eee4 100644 (file)
--- a/sql-classes.lisp
+++ b/sql-classes.lisp
@@ -1092,37 +1092,8 @@ is OBJNAME from TABLE where WHERE-NAME field = WHERE-VALUE with FIELDS"
 ;;; **************************
 
 
-(defun make-ustats ()
-  (with-sql-connection (conn)
-    (ignore-errors (sql-execute "drop table USTATS" conn))
-    (sql-execute "create table USTATS (NAME varchar(160), COUNT bigint, SRL integer)" conn)
-
-    (dolist (srl '(0 1 2 3 4 9))
-      (insert-ustats-count conn "Concept Count" "MRCONSO" "distinct CUI" "KCUILRL" srl)
-      (insert-ustats-count conn "Term Count" "MRCONSO" "distinct KCUILUI" "KCUILRL" srl)
-      (insert-ustats-count conn "Distinct Term Count" "MRCONSO" "distinct LUI" "KLUILRL" srl)
-      (insert-ustats-count conn "String Count" "MRCONSO" "*" "KSUILRL" srl)
-      (insert-ustats-count conn "Distinct String Count" "MRCONSO" "distinct SUI" "KSUILRL" srl)
-      (insert-ustats-count conn "Hierarchcy" "MRHIER" "*" "KSRL" srl)
-      (insert-ustats-count conn "Mappings" "MRMAP" "*" "KSRL" srl)
-      (insert-ustats-count conn "Simple Mappings" "MRSMAP" "*" "KSRL" srl)
-      (insert-ustats-count conn "Co-occuring Concept Count" "MRCOC" "*" "KLRL" srl)
-      (insert-ustats-count conn "Definition Count" "MRDEF" "*" "KSRL" srl)
-      (insert-ustats-count conn "Rank Count" "MRRANK" "*" "KSRL" srl)
-      (insert-ustats-count conn "Relationship Count" "MRREL" "*" "KSRL" srl)
-      (insert-ustats-count conn "Semantic Type Count" "MRSTY" "*" "KLRL" srl)
-      (insert-ustats-count conn "Simple Attribute Count" "MRSAT" "*" "KSRL" srl)
-      (insert-ustats-count conn "Source Abbreviation Count" "MRSAB" "*" "SRL" srl)
-      (insert-ustats-count conn "Word Index Count" "MRXW_ENG" "*" "KLRL" srl)
-      (insert-ustats-count conn "Normalized Word Index Count" "MRXNW_ENG" "*" "KLRL" srl)
-      (insert-ustats-count conn "Normalized String Index Count" "MRXNS_ENG" "*" "KLRL" srl))
-    (sql-execute "create index USTATS_SRL on USTATS (SRL)" conn))
-  (find-ustats-all))
-
-(defun insert-ustats-count (conn name table count-variable srl-control srl)
-  (insert-ustats conn name (find-count-table conn table srl count-variable srl-control) srl))
-
 (defun find-count-table (conn table srl count-variable srl-control)
+  (with-sql-connection (conn)
   (cond
    ((stringp srl-control)
     (ensure-integer
@@ -1136,7 +1107,7 @@ is OBJNAME from TABLE where WHERE-NAME field = WHERE-VALUE with FIELDS"
                       conn))))
    (t
     (error "Unknown srl-control")
-    0)))
+    0))))
 
 (defun insert-ustats (conn name count srl)
   (sql-execute (format nil "insert into USTATS (name,count,srl) values ('~a',~d,~d)"

diff --git a/sql.lisp b/sql.lisp
index 3847d02492692f4d5da0c2ee85f87b0be93a937e..1630562e6830b59fd8d3d9a150487726f6886493 100644 (file)
--- a/sql.lisp
+++ b/sql.lisp
@@ -71,7 +71,8 @@
   (if (eql *umls-sql-type* :mysql)
       (list *umls-sql-host* *umls-sql-db*
             *umls-sql-user* *umls-sql-passwd*
-            nil '((:local-infile . 1)))
+            nil '((:local-infile . 1))
+            )
     (list *umls-sql-host* *umls-sql-db*
           *umls-sql-user* *umls-sql-passwd*)))