package org.gjt.bugrat.dbi;
import java.text.SimpleDateFormat;
import java.text.FieldPosition;
import java.text.ParsePosition;
import java.sql.*;
import java.util.Hashtable;
import org.gjt.bugrat.db.*;
public class
EmailJDBC extends BugRatJDBC
implements EmailDBI
{
private static final boolean DEBUG = false;
private SimpleDateFormat dateFmt;
public
EmailJDBC( JDBCHandler handler )
{
super( handler );
this.dateFmt =
new SimpleDateFormat
( "yyyy-MM-dd HH:mm:ss" ); // UNDONE - configuration
}
public EmailMsg
getEmailMsg( int id )
throws DBIException
{
EmailMsg result = null;
String queryStr = null;
try {
queryStr =
"SELECT" +
" recvdate," +
" hdrmsgid," +
" hdrfrom," +
" hdrto," +
" hdrsubject " +
"FROM email " +
"WHERE id = '" + id + "'";
Statement stmt = this.handler.createStatement();
ResultSet rs = stmt.executeQuery( queryStr );
if ( rs.next() )
{
int col = 1;
result = new EmailMsg( id );
result.setModified( false );
result.setInDatabase( true );
ParsePosition pos = new ParsePosition(0);
java.util.Date recvDate =
this.dateFmt.parse( rs.getString( col++ ), pos );
result.setReceiveDate( recvDate );
result.setMessageID( rs.getString( col++ ) );
result.setFrom( rs.getString( col++ ) );
result.setTo( rs.getString( col++ ) );
result.setSubject( this.getString( rs, col++ ) );
}
rs.close();
stmt.close();
}
catch ( SQLException ex )
{
if ( DEBUG )
ex.printStackTrace( System.err );
throw new DBIException
( ex, "statement '" + queryStr + "'" );
}
return result;
}
public EmailMsg
getEmailMsg( String msgId )
throws DBIException
{
EmailMsg result = null;
String queryStr = null;
try {
queryStr =
"SELECT" +
" id," +
" recvdate," +
" hdrfrom," +
" hdrto," +
" hdrsubject " +
"FROM email " +
"WHERE hdrmsgid = '" + msgId + "'";
Statement stmt = this.handler.createStatement();
ResultSet rs = stmt.executeQuery( queryStr );
if ( rs.next() )
{
int col = 1;
int id = rs.getInt( col++ );
result = new EmailMsg( id );
result.setModified( false );
result.setInDatabase( true );
ParsePosition pos = new ParsePosition(0);
java.util.Date recvDate =
this.dateFmt.parse( rs.getString( col++ ), pos );
result.setReceiveDate( recvDate );
result.setMessageID( msgId );
result.setFrom( rs.getString( col++ ) );
result.setTo( rs.getString( col++ ) );
result.setSubject( this.getString( rs, col++ ) );
}
rs.close();
stmt.close();
}
catch ( SQLException ex )
{
if ( DEBUG )
ex.printStackTrace( System.err );
throw new DBIException
( ex, "statement '" + queryStr + "'" );
}
return result;
}
public EmailMsg
getNewEmailMsg()
throws DBIException
{
EmailMsg result = null;
String updStr = null;
try {
StringBuffer dateStr = new StringBuffer();
FieldPosition pos = new FieldPosition(0);
this.dateFmt.format( new java.util.Date(), dateStr, pos );
int emailId = this.getNextId( "email" );
updStr =
"INSERT INTO email " +
" ( id, recvdate, hdrmsgid, hdrfrom, hdrto, hdrsubject ) " +
"VALUES " +
" ( " + emailId + ", '" + dateStr + "', '', '', '', null ) ";
Statement stmt = this.handler.createStatement();
int rows = stmt.executeUpdate( updStr );
stmt.close();
if ( DEBUG )
System.err.println( "CREATING NEW PERSON rows=" + rows );
if ( rows < 1 )
{
throw new DBIException
( "could not create new email" );
}
if ( DEBUG )
System.err.println( "CREATE EMAIL id=" + emailId );
result = this.getEmailMsg( emailId );
}
catch ( SQLException ex )
{
if ( DEBUG )
ex.printStackTrace( System.err );
throw new DBIException
( ex, "statement '" + updStr + "'" );
}
return result;
}
public void
commit( EmailMsg msg )
throws DBIException
{
try {
StringBuffer dateStr = new StringBuffer();
FieldPosition pos = new FieldPosition(0);
this.dateFmt.format( new java.util.Date(), dateStr, pos );
StringBuffer buf = new StringBuffer();
if ( msg.isInDatabase() )
{
buf.append( "UPDATE email " );
buf.append( "SET " );
buf.append( " email.recvdate = ?," );
buf.append( " email.hdrmsgid = ?," );
buf.append( " email.hdrfrom = ?," );
buf.append( " email.hdrto = ?," );
buf.append( " email.hdrsubject = ? " );
buf.append( "WHERE id = '" + msg.getId() + "' " );
}
else
{
buf.append( "INSERT INTO person " );
buf.append( "( " );
buf.append( " email.id," );
buf.append( " email.recvdate," );
buf.append( " email.hdrmsgid," );
buf.append( " email.hdrfrom," );
buf.append( " email.hdrto," );
buf.append( " email.hdrsubject " );
buf.append( ") " );
buf.append( "VALUES " );
buf.append( "( ?, ?, ?, ?, ?, ? )" );
}
if ( DEBUG )
System.err.println
( "PersonJDBC.commit: isInDatabase = '"
+ msg.isInDatabase() + "' Cmd = '"
+ buf.toString() + "'" );
PreparedStatement pS =
this.handler.createPreparedStatement
( buf.toString() );
int pidx = 1;
if ( ! msg.isInDatabase() )
{
pS.setInt( pidx++, msg.getId() );
}
pS.setString( pidx++, dateStr.toString() );
pS.setString( pidx++, msg.getMessageID() );
pS.setString( pidx++, msg.getFrom() );
pS.setString( pidx++, msg.getTo() );
this.setNullString
( pS, pidx++, msg.getSubject(), java.sql.Types.CHAR );
int rows = pS.executeUpdate();
pS.close();
if ( DEBUG )
System.err.println
( "EmailJDBC.commit: rows = '" + rows + "'" );
}
catch ( SQLException ex )
{
if ( DEBUG )
ex.printStackTrace( System.err );
throw new DBIException
( ex, "committing email '" + msg.getId() + "'" );
}
}
public EmailContent
getEmailContent( int id )
throws DBIException
{
EmailContent result = null;
String queryStr = null;
try {
queryStr =
"SELECT" +
" content " +
"FROM emailcontent " +
"WHERE id = '" + id + "'";
Statement stmt = this.handler.createStatement();
ResultSet rs = stmt.executeQuery( queryStr );
if ( rs.next() )
{
int col = 1;
result = new EmailContent( id );
result.setModified( false );
result.setInDatabase( true );
byte[] content = rs.getBytes( col++ );
result.setContent( content );
}
rs.close();
stmt.close();
}
catch ( SQLException ex )
{
if ( DEBUG )
ex.printStackTrace( System.err );
throw new DBIException
( ex, "statement '" + queryStr + "'" );
}
return result;
}
public void
commitEmailContent( EmailContent cont )
throws DBIException
{
try {
StringBuffer buf = new StringBuffer();
if ( cont.isInDatabase() )
{
buf.append( "UPDATE emailcontent " );
buf.append( "SET " );
buf.append( " emailcontent.size = ?," );
buf.append( " emailcontent.content = ?," );
buf.append( "WHERE id = '" + cont.getId() + "' " );
}
else
{
buf.append( "INSERT INTO emailcontent " );
buf.append( "( " );
buf.append( " emailcontent.id," );
buf.append( " emailcontent.size," );
buf.append( " emailcontent.content " );
buf.append( ") " );
buf.append( "VALUES " );
buf.append( "( ?, ?, ? )" );
}
if ( DEBUG )
System.err.println
( "PersonJDBC.commit: isInDatabase = '"
+ cont.isInDatabase() + "' Cmd = '"
+ buf.toString() + "'" );
PreparedStatement pS =
this.handler.createPreparedStatement
( buf.toString() );
int pidx = 1;
if ( ! cont.isInDatabase() )
{
pS.setInt( pidx++, cont.getId() );
}
pS.setInt( pidx++, cont.getContent().length );
pS.setBytes( pidx++, cont.getContent() );
int rows = pS.executeUpdate();
pS.close();
if ( DEBUG )
System.err.println
( "EmailJDBC.commitContent: rows = '" + rows + "'" );
}
catch ( SQLException ex )
{
if ( DEBUG )
ex.printStackTrace( System.err );
throw new DBIException
( ex, "committing email '" + cont.getId() + "'" );
}
}
}