Re: Compound PK/FK fun

Hi Hugi,

I have no idea if this will work, but try the following:

* in Cayenne Modeler, add companyNumber (company_number) as an attribute
for Company.
* Regenerate classes.
* In Company.java, override the setCompanyNumber method to check for
nulls.  If non-null, call the super; if null, ignore it.


On Mon, Oct 21, 2019 at 7:19 AM Hugi Thordarson <[email protected]> wrote:

> Hi all.
>
> I'm currently in the progress of cleaning up an older system and I have a
> bit of a conundrum that I would love some input on.
>
> Almost all data in the DB belongs to/is related to a "Company" object. The
> PK of that company object forms part of a compound PK for the other objects.
> For example; here "Customer" and "Invoice" are related to "Company" on the
> "company_number" attribute, "Invoice" has a related "Customer" joined on
> "company_number" and "customer_number".
>
> Company
> ------------------------
> * company_number (pk)
>
> Customer
> ------------------------
> * company_number (pk)
> * customer_number (pk
>
> Invoice
> ------------------------
> * company_number (pk)
> * invoice_number (pk)
> * customer_number
>
>
> Now, I've modeled all of this in Cayenne and it's been working very well
> for years for reporting purposes. However, we're now moving the "writing"
> part of the system to Cayenne as well, and that's caused a small problem.
>
> For the most part it works very well. An object will never change
> companies once created, so if I set a "Customer" on an an "Invoice", it
> doesn't have any undesired side effects, it's essentially just setting the
> same company number again—no harm in that.
> However, a problem occurs when I set an Invoice's customer to null. In
> that case, Cayenne will nullify both the invoice's customer_number AND
> company_number, which is obviously problematic.
>
> I'm now wondering how I can solve this problem, i.e. how can I set an
> invoice's customer to "null" without blowing up the system.
>
> I'm open to "dirty" solutions since this is a temporary situation. The
> Cayenne version of the system just has to work alongside an older system
> for a couple of months, but once that's been closed, I have full control of
> the DB and these tables will be getting their own shiny new unique PKs.
>
> About the only thing I don't want to do is expose the FKs and/or handle
> relationships using manual fetching, since the relatiosnhips are already
> modeled and I have thousands of lines of logic that depend on the modeled
> relationships.
>
> Any ideas?
>
> Cheers,
> - hugi