5Connecting 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:

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.

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.

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.

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.2Procedural 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-interfacesnooze-object)

(define-snooze-interfaceprefix-idsnooze-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:

(definedb1-snooze

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

(definedb2-snooze

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

(define-snooze-interfacedb1-snooze)

(define-snooze-interfacealt: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-outprefix-id)

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

5.3Saving 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.