Received: by 10.100.95.15 with SMTP id s15mr1681374anb.26.1249304565161; Mon, 03 Aug 2009 06:02:45 -0700 (PDT)Date: Mon, 3 Aug 2009 06:02:45 -0700 (PDT)X-IP: 125.17.110.216User-Agent: G2/1.0X-Google-Token: H9p8QQwAAAAqWRNxh1-Vl_Ie-jz7mgMrX-HTTP-Via: 1.1 ISAX-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/530.5 (KHTML, like Gecko) Chrome/2.0.172.33 Safari/530.5,gzip(gfe),gzip(gfe)Message-ID: <68fdb34d-ded6-4548-b82c-4dc885961a76@s31g2000yqs.googlegroups.com>Subject: loading table with foreign keysFrom: bharani <bharani_vms>To: “Kdb+ Personal Developers” X-Google-Approved: simon.garland@gmail.com via web at 2009-08-03 14:35:33I am relative new to kdb+ and i am trying to load a table with foreignkey from a csv file.I have got two tables likep:([p:()] t:()) / parent tablea:([a:()] p:p$()) / child table with p as foreign keyi can load the table p from a csv file with out any problem withCols: ("II";",") 0:d:/p.dat`p insert flip Colsbut i am not sure how to do it for table a because it has the foreignkey p. From the documentation i understand it has to be of typeenumeration but not sure how to get it to enumerate when i am loadingfrom the csv file-Bharani</bharani_vms>
X-Mailer: Apple Mail (2.935.3)On Aug 3, 2009, at 9:02 PM, bharani wrote:> I have got two tables like>> p:([p:()] t:()) / parent table>> a:([a:()] p:p$()) / child table with p as foreign key\>\> i can load the table p from a csv file with out any problem with\>\> Cols: ("II";",") 0:d:/p.dat> p insert flip Cols\>\> but i am not sure how to do it for table a because it has the foreign\> key p. From the documentation i understand it has to be of type\> enumeration but not sure how to get it to enumerate when i am loading\> from the csv filei think the keying happens automatically, given that it's in the schemacompare:q)p:([p:()] t:()) / parent tableq)a:([a:()] p:p$()) / child table with p as foreign keyq)a upsert flip("II";",")0:a.dat’castie insert w/o corresponding parents failsq)p upsert flip("II";",")0:p.datpq)a upsert flip(“II”;“,”)0:a.dataq)exec p from a`p$1 2 3once parents exist, upsert succeeds, and column is an enumeration
User-Agent: G2/1.0X-Google-Token: mqQQTgwAAAAudDU7FvOwxbf2LF_WB24eX-HTTP-Via: 1.1 ISAX-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/530.5 (KHTML, like Gecko) Chrome/2.0.172.33 Safari/530.5,gzip(gfe),gzip(gfe)Message-ID: Subject: Re: loading table with foreign keysFrom: bharani <bharani_vms>To: “Kdb+ Personal Developers” X-Google-Approved: charlie@kx.com via web at 2009-08-04 12:22:50Thanks for the quick response. But I am wondering why insert fails inthis scenario and upsert worksp upsert flip Cols / this worksa upsert flip Cols / this also worksbutp insert flip Cols / this worksa insert flip Cols / but this does not. insert actually inserts thekey ( a in this case) but the enum column (p) is empty-BharaniOn Aug 3, 8:41?pm, Aaron Davies <aaron.dav…> wrote:> On Aug 3, 2009, at 9:02 PM, bharani wrote:>> > I have got two tables like>> > p:([p:()] t:()) ?/ parent table>> > a:([a:()] p:p$()) ?/ child table with p as foreign key>> > i can load the table p from a csv file with out any problem with>> > Cols: ("II";",") 0:d:/p.dat> > p insert flip Cols>> > but i am not sure how to do it for table a because it has the foreign> > key p. From the documentation i understand it has to be of type> > enumeration but not sure how to get it to enumerate when i am loading> > from the csv file>> i think the keying happens automatically, given that it's in the schema>> compare:>> q)p:([p:()] t:()) ?/ parent table> q)a:([a:()] p:p$()) ?/ child table with p as foreign key> q)a upsert flip("II";",")0:a.dat> 'cast>> ie insert w/o corresponding parents fails>> q)p upsert flip("II";",")0:p.dat> p> q)a upsert flip(“II”;“,”)0:a.dat> a> q)exec p from a> `p$1 2 3>> once parents exist, upsert succeeds, and column is an enumeration</aaron.dav…></bharani_vms>
X-Mailer: Apple Mail (2.935.3)On Aug 4, 2009, at 8:10 PM, bharani wrote:> p insert flip Cols / this works\> a insert flip Cols / but this does not. insert actually inserts the> key ( a in this case) but the enum column (p) is emptycan you post actual examples, including table data? insert and upsert have different semantics in many situations
I did some more tests and here is what it loos likedoing the following in q worksp:([p:()] t:())a:([a:()] p:p$())Cols: ("II";",") 0:d:/oc.datp upsert flip Colsa upsert flip Colswhere d:/oc.dat is a file with contents3,34,4now quit and enter into a new q session and do the followingp:([p:()] t:())a:([a:()] p:p$())Cols: ("II";",") 0:d:/oc.datp insert flip Colsa insert flip ColsI think the problem is because when i do p insert flip Cols i getoutput like0 1i dont know what that means but typing p gives mep|t3|43|4which is wrong - it should have been3|34|4so i am wondering is "insert flip" a good idea or i should only use"upsert flip"-BharaniOn Aug 4, 6:24?pm, Aaron Davies <aaron.dav...> wrote:> On Aug 4, 2009, at 8:10 PM, bharani wrote:>> > p insert flip Cols ?/ this works> > `a insert flip Cols ?/ but this does not. insert actually inserts the> > key ( a in this case) but the enum column (p) ?is empty>> can you post actual examples, including table data? insert and upsert ?> have different semantics in many situations</aaron.dav…>
X-Mailer: Apple Mail (2.935.3)> p:([p:()] t:())> a:([a:()] p:p$())\> Cols: ("II";",") 0:d:/oc.dat> p insert flip Cols\> a insert flip Cols>> I think the problem is because when i do p insert flip Cols i get\> output like\> 0 1that's the indices of the new rows\> i dont know what that means but typing p gives me\> p|t\> 3|4\> 3|4\>\> which is wrong - it should have been\>\> 3|3\> 4|4\>\> so i am wondering is "insert flip" a good idea or i should only use\> "upsert flip"it really helps if you give an actual session transcriptin this case, the linesq)a insert flip Cols’castare key–it indicates that what you’re trying to put into a.p doesn’t exist in p.pthings become clearer if you don’t use a square table:q)p:([p:()] t:())q)a:([a:()] p:p$())q)Cols: ("II";",") 0:oc2.datq)p insert flip Cols'lengthq)p insert Cols0 1 2q)`a insert Cols0 1 2q)pp| t-| -3| 34| 45| 5q)aa| p-| -3| 34| 45| 5so yes, “insert flip” is wrong