Source code

001package votorola.a.diff.harvest.cache;
002
003import java.security.NoSuchAlgorithmException;
004import java.sql.PreparedStatement;
005import java.sql.ResultSet;
006import java.sql.SQLException;
007import java.util.logging.Logger;
008
009import javax.mail.internet.AddressException;
010
011import votorola.a.diff.harvest.Message;
012import votorola.a.voter.IDPair;
013import votorola.g.lang.ThreadRestricted;
014import votorola.g.lang.ThreadSafe;
015import votorola.g.lang.Warning;
016import votorola.g.logging.LoggerX;
017import votorola.g.sql.Database;
018
019/**
020 * The relational store for diff messages. This class automatically creates the
021 * necessary table if it does not exist and basically hides away the database
022 * layer. Note: It throws errors on double insertions.
023 */
024@ThreadSafe
025final public class DiffMessageTable {
026
027    /**
028     * This is only exposed as a quick hint for somebody trying to understand
029     * the database structure. This value might change.
030     */
031    @Warning("non-Api")
032    public static final String SCHEMA_NAME = "harvest";
033
034    /**
035     * This is only exposed as a quick hint for somebody trying to understand
036     * the database structure. This value might change.
037     */
038    @Warning("non-Api")
039    public static final String TABLE_NAME = "diff_messages";
040
041    private static final String TABLE = "\"" + SCHEMA_NAME + "\".\""
042            + TABLE_NAME + "\"";
043
044    /**
045     * Maximum rows in the result. This should match the maximum client fetch
046     * length.
047     */
048    @Warning("non-Api")
049    public static final int MAX_RESULTS = 200;
050
051    private final static Logger LOGGER = LoggerX.i(DiffMessageTable.class);
052
053    /**
054     * Main database object.
055     */
056    private final transient Database database;
057
058    /**
059     * Constructs a Table.
060     * 
061     * @param database
062     *            Database to connect to.
063     * @throws SQLException
064     * @throws NoSuchAlgorithmException
065     */
066    DiffMessageTable(final Database database) throws SQLException {
067        this.database = database;
068
069        synchronized (database) {
070            database.ensureSchema(SCHEMA_NAME);
071        }
072
073        statementKeyBase = getClass().getName() + ":" + SCHEMA_NAME + "/"
074                + TABLE_NAME + ".";
075    }
076
077    /**
078     * Statement key base for all queries.
079     */
080    private final transient String statementKeyBase;
081
082    /**
083     * Creates this table in the database.
084     * 
085     * @throws SQLException
086     */
087    void create() throws SQLException {
088        final String key = statementKeyBase + "create";
089        synchronized (database) {
090            PreparedStatement prepStatem = database.statementCache().get(key);
091            if (prepStatem == null) {
092                prepStatem = database
093                        .connection()
094                        .prepareStatement(
095                                "CREATE TABLE "
096                                        + TABLE
097                                        + " (id serial primary key"
098                                        + ", author varchar NOT NULL"
099                                        + ", addressee varchar NOT NULL"
100                                        + ", pollname varchar NOT NULL"
101                                        + ", content varchar NOT NULL"
102                                        + ", a integer NOT NULL"
103                                        + ", ar integer NOT NULL"
104                                        + ", b integer NOT NULL"
105                                        + ", br integer NOT NULL"
106                                        + ", selectand varchar NOT NULL"
107                                        + ", archive_url varchar NOT NULL"
108                                        + ", path varchar NOT NULL"
109                                        + ", sent_ts timestamp with time zone NOT NULL"
110                                        + ", UNIQUE(author,addressee,archive_url,path,sent_ts)"
111                                        + " )");
112
113                database.statementCache().put(key, prepStatem);
114            }
115            prepStatem.execute();
116        }
117    }
118
119    /**
120     * Returns the database in which this table is stored.
121     * 
122     * @return This objects database.
123     */
124    Database getDatabase() {
125        return database;
126    }
127
128    /**
129     * Drops this table from the database.
130     * 
131     * @throws SQLException
132     */
133    void drop() throws SQLException {
134        final String key = statementKeyBase + "drop";
135        synchronized (database) {
136            PreparedStatement prepStatem = database.statementCache().get(key);
137            if (prepStatem == null) {
138                prepStatem = database.connection().prepareStatement(
139                        "DROP TABLE " + TABLE);
140                database.statementCache().put(key, prepStatem);
141            }
142            prepStatem.execute();
143        }
144    }
145
146    /**
147     * Returns true if this table exists in the database; false otherwise.
148     * 
149     * @return whether database already exists or not.
150     * @throws SQLException
151     */
152    public boolean exists() throws SQLException {
153        final String key = statementKeyBase + "exists";
154        synchronized (database) {
155            PreparedStatement prepStatem = database.statementCache().get(key);
156            if (prepStatem == null) {
157                prepStatem = database.connection().prepareStatement(
158                        "SELECT * FROM " + TABLE);
159                prepStatem.setMaxRows(1);
160                database.statementCache().put(key, prepStatem);
161            }
162            try {
163                prepStatem.execute();
164            } catch (SQLException x) {
165                final String sqlState = x.getSQLState();
166                if ("3F000".equals(sqlState)) {
167                    return false; // 3F000 = [missing schema]
168                }
169
170                if ("42P01".equals(sqlState)) {
171                    return false; // 42P01 = UNDEFINED TABLE
172                }
173
174                throw x;
175            }
176        }
177        return true;
178    }
179
180    /**
181     * Query the table for the parameters. Order is: id, author, addressee,
182     * pollname, content, archive_url, path, sent_ts, a, ar, b, br, selectand
183     * 
184     * 
185     * @param poll
186     *            poll-name
187     * @param users
188     *            mailish-username of one or two users
189     * @param id
190     *            only fetch posts newer than id
191     * @return rs ResultSet with newest first.
192     * @throws SQLException
193     * @throws AddressException
194     */
195    @ThreadRestricted("rs holds lock on database")
196    public ResultSet get(final String poll, final String[] users, final int id)
197            throws SQLException, AddressException {
198
199        final StringBuilder whereScope = new StringBuilder();
200        String conj = " WHERE"; // conjunction
201        if (poll != null && !poll.isEmpty()) {
202            whereScope.append(conj).append(" pollname = ?");
203            conj = " AND";
204        }
205        if (users != null && users.length != 0) {
206            for (final String user : users) {
207                if (user.isEmpty()) {
208                    continue;
209                }
210                whereScope.append(conj)
211                        .append(" (addressee = ? OR author = ?)");
212                conj = " AND";
213            }
214        }
215        if (id > 0) {
216            whereScope.append(conj).append(" id > ?");
217            conj = " AND";
218        }
219
220        final String key = statementKeyBase + "getDiffMsgs"
221                + whereScope.toString();
222
223        PreparedStatement prepStatem = database.statementCache().get(key);
224
225        final String query = "SELECT id, author, addressee, pollname"
226                + ", content, archive_url, path, sent_ts" /* message */
227                + ", a, ar, b, br, selectand" /* difference */
228                + " FROM " + TABLE + whereScope.toString()
229                + " ORDER BY sent_ts DESC LIMIT " + MAX_RESULTS;
230        if (prepStatem == null) {
231            prepStatem = database.connection().prepareStatement(query);
232            database.statementCache().put(key, prepStatem);
233        }
234
235        int index = 1;
236        if (poll != null && !poll.isEmpty()) {
237            prepStatem.setString(index++, poll);
238        }
239        if (users != null && users.length != 0) {
240            for (final String user : users) {
241                if (user.isEmpty()) {
242                    continue;
243                }
244                final String email = IDPair.toInternetAddress(user)
245                        .getAddress();
246                prepStatem.setString(index++, email);
247                prepStatem.setString(index++, email);
248            }
249        }
250        if (id != 0) {
251            prepStatem.setInt(index++, id);
252        }
253
254        return prepStatem.executeQuery();
255    }
256
257    /**
258     * Stores a message. This throws an exception on double insert attempts on
259     * the primary key, which consists author,addresse,url and sentDate
260     * 
261     * @throws SQLException
262     */
263    void put(final AuthDiffMessage authMsg) throws SQLException {
264
265        synchronized (database) {
266            final String insertKey = statementKeyBase + "putDiffMsgI";
267            PreparedStatement s = database.statementCache().get(insertKey);
268            if (s == null) {
269                s = database
270                        .connection()
271                        .prepareStatement(
272                                "INSERT INTO "
273                                        + TABLE
274                                        + " (author"
275                                        + ", addressee, archive_url, path, pollname"
276                                        + ", content, a, ar"
277                                        + ", b, br, sent_ts, selectand"
278                                        + ")"
279                                        + " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
280                database.statementCache().put(insertKey, s);
281            }
282            Message msg = authMsg.diffMessage().message();
283
284            s.setString(1, authMsg.author());
285            s.setString(2, authMsg.addressee());
286            s.setString(3, msg.mc().archiveUrl());
287            s.setString(4, msg.path());
288            s.setString(5, authMsg.diffMessage().draftPair().aCore()
289                    .pollName());
290            s.setString(6, msg.content());
291            {
292                // CWFIX obsolete form, e.g. by storing p.key() instead
293                final votorola.a.diff.DiffKeyParse parse =
294                  authMsg.diffMessage().draftPair().diffKeyParse();
295                final java.util.List<Integer> aPath = parse.aPath();
296                final java.util.List<Integer> bPath = parse.bPath();
297                final int aN = aPath.size();
298                final int bN = bPath.size();
299                if(aN > 2 || bN > 2) {
300                    throw new votorola.g.VotorolaRuntimeException(
301                      "new draft revision path too long for old Harvester" );
302                }
303
304                s.setInt(7, aPath.get(0));
305                s.setInt(8, aN > 1? aPath.get(1): -1);
306                s.setInt(9, bPath.get(0));
307                s.setInt(10, bN > 1? bPath.get(1): -1);
308            }
309            s.setTimestamp(11, new java.sql.Timestamp(msg.mc().sentDate()
310                    .getTime()));
311            s.setString(12, authMsg.selectand());
312            s.executeUpdate();
313
314        }
315    }
316
317    /**
318     * Remove all stored messages for an archive selected by its archive url.
319     * 
320     * @param archiveUrl
321     * @return number of removed messages
322     * @throws SQLException
323     */
324    public int removeArchive(final String archiveUrl) throws SQLException {
325        synchronized (database) {
326            final String removeKey = statementKeyBase + "remDiffMsgI";
327            PreparedStatement s = database.statementCache().get(removeKey);
328            if (s == null) {
329                s = database.connection().prepareStatement(
330                        "DELETE FROM " + TABLE + " where archive_url=?");
331                database.statementCache().put(removeKey, s);
332            }
333            s.setString(1, archiveUrl);
334
335            return s.executeUpdate();
336        }
337    }
338
339}