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