The LA Fox Developer Newsletter

June 1998

And The Award for “Longest Title” Goes To...

Rebuilding Your Index Tags

from Scratch Without Losing

Your Primary Key, Candidate

Keys, or Persistent Relations

by Christian Berngan

[Ed. Note: Christian Berrigan is an independent database

consultant. His company, Spartacus Development, provides

custom database solutions using FoxPro and Visual FoxPro,

including dynamic databased web sites using West Wind’s

Web Connection. Hosting for VFP/Web Connection driven web

sites is also available.]

Rebuilding a .cdx from scratch (DELETE TAG ALL, then

rebuilding each tag) will delete all your persistent relationships

in the .dbc, and if you haven’t stored your PRIMARY and

CANDIDATE index metadata, as well as testing for it when

rebuilding your index tags, all your index tags will be type

‘Regular’.

At first, it’s easy to just think we’ll rebuild index tags without

deleting them first. But sooner or later, we know we will have to

rebuild them from scratch. Here’s some ideas about how to

solve this challenge.

Assumes you already have a table of metadata, including

**

index tag names, index expressions and if

-

an index is a primary or candidate index

BEFORE YOU DELETE TAGS AND REINDEX, CAPTURE THE RELATIONS

IN THE .DBC SO YOU CAN RESTORE THEM LATER.

USE myDatabase.DBC AGAIN SHARED IN 0 ALIAS zDBC

SELECT 0, RECNO() AS ‘RECORDNO’;

FROM zDBC;

WHERE UPPER(ALLTRIM(objecttype)) == ‘RELATION’;

AND !DELETEDO;

INTO CURSOR zRels

PUT YOUR REINDEXING METHOD HERE AND INCLUDE THE FOLLOWING

CODE:

**

alndexExpr is an array SELECTed from the metadata file where

Columni = TAG NAME [from TAGO]

Column2 = INDEX EXPRESSION [from SYS(14) and SYS(2021)]

plus a ## tag indicating if the index is a

##CANDIDATE## [from CANDIDATEO]

or ##PRIMARY## key [from PRIMARY()]

IcTabte is already USED EXCLUSIVELY and SELECTed

DELETE TAG ALL

FOR x=1 TO ALEN(alndexExpr,1)

&& rebuild each tag

m.clndExpr = ALLTRIM(alndexExpr[x,2])

DO CASE

CASE ‘##PRIMARY##’ $ m.clndExpr

m.clndExpr = STRTRAN(m.clndExpr,’##PRIMARY##’,”)

IcExpr = (ALLTRIM(alndexExpr(x,1]))

ALTER TABLE (IcTable);

ADD PRIMARY KEY &IcExpr TAG (ALLTRIM(alndexExpr(x,1]))

CASE ‘##CANDIDATE##’ $ m.clndExpr

m.clndExpr = STRTRAN(m.clndExpr,’##CANDIDATE##’,”)

&clndExpr CANDIDATE

OTHERWISE

&clndExpr

ENDCASE

ENDFOR

FINISH YOUR REINDEXING METHOD HERE

**

now recall the deleted relations in the dbc

**

(this example assumes SET DELETED ON)

SELECT zRels

SET DELETED OFF

**

.dbc was already opened ALIAS zDBC earlier

SCAN

GO (zRels.RecordNo) IN zDBC

IF DELETED(’zDBC’)

SELECT (‘zDBC’)

RECALL NEXT I

SELECT zRels

ENDIF

ENDSCAN

USE IN zDBC

USE IN zRels

SET DELETED ON

**

end

That does it! The table(s) in the DBC have had their CDX rebuilt

from scratch, and they all still have their PRIMARY and CAN Dl-

DATE keys, and all their persistent relations. I haven’t tested it,

but believe this will also preserve the associated Relational

Integrity code and properties.

VFP Web to Data (Con’t from page 6)

some of my test applications, and the latter helped me add

forms and reports to my various applications.

Version 6.0 of Visual FoxPro offers developers a graphical and

extensible component gallery that makes organizing and using

the various class libraries a breeze.

Also, windows in the Visual FoxPro development environment

are dockable to the toolbar, which quickly frees up screen

space.

During my development I also used the new Coverage Profiler, a

tool that checks which lines of code are actually being executed

as well as showing where potential performance troubles may

exist. To check backward compatibility, I used this version to

work with some projects I had created using Version 5.0 of

Visual FoxPro. I was able to recompile my old code without any

hiccups.

Although Visual FoxPro contains numerous worthwhile new

features and enhancements, Microsoft needs to iron out the

beta issues prior to general release. If it is delivered as prom-

ised, Visual FoxPro is a good bet as an upgrade, and is viable

(Con’t, page 8)

Page 7