Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How do I update the Database Schema of a Distributed Application?

Databases and tables

How do I update the Database Schema of a Distributed Application?

by  wgcs  Posted    (Edited  )
Have you ever encountered the situation where you had many installations of your software on customers' machines "in the field" running on live data, and your next update to the software requires changes in the database structure?

This is an issue even if you have just one live distribution of your program: How do you go about updating that database structure while retaining the customer's existing data?

There are toolkits out there that can do this for you, but the process is simple enough to do it yourself.

Basically, My approach is to use a stored procedure to identify the version of a specific database, then if it is older than the version that *this* code is designed for:

1) Create a semaphore file UPDATE.SEM so no other network client starts up to use the data.
2) Establish EXCLUSIVE access to data.
3) CLOSE DATABASES ALL
4) Move .\Data directory to .\OldData
5) Create a new .\Data directory, run the GenDBC'd .PRG to create the new database in .\Data
6) Get a list of tables from the old database, and a list of tables from the new database.
7) For each table in the new database, check to see if it's in the old database, and APPEND FROM it, Only using FULLPATH names to get to each table.
8) Massage the data in any pre-determined way that is necessary.
9) Finally, Delete the UPDATE.SEM file.

Now that you've seen an overview, here's the code to do it (except for step #8, my database didn't need it this time):

(This example code also illustrates how to delete a directory like DELTREE, and how to MOVE/Rename a directory.)

Code: 
PROCEDURE THIS.GetThisDataVersion()
LOCAL laVer[1]
* Note: Data_ver() is a stored procedure in the
*       database that always returns a number,
*       such as 1.0, or 1.1, etc.
SELECT TOP 1 data_ver() FROM stdAdd ORDER BY 1 INTO ARRAY laVer
RETURN laVer[1]
ENDPROC

PROCEDURE THIS.Del( pcMask )
* Del( Skeleton )
LOCAL lcPath, lcFile 

  lcPath = ADDBS(JUSTPATH(pcMask))
  lcFile = sys( 2000, pcMask )      && Locate first file with this mask.
  
  do while .Not. Empty( lcFile )
    delete FILE(lcPath+lcFile)
    lcFile = sys( 2000, pcMask, 1 )  && find next matching file.
  enddo
return
ENDPROC

FUNCTION THIS.RenDir( OrigDir, NewDir )
  DECLARE INTEGER MoveFile IN WIN32API AS apiMoveFile STRING lpExistingFileName, STRING lpNewFileName
  RETURN 0<>apiMoveFile( OrigDir, NewDir )
ENDFUNC

PROCEDURE THIS.CheckDatabaseVersion()
LOCAL lnCurVer, lnDataVer, laTables[1], lnTables, lnNewTables, laNewTables[1]
lnCurVer = oApp.DataVersion
lnDataVer = THIS.GetThisDataVersion()
IF lnDataVer<lnCurVer
  STRTOFILE('Updating Database Structure','UPDATE.SEM')
  lnRes = MESSAGEBOX('The current database version ('+TRANSFORM(lnDataVer)+') is out of date.';
          +CHR(13)+'Update database structure to v'+TRANSFORM(lnCurVer)+' now?',;
          4,'Update Database Structure')
  IF lnRes = 6 && Yes
    CLOSE DATABASES ALL
    
    IF DIRECTORY( SET('DEFA')+ADDBS(CURDIR())+'OldData' )
      lnRes = MESSAGEBOX('The saved-data directory '+SET('DEFA')+ADDBS(CURDIR())+'OldData already exists.';
               +CHR(13)+'Delete it now?',;
               4,'Update Database Structure')
      IF lnres=6 && Yes
        WAIT WINDOW NOWAIT NOCLEAR "Removing OldData..."
        INKEY(0.5)
        THIS.Del( SET('DEFA')+ADDBS(CURDIR())+'OldData\*.*' )
        RMDIR ( SET('DEFA')+ADDBS(CURDIR())+'OldData' )
        IF DIRECTORY( SET('DEFA')+ADDBS(CURDIR())+'OldData' )
          = MESSAGEBOX('Delete directory .\OldData Failed.')
          DELETE FILE ('Update.sem')
          RETURN .F.
        ENDIF
      ELSE
        DELETE FILE ('Update.sem')
        RETURN .F.
      ENDIF
      WAIT WINDOW NOWAIT NOCLEAR "OldData Removed"
      INKEY(0.5)
    ENDIF
    WAIT WINDOW NOWAIT NOCLEAR "Opening Data Exclusively..."
    INKEY(0.5)
    DO WHILE .T.
      Err = 0
      ON ERROR Err=Error
      THIS.CheckingExclusive = .T.
      THIS.nError = 0
      SELECT 0 
      SET REPROCESS TO 0.7 SECONDS 
      OPEN DATABASE Data\AhRooming EXCLUSIVE
      ON ERROR
      
*      IF THIS.nError=1705 OR NOT DBUSED('AhRooming') && Access Denied

      IF Err=1705 OR NOT DBUSED('AhRooming') && Access Denied
      
         lnRes = MESSAGEBOX('Could not open database exclusively.';
                 +CHR(13)+'Other users may still be logged in.',;
                 +CHR(13)+'Try Again?',;
                 4,'Update Database Structure')
        IF lnRes = 6 && Yes
          LOOP
        ELSE
          DELETE FILE ('Update.sem')
          RETURN .F.
        ENDIF
      ENDIF
      CLOSE DATABASES ALL 
      WAIT WINDOW NOWAIT NOCLEAR "Archiving Data into OldData..."
      INKEY(0.5)
      THIS.CheckingExclusive = .F.
      IF NOT THIS.RenDir( FULLPATH('Data'),FULLPATH('OldData') )
         lnRes = MESSAGEBOX('Could not rename data directory to .\OldData',;
                 0,'Update Database Structure')
         DELETE FILE ('Update.sem')
         RETURN .F.
      ENDIF
      lcHome = CURDIR()
      
*!*	      COPY FILE (ADDBS(lcHome)+'OldData\ahRomming.dbc') TO (ADDBS(lcHome)+'OldData\ahr_old.dbc')
*!*	      COPY FILE (ADDBS(lcHome)+'OldData\ahRomming.dct') TO (ADDBS(lcHome)+'OldData\ahr_old.dct')
*!*	      COPY FILE (ADDBS(lcHome)+'OldData\ahRomming.dcx') TO (ADDBS(lcHome)+'OldData\ahr_old.dcx')
      
      MKDIR (ADDBS(CURDIR())+'Data')
      CD (ADDBS(CURDIR())+'Data')
      DO AHR_DBC && Create the database
      WAIT WINDOW NOWAIT NOCLEAR "New Database Schema Created"
      INKEY(0.5)
      CD (lcHome)
      
      OPEN DATABASE OldData\AhRooming EXCLUSIVE 
      lnTables = ADBOBJECTS(laTables,'TABLE')
      CLOSE DATABASES
      
      WAIT WINDOW NOWAIT NOCLEAR "Importing Data..."
      INKEY(0.5)
      OPEN DATABASE data\AhRooming  EXCLUSIVE 
      lnNewTables = ADBOBJECTS(laNewTables,'TABLE')
      FOR lnI = 1 TO lnTables
        IF ASCAN(laNewTables,laTables[lnI],1,ALEN(laTables),1,1+4+2)>0
          * Table still exists
          lcOldTable = ADDBS(CURDIR())+'OldData\'+laTables[lnI]
          USE (ADDBS(CURDIR())+'Data\'+laTables[lnI])
          APPEND FROM (lcOldTable)
        ENDIF
      ENDFOR
      
      CLOSE DATABASES ALL
      
      EXIT
    ENDDO
    
    OPEN DATABASE Data\AhRooming
    DELETE FILE ('Update.sem')
    
    WAIT WINDOW NOWAIT "Database Schema Update Complete!"
    INKEY(0.5)
    * Okay, we should have the database open now.
    
  ELSE
    DELETE FILE ('Update.sem')
    RETURN .F.
  ENDIF
ENDIF
RETURN .T.
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top