Referential Integrity vs multiple inheritance -- a thorny problem 4

[alan232]

Hi All;

When merging two databases, I've run across a thorny problem. It goes like this:

At the topmost level of the database, I'm using a 'ledger' file to have unique ledger accounts--system assigned with sys(2015). This ledger file then has its primary key, caccount, cascaded down to multiple files, in particular, 'people', 'company', 'policies', and 'billing'. caccount is the primary key in people, company, and policies, but is a referenced key as cpayor in the billing file. This arrangement has worked fine over the years, successfully letting the billing file 'bill' whichever payor was referenced in the cpayor field.

However, when 'merging' two databases from seperate offices, there has been occasions when one or more of the accounts --people, company, or policy--has to be moved to a different ledger account. That's not a problem directly, except when the child record in people, company, or policy, is moved from its original parent ledger, it leaves that ledger record and the billing record that references the ledger record meaningless.

I can think of no way that changes in the primary key, caccount, of either people, company, or policy, can be autmatically cascaded to the single cpayor field of zbilling, nor does it make sense when changing the primary key of a child record to trace up the tree to its original parent and change its primary key as well.

The only solution I can think of is to expand the cpayor field in zbilling into three seperate fields, one for each type of account, but then how would I 'insert' a billing record where these three fields are restricted to legitamate values without using a 'blank' record in people.dbf, company.dbf, or policy.dbf? Or, is there a way of reversing this somehow and make people, company, AND policy all parents of the single caccount entry in ledgers.dbf?

I'm open to any and all suggestions!
Thanks,
ajw

 

[alan232]

Hi All;

What do you all think of this solution:

The problem, as I see it, is that the parent ledger file is using the identical primary key's as the account files, people, companies, and policies. Although only one account in one file is identical to a ledger, they are essentially forming one record across two files. Hence, changing the primary key on the account record basically invalidates the ledger record and whatever other children it has.

So what if, rather then changing the billing file in someway, I simply add a new field to each of the account files and use this field to tie to the parent ledger record, rather then letting the ledger record tie to the primary key of the account record? Could I not preserve my referential integrity of cascade, cascade, restrict?

(I can see many hours of reprograming the accompanying software!!)

Any ideas anybody?
Thanks,
ajw

 

[alan232]

Thanks Mike;

I wish I had thought about this years ago when first setting this up. I'm using vfp 6. I don't know about the newer versions, but my reading of the generated referential integrity triggers seems to show that two or more field combinations do not cascade properly (automatically) down the chain. Hence, I'm stuck with one field (not really a problem though).

I figure the best way to catch the code (which is going to fail) when I add the additional field to each of the account files--policies, company, people--is to change what is currently being used as the primary key on the account files to different values from the parent ledger file. (sys(2015). I'm guessing (hoping) that any view or other relationship that depends on the original primary key will fail--thus showing up the code I need to change.

If there is a better way, I sure could use it!
Thanks again,
Alan

(Just curious, is there a way of adding a trigger to the database field on the account files so that any read/write access to this field forces an immediate programming stop? Or something similiar? This would help in finding the parts of the program that need correcting.)

 

[alvechurchdata]

adding a trigger to the database field on the account files so that any read/write access to this field forces an immediate programming stop?

A SUSPEND or CANCEL in the Update trigger for each table would trap the writes but the reads are more difficult. You could open a second instance of Fox and open all tables EXCLUSIVE before you start but that would tell you when a table was opened, not when the user actually looked at the information.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[alan232]

Thanks alvechurchdata;

The update triggers, so far, are being generated by the RI builder--how do I tell it to put a SUSPEND in place?

Thanks again,
Alan

 

[alvechurchdata]

If you go to the third tab of the Table Designer you'll see three boxes for Insert, Update and Delete triggers. Each can hold a call to a function and you could set all three to call a function which holds a SUSPEND.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[alan232]

Thanks Geoff;

So...I guess I could let vfp build the triggers, as it now does, then go back to the table designer and replace the trigger with a function that first calls a suspend, then goes on to call the default trigger?

Thanks again, any ideas on views?
Alan

 

[Olaf Doschke]

Hi alan

Then you would need a seperate __ri_suspend_update_tablename() for each table. Much easier would be one procedure:

procedure __ri_suspend()
  if type("_ridebug")="L" and _ridebug
     set step on
  endif
  return .t.
endproc

And then change the update trigger to:

__ri_suspend() .AND. __ri_update...

Now you can switch on or off this debug mode by setting a global var _ridebug:

public _ridebug
_ridebug = .t.
* or off
_ridebug = .f.

Even simpler would be modifying the riupdate() procedure that is called by each __ri_update..., then you would not even need to change the trigger call for each table.

Bye, Olaf.

 

[Olaf Doschke]

unfortunately riupdate() is not a central function always called from each __ri_update...() procedure, so forget my last recommendation.

If you use other RI builders, hook calls like _ri_begin() are generally available and even such a debug mechanism is already built in.

Bye, Olaf.

 

[alan232]

Hi Olaf:

Good idea...I may not be able to use it easily, but it is manageable. I'll see if I can work it out.

I am thinking though, the basic problem is much deeper then table write's or updates--i.e., I need to catch the code USING the data. i.e., reads. I'm thinking the only 'trigger' that will work is the basic error function. i.e., change the field names on the fields with different meanings. Hence, any existing code will (I hope) bomb out.

This is not going to be fun (the code was developed over the last 12 years, before MS owned vfp.) I need to be finished before Monday...(I see a long weekend ahead).

Thanks, Olaf. Good idea!
Alan

 

[alvechurchdata]

I see a long weekend ahead

Sorry to hear that - my weekend involves a christmas tree, decorations, the buying of presents and the visiting of relatives. I think I'd rather be coding.

On a more positive note - if there are only a few fields to check could you use the Code References tools to search for every time that the field is mentioned in code?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[alan232]

Hi Geoff;

Um..Don't know. What Code References tools do you refer to?

With some thought, it turns out that the parent file, zledger, cascades its primary field, caccount, down through about 8 files, five of which are also using caccount as their primary key--not good database design. I think I did that for simplicity at the time...not smart.

So I only really have the one field, caccount, to worry about.

(I went shopping last week--I'm having better luck with the code!)

Thanks again for your help,
Alan

 

[alvechurchdata]

What Code References tools do you refer to

It's on the Tools menu in VFP 8 and will pick up all the references to a string within a project or folder. You get a list showing the name of the file, the name and line number of the procedure or method and the line of code itself. Double-click to open the editor on any of the references. You can even do a global search-and-replace.

I reckon it's the best maintenance tool in VFP since I don't know when.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[alan232]

Hi Geoff;

Thanks..it sounds great...(sad)..I'm still with vfp 6.0 (no tools that I'm aware of)...I guess I could do a library print out and catch it that way...Oh well.

Thanks again,
Alan

 

[JimWWinter]

Alan,

Steve Dingle has a free search tool that works with VFP6. You can get it at

http://www.dsbusinesssolutions.co.uk/products.html.

Regards,
Jim

 

[alan232]

Hi Jim;

Thanks much ...(happy!). Sorry for the delay--I've been rewriting much code..I changed the field names on the database..ugh. Just out of curiousity, I'm wandering if I could set some kind of trap in the debugger that would catch reads?

Thansk again,
Alan

 

[Olaf Doschke]

To catch reads must be very low level. Some encryption tools manage to bind to events of opening and reading from dbf files, so it should be possible, but there is no native foxpro way to do it with eg triggers.

The best way may be database events, especially OpenTable(), but these events were introduced with vfp7, unfortunately there is no such thing for vfp6.