Skip to content

Commit 51aa072

Browse files
LucJosinLucJosin
committed
Add user, role and project models and schema
1 parent e828110 commit 51aa072

5 files changed

Lines changed: 564 additions & 110 deletions

File tree

‎docker/postgres/init/schema.sql‎

Lines changed: 178 additions & 106 deletions
Original file line numberDiff line numberDiff line change
@@ -14,26 +14,98 @@ BEGIN
1414
END;
1515
$$ language 'plpgsql';
1616

17+
--
18+
-- JWT Roles
19+
--
20+
CREATE TABLE IF NOT EXISTS roles (
21+
id INTEGER GENERATED ALWAYS AS IDENTITY,
22+
uuid UUID PRIMARY KEY UNIQUE NOT NULL DEFAULT uuid_generate_v4(),
23+
name VARCHAR(50) NOT NULL,
24+
description VARCHAR(255),
25+
created_at TIMESTAMP NOT NULL DEFAULT now(),
26+
updated_at TIMESTAMP NOT NULL DEFAULT now()
27+
);
28+
29+
CREATE TRIGGER handle_updated_at_field BEFORE
30+
UPDATE ON roles
31+
FOR EACH ROW
32+
EXECUTE PROCEDURE update_updated_at_field();
33+
34+
--
35+
-- JWT Users
36+
--
37+
CREATE TABLE IF NOT EXISTS users (
38+
id INTEGER GENERATED ALWAYS AS IDENTITY,
39+
uuid UUID PRIMARY KEY UNIQUE NOT NULL DEFAULT uuid_generate_v4(),
40+
nickname VARCHAR(50) NOT NULL,
41+
email VARCHAR(50) NOT NULL,
42+
password VARCHAR(255) NOT NULL,
43+
created_at TIMESTAMP NOT NULL DEFAULT now(),
44+
updated_at TIMESTAMP NOT NULL DEFAULT now()
45+
);
46+
47+
CREATE TRIGGER handle_updated_at_field BEFORE
48+
UPDATE ON users
49+
FOR EACH ROW
50+
EXECUTE PROCEDURE update_updated_at_field();
51+
52+
--
53+
-- JWT Users roles
54+
--
55+
CREATE TABLE IF NOT EXISTS users_roles (
56+
role_uuid UUID NOT NULL REFERENCES roles(uuid),
57+
user_uuid UUID NOT NULL,
58+
CONSTRAINT fk_user_uuid
59+
FOREIGN KEY(user_uuid)
60+
REFERENCES users(uuid)
61+
ON DELETE CASCADE
62+
);
63+
64+
--
65+
-- Projects
66+
--
67+
CREATE TABLE IF NOT EXISTS projects (
68+
id INTEGER GENERATED ALWAYS AS IDENTITY,
69+
uuid UUID PRIMARY KEY UNIQUE NOT NULL DEFAULT uuid_generate_v4(),
70+
name VARCHAR(50) NOT NULL,
71+
description VARCHAR(255) NOT NULL,
72+
url TEXT NOT NULL,
73+
git_url TEXT NOT NULL,
74+
tags VARCHAR(25) ARRAY,
75+
user_uuid UUID NOT NULL,
76+
created_at TIMESTAMP NOT NULL DEFAULT now(),
77+
updated_at TIMESTAMP NOT NULL DEFAULT now(),
78+
CONSTRAINT fk_user_uuid
79+
FOREIGN KEY(user_uuid)
80+
REFERENCES users(uuid)
81+
ON DELETE CASCADE
82+
);
83+
84+
CREATE TRIGGER handle_updated_at_field BEFORE
85+
UPDATE ON projects
86+
FOR EACH ROW
87+
EXECUTE PROCEDURE update_updated_at_field();
88+
1789
--
1890
-- Characters
1991
-- Gender ref: ISO/IEC 5218
2092
--
2193
CREATE TABLE IF NOT EXISTS characters (
22-
id INTEGER GENERATED ALWAYS AS IDENTITY,
23-
uuid UUID PRIMARY KEY UNIQUE NOT NULL DEFAULT uuid_generate_v4(),
24-
href VARCHAR(100) NOT NULL,
25-
first_name VARCHAR(50) NOT NULL,
26-
last_name VARCHAR(50) NOT NULL,
27-
nicknames VARCHAR ARRAY,
28-
birth_date TIMESTAMP,
29-
death_date TIMESTAMP,
30-
gender SMALLINT NOT NULL DEFAULT 0,
31-
thumbnail TEXT,
32-
actor VARCHAR(100) NOT NULL,
33-
images TEXT ARRAY,
34-
sources TEXT ARRAY,
35-
created_at TIMESTAMP NOT NULL DEFAULT now(),
36-
updated_at TIMESTAMP NOT NULL DEFAULT now()
94+
id INTEGER GENERATED ALWAYS AS IDENTITY,
95+
uuid UUID PRIMARY KEY UNIQUE NOT NULL DEFAULT uuid_generate_v4(),
96+
href VARCHAR(100) NOT NULL,
97+
first_name VARCHAR(50) NOT NULL,
98+
last_name VARCHAR(50) NOT NULL,
99+
nicknames VARCHAR ARRAY,
100+
birth_date TIMESTAMP,
101+
death_date TIMESTAMP,
102+
gender SMALLINT NOT NULL DEFAULT 0,
103+
thumbnail TEXT,
104+
actor VARCHAR(100) NOT NULL,
105+
images TEXT ARRAY,
106+
sources TEXT ARRAY,
107+
created_at TIMESTAMP NOT NULL DEFAULT now(),
108+
updated_at TIMESTAMP NOT NULL DEFAULT now()
37109
);
38110

