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))))