SQLSourcery
1 Motivation
2 Database Connection
sourcery-db
3 Sourcery Structures
3.1 Structure Definition
sourcery-struct
struct-name?
struct-name-create
struct-name-update
struct-name-field
3.2 Loading Structures
sourcery-load
3.3 Deleting Structures
sourcery-delete
sourcery-filter-delete
4 Testing
4.1 Testing Philosophy
4.2 Testing Variables
declare-test-vars
set-test-var!
clear-test-vars!
4.3 Testing Actions
action
define-action
action-compose
define-composed-action
4.4 Testing Cleanup
clear-sourcery-structs
4.5 Sourcery Test Suites
sourcery-test-suite
run-sourcery-tests
5 Programming with SQLSourcery
5.1 SQLSourcery, Side Effects, and Mutation
5.2 Under The Hood
5.3 Dead References
7.2

SQLSourcery

Adrian Kant and Taylor Murphy

Database backed structs for functional programmers. A SQLSourcery programmer is a sourcerer.

Github Repo

 (require sql-sourcery) package: sql-sourcery

1 Motivation

Racket programs require lots of boilerplate for any sort of basic I/O persistency, and getting this I/O to fit into a functional style causes many additional problems. SQLSourcery attempts to allow a sourcerer to easily spin up state persistency of their structures that can fit into their coding paradigms with minimal adaptation.

2 Database Connection

SQLSourcery programs must first connect to a SQLite database. It is typical to set the database at the top of a file or module. Test modules typically use a different database than programs. When a database is changed, all loaded sourcery structures currently in existence are subject to error. Any operations besides sourcery structure declarations will throw an error if a database is not set.

procedure

(sourcery-db db-file-path)  void

  db-file-path : string?
Creates a connection to a SQLite database at the given path location to be used for all SQLSourcery operations until the database is changed. Ensure tables exist for all previously defined structures in the program.

Example:
(sourcery-db "spells.db")

3 Sourcery Structures

3.1 Structure Definition

A sourcery-struct acts as a typical structure with added database persistence.

syntax

(sourcery-struct struct-name
                 [(field-name type) (field-name type) ...])
 
  struct-name : id?
  field-name : id?
  type : acceptable-struct-type?
  type = STRING
  | INTEGER
  | BOOLEAN

Example:
(sourcery-struct spell [(name STRING) (power INTEGER) (deadly? BOOLEAN)])

This will create a new structure source or connect to an existing structure source with an identical definition. If an existing sourcery-struct deinition with the same name already exists in the database but the definition is not identical, an error will be raised.

No field name identifier can start with the characters "__", nor can they be one of the following reserved fields:

A sourcery-struct definition will create the following functions:

procedure

(struct-name? x)  boolean?

  x : any?
A predicate for a structure. Will return true only when its argument is of the type struct-name.

procedure

(struct-name-create field ...)  struct-name?

  field : any?
Creates a new instance of the structure in both the program and the database. The number of arguments and the type of each argument must match the sourcery-struct definition.

