Centrina/database_tables.sql at master · OrbisWare/Centrina · 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
//i really need to make the tables more efficient
CREATE TABLE ct_loginattempts (
  userid INTEGER UNSIGNED NOT NULL,
  time INTEGER UNSIGNED NOT NULL,
  userip VARCHAR(45) NOT NULL
);

CREATE TABLE ct_users (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  username VARCHAR(255) NOT NULL,
  email  VARCHAR(255) NOT NULL,
  password VARCHAR(128) NOT NULL,
  salt VARCHAR(128) NOT NULL,
  time INTEGER(11) NOT NULL,

  PRIMARY KEY (id)
);

CREATE TABLE ct_roles (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,

  PRIMARY KEY (id)
);

CREATE TABLE ct_permissions (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  desc VARCHAR(50) NOT NULL,

  PRIMARY KEY (id)
);

CREATE TABLE ct_roleperms (
  role_id INTEGER UNSIGNED NOT NULL,
  perm_id INTEGER UNSIGNED NOT NULL,

  FOREIGN KEY (role_id) REFERENCES ct_roles(role_id),
  FOREIGN KEY (perm_id) REFERENCES ct_permissions(perm_id)
);

CREATE TABLE ct_userroles (
  user_id INTEGER UNSIGNED NOT NULL,
  role_id INTEGER UNSIGNED NOT NULL,

  FOREIGN KEY (user_id) REFERENCES ct_users(user_id),
  FOREIGN KEY (role_id) REFERENCES ct_roles(role_id)
);

//Invite Module
CREATE TABLE ct_invites (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  userid INTEGER UNSIGNED NOT NULL,
  code VARCHAR(32) NOT NULL,
  time INTEGER(11) NOT NULL,
  claim INTEGER UNSIGNED NOT NULL,
  taken INTEGER(1) NOT NULL,

  PRIMARY KEY (id)
);