Function DbRSelect() Foundation

// In the example, a link is produced between an invoice 
// file, an item file, and a customer file. The results 
// of the function DbRSelect() are displayed. 

PROCEDURE Main 
   USE Customer ALIAS Cust NEW EXCLUSIVE 
   INDEX ON CustNo TO CustNo 
   SET INDEX TO CustNo 

   USE InvItem ALIAS Items NEW EXCLUSIVE 
   INDEX ON ItemNo TO ItemNo 
   SET INDEX TO ItemNo 

   USE Invoice ALIAS Inv NEW 

   SET RELATION TO CustNo  INTO Cust , ; 
                TO ItemNo  INTO InvItem 

   ? Select()                    // result: 3 
   ? Alias()                     // result: Inv 

   ? DbRSelect(1)                // result: 1 
   ? Alias( DbRSelect(1) )       // result: CUST 

   ? DbRSelect(2)                // result: 2 
   ? Alias( DbRSelect(2) )       // result: ITEMS 

   SELECT Cust 
   ? Alias()                     // result: CUST 
   ? DbRSelect(1)                // result: 0 

   ? Inv->( DbRSelect(1) )       // result: 1 
   ? Inv->( DbRSelect(2) )       // result: 2 
   ? Inv->( DbRSelect(3) )       // result: 0 

   CLOSE DATABASES 
RETURN 

// The example shows how DbRSelect() and DbRelation() can be 
// used to create a deletion function that deletes all of 
// the dependent records in the child work areas when 
// the record in the parent work area is deleted. 

PROCEDURE Main 
   USE Customer ALIAS Cust NEW EXCLUSIVE 
   INDEX ON LastName TO CustName 
   SET INDEX TO CustName 

   USE Invoice ALIAS Inv NEW EXCLUSIVE 
   INDEX ON CustNo TO CustNo 
   SET INDEX TO CustNo 

   SELECT Cust 
   SET RELATION TO CustNo INTO Inv 

   ? Cust->( LastRec() )         // result: 100 
   ? Inv->( LastRec() )          // result: 253 

   ? Cust->( DbSeek( "Miller") ) // result: .T. 
   ? Cust->( CascadeDelete() )   // result: 10 
                                 // a total of ten records 
                                 // were deleted 
   SELECT Cust 
   PACK 

   SELECT Inv 
   PACK 

   ? Cust->( LastRec() )         // result:  99 
                                 // one record was deleted 
                                 // from the Customer file 

   ? Inv->( LastRec() )          // result: 244 
                                 // nine records were deleted 
                                 // from the Invoice file 
   CLOSE DATABASES 
RETURN 

// This function deletes the current record and cascades the 
// deletion operation through all dependent child work areas 
FUNCTION CascadeDelete() 
   STATIC snTotal    := 0 
   STATIC snRecursion:= 0 
   LOCAL nDeleted    := 0 
   LOCAL nRelation   := 1 
   LOCAL aRelation   := {} 
   LOCAL nChildArea, cExpression, xValue, ; 
         n, nMax, nRecno 

** Go through all child work areas 
   DO WHILE .NOT. ( nChildArea := DbRSelect(nRelation) ) == 0 

   ** Child work area has no matching data 
      IF (nChildArea)->( Eof() ) 
         nRelation ++ 
         LOOP 
      ENDIF 

   ** Save information from child work area 
      cExpression := DbRelation( nRelation ) 
      xValue      := (nChildArea)->( &(cExpression) ) 

      AAdd( aRelation , ; 
              { nChildArea , ;   // child work area number 
                cExpression, ;   // linking expression 
                xValue     , ;   // value of linking expression 
               (nChildArea)->(RecNo()) ; // child record 
              } ; 
          ) 

   ** Child work area is parent work area to other 
   ** work areas. Call function recursively. 
      IF .NOT. (nChildArea)->( DbRSelect(1) ) == 0 
         snRecursion++ 
         snTotal += (nChildArea)->( CascadeDelete() ) 
         snRecursion-- 
      ENDIF 

      nRelation ++               // next child work area 
   ENDDO 

   DbDelete()                    // delete record 
   nDeleted ++                   // in parent work area 

** Delete all records in the child work areas 
   nMax := Len( aRelation ) 

   FOR n:=1 TO nMax 
      nChildArea  := aRelation[n,1] 
      cExpression := aRelation[n,2] 
      xValue      := aRelation[n,3] 
      nRecno      := aRelation[n,4] 
      (nChildArea)->( DbGoto(nRecno) ) 

      DO WHILE (nChildArea)->( xValue == &(cExpression) ) 
         (nChildArea)->( DbDelete() ) 
         (nChildArea)->( DbSkip() ) 
         nDeleted ++ 
      ENDDO 
   NEXT 

** Total deleted records from the recursion, 
** and set STATIC variable back to 0, when there are 
** no more levels of recursion. 
   IF snRecursion == 0 
      nDeleted += snTotal 
      snTotal  := 0 
      DbGoto( RecNo() )          // position record pointer 
                                 // correctly again in all 
   ENDIF                         // child work areas 

RETURN nDeleted