com.healthmarketscience.jackcess.Database Java Examples

public void testInsertLongAutoNumberPK() throws Exception
{
  for (final FileFormat fileFormat : SUPPORTED_FILEFORMATS) {
    Database db = createMem(fileFormat);

    Table table = new TableBuilder("test")
      .addColumn(new ColumnBuilder("a", DataType.LONG)
                .setAutoNumber(true))
      .addColumn(new ColumnBuilder("b", DataType.TEXT))
      .setPrimaryKey("a")
      .toTable(db);

    doTestInsertLongAutoNumber(table);

    db.close();
  }    
}
 

private TableImpl getTable(TableInfo tableInfo, boolean includeSystemTables)
  throws IOException
{
  if(tableInfo.isLinked()) {

    if(_linkedDbs == null) {
      _linkedDbs = new HashMap<String,Database>();
    }

    String linkedDbName = ((LinkedTableInfo)tableInfo).linkedDbName;
    String linkedTableName = ((LinkedTableInfo)tableInfo).linkedTableName;
    Database linkedDb = _linkedDbs.get(linkedDbName);
    if(linkedDb == null) {
      linkedDb = getLinkResolver().resolveLinkedDatabase(this, linkedDbName);
      _linkedDbs.put(linkedDbName, linkedDb);
    }

    return ((DatabaseImpl)linkedDb).getTable(linkedTableName,
                                             includeSystemTables);
  }

  return readTable(tableInfo.tableName, tableInfo.pageNumber,
                   tableInfo.flags);
}
 

public void testImportFromFileWithOnlyHeaders() throws Exception
{
  for (final FileFormat fileFormat : JetFormatTest.SUPPORTED_FILEFORMATS) {
    Database db = create(fileFormat);
    String tableName = new ImportUtil.Builder(db, "test")
      .setDelimiter("\\t")
      .importFile(new File("src/test/data/sample-input-only-headers.tab"));

    Table t = db.getTable(tableName);

    List<String> colNames = new ArrayList<String>();
    for(Column c : t.getColumns()) {
      colNames.add(c.getName());
    }
    assertEquals(Arrays.asList(
                     "RESULT_PHYS_ID", "FIRST", "MIDDLE", "LAST", "OUTLIER",
                     "RANK", "CLAIM_COUNT", "PROCEDURE_COUNT",
                     "WEIGHTED_CLAIM_COUNT", "WEIGHTED_PROCEDURE_COUNT"),
                 colNames);

    db.close();
  }
}
 

public void testAutoNumber() throws Exception 
{
  for (final FileFormat fileFormat : SUPPORTED_FILEFORMATS) {
    Database db = createMem(fileFormat);

    Table table = new TableBuilder("test")
      .addColumn(new ColumnBuilder("a", DataType.LONG)
                .setAutoNumber(true))
      .addColumn(new ColumnBuilder("b", DataType.TEXT))
      .toTable(db);

    doTestAutoNumber(table);

    db.close();
  }
}
 

public void testReadCalcFields() throws Exception {

    for(TestDB testDB : TestDB.getSupportedForBasename(Basename.CALC_FIELD)) {
      Database db = open(testDB);
      Table t = db.getTable("Table1");

      List<String> rows = new ArrayList<String>();
      for(Row r : t) {
        rows.add(r.entrySet().toString());
      }

      List<String> expectedRows = Arrays.asList(
          "[ID=1, FirstName=Bruce, LastName=Wayne, LastFirst=Wayne, Bruce, City=Gotham, LastFirstLen=12, Salary=1000000.0000, MonthlySalary=83333.3333, IsRich=true, AllNames=Wayne, Bruce=Wayne, Bruce, WeeklySalary=19230.7692307692, SalaryTest=1000000.0000, BoolTest=true, Popularity=50.325000, DecimalTest=50.325000, FloatTest=2583.2092, BigNumTest=56505085819.424791296572280180]",
          "[ID=2, FirstName=Bart, LastName=Simpson, LastFirst=Simpson, Bart, City=Springfield, LastFirstLen=13, Salary=-1.0000, MonthlySalary=-0.0833, IsRich=false, AllNames=Simpson, Bart=Simpson, Bart, WeeklySalary=-0.0192307692307692, SalaryTest=-1.0000, BoolTest=true, Popularity=-36.222200, DecimalTest=-36.222200, FloatTest=0.0035889593, BigNumTest=-0.0784734499180612994241100748]",
          "[ID=3, FirstName=John, LastName=Doe, LastFirst=Doe, John, City=Nowhere, LastFirstLen=9, Salary=0.0000, MonthlySalary=0.0000, IsRich=false, AllNames=Doe, John=Doe, John, WeeklySalary=0, SalaryTest=0.0000, BoolTest=true, Popularity=0.012300, DecimalTest=0.012300, FloatTest=0.0, BigNumTest=0E-8]",
          "[ID=4, FirstName=Test, LastName=User, LastFirst=User, Test, City=Hockessin, LastFirstLen=10, Salary=100.0000, MonthlySalary=8.3333, IsRich=false, AllNames=User, Test=User, Test, WeeklySalary=1.92307692307692, SalaryTest=100.0000, BoolTest=true, Popularity=102030405060.654321, DecimalTest=102030405060.654321, FloatTest=1.27413E-10, BigNumTest=2.787019289824216980830E-7]");

      assertEquals(expectedRows, rows);

      db.close();
    }    
  }
 

