Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 9 of 9 total
Thread How to find circular references
Thu, Sep 17 2015 6:58 PMPermanent Link

Adam H.

Just wondering if anyone has come across the need for this before, and
if so if you have a solution?

Let's say I have a table with fields:

CompanyID
ParentCompanyID


I want to detect circular references and exclude them from being an option.

ie:

CompanyID   ParentCompanyID
2      1
3      2
4      3


.....is OK,

but

CompanyID ParentCompanyID
2 1
1 2

Is not OK - Ciruclar refrence.

This part is easy, but I get into trouble when I'm wanting to detect
more complex circular references, such as:


CompanyID ParentCompanyID
1 4
2 1
3 2
4 3

, etc..

Can anyone advise of the best approach with this?

Thanks & Regards

Adam.
Fri, Sep 18 2015 2:35 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Adam


Quick google

http://dba.stackexchange.com/questions/45158/how-to-write-a-query-which-finds-all-circular-references-when-a-table-references

http://stackoverflow.com/questions/7540760/detecting-circular-references-in-sql

Essentially you're going to have to do some recursion and walk back up the relationship tree. Never a pleasant job in sql

Roy Lambert
Fri, Sep 18 2015 2:43 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Adam


Another thought - you can do it with SQL/PSM but it may well be easier to write an external function in Delphi.

Roy Lambert
Fri, Sep 18 2015 9:42 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Adam,

<< I want to detect circular references and exclude them from being an option. >>

DBISAM's SQL implementation isn't going to be able to handle this, so you're going to need to write some navigational code to handle this.

Are you wanting to do this before inserts/updates in order to prevent them ?

Tim Young
Elevate Software
www.elevatesoft.com
Fri, Sep 18 2015 10:46 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Tim / Adam


This post has made me realise my brain is going - I thought it was ElevateDB - sorry.

I think I could do something using engine customization but if you can do it in your app as part of creating the SQL to insert / update a record I think it would be easier to go that route. All you need is a simple recursive function such as the ones I use for tree building.


Roy Lambert
Sun, Sep 20 2015 7:41 PMPermanent Link

Adam H.

Hi Roy & Tim,

Outside of SQL will be fine if it's going to be more simpler. (Or the
only possible option) Smile

My brain is going as well Roy - would you be able to give me a hint as
in regards to the tree building function you have. Smile

Cheers

Adam.

Mon, Sep 21 2015 4:30 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Adam

I've attached what I use for building my email trees - it uses ElevateDB but should be OK for DBISAM.



Roy Lambert





Attachments: emTree.pas
Mon, Sep 21 2015 7:28 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Adam


Had a think about it and this might do you

procedure TForm1.BandABeforePost(DataSet: TDataSet);
begin
if DisallowedRelationship(BandA.FieldByName('_BelongsTo').AsInteger, BandA.FieldByName('_BoxNo').AsInteger, Checker)
 then DatabaseError('Woops');
end;

function TForm1.DisallowedRelationship(Check, Blocked: integer; Qry: TDBISAMQuery): boolean;
begin
if Check = Blocked then begin
 Result := True;
 Exit;
end;
Qry.Close;
if not Qry.Prepared then Qry.Prepare;
Qry.ParamByName('Company').AsInteger := Check;
Qry.ExecSQL;
Result := Qry.RecordCount > 0;
if Result then begin
 Result := Qry.Fields[1].AsInteger = Blocked;
 if not Result then
  Result := DisallowedRelationship(Qry.Fields[1].AsInteger, Blocked, Qry);
end;
end;


You need a TDBISAMQuery on the form with this

SELECT _CompanyID, _ParentCompanyID FROM "whatever your table name is" WHERE _CompanyID = :Company

SQL

You can dynamically create the query each time inside DisallowedRelationship, and if you're not doing a lot of these that's the way I'd go

Roy Lambert
Mon, Sep 21 2015 6:28 PMPermanent Link

Adam H.

Hi Roy,

Thanks very much - I think I get the idea now!

I'll give this approach a go and see what happens. Appreciate your help!

Cheers

Adam.
Image