private HashMap<String,SqliteTable> tables = new HashMap<String,SqliteTable>();
public static final int REAPER_INTERVAL_SECONDS = 60 * 60;
+ private static final int DAYS = 24 * 60 * 60 * 1000;
+ public static final int REAP_EXPIRATION = 5 * DAYS;
public Connection getConnection() { return conn; }
- public synchronized SqliteTable getTable(String name) {
+ public synchronized SqliteTable getTable(String name, String schema) throws SQLException {
SqliteTable ret = tables.get(name);
- if (ret==null) ret = new SqliteTable(name);
+ if (ret==null) ret = new SqliteTable(name, schema);
return ret;
}
// check upstream: PRAGMA encoding = "UTF-8";
// create indices
- // PRAGMA auto_vacuum=1 (can only be set before any tables are created)
- // periodic "PRAGMA integrity_check; "?
+ // periodically run "analyze"?
public void setCacheSize(int kilobytes) throws SQLException {
conn.prepareStatement("PRAGMA cache_size="+Math.ceil(kilobytes/1.5)+";").executeUpdate();
}
- public SqliteDB(String filename, String[] tables) {
- this(filename, tables, false);
+
+ public void close(ResultSet rs) {
+ if (rs==null) return;
+ try {
+ rs.close();
+ } catch (SQLException s) {
+ Log.error(ResultSet.class, s);
+ }
}
- public SqliteDB(String filename, String[] tables, boolean fastButDangerous) {
+
+ public SqliteDB(String filename) {
this.filename = filename;
try {
+ Log.error("start", "initializing " + filename);
Class.forName("org.sqlite.JDBC");
conn = DriverManager.getConnection("jdbc:sqlite:"+filename);
- for(String s : tables)
- conn.prepareStatement(s).executeUpdate();
- conn.prepareStatement("PRAGMA temp_store = MEMORY").executeUpdate();
+ conn.prepareStatement("PRAGMA auto_vacuum = 1").executeUpdate();
+ //conn.prepareStatement("VACUUM").executeUpdate();
+
+ // until we have better assurances about locking on network filesystems...
+ conn.prepareStatement("PRAGMA locking_mode = EXCLUSIVE").executeQuery();
+
+ //conn.prepareStatement("PRAGMA temp_store = MEMORY").executeUpdate();
conn.prepareStatement("PRAGMA page_size=4096").executeUpdate();
conn.prepareStatement("PRAGMA cache_size=2000").executeUpdate();
- if (fastButDangerous)
- conn.prepareStatement("PRAGMA synchronous = OFF").executeUpdate();
+ ResultSet rs = conn.prepareStatement("PRAGMA integrity_check").executeQuery();
+ rs.next();
+ String result = rs.getString(1);
+ if (!result.equals("ok"))
+ throw new RuntimeException("PRAGMA integrity_check returned \""+result+"\"");
+ Log.error(".", "done initializing " + filename);
}
catch (SQLException e) { throw new RuntimeException(e); }
catch (ClassNotFoundException e) { throw new RuntimeException(e); }
}
+ public void setFastButDangerous(boolean on) throws SQLException {
+ conn.prepareStatement("PRAGMA synchronous = "+(on?"OFF":"ON")).executeUpdate();
+ }
+
+ /*
+ public void dump(OutputStream os) {
+ }
+ */
+
public class SqliteTable {
public final String name;
private String reapColumn = null;
- private SqliteTable(String name) {
+ private SqliteTable(String name, String schema) throws SQLException {
this.name = name;
+ PreparedStatement ps = conn.prepareStatement("create table if not exists " + name + " " + schema);
+ ps.executeUpdate();
tables.put(name, this);
}
+ public void createIndex(String column) throws SQLException { createIndex(column, column+"_index"); }
+ public void createIndex(String indexName, String column) throws SQLException {
+ PreparedStatement ps = conn.prepareStatement("create index if not exists "+column+" on "+name+" ("+indexName+")");
+ ps.executeUpdate();
+ }
protected void reap(String reapColumn) {
if (this.reapColumn != null) throw new RuntimeException("reapColumn already set");
this.reapColumn = reapColumn;
try {
Log.warn(Reaper.class, filename + " reaping...");
long when = System.currentTimeMillis();
- when -= 5 * 24 * 60 * 60 * 1000;
+ when -= REAP_EXPIRATION;
synchronized(SqliteDB.this) {
PreparedStatement ps =
conn.prepareStatement("select count(*) from "+reapTable+" where "+reapColumn+"<?");
}
}
+ static String streamToString(Stream stream) throws Exception {
+ // FIXME!!!! This is corrupting line endings!!!!
+ StringBuffer b = new StringBuffer();
+ for(String s = stream.readln(); s!=null; s=stream.readln())
+ b.append(s+"\n");
+ return b.toString();
+ }
}