pears.criterion [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.criterion ======
<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 =====
Individual instances of search criteria that make up Candidate & Vacancy Requirements. Stored Searches etc.

===== Columns =====
^ Column ^ Type ^ Null ^ Default ^ Comment ^
| **searchlocation** | char(3) | NOT NULL |  | Type of parent record which has id as its pk |
| **id** | char(20) | NOT NULL |  | ID of parent record |
| **critid** | binary(20) | NOT NULL |  | ID and location of this criterion |
| sourcelocation | char(3) | NOT NULL |  | Dictionarylocation or Taglocation |
| dictionaryid | char(20) | NULL |  | If dictionary criterion else null |
| tagid | char(3) | NULL |  | If tag criterion else null |
| tagchoiceid | char(4) | NULL |  |  |
| notflag | smallint | NULL |  | 1 if not |
| matchtype | char(1) | NULL |  | =equals, -range, |starts with, *contains, }ge, {le |
| textvalue | char(250) | NULL |  |  |
| value | double | NULL |  |  |
| uppervalue | double | NULL |  | For ranges |
| taganytotal | smallint | NULL |  | +ve for any, any 2 etc. on lists, -1 indicates total for scored list criteria |
| extravalue | double | NULL |  | For extra date/time ranges |
| extrauppervalue | double | NULL |  | For extra date/time ranges |
| extramatchtype | char(1) | NULL |  | For extra date/time ranges =equals, -range, }ge, {le |
| TextIndexedField | char(50) | NULL |  | For text index search would contain values like person.cvtext |

===== Primary Key =====
  * searchlocation, id, critid

===== Foreign Keys =====
^ Constraint ^ Columns ^ References ^ Delete/update action ^
| tagchoice | sourcelocation, tagid, tagchoiceid | [[database:tables:pears_tagchoice|pears.tagchoice (taglocation, tagid, tagchoiceid)]] | ON DELETE CASCADE |
| tag | sourcelocation, tagid | [[database:tables:pears_tag|pears.tag (taglocation, tagid)]] | ON DELETE CASCADE |
| dictionary | sourcelocation, dictionaryid | [[database:tables:pears_dictionary|pears.dictionary (dictionarylocation, dictionaryid)]] | ON DELETE CASCADE |

===== Referenced By =====
  * No incoming foreign keys found.

===== Indexes =====
^ Name ^ Type ^ Columns ^ Detail ^
| criterionIDandLocation | Index | id, searchlocation |  |

===== Triggers =====
  * No triggers found.

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