1 |
|
2 |
|
3 |
|
4 | 'use strict';
|
5 |
|
6 | const ESCAPE_REGEX = /[\0\n\r\b\t\\'"\x1a]/g;
|
7 |
|
8 | const Base = require('./Database');
|
9 |
|
10 | module.exports = class MysqlDatabase extends Base {
|
11 |
|
12 | constructor (config) {
|
13 | super({
|
14 | schema: 'mysql',
|
15 | QueryBuilder: require('./MysqlBuilder'),
|
16 | ...config
|
17 | });
|
18 | this._client = mysql.createPool(this.settings);
|
19 | }
|
20 |
|
21 | async openConnection () {
|
22 |
|
23 | return this.getConnection();
|
24 | }
|
25 |
|
26 | async closeConnection () {
|
27 | await PromiseHelper.promise(this._client.end, this._client);
|
28 | }
|
29 |
|
30 | getQueryData (data) {
|
31 | return data.err ? `${data.err.toString()}: ${data.sql}` : data.sql;
|
32 | }
|
33 |
|
34 | logCommand (data) {
|
35 | this.log('trace', this.settings.database, data);
|
36 | }
|
37 |
|
38 | async getConnection () {
|
39 | const connection = await PromiseHelper.promise(this._client.getConnection, this._client);
|
40 | if (connection) {
|
41 | connection.release();
|
42 | }
|
43 | return connection;
|
44 | }
|
45 |
|
46 | async execute (sql) {
|
47 | const connection = await this.getConnection();
|
48 | this.logCommand({sql});
|
49 | const result = await PromiseHelper.promise(connection.query, connection);
|
50 | connection.release();
|
51 | return result;
|
52 | }
|
53 |
|
54 |
|
55 |
|
56 | create (table, fields, keys, engine = 'innodb') {
|
57 | const data = [];
|
58 | for (const name of Object.keys(fields)) {
|
59 | data.push(`${this.escapeId(name)} ${fields[name]}`);
|
60 | }
|
61 | if (keys) {
|
62 | data.push(keys);
|
63 | }
|
64 | return this.execute(`CREATE TABLE IF NOT EXISTS ${this.escapeId(table)} (${data}) engine=${engine}`);
|
65 | }
|
66 |
|
67 | find (table, condition) {
|
68 | let sql = `SELECT * FROM ${this.escapeId(table)}`;
|
69 | if (condition) {
|
70 | sql += condition;
|
71 | }
|
72 | return this.execute(sql);
|
73 | }
|
74 |
|
75 | async insert (table, data) {
|
76 | const columns = this.escapeId(Object.keys(data)).join();
|
77 | const values = this.escape(Object.values(data));
|
78 | const sql = `INSERT INTO ${this.escapeId(table)} (${columns}) VALUES (${values})`;
|
79 | const result = await this.execute(sql);
|
80 | return result.insertId;
|
81 | }
|
82 |
|
83 | upsert (table, condition, data) {
|
84 | const columns = this.escapeId(Object.keys(data));
|
85 | const values = this.escape(Object.values(data));
|
86 | const updates = columns.map(column => `${column}=VALUES(${column})`).join();
|
87 | const sql = `INSERT INTO ${this.escapeId(table)} (${columns.join()}) VALUES (${values}) ON DUPLICATE KEY UPDATE ${updates}`;
|
88 | return this.execute(sql);
|
89 | }
|
90 |
|
91 | update (table, condition, data) {
|
92 | let values = Object.keys(data).map(key => `${this.escapeId(key)}=${this.escape(data[key])}`).join();
|
93 | let sql = `UPDATE ${this.escapeId(table)} SET ${values}`;
|
94 | if (condition) {
|
95 | sql += condition;
|
96 | }
|
97 | return this.execute(sql);
|
98 | }
|
99 |
|
100 | updateAll () {
|
101 | throw new Error('TODO');
|
102 | }
|
103 |
|
104 | delete (table, condition) {
|
105 | let sql = `DELETE FROM ${this.escapeId(table)}`;
|
106 | if (condition) {
|
107 | sql += condition;
|
108 | }
|
109 | return this.execute(sql);
|
110 | }
|
111 |
|
112 | drop (table) {
|
113 | return this.execute(`DROP TABLE IF EXISTS ${this.escapeId(table)}`);
|
114 | }
|
115 |
|
116 | truncate (table) {
|
117 | return this.execute(`TRUNCATE FROM ${this.escapeId(table)}`);
|
118 | }
|
119 |
|
120 |
|
121 |
|
122 | async count (table, condition) {
|
123 | const sql = `SELECT COUNT(*) FROM ${table} ${condition}`;
|
124 | const result = await this.execute(sql);
|
125 | return result[0]['COUNT(*)'];
|
126 | }
|
127 |
|
128 |
|
129 |
|
130 | async queryAll (query) {
|
131 | let cmd = await this.buildQuery(query);
|
132 | let docs = await this.execute(this._builder.stringify(cmd));
|
133 | if (!cmd.order) {
|
134 | docs = query.sortOrderByKeys(docs);
|
135 | }
|
136 | return query.populate(docs);
|
137 | }
|
138 |
|
139 | async queryColumn (query, key) {
|
140 | const data = await this.queryAll(query.raw().select(key));
|
141 | if (Array.isArray(data)) {
|
142 | return data.map(doc => doc[key]);
|
143 | }
|
144 | for (const name of Object.keys(data)) {
|
145 | data[name] = data[name][key];
|
146 | }
|
147 | return data;
|
148 | }
|
149 |
|
150 | async queryScalar (query, key) {
|
151 | const docs = await this.queryAll(query.raw().select(key).limit(1));
|
152 | return docs.length ? docs[0] : undefined;
|
153 | }
|
154 |
|
155 | async queryInsert (query, data) {
|
156 | const cmd = await this.buildQuery(query);
|
157 | return this.insert(cmd.from, data);
|
158 | }
|
159 |
|
160 | async queryUpdate (query, data) {
|
161 | const cmd = await this.buildQuery(query);
|
162 | return this.update(cmd.from, cmd.where, data);
|
163 | }
|
164 |
|
165 | async queryUpdateAll (query, data) {
|
166 | const cmd = await this.buildQuery(query);
|
167 | return this.updateAll(cmd.from, cmd.where, data);
|
168 | }
|
169 |
|
170 | async queryUpsert (query, data) {
|
171 | const cmd = await this.buildQuery(query);
|
172 | return this.upsert(cmd.from, cmd.where, data);
|
173 | }
|
174 |
|
175 | async queryDelete (query) {
|
176 | const cmd = await this.buildQuery(query);
|
177 | return this.delete(cmd.from, cmd.where);
|
178 | }
|
179 |
|
180 | async queryCount (query) {
|
181 | const cmd = await this.buildQuery(query);
|
182 | return this.count(cmd.from, cmd.where);
|
183 | }
|
184 |
|
185 |
|
186 |
|
187 | escapeId (id) {
|
188 | return Array.isArray(id)
|
189 | ? id.map(id => this.escapeOneId(id))
|
190 | : this.escapeOneId(id);
|
191 | }
|
192 |
|
193 | escapeOneId (id) {
|
194 | return id.charAt(0) === '`' ? id : ('`'+ id +'`');
|
195 | }
|
196 |
|
197 | escape (value) {
|
198 | if (value === undefined || value === null) {
|
199 | return 'NULL';
|
200 | }
|
201 | switch (typeof value) {
|
202 | case 'boolean': return value ? 'TRUE' : 'FALSE';
|
203 | case 'number': return value.toString();
|
204 | }
|
205 | if (value instanceof Date) {
|
206 | value = moment(value).format('YYYY-MM-DD HH:mm:ss')
|
207 | }
|
208 | if (Array.isArray(value)) {
|
209 | return this.escapeValueArray(value);
|
210 | }
|
211 | if (value instanceof Expression) {
|
212 | return value.get(this);
|
213 | }
|
214 | value = value.replace(ESCAPE_REGEX, function(s) {
|
215 | switch (s) {
|
216 | case "\0": return "\\0";
|
217 | case "\n": return "\\n";
|
218 | case "\r": return "\\r";
|
219 | case "\b": return "\\b";
|
220 | case "\t": return "\\t";
|
221 | case "\x1a": return "\\Z";
|
222 | }
|
223 | return "\\"+ s;
|
224 | });
|
225 | return `'${value}'`;
|
226 | }
|
227 |
|
228 | escapeValueArray (items) {
|
229 | return items.map(item => Array.isArray(item)
|
230 | ? this.escapeValueArray(item)
|
231 | : this.escape(item)
|
232 | ).join();
|
233 | }
|
234 |
|
235 |
|
236 |
|
237 | };
|
238 | module.exports.init();
|
239 |
|
240 | const moment = require('moment');
|
241 | const mysql = require('mysql');
|
242 | const PromiseHelper = require('../helper/PromiseHelper');
|
243 | const Expression = require('./Expression'); |
\ | No newline at end of file |