Creating a reindex prg on 'foreign' tables 1

[beedubau]

My app operates on and writes to a single table (from many)in a master 'foreign' application installed on my users' PC.

My app is a tool which provides facilities not available in the 'master'in relation to images that are pathed in one table.

I open the one table without using the index in the 'master'. I index this several ways in my app. A change to a record in this table obviously upsets the original index to that table.
When the 'master' is next opened it automatically performs a reindex.

In my app I automatically re-open the 'master' which then performs a reindex. The user can then return to my app with the new information included.

From the headers of the cdx files in the 'master' is it possible to recreate the reindex prg so that I do not have to open the 'master'?

Regards

 

[Olaf Doschke]

With which Foxpro-Version are you working?

If a table has Indices in a CDX file that is normally opened automatically together with the DBF and updated with any DBF update, not causing any need to reindex. If you use the INDEX clause of the USE command you can add other IDX or CDX files to be opened with the table. The master app surely does not know of these and does not update these indexes. But the master CDX file should not get out of sync.

What do you mean by "When the 'master' is next opened it automatically performs a reindex." When you open the foreign app or your app? And what CDX is reindexed automatically?

Bye, Olaf.

 

[beedubau]

Olaf,

Thanks for your reply.

I'm using VFP9.

The evidence is that the 'master' app always does a reindex when it is reopened after I have modified the 'target' table. I can only guess how it knows to do that.

I was trying to avoid having to open the 'master' from my app to do that reindex so that all is well in the 'master' when it is reopened by the user.

> The master app surely does not know of these and does not update these indexes.

I agree that these are not relevent to this matter.

>What do you mean by "When the 'master' is next opened it automatically performs a reindex.?"

I can't say it any other way - the 'master' app uses many related tables all of them with cdx. In my app I only operate on one of them - 'target'dbf. The others are opened to be used in SQL statements for temporary cursors.
I do not use any of the indices in the cdx files.

When I have changed records in 'target' table the 'master' runs a reindex before opening.

In my app I make a copy of the 'target' say 'temp' - the user then makes any changes to that table. These can be inspected until a 'commit' button is clicked which then causes my app to copy the 'temp' table to the original 'target' name cFilename.

SELECT Temp
COPY to  (cFilename)

I guess I just want to know whether I can try to run a programmed reindex in the 'master' tables fromm within my app if I have access to all the 'master' tables and their cdx files.

It is just an idea I have to do things behind the scenes. My users like the idea of not making changes to the 'master' tables until they are convinced everything is as they intended - hence I do not change the 'master' table interactively.

Regards

Bryan

 

[Olaf Doschke]

So you're copying the original table to say temp.dbf, index it and then let users edit data in your app.

And then you overwrite the original DBF but not the CDX?

Why overwrite the DBF? copy back the data to the original table, that will update it's CDX and you or the foreign app does not have to reindex it.

Bye, Olaf.

 

[dgrewe]

Is This what you are trying to do ???
Not Tested, You will have to fine tune it

pcMaster = "MASTERDBF"
pcTarget = "TARGETDBF"

DO WHILE GetACopy(pcMaster , pcTarget)=.F.
   =MESSAGEBOX("Opps Master Table not copied")
ENDDO
* USE (pcTarget) alias TARGET
*
* Your code to do what you need with the copy
*
DO WHILE PutBackCopy(pcMaster , pcTarget) = 1
   =MESSAGEBOX("Opps, Master not replaced")
ENDDO
*
* Your code to do what you need in your app next
	

PROCEDURE GetACopy
LPARAMETERS pcTableName, pcTargetDbf
STORE .f. TO llContinue 
DO CASE 
CASE FILE(pcMasterDbf) = .f.
* Ops no file

CASE USED(pcTArgetDbf)
	* WHAT YOU NEED TO DO TO CLOSE IT

CASE USED(pcMasterDbf)
	SELECT (pcMasterDbf)
        llCOntinue = .T.
OTHERWISE  
	SELECT 0
	USE (pcMasterDbf) ALIAS (pcMasterDbf)
        llContinue = .T.
