Jd/Ops common

From J Wiki
Jump to navigation Jump to search
Jd | Overview | General | Docs | Ops | Admin | Server | Replicate | Guide | Technical | Release | License | Support


The main function is jd:

   jd'...' NB. perform Jd op

The argument is parsed to a list of boxes. A string is treated as list of blank delimited boxes. The first box in a list of boxes is treated as a list of blank delimited values.

   jd'insert f a';23     === jd'insert';'f';'a';23

Column definitions have blanks and are delimited by , or LF and have more complicated rules. The following are equivalent:

   jd'createtable f a int,b byte 4'
   jd'createtable f a int',LF,'byte 4'
   jd'createtable';'f';'a int';'b byte 4'
   jd'createtable f a';'a int';'b byte 4'

Options follow the command, start with /, and have a fixed number (0 or more) parameters.

   jd'reads /lr /e from f'


table and column names have similar rules:

  • jd... prefix not allowed
  • unprintable chars (32{.a.) not allowed
  • used as folder names
    • unix file names are case sensitive - windows are not
    • names differing only in case could lead to grief
  • utf-8 supported
    • macOS problems - composed vs decomposed
      • could be resolved - for now avoid utf-8 names on macOS
  • table names
    • not allowed in "s
    • RESERVEDCHARS_jd_ not allowed
    • RESERVEDWORDS_jd_ not allowed
    • the ^ char is used in ptable names and should be avoided
    • the () chars could conflict with where clause and should be avoided
  • col names
    • col names can be in "s
    • " is escaped in a col name - "a\"b"
    • read op parsing may require col names in "s
      • jd'read "c d",a=b from f where "a=b"=23'
    • ref col can not have _ char
    • names with OSFNA_jd_ chars are not used directly as folder names
      • name a/b maps to folder name `a`b`b
        • dfromn_jd_'a/b' NB. folder name from col name
        • nfromd_jd_'`a`b`b' NB. folder name from col name
   jdaddq_jd_'a" /' NB. " if required by " or blank
"a\" /"
   jdremq_jd_'"a\" /"' NB. remove "s
a" /


boolean int float

correspond directly to J data types


corresponds directly to J literal data
only type that can have a trailing shape

int1 int2 int4

1/2/4 byte signed integers - stored as literal strings
converted to int for processing (queries/selections/etc.) and in results
set with J int values that are range checked
not allowed in ref/key/sort or ptable pcol (could be supported with more work)
op intx converts a col type/data between int/int1/int2/int4

edate edatetime edatetimem edatetimen (tutorial epochdt)

edate types are in iso 8601 format and are stored as nanoseconds before/after 2000-01-01
edate      2014-01-02
edatetime  2014-01-02T03:04:05
edatetimem 2014-01-02T03:04:05,123
edatetimen 2014-01-02T03:04:05,123456789

date datetime

date     yyyymmdd
datetime yyyymmddhhmmss
csv loader supports yyyy/mm/dd vs mm/dd/yyyy


with max size less than 50, it is better to store as byte N


jdlast - info on last error

jdlasty - last operation


list of pairs of column names and data

pairs are args to insert, update, and other ops

'a' ; 23 24 ; 'b' ; 1.2 1.3 ; 'c' ; 2 3$'abcdef' 'a' ; 23 24 ; 'b' ; 1.2 ; 'c' ; 'abc'

data extends so the count is the same for all cols

byte N col does undertake but not overtake

,jd'read ....' NB. valid pairs

,|:jd'reads ...' NB. not valid pairs (all cols rank 2 and varbyte not boxed)


One or more cols in a table can be treated as a key.

A key in a table does not have to be unique. If a table contains a key more than once, and an operation requires just one match, then the first occurrence (in row order) is the one that is used.

Col types int1/int2/int4/float/varbyte not allowed.

ref join from table F to table G creates a jdref col in table F that is the index to the corresponding row in table G. The jdref values are Gkey i. Fkey so the join from table F is to the first matching row in G (or nulls if no match).

ref /left join from table F to table G creates a jdref col in table F hat has all rows in F paired with a null row or all the rows (matched by key) in G.

upsert key is used to determine which data rows are used to update and which data rows are inserted.

update where clause can be a key.

delete where clause can be a key.

reads where clause does not currently support key. Use of key op to get rows and using them with jdindex in (...) is a workaround.