First Tutorial Part 3: Table Definition

Previous	Main	Next
<-- Part 2	Main	Part 4 -->

Base-Line Tables

Let's look at how you'd specify the base-line tables User, Form and FormAnswer in Tilda.

 { "name":"User"
  ,"description":"Users"
  ,"columns":[
      { "name":"id"   , "type":"STRING", "size": 40, "nullable":false, "description":"The user's id"    }
     ,{ "name":"email", "type":"STRING", "size":255, "nullable":false, "description":"The user's email" }
    ]
  ,"primary": { "autogen": true, "keyBatch": 500 }
  ,"indices": [ 
      { "name":"Id"   , "columns": ["id"   ] }
     ,{ "name":"Email", "columns": ["email"] }
     ,{ "name":"All"                        , "orderBy": ["lastUpdated"]}
    ]
 }

,{ "name": "Form"
  ,"description": "User-entered forms"
  ,"columns":[
      { "name":"userRefnum", "sameas":"User.refnum"    , "nullable":false, "description":"The user who created the form" }
     ,{ "name":"type"      , "type":"STRING", "size":40, "nullable":false, "description":"Form template type" }
     ,{ "name":"fillDate"  , "type":"DATETIME"         , "nullable":true , "description":"The date the form was filled" }
    ]
  ,"primary": { "autogen": true, "keyBatch": 500 }
  ,"foreign": [
      { "name":"User"  , "srcColumns":["userRefnum"   ], "destObject": "User"    }
    ]
  ,"indices": [ 
      { "name": "Type" , "columns": ["type"], "orderBy": ["fillDate"]}
     ,{ "name": "All"                       , "orderBy": ["lastUpdated"]}
    ]
 }

,{ "name": "FormAnswer"
  ,"description": "Form answers"
  ,"columns":[
      { "name":"formRefnum", "sameas":"Form.refnum"       , "nullable": false, "invariant": true, "description":"The form's refnum" },
     ,{ "name":"field"     , "type":"STRING", "size": 60  , "nullable": false                   , "description":"question/field id" },
     ,{ "name":"value"     , "type":"STRING", "size": 4000, "nullable": true                    , "description":"answer value" }
    ]
  ,"primary": { "autogen": true, "keyBatch": 500 }
  ,"foreign": [
      { "name":"Form" ,  "srcColumns":["formRefnum"], "destObject": "Form" }
    ]
  ,"indices": [ 
      { "name":"FormAnswer", "columns":["formRefnum", "field"]}
     ,{ "name":"All"       , "columns":[]                     , "orderBy":["lastUpdated"]}
    ]
 }

These are pretty vanilla definitions as per the Object Syntax:

• Tilda will automatically add a primary key "refnum" that is auto-generated.
• Tilda will add life-cycle tracking timestamp columns "created", "lastUpdated" and "deleted".
• Tilda will add a timezone tracking field fillDateTZ automatically to support the date-time column fillDate.

The SQL generated under the covers should be self-evident (T in Tilda stands for Transparent) and look like:

create table if not exists TILDATEST.User -- Users
 (  "refnum"       bigint        not null   -- The primary key for this record
  , "id"           varchar(40)   not null   -- The user's id
  , "email"        varchar(255)  not null   -- The user's email
  , "created"      timestamptz   not null DEFAULT now()   -- The timestamp for when the record was created. (TILDATEST.User)
  , "lastUpdated"  timestamptz   not null DEFAULT now()   -- The timestamp for when the record was last updated. (TILDATEST.User)
  , "deleted"      timestamptz              -- The timestamp for when the record was deleted. (TILDATEST.User)
  , PRIMARY KEY("refnum")
 );
COMMENT ON TABLE TILDATEST.User IS E'Users';
COMMENT ON COLUMN TILDATEST.User."refnum" IS E'The primary key for this record';
COMMENT ON COLUMN TILDATEST.User."id" IS E'The user''s id';
COMMENT ON COLUMN TILDATEST.User."email" IS E'The user''s email';
COMMENT ON COLUMN TILDATEST.User."created" IS E'The timestamp for when the record was created. (TILDATEST.User)';
COMMENT ON COLUMN TILDATEST.User."lastUpdated" IS E'The timestamp for when the record was last updated. (TILDATEST.User)';
COMMENT ON COLUMN TILDATEST.User."deleted" IS E'The timestamp for when the record was deleted. (TILDATEST.User)';
CREATE UNIQUE INDEX IF NOT EXISTS User_Id ON TILDATEST.User ("id");
CREATE UNIQUE INDEX IF NOT EXISTS User_Email ON TILDATEST.User ("email");
CREATE INDEX IF NOT EXISTS User_All ON TILDATEST.User ("lastUpdated" ASC);
delete from TILDA.Key where "name" = 'TILDATEST.USER';
insert into TILDA.Key ("refnum", "name", "max", "count", "created", "lastUpdated") values ((select COALESCE(max("refnum"),0)+1 from TILDA.Key), 'TILDATEST.USER',(select COALESCE(max("refnum"),0)+1 from TILDATEST.User), 500, current_timestamp, current_timestamp);


