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