Documentation

The FETCH Family

One of the most common and natural operations in all database applications is copying a value from one table to another. The textbook example is an CART_ITEMS table, which contains a PRICE value. At some point somewhere the programmer has written a bit of code that looks up the price in the ITEMS table and writes it to the CART_ITEMS table.

Using Andromeda you never have to code these operations. You define these operations, which we call FETCH operations, directly into the database definition, and after you build your database the value will be calculated automatically whenever anybody inserts or update the CART_ITEMS table.

By defining FETCH operations in your code, you gain the advantage that is does not matter what the privelege level of the user is or how they are connecting to the database. Even an admin user connecting directly to the database cannot override the value with a corrupt one.

The Basic FETCH

We will start with an ITEMS table, with a primary key SKU that contains a description and price for each item:

column sku:
    type_id: vchar
    colprec: 15
    description: SKU
    
table items:
    # assume we defined this module above somewhere
    module: inventory
    uisort: 100
    description: Items File
    
    column sku:
        primary_key: Y
        uisearch: Y
    column description:
        uisearch: Y
    column price:
        uisearch: Y
        description: Item Price

Now we want an CART_ITEMS table that also has a price column, and we want to FETCH the price into the cart. Here is how we do that:

table cart_items:
    # ...skip some details...
    
    # We begin with a foreign key to the items table.
    # This will put the column SKU into the cart
    foreign_key items:
    
    # Now we add the price and give it an automation
    column price:
        automation_id: fetch
        auto_formula: items.price

The FETCH will fire whenever a valid value of SKU is entered or changed on either an INSERT or UPDATE to CART_ITEMS.

There are two properties that work together here:

• The automation_id value of fetch defines the operation to perform
• The auto_formula tells us what table and column to go fetch.

Alternative Minimum Syntax

All automations support a shortcut syntax where you can specify the automation in one line. It looks like this:

table cart_items:
    # ...skip some details...
    
    column price:
        auto: fetch,items.price

The FETCHDEF Variation

The automation FETCHDEF is similar to FETCH, but it only operates if the user does not supply a value. So if the user provides a value, that value wins, and if the user does not supply a value, one is fetched.

Unlike the FETCH automation, the FETCHDEF operates only at insert, and only once. After that the user can override the value at any time.

table cart:
    module: ordering
    description: Cart Orders
    uisort: 100
    
    # An order requires a foreign key to customers
    foreign_key customers:
    
    # These are conventional fetches, the user cannot
    # override the values.
    column discount:
        auto: fetch,customers.discount
    column description:
        auto: fetch,customers.description
        
    # These values the user can override
    column add1:
        auto: fetchdef,customers.add1
    column add2:
        auto: fetchdef,customers.add2
    column city:
        auto: fetchdef,customers.city
    column state:
        auto: fetchdef,customers.state
    column zip9:
        auto: fetchdef,customers.zip9
        

The DISTRIBUTE Variation

The automation DISTRIBUTE variation works exactly like a FETCH but with a very important extra operation. If you change the value in the parent table, it will be distributed down to all matching rows in the child table.

The syntax for DISTRIBUTE is exactly the same as FETCH and FETCHDEF.