Question Schema analyzer

[anandknr]

Hi All,

I have to programmatically parse the schema defeniton file (.df file) to analyze various things. (For example, list all fields with HELP is missing, spell check all field names). Is there any way to acheive the same?
Basically I am planning to write a progress procedure which will parse the .df file. Before doing that, I thought, I can get a confirmation from here.

 

[TheMadDBA]

There is no API as such, especially for spell checking. You would probably be better off querying the metaschema tables (_File,_Field, etc) instead of parsing the DF file in my opinion.

You will need to filter the _File table by _File-Num... values depend on your version of Progress but should be easy to determine. Usually >= 1 and <= 32767 will work fine.

_Field._Help contains the help, _Field._Field-Name contains the field name, etc. You can check the documentation for more examples or just take a look at the contents.

FOR EACH _File NO-LOCK, 
          EACH _Field OF _File NO-LOCK:
  /*--- check your things here ---*/
END.

 

[anandknr]

Basically, I have to do this validation even before the DB creation.

 

[TheMadDBA]

Then where are you getting the DF from? Please tell me you aren't manually creating DF files.

 

[Rob Fitzpatrick]

Basically, I have to do this validation even before the DB creation.

So use the .df (without AREA phrases) to create a scratch database, run the validation program against it, and delete the database.

 

[TomBascom]

The format of the .df file is not documented.

It is unlikely that it ever will be.

The other guys are right -- load it into a scratch database and work from that.

Personally I question the value of things like HELP or VALIDATE -- it made a good demo in 1985 but it's a nightmare in real systems.

 

[Cringer]

I wrote some pcode yesterday to load a DF in and to change the areas of tables. It took me about an hour. The DF it produced out the other side was a little bad in the way it looked (but not horrendous), but it loaded fine and when I compared it against the master DB it came from after dumping it back out it was identical except for the areas. So in conclusion I think the DF parsing in Progress is pretty robust.

Here's a snippet.

DEFINE VARIABLE lv-InputFileName  AS CHARACTER NO-UNDO INITIAL "C:\Database\Live\icmasliv.df".
DEFINE VARIABLE lv-OutputFileName AS CHARACTER NO-UNDO INITIAL "C:\Database\Live\icmaslivnew.df".
DEFINE VARIABLE lv-Import         AS CHARACTER NO-UNDO.
DEFINE VARIABLE lv-i              AS INTEGER   NO-UNDO.
DEFINE VARIABLE lv-j              AS INTEGER   NO-UNDO.
DEFINE VARIABLE lv-ObjectSeq      AS INTEGER   NO-UNDO INITIAL 1.
DEFINE VARIABLE lv-PropertySeq    AS INTEGER   NO-UNDO INITIAL 1.
DEFINE VARIABLE lv-Table          AS CHARACTER NO-UNDO.
DEFINE VARIABLE lv-IndexArea      AS CHARACTER NO-UNDO.

DEFINE TEMP-TABLE tt-Object NO-UNDO 
  FIELD Sequence   AS INTEGER
  FIELD ObjectType AS CHARACTER
  FIELD ObjectName AS CHARACTER  
  FIELD TopLine    AS CHARACTER 
  INDEX idxSeq IS PRIMARY Sequence.
DEFINE TEMP-TABLE tt-Property NO-UNDO 
  FIELD Sequence       AS INTEGER
  FIELD ObjectSequence AS INTEGER 
  FIELD PropertyName   AS CHARACTER 
  FIELD PropertyValue  AS CHARACTER 
  FIELD LineValue      AS CHARACTER 
  INDEX idxSeq IS PRIMARY ObjectSequence Sequence. 
DEFINE TEMP-TABLE tt-TableArea NO-UNDO 
  FIELD Area        AS CHARACTER 
  FIELD TABLEName   AS CHARACTER 
  FIELD SizeK       AS CHARACTER 
  FIELD SIZE        AS CHARACTER 
  FIELD RecordNo    AS CHARACTER 
  FIELD MinSize     AS CHARACTER 
  FIELD MaxSize     AS CHARACTER 
  FIELD AvSize      AS CHARACTER 
  FIELD OptRecsBlk  AS CHARACTER 
  FIELD Col1        AS CHARACTER 
  FIELD StorageArea AS CHARACTER 
  FIELD Counter     AS CHARACTER 
  FIELD Difference  AS CHARACTER 
  FIELD Frag        AS CHARACTER .
DEFINE BUFFER ParentTable FOR tt-Object. 
DEFINE BUFFER ParentTableArea FOR tt-Property.
DEFINE STREAM s-in. 
DEFINE STREAM s-out. 

INPUT STREAM s-in FROM VALUE(lv-InputFileName).
REPEAT:
  IMPORT STREAM s-in UNFORMATTED lv-Import. 
  CASE TRUE:
    WHEN ENTRY(1,lv-Import," ") EQ "ADD" THEN 
      DO:
        CREATE tt-Object. 
        ASSIGN 
          tt-Object.Sequence   = lv-ObjectSeq
          tt-Object.ObjectName = TRIM(ENTRY(3,lv-Import," "),"~"")
          tt-Object.TopLine    = lv-Import
          tt-Object.ObjectType = ENTRY(2,lv-Import," ")
          lv-ObjectSeq         = lv-ObjectSeq + 1.
      END.  
    OTHERWISE 
    DO:
      CREATE tt-Property. 
      ASSIGN 
        tt-Property.ObjectSequence = tt-Object.Sequence
        tt-Property.Sequence       = lv-PropertySeq
        tt-Property.PropertyName   = TRIM(ENTRY(1,TRIM(lv-Import)," "))
        tt-Property.PropertyValue  = TRIM(lv-Import)
        tt-Property.LineValue      = lv-Import
        lv-PropertySeq             = lv-PropertySeq + 1.
      ASSIGN 
        tt-Property.PropertyValue = TRIM(REPLACE(tt-Property.PropertyValue,tt-Property.PropertyName,"")) 
        WHEN LENGTH(tt-Property.PropertyName) GT 0.
    END. 
  END CASE.
END. 

INPUT STREAM s-in CLOSE. 

OUTPUT STREAM s-out TO value(lv-OutputFileName).
FOR EACH tt-Object:
  PUT STREAM s-out UNFORMATTED tt-Object.TopLine SKIP.
  FOR EACH tt-Property
    WHERE tt-Property.ObjectSequence EQ tt-Object.Sequence:
    PUT STREAM s-out UNFORMATTED tt-Property.LineValue SKIP.
  END. 
END. 
OUTPUT STREAM s-out CLOSE.

It might not work with all DFs, but it'll get pretty darn close IMO.

 

[tamhas]

Note that ABL2DB includes a routine which reads a .df and builds a shadow schema of another database. It would be easy to adapt that to just about anything related to a .df.

 

[anandknr]

Thanks all. Thanks Cringer, I got a starting point.