Skip to content

Commit a3fdec8

Browse files
LucJosinLucJosin
authored
Database: Update schema (#10)
- Update schema.sql; - Add soundtrack and game models; - Rename place to location; - Update models.
1 parent 05363b5 commit a3fdec8

12 files changed

Lines changed: 401 additions & 108 deletions

File tree

‎docker/postgres/migration/schema.sql‎

Lines changed: 59 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -55,7 +55,6 @@ CREATE TABLE IF NOT EXISTS actors (
5555
death_date TIMESTAMP,
5656
gender SMALLINT NOT NULL DEFAULT 0,
5757
nationality VARCHAR(50),
58-
episodes VARCHAR ARRAY,
5958
seasons VARCHAR ARRAY,
6059
awards VARCHAR ARRAY,
6160
character VARCHAR(255) NOT NULL,
@@ -76,7 +75,8 @@ EXECUTE PROCEDURE update_updated_at_field();
7675
--
7776
CREATE TABLE IF NOT EXISTS actors_socials (
7877
id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
79-
name VARCHAR(50) NOT NULL,
78+
social VARCHAR(50) NOT NULL,
79+
handle VARCHAR(50) NOT NULL,
8080
url TEXT NOT NULL,
8181
actor_uuid UUID NOT NULL,
8282
CONSTRAINT fk_actor_uuid
@@ -100,7 +100,6 @@ CREATE TABLE IF NOT EXISTS episodes (
100100
next_episode VARCHAR(255),
101101
prev_episode VARCHAR(255),
102102
season VARCHAR(255) NOT NULL,
103-
trailers TEXT ARRAY,
104103
thumbnail TEXT,
105104
images TEXT ARRAY,
106105
sources TEXT ARRAY,
@@ -123,13 +122,15 @@ CREATE TABLE IF NOT EXISTS seasons (
123122
title VARCHAR(255) NOT NULL,
124123
description TEXT NOT NULL,
125124
duration_total INTEGER NOT NULL,
126-
tags VARCHAR ARRAY,
125+
genres VARCHAR(50) ARRAY,
127126
season_num SMALLINT NOT NULL,
128127
release_date TIMESTAMP NOT NULL,
129128
next_season VARCHAR(255),
130129
prev_season VARCHAR(255),
131130
episodes VARCHAR ARRAY,
131+
soundtracks VARCHAR(255) ARRAY,
132132
trailers VARCHAR ARRAY,
133+
budget INTEGER,
133134
thumbnail TEXT,
134135
images TEXT ARRAY,
135136
sources TEXT ARRAY,
@@ -143,9 +144,9 @@ CREATE TRIGGER handle_updated_at_field BEFORE
143144
EXECUTE PROCEDURE update_updated_at_field();
144145

145146
--
146-
-- Places
147+
-- Locations
147148
--
148-
CREATE TABLE IF NOT EXISTS places (
149+
CREATE TABLE IF NOT EXISTS locations (
149150
id INTEGER GENERATED ALWAYS AS IDENTITY,
150151
uuid UUID PRIMARY KEY UNIQUE NOT NULL DEFAULT uuid_generate_v4(),
151152
href VARCHAR(100) NOT NULL,
@@ -159,6 +160,57 @@ CREATE TABLE IF NOT EXISTS places (
159160
);
160161

161162
CREATE TRIGGER handle_updated_at_field BEFORE
162-
UPDATE ON places
163+
UPDATE ON locations
164+
FOR EACH ROW
165+
EXECUTE PROCEDURE update_updated_at_field();
166+
167+
--
168+
-- Soundtracks
169+
--
170+
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()
184+
);
185+
186+
CREATE TRIGGER handle_updated_at_field BEFORE
187+
UPDATE ON soundtracks
163188
FOR EACH ROW
164189
EXECUTE PROCEDURE update_updated_at_field();
190+
191+
--
192+
-- Games
193+
--
194+
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()
210+
);
211+
212+
CREATE TRIGGER handle_updated_at_field BEFORE
213+
UPDATE ON games
214+
FOR EACH ROW
215+
EXECUTE PROCEDURE update_updated_at_field();
216+

‎src/main/java/com/lucasjosino/hawapi/controllers/LocationController.java‎

Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,43 @@
1+
package com.lucasjosino.hawapi.controllers;
2+
3+
import com.lucasjosino.hawapi.models.LocationModel;
4+
import com.lucasjosino.hawapi.services.LocationService;
5+
import org.springframework.beans.factory.annotation.Autowired;
6+
import org.springframework.http.ResponseEntity;
7+
import org.springframework.web.bind.annotation.*;
8+
9+
import java.util.List;
10+
import java.util.UUID;
11+
12+
@RestController
13+
@RequestMapping("${hawapi.apiBaseUrl}/places")
14+
public class LocationController {
15+
16+
private final LocationService locationService;
17+
18+
@Autowired
19+
public LocationController(LocationService LocationService) {
20+
this.locationService = LocationService;
21+
}
22+
23+
@GetMapping
24+
public ResponseEntity<List<LocationModel>> findAll() {
25+
return ResponseEntity.ok(locationService.findAll());
26+
}
27+
28+
@GetMapping("/{uuid}")
29+
public ResponseEntity<LocationModel> findByUUID(@PathVariable UUID uuid) {
30+
return ResponseEntity.ok(locationService.findByUUID(uuid));
31+
}
32+
33+
@PostMapping
34+
public ResponseEntity<LocationModel> save(@RequestBody LocationModel episode) {
35+
return ResponseEntity.ok(locationService.save(episode));
36+
}
37+
38+
@DeleteMapping("/{uuid}")
39+
public ResponseEntity<Void> delete(@PathVariable UUID uuid) {
40+
locationService.delete(uuid);
41+
return ResponseEntity.noContent().build();
42+
}
43+
}

‎src/main/java/com/lucasjosino/hawapi/controllers/PlaceController.java‎

Lines changed: 0 additions & 43 deletions
This file was deleted.

‎src/main/java/com/lucasjosino/hawapi/models/ActorModel.java‎

Lines changed: 8 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -38,10 +38,6 @@ public class ActorModel extends BaseModel {
3838
@Column
3939
private String nationality;
4040

41-
@Type(type = "string-array")
42-
@Column(columnDefinition = "varchar[]")
43-
private String[] episodes;
44-
4541
@Type(type = "string-array")
4642
@Column(columnDefinition = "varchar[]")
4743
private String[] seasons;
@@ -120,14 +116,6 @@ public void setNationality(String nationality) {
120116
this.nationality = nationality;
121117
}
122118

123-
public String[] getEpisodes() {
124-
return episodes;
125-
}
126-
127-
public void setEpisodes(String[] episodes) {
128-
this.episodes = episodes;
129-
}
130-
131119
public String[] getSeasons() {
132120
return seasons;
133121
}
@@ -136,6 +124,14 @@ public void setSeasons(String[] seasons) {
136124
this.seasons = seasons;
137125
}
138126

127+
public String[] getAwards() {
128+
return awards;
129+
}
130+
131+
public void setAwards(String[] awards) {
132+
this.awards = awards;
133+
}
134+
139135
public String getCharacter() {
140136
return character;
141137
}

‎src/main/java/com/lucasjosino/hawapi/models/ActorSocialModel.java‎

Lines changed: 16 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -20,7 +20,10 @@ public class ActorSocialModel implements Serializable {
2020
private int id;
2121

2222
@Column(nullable = false)
23-
private String name;
23+
private String social;
24+
25+
@Column(nullable = false)
26+
private String handle;
2427

2528
@Column(nullable = false)
2629
private String url;
@@ -37,12 +40,20 @@ public void setId(int id) {
3740
this.id = id;
3841
}
3942

40-
public String getName() {
41-
return name;
43+
public String getSocial() {
44+
return social;
45+
}
46+
47+
public void setSocial(String social) {
48+
this.social = social;
49+
}
50+
51+
public String getHandle() {
52+
return handle;
4253
}
4354

44-
public void setName(String name) {
45-
this.name = name;
55+
public void setHandle(String handle) {
56+
this.handle = handle;
4657
}
4758

4859
public String getUrl() {

‎src/main/java/com/lucasjosino/hawapi/models/EpisodeModel.java‎

Lines changed: 0 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -36,10 +36,6 @@ public class EpisodeModel extends BaseModel {
3636
@Column(nullable = false)
3737
private String season;
3838

39-
@Type(type = "string-array")
40-
@Column(columnDefinition = "text[]")
41-
private String[] trailers;
42-
4339
@Type(type = "string-array")
4440
@Column(columnDefinition = "text[]")
4541
private String[] images;
@@ -103,14 +99,6 @@ public void setSeason(String season) {
10399
this.season = season;
104100
}
105101

106-
public String[] getTrailers() {
107-
return trailers;
108-
}
109-
110-
public void setTrailers(String[] trailers) {
111-
this.trailers = trailers;
112-
}
113-
114102
public String[] getImages() {
115103
return images;
116104
}

0 commit comments

Comments
 (0)