1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17 package com.healthmarketscience.jackcess;
18
19 import java.io.File;
20 import java.io.FileNotFoundException;
21 import java.io.IOException;
22 import java.math.BigDecimal;
23 import java.text.DateFormat;
24 import java.text.SimpleDateFormat;
25 import java.time.ZoneId;
26 import java.util.ArrayList;
27 import java.util.Arrays;
28 import java.util.Calendar;
29 import java.util.Date;
30 import java.util.HashSet;
31 import java.util.LinkedHashMap;
32 import java.util.List;
33 import java.util.Map;
34 import java.util.Set;
35 import java.util.TimeZone;
36 import java.util.TreeSet;
37 import java.util.UUID;
38 import java.util.stream.Collectors;
39
40 import static com.healthmarketscience.jackcess.Database.*;
41 import static com.healthmarketscience.jackcess.DatabaseBuilder.*;
42 import static com.healthmarketscience.jackcess.TestUtil.*;
43 import com.healthmarketscience.jackcess.impl.ColumnImpl;
44 import com.healthmarketscience.jackcess.impl.DatabaseImpl;
45 import static com.healthmarketscience.jackcess.impl.JetFormatTest.*;
46 import com.healthmarketscience.jackcess.impl.RowIdImpl;
47 import com.healthmarketscience.jackcess.impl.RowImpl;
48 import com.healthmarketscience.jackcess.impl.TableImpl;
49 import com.healthmarketscience.jackcess.util.RowFilterTest;
50 import junit.framework.TestCase;
51
52
53
54
55
56 @SuppressWarnings("deprecation")
57 public class DatabaseTest extends TestCase
58 {
59 public DatabaseTest(String name) throws Exception {
60 super(name);
61 }
62
63 public void testInvalidTableDefs() throws Exception {
64 for (final FileFormat fileFormat : SUPPORTED_FILEFORMATS) {
65 Database db = create(fileFormat);
66
67 try {
68 newTable("test").toTable(db);
69 fail("created table with no columns?");
70 } catch(IllegalArgumentException e) {
71
72 }
73
74 try {
75 newTable("test")
76 .addColumn(newColumn("A", DataType.TEXT))
77 .addColumn(newColumn("a", DataType.MEMO))
78 .toTable(db);
79 fail("created table with duplicate column names?");
80 } catch(IllegalArgumentException e) {
81
82 }
83
84 try {
85 newTable("test")
86 .addColumn(newColumn("A", DataType.TEXT)
87 .setLengthInUnits(352))
88 .toTable(db);
89 fail("created table with invalid column length?");
90 } catch(IllegalArgumentException e) {
91
92 }
93
94 try {
95 newTable("test")
96 .addColumn(newColumn("A_" + createString(70), DataType.TEXT))
97 .toTable(db);
98 fail("created table with too long column name?");
99 } catch(IllegalArgumentException e) {
100
101 }
102
103 newTable("test")
104 .addColumn(newColumn("A", DataType.TEXT))
105 .toTable(db);
106
107
108 try {
109 newTable("Test")
110 .addColumn(newColumn("A", DataType.TEXT))
111 .toTable(db);
112 fail("create duplicate tables?");
113 } catch(IllegalArgumentException e) {
114
115 }
116
117 db.close();
118 }
119 }
120
121 public void testReadDeletedRows() throws Exception {
122 for (final TestDB testDB : TestDB.getSupportedForBasename(Basename.DEL, true)) {
123 Table table = open(testDB).getTable("Table");
124 int rows = 0;
125 while (table.getNextRow() != null) {
126 rows++;
127 }
128 assertEquals(2, rows);
129 table.getDatabase().close();
130 }
131 }
132
133 public void testGetColumns() throws Exception {
134 for (final TestDB testDB : SUPPORTED_DBS_TEST_FOR_READ) {
135
136 List<? extends Column> columns = open(testDB).getTable("Table1").getColumns();
137 assertEquals(9, columns.size());
138 checkColumn(columns, 0, "A", DataType.TEXT);
139 checkColumn(columns, 1, "B", DataType.TEXT);
140 checkColumn(columns, 2, "C", DataType.BYTE);
141 checkColumn(columns, 3, "D", DataType.INT);
142 checkColumn(columns, 4, "E", DataType.LONG);
143 checkColumn(columns, 5, "F", DataType.DOUBLE);
144 checkColumn(columns, 6, "G", DataType.SHORT_DATE_TIME);
145 checkColumn(columns, 7, "H", DataType.MONEY);
146 checkColumn(columns, 8, "I", DataType.BOOLEAN);
147 }
148 }
149
150 private static void checkColumn(
151 List<? extends Column> columns, int columnNumber, String name,
152 DataType dataType)
153 throws Exception
154 {
155 Column column = columns.get(columnNumber);
156 assertEquals(name, column.getName());
157 assertEquals(dataType, column.getType());
158 }
159
160 public void testGetNextRow() throws Exception {
161 for (final TestDB testDB : SUPPORTED_DBS_TEST_FOR_READ) {
162 final Database db = open(testDB);
163 db.setDateTimeType(DateTimeType.DATE);
164
165 assertEquals(4, db.getTableNames().size());
166 final Table table = db.getTable("Table1");
167
168 Row row1 = table.getNextRow();
169 Row row2 = table.getNextRow();
170
171 if(!"abcdefg".equals(row1.get("A"))) {
172 Row tmpRow = row1;
173 row1 = row2;
174 row2 = tmpRow;
175 }
176
177 checkTestDBTable1RowABCDEFG(testDB, table, row1);
178 checkTestDBTable1RowA(testDB, table, row2);
179
180 db.close();
181 }
182 }
183
184 public void testCreate() throws Exception {
185 for (final FileFormat fileFormat : SUPPORTED_FILEFORMATS) {
186 Database db = create(fileFormat);
187 assertEquals(0, db.getTableNames().size());
188 db.close();
189 }
190 }
191
192 public void testDeleteCurrentRow() throws Exception {
193
194
195 for (final FileFormat fileFormat : SUPPORTED_FILEFORMATS) {
196 Database db = createMem(fileFormat);
197 createTestTable(db);
198 Map<String,Object> row1 = createTestRowMap("Tim1");
199 Map<String,Object> row2 = createTestRowMap("Tim2");
200 Map<String,Object> row3 = createTestRowMap("Tim3");
201 Table table = db.getTable("Test");
202 List<Map<String,Object>> rows = Arrays.asList(row1, row2, row3);
203 table.addRowsFromMaps(rows);
204 assertRowCount(3, table);
205
206 table.reset();
207 table.getNextRow();
208 table.getNextRow();
209 table.getDefaultCursor().deleteCurrentRow();
210
211 table.reset();
212
213 Map<String, Object> outRow = table.getNextRow();
214 assertEquals("Tim1", outRow.get("A"));
215 outRow = table.getNextRow();
216 assertEquals("Tim3", outRow.get("A"));
217 assertRowCount(2, table);
218
219 db.close();
220
221
222 db = createMem(fileFormat);
223 createTestTable(db);
224 Object[] row = createTestRow();
225 table = db.getTable("Test");
226 for (int i = 0; i < 10; i++) {
227 row[3] = i;
228 table.addRow(row);
229 }
230 row[3] = 1974;
231 assertRowCount(10, table);
232 table.reset();
233 table.getNextRow();
234 table.getDefaultCursor().deleteCurrentRow();
235 assertRowCount(9, table);
236 table.reset();
237 table.getNextRow();
238 table.getDefaultCursor().deleteCurrentRow();
239 assertRowCount(8, table);
240 table.reset();
241 for (int i = 0; i < 8; i++) {
242 table.getNextRow();
243 }
244 table.getDefaultCursor().deleteCurrentRow();
245 assertRowCount(7, table);
246 table.addRow(row);
247 assertRowCount(8, table);
248 table.reset();
249 for (int i = 0; i < 3; i++) {
250 table.getNextRow();
251 }
252 table.getDefaultCursor().deleteCurrentRow();
253 assertRowCount(7, table);
254 table.reset();
255 assertEquals(2, table.getNextRow().get("D"));
256
257 db.close();
258 }
259 }
260
261 public void testDeleteRow() throws Exception {
262
263
264 for (final FileFormat fileFormat : SUPPORTED_FILEFORMATS) {
265 Database db = createMem(fileFormat);
266 createTestTable(db);
267 Table table = db.getTable("Test");
268 for(int i = 0; i < 10; ++i) {
269 table.addRowFromMap(createTestRowMap("Tim" + i));
270 }
271 assertRowCount(10, table);
272
273 table.reset();
274
275 List<Row> rows = RowFilterTest.toList(table);
276
277 Row r1 = rows.remove(7);
278 Row r2 = rows.remove(3);
279 assertEquals(8, rows.size());
280
281 assertSame(r2, table.deleteRow(r2));
282 assertSame(r1, table.deleteRow(r1));
283
284 assertTable(rows, table);
285
286 table.deleteRow(r2);
287 table.deleteRow(r1);
288
289 assertTable(rows, table);
290 }
291 }
292
293 public void testMissingFile() throws Exception {
294 File bogusFile = new File("fooby-dooby.mdb");
295 assertTrue(!bogusFile.exists());
296 try {
297 newDatabase(bogusFile).setReadOnly(true).
298 setAutoSync(getTestAutoSync()).open();
299 fail("FileNotFoundException should have been thrown");
300 } catch(FileNotFoundException e) {
301 }
302 assertTrue(!bogusFile.exists());
303 }
304
305 public void testReadWithDeletedCols() throws Exception {
306 for (final TestDB testDB : TestDB.getSupportedForBasename(Basename.DEL_COL, true)) {
307 Table table = open(testDB).getTable("Table1");
308
309 Map<String, Object> expectedRow0 = new LinkedHashMap<String, Object>();
310 expectedRow0.put("id", 0);
311 expectedRow0.put("id2", 2);
312 expectedRow0.put("data", "foo");
313 expectedRow0.put("data2", "foo2");
314
315 Map<String, Object> expectedRow1 = new LinkedHashMap<String, Object>();
316 expectedRow1.put("id", 3);
317 expectedRow1.put("id2", 5);
318 expectedRow1.put("data", "bar");
319 expectedRow1.put("data2", "bar2");
320
321 int rowNum = 0;
322 Map<String, Object> row = null;
323 while ((row = table.getNextRow()) != null) {
324 if(rowNum == 0) {
325 assertEquals(expectedRow0, row);
326 } else if(rowNum == 1) {
327 assertEquals(expectedRow1, row);
328 } else if(rowNum >= 2) {
329 fail("should only have 2 rows");
330 }
331 rowNum++;
332 }
333
334 table.getDatabase().close();
335 }
336 }
337
338 public void testCurrency() throws Exception {
339 for (final FileFormat fileFormat : SUPPORTED_FILEFORMATS) {
340 Database db = create(fileFormat);
341
342 Table table = newTable("test")
343 .addColumn(newColumn("A", DataType.MONEY))
344 .toTable(db);
345
346 table.addRow(new BigDecimal("-2341234.03450"));
347 table.addRow(37L);
348 table.addRow("10000.45");
349
350 table.reset();
351
352 List<Object> foundValues = new ArrayList<Object>();
353 Map<String, Object> row = null;
354 while((row = table.getNextRow()) != null) {
355 foundValues.add(row.get("A"));
356 }
357
358 assertEquals(Arrays.asList(
359 new BigDecimal("-2341234.0345"),
360 new BigDecimal("37.0000"),
361 new BigDecimal("10000.4500")),
362 foundValues);
363
364 try {
365 table.addRow(new BigDecimal("342523234145343543.3453"));
366 fail("IOException should have been thrown");
367 } catch(IOException e) {
368
369 }
370
371 db.close();
372 }
373 }
374
375 public void testGUID() throws Exception
376 {
377 for (final FileFormat fileFormat : SUPPORTED_FILEFORMATS) {
378 Database db = create(fileFormat);
379
380 Table table = newTable("test")
381 .addColumn(newColumn("A", DataType.GUID))
382 .toTable(db);
383
384 table.addRow("{32A59F01-AA34-3E29-453F-4523453CD2E6}");
385 table.addRow("{32a59f01-aa34-3e29-453f-4523453cd2e6}");
386 table.addRow("{11111111-1111-1111-1111-111111111111}");
387 table.addRow(" {FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF} ");
388 table.addRow(UUID.fromString("32a59f01-1234-3e29-4aaf-4523453cd2e6"));
389
390 table.reset();
391
392 List<Object> foundValues = new ArrayList<Object>();
393 Map<String, Object> row = null;
394 while((row = table.getNextRow()) != null) {
395 foundValues.add(row.get("A"));
396 }
397
398 assertEquals(Arrays.asList(
399 "{32A59F01-AA34-3E29-453F-4523453CD2E6}",
400 "{32A59F01-AA34-3E29-453F-4523453CD2E6}",
401 "{11111111-1111-1111-1111-111111111111}",
402 "{FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF}",
403 "{32A59F01-1234-3E29-4AAF-4523453CD2E6}"),
404 foundValues);
405
406 try {
407 table.addRow("3245234");
408 fail("IOException should have been thrown");
409 } catch(IOException e) {
410
411 }
412
413 db.close();
414 }
415 }
416
417 public void testNumeric() throws Exception
418 {
419 for (final FileFormat fileFormat : SUPPORTED_FILEFORMATS) {
420 Database db = create(fileFormat);
421
422 ColumnBuilder col = newColumn("A", DataType.NUMERIC)
423 .setScale(4).setPrecision(8).toColumn();
424 assertTrue(col.getType().isVariableLength());
425
426 Table table = newTable("test")
427 .addColumn(col)
428 .addColumn(newColumn("B", DataType.NUMERIC)
429 .setScale(8).setPrecision(28))
430 .toTable(db);
431
432 table.addRow(new BigDecimal("-1234.03450"),
433 new BigDecimal("23923434453436.36234219"));
434 table.addRow(37L, 37L);
435 table.addRow("1000.45", "-3452345321000");
436
437 table.reset();
438
439 List<Object> foundSmallValues = new ArrayList<Object>();
440 List<Object> foundBigValues = new ArrayList<Object>();
441 Map<String, Object> row = null;
442 while((row = table.getNextRow()) != null) {
443 foundSmallValues.add(row.get("A"));
444 foundBigValues.add(row.get("B"));
445 }
446
447 assertEquals(Arrays.asList(
448 new BigDecimal("-1234.0345"),
449 new BigDecimal("37.0000"),
450 new BigDecimal("1000.4500")),
451 foundSmallValues);
452 assertEquals(Arrays.asList(
453 new BigDecimal("23923434453436.36234219"),
454 new BigDecimal("37.00000000"),
455 new BigDecimal("-3452345321000.00000000")),
456 foundBigValues);
457
458 try {
459 table.addRow(new BigDecimal("3245234.234"),
460 new BigDecimal("3245234.234"));
461 fail("IOException should have been thrown");
462 } catch(IOException e) {
463
464 }
465
466 db.close();
467 }
468 }
469
470 public void testFixedNumeric() throws Exception
471 {
472 for (final TestDB testDB : TestDB.getSupportedForBasename(Basename.FIXED_NUMERIC)) {
473 Database db = openCopy(testDB);
474 Table t = db.getTable("test");
475
476 boolean first = true;
477 for(Column col : t.getColumns()) {
478 if(first) {
479 assertTrue(col.isVariableLength());
480 assertEquals(DataType.MEMO, col.getType());
481 first = false;
482 } else {
483 assertFalse(col.isVariableLength());
484 assertEquals(DataType.NUMERIC, col.getType());
485 }
486 }
487
488 Map<String, Object> row = t.getNextRow();
489 assertEquals("some data", row.get("col1"));
490 assertEquals(new BigDecimal("1"), row.get("col2"));
491 assertEquals(new BigDecimal("0"), row.get("col3"));
492 assertEquals(new BigDecimal("0"), row.get("col4"));
493 assertEquals(new BigDecimal("4"), row.get("col5"));
494 assertEquals(new BigDecimal("-1"), row.get("col6"));
495 assertEquals(new BigDecimal("1"), row.get("col7"));
496
497 Object[] tmpRow = new Object[]{
498 "foo", new BigDecimal("1"), new BigDecimal(3), new BigDecimal("13"),
499 new BigDecimal("-17"), new BigDecimal("0"), new BigDecimal("8734")};
500 t.addRow(tmpRow);
501 t.reset();
502
503 t.getNextRow();
504 row = t.getNextRow();
505 assertEquals(tmpRow[0], row.get("col1"));
506 assertEquals(tmpRow[1], row.get("col2"));
507 assertEquals(tmpRow[2], row.get("col3"));
508 assertEquals(tmpRow[3], row.get("col4"));
509 assertEquals(tmpRow[4], row.get("col5"));
510 assertEquals(tmpRow[5], row.get("col6"));
511 assertEquals(tmpRow[6], row.get("col7"));
512
513 db.close();
514 }
515 }
516
517 public void testMultiPageTableDef() throws Exception
518 {
519 for (final TestDB testDB : SUPPORTED_DBS_TEST_FOR_READ) {
520 List<? extends Column> columns = open(testDB).getTable("Table2").getColumns();
521 assertEquals(89, columns.size());
522 }
523 }
524
525 public void testOverflow() throws Exception
526 {
527 for (final TestDB testDB : TestDB.getSupportedForBasename(Basename.OVERFLOW, true)) {
528 Database mdb = open(testDB);
529 Table table = mdb.getTable("Table1");
530
531
532 table.getNextRow();
533 table.getNextRow();
534
535 Map<String, Object> row = table.getNextRow();
536 assertEquals(Arrays.<Object>asList(
537 null, "row3col3", null, null, null, null, null,
538 "row3col9", null),
539 new ArrayList<Object>(row.values()));
540
541 table.getNextRow();
542
543 row = table.getNextRow();
544 assertEquals(Arrays.<Object>asList(
545 null, "row5col2", null, null, null, null, null, null,
546 null),
547 new ArrayList<Object>(row.values()));
548
549 table.reset();
550 assertRowCount(7, table);
551
552 mdb.close();
553 }
554 }
555
556
557 public void testUsageMapPromotion() throws Exception {
558 for (final TestDB testDB : TestDB.getSupportedForBasename(Basename.PROMOTION)) {
559 Database db = openMem(testDB);
560 Table t = db.getTable("jobDB1");
561
562 assertTrue(((TableImpl)t).getOwnedPagesCursor().getUsageMap().toString()
563 .startsWith("InlineHandler"));
564
565 String lval = createNonAsciiString(255);
566
567 ((DatabaseImpl)db).getPageChannel().startWrite();
568 try {
569 for(int i = 0; i < 1000; ++i) {
570 t.addRow(i, 13, 57, lval, lval, lval, lval, lval, lval, 47.0d);
571 }
572 } finally {
573 ((DatabaseImpl)db).getPageChannel().finishWrite();
574 }
575
576 Set<Integer> ids = t.stream()
577 .map(r -> r.getInt("ID"))
578 .collect(Collectors.toSet());
579 assertEquals(1000, ids.size());
580
581 assertTrue(((TableImpl)t).getOwnedPagesCursor().getUsageMap().toString()
582 .startsWith("ReferenceHandler"));
583
584 db.close();
585 }
586 }
587
588
589 public void testLargeTableDef() throws Exception {
590 for (final FileFormat fileFormat : SUPPORTED_FILEFORMATS) {
591 Database db = create(fileFormat);
592
593 final int numColumns = 90;
594
595 List<ColumnBuilder> columns = new ArrayList<ColumnBuilder>();
596 List<String> colNames = new ArrayList<String>();
597 for(int i = 0; i < numColumns; ++i) {
598 String colName = "MyColumnName" + i;
599 colNames.add(colName);
600 columns.add(newColumn(colName, DataType.TEXT).toColumn());
601 }
602
603 Table t = newTable("test")
604 .addColumns(columns)
605 .toTable(db);
606
607 List<String> row = new ArrayList<String>();
608 Map<String,Object> expectedRowData = new LinkedHashMap<String, Object>();
609 for(int i = 0; i < numColumns; ++i) {
610 String value = "" + i + " some row data";
611 row.add(value);
612 expectedRowData.put(colNames.get(i), value);
613 }
614
615 t.addRow(row.toArray());
616
617 t.reset();
618 assertEquals(expectedRowData, t.getNextRow());
619
620 db.close();
621 }
622 }
623
624 public void testWriteAndReadDate() throws Exception {
625 for (final FileFormat fileFormat : SUPPORTED_FILEFORMATS) {
626 Database db = createMem(fileFormat);
627 db.setDateTimeType(DateTimeType.DATE);
628
629 Table table = newTable("test")
630 .addColumn(newColumn("name", DataType.TEXT))
631 .addColumn(newColumn("date", DataType.SHORT_DATE_TIME))
632 .toTable(db);
633
634
635
636 long curTimeNoMillis = (System.currentTimeMillis() / 1000L);
637 curTimeNoMillis *= 1000L;
638
639 DateFormat df = new SimpleDateFormat("yyyyMMdd HH:mm:ss");
640 List<Date> dates =
641 new ArrayList<Date>(
642 Arrays.asList(
643 df.parse("19801231 00:00:00"),
644 df.parse("19930513 14:43:27"),
645 null,
646 df.parse("20210102 02:37:00"),
647 new Date(curTimeNoMillis)));
648
649 Calendar c = Calendar.getInstance();
650 for(int year = 1801; year < 2050; year +=3) {
651 for(int month = 0; month <= 12; ++month) {
652 for(int day = 1; day < 29; day += 3) {
653 c.clear();
654 c.set(Calendar.YEAR, year);
655 c.set(Calendar.MONTH, month);
656 c.set(Calendar.DAY_OF_MONTH, day);
657 dates.add(c.getTime());
658 }
659 }
660 }
661
662 ((DatabaseImpl)db).getPageChannel().startWrite();
663 try {
664 for(Date d : dates) {
665 table.addRow("row " + d, d);
666 }
667 } finally {
668 ((DatabaseImpl)db).getPageChannel().finishWrite();
669 }
670
671 List<Date> foundDates = table.stream()
672 .map(r -> r.getDate("date"))
673 .collect(Collectors.toList());
674
675 assertEquals(dates.size(), foundDates.size());
676 for(int i = 0; i < dates.size(); ++i) {
677 Date expected = dates.get(i);
678 Date found = foundDates.get(i);
679 assertSameDate(expected, found);
680 }
681
682 db.close();
683 }
684 }
685
686 public void testAncientDatesWrite() throws Exception
687 {
688 SimpleDateFormat sdf = DatabaseBuilder.createDateFormat("yyyy-MM-dd");
689
690 List<String> dates = Arrays.asList("1582-10-15", "1582-10-14",
691 "1492-01-10", "1392-01-10");
692
693
694 for (final FileFormat fileFormat : SUPPORTED_FILEFORMATS) {
695 Database db = createMem(fileFormat);
696 db.setDateTimeType(DateTimeType.DATE);
697
698 Table table = newTable("test")
699 .addColumn(newColumn("name", DataType.TEXT))
700 .addColumn(newColumn("date", DataType.SHORT_DATE_TIME))
701 .toTable(db);
702
703 for(String dateStr : dates) {
704 Date d = sdf.parse(dateStr);
705 table.addRow("row " + dateStr, d);
706 }
707
708 List<String> foundDates = table.stream()
709 .map(r -> sdf.format(r.getDate("date")))
710 .collect(Collectors.toList());
711
712 assertEquals(dates, foundDates);
713
714 db.close();
715 }
716
717 }
718
719
720
721
722 public void testAncientDatesRead() throws Exception
723 {
724 TimeZone tz = TimeZone.getTimeZone("America/New_York");
725 SimpleDateFormat sdf = DatabaseBuilder.createDateFormat("yyyy-MM-dd");
726 sdf.getCalendar().setTimeZone(tz);
727
728 List<String> dates = Arrays.asList("1582-10-15", "1582-10-14",
729 "1492-01-10", "1392-01-10");
730
731 for (final TestDB testDB : TestDB.getSupportedForBasename(Basename.OLD_DATES)) {
732 Database db = openCopy(testDB);
733 db.setTimeZone(tz);
734 db.setDateTimeType(DateTimeType.DATE);
735
736 Table t = db.getTable("Table1");
737
738 List<String> foundDates = new ArrayList<String>();
739 for(Row row : t) {
740 foundDates.add(sdf.format(row.getDate("DateField")));
741 }
742
743 assertEquals(dates, foundDates);
744
745 db.close();
746 }
747
748 }
749
750 public void testSystemTable() throws Exception
751 {
752 for (final FileFormat fileFormat : SUPPORTED_FILEFORMATS) {
753 Database db = create(fileFormat);
754
755 Set<String> sysTables = new TreeSet<String>(
756 String.CASE_INSENSITIVE_ORDER);
757 sysTables.addAll(
758 Arrays.asList("MSysObjects", "MSysQueries", "MSysACES",
759 "MSysRelationships"));
760
761 if (fileFormat == FileFormat.GENERIC_JET4) {
762 assertNull("file format: " + fileFormat, db.getSystemTable("MSysAccessObjects"));
763 } else if (fileFormat.ordinal() < FileFormat.V2003.ordinal()) {
764 assertNotNull("file format: " + fileFormat, db.getSystemTable("MSysAccessObjects"));
765 sysTables.add("MSysAccessObjects");
766 } else {
767
768 assertNull("file format: " + fileFormat, db.getSystemTable("MSysAccessObjects"));
769 sysTables.addAll(
770 Arrays.asList("MSysNavPaneGroupCategories",
771 "MSysNavPaneGroups", "MSysNavPaneGroupToObjects",
772 "MSysNavPaneObjectIDs", "MSysAccessStorage"));
773 if(fileFormat.ordinal() >= FileFormat.V2007.ordinal()) {
774 sysTables.addAll(
775 Arrays.asList(
776 "MSysComplexColumns", "MSysComplexType_Attachment",
777 "MSysComplexType_Decimal", "MSysComplexType_GUID",
778 "MSysComplexType_IEEEDouble", "MSysComplexType_IEEESingle",
779 "MSysComplexType_Long", "MSysComplexType_Short",
780 "MSysComplexType_Text", "MSysComplexType_UnsignedByte"));
781 }
782 if(fileFormat.ordinal() >= FileFormat.V2010.ordinal()) {
783 sysTables.add("f_12D7448B56564D8AAE333BCC9B3718E5_Data");
784 sysTables.add("MSysResources");
785 }
786 if(fileFormat.ordinal() >= FileFormat.V2019.ordinal()) {
787 sysTables.remove("f_12D7448B56564D8AAE333BCC9B3718E5_Data");
788 sysTables.add("f_8FA5340F56044616AE380F64A2FEC135_Data");
789 sysTables.add("MSysWSDPCacheComplexColumnMapping");
790 sysTables.add("MSysWSDPChangeTokenMapping");
791 sysTables.add("MSysWSDPRelationshipMapping");
792 }
793 }
794
795 assertEquals(sysTables, db.getSystemTableNames());
796
797 assertNotNull(db.getSystemTable("MSysObjects"));
798 assertNotNull(db.getSystemTable("MSysQueries"));
799 assertNotNull(db.getSystemTable("MSysACES"));
800 assertNotNull(db.getSystemTable("MSysRelationships"));
801
802 assertNull(db.getSystemTable("MSysBogus"));
803
804 TableMetaData tmd = db.getTableMetaData("MSysObjects");
805 assertEquals("MSysObjects", tmd.getName());
806 assertFalse(tmd.isLinked());
807 assertTrue(tmd.isSystem());
808
809 db.close();
810 }
811 }
812
813 public void testFixedText() throws Exception
814 {
815 for (final TestDB testDB : TestDB.getSupportedForBasename(Basename.FIXED_TEXT)) {
816 Database db = openCopy(testDB);
817
818 Table t = db.getTable("users");
819 Column c = t.getColumn("c_flag_");
820 assertEquals(DataType.TEXT, c.getType());
821 assertEquals(false, c.isVariableLength());
822 assertEquals(2, c.getLength());
823
824 Map<String,Object> row = t.getNextRow();
825 assertEquals("N", row.get("c_flag_"));
826
827 t.addRow(3, "testFixedText", "boo", "foo", "bob", 3, 5, 9, "Y",
828 new Date());
829
830 t.getNextRow();
831 row = t.getNextRow();
832 assertEquals("testFixedText", row.get("c_user_login"));
833 assertEquals("Y", row.get("c_flag_"));
834
835 db.close();
836 }
837 }
838
839 public void testDbSortOrder() throws Exception {
840
841 for (final TestDB testDB : SUPPORTED_DBS_TEST_FOR_READ) {
842
843 Database db = open(testDB);
844 assertEquals(((DatabaseImpl)db).getFormat().DEFAULT_SORT_ORDER,
845 ((DatabaseImpl)db).getDefaultSortOrder());
846 db.close();
847 }
848 }
849
850 public void testUnsupportedColumns() throws Exception {
851 for (final TestDB testDB : TestDB.getSupportedForBasename(Basename.UNSUPPORTED)) {
852
853 Database db = open(testDB);
854 Table t = db.getTable("Test");
855 Column varCol = t.getColumn("UnknownVar");
856 assertEquals(DataType.UNSUPPORTED_VARLEN, varCol.getType());
857 Column fixCol = t.getColumn("UnknownFix");
858 assertEquals(DataType.UNSUPPORTED_FIXEDLEN, fixCol.getType());
859
860 List<String> varVals = Arrays.asList(
861 "RawData[(10) FF FE 73 6F 6D 65 64 61 74 61]",
862 "RawData[(12) FF FE 6F 74 68 65 72 20 64 61 74 61]",
863 null);
864 List<String> fixVals = Arrays.asList("RawData[(4) 37 00 00 00]",
865 "RawData[(4) F3 FF FF FF]",
866 "RawData[(4) 02 00 00 00]");
867
868 int idx = 0;
869 for(Map<String,Object> row : t) {
870 checkRawValue(varVals.get(idx), varCol.getRowValue(row));
871 checkRawValue(fixVals.get(idx), fixCol.getRowValue(row));
872 ++idx;
873 }
874 db.close();
875 }
876 }
877
878 static List<Table> getTables(Iterable<Table> tableIter)
879 {
880 List<Table> tableList = new ArrayList<Table>();
881 for(Table t : tableIter) {
882 tableList.add(t);
883 }
884 return tableList;
885 }
886
887 public void testTimeZone() throws Exception
888 {
889 TimeZone tz = TimeZone.getTimeZone("America/New_York");
890 doTestTimeZone(tz);
891
892 tz = TimeZone.getTimeZone("Australia/Sydney");
893 doTestTimeZone(tz);
894 }
895
896 private static void doTestTimeZone(final TimeZone tz) throws Exception
897 {
898 ColumnImpl col = new ColumnImpl(null, null, DataType.SHORT_DATE_TIME, 0, 0, 0) {
899 @Override
900 public TimeZone getTimeZone() { return tz; }
901 @Override
902 public ZoneId getZoneId() { return null; }
903 @Override
904 public ColumnImpl.DateTimeFactory getDateTimeFactory() {
905 return getDateTimeFactory(DateTimeType.DATE);
906 }
907 };
908
909 SimpleDateFormat df = new SimpleDateFormat("yyyy.MM.dd");
910 df.setTimeZone(tz);
911
912 long startDate = df.parse("2012.01.01").getTime();
913 long endDate = df.parse("2013.01.01").getTime();
914
915 Calendar curCal = Calendar.getInstance(tz);
916 curCal.setTimeInMillis(startDate);
917
918 SimpleDateFormat sdf = new SimpleDateFormat("yyyy.MM.dd HH:mm:ss");
919 sdf.setTimeZone(tz);
920
921 while(curCal.getTimeInMillis() < endDate) {
922 Date curDate = curCal.getTime();
923 Date newDate = new Date(col.fromDateDouble(col.toDateDouble(curDate)));
924 if(curDate.getTime() != newDate.getTime()) {
925 assertEquals(sdf.format(curDate), sdf.format(newDate));
926 }
927 curCal.add(Calendar.MINUTE, 30);
928 }
929 }
930
931 public void testToString()
932 {
933 RowImpl row = new RowImpl(new RowIdImpl(1, 1));
934 row.put("id", 37);
935 row.put("data", null);
936 assertEquals("Row[1:1][{id=37,data=<null>}]", row.toString());
937 }
938
939 public void testIterateTableNames() throws Exception {
940 for (final TestDB testDB : SUPPORTED_DBS_TEST_FOR_READ) {
941 final Database db = open(testDB);
942
943 Set<String> names = new HashSet<>();
944 int sysCount = 0;
945 for(TableMetaData tmd : db.newTableMetaDataIterable()) {
946 if(tmd.isSystem()) {
947 ++sysCount;
948 continue;
949 }
950 assertFalse(tmd.isLinked());
951 assertNull(tmd.getLinkedTableName());
952 assertNull(tmd.getLinkedDbName());
953 names.add(tmd.getName());
954 }
955
956 assertTrue(sysCount > 4);
957 assertEquals(new HashSet<>(Arrays.asList("Table1", "Table2", "Table3",
958 "Table4")),
959 names);
960 }
961
962 for (final TestDB testDB : TestDB.getSupportedForBasename(Basename.LINKED)) {
963 final Database db = open(testDB);
964
965 Set<String> names = new HashSet<>();
966 for(TableMetaData tmd : db.newTableMetaDataIterable()) {
967 if(tmd.isSystem()) {
968 continue;
969 }
970 if("Table1".equals(tmd.getName())) {
971 assertFalse(tmd.isLinked());
972 assertNull(tmd.getLinkedTableName());
973 assertNull(tmd.getLinkedDbName());
974 } else {
975 assertTrue(tmd.isLinked());
976 assertEquals("Table1", tmd.getLinkedTableName());
977 assertEquals("Z:\\jackcess_test\\linkeeTest.accdb", tmd.getLinkedDbName());
978 }
979 names.add(tmd.getName());
980 }
981
982 assertEquals(new HashSet<>(Arrays.asList("Table1", "Table2")),
983 names);
984 }
985 }
986
987 public void testTableDates() throws Exception {
988 for (final TestDB testDB : SUPPORTED_DBS_TEST_FOR_READ) {
989 Table table = open(testDB).getTable("Table1");
990 String expectedCreateDate = null;
991 String expectedUpdateDate = null;
992 if(testDB.getExpectedFileFormat() == FileFormat.V1997) {
993 expectedCreateDate = "2010-03-05T14:48:26.420";
994 expectedUpdateDate = "2010-03-05T14:48:26.607";
995 } else {
996 expectedCreateDate = "2004-05-28T17:51:48.701";
997 expectedUpdateDate = "2006-07-24T09:56:19.701";
998 }
999 assertEquals(expectedCreateDate, table.getCreatedDate().toString());
1000 assertEquals(expectedUpdateDate, table.getUpdatedDate().toString());
1001 }
1002 }
1003
1004 public void testBrokenIndex() throws Exception {
1005 TestDB testDb = TestDB.getSupportedForBasename(Basename.TEST).get(0);
1006 try (Database db = new DatabaseBuilder(testDb.getFile())
1007 .setReadOnly(true).setIgnoreBrokenSystemCatalogIndex(true).open()) {
1008 Table test = db.getTable("Table1");
1009 assertNotNull(test);
1010 verifyFinderType(db, "FallbackTableFinder");
1011 }
1012 try (Database db = openMem(testDb)) {
1013 Table test = db.getTable("Table1");
1014 assertNotNull(test);
1015 verifyFinderType(db, "DefaultTableFinder");
1016 }
1017 }
1018
1019 private static void verifyFinderType(Database db, String clazzName)
1020 throws Exception{
1021 java.lang.reflect.Field f = db.getClass().getDeclaredField("_tableFinder");
1022 f.setAccessible(true);
1023 Object finder = f.get(db);
1024 assertNotNull(finder);
1025 assertEquals(clazzName, finder.getClass().getSimpleName());
1026 }
1027
1028 private static void checkRawValue(String expected, Object val)
1029 {
1030 if(expected != null) {
1031 assertTrue(ColumnImpl.isRawData(val));
1032 assertEquals(expected, val.toString());
1033 } else {
1034 assertNull(val);
1035 }
1036 }
1037 }