001: /*
002: JSPWiki - a JSP-based WikiWiki clone.
003:
004: Copyright (C) 2004-2005 Xan Gregg (xan.gregg@forthgo.com)
005:
006: This program is free software; you can redistribute it and/or modify
007: it under the terms of the GNU Lesser General Public License as published by
008: the Free Software Foundation; either version 2.1 of the License, or
009: (at your option) any later version.
010:
011: This program is distributed in the hope that it will be useful,
012: but WITHOUT ANY WARRANTY; without even the implied warranty of
013: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
014: GNU Lesser General Public License for more details.
015:
016: You should have received a copy of the GNU Lesser General Public License
017: along with this program; if not, write to the Free Software
018: Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
019: */
020: package com.forthgo.jspwiki.jdbcprovider;
021:
022: import com.ecyrd.jspwiki.*;
023: import com.ecyrd.jspwiki.attachment.Attachment;
024: import com.ecyrd.jspwiki.attachment.AttachmentManager;
025: import com.ecyrd.jspwiki.providers.ProviderException;
026: import com.ecyrd.jspwiki.providers.WikiAttachmentProvider;
027: import com.ecyrd.jspwiki.util.ClassUtil;
028: import org.apache.log4j.Category;
029:
030: import java.io.*;
031: import java.sql.*;
032: import java.util.*;
033: import java.util.Date;
034:
035: /*
036: * History:
037: * 2005-09-28 XG Use jspwiki-s as property prefix for security.
038: * 2005-09-07 XG Always use java.util.Date for LastModifield field to friendlier comparisons.
039: */
040:
041: /**
042: * Provides a database-based repository for Wiki attachments.
043: * MySQL commands to create the tables are provided in the code comments.
044: * <p/>
045: * Based on Thierry Lach's DatabaseProvider, which supported Wiki pages
046: * but not attachments.
047: *
048: * @author Thierry Lach
049: * @author Xan Gregg
050: * @see JDBCPageProvider
051: */
052: public class JDBCAttachmentProvider extends JDBCBaseProvider implements
053: WikiAttachmentProvider {
054:
055: /* MySQLtable creation commands:
056: CREATE TABLE WIKI_ATT
057: (
058: ATT_PAGENAME VARCHAR (100) NOT NULL,
059: ATT_FILENAME VARCHAR (100) NOT NULL,
060: ATT_VERSION INTEGER NOT NULL,
061: ATT_MODIFIED DATETIME,
062: ATT_MODIFIED_BY VARCHAR (50),
063: ATT_DATA MEDIUMBLOB,
064: PRIMARY KEY (ATT_PAGENAME, ATT_FILENAME, ATT_VERSION),
065: UNIQUE (ATT_PAGENAME, ATT_FILENAME, ATT_VERSION),
066: INDEX ATT_MODIFIED_IX (ATT_MODIFIED)
067: );
068: */
069:
070: private static final String ATT_TABLE_NAME = "WIKI_ATT";
071:
072: protected static final Category log = Category
073: .getInstance(JDBCAttachmentProvider.class);
074:
075: public String getProviderInfo() {
076: return "JDBC/MySQL attachment provider";
077: }
078:
079: public void initialize(WikiEngine engine, Properties properties)
080: throws NoRequiredPropertyException, IOException {
081: debug("Initializing JDBCAttachmentProvider");
082: super .initialize(engine, properties);
083: try {
084: checkTable(JDBCAttachmentProvider.ATT_TABLE_NAME);
085: } catch (SQLException e) {
086: throw new IOException("SQL Exception: " + e.getMessage());
087: }
088: int n = getAttachmentCount();
089: String migrationPath = properties.getProperty(getPropertyBase()
090: + "migrateFrom");
091: if (n == 0 && migrationPath != null)
092: migratePages(engine, migrationPath);
093: }
094:
095: protected String getPropertyBase() {
096: return "jspwiki-s.JDBCAttachmentProvider."; // -s prevents display as a wikii variable for security
097: }
098:
099: public Category getLog() {
100: return log;
101: }
102:
103: public String getCheckAliveTableName() {
104: return ATT_TABLE_NAME;
105: }
106:
107: public int getAttachmentCount() {
108: int count = 0;
109: Connection connection = null;
110: try {
111: connection = getConnection();
112: String sql = "SELECT COUNT(*) FROM " + ATT_TABLE_NAME;
113: Statement stmt = connection.createStatement();
114: ResultSet rs = stmt.executeQuery(sql);
115: rs.next();
116: count = rs.getInt(1);
117: rs.close();
118: stmt.close();
119: } catch (SQLException se) {
120: error("unable to get attachment count ", se);
121: } finally {
122: releaseConnection(connection);
123: }
124: return count;
125:
126: }
127:
128: // apparently version number and size should not be relied upon at this point
129: public void putAttachmentData(Attachment att, InputStream dataStream)
130: throws ProviderException, IOException {
131: ByteArrayOutputStream baos = new ByteArrayOutputStream();
132: FileUtil.copyContents(dataStream, baos);
133: byte data[] = baos.toByteArray();
134: int version = findLatestVersion(att) + 1;
135: Connection connection = null;
136: try {
137: connection = getConnection();
138: String sql = "INSERT INTO " + ATT_TABLE_NAME
139: + " (ATT_PAGENAME, ATT_FILENAME, ATT_VERSION,"
140: + " ATT_MODIFIED, ATT_MODIFIED_BY, ATT_DATA)"
141: + " VALUES (?, ?, ?, ?, ?, ?)";
142: PreparedStatement psPage = connection.prepareStatement(sql);
143: psPage.setString(1, att.getParentName());
144: psPage.setString(2, att.getFileName());
145: psPage.setInt(3, version);
146: Timestamp d;
147: if (att.getLastModified() != null) {
148: d = new Timestamp(att.getLastModified().getTime());
149: } else {
150: d = new Timestamp(System.currentTimeMillis());
151: }
152: psPage.setTimestamp(4, d);
153: psPage.setString(5, att.getAuthor());
154: psPage.setBytes(6, data);
155: psPage.execute();
156: psPage.close();
157: } catch (SQLException se) {
158: error("Saving attachment failed " + att, se);
159: throw new ProviderException(se.getMessage());
160: } finally {
161: releaseConnection(connection);
162: }
163: }
164:
165: public InputStream getAttachmentData(Attachment att)
166: throws ProviderException, IOException {
167: InputStream result = null;
168: Connection connection = null;
169: int version = att.getVersion();
170: if (version == WikiProvider.LATEST_VERSION) {
171: version = findLatestVersion(att);
172: }
173: try {
174: connection = getConnection();
175: String sql = "SELECT ATT_DATA FROM " + ATT_TABLE_NAME
176: + " WHERE ATT_PAGENAME = ?"
177: + " AND ATT_FILENAME = ?" + " AND ATT_VERSION = ?";
178: PreparedStatement ps = connection.prepareStatement(sql);
179: ps.setString(1, att.getParentName());
180: ps.setString(2, att.getFileName());
181: ps.setInt(3, version);
182: ResultSet rs = ps.executeQuery();
183:
184: if (rs.next()) {
185: byte[] bytes = rs.getBytes("ATT_DATA");
186: result = new ByteArrayInputStream(bytes);
187: //result = rs.getBinaryStream("VERSION_TEXT");
188: } else {
189: error("No attachments to read; '" + att + "'",
190: new SQLException("empty attachment set"));
191: }
192: rs.close();
193: ps.close();
194:
195: } catch (SQLException se) {
196: error("Unable to read attachment '" + att + "'", se);
197: } finally {
198: releaseConnection(connection);
199: }
200: return result;
201: }
202:
203: // latest versions only
204: public Collection listAttachments(WikiPage page)
205: throws ProviderException {
206: Collection result = new ArrayList();
207: Connection connection = null;
208: try {
209: connection = getConnection();
210: String sql = "SELECT LENGTH(ATT_DATA)," + " ATT_FILENAME, "
211: + " ATT_MODIFIED, ATT_MODIFIED_BY, ATT_VERSION"
212: + " FROM " + ATT_TABLE_NAME
213: + " WHERE ATT_PAGENAME = ?"
214: + " ORDER BY ATT_FILENAME ASC, ATT_VERSION DESC"; // so latest version is first
215: //" GROUP BY ATT_FILENAME" +
216: //" ORDER BY ATT_VERSION DESC"; // so latest version is first
217: PreparedStatement ps = connection.prepareStatement(sql);
218: ps.setString(1, page.getName());
219: ResultSet rs = ps.executeQuery();
220:
221: String previousFileName = "";
222: while (rs.next()) {
223: int size = rs.getInt(1);
224: String fileName = rs.getString("ATT_FILENAME");
225: if (fileName.equals(previousFileName))
226: continue; // only add latest version
227: previousFileName = fileName;
228: Attachment att = new Attachment(page.getName(),
229: fileName);
230: att.setSize(size);
231: // use Java Date for friendlier comparisons with other dates
232: att.setLastModified(new java.util.Date(rs.getTimestamp(
233: "ATT_MODIFIED").getTime()));
234: att.setAuthor(rs.getString("ATT_MODIFIED_BY"));
235: att.setVersion(rs.getInt("ATT_VERSION"));
236: result.add(att);
237: }
238: rs.close();
239: ps.close();
240:
241: } catch (SQLException se) {
242: error("Unable to list attachments", se);
243: } finally {
244: releaseConnection(connection);
245: }
246: return result;
247: }
248:
249: public Collection findAttachments(QueryItem[] query) {
250: return new ArrayList(); // fixme
251: }
252:
253: public List listAllChanged(Date timestamp) throws ProviderException {
254: List changedList = new ArrayList();
255:
256: Connection connection = null;
257: try {
258: connection = getConnection();
259: String sql = "SELECT ATT_PAGENAME, ATT_FILENAME,"
260: + " LENGTH(ATT_DATA),"
261: + " ATT_MODIFIED, ATT_MODIFIED_BY, ATT_VERSION"
262: + " FROM " + ATT_TABLE_NAME
263: + " WHERE ATT_MODIFIED > ?"
264: + " ORDER BY ATT_MODIFIED DESC";
265: PreparedStatement ps = connection.prepareStatement(sql);
266: ps.setTimestamp(1, new Timestamp(timestamp.getTime()));
267: ResultSet rs = ps.executeQuery();
268: while (rs.next()) {
269: Attachment att = new Attachment(rs
270: .getString("ATT_PAGENAME"), rs
271: .getString("ATT_FILENAME"));
272: att.setSize(rs.getInt(3));
273: // use Java Date for friendlier comparisons with other dates
274: att.setLastModified(new java.util.Date(rs.getTimestamp(
275: "ATT_MODIFIED").getTime()));
276: att.setAuthor(rs.getString("ATT_MODIFIED_BY"));
277: att.setVersion(rs.getInt("ATT_VERSION"));
278: changedList.add(att);
279: }
280: rs.close();
281: ps.close();
282: } catch (SQLException se) {
283: error("Error getting changed list, since " + timestamp, se);
284: } finally {
285: releaseConnection(connection);
286: }
287:
288: return changedList;
289: }
290:
291: public Attachment getAttachmentInfo(WikiPage page, String name,
292: int version) throws ProviderException {
293: Connection connection = null;
294: try {
295: connection = getConnection();
296: String sql = "SELECT LENGTH(ATT_DATA),"
297: + " ATT_MODIFIED, ATT_MODIFIED_BY, ATT_VERSION"
298: + " FROM " + ATT_TABLE_NAME
299: + " WHERE ATT_PAGENAME = ?"
300: + " AND ATT_FILENAME = ?"
301: + " AND ATT_VERSION = ?"
302: + " ORDER BY ATT_FILENAME ASC, ATT_VERSION DESC"; // so latest version is first
303: //" GROUP BY ATT_FILENAME" +
304: //" ORDER BY ATT_VERSION DESC"; // so latest version is first
305: PreparedStatement ps = connection.prepareStatement(sql);
306: ps.setString(1, page.getName());
307: ps.setString(2, name);
308: ps.setInt(3, version);
309: ResultSet rs = ps.executeQuery();
310:
311: Attachment att = null;
312: if (rs.next()) {
313: att = new Attachment(page.getName(), name);
314: att.setSize(rs.getInt(1));
315: // use Java Date for friendlier comparisons with other dates
316: att.setLastModified(new java.util.Date(rs.getTimestamp(
317: "ATT_MODIFIED").getTime()));
318: att.setAuthor(rs.getString("ATT_MODIFIED_BY"));
319: att.setVersion(rs.getInt("ATT_VERSION"));
320: } else {
321: debug("No attachment info for " + page + "/" + name
322: + ":" + version);
323: }
324: rs.close();
325: ps.close();
326: return att;
327: } catch (SQLException se) {
328: error("Unable to get attachment info for " + page + "/"
329: + name + ":" + version, se);
330: return null;
331: } finally {
332: releaseConnection(connection);
333: }
334: }
335:
336: /**
337: * Goes through the repository and decides which version is
338: * the newest one in that directory.
339: *
340: * @return Latest version number in the repository, or 0, if
341: * there is no page in the repository.
342: */
343: private int findLatestVersion(Attachment att) {
344: int version = 0;
345: Connection connection = null;
346: try {
347: connection = getConnection();
348: String sql = "SELECT ATT_VERSION" + " FROM "
349: + ATT_TABLE_NAME + " WHERE ATT_PAGENAME = ?"
350: + " AND ATT_FILENAME = ?"
351: + " ORDER BY ATT_VERSION DESC"; // so latest version is first
352: // MYSQL " LIMIT 1"; // and only take the first
353: PreparedStatement ps = connection.prepareStatement(sql);
354: ps.setString(1, att.getParentName());
355: ps.setString(2, att.getFileName());
356: ResultSet rs = ps.executeQuery();
357:
358: if (rs.next())
359: version = rs.getInt("ATT_VERSION");
360: rs.close();
361: ps.close();
362:
363: } catch (SQLException se) {
364: error("Error trying to find latest attachment: " + att, se);
365: } finally {
366: releaseConnection(connection);
367: }
368: return version;
369: }
370:
371: public List getVersionHistory(Attachment att) {
372: List list = new ArrayList();
373: Connection connection = null;
374: try {
375: connection = getConnection();
376: String sql = "SELECT LENGTH(ATT_DATA),"
377: + " ATT_MODIFIED, ATT_MODIFIED_BY, ATT_VERSION"
378: + " FROM " + ATT_TABLE_NAME
379: + " WHERE ATT_PAGENAME = ?"
380: + " AND ATT_FILENAME = ?"
381: + " ORDER BY ATT_VERSION DESC"; // so latest version is first
382: PreparedStatement ps = connection.prepareStatement(sql);
383: ps.setString(1, att.getParentName());
384: ps.setString(2, att.getFileName());
385: ResultSet rs = ps.executeQuery();
386:
387: while (rs.next()) {
388: Attachment vAtt = new Attachment(att.getParentName(),
389: att.getFileName());
390: vAtt.setSize(rs.getInt(1));
391: // use Java Date for friendlier comparisons with other dates
392: vAtt.setLastModified(new java.util.Date(rs
393: .getTimestamp("ATT_MODIFIED").getTime()));
394: vAtt.setAuthor(rs.getString("ATT_MODIFIED_BY"));
395: vAtt.setVersion(rs.getInt("ATT_VERSION"));
396: list.add(vAtt);
397: }
398: rs.close();
399: ps.close();
400:
401: } catch (SQLException se) {
402: error("Unable to list attachment version history for "
403: + att, se);
404: } finally {
405: releaseConnection(connection);
406: }
407: return list;
408: }
409:
410: public void deleteVersion(Attachment att) throws ProviderException {
411: Connection connection = null;
412: try {
413: connection = getConnection();
414: String sql = "DELETE FROM " + ATT_TABLE_NAME
415: + " WHERE ATT_PAGENAME = ?"
416: + " AND ATT_FILENAME = ?"
417: + " AND ATT_VERSION = ?";
418: PreparedStatement ps = connection.prepareStatement(sql);
419: ps.setString(1, att.getParentName());
420: ps.setString(2, att.getFileName());
421: ps.setInt(3, att.getVersion());
422: ps.execute();
423: ps.close();
424: } catch (SQLException se) {
425: error("Delete attachment version failed " + att, se);
426: } finally {
427: releaseConnection(connection);
428: }
429: }
430:
431: public void deleteAttachment(Attachment att)
432: throws ProviderException {
433: Connection connection = null;
434: try {
435: connection = getConnection();
436: String sql = "DELETE FROM " + ATT_TABLE_NAME
437: + " WHERE ATT_PAGENAME = ?"
438: + " AND ATT_FILENAME = ?";
439: PreparedStatement ps = connection.prepareStatement(sql);
440: ps.setString(1, att.getParentName());
441: ps.setString(2, att.getFileName());
442: ps.execute();
443: ps.close();
444: } catch (SQLException se) {
445: error("Delete attachment failed " + att, se);
446: } finally {
447: releaseConnection(connection);
448: }
449: }
450:
451: /**
452: * Copies pages from one provider to this provider. The source,
453: * "import" provider is specified by the properties file at
454: * the given path.
455: */
456: private void migratePages(WikiEngine engine, String path)
457: throws IOException {
458: Properties importProps = new Properties();
459: importProps.load(new FileInputStream(path));
460: String classname = importProps
461: .getProperty(AttachmentManager.PROP_PROVIDER);
462:
463: WikiAttachmentProvider importProvider;
464: try {
465: Class providerclass = ClassUtil.findClass(
466: "com.ecyrd.jspwiki.providers", classname);
467: importProvider = (WikiAttachmentProvider) providerclass
468: .newInstance();
469: } catch (Exception e) {
470: log.error(
471: "Unable to locate/instantiate import provider class "
472: + classname, e);
473: return;
474: }
475: try {
476: importProvider.initialize(engine, importProps);
477:
478: List attachments = importProvider
479: .listAllChanged(new Date(0));
480: for (Iterator i = attachments.iterator(); i.hasNext();) {
481: Attachment att = (Attachment) i.next();
482: InputStream data = importProvider
483: .getAttachmentData(att);
484: putAttachmentData(att, data);
485: }
486: } catch (ProviderException e) {
487: throw new IOException(e.getMessage());
488: } catch (NoRequiredPropertyException e) {
489: throw new IOException(e.getMessage());
490: }
491: }
492:
493: }
|