Table of Contents



pears.Collection

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.

Description

Links individual objects within a Collection.

Columns

Column Type Null Default Comment
CollectionID char(20) NOT NULL
Parent char(20) NULL
Name char(60) NOT NULL
staffid char(20) NULL
IDType char(2) NULL H=header P=person C=company E=employment V=vacancy L=placement T=timesheet CE=contact Event R=progress I=invoice TQ=timesheet query
ID char(20) NULL
CollectionTypeID char(20) NULL
WhenCreated timestamp NULL current timestamp
WhoCreated char(20) NULL
WhenArchived timestamp NULL
WhoArchived char(20) NULL
DateFrom date NULL
DateTo date NULL
Notes long varchar NULL
OtherNotes long varchar NULL
sortorder integer NULL
IsTemplate smallint NOT NULL 0 Only applies to top level

Primary Key

Foreign Keys

Constraint Columns References Delete/update action
staff staffid pears.staff (staffid)
CollectionType CollectionTypeID pears.CollectionType (CollectionTypeID) ON DELETE CASCADE
CollectionParent Parent pears.Collection (CollectionID) ON DELETE SET NULL

Referenced By

Table Constraint Columns Referenced columns
pears.Collection CollectionParent Parent CollectionID
pears.CollectionChat Collection CollectionID CollectionID
pears.CollectionChatRead Collection CollectionID CollectionID
pears.CollectionUsers Collection CollectionID CollectionID
pears.diary collection collectionid CollectionID

Indexes

Triggers

Name Timing Event
CollectionDelete before delete order 1

Original SQL

-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."Collection"
-- Table comment: Links individual objects within a Collection.
-- Statement count: 9
 
CREATE TABLE "pears"."Collection" (
    "CollectionID"                   CHAR(20) NOT NULL
   ,"Parent"                         CHAR(20) NULL
   ,"Name"                           CHAR(60) NOT NULL
   ,"staffid"                        CHAR(20) NULL
   ,"IDType"                         CHAR(2) NULL
   ,"ID"                             CHAR(20) NULL
   ,"CollectionTypeID"               CHAR(20) NULL
   ,"WhenCreated"                    TIMESTAMP NULL DEFAULT CURRENT TIMESTAMP
   ,"WhoCreated"                     CHAR(20) NULL
   ,"WhenArchived"                   TIMESTAMP NULL
   ,"WhoArchived"                    CHAR(20) NULL
   ,"DateFrom"                       DATE NULL
   ,"DateTo"                         DATE NULL
   ,"Notes"                          long VARCHAR NULL
   ,"OtherNotes"                     long VARCHAR NULL
   ,"sortorder"                      INTEGER NULL
   ,"IsTemplate"                     SMALLINT NOT NULL DEFAULT 0
   ,PRIMARY KEY ("CollectionID" ASC) 
)
GO
 
 
COMMENT ON COLUMN "pears"."Collection"."IDType" IS 
	'H=header P=person C=company E=employment V=vacancy L=placement T=timesheet CE=contact Event R=progress I=invoice TQ=timesheet query'
GO
 
 
COMMENT ON COLUMN "pears"."Collection"."IsTemplate" IS 
	'Only applies to top level'
GO
 
 
COMMENT ON TABLE "pears"."Collection" IS 
	'Links individual objects within a Collection.'
GO
 
 
ALTER TABLE "pears"."Collection"
    ADD FOREIGN KEY "staff" ("staffid" ASC)
    REFERENCES "pears"."staff" ("staffid")
GO
 
 
ALTER TABLE "pears"."Collection"
    ADD FOREIGN KEY "CollectionType" ("CollectionTypeID" ASC)
    REFERENCES "pears"."CollectionType" ("CollectionTypeID")
    ON DELETE CASCADE
GO
 
 
ALTER TABLE "pears"."Collection"
    ADD FOREIGN KEY "CollectionParent" ("Parent" ASC)
    REFERENCES "pears"."Collection" ("CollectionID")
    ON DELETE SET NULL
GO
 
 
CREATE TRIGGER "CollectionDelete" BEFORE DELETE ORDER 1 ON
"pears"."Collection"
REFERENCING OLD AS "old_name"
FOR each ROW
BEGIN
  DELETE FROM "collection" WHERE "parent" = "old_name"."collectionid"
END
GO
 
 
COMMENT TO PRESERVE FORMAT ON TRIGGER "pears"."Collection"."CollectionDelete" IS 
{CREATE TRIGGER CollectionDelete 
 BEFORE DELETE ORDER 1 ON
pears.Collection
REFERENCING OLD AS old_name
FOR each ROW
BEGIN
  DELETE FROM collection WHERE parent = old_name.collectionid
END
}
GO