/**
 * Returns a new table with a unique name and the given table definition.
 */
private static Table createUniqueTable(Database db, String name,
                                       List<ColumnBuilder> columns,
                                       ResultSetMetaData md,
                                       ImportFilter filter)
  throws IOException, SQLException
{
  // otherwise, find unique name and create new table
  String baseName = name;
  int counter = 2;
  while(db.getTable(name) != null) {
    name = baseName + (counter++);
  }

  return new TableBuilder(name)
    .addColumns(filter.filterColumns(columns, md))
    .toTable(db);
}
 

public void x_testCreateIsoFile() throws Exception
{
  Database db = create(Database.FileFormat.V2000, true);

  Table t = new TableBuilder("test")
    .addColumn(new ColumnBuilder("row", DataType.TEXT))
    .addColumn(new ColumnBuilder("data", DataType.TEXT))
    .toTable(db);

  for(int i = 0; i < 256; ++i) {
    String str = "AA" + ((char)i) + "AA";
    t.addRow("row" + i, str);
  }

  db.close();
}
 

public void x_testReadAllCodesMdb() throws Exception
  {
//     Database db = openCopy(new File("/data2/jackcess_test/testAllIndexCodes.mdb"));
//     Database db = openCopy(new File("/data2/jackcess_test/testAllIndexCodes_orig.mdb"));
//     Database db = openCopy(new File("/data2/jackcess_test/testSomeMoreCodes.mdb"));
    Database db = openCopy(Database.FileFormat.V2000, new File("/data2/jackcess_test/testStillMoreCodes.mdb"));
    Table t = db.getTable("Table5");

    Index ind = t.getIndexes().iterator().next();
    ((IndexImpl)ind).initialize();

    System.out.println("Ind " + ind);

    Cursor cursor = CursorBuilder.createCursor(ind);
    while(cursor.moveToNextRow()) {
      System.out.println("=======");
      String entryStr =
        entryToString(cursor.getSavepoint().getCurrentPosition());
      System.out.println("Entry Bytes: " + entryStr);
      System.out.println("Value: " + cursor.getCurrentRow() + "; " +
                         toUnicodeStr(cursor.getCurrentRow().get("data")));
    }

    db.close();
  }
 

public void x_testReadIsoMdb() throws Exception
  {
//     Database db = open(new File("/tmp/test_ind.mdb"));
//     Database db = open(new File("/tmp/test_ind2.mdb"));
    Database db = open(Database.FileFormat.V2000, new File("/tmp/test_ind3.mdb"));
//     Database db = open(new File("/tmp/test_ind4.mdb"));

    Table t = db.getTable("Table1");
    Index index = t.getIndex("B");
    ((IndexImpl)index).initialize();
    System.out.println("Ind " + index);

    Cursor cursor = CursorBuilder.createCursor(index);
    while(cursor.moveToNextRow()) {
      System.out.println("=======");
      System.out.println("Savepoint: " + cursor.getSavepoint());
      System.out.println("Value: " + cursor.getCurrentRow());
    }

    db.close();
  }
 

