-
Notifications
You must be signed in to change notification settings - Fork 5
/
todo-app.sql
99 lines (86 loc) · 4.51 KB
/
todo-app.sql
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
# Dump of table tbl_users
# ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tbl_users` (
`id` varchar(200) NOT NULL,
`profile_pic` char(100) NULL,
`email` unique varchar(220) NOT NULL,
`password` unque varchar(50) NOT NULL,
`about_me` text NULL,
`persist_code` varchar(150) NULL,
`activation_code` varchar(150) NULL,
`mobile_number` char(20) NOT NULL,
`reset_password_code` varchar(220) NULL,
`full_name` varchar(220) NULL,
`first_name` varchar(220) NOT NULL,
`last_name` varchar(220) NOT NULL,
`gender` enum('male', 'female') NOT NULL DEFAULT 'male',
`last_login` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`status` enum('suspended', 'active', 'barred') NOT NULL DEFAULT 'active',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf-8 COLLATE=utf8_unicode_ci;
# Dump of table tbl_user_roles
# ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tbl_user_roles` (
`id` varchar(200) NOT NULL,
`user_id` varchar(200) NOT NULL,
`role` enum('admin', 'manager') NOT NULL DEFAULT 'admin',
`permissions` longtext '{"admin":{"read":["/todos/@id"],"write":["/todos/@id"]},"manager":{"read":["/todos"],"write":[]}}',
`created_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY(`id`),
CONSTRAINT fk_uid FOREIGN KEY (`user_id`) REFERENCES `tbl_users`(`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf-8 COLLATE=utf8_unicode_ci;
# Dump of table tbl_user_throttles
# ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tbl_user_throttles` (
`id` varchar(200) NOT NULL, --(session id, browser fingerprint)
`ip_address` varchar(200) NOT NULL DEFAULT '0.0.0.0',
`attempts` int NOT NULL DEFAULT 0,
`suspended` tinyint NOT NULL DEFAULT 0,
`banned` tinyint NOT NULL DEFAULT 0,
`last_attempt_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`suspended_at` datetime NULL,
`banned_at` datetime NULL,
PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf-8 COLLATE=utf8_unicode_ci;
# Dump of table tbl_todos
# ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tbl_todos` (
`id` varchar(200) NOT NULL,
`title` varchar(150) NOT NULL,
`description` text NULL,
`start` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`due` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`list_id` varchar(200) NOT NULL,
`status` enum('done', 'incomplete') NOT NULL DEFAULT 'incomplete',
`assignee` unique varchar(220) NULL,
PRIMARY KEY(`id`),
CONSTRAINT fk_tdlist FOREIGN KEY (`list_id`) REFERENCES `tbl_todos_list`(`id`) ON UPDATE CASCADE ON DELETE CASCADE
-- CONSTRAINT fk_usr FOREIGN KEY (`assignee`) REFERENCES `tbl_users`(`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf-8 COLLATE=utf8_unicode_ci;
# Dump of table tbl_todos_list
# ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tbl_todos_list` (
`id` varchar(200) NOT NULL,
`name` varchar(250) NOT NULL DEFAULT 'general',
`user_id` varchar(200) NOT NULL,
`project_id` varchar(200) NOT NULL DEFAULT '45a2cd23f08bbd6477d2ff89715cba32de',
`priority` enum('urgent', 'upcoming') NOT NULL DEFAULT 'upcoming',
`reminder-rate` enum('frequent:interval=3.days', 'ocassional:interval=1.week') NOT NULL DEFAULT 'ocassional:interval=1.week',
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_last_at` datetime NOT NULL DEFAULT '000-00-00 00:00:00',
PRIMARY KEY(`id`),
CONSTRAINT fk_uid FOREIGN KEY (`user_id`) REFERENCES `tbl_users`(`id`) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT fk_uid FOREIGN KEY (`project_id`) REFERENCES `tbl_projects`(`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf-8 COLLATE=utf8_unicode_ci;
# Dump of table tbl_projects
# ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tbl_projects` (
`id` varchar(200) NOT NULL,
`name` varchar(250) NOT NULL DEFAULT 'personal',
`mode` enum('short-term', 'long-term') NOT NULL DEFAULT 'long-term',
PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf-8 COLLATE=utf8_unicode_ci;
INSERT INTO `tbl_projects` (`id`) VALUES ('45a2cd23f08bbd6477d2ff89715cba32de');