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