CommonlispでCRUDして見た。

いつかlispを使える職場に行くことを夢見て
common lidpでCRUDを実装して見ました。

(ql:quickload :cl-dbi)
#| mysql
CREATE DATABASE test;
USE test;
CREATE TABLE tests(id int(5), name varchar(20), created timestamp);
INSERT INTO tests(id, name, created) VALUES(0, "aaa", "2017-07-31 00:00:00");
INSERT INTO tests(id, name, created) VALUES(1, "bbb", "2017-07-31 00:00:00");
INSERT INTO tests(id, name, created) VALUES(2, "ccc", "2017-07-31 00:00:00");
|#

(defvar *db*
    (dbi:connect :mysql
                 :database-name "test"
                 :username "root"
                 :password ""))

(defun reader ()
  (let* ((query (dbi:prepare *db* "SELECT * FROM tests"))
         (result (dbi:execute query)))
    (loop for row = (dbi:fetch result) ;変数を更新しながらloop
          while row ;条件が真の間だけloop
          do
          (print row)
          (print (getf row :|id|))
          (print (getf row :|name|))
          (print (getf row :|created|))
          (terpri))))

(defun updater ()
  (let* ((query (dbi:prepare *db* "UPDATE tests SET name = ? WHERE id = ?"))
         (result (dbi:execute query "eee" 1)))))

(defun deleter ()
  (let* ((query (dbi:prepare *db* "DELETE FROM tests WHERE id = ?"))
         (result (dbi:execute query 0)))))

(defun creater ()
  (let* ((query (dbi:prepare *db* "INSERT INTO tests(id, name, created) VALUES(?, ?, ?)"))
         (result (dbi:execute query 4 "fff" "2017-07-31 00:00:00")))))


(defun main ()
  (print "before read ----------------------------")
  (reader)
  (print "create")
  (creater)
  (print "update")
  (updater)
  (print "delete")
  (deleter)
  (print "after read -----------------------------")
  (reader))

(main)

Common Lisp用のデータベースライブラリ「CL-DBI」を作りました - 八発白中
参考にしました。ありがとうございます。