lispEn 2022-08-02 13:07:01
212

Postmodern DAO example

In this post we are going to view a DAO example using Postmodern which is a Common Lisp library for interacting with PostgreSQL databases.

https://marijnhaverbeke.nl/postmodern/


First, we are going to create the user and  database into postgresql:

postgres=# create user lisp_user with password 'your-secret';

postgres=# create database my_database owner lisp_user ;


Second, we need to load the postmonder by quicklisp:

(ql:quickload "postmodern")
To load "postmodern":
  Load 1 ASDF system:
    postmodern
; Loading "postmodern"
("postmodern")


Finally, we need to create a file called postgresql-and-lisp.lisp with the following content:


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


You can get the file:

https://gist.github.com/juan-reynoso/f9595fac1d2693b684920c58be5c862e

#postgresql #DAO

También te podría interesar
lispEn 2022-08-02 12:17:39
How to run PostgreSQL queries with Common Lisp.