Command: dbstructure

Notes

Once defined, the dbstructure <name> may be used as a command to create an object of the dbstructure type.

When such an object is created, its values are retrieved from the database via search criteria (its "key value"). This key value comprises two string values used to locate one or more rows in the database. The first string is used as an SQL SELECT statement WHERE clause. The second is optional and may be used to provide supplementary information for the select, e.g.

object.key = "name = 'FRED'"

The first element of key may contain a dynamic parameter, indicated by a $, which is evaluated at mapping time. It gets replaced by the dbstructure object identifier name, or by a value of a parent structure member if the -mem switch was specified.

If the key value is undefined, and the dbstructure is part of a more complex structure, and the -mem switch was specified, the mapping selection criteria are derived entirely from the appropriate value in the parent structure.

Until the key value is defined, the dbstructure object is not linked to the database. Once the key value has been defined, the dbstructure is marked as "refreshable" which means that as soon as any dbstructure member values are referenced the whole dbstructure is fetched from the database. Once fetched, the dbstructure is not refreshed from the database again unless it gets marked as refreshable whenever one of the following happens:

1. The key changes value.

2. Following a dbupdate/dbdelete command.

The dbstructure definition must have application scope, but objects of the dbstructure type can have any scope.

Examples

Define and use a dbstructure from a fictional customer table in a stores database:

# Open the database
    database stores, -prop=oracle, -name='scott/tiger'
 
# Define a dbstructure into the customer table
# The table has 3 columns we will be interested in:
#  lname, fname and phone
    dbstructure ~app.cust_t,stores,'customer'
 
# Create an object of this class and set its key value
    ~app.cust_t person
    person.key = 'lname = "Miller"'
 
# Access the customer phone number
# Since this is the first access, the dbstructure values
#   will be loaded from the database.
# The equivalent of an SQL enquiry is used internally as follows:
# SELECT * FROM customer WHERE lname="Miller"
    tell person.phone
 
# Create an array of objects from multiple rows in the
#   database ordered alphabetically
    ~app.cust_t cs[]
# SQL enquiry: where state = "CA" order by lname
    cs.key = <'state = "CA"', 'order by lname'>
 
# Find out how many of them are there:
    tell <'There are', cs.alength, 'customers'>
 
# Display the names and phone numbers of the first and
#   last customers
    tell <’First:’,cs[1].fname,cs[1].lname, cs[1].phone>
    tell <’Last:’, cs[*].fname, cs[*].lname, cs[*].phone>

Define a dbstructure from selective columns, mapping the names to aliases

string names[] = < \
    'lname(surname)', \
    'fname(christian_name)', \
    'phone(tel)' >
dbstructure ~app.telnos,stores,'customer',names

The new dbstructure would have the resulting definition:

~app.telnos {
    string surname
    string christian_name
    string tel
}

See Also