· 7 years ago · Oct 12, 2018, 10:06 PM
1const mysql = require('mysql');
2var shortid = require('shortid');
3//shortid.characters('0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ-_');
4const objectAssign = require('object-assign');
5var SqlString = require('sqlstring');
6var escapeJSON = require('escape-json-node');
7const bcrypt = require('bcrypt');
8const saltRounds = 10;
9
10var inherits = require('util').inherits;
11
12var wrapper = {
13 /* .create(sqlopts, callback)
14 sqlopts = {
15 host:
16 username:
17 password:
18 database:
19 }
20 */
21 create: function (sqlopts) {
22 var buffer = objectAssign({}, syrupdb);
23 buffer.sqlopts = sqlopts
24 return buffer;
25 }
26}
27module.exports = wrapper;
28
29var syrupdb = {
30 // .connect(callback(err))
31 connect: function (cb, opt) {
32 //connect to database
33 this.connection = mysql.createPool(this.sqlopts);
34 this.isConnected = true;
35 //this.connection.connect(function(err) {
36 cb(false);
37 //});
38 },
39 sqlopts: {},
40
41 createTable: function (name, cachetime, columns, cb) {
42 if (typeof this.tables[name] == 'undefined') {
43 this.tables[name] = new table(name, cachetime, columns, this, false);
44 this.tables[name].save(() => {
45 if (typeof cb !== 'undefined') {cb(false, this.tables[name]);}
46 });
47 }
48 },
49
50 createMemTable: function (name, columns) {
51 if (typeof this.tables[name] == 'undefined') {
52 this.tables[name] = new table(name, -1, columns, this, true);
53 return this.tables[name]
54 }
55 },
56 tables: {},
57 connection: {}
58}
59
60//TABLE CONSTRUCTOR
61function table(name, cachetime, columns, database, memonly){
62 var buffer = this;
63 if (typeof memonly == 'undefined') {memonly = false;}
64 this.name = name;
65 this.keys = {};
66 this.cachetime = cachetime;
67 this.columns = columns;
68 this.database = database;
69 this.memonly = memonly;
70 this.rowPrototype = row;
71 this.layout = {}
72 Object.keys(columns).forEach((i) => {
73 buffer.layout[columns[i]['name']] = columns[i];
74 if (typeof buffer.layout[columns[i]['name']]['default'] == 'undefined') {buffer.layout[columns[i]['name']]['default'] = ''}
75 //console.log('layout.' + columns[i]['name'] + ' = ')
76 //console.log(columns[i])
77 });
78 //console.log('Layout for table ' + this.name)
79 //console.log(this.layout)
80}
81table.prototype.getKeys = function () {
82 //Load row keys
83}
84table.prototype.get = function (column, data, cb, isUnique) {
85 //console.log('getting')
86 //Search memory and then pull from SQL if can't find
87 var buffer = this;
88 var ret = [];
89 var flag = true;
90 if (typeof isUnique == 'undefined'){isUnique = true}
91 if (isUnique){
92 Object.keys(this.keys).forEach((i) => {
93 if (buffer.keys[i][column] === data){
94 ret.push(buffer.keys[i]);
95 if (isUnique) {flag = false;cb(false, ret[0]);return false}
96 }
97 })
98 }
99 if (this.memonly && flag){
100 if (isUnique) {cb(false, ret[0]);} else {cb(false, ret);}
101 } else {
102 if (flag){
103 //console.log('moving to sql')
104 ret = [];
105 var sql = "SELECT * FROM " + buffer.name + " WHERE " + column + "=" + toolkit.parseValue2SQL(data) + ";"
106 buffer.database.connection.query(sql, function (err, result) {
107 if (result.length == 0) {cb(true);return}
108 //load data into memory db for caching - TO DO
109 Object.keys(result).forEach((irow) => {
110 var key = result[irow]['rowkey']
111 buffer.keys[ key ] = new buffer.rowPrototype(buffer, key, true);
112 Object.keys(result[irow]).forEach((icol) => {
113 if (icol !== 'rowkey'){
114 if ( Object.getPrototypeOf(buffer.keys[key][icol]) === encryptedItem.prototype ){
115 buffer.keys[key][icol]['hash'] = result[irow][icol];
116 } else if (typeof buffer.layout[icol]['default'] == 'object') {
117 buffer.keys[key][icol] = JSON.parse(result[irow][icol])
118 } else {
119 buffer.keys[key][icol] = result[irow][icol]
120 }
121 }
122 })
123 ret.push(buffer.keys[key])
124 });
125 //console.log('found in SQL')
126 if (isUnique) {cb(false, ret[0]);} else {cb(false, ret);}
127 });
128 }
129 }
130}
131table.prototype.addRow = function (data, key, cb) {
132 var buffer = this;
133 if (typeof data == 'undefined'){data = {}}
134 if (typeof key == 'undefined') {key = shortid.generate();}
135 if (key == -1) {key = shortid.generate();}
136 this.keys[key] = new buffer.rowPrototype(this, key);
137 //console.log(this.keys[key]);
138 //console.log('Applying passed data..')
139 var delay = false;
140 Object.keys(data).forEach((i) => {
141 //console.log(Object.getPrototypeOf(buffer.keys[key][i]));
142 //console.log(i);
143
144 if (Object.getPrototypeOf(buffer.keys[key][i]) === encryptedItem.prototype) {
145 delay = true;
146 //console.log('Encrypted item found for ' + i)
147 buffer.keys[key][i].overwrite(data[i], (err, value) => {
148 cb(buffer.keys[key]);
149 if (buffer.memonly == false) {buffer.keys[key].save()}
150 delay = false;
151 })
152 } else {
153 //console.log('Regular item found for ' + i)
154 buffer.keys[key][i] = data[i]
155 }
156 })
157
158 if (typeof cb === 'function' && !delay){
159 cb(buffer.keys[key])
160 if (buffer.memonly == false) {buffer.keys[key].save()}
161 }
162}
163table.prototype.loadAll = function (cb) {
164 if (!this.memonly){
165 var buffer = this;
166 var sql = "SELECT * FROM " + this.name
167 this.database.connection.query(sql, function (err, result) {
168 Object.keys(result).forEach((i) => {
169 if (typeof buffer.keys[result[i]['rowkey']] == 'undefined') {
170 buffer.keys[result[i]['rowkey']] = new buffer.rowPrototype(buffer, result[i]['rowkey'], true);
171 }
172 Object.keys(result[i]).forEach((ii) => {
173 if (ii !== 'rowkey') {
174 var key = result[i]['rowkey'];
175 if (Object.getPrototypeOf(buffer.keys[key][ii]) === encryptedItem.prototype){
176 buffer.keys[key][ii].hash = result[i][ii];
177 } else if (typeof buffer.layout[ii]['default'] == 'object') {
178 buffer.keys[key][ii] = JSON.parse(result[i][ii])
179 } else {
180 buffer.keys[key][ii] = result[i][ii]
181 }
182 }
183 });
184 });
185 if (typeof cb === 'function'){cb(err)}
186 });
187 }
188}
189table.prototype.loadRow = function (key) {
190 if (!this.memonly){
191 var buffer = this;
192 var sql = "SELECT * FROM " + this.name + " WHERE rowkey='" + key + "'"
193 this.database.connection.query(sql, function (err, result) {
194 if (typeof buffer.keys[result[0]['rowkey']] == 'undefined') {
195 buffer.keys[result[0]['rowkey']] = new buffer.rowPrototype(buffer, result[0]['rowkey'], true);
196 }
197 Object.keys(result[0]).forEach((ii) => {
198 var key = result[0]['rowkey'];
199 if (ii !== 'rowkey') {
200 if (Object.getPrototypeOf(buffer.keys[key][ii]) === encryptedItem.prototype){
201 buffer.keys[key][ii].hash = result[0][ii];
202 } else if (typeof buffer.layout[ii]['default'] == 'object') {
203 buffer.keys[key][ii] = JSON.parse(result[i][ii])
204 } else {
205 buffer.keys[key][ii] = result[0][ii];
206 }
207 }
208 });
209 });
210 }
211}
212table.prototype.save = function (cb) {
213 if (!this.memonly){
214 //console.log(this);
215 var tbuffer = this;
216 var columnlist = '(rowkey VARCHAR(64)';
217 Object.keys(tbuffer.columns).forEach(function (i) {
218 var cbuffer = tbuffer.columns[i];
219 //if (cbuffer.encrypted){
220
221 //} else {
222 columnlist = columnlist + ', ' + cbuffer['name'] + ' ' + cbuffer['type'] + ' NULL'
223 if (typeof cbuffer['default'] !== 'undefined'){
224 columnlist += ' DEFAULT ' + toolkit.parseValue2SQL(cbuffer['default'])
225 }
226 //}
227 });
228 columnlist = columnlist + ", INDEX `rowkey` (`rowkey`)) COLLATE='utf8_general_ci'"
229 var sql = "CREATE TABLE IF NOT EXISTS " + tbuffer.name + columnlist + ";";
230 //console.log(sql);
231 tbuffer.database.connection.query(sql, function (err, result) {
232 if (err) throw err;
233 //console.log("Table created");
234 if (typeof cb !== 'undefined') {cb();}
235 return;
236 });
237 }
238}
239/*
240CREATE TABLE `users` (
241 `rowkey` VARCHAR(12) NULL,
242 `username` VARCHAR(50) NULL DEFAULT 'player1',
243 INDEX `rowkey` (`rowkey`)
244)
245COLLATE='utf8_general_ci'
246;
247*/
248
249//ROW CONSTRUCTOR
250function row(table, key, isLoading){
251 var buffer = this
252 buffer.rowkey = key;
253 buffer._table = table;
254 Object.keys(table.columns).forEach(function (i) {
255 var cbuffer = table.columns[i];
256 if (cbuffer.encrypted){
257 buffer[cbuffer.name] = new encryptedItem();
258 } else {
259 buffer[table.columns[i]['name']] = table.columns[i]['default'];
260 }
261 });
262 if (!isLoading && !table.memonly){
263 var sql = 'INSERT INTO ' + table.name + ' (rowkey) VALUES ("' + key + '")'
264 table.database.connection.query(sql, function (err, result) {
265 //if (err) {console.log(key + ' Row alrady exists on SQL? (row create)\n' + err)}
266 buffer.save()
267 });
268 }
269}
270row.prototype.save = function (cb) {
271 if (!this._table.memonly){
272 var buffer = this;
273 var columns = ""
274 Object.keys(buffer._table.columns).forEach(function (i) {
275 if (columns !== "") {columns += ", "}
276 columns += buffer._table.columns[i]['name'] + " = " + toolkit.parseValue2SQL( buffer[buffer._table.columns[i]['name']] );
277 });
278
279 var sql = "UPDATE " + buffer._table.name + " SET " + columns + " WHERE rowkey = '" + this.rowkey + "';"
280 //console.log(sql);
281 buffer._table.database.connection.query(sql, function (err, result) {
282 //if (err) {console.log('Problem saving row ' + this.rowkey + '\n' + err)}
283 if (typeof cb == 'function'){cb(err)}
284 });
285 }
286}
287row.prototype.unload = function (){
288 delete this._table.keys[this.rowkey]
289}
290row.prototype.purge = function () {
291 if (this._table.memonly) {
292 delete this._table.keys[this.rowkey]
293 } else {
294 var buffer = this;
295 sql = "DELETE FROM " + this._table.name + " WHERE rowkey='" + this.rowkey + "';"
296 console.log(sql);
297 this._table.database.connection.query(sql, function (err, result) {
298 if (err) {console.log(err)}
299 delete buffer._table.keys[buffer.rowkey]
300 });
301 }
302}
303
304
305//ENCRYPTED ITEM DEFINITION
306var encryptedItem = function (){
307 this.hash = '';
308}
309encryptedItem.prototype.comparehash = function (s_compare, cb){
310 //console.log('comparing...')
311 bcrypt.compare(s_compare, this.hash, function(err, res) {
312 //console.log('compared - ' + res)
313 cb(res);
314 });
315}
316encryptedItem.prototype.overwrite = function (newstring, cb){
317 var buffer = this;
318 bcrypt.hash(newstring, saltRounds, function(err, hash) {
319 if (err) {cb('Error hashing'); console.log('Error with hashing!')} else {
320 //console.log(hash)
321 buffer.hash = hash;
322 cb(err, buffer);
323 }
324 });
325}
326
327
328//INTERNAL FUNCTIONS
329var toolkit = {
330 parseValue2SQL: function(value) {
331 if (typeof value == 'string'){
332 return SqlString.escape(value);
333 } else if (typeof value == 'boolean') {
334 if (value) {return 1;} else {return 0;}
335 } else if ( Object.getPrototypeOf(value) === encryptedItem.prototype ) {
336 return SqlString.escape(value.hash);
337 } else if (typeof value == 'object') {
338 return SqlString.escape( JSON.stringify(value) );
339 } else {
340 //console.log('parse no type')
341 return value
342 }
343 }
344}