Visual FoxPro dbc container format
lazarus / 16-Databases

----------------------------------------------------------------------
BigChimp · 2014-11-06 11:49

I've been fiddling on and off with adding support for long field/table
names and referential integrity to TDBF by using Visual FoxPro
containers (.dbc files - basically dbf files - and associated memo
files).

See https://bitbucket.org/reiniero/fpc_laz_patch_playground/src
directory dbfri
(work is basically stalled currently though)

This code works (in container.lpr) for reading referential integrity
info from a dbc file:
Code: [Select]procedure TMain.ReadVFPRefInt(SourceDir,DBCFile:
string);
var
  Cont: TDBF;
  Ref: TRelation;
  Refs: array of TRelation;
  Action, i:integer;
  IntegrityType: TIntegrityType;
begin
  // We need a reference list for object IDs=>tables
  if not(FTablesRead) then
    ReadVFPTables(SourceDir,DBCFile);
  //todo: perhaps do the same check for fields?
  SetLength(Refs,0);
  Cont:=TDBF.Create(nil);
  try
    Cont.FilePathFull := sourcedir;
    Cont.TableName:=DBCFile;
    //todo: debug remove for production unless we do caching
    Cont.ReadOnly:=true; //demo file is readonly
    Cont.Open;

    while not(Cont.Eof) do
    begin
      if not(Cont.Fields.FieldByName('RIINFO').IsNull) then
      begin
        SetLength(Refs,Length(Refs)+1);
        // Parent of this relation object should be a table object
       
Ref.Table:=GetTable(Cont.Fields.FieldByName('PARENTID').AsInteger);
        // assumed format of PROPERTY field for RI records:
        // <control codes>child field index tag<control
codes>master/foreign table<control codes>master/foreign fields index
        // see http://www.foxite.com/archives/adding-stored-
procedures-to-a-dbc-0000132195.htm
        //todo: add write support; move http ref to readme
        // We're equating fields with indexes here which may work but
won't necessarily
        // However, no support for encrypted cdx indexes so it's the
best we can do??
        Ref.Fields:=ExtractWord(1,Cont.Fields.FieldByName('PROPERTY').
AsString,StdWordDelims);
        Ref.MasterTable:=ExtractWord(2,Cont.Fields.FieldByName('PROPER
TY').AsString,StdWordDelims);
        Ref.MasterFields:=ExtractWord(3,Cont.Fields.FieldByName('PROPE
RTY').AsString,StdWordDelims);
        // Actions in order UpdateAction, DeleteAction, InsertAction
        // Codes: I=Ignore, C=Cascade, R=Restrict
        for Action:=1 to 3 do
        begin
          case
copy(Cont.Fields.FieldByName('RIINFO').AsString,Action,1) of
          'C': {Cascade delete/update}
            IntegrityType:=itCascade;
          'R': {Restrict to key value}
            IntegrityType:=itRestrict;
          else {I Ignore, anything else}
            IntegrityType:=itIgnore;
          end;
          case Action of
          1: Ref.UpdateAction:=IntegrityType;
          2: Ref.DeleteAction:=IntegrityType;
          3: Ref.InsertAction:=IntegrityType;
          else raise Exception.create('Unknown action. Fix the TDBF
code.');
          end;
        end;
        Refs[Length(Refs)-1]:=Ref; //array starts with 0
      end;
      Cont.Next;
    end;
    Cont.Close;
  finally
    Cont.Free;
  end;
  //todo: debug output
  for i:=low(Refs) to high(Refs) do
  begin
    writeln(Refs[i].Table + '.' +
      Refs[i].Fields + '=>' +
      Refs[i].MasterTable + '.' +
      Refs[i].MasterFields);
    writeln('Update: ',Refs[i].UpdateAction);
    writeln('Delete: ',Refs[i].DeleteAction);
    writeln('Insert: ',Refs[i].InsertAction);
  end;
end;

As the code indicates however, there are some control characters
(<ASCII 32) included in the referential integrity record.

I've not been able to find any reference and wonder whether anybody
can give some more information.

Thanks.

----------------------------------------------------------------------
wbeppler · 2014-11-06 16:18

hi,

i'm ex-fox-programmer changing to lazarus.

i can not understand your problem but look to the Objectinspector to
DBF1.TableLevel
if your database container are newer kind set it to 30

at my linuxes (all versions) made with laz 1.2.6 all works fine (but i
think older laz are ok too)

if you could explain your problem to me please use english for stupids
or german

best regards
wolfgang

----------------------------------------------------------------------
BigChimp · 2014-11-06 17:07

Hallo Wolfgang,

Thanks for your help!

Sorry, my written German is very poor. Please do not hesitate to reply
with questions about my English below.

Yes, I know about table level 30. (I have helped implement improved
support for table level 30 in FPC).

I'm reading the actual .dbc file and the .dct (really a memo file)
that goes with it. There are no problems reading these files.

In the memo file, in the records for referential integrity, I find
some unexpected bytes. I want to know what these bytes mean - this is
e.g. useful for adding write support for .dbc files.

Sample file: e.g. w32.dbc and w32.dct from
http://www.news2news.com/vfp/?generic=Project#apiviewer

----------------------------------------------------------------------
BigChimp · 2014-11-07 10:12

The relevant field with control characters is OBJECTNAME.

 Output for relevant records for w32.dbc in sample program:

(The #... notation are hex codes for non-printable characters)
Code: [Select]Field OBJECTID has value: 24
Field PARENTID has value: 18
Field OBJECTTYPE has value: Relation
Field OBJECTNAME has value: Relation 1
functionid#0#21#0#0#0#1#0#18w32_functions#0#18#0#0#0#1#0#19functionid#
0
Field CODE is          ***NULL***
Field RIINFO has value: ICI
Field USER is          ***NULL***
and
Code: [Select]Field OBJECTID has value: 25
Field PARENTID has value: 18
Field OBJECTTYPE has value: Relation
Field OBJECTNAME has value: Relation 2
exampleid#0#20#0#0#0#1#0#18w32_examples#0#17#0#0#0#1#0#19exampleid#0
Field CODE is          ***NULL***
Field RIINFO has value: ICI
Field USER is          ***NULL***