VFP. Re-creating indexes

Question

I would like to be able to re-create the index to a table which is part of a database.

The index to the table is corrupted, so the table cannot be opened (certainly not without an error message). I would like to use the information in the database container to reconstruct the index (with its several tags)

Perhaps I could offer the user a facility to browse a list of tables in the database and then choose one table (or maybe all) to be re-indexed or packed. Have looked at DBGETPROP() and CURSORGETPROP() function calls, but have not found options which give me the information I need.

As always, grateful for guidance.

Thanks Tamar. Yes, I had been opening the .dbc, but then wanted the tag names and index expressions.

What I would like to do is build up a cursor with details of the tag names and index expressions for each table in the database.

As far as I can see I need to get the names of the tables from the .dbc and then open each table to find its indexes . I think these are only in the tables themselves not in the .dbc. Can see that the SYS(14) function will give me the index expressions and that TAG() will give me the tag names; Is that the way to go, or is there some function already available which will do these things for me?

score 2 · Accepted Answer · answered Aug 30 '16 at 20:26

2

The database container is a table, so you can open it with USE to read the data there.

answered Aug 30 '16 at 20:26

Tamar E. Granor

3,817
1
21
29

Thanks. I have responded by editing the question to seek further clarification. – Andrew_46 Aug 31 '16 at 12:37

score 0 · Answer 2 · answered Sep 01 '16 at 10:09

You shouldn't rely on dbc to find and to do anything afterwards. It doesn't have the index information anyway (except for some like PK, field is indexed etc which is not much of a value). Sometimes, opening the database exclusively (and have access to its tables exclusively) and then doing a:

validate database recover

helps but you can't rely on it either. To correctly recreate the indexes, you have to delete them all (aka delete the CDX files) and then recreate from scratch.

If it helps, below is the utility code I have written and using in "repair" in case needed. It creates a kind of data dictionary for both Dbc and free tables:

