Replicating SQL functionality - kevinlawler/kona GitHub Wiki

The following functions and examples are to implement sql-like functionality in kona, when tables are represented by kona dictionaries.

select:{[t;f;c]
        r:1; i:0
        if[0=#f; f:!t]
        if[0=#c; ret:+f!t[f]; :ret]
        :[7=4:c[0]; r:c[0][t[c[1]];c[2]];do[#c; d:c[i]; i:i+1; r:(r) & (d[0][t[d[1]];d[2]])]]
        d:t[f;&r]; :[1=#f; +f!,(d); +f!d]}

update:{[t;f;c]
        r:1; i:0
        :[7=4:c[0]; r:c[0][t[c[1]];c[2]];do[#c; d:c[i]; i:i+1; r:(r) & (d[0][t[d[1]];d[2]])]]
        i:0
        :[4=4:f[0]; t[f[0];&r]:f[1]; do[#f; d:f[i]; i:i+1; t[d[0];&r]:d[1]]]; t}

lj:{[t1;t2;k]             / left join
        ko:!t2                 / get original fields in t2
        ki:ko?k                / get index of primary key to join with
        kn:()                  / new keys that would get joined
        kv1:t1[k]              / Data in primary key column of t1
        kv2:t2[k]              / Data in primary key column of t2
        r:()                   / list of index of rows to be taken from t2
        i:0                    / counter
	ret:select[t1;();()]
        do[#ko; if[~i=ki; kn:kn,ko[i]]; i:i+1]          / for each ko,  if not i=idx of primary key,  append ko[i] to kn
	i:0
	do[#kv1; r:r,kv2?kv1[i]; i:i+1]
	i:0
	do[#kn; knv:t2[kn[i]],0; ret[kn[i]]:knv[r]; i:i+1]        / adds int's null value, works only for integer cols now.
	:ret}

insert:{[t;d]
        f:!t                   / get field names
	i:0
	do[#f; t[f[i]]:t[f[i]],d[i]; i:i+1]}

Examples

t1: + `name`elo!(`Dent`Beeblebrox`Superman`Prefect;1100 1600 3000 1800)

t2: + `name`iq`age!(`Dent`Beeblebrox`Prefect;98 42 126;10 20 30)

insert[t1;(`Sahas;1381)]

lj[t1;t2;`name]

select[t2;`name`iq;(=; `name; `Beeblebrox)]

update[t2; ((`name;`Superman);(`iq;200)); ((>;`age;10);(<;`iq;126))]

update[t2; (`age;29); (=;`age;20)]