October 25, 1985
 
TO:   All interested dBASE and Clipper users 

FROM: K. E. Saffer

RE:   Multi-user file access


     It all started in 1983 while I was writing a financial 
application for an interior design firm in Springfield, MA.  I 
was using dBASE II on a Columbia MPC running MP/M-68.  We had 8 
users on line and I had to come up with some sort of file access 
control methods or the users would most certainly collide with 
each other sooner or later.

     Being experienced with LAN systems I possessed a working 
knowledge of how they control file access.  Either on disk or 
located somewhere in shared memory is a structure called a 
semaphore table.  Each record within this structure has space for 
a data file name and in the case of record locking schemes a 
record number.  There is also other specialized information that 
varies from system to system, but we are only interested in the 
basics.

     When a user application wishes to open a file in a 
read/write mode, it would first check the semaphore table to 
determine if the file and record were free to use.  If so, the 
semaphore table was rapidly updated to show that the file was in 
use (locked).  The changes were made to the file, any dirty 
buffers were flushed, and the semaphore table was again updated 
to show that the file was now free to be used (unlocked).  

     Before we progress any further, some facts about dBASE data 
file access must be known.  When you USE a data file, you are in 
reality opening the file in read-write mode.  So long as you do 
not use any commands that make changes to a data file (EDIT, 
REINDEX, and the dreaded APPEND BLANK to name a few) the file may 
be closed without writing any information the the disk drive.  
This allows multiple users to scan and search though the file all 
day long without doing any damage.
                
     Also, the actual amount of data brought into memory from a 
file read is rarely a single record.  In reality, several records 
are brought into memory, so say bye-bye to any hope of using 
record locking techniques in this system (or at least until AT 
comes out with a workable multi-user database manager).

     This leaves us with file locking as our only recourse.  As 
the name implies, once a file is locked by an application, no 
other application can make changes to the file until the original 
application is through with it.  This requires that an 
application store the changes to be made in local memory 
variables, then quickly open the file, perform any REPLACEs and 
close it.  If you have been in the habit of allowing a user to 
keep a file open in USE all the time and are performing @..GETs 
on the data file fields directly, you will have to make changes 
in your programming style.

     I decided to implement a semaphore table on disk as a dBASE 
database.  At first a single database was used to hold all 
references to locked files, but one day that file became 
corrupted due to two users trying to lock two different files at 
the same time.  To correct this, I use a small database file as a 
semaphore table for each database file that requires controlled 
access.

     Enough of this! How do I use it ?!?!?!? 

     OK, OK. I know this is boring, but your multi-user users are 
gonna love you for this.  Here is a real-life example.  We have a 
dBASE file called CUSTOMER.DBF, with it's associated index 
CUSTOMER.NDX.  To provide control over the customer file we will 
create a semaphore file called CUSTLOCK.DBF.  Its structure 
consists of one logical field called LOCKED.  The CUSTLOCK data 
file will have only one record in it, and the LOCKED field will 
be initialized to .T.

     The program that manipulates the customer file has three 
separate sections in it: viewing, editing, and adding records.
When the application is viewing the file, the semaphore file is 
ignored because the view section will never make any changes to 
it.  When editing or adding records, the program will first check 
the semaphore file to make sure no one else is making changes.  
If the file is locked, the operator is prompted to wait until 
it becomes available, otherwise the file is locked and changes 
made.  Then the program will close the customer file, update the 
semaphore file and proceed about it's business.

     The trick here is to read the data file information into 
local memory variables, perform editing on these, then REPLACE 
the data file fields with the memory variables.  This gives the 
program the opportunity of knowing exactly when it is going to 
make any changes to the file.  We shall maintain control.
Make darn sure your program never changes a file without your 
permission, or you will most certainly corrupt the file and 
possibly lose your job.

     The following is an extract of a customer data file 
manipulation program I wrote for an advanced point-of-sale 
application used by a local retailer.  The system is compiled 
using Clipper, but all Clipper-specific code has been removed or 
rewritten to allow operation under dBASE III.  Remember that in 
addition to the data manipulation programs, you must provide a 
program that will unlock all semaphore files in case the system 
is accidentally interrupted (i.e. power failure, lamebrain user, 
etc.)



The structures of the files follow:

File CUSTOMER.DBF
Number of records:   30
Fieldname  Type Length Decimals
---------- ---- ------ --------
FIRST_NAME  C       20        0
INITIAL     C        2        0
LAST_NAME   C       20        0
ADDRESS1    C       40        0
ADDRESS2    C       40        0
ADDRESS3    C       40        0
ZIP_CODE    C       10        0

File Customer is indexed on UPPER(LAST_NAME) to CUSTOMER.NDX.


File CUSTLOCK.DBF
Number of records:    1
Fieldname  Type Length Decimals
---------- ---- ------ --------
LOCKED      L        1        0


The file manipulation program follows:

* Program ------:CUSTMAIN.PRG
* Author -------:Kevin E. Saffer
* Date ---------:October 28, 1985   
* Notes --------:Demonstrate methods for multi-users file access control
*
*    This program has been freely placed into the public domain without
*    any restrictions concerning its use.

