1 | CREATE TABLE IF NOT EXISTS `user` (
|
2 | `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
|
3 | `gmt_create` datetime NOT NULL COMMENT 'create time',
|
4 | `gmt_modified` datetime NOT NULL COMMENT 'modified time',
|
5 | `name` varchar(100) NOT NULL COMMENT 'user name',
|
6 | `salt` varchar(100) NOT NULL,
|
7 | `password_sha` varchar(100) NOT NULL COMMENT 'user password hash',
|
8 | `ip` varchar(64) NOT NULL COMMENT 'user last request ip',
|
9 | `roles` varchar(200) NOT NULL DEFAULT '[]',
|
10 | `rev` varchar(40) NOT NULL,
|
11 | `email` varchar(400) NOT NULL,
|
12 | `json` longtext CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT 'json details',
|
13 | `npm_user` tinyint(1) DEFAULT '0' COMMENT 'user sync from npm or not, 1: true, other: false',
|
14 | PRIMARY KEY (`id`),
|
15 | UNIQUE KEY `name` (`name`),
|
16 | KEY `gmt_modified` (`gmt_modified`)
|
17 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='user base info';
|
18 |
|
19 |
|
20 |
|
21 |
|
22 | CREATE TABLE IF NOT EXISTS `module_keyword` (
|
23 | `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
|
24 | `gmt_create` datetime NOT NULL COMMENT 'create time',
|
25 | `keyword` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'keyword',
|
26 | `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'module name',
|
27 | `description` longtext,
|
28 | PRIMARY KEY (`id`),
|
29 | UNIQUE KEY `keyword_module_name` (`keyword`,`name`),
|
30 | KEY `name` (`name`)
|
31 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='module keyword';
|
32 |
|
33 | CREATE TABLE IF NOT EXISTS `module_star` (
|
34 | `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
|
35 | `gmt_create` datetime NOT NULL COMMENT 'create time',
|
36 | `user` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'user name',
|
37 | `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'module name',
|
38 | PRIMARY KEY (`id`),
|
39 | UNIQUE KEY `user_module_name` (`user`,`name`),
|
40 | KEY `name` (`name`)
|
41 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='module star';
|
42 |
|
43 | CREATE TABLE IF NOT EXISTS `module_maintainer` (
|
44 | `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
|
45 | `gmt_create` datetime NOT NULL COMMENT 'create time',
|
46 | `user` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'user name',
|
47 | `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'module name',
|
48 | PRIMARY KEY (`id`),
|
49 | UNIQUE KEY `user_module_name` (`user`,`name`),
|
50 | KEY `name` (`name`)
|
51 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='private module maintainers';
|
52 |
|
53 | CREATE TABLE IF NOT EXISTS `npm_module_maintainer` (
|
54 | `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
|
55 | `gmt_create` datetime NOT NULL COMMENT 'create time',
|
56 | `user` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'user name',
|
57 | `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'module name',
|
58 | PRIMARY KEY (`id`),
|
59 | UNIQUE KEY `user_module_name` (`user`,`name`),
|
60 | KEY `name` (`name`)
|
61 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='npm original module maintainers';
|
62 |
|
63 | CREATE TABLE IF NOT EXISTS `module` (
|
64 | `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
|
65 | `gmt_create` datetime NOT NULL COMMENT 'create time',
|
66 | `gmt_modified` datetime NOT NULL COMMENT 'modified time',
|
67 | `author` varchar(100) NOT NULL,
|
68 | `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'module name',
|
69 | `version` varchar(30) NOT NULL COMMENT 'module version',
|
70 | `description` longtext,
|
71 | `package` longtext CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT 'package.json',
|
72 | `dist_shasum` varchar(100) DEFAULT NULL,
|
73 | `dist_tarball` varchar(2048) DEFAULT NULL,
|
74 | `dist_size` int(10) unsigned NOT NULL DEFAULT '0',
|
75 | `publish_time` bigint(20) unsigned,
|
76 | PRIMARY KEY (`id`),
|
77 | UNIQUE KEY `name` (`name`,`version`),
|
78 | KEY `gmt_modified` (`gmt_modified`),
|
79 | KEY `publish_time` (`publish_time`),
|
80 | KEY `author` (`author`)
|
81 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='module info';
|
82 |
|
83 |
|
84 |
|
85 |
|
86 |
|
87 |
|
88 |
|
89 | CREATE TABLE IF NOT EXISTS `module_log` (
|
90 | `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
|
91 | `gmt_create` datetime NOT NULL COMMENT 'create time',
|
92 | `gmt_modified` datetime NOT NULL COMMENT 'modified time',
|
93 | `username` varchar(100) NOT NULL,
|
94 | `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'module name',
|
95 | `log` longtext,
|
96 | PRIMARY KEY (`id`),
|
97 | KEY `name` (`name`)
|
98 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='module sync log';
|
99 |
|
100 |
|
101 | CREATE TABLE IF NOT EXISTS `tag` (
|
102 | `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
|
103 | `gmt_create` datetime NOT NULL COMMENT 'create time',
|
104 | `gmt_modified` datetime NOT NULL COMMENT 'modified time',
|
105 | `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'module name',
|
106 | `tag` varchar(30) NOT NULL COMMENT 'tag name',
|
107 | `version` varchar(30) NOT NULL COMMENT 'module version',
|
108 | `module_id` bigint(20) unsigned NOT NULL COMMENT 'module id',
|
109 | PRIMARY KEY (`id`),
|
110 | UNIQUE KEY `name` (`name`, `tag`),
|
111 | KEY `gmt_modified` (`gmt_modified`)
|
112 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='module tag';
|
113 |
|
114 |
|
115 |
|
116 |
|
117 | CREATE TABLE IF NOT EXISTS `module_unpublished` (
|
118 | `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
|
119 | `gmt_create` datetime NOT NULL COMMENT 'create time',
|
120 | `gmt_modified` datetime NOT NULL COMMENT 'modified time',
|
121 | `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'module name',
|
122 | `package` longtext CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT 'base info: tags, time, maintainers, description, versions',
|
123 | PRIMARY KEY (`id`),
|
124 | UNIQUE KEY `name` (`name`),
|
125 | KEY `gmt_modified` (`gmt_modified`)
|
126 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='module unpublished info';
|
127 |
|
128 | CREATE TABLE IF NOT EXISTS `total` (
|
129 | `name` varchar(100) NOT NULL COMMENT 'total name',
|
130 | `gmt_modified` datetime NOT NULL COMMENT 'modified time',
|
131 | `module_delete` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'module delete count',
|
132 | `last_sync_time` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'last timestamp sync from official registry',
|
133 | `last_exist_sync_time` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'last timestamp sync exist packages from official registry',
|
134 | `sync_status` tinyint unsigned NOT NULL DEFAULT '0' COMMENT 'system sync from official registry status',
|
135 | `need_sync_num` int unsigned NOT NULL DEFAULT '0' COMMENT 'how many packages need to be sync',
|
136 | `success_sync_num` int unsigned NOT NULL DEFAULT '0' COMMENT 'how many packages sync success at this time',
|
137 | `fail_sync_num` int unsigned NOT NULL DEFAULT '0' COMMENT 'how many packages sync fail at this time',
|
138 | `left_sync_num` int unsigned NOT NULL DEFAULT '0' COMMENT 'how many packages left to be sync',
|
139 | `last_sync_module` varchar(100) COMMENT 'last sync success module name',
|
140 | PRIMARY KEY (`name`)
|
141 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='total info';
|
142 |
|
143 | INSERT INTO total(name, gmt_modified) VALUES('total', now())
|
144 | ON DUPLICATE KEY UPDATE gmt_modified=now();
|
145 |
|
146 |
|
147 |
|
148 |
|
149 |
|
150 |
|
151 |
|
152 |
|
153 |
|
154 |
|
155 |
|
156 |
|
157 |
|
158 |
|
159 |
|
160 |
|
161 |
|
162 |
|
163 |
|
164 |
|
165 |
|
166 |
|
167 | CREATE TABLE IF NOT EXISTS `downloads` (
|
168 | `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
|
169 | `gmt_create` datetime NOT NULL COMMENT 'create time',
|
170 | `gmt_modified` datetime NOT NULL COMMENT 'modified time',
|
171 | `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'module name',
|
172 | `date` int unsigned NOT NULL COMMENT 'YYYYMM format',
|
173 | `d01` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '01 download count',
|
174 | `d02` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '02 download count',
|
175 | `d03` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '03 download count',
|
176 | `d04` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '04 download count',
|
177 | `d05` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '05 download count',
|
178 | `d06` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '06 download count',
|
179 | `d07` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '07 download count',
|
180 | `d08` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '08 download count',
|
181 | `d09` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '09 download count',
|
182 | `d10` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '10 download count',
|
183 | `d11` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '11 download count',
|
184 | `d12` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '12 download count',
|
185 | `d13` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '13 download count',
|
186 | `d14` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '14 download count',
|
187 | `d15` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '15 download count',
|
188 | `d16` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '16 download count',
|
189 | `d17` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '17 download count',
|
190 | `d18` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '18 download count',
|
191 | `d19` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '19 download count',
|
192 | `d20` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '20 download count',
|
193 | `d21` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '21 download count',
|
194 | `d22` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '22 download count',
|
195 | `d23` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '23 download count',
|
196 | `d24` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '24 download count',
|
197 | `d25` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '25 download count',
|
198 | `d26` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '26 download count',
|
199 | `d27` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '27 download count',
|
200 | `d28` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '28 download count',
|
201 | `d29` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '29 download count',
|
202 | `d30` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '30 download count',
|
203 | `d31` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '31 download count',
|
204 | PRIMARY KEY (`id`),
|
205 | UNIQUE KEY `name_date` (`name`, `date`),
|
206 | KEY `date` (`date`)
|
207 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='module download total info';
|
208 |
|
209 | CREATE TABLE IF NOT EXISTS `module_deps` (
|
210 | `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
|
211 | `gmt_create` datetime NOT NULL COMMENT 'create time',
|
212 | `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'module name',
|
213 | `deps` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'which module depend on this module',
|
214 | PRIMARY KEY (`id`),
|
215 | UNIQUE KEY `name_deps` (`name`,`deps`),
|
216 | KEY `name` (`name`)
|
217 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='module deps';
|
218 |
|
219 | CREATE TABLE IF NOT EXISTS `dist_dir` (
|
220 | `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
|
221 | `gmt_create` datetime NOT NULL COMMENT 'create time',
|
222 | `gmt_modified` datetime NOT NULL COMMENT 'modified time',
|
223 | `name` varchar(200) NOT NULL COMMENT 'dir name',
|
224 | `parent` varchar(200) NOT NULL COMMENT 'parent dir' DEFAULT '/',
|
225 | `date` varchar(20) COMMENT '02-May-2014 01:06',
|
226 | PRIMARY KEY (`id`),
|
227 | UNIQUE KEY `name` (`parent`, `name`),
|
228 | KEY `gmt_modified` (`gmt_modified`)
|
229 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='dist dir info';
|
230 |
|
231 | CREATE TABLE IF NOT EXISTS `dist_file` (
|
232 | `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
|
233 | `gmt_create` datetime NOT NULL COMMENT 'create time',
|
234 | `gmt_modified` datetime NOT NULL COMMENT 'modified time',
|
235 | `name` varchar(100) NOT NULL COMMENT 'file name',
|
236 | `parent` varchar(200) NOT NULL COMMENT 'parent dir' DEFAULT '/',
|
237 | `date` varchar(20) COMMENT '02-May-2014 01:06',
|
238 | `size` int(10) unsigned NOT NULL COMMENT 'file size' DEFAULT '0',
|
239 | `sha1` varchar(40) COMMENT 'sha1 hex value',
|
240 | `url` varchar(2048),
|
241 | PRIMARY KEY (`id`),
|
242 | UNIQUE KEY `fullname` (`parent`, `name`),
|
243 | KEY `gmt_modified` (`gmt_modified`)
|
244 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='dist file info';
|