public int getNumInvalidMessages(String boardName) {
if (boardName == null) {
try {
Statement s0 = connection.createStatement();
ResultSet set0 =
s0.executeQuery("select count(*) from Message where length(formatted_content) < 10");
if (!set0.next()) return 0;
int count = set0.getInt(1);
set0.close();
s0.close();
return count;
} catch (SQLException e) {
e.printStackTrace();
return 0;
}
} else {
try {
MessageBoard board = this.loadMessageBoard(boardName);
if (board == null) {
return 0;
}
Statement s0 = connection.createStatement();
ResultSet set0 =
s0.executeQuery(
"select count(m.id) from Message m, MessageThread t "
+ " where length(m.formatted_content) < 10 and m.threadID = t.id and t.boardID = "
+ board.getId());
int count = 0;
while (set0.next()) {
count += Integer.parseInt(set0.getString(1));
}
set0.close();
s0.close();
return count;
} catch (SQLException e) {
e.printStackTrace();
return 0;
}
}
}
public int getNumUsers(String boardName) {
if (boardName == null) {
try {
Statement s0 = connection.createStatement();
ResultSet set0 = s0.executeQuery("select count(*) from User");
if (!set0.next()) return 0;
int count = set0.getInt(1);
set0.close();
s0.close();
return count;
} catch (SQLException e) {
e.printStackTrace();
return 0;
}
} else {
try {
MessageBoard board = this.loadMessageBoard(boardName);
if (board == null) {
return 0;
}
Statement s0 = connection.createStatement();
ResultSet set0 =
s0.executeQuery(
"select count(u.id) from User u " + " where u.boardID = " + board.getId());
int count = 0;
while (set0.next()) {
count += Integer.parseInt(set0.getString(1));
}
set0.close();
s0.close();
return count;
} catch (SQLException e) {
e.printStackTrace();
return 0;
}
}
}
public void insertMessageThread(String url, String name, MessageBoard board)
throws SQLException, IllegalArgumentException {
// check for null params
//
if (url == null || board == null)
throw new IllegalArgumentException(
"Failed to insert message thread (url or message board are null)");
// make sure the thread doesn't exist
//
if (loadMessageThread(url) != null) {
return;
}
String query = "insert into MessageThread(name,url,boardID) values(?,?,?);";
PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, name);
statement.setString(2, url);
statement.setInt(3, board.getId());
statement.execute();
statement.close();
// System.out.println ("Inserted a new message thread:");
// System.out.println ("\t> name : " + name);
// System.out.println ("\t> url : " + url);
// if (board != null) {
// System.out.println ("\t> board : " + board.getName());
// } else {
// log this error
// }
}
public MessageBoard loadMessageBoard(String name) throws IllegalArgumentException, SQLException {
if (name == null) throw new IllegalArgumentException("Name is null");
Statement s0 = connection.createStatement();
ResultSet set0 = s0.executeQuery("select * from MessageBoard where name like ?");
if (!set0.next()) return null;
MessageBoard board = new MessageBoard();
board.setDescription(set0.getString("description"));
board.setId(set0.getInt("id"));
board.setName(set0.getString("name"));
board.setUrl(set0.getString("url"));
board.setMessageThreads(null);
board.setUsers(null);
set0.close();
s0.close();
return board;
}
public int getNumWords(String boardName, boolean distinct) {
if (distinct) {
try {
Statement s0 = connection.createStatement();
ResultSet set0 = s0.executeQuery("select count(*) from Word");
if (!set0.next()) return 0;
int count = set0.getInt(1);
set0.close();
s0.close();
return count;
} catch (SQLException e) {
e.printStackTrace();
return 0;
}
} else {
try {
if (boardName != null) {
MessageBoard board = this.loadMessageBoard(boardName);
if (board == null) {
return 0;
}
Statement s0 = connection.createStatement();
ResultSet set0 =
s0.executeQuery(
"select s.propertyValue from settings s, Message m, MessageThread t "
+ " where propertyKey like 'num-words' and s.tableID = m.id and m.threadID = t.id and t.boardID = "
+ board.getId());
int count = 0;
while (set0.next()) {
count += Integer.parseInt(set0.getString(1));
}
set0.close();
s0.close();
return count;
} else {
Statement s0 = connection.createStatement();
ResultSet set0 =
s0.executeQuery(
"select s.propertyValue from settings s "
+ " where propertyKey like 'num-words'");
int count = 0;
while (set0.next()) {
count += Integer.parseInt(set0.getString(1));
}
set0.close();
s0.close();
return count;
}
} catch (SQLException e) {
e.printStackTrace();
return 0;
}
}
}
public List<MessageWithProperties> loadMessagesWithProperties(String boardName)
throws SQLException {
List<MessageWithProperties> messages = new LinkedList<MessageWithProperties>();
if (boardName == null) {
// prepare the statement
//
String query = "select * from Message";
Statement statement = connection.createStatement();
ResultSet set = statement.executeQuery(query);
MessageWithProperties msg = null;
while (set.next()) {
msg = new MessageWithProperties();
msg.setId(set.getInt("id"));
msg.setContent(set.getString("content"));
msg.setFormattedContent(set.getString("formatted_content"));
msg.setPublishDate(set.getString("publishDate"));
msg.setUrl(set.getString("url"));
msg.loadProperties(connection);
messages.add(msg);
}
set.close();
statement.close();
} else {
MessageBoard board = this.loadMessageBoard(boardName);
if (board == null) {
return messages;
}
// prepare the statement
//
String query =
"select m.* from Message m, MessageThread t where m.threadID = t.id and t.boardID = "
+ board.getId();
Statement statement = connection.createStatement();
ResultSet set = statement.executeQuery(query);
MessageWithProperties msg = null;
while (set.next()) {
msg = new MessageWithProperties();
msg.setId(set.getInt("id"));
msg.setContent(set.getString("content"));
msg.setFormattedContent(set.getString("formatted_content"));
msg.setPublishDate(set.getString("publishDate"));
msg.setUrl(set.getString("url"));
msg.loadProperties(connection);
messages.add(msg);
}
set.close();
statement.close();
}
return messages;
}
