Change default SQL server host

[umlisp.git] / create-sql.lisp

diff --git a/create-sql.lisp b/create-sql.lisp
index 71195c3962b0381cdb4cec0a114e5cf4c2953949..3a1902019df6632feb72dcfecb2d3595913912c7 100644 (file)
--- a/create-sql.lisp
+++ b/create-sql.lisp
@@ -30,7 +30,7 @@
                   (setq sqltype "VARCHAR2(20)")))))
 
              (concatenate 'string
-               (col c)
+               (sqlname c)
                " "
                (if (or (string-equal sqltype "VARCHAR")
                        (string-equal sqltype "CHAR"))
@@ -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)
+#+mrcoc (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"
@@ -363,7 +403,7 @@ This is much faster that using create-umls-db-insert."
           (delete-file output-path)
           nil)
         ((eql input-lines translated-lines)
-          (format t "Translated file ~A already exists: skipping...~%" output-path)
+          (format t "Translated file ~A exists and is proper number of lines: skipping...~%" output-path)
           t)
         ((eql input-lines 0)
           (warn "The number of input lines is 0 for output file ~A." output-path)