database.lsp

Module index

Module: DF.DB, DF.SQL, DF.BLOB

Generic database access interface for Dragonfly using Objective newLISP

Version: 1.1
Author: Greg Slepak
Location: http://www.taoeffect.com/newlisp/database.lsp.txt

The purpose of this module is to standardize the interface to access databases. This will allow you, for the most part, to write your code once and easily switch the type of database that you're using.

To accomplish this, the interface introduces three Objective newLISP classes: DF.DB, DF.SQL, and DF.BLOB.

DF.DB

A DF.DB object represents a database connection. Using it you connect to the database and execute SQL queries.

DF.SQL

A DF.SQL object is a wrapper around an SQL statement, is retrieved through one of two functions: DF.DB:execute-query and the lower-level DF.DB:preprare-sql.

It is used to retrieve rows from the result set of a query one-by-one.

DF.BLOB

DF.BLOB is used to insert and retrieve (possibly large) binary data into databases. It is needed for two reasons:

1. newLISP uses strings to buffer and store binary data, and that's already used to store text.
2. BLOBs can be very large, so by storing them in a context we avoid excessive copying.

Unlike the other two classes, DF.BLOB provides a basic working implementation for DF.SQL subclasses to use. You may of course subclass it if your database requires additional functionality. It requires special usage considerations, see its documentation below.

