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/srcdirectory dbfri
(work is basically stalled currently though)
This code works (in container.lpr) for reading referential integrity info from a dbc file:
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.