;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;;
;;; mysql.lisp - A lightweight MySQL inteface for CCL
;;;


(require 'rffi)

(ff-load "/usr/local/mysql/lib/mysql/libmysqlclient.dylib")

(defun mysql-connect (&key (host 0)           ; default to localhost
                           (user 0)           ; default to current user
                           (password 0)       ; default to no password
                           (db 0)             ; default to no initial database
                           (port 0)           ; use default TCP port
                           (socket 0)         ; path to socket for local connection
                           (client-flags 0))  ; See MySQL reference manual for details
  (let ( (conn (ff-call "mysql_init" 0)) )
    (if (%null-ptr-p conn)
      (error "MySQL initialization failed."))
    (ff-call "mysql_real_connect" conn host user password db port socket client-flags)
    (if (%null-ptr-p conn)
      (error "MySQL connection failed.  Did you set the password?"))
    conn))

(defff "mysql_query" (:ptr :cstr) :int)
(defff "mysql_error" (:ptr) :cstr)
(defff "mysql_use_result" (:ptr) :ptr)
(defff "mysql_info" (:ptr) :cstr)
(defff "mysql_num_fields" (:ptr) :int)
(defff "mysql_fetch_row" (:ptr) :ptr)
(defff "mysql_fetch_field" (:ptr) :ptr)
(defff "mysql_free_result" (:ptr) :void)
(defff "mysql_close" (:ptr) :void)
(defff "mysql_insert_id" (:ptr) :uint)

(defff "mysql_stmt_init" (:ptr) :ptr)
(defff "mysql_stmt_prepare" (:ptr :cstr :int) :int)
(defff "mysql_stmt_execute" (:ptr) :int)
(defff "mysql_stmt_bind_param" (:ptr :ptr) :int)
; Need to reverse-engineer the BIND data structure.  Fuck.


(defmacro vcollect (n form)
  (let ( (vvar (gensym "V"))
         (ivar (if (consp n) (first n) (gensym "I")))
         (n (if (consp n) (second n) n)) )
    `(let ( (,vvar (make-array (list ,n))) )
       (dotimes (,ivar ,n) (setf (aref ,vvar ,ivar) ,form))
       ,vvar)))

(defun mysql-query (db query &optional get-fields)
  (unless (zerop (mysql_query db query))
    (error (or (mysql_error db) "Unknown SQL error")))
  (let ( (result (mysql_use_result db)) )
    (if (%null-ptr-p result)
      (mysql_info db)
      (unwind-protect
        (let* ( (cols (mysql_num_fields result))
                (rows (loop for row = (mysql_fetch_row result)
                              until (%null-ptr-p row)
                              collect (vcollect (i cols)
                                        (safe-get-cstring (%get-ptr row (* i +bytes-per-int+)))))) )
          (when get-fields
            (push (vcollect cols
                    (safe-get-cstring (%get-ptr (mysql_fetch_field result) 0)))
                  rows))
          rows)
        (mysql_free_result result)))))


#|

; Example:

(setf *db* (mysql-connect :user "root"))
(defun q (s) (mysql-query *db* s t))

(q "show databases")
(q "create database mcl")
(q "use mcl")
(q "create table foo (x integer, y float, z datetime)")
(q "describe foo")
(q "insert into foo(x,y,z) values(1,2.3,now())")
(q "select * from foo")

|#