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