IQX Quick Menu

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

Description

For use by Auto-matching procedures

Column	Type	Null	Default	Comment
AutoMatchNotificationQueueID	bigint	NOT NULL	autoincrement
TempShiftPlanID	char(20)	NULL
PersonID	char(20)	NULL
Created	timestamp	NULL	current timestamp
EmailCompleted	timestamp	NULL		means has been used successfully
SMSCompleted	timestamp	NULL		means has been used successfully
PushCompleted	timestamp	NULL		means has been used successfully
NotRequired	tinyint	NULL	0	Remove by sweeper job?
ShiftGroup	char(20)	NULL
ShiftGroupOnly	tinyint	NULL	0
NewRating	smallint	NULL	0
NotNewRating	smallint	NULL	0
NewWorkers	smallint	NULL	0
QtyWorkers	smallint	NULL	0
LeadTime	integer	NOT NULL		in hours may not be needed
Urgency	smallint	NULL		may not be needed
Updated	timestamp	NULL		may not be needed
MaxEmails	integer	NULL
MaxSMS	integer	NULL
MaxPush	integer	NULL
MinDelayBetweenEmails	integer	NULL
MinDelayBetweenSMS	integer	NULL
MinDelayBetweenPush	integer	NULL
ContactStart	time	NULL		Start Sending
ContactEnd	time	NULL		Stop Sending
Email	smallint	NULL	0
SMS	smallint	NULL	0
Push	smallint	NULL	0
EmailSent	timestamp	NULL
SMSSent	timestamp	NULL
PushSent	timestamp	NULL
EmailError	char(255)	NULL
SMSError	char(255)	NULL
PushError	char(255)	NULL
Locked	timestamp	NULL		job using this
DivisionID	char(20)	NULL

AutoMatchNotificationQueueID

Constraint	Columns	References	Delete/update action
tempshiftplan	TempShiftPlanID	pears.TempShiftPlan (TempShiftPlanID)	ON DELETE CASCADE
person	PersonID	pears.Person (personid)	ON DELETE CASCADE
division	DivisionID	pears.Division (divisionid)	ON DELETE CASCADE

No incoming foreign keys found.

No indexes found.

No triggers found.

-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."AutoMatchNotificationQueue"
-- Table comment: For use by Auto-matching procedures
-- Statement count: 15
 
CREATE TABLE "pears"."AutoMatchNotificationQueue" (
    "AutoMatchNotificationQueueID"   BIGINT NOT NULL DEFAULT autoincrement
   ,"TempShiftPlanID"                CHAR(20) NULL
   ,"PersonID"                       CHAR(20) NULL
   ,"Created"                        TIMESTAMP NULL DEFAULT CURRENT TIMESTAMP
   ,"EmailCompleted"                 TIMESTAMP NULL
   ,"SMSCompleted"                   TIMESTAMP NULL
   ,"PushCompleted"                  TIMESTAMP NULL
   ,"NotRequired"                    tinyint NULL DEFAULT 0
   ,"ShiftGroup"                     CHAR(20) NULL
   ,"ShiftGroupOnly"                 tinyint NULL DEFAULT 0
   ,"NewRating"                      SMALLINT NULL DEFAULT 0
   ,"NotNewRating"                   SMALLINT NULL DEFAULT 0
   ,"NewWorkers"                     SMALLINT NULL DEFAULT 0
   ,"QtyWorkers"                     SMALLINT NULL DEFAULT 0
   ,"LeadTime"                       INTEGER NOT NULL
   ,"Urgency"                        SMALLINT NULL
   ,"Updated"                        TIMESTAMP NULL
   ,"MaxEmails"                      INTEGER NULL
   ,"MaxSMS"                         INTEGER NULL
   ,"MaxPush"                        INTEGER NULL
   ,"MinDelayBetweenEmails"          INTEGER NULL
   ,"MinDelayBetweenSMS"             INTEGER NULL
   ,"MinDelayBetweenPush"            INTEGER NULL
   ,"ContactStart"                   TIME NULL
   ,"ContactEnd"                     TIME NULL
   ,"Email"                          SMALLINT NULL DEFAULT 0
   ,"SMS"                            SMALLINT NULL DEFAULT 0
   ,"Push"                           SMALLINT NULL DEFAULT 0
   ,"EmailSent"                      TIMESTAMP NULL
   ,"SMSSent"                        TIMESTAMP NULL
   ,"PushSent"                       TIMESTAMP NULL
   ,"EmailError"                     CHAR(255) NULL
   ,"SMSError"                       CHAR(255) NULL
   ,"PushError"                      CHAR(255) NULL
   ,"Locked"                         TIMESTAMP NULL
   ,"DivisionID"                     CHAR(20) NULL
   ,PRIMARY KEY ("AutoMatchNotificationQueueID" ASC) 
)
GO
 
 
COMMENT ON COLUMN "pears"."AutoMatchNotificationQueue"."EmailCompleted" IS 
	'means has been used successfully'
GO
 
 
COMMENT ON COLUMN "pears"."AutoMatchNotificationQueue"."SMSCompleted" IS 
	'means has been used successfully'
GO
 
 
COMMENT ON COLUMN "pears"."AutoMatchNotificationQueue"."PushCompleted" IS 
	'means has been used successfully'
GO
 
 
COMMENT ON COLUMN "pears"."AutoMatchNotificationQueue"."NotRequired" IS 
	'Remove by sweeper job?'
GO
 
 
COMMENT ON COLUMN "pears"."AutoMatchNotificationQueue"."LeadTime" IS 
	'in hours may not be needed'
GO
 
 
COMMENT ON COLUMN "pears"."AutoMatchNotificationQueue"."Urgency" IS 
	'may not be needed'
GO
 
 
COMMENT ON COLUMN "pears"."AutoMatchNotificationQueue"."Updated" IS 
	'may not be needed'
GO
 
 
COMMENT ON COLUMN "pears"."AutoMatchNotificationQueue"."ContactStart" IS 
	'Start Sending'
GO
 
 
COMMENT ON COLUMN "pears"."AutoMatchNotificationQueue"."ContactEnd" IS 
	'Stop Sending'
GO
 
 
COMMENT ON COLUMN "pears"."AutoMatchNotificationQueue"."Locked" IS 
	'job using this'
GO
 
 
COMMENT ON TABLE "pears"."AutoMatchNotificationQueue" IS 
	'For use by Auto-matching procedures'
GO
 
 
ALTER TABLE "pears"."AutoMatchNotificationQueue"
    ADD FOREIGN KEY "tempshiftplan" ("TempShiftPlanID" ASC)
    REFERENCES "pears"."TempShiftPlan" ("TempShiftPlanID")
    ON DELETE CASCADE
GO
 
 
ALTER TABLE "pears"."AutoMatchNotificationQueue"
    ADD FOREIGN KEY "person" ("PersonID" ASC)
    REFERENCES "pears"."Person" ("personid")
    ON DELETE CASCADE
GO
 
 
ALTER TABLE "pears"."AutoMatchNotificationQueue"
    ADD FOREIGN KEY "division" ("DivisionID" ASC)
    REFERENCES "pears"."Division" ("divisionid")
    ON DELETE CASCADE
GO

pears.AutoMatchNotificationQueue

Description

Columns

Primary Key

Foreign Keys

Referenced By

Indexes

Triggers

Original SQL

iqx