sql - 我如何传递这个论点？

Question

这是使用后现代。

这个有效:

(sql (:INSERT-ROWS-INTO 'SCRAPE :COLUMNS 'TERM 'NAME 'ADDRESS :VALUES
  '(("wine" "Bubba Wine & Spirits"
 "1234 N San Fake Rd,")
    ("wine" "Wine Shop"
     "123 Not Real Blvd,")
    ("wine" "Some Wine Group"
     "777 S Does Not Exist Ave,"))))

返回:

"INSERT INTO scrape (term, name, address) 
 VALUES (E'wine', E'Bubba Wine & Spirits', E'1234 N San Fake Rd,'), 
        (E'wine', E'Wine Shop', E'123 Not Real Blvd,'), 
        (E'wine', E'Some Wine Group', E'777 S Does Not Exist Ave,')"

这行不通:

(defvar *sql* '(:INSERT-ROWS-INTO 'SCRAPE :COLUMNS 'TERM 'NAME 'ADDRESS :VALUES
  '(("wine" "Bubba Wine & Spirits"
 "1234 N San Fake Rd,")
    ("wine" "Wine Shop"
     "123 Not Real Blvd,")
    ("wine" "Some Wine Group"
     "777 S Does Not Exist Ave,"))))

(sql *sql*)

这给了我:

Value (:INSERT-ROWS-INTO 'SCRAPE :COLUMNS 'TERM 'NAME 'ADDRESS
       :VALUES
       '(("wine" "Bubba Wine & Spirits"
          "1234 N San Fake Rd,")
         ("wine" "Wine Shop" "123 Not Real Blvd,")
         ("wine" "Some Wine Group" "777 S Does Not Exist Ave,"))) can not be converted to an SQL literal.
   [Condition of type SIMPLE-ERROR]

Restarts:
 0: [ABORT] Exit debugger, returning to top level.

Backtrace:
  0: ((SB-PCL::FAST-METHOD CL-POSTGRES:TO-SQL-STRING (T)) #<unavailable argument> #<unavailable argument> (:INSERT-ROWS-INTO 'SCRAPE :COLUMNS 'TERM 'NAME 'ADDRESS ...))
  1: ((SB-PCL::FAST-METHOD SQL-ESCAPE (T)) #<unavailable argument> #<unavailable argument> (:INSERT-ROWS-INTO 'SCRAPE :COLUMNS 'TERM 'NAME 'ADDRESS ...))
  2: (SB-INT:SIMPLE-EVAL-IN-LEXENV (SQL *SQL*) #<NULL-LEXENV>)
  3: (EVAL (SQL *SQL*))
  4: (SB-EXT:INTERACTIVE-EVAL (SQL *SQL*) :EVAL NIL)
  5: (SB-IMPL::REPL-FUN NIL)
  6: ((LAMBDA () :IN SB-IMPL::TOPLEVEL-REPL))
  7: (SB-IMPL::%WITH-REBOUND-IO-SYNTAX #<CLOSURE (LAMBDA # :IN SB-IMPL::TOPLEVEL-REPL) {BFC3BFD}>)
  8: (SB-IMPL::TOPLEVEL-REPL NIL)
  9: (SB-IMPL::TOPLEVEL-INIT)
 10: ((FLET #:WITHOUT-INTERRUPTS-BODY-223764 :IN SB-EXT:SAVE-LISP-AND-DIE))
 11: ((LABELS SB-IMPL::RESTART-LISP :IN SB-EXT:SAVE-LISP-AND-DIE))

所以我很明显搞砸了 (sql *sql*) 并且我一辈子都无法破译错误消息。

Answer 1

错误消息不是特别有用。 sql 是一个宏，不会计算它的参数，但会根据它未计算的参数做一些事情。如果它不是宏，(sql (:insert-rows-into ...)) 将是一个错误，因为要评估它需要评估 (:insert-rows- into ...) 首先，:insert-rows-into 不是定义的函数。

实现这些类型的宏的一种常见方法是使用类似(defun expand-sql-code (code) ...) 的函数实现代码转换，然后提供一个宏版本(defmacro sql (code) (expand-sql-code ',code))。

这是后现代的 sql 宏的定义:

(defmacro sql (form)
  "Compile form to an sql expression as far as possible."
  (let ((list (reduce-strings (sql-expand form))))
    (if (= 1 (length list))
        (car list)
        `(strcat (list ,@list)))))

看起来 sql-expand 正在完成大部分繁重的工作，因此您可能有兴趣调用它。例如:

(s-sql::sql-expand '(:INSERT-ROWS-INTO 'SCRAPE :COLUMNS 'TERM 'NAME 'ADDRESS :VALUES
                     '(("wine" "Bubba Wine & Spirits"
                        "1234 N San Fake Rd,")
                       ("wine" "Wine Shop"
                        "123 Not Real Blvd,")
                       ("wine" "Some Wine Group"
                        "777 S Does Not Exist Ave,"))))

("INSERT INTO " "scrape" " " "(" "term" ", " "name" ", " "address" ") "
 "VALUES "
 (S-SQL::EXPAND-ROWS
  '(("wine" "Bubba Wine & Spirits" "1234 N San Fake Rd,")
    ("wine" "Wine Shop" "123 Not Real Blvd,")
    ("wine" "Some Wine Group" "777 S Does Not Exist Ave,"))
  3))

不过，您仍然需要对结果应用字符串连接。原始表单的宏扩展很有启发性:

(macroexpand-1 '(postmodern:sql (:INSERT-ROWS-INTO 'SCRAPE :COLUMNS 'TERM 'NAME 'ADDRESS :VALUES
                                 '(("wine" "Bubba Wine & Spirits"
                                    "1234 N San Fake Rd,")
                                   ("wine" "Wine Shop"
                                    "123 Not Real Blvd,")
                                   ("wine" "Some Wine Group"
                                    "777 S Does Not Exist Ave,")))))
(S-SQL::STRCAT
 (LIST "INSERT INTO scrape (term, name, address) VALUES "
       (S-SQL::EXPAND-ROWS
        '(("wine" "Bubba Wine & Spirits" "1234 N San Fake Rd,")
          ("wine" "Wine Shop" "123 Not Real Blvd,")
          ("wine" "Some Wine Group" "777 S Does Not Exist Ave,"))
        3)))
T

总而言之，由于没有一个唯一的函数可以实现 sql 转换，这可能是最简单的选择可能就是使用 eval:

(eval `(postmodern:sql ,*sql*)) ; or (eval (cons 'postmodern:sql *sql*))
;=> "INSERT INTO scrape (term, name, address) VALUES (E'wine', E'Bubba Wine & Spirits', E'1234 N San Fake Rd,'), (E'wine', E'Wine Shop', E'123 Not Real Blvd,'), (E'wine', E'Some Wine Group', E'777 S Does Not Exist Ave,')"