DealTonBUT/structural_scriptOLD.sql at main · maximeNoiret/DealTonBUT · 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
DROP TABLE IF EXISTS transaction;
DROP TABLE IF EXISTS points;
DROP TABLE IF EXISTS tags;
DROP TABLE IF EXISTS subject;
DROP TABLE IF EXISTS tag;
DROP TABLE IF EXISTS offer;
DROP TABLE IF EXISTS token;
DROP TABLE IF EXISTS user_;

CREATE TABLE user_(
   email VARCHAR(70) PRIMARY KEY,
   username VARCHAR(50) NOT NULL,
   hashedpwd VARCHAR(100) NOT NULL DEFAULT 'not-verified',
   balance DECIMAL(8,2) NOT NULL DEFAULT 0.00,
   role VARCHAR(15) NOT NULL DEFAULT 'not-verified',
   CONSTRAINT CHK_USER_EMAIL
    CHECK (email LIKE '%_@__%.__%'),
   CONSTRAINT CHK_USER_BAL
    CHECK (balance >= 0)
) DEFAULT CHARSET=utf8;

CREATE TABLE token(
    token VARCHAR(32) PRIMARY KEY,
    email VARCHAR(70) NOT NULL,
    deadline TIMESTAMP DEFAULT (CURRENT_TIMESTAMP + INTERVAL 10 MINUTE) NOT NULL,
    FOREIGN KEY (email) REFERENCES user_(email) ON DELETE CASCADE
) DEFAULT CHARSET=utf8;

CREATE TABLE offer(
   ouid INT PRIMARY KEY AUTO_INCREMENT,
   owner VARCHAR(70) NOT NULL,
   title VARCHAR(50) NOT NULL,
   description TEXT,
   price DECIMAL(8,2) NOT NULL,
   creation_time DATETIME NOT NULL,
   deadline DATETIME NOT NULL,
   FOREIGN KEY(owner) REFERENCES user_(email)
    ON DELETE CASCADE,
   CONSTRAINT CHK_OFFER_PRICE
    CHECK (price >= 0),
   CONSTRAINT CHK_OFFER_TIME
    CHECK (creation_time < deadline)
) DEFAULT CHARSET=utf8;

CREATE TABLE tag(
   tagname VARCHAR(50) PRIMARY KEY
) DEFAULT CHARSET=utf8;

CREATE TABLE subject(
   subject_name VARCHAR(50) PRIMARY KEY
) DEFAULT CHARSET=utf8;

CREATE TABLE tags(
   ouid INT,
   tagname VARCHAR(50),
   PRIMARY KEY(ouid, tagname),
   FOREIGN KEY(ouid) REFERENCES offer(ouid)
    ON DELETE CASCADE,
   FOREIGN KEY(tagname) REFERENCES tag(tagname)
    ON DELETE CASCADE
) DEFAULT CHARSET=utf8;

CREATE TABLE points(
   email VARCHAR(70),
   subject_name VARCHAR(50),
   points DECIMAL(5,2),
   PRIMARY KEY(email, subject_name),
   FOREIGN KEY(email) REFERENCES user_(email)
    ON DELETE CASCADE,
   FOREIGN KEY(subject_name) REFERENCES subject(subject_name)
    ON DELETE CASCADE,
   CONSTRAINT CHK_POINTS_PTS
    CHECK (points >= 0)
) DEFAULT CHARSET=utf8;

CREATE TABLE transaction(
   email VARCHAR(70),
   ouid INT,
   amount DECIMAL(8,2) NOT NULL,
   transaction_time DATETIME NOT NULL,
   PRIMARY KEY(email, ouid),
   FOREIGN KEY(email) REFERENCES user_(email)
    ON DELETE CASCADE,
   FOREIGN KEY(ouid) REFERENCES offer(ouid)
    ON DELETE CASCADE,
   CONSTRAINT CHK_TRANSACTION_AMOUNT
    CHECK (amount >= 0)
) DEFAULT CHARSET=utf8;

SET NAMES utf8;