parse SQL and generate prepared sql and parameter names

parse.lisp

(defun parameterized-sql-parse (sql)
  (let* ((params nil)
         (sql (with-output-to-string (s)
                (loop with colon = NIL
                      with quoted = NIL
                      with prev = #\Space
                      with param = '()
                      for c across sql
                      if (and (not colon) (not quoted))
                        if (char= c #\:)
                            do (setf colon T)
                        else
                          do (write-char c s)
                          and when (char= c #\')
                                do (setf quoted T)
                              end
                        end
                      else
                        when colon
                          if (find c "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ_0123456789")
                            do (push c param)
                          else
                            do (setf colon nil)
                            and if (char= prev #\:)
                                  do (write-char prev s) (write-char c s)
                                     (setf colon nil)
                                else
                                  do (write-char #\? s) (write-char c s)
                                     (push (intern (format NIL "~:@(~{~A~}~)" (nreverse param)) "KEYWORD") params)
                                     (setf param nil)
                                end
                          end
                        end
                        and when quoted
                              do (write-char c s)
                              and if (char= c #\')
                                    if (char= prev #\')
                                      do (write-char #\' s)
                                    else
                                      do (setf quoted nil)
                                    end
                                  end
                        end
                      end
                      do (setf prev c)
                      finally (when colon
                                    (write-char #\? s)
                                     (push (intern (format NIL "~:@(~{~A~}~)" (nreverse param)) "KEYWORD") params))))))
    `(:sql ,sql :keys ,(nreverse params))))


;;
CL-USER> (parameterized-sql-parse "select * from tbl where id = :id and param = :param")
(:SQL "select * from tbl where id = ? and param = ?" :KEYS (:ID :PARAM))

;;
CL-USER> (parameterized-sql-parse "update tbl set param = ':param' where id = :id and param != ':param'")
(:SQL "update tbl set param = ':param' where id = ? and param != ':param'" :KEYS (:ID))

;;
CL-USER> (parameterized-sql-parse
          "select
             m_user.username,
             m_team.team_name,
             m_group.group_name
           from
             m_user,
             m_team,
             m_group
           where
             m_group.group_id = m_team.team_id
           and
             m_team.team_id = m_user.team_id
           and
             m_uesr.uesr_id = :userid
           and
             m_user.valid_start_date <= :start_date
           and
             m_user.valid_end_date > :end_date
           and
             m_team.valid_start_date <= :start_date
           and
             m_team.valid_end_date > :end_date
           and
             m_group.valid_start_date <= :start_date
           and
             m_group.valid_end_date > :end_date")
             
(:SQL "select
             m_user.username,
             m_team.team_name,
             m_group.group_name
           from
             m_user,
             m_team,
             m_group
           where
             m_group.group_id = m_team.team_id
           and
             m_team.team_id = m_user.team_id
           and
             m_uesr.uesr_id = ?
           and
             m_user.valid_start_date <= ?
           and
             m_user.valid_end_date > ?
           and
             m_team.valid_start_date <= ?
           and
             m_team.valid_end_date > ?
           and
             m_group.valid_start_date <= ?
           and
             m_group.valid_end_date > ?"
 :KEYS
 (:USERID :START_DATE :END_DATE :START_DATE :END_DATE :START_DATE :END_DATE))