====== pears.Collection ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== 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 =====
  * CollectionID

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| staff | staffid | [[database:tables:pears_staff|pears.staff (staffid)]] |  |
| CollectionType | CollectionTypeID | [[database:tables:pears_collectiontype|pears.CollectionType (CollectionTypeID)]] | ON DELETE CASCADE |
| CollectionParent | Parent | [[database:tables:pears_collection|pears.Collection (CollectionID)]] | ON DELETE SET NULL |

===== Referenced By =====
^ Table ^ Constraint ^ Columns ^ Referenced columns ^
| [[database:tables:pears_collection|pears.Collection]] | CollectionParent | Parent | CollectionID |
| [[database:tables:pears_collectionchat|pears.CollectionChat]] | Collection | CollectionID | CollectionID |
| [[database:tables:pears_collectionchatread|pears.CollectionChatRead]] | Collection | CollectionID | CollectionID |
| [[database:tables:pears_collectionusers|pears.CollectionUsers]] | Collection | CollectionID | CollectionID |
| [[database:tables:pears_diary|pears.diary]] | collection | collectionid | CollectionID |

===== Indexes =====
  * No indexes found.

===== Triggers =====
^ Name ^ Timing ^ Event ^
| CollectionDelete | before | delete order 1 |

===== Original SQL =====
<code 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
</code>