* establish working environment
SET BELL OFF
SET COLOR TO +7/0,7/0,0
SET DELETED ON
SET DELIMITER ON
SET DELIMITER TO '[]'
SET EXACT OFF
SET HEADINGS OFF
SET SAFETY OFF
SET TALK OFF

* Open the customer file and display heading
SELECT A
USE Customer.dbf INDEX Customer.ndx
CLEAR
@ 01,00 SAY "Customer Maintenance"
@ 01,67 SAY "Date " + DTOC(DATE())
@ 02,00 SAY "========================================" + ;
            "========================================"
* start main loop
DO WHILE .T. 

  SET COLOR TO 7/0,+7/0

  * read file fields into local memvars
  Mfirst = First_name
  Minit = Initial
  Mlast = Last_name
  Maddr1 = Address1
  Maddr2 = Address2
  Maddr3 = Address3
  Mzip = Zip_code

  * get the record number for use later
  Mrec_no = RECNO()

  * display memvars
  @ 04,00 SAY " Lastname" GET Mlast 
  @ 04,59 SAY "Record number [" + STR(Mrec_no,5,0) + "]"
  @ 05,00 SAY "Firstname" GET Mfirst
  @ 06,00 SAY "  Initial" GET Minit
  @ 08,00 SAY "  Address" GET Maddr1
  @ 09,00 SAY "         " GET Maddr2
  @ 10,00 SAY "         " GET Maddr3
  @ 11,00 SAY " Zip Code" GET Mzip 
  CLEAR GETS

  * prompt operator
  Choice = " "
  SET CONFIRM OFF
  DO WHILE AT(Choice,"PNAEDX") = 0
    Choice = " "
    @ 23,00
    @ 23,00 SAY "P)revious, N)ext, A)dd, E)dit, D)elete or eXit?" ;
    GET Choice PICTURE '!'
    READ
  ENDDO

  DO CASE
  CASE Choice = "X"
    * exit program
    CLEAR
    QUIT

  CASE Choice = "P"
    * previous record
    SKIP -1
    IF BOF()
      @ 23,00
      @ 23,00 SAY "Beginning of file encountered, going to bottom..."
      GO BOTTOM
      LOOP
    ELSE
      LOOP
    ENDIF

  CASE Choice = "N"

    * next record
    SKIP 
    IF EOF()
      @ 23,00
      @ 23,00 SAY "End of file encountered, going to top..."
      GO TOP 
      LOOP    
    ELSE
      LOOP
    ENDIF

  CASE Choice = "A"
    * add a new record

    * first, initialize memvars for operator input
    Mlast = "                    "
    Mfirst = "                    "
    Minit = "  "
    Maddr1 = "                                        "
    Maddr2 = "                                        "
    Maddr3 = "                                        "
    Mzip = "          "

    * allow operator to edit the memvars
    Edit_more = " "
    DO WHILE Edit_more <> "Y"
      Edit_more = " "
      @ 04,00 SAY " Lastname" GET Mlast 
      @ 05,00 SAY "Firstname" GET Mfirst
      @ 06,00 SAY "  Initial" GET Minit
      @ 08,00 SAY "  Address" GET Maddr1
      @ 09,00 SAY "         " GET Maddr2
      @ 10,00 SAY "         " GET Maddr3
      @ 11,00 SAY " Zip Code" GET Mzip
      @ 23,00
      @ 23,00 SAY "Please enter the customer information."
      READ
      @ 23,00
      @ 23,00 SAY "Is the above information correct? (Y/N)" ;
      GET Edit_more PICTURE '!'
      READ
    ENDDO

    * prompt operator to add or exit
    Add_it = " "
    @ 23,00
    @ 23,00 SAY "A)dd this new record or E)xit?" GET Add_it PICTURE '!'
    READ
    IF Add_it <> "A"
      * go back to display routine
      LOOP
    ENDIF

    * lock up customer file, we are still in work area A
    @ 23,00 

    @ 23,00 SAY "Adding new record..."

    USE Custlock.dbf
    IF Locked
      SET COLOR TO *15/0,+7/0
      @ 23,00
      @ 23,00 SAY "Customer file in use, retrying..."
      SET COLOR TO 7/0,+7/0
    ENDIF
    DO WHILE Locked
      USE
      USE Custlock.dbf
    ENDDO
    REPLACE Locked WITH .T.
    USE
    
    * the semaphore database now shows the customer file in use, perform
    * replacements

    USE Customer.dbf INDEX Customer.ndx
    APPEND BLANK
    REPLACE First_name WITH Mfirst
    REPLACE Last_name WITH Mlast
    REPLACE Initial WITH Minit
    REPLACE Address1 WITH Maddr1
    REPLACE Address2 WITH Maddr2
    REPLACE Address3 WITH Maddr3
    REPLACE Zip_code WITH Mzip
    Mrec_no = RECNO()
    USE

    * now, unlock the customer file and get back to the record we just added
    USE Custlock.dbf
    REPLACE Locked WITH .F.
    USE Customer.dbf INDEX Customer.ndx
    GO Mrec_no
    LOOP

  CASE Choice = "E"
    * editing the current record is just like adding, without initializing
    * the memvars

    * allow operator to edit the memvars
    Edit_more = " "
    DO WHILE Edit_more <> "Y"
      Edit_more = " "
      @ 04,00 SAY " Lastname" GET Mlast 
      @ 05,00 SAY "Firstname" GET Mfirst
      @ 06,00 SAY "  Initial" GET Minit
      @ 08,00 SAY "  Address" GET Maddr1
      @ 09,00 SAY "         " GET Maddr2
      @ 10,00 SAY "         " GET Maddr3
      @ 11,00 SAY " Zip Code" GET Mzip
      @ 23,00
      @ 23,00 SAY "Please enter any corrections."

      READ
      @ 23,00
      @ 23,00 SAY "Is the above information correct? (Y/N)" ;
      GET Edit_more PICTURE '!'
      READ
    ENDDO

    * prompt operator to add or exit
    Add_it = " "
    @ 23,00
    @ 23,00 SAY "A)dd these changes or E)xit?" GET Add_it PICTURE '!'
    READ
    IF Add_it <> "A"
      * go back to display routine
      LOOP
    ENDIF

    * lock up customer file, we are still in work area A
    @ 23,00 
    @ 23,00 SAY "Adding these changes..."
    USE Custlock.dbf
    IF Locked
      SET COLOR TO *15/0,+7/0
      @ 23,00 SAY "                                           "
      @ 23,00 SAY "Customer file in use, retrying..."
      SET COLOR TO 7/0,+7/0
    ENDIF
    DO WHILE Locked
      USE
      USE Custlock.dbf
    ENDDO
    REPLACE Locked WITH .T.
    USE
    
    * the semaphore database now shows the customer file in use, perform
    * replacements
    USE Customer.dbf INDEX Customer.ndx

    * get back to the record we want
    GO Mrec_no
    REPLACE First_name WITH Mfirst
    REPLACE Last_name WITH Mlast
    REPLACE Initial WITH Minit
    REPLACE Address1 WITH Maddr1
    REPLACE Address2 WITH Maddr2
    REPLACE Address3 WITH Maddr3
    REPLACE Zip_code WITH Mzip
    Mrec_no = RECNO()
    USE

    * now, unlock the customer file and get back to the record we just edited
    USE Custlock.dbf
    REPLACE Locked WITH .F.
    USE Customer.dbf INDEX Customer.ndx
    GO Mrec_no

    LOOP

  CASE Choice = "D"
    * delete record
    Kill_it = " "
    @ 23,00
    @ 23,01 SAY "ARE YOU SURE YOU WANT THIS CUSTOMER DELETED? (Y/N)" ;
    GET Kill_it PICTURE '!'
    READ
    IF Kill_it = "Y"
      @ 23,00
      @ 23,01 SAY "Deleting this customer..."
      Oldrec = RECNO()
      USE Custlock.dbf
      IF Locked
        SET COLOR TO *15/0,+7/0
        @ 23,00
        @ 23,01 SAY "Customer file in use, retrying..."
        SET COLOR TO 7/0,+7/0
      ENDIF
      DO WHILE Locked
        USE
        USE Custlock.dbf
      ENDDO
      REPLACE Locked WITH .T.
      USE
      USE Customer.dbf INDEX Customer.ndx
      GO Mrec_no

      * check to make sure no one else has deleted this record while we were 
      * locking up the file
      IF Last_name = Mlast .AND. First_name = Mfirst
        DELETE
        USE  
        USE Custlock.dbf
        REPLACE Locked WITH .F.
        USE
      ENDIF
      USE Customer.dbf INDEX Customer.ndx
      GO TOP      
      @ 23,00 CLEAR
    ENDIF
  ENDCASE 
ENDDO

* end of file CUSTMAIN.PRG



     The actual program in use has search and print capabilities, 
a function to unlock the semaphore file, and an indexing routine.  
The indexing routine is important because the program does not 
pack the data file when a record is deleted.  The operator thinks 
it's gone because we SET DELETED ON in the beginning of the 
program.  Therefore, once a week the file is re-indexed to remove 

any deleted records.

     The program runs relatively slowly in interpetive dBASE, but 
thats what the Clipper compiler is for.  The compiled application 
goes like lightning.

     The advantage to having the program take care of the file 
locking methods is that now the application is not "LAN 
specific".  It can run on nearly any LAN or multi-user system, so 
long as the system manager give everyone read-write access to the 
volume where the semaphore files reside.

     While you are experimenting with the program, see what 
happens when you try to edit the file when the semaphore database 
shows the file is locked.  Currently, I make the user wait a 
second or two until the file becomes available.  You might give 
the operator a choice of whether to wait for the file, or exit.

     The program by itself is only a demonstration of how 
controlled file sharing may be accomplished and is nowhere near a 
complete database manipulation program.  I leave it up to you to 
make it perfect.


Thank you for your support.

KES