(connect '("localhost" "" "username" "passwd") :database-type :mysql)

  (create-database :users)

  (define-view-class user ()
    ((id     :initarg :id    :accessor user-id :type integer
             :db-constraints :key :not-null :auto-increment)
    ((name   :initarg :name  :accessor user-id :type string)
     (passwd :initarg :passwd :accessor user-passwd :type string)))

   (create-view-from-class 'user)

Then in the REPL

  CL-USER 1> (list-tables)
  ("users")

Now for some interaction:

  CL-USER 2> (make-instance 'user :name "Popeye" :passwd "sailorM0n")

  #<user>     ; <- instantiating an object creates a users.user record

Some time later, perhaps after a machine restart

  CL-USER 1> (select 'user)
  ((#<user>))
  CL-USER 2> (select 'user :where [= :name "Popeye"])
   (#<user>)

Say, you wanna change Popeye's password.

  CL-USER 3> (setf (user-passwd 
                      (select 'user :where [= :name "Popeye"]))
                   "new-password")

But typically you would just define a function, say, FIND-USER which will get you an object corresponding with a user record.

  (defun find-user (by val)
    (caar (select 'user :where (sql-= by val))))

That way you can find an object by ANY slot (or in SQL, find a record by the value of a column.

  CL-USER> (user-name (find-user 'id 0))
  "Popeye"
  CL-USER> (user-id   (find-user 'name "Popeye"))
  0
  CL-USER> (user-passwd (find-user 'name "Popeye"))
  "new-password"

That last value is due to our last update.

So you're bored with this class, and the auto-incremented primary key ID is MySQL only. Fine, redefine the ID slot mapping its value to a sequence

  CL-USER 3> (create-sequence 'user)
  CL-USER 4> (defclass user ()
                ((id  :initform (sequence-next 'user)))

The rest of the class definition stays the same.

  mysql > show tables;
  user  _clsql_sequence_user

:-) so you're bored with the database. drop the classes/tables one by one or just drop the whole database.

   CL-USER 5> (drop-sequence 'user)
   NIL
   CL-USER 6> (drop-view-from-class 'user)
   NIL
   CL-USER 7> (drop-database 'user)
   NIL

You can use the same code in MySQL, Postgres, DB2, Oracle, ODBC, Sqlite?, and an in-memory Sqlite3 implementation. This shit is industrial-strength eye candy. Scan the manual:

http://clsql.b9.com/manual/

Tutorial:

http://www.ravenbrook.com/doc/2002/09/13/common-sql/

It comes in fully open source and corporate supported versions: The OSS one is called CLSQL and its compatible with LispWorks' CommonSQL.

You can do that pretty easily with SQLite:

        conn = sqlite3.connect(':memory:')
        conn.execute('''
        create table visits (plano text,
                             "column" text,
                             "row" text,
                             route text)
        ''')
        conn.execute('''
        create table square (plano text,
                             "column" text,
                             "row" text,
                             x int,
                             y int)
        ''')

        for line in infile:
            if line.startswith('#'):
                continue
            plano, column, row, routes = line.split(',')
            column = column.lower()
            x, y = square_coords(plano, column, row)
            conn.execute('''insert into square (plano, "column", "row", x, y)
                                        values (?,     ?,        ?,     ?, ?)
                                        ''',
                                              (plano, column,   row,    x, y))

However, the trouble is that SQL is still kind of verbose, not type-safe, etc. I've been writing up some thoughts on how to do better at http://canonical.org/~kragen/binary-relations.html but I have a long way to go still.

That's pretty cool. The thing is, I'm actually not looking for a "conn" (connection" of any sort). I'm thinking of database as a container class. Kind of like...

db = New db

db.addTable(colname, list[names], primaryKey) db.addTable(joinTable, foreignKey, colname, list[names])

Map a = db.(select col a, col b from ...)

db = nil

and so forth... in other words, we're going the opposite direction from database as storage. Instead, we're going to database as something that is as in-memory as a hash (probably more likely to be persisted at some point, but not necessarily).

Sometimes, I see classes created that are bending over backward to recreate a bit of sql, and the model and logic really only exist for one purpose - sql-like methods on the data. People rarely write their own list, set or map classes, but often they will essentially recreate a bug-ridden 1/2 of common sql in their app...

Anyway, looks like you've been thinking about this for a while, I'll definitely take a look at the link. Thanks for posting it.

It's not connected to anything. ":memory:" means "something that is as in-memory as a hash".

That's interesting, looks like I have something I need to read about.

This is a common practice in Sybase-derived RDBMSs.