From cd3df5cb167a754ef0d2f9cb21afeb821528f266 Mon Sep 17 00:00:00 2001 From: "Kevin M. Rosenberg" Date: Wed, 10 Apr 2013 16:58:49 -0600 Subject: [PATCH] Improve special table generation --- create-sql.lisp | 44 ++++++++++++++++++++++++++++++++++++++++++-- sql-classes.lisp | 33 ++------------------------------- sql.lisp | 3 ++- 3 files changed, 46 insertions(+), 34 deletions(-) diff --git a/create-sql.lisp b/create-sql.lisp index 71195c3..27f65de 100644 --- 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 df23c49..7736eb6 100644 --- 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 3847d02..1630562 100644 --- 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*))) -- 2.34.1