(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"
;;; **************************
-(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
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)"