39111
CREATE TRIGGER handle_updated_at_field BEFORE
@@ -45,24 +117,24 @@ EXECUTE PROCEDURE update_updated_at_field();
45117
-- Actors
46118
--
47119
CREATE TABLE IF NOT EXISTS actors (
48-
id INTEGER GENERATED ALWAYS AS IDENTITY,
49-
uuid UUID PRIMARY KEY UNIQUE NOT NULL DEFAULT uuid_generate_v4(),
50-
href VARCHAR(100) NOT NULL,
51-
first_name VARCHAR(50) NOT NULL,
52-
last_name VARCHAR(50) NOT NULL,
53-
nicknames VARCHAR ARRAY,
54-
birth_date TIMESTAMP,
55-
death_date TIMESTAMP,
56-
gender SMALLINT NOT NULL DEFAULT 0,
57-
nationality VARCHAR(50),
58-
seasons VARCHAR ARRAY,
59-
awards VARCHAR ARRAY,
60-
character VARCHAR(255) NOT NULL,
61-
thumbnail TEXT,
62-
images TEXT ARRAY,
63-
sources TEXT ARRAY,
64-
created_at TIMESTAMP NOT NULL DEFAULT now(),
65-
updated_at TIMESTAMP NOT NULL DEFAULT now()
120+
id INTEGER GENERATED ALWAYS AS IDENTITY,
121+
uuid UUID PRIMARY KEY UNIQUE NOT NULL DEFAULT uuid_generate_v4(),
122+
href VARCHAR(100) NOT NULL,
123+
first_name VARCHAR(50) NOT NULL,
124+
last_name VARCHAR(50) NOT NULL,
125+
nicknames VARCHAR ARRAY,
126+
birth_date TIMESTAMP,
127+
death_date TIMESTAMP,
128+
gender SMALLINT NOT NULL DEFAULT 0,
129+
nationality VARCHAR(50),
130+
seasons VARCHAR ARRAY,
131+
awards VARCHAR ARRAY,
132+
character VARCHAR(255) NOT NULL,
133+
thumbnail TEXT,
134+
images TEXT ARRAY,
135+
sources TEXT ARRAY,
136+
created_at TIMESTAMP NOT NULL DEFAULT now(),
137+
updated_at TIMESTAMP NOT NULL DEFAULT now()
66138
);
67139

