X-Git-Url: http://git.kpe.io/?p=umlisp.git;a=blobdiff_plain;f=create-sql.lisp;h=20964f5c4ae3a6a8f4023ecf033582a759061856;hp=974087726b0dde19e71c699399e0311f15bcb7d4;hb=HEAD;hpb=20b21169e71e1a70b95289fb0e6a6a7a8a55153e

diff --git a/create-sql.lisp b/create-sql.lisp
index 9740877..3a19020 100644
--- 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"))
@@ -44,7 +44,7 @@
                 " MAX_ROWS=200000000"
               "")
             (if (eq *umls-sql-type* :mysql)
-                " TYPE=MYISAM CHARACTER SET utf8 COLLATE utf8_bin"
+                " ENGINE=MYISAM CHARACTER SET utf8 COLLATE utf8_bin"
                 ""))))
 
 (defun create-custom-table-cmd (tablename sql-cmd)
@@ -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)