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