Relational Infrastructure (slib)

6.2 Relational Infrastructure

Base Table
Catalog Representation
Relational Database Objects
Database Operations

6.2.1 Base Table

A base-table is the primitive database layer upon which SLIB relational databases are built. At the minimum, it must support the types integer, symbol, string, and boolean. The base-table may restrict the size of integers, symbols, and strings it supports.

A base table implementation is available as the value of the identifier naming it (eg. alist-table) after requiring the symbol of that name.

Feature: alist-table ¶

(require 'alist-table)

Association-list base tables support all Scheme types and are suitable for small databases. In order to be retrieved after being written to a file, the data stored should include only objects which are readable and writeable in the Scheme implementation.

The alist-table base-table implementation is included in the SLIB distribution.

WB is a B-tree database package with SCM interfaces. Being disk-based, WB databases readily store and access hundreds of megabytes of data. WB comes with two base-table embeddings.

Feature: wb-table ¶

(require 'wb-table)

wb-table supports scheme expressions for keys and values whose text representations are less than 255 characters in length. See wb-table in WB.

Feature: rwb-isam ¶

(require 'rwb-isam)

rwb-isam is a sophisticated base-table implementation built on WB and SCM which uses binary numerical formats for key and non-key fields. It supports IEEE floating-point and fixed-precision integer keys with the correct numerical collation order.

This rest of this section documents the interface for a base table implementation from which the Relational Database package constructs a Relational system. It will be of interest primarily to those wishing to port or write new base-table implementations.

Variable: *base-table-implementations* ¶: To support automatic dispatch for open-database, each base-table module adds an association to *base-table-implementations* when loaded. This association is the list of the base-table symbol and the value returned by (make-relational-system base-table).

The Base
Base Tables
Base Field Types
Composite Keys
Base Record Operations
Match Keys
Aggregate Base Operations
Base ISAM Operations

6.2.1.1 The Base

All of these functions are accessed through a single procedure by calling that procedure with the symbol name of the operation. A procedure will be returned if that operation is supported and #f otherwise. For example:

(require 'alist-table)
(define my-base (alist-table 'make-base))
my-base         ⇒ *a procedure*
(define foo (alist-table 'foo))
foo             ⇒ #f

Operation on base-table: make-base filename key-dimension column-types ¶

Returns a new, open, low-level database (collection of tables) associated with filename. This returned database has an empty table associated with catalog-id. The positive integer key-dimension is the number of keys composed to make a primary-key for the catalog table. The list of symbols column-types describes the types of each column for that table. If the database cannot be created as specified, #f is returned.

Calling the close-base method on this database and possibly other operations will cause filename to be written to. If filename is #f a temporary, non-disk based database will be created if such can be supported by the base table implelentation.

Operation on base-table: open-base filename mutable ¶

Returns an open low-level database associated with filename. If mutable is #t, this database will have methods capable of effecting change to the database. If mutable is #f, only methods for inquiring the database will be available. If the database cannot be opened as specified #f is returned.

Calling the close-base (and possibly other) method on a mutable database will cause filename to be written to.

Operation on base-table: write-base lldb filename ¶: Causes the low-level database lldb to be written to filename. If the write is successful, also causes lldb to henceforth be associated with filename. Calling the close-database (and possibly other) method on lldb may cause filename to be written to. If filename is #f this database will be changed to a temporary, non-disk based database if such can be supported by the underlying base table implelentation. If the operations completed successfully, #t is returned. Otherwise, #f is returned.

Operation on base-table: sync-base lldb ¶: Causes the file associated with the low-level database lldb to be updated to reflect its current state. If the associated filename is #f, no action is taken and #f is returned. If this operation completes successfully, #t is returned. Otherwise, #f is returned.

Operation on base-table: close-base lldb ¶: Causes the low-level database lldb to be written to its associated file (if any). If the write is successful, subsequent operations to lldb will signal an error. If the operations complete successfully, #t is returned. Otherwise, #f is returned.

6.2.1.2 Base Tables

Operation on base-table: make-table lldb key-dimension column-types ¶: Returns the ordinal base-id for a new base table, otherwise returns #f. The base table can then be opened using (open-table lldb base-id). The positive integer key-dimension is the number of keys composed to make a primary-key for this table. The list of symbols column-types describes the types of each column.

Operation on base-table: open-table lldb base-id key-dimension column-types ¶

Returns a handle for an existing base table in the low-level database lldb if that table exists and can be opened in the mode indicated by mutable, otherwise returns #f.

As with make-table, the positive integer key-dimension is the number of keys composed to make a primary-key for this table. The list of symbols column-types describes the types of each column.

Operation on base-table: kill-table lldb base-id key-dimension column-types ¶: Returns #t if the base table associated with base-id was removed from the low level database lldb, and #f otherwise.

Operation on base-table: catalog-id ¶: A constant base-id ordinal suitable for passing as a parameter to open-table. catalog-id will be used as the base table for the system catalog.

6.2.1.3 Base Field Types

Operation on base-table: supported-type? symbol ¶: Returns #t if symbol names a type allowed as a column value by the implementation, and #f otherwise. At a minimum, an implementation must support the types integer, ordinal, symbol, string, and boolean.

Operation on base-table: supported-key-type? symbol ¶: Returns #t if symbol names a type allowed as a key value by the implementation, and #f otherwise. At a minimum, an implementation must support the types ordinal, and symbol.

An ordinal is an exact positive integer. The other types are standard Scheme.

6.2.1.4 Composite Keys

Operation on base-table: make-keyifier-1 type ¶

Returns a procedure which accepts a single argument which must be of type type. This returned procedure returns an object suitable for being a key argument in the functions whose descriptions follow.

Any 2 arguments of the supported type passed to the returned function which are not equal? must result in returned values which are not equal?.

Operation on base-table: make-list-keyifier key-dimension types ¶

The list of symbols types must have at least key-dimension elements. Returns a procedure which accepts a list of length key-dimension and whose types must corresopond to the types named by types. This returned procedure combines the elements of its list argument into an object suitable for being a key argument in the functions whose descriptions follow.

Any 2 lists of supported types (which must at least include symbols and non-negative integers) passed to the returned function which are not equal? must result in returned values which are not equal?.

Operation on base-table: make-key-extractor key-dimension types column-number ¶: Returns a procedure which accepts objects produced by application of the result of (make-list-keyifier key-dimension types). This procedure returns a key which is equal? to the column-numberth element of the list which was passed to create composite-key. The list types must have at least key-dimension elements.

Operation on base-table: make-key->list key-dimension types ¶: Returns a procedure which accepts objects produced by application of the result of (make-list-keyifier key-dimension types). This procedure returns a list of keys which are elementwise equal? to the list which was passed to create composite-key.

6.2.1.5 Base Record Operations

In the following functions, the key argument can always be assumed to be the value returned by a call to a keyify routine.

Operation on base-table: present? handle key ¶: Returns a non-#f value if there is a row associated with key in the table opened in handle and #f otherwise.

Operation on base-table: make-getter key-dimension types ¶: Returns a procedure which takes arguments handle and key. This procedure returns a list of the non-primary values of the relation (in the base table opened in handle) whose primary key is key if it exists, and #f otherwise.

make-getter-1 is a new operation. The relational-database module works with older base-table implementations by using make-getter.

Operation on base-table: make-getter-1 key-dimension types index ¶

Returns a procedure which takes arguments handle and key. This procedure returns the value of the indexth field (in the base table opened in handle) whose primary key is key if it exists, and #f otherwise.

index must be larger than key-dimension.

Operation on base-table: make-putter key-dimension types ¶: Returns a procedure which takes arguments handle and key and value-list. This procedure associates the primary key key with the values in value-list (in the base table opened in handle) and returns an unspecified value.

Operation on base-table: delete handle key ¶: Removes the row associated with key from the table opened in handle. An unspecified value is returned.

6.2.1.6 Match Keys

A match-keys argument is a list of length equal to the number of primary keys. The match-keys restrict the actions of the table command to those records whose primary keys all satisfy the corresponding element of the match-keys list. The elements and their actions are:

#f

The false value matches any key in the corresponding position.

an object of type procedure

This procedure must take a single argument, the key in the corresponding position. Any key for which the procedure returns a non-false value is a match; Any key for which the procedure returns a #f is not.

other values

Any other value matches only those keys equal? to it.

6.2.1.7 Aggregate Base Operations

The key-dimension and column-types arguments are needed to decode the composite-keys for matching with match-keys.

Operation on base-table: delete* handle key-dimension column-types match-keys ¶: Removes all rows which satisfy match-keys from the table opened in handle. An unspecified value is returned.

Operation on base-table: for-each-key handle procedure key-dimension column-types match-keys ¶: Calls procedure once with each key in the table opened in handle which satisfy match-keys in an unspecified order. An unspecified value is returned.

Operation on base-table: map-key handle procedure key-dimension column-types match-keys ¶: Returns a list of the values returned by calling procedure once with each key in the table opened in handle which satisfy match-keys in an unspecified order.

6.2.1.8 Base ISAM Operations

These operations are optional for a Base-Table implementation.

Operation on base-table: ordered-for-each-key handle procedure key-dimension column-types match-keys ¶: Calls procedure once with each key in the table opened in handle which satisfy match-keys in the natural order for the types of the primary key fields of that table. An unspecified value is returned.

Operation on base-table: make-nexter handle key-dimension column-types index ¶: Returns a procedure of arguments key1 key2 … which returns the key-list identifying the lowest record higher than key1 key2 … which is stored in the base-table and which differs in column index or a lower indexed key; or false if no higher record is present.

Operation on base-table: make-prever handle key-dimension column-types index ¶: Returns a procedure of arguments key1 key2 … which returns the key-list identifying the highest record less than key1 key2 … which is stored in the base-table and which differs in column index or a lower indexed key; or false if no higher record is present.

6.2.2 Catalog Representation

Each database (in an implementation) has a system catalog which describes all the user accessible tables in that database (including itself).

The system catalog base table has the following fields. PRI indicates a primary key for that table.

PRI table-name
    column-limit            the highest column number
    coltab-name             descriptor table name
    bastab-id               data base table identifier
    user-integrity-rule
    view-procedure          A scheme thunk which, when called,
                            produces a handle for the view.  coltab
                            and bastab are specified if and only if
                            view-procedure is not.

Descriptors for base tables (not views) are tables (pointed to by system catalog). Descriptor (base) tables have the fields:

PRI column-number           sequential integers from 1
    primary-key?            boolean TRUE for primary key components
    column-name
    column-integrity-rule
    domain-name

A primary key is any column marked as primary-key? in the corresponding descriptor table. All the primary-key? columns must have lower column numbers than any non-primary-key? columns. Every table must have at least one primary key. Primary keys must be sufficient to distinguish all rows from each other in the table. All of the system defined tables have a single primary key.

A domain is a category describing the allowable values to occur in a column. It is described by a (base) table with the fields:

PRI domain-name
    foreign-table
    domain-integrity-rule
    type-id
    type-param

The type-id field value is a symbol. This symbol may be used by the underlying base table implementation in storing that field.

If the foreign-table field is non-#f then that field names a table from the catalog. The values for that domain must match a primary key of the table referenced by the type-param (or #f, if allowed). This package currently does not support composite foreign-keys.

The types for which support is planned are:

    atom
    symbol
    string                  [<length>]
    number                  [<base>]
    money                   <currency>
    date-time
    boolean

    foreign-key             <table-name>
    expression
    virtual                 <expression>

6.2.3 Relational Database Objects

This object-oriented interface is deprecated for typical database applications; Using Databases provides an application programmer interface which is easier to understand and use.

Function: make-relational-system base-table-implementation ¶

Returns a procedure implementing a relational database using the base-table-implementation.

All of the operations of a base table implementation are accessed through a procedure defined by requireing that implementation. Similarly, all of the operations of the relational database implementation are accessed through the procedure returned by make-relational-system. For instance, a new relational database could be created from the procedure returned by make-relational-system by:

(require 'alist-table)
(define relational-alist-system
        (make-relational-system alist-table))
(define create-alist-database
        (relational-alist-system 'create-database))
(define my-database
        (create-alist-database "mydata.db"))

What follows are the descriptions of the methods available from relational system returned by a call to make-relational-system.

Operation on relational-system: create-database filename ¶: Returns an open, nearly empty relational database associated with filename. The only tables defined are the system catalog and domain table. Calling the close-database method on this database and possibly other operations will cause filename to be written to. If filename is #f a temporary, non-disk based database will be created if such can be supported by the underlying base table implelentation. If the database cannot be created as specified #f is returned. For the fields and layout of descriptor tables, Catalog Representation

Operation on relational-system: open-database filename mutable? ¶: Returns an open relational database associated with filename. If mutable? is #t, this database will have methods capable of effecting change to the database. If mutable? is #f, only methods for inquiring the database will be available. Calling the close-database (and possibly other) method on a mutable? database will cause filename to be written to. If the database cannot be opened as specified #f is returned.

6.2.4 Database Operations

This object-oriented interface is deprecated for typical database applications; Using Databases provides an application programmer interface which is easier to understand and use.

These are the descriptions of the methods available from an open relational database. A method is retrieved from a database by calling the database with the symbol name of the operation. For example:

(define my-database
        (create-alist-database "mydata.db"))
(define telephone-table-desc
        ((my-database 'create-table) 'telephone-table-desc))

Operation on relational-database: close-database ¶: Causes the relational database to be written to its associated file (if any). If the write is successful, subsequent operations to this database will signal an error. If the operations completed successfully, #t is returned. Otherwise, #f is returned.

Operation on relational-database: write-database filename ¶: Causes the relational database to be written to filename. If the write is successful, also causes the database to henceforth be associated with filename. Calling the close-database (and possibly other) method on this database will cause filename to be written to. If filename is #f this database will be changed to a temporary, non-disk based database if such can be supported by the underlying base table implelentation. If the operations completed successfully, #t is returned. Otherwise, #f is returned.

Operation on relational-database: sync-database ¶: Causes any pending updates to the database file to be written out. If the operations completed successfully, #t is returned. Otherwise, #f is returned.

Operation on relational-database: solidify-database ¶: Causes any pending updates to the database file to be written out. If the writes completed successfully, then the database is changed to be immutable and #t is returned. Otherwise, #f is returned.

Operation on relational-database: table-exists? table-name ¶: Returns #t if table-name exists in the system catalog, otherwise returns #f.

Operation on relational-database: open-table table-name mutable? ¶: Returns a methods procedure for an existing relational table in this database if it exists and can be opened in the mode indicated by mutable?, otherwise returns #f.

These methods will be present only in mutable databases.

Operation on relational-database: delete-table table-name ¶: Removes and returns the table-name row from the system catalog if the table or view associated with table-name gets removed from the database, and #f otherwise.

Operation on relational-database: create-table table-desc-name ¶: Returns a methods procedure for a new (open) relational table for describing the columns of a new base table in this database, otherwise returns #f. For the fields and layout of descriptor tables, See Catalog Representation.

Operation on relational-database: create-table table-name table-desc-name ¶: Returns a methods procedure for a new (open) relational table with columns as described by table-desc-name, otherwise returns #f.

Operation on relational-database: create-view ?? ¶
Operation on relational-database: project-table ?? ¶
Operation on relational-database: restrict-table ?? ¶
Operation on relational-database: cart-prod-tables ?? ¶: Not yet implemented.