68140
CREATE TRIGGER handle_updated_at_field BEFORE
@@ -90,21 +162,21 @@ CREATE TABLE IF NOT EXISTS actors_socials (
90162
-- Episodes
91163
--
92164
CREATE TABLE IF NOT EXISTS episodes (
93-
id INTEGER GENERATED ALWAYS AS IDENTITY,
94-
uuid UUID PRIMARY KEY UNIQUE NOT NULL DEFAULT uuid_generate_v4(),
95-
href VARCHAR(100) NOT NULL,
96-
title VARCHAR(255) NOT NULL,
97-
description TEXT NOT NULL,
98-
duration INTEGER NOT NULL,
99-
episode_num SMALLINT NOT NULL,
100-
next_episode VARCHAR(255),
101-
prev_episode VARCHAR(255),
102-
season VARCHAR(255) NOT NULL,
103-
thumbnail TEXT,
104-
images TEXT ARRAY,
105-
sources TEXT ARRAY,
106-
created_at TIMESTAMP NOT NULL DEFAULT now(),
107-
updated_at TIMESTAMP NOT NULL DEFAULT now()
165+
id INTEGER GENERATED ALWAYS AS IDENTITY,
166+
uuid UUID PRIMARY KEY UNIQUE NOT NULL DEFAULT uuid_generate_v4(),
167+
href VARCHAR(100) NOT NULL,
168+
title VARCHAR(255) NOT NULL,
169+
description TEXT NOT NULL,
170+
duration INTEGER NOT NULL,
171+
episode_num SMALLINT NOT NULL,
172+
next_episode VARCHAR(255),
173+
prev_episode VARCHAR(255),
174+
season VARCHAR(255) NOT NULL,
175+
thumbnail TEXT,
176+
images TEXT ARRAY,
177+
sources TEXT ARRAY,
178+
created_at TIMESTAMP NOT NULL DEFAULT now(),
179+
updated_at TIMESTAMP NOT NULL DEFAULT now()
108180
);
109181

110182
CREATE TRIGGER handle_updated_at_field BEFORE
@@ -116,26 +188,26 @@ EXECUTE PROCEDURE update_updated_at_field();
116188
-- Seasons
117189
--
118190
CREATE TABLE IF NOT EXISTS seasons (
119-
id INTEGER GENERATED ALWAYS AS IDENTITY,
120-
uuid UUID PRIMARY KEY UNIQUE NOT NULL DEFAULT uuid_generate_v4(),
121-
href VARCHAR(100) NOT NULL,
122-
title VARCHAR(255) NOT NULL,
123-
description TEXT NOT NULL,
124-
duration_total INTEGER NOT NULL,
125-
genres VARCHAR(50) ARRAY,
126-
season_num SMALLINT NOT NULL,
127-
release_date TIMESTAMP NOT NULL,
128-
next_season VARCHAR(255),
129-
prev_season VARCHAR(255),
130-
episodes VARCHAR ARRAY,
131-
soundtracks VARCHAR(255) ARRAY,
132-
trailers VARCHAR ARRAY,
133-
budget INTEGER,
134-
thumbnail TEXT,
135-
images TEXT ARRAY,
136-
sources TEXT ARRAY,
137-
created_at TIMESTAMP NOT NULL DEFAULT now(),
138-
updated_at TIMESTAMP NOT NULL DEFAULT now()
191+
id INTEGER GENERATED ALWAYS AS IDENTITY,
192+
uuid UUID PRIMARY KEY UNIQUE NOT NULL DEFAULT uuid_generate_v4(),
193+
href VARCHAR(100) NOT NULL,
194+
title VARCHAR(255) NOT NULL,
195+
description TEXT NOT NULL,
196+
duration_total INTEGER NOT NULL,
197+
genres VARCHAR(50) ARRAY,
198+
season_num SMALLINT NOT NULL,
199+
release_date TIMESTAMP NOT NULL,
200+
next_season VARCHAR(255),
201+
prev_season VARCHAR(255),
202+
episodes VARCHAR ARRAY,
203+
soundtracks VARCHAR(255) ARRAY,
204+
trailers VARCHAR ARRAY,
205+
budget INTEGER,
206+
thumbnail TEXT,
207+
images TEXT ARRAY,
208+
sources TEXT ARRAY,
209+
created_at TIMESTAMP NOT NULL DEFAULT now(),
210+
updated_at TIMESTAMP NOT NULL DEFAULT now()
139211
);
140212

141213
CREATE TRIGGER handle_updated_at_field BEFORE
@@ -147,16 +219,16 @@ EXECUTE PROCEDURE update_updated_at_field();
147219
-- Locations
148220
--
149221
CREATE TABLE IF NOT EXISTS locations (
150-
id INTEGER GENERATED ALWAYS AS IDENTITY,
151-
uuid UUID PRIMARY KEY UNIQUE NOT NULL DEFAULT uuid_generate_v4(),
152-
href VARCHAR(100) NOT NULL,
153-
name VARCHAR(255) NOT NULL,
154-
description TEXT NOT NULL,
155-
thumbnail TEXT,
156-
images TEXT ARRAY,
157-
sources TEXT ARRAY,
158-
created_at TIMESTAMP NOT NULL DEFAULT now(),
159-
updated_at TIMESTAMP NOT NULL DEFAULT now()
222+
id INTEGER GENERATED ALWAYS AS IDENTITY,
223+
uuid UUID PRIMARY KEY UNIQUE NOT NULL DEFAULT uuid_generate_v4(),
224+
href VARCHAR(100) NOT NULL,
225+
name VARCHAR(255) NOT NULL,
226+
description TEXT NOT NULL,
227+
thumbnail TEXT,
228+
images TEXT ARRAY,
229+
sources TEXT ARRAY,
230+
created_at TIMESTAMP NOT NULL DEFAULT now(),
231+
updated_at TIMESTAMP NOT NULL DEFAULT now()
160232
);
161233

162234
CREATE TRIGGER handle_updated_at_field BEFORE
@@ -168,19 +240,19 @@ EXECUTE PROCEDURE update_updated_at_field();
168240
-- Soundtracks
169241
--
170242
CREATE TABLE IF NOT EXISTS soundtracks (
171-
id INTEGER GENERATED ALWAYS AS IDENTITY,
172-
uuid UUID PRIMARY KEY UNIQUE NOT NULL DEFAULT uuid_generate_v4(),
173-
href VARCHAR(100) NOT NULL,
174-
name VARCHAR(255) NOT NULL,
175-
duration INTEGER NOT NULL,
176-
artist VARCHAR(255) NOT NULL,
177-
album VARCHAR (255),
178-
release_date TIMESTAMP NOT NULL,
179-
urls TEXT ARRAY NOT NULL DEFAULT '{}',
180-
thumbnail TEXT,
181-
sources TEXT ARRAY,
182-
created_at TIMESTAMP NOT NULL DEFAULT now(),
183-
updated_at TIMESTAMP NOT NULL DEFAULT now()
243+
id INTEGER GENERATED ALWAYS AS IDENTITY,
244+
uuid UUID PRIMARY KEY UNIQUE NOT NULL DEFAULT uuid_generate_v4(),
245+
href VARCHAR(100) NOT NULL,
246+
name VARCHAR(255) NOT NULL,
247+
duration INTEGER NOT NULL,
248+
artist VARCHAR(255) NOT NULL,
249+
album VARCHAR (255),
250+
release_date TIMESTAMP NOT NULL,
251+
urls TEXT ARRAY NOT NULL,
252+
thumbnail TEXT,
253+
sources TEXT ARRAY,
254+
created_at TIMESTAMP NOT NULL DEFAULT now(),
255+
updated_at TIMESTAMP NOT NULL DEFAULT now()
184256
);
185257

186258
CREATE TRIGGER handle_updated_at_field BEFORE
@@ -192,21 +264,21 @@ EXECUTE PROCEDURE update_updated_at_field();
192264
-- Games
193265
--
194266
CREATE TABLE IF NOT EXISTS games (
195-
id INTEGER GENERATED ALWAYS AS IDENTITY,
196-
uuid UUID PRIMARY KEY UNIQUE NOT NULL DEFAULT uuid_generate_v4(),
197-
href VARCHAR(100) NOT NULL,
198-
name VARCHAR(255) NOT NULL,
199-
platforms VARCHAR(50) ARRAY,
200-
genres VARCHAR(50) ARRAY,
201-
publishers VARCHAR(50) ARRAY,
202-
developers VARCHAR(50) ARRAY,
203-
release_date TIMESTAMP NOT NULL,
204-
url TEXT NOT NULL,
205-
trailer TEXT NOT NULL,
206-
thumbnail TEXT,
207-
sources TEXT ARRAY,
208-
created_at TIMESTAMP NOT NULL DEFAULT now(),
209-
updated_at TIMESTAMP NOT NULL DEFAULT now()
267+
id INTEGER GENERATED ALWAYS AS IDENTITY,
268+
uuid UUID PRIMARY KEY UNIQUE NOT NULL DEFAULT uuid_generate_v4(),
269+
href VARCHAR(100) NOT NULL,
270+
name VARCHAR(255) NOT NULL,
271+
platforms VARCHAR(50) ARRAY,
272+
genres VARCHAR(50) ARRAY,
273+
publishers VARCHAR(50) ARRAY,
274+
developers VARCHAR(50) ARRAY,
275+
release_date TIMESTAMP NOT NULL,
276+
url TEXT NOT NULL,
277+
trailer TEXT NOT NULL,
278+
thumbnail TEXT,
279+
sources TEXT ARRAY,
280+
created_at TIMESTAMP NOT NULL DEFAULT now(),
281+
updated_at TIMESTAMP NOT NULL DEFAULT now()
210282
);
211283

212284
CREATE TRIGGER handle_updated_at_field BEFORE

‎src/main/java/com/lucasjosino/hawapi/models/base/BaseModel.java‎

Lines changed: 6 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,7 @@
11
package com.lucasjosino.hawapi.models.base;
22

33
import com.fasterxml.jackson.annotation.JsonIgnore;
4+
import com.fasterxml.jackson.annotation.JsonInclude;
45
import com.fasterxml.jackson.annotation.JsonProperty;
56
import org.hibernate.annotations.CreationTimestamp;
67
import org.hibernate.annotations.Type;
@@ -14,6 +15,7 @@
1415
import java.util.UUID;
1516

1617
@MappedSuperclass
18+
@JsonInclude(JsonInclude.Include.NON_NULL)
1719
abstract public class BaseModel implements Serializable {
1820

1921
@JsonIgnore
@@ -33,14 +35,14 @@ abstract public class BaseModel implements Serializable {
3335
@Column(columnDefinition = "text[]")
3436
private String[] sources;
3537

36-
@JsonProperty(value = "created_at", access = JsonProperty.Access.READ_ONLY)
37-
@Column(name = "created_at", updatable = false, insertable = false)
3838
@CreationTimestamp
39+
@Column(name = "created_at", updatable = false, insertable = false)
40+
@JsonProperty(value = "created_at", access = JsonProperty.Access.READ_ONLY)
3941
private LocalDateTime createdAt;
4042

41-
@JsonProperty(value = "updated_at", access = JsonProperty.Access.READ_ONLY)
42-
@Column(name = "updated_at", updatable = false, insertable = false)
4343
@UpdateTimestamp
44+
@Column(name = "updated_at", updatable = false, insertable = false)
45+
@JsonProperty(value = "updated_at", access = JsonProperty.Access.READ_ONLY)
4446
private LocalDateTime updatedAt;
4547

4648
public Integer getId() {

0 commit comments

Comments
 (0)