class DBSupport {

    public static void createIndex(List<SearchIndexValue> indexValues, String dbFileDir) throws BuilderException {

        final String dbFile = concat(dbFileDir, DB_FILE);
        // Create DB file
        try (final Connection connection = DriverManager.getConnection("jdbc:sqlite:" + dbFile);
             final Statement stmt = connection.createStatement()){
            stmt.execute(CREATE_INDEX_SQL);
            // Update DB
            try (final PreparedStatement pst = connection.prepareStatement(INSERT_INDEX_SQL)) {
                for (final SearchIndexValue value : indexValues) {
                    pst.setString(1, value.getName());
                    pst.setString(2, value.getType().getTypeName());
                    pst.setString(3, value.getPath());
                    pst.execute();
                }
            }
        } catch (SQLException e) {
            throw new BuilderException("Error writing to SQLite DB", e);
        }
        logVerbose("Created the SQLite search index");
    }

}

class DBSupportTest {

    private static final File javadocLocation;
    private static final String QUERY;
    @Rule
    public TemporaryFolder temporaryFolder;

    @Test
    public void testCreateIndexDB() throws Exception {

        assertThat(javadocLocation, notNullValue());
        final IndexData indexData = JavadocSupport.findIndexFile(javadocLocation);
        final List<SearchIndexValue> indexValues = JavadocSupport.findSearchIndexValues(indexData.getFilesToIndex());
        final String docFileRoot = FilenameUtils.concat(temporaryFolder.getRoot().getPath(), "Foo");
        final String dbDirName = DocSetSupport.getDBDir(docFileRoot);
        final File dbDir = getFile(dbDirName);
        FileUtils.forceMkdir(dbDir);
        DBSupport.createIndex(indexValues, dbDirName);
        final File dbFile = getFile(dbDir, "docSet.dsidx");
        assertTrue("DB file does not exist", dbFile.exists());

        final Map<String, Integer> expectedTypes = ExpectedDataUtil.getExpectedData().getExpectedDataBaseTypes();
        try (final Connection connection = DriverManager.getConnection("jdbc:sqlite:" + dbFile);
             final PreparedStatement stmt = connection.prepareStatement(QUERY)){

            for (Map.Entry<String, Integer> expectedEntry : expectedTypes.entrySet()) {
                stmt.setString(1, expectedEntry.getKey());
                try (final ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()) {
                        final int count = rs.getInt(1);
                        assertThat(expectedEntry.getValue().intValue(), is(count));
                    }
                }
            }
        }
    }

}