5 Connecting to, querying and updating databases

Snooze applications interact with databases via scheme/class object that implement the snooze<%> interface. Each object establishes connections to a single database.

Snooze objects guarantee thread safety: multiple threads can connect concurrently to the same object without running into problems. This means that each object can actually maintain more than one database connection concurrently, although only one connection is ever visible in any given thread. This is illustrated by the following code sample:

#lang scheme/base

(require (planet untyped/snooze:2/snooze)

(planet untyped/snooze:2/sqlite3/sqlite3))

; snooze%

;

; Interface to the SQLite database in "mydata.db":

(define mydata (make-snooze (make-database "mydata.db")))

; (persistent-struct (U string #f))

(define-persistent-struct person

([name type:string]))

; -> void

;

; Connects to the database and inserts a record:

(define (save-new-person)

(send mydata call-with-connection

(lambda ()

(send mydata save! (make-person "Dave")))))

; These threads won't interfere with one another:

(thread save-new-person)

(thread save-new-person)

(thread save-new-person)

5.1 Object oriented interface

(make-snooze database) → snooze<%>

database : database<%>

Creates a Snooze object to talk to the specified database. Snooze objects implement the snooze<%> interface, which contains methods for connecting to, querying, updating and maintaining the database.

snooze<%> : interface?

(send a-snooze call-with-connection thunk) → any

thunk : (-> any)

Establishes a connection to the database and maintains it for the dynamic extent of thunk. The connection is closed when control is transferred outside of thunk via a continuation jump, an exception or a graceful return. The connection is re-established when control passes into thunk via a continuation jump.

Only one connection may be opened at a time in each thread. Child threads do not inherit their parents’ connections. exn:fail:snooze is raised if a connection is already open when control is transferred into thunk.

(send a-snooze current-connection) → (U connection? #f)

Returns the current connection, or #f if no connection is established.

The remaining methods require a connection to be open when they are called: exn:fail:snooze is raised in all cases if this is not the case.

(send a-snooze find-all query) → (listof result)

query : query?

find-all retrieves a list of all matching results from the database. conn is an optional connection: if omitted, the default connection from call-with-database is used.

Select statements

query is a select statement, as returned by sql:select. It determines both the data retrieved and the type of each result.

(send a-snooze find-one query) → (U result #f)

query : query?

Similar to find-all, but only returns the first result found. If no results are found, returns #f instead.

(send a-snooze g:find query) → (gen-> result)

query : query?

Working with generators

Similar to find-all, but returns a generator of results. This is the most general query mechanism offered by Snooze: generators allow you to manipulate results one at a time in a functional manner, without wasting lots of memory on intermediate lists.

(send a-snooze save! struct) → persistent-struct?

struct : persistent-struct?

Inserts or updates the database record for the supplied struct:

If struct has an id of #f, save! assumes that no corresponding database record exists. It sets the revision field to 0 and uses an SQL "INSERT" statement to insert a new database record. Finally, save! sets the id to the primary key of the record and returns the mutated struct.

If struct already has an integer id when save! is called, the behaviour is different. First, save! checks the database to make sure the stored revision number matches the revision number in struct. It then increments the revision and uses an SQL "UPDATE" statement to update the database record with the new information. Finally, save! returns the mutated struct.

save! raises exn:fail:snooze:revision if a revision number check fails. This normally indicates that struct has been concurrently loaded and saved by another thread.

(send a-snooze delete! struct) → persistent-struct?

struct : persistent-struct?

Deletes the database record for struct and sets its id and revision to #f. Returns the mutated struct to allow the programmer to chain the call with calls to other procedures.

delete! raises exn:fail:snooze if no database record exists, and exn:fail:snooze:revision if the revision in struct does not match the revision number stored in the database.

(send a-snooze create-table entity) → void?

entity : entity?

Issues an SQL "CREATE TABLE" statement to create a database table for the supplied entity. Raises exn:fail:snooze if the table already exists or cannot be created.

(send a-snooze drop-table entity) → void?

entity : entity?

Issues an SQL "DROP TABLE" statement to delete the database table for the supplied entity. Does nothing if no table is present. Raises exn:fail:snooze if the table exists and cannot be dropped.

(send a-snooze table-names) → (listof symbol?)

Returns a list of the names of the tables defined in the database. If the database supports multiple namespaces, only tables in the standard or public namespace are returned.

(send a-snooze table-exists? table) → boolean?

table : (U entity? symbol?)

Checks to see if a table exists for the supplied entity or table name. Note that if the argument is a symbol it refers to a table name rather than an entity name.

5.2 Procedural interface

The define-snooze-interface macro is provided for convenience, to convert the object oriented database interface above into a more Schemely procedural interface.

(define-snooze-interface snooze-object)

(define-snooze-interface prefix-id snooze-object)

Defines a procedure for each of the methods in snooze<%>, such that each procedure calls the matching method in the snooze-object. The optional prefix-id can be used to add a prefix to each identifier to avoid naming collisions. For example:

(define db1-snooze

(make-snooze (sqlite:make-database "db1.sqlite")))

(define db2-snooze

(make-snooze (sqlite:make-database "db2.sqlite")))

(define-snooze-interface db1-snooze)

(define-snooze-interface alt: db2-snooze)

; Connect to "db1.sqlite" and perform some operations:

(call-with-connection

(lambda ()

; ... ))

; Connect to "db2.sqlite" and perform some operations:

(alt:call-with-connection

(lambda ()

; ... ))

(snooze-interface-out)

(snooze-interface-out prefix-id)

Expands into a set of provide forms for the functional Snooze interface defined by (define-snooze-interface) or (define-snooze-interface prefix-id).

5.3 Saving and deleting structures

When a persistent structure is first created, it has no corresponding record in the database. A record is saved (inserted or updated) with a call to the save! method or procedure, and deleted with a call to the delete! method or procedure.

save! updates the database record for struct and sets its id and revision appropriately. It returns the mutated struct to allow the programmer to chain the call with calls to other procedures.

If struct has an id of #f, save! assumes that no corresponding database record exists. It sets the revision field to 0 and uses an SQL INSERT statement to insert a new database record. Finally, save! sets the id to the primary key of the record and returns the mutated struct.

If struct already has an integer id when save! is called, the behaviour is different. First, save! checks the database to make sure the stored revision number matches the revision number in struct. It then increments the revision and uses an SQL UPDATE statement to update the database record with the new information. Finally, save! returns the mutated struct.

save! raises exn:fail:snooze:revision if a revision number check fails. This is useful because it allows the programmer to detect and avoid concurrent updates.

Finally, delete! uses an SQL DELETE statement to delete the corresponding record from the database. delete! sets the id and revision of the struct to #f and then returns it.

The lifecycle of the id and revision fields is summarised in the code snippet below:

Note: The repeated calls to call-with-connection are only necessary to get Scribble to print the results of each statement: normal application programs should be able to all of this interaction with a single connection.

Extra note: The printed values in this example may not be correct. This is because of the way Scribble renders example blocks. A solution for this issue is being worked on.

; Define a new persistent struct type:

> (define-persistent-struct person       ([name type:string]))

; Create a DB table for this new type:

> (call-with-connection      (lambda ()        (create-table entity:person)))

; Create a struct: initially it has no corresponding DB record:

> (define person (make-person "Dave"))

> person

#(struct:person #f 1 "Noel")

; Insert a DB record and set the struct's ID and revision:

> (call-with-connection      (lambda ()        (save! person)))

#(struct:person #f 1 "Noel")

; Update the record and increment the revision:

> (set-person-name! person "Noel")

> (call-with-connection      (lambda ()        (save! person)))

#(struct:person #f 1 "Noel")

; Deleted the record and set the ID and revision to #f:

> (call-with-connection      (lambda ()        (delete! person)))

#(struct:person #f 1 "Noel")

; Finally, delete the DB table to clean up:

> (call-with-connection      (lambda ()        (drop-table entity:person)))