FfnR0hMa

1CREATE ROLE recipes
2  WITH LOGIN PASSWORD ''
3       CREATEDB;
4
5CREATE DATABASE api
6  WITH OWNER recipes;
7
8-- BEGIN: SPOTIFY TYPES
9
10DROP DOMAIN IF EXISTS spotifyUserIdType CASCADE;
11CREATE DOMAIN spotifyUserIdType
12    AS VARCHAR(32);
13
14DROP DOMAIN IF EXISTS spotifyUserCountryType CASCADE;
15CREATE DOMAIN spotifyUserCountryType
16    AS VARCHAR(2);
17
18DROP TYPE IF EXISTS spotifyUserSubscriptionType CASCADE;
19CREATE TYPE spotifyUserSubscriptionType
20    AS ENUM ('free', 'premium');
21
22DROP DOMAIN IF EXISTS spotifyUserAccessTokenType CASCADE;
23CREATE DOMAIN spotifyUserAccessTokenType
24    AS VARCHAR(256);
25
26DROP DOMAIN IF EXISTS spotifyUserRefreshTokenType CASCADE;
27CREATE DOMAIN spotifyUserRefreshTokenType
28    AS VARCHAR(256);
29
30-- END: SPOTIFY TYPES
31
32
33DROP TABLE IF EXISTS sessions CASCADE;
34CREATE TABLE sessions (
35    PRIMARY KEY (sessionId),
36    sessionId      SERIAL       NOT NULL,
37    token          VARCHAR(64)  NOT NULL,
38    startTime      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
39    expirationTime TIMESTAMP    NOT NULL,
40    userAgent      VARCHAR(256) NOT NULL,
41    UNIQUE (token)
42--  1-1: applicationUser (required)
43--  1-M: request
44);
45
46DROP TABLE IF EXISTS applicationUsers CASCADE;
47CREATE TABLE applicationUsers (
48    PRIMARY KEY (applicationUserId),
49    applicationUserId INT NOT NULL,
50    spotifyUserId     spotifyUserIdType,
51    UNIQUE (spotifyUserId),
52
53                      CONSTRAINT fkSessionId
54                      FOREIGN KEY (applicationUserId)
55                      REFERENCES sessions (sessionId)
56
57--  1-1: session (required)
58--  1-1: spotifyUser
59--  M-M: recipes
60);
61
62DROP TABLE IF EXISTS spotifyUsers CASCADE;
63CREATE TABLE spotifyUsers (
64    PRIMARY KEY (spotifyUserId),
65    spotifyUserId spotifyUserIdType           NOT NULL,
66    country       spotifyUserCountryType      NOT NULL,
67    product       spotifyUserSubscriptionType NOT NULL,
68    accessToken   spotifyUserAccessTokenType  NOT NULL,
69    refreshToken  spotifyUserRefreshTokenType NOT NULL,
70
71                  CONSTRAINT fkApplicationUser
72                  FOREIGN KEY (spotifyUserId)
73                  REFERENCES applicationUsers (spotifyUserId)
74--  1-1: applicationUser
75 );
76
77DROP TABLE IF EXISTS requests CASCADE;
78CREATE TABLE requests (
79    PRIMARY KEY (requestUrl),
80    requestId    SERIAL       NOT NULL,
81    requestUrl   VARCHAR(128) NOT NULL,
82    responseTime INT          NOT NULL,
83
84    fkSessionId  INT,
85
86                 FOREIGN KEY (fkSessionId)
87                 REFERENCES sessions (sessionId)
88                 ON DELETE CASCADE
89--  M-1: session
90);
91
92DROP TABLE IF EXISTS recipes CASCADE;
93CREATE TABLE recipes (
94    PRIMARY KEY (recipeId),
95    recipeId          SERIAL       NOT NULL,
96    recipeName        VARCHAR(128) NOT NULL,
97    usageCount        INT          NOT NULL DEFAULT 0,
98    creationDate      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
99--  M-M: applicationUser
100);
101
102DROP TABLE IF EXISTS recipesApplication CASCADE;
103CREATE TABLE recipesApplication (
104    pkRecipeId        INT REFERENCES recipes (recipeId)
105                      ON UPDATE CASCADE
106                      ON DELETE CASCADE,
107    pkApplicationUser INT REFERENCES applicationUsers (applicationUserId)
108                      ON UPDATE CASCADE
109                      ON DELETE CASCADE,
110
111                      CONSTRAINT pkRecipesApplication
112                      PRIMARY KEY (pkRecipeId, pkApplicationUser)
113);