ENDCASE 

IF llContinue
	COPY TO (pcTargetDbf)
	USE
ELSE
	* do what ever for file not found error trap
ENDIF

RETURN llContinue


PROCEDURE PutBackCopy
LPARAMETERS pcMasterDbf, pcTargetDbf
STORE 0 TO lnError
STORE ON("ERROR") TO lcOnError

IF USED (pcTargetDbf)
	* If Tagert is open close it
	SELECT (pcTArgetDbf)
	USE
ENDIF
*
IF USED(pcMasterDbf)
	* If TArget is Open Close it
	SELECT (pcMasterDbf)
	USE
ENDIF

ON ERROR lnError=1
USE (pcMasterDbf) ALIAS (pcMasterDbf) EXCLUSIVE
IF lnError =1
	* Table in use try again later
ELSE
	ZAP
	APPEND FROM (pcTargetDbf)
	USE
ENDIF
ON ERROR (lcOnError)
RETURN lnError

NOTE - The APPEND FROM reindex the file


David W. Grewe Dave

 

[Olaf Doschke]

Hi Dave,

ZAP and APPEND FROM is only good as a quick and dirty solution.

I'd prefer to sync the table in shared mode record by record, only those records that were edited, deleted or inserted in the temp.dbf.

And you might have to care about conflicts due to for example different modifications made by your and the foreign app.

