CLSQL & Webapps

A commonly experienced error when using CLSQL in a web environment is database connections conflicting with each other from simultaneous web requests. These problems arise because, by default, clsql standard-db-objects keep a reference to the connection they were queried / created from and reuse this database connection (rather than a new one you may have provided with clsql-sys:with-database). This means that two separate threads could try to use the same database connection (provided through clsql-sys:with-database or by having objects queried from the same connection accessed in multiple threads / http requests).

We solved this problem by introducing a clsql-sys::choose-database-for-instance method (available in clsql master branch from http://git.b9.com/clsql.git. (This branch will eventually be released as CLSQL6) Then in our web applications we define the following class and method override. Usually I then pass this name to clsql-orm or as a direct superclass to any of my web def-view-classes. After this, I just use with-database to establish dynamic connection bindings and everything pretty much works out (as these dynamic bindings are not shared across threads).

(defclass clsql::web-db-obj (clsql-sys:standard-db-object)
    nil
    (:metaclass clsql-sys::standard-db-class))

(defmethod clsql-sys::choose-database-for-instance
    ((object clsql::web-db-obj) &optional database)
  (or database clsql-sys:*default-database*))

(clsql-sys:def-view-class table-1 (clsql::web-db-obj)
    (...))

(clsql-orm:gen-view-classes
 :package :net.company.my.db
 :nicknames :my-db
 :export-symbols t
 :classes '(users employees salaries)
 :inherits-from '(clsql::web-db-obj))

Note: CLSQL-Fluid seems to be trying to accomplish much the same goals.

CL-Inflector

CL-Inflector is a branch of a port of ruby/ActiveRecord’s inflector class to make it easier singularize and pluralize english words. The original author didn’t seem much interested in it any more, so hoping to give it a better life, I added asdf files and a test suite and fleshed out some of the special cases. I also use it in clsql-orm to make singular class names from plural table names if that is your kind of thing.

CLSQL-ORM: Turn you existing database schema into Common Lisp CLSQL-View-Classes

CLSQL-ORM is a common lisp library I just pushed to my git hub account. Its primary goal is to generate CLSQL-view-classes based on an existing database. It uses the “information_schema” views to introspect relevant data from the database, then builds and evals the view-class definitions. This project is a significant branch of clsql-pg-introspect, attempting to remove its “pg” aspects in favor of the standard “information_schema”. It might have changed some semantics/conventions along the way, (I’m not sure as I didn’t use the original project much, and that was long ago).

I wanted to generate my lisp objects from the database for a couple reasons. One, I am fairly comfortable with SQL databases and am used to specifying them in whatever variant of sql the database engine supports. Two, I am most often presented with an extant working database that I want to interact with (such as a wordpress install), where the schema of the database can change, and I just want my common lisp to match whatever the database says, rather than trying to keep both up to date with each other manually. Obviously this project encodes many of my own, personal thoughts and tastes about databases, which may not be the same as your thoughts and tastes. This project is perhaps best though of as a jumping off point for creating your own personal common lisp ORM, though it should be usable as is, if your tastes and mine coincide.