Lisp Web Tales

My attempts at learning web development, foolishly using common lisp, and even more foolishly, writing about it in public

By Pavel Penev

Get the PDF/EPUP/MOBI version from leanpub.com

Get the source from Github

Table of Contents

  1. Preface
  2. Introduction
  3. Why Lisp
  4. Whats in the book
  5. Who is this for
  6. What you need to get started
  7. Typographic conventions
  8. 1 The basics
  9. Raw example
  10. A simple blog
  11. The source code
  12. Source walk-through
  13. Conclusion
  14. 2 Setting up a project
  15. Systems
  16. Quicklisp and manual installation
  17. restas-project
  18. Setting up a hello-world project
  19. Running the project
  20. Conclusion
  21. 3 HTML generation and templating
  22. Exploring all the options
  23. closure-template
  24. 4 Putting it together: Updated blog example
  25. Creating the project
  26. Setting up defmodule.lisp
  27. Updating the templates
  28. Rewriting the routes
  29. Post chapter refactoring
  30. Running the app
  31. 5 Persistence part I: PostgreSQL
  32. Introduction
  33. Setting up PostgreSQL
  34. What is a policy?
  35. Creating the project
  36. The schema
  37. Connecting
  38. Defining the tables.
  39. Defining our interface
  40. Exporting the interface
  41. Conclusion
  42. 6 Persistence part II: The UI
  43. Introduction
  44. Set up
  45. The templates
  46. The routes
  47. Getting it to run
  48. 7 Persistence part III: Redis
  49. Introduction
  50. A note on redis
  51. The “schema” or lack there of
  52. The implementation
  53. Handling upvotes
  54. Handling links
  55. Conclusion
  56. 8 Modules as reusable components part I: restas-directory-publisher
  57. Introduction
  58. Handling static files in restas
  59. Mounting modules
  60. Adding a stylesheet to linkdemo
  61. Contexts
  62. Conclusion
  63. 9 Modules as reusable components part II: Implementing a reusable module
  64. Introduction
  65. The interface
  66. The templates
  67. The routes
  68. Using authdemo in an application
  69. Conclusion
  70. Appendix A: Getting started
  71. Linux
  72. Windows
  73. Appendix B: Recomended reading
  74. Online tutorials
  75. Cliki: The Common Lisp wiki
  76. IRC

5 Persistence part I: PostgreSQL

Introduction

I think we’ve written enough horrible code for the sake of simplicity and now I want to focus on “doing it right”, to the best of my abilities, meaning, I want to use a real database. The example app I’ll show you in the next few chapters will be a simple Reddit-like link sharing site. As an exercise, you can later go back and redo the previous blog example app to use a database.

In this chapter I’ll use PostgreSQL and the excellent postmodern library, which is specific to Postgres. Other options exist, from the popular “NoSQL” data stores like Redis and MongoDB, to lisp specific object stores, like bknr-datastore. We’ll implement a database layer in several of these stores. I’ll show you how to use the Restas policy mechanism to define a single interface to the various back-ends.

Setting up PostgreSQL

Linux

Install postgresql using your distros package manager, for Debian based distros this looks like:

1  # apt-get install postgresql

I also usually install the graphical admin tool pgadmin3 but it is optional.

Next we need to set up a database and a user for it. Postgres automatically creates a system user account named postgres for administration of the database server. Log in from the shell, and start psql(the postgres shell) so we can configure it to our needs:

1  # su - postgres
2  $ psql

Next, we need to create a pg user and a database, I’ll name the user linkdemouser and the database linkdemo, then we quit with the \q command:

1  postgres=# CREATE USER linkdemouser WITH PASSWORD 'mypass';
2  postgres=# CREATE DATABASE linkdemo OWNER linkdemouser;
3  postgres=# \q

Log out as the user postgres, and were done:

1  $ exit

Windows

You can download a PostgreSQL graphical installer for windows from the PostgreSQL site. Installation is straightforward, and you should probably follow the default installation options.

