· 8 years ago · Aug 31, 2018, 05:56 PM
1/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
2/*!40101 SET NAMES utf8 */;
3/*!50503 SET NAMES utf8mb4 */;
4/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
5/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
6
7CREATE DATABASE IF NOT EXISTS `hyperos` /*!40100 DEFAULT CHARACTER SET utf8 */;
8USE `hyperos`;
9
10CREATE TABLE IF NOT EXISTS `centers` (
11 `point_hash` varchar(45) NOT NULL COMMENT 'global point hash',
12 `name` varchar(45) NOT NULL COMMENT 'center ì´ë¦„',
13 `location` varchar(45) DEFAULT NULL COMMENT '센터 위치',
14 `region_hash` varchar(45) DEFAULT NULL COMMENT 'region ì •ë³´',
15 PRIMARY KEY (`point_hash`),
16 UNIQUE KEY `UC_name` (`name`),
17 KEY `FK_centers_region_hash_regions_point_hash` (`region_hash`),
18 CONSTRAINT `FK_centers_point_hash_points_hash` FOREIGN KEY (`point_hash`) REFERENCES `points` (`hash`) ON UPDATE CASCADE,
19 CONSTRAINT `FK_centers_region_hash_regions_point_hash` FOREIGN KEY (`region_hash`) REFERENCES `regions` (`point_hash`) ON UPDATE CASCADE
20) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='êµì‚¬. serverë“¤ì˜ ì§‘í•©';
21
22CREATE TABLE IF NOT EXISTS `groups` (
23 `hash` varchar(45) NOT NULL COMMENT 'hash',
24 `name` varchar(45) NOT NULL COMMENT 'group ì´ë¦„',
25 `parent_hash` varchar(45) DEFAULT NULL COMMENT 'self-referenced',
26 PRIMARY KEY (`hash`),
27 KEY `FK_groups_parent_hash_groups_hash` (`parent_hash`),
28 CONSTRAINT `FK_groups_parent_hash_groups_hash` FOREIGN KEY (`parent_hash`) REFERENCES `groups` (`hash`) ON UPDATE CASCADE
29) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='group (소ì†) ì •ë³´';
30
31CREATE TABLE IF NOT EXISTS `guests` (
32 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ì¸ë±ìŠ¤',
33 `name` varchar(15) NOT NULL COMMENT '윈ë„ìš°ì´ë¦„ìˆ˜ì œí•œ',
34 `ip_address` varchar(45) NOT NULL,
35 `mac_address` varchar(45) DEFAULT NULL COMMENT 'MAC 주소',
36 `is_boot` tinyint(4) NOT NULL DEFAULT 0 COMMENT '부팅 여부',
37 `os_image_hash` varchar(45) DEFAULT NULL COMMENT '사용 ì¤‘ì¸ OS ì´ë¯¸ì§€',
38 `memo` text DEFAULT NULL COMMENT '메모',
39 `host_id` int(11) DEFAULT NULL COMMENT 'host ì •ë³´',
40 `uptime` bigint(20) DEFAULT 0 COMMENT 'minutes',
41 `update_time` datetime DEFAULT NULL COMMENT 'ì—…ë°ì´íŠ¸ 시간',
42 PRIMARY KEY (`id`),
43 UNIQUE KEY `UC_ip_address` (`ip_address`),
44 UNIQUE KEY `UC_mac_address` (`mac_address`),
45 KEY `FK_guests_host_id_hosts_id` (`host_id`),
46 CONSTRAINT `FK_guests_host_id_hosts_id` FOREIGN KEY (`host_id`) REFERENCES `hosts` (`id`) ON UPDATE CASCADE
47) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='guest ì •ë³´';
48
49CREATE TABLE IF NOT EXISTS `hosts` (
50 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ì¸ë±ìŠ¤',
51 `name` varchar(15) NOT NULL COMMENT 'host ì´ë¦„',
52 `ip_address` varchar(45) NOT NULL COMMENT 'IP Address Field',
53 `vpn_address` varchar(45) DEFAULT NULL COMMENT 'IP Address Field',
54 `mac_address` varchar(45) DEFAULT NULL COMMENT 'MAC 주소',
55 `is_boot` tinyint(4) NOT NULL DEFAULT 0,
56 `os_image_hashes` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'OS ì´ë¯¸ì§€ 리스트',
57 `os_image_status` varchar(45) NOT NULL DEFAULT 'ok' COMMENT 'OS ì´ë¯¸ì§€ ì—…ë°ì´íŠ¸ ìƒíƒœ',
58 `os_image_process` float NOT NULL DEFAULT -1,
59 `memo` text DEFAULT NULL COMMENT '메모',
60 `site_hash` varchar(45) DEFAULT NULL COMMENT 'site ì •ë³´',
61 `mainboard` varchar(45) DEFAULT NULL COMMENT 'ë©”ì¸ë³´ë“œ ì •ë³´',
62 `cpu` varchar(45) DEFAULT NULL COMMENT 'CPU ì •ë³´',
63 `graphic` varchar(45) DEFAULT NULL COMMENT '그래픽카드 ì •ë³´',
64 `nic` varchar(45) DEFAULT NULL COMMENT 'ëžœ ì¹´ë“œ ì •ë³´',
65 `cpu_usage` float DEFAULT 0,
66 `memory_used` bigint(20) DEFAULT 0,
67 `memory_total` bigint(20) DEFAULT 0,
68 `disk_used` bigint(20) DEFAULT 0,
69 `disk_total` bigint(20) DEFAULT 0,
70 `network_speed` int(11) DEFAULT 0,
71 `uptime` bigint(20) DEFAULT 0,
72 `disk_read` bigint(20) DEFAULT 0,
73 `disk_write` bigint(20) DEFAULT 0,
74 `network_rx` bigint(20) DEFAULT 0,
75 `network_tx` bigint(20) DEFAULT 0,
76 `update_time` datetime DEFAULT NULL COMMENT 'ì—…ë°ì´íŠ¸ 시간',
77 PRIMARY KEY (`id`),
78 UNIQUE KEY `UC_ip_address` (`ip_address`),
79 UNIQUE KEY `name` (`name`),
80 UNIQUE KEY `UC_vpn_address` (`vpn_address`),
81 UNIQUE KEY `UC_mac_address` (`mac_address`),
82 KEY `FK_hosts_site_hash_sites_point_hash` (`site_hash`),
83 CONSTRAINT `FK_hosts_site_hash_sites_point_hash` FOREIGN KEY (`site_hash`) REFERENCES `sites` (`point_hash`) ON UPDATE CASCADE
84) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='host ì •ë³´';
85
86CREATE TABLE IF NOT EXISTS `images` (
87 `hash` varchar(45) NOT NULL COMMENT 'hash',
88 `name` varchar(45) NOT NULL COMMENT 'image ì´ë¦„',
89 `size` bigint(20) NOT NULL DEFAULT 0 COMMENT 'í¬ê¸°',
90 `memo` text DEFAULT NULL COMMENT '메모',
91 PRIMARY KEY (`hash`),
92 UNIQUE KEY `UC_name` (`name`)
93) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='image ì •ë³´';
94
95CREATE TABLE IF NOT EXISTS `image_history` (
96 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ì¸ë±ìŠ¤',
97 `image_hash` varchar(45) NOT NULL COMMENT 'global image hash',
98 `value` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '로그 값',
99 `update_time` datetime DEFAULT NULL COMMENT 'ë°ì´í„° ì—…ë°ì´íŠ¸ 시간',
100 `previous_image_hash` varchar(45) DEFAULT NULL COMMENT '롤백 가능 image hash',
101 PRIMARY KEY (`id`),
102 KEY `FK_image_history_image_hash_images_hash` (`image_hash`),
103 KEY `FK_image_history_previous_image_hash_images_hash` (`previous_image_hash`),
104 CONSTRAINT `FK_image_history_image_hash_images_hash` FOREIGN KEY (`image_hash`) REFERENCES `images` (`hash`) ON UPDATE CASCADE,
105 CONSTRAINT `FK_image_history_previous_image_hash_images_hash` FOREIGN KEY (`previous_image_hash`) REFERENCES `images` (`hash`) ON UPDATE CASCADE
106) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='image ë‚´ì—';
107
108CREATE TABLE IF NOT EXISTS `permissions` (
109 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ì¸ë±ìŠ¤',
110 `name` varchar(45) NOT NULL COMMENT '권한 ì´ë¦„',
111 PRIMARY KEY (`id`),
112 UNIQUE KEY `UC_name` (`name`)
113) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='ê° ê¸°ëŠ¥ì— ëŒ€í•œ 권한 ì´ë¦„';
114
115CREATE TABLE IF NOT EXISTS `permission_group` (
116 `user_id` int(11) NOT NULL COMMENT 'user ì •ë³´',
117 `group_hash` varchar(45) NOT NULL COMMENT 'group ì •ë³´',
118 `permission_id` int(11) NOT NULL COMMENT 'permission ì •ë³´',
119 `value` varchar(45) NOT NULL COMMENT '권한 값',
120 PRIMARY KEY (`user_id`,`group_hash`,`permission_id`),
121 KEY `FK_permission_group_group_hash_groups_hash` (`group_hash`),
122 KEY `FK_permission_group_permission_id_permissions_id` (`permission_id`),
123 CONSTRAINT `FK_permission_group_group_hash_groups_hash` FOREIGN KEY (`group_hash`) REFERENCES `groups` (`hash`) ON DELETE CASCADE ON UPDATE CASCADE,
124 CONSTRAINT `FK_permission_group_permission_id_permissions_id` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
125 CONSTRAINT `FK_permission_group_user_id_users_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
126) ENGINE=InnoDB DEFAULT CHARSET=utf8;
127
128CREATE TABLE IF NOT EXISTS `permission_point` (
129 `user_id` int(11) NOT NULL COMMENT 'user ì •ë³´',
130 `point_hash` varchar(45) NOT NULL COMMENT 'point ì •ë³´',
131 `permission_id` int(11) NOT NULL COMMENT 'permission ì •ë³´',
132 `value` varchar(45) NOT NULL COMMENT '권한 값',
133 PRIMARY KEY (`user_id`,`point_hash`,`permission_id`),
134 KEY `FK_permission_point_permission_id_permissions_id` (`permission_id`),
135 KEY `FK_permission_point_point_hash_points_hash` (`point_hash`),
136 CONSTRAINT `FK_permission_point_permission_id_permissions_id` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
137 CONSTRAINT `FK_permission_point_point_hash_points_hash` FOREIGN KEY (`point_hash`) REFERENCES `points` (`hash`) ON DELETE CASCADE ON UPDATE CASCADE,
138 CONSTRAINT `FK_permission_point_user_id_users_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
139) ENGINE=InnoDB DEFAULT CHARSET=utf8;
140
141CREATE TABLE IF NOT EXISTS `points` (
142 `hash` varchar(45) NOT NULL COMMENT 'hash',
143 `parent_hash` varchar(45) DEFAULT NULL COMMENT 'self-referenced',
144 `type` varchar(45) NOT NULL COMMENT 'point 타입',
145 PRIMARY KEY (`hash`),
146 KEY `FK_points_parent_hash_points_hash` (`parent_hash`),
147 CONSTRAINT `FK_points_parent_hash_points_hash` FOREIGN KEY (`parent_hash`) REFERENCES `points` (`hash`) ON UPDATE CASCADE
148) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='center와 server, site ë“±ì˜ ê´€ë¦¬ í¬ì¸íŠ¸ ì •ë³´';
149
150CREATE TABLE IF NOT EXISTS `regions` (
151 `point_hash` varchar(45) NOT NULL COMMENT 'global point hash',
152 `name` varchar(45) NOT NULL COMMENT 'ì§€ì— ì´ë¦„',
153 `code` varchar(45) NOT NULL COMMENT '코드',
154 PRIMARY KEY (`point_hash`),
155 UNIQUE KEY `UC_name` (`name`),
156 UNIQUE KEY `UC_code` (`code`),
157 CONSTRAINT `FK_regions_point_hash_points_hash` FOREIGN KEY (`point_hash`) REFERENCES `points` (`hash`) ON UPDATE CASCADE
158) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='ì§€ì— ì½”ë“œ';
159
160CREATE TABLE IF NOT EXISTS `servers` (
161 `point_hash` varchar(45) NOT NULL COMMENT 'global point hash',
162 `center_hash` varchar(45) DEFAULT NULL COMMENT 'ì†Œì† center ì •ë³´',
163 `name` varchar(45) NOT NULL COMMENT 'server ì´ë¦„',
164 `ip_address` varchar(45) NOT NULL COMMENT 'IP 주소',
165 `cpu_usage` float DEFAULT 0 COMMENT 'percent',
166 `cpu_temp` float DEFAULT 0 COMMENT 'celsius',
167 `cpu_io_wait` float DEFAULT 0 COMMENT 'percent',
168 `memory_used` bigint(20) DEFAULT 0 COMMENT 'bytes',
169 `memory_total` bigint(20) DEFAULT 0 COMMENT 'bytes',
170 `network_rx` bigint(20) DEFAULT 0 COMMENT 'bytes/s',
171 `network_tx` bigint(20) DEFAULT 0 COMMENT 'bytes/s',
172 `firmware_used` bigint(20) DEFAULT 0 COMMENT 'bytes',
173 `firmware_total` bigint(20) DEFAULT 0 COMMENT 'bytes',
174 `volume_used` bigint(20) DEFAULT 0 COMMENT 'bytes',
175 `volume_total` bigint(20) DEFAULT 0 COMMENT 'bytes',
176 `volume_read` bigint(20) DEFAULT 0 COMMENT 'bytes/s',
177 `volume_write` bigint(20) DEFAULT 0 COMMENT 'bytes/s',
178 `update_time` datetime DEFAULT NULL COMMENT 'ë°ì´í„° ì—…ë°ì´íŠ¸ 시간',
179 PRIMARY KEY (`point_hash`),
180 UNIQUE KEY `UC_name` (`name`),
181 UNIQUE KEY `UC_ip_address` (`ip_address`),
182 KEY `FK_servers_center_hash_centers_point_hash` (`center_hash`),
183 CONSTRAINT `FK_servers_center_hash_centers_point_hash` FOREIGN KEY (`center_hash`) REFERENCES `centers` (`point_hash`) ON UPDATE CASCADE,
184 CONSTRAINT `FK_servers_point_hash_points_hash` FOREIGN KEY (`point_hash`) REFERENCES `points` (`hash`) ON UPDATE CASCADE
185) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='server ì •ë³´';
186
187CREATE TABLE IF NOT EXISTS `settings` (
188 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ì¸ë±ìŠ¤',
189 `key` varchar(45) NOT NULL COMMENT 'key ê°’',
190 `value` varchar(45) NOT NULL COMMENT 'value ê°’',
191 PRIMARY KEY (`id`),
192 UNIQUE KEY `UC_key` (`key`)
193) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='서버 ì„¤ì •';
194
195CREATE TABLE IF NOT EXISTS `sites` (
196 `point_hash` varchar(45) NOT NULL COMMENT 'global point hash',
197 `name` varchar(45) NOT NULL COMMENT 'site ì´ë¦„',
198 `code` varchar(45) DEFAULT NULL COMMENT '코드 값',
199 `group_hash` varchar(45) NOT NULL COMMENT 'group(소ì†) hash',
200 `location` varchar(45) DEFAULT NULL COMMENT '장소',
201 `client_total` int(11) DEFAULT NULL COMMENT 'í´ë¼ì´ì–¸íŠ¸ ì „ì²´ 대수',
202 `client_booted` int(11) DEFAULT NULL COMMENT 'í´ë¼ì´ì–¸íŠ¸ 중 ë¶€íŒ…ëœ ëŒ€ìˆ˜',
203 `subnet_address` varchar(45) DEFAULT NULL COMMENT '서브넷 주소',
204 `netmask_bit` int(11) DEFAULT NULL COMMENT 'ë„·ë§ˆìŠ¤í¬ ë¹„íŠ¸',
205 `gateway` varchar(45) DEFAULT NULL COMMENT '게ì´íŠ¸ì›¨ì´',
206 PRIMARY KEY (`point_hash`),
207 KEY `FK_sites_group_hash_groups_hash` (`group_hash`),
208 CONSTRAINT `FK_sites_group_hash_groups_hash` FOREIGN KEY (`group_hash`) REFERENCES `groups` (`hash`) ON UPDATE CASCADE,
209 CONSTRAINT `FK_sites_point_hash_points_hash` FOREIGN KEY (`point_hash`) REFERENCES `points` (`hash`) ON UPDATE CASCADE
210) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='site ì •ë³´';
211
212CREATE TABLE IF NOT EXISTS `users` (
213 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ì¸ë±ìŠ¤',
214 `username` varchar(45) NOT NULL COMMENT 'user (ê³„ì •) ì´ë¦„',
215 `password` varchar(45) NOT NULL COMMENT 'SHA-2 with salt',
216 `salt` varchar(45) NOT NULL COMMENT '솔트 값',
217 `last_token` text DEFAULT NULL COMMENT '최근 발급한 í† í°',
218 `group_hashes` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
219 `name` varchar(45) NOT NULL COMMENT 'ì‚¬ìš©ìž ì´ë¦„',
220 `auth_level` int(11) NOT NULL DEFAULT 0 COMMENT 'ì‚¬ìš©ìž ê¶Œí•œ 수준',
221 `is_active` tinyint(4) NOT NULL DEFAULT 0 COMMENT 'ê³„ì • 활성화(승ì¸) 여부',
222 `is_logon` tinyint(4) NOT NULL DEFAULT 0 COMMENT '현재 사용 여부',
223 `last_login_web` datetime DEFAULT NULL COMMENT '최근 ê´€ë¦¬ìž íŽ˜ì´ì§€ ë¡œê·¸ì¸ ì‹œê°„',
224 `last_login_os` datetime DEFAULT NULL COMMENT '최근 OS ë¡œê·¸ì¸ ì‹œê°„',
225 `logon_site` varchar(45) DEFAULT NULL COMMENT '현재 사용 ì¤‘ì¸ site',
226 `time_usage` int(11) NOT NULL DEFAULT 0 COMMENT 'minutes',
227 PRIMARY KEY (`id`),
228 UNIQUE KEY `UC_username` (`username`)
229) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='HyperOSì—ì„œ ì‚¬ìš©í• user ì •ë³´';
230
231CREATE TABLE IF NOT EXISTS `user_logon_history` (
232 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ì¸ë±ìŠ¤',
233 `username` varchar(45) NOT NULL COMMENT 'ë¡œê·¸ì¸ user ì •ë³´',
234 `last_login_web` datetime DEFAULT NULL COMMENT '최근 ê´€ë¦¬ìž íŽ˜ì´ì§€ ë¡œê·¸ì¸ ì‹œê°„',
235 `last_login_os` datetime DEFAULT NULL COMMENT '최근 OS ë¡œê·¸ì¸ ì‹œê°„',
236 `login_site` varchar(45) DEFAULT NULL COMMENT '최근 OS 로그ì¸ì„ ì‹œë„í•œ site',
237 `update_time` datetime DEFAULT NULL COMMENT 'ë°ì´í„° ì—…ë°ì´íŠ¸ 시간',
238 PRIMARY KEY (`id`),
239 KEY `FK_user_logon_history_username_users_username` (`username`),
240 CONSTRAINT `FK_user_logon_history_username_users_username` FOREIGN KEY (`username`) REFERENCES `users` (`username`) ON DELETE NO ACTION ON UPDATE CASCADE
241) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='user ë¡œê·¸ì¸ ì‹œ ë°œìƒí•˜ëŠ” 로그';
242
243/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
244/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
245/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;