bkServer/database.ts at master · Beatkhana/bkServer · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
import mysql from 'mysql';
import { connect } from 'http2';
// const mysql = mysql;
let con: mysql.Pool;

export class database {

    // to remove
    public oldCon: any;
    public connected: boolean = false;

    constructor() {
        const env = process.env.NODE_ENV || 'production';
        // console.log(env);
        if (!con) {
            if (env == 'production') {
                con = mysql.createPool({
                    host: "localhost",
                    user: "dan",
                    password: "root",
                    database: "bk",
                    connectionLimit: 100,
                    charset: 'utf8mb4',
                    timezone: 'utc'
                });
            } else {
                con = mysql.createPool({
                    host: "localhost",
                    user: "dan",
                    password: "test",
                    database: "bk",
                    connectionLimit: 100,
                    charset: 'utf8mb4',
                    timezone: 'utc'
                });
            }

        }
        if (env == 'production') {
            this.oldCon = mysql.createPool({
                host: "localhost",
                user: "dan",
                password: "root",
                database: "bk",
                connectionLimit: 100,
                charset: 'utf8mb4',
                timezone: 'utc'
            });
        } else {
            this.oldCon = mysql.createPool({
                host: "localhost",
                user: "dan",
                password: "test",
                database: "bk",
                connectionLimit: 100,
                charset: 'utf8mb4',
                timezone: 'utc'
            });
        }
    }

    query(sql: string, callback: Function) {
        this.oldCon.getConnection(function (err, connection) {
            if (err) throw err;
            var query = connection.query(sql, function (error, results, fields) {
                let result = results;
                err = error;
                connection.release();
                // if (error) throw error;
                // console.log(query.sql)
                return callback(err, result);
            });
        });
    }

    preparedQuery(sql: string, params: any[], callback: Function) {
        this.oldCon.getConnection(function (err, connection) {
            if (err) throw err;
            var result;
            var query = connection.query(sql, params, function (error, results, fields) {
                result = results;
                connection.release();
                return callback(error, result);
            });
        });
    }

    async aQuery(sql: string, params: any[] = []) {
        return new Promise<any[]>((resolve, reject) => {
            con.query(sql, params, (err: any, result: any, fields: any) => {
                if (err) reject(err);
                resolve(result);
            });
        });
    }

    async asyncPreparedQuery(sql: string, params: any[] = []) {
        return new Promise((resolve, reject) => {
            this.oldCon.query(sql, params, (err, result, fields) => {
                if (err) reject(err);
                resolve(result);
            });
        });
    }

    async paginationQuery(table: string, page: number, perPage: number, sql: string, params: any[] = []) {
        let numRecords: any = await this.asyncPreparedQuery('SELECT COUNT(*) as numRows FROM ??', [table]);
        numRecords = numRecords[0].numRows;
        if(page <= numRecords/perPage) {
            sql += " LIMIT ? OFFSET ?";
            params.push(+perPage);
            params.push(page*perPage);
            return new Promise((resolve, reject) => {
                let query = this.oldCon.query(sql, params, (err, result, fields) => {
                    if (err) reject(err);
                    // console.log(query.sql);
                    resolve({total: numRecords, data: result});
                });
            });
        } else {
            return {err: 'Invalid requested page'};
        }
    }
}