1. 1// MIT License
2. 1//
3. 1// Copyright (c) 2021 Kai Zhu
4. 1//
5. 1// Permission is hereby granted, free of charge, to any person obtaining a copy
6. 1// of this software and associated documentation files (the "Software"), to deal
7. 1// in the Software without restriction, including without limitation the rights
8. 1// to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
9. 1// copies of the Software, and to permit persons to whom the Software is
10. 1// furnished to do so, subject to the following conditions:
11. 1//
12. 1// The above copyright notice and this permission notice shall be included in
13. 1// all copies or substantial portions of the Software.
14. 1//
15. 1// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
16. 1// IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
17. 1// FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
18. 1// AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
19. 1// LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
20. 1// OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
21. 1// SOFTWARE.
22. 1
23. 1/*
24. 1 * example usage:
25. 1npm_config_mode_test_save2=1 npm test
26. 1 */
27. 1
28. 1/*jslint beta, node*/
29. 1import jslint from "./jslint.mjs";
30. 1import {
31. 1 LGBM_PREDICT_NORMAL,
32. 1 assertErrorThrownAsync,
33. 1 assertJsonEqual,
34. 1 assertNumericalEqual,
35. 1 assertOrThrow,
36. 1 childProcessSpawn2,
37. 1 ciBuildExt,
38. 1 dbCloseAsync,
39. 1 dbExecAndReturnLastBlob,
40. 1 dbExecAndReturnLastRow,
41. 1 dbExecAndReturnLastTable,
42. 1 dbExecAndReturnLastValue,
43. 1 dbExecAsync,
44. 1 dbFileLoadAsync,
45. 1 dbFileSaveAsync,
46. 1 dbNoopAsync,
47. 1 dbOpenAsync,
48. 1 dbTableImportAsync,
49. 1 debugInline,
50. 1 fsCopyFileUnlessTest,
51. 1 fsExistsUnlessTest,
52. 1 fsReadFileUnlessTest,
53. 1 fsWriteFileUnlessTest,
54. 1 jsbatonGetInt64,
55. 1 jsbatonGetString,
56. 1 listOrEmptyList,
57. 1 noop,
58. 1 sqlmathWebworkerInit,
59. 1 version,
60. 1 waitAsync
61. 1} from "./sqlmath.mjs";
62. 1let {
63. 1 jstestDescribe,
64. 1 jstestIt
65. 1} = jslint;
66. 1let {
67. 1 npm_config_mode_test_save
68. 1} = typeof process === "object" && process?.env;
69. 1noop(debugInline);
70. 1
71. 1jstestDescribe((
72. 1 "test_apidoc"
73. 1), function test_apidoc() {
74. 1 jstestIt((
75. 1 "test apidoc handling-behavior"
76. 1 ), function () {
77. 1 jslint.jslint_apidoc({
78. 1 example_list: [
79. 1 "README.md",
80. 1 "test.mjs",
81. 1 "sqlmath.mjs"
82. 1 ],
83. 1 github_repo: "https://github.com/sqlmath/sqlmath",
84. 1 module_list: [
85. 1 {
86. 1 pathname: "./sqlmath.mjs"
87. 1 }
88. 1 ],
89. 1 package_name: "sqlmath",
90. 1 pathname: ".artifact/apidoc.html",
91. 1 version
92. 1 });
93. 1 });
94. 1});
95. 1
96. 1jstestDescribe((
97. 1 "test_assertXxx"
98. 1), function test_assertXxx() {
99. 1 jstestIt((
100. 1 "test assertXxx handling-behavior"
101. 1 ), function () {
102. 1 assertErrorThrownAsync(function () {
103. 1 assertNumericalEqual(0, 0);
104. 1 }, "value cannot be 0 or falsy");
105. 1 assertErrorThrownAsync(function () {
106. 1 assertNumericalEqual(1, 2);
107. 1 }, "1 != 2");
108. 1 assertErrorThrownAsync(function () {
109. 1 assertNumericalEqual(1, 2, "aa");
110. 1 }, "aa");
111. 1 assertNumericalEqual(1, 1);
112. 1 });
113. 1});
114. 1
115. 1jstestDescribe((
116. 1 "test_childProcessSpawn2"
117. 1), function test_childProcessSpawn2() {
118. 1 jstestIt((
119. 1 "test childProcessSpawn2 handling-behavior"
120. 1 ), async function () {
121. 1 await Promise.all([
122. 1 childProcessSpawn2(
123. 1 "aa",
124. 1 [],
125. 1 {modeCapture: "utf8", modeDebug: true, stdio: []}
126. 1 )
127. 1 ]);
128. 1 });
129. 1});
130. 1
131. 1jstestDescribe((
132. 1 "test_ciBuildExtXxx"
133. 1), function test_ciBuildExtXxx() {
134. 1 jstestIt((
135. 1 "test ciBuildExt handling-behavior"
136. 1 ), async function () {
137. 1 await Promise.all([
138. 1 ciBuildExt({modeSkip: true, process: {}}),
139. 1 ciBuildExt({process: {arch: "arm", env: {}, platform: "win32"}}),
140. 1 ciBuildExt({process: {arch: "arm64", env: {}, platform: "win32"}}),
141. 1 ciBuildExt({process: {arch: "ia32", env: {}, platform: "win32"}}),
142. 1 ciBuildExt({process: {env: {}, platform: "darwin"}}),
143. 1 ciBuildExt({process: {env: {}, platform: "win32"}}),
144. 1 ciBuildExt({process: {}})
145. 1 ]);
146. 1 });
147. 1});
148. 1
149. 1jstestDescribe((
150. 1 "test_dbBind"
151. 1), function test_dbBind() {
152. 1 jstestIt((
153. 1 "test db-bind handling-behavior"
154. 1 ), async function test_dbBind() {
155. 1 let db;
156. 56 async function test_dbBind_exec(ii, valIn, valExpect) {
157. 56 await Promise.all([
158. 56 [
159. 56 [
160. 56 valExpect, valExpect, 0
161. 56 ],
162. 56 (
163. 56 "SELECT 0;"
164. 56 + " SELECT ? AS c1, ? AS c2, ? AS c3, ? AS c4"
165. 56 + " UNION ALL SELECT ?1, ?2, ?3, ?4"
166. 56 + " UNION ALL SELECT ?1, ?2, ?3, ?4"
167. 56 )
168. 56 ],
169. 56 [
170. 56 {
171. 56 k1: valExpect,
172. 56 k2: valExpect,
173. 56 k3: 0
174. 56 },
175. 56 (
176. 56 "SELECT 0;"
177. 56 + " SELECT $k1 AS c1, $k2 AS c2, $k3 AS c3, $k4 AS c4"
178. 56 + " UNION ALL SELECT :k1, :k2, :k3, :k4"
179. 56 + " UNION ALL SELECT @k1, @k2, @k3, @k4"
180. 56 )
181. 56 ]
182. 112 ].map(async function ([
183. 112 bindList, sql
184. 112 ]) {
185. 112 let bufActual = await dbExecAsync({
186. 112 bindList,
187. 112 db,
188. 112 responseType: "list",
189. 112 sql
190. 112 });
191. 112 let bufExpect = [
192. 112 [
193. 112 ["0"],
194. 112 [0]
195. 112 ],
196. 112 [
197. 112 ["c1", "c2", "c3", "c4"],
198. 112 [valExpect, valExpect, 0, undefined],
199. 112 [valExpect, valExpect, 0, undefined],
200. 112 [valExpect, valExpect, 0, undefined]
201. 112 ]
202. 112 ];
203. 112 assertJsonEqual(bufActual, bufExpect, {
204. 112 bufActual,
205. 112 bufExpect,
206. 112 ii,
207. 112 valExpect,
208. 112 valIn
209. 112 });
210. 112 }));
211. 56 }
212. 56 async function test_dbBind_lastBlob(ii, valIn, valExpect) {
213. 56 let bufActual;
214. 56 let bufExpect;
215. 2 if (valExpect === Error) {
216. 2 assertErrorThrownAsync(
217. 2 dbExecAndReturnLastBlob.bind(undefined, {
218. 2 bindList: [valIn],
219. 2 db,
220. 2 sql: "SELECT 1, 2, 3; SELECT 1, 2, ?"
221. 2 }),
222. 2 "inclusive-range|not JSON serializable"
223. 2 );
224. 2 return;
225. 54 }
226. 54 bufActual = new TextDecoder().decode(
227. 54 await dbExecAndReturnLastBlob({
228. 54 bindList: [valIn],
229. 54 db,
230. 54 sql: "SELECT 1, 2, 3; SELECT 1, 2, ?"
231. 54 })
232. 54 );
233. 54 bufExpect = String(valExpect);
234. 54 switch (typeof(valIn)) {
235. 54 case "bigint":
236. 10 valIn = Number(valIn);
237. 10 break;
238. 1 case "function":
239. 2 case "symbol":
240. 3 case "undefined":
241. 3 bufExpect = "";
242. 3 break;
243. 18 case "number":
244. 18 switch (valIn) {
245. 18 case -2:
246. 18 bufExpect = "-2.0";
247. 18 break;
248. 18 case -Infinity:
249. 18 bufExpect = "-Inf";
250. 18 break;
251. 18 case 2:
252. 18 bufExpect = "2.0";
253. 18 break;
254. 18 case Infinity:
255. 18 bufExpect = "Inf";
256. 18 break;
257. 18 default:
258. 18 if (Number.isNaN(valIn)) {
259. 18 bufExpect = "";
260. 18 }
261. 18 }
262. 18 break;
263. 12 case "object":
264. 12 if (valIn === null) {
265. 12 bufExpect = "";
266. 12 break;
267. 12 }
268. 12 if (
269. 12 valIn?.constructor === ArrayBuffer
270. 12 || ArrayBuffer.isView(valIn)
271. 12 ) {
272. 12 bufExpect = new TextDecoder().decode(valIn);
273. 12 break;
274. 12 }
275. 12 break;
276. 54 }
277. 54 assertJsonEqual(bufActual, bufExpect, {
278. 54 bufActual,
279. 54 bufExpect,
280. 54 ii,
281. 54 valExpect,
282. 54 valIn
283. 54 });
284. 54 }
285. 56 async function test_dbBind_lastValue(ii, valIn, valExpect) {
286. 56 let valActual;
287. 2 if (valExpect === Error) {
288. 2 assertErrorThrownAsync(
289. 2 dbExecAndReturnLastValue.bind(undefined, {
290. 2 bindList: [valIn],
291. 2 db,
292. 2 sql: "SELECT 1, 2, 3; SELECT 1, 2, ?"
293. 2 }),
294. 2 "inclusive-range|not JSON serializable"
295. 2 );
296. 2 return;
297. 54 }
298. 54 valActual = await dbExecAndReturnLastValue({
299. 54 bindList: [valIn],
300. 54 db,
301. 54 sql: "SELECT 1, 2, 3; SELECT 1, 2, ?"
302. 54 });
303. 54 assertJsonEqual(valActual, valExpect, {
304. 54 ii,
305. 54 valActual,
306. 54 valExpect,
307. 54 valIn
308. 54 });
309. 54 }
310. 56 async function test_dbBind_responseType(ii, valIn, valExpect) {
311. 56 await Promise.all([
312. 56 "arraybuffer",
313. 56 "list",
314. 56 "lastvalue",
315. 56 undefined
316. 224 ].map(async function (responseType) {
317. 224 let valActual;
318. 8 if (valExpect === Error) {
319. 8 assertErrorThrownAsync(
320. 8 dbExecAsync.bind(undefined, {
321. 8 bindList: [valIn],
322. 8 db,
323. 8 responseType,
324. 8 sql: "SELECT ? AS val"
325. 8 }),
326. 8 "inclusive-range|not JSON serializable"
327. 8 );
328. 8 return;
329. 216 }
330. 216 valActual = await dbExecAsync({
331. 216 bindList: [valIn],
332. 216 db,
333. 216 responseType,
334. 216 sql: "SELECT ? AS val"
335. 216 });
336. 216 switch (responseType) {
337. 216 case "arraybuffer":
338. 54 valActual = JSON.parse(
339. 54 new TextDecoder().decode(valActual)
340. 54 )[0][1][0];
341. 54 break;
342. 54 case "lastvalue":
343. 54 break;
344. 54 case "list":
345. 54 valActual = valActual[0][1][0];
346. 54 break;
347. 54 default:
348. 54 valActual = valActual[0][0].val;
349. 216 }
350. 216 assertJsonEqual(valActual, valExpect, {
351. 216 ii,
352. 216 responseType,
353. 216 valActual,
354. 216 valExpect,
355. 216 valIn
356. 216 });
357. 216 }));
358. 56 }
359. 1 db = await dbOpenAsync({filename: ":memory:"});
360. 1 await Promise.all([
361. 1 // 1. bigint
362. 1 [-0n, -0],
363. 1 [-0n, 0],
364. 1 [-0x8000000000000000n, "-9223372036854775808"],
365. 1 [-0x8000000000000001n, Error],
366. 1 [-1n, -1],
367. 1 [-2n, -2],
368. 1 [0n, -0],
369. 1 [0n, 0],
370. 1 [0x7fffffffffffffffn, "9223372036854775807"],
371. 1 [0x8000000000000000n, Error],
372. 1 [1n, 1],
373. 1 [2n, 2],
374. 1 // 2. boolean
375. 1 [false, 0],
376. 1 [true, 1],
377. 1 // 3. function
378. 1 [noop, null],
379. 1 // 4. number
380. 1 [-0, -0],
381. 1 [-0, 0],
382. 1 [-0.5, -0.5],
383. 1 [-1 / 0, null],
384. 1 [-1e-999, 0],
385. 1 [-1e999, null],
386. 1 [-2, -2],
387. 1 [-Infinity, null],
388. 1 [-NaN, null],
389. 1 [0, -0],
390. 1 [0, 0],
391. 1 [0.5, 0.5],
392. 1 [1 / 0, null],
393. 1 [1e-999, 0],
394. 1 [1e999, null],
395. 1 [2, 2],
396. 1 [Infinity, null],
397. 1 [NaN, null],
398. 1 // 5. object
399. 1 [[], "[]"],
400. 1 [new ArrayBuffer(0), null],
401. 1 [new ArrayBuffer(1), null],
402. 1 [new Date(0), "1970-01-01T00:00:00.000Z"],
403. 1 [new RegExp(), "{}"],
404. 1 [new TextEncoder().encode(""), null],
405. 1 [new TextEncoder().encode("\u0000"), null],
406. 1 [new TextEncoder().encode("\u0000\u{1f600}\u0000"), null],
407. 1 [new Uint8Array(0), null],
408. 1 [new Uint8Array(1), null],
409. 1 [null, null],
410. 1 [{}, "{}"],
411. 1 // 6. string
412. 1 ["", ""],
413. 1 ["'", "'"],
414. 1 ["0", "0"],
415. 1 ["1", "1"],
416. 1 ["2", "2"],
417. 1 ["\"", "\""],
418. 1 ["\u0000", "\u0000"],
419. 1 ["\u0000\u{1f600}\u0000", "\u0000\u{1f600}\u0000"],
420. 1 ["a".repeat(9999), "a".repeat(9999)],
421. 1 // 7. symbol
422. 1 [Symbol(), null],
423. 1 // 8. undefined
424. 1 [undefined, null]
425. 56 ].map(async function ([valIn, valExpect], ii) {
426. 56 await Promise.all([
427. 56 test_dbBind_exec(ii, valIn, valExpect),
428. 56 test_dbBind_lastBlob(ii, valIn, valExpect),
429. 56 test_dbBind_lastValue(ii, valIn, valExpect),
430. 56 test_dbBind_responseType(ii, valIn, valExpect)
431. 56 ]);
432. 56 }));
433. 1 });
434. 1});
435. 1
436. 1jstestDescribe((
437. 1 "test_dbNoopAsync"
438. 1), function test_dbNoopAsync() {
439. 1 jstestIt((
440. 1 "test dbNoopAsync handling-behavior"
441. 1 ), async function () {
442. 1 // test datatype handling-behavior
443. 1 await Promise.all([
444. 1 // 1. bigint
445. 1 [-0n, -0],
446. 1 [-0n, 0],
447. 1 [-0x8000000000000000n, -0x8000000000000000n],
448. 1 [-0x8000000000000001n, Error],
449. 1 [-1n, -1],
450. 1 [-2n, -2],
451. 1 [0n, -0],
452. 1 [0n, 0],
453. 1 [0x7fffffffffffffffn, 0x7fffffffffffffffn],
454. 1 [0x8000000000000000n, Error],
455. 1 [1n, 1],
456. 1 [2n, 2],
457. 1 // 2. boolean
458. 1 [false, 0],
459. 1 [true, 1],
460. 1 // 3. function
461. 1 [noop, Error],
462. 1 // 4. number
463. 1 [-0, -0],
464. 1 [-0, 0],
465. 1 [-0.5, Error],
466. 1 [-1 / 0, Error],
467. 1 [-1e-999, 0],
468. 1 [-1e999, Error],
469. 1 [-2, -2],
470. 1 [-Infinity, Error],
471. 1 [-NaN, Error],
472. 1 [0, -0],
473. 1 [0, 0],
474. 1 [0.5, Error],
475. 1 [1 / 0, Error],
476. 1 [1e-999, 0],
477. 1 [1e999, Error],
478. 1 [2, 2],
479. 1 [Infinity, Error],
480. 1 [NaN, Error],
481. 1 // 5. object
482. 1 [[], Error],
483. 1 [new ArrayBuffer(0), 0],
484. 1 [new ArrayBuffer(1), 0],
485. 1 [new Date(0), Error],
486. 1 [new RegExp(), Error],
487. 1 [new TextEncoder().encode(""), Error],
488. 1 [new TextEncoder().encode("\u0000"), Error],
489. 1 [new TextEncoder().encode("\u0000\u{1f600}\u0000"), Error],
490. 1 [new Uint8Array(0), Error],
491. 1 [new Uint8Array(1), Error],
492. 1 [null, 0],
493. 1 [{}, Error],
494. 1 // 6. string
495. 1 ["", ""],
496. 1 ["'", "'"],
497. 1 ["0", "0"],
498. 1 ["1", "1"],
499. 1 ["2", "2"],
500. 1 ["\"", "\""],
501. 1 ["\u0000", ""],
502. 1 ["\u0000\u{1f600}\u0000", "\u0000\u{1f600}"],
503. 1 ["a".repeat(9999), "a".repeat(9999)],
504. 1 // 7. symbol
505. 1 [Symbol(), Error],
506. 1 // 8. undefined
507. 1 [undefined, 0]
508. 56 ].map(async function ([valIn, valExpect], ii) {
509. 56 let baton;
510. 56 let valActual;
511. 23 if (valExpect === Error) {
512. 23 assertErrorThrownAsync(function () {
513. 23 return dbNoopAsync(undefined, valIn, undefined);
514. 23 }, "invalid arg|integer");
515. 23 return;
516. 33 }
517. 33 baton = await dbNoopAsync(undefined, valIn, undefined);
518. 33 baton = baton[0];
519. 33 valActual = (
520. 33 typeof valIn === "string"
521. 33 ? jsbatonGetString(baton, 1)
522. 24 : String(jsbatonGetInt64(baton, 1))
523. 56 );
524. 56 valExpect = String(valExpect);
525. 10 if (typeof valIn === "bigint") {
526. 10 valIn = String(valIn);
527. 33 }
528. 33 assertJsonEqual(valActual, valExpect, {
529. 33 ii,
530. 33 valActual,
531. 33 valExpect,
532. 33 valIn
533. 33 });
534. 33 }));
535. 1 });
536. 1});
537. 1
538. 1jstestDescribe((
539. 1 "test_dbXxxAsync"
540. 1), function test_dbXxxAsync() {
541. 1 jstestIt((
542. 1 "test dbCloseAsync handling-behavior"
543. 1 ), async function test_dbCloseAsync() {
544. 1 let db = await dbOpenAsync({
545. 1 filename: ":memory:"
546. 1 });
547. 1 // test null-case handling-behavior
548. 1 assertErrorThrownAsync(function () {
549. 1 return dbCloseAsync({});
550. 1 }, "cannot close db");
551. 1 // test close handling-behavior
552. 1 dbCloseAsync(db);
553. 1 });
554. 1 jstestIt((
555. 1 "test dbExecAndReturnXxx handling-behavior"
556. 1 ), async function test_dbExecAndReturnXxx() {
557. 1 let db = await dbOpenAsync({
558. 1 filename: ":memory:"
559. 1 });
560. 1 // test dbExecAndReturnLastRow null-case handling-behavior
561. 1 assertJsonEqual(
562. 1 noop(
563. 1 await dbExecAndReturnLastRow({
564. 1 db,
565. 1 sql: "SELECT 0 WHERE 0"
566. 1 })
567. 1 ),
568. 1 {}
569. 1 );
570. 1 // test dbExecAndReturnLastTable null-case handling-behavior
571. 1 assertJsonEqual(
572. 1 noop(
573. 1 await dbExecAndReturnLastTable({
574. 1 db,
575. 1 sql: "SELECT 0 WHERE 0"
576. 1 })
577. 1 ),
578. 1 []
579. 1 );
580. 1 // test dbExecAndReturnLastBlob null-case handling-behavior
581. 1 assertJsonEqual(
582. 1 new TextDecoder().decode(
583. 1 await dbExecAndReturnLastBlob({
584. 1 db,
585. 1 sql: "SELECT 0 WHERE 0"
586. 1 })
587. 1 ),
588. 1 ""
589. 1 );
590. 1 // test dbExecAndReturnLastBlob string handling-behavior
591. 1 assertJsonEqual(
592. 1 new TextDecoder().decode(
593. 1 await dbExecAndReturnLastBlob({
594. 1 db,
595. 1 sql: "SELECT 1, 2, 3"
596. 1 })
597. 1 ),
598. 1 "3"
599. 1 );
600. 1 // test dbExecAndReturnLastValue null-case handling-behavior
601. 1 assertJsonEqual(
602. 1 noop(
603. 1 await dbExecAndReturnLastValue({
604. 1 db,
605. 1 sql: "SELECT 0 WHERE 0"
606. 1 })
607. 1 ),
608. 1 null
609. 1 );
610. 1 // test dbExecAndReturnLastValue json handling-behavior
611. 1 assertJsonEqual(
612. 1 noop(
613. 1 await dbExecAndReturnLastValue({
614. 1 db,
615. 1 sql: "SELECT 1, 2, 3"
616. 1 })
617. 1 ),
618. 1 3
619. 1 );
620. 1 });
621. 1 jstestIt((
622. 1 "test dbExecAsync handling-behavior"
623. 1 ), async function test_dbExecAsync() {
624. 1 let db = await dbOpenAsync({
625. 1 filename: ":memory:"
626. 1 });
627. 1 // test null-case handling-behavior
628. 1 assertErrorThrownAsync(function () {
629. 1 return dbExecAsync({
630. 1 db,
631. 1 sql: undefined
632. 1 });
633. 1 }, "syntax error");
634. 1 // test race-condition handling-behavior
635. 4 Array.from(new Array(4)).forEach(async function () {
636. 4 let result;
637. 4 try {
638. 4 result = await dbExecAsync({
639. 4 bindList: [
640. 4 new TextEncoder().encode("foob"),
641. 4 new TextEncoder().encode("fooba"),
642. 4 new TextEncoder().encode("foobar")
643. 4 ],
644. 4 db,
645. 4 responseType: "list",
646. 4 sql: (`
647. 4CREATE TABLE testDbExecAsync1 AS
648. 4SELECT 101 AS c101, 102 AS c102
649. 4--
650. 4UNION ALL
651. 4VALUES
652. 4 (201, 202),
653. 4 (301, NULL);
654. 4CREATE TABLE testDbExecAsync2 AS
655. 4SELECT 401 AS c401, 402 AS c402, 403 AS c403
656. 4--
657. 4UNION ALL
658. 4VALUES
659. 4 (501, 502.0123, 5030123456789),
660. 4 (601, '602', '603_\"\x01\x08\x09\x0a\x0b\x0c\x0d\x0e'),
661. 4 (?1, ?2, ?3),
662. 4 (CAST(?1 AS TEXT), CAST(?2 AS TEXT), CAST(?3 AS TEXT)),
663. 4 (
664. 4 CAST(zlib_uncompress(zlib_compress(?1)) AS TEXT),
665. 4 CAST(zlib_uncompress(zlib_compress(?2)) AS TEXT),
666. 4 CAST(zlib_uncompress(zlib_compress(?3)) AS TEXT)
667. 4 );
668. 4SELECT * FROM testDbExecAsync1;
669. 4SELECT * FROM testDbExecAsync2;
670. 4 `)
671. 1 });
672. 1 assertJsonEqual(
673. 1 result,
674. 1 [
675. 1 [
676. 1 ["c101", "c102"],
677. 1 [101, 102],
678. 1 [201, 202],
679. 1 [301, null]
680. 1 ],
681. 1 [
682. 1 ["c401", "c402", "c403"],
683. 1 [401, 402, 403],
684. 1 [501, 502.0123, 5030123456789],
685. 1 [601, "602", "603_\"\u0001\b\t\n\u000b\f\r\u000e"],
686. 1 [null, null, null],
687. 1 ["foob", "fooba", "foobar"],
688. 1 ["foob", "fooba", "foobar"]
689. 1 ]
690. 1 ]
691. 1 );
692. 3 } catch (err) {
693. 3 assertOrThrow(
694. 3 err.message.indexOf(
695. 3 "table testDbExecAsync1 already exists"
696. 3 ) >= 0,
697. 3 err
698. 3 );
699. 3 }
700. 4 });
701. 1 // test close-while-busy handling-behavior
702. 1 assertErrorThrownAsync(function () {
703. 1 return dbCloseAsync(db);
704. 1 }, "cannot close db");
705. 1 });
706. 1 jstestIt((
707. 1 "test dbFileXxx handling-behavior"
708. 1 ), async function test_dbFileXxx() {
709. 1 let data;
710. 1 let db = await dbOpenAsync({
711. 1 filename: ":memory:"
712. 1 });
713. 1 // test null-case handling-behavior
714. 1 dbFileLoadAsync({
715. 1 modeNoop: true
716. 1 });
717. 1 assertErrorThrownAsync(function () {
718. 1 return dbFileLoadAsync({
719. 1 db
720. 1 });
721. 1 }, "invalid filename undefined");
722. 1 assertErrorThrownAsync(function () {
723. 1 return dbFileSaveAsync({
724. 1 db
725. 1 });
726. 1 }, "invalid filename undefined");
727. 1 await dbExecAsync({
728. 1 db,
729. 1 sql: "CREATE TABLE t01 AS SELECT 1 AS c01"
730. 1 });
731. 1 await dbFileSaveAsync({
732. 1 db,
733. 1 filename: ".testDbFileXxx.sqlite"
734. 1 });
735. 1 db = await dbOpenAsync({
736. 1 filename: ":memory:"
737. 1 });
738. 1 await dbFileLoadAsync({
739. 1 db,
740. 1 filename: ".testDbFileXxx.sqlite"
741. 1 });
742. 1 data = await dbExecAsync({
743. 1 db,
744. 1 sql: "SELECT * FROM t01"
745. 1 });
746. 1 assertJsonEqual(data, [[{c01: 1}]]);
747. 1 });
748. 1 jstestIt((
749. 1 "test dbOpenAsync handling-behavior"
750. 1 ), async function test_dbOpenAsync() {
751. 1 // test auto-finalization handling-behavior
752. 1 await new Promise(function (resolve) {
753. 1 dbOpenAsync({
754. 1 afterFinalization: resolve,
755. 1 filename: ":memory:"
756. 1 });
757. 1 });
758. 1 // test null-case handling-behavior
759. 1 assertErrorThrownAsync(function () {
760. 1 return dbOpenAsync({});
761. 1 }, "invalid filename");
762. 1 });
763. 1 jstestIt((
764. 1 "test dbTableXxx handling-behavior"
765. 1 ), async function test_dbTableXxx() {
766. 1 let db = await dbOpenAsync({
767. 1 filename: ":memory:"
768. 1 });
769. 1 await Promise.all([
770. 1 dbTableImportAsync({
771. 1 db,
772. 1 mode: "csv",
773. 1 tableName: "__csv0",
774. 1 textData: ""
775. 1 }),
776. 1 dbTableImportAsync({
777. 1 db,
778. 1 mode: "csv",
779. 1 tableName: "__csv1",
780. 1 textData: String(`
781. 1duplicate_header,duplicate_header
782. 1"aaa","b""bb","ccc"
783. 1"aaa","b
784. 1bb","ccc"
785. 1zzz,yyy,xxx
786. 1 `).trim()
787. 1 }),
788. 1 dbTableImportAsync({
789. 1 db,
790. 1 mode: "json",
791. 1 tableName: "__json0",
792. 1 textData: "null"
793. 1 }),
794. 1 dbTableImportAsync({
795. 1 db,
796. 1 mode: "json",
797. 1 tableName: "__json1",
798. 1 textData: JSON.stringify({
799. 1 aa: {aa: 1, bb: 2},
800. 1 bb: {aa: 3, bb: 4}
801. 1 })
802. 1 }),
803. 1 dbTableImportAsync({
804. 1 db,
805. 1 mode: "tsv",
806. 1 tableName: "__tsv1",
807. 1 textData: "aa,bb\tcc,dd"
808. 1 })
809. 1 ]);
810. 1 });
811. 1});
812. 1
813. 1jstestDescribe((
814. 1 "test_fsXxx"
815. 1), function test_fsXxx() {
816. 1 jstestIt((
817. 1 "test fsXxx handling-behavior"
818. 1 ), async function () {
819. 1 await Promise.all([
820. 1 fsCopyFileUnlessTest("", ""),
821. 1 fsExistsUnlessTest(""),
822. 1 fsReadFileUnlessTest("", ""),
823. 1 fsWriteFileUnlessTest("", ""),
824. 1 //
825. 1 fsCopyFileUnlessTest(
826. 1 "package.json",
827. 1 ".tmp/test_fsCopyFileUnlessTest_force",
828. 1 "force"
829. 1 ),
830. 1 fsExistsUnlessTest("", "force"),
831. 1 fsExistsUnlessTest("package.json", "force"),
832. 1 fsReadFileUnlessTest("package.json", "force"),
833. 1 fsWriteFileUnlessTest(
834. 1 ".tmp/test_fsWriteFileUnlessTest_force",
835. 1 "",
836. 1 "force"
837. 1 )
838. 1 ]);
839. 1 });
840. 1});
841. 1
842. 1jstestDescribe((
843. 1 "test_lgbm"
844. 1), function test_lgbm() {
845. 1 jstestIt((
846. 1 "test lgbm handling-behavior"
847. 1 ), async function () {
848. 1 let filePreb = "test_lgbm_preb.txt";
849. 1 let fileTest = "test_lgbm_binary.test";
850. 1 let fileTrain = "test_lgbm_binary.train";
851. 1 let promiseList = [];
852. 1 let sqlDataFile = (`
853. 1UPDATE __lgbm_state
854. 1 SET
855. 1 data_train_handle = (
856. 1 SELECT
857. 1 lgbm_datasetcreatefromfile(
858. 1 '${fileTrain}', -- filename
859. 1 'max_bin=15', -- param_data
860. 1 NULL -- reference
861. 1 )
862. 1 );
863. 1UPDATE __lgbm_state
864. 1 SET
865. 1 data_test_handle = (
866. 1 SELECT
867. 1 lgbm_datasetcreatefromfile(
868. 1 '${fileTest}', -- filename
869. 1 'max_bin=15', -- param_data
870. 1 data_train_handle -- reference
871. 1 )
872. 1 );
873. 1 `);
874. 1 let sqlDataTable = (`
875. 1UPDATE __lgbm_state
876. 1 SET
877. 1 data_train_handle = (
878. 1 SELECT
879. 1 lgbm_datasetcreatefromtable(
880. 1 'max_bin=15', -- param_data
881. 1 NULL, -- reference
882. 1 --
883. 1 _1, _2, _3, _4,
884. 1 _5, _6, _7, _8,
885. 1 _9, _10, _11, _12,
886. 1 _13, _14, _15, _16,
887. 1 _17, _18, _19, _20,
888. 1 _21, _22, _23, _24,
889. 1 _25, _26, _27, _28,
890. 1 _29
891. 1 )
892. 1 FROM __lgbm_file_train
893. 1 );
894. 1UPDATE __lgbm_state
895. 1 SET
896. 1 data_test_handle = (
897. 1 SELECT
898. 1 lgbm_datasetcreatefromtable(
899. 1 'max_bin=15', -- param_data
900. 1 data_train_handle, -- reference
901. 1 --
902. 1 _1, _2, _3, _4,
903. 1 _5, _6, _7, _8,
904. 1 _9, _10, _11, _12,
905. 1 _13, _14, _15, _16,
906. 1 _17, _18, _19, _20,
907. 1 _21, _22, _23, _24,
908. 1 _25, _26, _27, _28,
909. 1 _29
910. 1 )
911. 1 FROM __lgbm_file_test
912. 1 );
913. 1 `);
914. 1 let sqlIi = 0;
915. 1 let sqlPredictFile = (`
916. 1SELECT
917. 1 lgbm_predictforfile(
918. 1 model, -- model
919. 1 ${LGBM_PREDICT_NORMAL}, -- predict_type
920. 1 0, -- start_iteration
921. 1 25, -- num_iteration
922. 1 '', -- param_pred
923. 1 --
924. 1 '${fileTest}', -- data_filename
925. 1 0, -- data_has_header
926. 1 'fileActual' -- result_filename
927. 1 )
928. 1 FROM __lgbm_state;
929. 1SELECT
930. 1 lgbm_predictforfile(
931. 1 model, -- model
932. 1 ${LGBM_PREDICT_NORMAL}, -- predict_type
933. 1 10, -- start_iteration
934. 1 25, -- num_iteration
935. 1 '', -- param_pred
936. 1 --
937. 1 '${fileTest}', -- data_filename
938. 1 0, -- data_has_header
939. 1 'fileActual' -- result_filename
940. 1 )
941. 1 FROM __lgbm_state;
942. 1 `);
943. 1 let sqlPredictTable = (`
944. 1DROP TABLE IF EXISTS __lgbm_table_preb;
945. 1CREATE TABLE __lgbm_table_preb AS
946. 1 SELECT
947. 1 DOUBLEARRAY_EXTRACT(__lgp, 0) AS prediction
948. 1 FROM (
949. 1 SELECT
950. 1 lgbm_predictfortable(
951. 1 (SELECT model FROM __lgbm_state), -- model
952. 1 ${LGBM_PREDICT_NORMAL}, -- predict_type
953. 1 0, -- start_iteration
954. 1 25, -- num_iteration
955. 1 '', -- param_pred
956. 1 --
957. 1 _2, _3, _4,
958. 1 _5, _6, _7, _8,
959. 1 _9, _10, _11, _12,
960. 1 _13, _14, _15, _16,
961. 1 _17, _18, _19, _20,
962. 1 _21, _22, _23, _24,
963. 1 _25, _26, _27, _28,
964. 1 _29
965. 1 ) OVER (
966. 1 ORDER BY rowid ASC
967. 1 ROWS BETWEEN 0 PRECEDING AND 0 FOLLOWING
968. 1 ) AS __lgp
969. 1 FROM __lgbm_file_test
970. 1 );
971. 1DROP TABLE IF EXISTS __lgbm_table_preb;
972. 1CREATE TABLE __lgbm_table_preb AS
973. 1 SELECT
974. 1 DOUBLEARRAY_EXTRACT(__lgp, 0) AS _1
975. 1 FROM (
976. 1 SELECT
977. 1 lgbm_predictfortable(
978. 1 (SELECT model FROM __lgbm_state), -- model
979. 1 ${LGBM_PREDICT_NORMAL}, -- predict_type
980. 1 10, -- start_iteration
981. 1 25, -- num_iteration
982. 1 '', -- param_pred
983. 1 --
984. 1 _2, _3, _4,
985. 1 _5, _6, _7, _8,
986. 1 _9, _10, _11, _12,
987. 1 _13, _14, _15, _16,
988. 1 _17, _18, _19, _20,
989. 1 _21, _22, _23, _24,
990. 1 _25, _26, _27, _28,
991. 1 _29
992. 1 ) OVER (
993. 1 ORDER BY rowid ASC
994. 1 ROWS BETWEEN 0 PRECEDING AND 0 FOLLOWING
995. 1 ) AS __lgp
996. 1 FROM __lgbm_file_test
997. 1 );
998. 1 `);
999. 1 let sqlTrainData = (`
1000. 1UPDATE __lgbm_state
1001. 1 SET
1002. 1 model = lgbm_trainfromdataset(
1003. 1 -- param_train
1004. 1 (
1005. 1 'objective=binary'
1006. 1 || ' learning_rate=0.1' -- default=0.1
1007. 1 || ' max_depth=-1' -- default=-1
1008. 1 || ' metric=auc' -- default=""
1009. 1 || ' min_data_in_leaf=20' -- default=20
1010. 1 || ' num_class=1' -- default=1
1011. 1 || ' num_leaves=31' -- default=31
1012. 1 || ' verbosity=0' -- default=1
1013. 1 ),
1014. 1 50, -- num_iteration
1015. 1 10, -- eval_step
1016. 1 --
1017. 1 data_train_handle, -- train_data
1018. 1 data_test_handle -- test_data
1019. 1 );
1020. 1 `);
1021. 1 let sqlTrainFile = (`
1022. 1UPDATE __lgbm_state
1023. 1 SET
1024. 1 model = lgbm_trainfromfile(
1025. 1 -- param_train
1026. 1 (
1027. 1 'objective=binary'
1028. 1 || ' learning_rate=0.1' -- default=0.1
1029. 1 || ' max_depth=-1' -- default=-1
1030. 1 || ' metric=auc' -- default=""
1031. 1 || ' min_data_in_leaf=20' -- default=20
1032. 1 || ' num_class=1' -- default=1
1033. 1 || ' num_leaves=31' -- default=31
1034. 1 || ' verbosity=0' -- default=1
1035. 1 ),
1036. 1 50, -- num_iteration
1037. 1 10, -- eval_step
1038. 1 --
1039. 1 '${fileTrain}', -- file_train
1040. 1 'max_bin=15', -- param_data
1041. 1 '${fileTest}' -- file_test
1042. 1 );
1043. 1 `);
1044. 1 let sqlTrainTable = (`
1045. 1UPDATE __lgbm_state
1046. 1 SET
1047. 1 model = (
1048. 1 SELECT
1049. 1 lgbm_trainfromtable(
1050. 1 -- param_train
1051. 1 (
1052. 1 'objective=binary'
1053. 1 || ' learning_rate=0.1' -- default=0.1
1054. 1 || ' max_depth=-1' -- default=-1
1055. 1 || ' metric=auc' -- default=""
1056. 1 || ' min_data_in_leaf=20' -- default=20
1057. 1 || ' num_class=1' -- default=1
1058. 1 || ' num_leaves=31' -- default=31
1059. 1 || ' verbosity=0' -- default=1
1060. 1 ),
1061. 1 50, -- num_iteration
1062. 1 10, -- eval_step
1063. 1 --
1064. 1 'max_bin=15', -- param_data
1065. 1 NULL, -- reference
1066. 1 --
1067. 1 _1, _2, _3, _4,
1068. 1 _5, _6, _7, _8,
1069. 1 _9, _10, _11, _12,
1070. 1 _13, _14, _15, _16,
1071. 1 _17, _18, _19, _20,
1072. 1 _21, _22, _23, _24,
1073. 1 _25, _26, _27, _28,
1074. 1 _29
1075. 1 )
1076. 1 FROM __lgbm_file_train
1077. 1 );
1078. 1 `);
1079. 12 async function testLgbm(sqlDataXxx, sqlTrainXxx, sqlPredictXxx, sqlIi) {
1080. 12 let db = await dbOpenAsync({filename: ":memory:"});
1081. 12 let fileActual = `.tmp/test_lgbm_preb_${sqlIi}.txt`;
1082. 12 await Promise.all([
1083. 12 dbTableImportAsync({
1084. 12 db,
1085. 12 filename: filePreb,
1086. 12 headerMissing: true,
1087. 12 mode: "tsv",
1088. 12 tableName: "__lgbm_file_preb"
1089. 12 }),
1090. 12 dbTableImportAsync({
1091. 12 db,
1092. 12 filename: fileTest,
1093. 12 headerMissing: true,
1094. 12 mode: "tsv",
1095. 12 tableName: "__lgbm_file_test"
1096. 12 }),
1097. 12 dbTableImportAsync({
1098. 12 db,
1099. 12 filename: fileTrain,
1100. 12 headerMissing: true,
1101. 12 mode: "tsv",
1102. 12 tableName: "__lgbm_file_train"
1103. 12 })
1104. 12 ]);
1105. 12 await dbExecAsync({
1106. 12 db,
1107. 12 sql: (`
1108. 12-- lgbm - init
1109. 12CREATE TABLE __lgbm_state(
1110. 12 data_test_handle INTEGER,
1111. 12 data_test_num_data REAL,
1112. 12 data_test_num_feature REAL,
1113. 12 --
1114. 12 data_train_handle INTEGER,
1115. 12 data_train_num_data REAL,
1116. 12 data_train_num_feature REAL,
1117. 12 --
1118. 12 model BLOB
1119. 12);
1120. 12INSERT INTO __lgbm_state(rowid) SELECT 1;
1121. 12
1122. 12-- lgbm - data
1123. 12${sqlDataXxx};
1124. 12UPDATE __lgbm_state
1125. 12 SET
1126. 12 data_test_num_data = lgbm_datasetgetnumdata(data_test_handle),
1127. 12 data_test_num_feature = lgbm_datasetgetnumfeature(data_test_handle),
1128. 12 data_train_num_data = lgbm_datasetgetnumdata(data_train_handle),
1129. 12 data_train_num_feature = lgbm_datasetgetnumfeature(data_train_handle);
1130. 12
1131. 12-- lgbm - train
1132. 12${sqlTrainXxx};
1133. 12 `)
1134. 12 });
1135. 12 await dbExecAsync({
1136. 12 db,
1137. 12 sql: (`
1138. 12-- lgbm - predict
1139. 12${sqlPredictXxx.replace(/fileActual/g, fileActual)};
1140. 12
1141. 12-- lgbm - cleanup
1142. 12SELECT
1143. 12 lgbm_datasetfree(data_test_handle),
1144. 12 lgbm_datasetfree(data_train_handle)
1145. 12 FROM __lgbm_state;
1146. 12 `)
1147. 12 });
1148. 6 if (sqlPredictXxx === sqlPredictFile) {
1149. 6 dbTableImportAsync({
1150. 6 db,
1151. 6 filename: fileActual,
1152. 6 headerMissing: true,
1153. 6 mode: "tsv",
1154. 6 tableName: "__lgbm_table_preb"
1155. 6 });
1156. 6 }
1157. 12 await dbFileSaveAsync({
1158. 12 db,
1159. 12 filename: `.tmp/test_lgbm_${sqlIi}.sqlite`
1160. 12 });
1161. 12 assertJsonEqual(
1162. 12 noop(
1163. 12 await dbExecAndReturnLastRow({
1164. 12 db,
1165. 12 sql: (`
1166. 12SELECT
1167. 12 data_test_num_data,
1168. 12 data_test_num_feature,
1169. 12 data_train_num_data,
1170. 12 data_train_num_feature
1171. 12 FROM __lgbm_state;
1172. 12 `)
1173. 12 })
1174. 12 ),
1175. 12 {
1176. 12 "data_test_num_data": 500,
1177. 12 "data_test_num_feature": 28,
1178. 12 "data_train_num_data": 7000,
1179. 12 "data_train_num_feature": 28
1180. 12 }
1181. 12 );
1182. 6 if (sqlPredictXxx === sqlPredictFile) {
1183. 6 assertJsonEqual(
1184. 6 await fsReadFileUnlessTest(fileActual, "force"),
1185. 6 await fsReadFileUnlessTest(filePreb, "force")
1186. 6 );
1187. 6 }
1188. 12 assertJsonEqual(
1189. 12 noop(
1190. 12 await dbExecAndReturnLastTable({
1191. 12 db,
1192. 12 sql: (`
1193. 12SELECT ROUND(_1, 8) AS _1 FROM __lgbm_table_preb;
1194. 12 `)
1195. 12 })
1196. 12 ),
1197. 12 noop(
1198. 12 await dbExecAndReturnLastTable({
1199. 12 db,
1200. 12 sql: (`
1201. 12SELECT ROUND(_1, 8) AS _1 FROM __lgbm_file_preb;
1202. 12 `)
1203. 12 })
1204. 12 )
1205. 12 );
1206. 12 }
1207. 1 [
1208. 1 sqlDataFile, sqlDataTable
1209. 2 ].forEach(function (sqlDataXxx) {
1210. 2 [
1211. 2 sqlTrainData, sqlTrainFile, sqlTrainTable
1212. 6 ].forEach(function (sqlTrainXxx) {
1213. 6 [
1214. 6 sqlPredictFile, sqlPredictTable
1215. 12 ].forEach(function (sqlPredictXxx) {
1216. 12 sqlIi += 1;
1217. 12 promiseList.push(
1218. 12 testLgbm(sqlDataXxx, sqlTrainXxx, sqlPredictXxx, sqlIi)
1219. 12 );
1220. 12 });
1221. 6 });
1222. 2 });
1223. 1 await Promise.all(promiseList);
1224. 1 });
1225. 1});
1226. 1
1227. 1jstestDescribe((
1228. 1 "test_misc"
1229. 1), function test_misc() {
1230. 1 jstestIt((
1231. 1 "test misc handling-behavior"
1232. 1 ), async function () {
1233. 1 // test assertErrorThrownAsync error handling-behavior
1234. 1 await assertErrorThrownAsync(function () {
1235. 1 return assertErrorThrownAsync(noop);
1236. 1 }, "No error thrown");
1237. 1 // test assertJsonEqual error handling-behavior
1238. 1 await assertErrorThrownAsync(function () {
1239. 1 assertJsonEqual(1, 2);
1240. 1 }, "!==");
1241. 1 await assertErrorThrownAsync(function () {
1242. 1 assertJsonEqual(1, 2, "undefined");
1243. 1 }, "undefined");
1244. 1 await assertErrorThrownAsync(function () {
1245. 1 assertJsonEqual(1, 2, {});
1246. 1 }, "");
1247. 1 // test assertOrThrow error handling-behavior
1248. 1 await assertErrorThrownAsync(function () {
1249. 1 assertOrThrow(undefined, "undefined");
1250. 1 }, "undefined");
1251. 1 await assertErrorThrownAsync(function () {
1252. 1 assertOrThrow(undefined, new Error());
1253. 1 }, "");
1254. 1 // test listOrEmptyList null-case handling-behavior
1255. 1 assertJsonEqual(listOrEmptyList(), []);
1256. 1 // test waitAsync null-case handling-behavior
1257. 1 await waitAsync();
1258. 1 });
1259. 1});
1260. 1
1261. 1jstestDescribe((
1262. 1 "test_sqlite"
1263. 1), function test_sqlite() {
1264. 1 jstestIt((
1265. 1 "test sqlite-error handling-behavior"
1266. 1 ), async function test_sqliteError() {
1267. 1 let db = await dbOpenAsync({
1268. 1 filename: ":memory:"
1269. 1 });
1270. 1 assertJsonEqual(
1271. 1 "not an error",
1272. 1 noop(
1273. 1 await dbExecAndReturnLastRow({
1274. 1 db,
1275. 1 sql: `SELECT throwerror(0) AS val`
1276. 1 })
1277. 1 ).val
1278. 1 );
1279. 1 await Promise.all([
1280. 1 [1, "SQL logic error"],
1281. 1 [2, "unknown error"],
1282. 1 [3, "access permission denied"],
1283. 1 [4, "query aborted"],
1284. 1 [5, "database is locked"],
1285. 1 [6, "database table is locked"],
1286. 1 [7, "out of memory"],
1287. 1 [8, "attempt to write a readonly database"],
1288. 1 [9, "interrupted"],
1289. 1 [10, "disk I/O error"],
1290. 1 [11, "database disk image is malformed"],
1291. 1 [12, "unknown operation"],
1292. 1 [13, "database or disk is full"],
1293. 1 [14, "unable to open database file"],
1294. 1 [15, "locking protocol"],
1295. 1 [16, "unknown error"],
1296. 1 [17, "database schema has changed"],
1297. 1 [18, "string or blob too big"],
1298. 1 [19, "constraint failed"],
1299. 1 [20, "datatype mismatch"],
1300. 1 [21, "bad parameter or other API misuse"],
1301. 1 [22, "unknown error"],
1302. 1 [23, "authorization denied"],
1303. 1 [24, "unknown error"],
1304. 1 [25, "column index out of range"],
1305. 1 [26, "file is not a database"],
1306. 1 [27, "notification message"],
1307. 1 [28, "warning message"],
1308. 1 [100, "unknown error"],
1309. 1 [101, "unknown error"]
1310. 30 ].map(async function ([
1311. 30 errno, errmsg
1312. 30 ]) {
1313. 30 await assertErrorThrownAsync(function () {
1314. 30 return dbExecAsync({
1315. 30 db,
1316. 30 sql: `SELECT throwerror(${errno})`
1317. 30 });
1318. 30 }, errmsg);
1319. 30 }));
1320. 1 });
1321. 1 jstestIt((
1322. 1 "test sqlite-extension-doublearray_xxx handling-behavior"
1323. 1 ), async function test_sqlite_extension_doublearray_xxx() {
1324. 1 let db = await dbOpenAsync({
1325. 1 filename: ":memory:"
1326. 1 });
1327. 1 await Promise.all([
1328. 1 [" [ , 1 ] ", "error"],
1329. 1 [" [ , ] ", "error"],
1330. 1 [" [ 1 , ] ", "error"],
1331. 1 [" [ ] ", "[]"],
1332. 1 [" [ null ] ", "[0.0]"],
1333. 1 ["", "error"],
1334. 1 ["1,2]", "error"],
1335. 1 ["[,1]", "error"],
1336. 1 ["[,]", "error"],
1337. 1 ["[0]", "[0.0]"],
1338. 1 ["[1,2", "error"],
1339. 1 ["[1,2,a]", "[1.0,2.0,0.0]"],
1340. 1 ["[1,]", "error"],
1341. 1 ["[1,a,3]", "[1.0,0.0,3.0]"],
1342. 1 ["[1]", "[1.0]"],
1343. 1 ["[]", "[]"],
1344. 1 ["[a,2,3]", "[0.0,2.0,3.0]"],
1345. 1 [0, "error"],
1346. 1 [1, "error"],
1347. 1 [`[${"1".repeat(100)}]`, `[1.11111111111111e+99]`],
1348. 1 [null, "error"],
1349. 1 [undefined, "error"],
1350. 1 [{}, "error"]
1351. 23 ].map(async function ([valIn, valExpect], ii) {
1352. 23 let valActual;
1353. 23 try {
1354. 23 valActual = noop(
1355. 23 await dbExecAndReturnLastRow({
1356. 23 bindList: {
1357. 23 valIn
1358. 23 },
1359. 23 db,
1360. 23 sql: (`
1361. 23SELECT DOUBLEARRAY_JSONTO(DOUBLEARRAY_JSONFROM($valIn)) AS result;
1362. 23 `)
1363. 9 })
1364. 9 ).result;
1365. 14 } catch (ignore) {
1366. 14 assertOrThrow(valExpect === "error", JSON.stringify({
1367. 14 ii,
1368. 14 valActual,
1369. 14 valExpect,
1370. 14 valIn
1371. 14 }, undefined, 4));
1372. 14 return;
1373. 14 }
1374. 9 assertJsonEqual(valActual, valExpect, {
1375. 9 ii,
1376. 9 valActual,
1377. 9 valExpect,
1378. 9 valIn
1379. 9 });
1380. 9 }));
1381. 1 });
1382. 1 jstestIt((
1383. 1 "test_sqlite_extension_idate_xxx handling-behavior"
1384. 1 ), async function test_sqlite_extension_idate_xxx() {
1385. 1 let db = await dbOpenAsync({filename: ":memory:"});
1386. 1 let promiseList = [];
1387. 588 function idateArgNormalize(sqlFunc, arg, mode) {
1388. 268 function idateArgYmdTruncate() {
1389. 238 if (Number.isFinite(Number(arg))) {
1390. 238 return Math.floor(arg / 1_00_00_00);
1391. 238 }
1392. 30 return arg.split(" ")[0];
1393. 30 }
1394. 556 switch (arg !== null && mode) {
1395. 252 case "expect":
1396. 252 if ((/(?:IDATEFROM|'IYMDH\w*?').*?_YMD$/).test(sqlFunc)) {
1397. 252 return arg - (arg % 1_00_00_00);
1398. 252 }
1399. 252 if ((/'IY'/).test(sqlFunc)) {
1400. 252 arg = idateArgYmdTruncate(arg);
1401. 252 return arg - (arg % 1_00_00);
1402. 252 }
1403. 252 if ((/'IYM'/).test(sqlFunc)) {
1404. 252 arg = idateArgYmdTruncate(arg);
1405. 252 return arg - (arg % 1_00);
1406. 252 }
1407. 252 if ((/'IYMD'/).test(sqlFunc)) {
1408. 252 arg = idateArgYmdTruncate(arg);
1409. 252 return arg - (arg % 1);
1410. 252 }
1411. 252 if ((/'IYMDH'/).test(sqlFunc)) {
1412. 252 return arg - (arg % 1_00_00);
1413. 252 }
1414. 252 if ((/'IYMDHM'/).test(sqlFunc)) {
1415. 252 return arg - (arg % 1_00);
1416. 252 }
1417. 252 if (
1418. 252 (/^IDATEYMDFROM|'ITEXTYMD'/).test(sqlFunc)
1419. 252 || ((/_YMD$/).test(sqlFunc) && !(/IDATEFROM/).test(sqlFunc))
1420. 252 ) {
1421. 252 return idateArgYmdTruncate(arg);
1422. 252 }
1423. 252 break;
1424. 304 case "input":
1425. 304 if ((/_YMD$/).test(sqlFunc)) {
1426. 304 return idateArgYmdTruncate(arg);
1427. 304 }
1428. 304 break;
1429. 262 }
1430. 262 return arg;
1431. 262 }
1432. 1 promiseList.push([
1433. 1 "IDATEADD",
1434. 1 //
1435. 1 "IDATEFROM",
1436. 1 // "IDATEFROMEPOCH",
1437. 1 "IDATEYMDFROM",
1438. 1 // "IDATEYMDFROMEPOCH",
1439. 1 //
1440. 1 "IDATETO('IDATE')",
1441. 1 "IDATETO('IEPOCH')",
1442. 1 "IDATETO('IJULIAN')",
1443. 1 "IDATETO('ITEXT')",
1444. 1 "IDATETO('ITEXTYMD')",
1445. 1 "IDATETO('IY')",
1446. 1 "IDATETO('IYM')",
1447. 1 "IDATETO('IYMD')",
1448. 1 "IDATETO('IYMDH')",
1449. 1 "IDATETO('IYMDHM')",
1450. 1 "IDATETO('IYMDHMS')",
1451. 1 "IDATETOEPOCH"
1452. 15 ].map(function (sqlFunc) {
1453. 15 return [
1454. 15 ["", null],
1455. 15 ["+0", null],
1456. 15 ["+1", null],
1457. 15 ["-0", null],
1458. 15 ["-1", null],
1459. 15 ["-1000-01-01 00:00:00", null],
1460. 15 ["-10000101000000", null],
1461. 15 ["-9991231000000", null],
1462. 15 ["-9999-12-31 23:59:59", null],
1463. 15 ["-99991231235959", null],
1464. 15 ["-99991231235960", null],
1465. 15 ["0", null],
1466. 15 ["0999-12-31 23:59:00", null],
1467. 15 ["09991231235900", null],
1468. 15 ["1000-01-01 00:00:00", null, -1],
1469. 15 ["10000101000000", null, -1],
1470. 15 ["999-12-31 23:59:00", null],
1471. 15 ["999-12-31 23:59:59", null],
1472. 15 ["9991231000000", null],
1473. 15 ["9999-12-31 23:59:59", null, 1],
1474. 15 ["9999-12-31 23:59:60", null],
1475. 15 ["9999-12-32 23:59:59", null],
1476. 15 ["99991231235959", null, 1],
1477. 15 ["99991231235960", null],
1478. 15 [-1, null],
1479. 15 [-10000101000000, null],
1480. 15 [-9991231000000, null],
1481. 15 [-99991231235959, null],
1482. 15 [-99991231235960, null],
1483. 15 [0, null],
1484. 15 [1, null],
1485. 15 [10000101000000, null, -1],
1486. 15 [9991231000000, null],
1487. 15 [9991231235900, null],
1488. 15 [99991231235959, null, 1],
1489. 15 [99991231235960, null],
1490. 15 [99991232235959, null],
1491. 15 [null, null]
1492. 570 ].map(function ([valIn, valExpect, modifier]) {
1493. 570 return [sqlFunc, valIn, valExpect, modifier];
1494. 570 });
1495. 15 }).flat());
1496. 1 promiseList.push([
1497. 1 "IDATEFROM",
1498. 1 "IDATEYMDFROM"
1499. 2 ].map(function (sqlFunc) {
1500. 2 return [
1501. 2 [10000101000000, null],
1502. 2 [99991231235959, null]
1503. 4 ].map(function ([valIn, valExpect, modifier]) {
1504. 4 return [sqlFunc, valIn, valExpect, modifier];
1505. 4 });
1506. 2 }).flat());
1507. 1 promiseList.push([
1508. 1 "IDATEADD",
1509. 1 "IDATETO('IDATE')",
1510. 1 "IDATETO('IEPOCH')",
1511. 1 "IDATETO('IJULIAN')",
1512. 1 "IDATETO('ITEXT')",
1513. 1 "IDATETO('ITEXTYMD')",
1514. 1 "IDATETO('IY')",
1515. 1 "IDATETO('IYM')",
1516. 1 "IDATETO('IYMD')",
1517. 1 "IDATETO('IYMDH')",
1518. 1 "IDATETO('IYMDHM')",
1519. 1 "IDATETO('IYMDHMS')",
1520. 1 "IDATETOEPOCH"
1521. 13 ].map(function (sqlFunc) {
1522. 13 return [
1523. 13 ["1000-01-01 00:00:00", null],
1524. 13 ["9999-12-31 23:59:59", null],
1525. 13 [2086302.5, null],
1526. 13 [5373483.5, null]
1527. 52 ].map(function ([valIn, valExpect, modifier]) {
1528. 52 return [sqlFunc, valIn, valExpect, modifier];
1529. 52 });
1530. 13 }).flat());
1531. 1 promiseList.push([
1532. 1 "IDATEADD",
1533. 1 "IDATETO('IDATE')"
1534. 2 ].map(function (sqlFunc) {
1535. 2 return [
1536. 2 ["10000101000000", 10000101000000],
1537. 2 ["99991231235959", 99991231235959],
1538. 2 [10000101000000, 10000101000000],
1539. 2 [10000229000000, 10000301000000],
1540. 2 [10000301000000, 10000301000000],
1541. 2 [10040229000000, 10040229000000],
1542. 2 [99960229235959, 99960229235959],
1543. 2 [99970229235959, 99970301235959],
1544. 2 [99970301235959, 99970301235959],
1545. 2 [99991231235959, 99991231235959]
1546. 20 ].map(function ([valIn, valExpect]) {
1547. 40 return [sqlFunc, `${sqlFunc}_YMD`].map(function (sqlFunc) {
1548. 40 return [
1549. 40 sqlFunc,
1550. 40 idateArgNormalize(sqlFunc, valIn, "input"),
1551. 40 idateArgNormalize(sqlFunc, valExpect, "expect")
1552. 40 ];
1553. 40 });
1554. 20 }).flat();
1555. 2 }).flat());
1556. 1 promiseList.push([
1557. 1 "IDATEFROMEPOCH",
1558. 1 "IDATEYMDFROMEPOCH"
1559. 2 ].map(function (sqlFunc) {
1560. 2 return [
1561. 2 [10000101000000, "-30610224000"],
1562. 2 [10000101000000, -30610224000],
1563. 2 [10000301000000, -30605126400],
1564. 2 [10040229000000, -30478982400],
1565. 2 [99960229235959, 253281254399, 253281168000],
1566. 2 [99970301235959, 253312876799, 253312790400],
1567. 2 [99991231235959, "253402300799", "253402214400"],
1568. 2 [99991231235959, 253402300799, 253402214400]
1569. 16 ].map(function ([valExpect, valIn, valInYmd]) {
1570. 16 return [
1571. 16 sqlFunc,
1572. 16 (
1573. 16 (/^IDATEYMDFROM|_YMD$/).test(sqlFunc)
1574. 8 ? valInYmd || valIn
1575. 8 : valIn
1576. 16 ),
1577. 16 idateArgNormalize(sqlFunc, valExpect, "expect")
1578. 16 ];
1579. 16 });
1580. 2 }).flat());
1581. 1 promiseList.push([
1582. 1 "IDATEFROMEPOCH",
1583. 1 "IDATEYMDFROMEPOCH"
1584. 2 ].map(function (sqlFunc) {
1585. 2 return [
1586. 2 [-30610224000, 10000101000000, "-0 SECOND"],
1587. 2 [-30610224000, null, "-1 SECOND"],
1588. 2 [253402214400, 99991231000000, "+0 DAY"],
1589. 2 [253402214400, null, "+1 DAY"]
1590. 8 ].map(function ([valIn, valExpect, modifier]) {
1591. 8 return [
1592. 8 sqlFunc,
1593. 8 valIn,
1594. 8 idateArgNormalize(sqlFunc, valExpect, "expect"),
1595. 8 modifier
1596. 8 ];
1597. 8 });
1598. 2 }).flat());
1599. 1 promiseList.push([
1600. 1 "IDATEFROM_JULIAN",
1601. 1 "IDATEYMDFROM_JULIAN"
1602. 2 ].map(function (sqlFunc) {
1603. 2 return [
1604. 2 [10000101000000, "2086302.5"],
1605. 2 [10000101000000, 2086302.5],
1606. 2 [10000301000000, 2086361.5],
1607. 2 [10040229000000, 2087821.5],
1608. 2 [99960229235959, 5372083.49998843, 5372082.5],
1609. 2 [99970301235959, 5372449.49998843, 5372448.5],
1610. 2 [99991231235959, "5373484.49998843", "5373483.5"],
1611. 2 [99991231235959, 5373484.49998843, 5373483.5]
1612. 16 ].map(function ([valExpect, valIn, valInYmd]) {
1613. 16 return [
1614. 16 sqlFunc,
1615. 16 (
1616. 16 (/^IDATEYMDFROM|_YMD$/).test(sqlFunc)
1617. 8 ? valInYmd || valIn
1618. 8 : valIn
1619. 16 ),
1620. 16 idateArgNormalize(sqlFunc, valExpect, "expect")
1621. 16 ];
1622. 16 });
1623. 2 }).flat());
1624. 1 promiseList.push([
1625. 1 "IDATEFROM_JULIAN",
1626. 1 "IDATEYMDFROM_JULIAN"
1627. 2 ].map(function (sqlFunc) {
1628. 2 return [
1629. 2 [2086302.5, 10000101000000, "-0 SECOND"],
1630. 2 [2086302.5, null, "-1 SECOND"],
1631. 2 [5373483.5, 99991231000000, "+0 DAY"],
1632. 2 [5373483.5, null, "+1 DAY"]
1633. 8 ].map(function ([valIn, valExpect, modifier]) {
1634. 8 return [
1635. 8 sqlFunc,
1636. 8 valIn,
1637. 8 idateArgNormalize(sqlFunc, valExpect, "expect"),
1638. 8 modifier
1639. 8 ];
1640. 8 });
1641. 2 }).flat());
1642. 1 promiseList.push([
1643. 1 "IDATEFROM_TEXT",
1644. 1 "IDATEYMDFROM_TEXT"
1645. 2 ].map(function (sqlFunc) {
1646. 2 return [
1647. 2 ["1000-01-01 00:00:00", 10000101000000],
1648. 2 ["1000-02-29 00:00:00", 10000301000000],
1649. 2 ["1000-03-01 00:00:00", 10000301000000],
1650. 2 ["1004-02-29 00:00:00", 10040229000000],
1651. 2 ["9996-02-29 23:59:59", 99960229235959],
1652. 2 ["9997-02-29 23:59:59", 99970301235959],
1653. 2 ["9997-03-01 23:59:59", 99970301235959],
1654. 2 ["9999-12-31 23:59:59", 99991231235959]
1655. 16 ].map(function ([valIn, valExpect]) {
1656. 32 return [sqlFunc, `${sqlFunc}_YMD`].map(function (sqlFunc) {
1657. 32 return [
1658. 32 sqlFunc,
1659. 32 idateArgNormalize(sqlFunc, valIn, "input"),
1660. 32 idateArgNormalize(sqlFunc, valExpect, "expect")
1661. 32 ];
1662. 32 });
1663. 16 }).flat();
1664. 2 }).flat());
1665. 1 promiseList.push([
1666. 1 "IDATETO('IEPOCH')",
1667. 1 "IDATETOEPOCH"
1668. 2 ].map(function (sqlFunc) {
1669. 2 return [
1670. 2 ["10000101000000", -30610224000],
1671. 2 ["99991231235959", 253402300799, 253402214400],
1672. 2 [10000101000000, -30610224000],
1673. 2 [10000229000000, -30605126400],
1674. 2 [10000301000000, -30605126400],
1675. 2 [10040229000000, -30478982400],
1676. 2 [99960229235959, 253281254399, 253281168000],
1677. 2 [99970229235959, 253312876799, 253312790400],
1678. 2 [99970301235959, 253312876799, 253312790400],
1679. 2 [99991231235959, 253402300799, 253402214400],
1680. 2 //
1681. 2 ["1000-01-01 00:00:00", null],
1682. 2 ["9999-12-31 23:59:59", null]
1683. 24 ].map(function ([valIn, valExpect, valExpectYmd]) {
1684. 48 return [sqlFunc, `${sqlFunc}_YMD`].map(function (sqlFunc) {
1685. 48 return [
1686. 48 sqlFunc,
1687. 48 idateArgNormalize(sqlFunc, valIn, "input"),
1688. 48 (
1689. 48 (/^IDATEYMDFROM|_YMD$/).test(sqlFunc)
1690. 24 ? valExpectYmd || valExpect
1691. 24 : valExpect
1692. 48 )
1693. 48 ];
1694. 48 });
1695. 24 }).flat();
1696. 2 }).flat());
1697. 1 promiseList.push([
1698. 1 "IDATETO('IJULIAN')"
1699. 1 ].map(function (sqlFunc) {
1700. 1 return [
1701. 1 ["10000101000000", 2086302.5],
1702. 1 ["99991231235959", 5373484.49998843, 5373483.5],
1703. 1 [10000101000000, 2086302.5],
1704. 1 [10000229000000, 2086361.5],
1705. 1 [10000301000000, 2086361.5],
1706. 1 [10040229000000, 2087821.5],
1707. 1 [99960229235959, 5372083.49998843, 5372082.5],
1708. 1 [99970229235959, 5372449.49998843, 5372448.5],
1709. 1 [99970301235959, 5372449.49998843, 5372448.5],
1710. 1 [99991231235959, 5373484.49998843, 5373483.5]
1711. 10 ].map(function ([valIn, valExpect, valExpectYmd]) {
1712. 20 return [sqlFunc, `${sqlFunc}_YMD`].map(function (sqlFunc) {
1713. 20 return [
1714. 20 sqlFunc,
1715. 20 idateArgNormalize(sqlFunc, valIn, "input"),
1716. 20 (
1717. 20 (/^IDATEYMDFROM|_YMD$/).test(sqlFunc)
1718. 10 ? valExpectYmd || valExpect
1719. 10 : valExpect
1720. 20 )
1721. 20 ];
1722. 20 });
1723. 10 }).flat();
1724. 1 }).flat());
1725. 1 promiseList.push([
1726. 1 "IDATETO('ITEXT')",
1727. 1 "IDATETO('ITEXTYMD')"
1728. 2 ].map(function (sqlFunc) {
1729. 2 return [
1730. 2 ["10000101000000", "1000-01-01 00:00:00"],
1731. 2 ["99991231235959", "9999-12-31 23:59:59"],
1732. 2 [10000101000000, "1000-01-01 00:00:00"],
1733. 2 [10000229000000, "1000-03-01 00:00:00"],
1734. 2 [10000301000000, "1000-03-01 00:00:00"],
1735. 2 [10040229000000, "1004-02-29 00:00:00"],
1736. 2 [99960229235959, "9996-02-29 23:59:59"],
1737. 2 [99970229235959, "9997-03-01 23:59:59"],
1738. 2 [99970301235959, "9997-03-01 23:59:59"],
1739. 2 [99991231235959, "9999-12-31 23:59:59"]
1740. 20 ].map(function ([valIn, valExpect]) {
1741. 20 return [
1742. 20 sqlFunc,
1743. 20 idateArgNormalize(sqlFunc, valIn, "input"),
1744. 20 idateArgNormalize(sqlFunc, valExpect, "expect")
1745. 20 ];
1746. 20 });
1747. 2 }).flat());
1748. 1 promiseList.push([
1749. 1 "IDATETO('IY')",
1750. 1 "IDATETO('IYM')",
1751. 1 "IDATETO('IYMD')",
1752. 1 "IDATETO('IYMDH')",
1753. 1 "IDATETO('IYMDHM')",
1754. 1 "IDATETO('IYMDHMS')"
1755. 6 ].map(function (sqlFunc) {
1756. 6 return [
1757. 6 ["10000101000000", 10000101000000],
1758. 6 ["99991231235959", 99991231235959],
1759. 6 [10000101000000, 10000101000000],
1760. 6 [10000101000000, null, -1],
1761. 6 [10000229000000, 10000301000000],
1762. 6 [10000301000000, 10000301000000],
1763. 6 [10040229000000, 10040229000000],
1764. 6 [99960229235959, 99960229235959],
1765. 6 [99970229235959, 99970301235959],
1766. 6 [99970301235959, 99970301235959],
1767. 6 [99991231235959, 99991231235959],
1768. 6 [99991231235959, null, 1]
1769. 72 ].map(function ([valIn, valExpect, modifier]) {
1770. 144 return [sqlFunc, `${sqlFunc}_YMD`].map(function (sqlFunc) {
1771. 144 return [
1772. 144 sqlFunc,
1773. 144 idateArgNormalize(sqlFunc, valIn, "input"),
1774. 144 idateArgNormalize(sqlFunc, valExpect, "expect"),
1775. 144 modifier
1776. 144 ];
1777. 144 });
1778. 72 }).flat();
1779. 6 }).flat());
1780. 978 await Promise.all(promiseList.flat().map(async function ([
1781. 978 sqlFunc, valIn, valExpect, modifier
1782. 978 ], ii) {
1783. 978 let sql;
1784. 978 let sqlFunc2 = sqlFunc.replace((/_JULIAN|_TEXT|_YMD/g), "");
1785. 978 let valActual;
1786. 978 sql = String(
1787. 978 modifier === undefined
1788. 848 ? `SELECT ${sqlFunc2}($valIn, '+0 SECOND');`
1789. 130 : typeof modifier === "number"
1790. 130 ? (
1791. 130 (/^IDATEYMDFROM|YMD$/).test(sqlFunc)
1792. 130 ? `SELECT ${sqlFunc2}($valIn, '${modifier} DAY');`
1793. 130 : `SELECT ${sqlFunc2}($valIn, '${modifier} SECOND');`
1794. 130 )
1795. 130 : `SELECT ${sqlFunc2}($valIn, '${modifier}');`
1796. 978 ).replace(")(", ", ");
1797. 978 valActual = (
1798. 978 await dbExecAndReturnLastValue({
1799. 978 bindList: {
1800. 978 valIn
1801. 978 },
1802. 978 db,
1803. 978 sql
1804. 978 })
1805. 978 );
1806. 978 assertJsonEqual(valActual, valExpect, {
1807. 978 ii,
1808. 978 modifier,
1809. 978 sql,
1810. 978 sqlFunc,
1811. 978 valActual,
1812. 978 valExpect,
1813. 978 valIn
1814. 978 });
1815. 978 }));
1816. 1 });
1817. 1 jstestIt((
1818. 1 "test sqlite-extension-math handling-behavior"
1819. 1 ), async function test_sqlite_extension_math() {
1820. 1 let db = await dbOpenAsync({filename: ":memory:"});
1821. 1 // test sqlmath-defined-func handling-behavior
1822. 1 Object.entries({
1823. 1 "''": {
1824. 1 "CASTREALORNULL": 0,
1825. 1 "CASTREALORZERO": 0,
1826. 1 "CASTTEXTOREMPTY": "",
1827. 1 "COPYBLOB": "",
1828. 1 "SHA256": (
1829. 1 "E3B0C44298FC1C149AFBF4C8996FB924"
1830. 1 + "27AE41E4649B934CA495991B7852B855"
1831. 1 )
1832. 1 },
1833. 1 "'-0.5'": {
1834. 1 "CASTREALORNULL": -0.5,
1835. 1 "CASTREALORZERO": -0.5,
1836. 1 "CASTTEXTOREMPTY": "-0.5",
1837. 1 "COPYBLOB": "-0.5",
1838. 1 "SHA256": (
1839. 1 "1B07B0CFFA0B3F596B5E048B01151688"
1840. 1 + "86CC5183DD518655B5515EE5DDDAC6D1"
1841. 1 )
1842. 1 },
1843. 1 "'-1'": {
1844. 1 "CASTREALORNULL": -1,
1845. 1 "CASTREALORZERO": -1,
1846. 1 "CASTTEXTOREMPTY": "-1",
1847. 1 "COPYBLOB": "-1",
1848. 1 "COT": -0.642092615934331,
1849. 1 "COTH": -1.31303528549933,
1850. 1 "SHA256": (
1851. 1 "1BAD6B8CF97131FCEAB8543E81F77571"
1852. 1 + "95FBB1D36B376EE994AD1CF17699C464"
1853. 1 ),
1854. 1 "SIGN": -1,
1855. 1 "SQRTWITHSIGN": -1
1856. 1 },
1857. 1 "'0'": {
1858. 1 "CASTREALORNULL": 0,
1859. 1 "CASTREALORZERO": 0,
1860. 1 "CASTTEXTOREMPTY": "0",
1861. 1 "COPYBLOB": "0",
1862. 1 "COT": null,
1863. 1 "COTH": null,
1864. 1 "SHA256": (
1865. 1 "5FECEB66FFC86F38D952786C6D696C79"
1866. 1 + "C2DBC239DD4E91B46729D73A27FB57E9"
1867. 1 ),
1868. 1 "SIGN": 0,
1869. 1 "SQRTWITHSIGN": 0
1870. 1 },
1871. 1 "'0.5'": {
1872. 1 "CASTREALORNULL": 0.5,
1873. 1 "CASTREALORZERO": 0.5,
1874. 1 "CASTTEXTOREMPTY": "0.5",
1875. 1 "COPYBLOB": "0.5",
1876. 1 "SHA256": (
1877. 1 "D2CBAD71FF333DE67D07EC676E352AB7"
1878. 1 + "F38248EB69C942950157220607C55E84"
1879. 1 )
1880. 1 },
1881. 1 "'1'": {
1882. 1 "CASTREALORNULL": 1,
1883. 1 "CASTREALORZERO": 1,
1884. 1 "CASTTEXTOREMPTY": "1",
1885. 1 "COPYBLOB": "1",
1886. 1 "COT": 0.642092615934331,
1887. 1 "COTH": 1.31303528549933,
1888. 1 "SHA256": (
1889. 1 "6B86B273FF34FCE19D6B804EFF5A3F57"
1890. 1 + "47ADA4EAA22F1D49C01E52DDB7875B4B"
1891. 1 ),
1892. 1 "SIGN": 1,
1893. 1 "SQRTWITHSIGN": 1
1894. 1 },
1895. 1 "'aa'": {
1896. 1 "CASTREALORNULL": 0,
1897. 1 "CASTREALORZERO": 0,
1898. 1 "CASTTEXTOREMPTY": "aa",
1899. 1 "COPYBLOB": "aa",
1900. 1 "SHA256": (
1901. 1 "961B6DD3EDE3CB8ECBAACBD68DE040CD"
1902. 1 + "78EB2ED5889130CCEB4C49268EA4D506"
1903. 1 )
1904. 1 },
1905. 1 "'abc'": {
1906. 1 "SHA256": (
1907. 1 "BA7816BF8F01CFEA414140DE5DAE2223"
1908. 1 + "B00361A396177A9CB410FF61F20015AD"
1909. 1 )
1910. 1 },
1911. 1 "'abcdbcdecdefdefgefghfghighijhijkijkljklmklmnlmnomnopnopq'": {
1912. 1 "SHA256": (
1913. 1 "248D6A61D20638B8E5C026930C3E6039"
1914. 1 + "A33CE45964FF2167F6ECEDD419DB06C1"
1915. 1 )
1916. 1 },
1917. 1 "'hello'": {
1918. 1 "CASTREALORNULL": 0,
1919. 1 "CASTREALORZERO": 0,
1920. 1 "CASTTEXTOREMPTY": "hello",
1921. 1 "COPYBLOB": "hello",
1922. 1 "SHA256": (
1923. 1 "2CF24DBA5FB0A30E26E83B2AC5B9E29E"
1924. 1 + "1B161E5C1FA7425E73043362938B9824"
1925. 1 )
1926. 1 },
1927. 1 "-0.5": {
1928. 1 "CASTREALORNULL": -0.5,
1929. 1 "CASTREALORZERO": -0.5,
1930. 1 "CASTTEXTOREMPTY": "-0.5",
1931. 1 "COPYBLOB": -0.5,
1932. 1 "SHA256": (
1933. 1 "1B07B0CFFA0B3F596B5E048B01151688"
1934. 1 + "86CC5183DD518655B5515EE5DDDAC6D1"
1935. 1 )
1936. 1 },
1937. 1 "-0x7fffffffffffffff": {
1938. 1 "COT": -0.0118008981305845,
1939. 1 "COTH": -1,
1940. 1 "SHA256": ( // '-9223372036854775807'
1941. 1 "B7AE81320053F61245ED2D36E72E1D05"
1942. 1 + "AD4235D8C784E60285F1EB1F06DA7845"
1943. 1 ),
1944. 1 "SIGN": -1,
1945. 1 "SQRTWITHSIGN": -3037000499.97605
1946. 1 },
1947. 1 "-1": {
1948. 1 "CASTREALORNULL": -1,
1949. 1 "CASTREALORZERO": -1,
1950. 1 "CASTTEXTOREMPTY": "-1",
1951. 1 "COPYBLOB": -1,
1952. 1 "COT": -0.642092615934331,
1953. 1 "COTH": -1.31303528549933,
1954. 1 "SHA256": (
1955. 1 "1BAD6B8CF97131FCEAB8543E81F77571"
1956. 1 + "95FBB1D36B376EE994AD1CF17699C464"
1957. 1 ),
1958. 1 "SIGN": -1,
1959. 1 "SQRTWITHSIGN": -1
1960. 1 },
1961. 1 "-1e999": {
1962. 1 "COT": null,
1963. 1 "COTH": -1,
1964. 1 "SHA256": ( // '-Inf'
1965. 1 "8C1FB05600CEB1FF74474E66DDD603F5"
1966. 1 + "FE8C839B03598A124E2AACB6A08C8837"
1967. 1 ),
1968. 1 "SIGN": -1,
1969. 1 "SQRTWITHSIGN": null
1970. 1 },
1971. 1 "0": {
1972. 1 "CASTREALORNULL": 0,
1973. 1 "CASTREALORZERO": 0,
1974. 1 "CASTTEXTOREMPTY": "0",
1975. 1 "COPYBLOB": 0,
1976. 1 "COT": null,
1977. 1 "COTH": null,
1978. 1 "SHA256": (
1979. 1 "5FECEB66FFC86F38D952786C6D696C79"
1980. 1 + "C2DBC239DD4E91B46729D73A27FB57E9"
1981. 1 ),
1982. 1 "SIGN": 0,
1983. 1 "SQRTWITHSIGN": 0
1984. 1 },
1985. 1 "0.5": {
1986. 1 "CASTREALORNULL": 0.5,
1987. 1 "CASTREALORZERO": 0.5,
1988. 1 "CASTTEXTOREMPTY": "0.5",
1989. 1 "COPYBLOB": 0.5,
1990. 1 "SHA256": (
1991. 1 "D2CBAD71FF333DE67D07EC676E352AB7"
1992. 1 + "F38248EB69C942950157220607C55E84"
1993. 1 )
1994. 1 },
1995. 1 "0.5, 0.5": {
1996. 1 "ROUNDORZERO": 1
1997. 1 },
1998. 1 "0.5, 1": {
1999. 1 "ROUNDORZERO": 0.5
2000. 1 },
2001. 1 "0.5, NULL": {
2002. 1 "ROUNDORZERO": 1
2003. 1 },
2004. 1 "0x7fffffffffffffff": {
2005. 1 "COT": 0.0118008981305845,
2006. 1 "COTH": 1,
2007. 1 "SHA256": ( // '9223372036854775807'
2008. 1 "B34A1C30A715F6BF8B7243AFA7FAB883"
2009. 1 + "CE3612B7231716BDCBBDC1982E1AED29"
2010. 1 ),
2011. 1 "SIGN": 1,
2012. 1 "SQRTWITHSIGN": 3037000499.97605
2013. 1 },
2014. 1 "0x8000000000000000": {
2015. 1 "COT": -0.0118008981305845,
2016. 1 "COTH": -1,
2017. 1 "SHA256": ( // '-9223372036854775808'
2018. 1 "85386477F3AF47E4A0B308EE3B3A688D"
2019. 1 + "F16E8B2228105DD7D4DCD42A9807CB78"
2020. 1 ),
2021. 1 "SIGN": -1,
2022. 1 "SQRTWITHSIGN": -3037000499.97605
2023. 1 },
2024. 1 "0xffffffffffffffff": {
2025. 1 "COT": -0.642092615934331,
2026. 1 "COTH": -1.31303528549933,
2027. 1 "SHA256": ( // '-1'
2028. 1 "1BAD6B8CF97131FCEAB8543E81F77571"
2029. 1 + "95FBB1D36B376EE994AD1CF17699C464"
2030. 1 ),
2031. 1 "SIGN": -1,
2032. 1 "SQRTWITHSIGN": -1
2033. 1 },
2034. 1 "1": {
2035. 1 "CASTREALORNULL": 1,
2036. 1 "CASTREALORZERO": 1,
2037. 1 "CASTTEXTOREMPTY": "1",
2038. 1 "COPYBLOB": 1,
2039. 1 "COT": 0.642092615934331,
2040. 1 "COTH": 1.31303528549933,
2041. 1 "SHA256": (
2042. 1 "6B86B273FF34FCE19D6B804EFF5A3F57"
2043. 1 + "47ADA4EAA22F1D49C01E52DDB7875B4B"
2044. 1 ),
2045. 1 "SIGN": 1,
2046. 1 "SQRTWITHSIGN": 1
2047. 1 },
2048. 1 "1e999": {
2049. 1 "CASTREALORNULL": null,
2050. 1 "CASTREALORZERO": 0,
2051. 1 "COT": null,
2052. 1 "COTH": 1,
2053. 1 "SHA256": ( // 'Inf'
2054. 1 "1DAEC9C71EE2A842CDEE6977AD8C562E"
2055. 1 + "D4AA4FB1338BECD25D79A104B473D9D8"
2056. 1 ),
2057. 1 "SIGN": 1,
2058. 1 "SQRTWITHSIGN": null
2059. 1 },
2060. 1 "NULL": {
2061. 1 "CASTREALORNULL": null,
2062. 1 "CASTREALORZERO": 0,
2063. 1 "CASTTEXTOREMPTY": "",
2064. 1 "COPYBLOB": null,
2065. 1 "COT": null,
2066. 1 "COTH": null,
2067. 1 "SHA256": "",
2068. 1 "SIGN": null,
2069. 1 "SQRTWITHSIGN": null
2070. 1 },
2071. 1 "NULL, 0": {
2072. 1 "ROUNDORZERO": 0
2073. 1 },
2074. 1 "NULL, 0.5": {
2075. 1 "ROUNDORZERO": 0
2076. 1 },
2077. 1 "NULL, NULL": {
2078. 1 "ROUNDORZERO": 0
2079. 1 },
2080. 1 "ZEROBLOB(0)": {
2081. 1 "CASTREALORNULL": 0,
2082. 1 "CASTREALORZERO": 0,
2083. 1 "CASTTEXTOREMPTY": "",
2084. 1 "COPYBLOB": null,
2085. 1 "SHA256": (
2086. 1 "E3B0C44298FC1C149AFBF4C8996FB924"
2087. 1 + "27AE41E4649B934CA495991B7852B855"
2088. 1 )
2089. 1 },
2090. 1 "ZEROBLOB(1)": {
2091. 1 "CASTREALORNULL": 0,
2092. 1 "CASTREALORZERO": 0,
2093. 1 "CASTTEXTOREMPTY": "",
2094. 1 "COPYBLOB": null,
2095. 1 "SHA256": (
2096. 1 "6E340B9CFFB37A989CA544E6BB780A2C"
2097. 1 + "78901D3FB33738768511A30617AFA01D"
2098. 1 )
2099. 1 }
2100. 30 }).forEach(function ([
2101. 30 arg, funcDict
2102. 30 ]) {
2103. 148 Object.entries(funcDict).forEach(async function ([
2104. 148 func, valExpect
2105. 148 ]) {
2106. 148 let sql;
2107. 148 let valActual;
2108. 148 sql = (
2109. 148 func === "SHA256"
2110. 24 ? `SELECT HEX(${func}(${arg}))`
2111. 124 : `SELECT ${func}(${arg})`
2112. 148 );
2113. 148 valActual = noop(
2114. 148 await dbExecAndReturnLastValue({
2115. 148 db,
2116. 148 sql
2117. 148 })
2118. 148 );
2119. 148 assertJsonEqual(valActual, valExpect, {
2120. 148 sql,
2121. 148 valExpect
2122. 148 });
2123. 148 });
2124. 30 });
2125. 1 });
2126. 1 jstestIt((
2127. 1 "test sqlite-extension-quantile handling-behavior"
2128. 1 ), async function test_sqlite_extension_quantile() {
2129. 1 let db = await dbOpenAsync({
2130. 1 filename: ":memory:"
2131. 1 });
2132. 1 await (async function () {
2133. 1 let valActual = await dbExecAndReturnLastTable({
2134. 1 db,
2135. 1 sql: (`
2136. 1-- test null-case handling-behavior
2137. 1DROP TABLE IF EXISTS __tmp1;
2138. 1CREATE TEMP TABLE __tmp1 (val REAL);
2139. 1SELECT
2140. 1 1 AS id,
2141. 1 MEDIAN2(val) AS mdn,
2142. 1 QUANTILE(val, 0.5) AS qnt,
2143. 1 STDEV(val) AS std
2144. 1 FROM __tmp1;
2145. 1 `)
2146. 1 });
2147. 1 assertJsonEqual(
2148. 1 valActual,
2149. 1 [{id: 1, mdn: null, qnt: null, std: null}]
2150. 1 );
2151. 1 }());
2152. 1 await Promise.all([
2153. 1 [
2154. 1 [[], -99, undefined],
2155. 1 [[], 0, undefined],
2156. 1 [[], 0.5, undefined],
2157. 1 [[], 1, undefined],
2158. 1 [[], 99, undefined],
2159. 1 [[undefined, undefined, 1, 1, 2, 3, 4], 0.5, 2],
2160. 1 [[undefined, undefined, 1, 2, 3, 4], 0.5, 2.5],
2161. 1 [[undefined], 0.5, undefined]
2162. 1 ],
2163. 1 [
2164. 1 [[], -99, 1],
2165. 1 [[], 0, 1],
2166. 1 [[], 0.125, 1.875],
2167. 1 [[], 0.250, 2.75],
2168. 1 [[], 0.375, 3.625],
2169. 1 [[], 0.500, 4.5],
2170. 1 [[], 0.625, 5.375],
2171. 1 [[], 0.750, 6.25],
2172. 1 [[], 0.875, 7.125],
2173. 1 [[], 1, 8],
2174. 1 [[], 99, 8],
2175. 1 [[0.1], 0, 0.1],
2176. 1 [[1.1], 0.125, 1.1],
2177. 1 [[2.1], 0.250, 2.1],
2178. 1 [[3.1], 0.375, 3.1],
2179. 1 [[4.1], 0.500, 4.1],
2180. 1 [[4.1], 0.625, 5],
2181. 1 [[5.1], 0.750, 6],
2182. 1 [[6.1], 0.875, 7],
2183. 1 [[7.1], 1, 8],
2184. 1 [[0], 0, 0],
2185. 1 [[1], 0.125, 1],
2186. 1 [[2], 0.250, 2],
2187. 1 [[3], 0.375, 3],
2188. 1 [[4], 0.500, 4],
2189. 1 [[5], 0.625, 5],
2190. 1 [[6], 0.750, 6],
2191. 1 [[7], 0.875, 7],
2192. 1 [[8], 1, 8],
2193. 1 [[], 0, 1]
2194. 30 ].map(function ([
2195. 30 data, qq, valExpect
2196. 30 ]) {
2197. 30 return [
2198. 30 data.concat([
2199. 30 undefined, -Infinity, Infinity, NaN,
2200. 30 "8", 7, 6, "5", "4", 3, 2, "1",
2201. 30 undefined
2202. 30 ]),
2203. 30 qq,
2204. 30 valExpect
2205. 30 ];
2206. 30 })
2207. 38 ].flat().map(async function ([
2208. 38 data, qq, valExpect
2209. 38 ]) {
2210. 38 let avg = 0;
2211. 38 let data2;
2212. 38 let valExpectMdn;
2213. 38 let valExpectStd = 0;
2214. 422 data2 = data.map(function (elem) {
2215. 422 return Number(elem);
2216. 422 }).filter(function (elem) {
2217. 422 return Number.isFinite(elem);
2218. 422 }).sort();
2219. 38 valExpectMdn = (
2220. 38 data2.length % 2 === 0
2221. 19 ? 0.5 * (
2222. 19 data2[0.5 * data2.length - 1] + data2[0.5 * data2.length]
2223. 19 )
2224. 19 : data2[0.5 * (data2.length - 1)]
2225. 38 );
2226. 267 data2.forEach(function (elem) {
2227. 267 avg += elem;
2228. 267 });
2229. 38 avg *= 1 / data2.length;
2230. 267 data2.forEach(function (elem) {
2231. 267 valExpectStd += (elem - avg) ** 2;
2232. 267 });
2233. 38 valExpectStd = (
2234. 38 data2.length <= 0
2235. 6 ? null
2236. 38 // : data2.length === 1
2237. 38 // ? 0
2238. 32 : Number(Math.sqrt(
2239. 32 valExpectStd / (data2.length - 1)
2240. 32 ).toFixed(8))
2241. 38 );
2242. 38 await Promise.all([
2243. 38 data,
2244. 38 Array.from(data).reverse()
2245. 76 ].map(async function (data) {
2246. 76 let valActual;
2247. 76 valActual = await dbExecAndReturnLastRow({
2248. 76 bindList: {
2249. 76 tmp1: JSON.stringify(data)
2250. 76 },
2251. 76 db,
2252. 76 sql: (`
2253. 76-- test null-case handling-behavior
2254. 76SELECT QUANTILE(value, ${qq}) AS qnt FROM JSON_EACH($tmp1) WHERE 0;
2255. 76-- test last-row handling-behavior
2256. 76SELECT
2257. 76 MEDIAN2(value) AS mdn,
2258. 76 QUANTILE(value, ${qq}) AS qnt,
2259. 76 ROUND(stdev(value), 8) AS std
2260. 76 FROM JSON_EACH($tmp1);
2261. 76 `)
2262. 76 });
2263. 76 assertJsonEqual(
2264. 76 valActual,
2265. 76 {
2266. 76 mdn: valExpectMdn,
2267. 12 qnt: valExpect ?? null,
2268. 76 std: valExpectStd
2269. 76 },
2270. 76 {
2271. 76 data,
2272. 76 qq,
2273. 76 valActual,
2274. 76 valExpect,
2275. 76 valExpectMdn,
2276. 76 valExpectStd
2277. 76 }
2278. 76 );
2279. 76 }));
2280. 38 }));
2281. 1 });
2282. 1 jstestIt((
2283. 1 "test sqlite-extension-win_avgx handling-behavior"
2284. 1 ), async function test_sqlite_extension_win_avgx() {
2285. 1 let db = await dbOpenAsync({filename: ":memory:"});
2286. 1 let valIn;
2287. 4 async function test_win_avgx_aggregate({
2288. 4 aa,
2289. 4 bb,
2290. 4 valExpect,
2291. 4 valExpect2
2292. 4 }) {
2293. 4 let sqlBetween = "";
2294. 4 let valActual;
2295. 3 if (aa !== undefined) {
2296. 3 sqlBetween = (
2297. 3 `ROWS BETWEEN ${aa - 1} PRECEDING AND ${bb} FOLLOWING`
2298. 3 );
2299. 3 }
2300. 4 // test win_avg1-aggregate handling-behavior
2301. 4 valActual = await dbExecAndReturnLastTable({
2302. 4 bindList: {
2303. 4 valIn: JSON.stringify(valIn)
2304. 4 },
2305. 4 db,
2306. 4 sql: (`
2307. 4SELECT
2308. 4 WIN_AVG1(value->>1) OVER (
2309. 4 ORDER BY value->>0 ASC
2310. 4 ${sqlBetween}
2311. 4 ) AS val
2312. 4 FROM JSON_EAcH($valIn);
2313. 4 `)
2314. 4 });
2315. 48 valActual = valActual.map(function ({val}) {
2316. 48 return Number(val.toFixed(4));
2317. 48 });
2318. 4 assertJsonEqual(valActual, valExpect);
2319. 4 // test win_avg2-aggregate handling-behavior
2320. 4 valActual = await dbExecAndReturnLastTable({
2321. 4 bindList: {
2322. 4 valIn: JSON.stringify(valIn)
2323. 4 },
2324. 4 db,
2325. 4 sql: (`
2326. 4SELECT
2327. 4 id2,
2328. 4 DOUBLEARRAY_JSONTO(WIN_AVG2(
2329. 4 value->>1,
2330. 4 value->>1,
2331. 4 value->>1,
2332. 4 value->>1,
2333. 4 value->>1,
2334. 4 value->>1,
2335. 4 value->>1,
2336. 4 value->>1,
2337. 4 value->>1,
2338. 4 IIF(id2 = 1, -1, value->>1)
2339. 4 ) OVER (
2340. 4 ORDER BY value->>0 ASC
2341. 4 ${sqlBetween}
2342. 4 )) AS val
2343. 4 FROM (
2344. 4 SELECT
2345. 4 *,
2346. 4 ROW_NUMBER() OVER(ORDER BY id ASC) AS id2
2347. 4 FROM JSON_EAcH($valIn)
2348. 4 );
2349. 4 `)
2350. 4 });
2351. 48 valActual = valActual.map(function ({val}, ii, list) {
2352. 480 val = JSON.parse(val).map(function (elem, jj) {
2353. 480 elem = Number(elem.toFixed(4));
2354. 240 if (ii + (bb || 0) + 1 >= list.length && jj === 9) {
2355. 8 assertJsonEqual(elem, valExpect2, valActual);
2356. 472 } else {
2357. 472 assertJsonEqual(elem, valExpect[ii], valActual);
2358. 472 }
2359. 480 return elem;
2360. 480 });
2361. 48 return val[0];
2362. 48 });
2363. 4 assertJsonEqual(valActual, valExpect);
2364. 4 }
2365. 1 valIn = [
2366. 1 [11, NaN],
2367. 1 [10, "10"],
2368. 1 [9, 9],
2369. 1 [8, "8"],
2370. 1 [7, 7],
2371. 1 [6, 6],
2372. 1 [5, Infinity],
2373. 1 [4, "4"],
2374. 1 [3, 3],
2375. 1 [2, 2],
2376. 1 [1, "1"],
2377. 1 [0, undefined]
2378. 1 ];
2379. 1 await Promise.all([
2380. 1 (async function () {
2381. 1 let valActual;
2382. 1 // test win_avg2-error handling-behavior
2383. 1 await assertErrorThrownAsync(function () {
2384. 1 return dbExecAsync({
2385. 1 db,
2386. 1 sql: (`
2387. 1SELECT WIN_AVG2() FROM (SELECT 1);
2388. 1 `)
2389. 1 });
2390. 1 }, "wrong number of arguments");
2391. 1 // test win_avg1-null-case handling-behavior
2392. 1 valActual = await dbExecAndReturnLastTable({
2393. 1 db,
2394. 1 sql: (`
2395. 1DROP TABLE IF EXISTS __tmp1;
2396. 1CREATE TEMP TABLE __tmp1 (val REAL);
2397. 1SELECT WIN_AVG1(1) FROM __tmp1;
2398. 1 `)
2399. 1 });
2400. 1 valActual = valActual.map(function ({val}) {
2401. 1 return val;
2402. 1 });
2403. 1 assertJsonEqual(valActual, [null]);
2404. 1 // test win_avg2-null-case handling-behavior
2405. 1 valActual = await dbExecAndReturnLastTable({
2406. 1 db,
2407. 1 sql: (`
2408. 1DROP TABLE IF EXISTS __tmp1;
2409. 1CREATE TEMP TABLE __tmp1 (val REAL);
2410. 1SELECT DOUBLEARRAY_JSONTO(WIN_AVG2(1, 2, 3)) FROM __tmp1;
2411. 1 `)
2412. 1 });
2413. 1 valActual = valActual.map(function ({val}) {
2414. 1 return val;
2415. 1 });
2416. 1 assertJsonEqual(valActual, [null]);
2417. 1 }()),
2418. 1 // test win_avg2-aggregate-normal handling-behavior
2419. 1 test_win_avgx_aggregate({
2420. 1 valExpect: [
2421. 1 0, 0.5, 1, 1.5,
2422. 1 2, 2.3333, 2.8571, 3.375,
2423. 1 3.8889, 4.4, 4.9091, 5.3333
2424. 1 ],
2425. 1 valExpect2: 4.4167
2426. 1 }),
2427. 1 // test win_avg2-aggregate-window handling-behavior
2428. 1 test_win_avgx_aggregate({
2429. 1 aa: 1,
2430. 1 bb: 3,
2431. 1 valExpect: [
2432. 1 1.5, 2.5, 3.25, 4.25,
2433. 1 5.25, 6.25, 7.5, 8.5,
2434. 1 9.25, 9.25, 9.25, 9.25
2435. 1 ],
2436. 1 valExpect2: 6.5
2437. 1 }),
2438. 1 test_win_avgx_aggregate({
2439. 1 aa: 3,
2440. 1 bb: 1,
2441. 1 valExpect: [
2442. 1 0.5, 1, 1.5, 2.5,
2443. 1 3.25, 4.25, 5.25, 6.25,
2444. 1 7.5, 8.5, 9.25, 9.25
2445. 1 ],
2446. 1 valExpect2: 6.5
2447. 1 }),
2448. 1 test_win_avgx_aggregate({
2449. 1 aa: 4,
2450. 1 bb: 0,
2451. 1 valExpect: [
2452. 1 0, 0.5, 1, 1.5,
2453. 1 2.5, 3.25, 4.25, 5.25,
2454. 1 6.25, 7.5, 8.5, 9.25
2455. 1 ],
2456. 1 valExpect2: 6.5
2457. 1 })
2458. 1 ]);
2459. 1 });
2460. 1 jstestIt((
2461. 1 "test sqlite-extension-win_emax handling-behavior"
2462. 1 ), async function test_sqlite_extension_win_emax() {
2463. 1 let db = await dbOpenAsync({filename: ":memory:"});
2464. 1 let valIn;
2465. 4 async function test_win_emax_aggregate({
2466. 4 aa,
2467. 4 bb,
2468. 4 valExpect,
2469. 4 valExpect2
2470. 4 }) {
2471. 4 let alpha = 2 * 1.0 / (4 + 1);
2472. 4 let sqlBetween = "";
2473. 4 let valActual;
2474. 3 if (aa !== undefined) {
2475. 3 sqlBetween = (
2476. 3 `ROWS BETWEEN ${aa - 1} PRECEDING AND ${bb} FOLLOWING`
2477. 3 );
2478. 3 }
2479. 4 // test win_ema1-aggregate handling-behavior
2480. 4 valActual = await dbExecAsync({
2481. 4 bindList: {
2482. 4 valIn: JSON.stringify(valIn)
2483. 4 },
2484. 4 db,
2485. 4 sql: (`
2486. 4SELECT
2487. 4 WIN_EMA1(${alpha}, value->>1) OVER (
2488. 4 ORDER BY value->>0 ASC
2489. 4 ${sqlBetween}
2490. 4 ) AS val
2491. 4 FROM (
2492. 4 SELECT
2493. 4 *,
2494. 4 ROW_NUMBER() OVER(ORDER BY id ASC) AS id2
2495. 4 FROM JSON_EAcH($valIn)
2496. 4 );
2497. 4 `)
2498. 4 });
2499. 48 valActual = valActual[0].map(function ({val}) {
2500. 48 return Number(val.toFixed(4));
2501. 48 });
2502. 4 assertJsonEqual(valActual, valExpect);
2503. 4 // test win_ema2-aggregate handling-behavior
2504. 4 valActual = await dbExecAsync({
2505. 4 bindList: {
2506. 4 valIn: JSON.stringify(valIn)
2507. 4 },
2508. 4 db,
2509. 4 sql: (`
2510. 4SELECT
2511. 4 id2,
2512. 4 DOUBLEARRAY_JSONTO(WIN_EMA2(
2513. 4 ${alpha},
2514. 4 value->>1,
2515. 4 value->>1,
2516. 4 value->>1,
2517. 4 value->>1,
2518. 4 value->>1,
2519. 4 value->>1,
2520. 4 value->>1,
2521. 4 value->>1,
2522. 4 value->>1,
2523. 4 IIF(id2 = 1, -1, value->>1)
2524. 4 ) OVER (
2525. 4 ORDER BY value->>0 ASC
2526. 4 ${sqlBetween}
2527. 4 )) AS val
2528. 4 FROM (
2529. 4 SELECT
2530. 4 *,
2531. 4 ROW_NUMBER() OVER(ORDER BY id ASC) AS id2
2532. 4 FROM JSON_EAcH($valIn)
2533. 4 );
2534. 4 `)
2535. 4 });
2536. 48 valActual = valActual[0].map(function ({val}, ii, list) {
2537. 480 val = JSON.parse(val).map(function (elem, jj) {
2538. 480 elem = Number(elem.toFixed(4));
2539. 240 if (ii + (bb || 0) + 1 >= list.length && jj === 9) {
2540. 8 assertJsonEqual(elem, valExpect2, valActual);
2541. 472 } else {
2542. 472 assertJsonEqual(elem, valExpect[ii], valActual);
2543. 472 }
2544. 480 return elem;
2545. 480 });
2546. 48 return val[0];
2547. 48 });
2548. 4 assertJsonEqual(valActual, valExpect);
2549. 4 }
2550. 1 valIn = [
2551. 1 [11, NaN],
2552. 1 [10, "10"],
2553. 1 [9, 9],
2554. 1 [8, "8"],
2555. 1 [7, 7],
2556. 1 [6, 6],
2557. 1 [5, Infinity],
2558. 1 [4, "4"],
2559. 1 [3, 3],
2560. 1 [2, 2],
2561. 1 [1, "1"],
2562. 1 [0, undefined]
2563. 1 ];
2564. 1 await Promise.all([
2565. 1 (async function () {
2566. 1 let valActual;
2567. 1 // test win_ema2-error handling-behavior
2568. 1 await assertErrorThrownAsync(function () {
2569. 1 return dbExecAsync({
2570. 1 db,
2571. 1 sql: (`
2572. 1SELECT WIN_EMA2(1) FROM (SELECT 1);
2573. 1 `)
2574. 1 });
2575. 1 }, "wrong number of arguments");
2576. 1 await assertErrorThrownAsync(function () {
2577. 1 return dbExecAsync({
2578. 1 db,
2579. 1 sql: (`
2580. 1SELECT WIN_EMA2(NULL, 1) FROM (SELECT 1);
2581. 1 `)
2582. 1 });
2583. 1 }, "invalid argument 'alpha'");
2584. 1 // test win_ema1-null-case handling-behavior
2585. 1 valActual = await dbExecAsync({
2586. 1 db,
2587. 1 sql: (`
2588. 1DROP TABLE IF EXISTS __tmp1;
2589. 1CREATE TEMP TABLE __tmp1 (val REAL);
2590. 1SELECT WIN_EMA1(1, 2) FROM __tmp1;
2591. 1 `)
2592. 1 });
2593. 1 valActual = valActual[0].map(function ({val}) {
2594. 1 return val;
2595. 1 });
2596. 1 assertJsonEqual(valActual, [null]);
2597. 1 // test win_ema2-null-case handling-behavior
2598. 1 valActual = await dbExecAsync({
2599. 1 db,
2600. 1 sql: (`
2601. 1DROP TABLE IF EXISTS __tmp1;
2602. 1CREATE TEMP TABLE __tmp1 (val REAL);
2603. 1SELECT DOUBLEARRAY_JSONTO(WIN_EMA2(1, 2, 3)) FROM __tmp1;
2604. 1 `)
2605. 1 });
2606. 1 valActual = valActual[0].map(function ({val}) {
2607. 1 return val;
2608. 1 });
2609. 1 assertJsonEqual(valActual, [null]);
2610. 1 }()),
2611. 1 // test win_emax-aggregate-normal handling-behavior
2612. 1 test_win_emax_aggregate({
2613. 1 valExpect: [
2614. 1 0.0000, 0.4000, 1.0400, 1.8240,
2615. 1 2.6944, 3.2166, 4.3300, 5.3980,
2616. 1 6.4388, 7.4633, 8.4780, 9.0868
2617. 1 ],
2618. 1 valExpect2: 4.6868
2619. 1 }),
2620. 1 // test win_emax-aggregate-window handling-behavior
2621. 1 test_win_emax_aggregate({
2622. 1 aa: 1,
2623. 1 bb: 3,
2624. 1 valExpect: [
2625. 1 1.824, 2.824, 3.424, 4.584,
2626. 1 5.680, 6.608, 7.824, 8.824,
2627. 1 9.424, 9.424, 9.424, 9.424
2628. 1 ],
2629. 1 valExpect2: 5.024
2630. 1 }),
2631. 1 test_win_emax_aggregate({
2632. 1 aa: 3,
2633. 1 bb: 1,
2634. 1 valExpect: [
2635. 1 0.400, 1.040, 1.824, 2.824,
2636. 1 3.424, 4.584, 5.680, 6.608,
2637. 1 7.824, 8.824, 9.424, 9.424
2638. 1 ],
2639. 1 valExpect2: 5.024
2640. 1 }),
2641. 1 test_win_emax_aggregate({
2642. 1 aa: 4,
2643. 1 bb: 0,
2644. 1 valExpect: [
2645. 1 0.000, 0.400, 1.040, 1.824,
2646. 1 2.824, 3.424, 4.584, 5.680,
2647. 1 6.608, 7.824, 8.824, 9.424
2648. 1 ],
2649. 1 valExpect2: 5.024
2650. 1 })
2651. 1 ]);
2652. 1 });
2653. 1 jstestIt((
2654. 1 "test sqlite-extension-win_quantilex handling-behavior"
2655. 1 ), async function test_sqlite_extension_win_quantilex() {
2656. 1 let db = await dbOpenAsync({filename: ":memory:"});
2657. 1 let valIn;
2658. 14 async function test_win_quantilex_aggregate({
2659. 14 aa,
2660. 14 bb,
2661. 14 quantile,
2662. 14 valExpect,
2663. 14 valExpect2
2664. 14 }) {
2665. 14 let sqlBetween = "";
2666. 14 let valActual;
2667. 7 if (aa !== undefined) {
2668. 7 sqlBetween = (
2669. 7 `ROWS BETWEEN ${aa - 1} PRECEDING AND ${bb} FOLLOWING`
2670. 7 );
2671. 7 }
2672. 14 // test win_quantile1-aggregate handling-behavior
2673. 14 valActual = await dbExecAsync({
2674. 14 bindList: {
2675. 14 valIn: JSON.stringify(valIn)
2676. 14 },
2677. 14 db,
2678. 14 sql: (`
2679. 14SELECT
2680. 14 WIN_QUANTILE1(${quantile}, value->>1) OVER (
2681. 14 ORDER BY value->>0 ASC
2682. 14 ${sqlBetween}
2683. 14 ) AS val
2684. 14 FROM (
2685. 14 SELECT
2686. 14 *,
2687. 14 ROW_NUMBER() OVER(ORDER BY id ASC) AS id2
2688. 14 FROM JSON_EAcH($valIn)
2689. 14 );
2690. 14 `)
2691. 14 });
2692. 168 valActual = valActual[0].map(function ({val}) {
2693. 168 return Number(val.toFixed(4));
2694. 168 });
2695. 14 assertJsonEqual(valActual, valExpect);
2696. 14 // test win_quantile2-aggregate handling-behavior
2697. 14 valActual = await dbExecAndReturnLastTable({
2698. 14 bindList: {
2699. 14 valIn: JSON.stringify(valIn)
2700. 14 },
2701. 14 db,
2702. 14 sql: (`
2703. 14SELECT
2704. 14 id2,
2705. 14 DOUBLEARRAY_JSONTO(WIN_QUANTILE2(
2706. 14 ${quantile}, value->>1,
2707. 14 ${quantile}, value->>1,
2708. 14 ${quantile}, value->>1,
2709. 14 ${quantile}, value->>1,
2710. 14 ${quantile}, value->>1,
2711. 14 ${quantile}, value->>1,
2712. 14 ${quantile}, value->>1,
2713. 14 ${quantile}, value->>1,
2714. 14 ${quantile}, value->>1,
2715. 14 ${quantile}, IIF(id2 = 1, -1, value->>1)
2716. 14 ) OVER (
2717. 14 ORDER BY value->>0 ASC
2718. 14 ${sqlBetween}
2719. 14 )) AS val
2720. 14 FROM (
2721. 14 SELECT
2722. 14 *,
2723. 14 ROW_NUMBER() OVER(ORDER BY id ASC) AS id2
2724. 14 FROM JSON_EAcH($valIn)
2725. 14 );
2726. 14 `)
2727. 14 });
2728. 168 valActual = valActual.map(function ({val}, ii, list) {
2729. 1680 val = JSON.parse(val).map(function (elem, jj) {
2730. 1680 elem = Number(elem.toFixed(4));
2731. 140 if (ii + (bb || 0) + 1 >= list.length && jj === 9) {
2732. 14 assertJsonEqual(elem, valExpect2, valActual);
2733. 1666 } else {
2734. 1666 assertJsonEqual(elem, valExpect[ii], valActual);
2735. 1666 }
2736. 1680 return elem;
2737. 1680 });
2738. 168 return val[0];
2739. 168 });
2740. 14 assertJsonEqual(valActual, valExpect);
2741. 14 }
2742. 1 valIn = [
2743. 1 [12, 11],
2744. 1 [11, 10],
2745. 1 [10, 9],
2746. 1 [9, 8],
2747. 1 [8, NaN],
2748. 1 [7, 6],
2749. 1 [6, "abcd"],
2750. 1 [5, 4],
2751. 1 [4, 3],
2752. 1 [3, "2"],
2753. 1 [2, "1"],
2754. 1 [1, undefined]
2755. 1 ];
2756. 1 await Promise.all([
2757. 1 (async function () {
2758. 1 let valActual;
2759. 1 // test win_quantile2-error handling-behavior
2760. 1 await assertErrorThrownAsync(function () {
2761. 1 return dbExecAsync({
2762. 1 db,
2763. 1 sql: (`
2764. 1SELECT WIN_QUANTILE2(1) FROM (SELECT 1);
2765. 1 `)
2766. 1 });
2767. 1 }, "wrong number of arguments");
2768. 1 await assertErrorThrownAsync(function () {
2769. 1 return dbExecAsync({
2770. 1 db,
2771. 1 sql: (`
2772. 1SELECT WIN_QUANTILE2(NULL, 1) FROM (SELECT 1);
2773. 1 `)
2774. 1 });
2775. 1 }, "argument 'quantile'");
2776. 1 // test win_quantile1-null-case handling-behavior
2777. 1 valActual = await dbExecAndReturnLastTable({
2778. 1 db,
2779. 1 sql: (`
2780. 1DROP TABLE IF EXISTS __tmp1;
2781. 1CREATE TEMP TABLE __tmp1 (val REAL);
2782. 1SELECT WIN_QUANTILE1(1, 2) FROM __tmp1;
2783. 1 `)
2784. 1 });
2785. 1 valActual = valActual.map(function ({val}) {
2786. 1 return val;
2787. 1 });
2788. 1 assertJsonEqual(valActual, [null]);
2789. 1 // test win_quantile2-null-case handling-behavior
2790. 1 valActual = await dbExecAndReturnLastTable({
2791. 1 db,
2792. 1 sql: (`
2793. 1DROP TABLE IF EXISTS __tmp1;
2794. 1CREATE TEMP TABLE __tmp1 (val REAL);
2795. 1SELECT DOUBLEARRAY_JSONTO(WIN_QUANTILE2(1, 2, 3)) FROM __tmp1;
2796. 1 `)
2797. 1 });
2798. 1 valActual = valActual.map(function ({val}) {
2799. 1 return val;
2800. 1 });
2801. 1 assertJsonEqual(valActual, [null]);
2802. 1 }()),
2803. 1 // test win_quantilex-aggregate-normal handling-behavior
2804. 1 test_win_quantilex_aggregate({
2805. 1 quantile: 0,
2806. 1 valExpect: [
2807. 1 0.0000, 0.0000, 0.0000, 0.0000,
2808. 1 0.0000, 0.0000, 0.0000, 0.0000,
2809. 1 0.0000, 0.0000, 0.0000, 0.0000
2810. 1 ],
2811. 1 valExpect2: -1
2812. 1 }),
2813. 1 test_win_quantilex_aggregate({
2814. 1 quantile: 0.25,
2815. 1 valExpect: [
2816. 1 0.0000, 0.2500, 0.5000, 0.7500,
2817. 1 1.0000, 0.2500, 0.5000, 0.7500,
2818. 1 1.0000, 1.2500, 1.5000, 1.7500
2819. 1 ],
2820. 1 valExpect2: 0.7500
2821. 1 }),
2822. 1 test_win_quantilex_aggregate({
2823. 1 quantile: 0.33333333,
2824. 1 valExpect: [
2825. 1 0.0000, 0.3333, 0.6667, 1.0000,
2826. 1 1.3333, 0.6667, 1.0000, 1.3333,
2827. 1 1.6667, 2.0000, 2.3333, 2.6667
2828. 1 ],
2829. 1 valExpect2: 1.6667
2830. 1 }),
2831. 1 test_win_quantilex_aggregate({
2832. 1 quantile: 0.5,
2833. 1 valExpect: [
2834. 1 0.0000, 0.5000, 1.0000, 1.5000,
2835. 1 2.0000, 1.5000, 2.0000, 2.5000,
2836. 1 3.0000, 3.5000, 4.0000, 5.0000
2837. 1 ],
2838. 1 valExpect2: 3.5000
2839. 1 }),
2840. 1 test_win_quantilex_aggregate({
2841. 1 quantile: 0.66666667,
2842. 1 valExpect: [
2843. 1 0.0000, 0.6667, 1.3333, 2.0000,
2844. 1 2.6667, 2.3333, 3.0000, 3.6667,
2845. 1 4.6667, 6.0000, 6.0000, 6.6667
2846. 1 ],
2847. 1 valExpect2: 6.0000
2848. 1 }),
2849. 1 test_win_quantilex_aggregate({
2850. 1 quantile: 0.75,
2851. 1 valExpect: [
2852. 1 0.0000, 0.7500, 1.5000, 2.2500,
2853. 1 3.0000, 2.7500, 3.5000, 4.5000,
2854. 1 6.0000, 6.0000, 7.0000, 8.2500
2855. 1 ],
2856. 1 valExpect2: 6.5000
2857. 1 }),
2858. 1 test_win_quantilex_aggregate({
2859. 1 quantile: 1,
2860. 1 valExpect: [
2861. 1 0.0000, 1.0000, 2.0000, 3.0000,
2862. 1 4.0000, 4.0000, 6.0000, 6.0000,
2863. 1 8.0000, 9.0000, 10.0000, 11.0000
2864. 1 ],
2865. 1 valExpect2: 10.0000
2866. 1 }),
2867. 1 // test win_quantilex-aggregate-window handling-behavior
2868. 1 test_win_quantilex_aggregate({
2869. 1 aa: 8,
2870. 1 bb: 0,
2871. 1 quantile: 0,
2872. 1 valExpect: [
2873. 1 0.0000, 0.0000, 0.0000, 0.0000,
2874. 1 0.0000, 0.0000, 0.0000, 0.0000,
2875. 1 0.0000, 0.0000, 0.0000, 0.0000
2876. 1 ],
2877. 1 valExpect2: -1
2878. 1 }),
2879. 1 test_win_quantilex_aggregate({
2880. 1 aa: 8,
2881. 1 bb: 0,
2882. 1 quantile: 0.25,
2883. 1 valExpect: [
2884. 1 0.0000, 0.2500, 0.5000, 0.7500,
2885. 1 1.0000, 0.2500, 0.5000, 0.7500,
2886. 1 1.7500, 2.7500, 3.7500, 5.5000
2887. 1 ],
2888. 1 valExpect2: 3
2889. 1 }),
2890. 1 test_win_quantilex_aggregate({
2891. 1 aa: 8,
2892. 1 bb: 0,
2893. 1 quantile: 0.33333333,
2894. 1 valExpect: [
2895. 1 0.0000, 0.3333, 0.6667, 1.0000,
2896. 1 1.3333, 0.6667, 1.0000, 1.3333,
2897. 1 2.3333, 3.3333, 4.6667, 6.0000
2898. 1 ],
2899. 1 valExpect2: 4.6667
2900. 1 }),
2901. 1 test_win_quantilex_aggregate({
2902. 1 aa: 8,
2903. 1 bb: 0,
2904. 1 quantile: 0.5000,
2905. 1 valExpect: [
2906. 1 0.0000, 0.5000, 1.0000, 1.5000,
2907. 1 2.0000, 1.5000, 2.0000, 2.5000,
2908. 1 3.5000, 5.0000, 6.0000, 7.0000
2909. 1 ],
2910. 1 valExpect2: 6.0000
2911. 1 }),
2912. 1 test_win_quantilex_aggregate({
2913. 1 aa: 8,
2914. 1 bb: 0,
2915. 1 quantile: 0.66666667,
2916. 1 valExpect: [
2917. 1 0.0000, 0.6667, 1.3333, 2.0000,
2918. 1 2.6667, 2.3333, 3.0000, 3.6667,
2919. 1 5.3333, 6.0000, 7.3333, 8.6667
2920. 1 ],
2921. 1 valExpect2: 7.3333
2922. 1 }),
2923. 1 test_win_quantilex_aggregate({
2924. 1 aa: 8,
2925. 1 bb: 0,
2926. 1 quantile: 0.75,
2927. 1 valExpect: [
2928. 1 0.0000, 0.7500, 1.5000, 2.2500,
2929. 1 3.0000, 2.7500, 3.5000, 4.5000,
2930. 1 6.0000, 6.5000, 8.2500, 9.2500
2931. 1 ],
2932. 1 valExpect2: 8.2500
2933. 1 }),
2934. 1 test_win_quantilex_aggregate({
2935. 1 aa: 8,
2936. 1 bb: 0,
2937. 1 quantile: 1.0000,
2938. 1 valExpect: [
2939. 1 0.0000, 1.0000, 2.0000, 3.0000,
2940. 1 4.0000, 4.0000, 6.0000, 6.0000,
2941. 1 8.0000, 9.0000, 10.0000, 11.0000
2942. 1 ],
2943. 1 valExpect2: 10.0000
2944. 1 })
2945. 1 ]);
2946. 1 });
2947. 1 jstestIt((
2948. 1 "test sqlite-extension-win_sinefit2 handling-behavior"
2949. 1 ), async function test_sqlite_extension_win_sinefit2() {
2950. 1 let db = await dbOpenAsync({filename: ":memory:"});
2951. 1 let id3 = 9;
2952. 1 let id4 = 10;
2953. 1 let valExpect0;
2954. 1 let valIn;
2955. 6 function sqlSinefitExtractLnr(wsf, ii, suffix) {
2956. 6 return (`
2957. 6 ROUND(sinefit_extract(${wsf}, ${ii}, 'gyy', 0), 8) AS gyy${suffix},
2958. 6 ROUND(sinefit_extract(${wsf}, ${ii}, 'laa', 0), 8) AS laa${suffix},
2959. 6 ROUND(sinefit_extract(${wsf}, ${ii}, 'lbb', 0), 8) AS lbb${suffix},
2960. 6 ROUND(sinefit_extract(${wsf}, ${ii}, 'lee', 0), 8) AS lee${suffix},
2961. 6 ROUND(sinefit_extract(${wsf}, ${ii}, 'lxy', 0), 8) AS lxy${suffix},
2962. 6 ROUND(sinefit_extract(${wsf}, ${ii}, 'lyy', 0), 8) AS lyy${suffix},
2963. 6 ROUND(sinefit_extract(${wsf}, ${ii}, 'mee', 0), 8) AS mee${suffix},
2964. 6 ROUND(sinefit_extract(${wsf}, ${ii}, 'mxe', 0), 8) AS mxe${suffix},
2965. 6 ROUND(sinefit_extract(${wsf}, ${ii}, 'mxx', 0), 8) AS mxx${suffix},
2966. 6 ROUND(sinefit_extract(${wsf}, ${ii}, 'myy', 0), 8) AS myy${suffix},
2967. 6 ROUND(sinefit_extract(${wsf}, ${ii}, 'nnn', 0), 8) AS nnn${suffix},
2968. 6 ROUND(sinefit_extract(${wsf}, ${ii}, 'rr0', 0), 8) AS rr0${suffix},
2969. 6 ROUND(sinefit_extract(${wsf}, ${ii}, 'rr1', 0), 8) AS rr1${suffix},
2970. 6 ROUND(sinefit_extract(${wsf}, ${ii}, 'xx1', 0), 8) AS xx1${suffix},
2971. 6 ROUND(sinefit_extract(${wsf}, ${ii}, 'yy1', 0), 8) AS yy1${suffix}
2972. 6 `);
2973. 6 }
2974. 1 async function test_win_sinefit2_aggregate({
2975. 1 aa,
2976. 1 bb,
2977. 1 valExpect,
2978. 1 valExpect2,
2979. 1 valExpect3
2980. 1 }) {
2981. 1 let sqlBetween = "";
2982. 1 let valActual;
2983. 1 let xx2 = 2;
2984. 1 if (aa !== undefined) {
2985. 1 sqlBetween = (
2986. 1 `ROWS BETWEEN ${aa - 1} PRECEDING AND ${bb} FOLLOWING`
2987. 1 );
2988. 1 }
2989. 1 // test win_sinefit2-aggregate handling-behavior
2990. 1 valActual = await dbExecAndReturnLastTable({
2991. 1 bindList: {
2992. 1 valIn
2993. 1 },
2994. 1 db,
2995. 1 sql: (`
2996. 1DROP TABLE IF EXISTS __sinefit_win;
2997. 1CREATE TEMP TABLE __sinefit_win AS
2998. 1 SELECT
2999. 1 id2,
3000. 1 __wsf,
3001. 1 sinefit_extract(__wsf, 0, 'xx1', 0) AS xx11,
3002. 1 sinefit_extract(__wsf, 0, 'yy1', 0) AS yy11,
3003. 1 sinefit_extract(__wsf, 8, 'xx1', 0) AS xx12,
3004. 1 sinefit_extract(__wsf, 8, 'yy1', 0) AS yy12,
3005. 1 sinefit_extract(__wsf, 9, 'xx1', 0) AS xx13,
3006. 1 sinefit_extract(__wsf, 9, 'yy1', 0) AS yy13
3007. 1 FROM (
3008. 1 SELECT
3009. 1 id2,
3010. 1 WIN_SINEFIT2(
3011. 1 1, ${xx2},
3012. 1 value->>0, value->>1,
3013. 1 value->>0, value->>1,
3014. 1 value->>0, value->>1,
3015. 1 value->>0, value->>1,
3016. 1 value->>0, value->>1,
3017. 1 value->>0, value->>1,
3018. 1 value->>0, value->>1,
3019. 1 value->>0, value->>1,
3020. 1 value->>0, value->>1,
3021. 1 value->>0, IIF(id2 = ${id3}, -1, value->>1)
3022. 1 ) OVER (
3023. 1 ORDER BY NULL ASC
3024. 1 ${sqlBetween}
3025. 1 ) AS __wsf
3026. 1 FROM (
3027. 1 SELECT
3028. 1 *,
3029. 1 ROW_NUMBER() OVER(ORDER BY id ASC) AS id2
3030. 1 FROM JSON_EAcH($valIn)
3031. 1 )
3032. 1 );
3033. 1UPDATE __sinefit_win
3034. 1 SET
3035. 1 __wsf = sinefit_refitlast(
3036. 1 __wsf,
3037. 1 0, 0,
3038. 1 0, 0,
3039. 1 0, 0,
3040. 1 0, 0,
3041. 1 0, 0,
3042. 1 0, 0,
3043. 1 0, 0,
3044. 1 0, 0,
3045. 1 0, 0,
3046. 1 0, 0
3047. 1 )
3048. 1 WHERE id2 = ${id4};
3049. 1UPDATE __sinefit_win
3050. 1 SET
3051. 1 __wsf = sinefit_refitlast(
3052. 1 __wsf,
3053. 1 xx11, yy11,
3054. 1 xx11, yy11,
3055. 1 xx11, yy11,
3056. 1 xx11, yy11,
3057. 1 xx11, yy11,
3058. 1 xx11, yy11,
3059. 1 xx11, yy11,
3060. 1 xx11, yy11,
3061. 1 xx12, yy12,
3062. 1 xx13, yy13
3063. 1 )
3064. 1 WHERE id2 = ${id4};
3065. 1SELECT
3066. 1 id2,
3067. 1 ${sqlSinefitExtractLnr("__wsf", 0, "1")},
3068. 1 ${sqlSinefitExtractLnr("__wsf", 8, "2")},
3069. 1 ${sqlSinefitExtractLnr("__wsf", 9, "3")}
3070. 1 FROM __sinefit_win;
3071. 1 `)
3072. 1 });
3073. 10 valActual = valActual.map(function ({
3074. 10 id2,
3075. 10 laa1,
3076. 10 laa2,
3077. 10 laa3,
3078. 10 lbb1,
3079. 10 lbb2,
3080. 10 lbb3,
3081. 10 lee1,
3082. 10 lee2,
3083. 10 lee3,
3084. 10 lxy1,
3085. 10 lxy2,
3086. 10 lxy3,
3087. 10 lyy1,
3088. 10 lyy2,
3089. 10 lyy3,
3090. 10 mee1,
3091. 10 mee2,
3092. 10 mee3,
3093. 10 mxe1,
3094. 10 mxe2,
3095. 10 mxe3,
3096. 10 mxx1,
3097. 10 mxx2,
3098. 10 mxx3,
3099. 10 myy1,
3100. 10 myy2,
3101. 10 myy3,
3102. 10 nnn1,
3103. 10 nnn2,
3104. 10 nnn3,
3105. 10 rr01,
3106. 10 rr02,
3107. 10 rr03,
3108. 10 rr11,
3109. 10 rr12,
3110. 10 rr13,
3111. 10 xx11,
3112. 10 xx12,
3113. 10 xx13,
3114. 10 yy11,
3115. 10 yy12,
3116. 10 yy13
3117. 10 }, ii, list) {
3118. 10 let obj1;
3119. 10 let obj2;
3120. 10 let obj3;
3121. 10 obj1 = {
3122. 10 id2,
3123. 10 "laa": laa1,
3124. 10 "lbb": lbb1,
3125. 10 "lee": lee1,
3126. 10 "lxy": lxy1,
3127. 10 "lyy": lyy1,
3128. 10 "mee": mee1,
3129. 10 "mxe": mxe1,
3130. 10 "mxx": mxx1,
3131. 10 "myy": myy1,
3132. 10 "nnn": nnn1,
3133. 10 "rr0": rr01,
3134. 10 "rr1": rr11,
3135. 10 "xx1": xx11,
3136. 10 "yy1": yy11
3137. 10 };
3138. 10 obj2 = {
3139. 10 id2,
3140. 10 "laa": laa2,
3141. 10 "lbb": lbb2,
3142. 10 "lee": lee2,
3143. 10 "lxy": lxy2,
3144. 10 "lyy": lyy2,
3145. 10 "mee": mee2,
3146. 10 "mxe": mxe2,
3147. 10 "mxx": mxx2,
3148. 10 "myy": myy2,
3149. 10 "nnn": nnn2,
3150. 10 "rr0": rr02,
3151. 10 "rr1": rr12,
3152. 10 "xx1": xx12,
3153. 10 "yy1": yy12
3154. 10 };
3155. 10 obj3 = {
3156. 10 id2,
3157. 10 "laa": laa3,
3158. 10 "lbb": lbb3,
3159. 10 "lee": lee3,
3160. 10 "lxy": lxy3,
3161. 10 "lyy": lyy3,
3162. 10 "mee": mee3,
3163. 10 "mxe": mxe3,
3164. 10 "mxx": mxx3,
3165. 10 "myy": myy3,
3166. 10 "nnn": nnn3,
3167. 10 "rr0": rr03,
3168. 10 "rr1": rr13,
3169. 10 "xx1": xx13,
3170. 10 "yy1": yy13
3171. 10 };
3172. 10 switch (list.length - ii) {
3173. 1 case 1:
3174. 1 assertJsonEqual(obj2, obj1, valActual);
3175. 1 assertJsonEqual(obj3, valExpect3, valActual);
3176. 1 break;
3177. 1 case 2:
3178. 1 assertJsonEqual(obj2, obj1, valActual);
3179. 1 assertJsonEqual(obj3, valExpect2, valActual);
3180. 1 break;
3181. 8 default:
3182. 8 assertJsonEqual(obj2, obj1, valActual);
3183. 8 assertJsonEqual(obj3, obj1, valActual);
3184. 10 }
3185. 10 return obj1;
3186. 10 });
3187. 1 assertJsonEqual(valActual, valExpect);
3188. 1 }
3189. 1 valExpect0 = [
3190. 1 {
3191. 1 "id2": 1,
3192. 1 "laa": null,
3193. 1 "lbb": null,
3194. 1 "lee": null,
3195. 1 "lxy": null,
3196. 1 "lyy": null,
3197. 1 "mee": null,
3198. 1 "mxe": null,
3199. 1 "mxx": 2,
3200. 1 "myy": 0,
3201. 1 "nnn": 1,
3202. 1 "rr0": 0,
3203. 1 "rr1": null,
3204. 1 "xx1": 2,
3205. 1 "yy1": 0
3206. 1 },
3207. 1 {
3208. 1 "id2": 2,
3209. 1 "laa": null,
3210. 1 "lbb": null,
3211. 1 "lee": null,
3212. 1 "lxy": null,
3213. 1 "lyy": null,
3214. 1 "mee": 0.70710678,
3215. 1 "mxe": 0,
3216. 1 "mxx": 2,
3217. 1 "myy": 0.5,
3218. 1 "nnn": 2,
3219. 1 "rr0": 0,
3220. 1 "rr1": null,
3221. 1 "xx1": 2,
3222. 1 "yy1": 1
3223. 1 },
3224. 1 {
3225. 1 "id2": 3,
3226. 1 "laa": -4.5,
3227. 1 "lbb": 2.5,
3228. 1 "lee": 0.40824829,
3229. 1 "lxy": 0.94491118,
3230. 1 "lyy": 3,
3231. 1 "mee": 1.52752523,
3232. 1 "mxe": 0.57735027,
3233. 1 "mxx": 2.33333333,
3234. 1 "myy": 1.33333333,
3235. 1 "nnn": 3,
3236. 1 "rr0": 0,
3237. 1 "rr1": 0,
3238. 1 "xx1": 3,
3239. 1 "yy1": 3
3240. 1 },
3241. 1 {
3242. 1 "id2": 4,
3243. 1 "laa": -3,
3244. 1 "lbb": 1.81818182,
3245. 1 "lee": 0.47673129,
3246. 1 "lxy": 0.95346259,
3247. 1 "lyy": 4.27272727,
3248. 1 "mee": 1.82574186,
3249. 1 "mxe": 0.95742711,
3250. 1 "mxx": 2.75,
3251. 1 "myy": 2,
3252. 1 "nnn": 4,
3253. 1 "rr0": 0,
3254. 1 "rr1": -0.27272727,
3255. 1 "xx1": 4,
3256. 1 "yy1": 4
3257. 1 },
3258. 1 {
3259. 1 "id2": 5,
3260. 1 "laa": -2.29411765,
3261. 1 "lbb": 1.52941176,
3262. 1 "lee": 0.50874702,
3263. 1 "lxy": 0.96164474,
3264. 1 "lyy": 5.35294118,
3265. 1 "mee": 2.07364414,
3266. 1 "mxe": 1.30384048,
3267. 1 "mxx": 3.2,
3268. 1 "myy": 2.6,
3269. 1 "nnn": 5,
3270. 1 "rr0": 0,
3271. 1 "rr1": -0.35294118,
3272. 1 "xx1": 5,
3273. 1 "yy1": 5
3274. 1 },
3275. 1 {
3276. 1 "id2": 6,
3277. 1 "laa": -2.54385965,
3278. 1 "lbb": 1.63157895,
3279. 1 "lee": 0.50725727,
3280. 1 "lxy": 0.97080629,
3281. 1 "lyy": 5.61403509,
3282. 1 "mee": 2.31660671,
3283. 1 "mxe": 1.37840488,
3284. 1 "mxx": 3.5,
3285. 1 "myy": 3.16666667,
3286. 1 "nnn": 6,
3287. 1 "rr0": 0,
3288. 1 "rr1": 0.38596491,
3289. 1 "xx1": 5,
3290. 1 "yy1": 6
3291. 1 },
3292. 1 {
3293. 1 "id2": 7,
3294. 1 "laa": -2.65,
3295. 1 "lbb": 1.675,
3296. 1 "lee": 0.48550416,
3297. 1 "lxy": 0.9752227,
3298. 1 "lyy": 5.725,
3299. 1 "mee": 2.37045304,
3300. 1 "mxe": 1.38013112,
3301. 1 "mxx": 3.71428571,
3302. 1 "myy": 3.57142857,
3303. 1 "nnn": 7,
3304. 1 "rr0": 0,
3305. 1 "rr1": 0.275,
3306. 1 "xx1": 5,
3307. 1 "yy1": 6
3308. 1 },
3309. 1 {
3310. 1 "id2": 8,
3311. 1 "laa": -2.5,
3312. 1 "lbb": 1.625,
3313. 1 "lee": 0.46770717,
3314. 1 "lxy": 0.97991187,
3315. 1 "lyy": 7.25,
3316. 1 "mee": 2.50713268,
3317. 1 "mxe": 1.51185789,
3318. 1 "mxx": 4,
3319. 1 "myy": 4,
3320. 1 "nnn": 8,
3321. 1 "rr0": 0,
3322. 1 "rr1": -0.25,
3323. 1 "xx1": 6,
3324. 1 "yy1": 7
3325. 1 },
3326. 1 {
3327. 1 "id2": 9,
3328. 1 "laa": 0.75,
3329. 1 "lbb": 0.85,
3330. 1 "lee": 0.94207218,
3331. 1 "lxy": 0.89597867,
3332. 1 "lyy": 9.25,
3333. 1 "mee": 2.26778684,
3334. 1 "mxe": 2.39045722,
3335. 1 "mxx": 5,
3336. 1 "myy": 5,
3337. 1 "nnn": 8,
3338. 1 "rr0": 0,
3339. 1 "rr1": -1.25,
3340. 1 "xx1": 10,
3341. 1 "yy1": 8
3342. 1 },
3343. 1 {
3344. 1 "id2": 10,
3345. 1 "laa": 2.75,
3346. 1 "lbb": 0.55,
3347. 1 "lee": 0.8587782,
3348. 1 "lxy": 0.81989159,
3349. 1 "lyy": 3.85,
3350. 1 "mee": 1.60356745,
3351. 1 "mxe": 2.39045722,
3352. 1 "mxx": 5,
3353. 1 "myy": 5.5,
3354. 1 "nnn": 8,
3355. 1 "rr0": -0.87387387,
3356. 1 "rr1": 1.15,
3357. 1 "xx1": 2,
3358. 1 "yy1": 5
3359. 1 }
3360. 1 ];
3361. 1 valIn = [
3362. 1 [2, "abcd"],
3363. 1 [NaN, 1],
3364. 1 [3, 3],
3365. 1 [4, 4],
3366. 1 [5, 5],
3367. 1 [5, 6],
3368. 1 [5, undefined],
3369. 1 [6, 7],
3370. 1 //
3371. 1 [10, 8],
3372. 1 [2, 5]
3373. 1 ];
3374. 1 await Promise.all([
3375. 1 (async function () {
3376. 1 let valActual;
3377. 1 // test win_sinefit2-error handling-behavior
3378. 1 await assertErrorThrownAsync(function () {
3379. 1 return dbExecAsync({
3380. 1 db,
3381. 1 sql: (`
3382. 1SELECT WIN_SINEFIT2(1, 2, 3) FROM (SELECT 1);
3383. 1 `)
3384. 1 });
3385. 1 }, "wrong number of arguments");
3386. 1 // test win_sinefit2-null-case handling-behavior
3387. 1 valActual = await dbExecAndReturnLastTable({
3388. 1 db,
3389. 1 sql: (`
3390. 1DROP TABLE IF EXISTS __tmp1;
3391. 1CREATE TEMP TABLE __tmp1 (val REAL);
3392. 1SELECT DOUBLEARRAY_JSONTO(WIN_SINEFIT2(1, 2, 3, 4)) FROM __tmp1;
3393. 1 `)
3394. 1 });
3395. 1 valActual = valActual.map(function ({val}) {
3396. 1 return val;
3397. 1 });
3398. 1 assertJsonEqual(valActual, [null]);
3399. 1 }()),
3400. 1 // test win_sinefit2-aggregate-normal handling-behavior
3401. 1 (async function () {
3402. 1 let valActual;
3403. 1 valActual = await dbExecAndReturnLastRow({
3404. 1 bindList: {
3405. 1 valIn
3406. 1 },
3407. 1 db,
3408. 1 sql: (`
3409. 1SELECT
3410. 1 ${sqlSinefitExtractLnr("__wsf", 0, "")}
3411. 1 FROM (
3412. 1 SELECT
3413. 1 WIN_SINEFIT2(1, NULL, value->>0, value->>1) AS __wsf
3414. 1 FROM (
3415. 1 SELECT
3416. 1 *,
3417. 1 ROW_NUMBER() OVER(ORDER BY id ASC) AS id2
3418. 1 FROM JSON_EAcH($valIn)
3419. 1 )
3420. 1 );
3421. 1 `)
3422. 1 });
3423. 1 assertJsonEqual(
3424. 1 valActual,
3425. 1 {
3426. 1 "gyy": 0.19611614,
3427. 1 "laa": 0.77941176,
3428. 1 "lbb": 0.84558824,
3429. 1 "lee": 1.40010504,
3430. 1 "lxy": 0.81541829,
3431. 1 "lyy": 2.47058824,
3432. 1 "mee": 2.54950976,
3433. 1 "mxe": 2.45854519,
3434. 1 "mxx": 4.4,
3435. 1 "myy": 4.5,
3436. 1 "nnn": 10,
3437. 1 "rr0": 0,
3438. 1 "rr1": 2.52941176,
3439. 1 "xx1": 2,
3440. 1 "yy1": 5
3441. 1 }
3442. 1 );
3443. 1 }()),
3444. 1 // test win_sinefit2-aggregate-window handling-behavior
3445. 1 (async function () {
3446. 1 let valActual;
3447. 1 let valExpect;
3448. 1 valExpect = {
3449. 1 "gyy": -1.02062073,
3450. 1 "laa": -0.82025678,
3451. 1 "lbb": 0.14621969,
3452. 1 "lee": 2.23885734,
3453. 1 "lxy": 0.865665,
3454. 1 "lyy": 6.63694722,
3455. 1 "mee": 4.89897949,
3456. 1 "mxe": 29.00344807,
3457. 1 "mxx": 74,
3458. 1 "myy": 10,
3459. 1 "nnn": 6,
3460. 1 "rr0": 0,
3461. 1 "rr1": -1.63694722,
3462. 1 "xx1": 51,
3463. 1 "yy1": 5
3464. 1 };
3465. 1 valActual = await dbExecAndReturnLastRow({
3466. 1 db,
3467. 1 sql: (`
3468. 1SELECT
3469. 1 ${sqlSinefitExtractLnr("__wsf", 0, "")}
3470. 1 FROM (
3471. 1 SELECT
3472. 1 WIN_SINEFIT2(1, NULL, xx, yy) AS __wsf
3473. 1 FROM (
3474. 1 SELECT 34 AS xx, 5 AS yy
3475. 1 UNION ALL SELECT 108, 17
3476. 1 UNION ALL SELECT 64, 11
3477. 1 UNION ALL SELECT 88, 8
3478. 1 UNION ALL SELECT 99, 14
3479. 1 UNION ALL SELECT 51, 5
3480. 1 )
3481. 1 )
3482. 1 `)
3483. 1 });
3484. 1 assertJsonEqual(valActual, valExpect);
3485. 1 }()),
3486. 1 // test win_sinefit2-aggregate-window handling-behavior
3487. 1 test_win_sinefit2_aggregate({
3488. 1 aa: 8,
3489. 1 bb: 0,
3490. 1 valExpect: valExpect0,
3491. 1 valExpect2: {
3492. 1 "id2": id3,
3493. 1 "laa": 5.25,
3494. 1 "lbb": -0.275,
3495. 1 "lee": 2.49624718,
3496. 1 "lxy": -0.23918696,
3497. 1 "lyy": 2.5,
3498. 1 "mee": 2.74837614,
3499. 1 "mxe": 2.39045722,
3500. 1 "mxx": 5,
3501. 1 "myy": 3.875,
3502. 1 "nnn": 8,
3503. 1 "rr0": 0,
3504. 1 "rr1": -3.5,
3505. 1 "xx1": 10,
3506. 1 "yy1": -1
3507. 1 },
3508. 1 valExpect3: {
3509. 1 "id2": id4,
3510. 1 "laa": 7.25,
3511. 1 "lbb": -0.575,
3512. 1 "lee": 1.95735791,
3513. 1 "lxy": -0.5490214,
3514. 1 "lyy": 6.1,
3515. 1 "mee": 2.50356888,
3516. 1 "mxe": 2.39045722,
3517. 1 "mxx": 5,
3518. 1 "myy": 4.375,
3519. 1 "nnn": 8,
3520. 1 "rr0": -3.79279279,
3521. 1 "rr1": -1.1,
3522. 1 "xx1": 2,
3523. 1 "yy1": 5
3524. 1 }
3525. 1 }),
3526. 1 // test win_sinefit2-spx handling-behavior
3527. 1 (async function () {
3528. 1 let testDataSpx;
3529. 1 let ttSinefit = 128;
3530. 1 let valActual;
3531. 1 let valExpect;
3532. 1 testDataSpx = (`
3533. 1##
3534. 1date close
3535. 12018-12-31 2506.85 2019-01-02 2510.03 2019-01-03 2447.89 2019-01-04 2531.94
3536. 12019-01-07 2549.69 2019-01-08 2574.41 2019-01-09 2584.96 2019-01-10 2596.64
3537. 12019-01-11 2596.26 2019-01-14 2582.61 2019-01-15 2610.30 2019-01-16 2616.10
3538. 12019-01-17 2635.96 2019-01-18 2670.71 2019-01-22 2632.90 2019-01-23 2638.70
3539. 12019-01-24 2642.33 2019-01-25 2664.76 2019-01-28 2643.85 2019-01-29 2640.00
3540. 12019-01-30 2681.05 2019-01-31 2704.10 2019-02-01 2706.53 2019-02-04 2724.87
3541. 12019-02-05 2737.70 2019-02-06 2731.61 2019-02-07 2706.05 2019-02-08 2707.88
3542. 12019-02-11 2709.80 2019-02-12 2744.73 2019-02-13 2753.03 2019-02-14 2745.73
3543. 12019-02-15 2775.60 2019-02-19 2779.76 2019-02-20 2784.70 2019-02-21 2774.88
3544. 12019-02-22 2792.67 2019-02-25 2796.11 2019-02-26 2793.90 2019-02-27 2792.38
3545. 12019-02-28 2784.49 2019-03-01 2803.69 2019-03-04 2792.81 2019-03-05 2789.65
3546. 12019-03-06 2771.45 2019-03-07 2748.93 2019-03-08 2743.07 2019-03-11 2783.30
3547. 12019-03-12 2791.52 2019-03-13 2810.92 2019-03-14 2808.48 2019-03-15 2822.48
3548. 12019-03-18 2832.94 2019-03-19 2832.57 2019-03-20 2824.23 2019-03-21 2854.88
3549. 12019-03-22 2800.71 2019-03-25 2798.36 2019-03-26 2818.46 2019-03-27 2805.37
3550. 12019-03-28 2815.44 2019-03-29 2834.40 2019-04-01 2867.19 2019-04-02 2867.24
3551. 12019-04-03 2873.40 2019-04-04 2879.39 2019-04-05 2892.74 2019-04-08 2895.77
3552. 12019-04-09 2878.20 2019-04-10 2888.21 2019-04-11 2888.32 2019-04-12 2907.41
3553. 12019-04-15 2905.58 2019-04-16 2907.06 2019-04-17 2900.45 2019-04-18 2905.03
3554. 12019-04-22 2907.97 2019-04-23 2933.68 2019-04-24 2927.25 2019-04-25 2926.17
3555. 12019-04-26 2939.88 2019-04-29 2943.03 2019-04-30 2945.83 2019-05-01 2923.73
3556. 12019-05-02 2917.52 2019-05-03 2945.64 2019-05-06 2932.47 2019-05-07 2884.05
3557. 12019-05-08 2879.42 2019-05-09 2870.72 2019-05-10 2881.40 2019-05-13 2811.87
3558. 12019-05-14 2834.41 2019-05-15 2850.96 2019-05-16 2876.32 2019-05-17 2859.53
3559. 12019-05-20 2840.23 2019-05-21 2864.36 2019-05-22 2856.27 2019-05-23 2822.24
3560. 12019-05-24 2826.06 2019-05-28 2802.39 2019-05-29 2783.02 2019-05-30 2788.86
3561. 12019-05-31 2752.06 2019-06-03 2744.45 2019-06-04 2803.27 2019-06-05 2826.15
3562. 12019-06-06 2843.49 2019-06-07 2873.34 2019-06-10 2886.73 2019-06-11 2885.72
3563. 12019-06-12 2879.84 2019-06-13 2891.64 2019-06-14 2886.98 2019-06-17 2889.67
3564. 12019-06-18 2917.75 2019-06-19 2926.46 2019-06-20 2954.18 2019-06-21 2950.46
3565. 12019-06-24 2945.35 2019-06-25 2917.38 2019-06-26 2913.78 2019-06-27 2924.92
3566. 12019-06-28 2941.76 2019-07-01 2964.33 2019-07-02 2973.01 2019-07-03 2995.82
3567. 12019-07-05 2990.41 2019-07-08 2975.95 2019-07-09 2979.63 2019-07-10 2993.07
3568. 12019-07-11 2999.91 2019-07-12 3013.77 2019-07-15 3014.30 2019-07-16 3004.04
3569. 12019-07-17 2984.42 2019-07-18 2995.11 2019-07-19 2976.61 2019-07-22 2985.03
3570. 12019-07-23 3005.47 2019-07-24 3019.56 2019-07-25 3003.67 2019-07-26 3025.86
3571. 12019-07-29 3020.97 2019-07-30 3013.18 2019-07-31 2980.38 2019-08-01 2953.56
3572. 12019-08-02 2932.05 2019-08-05 2844.74 2019-08-06 2881.77 2019-08-07 2883.98
3573. 12019-08-08 2938.09 2019-08-09 2918.65 2019-08-12 2882.70 2019-08-13 2926.32
3574. 12019-08-14 2840.60 2019-08-15 2847.60 2019-08-16 2888.68 2019-08-19 2923.65
3575. 12019-08-20 2900.51 2019-08-21 2924.43 2019-08-22 2922.95 2019-08-23 2847.11
3576. 12019-08-26 2878.38 2019-08-27 2869.16 2019-08-28 2887.94 2019-08-29 2924.58
3577. 12019-08-30 2926.46 2019-09-03 2906.27 2019-09-04 2937.78 2019-09-05 2976.00
3578. 12019-09-06 2978.71 2019-09-09 2978.43 2019-09-10 2979.39 2019-09-11 3000.93
3579. 12019-09-12 3009.57 2019-09-13 3007.39 2019-09-16 2997.96 2019-09-17 3005.70
3580. 12019-09-18 3006.73 2019-09-19 3006.79 2019-09-20 2992.07 2019-09-23 2991.78
3581. 12019-09-24 2966.60 2019-09-25 2984.87 2019-09-26 2977.62 2019-09-27 2961.79
3582. 12019-09-30 2976.74 2019-10-01 2940.25 2019-10-02 2887.61 2019-10-03 2910.63
3583. 12019-10-04 2952.01 2019-10-07 2938.79 2019-10-08 2893.06 2019-10-09 2919.40
3584. 12019-10-10 2938.13 2019-10-11 2970.27 2019-10-14 2966.15 2019-10-15 2995.68
3585. 12019-10-16 2989.69 2019-10-17 2997.95 2019-10-18 2986.20 2019-10-21 3006.72
3586. 12019-10-22 2995.99 2019-10-23 3004.52 2019-10-24 3010.29 2019-10-25 3022.55
3587. 12019-10-28 3039.42 2019-10-29 3036.89 2019-10-30 3046.77 2019-10-31 3037.56
3588. 12019-11-01 3066.91 2019-11-04 3078.27 2019-11-05 3074.62 2019-11-06 3076.78
3589. 12019-11-07 3085.18 2019-11-08 3093.08 2019-11-11 3087.01 2019-11-12 3091.84
3590. 12019-11-13 3094.04 2019-11-14 3096.63 2019-11-15 3120.46 2019-11-18 3122.03
3591. 12019-11-19 3120.18 2019-11-20 3108.46 2019-11-21 3103.54 2019-11-22 3110.29
3592. 12019-11-25 3133.64 2019-11-26 3140.52 2019-11-27 3153.63 2019-11-29 3140.98
3593. 12019-12-02 3113.87 2019-12-03 3093.20 2019-12-04 3112.76 2019-12-05 3117.43
3594. 12019-12-06 3145.91 2019-12-09 3135.96 2019-12-10 3132.52 2019-12-11 3141.63
3595. 12019-12-12 3168.57 2019-12-13 3168.80 2019-12-16 3191.45 2019-12-17 3192.52
3596. 12019-12-18 3191.14 2019-12-19 3205.37 2019-12-20 3221.22 2019-12-23 3224.01
3597. 12019-12-24 3223.38 2019-12-26 3239.91 2019-12-27 3240.02 2019-12-30 3221.29
3598. 12019-12-31 3230.78 2020-01-02 3257.85 2020-01-03 3234.85 2020-01-06 3246.28
3599. 12020-01-07 3237.18 2020-01-08 3253.05 2020-01-09 3274.70 2020-01-10 3265.35
3600. 12020-01-13 3288.13 2020-01-14 3283.15 2020-01-15 3289.29 2020-01-16 3316.81
3601. 12020-01-17 3329.62 2020-01-21 3320.79 2020-01-22 3321.75 2020-01-23 3325.54
3602. 12020-01-24 3295.47 2020-01-27 3243.63 2020-01-28 3276.24 2020-01-29 3273.40
3603. 12020-01-30 3283.66 2020-01-31 3225.52 2020-02-03 3248.92 2020-02-04 3297.59
3604. 12020-02-05 3334.69 2020-02-06 3345.78 2020-02-07 3327.71 2020-02-10 3352.09
3605. 12020-02-11 3357.75 2020-02-12 3379.45 2020-02-13 3373.94 2020-02-14 3380.16
3606. 12020-02-18 3370.29 2020-02-19 3386.15 2020-02-20 3373.23 2020-02-21 3337.75
3607. 12020-02-24 3225.89 2020-02-25 3128.21 2020-02-26 3116.39 2020-02-27 2978.76
3608. 12020-02-28 2954.22 2020-03-02 3090.23 2020-03-03 3003.37 2020-03-04 3130.12
3609. 12020-03-05 3023.94 2020-03-06 2972.37 2020-03-09 2746.56 2020-03-10 2882.23
3610. 12020-03-11 2741.38 2020-03-12 2480.64 2020-03-13 2711.02 2020-03-16 2386.13
3611. 12020-03-17 2529.19 2020-03-18 2398.10 2020-03-19 2409.39 2020-03-20 2304.92
3612. 12020-03-23 2237.40 2020-03-24 2447.33 2020-03-25 2475.56 2020-03-26 2630.07
3613. 12020-03-27 2541.47 2020-03-30 2626.65 2020-03-31 2584.59 2020-04-01 2470.50
3614. 12020-04-02 2526.90 2020-04-03 2488.65 2020-04-06 2663.68 2020-04-07 2659.41
3615. 12020-04-08 2749.98 2020-04-09 2789.82 2020-04-13 2761.63 2020-04-14 2846.06
3616. 12020-04-15 2783.36 2020-04-16 2799.55 2020-04-17 2874.56 2020-04-20 2823.16
3617. 12020-04-21 2736.56 2020-04-22 2799.31 2020-04-23 2797.80 2020-04-24 2836.74
3618. 12020-04-27 2878.48 2020-04-28 2863.39 2020-04-29 2939.51 2020-04-30 2912.43
3619. 12020-05-01 2830.71 2020-05-04 2842.74 2020-05-05 2868.44 2020-05-06 2848.42
3620. 12020-05-07 2881.19 2020-05-08 2929.80 2020-05-11 2930.19 2020-05-12 2870.12
3621. 12020-05-13 2820.00 2020-05-14 2852.50 2020-05-15 2863.70 2020-05-18 2953.91
3622. 12020-05-19 2922.94 2020-05-20 2971.61 2020-05-21 2948.51 2020-05-22 2955.45
3623. 12020-05-26 2991.77 2020-05-27 3036.13 2020-05-28 3029.73 2020-05-29 3044.31
3624. 12020-06-01 3055.73 2020-06-02 3080.82 2020-06-03 3122.87 2020-06-04 3112.35
3625. 12020-06-05 3193.93 2020-06-08 3232.39 2020-06-09 3207.18 2020-06-10 3190.14
3626. 12020-06-11 3002.10 2020-06-12 3041.31 2020-06-15 3066.59 2020-06-16 3124.74
3627. 12020-06-17 3113.49 2020-06-18 3115.34 2020-06-19 3097.74 2020-06-22 3117.86
3628. 12020-06-23 3131.29 2020-06-24 3050.33 2020-06-25 3083.76 2020-06-26 3009.05
3629. 12020-06-29 3053.24 2020-06-30 3100.29 2020-07-01 3115.86 2020-07-02 3130.01
3630. 12020-07-06 3179.72 2020-07-07 3145.32 2020-07-08 3169.94 2020-07-09 3152.05
3631. 12020-07-10 3185.04 2020-07-13 3155.22 2020-07-14 3197.52 2020-07-15 3226.56
3632. 12020-07-16 3215.57 2020-07-17 3224.73 2020-07-20 3251.84 2020-07-21 3257.30
3633. 12020-07-22 3276.02 2020-07-23 3235.66 2020-07-24 3215.63 2020-07-27 3239.41
3634. 12020-07-28 3218.44 2020-07-29 3258.44 2020-07-30 3246.22 2020-07-31 3271.12
3635. 12020-08-03 3294.61 2020-08-04 3306.51 2020-08-05 3327.77 2020-08-06 3349.16
3636. 12020-08-07 3351.28 2020-08-10 3360.47 2020-08-11 3333.69 2020-08-12 3380.35
3637. 12020-08-13 3373.43 2020-08-14 3372.85 2020-08-17 3381.99 2020-08-18 3389.78
3638. 12020-08-19 3374.85 2020-08-20 3385.51 2020-08-21 3397.16 2020-08-24 3431.28
3639. 12020-08-25 3443.62 2020-08-26 3478.73 2020-08-27 3484.55 2020-08-28 3508.01
3640. 12020-08-31 3500.31 2020-09-01 3526.65 2020-09-02 3580.84 2020-09-03 3455.06
3641. 12020-09-04 3426.96 2020-09-08 3331.84 2020-09-09 3398.96 2020-09-10 3339.19
3642. 12020-09-11 3340.97 2020-09-14 3383.54 2020-09-15 3401.20 2020-09-16 3385.49
3643. 12020-09-17 3357.01 2020-09-18 3319.47 2020-09-21 3281.06 2020-09-22 3315.57
3644. 12020-09-23 3236.92 2020-09-24 3246.59 2020-09-25 3298.46 2020-09-28 3351.60
3645. 12020-09-29 3335.47 2020-09-30 3363.00 2020-10-01 3380.80 2020-10-02 3348.42
3646. 12020-10-05 3408.60 2020-10-06 3360.97 2020-10-07 3419.44 2020-10-08 3446.83
3647. 12020-10-09 3477.14 2020-10-12 3534.22 2020-10-13 3511.93 2020-10-14 3488.67
3648. 12020-10-15 3483.34 2020-10-16 3483.81 2020-10-19 3426.92 2020-10-20 3443.12
3649. 12020-10-21 3435.56 2020-10-22 3453.49 2020-10-23 3465.39 2020-10-26 3400.97
3650. 12020-10-27 3390.68 2020-10-28 3271.03 2020-10-29 3310.11 2020-10-30 3269.96
3651. 12020-11-02 3310.24 2020-11-03 3369.16 2020-11-04 3443.44 2020-11-05 3510.45
3652. 12020-11-06 3509.44 2020-11-09 3550.50 2020-11-10 3545.53 2020-11-11 3572.66
3653. 12020-11-12 3537.01 2020-11-13 3585.15 2020-11-16 3626.91 2020-11-17 3609.53
3654. 12020-11-18 3567.79 2020-11-19 3581.87 2020-11-20 3557.54 2020-11-23 3577.59
3655. 12020-11-24 3635.41 2020-11-25 3629.65 2020-11-27 3638.35 2020-11-30 3621.63
3656. 12020-12-01 3662.45 2020-12-02 3669.01 2020-12-03 3666.72 2020-12-04 3699.12
3657. 12020-12-07 3691.96 2020-12-08 3702.25 2020-12-09 3672.82 2020-12-10 3668.10
3658. 12020-12-11 3663.46 2020-12-14 3647.49 2020-12-15 3694.62 2020-12-16 3701.17
3659. 12020-12-17 3722.48 2020-12-18 3709.41 2020-12-21 3694.92 2020-12-22 3687.26
3660. 12020-12-23 3690.01 2020-12-24 3703.06 2020-12-28 3735.36 2020-12-29 3727.04
3661. 12020-12-30 3732.04 2020-12-31 3756.07 2021-01-04 3700.65 2021-01-05 3726.86
3662. 12021-01-06 3748.14 2021-01-07 3803.79 2021-01-08 3824.68 2021-01-11 3799.61
3663. 12021-01-12 3801.19 2021-01-13 3809.84 2021-01-14 3795.54 2021-01-15 3768.25
3664. 12021-01-19 3798.91 2021-01-20 3851.85 2021-01-21 3853.07 2021-01-22 3841.47
3665. 12021-01-25 3855.36 2021-01-26 3849.62 2021-01-27 3750.77 2021-01-28 3787.38
3666. 12021-01-29 3714.24 2021-02-01 3773.86 2021-02-02 3826.31 2021-02-03 3830.17
3667. 12021-02-04 3871.74 2021-02-05 3886.83 2021-02-08 3915.59 2021-02-09 3911.23
3668. 12021-02-10 3909.88 2021-02-11 3916.38 2021-02-12 3934.83 2021-02-16 3932.59
3669. 12021-02-17 3931.33 2021-02-18 3913.97 2021-02-19 3906.71 2021-02-22 3876.50
3670. 12021-02-23 3881.37 2021-02-24 3925.43 2021-02-25 3829.34 2021-02-26 3811.15
3671. 12021-03-01 3901.82 2021-03-02 3870.29 2021-03-03 3819.72 2021-03-04 3768.47
3672. 12021-03-05 3841.94 2021-03-08 3821.35 2021-03-09 3875.44 2021-03-10 3898.81
3673. 12021-03-11 3939.34 2021-03-12 3943.34 2021-03-15 3968.94 2021-03-16 3962.71
3674. 12021-03-17 3974.12 2021-03-18 3915.46 2021-03-19 3913.10 2021-03-22 3940.59
3675. 12021-03-23 3910.52 2021-03-24 3889.14 2021-03-25 3909.52 2021-03-26 3974.54
3676. 12021-03-29 3971.09 2021-03-30 3958.55 2021-03-31 3972.89 2021-04-01 4019.87
3677. 12021-04-05 4077.91 2021-04-06 4073.94 2021-04-07 4079.95 2021-04-08 4097.17
3678. 12021-04-09 4128.80 2021-04-12 4127.99 2021-04-13 4141.59 2021-04-14 4124.66
3679. 12021-04-15 4170.42 2021-04-16 4185.47 2021-04-19 4163.26 2021-04-20 4134.94
3680. 12021-04-21 4173.42 2021-04-22 4134.98 2021-04-23 4180.17 2021-04-26 4187.62
3681. 12021-04-27 4186.72 2021-04-28 4183.18 2021-04-29 4211.47 2021-04-30 4181.17
3682. 12021-05-03 4192.66 2021-05-04 4164.66 2021-05-05 4167.59 2021-05-06 4201.62
3683. 12021-05-07 4232.60 2021-05-10 4188.43 2021-05-11 4152.10 2021-05-12 4063.04
3684. 12021-05-13 4112.50 2021-05-14 4173.85 2021-05-17 4163.29 2021-05-18 4127.83
3685. 12021-05-19 4115.68 2021-05-20 4159.12 2021-05-21 4155.86 2021-05-24 4197.05
3686. 12021-05-25 4188.13 2021-05-26 4195.99 2021-05-27 4200.88 2021-05-28 4204.11
3687. 12021-06-01 4202.04 2021-06-02 4208.12 2021-06-03 4192.85 2021-06-04 4229.89
3688. 12021-06-07 4226.52 2021-06-08 4227.26 2021-06-09 4219.55 2021-06-10 4239.18
3689. 12021-06-11 4247.44 2021-06-14 4255.15 2021-06-15 4246.59 2021-06-16 4223.70
3690. 12021-06-17 4221.86 2021-06-18 4166.45 2021-06-21 4224.79 2021-06-22 4246.44
3691. 12021-06-23 4241.84 2021-06-24 4266.49 2021-06-25 4280.70 2021-06-28 4290.61
3692. 12021-06-29 4291.80 2021-06-30 4297.50 2021-07-01 4319.94 2021-07-02 4352.34
3693. 12021-07-06 4343.54 2021-07-07 4358.13 2021-07-08 4320.82 2021-07-09 4369.55
3694. 12021-07-12 4384.63 2021-07-13 4369.21 2021-07-14 4374.30 2021-07-15 4360.03
3695. 12021-07-16 4327.16 2021-07-19 4258.49 2021-07-20 4323.06 2021-07-21 4358.69
3696. 12021-07-22 4367.48 2021-07-23 4411.79 2021-07-26 4422.30 2021-07-27 4401.46
3697. 12021-07-28 4400.64 2021-07-29 4419.15 2021-07-30 4395.26 2021-08-02 4387.16
3698. 12021-08-03 4423.15 2021-08-04 4402.66 2021-08-05 4429.10 2021-08-06 4436.52
3699. 12021-08-09 4432.35 2021-08-10 4436.75 2021-08-11 4442.41 2021-08-12 4460.83
3700. 12021-08-13 4468.00 2021-08-16 4479.71 2021-08-17 4448.08 2021-08-18 4400.27
3701. 12021-08-19 4405.80 2021-08-20 4441.67 2021-08-23 4479.53 2021-08-24 4486.23
3702. 12021-08-25 4496.19 2021-08-26 4470.00 2021-08-27 4509.37 2021-08-30 4528.79
3703. 12021-08-31 4522.68 2021-09-01 4524.09 2021-09-02 4536.95 2021-09-03 4535.43
3704. 12021-09-07 4520.03 2021-09-08 4514.07 2021-09-09 4493.28 2021-09-10 4458.58
3705. 12021-09-13 4468.73 2021-09-14 4443.05 2021-09-15 4480.70 2021-09-16 4473.75
3706. 12021-09-17 4432.99 2021-09-20 4357.73 2021-09-21 4354.19 2021-09-22 4395.64
3707. 12021-09-23 4448.98 2021-09-24 4455.48 2021-09-27 4443.11 2021-09-28 4352.63
3708. 12021-09-29 4359.46 2021-09-30 4307.54 2021-10-01 4357.04 2021-10-04 4300.46
3709. 12021-10-05 4345.72 2021-10-06 4363.55 2021-10-07 4399.76 2021-10-08 4391.34
3710. 12021-10-11 4361.19 2021-10-12 4350.65 2021-10-13 4363.80 2021-10-14 4438.26
3711. 12021-10-15 4471.37 2021-10-18 4486.46 2021-10-19 4519.63 2021-10-20 4536.19
3712. 12021-10-21 4549.78 2021-10-22 4544.90 2021-10-25 4566.48 2021-10-26 4574.79
3713. 12021-10-27 4551.68 2021-10-28 4596.42 2021-10-29 4605.38 2021-11-01 4613.67
3714. 12021-11-02 4630.65 2021-11-03 4660.57 2021-11-04 4680.06 2021-11-05 4697.53
3715. 12021-11-08 4701.70 2021-11-09 4685.25 2021-11-10 4646.71 2021-11-11 4649.27
3716. 12021-11-12 4682.85 2021-11-15 4682.80 2021-11-16 4700.90 2021-11-17 4688.67
3717. 12021-11-18 4704.54 2021-11-19 4697.96 2021-11-22 4682.94 2021-11-23 4690.70
3718. 12021-11-24 4701.46 2021-11-26 4594.62 2021-11-29 4655.27 2021-11-30 4567.00
3719. 12021-12-01 4513.04 2021-12-02 4577.10 2021-12-03 4538.43 2021-12-06 4591.67
3720. 12021-12-07 4686.75 2021-12-08 4701.21 2021-12-09 4667.45 2021-12-10 4712.02
3721. 12021-12-13 4668.97 2021-12-14 4634.09 2021-12-15 4709.85 2021-12-16 4668.67
3722. 12021-12-17 4620.64 2021-12-20 4568.02 2021-12-21 4649.23 2021-12-22 4696.56
3723. 12021-12-23 4725.79 2021-12-27 4791.19 2021-12-28 4786.35 2021-12-29 4793.06
3724. 12021-12-30 4778.73 2021-12-31 4766.18 2022-01-03 4796.56 2022-01-04 4793.54
3725. 12022-01-05 4700.58 2022-01-06 4696.05 2022-01-07 4677.03 2022-01-10 4670.29
3726. 12022-01-11 4713.07 2022-01-12 4726.35 2022-01-13 4659.03 2022-01-14 4662.85
3727. 12022-01-18 4577.11 2022-01-19 4532.76 2022-01-20 4482.73 2022-01-21 4397.94
3728. 12022-01-24 4410.13 2022-01-25 4356.45 2022-01-26 4349.93 2022-01-27 4326.51
3729. 12022-01-28 4431.85 2022-01-31 4515.55 2022-02-01 4546.54 2022-02-02 4589.38
3730. 12022-02-03 4477.44 2022-02-04 4500.53 2022-02-07 4483.87 2022-02-08 4521.54
3731. 12022-02-09 4587.18 2022-02-10 4504.08 2022-02-11 4418.64 2022-02-14 4401.67
3732. 12022-02-15 4471.07 2022-02-16 4475.01 2022-02-17 4380.26 2022-02-18 4348.87
3733. 12022-02-22 4304.76 2022-02-23 4225.50 2022-02-24 4288.70 2022-02-25 4384.65
3734. 12022-02-28 4373.94 2022-03-01 4306.26 2022-03-02 4386.54 2022-03-03 4363.49
3735. 12022-03-04 4328.87 2022-03-07 4201.09 2022-03-08 4170.70 2022-03-09 4277.88
3736. 12022-03-10 4259.52 2022-03-11 4204.31 2022-03-14 4173.11 2022-03-15 4262.45
3737. 12022-03-16 4357.86 2022-03-17 4411.67 2022-03-18 4463.12 2022-03-21 4461.18
3738. 12022-03-22 4511.61 2022-03-23 4456.24 2022-03-24 4520.16 2022-03-25 4543.06
3739. 12022-03-28 4575.52 2022-03-29 4631.60 2022-03-30 4602.45 2022-03-31 4530.41
3740. 12022-04-01 4545.86 2022-04-04 4582.64 2022-04-05 4525.12 2022-04-06 4481.15
3741. 12022-04-07 4500.21 2022-04-08 4488.28 2022-04-11 4412.53 2022-04-12 4397.45
3742. 12022-04-13 4446.59 2022-04-14 4392.59 2022-04-18 4391.69 2022-04-19 4462.21
3743. 12022-04-20 4459.45 2022-04-21 4393.66 2022-04-22 4271.78 2022-04-25 4296.12
3744. 12022-04-26 4175.20 2022-04-27 4183.96 2022-04-28 4287.50 2022-04-29 4131.93
3745. 12022-05-02 4155.38 2022-05-03 4175.48 2022-05-04 4300.17 2022-05-05 4146.87
3746. 12022-05-06 4123.34 2022-05-09 3991.24 2022-05-10 4001.05 2022-05-11 3935.18
3747. 12022-05-12 3930.08 2022-05-13 4023.89 2022-05-16 4008.01 2022-05-17 4088.85
3748. 12022-05-18 3923.68 2022-05-19 3900.79 2022-05-20 3901.36 2022-05-23 3973.75
3749. 12022-05-24 3941.48 2022-05-25 3978.73 2022-05-26 4057.84 2022-05-27 4158.24
3750. 12022-05-31 4132.15 2022-06-01 4101.23 2022-06-02 4176.82 2022-06-03 4108.54
3751. 12022-06-06 4121.43 2022-06-07 4160.68 2022-06-08 4115.77 2022-06-09 4017.82
3752. 12022-06-10 3900.86 2022-06-13 3749.63 2022-06-14 3735.48 2022-06-15 3789.99
3753. 12022-06-16 3666.77 2022-06-17 3674.84 2022-06-21 3764.79 2022-06-22 3759.89
3754. 12022-06-23 3795.73 2022-06-24 3911.74 2022-06-27 3900.11 2022-06-28 3821.55
3755. 12022-06-29 3818.83 2022-06-30 3785.38 2022-07-01 3825.33 2022-07-05 3831.39
3756. 12022-07-06 3845.08 2022-07-07 3902.62 2022-07-08 3899.38 2022-07-11 3854.43
3757. 12022-07-12 3818.80 2022-07-13 3801.78 2022-07-14 3790.38 2022-07-15 3863.16
3758. 12022-07-18 3830.85 2022-07-19 3936.69 2022-07-20 3959.90 2022-07-21 3998.95
3759. 12022-07-22 3961.63 2022-07-25 3966.84 2022-07-26 3921.05 2022-07-27 4023.61
3760. 12022-07-28 4072.43 2022-07-29 4130.29 2022-08-01 4118.63 2022-08-02 4091.19
3761. 12022-08-03 4155.17 2022-08-04 4151.94 2022-08-05 4145.19 2022-08-08 4140.06
3762. 12022-08-09 4122.47 2022-08-10 4210.24 2022-08-11 4207.27 2022-08-12 4280.15
3763. 12022-08-15 4297.14 2022-08-16 4305.20 2022-08-17 4274.04 2022-08-18 4283.74
3764. 12022-08-19 4228.48 2022-08-22 4137.99 2022-08-23 4128.73 2022-08-24 4140.77
3765. 12022-08-25 4199.12 2022-08-26 4057.66 2022-08-29 4030.61 2022-08-30 3986.16
3766. 12022-08-31 3955.00 2022-09-01 3966.85 2022-09-02 3924.26 2022-09-06 3908.19
3767. 12022-09-07 3979.87 2022-09-08 4006.18 2022-09-09 4067.36 2022-09-12 4110.41
3768. 12022-09-13 3932.69 2022-09-14 3946.01 2022-09-15 3901.35 2022-09-16 3873.33
3769. 12022-09-19 3899.89 2022-09-20 3855.93 2022-09-21 3789.93 2022-09-22 3757.99
3770. 12022-09-23 3693.23 2022-09-26 3655.04 2022-09-27 3647.29 2022-09-28 3719.04
3771. 12022-09-29 3640.47 2022-09-30 3585.62 2022-10-03 3678.43 2022-10-04 3790.93
3772. 12022-10-05 3783.28 2022-10-06 3744.52 2022-10-07 3639.66 2022-10-10 3612.39
3773. 12022-10-11 3588.84 2022-10-12 3577.03 2022-10-13 3669.91 2022-10-14 3583.07
3774. 12022-10-17 3677.95 2022-10-18 3719.98 2022-10-19 3695.16 2022-10-20 3665.78
3775. 12022-10-21 3752.75 2022-10-24 3797.34 2022-10-25 3859.11 2022-10-26 3830.60
3776. 12022-10-27 3807.30 2022-10-28 3901.06 2022-10-31 3871.98 2022-11-01 3856.10
3777. 12022-11-02 3759.69 2022-11-03 3719.89 2022-11-04 3770.55 2022-11-07 3806.80
3778. 12022-11-08 3828.11 2022-11-09 3748.57 2022-11-10 3956.37 2022-11-11 3992.93
3779. 12022-11-14 3957.25 2022-11-15 3991.73 2022-11-16 3958.79 2022-11-17 3946.56
3780. 12022-11-18 3965.34 2022-11-21 3949.94 2022-11-22 4003.58 2022-11-23 4027.26
3781. 12022-11-25 4026.12 2022-11-28 3963.94 2022-11-29 3957.63 2022-11-30 4080.11
3782. 12022-12-01 4076.57 2022-12-02 4071.70 2022-12-05 3998.84 2022-12-06 3941.26
3783. 12022-12-07 3933.92 2022-12-08 3963.51 2022-12-09 3934.38 2022-12-12 3990.56
3784. 12022-12-13 4019.65 2022-12-14 3995.32 2022-12-15 3895.75 2022-12-16 3852.36
3785. 12022-12-19 3817.66 2022-12-20 3821.62 2022-12-21 3878.44 2022-12-22 3822.39
3786. 12022-12-23 3844.82 2022-12-27 3829.25 2022-12-28 3783.22 2022-12-29 3849.28
3787. 12022-12-30 3839.50 2023-01-03 3824.14 2023-01-04 3852.97 2023-01-05 3808.10
3788. 1##
3789. 1 `);
3790. 1 testDataSpx = testDataSpx.replace((/ (20..-)/g), "\n$1");
3791. 1 testDataSpx = testDataSpx.trim().split("\n").slice(2, -1);
3792. 1012 testDataSpx = testDataSpx.map(function (elem, ii) {
3793. 1012 elem = elem.split(" ");
3794. 1012 return {
3795. 1012 date: elem[0],
3796. 1012 ii,
3797. 1012 priceClose: Number(elem[1])
3798. 1012 };
3799. 1012 });
3800. 1 valActual = await dbExecAndReturnLastTable({
3801. 1 bindList: {
3802. 1 testDataSpx
3803. 1 },
3804. 1 db,
3805. 1 sql: (`
3806. 1DROP TABLE IF EXISTS __sinefit_csv;
3807. 1CREATE TEMP TABLE __sinefit_csv AS
3808. 1 SELECT
3809. 1 *,
3810. 1 WIN_SINEFIT2(1, NULL, ii, yy, ii, yy) OVER (
3811. 1 ORDER BY date ASC
3812. 1 ROWS BETWEEN ${ttSinefit - 1} PRECEDING AND 0 FOLLOWING
3813. 1 ) AS __wsf
3814. 1 FROM (
3815. 1 SELECT
3816. 1 value->>'ii' AS ii,
3817. 1 value->>'date' AS date,
3818. 1 value->>'priceClose' AS yy,
3819. 1 0 AS rr
3820. 1 FROM JSON_EAcH($testDataSpx)
3821. 1 );
3822. 1UPDATE __sinefit_csv
3823. 1 SET
3824. 1 rr = yy - predict_lnr
3825. 1 FROM (
3826. 1 SELECT
3827. 1 ii + 1 AS ii,
3828. 1 sinefit_extract(__wsf, 0, 'predict_lnr', ii + 1) AS predict_lnr
3829. 1 FROM __sinefit_csv
3830. 1 ) AS __join1
3831. 1 WHERE __join1.ii = __sinefit_csv.ii;
3832. 1SELECT
3833. 1 *,
3834. 1 sinefit_extract(__wsf, 0, 'saa', 0) AS saa,
3835. 1 sinefit_extract(__wsf, 0, 'spp', 0) AS spp,
3836. 1 sinefit_extract(__wsf, 0, 'sww', 0) AS sww,
3837. 1 ${sqlSinefitExtractLnr("__wsf", 0, "")}
3838. 1 FROM __sinefit_csv
3839. 1 JOIN (
3840. 1 SELECT
3841. 1 MEDIAN2(rr) AS rr_avg,
3842. 1 STDEV(rr) AS rr_err
3843. 1 FROM __sinefit_csv
3844. 1 )
3845. 1 LEFT JOIN (
3846. 1 SELECT
3847. 1 ii + 1 AS ii,
3848. 1 sinefit_extract(__wsf, 0, 'predict_snr', ii + 1) AS predict_snr
3849. 1 FROM __sinefit_csv
3850. 1 ) USING (ii);
3851. 1 `)
3852. 1 });
3853. 1 valActual = (
3854. 1 "date saa sww spp"
3855. 1 + " ii linear_residual predict_sine\n"
3856. 1012 + valActual.map(function (elem) {
3857. 1012 return [
3858. 1012 elem.date,
3859. 1012 elem.saa,
3860. 1012 elem.sww,
3861. 1012 elem.spp,
3862. 1012 elem.ii,
3863. 1012 (elem.rr - elem.rr_avg) / elem.rr_err,
3864. 1012 elem.predict_snr / 100
3865. 7084 ].map(function (num) {
3866. 7084 return (
3867. 7084 typeof num === "number"
3868. 6072 ? num.toFixed(4)
3869. 1012 : num
3870. 7084 );
3871. 7084 }).join(" ");
3872. 1012 }).join("\n")
3873. 1 );
3874. 1 valActual = valActual.replace((/ /g), " null ");
3875. 1 valActual = valActual.replace((/ \n/g), "\n");
3876. 1 valActual = valActual.replace((/ /g), "\t");
3877. 1 valActual = valActual.trim() + "\n";
3878. 1 await fsWriteFileUnlessTest(
3879. 1 "test_data_sinefit.csv",
3880. 1 valActual,
3881. 1 String("1").replace(npm_config_mode_test_save, "force")
3882. 1 );
3883. 1 valExpect = await fsReadFileUnlessTest(
3884. 1 "test_data_sinefit.csv",
3885. 1 "force"
3886. 1 );
3887. 1 assertJsonEqual(valActual, valExpect);
3888. 1 }())
3889. 1 ]);
3890. 1 });
3891. 1 jstestIt((
3892. 1 "test sqlite-extension-win_sumx handling-behavior"
3893. 1 ), async function test_sqlite_extension_win_sumx() {
3894. 1 let db = await dbOpenAsync({filename: ":memory:"});
3895. 1 let valIn;
3896. 4 async function test_win_sumx_aggregate({
3897. 4 aa,
3898. 4 bb,
3899. 4 valExpect,
3900. 4 valExpect2
3901. 4 }) {
3902. 4 let sqlBetween = "";
3903. 4 let valActual;
3904. 3 if (aa !== undefined) {
3905. 3 sqlBetween = (
3906. 3 `ROWS BETWEEN ${aa - 1} PRECEDING AND ${bb} FOLLOWING`
3907. 3 );
3908. 3 }
3909. 4 // test win_sum1-aggregate handling-behavior
3910. 4 valActual = await dbExecAndReturnLastTable({
3911. 4 bindList: {
3912. 4 valIn: JSON.stringify(valIn)
3913. 4 },
3914. 4 db,
3915. 4 sql: (`
3916. 4SELECT
3917. 4 WIN_SUM1(value->>1) OVER (
3918. 4 ORDER BY value->>0 ASC
3919. 4 ${sqlBetween}
3920. 4 ) AS val
3921. 4 FROM JSON_EAcH($valIn);
3922. 4 `)
3923. 4 });
3924. 48 valActual = valActual.map(function ({val}) {
3925. 48 return Number(val.toFixed(4));
3926. 48 });
3927. 4 assertJsonEqual(valActual, valExpect);
3928. 4 // test win_sum2-aggregate handling-behavior
3929. 4 valActual = await dbExecAndReturnLastTable({
3930. 4 bindList: {
3931. 4 valIn: JSON.stringify(valIn)
3932. 4 },
3933. 4 db,
3934. 4 sql: (`
3935. 4SELECT
3936. 4 id2,
3937. 4 DOUBLEARRAY_JSONTO(WIN_SUM2(
3938. 4 value->>1,
3939. 4 value->>1,
3940. 4 value->>1,
3941. 4 value->>1,
3942. 4 value->>1,
3943. 4 value->>1,
3944. 4 value->>1,
3945. 4 value->>1,
3946. 4 value->>1,
3947. 4 IIF(id2 = 1, -1, value->>1)
3948. 4 ) OVER (
3949. 4 ORDER BY value->>0 ASC
3950. 4 ${sqlBetween}
3951. 4 )) AS val
3952. 4 FROM (
3953. 4 SELECT
3954. 4 *,
3955. 4 ROW_NUMBER() OVER(ORDER BY id ASC) AS id2
3956. 4 FROM JSON_EAcH($valIn)
3957. 4 );
3958. 4 `)
3959. 4 });
3960. 48 valActual = valActual.map(function ({val}, ii, list) {
3961. 480 val = JSON.parse(val).map(function (elem, jj) {
3962. 480 elem = Number(elem.toFixed(4));
3963. 240 if (ii + (bb || 0) + 1 >= list.length && jj === 9) {
3964. 8 assertJsonEqual(elem, valExpect2, valActual);
3965. 472 } else {
3966. 472 assertJsonEqual(elem, valExpect[ii], valActual);
3967. 472 }
3968. 480 return elem;
3969. 480 });
3970. 48 return val[0];
3971. 48 });
3972. 4 assertJsonEqual(valActual, valExpect);
3973. 4 }
3974. 1 valIn = [
3975. 1 [11, NaN],
3976. 1 [10, "10"],
3977. 1 [9, 9],
3978. 1 [8, "8"],
3979. 1 [7, 7],
3980. 1 [6, 6],
3981. 1 [5, Infinity],
3982. 1 [4, "4"],
3983. 1 [3, 3],
3984. 1 [2, 2],
3985. 1 [1, "1"],
3986. 1 [0, undefined]
3987. 1 ];
3988. 1 await Promise.all([
3989. 1 (async function () {
3990. 1 let valActual;
3991. 1 // test win_sum2-error handling-behavior
3992. 1 await assertErrorThrownAsync(function () {
3993. 1 return dbExecAsync({
3994. 1 db,
3995. 1 sql: (`
3996. 1SELECT WIN_SUM2() FROM (SELECT 1);
3997. 1 `)
3998. 1 });
3999. 1 }, "wrong number of arguments");
4000. 1 // test win_sum1-null-case handling-behavior
4001. 1 valActual = await dbExecAndReturnLastTable({
4002. 1 db,
4003. 1 sql: (`
4004. 1DROP TABLE IF EXISTS __tmp1;
4005. 1CREATE TEMP TABLE __tmp1 (val REAL);
4006. 1SELECT WIN_SUM1(1) FROM __tmp1;
4007. 1 `)
4008. 1 });
4009. 1 valActual = valActual.map(function ({val}) {
4010. 1 return val;
4011. 1 });
4012. 1 assertJsonEqual(valActual, [null]);
4013. 1 // test win_sum2-null-case handling-behavior
4014. 1 valActual = await dbExecAndReturnLastTable({
4015. 1 db,
4016. 1 sql: (`
4017. 1DROP TABLE IF EXISTS __tmp1;
4018. 1CREATE TEMP TABLE __tmp1 (val REAL);
4019. 1SELECT DOUBLEARRAY_JSONTO(WIN_SUM2(1, 2, 3)) FROM __tmp1;
4020. 1 `)
4021. 1 });
4022. 1 valActual = valActual.map(function ({val}) {
4023. 1 return val;
4024. 1 });
4025. 1 assertJsonEqual(valActual, [null]);
4026. 1 }()),
4027. 1 // test win_sum2-aggregate-normal handling-behavior
4028. 1 test_win_sumx_aggregate({
4029. 1 valExpect: [
4030. 1 0, 1, 3, 6,
4031. 1 10, 14, 20, 27,
4032. 1 35, 44, 54, 64
4033. 1 ],
4034. 1 valExpect2: 53
4035. 1 }),
4036. 1 // test win_sum2-aggregate-window handling-behavior
4037. 1 test_win_sumx_aggregate({
4038. 1 aa: 1,
4039. 1 bb: 3,
4040. 1 valExpect: [
4041. 1 6, 10, 13, 17,
4042. 1 21, 25, 30, 34,
4043. 1 37, 37, 37, 37
4044. 1 ],
4045. 1 valExpect2: 26
4046. 1 }),
4047. 1 test_win_sumx_aggregate({
4048. 1 aa: 3,
4049. 1 bb: 1,
4050. 1 valExpect: [
4051. 1 1, 3, 6, 10,
4052. 1 13, 17, 21, 25,
4053. 1 30, 34, 37, 37
4054. 1 ],
4055. 1 valExpect2: 26
4056. 1 }),
4057. 1 test_win_sumx_aggregate({
4058. 1 aa: 4,
4059. 1 bb: 0,
4060. 1 valExpect: [
4061. 1 0, 1, 3, 6,
4062. 1 10, 13, 17, 21,
4063. 1 25, 30, 34, 37
4064. 1 ],
4065. 1 valExpect2: 26
4066. 1 })
4067. 1 ]);
4068. 1 });
4069. 1});
4070. 1
4071. 1jstestDescribe((
4072. 1 "test_sqlmathWebworkerInit"
4073. 1), function test_sqlmathWebworkerInit() {
4074. 1 jstestIt((
4075. 1 "test sqlmathWebworkerInit handling-behavior"
4076. 1 ), async function () {
4077. 1 let db = await dbOpenAsync({
4078. 1 dbData: new ArrayBuffer(),
4079. 1 filename: ":memory:"
4080. 1 });
4081. 1 sqlmathWebworkerInit({
4082. 1 db,
4083. 1 modeTest: true
4084. 1 });
4085. 1 });
4086. 1});
4087. 1