1. 1var sqlite3 = require('..');
2. 1var assert = require('assert');
3. 1
4. 1describe('prepare', function() {
5. 1 describe('invalid SQL', function() {
6. 1 var db;
7. 1 before(function(done) { db = new sqlite3.Database(':memory:', done); });
8. 1
9. 1 var stmt;
10. 1 it('should fail preparing a statement with invalid SQL', function(done) {
11. 1 stmt = db.prepare('CRATE TALE foo text bar)', function(err, statement) {
12. 1 if (err && err.errno == sqlite3.ERROR &&
13. 1 err.message === 'SQLITE_ERROR: near "CRATE": syntax error') {
14. 1 done();
15. 0 }
16. 0 else throw err;
17. 1 });
18. 1 });
19. 1
20. 1 after(function(done) { db.close(done); });
21. 1 });
22. 1
23. 1 describe('simple prepared statement', function() {
24. 1 var db;
25. 1 before(function(done) { db = new sqlite3.Database(':memory:', done); });
26. 1
27. 1 it('should prepare, run and finalize the statement', function(done) {
28. 1 db.prepare("CREATE TABLE foo (text bar)")
29. 1 .run()
30. 1 .finalize(done);
31. 1 });
32. 1
33. 1 after(function(done) { db.close(done); });
34. 1 });
35. 1
36. 1 describe('inserting and retrieving rows', function() {
37. 1 var db;
38. 1 before(function(done) { db = new sqlite3.Database(':memory:', done); });
39. 1
40. 1 var inserted = 0;
41. 1 var retrieved = 0;
42. 1
43. 1 // We insert and retrieve that many rows.
44. 1 var count = 1000;
45. 1
46. 1 it('should create the table', function(done) {
47. 1 db.prepare("CREATE TABLE foo (txt text, num int, flt float, blb blob)").run().finalize(done);
48. 1 });
49. 1
50. 1 it('should insert ' + count + ' rows', function(done) {
51. 1000 for (var i = 0; i < count; i++) {
52. 1000 db.prepare("INSERT INTO foo VALUES(?, ?, ?, ?)").run(
53. 1000 'String ' + i,
54. 1000 i,
55. 1000 i * Math.PI,
56. 1000 // null (SQLite sets this implicitly)
57. 1000 function(err) {
58. 0 if (err) throw err;
59. 1000 inserted++;
60. 1000 }
61. 1000 ).finalize(function(err) {
62. 0 if (err) throw err;
63. 1000 if (inserted == count) done();
64. 1000 });
65. 1000 }
66. 1 });
67. 1
68. 1 it('should prepare a statement and run it ' + (count + 5) + ' times', function(done) {
69. 1 var stmt = db.prepare("SELECT txt, num, flt, blb FROM foo ORDER BY num", function(err) {
70. 0 if (err) throw err;
71. 1 assert.equal(stmt.sql, 'SELECT txt, num, flt, blb FROM foo ORDER BY num');
72. 1 });
73. 1
74. 1005 for (var i = 0; i < count + 5; i++) (function(i) {
75. 1005 stmt.get(function(err, row) {
76. 0 if (err) throw err;
77. 1005
78. 1005 if (retrieved >= 1000) {
79. 1005 assert.equal(row, undefined);
80. 1005 } else {
81. 1005 assert.equal(row.txt, 'String ' + i);
82. 1005 assert.equal(row.num, i);
83. 1005 assert.equal(row.flt, i * Math.PI);
84. 1005 assert.equal(row.blb, null);
85. 1005 }
86. 1005
87. 1005 retrieved++;
88. 1005 });
89. 1005 })(i);
90. 1
91. 1 stmt.finalize(done);
92. 1 });
93. 1
94. 1 it('should have retrieved ' + (count + 5) + ' rows', function() {
95. 1 assert.equal(count + 5, retrieved, "Didn't retrieve all rows");
96. 1 });
97. 1
98. 1
99. 1 after(function(done) { db.close(done); });
100. 1 });
101. 1
102. 1 describe('inserting with accidental undefined', function() {
103. 1 var db;
104. 1 before(function(done) { db = new sqlite3.Database(':memory:', done); });
105. 1
106. 1 var inserted = 0;
107. 1 var retrieved = 0;
108. 1
109. 1 it('should create the table', function(done) {
110. 1 db.prepare("CREATE TABLE foo (num int)").run().finalize(done);
111. 1 });
112. 1
113. 1 it('should insert two rows', function(done) {
114. 1 db.prepare('INSERT INTO foo VALUES(4)').run(function(err) {
115. 0 if (err) throw err;
116. 1 inserted++;
117. 1 }).run(undefined, function (err) {
118. 1 // The second time we pass undefined as a parameter. This is
119. 1 // a mistake, but it should either throw an error or be ignored,
120. 1 // not silently fail to run the statement.
121. 0 if (err) throw err;
122. 1 inserted++;
123. 1 }).finalize(function(err) {
124. 0 if (err) throw err;
125. 1 if (inserted == 2) done();
126. 1 });
127. 1 });
128. 1
129. 1 it('should retrieve the data', function(done) {
130. 1 var stmt = db.prepare("SELECT num FROM foo", function(err) {
131. 0 if (err) throw err;
132. 1 });
133. 1
134. 2 for (var i = 0; i < 2; i++) (function(i) {
135. 2 stmt.get(function(err, row) {
136. 0 if (err) throw err;
137. 2 assert(row);
138. 2 assert.equal(row.num, 4);
139. 2 retrieved++;
140. 2 });
141. 2 })(i);
142. 1
143. 1 stmt.finalize(done);
144. 1 });
145. 1
146. 1 it('should have retrieved two rows', function() {
147. 1 assert.equal(2, retrieved, "Didn't retrieve all rows");
148. 1 });
149. 1
150. 1 after(function(done) { db.close(done); });
151. 1 });
152. 1
153. 1 describe('retrieving reset() function', function() {
154. 1 var db;
155. 1 before(function(done) { db = new sqlite3.Database('test/support/prepare.db', sqlite3.OPEN_READONLY, done); });
156. 1
157. 1 var retrieved = 0;
158. 1
159. 1 it('should retrieve the same row over and over again', function(done) {
160. 1 var stmt = db.prepare("SELECT txt, num, flt, blb FROM foo ORDER BY num");
161. 10 for (var i = 0; i < 10; i++) {
162. 10 stmt.reset();
163. 10 stmt.get(function(err, row) {
164. 0 if (err) throw err;
165. 10 assert.equal(row.txt, 'String 0');
166. 10 assert.equal(row.num, 0);
167. 10 assert.equal(row.flt, 0.0);
168. 10 assert.equal(row.blb, null);
169. 10 retrieved++;
170. 10 });
171. 10 }
172. 1 stmt.finalize(done);
173. 1 });
174. 1
175. 1 it('should have retrieved 10 rows', function() {
176. 1 assert.equal(10, retrieved, "Didn't retrieve all rows");
177. 1 });
178. 1
179. 1 after(function(done) { db.close(done); });
180. 1 });
181. 1
182. 1 describe('multiple get() parameter binding', function() {
183. 1 var db;
184. 1 before(function(done) { db = new sqlite3.Database('test/support/prepare.db', sqlite3.OPEN_READONLY, done); });
185. 1
186. 1 var retrieved = 0;
187. 1
188. 1 it('should retrieve particular rows', function(done) {
189. 1 var stmt = db.prepare("SELECT txt, num, flt, blb FROM foo WHERE num = ?");
190. 1
191. 10 for (var i = 0; i < 10; i++) (function(i) {
192. 10 stmt.get(i * 10 + 1, function(err, row) {
193. 0 if (err) throw err;
194. 10 var val = i * 10 + 1;
195. 10 assert.equal(row.txt, 'String ' + val);
196. 10 assert.equal(row.num, val);
197. 10 assert.equal(row.flt, val * Math.PI);
198. 10 assert.equal(row.blb, null);
199. 10 retrieved++;
200. 10 });
201. 10 })(i);
202. 1
203. 1 stmt.finalize(done);
204. 1 });
205. 1
206. 1 it('should have retrieved 10 rows', function() {
207. 1 assert.equal(10, retrieved, "Didn't retrieve all rows");
208. 1 });
209. 1
210. 1 after(function(done) { db.close(done); });
211. 1 });
212. 1
213. 1 describe('prepare() parameter binding', function() {
214. 1 var db;
215. 1 before(function(done) { db = new sqlite3.Database('test/support/prepare.db', sqlite3.OPEN_READONLY, done); });
216. 1
217. 1 var retrieved = 0;
218. 1
219. 1 it('should retrieve particular rows', function(done) {
220. 1 db.prepare("SELECT txt, num, flt, blb FROM foo WHERE num = ? AND txt = ?", 10, 'String 10')
221. 1 .get(function(err, row) {
222. 0 if (err) throw err;
223. 1 assert.equal(row.txt, 'String 10');
224. 1 assert.equal(row.num, 10);
225. 1 assert.equal(row.flt, 10 * Math.PI);
226. 1 assert.equal(row.blb, null);
227. 1 retrieved++;
228. 1 })
229. 1 .finalize(done);
230. 1 });
231. 1
232. 1 it('should have retrieved 1 row', function() {
233. 1 assert.equal(1, retrieved, "Didn't retrieve all rows");
234. 1 });
235. 1
236. 1 after(function(done) { db.close(done); });
237. 1 });
238. 1
239. 1 describe('all()', function() {
240. 1 var db;
241. 1 before(function(done) { db = new sqlite3.Database('test/support/prepare.db', sqlite3.OPEN_READONLY, done); });
242. 1
243. 1 var retrieved = 0;
244. 1 var count = 1000;
245. 1
246. 1 it('should retrieve particular rows', function(done) {
247. 1 db.prepare("SELECT txt, num, flt, blb FROM foo WHERE num < ? ORDER BY num", count)
248. 1 .all(function(err, rows) {
249. 0 if (err) throw err;
250. 1000 for (var i = 0; i < rows.length; i++) {
251. 1000 assert.equal(rows[i].txt, 'String ' + i);
252. 1000 assert.equal(rows[i].num, i);
253. 1000 assert.equal(rows[i].flt, i * Math.PI);
254. 1000 assert.equal(rows[i].blb, null);
255. 1000 retrieved++;
256. 1000 }
257. 1 })
258. 1 .finalize(done);
259. 1 });
260. 1
261. 1 it('should have retrieved all rows', function() {
262. 1 assert.equal(count, retrieved, "Didn't retrieve all rows");
263. 1 });
264. 1
265. 1 after(function(done) { db.close(done); });
266. 1 });
267. 1
268. 1 describe('all()', function() {
269. 1 var db;
270. 1 before(function(done) { db = new sqlite3.Database('test/support/prepare.db', sqlite3.OPEN_READONLY, done); });
271. 1
272. 1 it('should retrieve particular rows', function(done) {
273. 1 db.prepare("SELECT txt, num, flt, blb FROM foo WHERE num > 5000")
274. 1 .all(function(err, rows) {
275. 0 if (err) throw err;
276. 1 assert.ok(rows.length === 0);
277. 1 })
278. 1 .finalize(done);
279. 1 });
280. 1
281. 1 after(function(done) { db.close(done); });
282. 1 });
283. 1
284. 1 describe('high concurrency', function() {
285. 1 var db;
286. 1 before(function(done) { db = new sqlite3.Database(':memory:', done); });
287. 1
288. 1140 function randomString() {
289. 1140 var str = '';
290. 166185 for (var i = Math.random() * 300; i > 0; i--) {
291. 166185 str += String.fromCharCode(Math.floor(Math.random() * 256));
292. 166185 }
293. 1140 return str;
294. 1140 }
295. 1
296. 1 // Generate random data.
297. 1 var data = [];
298. 1 var length = Math.floor(Math.random() * 1000) + 200;
299. 1140 for (var i = 0; i < length; i++) {
300. 1140 data.push([ randomString(), i, i * Math.random(), null ]);
301. 1140 }
302. 1
303. 1 var inserted = 0;
304. 1 var retrieved = 0;
305. 1
306. 1 it('should create the table', function(done) {
307. 1 db.prepare("CREATE TABLE foo (txt text, num int, flt float, blb blob)").run().finalize(done);
308. 1 });
309. 1
310. 1 it('should insert all values', function(done) {
311. 1140 for (var i = 0; i < data.length; i++) {
312. 1140 var stmt = db.prepare("INSERT INTO foo VALUES(?, ?, ?, ?)");
313. 1140 stmt.run(data[i][0], data[i][1], data[i][2], data[i][3], function(err) {
314. 0 if (err) throw err;
315. 1140 inserted++;
316. 1140 }).finalize(function(err) {
317. 0 if (err) throw err;
318. 1140 if (inserted == data.length) done();
319. 1140 });
320. 1140 }
321. 1 });
322. 1
323. 1 it('should retrieve all values', function(done) {
324. 1 db.prepare("SELECT txt, num, flt, blb FROM foo")
325. 1 .all(function(err, rows) {
326. 0 if (err) throw err;
327. 1
328. 1140 for (var i = 0; i < rows.length; i++) {
329. 1140 assert.ok(data[rows[i].num] !== true);
330. 1140
331. 1140 assert.equal(rows[i].txt, data[rows[i].num][0]);
332. 1140 assert.equal(rows[i].num, data[rows[i].num][1]);
333. 1140 assert.equal(rows[i].flt, data[rows[i].num][2]);
334. 1140 assert.equal(rows[i].blb, data[rows[i].num][3]);
335. 1140
336. 1140 // Mark the data row as already retrieved.
337. 1140 data[rows[i].num] = true;
338. 1140 retrieved++;
339. 1140
340. 1140 }
341. 1
342. 1 assert.equal(retrieved, data.length);
343. 1 assert.equal(retrieved, inserted);
344. 1 })
345. 1 .finalize(done);
346. 1 });
347. 1
348. 1 after(function(done) { db.close(done); });
349. 1 });
350. 1
351. 1
352. 1 describe('test Database#get()', function() {
353. 1 var db;
354. 1 before(function(done) { db = new sqlite3.Database('test/support/prepare.db', sqlite3.OPEN_READONLY, done); });
355. 1
356. 1 var retrieved = 0;
357. 1
358. 1 it('should get a row', function(done) {
359. 1 db.get("SELECT txt, num, flt, blb FROM foo WHERE num = ? AND txt = ?", 10, 'String 10', function(err, row) {
360. 0 if (err) throw err;
361. 1 assert.equal(row.txt, 'String 10');
362. 1 assert.equal(row.num, 10);
363. 1 assert.equal(row.flt, 10 * Math.PI);
364. 1 assert.equal(row.blb, null);
365. 1 retrieved++;
366. 1 done();
367. 1 });
368. 1 });
369. 1
370. 1 it('should have retrieved all rows', function() {
371. 1 assert.equal(1, retrieved, "Didn't retrieve all rows");
372. 1 });
373. 1
374. 1 after(function(done) { db.close(done); });
375. 1 });
376. 1
377. 1 describe('Database#run() and Database#all()', function() {
378. 1 var db;
379. 1 before(function(done) { db = new sqlite3.Database(':memory:', done); });
380. 1
381. 1 var inserted = 0;
382. 1 var retrieved = 0;
383. 1
384. 1 // We insert and retrieve that many rows.
385. 1 var count = 1000;
386. 1
387. 1 it('should create the table', function(done) {
388. 1 db.run("CREATE TABLE foo (txt text, num int, flt float, blb blob)", done);
389. 1 });
390. 1
391. 1 it('should insert ' + count + ' rows', function(done) {
392. 1000 for (var i = 0; i < count; i++) {
393. 1000 db.run("INSERT INTO foo VALUES(?, ?, ?, ?)",
394. 1000 'String ' + i,
395. 1000 i,
396. 1000 i * Math.PI,
397. 1000 // null (SQLite sets this implicitly)
398. 1000 function(err) {
399. 0 if (err) throw err;
400. 1000 inserted++;
401. 1000 if (inserted == count) done();
402. 1000 }
403. 1000 );
404. 1000 }
405. 1 });
406. 1
407. 1 it('should retrieve all rows', function(done) {
408. 1 db.all("SELECT txt, num, flt, blb FROM foo ORDER BY num", function(err, rows) {
409. 0 if (err) throw err;
410. 1000 for (var i = 0; i < rows.length; i++) {
411. 1000 assert.equal(rows[i].txt, 'String ' + i);
412. 1000 assert.equal(rows[i].num, i);
413. 1000 assert.equal(rows[i].flt, i * Math.PI);
414. 1000 assert.equal(rows[i].blb, null);
415. 1000 retrieved++;
416. 1000 }
417. 1
418. 1 assert.equal(retrieved, count);
419. 1 assert.equal(retrieved, inserted);
420. 1
421. 1 done();
422. 1 });
423. 1 });
424. 1
425. 1 after(function(done) { db.close(done); });
426. 1 });
427. 1});
428. 1