POPPER_Server/init.sql at dev · POPPER-team/POPPER_Server · GitHub

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
create database if not exists POPPERDB;

use POPPERDB;

create table if not exists Users
(
    id             int auto_increment,
    Guid           nvarchar(50)  not null,
    FirstName      nvarchar(255) not null,
    LastName       nvarchar(255) not null,
    Username       nvarchar(255) not null,
    Password       nvarchar(255) not null,
    Created        datetime      not null,
    DateOfBirth    date          null,
    Status         nvarchar(255),
    WebLink        nvarchar(255),
    PreferredUnits nvarchar(255),
    Language       nvarchar(255),
    Primary key (id)
);

create table if not exists Following
(
    id          int auto_increment,
    UserID      int not null,
    FollowingID int not null,
    Primary key (id),
    foreign key (UserID) references Users (id),
    foreign key (FollowingID) references Users (id)
);

create table if not exists Post
(
    id          int auto_increment,
    Guid        nvarchar(50)  not null,
    Title       nvarchar(255) not null,
    Description nvarchar(255) not null,
    MediaGuid   nvarchar(255) null,
    Duration    TIME          not null,
    Created     DATETIME      not null,
    UserId      int           not null,
    foreign key (UserId) references Users (id),
    primary key (id)
);

create table if not exists Ingredients
(
    id     int auto_increment,
    Text   nvarchar(255) not null,
    Unit   nvarchar(20)  not null,
    Amount double        not null,
    PostId int           not null,
    foreign key (PostId) references Post (id),
    primary key (id)
);

create table if not exists Views
(
    id     int auto_increment,
    UserId int not null,
    PostId int not null,
    foreign key (UserId) references Users (id),
    foreign key (PostId) references Post (id),
    primary key (id)
);

create table if not exists Saved
(
    id     int auto_increment,
    UserId int not null,
    PostId int not null,
    foreign key (UserId) references Users (id),
    foreign key (PostId) references Post (id),
    primary key (id)
);

create table if not exists Likes
(
    id     int auto_increment,
    UserId int not null,
    PostId int not null,
    foreign key (UserId) references Users (id),
    foreign key (PostId) references Post (id),
    primary key (id)
);

create table if not exists Steps
(
    id         int auto_increment,
    StepNumber int           not null,
    Text       nvarchar(255) not null,
    PostId     int           not null,
    foreign key (PostId) references Post (id),
    primary key (id)
);

create table if not exists Comments
(
    id      int auto_increment,
    Guid    nvarchar(50) not null,
    UserId  int          not null,
    PostId  int          not null,
    Text    nvarchar(255),
    Rating  int,
    Created datetime     not null,
    foreign key (UserId) references Users (id),
    foreign key (PostId) references Post (id),
    primary key (id)
);