Postgresql and Lisp (DAO)

http://www.huuii.com/people/juan/3868

Postgresql and Lisp

There is a project called postmodern.

Postmodern is a Common Lisp library for interacting with PostgreSQL databases.

see for more information: http://marijnhaverbeke.nl/postmodern/#quickstart

First I am going to create the user and database into postgresql:

create user foo with password 'foobar';

create database bar owner foo ;

Now we need to load the library by quicklisp

(ql:quickload "postmodern")

To load "postmodern":

Load 1 ASDF system:

postmodern

; Loading "postmodern"

("postmodern")

After that, we are going to create a file with:

(in-package :postmodern)

;;; define the var with the values of the database
(defvar db-parameters '("bar" "foo" "foobar" "localhost" :POOLED-P T))

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

Create the table

;;; define a simple table

(defclass fruits ()

((id-fruit :accessor id-fruit :col-type serial :initarg :id-fruit)

(name :accessor name :col-type string :initarg :name :initform ""))

(:documentation "Dao class for a fruit record.")

(:metaclass postmodern:dao-class)

(:table-name fruits)(:keys id-fruit))

;;create the table in postgresql server

(with-database (create-table 'fruits))

Database access objects (CRUD)

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

;; select
(with-database
(get-dao 'fruits 1))

(with-database
(select-dao 'fruits (:= 'id-fruit 2)))

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

;;delete
(with-database
(delete-dao (make-instance 'fruits :id-fruit 1)))

;;update
(defun the-update (id new-name)
(let ((record nil))
;; first we neet the record
(setf record (with-database
(get-dao 'fruits id)))
;; the the new value
(setf (name record) new-name)
;; finally update the record
(with-database
(update-dao record))))