MrRpbrZE

1-- =============================================================================
2-- Diagram Name: s1
3-- Created on: 17/11/2011 18:08:52
4-- Diagram Version: 0.0.5
5-- =============================================================================
6
7
8DROP TABLE IF EXISTS "users" CASCADE;
9
10CREATE TABLE "users" (
11	"id" SERIAL NOT NULL,
12	"cpf" char(14) NOT NULL,
13	"pwd" varchar(72) NOT NULL,
14	CONSTRAINT "users_cpf_unique" UNIQUE("cpf"),
15	CONSTRAINT "users_id_pk" PRIMARY KEY("id"),
16	CONSTRAINT "users_cpf_length" CHECK(LENGTH(cpf) = 14)
17);
18
19
20DROP TABLE IF EXISTS "user_tokens" CASCADE;
21
22CREATE TABLE "user_tokens" (
23	"id" SERIAL NOT NULL,
24	"user_id" int4 NOT NULL,
25	"token" varchar(40) NOT NULL,
26	"created_at" date NOT NULL,
27	CONSTRAINT "user_tokens_id_pk" PRIMARY KEY("id"),
28	CONSTRAINT "user_tokens_token_unique" UNIQUE("token")
29);
30
31
32DROP TABLE IF EXISTS "companies" CASCADE;
33
34CREATE TABLE "companies" (
35	"id" int4 NOT NULL,
36	"cnpj" char(18) NOT NULL,
37	"name" varchar(100) NOT NULL,
38	CONSTRAINT "companies_id_pk" PRIMARY KEY("id"),
39	CONSTRAINT "companies_cnpj_unique" UNIQUE("cnpj"),
40	CONSTRAINT "companies_cnpj_length" CHECK(LENGTH(cnpj = 18))
41);
42
43
44DROP TABLE IF EXISTS "profiles" CASCADE;
45
46CREATE TABLE "profiles" (
47	"id" int4 NOT NULL,
48	"user_id" int4,
49	"company_id" int4,
50	CONSTRAINT "profiles_id_pk" PRIMARY KEY("id")
51);
52
53
54DROP TABLE IF EXISTS "company_groups" CASCADE;
55
56CREATE TABLE "company_groups" (
57	"id" int4 NOT NULL,
58	"name" varchar(200) NOT NULL,
59	CONSTRAINT "company_groups_id_pk" PRIMARY KEY("id"),
60	CONSTRAINT "company_groups_name_unique" UNIQUE("name")
61);
62
63
64DROP TABLE IF EXISTS "companies_in_company_groups" CASCADE;
65
66CREATE TABLE "companies_in_company_groups" (
67	"id" SERIAL NOT NULL,
68	"company_group_id" int4 NOT NULL,
69	"company_id" int4 NOT NULL,
70	"is_headoffice" bool DEFAULT False,
71	CONSTRAINT "companies_in_company_groups_id_pk" PRIMARY KEY("id")
72);
73
74
75DROP TABLE IF EXISTS "resources" CASCADE;
76
77CREATE TABLE "resources" (
78	"id" int4 NOT NULL,
79	"name" varchar(100) NOT NULL,
80	CONSTRAINT "resources_id_pk" PRIMARY KEY("id"),
81	CONSTRAINT "resources_name_unique" UNIQUE("name")
82);
83
84
85DROP TABLE IF EXISTS "access_clearances" CASCADE;
86
87CREATE TABLE "access_clearances" (
88	"id" SERIAL NOT NULL,
89	"resource_id" int4 NOT NULL,
90	"profile_id" int4 NOT NULL,
91	"until" date,
92	CONSTRAINT "access_clearances_id_pk" PRIMARY KEY("id")
93);
94
95
96
97ALTER TABLE "user_tokens" ADD CONSTRAINT "user_tokens_to_users" FOREIGN KEY ("user_id")
98	REFERENCES "users"("id")
99	MATCH SIMPLE
100	ON DELETE RESTRICT
101	ON UPDATE CASCADE
102	NOT DEFERRABLE;
103
104ALTER TABLE "profiles" ADD CONSTRAINT "profiles_to_users" FOREIGN KEY ("user_id")
105	REFERENCES "users"("id")
106	MATCH SIMPLE
107	ON DELETE RESTRICT
108	ON UPDATE CASCADE
109	NOT DEFERRABLE;
110
111ALTER TABLE "profiles" ADD CONSTRAINT "profiles_to_companies" FOREIGN KEY ("company_id")
112	REFERENCES "companies"("id")
113	MATCH SIMPLE
114	ON DELETE RESTRICT
115	ON UPDATE CASCADE
116	NOT DEFERRABLE;
117
118ALTER TABLE "companies_in_company_groups" ADD CONSTRAINT "companies_in_company_groups_to_company_groups" FOREIGN KEY ("company_group_id")
119	REFERENCES "company_groups"("id")
120	MATCH SIMPLE
121	ON DELETE RESTRICT
122	ON UPDATE CASCADE
123	NOT DEFERRABLE;
124
125ALTER TABLE "companies_in_company_groups" ADD CONSTRAINT "companies_in_company_groups_to_companies" FOREIGN KEY ("company_id")
126	REFERENCES "companies"("id")
127	MATCH SIMPLE
128	ON DELETE RESTRICT
129	ON UPDATE CASCADE
130	NOT DEFERRABLE;
131
132ALTER TABLE "access_clearances" ADD CONSTRAINT "access_clearances_to_resources" FOREIGN KEY ("resource_id")
133	REFERENCES "resources"("id")
134	MATCH SIMPLE
135	ON DELETE RESTRICT
136	ON UPDATE CASCADE
137	NOT DEFERRABLE;
138
139ALTER TABLE "access_clearances" ADD CONSTRAINT "access_clearances_to_profiles" FOREIGN KEY ("profile_id")
140	REFERENCES "profiles"("id")
141	MATCH SIMPLE
142	ON DELETE RESTRICT
143	ON UPDATE CASCADE
144	NOT DEFERRABLE;