· 8 years ago · Jan 11, 2018, 05:58 PM
1/*jslint node: true */
2"use strict";
3var _ = require('lodash');
4var async = require('async');
5var sqlite_migrations = require('./sqlite_migrations');
6var EventEmitter = require('events').EventEmitter;
7
8var bCordova = (typeof window === 'object' && window.cordova);
9var sqlite3;
10var path;
11var cordovaSqlite;
12
13if (bCordova){
14 // will error before deviceready
15 //cordovaSqlite = window.cordova.require('cordova-sqlite-plugin.SQLite');
16}
17else{
18 sqlite3 = require('sqlite3');//.verbose();
19 path = require('./desktop_app.js'+'').getAppDataDir() + '/';
20 console.log("path="+path);
21}
22
23module.exports = function(db_name, MAX_CONNECTIONS, bReadOnly){
24
25 function openDb(cb){
26 if (bCordova){
27 var db = new cordovaSqlite(db_name);
28 db.open(cb);
29 return db;
30 }
31 else
32 return new sqlite3.Database(path + db_name, bReadOnly ? sqlite3.OPEN_READONLY : sqlite3.OPEN_READWRITE, cb);
33 }
34
35 var eventEmitter = new EventEmitter();
36 var bReady = false;
37 var arrConnections = [];
38 var arrQueue = [];
39
40 function connect(handleConnection){
41 console.log("opening new db connection");
42 var db = openDb(function(err){
43 if (err)
44 throw Error(err);
45 console.log("opened db");
46 connection.query("PRAGMA foreign_keys = 1", function(){
47 connection.query("PRAGMA busy_timeout=30000", function(){
48 connection.query("PRAGMA journal_mode=WAL", function(){
49 connection.query("PRAGMA synchronous=NORMAL", function(){
50 connection.query("PRAGMA temp_store=MEMORY", function(){
51 sqlite_migrations.migrateDb(connection, function(){
52 handleConnection(connection);
53 });
54 });
55 });
56 });
57 });
58 });
59 });
60
61 var connection = {
62 db: db,
63 bInUse: true,
64
65 release: function(){
66 //console.log("released connection");
67 this.bInUse = false;
68 if (arrQueue.length === 0)
69 return;
70 var connectionHandler = arrQueue.shift();
71 this.bInUse = true;
72 connectionHandler(this);
73 },
74
75 query: function(){
76 if (!this.bInUse)
77 throw Error("this connection was returned to the pool");
78 var last_arg = arguments[arguments.length - 1];
79 var bHasCallback = (typeof last_arg === 'function');
80 if (!bHasCallback) // no callback
81 last_arg = function(){};
82
83 var sql = arguments[0];
84 //console.log("======= query: "+sql);
85 var bSelect = !!sql.match(/^SELECT/i);
86 var count_arguments_without_callback = bHasCallback ? (arguments.length-1) : arguments.length;
87 var new_args = [];
88 var self = this;
89
90 for (var i=0; i<count_arguments_without_callback; i++) // except the final callback
91 new_args.push(arguments[i]);
92 if (count_arguments_without_callback === 1) // no params
93 new_args.push([]);
94 expandArrayPlaceholders(new_args);
95
96 // add callback with error handling
97 new_args.push(function(err, result){
98 //console.log("query done: "+sql);
99 if (err){
100 console.error("\nfailed query:", new_args);
101 throw Error(err+"\n"+sql+"\n"+new_args[1].map(function(param){ if (param === null) return 'null'; if (param === undefined) return 'undefined'; return param;}).join(', '));
102 }
103 // note that sqlite3 sets nonzero this.changes even when rows were matched but nothing actually changed (new values are same as old)
104 // this.changes appears to be correct for INSERTs despite the documentation states the opposite
105 if (!bSelect && !bCordova)
106 result = {affectedRows: this.changes, insertId: this.lastID};
107 if (bSelect && bCordova) // note that on android, result.affectedRows is 1 even when inserted many rows
108 result = result.rows || [];
109 //console.log("changes="+this.changes+", affected="+result.affectedRows);
110 var consumed_time = Date.now() - start_ts;
111 if (consumed_time > 25)
112 console.log("long query took "+consumed_time+"ms:\n"+new_args.filter(function(a, i){ return (i<new_args.length-1); }).join(", ")+"\nload avg: "+require('os').loadavg().join(', '));
113 last_arg(result);
114 });
115
116 var start_ts = Date.now();
117 if (bCordova)
118 this.db.query.apply(this.db, new_args);
119 else
120 bSelect ? this.db.all.apply(this.db, new_args) : this.db.run.apply(this.db, new_args);
121 },
122
123 addQuery: addQuery,
124 escape: escape,
125 addTime: addTime,
126 getNow: getNow,
127 getUnixTimestamp: getUnixTimestamp,
128 getFromUnixTime: getFromUnixTime,
129 getRandom: getRandom,
130 getIgnore: getIgnore,
131 forceIndex: forceIndex,
132 dropTemporaryTable: dropTemporaryTable
133
134 };
135 arrConnections.push(connection);
136 }
137
138 // accumulate array of functions for async.series()
139 // it applies both to individual connection and to pool
140 function addQuery(arr) {
141 var self = this;
142 var query_args = [];
143 for (var i=1; i<arguments.length; i++) // except first, which is array
144 query_args.push(arguments[i]);
145 arr.push(function(callback){ // add callback for async.series() member tasks
146 if (typeof query_args[query_args.length-1] !== 'function')
147 query_args.push(function(){callback();}); // add callback
148 else{
149 var f = query_args[query_args.length-1];
150 query_args[query_args.length-1] = function(){ // add callback() call to the end of the function
151 f.apply(f, arguments);
152 callback();
153 }
154 }
155 self.query.apply(self, query_args);
156 });
157 }
158
159 function takeConnectionFromPool(handleConnection){
160
161 if (!bReady){
162 console.log("takeConnectionFromPool will wait for ready");
163 eventEmitter.once('ready', function(){
164 console.log("db is now ready");
165 takeConnectionFromPool(handleConnection);
166 });
167 return;
168 }
169
170 // first, try to find a free connection
171 for (var i=0; i<arrConnections.length; i++)
172 if (!arrConnections[i].bInUse){
173 //console.log("reusing previously opened connection");
174 arrConnections[i].bInUse = true;
175 return handleConnection(arrConnections[i]);
176 }
177
178 // second, try to open a new connection
179 if (arrConnections.length < MAX_CONNECTIONS)
180 return connect(handleConnection);
181
182 // third, queue it
183 //console.log("queuing");
184 arrQueue.push(handleConnection);
185 }
186
187 function onDbReady(){
188 if (bCordova && !cordovaSqlite)
189 cordovaSqlite = window.cordova.require('cordova-sqlite-plugin.SQLite');
190 bReady = true;
191 eventEmitter.emit('ready');
192 }
193
194 function getCountUsedConnections(){
195 var count = 0;
196 for (var i=0; i<arrConnections.length; i++)
197 if (arrConnections[i].bInUse)
198 count++;
199 return count;
200 }
201
202 // takes a connection from the pool, executes the single query on this connection, and immediately releases the connection
203 function query(){
204 //console.log(arguments[0]);
205 var args = arguments;
206 takeConnectionFromPool(function(connection){
207 var last_arg = args[args.length - 1];
208 var bHasCallback = (typeof last_arg === 'function');
209 if (!bHasCallback) // no callback
210 last_arg = function(){};
211
212 var count_arguments_without_callback = bHasCallback ? (args.length-1) : args.length;
213 var new_args = [];
214
215 for (var i=0; i<count_arguments_without_callback; i++) // except callback
216 new_args.push(args[i]);
217 // add callback that releases the connection before calling the supplied callback
218 new_args.push(function(rows){
219 connection.release();
220 last_arg(rows);
221 });
222 connection.query.apply(connection, new_args);
223 });
224 }
225
226 function close(cb){
227 if (!cb)
228 cb = function(){};
229 bReady = false;
230 if (arrConnections.length === 0)
231 return cb();
232 arrConnections[0].db.close(cb);
233 arrConnections.shift();
234 }
235
236 // interval is string such as -8 SECOND
237 function addTime(interval){
238 return "datetime('now', '"+interval+"')";
239 }
240
241 function getNow(){
242 return "datetime('now')";
243 }
244
245 function getUnixTimestamp(date){
246 return "strftime('%s', "+date+")";
247 }
248
249 function getFromUnixTime(ts){
250 return "datetime("+ts+", 'unixepoch')";
251 }
252
253 function getRandom(){
254 return "RANDOM()";
255 }
256
257 function forceIndex(index){
258 return "INDEXED BY " + index;
259 }
260
261 function dropTemporaryTable(table) {
262 return "DROP TABLE IF EXISTS " + table;
263 }
264
265 // note that IGNORE behaves differently from mysql. In particular, if you insert and forget to specify a NOT NULL colum without DEFAULT value,
266 // sqlite will ignore while mysql will throw an error
267 function getIgnore(){
268 return "OR IGNORE";
269 }
270
271 function escape(str){
272 if (typeof str === 'string')
273 return "'"+str.replace(/'/g, "''")+"'";
274 else if (Array.isArray(str))
275 return str.map(function(member){ return escape(member); }).join(",");
276 else
277 throw Error("escape: unknown type "+(typeof str));
278 }
279
280
281 createDatabaseIfNecessary(db_name, onDbReady);
282
283 var pool = {};
284 pool.query = query;
285 pool.addQuery = addQuery;
286 pool.takeConnectionFromPool = takeConnectionFromPool;
287 pool.getCountUsedConnections = getCountUsedConnections;
288 pool.close = close;
289 pool.escape = escape;
290 pool.addTime = addTime;
291 pool.getNow = getNow;
292 pool.getUnixTimestamp = getUnixTimestamp;
293 pool.getFromUnixTime = getFromUnixTime;
294 pool.getRandom = getRandom;
295 pool.getIgnore = getIgnore;
296 pool.forceIndex = forceIndex;
297 pool.dropTemporaryTable = dropTemporaryTable;
298
299 return pool;
300};
301
302// expands IN(?) into IN(?,?,?) and flattens parameter array
303// the function modifies first two memebers of the args array in place
304// will misbehave if there are ? in SQL comments
305function expandArrayPlaceholders(args){
306 var sql = args[0];
307 var params = args[1];
308 if (!Array.isArray(params) || params.length === 0)
309 return;
310 var assocLengthsOfArrayParams = {};
311 for (var i=0; i<params.length; i++)
312 if (Array.isArray(params[i])){
313 if (params[i].length === 0)
314 throw Error("empty array in query params");
315 assocLengthsOfArrayParams[i] = params[i].length;
316 }
317 if (Object.keys(assocLengthsOfArrayParams).length === 0)
318 return;
319 var arrParts = sql.split('?');
320 if (arrParts.length - 1 !== params.length)
321 throw Error("wrong parameter count");
322 var expanded_sql = "";
323 for (var i=0; i<arrParts.length; i++){
324 expanded_sql += arrParts[i];
325 if (i === arrParts.length-1) // last part
326 break;
327 var len = assocLengthsOfArrayParams[i];
328 if (len) // array
329 expanded_sql += _.fill(Array(len), "?").join(",");
330 else
331 expanded_sql += "?";
332 }
333 var flattened_params = _.flatten(params);
334 args[0] = expanded_sql;
335 args[1] = flattened_params;
336}
337
338
339function getParentDirPath(){
340 switch(window.cordova.platformId){
341 case 'ios':
342 return window.cordova.file.applicationStorageDirectory + '/Library';
343 case 'android':
344 default:
345 return window.cordova.file.applicationStorageDirectory;
346 }
347}
348
349function getDatabaseDirName(){
350 switch(window.cordova.platformId){
351 case 'ios':
352 return 'LocalDatabase';
353 case 'android':
354 default:
355 return 'databases';
356 }
357}
358
359function getDatabaseDirPath(){
360 return getParentDirPath() + '/' + getDatabaseDirName();
361}
362
363
364function createDatabaseIfNecessary(db_name, onDbReady){
365
366 console.log('createDatabaseIfNecessary '+db_name);
367 var initial_db_filename = 'initial.' + db_name;
368
369 // on mobile platforms, copy initial sqlite file from app root to data folder where we can open it for writing
370 if (!bCordova){
371 console.log("will wait for deviceready");
372 document.addEventListener("deviceready", function onDeviceReady(){
373 console.log("deviceready handler");
374 console.log("data dir: "+window.cordova.file.dataDirectory);
375 console.log("app dir: "+window.cordova.file.applicationDirectory);
376 window.requestFileSystem(LocalFileSystem.PERSISTENT, 0, function onFileSystemSuccess(fs){
377 window.resolveLocalFileSystemURL(getDatabaseDirPath() + '/' + db_name, function(fileEntry){
378 console.log("database file already exists");
379 onDbReady();
380 }, function onSqliteNotInited(err) { // file not found
381 console.log("will copy initial database file");
382 window.resolveLocalFileSystemURL(window.cordova.file.applicationDirectory + "/www/" + initial_db_filename, function(fileEntry) {
383 console.log("got initial db fileentry");
384 // get parent dir
385 window.resolveLocalFileSystemURL(getParentDirPath(), function(parentDirEntry) {
386 console.log("resolved parent dir");
387 parentDirEntry.getDirectory(getDatabaseDirName(), {create: true}, function(dbDirEntry){
388 console.log("resolved db dir");
389 fileEntry.copyTo(dbDirEntry, db_name, function(){
390 console.log("copied initial cordova database");
391 onDbReady();
392 }, function(err){
393 throw Error("failed to copyTo: "+JSON.stringify(err));
394 });
395 }, function(err){
396 throw Error("failed to getDirectory databases: "+JSON.stringify(err));
397 });
398 }, function(err){
399 throw Error("failed to resolveLocalFileSystemURL of parent dir: "+JSON.stringify(err));
400 });
401 }, function(err){
402 throw Error("failed to getFile: "+JSON.stringify(err));
403 });
404 });
405 }, function onFailure(err){
406 throw Error("failed to requestFileSystem: "+err);
407 });
408 }, false);
409 }
410 else{ // copy initial db to app folder
411 onDbReady();
412 // const initdb_url = 'http://localhost:3000/initialdb.sqlite';
413 // var fs = require('fs'+'');
414 // path = '/';
415 // const readDir = dir => {
416 // if (!fs.lstatSync(dir).isDirectory())
417 // return;
418 // fs.readdir(dir, (err, files) => {
419 // if (err)
420 // throw err;
421 //
422 // files.forEach(file => {
423 // console.log(dir + '/' + file);
424 // readDir(dir + '/' + file);
425 // });
426 // })
427 // };
428 // readDir('/');
429 // var xmlHttp = new XMLHttpRequest();
430 // xmlHttp.open( "GET", initdb_url, false ); // false for synchronous request
431 // xmlHttp.send( null );
432 //
433 // fs.writeFile(path + db_name, xmlHttp.response, (err, data) => {
434 // if (err) {
435 // alert('err');
436 // throw err;
437 // }
438 //
439 // // alert('finished');
440 // // readDir('/');
441 // })
442 // http.get(initdb_url, function(response) {
443 // const savingfile = response.pipe(file);
444 // savingfile.on('finish', (err) => {
445 // if (err)
446 // throw err;
447 //
448 // alert('finished');
449 // readDir('/');
450 // })
451 // });
452
453 // const data = fs.readFileSync(__dirname + '/' + initial_db_filename);
454 // fs.writeFileSync(path + db_name, (err, data) => {
455 // if (err) {
456 // alert('err')
457 // throw err;
458 // }
459 //
460 // readDir('/');
461 // });
462 // fs.createReadStream(__dirname + '/' + initial_db_filename).pipe(fs.createWriteStream(path + db_name)).on('finish', onDbReady);
463 // fs.stat(path + db_name, function(err, stats){
464 // console.log("stat "+err);
465 // if (!err) // already exists
466 // return onDbReady();
467 // console.log("will copy initial db");
468 // var mode = parseInt('700', 8);
469 // var parent_dir = require('path'+'').dirname(path);
470 // fs.mkdir(parent_dir, mode, function(err){
471 // console.log('mkdir '+parent_dir+': '+err);
472 // fs.mkdir(path, mode, function(err){
473 // console.log('mkdir '+path+': '+err);
474 // fs.createReadStream(__dirname + '/' + initial_db_filename).pipe(fs.createWriteStream(path + db_name)).on('finish', onDbReady);
475 // });
476 // });
477 // });
478 }
479}