User:Joe Bogner/JTable

DRAFT

jtable assists with working with tabular data by wrapping it in an object and providing a convenient syntax similar to SQL

The commands flow from right to left.

Reading from right to left, all statements must start with a jtable instance and the keyword from and must end with a top level keyword

Top Level Keywords:

1. select
2. count

select from tbl

count from tbl

The jt command will pretty-print the table instance and will limit based upon the OUTPUTROWS_jtable_ global, or the rowLimit instance variable set by the limit keyword

This page will be updated as jtable becomes more complete

Collected Definition

coclass 'jtfilter'

NB. evals a filter in a locale so we set names to columns
eval=: 4 : 0
    tbl =. y
    for_c. headers__tbl do.
        colData=. (c_index) {:: data__tbl
        if. (c_index{colTypes__tbl) = JCHAR do. colData=. dtb"1 colData end.
        (>c) =: colData
    end.

    ". x

)

coclass 'jtable'
OUTPUTROWS=:10

create=: 3 : 0
    outputCols=:''
    headers=:''
    rowCount=:0
    rowLimit=: OUTPUTROWS
    orderIdx=:''
    whereIdx=:''
    colTypes=:''
    orderCol=:''
)

reset =: 4 : 0
    orderIdx=:''
    whereIdx=:''
    orderCol=:''
    y
)

selectcol =: 3 : 0
    if. (y-:'*') do. (outputCols =: i. # headers) else. (outputCols =: headers i. (boxxopen y) ) end.
)

limit =: 4 : 0
    rowLimit=.x
    y
)

order =: 4 : 0
    orderCol=: x
    sortCol =. headers i. (cutopen x)
    sortData=. sortCol {:: data
    if. (# whereIdx) do.
        orderIdx=: /: (whereIdx # sortData)
    else.
        orderIdx=: /: sortData
    end.
    y
)

where =: 4 : 0
    filter=. conew 'jtfilter'
    whereIdx=: x eval__filter (coname'')
    NB. need to reorder after filtering
    if. (# orderCol) do.
        orderCol order (coname'')
    end.
    coerase filter
    y
)

count =: 3 : 0
   ct =. rowCount
   18!:4 <'base'
   ct
)

select =: 3 : 0
    NB. can't call dyadic version because 18!:4 won't due to nested named
    selectcol '*'
    18!:4 <'base'
    y
:
   selectcol x
    18!:4 <'base'
    y
)

NB. gets a column from a ((N*C), W) shaped array
NB. where N is the number of rows, C is the # of columns
NB. and W is the widest
getCol =: 2 : 'y #~ (0{::$y) $ 1 (m) } n$0'

