Part Two: Introduction to web development with Lisp

In this part of the book I’ll introduce basic web development tools for Lisp. These include the Caveman2 framework and it’s assosiated libraries. We’ll cover creating a caveman project and we’ll walk trough it’s basic functionality.

Chapter 4: Caveman2

The web stack

Caveman2 is a rather simple but feature complete framework for web development. It includes most of what you’d expect such a tool to have, such as a template language, database access, configuration, url routing, ect.

Caveman is based on ningle, a micro-framework which handles only the most basic functionality, such as url routing. Ningle itself is based on the Clack HTTP library and server interface. Because common lisp apps can support many different web servers such as hunchentoot and woo, you need a common interface between them, clack fills that role. Other than abstracting the server backend it also provides request and response objects and handler middleware. We’ll take a deeper look into these features in due time. For now let’s take a closer look at caveman itself.

In this chapter we’ll take a look at what caveman provides for us and we’ll setup a simple project. Here’s what we’ve got:

• Ningle based url routing
• Database access using the datafly and sxql libraries
• The Djula templating library, a port of the Django template language.
• A configuration system called ENVY that allows us to configure our app using environment variables.

Setting up a simple project

First let’s install caveman with quicklisp

1  * (ql:quickload "caveman2") 
2  => ("caveman2")

Caveman comes with a built in project generator. Let’s say we want to write our own wiki, here is how we can generate a project skeleton for it:

 1  * (caveman2:make-project #P"~/.roswell/local-projects/fullstackwiki"
 2                           :author "Pavel" 
 3                           :license "MIT")
 4 
 5 writing ~/.roswell/local-projects/fullstackwiki/fullstackwiki.asd
 6 writing ~/.roswell/local-projects/fullstackwiki/fullstackwiki-test.asd
 7 writing ~/.roswell/local-projects/fullstackwiki/app.lisp
 8 writing ~/.roswell/local-projects/fullstackwiki/README.markdown
 9 writing ~/.roswell/local-projects/fullstackwiki/.gitignore
10 writing ~/.roswell/local-projects/fullstackwiki/db/schema.sql
11 writing ~/.roswell/local-projects/fullstackwiki/src/config.lisp
12 writing ~/.roswell/local-projects/fullstackwiki/src/db.lisp
13 writing ~/.roswell/local-projects/fullstackwiki/src/main.lisp
14 writing ~/.roswell/local-projects/fullstackwiki/src/view.lisp
15 writing ~/.roswell/local-projects/fullstackwiki/src/web.lisp
16 writing ~/.roswell/local-projects/fullstackwiki/static/css/main.css
17 writing ~/.roswell/local-projects/fullstackwiki/t/fullstackwiki.lisp
18 writing ~/.roswell/local-projects/fullstackwiki/templates/index.html
19 writing ~/.roswell/local-projects/fullstackwiki/templates/_errors/404.html
20 writing ~/.roswell/local-projects/fullstackwiki/templates/layouts/default.html
21 
22  => T

What we did was we called the function caveman2:make-project with the path to our project as an argument. We put our project in local-projects, so ASDF and quicklisp can find it. We called the project “fullstackwiki” and we gave the author name and license as keyword arguments. Note that we must pass it a pathname object rather than a string.

As you can see from the printed output of make-project it generated quite a bit of files. Let’s take closer look.

The root directory of our project contains asd files for our app and an auto generated test system, a README file and a lisp specific .gitignore file.

Let’s take a closer look at fullstackwiki.asd

 1 (in-package :cl-user)
 2 (defpackage fullstackwiki-asd
 3   (:use :cl :asdf))
 4 (in-package :fullstackwiki-asd)
 5 
 6 (defsystem fullstackwiki
 7   :version "0.1"
 8   :author "Pavel"
 9   :license "MIT"
10   :depends-on (:clack
11                :lack
12                :caveman2
13                :envy
14                :cl-ppcre
15                :uiop
16 
17                ;; for @route annotation
18                :cl-syntax-annot
19 
20                ;; HTML Template
21                :djula
22 
23                ;; for DB
24                :datafly
25                :sxql)
26   :components ((:module "src"
27                 :components
28                 ((:file "main" :depends-on ("config" "view" "db"))
29                  (:file "web" :depends-on ("view"))
30                  (:file "view" :depends-on ("config"))
31                  (:file "db" :depends-on ("config"))
32                  (:file "config"))))
33   :description ""
34   :in-order-to ((test-op (load-op fullstackwiki-test))))

Here we see that the code to our app is in the “src” directory(called a module by ASDF), and it has the following componets:

• config.lisp is the main configuration, it uses ENVY.
• db.lisp defines our database layer
• view.lisp handles html templates
• web.lisp defines our routes
• main.lisp handles how our app is started and stopped

We’ll take a look at all of these files in the next sections.

Configuration

Envy is a configuration utility which allows you to define several different configurations and then switch between them based on the value of an environment variable. For example you can have a separate configuration on a development machine and one for production deployment. It also allows you to define a common configuration and have the other ones just define the differences, rather than duplicate code. Let’s take a closer look at src/config.lisp:

 1 (in-package :cl-user)
 2 (defpackage fullstackwiki.config
 3   (:use :cl)
 4   (:import-from :envy
 5                 :config-env-var
 6                 :defconfig)
 7   (:export :config
 8            :*application-root*
 9            :*static-directory*
10            :*template-directory*
11            :appenv
12            :developmentp
13            :productionp))
14 (in-package :fullstackwiki.config)
15 
16 (setf (config-env-var) "APP_ENV")
17 
18 (defparameter *application-root*   (asdf:system-source-directory :fullstackwiki))
19 (defparameter *static-directory*   (merge-pathnames #P"static/" *application-roo\
20 t*))
21 (defparameter *template-directory* (merge-pathnames #P"templates/" *application-\
22 root*))
23 
24 (defconfig :common
25   `(:databases ((:maindb :sqlite3 :database-name ":memory:"))))
26 
27 (defconfig |development|
28   '())
29 
30 (defconfig |production|
31   '())
32 
33 (defconfig |test|
34   '())
35 
36 (defun config (&optional key)
37   (envy:config #.(package-name *package*) key))
38 
39 (defun appenv ()
40   (uiop:getenv (config-env-var #.(package-name *package*))))
41 
42 (defun developmentp ()
43   (string= (appenv) "development"))
44 
45 (defun productionp ()
46   (string= (appenv) "production"))

First the line (setf (config-env-var) "APP_ENV") tells ENVY to switch configuration based on the APP_ENV environment variable.

Next the *application-root*, *static-directory* and *temlate-directory* variables are defined which contain our app, our static resources and our html templates respectively.

The macro defconfig is used to define configurations as the name suggests. A config has a name and a body. As you can see above the body itself is a plist of values. The :common name is special, because it is included in every other configuration. In this case it only defines that the database used by default is sqlite3 and it’s to be stored in memory, rather than create a file on disk.

The other defined configurations are |development|, |production| and |test|, which are all empty. The reason they are named with || is because that’s how common lisp symbols are written in lower case. Remember that a configuration is chosen based on the value of an environment variable. You could name your config production, but since lisp symbols are upcased by default, you’ll have to set your environment variable in upcase: export APP_ENV=PRODUCTION.

Finally the file defines a few utility functions: * config gives returns back the configuration, or if an optional key argument is given, it looks it up and returns it’s value. * appenv returns the value of the configured environment variable * developmentp and productionp simply tell you if you’re running in dev or prod respectively.

That’s it. We’ll take a closer look again at configuration when we set up a database for our project other than sqlite3, for now you get the idea.

Views

By default caveman apps use Djula for html templating. Djula is an implementation of the Django template engine. If you haven’t used a template language before it’s basically an ordinary text file with special tags in between which insert or transform data. For example this is a very simple Djula template:

1 {% if username %}
2 Hello {{username}}!
3 {% else %}
4 Please login!
5 {% endif %}

This template defines a variable called username, when we render our template we’ll pass it an argument with the value of username and it will use it to transform the text. For example it’s value is Pavel, well get the string “Hello Pavel”, but if it’s nil we’ll get the string “Please login!”. Although template languages aren’t specific to HTML, they are extremely useful for just that purpose and that’s mostly what we’ll be using them for in this book. We’ll go through the template language itself in a separate chapter later.

In a caveman app, the Djula templates are kept in the directory designated by *template-directory*, which in our case is fullstackwiki/templates/. A fresh project will have a few such templates already defined in that directory. Like the Django engine, Djula supports template inheritance, which means that means we can put common code in separate files and reuse it in other templates. Our project already has such a shared template defined in the directory fullstackwiki/templates/layout/. Let’s take a look at the file named default.html in that directory:

 1 <!DOCTYPE html>
 2 <html>
 3 <head>
 4   <meta charset="utf-8">
 5   <title>{% block title %}{% endblock %}</title>
 6   <link rel="stylesheet" type="text/css" media="screen" href="/css/main.css">
 7 </head>
 8 <body>
 9   {% block content %}{% endblock %}
10 </body>
11 </html>

As you can see, this is just an ordinary HTML document, but with Djula tags added. In this case the tags are two pairs of {% block $}/{% endblock %}. What this tag does is it allows other templates to insert data in these blocks when they extend the default.html file. In our case we’ve defined two named blocks: title and content. Let’s take a look at index.html, which extends base.html:

1 {% extends "layouts/default.html" %}
2 {% block title %}Welcome to Caveman2{% endblock %}
3 {% block content %}
4 <div id="main">
5   Welcome to <a href="http://8arrow.org/caveman/">Caveman2</a>!
6 </div>
7 {% endblock %}

The first line introduces the extends tag, we give it the base template as an argument. Now any time we use the block tag with the name of a block, anything in it will be inserted into the base template.

Other than that caveman also defines a errors/404.html page for us.

That’s it for now. We’ll take a look at more tags as we need them.

Routing

The main purpose of a web framework is to match urls to code that returns a response to the client. Let’s see how caveman does that. The important code is in fullstackwiki/src/web.lisp. Let’s look at some of the code in that file.

First up, caveman2 stores url routing rules in an object called an app, which is an instance of the <app> class. Here we create a subclass called <web> and instantiate it, store it in a variable called fullstackwiki.web:*web* and clear it’s routing rules:

19 (defclass <web> (<app>) ())
20 (defvar *web* (make-instance '<web>))
21 (clear-routing-rules *web*)

Now we can begin defining routes. A route is a mapping of url template and additional conditions to Common Lisp functions. Caveman2 gives us two ways to define them. The first is annotation syntax, and the other is the defroute macro. Notice on line 14 of the file we have the following code: (syntax:use-syntax :annot). This allows us to annotate functions using the @annotation syntax, this is how it might look like:

1 @route GET "/"
2 (defun index ()
3   (render #P"index.html"))

The first line is an annotation, followed by a function. This isn’t standard Common Lisp, but a reader syntax extension. Annotations are essentially functions that take a form and transform it in some way, in this case it creates a routing rule for the url template "/" and associates the function index as it’s handler. We won’t be using annotation syntax in this book, since it is equivalent to the defroute macro in functionality and I have a strong aesthetic preference, at least for my projects, you are free to choose annotations. Now let’s look at the equivalent code as a defroute as it was defined in fullstackwiki/src/web.lisp:

26 (defroute "/" ()
27   (render #P"index.html"))

This is essentially equivalent to the annotation code. It defines a handler for the "/" url. The body of the route simply renders the index.html template and returns it to the client. We’ll take a look at much more complicated examples of routes later in the book.

Finally at the bottom of the file is this interesting method:

32 (defmethod on-exception ((app <web>) (code (eql 404)))
33   (declare (ignore app))
34   (merge-pathnames #P"_errors/404.html"
35                    *template-directory*))

In case no route matches the url of the request, this method will handle it by sending back a 404 Not Found response.

A few words about Clack/Lack

TBW

Running the app

The way clack based apps are run is with the clack:clackup function. Other than a function or a component it can also take an app file that configures our application as an argument:

1 (clack:clackup #P"/path/to/app.lisp" args)

This is useful since we might want to add middleware and other options to our app and we need a place where we can do that. Also the file can be used to start our application from the command line. We’ll see how that might be done in later chapters. In our case, caveman defined one such file in the root of our project called app.lisp, let’s take a look:

 1 (ql:quickload :fullstackwiki)
 2 
 3 (defpackage fullstackwiki.app
 4   (:use :cl)
 5   (:import-from :lack.builder
 6                 :builder)
 7   (:import-from :ppcre
 8                 :scan
 9                 :regex-replace)
10   (:import-from :fullstackwiki.web
11                 :*web*)
12   (:import-from :fullstackwiki.config
13                 :config
14                 :productionp
15                 :*static-directory*))
16 (in-package :fullstackwiki.app)
17 
18 (builder
19  (:static
20   :path (lambda (path)
21           (if (ppcre:scan "^(?:/images/|/css/|/js/|/robot\\.txt$|/favicon\\.ico$\
22 )" path)
23               path
24               nil))
25   :root *static-directory*)
26  (if (productionp)
27      nil
28      :accesslog)
29  (if (getf (config) :error-log)
30      `(:backtrace
31        :output ,(getf (config) :error-log))
32      nil)
33  :session
34  (if (productionp)
35      nil
36      (lambda (app)
37        (lambda (env)
38          (let ((datafly:*trace-sql* t))
39            (funcall app env)))))
40  *web*)

