in the last article in this series i will look at the problem of detecting and resolving what visual foxpro thinks are update conflicts. the idea behind this is that we want to ensure that when vfp reports an update conflict there really is one. the reason that there is any doubt is that vfp will report an update conflict if any change has been made to disk copy of the record that is being saved – whether or not the changes made by the current user actually conflict with saved data or not. as an example consider the following scenario:
· user #1 opens a customer record to edit their phone number
· user #2 opens the same customer record to add the zip + 4 value
· user #1 saves their change to the phone number
· user #2 tries to save their change to the zip code, but gets an “update conflict” error even though the value that this user is changing was not affected by
the changes that were made and saved by user 1.
with a little effort we can detect and resolve such errors and only reject a user change when it truly conflicts with existing data. to do that we will make use of the ability of visual foxpro to return the curval() and oldval() values for any column (for an explanation of the role of these functions see my december 2005 article on “handling buffered data in visual foxpro”).
the process consists of several steps and is shown here (for clarity) as a simple program with some associated functions though, of course, it could easily be built into a class and instantiated as an object when needed. the program shown here only returns true when conflicts have been resolved. a return value of false indicates that either an error has occurred, or that conflicts remain. in the latter case a cursor contains the details of all unresolved conflicts.
[1] first we need to check that we have a table name and that it is actually buffered. since, as we will see, the resolution process relies on using tableupdate(), we can only handle conflicts for buffered tables anyway. the program creates a cursor which will be used to record details of any conflicts that cannot be resolved programmatically so that upon completion, they can be presented to the user for review and decision.
lparameters tutable local llretval, lnbuffmode, lctable, lnoldarea, lnnextrec, lnrows *** check parameters if empty(tutable) *** nothing passed, use current table lctable = alltrim( alias() ) if empty( lctable ) *** no table, so we can’t track any update conflicts – return return llretval endif else do case case type( "tutable" ) = "c" *** assume character string is the required alias lctable = alltrim( tutable) case type( "tutable" ) = "n" *** get the alias for the specified work area lctable = alias( tutable ) otherwise *** invalid parameter return llretval endcase endif *** create a local cursor for storing conflicts create cursor curcflix ( ; cfxrecnum c ( 8), ; && conflict number cfxfldnam c (200), ; && field name cfxoldval c (200), ; && original value cfxcurval c (200), ; && current value on disk cfxusrval c (200), ; && change in the buffer cfxforcit n ( 1) ) && user defined action *** check buffermode llretval = .t. lnbuffmode = cursorgetprop( 'buffering', lctable ) if lnbuffmode < 2 *** if table is not buffered just return false – we can’t use tableupdate() anyway return .f. else *** save current work area and select required table lnoldarea = select() select (lctable) endif
[2] the next part of the process depends upon the type of buffering that is being used. if the table is row buffered then we only need to check the current row. when table buffering is in effect we need to process all records with pending changes. that means wrapping the row-level check inside code that uses getnextmodified() to find all records with pending changes.
*** if row-buffered, just process this row if lnbuffmode < 4 *** row buffering llretval = checkrow( recno(), lctable ) else *** table buffering - need to find all modified records *** which means calling getnextmodified() until it returns 0 *** indicating that there are no more records with changes lnnextrec = 0 do while .t. lnnextrec = getnextmodified( lnnextrec ) if lnnextrec = 0 exit endif *** try and update the record llretval = checkrow( lnnextrec, lctable ) if ! llretval *** if failed, exit exit endif enddo endif
[3] the checkrow() function (or method if you are creating an object from this) is where the real work is done. for each field in the record, this method reads the current user's buffered value, the oldval() and curval() values and passes them through a logic check as follows:
if the user has not changed this field, and the old and current values are identical,
ignore this field – it will not cause a conflict anyway
else
if the user has not changed the field, but the old and current values are different,
update the buffer directly with the current value
else
if the user has changed the field, but the value in the buffer is already identical
to the current value, ignore this change
else
this really is a conflict, so record it
but this logic on knowing whether the current user has actually change any given field. to get a list of the fields that vfp regards as having been changed we use a function named getuserchanges() that returns a comma separated list of columns with pending changes:
function getuserchanges local lcretval, lctable, lcfldstate, lncnt, lcstatus *** initialize the return value lcretval = '' *** and the current alias - which was handled in the calling code above lctable = alias() *** first check for fields that vfp sees as having have changed values lcfldstate = nvl( getfldstate( -1, lctable ), "") if empty( chrtran( lcfldstate, '1', '')) *** nothing but '1', therefore nothing has changed return lcretval endif *** so, we have got at least one changed field! but we need to handle the *** deleted flag indicator first. we can use "deleted()" as the field name here! if ! inlist( left( lcfldstate, 1), "1", "3" ) lcretval = "deleted()" endif *** now get rid of the deleted flag indicator lcfldstate = substr( lcfldstate, 2 ) *** get the field names for changed fields for lncnt = 1 to fcount() *** loop through the fields lcstatus = substr( lcfldstate, lncnt, 1 ) if inlist( lcstatus, "2", "4" ) lcretval = lcretval + iif( ! empty( lcretval ), ",", "") + field( lncnt ) endif next *** return the list of changed fields return lcretval
notice that we use the native deleted() function as a field name in this function. both curval() and oldval() will accept this as a valid "field name" (returning a logical value indicating whether the field was deleted in the underlying table) so we can actually check for deletions as well as changes to values!
[4] as noted above, the checkrow function gets called once for each row that needs to be validated and is where the decision is made as to whether user intervention is required or not. for rows where a conflict cannot be resolved programmatically we write out the details to the cursor we created earlier. here is the function:
function checkrow( tnrecnum, tctable )
local lncnt, lucurval, luoldval, lnrows, llretval, lcfldlist, lcfldname, luusrval
*** force the correct record to be current select (tctable) if recno() # tnrecnum goto tnrecnum endif *** get the list of fields changed by the current user lcfldlist = "" lcfldlist = thisform.getuserchanges( tctable ) *** scan through the fields for lncnt = 1 to fcount() lcfldname = field( lncnt ) lucurval = curval( field( lncnt )) luoldval = oldval( field( lncnt )) luusrval = eval( field( lncnt )) *** will this field cause a conflict? if lucurval == luoldval *** no changes have been made to the field *** so no problem will arise else *** changes have been made to the field if ! field( lncnt ) $ lcfldlist *** but the curent user has not modified the field *** so we can just update it from curval() replace (field(lncnt)) with lucurval else *** something has changed! the question is what? if eval( field(lncnt) ) == lucurval *** user has not actually changed anything loop else *** this is a conflict that we cannot resolve programmatically *** so write out the information as character string data to the cursor insert into curcflix ( cfxrecnum, cfxfldnam, cfxoldval, cfxcurval, cfxusrval, cfxforcit); values ( transform(recno()), lcfldname, transform(luoldval), transform(lucurval), ; transform(luusrval), 2 ) endif endif endif next
[5] the final section of the main process code merely checks the record count of the conflict cursor to see if anything needs to be done by the user. if there are no records in this cursor, all conflicts have been resolved by forcing the user’s buffer to match the underlying data where there were discrepancies. so we now use a tableupdate() and include the force parameter to update the data and clear the buffers:
*** check the conflict cursor if reccount( "curcflix") = 0 *** there are no unresolvable conflicts so just force the update llretval = tableupdate( .t., .t., lctable ) else go top in curcflix llretval = .f. endif *** return final status return llretval
obviously if there are any records in the conflict cursor you would need to allow the user to decide what to do and then either force the update or revert it. but that is an exercise for you, the reader.
No comments:
Post a Comment