-
Notifications
You must be signed in to change notification settings - Fork 0
/
init.sql
112 lines (86 loc) · 3.17 KB
/
init.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
100
101
102
103
104
105
106
107
108
109
110
111
112
CREATE DATABASE IF NOT EXISTS tobid;
USE tobid;
CREATE TABLE IF NOT EXISTS users (
id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(255) NOT NULL,
role VARCHAR(45),
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS attractions (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(45) NOT NULL,
description VARCHAR(45) NOT NULL,
latitude DECIMAL(10, 8),
longitude DECIMAL(11, 8),
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS courses (
id INT NOT NULL AUTO_INCREMENT,
tourist_id INT,
title VARCHAR(255),
category VARCHAR(255),
reservation_time DATETIME,
status VARCHAR(45) DEFAULT 'Active',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS course_cost (
id INT NOT NULL AUTO_INCREMENT,
driver_id INT,
course_id INT,
cost FLOAT,
is_selected BOOLEAN DEFAULT false,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS course_attractions (
id INT NOT NULL AUTO_INCREMENT,
course_id INT,
attraction_id INT,
order_num INT,
is_last BOOLEAN,
departure_time TIMESTAMP,
PRIMARY KEY (id)
);
ALTER TABLE `courses` ADD FOREIGN KEY (`tourist_id`) REFERENCES `users` (`id`);
ALTER TABLE `course_attractions` ADD FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`);
ALTER TABLE `course_attractions` ADD FOREIGN KEY (`attraction_id`) REFERENCES `attractions` (`id`);
ALTER TABLE `course_cost` ADD FOREIGN KEY (`driver_id`) REFERENCES `users` (`id`);
ALTER TABLE `course_cost` ADD FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`);
-- 예시 데이터
INSERT INTO users (`username`, `role`, `created_at`)
VALUES ('admin', 'admin', NOW()),
('tourist_user', 'tourist 1', NOW()),
('driver_user', 'driver 1', NOW()),
('tourist_user', 'tourist 2', NOW()),
('driver_user', 'driver 2', NOW());
INSERT INTO attractions (name, description, created_at)
VALUES
('Attraction 1', 'Description 1', '2023-08-19 10:00:00'),
('Attraction 2', 'Description 2', '2023-08-19 11:15:00'),
('Attraction 3', 'Description 3', '2023-08-19 12:30:00'),
('Attraction 4', 'Description 4', '2023-08-19 14:45:00'),
('Attraction 5', 'Description 5', '2023-08-19 16:00:00');
INSERT INTO courses (tourist_id, title, category, reservation_time, status, created_at)
VALUES
(1, 'Tour 1', 'Family', '2023-08-20 10:00:00', 'ACTIVE', NOW()),
(2, 'Tour 2', 'Alone', '2023-08-21 14:30:00', 'ACTIVE', NOW()),
(2, 'Tour 3', 'Couple', '2023-08-22 09:15:00', 'INACTIVE', NOW()),
(4, 'Tour 3', 'Relaxed', '2023-08-22 09:15:00', 'INACTIVE', NOW()),
(4, 'Tour 4', 'HotPlace', '2023-08-23 11:45:00', 'ACTIVE', NOW());
INSERT INTO course_attractions (course_id, attraction_id, order_num, is_last, departure_time)
VALUES
(1, 1, 1, false, '2023-08-20 10:00:00'),
(1, 2, 2, true, '2023-08-20 12:00:00'),
(2, 3, 2, true, '2023-08-20 12:00:00'),
(2, 4, 1, true, '2023-08-20 12:00:00');
INSERT INTO course_cost (driver_id, course_id, cost)
VALUES
(3, 2, 45000),
(5, 2, 20000),
(3, 4, 100000),
(5, 4, 7000),
(3, 4, 5000),
(5, 4, 2000);