-
Notifications
You must be signed in to change notification settings - Fork 0
/
persistence.js
358 lines (339 loc) · 13.9 KB
/
persistence.js
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
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
var config = require( './config.js' ),
promiseDbLib = require( 'mysql-promise' );
function getConnection() {
var promiseDb = promiseDbLib();
promiseDb.configure( {
host: config.userDbServer,
user: config.userDbLogin,
password: config.userDbPwd,
database: config.userDb,
multipleStatements: true
} );
return promiseDb;
}
function insertWidgetList( board, connection ) {
var numWidgets = board.widgets.length,
i,
insertWidgets = 'INSERT INTO dash_widget_instance_board ( instance_id, board_id, widget_position ) VALUES ',
placeholders = '',
values = [];
if ( numWidgets === 0 ) {
return;
}
for ( i = 0; i < numWidgets; i++ ) {
if ( i > 0 ) {
placeholders += ',';
}
placeholders += ' ( ?, ?, ? )';
values.push( board.widgets[ i ] );
values.push( board.id );
values.push( i );
}
return connection.query( insertWidgets + placeholders, values );
}
module.exports = {
/**
* Run a query
*/
query: function ( query, params, callback, errorCallback ) {
var connection = getConnection();
connection.query( query, params )
.then( callback )
.catch( errorCallback );
},
/**
* Ensures a user exists in the user table and saves the user's local db id
* in session. Creates a default board if none exists.
*
* @param {Object} user should have displayName, provider, and id set by oauth
* @return {Promise} that fulfills on completion or rejects with error
*/
loginUser: function ( user ) {
var params = [ user.id, user.provider, user.displayName ],
insertUser = 'INSERT IGNORE INTO dash_user ( oauth_id, oauth_provider, display_name ) VALUES ( ?, ?, ? )',
getInfo = 'SELECT id, default_board, avatar, title, email from dash_user where oauth_id = ? and oauth_provider = ?',
insertBoard = 'INSERT INTO dash_board ( display_name, description, owner_id ) VALUES ( ?, \'\', ? );' +
'UPDATE dash_user SET default_board = LAST_INSERT_ID() WHERE id = ?;' +
'SELECT LAST_INSERT_ID() AS id',
insertBigEnglish = 'SET @uid = ?;\n' +
'INSERT INTO dash_board ( display_name, description, owner_id ) VALUES ( \'Big English\', \'\', @uid );\n' +
'SET @beboard = LAST_INSERT_ID();\n' +
'INSERT INTO dash_widget_instance ( widget_id, owner_id, display_name, description )\n' +
'SELECT id, @uid, display_name, description FROM dash_widget WHERE code IN ( \'totals-earned-chart\', \'distance-to-goal-chart\', \'amt-per-second-chart\' );\n' +
'INSERT INTO dash_widget_instance_board ( instance_id, board_id, widget_position )\n' +
'SELECT dwi.id, @beboard, 1 FROM dash_widget_instance dwi JOIN dash_widget dw ON dwi.widget_id = dw.id WHERE owner_id = @uid AND code = \'totals-earned-chart\';\n' +
'INSERT INTO dash_widget_instance_board ( instance_id, board_id, widget_position )\n' +
'SELECT dwi.id, @beboard, 2 FROM dash_widget_instance dwi JOIN dash_widget dw ON dwi.widget_id = dw.id WHERE owner_id = @uid AND code = \'distance-to-goal-chart\';\n' +
'INSERT INTO dash_widget_instance_board ( instance_id, board_id, widget_position )\n' +
'SELECT dwi.id, @beboard, 3 FROM dash_widget_instance dwi JOIN dash_widget dw ON dwi.widget_id = dw.id WHERE owner_id = @uid AND code = \'amt-per-second-chart\';' +
'INSERT INTO dash_widget_instance_board ( instance_id, board_id, widget_position )\n' +
'SELECT dwi.id, @beboard, 4 FROM dash_widget_instance dwi JOIN dash_widget dw ON dwi.widget_id = dw.id WHERE owner_id = @uid AND code = \'donation-age\';\n',
connection = getConnection(),
defaultBoard,
userId;
return connection.query( insertUser, params )
.then( function () {
return connection.query( getInfo, params );
} )
.then( function ( dbResults ) {
var avatar = dbResults[ 0 ][ 0 ].avatar,
title = dbResults[ 0 ][ 0 ].title,
email = dbResults[ 0 ][ 0 ].email,
username;
userId = dbResults[ 0 ][ 0 ].id;
defaultBoard = dbResults[ 0 ][ 0 ].default_board;
user.localId = userId;
user.avatar = avatar;
user.title = title;
user.email = email;
if ( defaultBoard ) {
user.defaultBoard = defaultBoard;
return;
}
// If user doesn't have a default board, insert one now
username = user.displayName.charAt( 0 ).toUpperCase() + user.displayName.slice( 1 );
return connection.query( insertBoard, [ username + '\'s Board', userId, userId ] );
} )
.then( function ( dbResults ) {
if ( !dbResults ) {
return;
}
user.defaultBoard = dbResults[ 0 ][ 2 ][ 0 ].id;
return connection.query( insertBigEnglish, [ userId ] );
} );
},
/**
* Saves a widget configuration
*
* @param {Object} instance should have ownerId, widgetId, displayName,
* isShared, and configuration set. If id is not set, this function creates
* a new instance and sets the id.
* @return {Promise} that fulfills on completion or rejects with error
*/
saveWidgetInstance: function ( instance ) {
var insert = 'INSERT INTO dash_widget_instance ( widget_id, owner_id, display_name, description, is_shared, configuration ) VALUES ( ?, ?, ?, ?, ?, ? )',
update = 'UPDATE dash_widget_instance set display_name = ?, description = ?, is_shared = ?, configuration = ? WHERE id = ? AND owner_id = ?',
insertParams = [ instance.widgetId, instance.ownerId, instance.displayName, instance.description, instance.isShared ? 1 : 0, instance.configuration ],
updateParams = [ instance.displayName, instance.description, instance.isShared ? 1 : 0, instance.configuration, instance.id, instance.ownerId ],
connection = getConnection();
if ( instance.id ) {
return connection.query( update, updateParams )
.then( function ( dbResults ) {
if ( dbResults[ 0 ].affectedRows !== 1 ) {
// Either the instance doesn't exist or it's not ours
throw new Error( 'Instance ' + instance.id + ' with owner ' + instance.ownerId + ' not found' );
}
} );
}
return connection.query( insert, insertParams ).then( function ( dbResults ) {
instance.id = dbResults[ 0 ].insertId;
} );
},
/**
* @param {number} instanceId ID of widget instance to fetch
* @param {number} userId local ID of user
* @return {Promise} that resolves with a JSON representation of all
* board widgets or rejects with error
*/
getWidgetInstance: function ( instanceId, userId ) {
var connection = getConnection(),
select = 'SELECT wi.widget_id, w.code, wi.owner_id, wi.display_name, wi.description, wi.is_shared, wi.configuration FROM dash_widget_instance wi INNER JOIN dash_widget w ON w.id = wi.widget_id WHERE wi.id = ? AND ( wi.is_shared OR wi.owner_id = ? )';
return connection.query( select, [ instanceId, userId ] )
.then( function ( dbResults ) {
var result = dbResults[ 0 ][ 0 ];
if ( result.owner_id ) {
return {
id: instanceId,
widgetId: result.widget_id,
widgetCode: result.code,
ownerId: result.owner_id,
displayName: result.display_name,
description: result.description,
isShared: result.is_shared === 1,
configuration: JSON.parse( result.configuration )
};
} else {
throw new Error( 'Instance ' + instanceId + ' for user ' + userId + ' not found' );
}
} );
},
/**
* List all widget instances available to a user
*
* @param {number} userId local ID of user
* @return {Promise} that resolves with a JSON representation of all
* widget instances owned by or shared with the user, or rejects with error
*/
listWidgetInstances: function ( userId ) {
var connection = getConnection(),
select = 'SELECT wi.id, wi.widget_id, w.code, wi.owner_id, wi.display_name, wi.description, wi.is_shared, wi.configuration, w.preview_path FROM dash_widget_instance wi INNER JOIN dash_widget w on w.id = wi.widget_id WHERE wi.is_shared OR wi.owner_id = ?';
return connection.query( select, [ userId ] )
.then( function ( dbResults ) {
var rows = dbResults[ 0 ],
count = rows.length,
i,
result = [];
for ( i = 0; i < count; i++ ) {
result[ i ] = {
id: rows[ i ].id,
widgetId: rows[ i ].widget_id,
widgetCode: rows[ i ].code,
ownerId: rows[ i ].owner_id,
displayName: rows[ i ].display_name,
description: rows[ i ].description,
isShared: rows[ i ].is_shared === 1,
configuration: JSON.parse( rows[ i ].configuration ),
previewPath: rows[ i ].preview_path
};
}
return result;
} );
},
/**
* Saves a board
*
* @param {Object} board should have ownerId, displayName, description,
* isShared, and widgets (an ordered array of widget instance ids) set.
* If id is not set, this function creates a new board and sets the id.
* @return {Promise} that fulfills on completion or rejects with error
* TODO: transactional!
*/
saveBoard: function ( board ) {
var connection = getConnection(),
insert = 'INSERT INTO dash_board ( owner_id, display_name, description, is_shared ) VALUES ( ?, ?, ?, ? )',
addWidget = 'INSERT INTO dash_widget_instance_board ( instance_id, board_id, widget_position ) SELECT ?, b.id, COALESCE( MAX( widget_position ), 0 ) + 1 FROM dash_board b LEFT JOIN dash_widget_instance_board i ON b.id = i.board_id WHERE b.id = ? AND b.owner_id = ?',
addWidgetParams = [ board.addWidget, board.id, board.ownerId ],
deleteWidget = 'DELETE FROM dash_widget_instance_board WHERE instance_id = ? AND board_id = ? AND EXISTS( SELECT 1 FROM dash_board WHERE id = ? AND owner_id = ? )',
deleteWidgetParams = [ board.deleteWidget, board.id, board.id, board.ownerId ],
insertParams = [ board.ownerId, board.displayName, board.description, board.isShared ? 1 : 0 ],
update = 'UPDATE dash_board set display_name = ?, description = ?, is_shared = ? WHERE id = ? AND owner_id = ?',
updateParams = [ board.displayName, board.description, board.isShared ? 1 : 0, board.id, board.ownerId ],
deleteWidgets = 'DELETE FROM dash_widget_instance_board WHERE board_id = ?';
if ( board.id ) {
if ( board.addWidget ) {
return connection.query( addWidget, addWidgetParams );
}
if ( board.deleteWidget ) {
return connection.query( deleteWidget, deleteWidgetParams );
}
return connection.query( update, updateParams )
.then( function ( dbResults ) {
if ( dbResults[ 0 ].affectedRows !== 1 ) {
// Either the board doesn't exist or it's not ours
throw new Error( 'Board ' + board.id + ' with owner ' + board.ownerId + ' not found' );
}
} )
.then( function () {
return connection.query( deleteWidgets, [ board.id ] );
} )
.then( function () {
return insertWidgetList( board, connection );
} );
}
return connection.query( insert, insertParams )
.then( function ( dbResults ) {
board.id = dbResults[ 0 ].insertId;
} )
.then( function () {
return insertWidgetList( board, connection );
} );
},
/**
* @param {number} boardId ID of board to fetch
* @param {number} userId local ID of user
* @return {Promise} that resolves with a JSON representation of all
* board widgets or rejects with error
*/
getBoard: function ( boardId, userId ) {
var board,
connection = getConnection(),
select = 'SELECT owner_id, display_name, description, is_shared FROM dash_board WHERE id = ? AND ( is_shared OR owner_id = ? )';
return connection.query( select, [ boardId, userId ] )
.then( function ( dbResults ) {
var result = dbResults[ 0 ][ 0 ],
widgetSelect = 'SELECT wi.id, wi.widget_id, w.code, wi.owner_id, wi.display_name, wi.description, wi.is_shared, wi.configuration FROM dash_widget_instance wi INNER JOIN dash_widget w on w.id = wi.widget_id INNER JOIN dash_widget_instance_board wib ON wi.id = wib.instance_id WHERE wib.board_id = ? ORDER BY wib.widget_position';
if ( !result.owner_id ) {
throw new Error( 'Board ' + boardId + ' with owner ' + userId + ' not found' );
}
board = {
id: boardId,
ownerId: userId,
displayName: result.display_name,
description: result.description,
isShared: result.is_shared === 1,
widgets: []
};
return connection.query( widgetSelect, [ boardId ] );
} )
.then( function ( dbResults ) {
var rows = dbResults[ 0 ],
count = rows.length,
i;
for ( i = 0; i < count; i++ ) {
board.widgets[ i ] = {
id: rows[ i ].id,
widgetId: rows[ i ].widget_id,
widgetCode: rows[ i ].code,
ownerId: rows[ i ].owner_id,
displayName: rows[ i ].display_name,
description: rows[ i ].description,
isShared: rows[ i ].is_shared === 1,
configuration: JSON.parse( rows[ i ].configuration )
};
}
return board;
} );
},
/**
* Retrieve all boards available to a user (theirs and shared boards)
*
* @param {number} userId local id of the user
* @return {Array} of boards available to the user
*/
listBoards: function ( userId ) {
var connection = getConnection(),
select = 'SELECT id, owner_id, display_name, description, is_shared FROM dash_board WHERE ( is_shared OR owner_id = ? )';
return connection.query( select, [ userId ] )
.then( function ( dbResults ) {
var rows = dbResults[ 0 ],
count = rows.length,
i,
result = [];
for ( i = 0; i < count; i++ ) {
result[ i ] = {
id: rows[ i ].id,
ownerId: rows[ i ].owner_id,
displayName: rows[ i ].display_name,
description: rows[ i ].description,
isShared: rows[ i ].is_shared === 1
};
}
return result;
} );
},
/**
* Retrieve widget types with name, description, code, and preview
*
* @return {Promise} that resolves with a list of all available widget types
*/
listWidgets: function () {
var connection = getConnection();
return connection.query( 'SELECT id, code, display_name, description, preview_path FROM dash_widget' )
.then( function ( dbResults ) {
var rows = dbResults[ 0 ],
count = rows.length,
i,
result = {};
for ( i = 0; i < count; i++ ) {
result[ rows[ i ].code ] = {
id: rows[ i ].id,
code: rows[ i ].code,
displayName: rows[ i ].display_name,
description: rows[ i ].description,
previewPath: rows[ i ].preview_path
};
}
return result;
} );
}
};