package votorola.a.diff.harvest.cache;
import java.security.NoSuchAlgorithmException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Logger;
import javax.mail.internet.AddressException;
import votorola.a.diff.harvest.Message;
import votorola.a.voter.IDPair;
import votorola.g.lang.ThreadRestricted;
import votorola.g.lang.ThreadSafe;
import votorola.g.lang.Warning;
import votorola.g.logging.LoggerX;
import votorola.g.sql.Database;
/**
* The relational store for diff messages. This class automatically creates the
* necessary table if it does not exist and basically hides away the database
* layer. Note: It throws errors on double insertions.
*/
@ThreadSafe
final public class DiffMessageTable {
/**
* This is only exposed as a quick hint for somebody trying to understand
* the database structure. This value might change.
*/
@Warning("non-Api")
public static final String SCHEMA_NAME = "harvest";
/**
* This is only exposed as a quick hint for somebody trying to understand
* the database structure. This value might change.
*/
@Warning("non-Api")
public static final String TABLE_NAME = "diff_messages";
private static final String TABLE = "\"" + SCHEMA_NAME + "\".\""
+ TABLE_NAME + "\"";
/**
* Maximum rows in the result. This should match the maximum client fetch
* length.
*/
@Warning("non-Api")
public static final int MAX_RESULTS = 200;
private final static Logger LOGGER = LoggerX.i(DiffMessageTable.class);
/**
* Main database object.
*/
private final transient Database database;
/**
* Constructs a Table.
*
* @param database
* Database to connect to.
* @throws SQLException
* @throws NoSuchAlgorithmException
*/
DiffMessageTable(final Database database) throws SQLException {
this.database = database;
synchronized (database) {
database.ensureSchema(SCHEMA_NAME);
}
statementKeyBase = getClass().getName() + ":" + SCHEMA_NAME + "/"
+ TABLE_NAME + ".";
}
/**
* Statement key base for all queries.
*/
private final transient String statementKeyBase;
/**
* Creates this table in the database.
*
* @throws SQLException
*/
void create() throws SQLException {
final String key = statementKeyBase + "create";
synchronized (database) {
PreparedStatement prepStatem = database.statementCache().get(key);
if (prepStatem == null) {
prepStatem = database
.connection()
.prepareStatement(
"CREATE TABLE "
+ TABLE
+ " (id serial primary key"
+ ", author varchar NOT NULL"
+ ", addressee varchar NOT NULL"
+ ", pollname varchar NOT NULL"
+ ", content varchar NOT NULL"
+ ", a integer NOT NULL"
+ ", ar integer NOT NULL"
+ ", b integer NOT NULL"
+ ", br integer NOT NULL"
+ ", selectand varchar NOT NULL"
+ ", archive_url varchar NOT NULL"
+ ", path varchar NOT NULL"
+ ", sent_ts timestamp with time zone NOT NULL"
+ ", UNIQUE(author,addressee,archive_url,path,sent_ts)"
+ " )");
database.statementCache().put(key, prepStatem);
}
prepStatem.execute();
}
}
/**
* Returns the database in which this table is stored.
*
* @return This objects database.
*/
Database getDatabase() {
return database;
}
/**
* Drops this table from the database.
*
* @throws SQLException
*/
void drop() throws SQLException {
final String key = statementKeyBase + "drop";
synchronized (database) {
PreparedStatement prepStatem = database.statementCache().get(key);
if (prepStatem == null) {
prepStatem = database.connection().prepareStatement(
"DROP TABLE " + TABLE);
database.statementCache().put(key, prepStatem);
}
prepStatem.execute();
}
}
/**
* Returns true if this table exists in the database; false otherwise.
*
* @return whether database already exists or not.
* @throws SQLException
*/
public boolean exists() throws SQLException {
final String key = statementKeyBase + "exists";
synchronized (database) {
PreparedStatement prepStatem = database.statementCache().get(key);
if (prepStatem == null) {
prepStatem = database.connection().prepareStatement(
"SELECT * FROM " + TABLE);
prepStatem.setMaxRows(1);
database.statementCache().put(key, prepStatem);
}
try {
prepStatem.execute();
} catch (SQLException x) {
final String sqlState = x.getSQLState();
if ("3F000".equals(sqlState)) {
return false; // 3F000 = [missing schema]
}
if ("42P01".equals(sqlState)) {
return false; // 42P01 = UNDEFINED TABLE
}
throw x;
}
}
return true;
}
/**
* Query the table for the parameters. Order is: id, author, addressee,
* pollname, content, archive_url, path, sent_ts, a, ar, b, br, selectand
*
*
* @param poll
* poll-name
* @param users
* mailish-username of one or two users
* @param id
* only fetch posts newer than id
* @return rs ResultSet with newest first.
* @throws SQLException
* @throws AddressException
*/
@ThreadRestricted("rs holds lock on database")
public ResultSet get(final String poll, final String[] users, final int id)
throws SQLException, AddressException {
final StringBuilder whereScope = new StringBuilder();
String conj = " WHERE"; // conjunction
if (poll != null && !poll.isEmpty()) {
whereScope.append(conj).append(" pollname = ?");
conj = " AND";
}
if (users != null && users.length != 0) {
for (final String user : users) {
if (user.isEmpty()) {
continue;
}
whereScope.append(conj)
.append(" (addressee = ? OR author = ?)");
conj = " AND";
}
}
if (id > 0) {
whereScope.append(conj).append(" id > ?");
conj = " AND";
}
final String key = statementKeyBase + "getDiffMsgs"
+ whereScope.toString();
PreparedStatement prepStatem = database.statementCache().get(key);
final String query = "SELECT id, author, addressee, pollname"
+ ", content, archive_url, path, sent_ts" /* message */
+ ", a, ar, b, br, selectand" /* difference */
+ " FROM " + TABLE + whereScope.toString()
+ " ORDER BY sent_ts DESC LIMIT " + MAX_RESULTS;
if (prepStatem == null) {
prepStatem = database.connection().prepareStatement(query);
database.statementCache().put(key, prepStatem);
}
int index = 1;
if (poll != null && !poll.isEmpty()) {
prepStatem.setString(index++, poll);
}
if (users != null && users.length != 0) {
for (final String user : users) {
if (user.isEmpty()) {
continue;
}
final String email = IDPair.toInternetAddress(user)
.getAddress();
prepStatem.setString(index++, email);
prepStatem.setString(index++, email);
}
}
if (id != 0) {
prepStatem.setInt(index++, id);
}
return prepStatem.executeQuery();
}
/**
* Stores a message. This throws an exception on double insert attempts on
* the primary key, which consists author,addresse,url and sentDate
*
* @throws SQLException
*/
void put(final AuthDiffMessage authMsg) throws SQLException {
synchronized (database) {
final String insertKey = statementKeyBase + "putDiffMsgI";
PreparedStatement s = database.statementCache().get(insertKey);
if (s == null) {
s = database
.connection()
.prepareStatement(
"INSERT INTO "
+ TABLE
+ " (author"
+ ", addressee, archive_url, path, pollname"
+ ", content, a, ar"
+ ", b, br, sent_ts, selectand"
+ ")"
+ " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
database.statementCache().put(insertKey, s);
}
Message msg = authMsg.diffMessage().message();
s.setString(1, authMsg.author());
s.setString(2, authMsg.addressee());
s.setString(3, msg.mc().archiveUrl());
s.setString(4, msg.path());
s.setString(5, authMsg.diffMessage().draftPair().aCore()
.pollName());
s.setString(6, msg.content());
{
// CWFIX obsolete form, e.g. by storing p.key() instead
final votorola.a.diff.DiffKeyParse parse =
authMsg.diffMessage().draftPair().diffKeyParse();
final java.util.List<Integer> aPath = parse.aPath();
final java.util.List<Integer> bPath = parse.bPath();
final int aN = aPath.size();
final int bN = bPath.size();
if(aN > 2 || bN > 2) {
throw new votorola.g.VotorolaRuntimeException(
"new draft revision path too long for old Harvester" );
}
s.setInt(7, aPath.get(0));
s.setInt(8, aN > 1? aPath.get(1): -1);
s.setInt(9, bPath.get(0));
s.setInt(10, bN > 1? bPath.get(1): -1);
}
s.setTimestamp(11, new java.sql.Timestamp(msg.mc().sentDate()
.getTime()));
s.setString(12, authMsg.selectand());
s.executeUpdate();
}
}
/**
* Remove all stored messages for an archive selected by its archive url.
*
* @param archiveUrl
* @return number of removed messages
* @throws SQLException
*/
public int removeArchive(final String archiveUrl) throws SQLException {
synchronized (database) {
final String removeKey = statementKeyBase + "remDiffMsgI";
PreparedStatement s = database.statementCache().get(removeKey);
if (s == null) {
s = database.connection().prepareStatement(
"DELETE FROM " + TABLE + " where archive_url=?");
database.statementCache().put(removeKey, s);
}
s.setString(1, archiveUrl);
return s.executeUpdate();
}
}
}