7.4 Retrieving and displaying rows

Once we have a keylist describing the tuple trees that satisfy our query, we can determine all matching full or partial rows with qddb_rows. There are many applications of qddb_rows, but here we will discuss only the most common. Typically, you want to do one of the following: (1) produce rows (possibly sorted) for viewing purposes, (2) produce rows for update purposes, and (3) produce rows to determine whether a tuple meets some criterion, perhaps for deletion purposes. This section discusses these.

7.4.1 Producing rows for display

The most common reason to produce rows is for viewing purposes. A user has some query and wants to see all the matching rows in some predetermined order. Usually, you want to provide a sorted list of matching rows, displaying only those columns (attributes) that are interesting. You might want to arrange the columns in a different order than they are presented in the Schema.

Example:

Suppose you have the relation described in Section 7.2.4. You want to produce a report containing the list of everyone who owes you money, how much they owe, and their phone numbers. You want the list in a descending order based on the Amount attribute. The following code accomplishes this task:

    #!/usr/local/qddb/bin/qtclsh
    set s [qddb_schema open Debtors]
    set k [qddb_search $s -prunebyattr Amount number_range 0.01 -]
    set r [qddb_rows select -attrs Amount -suppress on \
        -print {Amount Name.First Name.Last} $k]
    qddb_keylist delete $k
    foreach i $r {lappend sorted [lindex $i 1]}
    set sorted [qddb_rows sort Amount $sorted]
    foreach i $sorted {
        set t [qddb_rows tuplename $i]
        set v [qddb_view define $t {
            {Amount amt}
            {Name.First fn}
            {Name.Last ln}
            {Phones.Desc ph_desc}
            {Phones.Area ph_area}
            {Phones.Number ph_num}
        }]
        puts [format "%30s %6.2f (%s) %s-%s" \
            "$ln,$fn" $amt $ph_desc $ph_area $ph_num]
        set max [qddb_instance maxnum $v Phones]
        if {$max > 1} {
            for {set i 2} {$i <= $max} {incr i} {
                qddb_instance switch $v Phones $i
                puts [format "%30s %6s (%s) %s-%s" \
                    " "  " " $ph_desc $ph_area $ph_num]
            }
        }
        qddb_tuple delete $t
    }
    qddb_schema close $s

The rows are sorted immediately after calling qddb_rows select. Each row is processed in order. If a record has multiple phone numbers, then one extra line for each phone number is printed immediately after the initial row.

7.4.2 Updating records

A common database task is to update all records containing rows that match some query. Section 7.2.4 provides one example; here's another.

Example:

Using our Debtors relation, suppose you want to give a gift of $100 to everyone living in Lexington, Kentucky that owes you more than $500. The following Qtcl script does this.

    #!/usr/local/qddb/bin/qtclsh
    set s [qddb_schema open Debtors]
    set k [qddb_search $s -prunebyattr Amount number_range 500.00 -]
    set k1 [qddb_search $s -prunebyattr Address.City Lexington]
    set k [qddb_keylist op intersection $k $k1] ;# deletes old $k & $k1
    set k2 [qddb_search $s -prunebyattr Address.State Kentucky]
    set k [qddb_keylist op intersection $k $k2]
    set r [qddb_rows select -attrs {Amount Address.City Address.State} \
         -suppress on -deldup_rows on $k]
    qddb_keylist delete $k ;# finished with the keylist
    foreach i $r {
        set t [lindex $i 0] ;# tuple descriptor
        set v [qddb_view define $t {}]
        while {[qddb_tuple lock $t] == 0} {
            puts "Waiting for tuple to be released"
            exec sleep 1
        }
        set amt [qddb_instance getval $v Amount]
        qddb_instance setval Amount [expr $amt - 100.0]
        qddb_tuple write $t
        qddb_tuple delete $t ;# deletes row, tuple, and view
    }
    qddb_schema close $s

7.4.3 Tuple manipulation by rows

Finding tuples containing a row matching some query is useful for deleting outdated records, moving them to a different relation, and so forth. The qddb_rows select command can be used to find tuples, but there is a more efficient way. The qddb_keylist prune command is a nice way to find tuples matching a query without the expense of producing the rows.

Example:

Suppose you want to delete everyone from your Debtors relation that has a balance of $0.00.

    #!/usr/local/qddb/bin/qtclsh
    set s [qddb_schema open Debtors]
    set k [qddb_search $s -prunebyattr Amount n 0.00]
    set k [qddb_keylist process prune -prunebyrow {Amount} \
        -deldup_sameentry on $k]
    foreach i $k {
        set t [qddb_tuple read $i] ;# tuple descriptor
        if {[string compare $t ""] == 0} {continue}
        qddb_tuple remove $t ;# delete from relation
    }
    qddb_schema close $s