Jd/Ops common

From J Wiki
Jump to: navigation, search
Jd | Overview | General | Ops | Admin | Guide | Technical | Release | License | Support

args

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'

names

Column, table, and database names have similar validation:

  • names are used directly as file and folder names
    • windows file names ignore case - linux file names are case sensitive
    • avoid names with upper-case if possible
    • names that differ only in case will probably lead to grief
  • utf-8 supported
    • macOS problems with uft-8 file names - composed vs decomposed
      • could be resolved - for now avoid utf-8 names on macOS
  • RESERVEDCHARS_jd_ are not allowed in names
  • RESERVEDWORDS_jd_ are not allowed
  • the jd... prefix is not allowed in table or column names
  • the ~... prefix is not allowed
  • the +- characters are allowed in names, but should be avoided in table names because of conflicts when parsing the read from clause
  • the ^ character is used by the system in table names for ptable and should be avoided

types

boolean int float

correspond directly to J data types

byte

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 dd/mm/yyyy

varbyte

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

errors

jdlast - info on last error

jdlasty - last operation

pairs

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)

key

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 last 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 last 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.