1 | 'use strict';
|
2 |
|
3 | var dbm;
|
4 | var type;
|
5 | var seed;
|
6 |
|
7 |
|
8 |
|
9 |
|
10 |
|
11 | exports.setup = function(options, seedLink) {
|
12 | dbm = options.dbmigrate;
|
13 | type = dbm.dataType;
|
14 | seed = seedLink;
|
15 | };
|
16 |
|
17 | exports.up = function(db) {
|
18 |
|
19 | return db.runSql('set global max_connections=500;', ()=>{
|
20 |
|
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 |
|
47 | exports.down = function(db) {
|
48 | return db.runSql('DROP PROCEDURE IF EXISTS `survive`;', ()=>{});
|
49 | };
|
50 |
|
51 | exports._meta = {
|
52 | "version": 1
|
53 | };
|