I was following this docs : https://code.kx.com/q/wp/capi/#publishing-to-a-kdb-tickerplant
In the sym.q :
trade:([]time:timespan$();[sym:symbol$()];price:float$();size:long$())
Now what I want is to use keyed table that is why above schema is following keyed table syntax.
But the problem is all the tickerplant schema should be starting with time:timespan column and problem it was causing is that when i typed type trade its returning 98h and not 99h (which is the type number for keyed table).
So in the r.q :
/q tick/r.q [host]:port[:usr:pwd] [host]:port[:usr:pwd]
/2008.09.09 .k ->.q
if[not "w"=first string .z.o;system "sleep 1"];
upd:upsert
/ get the ticker plant and history ports, defaults are 5010,5012
.u.x:.z.x,(count .z.x)_(":5010";":5012");
/ end of day: save, clear, hdb reload
.u.end:{t:tables`.;t@:where `g=attr each t@\:`sym;.Q.hdpf[`$":",.u.x 1;`:.;x;`sym];@[;`sym;`g#] each t;};
/ init schema and sync up from log file;cd to hdb(so client save can run)
.u.rep:{(.[;();:;].)each x;if[null first y;:()];-11!y;system "cd ",1_-10_string first reverse y};
/ HARDCODE \cd if other than logdir/db
/ connect to ticker plant for (schema;(logcount;log))
.u.rep .(hopen `$":",.u.x 0)"(.u.sub[`;`];`.u `i`L)";
I have upsert, but since its not really keyed table its causing to insert duplicate entries again and again instead of updating the same row of unique key.
Your table definition isn't valid. You seem to have
trade:([]time:`timespan$();[sym:`symbol$()];price:`float$();size:`long$())
which I think means that you want the table to be keyed by symbol. In that case the table definition should be:
trade:([sym:`symbol$()]time:`timespan$();price:`float$();size:`long$())
But you probably don't want to have the table keyed within the tickerplant schema, only the rdb should key it. That way you maintain a replayable log in the tickerplant, and you don't have to alter the tickerplant logic (the "timesym" check would need to be removed and the upd function would have to timestamp the data not as the first column).
So you should keep the trade schema unkeyed
trade:([]time:`timespan$();sym:`symbol$();price:`float$();size:`long$())
and you could change your replay function in the rdb to:
upd:insert; /initially set it to be insert so that the replay will work
.u.rep:{(.[;();:;].)each x;if[null first y;:()];-11!y;`trade set select by sym from trade;`upd set (upsert);system "cd ",1_-10_string first reverse y};
Here I'm suggesting you initially set upd:insert so that the replay can work as normal (note - the tickerplant logs lists to log file, not tables, and upsert only works with tables). After the replay is complete then you can key your trade table and set upd to be upsert.
Final point to note is that the upsert will be slower than the default upd:insert on the subsequent messages.