create table if not exists TILDATEST.Form -- User-entered forms
 (  "refnum"       bigint        not null   -- The primary key for this record
  , "userRefnum"   bigint        not null   -- The user who created the form
  , "type"         varchar(40)   not null   -- Form template type
  , "fillDateTZ"   character(5)             -- Generated helper column to hold the time zone ID for 'fillDate'.
  , "fillDate"     timestamptz              -- The date the form was filled
  , "created"      timestamptz   not null DEFAULT now()   -- The timestamp for when the record was created. (TILDATEST.Form)
  , "lastUpdated"  timestamptz   not null DEFAULT now()   -- The timestamp for when the record was last updated. (TILDATEST.Form)
  , "deleted"      timestamptz              -- The timestamp for when the record was deleted. (TILDATEST.Form)
  , PRIMARY KEY("refnum")
  , CONSTRAINT fk_Form_User FOREIGN KEY ("userRefnum") REFERENCES TILDATEST.User ON DELETE restrict ON UPDATE cascade
  , CONSTRAINT fk_Form_fillDate FOREIGN KEY ("fillDateTZ") REFERENCES TILDA.ZoneInfo ON DELETE restrict ON UPDATE cascade
 );
COMMENT ON TABLE TILDATEST.Form IS E'User-entered forms';
COMMENT ON COLUMN TILDATEST.Form."refnum" IS E'The primary key for this record';
COMMENT ON COLUMN TILDATEST.Form."userRefnum" IS E'The user who created the form';
COMMENT ON COLUMN TILDATEST.Form."type" IS E'Form template type';
COMMENT ON COLUMN TILDATEST.Form."fillDateTZ" IS E'Generated helper column to hold the time zone ID for ''fillDate''.';
COMMENT ON COLUMN TILDATEST.Form."fillDate" IS E'The date the form was filled';
COMMENT ON COLUMN TILDATEST.Form."created" IS E'The timestamp for when the record was created. (TILDATEST.Form)';
COMMENT ON COLUMN TILDATEST.Form."lastUpdated" IS E'The timestamp for when the record was last updated. (TILDATEST.Form)';
COMMENT ON COLUMN TILDATEST.Form."deleted" IS E'The timestamp for when the record was deleted. (TILDATEST.Form)';
CREATE INDEX IF NOT EXISTS Form_Type ON TILDATEST.Form ("type", "fillDate" ASC);
CREATE INDEX IF NOT EXISTS Form_All ON TILDATEST.Form ("lastUpdated" ASC);
delete from TILDA.Key where "name" = 'TILDATEST.FORM';
insert into TILDA.Key ("refnum", "name", "max", "count", "created", "lastUpdated") values ((select COALESCE(max("refnum"),0)+1 from TILDA.Key), 'TILDATEST.FORM',(select COALESCE(max("refnum"),0)+1 from TILDATEST.Form), 500, current_timestamp, current_timestamp);


create table if not exists TILDATEST.FormAnswer -- Form answers
 (  "refnum"       bigint         not null   -- The primary key for this record
  , "formRefnum"   bigint         not null   -- The form's refnum
  , "field"        varchar(60)    not null   -- question/field id
  , "value"        varchar(4000)             -- answer value
  , "created"      timestamptz    not null DEFAULT now()   -- The timestamp for when the record was created. (TILDATEST.FormAnswer)
  , "lastUpdated"  timestamptz    not null DEFAULT now()   -- The timestamp for when the record was last updated. (TILDATEST.FormAnswer)
  , "deleted"      timestamptz               -- The timestamp for when the record was deleted. (TILDATEST.FormAnswer)
  , PRIMARY KEY("refnum")
  , CONSTRAINT fk_FormAnswer_Form FOREIGN KEY ("formRefnum") REFERENCES TILDATEST.Form ON DELETE restrict ON UPDATE cascade
 );
COMMENT ON TABLE TILDATEST.FormAnswer IS E'Form answers';
COMMENT ON COLUMN TILDATEST.FormAnswer."refnum" IS E'The primary key for this record';
COMMENT ON COLUMN TILDATEST.FormAnswer."formRefnum" IS E'The form''s refnum';
COMMENT ON COLUMN TILDATEST.FormAnswer."field" IS E'question/field id';
COMMENT ON COLUMN TILDATEST.FormAnswer."value" IS E'answer value';
COMMENT ON COLUMN TILDATEST.FormAnswer."created" IS E'The timestamp for when the record was created. (TILDATEST.FormAnswer)';
COMMENT ON COLUMN TILDATEST.FormAnswer."lastUpdated" IS E'The timestamp for when the record was last updated. (TILDATEST.FormAnswer)';
COMMENT ON COLUMN TILDATEST.FormAnswer."deleted" IS E'The timestamp for when the record was deleted. (TILDATEST.FormAnswer)';
CREATE UNIQUE INDEX IF NOT EXISTS FormAnswer_FormAnswer ON TILDATEST.FormAnswer ("formRefnum", "field");
CREATE INDEX IF NOT EXISTS FormAnswer_All ON TILDATEST.FormAnswer ("lastUpdated" ASC);
delete from TILDA.Key where "name" = 'TILDATEST.FORMANSWER';
insert into TILDA.Key ("refnum", "name", "max", "count", "created", "lastUpdated") values ((select COALESCE(max("refnum"),0)+1 from TILDA.Key), 'TILDATEST.FORMANSWER',(select COALESCE(max("refnum"),0)+1 from TILDATEST.FormAnswer), 500, current_timestamp, current_timestamp);

Previous	Main	Next
<-- Part 2	Main	Part 4 -->