From 5be6bdf06e0c99612a4c1a1d6f110e424400d96e Mon Sep 17 00:00:00 2001
From: "Kevin M. Rosenberg" <kevin@rosenberg.net>
Date: Mon, 19 Apr 2004 11:02:20 +0000
Subject: [PATCH] r9093: changes for postgresql

---
 create-sql.lisp   | 53 +++++++++++++++++++++++++++++++++--------------
 parse-2002.lisp   |  3 ++-
 parse-common.lisp |  4 +++-
 sql-classes.lisp  |  2 +-
 4 files changed, 44 insertions(+), 18 deletions(-)

diff --git a/create-sql.lisp b/create-sql.lisp
index f842eb2..ec162b8 100644
--- a/create-sql.lisp
+++ b/create-sql.lisp
@@ -102,10 +102,21 @@
 
 (defun create-index-cmd (colname tablename length)
   "Return sql create index command"
-  (format nil "CREATE INDEX ~a ON ~a (~a ~a)"
+  (format nil "CREATE INDEX ~a ON ~a (~a)"
 	  (concatenate 'string tablename "_" colname "_X")
-	  tablename colname
-	  (if (integerp length) (format nil "(~d)" length) "")))
+	  tablename 
+	  (case *umls-sql-type*
+	    (:mysql
+	     (concatenate 'string colname
+			  (if (integerp length)
+			      (format nil " (~d)" length)
+			      "")))
+	    ((:postgresql :postgresql-socket)
+	     (if (integerp length)
+		 (format nil "substr(~A,1,~D)" colname length)
+		 colname))
+	    (t
+	     colname))))
 
 (defun create-all-tables-cmdfile ()
   "Return sql commands to create all tables. Not need for automated SQL import"
@@ -145,7 +156,9 @@
     (sql-execute (create-index-cmd (car idx) (cadr idx) (caddr idx)) conn))) 
 
 (defun make-usrl (conn)
-  (sql-execute "drop table if exists USRL" conn)
+  (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)
   (dolist (tuple (mutex-sql-query
 		  "select distinct SAB,SRL from MRSO order by SAB asc"))
@@ -170,22 +183,32 @@
     (sql-create-indexes conn +custom-index-cols+)
     (sql-create-special-tables conn)))
 
-(defun create-umls-db (&optional (extension ".trans") 
-		       (copy-cmd #'mysql-copy-cmd))
+(defun create-umls-db (&optional (extension ".trans"))
   "SQL Databases: initializes entire database via SQL copy commands. 
 This is much faster that using create-umls-db-insert."
+  (ignore-errors
+    (clsql:destroy-database (list *umls-sql-host* (lookup-db-name *umls-sql-db*)
+				  *umls-sql-user* *umls-sql-passwd*) 
+			    :database-type *umls-sql-type*))
+  (clsql:create-database (list *umls-sql-host* (lookup-db-name *umls-sql-db*)
+			       *umls-sql-user* *umls-sql-passwd*) 
+			 :database-type *umls-sql-type*)
   (ensure-ucols+ufiles)
   (ensure-preparse)
   (translate-all-files extension)
-  (with-sql-connection (conn)
-    (sql-drop-tables conn)
-    (sql-create-tables conn)
-    (dolist (file *umls-files*)
-      (sql-execute (funcall copy-cmd file extension) conn))
-    (sql-create-indexes conn)
-    (sql-create-custom-tables conn)
-    (sql-create-indexes conn +custom-index-cols+)
-    (sql-create-special-tables conn)))
+  (let ((copy-cmd
+	 (ecase (umls-sql-type)
+	   (:mysql #'mysql-copy-cmd)
+	   (:postgresql #'pg-copy-cmd))))
+    (with-sql-connection (conn)
+      (sql-drop-tables conn)
+      (sql-create-tables conn)
+      (dolist (file *umls-files*)
+	(sql-execute (funcall copy-cmd file extension) conn))
+      (sql-create-indexes conn)
+      (sql-create-custom-tables conn)
+      (sql-create-indexes conn +custom-index-cols+)
+      (sql-create-special-tables conn))))
 
 (defun translate-all-files (&optional (extension ".trans"))
   "Copy translated files and return postgresql copy commands to import"
diff --git a/parse-2002.lisp b/parse-2002.lisp
index 4c7e5a1..ae126a0 100644
--- a/parse-2002.lisp
+++ b/parse-2002.lisp
@@ -122,11 +122,12 @@
 ;;; sql-i - Integer (32-bit)
 ;;; sql-l - Big integer (64-bit)
 ;;; sql-f - Floating point
+;;; sql-c - Character data
 
 (defparameter +col-datatypes+
     '(("AV" sql-f) ("BTS" sql-i) ("CLS" sql-i) ("COF" sql-i) ("CUI1" sql-u)
       ("CUI2" sql-u) ("CUI" sql-u) ("CXN" sql-s) ("FR" sql-i) ("LRL" sql-s)
-      ("LUI" sql-u) ("MAX" sql-s) ("MIN" sql-s) ("RANK" sql-s) ("REF" sql-s)
+      ("LUI" sql-u) ("MAX" sql-s) ("MIN" sql-s) ("RANK" sql-s) ("REF" sql-c)
       ("RNK" sql-s) ("RWS" sql-i) ("SRL" sql-s) ("SUI" sql-u) ("TUI" sql-u)
       ;;; Custom columns
       ("KCUISUI" sql-l) ("KCUILUI" sql-l) ("KCUILRL" sql-i) ("KLUILRL" sql-i)
diff --git a/parse-common.lisp b/parse-common.lisp
index 452d4ec..28a57db 100644
--- a/parse-common.lisp
+++ b/parse-common.lisp
@@ -176,7 +176,9 @@ Currently, these are the LEX and NET files."
 		  (quote-str "'") (custom-value-fun))
   (let ((ucol (make-instance
 	       'ucol
-	       :col col :des des :ref ref :min min :av av :max max :fil fil
+	       :col col :des des :ref ref :min min :av av 
+	       :max (if (eql max 0) 1 max) ;; ensure at least one char wide
+	       :fil fil
 	       :dty dty :sqltype sqltype :quote-str quote-str
 	       :parse-fun (ensure-compiled-fun parse-fun)
  	       :custom-value-fun (ensure-compiled-fun custom-value-fun))))
diff --git a/sql-classes.lisp b/sql-classes.lisp
index 6b606db..48e7d54 100644
--- a/sql-classes.lisp
+++ b/sql-classes.lisp
@@ -790,7 +790,7 @@ is OBJNAME from TABLE where WHERE-NAME field = WHERE-VALUE with FIELDS"
 
 (defun make-ustats ()
   (with-sql-connection (conn)
-    (sql-execute "drop table if exists USTATS" conn)
+    (ignore-errors (sql-execute "drop table USTATS" conn))
     (sql-execute "create table USTATS (NAME varchar(160), COUNT bigint, SRL integer)" conn)
     
     (dotimes (srl 4)
-- 
2.34.1