Re: Multicolumn primary keys + relationships

[prev] [thread] [next] [Date index for 2005/02/24]

--0-669808199-1109235582=:70564
Content-Type: text/plain; charset=us-ascii
Content-Id: 
Content-Disposition: inline

I modified my CDBI::RElationship::MightHave.pm file to support multi column
fks. It was really simple. Just a few lines added and one changed.  Usage is
different in that you specify an extra hashref arg of your Key names and values
after the qw/.../ arg.  The col in the might_have that stores the owner's PK
gets a special key name , '_FK_'.  

So traditional usage was:
Music::CD->might_have(liner_notes => LinerNotes => qw/notes/);

My version you can still say that or you can say:
Music::CD->might_have(front_notes => LinerNotes => qw/notes/,
                    {  _FK_ => 'cd_id', type => 'front' }
);
Music::CD->might_have(back_notes => LinerNotes => qw//, # note blank qw
                     { _FK_ => 'cd_id',  type => 'back' }
);

I haven't tested this a whole lot. just what i needed it for which was mainly
creating the might_have objects automatically from a cd form that had the
inputs for front and back liner notes on it.  One function --
update_create_all_from_cgi -- creates every might_have, has_a, and has_one (my
own rel.) object and relates it to the main object. I'm cleaning that up now.
One caveat is   Multi keyed object may have trouble might having another Multi
keyed object. But it should be easy to fix that too.

If you try it please let me know if you find any bad side effects of my changes
or if you have any ideas.

thanks




--- Crossfire <xfire-cdbi@xxxxx.xx> wrote:

> I'm relatively new to CDBI, and I've become aware that relationships
> using a multicolumn primary key currently are not supported.
> 
> my current db schema has quite a few places where I use a 2-3 column
> primary key, and then use that (with matching foreign key) to refer to
> a parent table...
> 
> From what I've been able to deduct from the documentation, CDBI
> assumes that you only have a single value key if you want to
> use a relationship of any kind.
> 
> Now, I had a look, and attempts to use might_have to link these tables
> failed...
> 
> I had a quick peek, and I would appreciate if anybody can confirm if
> this idea will work or not, if it will break anything major internally
> (nevermind user code just yet - I don't know the gritty details of
> exactly how much carnage a change like this might cause) and if its a
> reasonable idea or not.
> 
> The idea is to to change ->retrieve() to support hashrefs + change
> CDBI's default value to be a suitable reference to a hash of the
> primary key column namess and values if there is more than a single
> column in the primary key.  (Rather than the current
> 'value/value/value' style value for tables with multicolumn primary
> keys)
> 
> With that change, if I'm not mistaken, might_have should be able to
> work with multicolumn primary keys...  (barring any other serious
> issues I may have missed)
> 
> Comments?  Flames?
> 
> C.
> 

=====
pjs

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 
--0-669808199-1109235582=:70564
Content-Type: application/x-perl-module; name="MightHave.pm"
Content-Transfer-Encoding: base64
Content-Description: MightHave.pm
Content-Disposition: attachment; filename="MightHave.pm"

