pears.criterion

Individual instances of search criteria that make up Candidate & Vacancy Requirements. Stored Searches etc.

• searchlocation, id, critid

• No incoming foreign keys found.

• No triggers found.

-- IQX database structure split by table
-- Source: IQXDatabaseStructure - with comments.sql
-- Table: "pears"."criterion"
-- Table comment: Individual instances of search criteria that make up Candidate & Vacancy Requirements. Stored Searches etc.
-- Statement count: 20
 
CREATE TABLE "pears"."criterion" (
    "searchlocation"                 CHAR(3) NOT NULL
   ,"id"                             CHAR(20) NOT NULL
   ,"critid"                         BINARY(20) NOT NULL
   ,"sourcelocation"                 CHAR(3) NOT NULL
   ,"dictionaryid"                   CHAR(20) NULL
   ,"tagid"                          CHAR(3) NULL
   ,"tagchoiceid"                    CHAR(4) NULL
   ,"notflag"                        SMALLINT NULL
   ,"matchtype"                      CHAR(1) NULL
   ,"textvalue"                      CHAR(250) NULL
   ,"value"                          DOUBLE NULL
   ,"uppervalue"                     DOUBLE NULL
   ,"taganytotal"                    SMALLINT NULL
   ,"extravalue"                     DOUBLE NULL
   ,"extrauppervalue"                DOUBLE NULL
   ,"extramatchtype"                 CHAR(1) NULL
   ,"TextIndexedField"               CHAR(50) NULL
   ,PRIMARY KEY ("searchlocation" ASC,"id" ASC,"critid" ASC) 
)
GO
 
 
COMMENT ON COLUMN "pears"."criterion"."searchlocation" IS 
	'Type of parent record which has id as its pk'
GO
 
 
COMMENT ON COLUMN "pears"."criterion"."id" IS 
	'ID of parent record'
GO
 
 
COMMENT ON COLUMN "pears"."criterion"."critid" IS 
	'ID and location of this criterion'
GO
 
 
COMMENT ON COLUMN "pears"."criterion"."sourcelocation" IS 
	'Dictionarylocation or Taglocation'
GO
 
 
COMMENT ON COLUMN "pears"."criterion"."dictionaryid" IS 
	'If dictionary criterion else null'
GO
 
 
COMMENT ON COLUMN "pears"."criterion"."tagid" IS 
	'If tag criterion else null'
GO
 
 
COMMENT ON COLUMN "pears"."criterion"."notflag" IS 
	'1 if not'
GO
 
 
COMMENT ON COLUMN "pears"."criterion"."matchtype" IS 
	'=equals, -range, |starts with, *contains, }ge, {le'
GO
 
 
COMMENT ON COLUMN "pears"."criterion"."uppervalue" IS 
	'For ranges'
GO
 
 
COMMENT ON COLUMN "pears"."criterion"."taganytotal" IS 
	'+ve for any, any 2 etc. on lists, -1 indicates total for scored list criteria'
GO
 
 
COMMENT ON COLUMN "pears"."criterion"."extravalue" IS 
	'For extra date/time ranges'
GO
 
 
COMMENT ON COLUMN "pears"."criterion"."extrauppervalue" IS 
	'For extra date/time ranges'
GO
 
 
COMMENT ON COLUMN "pears"."criterion"."extramatchtype" IS 
	'For extra date/time ranges =equals, -range, }ge, {le'
GO
 
 
COMMENT ON COLUMN "pears"."criterion"."TextIndexedField" IS 
	'For text index search would contain values like person.cvtext'
GO
 
 
COMMENT ON TABLE "pears"."criterion" IS 
	'Individual instances of search criteria that make up Candidate & Vacancy Requirements. Stored Searches etc.'
GO
 
 
ALTER TABLE "pears"."criterion"
    ADD FOREIGN KEY "tagchoice" ("sourcelocation" ASC,"tagid" ASC,"tagchoiceid" ASC)
    REFERENCES "pears"."tagchoice" ("taglocation","tagid","tagchoiceid")
    ON DELETE CASCADE
GO
 
 
ALTER TABLE "pears"."criterion"
    ADD FOREIGN KEY "tag" ("sourcelocation" ASC,"tagid" ASC)
    REFERENCES "pears"."tag" ("taglocation","tagid")
    ON DELETE CASCADE
GO
 
 
ALTER TABLE "pears"."criterion"
    ADD FOREIGN KEY "dictionary" ("sourcelocation" ASC,"dictionaryid" ASC)
    REFERENCES "pears"."dictionary" ("dictionarylocation","dictionaryid")
    ON DELETE CASCADE
GO
 
 
CREATE INDEX "criterionIDandLocation" ON "pears"."criterion"
    ( "id","searchlocation" )
GO