postgresql-and-lisp.lisp

(in-package :postmodern)

(defvar *db-parameters* '("my_database" "lisp_user" "your-secret" "localhost" :POOLED-P T)
  "Information about the connection of database.")

(defparameter *my-fruit* nil
  "This var will has an object of fruits.")

;;; Define the macro to connect to the postgresql server
(defmacro with-database (&body query)
  "This macro creates the connection with specified database in *db-parameter* and execute the query."
  `(postmodern:with-connection *db-parameters* ,@query))

;;; define a simple table in other words define a table called fruits
(defclass fruits ()
  ((fruit-id :accessor fruit-id :col-type serial :initarg :fruit-id)
   (name :accessor name :col-type string :initarg :name :initform ""))
  (:documentation "DAO class example")
  (:metaclass postmodern:dao-class)
  (:table-name fruits)(:keys fruit-id))


(deftable fruits (!dao-def))
;;create the table in postgresql server
(with-database (create-table 'fruits))

;;; Database access objects (CRUD)

;;; Crate a record
(with-database
  (insert-dao (make-instance 'fruits :name "apple")))

;; Read a record
(with-database
  (get-dao 'fruits 1))

;; Read the information and set it in *my-fruit* var.
(setf *my-fruit* (with-database
		   (get-dao 'fruits 1)))

;;; define a method in order to display the information
(defmethod get-fruit-information ((obj fruits))
  (format t "id= ~a ~%name= ~a~%" (fruit-id obj) (name obj)))

;;; Update information
(defmethod update-my-fruit ((obj fruits) new-name)
  ;; set the new value
  (setf (name obj) new-name)
  ;; finally update the record
  (with-database
    (update-dao obj)))

;;; Delete information
(defmethod delete-my-fruit ((obj fruits))
  (with-database
    ;; Delete the object
    (delete-dao obj)))