-
Notifications
You must be signed in to change notification settings - Fork 0
/
foodfydb.sql
131 lines (114 loc) · 3.19 KB
/
foodfydb.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
-- COMANDOS PARA DELETAR E CRIAR O BANCO DE DADOS
-- DROP DATABASE IF EXISTS foodfy;
-- CREATE DATABASE foodfy;
CREATE TABLE "users" (
"id" SERIAL PRIMARY KEY,
"name" TEXT NOT NULL,
"email" TEXT UNIQUE NOT NULL,
"password" TEXT NOT NULL,
"reset_token" TEXT,
"reset_token_expires" TEXT,
"is_admin" BOOLEAN DEFAULT false,
"created_at" TIMESTAMP DEFAULT(now()),
"updated_at" TIMESTAMP DEFAULT(now())
);
CREATE TABLE "files" (
"id" SERIAL PRIMARY KEY,
"name" text NOT NULL,
"path" text NOT NULL
);
CREATE TABLE "chefs" (
"id" SERIAL PRIMARY KEY,
"name" text NOT NULL,
"file_id" integer NOT NULL REFERENCES "files" ("id"),
"created_at" timestamp DEFAULT (now()),
"updated_at" timestamp DEFAULT (now())
);
CREATE TABLE "recipes" (
"id" SERIAL PRIMARY KEY,
"chef_id" integer NOT NULL REFERENCES "chefs" ("id"),
"user_id" integer NOT NULL REFERENCES "users" ("id") ON DELETE CASCADE,
"title" text NOT NULL,
"ingredients" text[] NOT NULL,
"preparation" text[] NOT NULL,
"information" text NOT NULL,
"created_at" timestamp DEFAULT (now()),
"updated_at" timestamp DEFAULT (now())
);
CREATE TABLE "recipes_files" (
"id" SERIAL PRIMARY KEY,
"recipe_id" integer REFERENCES "recipes" ("id") ON DELETE CASCADE,
"file_id" integer REFERENCES "files" ("id")
);
CREATE TABLE "session" (
"sid" varchar NOT NULL COLLATE "default",
"sess" json NOT NULL,
"expire" timestamp(6) NOT NULL
)
WITH (OIDS=FALSE);
ALTER TABLE "session"
ADD CONSTRAINT "session_pkey"
PRIMARY KEY ("sid") NOT DEFERRABLE INITIALLY IMMEDIATE;
--create procedure
CREATE FUNCTION trigger_set_timestamp()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
--auto updated_at recipes
CREATE TRIGGER set_timestamp
BEFORE UPDATE ON recipes
FOR EACH ROW
EXECUTE PROCEDURE trigger_set_timestamp();
--auto updated_at chefs
CREATE TRIGGER set_timestamp
BEFORE UPDATE ON chefs
FOR EACH ROW
EXECUTE PROCEDURE trigger_set_timestamp();
--auto updated_at users
CREATE TRIGGER set_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE PROCEDURE trigger_set_timestamp();
-- user cascade function
CREATE OR REPLACE FUNCTION delete_files_when_recipes_files_row_was_deleted()
RETURNS TRIGGER AS $$
BEGIN
EXECUTE ('DELETE FROM files
WHERE id = $1')
USING OLD.file_id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- user cascade trigger
CREATE TRIGGER delete_recipes_files
AFTER DELETE ON recipes_files
FOR EACH ROW
EXECUTE PROCEDURE delete_files_when_recipes_files_row_was_deleted();
-- DELETE CASCADE
ALTER TABLE "recipes_files"
DROP CONSTRAINT recipes_files_recipe_id_fkey,
ADD CONSTRAINT recipes_files_recipe_id_fkey
FOREIGN KEY ("recipe_id")
REFERENCES recipes("id")
ON DELETE CASCADE;
ALTER TABLE "recipes_files"
DROP CONSTRAINT recipes_files_file_id_fkey,
ADD CONSTRAINT recipes_files_file_id_fkey
FOREIGN KEY ("file_id")
REFERENCES files("id")
ON DELETE CASCADE;
-- to run seeds
DELETE FROM recipes_files;
DELETE FROM recipes;
DELETE FROM chefs;
DELETE FROM files;
DELETE FROM users;
-- restart sequence auto_increment from tables ids
ALTER SEQUENCE files_id_seq RESTART WITH 1;
ALTER SEQUENCE recipes_files_id_seq RESTART WITH 1;
ALTER SEQUENCE chefs_id_seq RESTART WITH 1;
ALTER SEQUENCE recipes_id_seq RESTART WITH 1;
ALTER SEQUENCE users_id_seq RESTART WITH 1;