* CreateDictionary.prg
* Author: Cetin Basoz
* CreateDictionary('c:\mypath\v210\data','','zipcodes,states')
* Creates DataDictionary files in 'DataDic' directory (Default if not specified)
* using 'c:\mypath\v210\data' dir as source data dir
* adds zipcodes and states.dbf as static files (with data as is)
* tcUserStatic - tables that should be kept on user as is
Lparameters tcDataDir, tcDicDir, tcStaticList, tcUserStaticList
tcDataDir = iif(type('tcDataDir')='C' and directory(addbs(m.tcDataDir)), addbs(m.tcDataDir), sys(5)+curdir())
tcDicDir = iif(type('tcDicDir')='C' and !empty(m.tcDicDir), addbs(m.tcDicDir), 'DataDic\')
tcStaticList = iif(Type('tcStaticList')='C',trim(m.tcStaticList),'')
If !directory(justpath(m.tcDicDir))
    Md (justpath(m.tcDicDir))
Endif
Close data all
lnDatabases = adir(arrDBC,m.tcDataDir+'*.dbc')
Create table (m.tcDicDir+'DBCreator') (DBCName M nocptrans, FileBin M nocptrans, Filename c(128) nocptrans)
for ix = 1 to m.lnDatabases
    open data (m.tcDataDir+arrDBC[m.ix,1])
    do home()+'Tools\Gendbc\gendbc' with forceext(m.tcDicDir+arrDBC[m.ix,1],'PRG')
    compile (forceext(m.tcDicDir+arrDBC[m.ix,1],'PRG'))
    insert into (m.tcDicDir+'DBCreator') ;
        values (arrDBC[m.ix,1], ;
        FileToStr(forceext(m.tcDicDir+arrDBC[m.ix,1],'FXP')), ;
        forceext(arrDBC[m.ix,1],'FXP'))
    erase (forceext(m.tcDicDir+arrDBC[m.ix,1],'PRG'))
    erase (forceext(m.tcDicDir+arrDBC[m.ix,1],'FXP'))
    if file(forceext(m.tcDicDir+arrDBC[m.ix,1],'KRT'))
        insert into (m.tcDicDir+'DBCreator') ;
            values (arrDBC[m.ix,1], ;
            FileToStr(forceext(m.tcDicDir+arrDBC[m.ix,1],'KRT')),;
            forceext(arrDBC[m.ix,1],'KRT'))
        erase (forceext(m.tcDicDir+arrDBC[m.ix,1],'KRT'))
    endif   
endfor
Close data all

Create cursor crsSTRUCTS ;
    (FIELD_NAME C(128) nocptrans, ;
    FIELD_TYPE C(1), ;
    FIELD_LEN N(3, 0), ;
    FIELD_DEC N(3, 0), ;
    FIELD_NULL L, ;
    FIELD_NOCP L, ;
    _TABLENAME M nocptrans)
Create cursor crsINDEXES ;
    (TAG_NAME C(10) nocptrans, ;
    KEY_EXPR M, ;
    NDXTYPE C(1), ;
    IS_DESC L, ;
    FILTEREXPR M nocptrans, ;
    _TABLENAME M nocptrans)
Select 0
lnTables = adir(arrTables,m.tcDataDir+'*.dbf')
For ix=1 to m.lnTables
    Use (m.tcDataDir+arrTables[m.ix,1])
    if empty(cursorgetprop('Database'))
        lnFields=afields(arrStruc)
        For jx=1 to m.lnFields
            arrStruc[m.jx,7]=arrTables[m.ix,1]
        Endfor
        Insert into crsSTRUCTS from array arrStruc
        Release arrStruc
        If tagcount()>0
            Dimension arrIndexes[tagcount(),6]
            For jx=1 to tagcount()
                arrIndexes[m.jx,1] = tag(m.jx)
                arrIndexes[m.jx,2] = key(m.jx)
                arrIndexes[m.jx,3] = iif(Primary(m.jx),'P',iif(Candidate(m.jx),'C',iif(unique(m.jx),'U','R')))
                arrIndexes[m.jx,4] = descending(m.jx)
                arrIndexes[m.jx,5] = sys(2021,m.jx)
                arrIndexes[m.jx,6] = arrTables[m.ix,1]
            Endfor
            Insert into crsINDEXES from array arrIndexes
        Endif
    endif
    Use
Endfor
Select crsSTRUCTS
Copy to (m.tcDicDir+'NewStruc')
Select crsINDEXES
Copy to (m.tcDicDir+'NewIndexes')

Create table (m.tcDicDir+'static') (FileName M nocptrans, FileBin M nocptrans)
If !empty(m.tcStaticList)
    lnStatic = alines(arrStatic,chrtran(m.tcStaticList,',',chr(13)))
    For ix = 1 to m.lnStatic
        lnFiles = adir(arrFiles,m.tcDataDir+trim(arrStatic[m.ix])+'.*')
        For jx=1 to m.lnFiles
            If inlist(justext(arrFiles[m.jx,1]),'DBF','CDX','FPT')
                Insert into (m.tcDicDir+'static') values ;
                    (arrFiles[m.jx,1], FileToStr(m.tcDataDir+arrFiles[m.jx,1]))
            Endif
        Endfor
        Release arrFiles
    Endfor
Endif

CREATE TABLE (m.tcDicDir+'userstatic') (FileName c(50))
If !empty(m.tcUserStaticList)
    lnUserStatic = alines(arrUserStatic,chrtran(m.tcUserStaticList,',',chr(13)))
    For ix = 1 to m.lnUserStatic
        lnFiles = adir(arrFiles,m.tcDataDir+trim(arrUserStatic[m.ix])+'.*')
        For jx=1 to m.lnFiles
            If inlist(justext(arrFiles[m.jx,1]),'DBF','CDX','FPT')
                Insert into (m.tcDicDir+'userstatic') values (arrFiles[m.jx,1])
            Endif
        Endfor
        Release arrFiles
    Endfor
Endif
close data all
close tables all

VFP. Re-creating indexes

2 Answers2