public void testNoEnforceForeignKeys() throws Exception {
  for (final TestDB testDB : TestDB.getSupportedForBasename(Basename.INDEX)) {

    Database db = openCopy(testDB);
    db.setEnforceForeignKeys(false);
    Table t1 = db.getTable("Table1");
    Table t2 = db.getTable("Table2");
    Table t3 = db.getTable("Table3");

    t1.addRow(20, 0, 20, "some data", 20);

    Cursor c = CursorBuilder.createCursor(t2);
    c.moveToNextRow();
    c.updateCurrentRow(30, "foo30");

    c = CursorBuilder.createCursor(t3);
    c.moveToNextRow();
    c.deleteCurrentRow();

    db.close();
  }
  
}
 

public void testInsertLongAutoNumber() throws Exception
{
  for (final FileFormat fileFormat : SUPPORTED_FILEFORMATS) {
    Database db = createMem(fileFormat);

    Table table = new TableBuilder("test")
      .addColumn(new ColumnBuilder("a", DataType.LONG)
                .setAutoNumber(true))
      .addColumn(new ColumnBuilder("b", DataType.TEXT))
      .toTable(db);

    doTestInsertLongAutoNumber(table);

    db.close();
  }    
}
 

public void testReadLongValue() throws Exception {

    for (final TestDB testDB : TestDB.getSupportedForBasename(Basename.TEST2, true)) {
      Database db = openMem(testDB);
      Table table = db.getTable("MSP_PROJECTS");
      Row row = table.getNextRow();
      assertEquals("Jon Iles this is a a vawesrasoih aksdkl fas dlkjflkasjd flkjaslkdjflkajlksj dfl lkasjdf lkjaskldfj lkas dlk lkjsjdfkl; aslkdf lkasjkldjf lka skldf lka sdkjfl;kasjd falksjdfljaslkdjf laskjdfk jalskjd flkj aslkdjflkjkjasljdflkjas jf;lkasjd fjkas dasdf asd fasdf asdf asdmhf lksaiyudfoi jasodfj902384jsdf9 aw90se fisajldkfj lkasj dlkfslkd jflksjadf as", row.get("PROJ_PROP_AUTHOR"));
      assertEquals("T", row.get("PROJ_PROP_COMPANY"));
      assertEquals("Standard", row.get("PROJ_INFO_CAL_NAME"));
      assertEquals("Project1", row.get("PROJ_PROP_TITLE"));
      byte[] foundBinaryData = row.getBytes("RESERVED_BINARY_DATA");
      byte[] expectedBinaryData =
        toByteArray(new File("src/test/data/test2BinData.dat"));
      assertTrue(Arrays.equals(expectedBinaryData, foundBinaryData));

      db.close();
    }
  }
 

public void testSqlTypes() throws Exception {

    JetFormat v2000 = JetFormat.VERSION_4;
    for(DataType dt : DataType.values()) {
      if(v2000.isSupportedDataType(dt)) {
        Integer sqlType = null;
        try {
          sqlType = dt.getSQLType();
        } catch(SQLException ignored) {}

        if(sqlType != null) {
          assertEquals(dt, DataType.fromSQLType(sqlType));
        }
      }
    }

    assertEquals(DataType.LONG, DataType.fromSQLType(java.sql.Types.BIGINT));
    assertEquals(DataType.BIG_INT, DataType.fromSQLType(
                     java.sql.Types.BIGINT, 0, Database.FileFormat.V2016));
    assertEquals(java.sql.Types.BIGINT, DataType.BIG_INT.getSQLType());
    assertEquals(DataType.MEMO, DataType.fromSQLType(
                     java.sql.Types.VARCHAR, 1000));
  }
 

public void testLongValueAsMiddleColumn() throws Exception
{
  for (final FileFormat fileFormat : SUPPORTED_FILEFORMATS) {
    Database db = createMem(fileFormat);
    Table newTable = new TableBuilder("NewTable")
      .addColumn(new ColumnBuilder("a").setSQLType(Types.INTEGER))
      .addColumn(new ColumnBuilder("b").setSQLType(Types.LONGVARCHAR))
      .addColumn(new ColumnBuilder("c").setSQLType(Types.VARCHAR))
      .toTable(db);

    String lval = createString(2000); // "--2000 chars long text--";
    String tval = createString(40); // "--40chars long text--";
    newTable.addRow(new Integer(1), lval, tval);

    newTable = db.getTable("NewTable");
    Map<String, Object> readRow = newTable.getNextRow();
    assertEquals(new Integer(1), readRow.get("a"));
    assertEquals(lval, readRow.get("b"));
    assertEquals(tval, readRow.get("c"));

    db.close();
  }
}
 