cGFja2FnZSBDbGFzczo6REJJOjpSZWxhdGlvbnNoaXA6Ok1pZ2h0SGF2ZTsK
dXNlIHN0cmljdDsKdXNlIHdhcm5pbmdzOwoKdXNlIGJhc2UgJ0NsYXNzOjpE
Qkk6OlJlbGF0aW9uc2hpcCc7CnVzZSBEYXRhOjpEdW1wZXI7IAoKc3ViIHJl
bWFwX2FyZ3VtZW50cyB7CglteSAoJHByb3RvLCAkY2xhc3MsICRtZXRob2Qs
ICRmX2NsYXNzLCBAbWV0aG9kcykgPSBAXzsKCSRjbGFzcy0+X3JlcXVpcmVf
Y2xhc3MoJGZfY2xhc3MpOwoKCSMgcGpzIC0tIHB1dCBjb25zdHJhaW50cyBh
bmQgY29sdW1uIG5hbWUgaW4gYXJncyAKCW15ICgkY29uc3RyYWludHMsICRm
X2NvbCkgPSAoe30sJycpOyAKCSRjb25zdHJhaW50cyA9IHBvcCBAbWV0aG9k
cyAgaWYgKHJlZiAkbWV0aG9kc1stMV0gZXEgIkhBU0giKTsKCSRmX2NvbCA9
IGRlbGV0ZSAkY29uc3RyYWludHMtPntfRktffSB8fCAkZl9jbGFzcy0+cHJp
bWFyeV9jb2x1bW47ICAKCglyZXR1cm4gKCRjbGFzcywgJG1ldGhvZCwgJGZf
Y2xhc3MsIAoJICAgICAgICB7IGZvcmVpZ25fY29sdW1uID0+ICRmX2NvbCwg
CgkJCSAgY29uc3RyYWludHMgPT4gJGNvbnN0cmFpbnRzLCAKCQkJICBpbXBv
cnQgPT4gXEBtZXRob2RzIAoJCQl9CgkpOwp9CgpzdWIgdHJpZ2dlcnMgewoJ
bXkgJHNlbGYgPSBzaGlmdDsKCglteSAkbWV0aG9kID0gJHNlbGYtPmFjY2Vz
c29yOwoKCXJldHVybiAoCgkJYmVmb3JlX3VwZGF0ZSA9PiBzdWIgewoJCQlp
ZiAobXkgJGZvcl9vYmogPSBzaGlmdC0+JG1ldGhvZCgpKSB7ICRmb3Jfb2Jq
LT51cGRhdGUgfQoJCX0sCgoJCWJlZm9yZV9kZWxldGUgPT4gc3ViIHsKCQkJ
aWYgKG15ICRmb3Jfb2JqID0gc2hpZnQtPiRtZXRob2QoKSkgeyAkZm9yX29i
ai0+ZGVsZXRlIH0KCQl9LAoKCSk7Cn0KCnN1YiBtZXRob2RzIHsKCW15ICRz
ZWxmID0gc2hpZnQ7CglteSAoJGNsYXNzLCAkbWV0aG9kKSA9ICgkc2VsZi0+
Y2xhc3MsICRzZWxmLT5hY2Nlc3Nvcik7CglyZXR1cm4gKAoJCSRtZXRob2Qg
PT4gJHNlbGYtPl9vYmplY3RfYWNjZXNzb3IsCgkJbWFwIHsgJF8gPT4gJHNl
bGYtPl9pbXBvcnRlZF9hY2Nlc3NvcigkXykgfSBAeyAkc2VsZi0+YXJncy0+
e2ltcG9ydH0gfSk7Cn0KCgpzdWIgX29iamVjdF9hY2Nlc3NvciB7CglteSAk
c2VsZiA9IHNoaWZ0OwoJbXkgKCRjbGFzcywgJG1ldGhvZCkgPSAoJHNlbGYt
PmNsYXNzLCAkc2VsZi0+YWNjZXNzb3IpOwoJcmV0dXJuIHN1YiB7CgkJbXkg
JHNlbGYgPSBzaGlmdDsKCQlteSAkbWV0YSA9ICRjbGFzcy0+bWV0YV9pbmZv
KG1pZ2h0X2hhdmUgPT4gJG1ldGhvZCk7CgkJbXkgKCRmX2NsYXNzLCBAZXh0
cmEpID0KCQkJKCRtZXRhLT5mb3JlaWduX2NsYXNzLCBAeyAkbWV0YS0+YXJn
cy0+e2ltcG9ydH0gfSk7CgkJIyBwanMgLS0gdG8gbWFrZSB0aGlzIGFuIE06
MSBuZWVkIHNlYXJjaCB3aXRoIGNvbnN0cmFpbnRzCgkJbXkgJWNvbnN0ciA9
KCAkbWV0YS0+YXJncy0+e2ZvcmVpZ25fY29sdW1ufSA9PiAkc2VsZi0+aWQs
ICV7JG1ldGEtPmFyZ3MtPntjb25zdHJhaW50c319ICk7CgkJdW5sZXNzICgk
c2VsZi0+eyJfJHttZXRob2R9X29iamVjdCJ9KSB7CgkJCSgkc2VsZi0+eyJf
JHttZXRob2R9X29iamVjdCJ9KSA9ICRmX2NsYXNzLT5zZWFyY2goJWNvbnN0
cik7CgkJfQoJCSRzZWxmLT57Il8ke21ldGhvZH1fb2JqZWN0In07Cgl9OwoK
fQoKc3ViIF9pbXBvcnRlZF9hY2Nlc3NvciB7CglteSAoJHNlbGYsICRuYW1l
KSA9IEBfOwoJbXkgKCRjbGFzcywgJG1ldGhvZCkgPSAoJHNlbGYtPmNsYXNz
LCAkc2VsZi0+YWNjZXNzb3IpOwoJcmV0dXJuIHN1YiB7CgkJbXkgJHNlbGYg
PSBzaGlmdDsKCQlteSAkbWV0YSA9ICRjbGFzcy0+bWV0YV9pbmZvKG1pZ2h0
X2hhdmUgPT4gJG1ldGhvZCk7CgkJbXkgKCRmX2NsYXNzLCBAZXh0cmEpID0K
CQkJKCRtZXRhLT5mb3JlaWduX2NsYXNzLCBAeyAkbWV0YS0+YXJncy0+e2lt
cG9ydH0gfSk7CgkJbXkgJGZvcl9vYmogPSAkc2VsZi0+JG1ldGhvZCgpIHx8
IGRvIHsKCQkJbXkgJHZhbCA9IHNoaWZ0IG9yIHJldHVybjsgICAgIyBqdXN0
IGZldGNoaW5nCgkJCSMgcGpzICBubyBwayBwdXQgYW55bW9yZT8KCQkJJGZf
Y2xhc3MtPmNyZWF0ZSh7ICRtZXRhLT5hcmdzLT57Zm9yZWlnbl9jb2x1bW59
ID0+ICRzZWxmLT5pZCwgJG5hbWUgPT4gJHZhbCB9KTsKCQkJJHNlbGYtPiRt
ZXRob2QoKTsKCQl9OwoJCSRmb3Jfb2JqLT4kbmFtZShAXyk7Cgl9Owp9Cgox
OwoKX19FTkRfXwoKPXBvZAoKPWhlYWQxCgpXQVJOSU5HIC0tIEVYUEVSSU1F
TlRBTCAKCj1oZWFkMSBOQU1FCgpDbGFzczo6REJJOjpSZWxhdGlvbnNoaXA6
Ok1pZ2h0SGF2ZSAtIApqdXN0IGxpa2Ugb2xkIGJ1dCBtb3JlIGZsZXhpYmxl
Cgo9aGVhZDEgU1lOT1BTSVMKCgkjIEV4YW1wbGUgbm90IHNwZWNpZmljYWxs
eSB0ZXN0ZWQgCgoJcGFja2FnZSBNdXNpYzo6Q0Q7CgkuLi4KCSMganVzdCBs
aWtlIG9sZCAtLSBMaW5lck5vdGVzIHByaW1hcnkgY29sdW1uIGlzIAoJIyBl
eHBlY3RlZCB0byBjb250YWluIHRoZSBjZF9pZC4KCglNdXNpYzo6Q0QtPm1p
Z2h0X2hhdmUobGluZXJfbm90ZXMgPT4gTGluZXJOb3RlcyA9PiBxdyhub3Rl
cykpOwoKCSMgc3BlY2lmeSB3aGF0IGNvbHVtbiBjZF9pZCBpcyBpbiBpbiBm
b3JlaWduIGNsYXNzIAoJIyB3aXRoICJfRktfIiBhcyB3ZWxsIGFzIGNvbnN0
cmFpbnRzIHRvIGZpbmQgdGhlIHJpZ2h0IHJvdy4gCgkjIEluIGdlbmVyYWwg
bWlnaHRfaGF2ZSBhc3N1bWVzIF9GS18gYW5kIG90aGVyIGNvbnN0cmFpbnRz
IAoJIyBtYWtlIHVwIGEgdW5pcXVlIGtleSBpbiB0aGUgZm9yZWlnbiBjbGFz
cyBhbmQgZ2l2ZXMgeW91IAoJIyBmaXJzdCBvbmUgcmV0dXJuZWQgZnJvbSBh
ICJzZWFyY2giIGNhbGwuCgoJTXVzaWM6OkNELT5taWdodF9oYXZlKHNlY29u
ZGFyeV9hcnRpc3QgPT4gTXVzaWM6OkNEQXJ0aXN0ID0+IAoJCXF3KCksIHtf
RktfPT4nY2RfaWQnLCBhcnRpc3RfdHlwZSA9PiAnc2Vjb25kYXJ5J30gCgkp
OwoKCSMgQ2QgbWlnaHQgaGF2ZSBhbm90aGVyIGFydGlzdAoJCglNdXNpYzo6
Q0QtPm1pZ2h0X2hhdmUoZ3Vlc3Rfc3RhciA9PiBNdXNpYzo6Q0RBcnRpc3Qg
PT4gCgkJcXcoKSwge19GS189PidjZF9pZCcsIGFydGlzdF90eXBlID0+ICdn
dWVzdF9zdGFyJ30gCgkpOwoKCgkjIGdldCBtaWdodF9oYXZlcyBrZXllZCBv
biBmb3JlaWduIGNsYXNzOwoJTXVzaWM6OkJhc2U6Om9wdGlvbmFsX2NvbXBv
bmVudHMgewoJCW15ICgkY2xhc3MsICRmX2NsYXNzKSA9IEBfOwoJCW15ICRv
cHRzID0gICRjbGFzcy0+bWV0YV9pbmZvKCdtaWdodF9oYXZlJyk7CgkJbXkg
JXJlc3VsdDsgCgkJZm9yZWFjaCBteSAkYSAoa2V5cyAlJG9wdHMpIHsKCQkJ
bXkgJGZjbGFzcyA9ICRvcHRzLT57JGF9LT57Zm9yZWlnbl9jbGFzc307CgkJ
CSRyZXN1bHR7JGZjbGFzc30gPSBbXSB1bmxlc3MgJHJlc3VsdHskZmNsYXNz
fTsKCQkJcHVzaCBAeyRyZXN1bHR7JGZjbGFzc319LCAkb3B0cy0+eyRhfTsK
CQl9CgoJCSRmX2NsYXNzID8gcmV0dXJuICRyZXN1bHR7JGZfY2xhc3N9IDog
cmV0dXJuIFwlcmVzdWx0OwoJfQoJCgo9Y3V0Cgo=

--0-669808199-1109235582=:70564--