Example

 (push-autorelease-pool) ; were going to be using DF.BLOBs.
 (setf db (instantiate Sqlite3 ":memory:"))
 (if-not db (throw-error "couldn't open db"))
 (db:execute-update "CREATE TABLE fish (id INTEGER PRIMARY KEY, name TEXT, weight REAL, blah BLOB)")
 (db:execute-update "INSERT INTO fish (name,weight) VALUES (?,?)" '("flipper" 234.123))
 (db:execute-update "INSERT INTO fish (name,weight) VALUES (?1,?2)" '(("?1" "catfish") ("?2" 100.3)))
 (db:execute-update "INSERT INTO fish (blah) VALUES (?)" (list (DF.BLOB (dup "\000" 10))))
 (db:execute-update "INSERT INTO fish (blah) VALUES (:cat)" (list (list ":cat" (DF.BLOB (dup "\000" 10)))))
 (setf sql (db:execute-query "SELECT * FROM fish"))
 (do-while (list? row)
  (push-autorelease-pool) ; "in case" we end up fetching a lot of large blobs
  (setf row (sql:next-row))
 	(println "row: " row)
  (pop-autorelease-pool)
 )
 (deallocate sql)
 (deallocate db)
 (pop-autorelease-pool) ; deallocate the blobs we created
 

Requirements

• Dragonfly newLISP Web Framework (see note below)
• newLISP 10.1.9 or higher is strongly recommended, but any version after 10.1 should work.
• Objective newLISP 1.0
• Libraries for a supported database

Dragonfly is only required for its logging functions. You can easily implement your own versions of DF:log-err, DF:log-debug, and the other functions found in Dragonfly's log.lsp.

Version history

1.1 • DF.BLOB added
1.0 • initial release

- § -

DF.DB

syntax: DF.DB

Represents a database connection. You create a DF.DB object like so:

(instantiate DF.DB-Subclass [connection [args...]])

What type should be represented by connection is undefined, but it's recommended that subclasses use strings. If connection is specified the a connected instance must be returned (or nil upon failure).

The possibly optional args... are specific to which subclass you're using. See its corresponding documentation.

Instances must have code in their dealloc method so that they can properly cleanup their resources (e.g. shutdown connection) if deallocated with deallocate.

- § -

DF.DB:open

syntax: (DF.DB:open connection)

Returns true if the connection was opened successfully, nil if there was an error opening the connection, or throws an exception if already connected.

- § -

DF.DB:close

syntax: (DF.DB:close)

Returns true if the connection was closed successfully or was already closed, or nil if there was an error closing the connection.

- § -

DF.DB:prepare-sql

syntax: (DF.DB:prepare-sql str-sql)
parameter: str-sql - A single SQL statement. Does not need to end in ';'

Returns a DF.SQL object upon success, nil on failure, or throws an exception if not connected.

important: If your SQL statement contains placeholders (to be bound later using DF.SQL:bind-params) you may not mix and match placeholder styles! Pick one placeholder style and stick with it for the entire statement.

- § -

DF.DB:execute-update

syntax: (DF.DB:execute-update str-sql [list-params])
parameter: str-sql - A single SQL statement. Does not need to end in ';'
parameter: list-params - A list of parameters to bind to a parameterized query

Same as DF.DB:execute-query but returns true instead of a DF.SQL object upon success. Useful for SQL such as "UPDATE" and "INSERT".

- § -

DF.DB:execute-query

syntax: (DF.DB:execute-query str-sql [list-params])
parameter: str-sql - A single SQL statement. Does not need to end in ';'
parameter: list-params - A list of parameters to bind to a parameterized query

A short-hand for DF.DB:prepare-sql and DF.SQL:bind-params. Returns a DF.SQL object upon success, nil on failure, or throws an exception if not connected.

see: documentation for DF.SQL:bind-params for more info on list-params.

- § -

DF.DB:rows-for-query

syntax: (DF.DB:rows-for-query str-sql [list-params])

Same as DF.DB:execute-query but retrieves all of the rows and returns them as a list of results.

important: If any columns contain BLOB types, you must have an autorelease pool allocated prior to calling this function!

- § -

DF.DB:rowid

syntax: (DF.DB:rowid)

Returns the row id for the last row that was inserted or throws an exception if not connected.

- § -

DF.DB:changes

syntax: (DF.DB:changes)

Returns how many rows were affected by the last INSERT/UPDATE, or throws an exception of not connected

- § -

DF.DB:version

syntax: (DF.DB:version)

Returns the version number of the database library being used as an integer.

- § -

DF.DB:table-exists?

syntax: (DF.DB:table-exists? table-name)

Returns nil or non-nil depending on whether the table named table-name exists, or throws an exception if not connected.

- § -

DF.DB:connected?

syntax: (DF.DB:connected?)

return: nil or non-nil depending on whether this DF.DB object has an active connection.

- § -

DF.DB:last-error

syntax: (DF.DB:last-error)

return: a list of two elements: the most recent error code and a description string.

- § -

DF.SQL

syntax: DF.SQL

Represents a prepared statement. It is used to bind values to a statement's parameters and retrieve the results of a query.

You do not create a DF.SQL instance yourself but obtain one through DF.DB:prepare-sql. However, if you've obtained an instance then you are responsible for freeing its memory and closing its connection when you're finished with it.

Subclasses should make sure that their dealloc method calls DF.SQL:close so that statements can be freed and closed in one function call using deallocate.

- § -

DF.SQL:bind-params

syntax: (DF.SQL:bind-params list-params)

Binds the placeholders of this SQL statement to the values in list-params.

list-params may be simply a list of values, or an association list of key/value pairs, depending on the placeholder pattern used in the SQL. Placeholder styles may not be mixed per SQL statement.

Values may be any of newLISP's primitives. To specify a BLOB wrap your string in a DF.BLOB:

example:

 ; "weight" is a REAL and "blah" is a BLOB
 (push-autorelease-pool) ; create our autorelease pool since we're using DF.BLOB
 (setf sql (db:prepare-sql "INSERT INTO fish (weight,blah) VALUES (?,?)"))
 (sql:bind-params (list 1043.3 (DF.BLOB (dup "\000" 5))))
 (println (sql:next-row))
 (deallocate sql)
 (pop-autorelease-pool)
 

- § -

DF.SQL:next-row

syntax: (DF.SQL:next-row)

Returns a row as a list from the result set of a statement, true if there are no more rows to return, or nil if there was an error.

example:

(push-autorelease-pool) ; only necessary if there's a BLOB column
 (while (list? (setf row (sql:next-row)))
 	(println "row: " row)
 )
 (pop-autorelease-pool) ; free any blobs

important: If any columns contain BLOB types, you must have an autorelease pool allocated prior to calling this function!

- § -

DF.SQL:reset

syntax: (DF.SQL:reset)

Resets the statement so that it can be re-used without preparing another statement (which is more efficient). After reseting a statement you typically use DF.SQL:bind-params to bind new values and then DF.SQL:next-row.

- § -

DF.SQL:close

syntax: (DF.SQL:close)

Releases the resources used by the SQL statement represented by this object. You cannot use it anymore after calling this method, the only thing left to do is to deallocate it, and since sublasses of DF.SQL must call close in their dealloc methods, its often simpler to call 'deallocate on the object instead.

Returns true upon success, nil on failure or if already closed.

- § -

DF.BLOB:DF.BLOB

syntax: (DF.BLOB:DF.BLOB str-blob)

An object wrapper around a blob of data represented by str-blob, used for efficient passing of possibly large binary data.

Unlike most other ObjNL classes, you typically create an instance by simply calling its constructor instead of calling instantiate yourself. It will then instantiate and autorelease a DF.BLOB instance containing the data in DF.BLOB:blob.

important: An autorelease pool must be in place when using the constructor to instantiate a blob!

see: the introduction to this document for example usage.

- ∂ -
generated with newLISP  and newLISPdoc