private static void doTestWriteAndRead(Database db) throws Exception {
    createTestTable(db);
    Object[] row = createTestRow();
    row[3] = null;
    Table table = db.getTable("Test");
    int count = 1000;
    ((DatabaseImpl)db).getPageChannel().startWrite();
    try {
      for (int i = 0; i < count; i++) {
        table.addRow(row);
      }
    } finally {
      ((DatabaseImpl)db).getPageChannel().finishWrite();
    }
    for (int i = 0; i < count; i++) {
      Map<String, Object> readRow = table.getNextRow();
      assertEquals(row[0], readRow.get("A"));
      assertEquals(row[1], readRow.get("B"));
      assertEquals(row[2], readRow.get("C"));
      assertEquals(row[3], readRow.get("D"));
      assertEquals(row[4], readRow.get("E"));
      assertEquals(row[5], readRow.get("F"));
      assertEquals(row[6], readRow.get("G"));
      assertEquals(row[7], readRow.get("H"));
    }
}
 

public void testReadOnlyFormat() throws Exception {

    for (final TestDB testDB : SUPPORTED_DBS_TEST_FOR_READ) {

      Database db = null;
      Exception failure = null;
      try {
        db = openCopy(testDB);

        if(testDB.getExpectedFormat().READ_ONLY) {
          PropertyMap props = db.getUserDefinedProperties();
          props.put("foo", "bar");
          props.save();
        }

      } catch(Exception e) {
        failure = e;
      } finally {
        if(db != null) {
          db.close();
        }
      }

      if(!testDB.getExpectedFormat().READ_ONLY) {
        assertNull(failure);
      } else {
        assertTrue(failure instanceof NonWritableChannelException);
      }

    }
  }
 

public void testIndexCodes() throws Exception
{
  for (final TestDB testDB : TestDB.getSupportedForBasename(Basename.INDEX_CODES, true)) {
    Database db = openMem(testDB);

    for(Table t : db) {
      for(Index index : t.getIndexes()) {
//         System.out.println("Checking " + t.getName() + "." + index.getName());
        checkIndexEntries(testDB, t, index);
      }
    }

    db.close();
  }
}
 

@Override
protected Database createTempDb(Object customFile, FileFormat format,
                                boolean inMemory, Path tempDir,
                                boolean readOnly)
  throws IOException
{
  inMemory = "testFile1.txt".equals(customFile);
  return super.createTempDb(customFile, format, inMemory, tempDir,
                            readOnly);
}
 

/**
 * Get a list of the songs contained in the given pack.
 *
 * @param location the location of the txt file.
 * @return a list of the songs found.
 */
@Override
public List<SongDisplayable> getSongs(File location, StatusPanel statusPanel) {
    List<SongDisplayable> ret = new ArrayList<>();
    try {
        Database d = new DatabaseBuilder()
                .setCharset(Charset.forName("CP1252"))
                .setReadOnly(true)
                .setFile(location)
                .open();
        Table table = d.getTable("Song list");
        for (Row row : table) {
            String title = row.get("Song").toString();
            if(title.trim().isEmpty()) continue; //Ignore empty titled songs
            String author = row.get("Author name").toString();
            StringBuilder lyricsBuilder = new StringBuilder();
            addToLyrics(lyricsBuilder, "Verse", 1, row.get("Stanza 1"));
            for (int i = 1; i <= 5; i++) {
                addToLyrics(lyricsBuilder, "Chorus", i, row.get("Refrain " + i));
            }
            for (int i = 2; i <= 13; i++) {
                addToLyrics(lyricsBuilder, "Verse", i, row.get("Stanza " + i));
            }
            for (int i = 1; i <= 5; i++) {
                addToLyrics(lyricsBuilder, "Bridge", i, row.get("Bridge " + i));
            }
            String lyrics = lyricsBuilder.toString().trim();

            SongDisplayable song = new SongDisplayable(title, author);
            song.setLyrics(lyrics);
            ret.add(song);
        }
    } catch (IOException ex) {
        LOGGER.log(Level.WARNING, "Error importing worship hymn library", ex);
    }
    return ret;
}
 

private static void checkUsageMapRead(
    final File dbFile, final int expectedFirstPage, final int expectedLastPage)
  throws IOException {

  final Database db = DatabaseBuilder.open(dbFile);
  final UsageMap usageMap = UsageMap.read((DatabaseImpl)db,
                                          PageChannel.PAGE_GLOBAL_USAGE_MAP,
                                          PageChannel.ROW_GLOBAL_USAGE_MAP,
                                          true);
  assertEquals("Unexpected FirstPageNumber.", expectedFirstPage, 
               usageMap.getFirstPageNumber());
  assertEquals("Unexpected LastPageNumber.", expectedLastPage, 
               usageMap.getLastPageNumber());
}
 

private static void doTestCodecHandler(boolean simple) throws Exception
{
  for(Database.FileFormat ff : SUPPORTED_FILEFORMATS) {
    Database db = TestUtil.createFile(ff);
    int pageSize = ((DatabaseImpl)db).getFormat().PAGE_SIZE;
    File dbFile = db.getFile();
    db.close();

    // apply encoding to file
    encodeFile(dbFile, pageSize, simple);

    db = new DatabaseBuilder(dbFile)
      .setCodecProvider(simple ? SIMPLE_PROVIDER : FULL_PROVIDER)
      .open();

    Table t1 = new TableBuilder("test1")
      .addColumn(new ColumnBuilder("id", DataType.LONG).setAutoNumber(true))
      .addColumn(new ColumnBuilder("data", DataType.TEXT).setLength(250))
      .setPrimaryKey("id")
      .addIndex(new IndexBuilder("data_idx").addColumns("data"))
      .toTable(db);

    Table t2 = new TableBuilder("test2")
      .addColumn(new ColumnBuilder("id", DataType.LONG).setAutoNumber(true))
      .addColumn(new ColumnBuilder("data", DataType.TEXT).setLength(250))
      .setPrimaryKey("id")
      .addIndex(new IndexBuilder("data_idx").addColumns("data"))
      .toTable(db);

    int autonum = 1;
    for(int i = 1; i < 2; ++i) {
      writeData(t1, t2, autonum, autonum + 100);
      autonum += 100;
    }

    db.close();
  }
}
 

public void testWriteAndRead() throws Exception {
  for (final FileFormat fileFormat : SUPPORTED_FILEFORMATS) {
    Database db = create(fileFormat);
    doTestWriteAndRead(db);
    db.close();
  }
}
 

public void testWriteAndReadInBatch() throws Exception {
  for (final FileFormat fileFormat : SUPPORTED_FILEFORMATS) {
    Database db = createMem(fileFormat);
    createTestTable(db);
    int count = 1000;
    List<Object[]> rows = new ArrayList<Object[]>(count);
    Object[] row = createTestRow();
    for (int i = 0; i < count; i++) {
      rows.add(row);
    }
    Table table = db.getTable("Test");
    table.addRows(rows);
    for (int i = 0; i < count; i++) {
      Map<String, Object> readRow = table.getNextRow();
      assertEquals(row[0], readRow.get("A"));
      assertEquals(row[1], readRow.get("B"));
      assertEquals(row[2], readRow.get("C"));
      assertEquals(row[3], readRow.get("D"));
      assertEquals(row[4], readRow.get("E"));
      assertEquals(row[5], readRow.get("F"));
      assertEquals(row[6], readRow.get("G"));
      assertEquals(row[7], readRow.get("H"));
    }

    db.close();
  }
}
 

public void x_testCreateAltIsoFile() throws Exception
{
  Database db = openCopy(Database.FileFormat.V2000, new File("/tmp/test_ind.mdb"), true);

  Table t = db.getTable("Table1");

  for(int i = 0; i < 256; ++i) {
    String str = "AA" + ((char)i) + "AA";
    t.addRow("row" + i, str,
             (byte)42 + i, (short)53 + i, 13 * i,
             (6.7d / i), null, null, true);
  }

  db.close();
}
 

public void testJoiner() throws Exception
{
  for (final TestDB testDB : TestDB.getSupportedForBasename(Basename.INDEX)) {

    Database db = openCopy(testDB);
    Table t1 = db.getTable("Table1");
    Table t2 = db.getTable("Table2");
    Table t3 = db.getTable("Table3");

    Index t1t2 = t1.getIndex("Table2Table1");
    Index t1t3 = t1.getIndex("Table3Table1");

    Index t2t1 = t1t2.getReferencedIndex();
    assertSame(t2, t2t1.getTable());
    Joiner t2t1Join = Joiner.create(t2t1);

    assertSame(t2, t2t1Join.getFromTable());
    assertSame(t2t1, t2t1Join.getFromIndex());
    assertSame(t1, t2t1Join.getToTable());
    assertSame(t1t2, t2t1Join.getToIndex());
    
    doTestJoiner(t2t1Join, createT2T1Data());
    
    Index t3t1 = t1t3.getReferencedIndex();
    assertSame(t3, t3t1.getTable());
    Joiner t3t1Join = Joiner.create(t3t1);

    assertSame(t3, t3t1Join.getFromTable());
    assertSame(t3t1, t3t1Join.getFromIndex());
    assertSame(t1, t3t1Join.getToTable());
    assertSame(t1t3, t3t1Join.getToIndex());
    
    doTestJoiner(t3t1Join, createT3T1Data());      

    doTestJoinerDelete(t2t1Join);
  }    
}
 

public static void checkIndexEntries(final TestDB testDB, Table t, Index index) throws Exception
  {
//         index.initialize();
//         System.out.println("Ind " + index);

    Cursor cursor = CursorBuilder.createCursor(index);
    while(cursor.moveToNextRow()) {

      Row row = cursor.getCurrentRow();

      Object data = row.get("data");
      if((testDB.getExpectedFileFormat() == Database.FileFormat.V1997) &&
         (data instanceof String) && ((String)data).contains("\uFFFD")) {
        // this row has a character not supported in the v1997 charset
        continue;
      }

      Cursor.Position curPos = cursor.getSavepoint().getCurrentPosition();
      boolean success = false;
      try {
        findRow(testDB, t, index, row, curPos);
        success = true;
      } finally {
        if(!success) {
          System.out.println("CurPos: " + curPos);
          System.out.println("Value: " + row + ": " +
                             toUnicodeStr(row.get("data")));
        }
      }
    }

  }
 

@Override
public void flush() throws IOException {
  if(_linkedDbs != null) {
    for(Database linkedDb : _linkedDbs.values()) {
      linkedDb.flush();
    }
  }
  _pageChannel.flush();
}
 

public void testAutoNumberGuid() throws Exception 
{
  for (final FileFormat fileFormat : SUPPORTED_FILEFORMATS) {
    Database db = createMem(fileFormat);

    Table table = new TableBuilder("test")
      .addColumn(new ColumnBuilder("a", DataType.GUID)
                .setAutoNumber(true))
      .addColumn(new ColumnBuilder("b", DataType.TEXT))
      .toTable(db);

    Object[] row = {null, "row1"};
    assertSame(row, table.addRow(row));
    assertTrue(ColumnImpl.isGUIDValue(row[0]));
    row = table.addRow(13, "row2");
    assertTrue(ColumnImpl.isGUIDValue(row[0]));
    row = table.addRow("flubber", "row3");
    assertTrue(ColumnImpl.isGUIDValue(row[0]));

    Object[] smallRow = {Column.AUTO_NUMBER};
    row = table.addRow(smallRow);
    assertNotSame(row, smallRow);
    assertTrue(ColumnImpl.isGUIDValue(row[0]));

    db.close();
  }
}
 

private Object now() {
  Database db = getColumn().getDatabase();
  if(db.getDateTimeType() == DateTimeType.DATE) {
    return new Date();
  }
  return LocalDateTime.now(db.getZoneId());
}
 

@Override
public void close() throws IOException {
  if(_linkedDbs != null) {
    for(Database linkedDb : _linkedDbs.values()) {
      linkedDb.close();
    }
  }
  _pageChannel.close();
}