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