UNPKG

1.88 kBJavaScriptView Raw
1'use strict';
2
3var dbm;
4var type;
5var seed;
6
7/**
8 * We receive the dbmigrate dependency from dbmigrate initially.
9 * This enables us to not have to rely on NODE_PATH.
10 */
11exports.setup = function(options, seedLink) {
12 dbm = options.dbmigrate;
13 type = dbm.dataType;
14 seed = seedLink;
15};
16
17exports.up = function(db) {
18 //设定最大连接数
19 return db.runSql('set global max_connections=500;', ()=>{
20 //创建存储过程 survive
21 return db.runSql(
22 "CREATE PROCEDURE `survive` (IN `time` VARCHAR(50), OUT `r1` FLOAT, OUT `r3` FLOAT, OUT `r7` FLOAT) " +
23 "BEGIN \n" +
24 " declare a int; \n" +
25 " declare b1 int; \n" +
26 " declare b3 int; \n" +
27 " declare b7 int; \n" +
28 " select count(id) into a from users where createdAt >= time and createdAt < DATE_ADD(time,INTERVAL 1 DAY); \n" +
29 " IF (a = 0) THEN \n" +
30 " set r1 = 0; \n" +
31 " set r3 = 0; \n" +
32 " set r7 = 0; \n" +
33 " ELSE \n" +
34 " select count(*) into b1 from login where type = 1 and uid in (select id from users where createdAt >= time and createdAt < DATE_ADD(time, INTERVAL 1 DAY)); \n" +
35 " select count(*) into b3 from login where type = 3 and uid in (select id from users where createdAt >= time and createdAt < DATE_ADD(time, INTERVAL 1 DAY)); \n" +
36 " select count(*) into b7 from login where type = 7 and uid in (select id from users where createdAt >= time and createdAt < DATE_ADD(time, INTERVAL 1 DAY)); \n" +
37 " set r1 = b1 / a; \n" +
38 " set r3 = b3 / a; \n" +
39 " set r7 = b7 / a; \n" +
40 " END IF; \n" +
41 " select r1, r3, r7; \n" +
42 "END\n"
43 , ()=>{});
44 });
45};
46
47exports.down = function(db) {
48 return db.runSql('DROP PROCEDURE IF EXISTS `survive`;', ()=>{});
49};
50
51exports._meta = {
52 "version": 1
53};