I think this would be much easier by working on your indexed copy plus on the original table at the same time, eg making inserts in both tables. Or even not copy the master.dbf (als long as you don't need new temporary fields) but create an additional CDX file, you're not limited to one.

Bye, Olaf.

 

[dgrewe]

Olaf,
I have a routine I wrote back in the Fox 2.6 days I can give him if the Use exclusive fails a lot. Doing a Record by record compair is slow.

I do not understand whay he has to make a copy. What he just does not open the table shared and make the change to the Master table is beyond me. Sometimes you just have to eat correct computer programming and give the customer what he wants then stand back and be ready to say "I told you so".

David W. Grewe Dave

 

[Olaf Doschke]

>Doing a Record by record compair is slow.

Yes, that's why I would suggest keeping track of modified, deleted and new records.

But the better option should be editing the original DBF.

Bye, Olaf.

 

[beedubau]

Olaf and David,

Thank you for your interest.

My application is an 'add-on' to a 'Genealogy' program.

The users of the 'master' program are paranoid about losing data and/or my program corrupting their data.

My app has been used by over 300 users of the 'master' program for over 4 years.

I was just trying to do things a bit better for the latest release of my app and not have the 'master' do a reindex.

I do not want to edit the original table even though both applications will not be used together.

The 'master' table I work on is unlikely to have more than 10,000 records.

Dave,

I tried your routine but the Use exclusive at the end did not want to work

Regards

Bryan

 

[Olaf Doschke]

Hi Bryan,

Exclusive only works, if nobody is using the table. If the users are running the other application it's of course most likely this does not work. That's why you should work inside that table.

Their main concern should not be your app breaking the database, the database can be broken anyway also from the master app. Backups of the data are needed anyway.

Bye, Olaf.

 

[dgrewe]

Bryan
To Reindex a File you have to open the DBF exclusive.
If the Master reindexes the file It has it open exclusive.
If you App reindex it I must have it open exclusive, which is not much of a problem, but it also must open the Master exclusive to rebuild/replace the CDX.

I do not know of a way to do that under the constrants stated in this thread.

To get back to you original question about stripping out the index info from a CDX file. This routine is designed to replace a MEMO field in a DBF called SYSINDEX.CDXPRG. TAke what you need out of the function.

*/***************************************************************************
*# Get CDX Structure of a DBF
*/Program   : SI_CDXPRG
*/System    : Fox Library
*/Purpose   : Place cdx information in a memo field in the SYSINDEX.DBF
*/Syntax    : do sysindex
*/Returns   : nothing
*/Parameter : none
*/Defaults  : none
*/Requires  : There Must Be A Database SYSINDEX in the default directory
*/Changes   : SYSINDEX.CDXPRG FIELD
*/Calls     : Nothing
*/Version   : 1.0
*/Dated     : 12/03/96
*/Written By: David W. Grewe
*/***************************************************************************
*& Utility - Reindex maintenance
*/***************************************************************************
*/ Record Of Change
*/
*/***************************************************************************
LPARAMETERS plTest
*
* set up enviroment
*
IF  plTest
	SET STEP ON
ENDIF
*
PUBLIC lcOnError,lcDbf1,lcTXT1,lcTXT2
PUBLIC lcDbf
lcOnError = ON('ERROR')
*
SET TALK OFF
*
* open database to collect info from
*
CLOSE TABLES ALL
CLOSE DATABASES ALL
USE sysindex
GOTO TOP
SCAN ALL
*
* check to make sure your not going to open sysindex.dbf a 2nd time
*
	lcDbf = ALLTRIM(UPPER(sysindex.NAME))
	IF  lcDbf = "SYSINDEX"
		LOOP
	ENDIF
	WAIT "Working on file " + lcDbf WINDOW NOWAIT NOCLEAR
*
* define loop memvars
*

*
* open dbf and get the status to a temp file
*
	SELECT 0
	ON ERROR *
	USE (lcDbf)
*
* Create the output file
*
	DO CASE
	CASE VAL(VERSION(4)) => 7
		DO VFP_V7
	CASE VAL(VERSION(4)) => 5
		DO VFP_V5
	OTHERWISE
		DO VFP_V3
	ENDCASE
*
ENDSCAN
*
ON ERROR &lcOnError
CLEAR
WAIT  CLEAR
RETURN
*
* define local procedures
*
*************************************************************
*this is a routine that can be used if no Index ASC file is created
*
*
*
PROCEDURE VFP_V3
LOCAL lcString,lnTag,lnKey,lcClause
*
* make sure the temp files do not exist
*
lcDbf1 = lcDbf + ".DBF"
lcTXT1 = lcDbf + ".TXT"
lcTXT2 = lcDbf + ".TTX"
DELETE FILE (lcTXT1)
DELETE FILE (lcTXT2)
LIST STATUS TO FILE (lcTXT1)
USE
*
* open the temp status file and check to see if it is big enought to play with
*
lnIntxt	= FOPEN(lcTXT1 ,12)
L_SIZE 	= FSEEK(lnIntxt , 0 , 2)
IF  L_SIZE > 1
	= FSEEK(lnIntxt , 0)
	lnOuttxt = FCREATE(lcTXT2)

	ON ERROR
	DO WHILE !FEOF(lnIntxt)
		lcString = FGETS(lnIntxt)
		lcString = UPPER(ALLTRIM(lcString))
		IF  lcString = "INDEX "
			lcString = STRTRAN(lcString , "  " , " ")
			lcString = STRTRAN(lcString , "  " , " ")
			lcString = STRTRAN(lcString , "  " , " ")
			lcString = STRTRAN(lcString , " COLLATE: MACHINE" , "")
			lcString = STRTRAN(lcString , ":" , "")
			lcString = STRTRAN(lcString," KEY "," ON ")
			lcString = STRTRAN(lcString , "  " , " ")
			=FPUTS(lnOuttxt,lcString)
		ENDIF
		IF  lcString = "FILE SEARCH PATH:"
			EXIT
		ENDIF
	ENDDO
*
	= FCLOSE(lnOuttxt)
	= FCLOSE(lnIntxt)
*
* bring the output file into the database
*
	SELECT  sysindex
	APPEND MEMO sysindex.CDXPRG FROM (lcTXT2) OVERWRITE
ELSE
	= FCLOSE(lnIntxt)
ENDIF
DELETE FILE (lcTXT1)
DELETE FILE (lcTXT2)
RELEASE pnInTxt,pnOutTxt,lcString,lnTag,lnKey,lcClause
RETURN
*
*
*
PROCEDURE VFP_V5
LOCAL lcString AS CHARACTER
LOCAL lnTags AS NUMBER
*
* get all indexes on table
*
lnTags = TAGCOUNT()
FOR x = 1 TO lnTags
	lcString = ''
	IF !EMPTY(TAG(x))    && Checks for index tags
		lcString = "INDEX ON " + SYS(14,x) + " TAG "+ TAG(x) + " "
		lcFilter = SYS(2021,x)
		IF !EMPTY(lcFilter)
			lcString = lcString + "FOR "+lcFiler+" "
		ENDIF
		lcString = lcString + IIF(DESCENDING(x) , "DESCENDING " , "ASCENDING ")
		DO CASE
		CASE UNIQUE(x)
			lcString = lcString + "UNIQUE "
		CASE CANDIDATE(x)
			lcString = lcString + "CANDIDATE "
		ENDCASE
		IF x = 1
			REPLACE sysindex.CDXPRG WITH lcString+CHR(13)+CHR(10)
		ELSE
			REPLACE sysindex.CDXPRG WITH lcString+CHR(13)+CHR(10) ADDITIVE
		ENDIF
	ELSE
		EXIT
	ENDIF
ENDFOR
USE
SELECT sysindex
RELEASE lnTags,lcString,x
RETURN
*
*
*
PROCEDURE VFP_V7
LOCAL lcString AS CHARACTER
LOCAL lnTags AS NUMBER
*
* Get Tag Information In an Array
*
lnTags=ATAGINFO(laCdxTag)
IF lnTags > 0
	FOR x = 1 TO lnTags
		lcString = "INDEX ON " + laCdxTag[x,3] + " TAG " +laCdxTag[x,1] + " "
		IF !EMPTY(laCdxTag[x,4])
			lcString = lcString  + "FOR " + laCdxTag[x,4] + " "
		ENDIF
		IF !EMPTY(laCdxTag[x,5])
			lcString = lcString + laCdxTag[x,5] + " "
		ENDIF
		IF laCdxTag[x,2] <> "REGULAR"
			lcString = lcString + laCdxTag[x,2] + " "
		ENDIF
		IF x = 1
			REPLACE sysindex.CDXPRG WITH lcString+CHR(13)+CHR(10)
		ELSE
			REPLACE sysindex.CDXPRG WITH lcString+CHR(13)+CHR(10) ADDITIVE
		ENDIF
	ENDFOR
ENDIF
*
USE
SELECT sysindex
RELEASE lcString,lnTags,laCdxTag,x
RETURN

David W. Grewe Dave

 

[beedubau]

Dave,


Thanks for that code - I have collected all the cdx information now need to code for the reindex .

However,

Using the zap append you wrote above there appears to be an anomaly.

Test 1

Update a field in 'master' - I see the 3 files (cbf,fpt,cdx with the same date/time stamp.

Test 2

Using the zap append above I see a new dbf and fpt ( with the new values from the Temp file - but the cdx does not change. I tried a re index after the append but this did not change the cdx either.

Regards

Bryan

 

[beedubau]

Thanks to you guys I now have what I want.

AS each user cannot use 'master' while my app is open I have used the ZAP method followed by a recreation of the cdx.

This has worked everytime so far and I no longer need to Reindex 'master' to enable further operations in my app. It opens the subject table perfectly next time around.

To check that the process is succesful I have the following

*-----checking that the 3 files exist in the project folder  ------------------

IF FILE(cFile_dbf)

	time1 = FTIME(cFile_dbf)

	IF  FILE(cFile_fpt)

		time2 = FTIME(cFile_fpt)

		IF  FILE(cFile_cdx)

			time3 = FTIME(cFile_cdx)

			IF time3 = time2 AND time2 = time1
							
				done = 1

			ENDIF

		ENDIF

     ENDIF

ENDIF

	IF done = 1

		cMessageText = '[Commit] successful'  etc

Is there a better way to test the operation?

What if the 3 operations happen to be on a seconds changeover?

Regards

Bryan