Add support for MYSQL 8 with sqlname indirection to prevent reserved word collisions

[umlisp.git] / sql-classes.lisp

diff --git a/sql-classes.lisp b/sql-classes.lisp
index d1e19f3fea74dd3a36293dab1793f3b24250fad4..2b468dc095080d61e621470005df128dbf1131b1 100644 (file)
--- a/sql-classes.lisp
+++ b/sql-classes.lisp
@@ -23,17 +23,31 @@
 (defun set-current-srl (srl)
   (setq *current-srl* srl))
 
+;; 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 " "") fields table))
+                           (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" where-name
+                    (format nil " WHERE ~:@(~A~)~A" (sqlname where-name)
                           (if like " like " ""))))
          (%filter (gensym "FILTER-"))
          (%single (gensym "SINGLE-"))
@@ -77,8 +91,8 @@
   (concatenate
    'string
    (format nil "SELECT ~A~{~:@(~A~)~^,~} FROM ~:@(~A~)"
-           (if distinct "DISTINCT " "") fields table)
-   (if where-name (format nil " WHERE ~:@(~A~)" where-name) "")
+           (if distinct "DISTINCT " "") (sqlname fields) table)
+   (if where-name (format nil " WHERE ~:@(~A~)" (sqlname where-name)) "")
    (if where-name
        (format nil
                (typecase where-value
@@ -1130,3 +1144,14 @@ is OBJNAME from TABLE where WHERE-NAME field = WHERE-VALUE with FIELDS"
 (defun find-ustats-srl (srl)
   (collect-umlisp-query (ustats (name count) nil srl srl :order (name asc))
                            (make-instance 'ustats :name name :hits (ensure-integer count))))
+
+(defun find-urank-sab (sab &key (srl *current-srl*))
+  (collect-umlisp-query (mrrank (rank sab suppress tty) nil sab sab)
+                        (make-instance 'urank :rank rank :sab sab :tty tty :suppress suppress)))
+
+(defun find-urank-all (&key (srl *current-srl*))
+  (if srl
+      (collect-umlisp-query (mrrank (rank sab suppress tty) nil ksrl srl)
+                            (make-instance 'urank :rank rank :sab sab :tty tty :suppress suppress))
+      (collect-umlisp-query (mrrank (rank sab suppress tty ksrl) nil nil nil)
+                            (make-instance 'urank :rank rank :sab sab :tty tty :suppress suppress))))