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)

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

; snooze%

;

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

(define mydata

(make-snooze (make-database (build-path "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

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 db1-snooze

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

(define db2-snooze

(make-snooze (sqlite:make-database (build-path "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 ()

; ... ))

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.

; 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 person)))

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

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

> person

#<entity:person>

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

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

Could not insert database record for #(struct:person #f 0

Dave): SQLite Error: no such table: person

; Update the record and increment the revision:

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

set-person-name!: expects type <struct:person> as 1st

argument, given: #<entity:person>; other arguments were:

"Noel"

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

Could not insert database record for #(struct:person #f 0

Dave): SQLite Error: no such table: person

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

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

Cannot delete a struct that has not been saved to the

database: #(struct:person #f #f Dave)

; Finally, delete the DB table to clean up:

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