UNPKG

14.7 kBapplication/x-sqlView Raw
1CREATE 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-- ALTER TABLE `user`
19-- ADD `json` longtext CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT 'json details',
20-- ADD `npm_user` tinyint(1) DEFAULT '0' COMMENT 'user sync from npm or not, 1: true, other: false';
21
22CREATE 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
33CREATE 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
43CREATE 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
53CREATE 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
63CREATE 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-- ALTER TABLE `module` ADD `description` longtext;
83-- ALTER TABLE `module` ADD `publish_time` bigint(20) unsigned, ADD KEY `publish_time` (`publish_time`);
84-- ALTER TABLE `module` CHANGE `package` `package` LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci;
85-- ALTER TABLE `module` CHANGE `description` `description` LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci;
86-- show create table module\G
87-- ALTER TABLE `module` CHANGE `name` `name` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'module name';
88
89CREATE 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-- ALTER TABLE `module_log` CHANGE `name` `name` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'module name';
100
101CREATE 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-- ALTER TABLE `tag` ADD `module_id` BIGINT( 20 ) UNSIGNED NOT NULL;
114-- ALTER TABLE `tag` CHANGE `name` `name` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'module name';
115-- ALTER TABLE `tag` ADD KEY `gmt_modified` (`gmt_modified`);
116
117CREATE 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
128CREATE 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-- init `total` count
143INSERT INTO total(name, gmt_modified) VALUES('total', now())
144 ON DUPLICATE KEY UPDATE gmt_modified=now();
145-- ALTER TABLE `total` ADD `last_sync_time` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'last timestamp sync from official registry'
146-- ALTER TABLE `total` ADD `last_exist_sync_time` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'last timestamp sync exist packages from official registry'
147-- ALTER TABLE `total` ADD `sync_status` tinyint unsigned NOT NULL DEFAULT '0' COMMENT 'system sync from official registry status'
148-- ALTER TABLE `total` ADD `need_sync_num` int unsigned NOT NULL DEFAULT '0' COMMENT 'how many packages need to be sync'
149-- ALTER TABLE `total` ADD `success_sync_num` int unsigned NOT NULL DEFAULT '0' COMMENT 'how many packages sync success at this time'
150-- ALTER TABLE `total` ADD `fail_sync_num` int unsigned NOT NULL DEFAULT '0' COMMENT 'how many packages sync fail at this time'
151-- ALTER TABLE `total` ADD `left_sync_num` int unsigned NOT NULL DEFAULT '0' COMMENT 'how many packages left to be sync'
152-- ALTER TABLE `total` ADD `last_sync_module` varchar(100) NOT NULL COMMENT 'last sync success module name';
153
154-- CREATE TABLE IF NOT EXISTS `download_total` (
155-- `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
156-- `gmt_create` datetime NOT NULL COMMENT 'create time',
157-- `gmt_modified` datetime NOT NULL COMMENT 'modified time',
158-- `date` datetime NOT NULL COMMENT 'YYYY-MM-DD format',
159-- `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'module name',
160-- `count` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'download count',
161-- PRIMARY KEY (`id`),
162-- UNIQUE KEY `date_name` (`date`, `name`)
163-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='module download total info';
164-- ALTER TABLE `download_total` CHANGE `name` `name` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'module name';
165-- ALTER TABLE `download_total` CHANGE `date` `date` datetime NOT NULL COMMENT 'YYYY-MM-DD format';
166
167CREATE 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
209CREATE 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
219CREATE 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
231CREATE 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';