ucwTutorialDataModel
_ucw Tutorial

Data model

Now we have two possibilities regarding how we define our data model. We can simply generate them with the data definition language of our Database or we can use the stuff cl-sql provides to us. However, first let us define the data to store about our users. The general idea is that we want to store a name and a total count of how many times he/she has accessed our pages.

We can define our class as follows:

(clsql:def-view-class downloaders ()
  ((id :accessor id :initarg :id :db-kind :key :type integer)
   (name :accessor name :initarg :name :type string)
   (downloads :accessor downloads :initarg :downloads :type integer)))

This is our first round and we do not care about ease of use, convenience, or the like; we're just trying to make it work.

Database stuff

Now we have defined a class which represents the SQL table in the database (an OO-relational mapping). But we still havn't created our database yet. So let's do that now. I'm using the command line programs from postgres for that task
  1. Create a database named ucw_tutorial
$ createdb ucw_tutorial

  • First access our newly built database
  • $ psql ucw_tutorial

  • Now create a user 'ucw_tutorial' with password 'ucw_tutorial'
  • ucw_tutorial=# create user ucw_tutorial with password 'ucw_tutorial';
    PLEASE NOTE: using the password 'ucw_tutorial' is highly inadvisable. This kind of password would be pretty easy to crack. However, we just want to get something running quickly and in general postgresql databases by default are only accessible via the localhost or your web hosting control panel.
    HOWEVER: please check the documentation for your version of postgresql and take proper precautions with daemons that could be potentially exposed to the internet. I usually make random passwords with the makepasswd utility available in Gentoo (package makepasswd) by executing:
    $ makepasswd --chars=15
    p18eDTGxTWft4P8
  • Now grant privileges on the database to the user (assuming you are still connected (in the psql program)
  • ucw_tutorial=# grant all on database ucw_tutorial to ucw_tutorial;
    Now 'ucw_tutorial' can do whatever he/she likes with that proper database

    All this is great but we still need some way of accessing the database from our UCW application. Common Lisp to the help please: I implement the following helpers:

    (defvar *db-database* "ucw_tutorial")
    (defparameter *db-user* "ucw_tutorial")
    (defparameter *db-password* "ucw_tutorial")
    (defvar *db-host* "localhost")
    
    
    (defun db-connect (&rest keys &key (database *db-database*) 
                          (host *db-host*)
                          (user *db-user*) 
                          (password *db-password*)
                          (port 5432)
                          (database-type :postgresql) &allow-other-keys)
      "Connect to 'database' as 'user' with 'password'." 
      #+use-lw-sql (declare (ignore host port database-type))
      (let ((con
             #-use-lw-sql
             (case database-type
               (:postgresql  (apply #'clsql:connect 
                                    (list host
                                          database 
                                         user
                                         password
                                         (if (integerp port)
                                             (format nil "~d" port)
                                           port))
                                    :database-type database-type
                                   :allow-other-keys t
                                   :if-exists :old
                                   keys))
               (:mysql (apply #'clsql:connect
                              (list nil database
                                   user password)
                                   :database-type database-type
                                   :allow-other-keys t
                                   :if-exists :old
                                   keys))
               (t
                (error "Not supported Database backend")))
             
             #+use-lw-sql
             (apply #'sql:connect 
                    (format nil "~a/~a/~a" database user password)
                    :if-exists :old
                    :allow-other-keys t
                    keys)
             ))
        (if con
            (clsql:enable-sql-reader-syntax)
          (error "Connection failure"))
        con))
    
    (defun db-disconnect (&key (database clsql:*default-database*))
      #+use-lw-sql
      (sql:disconnect :database database)
      #-use-lw-sql
      (clsql:disconnect :database database))
    

    It's just copied and pasted this from some old code I had hanging around. Getting this going is as easy as calling the function

    (db-connect)
    which will get you a connection to the database. One of the nice features of Common Lisp is that snippets are "simple" to try so load that function into a .lisp file, compile/interpret it and let's go ahead and do it from the REPL:
    TUTORIAL> (db-connect)
    output:
    TUTORIAL> (db-connect)
    #<CLSQL-POSTGRESQL:POSTGRESQL-DATABASE localhost:5432/ucw_tutorial/ucw_tutorial OPEN {1005DB6001}>
    

    Great! We now have a connection. So we can generate the table by typing

    TUTORIAL> (clsql:create-view-from-class 'downloaders)
    (clsql:create-view-from-class 'downloaders) ; No value

    So let's check whether the database is still there.

    (clsql:list-tables)
    ("sql_features" "sql_implementation_info" "sql_languages" "sql_packages"
     "sql_sizing" "sql_sizing_profiles" "pg_authid" "pg_statistic" "pg_type"
     "pg_attribute" "pg_class" "pg_autovacuum" "pg_inherits" "pg_index"
     "pg_operator" "pg_opclass" "pg_am" "pg_amop" "pg_amproc" "pg_language"
     "pg_largeobject" "pg_aggregate" "pg_trigger" "pg_listener" "pg_cast"
     "pg_namespace" "pg_conversion" "pg_depend" "pg_tablespace" "pg_pltemplate"
     "pg_shdepend" "pg_auth_members" "test" "downloaders" "pg_attrdef"
     "pg_constraint" "pg_database" "pg_description" "pg_proc" "pg_rewrite")
    

    Aha, and it is. Very concise (obscure?), but well it is there. So what do we know about our created table:

    TUTORIAL> (clsql:list-attributes 'downloaders)
    ("id" "name" "downloads")
    
    and
    TUTORIAL> (clsql:list-attribute-types 'downloaders)
    (("id" :INT4 4 NIL 0) ("name" :VARCHAR NIL NIL 1) ("downloads" :INT4 4 NIL 1))
    

    Great. So we've just examined the attributes for our table and the types of each field in our table. Nothing has been lost on the way. ;-)

    A last test: Can we get an entry into it?

    TUTORIAL> (defvar *tdownloader* (make-instance 'downloaders :id 1 :name "ucw_tutorial" :downloads 1))
    *TDOWNLOADER*
    
    Ok we've made an entry but we have to commit it to the database.
    TUTORIAL> (clsql:update-records-from-instance *tdownloader*)
    ; No value
    

    Hmm "; No value?" Is that good or bad? We have to check:

    (clsql:select [*] :from 'downloaders)
    ((1 "ucw_tutorial" 1))
    ("id" "name" "downloads")
    

    Oh, no news is good news then ;-) or in the words of Eric Raymond in reference to Unix culture, "Silence is Golden" but CRASH LOUDLY!

    Status

    We have now set up the database backend for our mighty impressive Uncommon Web Application. We know, we can connect, we know we can put in data, and we even can get that data back; all done via CL-SQL. what a nice and successful beginning ;-)

    Getting to some clean state

    Well this tutorial should be about UCW, but till now we've spent almost no time on UCW really, and unfortunately we are still not ready. All of this stuff should be available for you to tinker with it. So now we have to make it somewhat neat and in turn possible to install.

    To this end, I create a directory, and prepare a asdf-installable package. Here we go:

    ucw-tutorial.asd File

    (defpackage :ucw.tutorial.system
        (:use :cl :asdf))
    
    (in-package :ucw.tutorial.system)
    
    (defsystem ucw-tutorial
      :version "0.1.0"
      :depends-on (:clsql :ucw)
      :serial t
      :author "Friedrich Dominicus <frido at q-software-solutions.de>"
      :components ((:file "packages")
                   (:file "utils")
                   (:file "ucw-tutorial")))

    I thought a package would be a good idea:

    packages.lisp

    (in-package :cl-user)
    
    (defpackage :ucw.tutorial
        (:use :cl :ucw))
    Unfortunately a few symbols from :ucw and :clsql overlap. I was too lazy for now to fix that, but I guess I will during the course of this tutorial.

    utils.lisp

    You cannot get away without them, so without documentation and all the like
    (in-package :ucw.tutorial)
    (defvar *db-database* "ucw_tutorial")
    (defparameter *db-user* "ucw_tutorial")
    (defparameter *db-password* "ucw_tutorial")
    (defvar *db-host* "localhost")
    
    (defun db-connect (&rest keys &key (database *db-database*) 
                          (host *db-host*)
                          (user *db-user*) 
                          (password *db-password*)
                          (port 5432)
                          (database-type :postgresql) &allow-other-keys)
      "Connect to 'database' as 'user' with 'password'." 
      #+use-lw-sql (declare (ignore host port database-type))
      (let ((con
             #-use-lw-sql
             (case database-type
               (:postgresql  (apply #'clsql:connect 
                                    (list host
                                          database 
                                         user
                                         password
                                         (if (integerp port)
                                             (format nil "~d" port)
                                           port))
                                    :database-type database-type
                                   :allow-other-keys t
                                   :if-exists :old
                                   keys))
               (:mysql (apply #'clsql:connect
                              (list nil database
                                   user password)
                                   :database-type database-type
                                   :allow-other-keys t
                                   :if-exists :old
                                   keys))
               (t
                (error "Not supported Database backend")))
             
             #+use-lw-sql
             (apply #'sql:connect 
                    (format nil "~a/~a/~a" database user password)
                    :if-exists :old
                    :allow-other-keys t
                    keys)
             ))
        (if con
            (clsql:enable-sql-reader-syntax)
          (error "Connection failure"))
        con))
    
    (defun db-disconnect (&key (database clsql:*default-database*))
      #+use-lw-sql
      (sql:disconnect :database database)
      #-use-lw-sql
      (clsql:disconnect :database database))

    and last but not least our impressive data model

    ucw-tutorial.lisp

    (in-package :ucw.tutorial)
    
    (clsql:def-view-class downloaders ()
      ((id :accessor id :initarg :id :db-kind :key :type integer)
       (name :accessor name :initarg :name :type string)
       (downloads :accessor downloads :initarg :downloads :type integer)))

    Next: gettingUCWIntoPlay ; Previous: ucwTutorialExample