First it uses quicklisp to load our project, since the file itself isn’t part of it. It then imports our app and its configuration and uses lack:builder to build a lack component. It’s setting up static file handling, session and other middleware based on our configuration and applying them to our app. When you call clack:clackup with that file as an argument, it gets evaluated and the result of the lack:builder form is run as our app.

For actually running the app, caveman generated a few convenience functions in fullstackwiki/src/main.lisp let’s take a look:

 1 (in-package :cl-user)
 2 (defpackage fullstackwiki
 3   (:use :cl)
 4   (:import-from :fullstackwiki.config
 5                 :config)
 6   (:import-from :clack
 7                 :clackup)
 8   (:export :start
 9            :stop))
10 (in-package :fullstackwiki)
11 
12 (defvar *appfile-path*
13   (asdf:system-relative-pathname :fullstackwiki #P"app.lisp"))
14 
15 (defvar *handler* nil)
16 
17 (defun start (&rest args &key server port debug &allow-other-keys)
18   (declare (ignore server port debug))
19   (when *handler*
20     (restart-case (error "Server is already running.")
21       (restart-server ()
22         :report "Restart the server"
23         (stop))))
24   (setf *handler*
25         (apply #'clackup *appfile-path* args)))
26 
27 (defun stop ()
28   (prog1
29       (clack:stop *handler*)
30     (setf *handler* nil)))

This file defines our main package fullstackwiki. It also defines our app file and a pair of start/stop functions. As you can see, start checks if the app is running, and if it is, it offers to restart it, otherwise it calls clack:clackup with the file and sets the result to the *handler* variable. Stop is pretty self explanatory.

Conclusion

TBW

Chapter 5: Datafly and SXQL

In this chapter we’ll take a look at Caveman2’s database access capability and define a simple model for our wiki app.

Database access

The default Caveman2 data layer uses a library called CL-DBI, which provides drivers for most popular RDBMSs out there, including PostgreSQL, SQLite and MySQL. Instead of using it directly though, most of the time we’ll be using another library called datafly to access our data. This library provides a thin wrapper over CL-DBI and gives us 3 main functions: EXECUTE executes an SQL statement, RETRIEVE-ONE and RETRIEVE-ALL are used to fetch data, again with an SQL statement. Instead of writing SQL by hand, we’ll be using a lispy SQL dsl called SXQL, which allows us to write queries in a lispy syntax. You can use pretty much any database libraries you wish with Caveman2, and later on we’ll take a look at some alternatives, but for now the default choices will work just fine.

Before we look at some examples, let’s first set up a database. This book will use PostgreSQL, so let’s install it, on debian-like Linux distros this will be

1  $ sudo apt-get install postgresql

I also advise you to install the excellent pgadmin3 package, which provides a nice GUI for database management:

1  $ sudo apt-get install pgadmin3

Next, the first thing we must do us set up a postgreSQL database and a user. We must do this with the postgres unix account, type the following to log in as the postgres user

1  $ sudo su - postgres

Now start the PostgreSQL CLI client psql:

1  $ psql

You will notice the new prompt:

1 postgres=# 

Let’s create a new user, type the following:

1 CREATE USER fswiki WITH PASSWORD 'fswikipass';

Now we must create a new database:

1 CREATE DATABASE fswikidb;

And finally, we must grant our user all the privileges to use the new database:

1 GRANT ALL PRIVILEGES ON DATABASE fswikidb TO fswiki;

That’s it, type \q to exist psql, and type exit to logout of the postgres account.

Now, let’s try it out. Open the src/config.lisp, and edit the (defconfig :common ...) section:

22 (defconfig :common
23     `(:databases ((:maindb :postgres
24                            :database-name "fswikidb"
25                            :username "fswiki"
26                            :password "fswikipass"))))

Now let’s take a look at the src/db.lisp file:

 1 (in-package :cl-user)
 2 (defpackage fullstackwiki.db
 3   (:use :cl)
 4   (:import-from :fullstackwiki.config
 5                 :config)
 6   (:import-from :datafly
 7                 :*connection*
 8                 :connect-cached)
 9   (:export :connection-settings
10            :db
11            :with-connection
12            :with-transaction))
13 (in-package :fullstackwiki.db)
14 
15 (defun connection-settings (&optional (db :maindb))
16   (cdr (assoc db (config :databases))))
17 
18 (defun db (&optional (db :maindb))
19   (apply #'connect-cached (connection-settings db)))
20 
21 (defmacro with-connection (conn &body body)
22   `(let ((*connection* ,conn))
23      ,@body))

What we see here is a few utility functions defined for us. connection-settings pulls the current db configuration, db establishes a new connection, or pulles a an already existing one from a chache, and the macro with-connection simply binds it to the *connection* variable so that datafly functions know which connection to use when querying the database, and enter into this package

Now let’s start a fresh repl and load our project to look at some examples:

1  * (ql:quickload "fullstackwiki")
2  * (in-package :fullstackwiki.db)

For all our code we should use with-connection, but it’s a bit cumbersome to write it all the time in the repl, so let’s set a top-level connection:

1  * (apply #'datafly:connect-toplevel (connection-settings :maindb))

Now we can call datafly functions without wrapping them in calls to with-connection. Let’s create a simple table:

1  * (datafly:execute
2      (sxql:create-table (:test-table if-not-exists t)
3          ((id :type 'serial :primary-key t)
4           (name :type 'text))))

This creates a new table called test-table if it doesn’t already exist. Calling this code again will not do anything, since it already exists. The table has two fields, id, which is of type serial, a postgreSQL specific way to get auto-incremented integers, this field is our primary key, and name, which is a text field. The syntax for sxql:create-table is in general fairly simple:

1 (sxql:create-table <table-name> (<fields>*) <options>*)

Fairly similar to standard SQL, but in lisp syntax. The field definitions have various options, we’ve seen type, and primary-key, but others like not-null and unique are also supported.

Now let’s insert a row into the table:

1  * (datafly:execute
2      (sxql:insert-into :test-table
3        (sxql:set= :name "Hello World")))

Similar to standard SQL insert into statement, sxql:insert-into takes a table same and a list of clauses, in this case only one sxql:set= clause. We set the name to “Hello World”, the id is auto-incremented, so we don’t have to specify it.

Retrieving data works in a similar way:

1  * (datafly:retrieve-all (sxql:select :* (sxql:from :test-table)))
2 => ((:ID 1 :NAME "Hello World"))

Again, the statement sxql:select works like in regular SQL, It expects a list of fielsds and a list of clauses, in this case we have a single field :* equivalent to the SQL * operator, and a single from clause specifying the table. Instead of datafly:execute we use datafly:retrieve-all since we expect to get back results instead of just executing a statement on the server. The result is a list of plists, in this case just one, since we only have one record in the table. The function datafly:retrieve-one works in a similar way, but returns only a single result:

1  * (datafly:retrieve-one (sxql:select :* (sxql:from :test-table)))
2 => (:ID 1 :NAME "Hello World")

We’ll see more examples and I’ll go into more depth in a moment, this is just to get your feet wet. Now that we’ve seen the basics, let’s drop this table and disconnect, so we can get to some real work in the next section:

1  * (datafly:execute (sxql:drop-table :test-table))
2 
3  * (datafly:disconnect-toplevel)

The Wiki Model

Our wiki app has some pretty basic requirements. First we need to be able to create users and log them in. Next users have to be able to create and edit wiki pages. We also want pages to have different versions, so they have a history we can go back to and see what was edited. In this chapter we’ll see a very simple model that can do what we want. The model only has two tables, user and page, and a few functions to create and query the data. The first thing we’ll need to do though is update our fullstackwiki.asd file. We’ll need to add two lines, the first is adding a dependency to cl-pass a library for hashing passwords, since we don’t want to store them in plain text. The other one is to add a src/model.lisp file where our code will be. Here is how the updated asd file looks like. The lines we’ve changed are number 27 and number 34. Notice that model depends on db:

 1 (in-package :cl-user)
 2 (defpackage fullstackwiki-asd
 3   (:use :cl :asdf))
 4 (in-package :fullstackwiki-asd)
 5 
 6 (defsystem fullstackwiki
 7   :version "0.1"
 8   :author "Pavel"
 9   :license "MIT"
10   :depends-on (:clack
11                :lack
12                :caveman2
13                :envy
14                :cl-ppcre
15                :uiop
16 
17                ;; for @route annotation
18                :cl-syntax-annot
19 
20                ;; HTML Template
21                :djula
22 
23                ;; for DB
24                :datafly
25                :sxql
26                ;; Password hashing
27                :cl-pass)
28   :components ((:module "src"
29                 :components
30                 ((:file "main" :depends-on ("config" "view" "db"))
31                  (:file "web" :depends-on ("view"))
32                  (:file "view" :depends-on ("config"))
33                  (:file "db" :depends-on ("config"))
34                  (:file "model" :depends-on ("db")) ;; model
35                  (:file "config"))))
36   :description ""
37   :in-order-to ((test-op (load-op fullstackwiki-test))))

Another bit of houskeeping we must do is extend src/db.lisp with an additional macro. Our model will need to be able to execute SQL statements inside a database transaction, for this we’ll need to implement a with-transaction macro, add this to the bottom of the file:

19 (defmacro with-transaction (conn &body body)
20   `(let ((*connection* ,conn))
21      (cl-dbi:with-transaction *connection*
22        ,@body)))

This macro is similar to with-connection, but we additionally make a call to the low-level CL-DBI:with-transaction macro to make sure everything in the body is executed in the proper order and we don’t break our database. We also have to export it from the defpackage form at top of the file:

 1 (in-package :cl-user)
 2 (defpackage fullstackwiki.db
 3   (:use :cl)
 4   (:import-from :fullstackwiki.config
 5                 :config)
 6   (:import-from :datafly
 7                 :*connection*
 8                 :connect-cached)
 9   (:export :connection-settings
10            :db
11            :with-connection
12            :with-transaction)) ;; new macro

Now we’re almost all set. Let’s create a new file called src/model.lisp and create a package for it:

 1 ;;;; model.lisp
 2 
 3 (in-package :cl-user)
 4 
 5 (defpackage fullstackwiki.model
 6   (:use :cl :sxql)
 7   (:import-from :fullstackwiki.db
 8                 :db
 9                 :with-connection
10                 :with-transaction) 
11   (:import-from :datafly
12                 :execute
13                 :retrieve-all
14                 :retrieve-one)
15   (:export :create-tables
16            :find-user
17            :add-user
18            :authenticate-user
19            :add-page
20            :get-latest-page
21            :get-latest-pages-by-user
22            :get-sorted-pages
23            :count-pages
24            :nth-page-revision))
25 
26 (in-package :fullstackwiki.model)

This sets up a new package called fullstackwiki.model for us, and it imports all the symbols we’ll need from sxql, fullstackwiki.db and datafly. It also exports the package’s public API. I’ll explain what all these functions do in the next sections.

User model

Our user model is very simple, It consists of a user table, let’s write a function that creates it for us:

30 (defun create-user-table ()
31   "Create user table if it doesn't exist yet."
32   (with-connection (db)
33     (execute
34      (create-table (:user :if-not-exists t)
35          ((id :type 'serial :primary-key t)
36           (username :type 'text :not-null t :unique t)
37           (email :type 'text :not-null t :unique t)
38           (password :type 'text :not-null t))))))

The user table has four fields, an id, username, email and password. This is pretty self-explanatory. We make all fields :not-null and the email and username fields have to be :unique. Now a function to register a new user:

40 (defun add-user (username email password)
41   "add user record to database."
42   (with-connection (db)
43     (execute
44      (insert-into :user
45        (set= :username username
46              :email email
47              :password (cl-pass:hash password))))))

Again, nothing we haven’t seen before, we insert a new user row, but the password is hashed with the cl-pass:hash function. This is how it might look like in the repl:

1 * (cl-pass:hash "hello")
2 "PBKDF2$SHA256:20000$307629b11dd0f4df0c55c2b78f2f97a8$5213c150869544fc0a96e95d10\
3 d4a3157cd398ddeb4b3f128ebcaa90e6e50482"
4 * (cl-pass:check-password "hello" *)
5 T
6 * (cl-pass:check-password "wrong password" **)
7 NIL

Next, we need a way to retrieve a user:

49 (defun find-user-by-username (username)
50   "lookup user record by username."
51   (with-connection (db)
52     (retrieve-one
53      (select :*
54        (from :user)
55        (where (:= :username username))))))

This is the first time we see a clause other that from in an SXQL statement, in this case we specify what the username must be with the where clause. Let’s do the same with the email field:

57 (defun find-user-by-email (email)
58   "lookup user record by email."
59   (with-connection (db)
60     (retrieve-one
61      (select :* (from :user)
62              (where (:= :email email))))))

Now, since we’ll want the user to be able to log in either with a username or with an email address, let’s combine the two functions:

57 (defun find-user (username-or-email)
58   "lookup user record by username or email."
59   (or (find-user-by-username username-or-email)
60       (find-user-by-email username-or-email)))

And finally, we must check to see if the user credentials are valid:

69 (defun authenticate-user (username-or-email password)
70   "Lookup user record and validate password. Returns two values:
71    1st value, was password correct T or NIL
72    2nd value, was user found, T or NIL
73 Example:
74    (VALUES NIL NIL) -> user not found
75    (VALUES NIL T) -> user found, but wrong password
76    (VALUES T T) -> password correct"
77   (let ((password-hash (getf (find-user username-or-email) :password)))
78     (if password-hash 
79         (values (cl-pass:check-password password password-hash) t)
80         (values nil nil))))

This is a bit more advanced, so let’s look a bit closer. First we find the user and bind the password field to the local variable password-hash, if the value is not nil, such a user exists and we can check it’s password, if not, no such user exists and the login fails. What’s a bit more interesting here is the use of multiple return values. If you’re new to lisp, this is one of the more unique features of the language. We have the ability to return more than one value. By default only the first is used, but since there is more than one way for this function to fail(wrong password and no such user) we want to add a bit of extra information in case we might need it to display an error message for example. Basically the first value tells you if the login succeeded or it failed, and the second value tells you if it failed because of a wrong password or a non-existent user.

And that’s it, that’s our user model. We have everything we need for now. Let’s move on.

Page model

The basic idea of the page model is fairly simple. A page is identified by it’s title, but it can have different versions. Only one of those versions is the latest though, and when a user edits a page, a new record is inserted into the database and is designated the latest version. This is a very simple model that will do just fine for our purposes. Let’s look at the table definition:

84 (defun create-page-table ()
85   "Create page table if it doesn't exist yet."
86   (with-connection (db)
87     (execute
88      (create-table (:page if-not-exists t)
89          ((id :type 'serial :primary-key t)
90           (title :type 'text :not-null t)
91           (author-id :type 'integer :not-null t)
92           (content :type 'text :not-null t)
93           (date :type 'timestamp :not-null t)
94           (latest :type 'boolean :not-null t)) ;; is this the latest version of \
95 the page
96        (foreign-key '(:author-id) :references '(:user :id))))))

The fields id, title and content should be obvious by now, the author-id field is a foreign key to the user id, so it must be an integer. The date is a timestamp, I’ll explain that in a bit. The last field latest is a bit more interesting. It’s a binary flag that designates if the page is the last revision. This is to make lookup a bit easier, since most of the time we only need to see the latest version of an article. I’ll explain that in a bit more detail in a bit as well. The last line is a table option that specifies that author-id is a foreign-key to the user table and the field id. now let’s add a new page:

 97 (defun add-page (title author content)
 98   "Add page to database. Mark it as \"latest: true\", mark previous \"latest: fa\
 99 lse\"."
100   (with-transaction (db)
101     (execute
102      (update :page
103        (set= :latest "false")
104        (where (:and
105                (:= :title title)
106                (:= :latest "true")))))
107     (execute
108      (insert-into :page
109        (set= :title title
110              :author-id (getf (find-user author) :id)
111              :content content
112              :date (local-time:now)
113              :latest "true")))))

This is where the with-transaction macro comes into play. Since we can only have one latest version of a page, we need some way to simultaneously set the previous value to "false", and insert a new “latest” version, without worrying that we’ll mess up the database and end up with two new versions, or no new versions. This is done with an SQL transaction, it ensures that two queries will be executed as one so we don’t have to worry about order. The first execute statement does an SQL update on the previous article with the same title and a latest value of "true", and sets it to "false". Next we insert a new row in the table, with a latest value of “true”. We’ll see how that’s useful in the next function. Another thing to note is that the date field is given a value of (local-time:now), this function creates a new timestamp for us. I’ll go into more detail on how to deal with dates and time in Common Lisp in another chapter.

Let’s look at the rest of the functions, next up we need to find the latest version of an article:

114 (defun get-latest-page (title)
115   "Get the latest version of a page by TITLE."
116   (with-connection (db)
117     (retrieve-one
118      (select :*
119        (from :page)
120        (where (:and (:= :title title)
121                     (:= :latest "true")))))))

The query is relatively simple, we select the page with the title we want, and we ask for the latest version. This is much simpler that the alternative approach of sorting by the date and selecting the first result.

Now for a more complex query, this one has an implicit join:

123 (defun get-latest-pages-by-user (username)
124   "Get the latest versions of all pages by USERNAME."
125   (with-connection (db)
126     (retrieve-all
127      (select (:page.id :title :username :content :date :latest)
128        (from :page :user)
129        (where (:and (:= :user.id :author-id)
130                     (:= :user.username username)
131                     (:= :latest "true")))))))

Here we see a few features of SXQL we haven’t seen before, namely selecting specific fields(instead of just using :*) and qualifying field names with their tables, for example, because user and page both have fields named id we need a way to distinguish between them, so we call them :page.id and :user.id respectively. This works automatically. We also select from more than one table. What we’re doing here is we’re telling PostgreSQL to join the tables together, and select those fields where the author-id and the user.id are equal for the user named with the variable username and we also want the latest versions of the articles. Notice how much more complex this query would have been if we had to sort all the articles and get the latest one ourselves. This is actually what the next function does, but in this case we want to get all the versions of a single article:

133 (defun get-sorted-pages (title)
134   "Get all versions of a page by TITLE, sorted by it's timestamp in descending o\
135 rder, newest first."
136   (with-connection (db)
137     (retrieve-all
138      (select :*
139        (from :page)
140        (where (:= :title title))
141        (order-by (:desc :date))))))

This is a fairly simple query, we select all the pages with the given title, and we order them by the date. The latest article will be of course the first result. If we want to know how many versions a page has without retrieving them all, we can use a query like this:

142 (defun count-pages (title)
143   "Count the number of versions a page has."
144   (with-connection (db)
145     (getf (retrieve-one
146            (select (:title (:count :*))
147              (from :page)
148              (where (:= :title title))
149              (group-by :title)))
150           :count)))

This one is a bit more complex. What we’re doing here is were calling the COUNT() sql function on all rows and we’re grouping them by title. Since there will be only one title, there will be one result returned. The result will look something like this: (:title "foo" :count 3) if the article named foo has 3 versions. We simply getf the count from that result and return it. Next up, if we want to get a specific revision of a page, we can now do this pretty easily:

152 (defun nth-page-revision (title n)
153   "Get the nth version of a page, sorted by its DATE."
154   (nth n (get-sorted-pages title)))

We simply get the nth element of the result returned by get-sorted-pages. The (nth-page-revision "foo" 0) will give us the latest version of course, even if that’s a bit inefficient.

Finally we want a convenience function co create all of our tables for us:

158 (defun create-tables ()
159   "Create all tables, if they don't exist already."
160   (create-user-table)
161   (create-page-table))

And that’s our database model. Now let’s play around with it.

Playing with the model in the repl

Go to the repl, load our project, go into the fullstackwiki.model package and create the tables:

1 * (ql:quickload "fullstackwiki")
2 
3 * (in-package :fullstackwiki.model)
4 
5 * (create-tables)

Now let’s add a user and play around with it:

 1 * (add-user "fullstackwiki" "fullstackwiki@fullstackwiki.com" "this-is-a-passwor\
 2 d")
 3 
 4 * (find-user "fullstackwiki")
 5 => (:ID 1 
 6     :USERNAME "fullstackwiki" 
 7     :EMAIL "fullstackwiki@fullstackwiki.com"
 8     :PASSWORD "PBKDF2$SHA256:20000$e57a9bef3bb97ed3aff8d1c9d69d4f9f$bed1f429fc76\
 9 ed95d8f6d729d10897ce4ea7a1b93cea93a6e4690af7305ccd23")
10 
11 * (find-user "fullstackwiki@fullstackwiki.com")
12 => (:ID 1 
13     :USERNAME "fullstackwiki" 
14     :EMAIL "fullstackwiki@fullstackwiki.com"
15     :PASSWORD "PBKDF2$SHA256:20000$e57a9bef3bb97ed3aff8d1c9d69d4f9f$bed1f429fc76\
16 ed95d8f6d729d10897ce4ea7a1b93cea93a6e4690af7305ccd23")

Next up we’ll try to authenticate it:

1 * (authenticate-user "fullstackwiki" "this-is-a-password")
2 => T
3 => T

Notice the two return values. Same works with the email:

1 * (authenticate-user "fullstackwiki@fullstackwiki.com" "this-is-a-password")
2 => T
3 => T

Now let’s fail a few times:

1 * (authenticate-user "No Such User" "this-is-a-password")
2 => NIL
3 => NIL

notice the second value is also nil, so no user was found.

1 * (authenticate-user "fullstackwiki" "wrong password")
2 => NIL
3 => T

Here the first value is nil, but the second value is T, because the username is correct, but the password is wrong.

Now let’s create a few pages:

1 * (add-page "page1" "fullstackwiki" "This is the first version")
2 
3 * (add-page "page1" "fullstackwiki" "This is the second versions")
4 
5 * (add-page "page1" "fullstackwiki" "This is the third version")
6 
7 * (add-page "page 2" "fullstackwiki" "This is the first version of page 2")
8 
9 * (add-page "page 2" "fullstackwiki" "This is the second version of page 2")

Now let’s query them a bit:

1 * (get-latest-page "page1")
2 => (:ID 3 :TITLE "page1" :AUTHOR-ID 1 :CONTENT "This is the third version" :DATE\
3  3673640374 :LATEST T)
4 
5 * (get-latest-page "page 2")
6 => (:ID 5 :TITLE "page 2" :AUTHOR-ID 1 :CONTENT "This is the second version of p\
7 age 2" :DATE 3673640434 :LATEST T)

Now let’s get all the pages by our user:

1 * (get-latest-pages-by-user "fullstackwiki")
2 =>((:ID 3 :TITLE "page1" :USERNAME "fullstackwiki" :CONTENT "This is the third v\
3 ersion" :DATE 3673640374 :LATEST T)
4    (:ID 5 :TITLE "page 2" :USERNAME "fullstackwiki" :CONTENT "This is the second\
5  version of page 2" :DATE 3673640434 :LATEST T))

And all versions of page1:

1 * (get-sorted-pages "page1")
2 => ((:ID 3 :TITLE "page1" :AUTHOR-ID 1 :CONTENT "This is the third version" :DAT\
3 E 3673640374 :LATEST T)
4     (:ID 2 :TITLE "page1" :AUTHOR-ID 1 :CONTENT "This is the second versions" :D\
5 ATE 3673640356 :LATEST NIL)
6     (:ID 1 :TITLE "page1" :AUTHOR-ID 1 :CONTENT "This is the first version" :DAT\
7 E 3673640338 :LATEST NIL))

Notice only the first one is the latest. Let’s get the oldest one:

1 * (nth-page-revision "page1" 2)
2 => (:ID 1 :TITLE "page1" :AUTHOR-ID 1 :CONTENT "This is the first version" :DATE\
3  3673640338 :LATEST NIL)

And now let’s see how many are there:

1 * (count-pages "page1")
2 => 3
3 * (count-pages "page 2")
4 => 2

And that’s it. This is our model. In this chapter we learned how to use a relational database with Common Lisp and implemented this simple schema so now we can continue with building our app.