INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Databases and tables

How do I update the Database Schema of a Distributed Application? by wgcs
Posted: 26 Sep 02

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.)


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.



Back to Microsoft: Visual FoxPro FAQ Index
Back to Microsoft: Visual FoxPro Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close