Project

General

Profile

update_2.7_to_3.0.sql

SQL script needed to add tables for the Shift Scheduler - Igor Mandrichenko, 03/16/2012 03:54 PM

 
1
CREATE TABLE "ecl_institution" (
2
    "full_name" text NOT NULL,
3
    "nick_name" varchar(132) NOT NULL PRIMARY KEY,
4
    "address" text NOT NULL
5
)
6
;
7
CREATE TABLE "ecl_scheduleinterval" (
8
    "id" serial NOT NULL PRIMARY KEY,
9
    "begin_date" date NOT NULL UNIQUE,
10
    "end_date" date UNIQUE,
11
    "locked" boolean default 'false',
12
    "name" varchar(132) not null unique
13
)
14
;
15
CREATE TABLE "ecl_shift" (
16
    "id" serial NOT NULL PRIMARY KEY,
17
    "schedule_interval_id" integer NOT NULL REFERENCES "ecl_scheduleinterval" ("id") DEFERRABLE INITIALLY DEFERRED,
18
    "name" varchar(132) NOT NULL,
19
    "begin_day" smallint NOT NULL,
20
    "end_day" smallint NOT NULL,
21
    "begin_time" time NOT NULL,
22
    "end_time" time NOT NULL,
23
    "auto_week" boolean default 'True'
24
)
25
;
26
CREATE TABLE "ecl_shiftrole" (
27
    "id" serial NOT NULL PRIMARY KEY,
28
    "shift_id" integer NOT NULL REFERENCES "ecl_shift" ("id") DEFERRABLE INITIALLY DEFERRED,
29
    "name" varchar(132) NOT NULL,
30
    "points" double precision NOT NULL
31
)
32
;
33
CREATE TABLE "ecl_shiftassignment" (
34
    "id" serial NOT NULL PRIMARY KEY,
35
    "shift_role_id" integer NOT NULL REFERENCES "ecl_shiftrole" ("id") DEFERRABLE INITIALLY DEFERRED,
36
    "begin_date" date NOT NULL,
37
    "user_id" integer REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED,
38
    "institution_id" varchar(132) REFERENCES "ecl_institution" ("nick_name") DEFERRABLE INITIALLY DEFERRED,
39
    "reminder_sent" boolean default 'False',
40
    UNIQUE ("shift_role_id", "begin_date")
41
)
42
;
43
CREATE TABLE "ecl_swaprequest" (
44
    "id" serial NOT NULL PRIMARY KEY,
45
    "assignment_id" integer NOT NULL REFERENCES "ecl_shiftassignment" ("id") DEFERRABLE INITIALLY DEFERRED,
46
    "requestor_id" integer NOT NULL REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED,
47
    "timestamp" timestamp with time zone NOT NULL,
48
    UNIQUE ("assignment_id", "requestor_id")
49
)
50
;
51
CREATE TABLE "ecl_skill_members" (
52
    "id" serial NOT NULL PRIMARY KEY,
53
    "skill_id" integer NOT NULL,
54
    "user_id" integer NOT NULL REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED,
55
    UNIQUE ("skill_id", "user_id")
56
)
57
;
58
CREATE TABLE "ecl_skill" (
59
    "id" serial NOT NULL PRIMARY KEY,
60
    "name" varchar(132) NOT NULL UNIQUE
61
)
62
;
63
ALTER TABLE "ecl_skill_members" ADD CONSTRAINT "skill_id_refs_id_f6803ab5" FOREIGN KEY ("skill_id") REFERENCES "ecl_skill" ("id") DEFERRABLE INITIALLY DEFERRED;
64
CREATE TABLE "ecl_contactinfo" (
65
    "id" serial NOT NULL PRIMARY KEY,
66
    "user_id" integer NOT NULL REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED,
67
    "type" varchar(132) NOT NULL,
68
    "contact" varchar(132) NOT NULL,
69
    UNIQUE ("type", "user_id")
70
)
71
;
72
CREATE TABLE "ecl_affiliation" (
73
    "id" serial NOT NULL PRIMARY KEY,
74
    "institution_id" varchar(132) NOT NULL REFERENCES "ecl_institution" ("nick_name") DEFERRABLE INITIALLY DEFERRED,
75
    "member_id" integer NOT NULL REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED,
76
    "home" boolean NOT NULL,
77
    "representative" boolean NOT NULL,
78
    UNIQUE ("institution_id", "member_id")
79
)
80
;
81

    
82
CREATE TABLE "ecl_expertise" (
83
    "name" varchar(132) NOT NULL PRIMARY KEY,
84
    "description" text NOT NULL
85
);
86

    
87
CREATE TABLE "ecl_configuration" (
88
    "name" varchar(132) NOT NULL PRIMARY KEY,
89
    "value" varchar(1024)
90
)
91
;
92

    
93
alter table ecl_userprofile 
94
    add column "status" varchar(2),
95
    add column "shifter" boolean NOT NULL default 'true',
96
    add column "author" boolean NOT NULL default 'true',
97
    add column "expertise_id" varchar(132) REFERENCES "ecl_expertise" ("name") DEFERRABLE INITIALLY DEFERRED,
98
    add column "shift_start_date" date,
99
    add column "email_shift_reminders_interval" int,
100
    add column "email_shift_confirmations" boolean default 'false';
101
    
102