Add SQLite Free Text Search functionality to Java app on Mac

Question: I’d like to pre-build a platform independent SQLite database with Java desktop app. This database file with FTS (Free Text Search) indexes will be embedded into Android and iPhone applications. How can I install an SQLite3 free text search (FTS3) module for my Java desktop application? Somehow this binary, library (or app file) cannot be found on the net. Environment: OS X 10.6, NetBeans IDE, Java Swing desktop app with sqlite-jdbc-3.7.2.jar wrapper. Solution: The default SQLITE3 installation on OS X does not contain the extension you need to run FTS functions. You have to build and install your own libsqlite3.dylib for your Snow Leopard. FTS3 and FTS4 are included with the SQLite core source code, but they are not enabled by default. Luckily you do not need Eclipse or Xcode to build the custom dynamic library. 1. Download the tarball file containing the amalgamation for the latest SQLite database (v. 3.7.5 now) together with configure script and makefile for building it from sqlite.org. 2. Untar the downloaded tar.gz file. Remember the path. 3. Open Terminal window. Navigate to the folder your decompressed files are, like
cd /users/me/sqlite3
4. In Terminal execute the configuration command:
CPPFLAGS="-DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_FTS3_PARENTHESIS" ./configure
This sets CPPFLAGS environment variables. 5. In the Terminal make the dynamic library and copy it to the /usr/lib system folder with this command:
make install
6. Test your installation in the Terminal:
sqlite3 mydatabasefile.sqlite

The dynamic library will return its version number like this:
SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
When the command returns an older version, or you are getting “no such module: fts3” or “no such module: fts4” error message while trying to call FTS functions, than – there is more than one copy of libsqlite3.dylib in your Mac, and/or you have – incorrect PATH environment. The new library is probably installed to /usr/local/bin folder. In this case be sure that your PATH environment string starts with /usr/local/bin:. If not, edit or create .bashrc file in your home folder (/Users/yourhomeaccount). It’s a hidden file and look for PATH variable and change (or set) that. Use defaults write com.apple.finder AppleShowAllFiles TRUE command to show it in Finder. Edit with TextEdit like this: PATH=/usr/local/mysql/bin:${PATH}. In the .bash_profile you can also insert this line: export PATH=”/usr/local/bin:$PATH” Test installation from your Java code like this:
public boolean checkFTS3(){

    Connection testConnection = null;
    boolean success = false;
    try {

        // We are building a temporary memory SQLite database
        testConnection = DriverManager.getConnection("jdbc:sqlite::memory:");
        Statement testStatement = testConnection.createStatement();
        testStatement.setQueryTimeout(30);

        testStatement.executeUpdate("DROP TABLE IF EXISTS testtable;");
        testStatement.executeUpdate("CREATE VIRTUAL TABLE testtable USING FTS4(tid text, description text);");
        testStatement.executeUpdate("INSERT INTO testtable VALUES("1", "Test Record");");
        // No exception so far
        success = true;
    }
    catch (SQLException e) {
        System.out.println("checkFTS3 exception=" + e.getMessage());
    }
    finally {
        try {
            if (testConnection != null) {
                testConnection.close();
            }
        }
        catch (SQLException e) {
            System.err.println(e);
        }
        return success;
    }
}
Notes: – In Terminal window, from the command line, you can execute sqlite3 -version command to see what OS X finds on your machine – From your code you can check the run-time library version by calling sqlite3_libversion_number() core function.