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