Example:
(spell-create "Summon Bees" 100 #true)
Result:

(spell "Summon Bees" 100 #true)

procedure

(struct-name-update ref-struct field ...)  struct-name?

  ref-struct : struct-name?
  field : any?
Updates the given structure in the database to have the given field values and return the newly created structure in the program. All references to the given structure will also be changed.

Example:
(define bees     (spell-create "Summon Bees" 100 #true))
(define bees-new (spell-update bees "Summon Bees" 200 #true))

> (spell-power bees-new)

200

> (spell-power bees)

200

Here both bees and new-bees reference the same spell.

procedure

(struct-name-field s)  any?

  s : struct-name?
Creates accessors for each field are generated as the racket struct construct does.

Examples:
> (spell-name bees)

"Summon Bees"

> (spell-power bees)

200

> (spell-deadly? bees)

#t

3.2 Loading Structures

procedure

(sourcery-load struct-name)  (listof struct-name)

  struct-name : id?
Return a list of all of the structures currently in existence in the current database.

Example:
(spell-create "Expecto" 10 #false)
(spell-create "Patronum" 100 #true)
(sourcery-load spell)

Result:

(list (spell "Expecto" 10 #false) (spell "Patronum" 100 #true))

3.3 Deleting Structures

procedure

(sourcery-delete sourcery-struct)  void

  sourcery-struct : sourcery-struct?
Delete the given sourcery-struct from the database. Any existing references to the sourcery-struct will become dead references.

Example:
(define bees (spell-create "Summon Bees" 100 #true))
(sourcery-delete bees)

Attempting to operate on bees will result in an error.

Attempting to display bees will result in:

(spell 'dead-reference)

procedure

(sourcery-filter-delete predicate refs)

  (list-of sourcery-struct)
  predicate : (-> sourcery-struct? boolean?)
  refs : (list-of sourcery-struct)
Return all sourcery-struct’s which match the predicate and sourcery-delete the structures that fail the predicate.

Example:
(spell-create "Expecto" 10 #false)
(spell-create "Patronum" 100 #true)
(sourcery-filter-delete (λ (s) (spell-deadly? s)) (sourcery-load spell))

Result:

(list (spell "Patronum" 100 #true))

The example above deletes all spells that are not deadly.

4 Testing

SQLSourcery programs must test mutation and be able to easily write setup and teardown. While possible with racket, the library can be combersome to use. SQLSourcery comes with a testing library.

4.1 Testing Philosophy

There are a few main concepts that come with SQLSourcery’s testing library:

Sourcery Test Suites uses the racket test-suite and adds the tests to a module level global lists of tests that can be run with a single command. At its core, these are most useful for their before and after clauses, which take in actions.

In order to take advantage of before and after clauses in test suites, varaibles must be avaiable in the before, during, and after stages, as well as being able to be modified. While this can be done with set!, the testing library allows easy declaration, setting, and clearing of testing varaibles through a simple API that clearly signifies their status for testing and will prevent accidental mutation of variables not used in testing.

Before and after clauses in test-suite are thunks that return void. To simplify writing these thunks, the testing library introduces the concept of actions that can perform multiple operations at once and can be composed together in an intuitive order for the context of testing.

Testing will use the currently set sourcery-db. It is customary to test by using a test module and using a testing database via a call to sourcery-db at the start of the module

4.2 Testing Variables

syntax

(declare-test-vars [var-id id?])

Define the given ids to be test variables with the initial value of #f using the racket define.

Example:
> (declare-test-vars a b c)
> a

#f

> b

#f

> c

#f

procedure

(set-test-var! var-id value)  void

  var-id : id?
  value : any?
Set the given test varaible to the given value. Will error if the given id is not a test varaible.

Example:
> (define d #f)
> a

#f

> b

#f

> c

#f

> d

#f

> (set-test-var! a 1)
> a

1

> (set-test-var! d 1)

set-test-var!: Invalid test variable: d

procedure

(clear-test-vars! var-id ...)  void

  var-id : id?
Set the given test variable ids to #false. Error if given an invalid test id. If error occurs, all ids listed before the invalid id will be set.

Example:
> (set-test-var! a 2)
> (set-test-var! b 3)
> (set-test-var! c 3)
> a

2

> b

3

> c

3

> (clear-test-vars! a b)
> a

#f

> b

#f

> c

3

4.3 Testing Actions

procedure

(action expr)  thunk?

  expr : ...
Create an action (thunk) that runs all expressions inside a begin and then returns void.

Example using set!:
> (define x 1)
> ((action (set! x 2)))
> x

2

Example using test variables:
> a

#f

> ((action (set-test-var! a 3)))
> a

3

syntax

(define-action [name id?] [expr any?] ...)

Define an action with the given name.

Shortcut for:

(define name (action expr ...))

Example using set!:
> (define x 1)
> (define-action action-1 (set! x 2))
> (action-1)
> x

2

Example using test variables:
> a

3

> (define-action action-2 (set-test-var! a 4))
> (action-2)
> a

4

procedure

(action-compose action ...)  thunk?

  action : thunk?
Create a single action that executes the given actions from left to right.

Example:
> (declare-test-vars v1 v2)
> (define-action v-action-1 (set-test-var! v1 1))
> (define-action v-action-2 (set-test-var! v2 2))
> (define-action v-action-3 (set-test-var! v1 3))
> (define v-action-4 (action-compose v-action-1 v-action-2 v-action-3))
> v1

#f

> v2

#f

> (v-action-4)
> v1

3

> v2

2

syntax

(define-composed-action [name id?] [[action action?] ...])

Define an action with the given name by composing the given actions.

:Example
> (define-composed-action v-action-4-easier [v-action-1 v-action-2 v-action-3])
> v1

#f

> v2

#f

> (v-action-4-easier)
> v1

3

> v2

2

4.4 Testing Cleanup

procedure

(clear-sourcery-structs struct-name ...)  void

  struct-name : struct-name?
sourcery-delete all existing sourcery-structs of the type of the given names. Will possibly create dead references.

:Example
> (define alakazam (spell-create "Alakazam" 100 #false))
> (clear-sourcery-structs spell)
> (sourcery-load spell)

'()

Accessesing alakazam will result in:

(spell 'dead-reference)

4.5 Sourcery Test Suites

procedure

(sourcery-test-suite name-expr    
  maybe-before    
  maybe-after    
  test ...)  void
  name-expr : string?
  maybe-before : before-action
  maybe-after : after-action
  test : test?
Acts the same as test-suite except that it will add the suite to the global tests.

Example:
(sourcery-test-suite
 "A sourcery-test-suite using fictional actions"
 #:before su-create-all
 #:after  td-complete
 (check-equal? (spell-name (first sourcery-load-results)) "Summon Bees")
 (check-equal? (spell-name (second sourcery-load-results)) "Create Pig Tail On A Dursley"))

procedure

(run-sourcery-tests test-db-path    
  end-db-path)  void
  test-db-path : string?
  end-db-path : string?
Set the database using sourcery-db to the given test-db-path and run all of the sourcery-test-suites defined before the call using run-tests , setting the database using sourcery-db to the given end-db-path after completion.

5 Programming with SQLSourcery

5.1 SQLSourcery, Side Effects, and Mutation

By nature, SQLSourcery is at odds with one of the often noted rules and advantages of functional programming - that mutation and side effects should be avoided when possible.

However, this does not mean that programmers should be forced to change the way they write their functional code, only that a few additional considerations must be made in order to get the advantages of database backing. This section is dedicated to explaining how programmers can think about these issues while still writing idiomatic code.

5.2 Under The Hood

At its core, SQLSourcery simply translates all operations with structures into SQL queries. It then introduces unique numeric idenifiers that are tied to each structure. Instances of structures are in fact simply references to these identifiers. Programming with sourcery structures is thus working with references, not values. While SQLSourcery allows for multiple references to the same value, it is designed so that there is no need use the feature, and one can write fully functional code, which then tracks its state with the connected database.

5.3 Dead References

Since sourcery structures at their core are references, and the values that a reference points to can be modified or deleted, it is then possible to have an invalid or "dead" reference. When sourcery-delete is used, any remaining references then become dead references. If a sourcerer chooses to use multiple references to the same value, it is the responsibility of the sourcerer to keep track of the status of references.