Category Archives: databases

Data-Table and CL-CSV

Posted on by
4

Data-Table

I have quite a few database driven web applications that make heavy use of tabular imports and exports (from their primary database, other databases, and exterior data sources (eg: CSVs). This data structure provides column, row, and cell access to getting and setting values, as well as providing functionality to create composite data-tables by retrieving and combining subsections of existing data-tables. This library also aims to ease type coercion from strings to common-lisp types.

CL-CSV

I had many scattered, not well tested, not easily runnable pieces of CSV code. I was unhappy with this situation, then decided to refactor all of this into a single project. I wrote tests for it and had a library so I thought I might release it. This project started as extensions and bugfixes on arnesi’s CSV.

I then looked around and saw there are other CSV libraries out there that probably mostly accomplished what I had set out to do. However, I already had my code that was tested, had an easier license (BSD), and provided a framework to interact with my other libraries and systems, so I figured why not just release it anyway.

The only interesting code in this library (to me) is that I managed to make the read/write-csv functions accept a string, pathname, or stream as the first argument and I managed to make sure that streams get closed if these functions created them (file streams for example), but not if the stream was passed in. Nothing great, but I had fun writing it.

Other niceties I would like to continue to build out in this library is its integration with other related libs (like CLSQL). I have code to handle exporting database queries as CSVs as well as code to handle importing CSVs into databases both serially and in bulk. I also use data-tables to have a lisp representation of the just parsed data-table and to coerce that table of string values into relevant common-lisp types.

CLSQL & Webapps

Posted on by
4

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.

Aqua Data Studio OSS License Program rocks!

Posted on by
1

I have been using Aqua Data Studio 7 since its release (and had used a couple versions before that I believe). I really enjoy using ADS, but have been wanting a new version for quite a while. Today while browsing their site I saw that a version 9 beta is available and also that they have a FREE license available for OSS developers. (They also offer student licensing). This is seriously quality software that makes my life so much easier. If you work with a variety of database systems, you can’t do much better than Aqua Data Studio to talk to all of them with one program.

Thanks Aqua Fold for my free licence! (I qualified for my trac plugin work)