-(defmacro query-string (table fields &optional srl where-name where-value
- &key (lrl "KCUILRL") single distinct order like)
- (let* ((%%fields (format nil "select ~A~{~:@(~A~)~^,~} from ~:@(~A~)"
- (if distinct "distinct " "") fields table))
- (%%order (if order (format nil " order by ~{~:@(~A~) ~(~A~)~^,~}"
- order)
- ""))
- (%%lrl (format nil " and ~:@(~A~)<=" lrl))
- (%%where (when where-name
- (format nil " where ~:@(~A~)~A" where-name
- (if like " like " "")))))
- `(concatenate
- 'string
- ,%%fields
- ,@(when %%where (list %%where))
- ,@(when %%where
- `((typecase ,where-value
- (fixnum
- (prefixed-fixnum-string ,where-value #\= 10))
- (number
- (concatenate 'string "=" (write-to-string ,where-value)))
- (null
- " is null")
- (t
- (format nil ,(if like "'%~A%'" "='~A'") ,where-value)))))
- (if ,srl (concatenate 'string ,%%lrl (write-to-string ,srl)) "")
- ,@(when %%order (list %%order))
- ,@(when single (list " limit 1")))))
-
-(defun query-string-eval (table fields &optional srl where-name where-value
- &key (lrl "KCUILRL") single distinct order like)
+;; SQLNAME is required for collision of SQL reserved words (MYSQL 8: RANK)
+;; and column names in UMLS (RANK in MRRANK)
+(defvar *sql-reserved-names* '("RANK"))
+(defmethod sqlname ((c ucol))
+ (sqlname (col c)))
+(defmethod sqlname ((name string))
+ (if (find name *sql-reserved-names* :test #'string-equal)
+ (concatenate 'string "_" name)
+ name))
+(defmethod sqlname ((l list))
+ (mapcar #'sqlname l))
+(defmethod sqlname ((s symbol))
+ (sqlname (symbol-name s)))
+
+(defmacro query-string (table fields srl where-name where-value
+ &key (lrl "KCUILRL") single distinct order like limit
+ filter)
+ (let* ((%%fields (format nil "SELECT ~A~{~:@(~A~)~^,~} FROM ~:@(~A~)"
+ (if distinct "DISTINCT " "") (sqlname fields) table))
+ (%%order (if order (format nil " ORDER BY ~{~:@(~A~) ~(~A~)~^,~}"
+ order)
+ ""))
+ (%%lrl (format nil " AND ~:@(~A~)<=" lrl))
+ (%%where (when where-name
+ (format nil " WHERE ~:@(~A~)~A" (sqlname where-name)
+ (if like " like " ""))))
+ (%filter (gensym "FILTER-"))
+ (%single (gensym "SINGLE-"))
+ (%limit (gensym "LIMIT-")))
+ `(let ((,%limit ,limit)
+ (,%single ,single)
+ (,%filter ,filter))
+ (concatenate
+ 'string
+ ,%%fields
+ ,@(when %%where (list %%where))
+ ,@(when %%where
+ `((typecase ,where-value
+ #+ignore
+ (fixnum
+ (concatenate 'string "='" (prefixed-fixnum-string ,where-value #\0 10) "'"))
+ (number
+ (concatenate 'string "='" (write-to-string ,where-value) "'"))
+ (null
+ " IS NULL")
+ (t
+ (format nil ,(if like "'%~A%'" "='~A'") ,where-value)))))
+ (if ,%filter (concatenate 'string
+ ,(if %%where " AND " " WHERE ")
+ ,%filter) "")
+ (if ,srl (concatenate 'string ,%%lrl (write-to-string ,srl)) "")
+ ,@(when %%order (list %%order))
+ (cond
+ ((and ,%single ,%limit)
+ (error "Can't set single (~S) and limit (~S)" ,%single ,%limit))
+ (,%single
+ " LIMIT 1")
+ (,%limit
+ (format nil " LIMIT ~D" ,%limit))
+ (t
+ ""))))))
+
+(defun query-string-eval (table fields srl where-name where-value
+ &key (lrl "KCUILRL") single distinct order like limit filter)
+ (when single (setq limit 1))