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

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:

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

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

SELECT zRels

SET DELETED OFF

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

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 DlDATE 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 promised, Visual FoxPro is a good bet as an upgrade, and is viable

(Con’t, page 8)

Page 7