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 @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
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
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
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
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
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
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);
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
636
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
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 }