readcsv =: 3 : 0
'' readcsv y
:
    create''
    if. (1=#y) do. txt=. fread y else. txt=. y end.
    headers =: ',' cut 0 {:: LF cut 4000 {. txt
    rawData=: (',',LF) (e.~ ];._1 ]) LF,txt
    data=:''
    for_i. headers do.
        d=. (}. i_index getCol (# headers) rawData)

        NB. chop off the trailing LF on the first col
        if. (i_index = 0)  do. d=. _1 }. d end.

        NB. hack to convert
        NB. todo add dynamic detection
        if. (i_index=2) do.
            d =. ". d
            colTypes=:colTypes,JINT
        else.
            colTypes=:colTypes,JCHAR
        end.
        data =: data , (<d)
    end.
    rowCount =: (# 0{:: data)
)

NB. from_z_ =: (3 : 'CURRENT_jtable_ =: y [ 18!:4 <''jtable''')
from_z_ =: (3 : 'CURRENT_jtable_=: y [ 18!:4 y')

jt_z_ =: 3 : 0
    cur =. CURRENT_jtable_
    cols =. outputCols__cur
    
    limit =. <. / (OUTPUTROWS_jtable_ ,  rowCount__cur, rowLimit__cur)
    if. (# whereIdx__cur) > 0 do.
        limit=. limit <. (+/ whereIdx__cur)
    end. 
    row=.''
    for_c. cols do.
        row=. row, (c { headers__cur)
    end.
    output =: row

    for_i. (i. limit) do.
        row=.''
        for_c. cols do.
            colData=. c {:: data__cur
            if. # whereIdx__cur do.
                colData =. (whereIdx__cur) # colData            
            end. 
            NB. sort the data if it's ordered
            if. # orderIdx__cur do. colData=. orderIdx__cur { colData end.
            row=. row , (<(i {:: colData))
        end.
        output =.  output , row
    end.
    smoutput (-1*(#cols))[\ output
)

Test Code

   coclass 'base'
   
   
   
   tbl=:conew 'jtable'
   
   readcsv__tbl (0 : 0)
name,gender,id
joe,m,1
sally,f,2
jane,f,3
jack,m,4
)
4
   
   

   NB. 'BASIC test'

   jt select from tbl
┌──────┬──────┬──┐
│name  │gender│id│
├──────┼──────┼──┤
│joe   │m     │1 │
├──────┼──────┼──┤
│sally │f     │2 │
├──────┼──────┼──┤
│jane  │f     │3 │
├──────┼──────┼──┤
│jack  │m     │4 │
└──────┴──────┴──┘
   
   NB. 'SELECT name;gender'
   
   jt ('name';'gender') select from tbl
┌──────┬──────┐
│name  │gender│
├──────┼──────┤
│joe   │m     │
├──────┼──────┤
│sally │f     │
├──────┼──────┤
│jane  │f     │
├──────┼──────┤
│jack  │m     │
└──────┴──────┘
   
   NB. 'SELECT gender;name'

   jt ('gender';'name') select from tbl
┌──────┬──────┐
│gender│name  │
├──────┼──────┤
│m     │joe   │
├──────┼──────┤
│f     │sally │
├──────┼──────┤
│f     │jane  │
├──────┼──────┤
│m     │jack  │
└──────┴──────┘
   
   NB. 'LIMIT 1'

   jt ('id';'name') select 1 limit from tbl
┌──┬──────┐
│id│name  │
├──┼──────┤
│1 │joe   │
├──┼──────┤
│2 │sally │
├──┼──────┤
│3 │jane  │
├──┼──────┤
│4 │jack  │
└──┴──────┘
   
   
   NB. order data order by name

   jt ('id';'name') select 'name' order 999 limit from tbl
┌──┬──────┐
│id│name  │
├──┼──────┤
│4 │jack  │
├──┼──────┤
│3 │jane  │
├──┼──────┤
│1 │joe   │
├──┼──────┤
│2 │sally │
└──┴──────┘
   
   NB. million row table
   N=:1e6
   tbl2=:conew 'jtable'
   testData=: ('a',',b',LF) , (,  ,&LF"1  ((2&{.),',' , (_3&{.))"1  (_5[\ a. {~ 97+(?. (N*5)#26)))
   readcsv__tbl2 testData
   
   NB. big table 

   jt select from tbl2
┌───┬───┐
│a  │b  │
├───┼───┤
│mw │qwx│
├───┼───┤
│eo │uqn│
├───┼───┤
│by │xpt│
├───┼───┤
│sn │knz│
├───┼───┤
│lh │bxc│
├───┼───┤
│av │gmp│
├───┼───┤
│zs │ujj│
├───┼───┤
│no │rki│
├───┼───┤
│od │tpl│
├───┼───┤
│yp │qsc│
└───┴───┘
   

   jt select 'a -: "1 ''mw''' where from tbl2
┌───┬───┐
│a  │b  │
├───┼───┤
│mw │qwx│
└───┴───┘
   
   
   NB. another WHERE test

   jt select 'gender -: "1 (,''m'')' where from tbl
┌──────┬──────┬──┐
│name  │gender│id│
├──────┼──────┼──┤
│jack  │m     │4 │
├──────┼──────┼──┤
│joe   │m     │1 │
└──────┴──────┴──┘


  jt select 'id > 2' where from tbl
┌──────┬──────┬──┐
│name  │gender│id│
├──────┼──────┼──┤
│jack  │m     │4 │
├──────┼──────┼──┤
│jane  │f     │3 │
└──────┴──────┴──┘