On windows 7 you will have to run the installer as an Administrator. The installer will ask you for a password for the super user postgres, you will use this DB account only to create a new user and a database.

At the end of the installation, deselect the check-box “Launch Stack Builder at exit?”, we won’t be needing that. Click finish.

After the installation completes, click the start menu, navigate to the PostgreSQL sub-menu and open “SQL Shell(psql)”. It will prompt you for a server name, database, port and user. Press Enter on all four for the default values. Then it will ask for the postgres user password, type it in and press Enter.

Next, we need to create a pg user and a database, I’ll name the user linkdemouser and the database linkdemo, then we quit with the \q command:

1  postgres=# CREATE USER linkdemouser WITH PASSWORD 'mypass';
2  postgres=# CREATE DATABASE linkdemo OWNER linkdemouser;
3  postgres=# \q

That’s it.

What is a policy?

Although we don’t need to do so, we’ll use the restas policy mechanism to define an interface to our database. But first lets have a short discussion of the problem policies solve for us.

In our app, if we need to access the database we’ll have a bunch of queries. Usually they are encapsulated in functions. So for example we might have a set of function like (find-user id) and (auth-user username password) which both contain queries written using postmodern. Lets say we want to have the option of using MySQL or some other backend and be able to switch between them. Common Lisp has a powerful OO system that allows us to do this easily. In our project we can define a variable called for example *datastore* and depending on its value a different implementation of our database layer gets used. Using generic functions this is easy, we simply define a class for each layer, for example:

1 (defclass postmodern-datastore () ...)
2 (defclass mysql-datastore () ...)

And set the *datastore* variable to an instance of one such class:

1 (setf *datastore* (make-instance 'postmodern-datastore ...))

Now, the functions find-user and auth-user can be defined in terms of generic functions:

1 (defun find-user (id)
2   (datastore-find-user *datastore* id))
3   
4 (defun auth-user (username password)
5   (datastore-auth-user *datastore* username password))

Here datastore-find-user and datastore-auth-user are both methods defined on the postmodern-datastore and mysql-datastore classes, an instance of which we pass as the first argument. This pattern is fairly common, and restas provides a mechanism, called a policy, for generating all of the boilerplate necessary for it, such as defining the generic functions, the dispatch variable, the functions that call the methods, and optionally, various packages to put all of the stuff in, etc.

Creating the project

Now let’s get on with writing our link sharing site. Let’s create the project skeleton with restas-project:

1  * (ql:quickload "restas-project")
2  * (restas-project:start-restas-project 
3     "linkdemo"
4     :depends-on '(:sexml 
5                   :postmodern 
6                   :ironclad 
7                   :babel))

Other than postmodern and sexml, we’ll need the ironclad and babel libraries which will be used to hash the user passwords.

Defining the policy

Our next order of business is to define our policy, and all of the packages we’ll be using. restas:define-policy has the following syntax:

 1 (restas:define-policy <policy-name>
 2   (:interface-package <interface-package-name>)
 3   (:interface-method-template <interface-method-template>)
 4   (:internal-package <internal-package-name>)
 5   (:internal-function-template <internal-function-template>)
 6   
 7   (define-method <method-name> (args...)
 8     "Documentation string")
 9     
10   <more method-definitions>)

What this does is:

  • Defines a dynamic variable with the same name as the policy: *policy-name*
  • Defines an interface package where all of the generic functions will be defined. This package will be used by the policy implementation we’ll write for the specific backends
  • Defines an internal package where the functions calling the methods will reside. This is the package our app will be using to access the database, instead of using the generic functions directly
  • We can specify name templates for the functions and the methods. For example if we define a policy method foo we can say that we want the corresponding generic function to be called generic-foo and the internal function to be called foo-bar. We do this by specifying a format string(the new names are generated with format) to the declarations in the policy definition: "GENERIC-~A" and "~A-BAR" respectively.

In our case, this is the concrete example declaration we’ll be putting in defmodule.lisp, also containing two method declarations for find-user and auth-user(there are more methods, but we’ll add them later):

 1 (restas:define-policy datastore
 2   (:interface-package #:linkdemo.policy.datastore)
 3   (:interface-method-template "DATASTORE-~A")
 4   (:internal-package #:linkdemo.datastore)
 5   
 6   (define-method find-user (username)
 7     "Find the user by username")
 8   
 9   (define-method auth-user (username password)
10     "Check if a user exists and has the suplied password"))

The policy is named datastore, which means that the dynamic variable controlling dispatch will be named *datastore*. This variable is defined in the internal package, in our case named linkdemo.datastore. This package will also include the functions we actually call in our app, such as find-user. The interface package is called linkdemo.policy.datastore and this is where the generic functions that define our interface to the database are defined.

Notice the declaration of :interface-method-template. The declaration means that we want the generic functions in the interface package to be renamed according to the template "DATASTORE-~A" so for instance the generic function for find-user will be named datastore-find-user. I opted to skip defining such a rule for the functions in the internal package, but I could have done the same thing using :internal-function-template.

Also notice that method declarations are done with define-method. Do not be confused! Methods in Common Lisp are defined with defmethod, and here define-method is just part of the syntax of the define-policy macro. The argument lists of these method declarations will be the same as the functions in linkdemo.datastore. The argument lists of the generic functions in linkdemo.policy.datastore will have an extra argument called datastore which will be used for dispatch. For example (find-user username) -> (datastore-find-user datastore username).

Here is the complete interface we will define today, complete with all the methods we need. Put this at the top of defmodule.lisp:

 1 (restas:define-policy datastore
 2   (:interface-package #:linkdemo.policy.datastore)
 3   (:interface-method-template "DATASTORE-~A")
 4   (:internal-package #:linkdemo.datastore)
 5 
 6   (define-method init ()
 7     "initiate the datastore")
 8   
 9   (define-method find-user (username)
10     "find the user by username")
11   
12   (define-method auth-user (username password)
13     "Check if a user exists and has the suplied password")
14   
15   (define-method register-user (username password)
16     "Register a new user")
17   
18   (define-method upvoted-p (link-id username)
19     "Check if a user has upvoted a link")
20   
21   (define-method upvote (link-id user)
22     "upvote a link")
23   
24   (define-method post-link (url title user)
25     "post a new link")
26   
27   (define-method get-all-links (&optional user)
28     "Get all of the links in the datastore")
29   
30   (define-method upvote-count (link-id)
31     "get the number of upvotes for a given link"))

Defining the rest of the packages

Next, we need to define the restas module for our application, and the package where we will implement the policy interface for PostgreSQL, put this code after the policy declaration in defmodule.lisp:

 1 (restas:define-module #:linkdemo
 2   (:use #:cl #:restas #:linkdemo.datastore))
 3 
 4 (defpackage #:linkdemo.pg-datastore
 5   (:use #:cl #:postmodern #:linkdemo.policy.datastore))
 6 
 7 (in-package #:linkdemo)
 8 
 9 (defparameter *template-directory*
10   (merge-pathnames #P"templates/" linkdemo-config:*base-directory*))
11 
12 (defparameter *static-directory*
13   (merge-pathnames #P"static/" linkdemo-config:*base-directory*))

Notice that linkdemo “uses” the internal package linkdemo.datastore where all of the functions like find-user are defined, and linkdemo.pg-datastore “uses” the interface package linkdemo.policy.datastore where the generic functions we need to implement methods for are defined.

The PostgreSQL backend will be implemented in a new file called pg-datastore.lisp, lets add it to linkdemo.asd:

1 (asdf:defsystem #:linkdemo
2   :serial t
3   :description "Your description here"
4   :author "Your name here"
5   :license "Your license here"
6   :depends-on (:RESTAS :SEXML :POSTMODERN :IRONCLAD :BABEL)
7   :components ((:file "defmodule")
8                (:file "pg-datastore")
9                (:file "linkdemo")))

Next, we create the file pg-datastore.lisp in the project directory and add an in-package declaration:

1 ;;;; pg-datastore.lisp
2 
3 (in-package #:linkdemo.pg-datastore)

The schema

The app will be very simple, it will have users, who can post links, and vote on them. That makes three tables:

  • A users table with an id, username and password fields.
  • A links table with an id, url, title, and submitter fields, where the submitter will be a foreign key to the users table.
  • The third table will be called votes and we will store all of the upvotes, it will have two fields, both foreign keys to the link and the user who upvoted it.

We could have stored the upvotes as an integer in the links table, but then users would be able to vote more than once per link, and we don’t want that. What we need in this case is a many to many relation. If you are familiar with the basics of relational databases, this would be the most straightforward way to model our data.

Connecting

There are two ways to connect to a PostgreSQL database, using the macro with-connection whose body will be executed in the context of a connection. Or using connect-toplevel which will create a connection and setup the special variable *database* to the new connection. This variable is used to execute queries. with-connection automatically binds it in its body. I’ll be using with-connection for our code, but connect-toplevel is useful for testing at the REPL so we don’t have to wrap all of our queries in with-connection.

In order to use the macro, we’ll need to have a variable with the connection spec, which has the following form: (database user password host). The connection spec will be stored in a slot of our pg-datastore class(the one used for dispatch). Let’s define this class in pg-datastore.lisp:

1 (defclass pg-datastore ()
2   ((connection-spec :initarg :connection-spec
3                     :accessor connection-spec)))

For testing purposes, I’ll create an instance of this class and store it in a variable called *db*(in our real app, we’ll use *datastore* in the internal package):

1 (defparameter *db*
2   (make-instance 'pg-datastore
3                  :connection-spec '("linkdemo" "linkdemouser" "mypass" "localhost")))

We can now do this:

1 (with-connection (connection-spec *db*)
2   ;query goes here
3   )

Defining the tables.

DAO classes

Postmodern isn’t an ORM, so if you’re used to them from places like Django or Rails, you’re in luck, because in my very humble opinion, they suck. If you’re using a RDBMS, learn the relational model already. Now that I got that rant out of the way, lets move on. Even though it isn’t an ORM, postmodern does allow us to work with objects, but they are just simple DAOs(Database Access Objects). DAO objects are defined the same way ordinary lisp objects are, using defclass, but they have additional syntax for giving database types to our slots, and we need to add the dao-class 1 to the definition. Here is how the users table will be defined:

1 (defclass users ()
2   ((id :col-type serial :reader user-id)
3    (name :col-type string :reader user-name :initarg :name)
4    (password :col-type string :reader user-password :initarg :password)
5    (salt :col-type string :reader user-salt :initarg :salt))
6   (:metaclass dao-class)
7   (:keys id))

The difference between a standard class definition and a dao class is that we have a :col-type option to slot definitions that specify what database type we want to create. In our case, id will be a serial which is the PostgreSQL type for an integer that will auto-increment every time we add a record. The other two fields will be strings. In order to add the :col-type option to our slots, as well as other additions to our dao classes we must specify dao-class as a metaclass. Metaclasses are the standard Common Lisp mechanism for extending the object system. We also specify that we want id to be a primary key. The password and salt slots will contain the password hash and salt from encrypting the password of the user.

We can see what SQL code will be generated by this definition with dao-table-definition:

1  * (dao-table-definition 'users)

It will give us the following output as a string(formatting by me):

1 CREATE TABLE users (
2   id SERIAL NOT NULL,
3   name TEXT NOT NULL,
4   password TEXT NOT NULL,
5   salt TEXT NOT NULL,
6   PRIMARY KEY (id)
7 )

Lets implement the method used for initiating the datastore, creating the tables seems like a good thing to put in it. The generic function is named datastore-init, here it is:

1 (defmethod datastore-init ((datastore 'pg-datastore))
2   (with-connection (connection-spec datastore)
3     (unless (table-exists-p 'users)
4       (execute (dao-table-definition 'users)))))

First we connect to the database, then, using the table-exists-p predicate we check if the table is already defined. If it isn’t, we use the execute function, which will execute an SQL expression, in our case, it will be the output of dao-table-definition. Later we’ll augment this method with the definitions of the other tables.

We can call this method like this:

1 (datastore-init *db*)

After the table is defined, we can add users by instantiating objects of the users class, and inserting them into the db using insert-dao, here is an example:

1 (with-connection (connection-spec *db*)
2   (insert-dao (make-instance 'users
3                              :name "user"
4                              :password "pass")))

Querying

Say we’ve added a bunch of users to the db, we can now query them in two ways, as DAOs, or as an ordinary table. The dao way is with select-dao, which returns a list of lisp objects:

1  * (with-connection (connection-spec *db*)
2      (select-dao 'users ))
3 
4  => (#<USERS {10089B4443}> #<USERS {10089B63F3}> #<USERS {10089B69F3}>
5  #<USERS {10089B6FF3}>)

We can also use a normal query using S-SQL, a lispy syntax for SQL. Have a look at the example(the password and salt values are made up of course):

1  * (with-connection (connection-spec *db*)
2      (query (:select :* :from 'users)))
3 
4  => ((1 "user" "pass" "salt")
5      (2 "user1" "pass1" "salt1")
6      (3 "user2" "pass2" "salt2")
7      (4 "user3" "pass3" "salt3"))

The query form takes an S-SQL expression. S-sql operators are keywords. Our query returns a list of lists, with the values in the table. We can get slightly more useful output with the query args/format optional parameter which specifies the format of the result. The most common values are :plists and :alists, returning the result in the format of a plist or alist, with the column names. Example:

 1  * (with-connection (connection-spec *db*)
 2      (query (:select :* :from 'users) :plists))
 3 
 4  => ((:ID 1 :NAME "user" :PASSWORD "pass" :SALT "salt")
 5      (:ID 2 :NAME "user1" :PASSWORD "pass1" :SALT "salt1")
 6      (:ID 3 :NAME "user2" :PASSWORD "pass2" :SALT "salt2")
 7      (:ID 4 :NAME "user3" :PASSWORD "pass3" :SALT "salt3"))
 8 
 9  * (with-connection (connection-spec *db*)
10      (query (:select :* :from 'users) :alists))
11 
12  => (((:ID . 1) (:NAME . "user") (:PASSWORD . "pass") (:SALT . "salt"))
13      ((:ID . 2) (:NAME . "user1") (:PASSWORD . "pass1") (:SALT . "salt1"))
14      ((:ID . 3) (:NAME . "user2") (:PASSWORD . "pass2") (:SALT . "salt2"))
15      ((:ID . 4) (:NAME . "user3") (:PASSWORD . "pass3") (:SALT . "salt3")))

We’ll see other format examples later on.

Because defclass definitions of dao objects don’t support adding foreign keys, well have to use a slightly different method of defining tables using the deftable macro. We start off by defining our DAO:

1 (defclass links ()
2   ((id :col-type serial :reader link-id)
3    (url :col-type string :reader link-url :initarg :url)
4    (title :col-type string :reader link-title :initarg :title)
5    (submitter-id :col-type integer :reader link-submitter-id :initarg :submitter-id))
6   (:metaclass dao-class)
7   (:keys id))

Next, because we need to add the foreign key constrain, we use the deftable macro to define a table. The table will inherit all of the fields of the dao class:

1 (deftable links
2   (!dao-def)
3   (!foreign 'users 'submitter-id 'id))

!dao-def tells deftable to inherit the field definitions from the dao class definition, and !foreign tells deftable to add a foreign key constrain to the table. !foreigns first parameter is the target table, the second is the field, and if the field has a different name in the definition of the target table, add it as a third parameter.

Lets do the same for votes:

 1 (defclass votes ()
 2   ((link-id :col-type integer :reader vote-link-id :initarg :link-id)
 3    (submitter-id :col-type integer :reader vote-submitter-id :initarg :submitter-id))
 4   (:metaclass dao-class)
 5   (:keys link-id submitter-id))
 6 
 7 (deftable votes
 8   (!dao-def)
 9   (!foreign 'links 'link-id 'id)
10   (!foreign 'users 'submitter-id 'id))

Now, let’s update the datastore-init method to create these tables as well. Note that unlike ordinary dao-defined tables, tables defined with deftable are created in the database using the function create-table:

1 (defmethod datastore-init ((datastore pg-datastore))
2   (with-connection (connection-spec datastore)
3     (unless (table-exists-p 'users)
4       (execute (dao-table-definition 'users)))
5     (unless (table-exists-p 'links)
6       (create-table 'links))
7     (unless (table-exists-p 'votes)
8       (create-table 'votes))))

Defining our interface

What will the interface consist of? We’ll need a way to register a user, and authenticate one at login. We’ll also need to be able to post a link and upvote it. Also we’ll need a way to get a list of all the links for the home page, and a way to get their score, since we’ll want to sort by it. That’s about it for a quick version 1.

Hashing passwords

The original version of this chapter stored passwords in plain text, I decided to actually try to be secure in this revision. For this purpose I’ll use the ironclad cryptography library to hash passwords. We’ll use the pbkdf2 algorithm to hash our passwords:

1 (defun hash-password (password)
2   (multiple-value-bind (hash salt)
3       (ironclad:pbkdf2-hash-password (babel:string-to-octets password))
4     (list :password-hash (ironclad:byte-array-to-hex-string hash)
5           :salt (ironclad:byte-array-to-hex-string salt))))

This code is kind of dense, all you need to know about it is that it returns a plist with a password hash and a salt, ready to be stored into a database.

Checking to see if a password matches involves taking said password, hash and salt, hashing the password using the salt, and comparing hashes:

1 (defun check-password (password password-hash salt)
2   (let ((hash (ironclad:pbkdf2-hash-password
3                (babel:string-to-octets password)
4                :salt (ironclad:hex-string-to-byte-array salt))))
5     (string= (ironclad:byte-array-to-hex-string hash)
6              password-hash)))

With this out of the way, we can now go on and write the user handling logic.

Handling users

When we create and authenticate a user we’ll need a way to find if a user already exists in the database, datastore-find-user does this and returns a plist with the users credentials, and nil if no such user exists:

1 (defmethod datastore-find-user ((datastore pg-datastore) username)
2   (with-connection (connection-spec datastore)
3     (query (:select :* :from 'users
4                     :where (:= 'name username))
5            :plist)))

Note that the argument to query is :plist and not the plural :plists. This tells postmodern to return just one result.

Next, when a user logs in, we simply find the user, and check if the password matches. If so, we return the username. If no such user exists or the passwords don’t match, we return nil:

1 (defmethod datastore-auth-user ((datastore pg-datastore) username password)
2   (let ((user (datastore-find-user datastore username)))
3     (when (and user
4                (check-password password (getf user :password) 
5                                         (getf user :salt)))
6       username)))

And finally registering the user. We check if the user is registered, and if not, we create a record in the db:

 1 (defmethod datastore-register-user ((datastore pg-datastore) username password)
 2   (with-connection (connection-spec datastore)
 3     (unless (datastore-find-user datastore username)
 4       (let ((password-salt (hash-password password)))
 5         (when
 6             (save-dao
 7              (make-instance 'users
 8                             :name username
 9                             :password (getf password-salt :password-hash)
10                             :salt (getf password-salt :salt)))
11           username)))))

We check to see if the user isn’t registered if he isn’t, we hash the password, make a DAO object with the username, hash and salt, and save it. The reason save-dao is wrapped in a ‘when` is to make sure the operation was successful, if so, we return the username.

In order to handle links properly, let’s write an datastore-upvoted-p predicate method:

1 (defmethod datastore-upvoted-p ((datastore pg-datastore) link-id user)
2   (with-connection (connection-spec datastore)
3     (query (:select :link-id :users.name :from 'votes 'users
4                     :where (:and (:= 'users.id 'submitter-id)
5                                  (:= 'users.name user)
6                                  (:= 'link-id link-id)))
7            :plist)))

This is a slightly more complicated query, it even has an implicit join. Essentially, we query the votes table for any rows with the link-id and submitter-id matching that of the username of the user, and we return the link-id and the username.

Upvoting a link involves finding the id of the user, checking if the user hasn’t already upvoted that link, and then simply doing an insert:

1 (defmethod datastore-upvote ((datastore pg-datastore) link-id user)
2   (with-connection (connection-spec datastore)
3     (let ((submitter-id (getf (datastore-find-user datastore user) :id)))
4       (when (and submitter-id
5                  (not (datastore-upvoted-p datastore link-id user)))
6         (when (save-dao (make-instance 'votes
7                                        :link-id link-id
8                                        :submitter-id submitter-id))
9           link-id)))))

For posting a link, we want the user submitting it to also automatically upvote it. In order to do that though, we have to use a DAO, since query will not return us the inserted value, and we need the links id in order to upvote it. After we save-dao the DAO though, its link-id accessor function will return it for us and we can upvote it. This is how it looks like:

1 (defmethod datastore-post-link ((datastore pg-datastore) url title user)
2   (with-connection (connection-spec datastore)
3     (let* ((submitter-id (getf (datastore-find-user datastore user) :id))
4            (link (make-instance 'links
5                                 :url url
6                                 :title title
7                                 :submitter-id submitter-id)))
8       (save-dao link)
9       (datastore-upvote datastore (link-id link) user))))

Getting all the links involved 3 steps, selecting them, checking their upvote count, and then sorting them. We’ll need to write a bunch of functions to do so. First let’s write a function that selects them all:

1 (defun get-all-links/internal ()
2   (query (:select :* :from 'links) :plists))

This function doesn’t have a with-connection in its body because it is internal and will only be used in a context that has a connection.

Now, given a link-id, getting the upvote count is as easy as using the sql COUNT function. We tell query to format the result with the :single keyword, which returns a single result, in our case an integer:

1 (defmethod datastore-upvote-count ((datastore pg-datastore) link-id)
2   (with-connection (connection-spec datastore)
3     (query (:select (:count link-id) :from 'votes
4                     :where (:= link-id 'link-id))
5            :single)))

We’ll need to augment the link plist with two keys: :votes is the number of votes the link has, and voted-p is a boolean specifying whether or not the currently logged in user has upvoted it. We do this for every link returned by get-all-links/internal. Let’s define a function to do that. We have to pass datastore to it because it will call datastore-upvoted-p:

1 (defun add-vote-count (datastore links username)
2   (loop
3      for link in links
4      for id = (getf link :id)
5      collect (list* :votes (datastore-upvote-count datastore id)
6                     :voted-p (datastore-upvoted-p datastore id username)
7                     link)))

The simplest way I found to get them sorted is using common lisps sort function:

1 (defun sort-links (links)
2   (sort links #'>
3         :key #'(lambda (link) (getf link :votes))))

And finally we define our method:

1 (defmethod datastore-get-all-links ((datastore pg-datastore) 
2                                     &optional username)
3   (with-connection (connection-spec datastore)
4     (sort-links
5      (add-vote-count datastore
6                      (get-all-links/internal)
7                      (or username "")))))

Note that we use or to pass the optional value username, in case it is nil, we want to pass an empty string, since there might be a case where no user is logged in, and upvoted-p expects a string, and will choke on nil.

Exporting the interface

The only thing we’ll need to export from this package is the pg-datastore classname. Lets do that in defmodule.lisp:

1 (defpackage #:linkdemo.pg-datastore
2   (:use #:cl #:postmodern #:linkdemo.policy.datastore)
3   (:export #:pg-datastore))

Conclusion

That’s it for the DB layer for now, In the next chapter we’ll start using it to finish our app, and then we’ll augment it to use a different backend datastore.

Here are some links for the curious: