konnosDashboard
table-row-query.test.ts1850 lines · main
1import { afterAll, beforeAll, describe, expect, test } from 'vitest'
2
3import pgMeta from '../../src/index'
4import { Filter, Sort } from '../../src/query'
5import { getDefaultOrderByColumns, getTableRowsSql } from '../../src/query/table-row-query'
6import { cleanupRoot, createTestDatabase } from '../db/utils'
7
8beforeAll(async () => {
9 // Any global setup if needed
10})
11
12afterAll(async () => {
13 await cleanupRoot()
14})
15
16type TestDb = Awaited<ReturnType<typeof createTestDatabase>>
17
18const withTestDatabase = (name: string, fn: (db: TestDb) => Promise<void>) => {
19 test(name, async () => {
20 const db = await createTestDatabase()
21 try {
22 await fn(db)
23 } finally {
24 await db.cleanup()
25 }
26 })
27}
28
29describe('Table Row Query', () => {
30 describe('getDefaultOrderByColumns', () => {
31 test('should return empty array when no primary keys and no columns exist', () => {
32 const result = getDefaultOrderByColumns({
33 primary_keys: [],
34 columns: [],
35 })
36 expect(result).toEqual([])
37 })
38
39 test('should exclude specified columns when determining default sort', () => {
40 const table = {
41 primary_keys: [{ name: 'id' }],
42 columns: [
43 {
44 name: 'id',
45 data_type: 'integer',
46 format: 'int4',
47 ordinal_position: 1,
48 },
49 {
50 name: 'name',
51 data_type: 'text',
52 format: 'text',
53 ordinal_position: 2,
54 },
55 ],
56 } as any
57
58 const result = getDefaultOrderByColumns(table, { excludedColumns: ['id'] })
59 expect(result).toEqual(['name'])
60 })
61 })
62
63 describe('getTableRowsSql', () => {
64 withTestDatabase('should handle array of enums correctly', async (db) => {
65 // Create an enum type and a table with an array of that enum
66 await db.executeQuery(`
67 -- Create enum type
68 CREATE TYPE status_type AS ENUM ('pending', 'active', 'completed', 'canceled');
69
70 -- Create table with array of enums
71 CREATE TABLE test_enum_array (
72 id SERIAL PRIMARY KEY,
73 name TEXT,
74 status status_type, -- Regular enum column
75 history status_type[] -- Array of enums
76 );
77
78 -- Insert test data with various enum array values
79 INSERT INTO test_enum_array (name, status, history) VALUES
80 ('Item 1', 'active', ARRAY['pending', 'active']::status_type[]),
81 ('Item 2', 'completed', ARRAY['pending', 'active', 'completed']::status_type[]),
82 ('Item 3', 'canceled', ARRAY['active', 'canceled']::status_type[]),
83 ('Item 4', 'pending', NULL);
84 `)
85
86 // Get table metadata
87 const { sql: tablesSql, zod: tablesZod } = pgMeta.tables.list()
88 const tables = tablesZod.parse(await db.executeQuery(tablesSql))
89 const testTable = tables.find((table) => table.name === 'test_enum_array')
90
91 expect(testTable).toBeDefined()
92
93 // Generate SQL
94 const sql = getTableRowsSql({
95 table: testTable!,
96 page: 1,
97 limit: 10,
98 })
99
100 // Verify SQL generation with snapshot
101 expect(sql).toMatchInlineSnapshot(`
102 "with _base_query as (select * from public.test_enum_array order by test_enum_array.id asc nulls last limit 10 offset 0)
103 select id,case
104 when octet_length(name::text) > 10240
105 then left(name::text, 10240) || '...'
106 else name::text
107 end as name,status,
108 case
109 when octet_length(history::text) > 10240
110 then
111 case
112 when array_ndims(history) = 1
113 then
114 (select array_cat(history[1:50]::text[], array['...']::text[]))::text[]
115 else
116 history[1:50]::text[]
117 end
118 else history::text[]
119 end
120 from _base_query;"
121 `)
122
123 // Execute the generated SQL and verify the results
124 const queryResult = await db.executeQuery(sql)
125 expect(queryResult).toMatchInlineSnapshot(`
126 [
127 {
128 "history": [
129 "pending",
130 "active",
131 ],
132 "id": 1,
133 "name": "Item 1",
134 "status": "active",
135 },
136 {
137 "history": [
138 "pending",
139 "active",
140 "completed",
141 ],
142 "id": 2,
143 "name": "Item 2",
144 "status": "completed",
145 },
146 {
147 "history": [
148 "active",
149 "canceled",
150 ],
151 "id": 3,
152 "name": "Item 3",
153 "status": "canceled",
154 },
155 {
156 "history": null,
157 "id": 4,
158 "name": "Item 4",
159 "status": "pending",
160 },
161 ]
162 `)
163
164 // Test filtering on enum array values
165 const filtersStatusSQL = getTableRowsSql({
166 table: testTable!,
167 filters: [
168 { column: 'status', operator: '=', value: `active` }, // Contains 'active' using text pattern matching
169 ],
170 page: 1,
171 limit: 10,
172 })
173
174 expect(filtersStatusSQL).toMatchInlineSnapshot(`
175 "with _base_query as (select * from public.test_enum_array where status = 'active' order by test_enum_array.id asc nulls last limit 10 offset 0)
176 select id,case
177 when octet_length(name::text) > 10240
178 then left(name::text, 10240) || '...'
179 else name::text
180 end as name,status,
181 case
182 when octet_length(history::text) > 10240
183 then
184 case
185 when array_ndims(history) = 1
186 then
187 (select array_cat(history[1:50]::text[], array['...']::text[]))::text[]
188 else
189 history[1:50]::text[]
190 end
191 else history::text[]
192 end
193 from _base_query;"
194 `)
195 // Execute the filtered query
196 const filtersStatusResult = await db.executeQuery(filtersStatusSQL)
197 expect(filtersStatusResult).toMatchInlineSnapshot(`
198 [
199 {
200 "history": [
201 "pending",
202 "active",
203 ],
204 "id": 1,
205 "name": "Item 1",
206 "status": "active",
207 },
208 ]
209 `)
210
211 const filtersHistorySQL = getTableRowsSql({
212 table: testTable!,
213 filters: [
214 { column: 'history', operator: '=', value: `ARRAY['active']::status_type[]` }, // Contains 'active' using text pattern matching
215 ],
216 page: 1,
217 limit: 10,
218 })
219
220 expect(filtersHistorySQL).toMatchInlineSnapshot(`
221 "with _base_query as (select * from public.test_enum_array where history = ARRAY['active']::status_type[] order by test_enum_array.id asc nulls last limit 10 offset 0)
222 select id,case
223 when octet_length(name::text) > 10240
224 then left(name::text, 10240) || '...'
225 else name::text
226 end as name,status,
227 case
228 when octet_length(history::text) > 10240
229 then
230 case
231 when array_ndims(history) = 1
232 then
233 (select array_cat(history[1:50]::text[], array['...']::text[]))::text[]
234 else
235 history[1:50]::text[]
236 end
237 else history::text[]
238 end
239 from _base_query;"
240 `)
241 const filtersHistoryResult = await db.executeQuery(filtersHistorySQL)
242 expect(filtersHistoryResult).toMatchInlineSnapshot(`[]`)
243 })
244
245 withTestDatabase('should handle array columns correctly', async (db) => {
246 await db.executeQuery(`
247 CREATE TABLE test_array_table (
248 id SERIAL PRIMARY KEY,
249 name TEXT,
250 tags TEXT[] -- Array of text
251 );
252
253 -- Insert test data with array values
254 INSERT INTO test_array_table (name, tags) VALUES
255 ('Item 1', ARRAY['tag1', 'tag2']),
256 ('Item 2', ARRAY['tag3']),
257 ('Item 3', ARRAY['tag1', 'tag4']);
258 `)
259
260 // Get table metadata
261 const { sql: tablesSql, zod: tablesZod } = pgMeta.tables.list()
262 const tables = tablesZod.parse(await db.executeQuery(tablesSql))
263 const testTable = tables.find((table) => table.name === 'test_array_table')
264
265 expect(testTable).toBeDefined()
266
267 // Generate SQL
268 const sql = getTableRowsSql({
269 table: testTable!,
270 page: 1,
271 limit: 10,
272 })
273 expect(sql).toMatchInlineSnapshot(`
274 "with _base_query as (select * from public.test_array_table order by test_array_table.id asc nulls last limit 10 offset 0)
275 select id,case
276 when octet_length(name::text) > 10240
277 then left(name::text, 10240) || '...'
278 else name::text
279 end as name,
280 case
281 when octet_length(tags::text) > 10240
282 then
283 case
284 when array_ndims(tags) = 1
285 then
286 (select array_cat(tags[1:50]::text[], array['...']::text[]))::text[]
287 else
288 tags[1:50]::text[]
289 end
290 else tags::text[]
291 end
292 from _base_query;"
293 `)
294
295 const queryResult = await db.executeQuery(sql)
296 expect(queryResult.length).toBe(3)
297 expect(queryResult).toMatchInlineSnapshot(`
298 [
299 {
300 "id": 1,
301 "name": "Item 1",
302 "tags": [
303 "tag1",
304 "tag2",
305 ],
306 },
307 {
308 "id": 2,
309 "name": "Item 2",
310 "tags": [
311 "tag3",
312 ],
313 },
314 {
315 "id": 3,
316 "name": "Item 3",
317 "tags": [
318 "tag1",
319 "tag4",
320 ],
321 },
322 ]
323 `)
324 })
325
326 withTestDatabase('should generate basic SELECT SQL for a table', async (db) => {
327 // Create test table and insert data
328 await db.executeQuery(`
329 CREATE TABLE test_sql_gen (
330 id SERIAL PRIMARY KEY,
331 name TEXT,
332 description TEXT,
333 created_at TIMESTAMP DEFAULT NOW()
334 );
335
336 -- Insert test data
337 INSERT INTO test_sql_gen (name, description) VALUES
338 ('Row 1', 'Description 1'),
339 ('Row 2', 'Description 2'),
340 ('Row 3', 'Description 3');
341 `)
342
343 // Get table metadata
344 const { sql: tablesSql, zod: tablesZod } = pgMeta.tables.list()
345 const tables = tablesZod.parse(await db.executeQuery(tablesSql))
346 const testTable = tables.find((table) => table.name === 'test_sql_gen')
347
348 expect(testTable).toBeDefined()
349
350 // Generate SQL
351 const sql = getTableRowsSql({
352 table: testTable!,
353 page: 1,
354 limit: 10,
355 })
356
357 // Verify SQL generation with snapshot
358 expect(sql).toMatchInlineSnapshot(
359 `
360 "with _base_query as (select * from public.test_sql_gen order by test_sql_gen.id asc nulls last limit 10 offset 0)
361 select id,case
362 when octet_length(name::text) > 10240
363 then left(name::text, 10240) || '...'
364 else name::text
365 end as name,case
366 when octet_length(description::text) > 10240
367 then left(description::text, 10240) || '...'
368 else description::text
369 end as description,created_at from _base_query;"
370 `
371 )
372
373 // E2E Test: Execute the SQL and verify results
374 const queryResult = await db.executeQuery(sql)
375 expect(queryResult.length).toBe(3)
376 expect(queryResult.map((row: any) => row.name)).toEqual(['Row 1', 'Row 2', 'Row 3'])
377 expect(queryResult.map((row: any) => row.description)).toEqual([
378 'Description 1',
379 'Description 2',
380 'Description 3',
381 ])
382 })
383
384 withTestDatabase(
385 'should truncate large arrays to maxArraySize elements if their size is > maxCharacters',
386 async (db) => {
387 // Create test table with array column
388 await db.executeQuery(`
389 CREATE TABLE test_large_array_table (
390 id SERIAL PRIMARY KEY,
391 name TEXT,
392 large_array TEXT[] -- Will hold a very large array
393 );
394
395 -- Insert test data with a large array (>10KB)
396 -- Create an array with 1000 elements to ensure it exceeds 10KB
397 INSERT INTO test_large_array_table (name, large_array) VALUES
398 ('Large Array Item', (SELECT array_agg('element_' || i) FROM generate_series(1, 1000) i)),
399 ('Large Array Small items', (SELECT array_agg('' || i) FROM generate_series(1, 100) i)),
400 ('Normal Array Item', ARRAY['tag1', 'tag2', 'tag3']);
401 `)
402
403 // Get table metadata
404 const { sql: tablesSql, zod: tablesZod } = pgMeta.tables.list()
405 const tables = tablesZod.parse(await db.executeQuery(tablesSql))
406 const testTable = tables.find((table) => table.name === 'test_large_array_table')
407
408 expect(testTable).toBeDefined()
409
410 // Generate SQL with lower maxCharacters and maxArraySize limits
411 const sql = getTableRowsSql({
412 table: testTable!,
413 page: 1,
414 limit: 10,
415 maxCharacters: 2048,
416 maxArraySize: 10,
417 })
418
419 // Verify the SQL contains the array truncation logic
420 expect(sql).toMatchInlineSnapshot(`
421 "with _base_query as (select * from public.test_large_array_table order by test_large_array_table.id asc nulls last limit 10 offset 0)
422 select id,case
423 when octet_length(name::text) > 2048
424 then left(name::text, 2048) || '...'
425 else name::text
426 end as name,
427 case
428 when octet_length(large_array::text) > 2048
429 then
430 case
431 when array_ndims(large_array) = 1
432 then
433 (select array_cat(large_array[1:10]::text[], array['...']::text[]))::text[]
434 else
435 large_array[1:10]::text[]
436 end
437 else large_array::text[]
438 end
439 from _base_query;"
440 `)
441
442 // Execute the SQL and verify results
443 const queryResult = await db.executeQuery(sql)
444 expect(queryResult).toMatchInlineSnapshot(`
445 [
446 {
447 "id": 1,
448 "large_array": [
449 "element_1",
450 "element_2",
451 "element_3",
452 "element_4",
453 "element_5",
454 "element_6",
455 "element_7",
456 "element_8",
457 "element_9",
458 "element_10",
459 "...",
460 ],
461 "name": "Large Array Item",
462 },
463 {
464 "id": 2,
465 "large_array": [
466 "1",
467 "2",
468 "3",
469 "4",
470 "5",
471 "6",
472 "7",
473 "8",
474 "9",
475 "10",
476 "11",
477 "12",
478 "13",
479 "14",
480 "15",
481 "16",
482 "17",
483 "18",
484 "19",
485 "20",
486 "21",
487 "22",
488 "23",
489 "24",
490 "25",
491 "26",
492 "27",
493 "28",
494 "29",
495 "30",
496 "31",
497 "32",
498 "33",
499 "34",
500 "35",
501 "36",
502 "37",
503 "38",
504 "39",
505 "40",
506 "41",
507 "42",
508 "43",
509 "44",
510 "45",
511 "46",
512 "47",
513 "48",
514 "49",
515 "50",
516 "51",
517 "52",
518 "53",
519 "54",
520 "55",
521 "56",
522 "57",
523 "58",
524 "59",
525 "60",
526 "61",
527 "62",
528 "63",
529 "64",
530 "65",
531 "66",
532 "67",
533 "68",
534 "69",
535 "70",
536 "71",
537 "72",
538 "73",
539 "74",
540 "75",
541 "76",
542 "77",
543 "78",
544 "79",
545 "80",
546 "81",
547 "82",
548 "83",
549 "84",
550 "85",
551 "86",
552 "87",
553 "88",
554 "89",
555 "90",
556 "91",
557 "92",
558 "93",
559 "94",
560 "95",
561 "96",
562 "97",
563 "98",
564 "99",
565 "100",
566 ],
567 "name": "Large Array Small items",
568 },
569 {
570 "id": 3,
571 "large_array": [
572 "tag1",
573 "tag2",
574 "tag3",
575 ],
576 "name": "Normal Array Item",
577 },
578 ]
579 `)
580 }
581 )
582
583 withTestDatabase(
584 'should truncate large arrays of jsonb and json to maxArraySize elements if their size is > maxCharacters',
585 async (db) => {
586 // Create test table with array column
587 await db.executeQuery(`
588 CREATE TABLE test_large_array_table (
589 id SERIAL PRIMARY KEY,
590 name TEXT,
591 large_array_jsonb jsonb[],
592 large_array_json json[]
593 );
594
595 -- Insert test data with a large array (>10KB)
596 -- Create arrays with JSON objects
597 INSERT INTO test_large_array_table (name, large_array_jsonb, large_array_json) VALUES
598 (
599 'Large Array Item',
600 (SELECT array_agg(jsonb_build_object(
601 'id', i,
602 'name', 'element_' || i,
603 'data', jsonb_build_object('value', i * 10, 'active', true)
604 )) FROM generate_series(1, 1000) i),
605 (SELECT array_agg(json_build_object(
606 'id', i,
607 'name', 'element_' || i,
608 'data', json_build_object('value', i * 10, 'active', true)
609 )) FROM generate_series(1, 1000) i)
610 ),
611 (
612 'Large Array Small items',
613 (SELECT array_agg(jsonb_build_object(
614 'id', i,
615 'value', i
616 )) FROM generate_series(1, 100) i),
617 (SELECT array_agg(json_build_object(
618 'id', i,
619 'value', i
620 )) FROM generate_series(1, 100) i)
621 ),
622 (
623 'Normal Array Item',
624 ARRAY[
625 '{"id": 1, "tag": "tag1"}'::jsonb,
626 '{"id": 2, "tag": "tag2"}'::jsonb,
627 '{"id": 3, "tag": "tag3"}'::jsonb
628 ],
629 ARRAY[
630 '{"id": 1, "tag": "tag1"}'::json,
631 '{"id": 2, "tag": "tag2"}'::json,
632 '{"id": 3, "tag": "tag3"}'::json
633 ]
634 );
635 `)
636
637 // Get table metadata
638 const { sql: tablesSql, zod: tablesZod } = pgMeta.tables.list()
639 const tables = tablesZod.parse(await db.executeQuery(tablesSql))
640 const testTable = tables.find((table) => table.name === 'test_large_array_table')
641
642 expect(testTable).toBeDefined()
643
644 // Generate SQL with lower maxCharacters and maxArraySize limits
645 const sql = getTableRowsSql({
646 table: testTable!,
647 page: 1,
648 limit: 10,
649 maxCharacters: 2048,
650 maxArraySize: 10,
651 })
652
653 // Verify the SQL contains the array truncation logic
654 expect(sql).toMatchInlineSnapshot(`
655 "with _base_query as (select * from public.test_large_array_table order by test_large_array_table.id asc nulls last limit 10 offset 0)
656 select id,case
657 when octet_length(name::text) > 2048
658 then left(name::text, 2048) || '...'
659 else name::text
660 end as name,
661 case
662 when octet_length(large_array_jsonb::text) > 2048
663 then
664 case
665 when array_ndims(large_array_jsonb) = 1
666 then
667 (select array_cat(large_array_jsonb[1:10]::jsonb[], array['{"truncated": true}'::json]::jsonb[]))::jsonb[]
668 else
669 large_array_jsonb[1:10]::jsonb[]
670 end
671 else large_array_jsonb::jsonb[]
672 end
673 ,
674 case
675 when octet_length(large_array_json::text) > 2048
676 then
677 case
678 when array_ndims(large_array_json) = 1
679 then
680 (select array_cat(large_array_json[1:10]::json[], array['{"truncated": true}'::json]::json[]))::json[]
681 else
682 large_array_json[1:10]::json[]
683 end
684 else large_array_json::json[]
685 end
686 from _base_query;"
687 `)
688
689 // Execute the SQL and verify results
690 const queryResult = await db.executeQuery(sql)
691 expect(queryResult).toMatchInlineSnapshot(`
692 [
693 {
694 "id": 1,
695 "large_array_json": [
696 {
697 "data": {
698 "active": true,
699 "value": 10,
700 },
701 "id": 1,
702 "name": "element_1",
703 },
704 {
705 "data": {
706 "active": true,
707 "value": 20,
708 },
709 "id": 2,
710 "name": "element_2",
711 },
712 {
713 "data": {
714 "active": true,
715 "value": 30,
716 },
717 "id": 3,
718 "name": "element_3",
719 },
720 {
721 "data": {
722 "active": true,
723 "value": 40,
724 },
725 "id": 4,
726 "name": "element_4",
727 },
728 {
729 "data": {
730 "active": true,
731 "value": 50,
732 },
733 "id": 5,
734 "name": "element_5",
735 },
736 {
737 "data": {
738 "active": true,
739 "value": 60,
740 },
741 "id": 6,
742 "name": "element_6",
743 },
744 {
745 "data": {
746 "active": true,
747 "value": 70,
748 },
749 "id": 7,
750 "name": "element_7",
751 },
752 {
753 "data": {
754 "active": true,
755 "value": 80,
756 },
757 "id": 8,
758 "name": "element_8",
759 },
760 {
761 "data": {
762 "active": true,
763 "value": 90,
764 },
765 "id": 9,
766 "name": "element_9",
767 },
768 {
769 "data": {
770 "active": true,
771 "value": 100,
772 },
773 "id": 10,
774 "name": "element_10",
775 },
776 {
777 "truncated": true,
778 },
779 ],
780 "large_array_jsonb": [
781 {
782 "data": {
783 "active": true,
784 "value": 10,
785 },
786 "id": 1,
787 "name": "element_1",
788 },
789 {
790 "data": {
791 "active": true,
792 "value": 20,
793 },
794 "id": 2,
795 "name": "element_2",
796 },
797 {
798 "data": {
799 "active": true,
800 "value": 30,
801 },
802 "id": 3,
803 "name": "element_3",
804 },
805 {
806 "data": {
807 "active": true,
808 "value": 40,
809 },
810 "id": 4,
811 "name": "element_4",
812 },
813 {
814 "data": {
815 "active": true,
816 "value": 50,
817 },
818 "id": 5,
819 "name": "element_5",
820 },
821 {
822 "data": {
823 "active": true,
824 "value": 60,
825 },
826 "id": 6,
827 "name": "element_6",
828 },
829 {
830 "data": {
831 "active": true,
832 "value": 70,
833 },
834 "id": 7,
835 "name": "element_7",
836 },
837 {
838 "data": {
839 "active": true,
840 "value": 80,
841 },
842 "id": 8,
843 "name": "element_8",
844 },
845 {
846 "data": {
847 "active": true,
848 "value": 90,
849 },
850 "id": 9,
851 "name": "element_9",
852 },
853 {
854 "data": {
855 "active": true,
856 "value": 100,
857 },
858 "id": 10,
859 "name": "element_10",
860 },
861 {
862 "truncated": true,
863 },
864 ],
865 "name": "Large Array Item",
866 },
867 {
868 "id": 2,
869 "large_array_json": [
870 {
871 "id": 1,
872 "value": 1,
873 },
874 {
875 "id": 2,
876 "value": 2,
877 },
878 {
879 "id": 3,
880 "value": 3,
881 },
882 {
883 "id": 4,
884 "value": 4,
885 },
886 {
887 "id": 5,
888 "value": 5,
889 },
890 {
891 "id": 6,
892 "value": 6,
893 },
894 {
895 "id": 7,
896 "value": 7,
897 },
898 {
899 "id": 8,
900 "value": 8,
901 },
902 {
903 "id": 9,
904 "value": 9,
905 },
906 {
907 "id": 10,
908 "value": 10,
909 },
910 {
911 "truncated": true,
912 },
913 ],
914 "large_array_jsonb": [
915 {
916 "id": 1,
917 "value": 1,
918 },
919 {
920 "id": 2,
921 "value": 2,
922 },
923 {
924 "id": 3,
925 "value": 3,
926 },
927 {
928 "id": 4,
929 "value": 4,
930 },
931 {
932 "id": 5,
933 "value": 5,
934 },
935 {
936 "id": 6,
937 "value": 6,
938 },
939 {
940 "id": 7,
941 "value": 7,
942 },
943 {
944 "id": 8,
945 "value": 8,
946 },
947 {
948 "id": 9,
949 "value": 9,
950 },
951 {
952 "id": 10,
953 "value": 10,
954 },
955 {
956 "truncated": true,
957 },
958 ],
959 "name": "Large Array Small items",
960 },
961 {
962 "id": 3,
963 "large_array_json": [
964 {
965 "id": 1,
966 "tag": "tag1",
967 },
968 {
969 "id": 2,
970 "tag": "tag2",
971 },
972 {
973 "id": 3,
974 "tag": "tag3",
975 },
976 ],
977 "large_array_jsonb": [
978 {
979 "id": 1,
980 "tag": "tag1",
981 },
982 {
983 "id": 2,
984 "tag": "tag2",
985 },
986 {
987 "id": 3,
988 "tag": "tag3",
989 },
990 ],
991 "name": "Normal Array Item",
992 },
993 ]
994 `)
995 }
996 )
997
998 withTestDatabase('should truncate fields to maxCharacters avoid', async (db) => {
999 // Create test table with array column
1000 await db.executeQuery(`
1001 CREATE TABLE test_large_array_table (
1002 id SERIAL PRIMARY KEY,
1003 name TEXT,
1004 large_array TEXT[] -- Will hold a very large array
1005 );
1006
1007 -- Insert test data with a large array (>10KB)
1008 -- Create an array with 1000 elements to ensure it exceeds 10KB
1009 INSERT INTO test_large_array_table (name, large_array) VALUES
1010 ('Normal Array Item', ARRAY['tag1', 'tag2', 'tag3']),
1011 -- Locally testing with up to 700 Mo in size should work and not raise a JS string alloc size error
1012 (repeat('A', 5 * 1024 * 1024), ARRAY['tag1', 'tag2', 'tag3']);
1013 `)
1014
1015 // Get table metadata
1016 const { sql: tablesSql, zod: tablesZod } = pgMeta.tables.list()
1017 const tables = tablesZod.parse(await db.executeQuery(tablesSql))
1018 const testTable = tables.find((table) => table.name === 'test_large_array_table')
1019
1020 expect(testTable).toBeDefined()
1021
1022 // Generate SQL with lower maxCharacters and maxArraySize limits
1023 const sql = getTableRowsSql({
1024 table: testTable!,
1025 page: 1,
1026 limit: 10,
1027 maxCharacters: 256,
1028 })
1029
1030 // Verify the SQL contains the array truncation logic
1031 expect(sql).toMatchInlineSnapshot(`
1032 "with _base_query as (select * from public.test_large_array_table order by test_large_array_table.id asc nulls last limit 10 offset 0)
1033 select id,case
1034 when octet_length(name::text) > 256
1035 then left(name::text, 256) || '...'
1036 else name::text
1037 end as name,
1038 case
1039 when octet_length(large_array::text) > 256
1040 then
1041 case
1042 when array_ndims(large_array) = 1
1043 then
1044 (select array_cat(large_array[1:50]::text[], array['...']::text[]))::text[]
1045 else
1046 large_array[1:50]::text[]
1047 end
1048 else large_array::text[]
1049 end
1050 from _base_query;"
1051 `)
1052
1053 // Execute the SQL and verify results
1054 const start = performance.now()
1055 const queryResult = await db.executeQuery(sql)
1056 const end = performance.now()
1057 expect(end - start).lessThan(1000)
1058
1059 expect(queryResult).toMatchInlineSnapshot(`
1060 [
1061 {
1062 "id": 1,
1063 "large_array": [
1064 "tag1",
1065 "tag2",
1066 "tag3",
1067 ],
1068 "name": "Normal Array Item",
1069 },
1070 {
1071 "id": 2,
1072 "large_array": [
1073 "tag1",
1074 "tag2",
1075 "tag3",
1076 ],
1077 "name": "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA...",
1078 },
1079 ]
1080 `)
1081 })
1082
1083 withTestDatabase('should generate SQL with filtering', async (db) => {
1084 // Create test table and insert data
1085 await db.executeQuery(`
1086 CREATE TABLE test_sql_filter (
1087 id SERIAL PRIMARY KEY,
1088 name TEXT,
1089 category TEXT
1090 );
1091
1092 -- Insert test data with different categories
1093 INSERT INTO test_sql_filter (name, category) VALUES
1094 ('Test Item 1', 'A'),
1095 ('Test Item 2', 'B'),
1096 ('Test Item 3', 'A'),
1097 ('Another Item', 'A'),
1098 ('Different Item', 'C');
1099 `)
1100
1101 // Get table metadata
1102 const { sql: tablesSql, zod: tablesZod } = pgMeta.tables.list()
1103 const tables = tablesZod.parse(await db.executeQuery(tablesSql))
1104 const testTable = tables.find((table) => table.name === 'test_sql_filter')
1105
1106 expect(testTable).toBeDefined()
1107
1108 // Define filters
1109 const filters: Filter[] = [
1110 { column: 'name', operator: '~~', value: 'Test%' },
1111 { column: 'category', operator: '=', value: 'A' },
1112 ]
1113
1114 // Generate SQL with filters
1115 const sql = getTableRowsSql({
1116 table: testTable!,
1117 filters,
1118 page: 1,
1119 limit: 10,
1120 })
1121
1122 // Verify SQL generation with snapshot
1123 expect(sql).toMatchInlineSnapshot(
1124 `
1125 "with _base_query as (select * from public.test_sql_filter where name::text ~~ 'Test%' and category = 'A' order by test_sql_filter.id asc nulls last limit 10 offset 0)
1126 select id,case
1127 when octet_length(name::text) > 10240
1128 then left(name::text, 10240) || '...'
1129 else name::text
1130 end as name,case
1131 when octet_length(category::text) > 10240
1132 then left(category::text, 10240) || '...'
1133 else category::text
1134 end as category from _base_query;"
1135 `
1136 )
1137
1138 // E2E Test: Execute the SQL and verify results
1139 const queryResult = await db.executeQuery(sql)
1140 expect(queryResult.length).toBe(2) // Should only get items that match both filters
1141 expect(queryResult.map((row: any) => row.name)).toEqual(['Test Item 1', 'Test Item 3'])
1142 expect(queryResult.every((row: any) => row.category === 'A')).toBe(true)
1143 })
1144
1145 withTestDatabase('should generate SQL with sorting', async (db) => {
1146 // Create test table and insert data
1147 await db.executeQuery(`
1148 CREATE TABLE test_sql_sort (
1149 id SERIAL PRIMARY KEY,
1150 name TEXT,
1151 value INTEGER
1152 );
1153
1154 -- Insert test data with varying values
1155 INSERT INTO test_sql_sort (name, value) VALUES
1156 ('Z Item', 10),
1157 ('A Item', 30),
1158 ('M Item', 20),
1159 ('X Item', null);
1160 `)
1161
1162 // Get table metadata
1163 const { sql: tablesSql, zod: tablesZod } = pgMeta.tables.list()
1164 const tables = tablesZod.parse(await db.executeQuery(tablesSql))
1165 const testTable = tables.find((table) => table.name === 'test_sql_sort')
1166
1167 expect(testTable).toBeDefined()
1168
1169 // Define sorts
1170 const sorts: Sort[] = [
1171 { column: 'name', table: 'test_sql_sort', ascending: true, nullsFirst: false },
1172 { column: 'value', table: 'test_sql_sort', ascending: false, nullsFirst: true },
1173 ]
1174
1175 // Generate SQL with sorting
1176 const sql = getTableRowsSql({
1177 table: testTable!,
1178 sorts,
1179 page: 1,
1180 limit: 10,
1181 })
1182
1183 // Verify SQL generation with snapshot
1184 expect(sql).toMatchInlineSnapshot(
1185 `
1186 "with _base_query as (select * from public.test_sql_sort order by test_sql_sort.name asc nulls last, test_sql_sort.value desc nulls first limit 10 offset 0)
1187 select id,case
1188 when octet_length(name::text) > 10240
1189 then left(name::text, 10240) || '...'
1190 else name::text
1191 end as name,value from _base_query;"
1192 `
1193 )
1194
1195 // E2E Test: Execute the SQL and verify results
1196 const queryResult = await db.executeQuery(sql)
1197 expect(queryResult.length).toBe(4)
1198
1199 // Should be sorted by name (asc) first, then by value (desc, nulls first)
1200 expect(queryResult.map((row: any) => row.name)).toEqual([
1201 'A Item',
1202 'M Item',
1203 'X Item',
1204 'Z Item',
1205 ])
1206
1207 // The first item (A Item) should have value 30
1208 expect(queryResult[0].value).toBe(30)
1209
1210 // Check if the X Item (with null value) is before Z Item (non-null value)
1211 // due to nullsFirst: true for the value sort
1212 const xItemIndex = queryResult.findIndex((row: any) => row.name === 'X Item')
1213 const zItemIndex = queryResult.findIndex((row: any) => row.name === 'Z Item')
1214 expect(xItemIndex).toBeLessThan(zItemIndex)
1215 })
1216
1217 withTestDatabase('should generate SQL for special/quoted column names', async (db) => {
1218 // Create test table with quoted names and insert data
1219 await db.executeQuery(`
1220 CREATE TABLE "test sql spaces" (
1221 id SERIAL PRIMARY KEY,
1222 "user name" TEXT,
1223 "column-with-dashes" TEXT,
1224 "quoted""column" TEXT
1225 );
1226
1227 -- Insert test data
1228 INSERT INTO "test sql spaces" ("user name", "column-with-dashes", "quoted""column") VALUES
1229 ('User 1', 'Value 1', 'Quoted 1'),
1230 ('User 2', 'Value 2', 'Quoted 2');
1231 `)
1232
1233 // Get table metadata
1234 const { sql: tablesSql, zod: tablesZod } = pgMeta.tables.list()
1235 const tables = tablesZod.parse(await db.executeQuery(tablesSql))
1236 const testTable = tables.find((table) => table.name === 'test sql spaces')
1237
1238 expect(testTable).toBeDefined()
1239
1240 // Generate SQL
1241 const sql = getTableRowsSql({
1242 table: testTable!,
1243 page: 1,
1244 limit: 10,
1245 })
1246
1247 // Verify SQL generation with snapshot
1248 expect(sql).toMatchInlineSnapshot(
1249 `
1250 "with _base_query as (select * from public."test sql spaces" order by "test sql spaces".id asc nulls last limit 10 offset 0)
1251 select id,case
1252 when octet_length("user name"::text) > 10240
1253 then left("user name"::text, 10240) || '...'
1254 else "user name"::text
1255 end as "user name",case
1256 when octet_length("column-with-dashes"::text) > 10240
1257 then left("column-with-dashes"::text, 10240) || '...'
1258 else "column-with-dashes"::text
1259 end as "column-with-dashes",case
1260 when octet_length("quoted""column"::text) > 10240
1261 then left("quoted""column"::text, 10240) || '...'
1262 else "quoted""column"::text
1263 end as "quoted""column" from _base_query;"
1264 `
1265 )
1266
1267 // E2E Test: Execute the SQL and verify results
1268 const queryResult = await db.executeQuery(sql)
1269 expect(queryResult.length).toBe(2)
1270 expect(queryResult.map((row: any) => row['user name'])).toEqual(['User 1', 'User 2'])
1271 expect(queryResult.map((row: any) => row['column-with-dashes'])).toEqual([
1272 'Value 1',
1273 'Value 2',
1274 ])
1275 expect(queryResult.map((row: any) => row['quoted"column'])).toEqual(['Quoted 1', 'Quoted 2'])
1276 })
1277
1278 withTestDatabase('should generate SQL for tables with large text fields', async (db) => {
1279 // Create test table with large text fields
1280 await db.executeQuery(`
1281 CREATE TABLE test_large_text (
1282 id SERIAL PRIMARY KEY,
1283 small_text VARCHAR(100),
1284 large_text TEXT,
1285 json_data JSONB
1286 );
1287
1288 -- Insert test data including a large text field
1289 INSERT INTO test_large_text (small_text, large_text, json_data) VALUES
1290 ('Small text', repeat('Lorem ipsum ', 100), '{"key": "value", "nested": {"data": true}}'),
1291 ('Another small text', repeat('Dolor sit amet ', 100), '{"array": [1, 2, 3], "bool": false}');
1292 `)
1293
1294 // Get table metadata
1295 const { sql: tablesSql, zod: tablesZod } = pgMeta.tables.list()
1296 const tables = tablesZod.parse(await db.executeQuery(tablesSql))
1297 const testTable = tables.find((table) => table.name === 'test_large_text')
1298
1299 expect(testTable).toBeDefined()
1300
1301 // Generate SQL
1302 const sql = getTableRowsSql({
1303 table: testTable!,
1304 page: 1,
1305 limit: 10,
1306 })
1307
1308 // Verify SQL generation with snapshot
1309 expect(sql).toMatchInlineSnapshot(
1310 `
1311 "with _base_query as (select * from public.test_large_text order by test_large_text.id asc nulls last limit 10 offset 0)
1312 select id,case
1313 when octet_length(small_text::text) > 10240
1314 then left(small_text::text, 10240) || '...'
1315 else small_text::text
1316 end as small_text,case
1317 when octet_length(large_text::text) > 10240
1318 then left(large_text::text, 10240) || '...'
1319 else large_text::text
1320 end as large_text,case
1321 when octet_length(json_data::text) > 10240
1322 then left(json_data::text, 10240) || '...'
1323 else json_data::text
1324 end as json_data from _base_query;"
1325 `
1326 )
1327
1328 // E2E Test: Execute the SQL and verify results
1329 const queryResult = await db.executeQuery(sql)
1330 expect(queryResult.length).toBe(2)
1331 expect(queryResult.map((row: any) => row.small_text)).toEqual([
1332 'Small text',
1333 'Another small text',
1334 ])
1335
1336 expect(queryResult[0].large_text.startsWith('Lorem ipsum')).toBe(true)
1337 expect(queryResult[1].large_text.startsWith('Dolor sit amet')).toBe(true)
1338
1339 expect(JSON.parse(queryResult[0].json_data)).toHaveProperty('key', 'value')
1340 expect(JSON.parse(queryResult[1].json_data)).toHaveProperty('array')
1341 })
1342
1343 withTestDatabase('should generate SQL with pagination', async (db) => {
1344 // Create test table and insert multiple rows for pagination
1345 await db.executeQuery(`
1346 CREATE TABLE test_pagination (
1347 id SERIAL PRIMARY KEY,
1348 name TEXT
1349 );
1350
1351 -- Insert 15 rows for pagination testing
1352 INSERT INTO test_pagination (name)
1353 SELECT 'Item ' || i FROM generate_series(1, 15) i;
1354 `)
1355
1356 // Get table metadata
1357 const { sql: tablesSql, zod: tablesZod } = pgMeta.tables.list()
1358 const tables = tablesZod.parse(await db.executeQuery(tablesSql))
1359 const testTable = tables.find((table) => table.name === 'test_pagination')
1360
1361 expect(testTable).toBeDefined()
1362
1363 // Generate SQL for page 1 (5 items)
1364 const sql1 = getTableRowsSql({
1365 table: testTable!,
1366 page: 1,
1367 limit: 5,
1368 })
1369
1370 expect(sql1).toMatchInlineSnapshot(
1371 `
1372 "with _base_query as (select * from public.test_pagination order by test_pagination.id asc nulls last limit 5 offset 0)
1373 select id,case
1374 when octet_length(name::text) > 10240
1375 then left(name::text, 10240) || '...'
1376 else name::text
1377 end as name from _base_query;"
1378 `
1379 )
1380
1381 const page1Result = await db.executeQuery(sql1)
1382 expect(page1Result.length).toBe(5)
1383 expect(page1Result.map((row: any) => row.name)).toEqual([
1384 'Item 1',
1385 'Item 2',
1386 'Item 3',
1387 'Item 4',
1388 'Item 5',
1389 ])
1390
1391 const sql2 = getTableRowsSql({
1392 table: testTable!,
1393 page: 2,
1394 limit: 5,
1395 })
1396
1397 // Verify SQL generation for page 2
1398 expect(sql2).toMatchInlineSnapshot(
1399 `
1400 "with _base_query as (select * from public.test_pagination order by test_pagination.id asc nulls last limit 5 offset 5)
1401 select id,case
1402 when octet_length(name::text) > 10240
1403 then left(name::text, 10240) || '...'
1404 else name::text
1405 end as name from _base_query;"
1406 `
1407 )
1408
1409 const page2Result = await db.executeQuery(sql2)
1410 expect(page2Result.length).toBe(5)
1411 expect(page2Result.map((row: any) => row.name)).toEqual([
1412 'Item 6',
1413 'Item 7',
1414 'Item 8',
1415 'Item 9',
1416 'Item 10',
1417 ])
1418 })
1419
1420 withTestDatabase('should generate SQL for view', async (db) => {
1421 // Create table and view
1422 await db.executeQuery(`
1423 CREATE TABLE test_view_source (
1424 id SERIAL PRIMARY KEY,
1425 name TEXT,
1426 active BOOLEAN
1427 );
1428
1429 -- Insert test data
1430 INSERT INTO test_view_source (name, active) VALUES
1431 ('Active Item 1', true),
1432 ('Inactive Item', false),
1433 ('Active Item 2', true);
1434
1435 -- Create view that only shows active items
1436 CREATE VIEW test_sql_view AS
1437 SELECT id, name, active FROM test_view_source WHERE active = true;
1438 `)
1439
1440 // Get view metadata
1441 const { sql: viewsSql, zod: viewsZod } = pgMeta.views.list()
1442 const views = viewsZod.parse(await db.executeQuery(viewsSql))
1443 const testView = views.find((view) => view.name === 'test_sql_view')
1444
1445 expect(testView).toBeDefined()
1446
1447 // Generate SQL
1448 const sql = getTableRowsSql({
1449 table: testView!,
1450 page: 1,
1451 limit: 10,
1452 })
1453
1454 // Verify SQL generation with snapshot
1455 expect(sql).toMatchInlineSnapshot(
1456 `
1457 "with _base_query as (select * from public.test_sql_view order by test_sql_view.id asc nulls last limit 10 offset 0)
1458 select id,case
1459 when octet_length(name::text) > 10240
1460 then left(name::text, 10240) || '...'
1461 else name::text
1462 end as name,active from _base_query;"
1463 `
1464 )
1465
1466 // E2E Test: Execute the SQL and verify results
1467 const queryResult = await db.executeQuery(sql)
1468 expect(queryResult.length).toBe(2) // Only active items should be in the view
1469 expect(queryResult.map((row: any) => row.name)).toEqual(['Active Item 1', 'Active Item 2'])
1470 expect(queryResult.every((row: any) => row.active === true)).toBe(true)
1471 })
1472
1473 withTestDatabase('should generate SQL for materialized view', async (db) => {
1474 // Create table and materialized view
1475 await db.executeQuery(`
1476 CREATE TABLE test_mv_source (
1477 id SERIAL PRIMARY KEY,
1478 name TEXT,
1479 value NUMERIC
1480 );
1481
1482 -- Insert test data
1483 INSERT INTO test_mv_source (name, value) VALUES
1484 ('Item 1', 10.5),
1485 ('Item 2', -5.25),
1486 ('Item 3', 20);
1487
1488 -- Create materialized view that only includes positive values
1489 CREATE MATERIALIZED VIEW test_sql_mv AS
1490 SELECT id, name, value FROM test_mv_source WHERE value > 0;
1491 `)
1492
1493 // Get materialized view metadata
1494 const { sql: mvSql, zod: mvZod } = pgMeta.materializedViews.list()
1495 const materializedViews = mvZod.parse(await db.executeQuery(mvSql))
1496 const testMv = materializedViews.find((mv) => mv.name === 'test_sql_mv')
1497
1498 expect(testMv).toBeDefined()
1499
1500 // Generate SQL
1501 const sql = getTableRowsSql({
1502 table: testMv!,
1503 page: 1,
1504 limit: 10,
1505 })
1506
1507 // Verify SQL generation with snapshot
1508 expect(sql).toMatchInlineSnapshot(
1509 `
1510 "with _base_query as (select * from public.test_sql_mv order by test_sql_mv.id asc nulls last limit 10 offset 0)
1511 select id,case
1512 when octet_length(name::text) > 10240
1513 then left(name::text, 10240) || '...'
1514 else name::text
1515 end as name,value from _base_query;"
1516 `
1517 )
1518
1519 // E2E Test: Execute the SQL and verify results
1520 const queryResult = await db.executeQuery(sql)
1521 expect(queryResult.length).toBe(2) // Only items with positive values
1522 expect(queryResult.map((row: any) => row.name)).toEqual(['Item 1', 'Item 3'])
1523 expect(queryResult.every((row: any) => row.value > 0)).toBe(true)
1524 })
1525
1526 withTestDatabase('should generate SQL for foreign table', async (db) => {
1527 // Set up a foreign table with the file_fdw extension
1528 await db.executeQuery(`
1529 -- Create the extension if it doesn't exist
1530 CREATE EXTENSION IF NOT EXISTS file_fdw;
1531
1532 -- Create a foreign server
1533 DROP SERVER IF EXISTS file_server2 CASCADE;
1534 CREATE SERVER file_server2 FOREIGN DATA WRAPPER file_fdw;
1535
1536 -- Create a table to export data from
1537 CREATE TABLE source_for_foreign_test (
1538 id SERIAL PRIMARY KEY,
1539 name TEXT,
1540 description TEXT
1541 );
1542
1543 -- Insert test data
1544 INSERT INTO source_for_foreign_test (name, description) VALUES
1545 ('Foreign Item 1', 'Description 1'),
1546 ('Foreign Item 2', 'Description 2');
1547
1548 -- Export to CSV for the foreign table
1549 COPY source_for_foreign_test TO '/tmp/foreign_test2.csv' WITH (FORMAT csv, HEADER);
1550
1551 -- Create the foreign table
1552 CREATE FOREIGN TABLE test_sql_foreign (
1553 id INT,
1554 name TEXT,
1555 description TEXT
1556 ) SERVER file_server2
1557 OPTIONS (filename '/tmp/foreign_test2.csv', format 'csv', header 'true');
1558 `)
1559
1560 // Get foreign table metadata
1561 const { sql: ftSql, zod: ftZod } = pgMeta.foreignTables.list()
1562 const foreignTables = ftZod.parse(await db.executeQuery(ftSql))
1563 const testFt = foreignTables.find((ft) => ft.name === 'test_sql_foreign')
1564
1565 expect(testFt).toBeDefined()
1566
1567 // Generate SQL
1568 const sql = getTableRowsSql({
1569 table: testFt!,
1570 page: 1,
1571 limit: 10,
1572 })
1573
1574 // Verify SQL generation with snapshot
1575 expect(sql).toMatchInlineSnapshot(
1576 `
1577 "with _base_query as (select * from public.test_sql_foreign order by test_sql_foreign.id asc nulls last limit 10 offset 0)
1578 select id,case
1579 when octet_length(name::text) > 10240
1580 then left(name::text, 10240) || '...'
1581 else name::text
1582 end as name,case
1583 when octet_length(description::text) > 10240
1584 then left(description::text, 10240) || '...'
1585 else description::text
1586 end as description from _base_query;"
1587 `
1588 )
1589
1590 // E2E Test: Execute the SQL and verify results
1591 const queryResult = await db.executeQuery(sql)
1592 expect(queryResult).toMatchInlineSnapshot(`
1593 [
1594 {
1595 "description": "Description 1",
1596 "id": 1,
1597 "name": "Foreign Item 1",
1598 },
1599 {
1600 "description": "Description 2",
1601 "id": 2,
1602 "name": "Foreign Item 2",
1603 },
1604 ]
1605 `)
1606 })
1607 })
1608 withTestDatabase('should handle large multi-dimensional arrays correctly', async (db) => {
1609 // Create test table with multi-dimensional arrays
1610 await db.executeQuery(`
1611 CREATE TABLE public.monitor_data (
1612 subject_id TEXT,
1613 "timestamp" TIMESTAMP[],
1614 "PPG" FLOAT8[][],
1615 "ACC" FLOAT8[][]
1616 );
1617
1618 INSERT INTO public.monitor_data (subject_id, "timestamp", "PPG", "ACC")
1619 VALUES (
1620 'subject-1',
1621 ARRAY['2024-01-01 00:00:00'::timestamp, '2024-01-02 00:00:00'::timestamp],
1622 ARRAY[
1623 [1.1, 1.2, 1.3, 1.4, 1.5, 1.6],
1624 [2.1, 2.2, 2.3, 2.4, 2.5, 2.6],
1625 [3.1, 3.2, 3.3, 3.4, 3.5, 3.6]
1626 ]::FLOAT8[][],
1627 ARRAY[
1628 [4.1, 4.2, 4.3, 4.4, 4.5, 4.6],
1629 [5.1, 5.2, 5.3, 5.4, 5.5, 5.6],
1630 [6.1, 6.2, 6.3, 6.4, 6.5, 6.6]
1631 ]::FLOAT8[][]
1632 );
1633
1634 INSERT INTO public.monitor_data (subject_id, "timestamp", "PPG", "ACC")
1635 VALUES (
1636 'subject-large',
1637 -- large 1D timestamp array (e.g., 1000 timestamps)
1638 ARRAY(
1639 SELECT generate_series('2024-01-01'::timestamp, '2024-01-01'::timestamp + interval '999 minutes', '1 minute')
1640 ),
1641 -- large 2D float8 arrays (e.g., 1000 x 6)
1642 ARRAY(
1643 SELECT ARRAY[
1644 random(), random(), random(), random(), random(), random()
1645 ]::float8[]
1646 FROM generate_series(1, 1000)
1647 )::float8[][],
1648 ARRAY(
1649 SELECT ARRAY[
1650 random(), random(), random(), random(), random(), random()
1651 ]::float8[]
1652 FROM generate_series(1, 1000)
1653 )::float8[][]
1654 );
1655 `)
1656
1657 // Get table metadata
1658 const { sql: tablesSql, zod: tablesZod } = pgMeta.tables.list()
1659 const tables = tablesZod.parse(await db.executeQuery(tablesSql))
1660 const testTable = tables.find((table) => table.name === 'monitor_data')
1661
1662 expect(testTable).toBeDefined()
1663
1664 // Generate SQL with default settings
1665 const sql = getTableRowsSql({
1666 table: testTable!,
1667 page: 1,
1668 limit: 10,
1669 })
1670
1671 // Verify SQL generation with snapshot
1672 expect(sql).toMatchInlineSnapshot(`
1673 "with _base_query as (select * from public.monitor_data order by monitor_data.subject_id asc nulls last limit 10 offset 0)
1674 select case
1675 when octet_length(subject_id::text) > 10240
1676 then left(subject_id::text, 10240) || '...'
1677 else subject_id::text
1678 end as subject_id,
1679 case
1680 when octet_length("timestamp"::text) > 10240
1681 then
1682 case
1683 when array_ndims("timestamp") = 1
1684 then
1685 (select array_cat("timestamp"[1:50]::text[], array['...']::text[]))::text[]
1686 else
1687 "timestamp"[1:50]::text[]
1688 end
1689 else "timestamp"::text[]
1690 end
1691 ,
1692 case
1693 when octet_length("PPG"::text) > 10240
1694 then
1695 case
1696 when array_ndims("PPG") = 1
1697 then
1698 (select array_cat("PPG"[1:50]::text[], array['...']::text[]))::text[]
1699 else
1700 "PPG"[1:50]::text[]
1701 end
1702 else "PPG"::text[]
1703 end
1704 ,
1705 case
1706 when octet_length("ACC"::text) > 10240
1707 then
1708 case
1709 when array_ndims("ACC") = 1
1710 then
1711 (select array_cat("ACC"[1:50]::text[], array['...']::text[]))::text[]
1712 else
1713 "ACC"[1:50]::text[]
1714 end
1715 else "ACC"::text[]
1716 end
1717 from _base_query;"
1718 `)
1719
1720 // Execute the SQL and verify results
1721 const queryResult = await db.executeQuery(sql)
1722 expect(queryResult.length).toBe(2)
1723
1724 // Verify the first row (small arrays)
1725 const smallRow = queryResult.find((row: any) => row.subject_id === 'subject-1')
1726 expect(smallRow).toBeDefined()
1727 expect(smallRow.timestamp).toHaveLength(2)
1728 expect(smallRow.PPG).toHaveLength(3)
1729 expect(smallRow.PPG[0]).toHaveLength(6)
1730 expect(smallRow.ACC).toHaveLength(3)
1731 expect(smallRow.ACC[0]).toHaveLength(6)
1732
1733 // Verify the second row (large arrays)
1734 const largeRow = queryResult.find((row: any) => row.subject_id === 'subject-large')
1735 expect(largeRow).toBeDefined()
1736 expect(largeRow.timestamp).toHaveLength(51) // Has the extra '...' element
1737 expect(largeRow.PPG).toHaveLength(50)
1738 expect(largeRow.PPG[0]).toHaveLength(6)
1739 expect(largeRow.ACC).toHaveLength(50)
1740 expect(largeRow.ACC[0]).toHaveLength(6)
1741
1742 // Test with custom maxArraySize
1743 const sqlWithCustomSize = getTableRowsSql({
1744 table: testTable!,
1745 page: 1,
1746 limit: 10,
1747 maxArraySize: 10,
1748 })
1749
1750 const customSizeResult = await db.executeQuery(sqlWithCustomSize)
1751 const largeRowCustom = customSizeResult.find((row: any) => row.subject_id === 'subject-large')
1752 expect(largeRowCustom.timestamp).toHaveLength(11) // Has the extra '...' element
1753 expect(largeRowCustom.PPG).toHaveLength(10) // multi-dimentional array are truncated
1754 expect(largeRowCustom.ACC).toHaveLength(10)
1755 })
1756
1757 withTestDatabase('should handle reserved keyword "collation" as column name', async (db) => {
1758 // Create a table with a column named "collation" (a PostgreSQL reserved keyword)
1759 await db.executeQuery(`
1760 CREATE TABLE IF NOT EXISTS "public"."test" (
1761 id SERIAL PRIMARY KEY,
1762 "collation" TEXT
1763 );
1764
1765 DELETE FROM "public"."test";
1766 INSERT INTO "public"."test" ("collation")
1767 VALUES
1768 ('value1'),
1769 ('value2'),
1770 ('value3');
1771 `)
1772
1773 // Get table metadata
1774 const { sql: tablesSql, zod: tablesZod } = pgMeta.tables.list()
1775 const tables = tablesZod.parse(await db.executeQuery(tablesSql))
1776 const testTable = tables.find((table) => table.name === 'test')
1777
1778 expect(testTable).toBeDefined()
1779
1780 // Generate SQL - this should properly quote the "collation" column name
1781 const sql = getTableRowsSql({
1782 table: testTable!,
1783 page: 1,
1784 limit: 100,
1785 })
1786
1787 // Verify SQL generation - the "collation" column should be properly quoted
1788 expect(sql).toMatchInlineSnapshot(`
1789 "with _base_query as (select * from public.test order by test.id asc nulls last limit 100 offset 0)
1790 select id,case
1791 when octet_length("collation"::text) > 10240
1792 then left("collation"::text, 10240) || '...'
1793 else "collation"::text
1794 end as "collation" from _base_query;"
1795 `)
1796
1797 // Execute the generated SQL and verify the results
1798 const queryResult = await db.executeQuery(sql)
1799 expect(queryResult.length).toBe(3)
1800 expect(queryResult[0].collation).toBe('value1')
1801 expect(queryResult[1].collation).toBe('value2')
1802 expect(queryResult[2].collation).toBe('value3')
1803
1804 // Test with ORDER BY on the collation column
1805 const sqlWithOrder = getTableRowsSql({
1806 table: testTable!,
1807 page: 1,
1808 limit: 100,
1809 sorts: [{ table: 'test', column: 'collation', ascending: true, nullsFirst: false }],
1810 })
1811
1812 // Verify the ORDER BY clause properly quotes the collation column
1813 expect(sqlWithOrder).toMatchInlineSnapshot(`
1814 "with _base_query as (select * from public.test order by test."collation" asc nulls last limit 100 offset 0)
1815 select id,case
1816 when octet_length("collation"::text) > 10240
1817 then left("collation"::text, 10240) || '...'
1818 else "collation"::text
1819 end as "collation" from _base_query;"
1820 `)
1821
1822 const queryResultWithOrder = await db.executeQuery(sqlWithOrder)
1823 expect(queryResultWithOrder.length).toBe(3)
1824 expect(queryResultWithOrder[0].collation).toBe('value1')
1825 expect(queryResultWithOrder[1].collation).toBe('value2')
1826 expect(queryResultWithOrder[2].collation).toBe('value3')
1827
1828 // Test with FILTER on the collation column
1829 const sqlWithFilter = getTableRowsSql({
1830 table: testTable!,
1831 page: 1,
1832 limit: 100,
1833 filters: [{ column: 'collation', operator: '=', value: 'value2' }],
1834 })
1835
1836 // Verify the WHERE clause properly quotes the collation column
1837 expect(sqlWithFilter).toMatchInlineSnapshot(`
1838 "with _base_query as (select * from public.test where "collation" = 'value2' order by test.id asc nulls last limit 100 offset 0)
1839 select id,case
1840 when octet_length("collation"::text) > 10240
1841 then left("collation"::text, 10240) || '...'
1842 else "collation"::text
1843 end as "collation" from _base_query;"
1844 `)
1845
1846 const queryResultWithFilter = await db.executeQuery(sqlWithFilter)
1847 expect(queryResultWithFilter.length).toBe(1)
1848 expect(queryResultWithFilter[0].collation).toBe('value2')
1849 })
1850})