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.ordinal() < FileFormat.V2003.ordinal()) {
746         assertNotNull("file format: " + fileFormat, db.getSystemTable("MSysAccessObjects"));
747         sysTables.add("MSysAccessObjects");
748       } else {
749         // v2003+ template files have no "MSysAccessObjects" table
750         assertNull("file format: " + fileFormat, db.getSystemTable("MSysAccessObjects"));
751         sysTables.addAll(
752             Arrays.asList("MSysNavPaneGroupCategories",
753                           "MSysNavPaneGroups", "MSysNavPaneGroupToObjects",
754                           "MSysNavPaneObjectIDs", "MSysAccessStorage"));
755         if(fileFormat.ordinal() >= FileFormat.V2007.ordinal()) {
756           sysTables.addAll(
757               Arrays.asList(
758                   "MSysComplexColumns", "MSysComplexType_Attachment",
759                   "MSysComplexType_Decimal", "MSysComplexType_GUID",
760                   "MSysComplexType_IEEEDouble", "MSysComplexType_IEEESingle",
761                   "MSysComplexType_Long", "MSysComplexType_Short",
762                   "MSysComplexType_Text", "MSysComplexType_UnsignedByte"));
763         }
764         if(fileFormat.ordinal() >= FileFormat.V2010.ordinal()) {
765           sysTables.add("f_12D7448B56564D8AAE333BCC9B3718E5_Data");
766           sysTables.add("MSysResources");
767         } 
768       }
769 
770       assertEquals(sysTables, db.getSystemTableNames());
771       
772       assertNotNull(db.getSystemTable("MSysObjects"));
773       assertNotNull(db.getSystemTable("MSysQueries"));
774       assertNotNull(db.getSystemTable("MSysACES"));
775       assertNotNull(db.getSystemTable("MSysRelationships"));
776 
777       assertNull(db.getSystemTable("MSysBogus"));
778 
779       TableMetaData tmd = db.getTableMetaData("MSysObjects");
780       assertEquals("MSysObjects", tmd.getName());
781       assertFalse(tmd.isLinked());
782       assertTrue(tmd.isSystem());
783       
784       db.close();
785     }
786   }
787 
788   public void testFixedText() throws Exception
789   {
790     for (final TestDB testDB : TestDB.getSupportedForBasename(Basename.FIXED_TEXT)) {
791       Database db = openCopy(testDB);
792 
793       Table t = db.getTable("users");
794       Column c = t.getColumn("c_flag_");
795       assertEquals(DataType.TEXT, c.getType());
796       assertEquals(false, c.isVariableLength());
797       assertEquals(2, c.getLength());
798 
799       Map<String,Object> row = t.getNextRow();
800       assertEquals("N", row.get("c_flag_"));
801 
802       t.addRow(3, "testFixedText", "boo", "foo", "bob", 3, 5, 9, "Y",
803                new Date());
804 
805       t.getNextRow();
806       row = t.getNextRow();
807       assertEquals("testFixedText", row.get("c_user_login"));
808       assertEquals("Y", row.get("c_flag_"));
809 
810       db.close();
811     }
812   }
813 
814   public void testDbSortOrder() throws Exception {
815 
816     for (final TestDB testDB : SUPPORTED_DBS_TEST_FOR_READ) {
817 
818       Database db = open(testDB);
819       assertEquals(((DatabaseImpl)db).getFormat().DEFAULT_SORT_ORDER,
820                    ((DatabaseImpl)db).getDefaultSortOrder());
821       db.close();
822     }
823   }
824 
825   public void testUnsupportedColumns() throws Exception {
826     for (final TestDB testDB : TestDB.getSupportedForBasename(Basename.UNSUPPORTED)) {
827 
828       Database db = open(testDB);
829       Table t = db.getTable("Test");
830       Column varCol = t.getColumn("UnknownVar");
831       assertEquals(DataType.UNSUPPORTED_VARLEN, varCol.getType());
832       Column fixCol = t.getColumn("UnknownFix");
833       assertEquals(DataType.UNSUPPORTED_FIXEDLEN, fixCol.getType());
834 
835       List<String> varVals = Arrays.asList(
836           "RawData[(10) FF FE 73 6F  6D 65 64 61  74 61]",
837           "RawData[(12) FF FE 6F 74  68 65 72 20  64 61 74 61]",
838           null);
839       List<String> fixVals = Arrays.asList("RawData[(4) 37 00 00 00]",
840                                            "RawData[(4) F3 FF FF FF]", 
841                                            "RawData[(4) 02 00 00 00]");
842 
843       int idx = 0;
844       for(Map<String,Object> row : t) {
845         checkRawValue(varVals.get(idx), varCol.getRowValue(row));
846         checkRawValue(fixVals.get(idx), fixCol.getRowValue(row));
847         ++idx;
848       }
849       db.close();
850     }
851   }
852 
853   public void testLinkedTables() throws Exception {
854     for (final TestDB testDB : TestDB.getSupportedForBasename(Basename.LINKED)) {
855       Database db = openCopy(testDB);
856 
857       try {
858         db.getTable("Table2");
859         fail("FileNotFoundException should have been thrown");
860       } catch(FileNotFoundException e) {
861         // success
862       }
863 
864       TableMetaData tmd = db.getTableMetaData("Table2");
865       assertEquals("Table2", tmd.getName());
866       assertTrue(tmd.isLinked());
867       assertFalse(tmd.isSystem());
868       assertEquals("Table1", tmd.getLinkedTableName());
869       assertEquals("Z:\\jackcess_test\\linkeeTest.accdb", tmd.getLinkedDbName());
870 
871       tmd = db.getTableMetaData("FooTable");
872       assertNull(tmd);
873 
874       assertTrue(db.getLinkedDatabases().isEmpty());
875 
876       final String linkeeDbName = "Z:\\jackcess_test\\linkeeTest.accdb";
877       final File linkeeFile = new File("src/test/data/linkeeTest.accdb");
878       db.setLinkResolver(new LinkResolver() {
879         public Database resolveLinkedDatabase(Database linkerdb, String dbName)
880           throws IOException {
881           assertEquals(linkeeDbName, dbName);
882           return DatabaseBuilder.open(linkeeFile);
883         }
884       });
885 
886       Table t2 = db.getTable("Table2");
887 
888       assertEquals(1, db.getLinkedDatabases().size());
889       Database linkeeDb = db.getLinkedDatabases().get(linkeeDbName);
890       assertNotNull(linkeeDb);
891       assertEquals(linkeeFile, linkeeDb.getFile());
892       
893       List<? extends Map<String, Object>> expectedRows =
894         createExpectedTable(
895             createExpectedRow(
896                 "ID", 1,
897                 "Field1", "bar"));
898 
899       assertTable(expectedRows, t2);
900 
901       db.createLinkedTable("FooTable", linkeeDbName, "Table2");      
902 
903       tmd = db.getTableMetaData("FooTable");
904       assertEquals("FooTable", tmd.getName());
905       assertTrue(tmd.isLinked());
906       assertFalse(tmd.isSystem());
907       assertEquals("Table2", tmd.getLinkedTableName());
908       assertEquals("Z:\\jackcess_test\\linkeeTest.accdb", tmd.getLinkedDbName());
909 
910       Table t3 = db.getTable("FooTable");
911 
912       assertEquals(1, db.getLinkedDatabases().size());
913 
914       expectedRows =
915         createExpectedTable(
916             createExpectedRow(
917                 "ID", 1,
918                 "Field1", "buzz"));
919 
920       assertTable(expectedRows, t3);
921 
922       tmd = db.getTableMetaData("Table1");
923       assertEquals("Table1", tmd.getName());
924       assertFalse(tmd.isLinked());
925       assertFalse(tmd.isSystem());
926       assertNull(tmd.getLinkedTableName());
927       assertNull(tmd.getLinkedDbName());
928 
929       Table t1 = tmd.open(db);
930       
931       assertFalse(db.isLinkedTable(null));
932       assertTrue(db.isLinkedTable(t2));
933       assertTrue(db.isLinkedTable(t3));
934       assertFalse(db.isLinkedTable(t1));
935 
936       List<Table> tables = getTables(db.newIterable());
937       assertEquals(3, tables.size());
938       assertTrue(tables.contains(t1));
939       assertTrue(tables.contains(t2));
940       assertTrue(tables.contains(t3));
941       assertFalse(tables.contains(((DatabaseImpl)db).getSystemCatalog()));
942       
943       tables = getTables(db.newIterable().setIncludeNormalTables(false));
944       assertEquals(2, tables.size());
945       assertFalse(tables.contains(t1));
946       assertTrue(tables.contains(t2));
947       assertTrue(tables.contains(t3));
948       assertFalse(tables.contains(((DatabaseImpl)db).getSystemCatalog()));
949       
950       tables = getTables(db.newIterable().withLocalUserTablesOnly());
951       assertEquals(1, tables.size());
952       assertTrue(tables.contains(t1));
953       assertFalse(tables.contains(t2));
954       assertFalse(tables.contains(t3));
955       assertFalse(tables.contains(((DatabaseImpl)db).getSystemCatalog()));
956       
957       tables = getTables(db.newIterable().withSystemTablesOnly());
958       assertTrue(tables.size() > 5);
959       assertFalse(tables.contains(t1));
960       assertFalse(tables.contains(t2));
961       assertFalse(tables.contains(t3));
962       assertTrue(tables.contains(((DatabaseImpl)db).getSystemCatalog()));
963 
964       db.close();
965     }
966   }
967 
968   private static List<Table> getTables(Iterable<Table> tableIter)
969   {
970     List<Table> tableList = new ArrayList<Table>();
971     for(Table t : tableIter) {
972       tableList.add(t);
973     }
974     return tableList;
975   }
976   
977   public void testTimeZone() throws Exception
978   {
979     TimeZone tz = TimeZone.getTimeZone("America/New_York");
980     doTestTimeZone(tz);
981 
982     tz = TimeZone.getTimeZone("Australia/Sydney");
983     doTestTimeZone(tz);
984   }
985 
986   private static void doTestTimeZone(final TimeZone tz) throws Exception
987   {
988     ColumnImpl col = new ColumnImpl(null, null, DataType.SHORT_DATE_TIME, 0, 0, 0) {
989       @Override
990       protected Calendar getCalendar() { return Calendar.getInstance(tz); }
991     };
992 
993     SimpleDateFormat df = new SimpleDateFormat("yyyy.MM.dd");
994     df.setTimeZone(tz);
995 
996     long startDate = df.parse("2012.01.01").getTime();
997     long endDate = df.parse("2013.01.01").getTime();
998 
999     Calendar curCal = Calendar.getInstance(tz);
1000     curCal.setTimeInMillis(startDate);
1001 
1002     SimpleDateFormat sdf = new SimpleDateFormat("yyyy.MM.dd HH:mm:ss");
1003     sdf.setTimeZone(tz);
1004 
1005     while(curCal.getTimeInMillis() < endDate) {
1006       Date curDate = curCal.getTime();
1007       Date newDate = new Date(col.fromDateDouble(col.toDateDouble(curDate)));
1008       if(curDate.getTime() != newDate.getTime()) {
1009         assertEquals(sdf.format(curDate), sdf.format(newDate));
1010       }
1011       curCal.add(Calendar.MINUTE, 30);
1012     }
1013   }
1014 
1015   public void testToString()
1016   {
1017     RowImpl row = new RowImpl(new RowIdImpl(1, 1));
1018     row.put("id", 37);
1019     row.put("data", null);
1020     assertEquals("Row[1:1][{id=37,data=<null>}]", row.toString());
1021   }
1022 
1023   private static void checkRawValue(String expected, Object val)
1024   {
1025     if(expected != null) {
1026       assertTrue(ColumnImpl.isRawData(val));
1027       assertEquals(expected, val.toString());
1028     } else {
1029       assertNull(val);
1030     }
1031   }
1032 }