### 6.2 Relational Infrastructure ¶

#### 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)`.

#### 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 `require`ing 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.