Recent

Author Topic: Visual FoxPro dbc container format  (Read 11497 times)

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Visual FoxPro dbc container format
« on: November 06, 2014, 12:49:46 pm »
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('PROPERTY').AsString,StdWordDelims);
        Ref.MasterFields:=ExtractWord(3,Cont.Fields.FieldByName('PROPERTY').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.
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

wbeppler

  • New member
  • *
  • Posts: 9
Re: Visual FoxPro dbc container format
« Reply #1 on: November 06, 2014, 05:18:20 pm »
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

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Visual FoxPro dbc container format
« Reply #2 on: November 06, 2014, 06:07:09 pm »
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
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Visual FoxPro dbc container format
« Reply #3 on: November 07, 2014, 11:12:28 am »
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***
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified