package no.priv.garshol.duke; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Collection; import java.util.List; import no.priv.garshol.duke.utils.JDBCUtils; /** * An abstract SQL-based link database implementation which can can * maintain a set of links in an H2 or Oracle database over JDBC. It * could be extended to work with more database implementations. What * the abstract class cannot do is create a connection, which is left * for subclasses to do. */ public abstract class RDBMSLinkDatabase implements LinkDatabase { private DatabaseType dbtype; private String tblprefix; // prefix for table names ("foo."); never null protected Statement stmt; // set by subclass private Logger logger; private static final SimpleDateFormat dtformat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS"); public RDBMSLinkDatabase(String dbtype) { this.dbtype = getDatabaseType(dbtype); this.tblprefix = ""; this.logger = new DummyLogger(); } /** * This method must be called to initialize the database. In order * to avoid problems with constructor call sequencing (parent * constructor must be called first), we had to move this out into a * separate method. */ public void init() { try { verifySchema(); } catch (Throwable e) { close(); throw new DukeException(e); } } public void setLogger(Logger logger) { this.logger = logger; } public void setTablePrefix(String tblprefix) { this.tblprefix = tblprefix; } public List<Link> getAllLinks() { return getChangesSince(0, 0, 0); } public List<Link> getChangesSince(long since) { return getChangesSince(since, 0, 0); } public List<Link> getChangesSince(long since, long before) { return getChangesSince(since, before, 0); } public List<Link> getChangesSince(long since, long before, int pagesize) { String where = ""; if (since != 0 || before != 0) where = "where "; if (since != 0) where += "timestamp > TIMESTAMP '" + dtformat.format(since) + "'"; if (before != 0) { if (since != 0) where += "and "; where += "timestamp <= TIMESTAMP '" + dtformat.format(before) + "'"; } // oracle must do the limit in where, while H2 supports normal SQL String limit = ""; if (pagesize != 0) { limit = dbtype.getLimit(pagesize); if (limit.length() == 0) { // we are in oracle territory now. prepare for some seriously ugly // string hacking if (where.length() > 0) // hack hack where += " AND " + dbtype.getWhereLimit(pagesize); else // hackety hackty where += " where " + dbtype.getWhereLimit(pagesize); // *vomit* } } return queryForLinks("select * from " + tblprefix + "links " + where + " order by timestamp desc " + limit); } public Collection<Link> getAllLinksFor(String id) { return queryForLinks("select * from " + tblprefix + "links where " + "id1 = '" + escape(id) + "' or " + "id2 = '" + escape(id) + "'"); } public void assertLink(Link link) { logger.debug("Asserting link " + link); // (1) query to see if the link is already there // FIXME: use prepared statement try { Link existing = null; ResultSet rs = stmt.executeQuery("select * from " + tblprefix + "links where " + "id1 = '" + escape(link.getID1()) + "' and " + "id2 = '" + escape(link.getID2()) + "'"); if (rs.next()) { existing = makeLink(rs); rs.close(); if (!link.overrides(existing)) return; // the existing link rules, so we shut up and go away } rs.close(); // (2) write link to database String query; if (existing != null) { logger.trace("Updating link for " + link.getID1() + " and " + link.getID2()); query = "update " + tblprefix + "links set status = " + link.getStatus().getId() + " , kind = " + link.getKind().getId() + " , timestamp = " + dbtype.getNow() + " " + " , confidence = " + link.getConfidence() + " " + "where id1 = '" + escape(link.getID1()) + "' " + " and id2 = '" + escape(link.getID2()) + "' "; } else { logger.trace("Inserting link for " + link.getID1() + " and " + link.getID2()); query = "insert into " + tblprefix + "links values ('" + escape(link.getID1()) + "', " + " '" + escape(link.getID2()) + "', " + link.getKind().getId() + " , " + link.getStatus().getId() + ", " + dbtype.getNow() + ", " + link.getConfidence() + ") "; } stmt.executeUpdate(query); } catch (SQLException e) { close(); // releasing connection throw new DukeException(e); } } public Link inferLink(String id1, String id2) { // are we sure this method really belongs in the interface, and // not in an external utility? throw new DukeException("not implemented yet"); } /** * Empties the link database. Used only for testing at the moment. */ public void clear() { try { stmt.executeUpdate("delete from " + tblprefix + "links"); } catch (SQLException e) { close(); // releasing connection throw new DukeException(e); } } public void commit() { try { Connection conn = stmt.getConnection(); if (!conn.getAutoCommit()) // we only call commit if the connection is not auto-committing, as // mysql throws an exception otherwise (issue 105) conn.commit(); } catch (SQLException e) { close(); // releasing connection throw new DukeException(e); } } public void close() { JDBCUtils.close(stmt); } private void verifySchema() throws SQLException { String lastpart = ""; if (!tblprefix.equals("")) lastpart = "AND owner = '" + tblprefix.substring(0, tblprefix.length() - 1) + "'"; boolean present = JDBCUtils.queryHasResult(stmt, "select * from " + dbtype.getMetaTableName() + " " + "where table_name = 'LINKS'" + lastpart); if (present) return; logger.warn("Table LINKS not found; recreating"); stmt.executeUpdate(dbtype.getCreateTable()); // creating indexes, too, as that makes processing *much* faster stmt.executeUpdate("create index " + tblprefix + "links_ix_id1 on " + tblprefix + "links (id1)"); stmt.executeUpdate("create index " + tblprefix + "links_ix_id2 on " + tblprefix + "links (id2)"); } private String escape(String strval) { return strval.replace("'", "''"); } private List<Link> queryForLinks(String query) { List<Link> links = new ArrayList(); try { logger.trace("Querying for links: " + query); ResultSet rs = stmt.executeQuery(query); while (rs.next()) links.add(makeLink(rs)); rs.close(); // FIXME: finally } catch (SQLException e) { close(); // releasing connection throw new DukeException(e); } return links; } private Link makeLink(ResultSet rs) throws SQLException { return new Link(rs.getString("id1"), rs.getString("id2"), LinkStatus.getbyid(rs.getInt("status")), LinkKind.getbyid(rs.getInt("kind")), rs.getTimestamp("timestamp").getTime(), rs.getDouble("confidence")); } // ===== DATABASE TYPES private static DatabaseType getDatabaseType(String dbtype) { if (dbtype.equals("h2")) return DatabaseType.H2; else if (dbtype.equals("oracle")) return DatabaseType.ORACLE; else if (dbtype.equals("mysql")) return DatabaseType.MYSQL; else throw new DukeConfigException("Unknown database type: '" + dbtype + "'"); } public enum DatabaseType { MYSQL { public String getMetaTableName() { return "information_schema.tables"; } public String getCreateTable() { return "create table LINKS ( " + " id1 varchar (100) not null, " + " id2 varchar (100) not null, " + " kind int not null, " + " status int not null, " + " timestamp timestamp not null, " + " confidence float not null, " + " primary key (id1, id2)) "; } public String getNow() { return "now()"; } public String getLimit(int no) { return "limit " + no; } public String getWhereLimit(int no) { return ""; } }, H2 { public String getMetaTableName() { return "information_schema.tables"; } public String getCreateTable() { return "create table LINKS ( " + " id1 varchar not null, " + " id2 varchar not null, " + " kind int not null, " + " status int not null, " + " timestamp timestamp not null, " + " confidence float not null, " + " primary key (id1, id2)) "; } public String getNow() { return "now()"; } public String getLimit(int no) { return "limit " + no; } public String getWhereLimit(int no) { return ""; } }, ORACLE { public String getMetaTableName() { return "all_tables"; } public String getCreateTable() { return "create table LINKS ( " + " id1 varchar(200) not null, " + " id2 varchar(200) not null, " + " kind int not null, " + " status int not null, " + " timestamp timestamp not null, " + " confidence float not null, " + " primary key (id1, id2)) "; } public String getNow() { return "current_timestamp"; } public String getLimit(int no) { return ""; } public String getWhereLimit(int no) { return "rownum <= " + no; } }; public abstract String getMetaTableName(); public abstract String getCreateTable(); public abstract String getNow(); public abstract String getLimit(int no); public abstract String getWhereLimit(int no); } }