TUgTy0Vb

1DROP DATABASE IF EXISTS socnetDB;
2
3CREATE DATABASE IF NOT EXISTS socnetDB;
4
5USE socnetDB;
6
7-- Table profile
8CREATE TABLE profile
9(
10  id          BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,
11  firstname   VARCHAR(30),
12  lastname    VARCHAR(30),
13  birthDate   DATE,
14  email       VARCHAR(30) UNIQUE,
15  age         INT(30)                     DEFAULT NULL,
16  sex         VARCHAR(30),
17  city        VARCHAR(30),
18  phoneNumber VARCHAR(30)
19);
20
21-- Table users
22CREATE TABLE users
23(
24  id        BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,
25  firstname VARCHAR(30)        NOT NULL,
26  lastname  VARCHAR(30)        NOT NULL,
27  email     VARCHAR(30)        NOT NULL UNIQUE,
28  login     VARCHAR(30)        NOT NULL UNIQUE,
29  password  VARCHAR(30)        NOT NULL,
30  profileID BIGINT             NOT NULL,
31  FOREIGN KEY (profileID) REFERENCES profile (id)
32    ON UPDATE CASCADE
33    ON DELETE CASCADE
34);
35
36-- Table roles
37CREATE TABLE roles
38(
39  id   INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
40  name VARCHAR(100)    NOT NULL
41);
42
43-- Table for mapping user and roles
44CREATE TABLE user_roles
45(
46  user_id INT NOT NULL,
47  role_id INT NOT NULL,
48
49  FOREIGN KEY (user_id) REFERENCES users (id),
50  FOREIGN KEY (role_id) REFERENCES roles (id)
51);
52
53
54-- Insert data
55
56INSERT INTO profile VALUES (1, 'admin', 'admin', NULL, 'admin@admin.com', NULL, NULL, NULL, NULL);
57INSERT INTO users VALUES (1, 'admin', 'admin', 'admin@admin.com', 'admin', '1234567', '1');
58
59
60INSERT INTO socnetDB.roles VALUES (1, 'ROLE_USER');
61INSERT INTO socnetDB.roles VALUES (2, 'ROLE_ADMIN');
62
63INSERT INTO socnetDB.user_roles VALUES (1, 2);