Extended Metadata

Question

The meta function in kdb/q returns the following info about the table:

1. c – (symbol) column names
2. t – (char) data type
3. f – (symbol) domain of foreign keys
4. a - (symbol) attributes.

I would like to extend this to include more information about the table. The specific case that I am trying to solve is to include the timezone information about the time data columns in the table.

For example: select from Price

+-------------------------+-------------------------+--------+-------+
|          Time           |         SysTime         | Ticker | Price |
+-------------------------+-------------------------+--------+-------+
| 2016.09.15D09:18:02.391 | 2016.09.15D08:18:02.391 | IBM    | 63.46 |
| 2016.09.15D09:18:02.491 | 2016.09.15D08:16:22.391 | MSFT   | 96.72 |
| 2016.09.15D09:18:02.591 | 2016.09.15D08:14:42.391 | AAPL   | 23.06 |
+-------------------------+-------------------------+--------+-------+

meta Price

+---------+---+---+---+
|    c    | t | f | a |
+---------+---+---+---+
| Time    | p |   |   |
| SysTime | p |   |   |
| Ticker  | s |   |   |
| Price   | f |   |   |
+---------+---+---+---+

I would like to have additional info about the time data columns (Time and SysTime) in the meta.

For Example, something like this:

metaExtended Price

+---------+---+---+---+------------------+
|    c    | t | f | a |        z         |
+---------+---+---+---+------------------+
| Time    | p |   |   | America/New_York |
| SysTime | p |   |   | America/Chicago  |
| Ticker  | s |   |   |                  |
| Price   | f |   |   |                  |
+---------+---+---+---+------------------+

Please note that I have a function that takes in the table and column to return the time zone. TimeZone[Price;Time] returns America/New_York

My question is only about how to include this information in the meta function. The second question that I have is that if the user does something like this, newPriceTable:Price (creating a new table which is the same as the previous table) then the metaExtended function should return the same value for both the tables (akin to calling a function on two different variables having the same object reference)

Does something similar exist in sql?

Answer 1

meta is a reserved word and therefore cannot be redefined. But you can create your own implementation and use it in place of meta:

TimeZone:{[Table;Col] ... } / your TimeZone function

metaExtended:{meta[x],'([]z:TimeZone[t]each cols x)}

metaExtended Price

Regarding your second question, I don't think it's possible to do what you want in k/q. Immediately after assigning Price to newPriceTable the latter is indeed a reference, but as soon as you modify it kdb will create a copy and modify it instead of the original. The problem is there is no way to tell whether newPriceTable is still a reference to Price or a fresh new object.

Answer 2

You can use lj to join them into one metaExtended function.

The function will check for all the time cols and run TimeZone function on them and join the result with meta result:

      metaExtended:{[tbl] meta[tbl] lj  1!select c,z:TimeZone[tbl] each t from meta[tbl] where t in "tp"}

      metaExtended `t

when you assign this table to new variable it will be assigned as a reference.

         nt:t / nt and t pointing to same object

Yo can check the reference count of a variable using -16! .

          -16!t

At this point metaExtended function will give same output. But once some update is done on any of these variables pointing to same table, kdb will create a new copy for updated table/variable. From this point they are 2 different objects. Now output of metaExtended function depends on the object schema.