C# and composite keys

[borota]

I am using C# interface as described here: http://code.kx.com/wiki/Cookbook/InterfacingWithCSharp

I have a q table that has a composite foreign key and I want to bulk insert into it. How do I pass the composite key column values?

I tried Array of Array but that doesn't work.

[m_lach]

Here is an example of bulk insert via the qSharp interface:

q.Sync("insert", "sp", new object[] { new string[] { "s1", "s1" }, new string[] { "p1", "p2" }, new int[] { 456, 333 } });

and c.cs:

c.k("insert", "sp", new object[] { new string[] { "s1", "s1" }, new string[] { "p1", "p2" }, new int[] { 456, 333 } });

Maciej Lach

M.Lach@devnet.de

exxeleron | a brand of DEVnet

[borota]

I am already using that and it works indeed, my question was though for when your foreign key is composite. If the parent table has a primary key formed of 2 columns, how do insert values in a child table via c#? Are you implying that inserting the 2 columns separately as arrays should work?

[m_lach]

Let's define a table with compound primary key:

ktc:([lname:`Dent`Beeblebrox`Prefect; fname:`Arthur`Zaphod`Ford]; iq:98 42 126)

A details table with a foreign key enumeration over ktc:

tdetails:([] name:`ktc$(`Dent`Arthur;`Prefect`Ford;`Dent`Arthur); sc:36 126 42; c: 1 2 1)

Bulk insert in q:

`tdetails insert (`ktc$(`Dent`Arthur;`Dent`Arthur); 11 34; 2 0)

While inserting from C#, simply using 2 columns to store foreign key values won't work. You can use additional q function which takes care of enumerating the foreign key values over a parent table:

c.k("{[x;y;z] x insert (enlist y$first z),1_z}", "tdetails", "ktc", new object[] { new object[] { "Prefect", "Ford" }, 133, 2 });
c.k("{[x;y;z] x insert (enlist y$first z),1_z}", "tdetails", "ktc", new object[] { new object[] { new string[] { "Prefect", "Ford" }, new string[] { "Prefect", "Ford" } }, new int[] { 156, 33 }, new int[] { 1, 2 } });

Maciej Lach

M.Lach@devnet.de

exxeleron | a brand of DEVnet

[borota]

That example was very useful for me to learn more of q, thanks.

But when you have a table that has 10 composite foreign keys like that, I guess it will end up pretty ugly.  This is external data shaped like that, which I am trying to bring into q, as a learning exercise at this stage.

Unless somebody has some better idea, I am thinking to insert the data first as pairs of symbols and then cast it to the parent table type by doing something like this:

q) update name:`ktc$name from `tdetails

[borota]

Here is a shot at trying to get a generalized solution. 

csert function is my first more complex Q function, so feedback for improvement/conciseness is gladly accepted.

q)ktc:([lname:`Dent`Beeblebrox`Prefect`Valerie; fname:`Arthur`Zaphod`Ford`Johan]; iq:98 42 126 133)

q)tdetails:([] name:`ktc$(); sc:`int$())

q)csert:{[t;l]cs:cols t;t insert ?[flip cs!l;();0b;cs!each[{[f;c]$[`=f c;c;($;(enlist f c);c)]}fkeys t]cs]}

In C#:

c.k("csert", "tdetails", new object[] {

    new object[] { new string[] {"Beeblebrox", "Zaphod"}, new string[] {"Prefect", "Ford"}, new string [] {"Beeblebrox", "Zaphod"} },

    new int[] { 36, 126, 42 }});

This seems to work and unless more testing finds issue, it should work with multiple composite foreign keys, even simple keys, etc.

[borota]

Maciej,

I gave qSharp a shot and I find out it outperforms kx c# interface. Thanks for pointing me to it.

One thing which I would hold as a minor bug though. While you can have columns with a runtime type of 'type'[][], if you declare column with a compile time of say char[][], qSharp throws an exception saying it's not able to handle char[][] types. You are forced to use object[] type for that. kx's c class doesn't have that problem.

Thank you for pointing me to qSharp.

[m_lach]

Hi Greg,

latest qSharp release provides a convenience enhancement, which allows to seamlessly serialize char[][] to q general list of strings.

Maciej Lach
M.Lach@devnet.de

exxeleron | a brand of DEVnet

On Saturday, March 21, 2015 at 10:20:18 AM UTC+1, Greg Borota wrote:

Maciej,

I gave qSharp a shot and I find out it outperforms kx c# interface. Thanks for pointing me to it.

One thing which I would hold as a minor bug though. While you can have columns with a runtime type of 'type'[][], if you declare column with a compile time of say char[][], qSharp throws an exception saying it's not able to handle char[][] types. You are forced to use object[] type for that. kx's c class doesn't have that problem.

Thank you for pointing me to qSharp.