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