📄 ug_ch5b.htm
字号:
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 3.2//EN"><html><head><meta name="generator" content="HTML Tidy for Linux/x86 (vers 1 September 2005), see www.w3.org"><meta http-equiv="content-type" content="text/html; charset=us-ascii"><meta name="Generator" content="Microsoft Word 97"><title>db.* User's Guide Chapter 5</title></head><body><h3><a name="UsingKeys"></a>5.4.1 Data Retrieval Using Keys</h3><p><font size="2">Key field usage from the database designperspective was introduced in section 4.4.1, "Logical DesignConsiderations." The database manipulation aspects of key fieldusage is presented here, using the C code that would implement theexamples presented in that section.</font></p><p><b><a name="Validation"></a>Code Validation</b></p><p>The following example from the "vehicle make" exampleillustrates how to use keys to validate coded data fields.</p><pre><font color="#0000FF">char vma_desc[25]; /* variable to contain vehicle make */struct fleet f; /* variable to hold a fleet record */get_user_info(&f); /* fleet record entered by user *//* validate correct vehicle make code */if (d_keyfind(VMA_CODE, f.vma, task, CURR_DB) == S_NOTFOUND) entry_error("invalid vma code");else { /* read vehicle description */ d_crread(VMA_DESC, vma_desc, task, CURR_DB); /* enter fleet record */ ...}</font></pre><p><font size="2">The vehicle make code entered by the user as partof the fleet record is used as the key value argument of the<b>d_keyfind</b> function, to check that a vehicle record for thatmake exists in the database. If the record does exist, itsdescription is read (function <b>d_crread</b>) and is displayed tothe user once the record entry is completed. VMA_CODE and VMA_DESCare constants defined in the database header file created by<b>ddlp</b>. The second argument to both <b>d_keyfind</b> and<b>d_crread</b> are pointers to the variables that contain thenecessary data.</font></p><p><b><a name="Range"></a>Retrieval Based On a Range ofValues</b></p><p>Recall from the "checking account" database example of Chapter4, "Database Design," that the check record type contained a keyfield called <b>check_date</b>. This field was defined as a keyfield to facilitate rapid retrieval of the check record occurrencesfor a particular time period. The example code below prints thechecks dated in the month of May, 1993.</p><pre><font color="#0000FF">#include "db.star.h"#include "ckngacct.h"struct check chk; /* check record variable */int start_date; /* julian of start date of range */int end_date; /* julian of end date of range */int date; /* last scanned check date /DB_TASK task; /* default task *//* application functions found elsewhere */extern char *calendar(); /* converts julian to calendar date */extern int julian(); /* converts calendar to julian date */main(){ int status; status = d_opentask(&task); status = d_open("ckngacct", "o", task); start_date = julian("05/01/93"); end_date = julian("05/31/93"); /* position to first key in range */ if ((status=d_keyfind(CHECK_DATE, &start_date, task, CURR_DB)) == S_NOTFOUND) status = d_keynext(CHECK_DATE, task, CURR_DB); /* scan thru all keys in range */ while (status == S_OKAY) { d_keyread(&date, task); if (date > end_date) break; /* no longer in range */ d_recread(&chk, task, CURR_DB); printf("number : %d\n", chk.check_no); printf("date : %s\n", calendar(chk.check_date)); printf("paid to : %s\n", chk.paid_to); printf("amount : $ %f.2\n", chk.amount); status = d_keynext(CHECK_DATE, task, CURR_DB); } d_close(task); d_closetask(task);}</font></pre><p><font size="2">The <b>d_keyfind</b> call will position to thefirst check date key equal to start date. If there are no checksdated 05/01/93 on file, then function <b>d_keynext</b> is called toposition to the first check whose date is greater than the startdate. The <b>while</b> loop first reads the value of the positionedkey using function <b>d_keyread</b>. Note that this reads the keyvalue only and does not read the record. The record contents areonly read if the check date returned from the <b>d_keyread</b> callis within the desired range. Function <b>d_recread</b> reads thecontents of the current record for display by the subsequent<b>printf</b> calls. The final <b>d_keynext</b> call positions tothe next check date key.</font></p><p><b><a name="Complex"></a>Complex Searches</b></p><p>The m.o. (modus operandi) example of Chapter 4, "DatabaseDesign," introduced how complex searches can be rapidly performedthrough the use of keys. The m.o. record consists of a single25-byte key field in which each element of the array represents acoded m.o. attribute.</p><p>The simplest approach is to scan through all of the<b>mo_data</b> keys, checking each one for a match, as follows:</p><pre><font color="#0000FF">char mo_key[25]; /* m.o. key is 25 byte array */char mo_search[25]; /* user entered search data */int status; ... /* user enters m.o. search data */for (status = d_keyfrst(MO_DATA, task, CURR_DB); status == S_OKAY; status = d_keynext(MO_DATA, task, CURR_DB) ) { d_keyread(mo_key, task); if (mo_match(mo_key, mo_search)) ... /* report match */}</font></pre><p><font size="2">Function <b>mo_match</b> checks the m.o. keyagainst the m.o. search data entered by the user, returning true(that is, non-zero) if they match and false (that is, zero) if theydo not. A zero value in an m.o. search data element means thatattribute is not to be used in the search.</font></p><pre><font color="#0000FF">/* Check for matching m.o.s */mo_match(mo1, mo2)char *mo1;char *mo2;{ int x; for ( x = 0; x < 25; ++x ) { if ((mo2[x] != 0) && (mo1[x] != mo2[x])) return (0); } return (1);}</font></pre><p><font size="2">Since keys are sorted, the number of keys thatneed to be scanned can be reduced when the first element (and anysubsequent elements) is non-zero. For example, if the user hassupplied m.o. values for the first three elements, a key scan ofonly the keys on file with those values can be performed, yieldinggreat performance improvements.</font></p><pre><font color="#0000FF">char mo_key[25]; /* m.o. key is 25 byte array */char mo_search[25]; /* user entered search data */int mo_prefix; /* initial non-0 elements in mo_search */int lc; /* loop control index */int status; ... /* user enters m.o. search data *//* compute mo_prefix */for (mo_prefix = 0; mo_search[mo_prefix] != 0; ++mo_prefix) ; /* count number of initial non-0 elements *//* initialize mo_key */for (lc = 0; lc < 25; ++lc) { if ( lc < mo_prefix ) mo_key[lc] = mo_search[lc]; else mo_key[lc] = 0;}/* position to first key with matching prefix */if ((status=d_keyfind(MO_DATA, mo_key, task, CURR_DB)) == S_NOTFOUND) status = d_keynext(MO_DATA, task, CURR_DB);/* scan all keys with matching prefix */while (status == S_OKAY){ d_keyread(mo_key, task); /* ensure prefix still matches */ for (lc = 0; (lc < mo_prefix) && (mo_key[lc] == mo_search[lc]); ++lc) ; if (i < mo_prefix) break; /* prefix doesn't match - scan ends here */ if (mo_match(mo_key, mo_search)) ... /* report match */ status = d_keynext(MO_DATA, task, CURR_DB);}</font></pre><p><font size="2">Notice that this example is very similar to theexample of retrieval by a range of values. Also notice that if thefirst element of <b>mo_search</b> is zero, all <b>mo_data</b> keyswill be checked.</font></p><p><b><a name="Compound"></a>Using Compound Keys</b></p><p>Suppose that the <b>tims</b> borrower record definition(presented in section 4.5.3, "Database Design") were modified suchthat the friend's name was not one field, but two (for the last andfirst name). A compound key may be defined to make one key from thetwo fields:</p><pre><font color="#0000FF">record borrower { key char fr_last[16]; char fr_first[16]; long date_borrowed; long date_returned; compound unique key friend { fr_last; fr_first; }}</font></pre><p><font size="2">Note that the last name may be used as a key byitself. A new key definition, named <b>friend</b>, is defined inthe record. (It must also be included in a key filelist.)</font></p><p>When <b>ddlp</b> encounters a compound key, it creates a specialstructure for the key in the header file it generates. In thiscase, within <b>tims.h</b> will be the following structuredefinition:</p><pre><font color="#0000FF">struct friend { char fr_last[16]; char fr_first[16];};</font></pre><p><font size="2">The definition is intended to be used to performsearches for compound keys, as in the following codefragment:</font></p><pre><font color="#0000FF">#include "tims.h"...struct friend fr;...printf("Last name: ");gets(fr.fr_last);printf("First name: ");gets(fr.fr_first);if (d_keyfind(FRIEND, &fr, task, CURR_DB) == S_NOTFOUND) printf( "No one by that name found\n" );else { /* use the borrower record */</font></pre><h3><a name="UsingSets"></a>5.4.2 Data Retrieval Using Sets</h3><p><font size="2">The process of retrieving records from a databaseby moving through the various set relationships defined in theschema is called set navigation. A general procedure for navigatingsets follows:</font></p><ol><li>Find the record that is the owner of the set whose members areto be read. Typically, this can be done using keys, by iterativelyapplying this procedure, or by a combination of both.</li><li>Make the located owner record the current owner of the set tobe traversed, using an appropriate currency manipulation function(such as <b>d_setor</b>).</li><li>Find the members of the set, using the set navigation functions(for example, <b>d_findnm</b> will set the current record andcurrent member of the set to the next member record).</li></ol><p>Each of the set member find functions (<b>d_findXm</b>) will setboth the current member and the current record to point to thefound record occurrence. A reciprocal function, <b>d_findco</b>,will set the current owner of the specified set from a currentrecord that is connected through the specified set. The currencychanges made by each <b><i>db.*</i></b> function are identified inthe function descriptions in the <b><i>db.*</i> ReferenceManual</b><b>.</b></p><p>As an example of the above procedure, consider the transactionsset from the checking account example in Chapter 4, "DatabaseDesign." The budget record is the owner and the check record is themember. The following code will display all of the checks writtenagainst budget category FOOD.</p><pre><font color="#0000FF">/* locate the budget record for the FOOD budget */d_keyfind(CODE, "FOOD", task, CURR_DB);/* make the FOOD budget the current owner of set transactions */d_setor(TRANSACTIONS, task, CURR_DB);/* find each member of the set and read and print its contents */for (status = d_findfm(TRANSACTIONS, task, CURR_DB); status == S_OKAY; status = d_findnm(TRANSACTIONS, task, CURR_DB)){ d_recread(&chk, task, CURR_DB); ... /* print check record */}</font></pre><p><font size="2">The set navigation procedure above describes atop-down navigation, wherein the owner is located and then themembers. <b><i>db.*</i></b> also provides the ability to firstlocate a member and then the owner by using function<b>d_findco</b>, which finds the owner of the current record forthe specified set. For example, the following code will locate acheck record by check number, and then find and print its budgetcategory.</font></p><pre><font color="#0000FF">struct check chk; /* check record variable */struct budget bud; /* budget record variable *//* locate check numbered 3104 */chk.check_no = 3104;d_keyfind(CHECK_NO, &chk.check_no, task, CURR_DB);/* read check record contents */d_recread(&chk, task, CURR_DB);/* find its owner thru the transactions set */d_findco(TRANSACTIONS, task, CURR_DB);/* read budget record contents */d_recread(&bud, task, CURR_DB);/* print results */printf("check : %d\n", chk.check_no);printf("date : %s\n", calendar(chk.check_date));printf("paid to : %s\n", chk.paid_to);printf("amount : $ %f.2\n", chk.amount);printf("budget : %s\n", bud.code);</font></pre><p><font size="2"><b><a name="Navigation"></a>Many-to-ManyNavigation</b></font></p><p>The navigation of the students to classes, many-to-many, setexample in Chapter 4, "Database Design," is shown in the followingcode, which lists the students registered in class CS101.</p><pre><font color="#0000FF">struct class crec; /* class record variable */struct student srec; /* student record variable *//* find CS101 class record */d_keyfind(CLASS_ID, "CS101", task, CURR_DB);/* read contents of class record */d_recread(&crec, task, CURR_DB);/* make the class record owner of the my_students set */d_setor(MY_STUDENTS, task, CURR_DB);/* scan each member of my_students set */while (d_findnm(MY_STUDENTS, task, CURR_DB) == S_OKAY) { /* find student record which owns current intersect record */ d_findco(MY_CLASSES, task, CURR_DB); /* read and print contents of student record */ d_recread(&srec, task, CURR_DB); printf("CS101: %s\n", srec.name);}</font></pre><p><font size="2">Note that function <b>d_setor</b> actually setsboth the current owner and the current member of set<b>my_students</b>. The current owner is set to the CS101 record,and the current member is set to NULL_DBA. This allows the initialcall to <b>d_findnm</b> to return the first member of the set. Theoutput from execution of the above code might be:</font></p><pre><font color="#0000FF">CS101: CarlsonCS101: JonesCS101: KellyCS101: Smith</font></pre><p><font size="2">From this example you should be able to producethe code for listing the classes in which a particular student isregistered.</font></p><p><b><a name="Variable"></a>Variable-length Text Retrieval</b></p><p>Section 4.4.1, "Logical Design Considerations," included adescription of the use of multiple-member sets in implementing textdata. The code needed to retrieve the text data in this example isquite simple, as shown below. Here the customer note for customerid <b>IBMFL</b> is displayed.</p><pre><font color="#0000FF">struct customer cust; /* customer record variable */char text[80]; /* text data - size of largest line *//* find customer with id IBMFL */d_keyfind(CUST_ID, "IBMFL", task, CURR_DB);/* make customer current owner of cust_notes set */d_setor(CUST_NOTES, task, CURR_DB);⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -