cms/sql.sql at master · r3v3l/cms · 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
110
111
112
/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
/**
 * Author:  adrian
 * Created: 2016-07-06
 */

create table newsletter(
    id int not null auto_increment primary key,
    email varchar(255) not null unique key,
    creationDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    index(id),
    index(email),
    index(creationDate)
);

create table contact(
    id int not null auto_increment primary key,
    username varchar(255) not null,
    email varchar(255) not null,
    subject varchar(255) not null,
    message text not null,
    creationDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    index(id),
    index(email),
    index(username),
    index(subject),
    index(creationDate)
);

create table appStatus(
    id int not null auto_increment primary key,
    statusName varchar(255) not null unique key,
    creationDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    updateDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    index(statusName),
    index(creationDate),
    index(updateDate)
);

insert into appStatus(statusName) values('aktywny'), ('nieaktywny'),
('zablokowany'), ('zamrożony'), ('online'), ('offline');

create table appRole(
    id int not null auto_increment primary key,
    roleName varchar(255) not null unique key,
    statusId int not null,
    creationDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    updateDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    index(roleName),
    index(statusId),
    index(creationDate),
    index(updateDate),
    FOREIGN KEY (statusId)
        REFERENCES appStatus(id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

insert into appRole(roleName, statusId)
select
    'uzytkownik' as roleName,
    (select id from appStatus where statusName='aktywny');
insert into appRole(roleName, statusId)
select
    'klient' as roleName,
    (select id from appStatus where statusName='aktywny');
insert into appRole(roleName, statusId)
select
    'reklamodawca' as roleName,
    (select id from appStatus where statusName='aktywny');
insert into appRole(roleName, statusId)
select
    'autor' as roleName,
    (select id from appStatus where statusName='aktywny');
insert into appRole(roleName, statusId)
select
    'edytor' as roleName,
    (select id from appStatus where statusName='aktywny');
insert into appRole(roleName, statusId)
select
    'moderator' as roleName,
    (select id from appStatus where statusName='aktywny');
insert into appRole(roleName, statusId)
select
    'administrator' as roleName,
    (select id from appStatus where statusName='aktywny');

create table appUser(
    id int not null auto_increment primary key,
    username varchar(255) not null unique key,
    email varchar(255) not null unique key,
    password varchar(255) not null,
    statusId int not null,
    roleId int not null,
    creationDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    updateDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    index(id),
    index(username),
    index(email),
    index(password),
    index(statusId),
    index(roleId),
    FOREIGN KEY (statusId)
        REFERENCES appStatus(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (roleId)
        REFERENCES appRole(id)
        ON DELETE CASCADE ON UPDATE CASCADE
);