(format nil " (~d)" length)
"")))
((:postgresql :postgresql-socket)
+ ;; FIXME: incorrect syntax
(if (integerp length)
- (format nil "substr(~A,1,~D)" colname length)
+ (format nil "substr((~A)::text,1,~D)" colname length)
colname))
(t
colname))))
(dolist (file *umls-files*)
(sql-insert-values conn file)))
+(defun drop-index-cmd (colname tablename)
+ "Return sql create index command"
+ (case *umls-sql-type*
+ (:mysql
+ (format nil "DROP INDEX ~a ON ~a"
+ (concatenate 'string tablename "_" colname "_X")
+ tablename))
+ (t
+ (format nil "DROP INDEX ~a"
+ (concatenate 'string tablename "_" colname "_X")))))
+
(defun sql-create-indexes (conn &optional (indexes +index-cols+))
"SQL Databases: create all indexes"
(dolist (idx indexes)
+ (ignore-errors (sql-execute (drop-index-cmd (car idx) (cadr idx)) conn))
(sql-execute (create-index-cmd (car idx) (cadr idx) (caddr idx)) conn)))
(defun make-usrl (conn)
(sql-create-indexes conn +custom-index-cols+)
(sql-create-special-tables conn)))
-(defun create-umls-db (&optional (extension ".trans"))
+(defun create-umls-db (&key (extension ".trans") (skip-translation nil))
"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)
+ (unless skip-translation
+ (translate-all-files extension))
(let ((copy-cmd
(ecase (umls-sql-type)
(:mysql #'mysql-copy-cmd)
(:postgresql #'pg-copy-cmd))))
(with-sql-connection (conn)
+ (clsql:truncate-database :database conn)
(sql-drop-tables conn)
(sql-create-tables conn)
(dolist (file *umls-files*)
nil "COPY ~a FROM '~a' using delimiters '|' with null as ''"
(table file) (umls-pathname (fil file) extension)))
-(defun mysql-copy-cmd (file extension)
+(defun mysql-copy-cmd (file extension &key local-file)
"Return mysql copy statement for a file"
(format
nil
- "LOAD DATA LOCAL INFILE \"~a\" INTO TABLE ~a FIELDS TERMINATED BY \"|\""
+ "LOAD DATA ~AINFILE \"~a\" INTO TABLE ~a FIELDS TERMINATED BY \"|\""
+ (if local-file "LOCAL " "")
(umls-pathname (fil file) extension) (table file)))