Firefox 3

Special fields

There are three special types of fields used by pjjTextBase:

  1. id field
  2. link field
  3. multi field

id field

This field is meant to be a primary key, so it should contain consecutive natural numbers (at least they mustn't be recurrent). However, in fact only ptb_append treats it this way: when records are appended from the second table, its ids are being renumbered, starting from the greatest id of the first table, increased by 1.

Link field, which name starts with @, contains single reference to value included in the child table. Child table's name should be the same as link field's (without the @ sign, though):

  christie.csv – main table:
  1|1920|1|The Mysterious Affair at Styles|
  2|1922|3|The Secret Adversary|
  3|1923|1|The Murder on the Links|

  protagonist.csv – child table:
  1|Hercule Poirot
  2|Miss Marple
  3|Tommy and Tuppence

Child tables are read automagically by ptb_connect. BTW they can contain more than just two columns: id and link field values, eg.:

  protagonist.csv – child table:
  1|Hercule Poirot|Belgian
  2|Miss Marple|English
  3|Tommy and Tuppence|English

You can certainly go on with the normalization of the database by moving protagonists' nationality to nationality.csv, which thus would contain child table of the one included in protagonist.csv.

Data files with child tables must have the same extension as data file with parent table.

multi field

Multi field can contain multiple references, separated with commas, to values included in some other table. If multi field's name starts with %, the other table will be read automatically. Child table's name should be the same as multi field's (without the % sign):

  christie.csv – main table:
  24|1937|1|Murder in the Mews|7
  25|1937|1|Dumb Witness|8
  26|1938|1|Appointment with Death|
  27|1938|1|Hercule Poirot's Christmas|9,10
  28|1940|1|One, Two, Buckle My Shoe|11,12

  aka.csv – child table:
  7|Dead Man's Mirror
  8|Poirot Loses a Client
  9|A Holiday for Murder
  10|Murder for Christmas
  11|An Overdose of Death
  12|The Patriotic Murders

Only two columns are read from child table. Data files with child tables must have the same extension as data file with parent table.–To find if a certain reference is included in a multi field, you can use isThere or isThereExt functions.

Some may argue that multi field concept is wrong; eg. this is what Mark Whitehorn claims in his Multivalued datatypes considered harmful article. The text is certainly worth reading; however it focuses on SQL, and this is not the case with pjjTextBase. Whitehorn writes that runing "a normal SQL query against our single table solution [with more than one value may] return zero rows". Undoubtedly true with SQL, but not with pjjTextBase, if you use isThere or isThereExt; at the same time multi fields can save you a lot of work. All in all, I daresay multi fields are good thing.