A java class to create a sqlite database from a collection of raw UNIX 'fortune' files

fortune2sqlite.java

package com.arg3.java.fortunes;

import java.io.BufferedInputStream;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.zip.ZipEntry;
import java.util.zip.ZipInputStream;

/**
 * Reads a fortune database into a sqlite database
 * @author c0der78 (c0der78@gmail.com) [http://arg3.com/]
 */
public class Fortune2SQLite {
	
	// the name of the sqlite database file
	public static final String dbName = "fortunes.sqlite";
	
	// the fortune table
	public static final String fortuneTable = "fortunes";
	public static final String fortuneId = "fortuneId";
	public static final String fortuneText = "text";
	public static final String fortuneType = "type";
	
	// the category table
	public static final String categoryTable = "category";
	public static final String categoryId = "categoryId";
	public static final String categoryName = "name";
	public static final String categoryOffensive = "offensive";
	public static final String categoryEnabled = "enabled";

	// indicates offensive fortunes
	private static final String OffensivePath = "off/";
	
	Connection db;
	

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		try {
			Fortune2SQLite importer = new Fortune2SQLite();
			
			importer.run(args);
		} catch(Exception e) {
			System.err.println(e);
		}
	}

	public Fortune2SQLite() throws ClassNotFoundException, SQLException {
		Class.forName("org.sqlite.JDBC");
	    db = DriverManager.getConnection("jdbc:sqlite:" + dbName);
	}
	
	
	public void run(String[] args) throws SQLException, IOException {
		
		if(args.length == 0) {
			System.out.println("Syntax: Importer <directory|file|zipfile>");
			System.exit(0);
		}
		
		createTable();
		
		// get directory or file
		
		File f = new File(args[0]);
		
		if(f.isDirectory())
			importDirectory(f);
		else if(f.getName().endsWith(".zip"))
			importZip(f);
		else
			importFile(f);
	}
	
	// imports a directory of fortunes
	private void importDirectory(File d) throws SQLException, IOException {
		if(!d.isDirectory()) return;
		
		for(File f : d.listFiles())
		{
			if(f.isDirectory()) {
				importDirectory(f);
			} else if(f.getName().endsWith(".zip")) {
				importZip(f);
			} else if(!f.getName().startsWith(".")){
				importFile(f);
			}
		}
	}
	
	// imports a fortune file
	private void importFile(File f) throws SQLException, IOException {
		
		
		BufferedReader br = new BufferedReader(new FileReader(f));
		
		importBuffer(br, f.getName());
	}
	
	// does the actual inserting of a fortune/category
	private void doInsert(PreparedStatement fortuneStatement, PreparedStatement categoryStatement, String fileName, StringBuffer buf) throws SQLException
	{
		boolean isOffensive = fileName.startsWith(OffensivePath);
		
		if(isOffensive) {
			rot13(buf);
			fileName = fileName.substring(4);
		}
		
		// remove ending newline
		int len = buf.length();
		if(buf.charAt(len-1) == '\n')
			buf.deleteCharAt(len-1);
		
		// add the fortune
		fortuneStatement.setString(1, buf.toString());
		fortuneStatement.setString(2, fileName);
		fortuneStatement.addBatch();
		
		// and its category
		categoryStatement.setString(1, fileName);
		categoryStatement.setBoolean(2, isOffensive);
		categoryStatement.addBatch();
	}
	
	// imports from a buffered reader
	private void importBuffer(BufferedReader br, String fileName) throws SQLException, IOException
	{
		StringBuffer buf = new StringBuffer();

		System.out.println("Importing " + fileName + "...");
		
		// the fortune insert statement
		PreparedStatement fortuneStatement = db.prepareStatement(
	      "insert into " + fortuneTable + " (" + fortuneText + ","+fortuneType+") values (?, ?)");
		
		// the category insert statement
		PreparedStatement categoryStatement = db.prepareStatement(
		  "insert into " + categoryTable + " (" + categoryName + "," + categoryOffensive + ") values(?,?)");
		
		for(String line = br.readLine(); line != null; line = br.readLine())
		{
			if(line.equals("%")) {
				
				doInsert(fortuneStatement, categoryStatement, fileName, buf);
				
				buf = new StringBuffer();
				continue;
			}
			
			buf.append(line).append("\n");
		}
		
		if(buf.length() > 0) {
			
			doInsert(fortuneStatement, categoryStatement, fileName, buf);
		}

		db.setAutoCommit(false);
	    fortuneStatement.executeBatch();
	    categoryStatement.executeBatch();
	    db.setAutoCommit(true);
	}

	public void rot13(StringBuffer s) {
		for (int i = 0, l = s.length(); i < l; i++) {
            char c = s.charAt(i);
            if       (c >= 'a' && c <= 'm') c += 13;
            else if  (c >= 'n' && c <= 'z') c -= 13;
            else if  (c >= 'A' && c <= 'M') c += 13;
            else if  (c >= 'A' && c <= 'Z') c -= 13;
            s.setCharAt(i, c);
        }
	}
	
	// imports from a zip file containing one or more fortune files
	public void importZip(File f) throws SQLException, IOException {
		InputStream is = new FileInputStream(f);
		
		ZipInputStream zin = new ZipInputStream(new BufferedInputStream(is));
		ZipEntry ze = null;

		BufferedReader br = new BufferedReader(new InputStreamReader(zin));
		
		while ((ze = zin.getNextEntry()) != null) {

			if (ze.isDirectory()) {
				continue;
			}
			
			importBuffer(br, ze.getName());
		}
	}
	
	private void createTable() throws SQLException
	{
		StringBuffer buf = new StringBuffer("CREATE TABLE IF NOT EXISTS ");
		buf.append(fortuneTable).append(" (");
		buf.append(fortuneId).append(" INTEGER PRIMARY KEY AUTOINCREMENT, ");
		buf.append(fortuneText).append(" TEXT NOT NULL UNIQUE ON CONFLICT IGNORE, ");
		buf.append(fortuneType).append(" VARCHAR(250) )");
		
		Statement stmt = db.createStatement();
		
		stmt.executeUpdate(buf.toString());
		
		stmt.close();
		
		stmt = db.createStatement();
		
		buf = new StringBuffer("CREATE TABLE IF NOT EXISTS ");
		buf.append(categoryTable).append(" (");
		buf.append(categoryId).append(" INTEGER PRIMARY KEY AUTOINCREMENT, ");
		buf.append(categoryName).append(" VARCHAR(250) NOT NULL UNIQUE ON CONFLICT IGNORE, ");
		buf.append(categoryOffensive).append(" TINYINT(1) NOT NULL DEFAULT 0, ");
		buf.append(categoryEnabled).append(" TINYINT(1) NOT NULL DEFAULT 1 )");
		
		stmt.executeUpdate(buf.toString());
		
		stmt.close();
		
	}
}