001: /*
002: Very Quick Wiki - WikiWikiWeb clone
003: Copyright (C) 2001-2002 Gareth Cronin
004:
005: This program is free software; you can redistribute it and/or modify
006: it under the terms of the latest version of the GNU Lesser General
007: Public License as published by the Free Software Foundation;
008:
009: This program is distributed in the hope that it will be useful,
010: but WITHOUT ANY WARRANTY; without even the implied warranty of
011: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
012: GNU Lesser General Public License for more details.
013:
014: You should have received a copy of the GNU Lesser General Public License
015: along with this program (gpl.txt); if not, write to the Free Software
016: Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
017: */
018: package vqwiki.db;
019:
020: import org.apache.log4j.Logger;
021: import vqwiki.Environment;
022: import vqwiki.Handler;
023: import vqwiki.TopicLock;
024: import vqwiki.VersionManager;
025: import vqwiki.WikiBase;
026: import vqwiki.WikiException;
027: import vqwiki.PseudoTopicHandler;
028: import vqwiki.utils.Utilities;
029:
030: import java.io.BufferedReader;
031: import java.io.InputStream;
032: import java.io.InputStreamReader;
033: import java.sql.Connection;
034: import java.sql.PreparedStatement;
035: import java.sql.ResultSet;
036: import java.sql.SQLException;
037: import java.sql.Statement;
038: import java.util.ArrayList;
039: import java.util.Collection;
040: import java.util.Iterator;
041: import java.util.List;
042: import java.util.Locale;
043: import java.util.ResourceBundle;
044: import java.util.StringTokenizer;
045: import java.util.Vector;
046:
047: public class DatabaseHandler implements Handler {
048:
049: protected final static String STATEMENT_READ = "SELECT contents FROM Topic WHERE name = ? AND virtualwiki = ?";
050: protected final static String STATEMENT_UPDATE = "UPDATE Topic SET contents = ? WHERE name = ? AND virtualwiki = ?";
051: protected final static String STATEMENT_UPDATE_ORACLE1 = "UPDATE Topic SET contents = EMPTY_CLOB() WHERE name = ? AND virtualwiki = ?";
052: protected final static String STATEMENT_UPDATE_ORACLE2 = "SELECT contents FROM Topic WHERE name = ? AND virtualwiki = ? FOR UPDATE";
053: protected final static String STATEMENT_INSERT = "INSERT INTO Topic( name, contents, virtualwiki ) VALUES ( ?, ?, ? )";
054: protected final static String STATEMENT_INSERT_ORACLE = "INSERT INTO Topic( name, contents, virtualwiki ) VALUES ( ?, EMPTY_CLOB(), ? )";
055: protected final static String STATEMENT_EXISTS = "SELECT COUNT(*) FROM Topic WHERE name = ? AND virtualwiki = ?";
056: protected final static String STATEMENT_SET_LOCK = "INSERT INTO TopicLock( topic, sessionkey, lockat, virtualwiki ) VALUES( ?, ?, ?, ? )";
057: protected final static String STATEMENT_CHECK_LOCK = "SELECT lockat, sessionkey FROM TopicLock WHERE topic = ? AND virtualwiki = ?";
058: protected final static String STATEMENT_CHECK_SPECIFIC_LOCK = "SELECT lockat, sessionkey FROM TopicLock WHERE topic = ? AND virtualwiki = ? AND sessionkey = ?";
059: protected final static String STATEMENT_REMOVE_LOCK = "DELETE FROM TopicLock WHERE topic = ? AND virtualwiki = ?";
060: protected final static String STATEMENT_REMOVE_ANY_LOCK = "DELETE FROM TopicLock WHERE topic = ? AND virtualwiki = ?";
061: protected final static String STATEMENT_READONLY_INSERT = "INSERT INTO TopicReadOnly( topic, virtualwiki ) VALUES ( ?, ? )";
062: protected final static String STATEMENT_READONLY_DELETE = "DELETE FROM TopicReadOnly WHERE topic = ? AND virtualwiki = ?";
063: protected final static String STATEMENT_READONLY_ALL = "SELECT topic FROM TopicReadOnly";
064: protected final static String STATEMENT_READONLY_FIND = "SELECT COUNT(*) FROM TopicReadOnly WHERE topic = ? AND virtualwiki = ?";
065: protected static final String STATEMENT_GET_ALL_VIRTUAL_WIKIS = "SELECT name FROM VirtualWiki";
066: protected static final String STATEMENT_GET_TEMPLATE_NAMES = "SELECT name FROM WikiTemplate WHERE virtualwiki = ?";
067: protected static final String STATEMENT_GET_TEMPLATE = "SELECT contents FROM WikiTemplate WHERE virtualwiki = ? AND name = ?";
068: protected static final String STATEMENT_ADD_VIRTUAL_WIKI = "INSERT INTO VirtualWiki VALUES(?)";
069: protected static final String STATEMENT_PURGE_DELETES = "DELETE FROM Topic WHERE virtualwiki = ? AND (contents = 'delete\n' or contents = '\n' or contents = '')";
070: protected static final String STATEMENT_PURGE_TOPIC = "DELETE FROM Topic WHERE virtualwiki = ? AND name = ?";
071: protected static final String STATEMENT_TOPICS_TO_PURGE = "SELECT name FROM Topic WHERE virtualwiki = ? AND (contents = 'delete\n' or contents = '\n' or contents = '')";
072: protected static final String STATEMENT_ALL_TOPICS = "SELECT name, contents FROM Topic WHERE virtualwiki = ?";
073: protected static final String STATEMENT_ALL_OLDER_TOPICS = "SELECT name, contents FROM Topic WHERE virtualwiki = ? AND versionat < ?";
074: protected final static String STATEMENT_PURGE_VERSIONS = "DELETE FROM TopicVersion WHERE versionat < ? AND virtualwiki = ?";
075: protected final static String STATEMENT_ADD_TEMPLATE = "INSERT INTO WikiTemplate( virtualwiki, name, contents ) VALUES( ?, ?, ? )";
076: protected final static String STATEMENT_ADD_TEMPLATE_ORACLE = "INSERT INTO WikiTemplate( virtualwiki, name, contents ) VALUES( ?, ?, EMPTY_CLOB() )";
077: protected final static String STATEMENT_TEMPLATE_EXISTS = "SELECT COUNT(*) FROM WikiTemplate WHERE virtualwiki = ? AND name = ?";
078: protected final static String STATEMENT_UPDATE_TEMPLATE = "UPDATE WikiTemplate SET contents = ? WHERE virtualwiki = ? AND name = ?";
079: protected final static String STATEMENT_UPDATE_TEMPLATE_ORACLE1 = "UPDATE WikiTemplate SET contents = EMPTY_CLOB() WHERE virtualwiki = ? AND name = ?";
080: protected final static String STATEMENT_UPDATE_TEMPLATE_ORACLE2 = "SELECT contents FROM WikiTemplate WHERE virtualwiki = ? AND name = ? FOR UPDATE";
081: protected final static String STATEMENT_GET_LOCK_LIST = "SELECT * FROM TopicLock WHERE virtualwiki = ?";
082:
083: private static final Logger logger = Logger
084: .getLogger(DatabaseHandler.class);
085:
086: /**
087: *
088: */
089: public DatabaseHandler() throws Exception {
090: setDefaults(Locale.ENGLISH);
091: }
092:
093: /**
094: *
095: */
096: public void setDefaults(Locale locale) throws Exception {
097: logger.debug("Setting defaults");
098: // resources for i18n
099: ResourceBundle messages = ResourceBundle.getBundle(
100: "ApplicationResources", locale);
101: //set up tables
102: createTables();
103: Connection conn = null;
104: try {
105: conn = DatabaseConnection.getConnection();
106: PreparedStatement stmt = conn
107: .prepareStatement(STATEMENT_GET_ALL_VIRTUAL_WIKIS);
108: ResultSet rs = stmt.executeQuery();
109: if (!rs.next()) {
110: stmt.close();
111: stmt = conn
112: .prepareStatement(STATEMENT_ADD_VIRTUAL_WIKI);
113: stmt.setString(1, WikiBase.DEFAULT_VWIKI);
114: stmt.execute();
115: }
116: stmt.close();
117: Statement st = conn.createStatement();
118: rs = st.executeQuery(STATEMENT_GET_ALL_VIRTUAL_WIKIS);
119: while (rs.next()) {
120: String vWiki = rs.getString("name");
121: // starting points
122: setupSpecialPage(vWiki, messages
123: .getString("specialpages.startingpoints"));
124: // text formatting rules
125: setupSpecialPage(vWiki, messages
126: .getString("specialpages.textformattingrules"));
127: // leftMenu
128: setupSpecialPage(vWiki, messages
129: .getString("specialpages.leftMenu"));
130: // topArea
131: setupSpecialPage(vWiki, messages
132: .getString("specialpages.topArea"));
133: // bottomArea
134: setupSpecialPage(vWiki, messages
135: .getString("specialpages.bottomArea"));
136: // stylesheet
137: setupSpecialPage(vWiki, messages
138: .getString("specialpages.stylesheet"));
139: // list of topics that only admin is allowed to edit/view by themselves
140: setupSpecialPage(vWiki, messages
141: .getString("specialpages.adminonlytopics"));
142: if (!exists(vWiki, "SetUsername")) {
143: write(vWiki, "", false, "SetUsername");
144: }
145: }
146: rs.close();
147: st.close();
148: } finally {
149: DatabaseConnection.closeConnection(conn);
150: }
151: }
152:
153: /**
154: *
155: */
156: private void setupSpecialPage(String vWiki, String specialPage)
157: throws Exception {
158: if (!exists(vWiki, specialPage)) {
159: logger.debug("Setting up " + specialPage);
160: write(vWiki, WikiBase.readDefaultTopic(specialPage), true,
161: specialPage);
162: }
163: }
164:
165: /**
166: * Run the create tables script
167: * Ignore SQL exceptions as these may just be the result of existing tables getting in the
168: * way of create table calls
169: *
170: * @throws java.lang.Exception
171: */
172: private void createTables() throws Exception {
173: String databaseType = Environment.getInstance()
174: .getDatabaseType();
175: StringBuffer buffer = new StringBuffer();
176: buffer.append("/create_");
177: buffer.append(databaseType);
178: buffer.append(".sql");
179: String resourceName = buffer.toString();
180: InputStream createScriptStream = getClass()
181: .getResourceAsStream(resourceName);
182: if (createScriptStream == null) {
183: logger.error("Can't find create script: " + resourceName);
184: }
185: BufferedReader in = new BufferedReader(new InputStreamReader(
186: createScriptStream));
187: buffer = new StringBuffer();
188: while (true) {
189: String line = in.readLine();
190: if (line == null) {
191: break;
192: }
193: buffer.append(line);
194: }
195: in.close();
196: StringTokenizer tokens = new StringTokenizer(buffer.toString(),
197: ";");
198: Connection conn = null;
199: try {
200: conn = DatabaseConnection.getConnection();
201: Statement st = conn.createStatement();
202: while (tokens.hasMoreTokens()) {
203: String token = tokens.nextToken();
204: try {
205: st.executeUpdate(token);
206: } catch (SQLException e) {
207: logger.warn(e);
208: }
209: }
210: st.close();
211: } finally {
212: DatabaseConnection.closeConnection(conn);
213: }
214: }
215:
216: /**
217: *
218: */
219: public String read(String virtualWiki, String topicName)
220: throws Exception {
221: if (virtualWiki == null || virtualWiki.length() == 0) {
222: virtualWiki = WikiBase.DEFAULT_VWIKI;
223: }
224: String contents = null;
225: Connection conn = null;
226: try {
227: conn = DatabaseConnection.getConnection();
228: PreparedStatement readStatement = conn
229: .prepareStatement(STATEMENT_READ);
230: readStatement.setString(1, topicName);
231: readStatement.setString(2, virtualWiki);
232: ResultSet rs = readStatement.executeQuery();
233: if (!rs.next()) {
234: return "This is a new topic";
235: }
236: if (Environment.getInstance().isOracle()) {
237: contents = OracleClobHelper.getClobValue(rs
238: .getClob("contents"));
239: } else {
240: contents = rs.getString("contents");
241: }
242: rs.close();
243: readStatement.close();
244: } finally {
245: DatabaseConnection.closeConnection(conn);
246: }
247: return (contents);
248: }
249:
250: /**
251: *
252: */
253: public void write(String virtualWiki, String contents,
254: boolean convertTabs, String topicName) throws Exception {
255: if (convertTabs) {
256: contents = Utilities.convertTabs(contents);
257: }
258: Connection conn = null;
259: try {
260: conn = DatabaseConnection.getConnection();
261: if (!exists(virtualWiki, topicName)) {
262: logger.debug("Inserting into topic " + topicName + ", "
263: + contents);
264: PreparedStatement insertStatement;
265: if (Environment.getInstance().isOracle()) {
266: boolean savedAutoCommit = conn.getAutoCommit();
267: conn.setAutoCommit(false);
268: insertStatement = conn
269: .prepareStatement(STATEMENT_INSERT_ORACLE);
270: insertStatement.setString(1, topicName);
271: insertStatement.setString(2, virtualWiki);
272: insertStatement.execute();
273: insertStatement.close();
274: conn.commit();
275: insertStatement = conn.prepareStatement(
276: STATEMENT_UPDATE_ORACLE2,
277: ResultSet.TYPE_SCROLL_INSENSITIVE,
278: ResultSet.CONCUR_UPDATABLE);
279: insertStatement.setString(1, topicName);
280: insertStatement.setString(2, virtualWiki);
281: ResultSet rs = insertStatement.executeQuery();
282: rs.next();
283: OracleClobHelper.setClobValue(rs.getClob(1),
284: contents);
285: rs.close();
286: insertStatement.close();
287: conn.setAutoCommit(savedAutoCommit);
288: } else {
289: insertStatement = conn
290: .prepareStatement(STATEMENT_INSERT);
291: insertStatement.setString(1, topicName);
292: insertStatement.setString(2, contents);
293: insertStatement.setString(3, virtualWiki);
294: insertStatement.execute();
295: insertStatement.close();
296: }
297: } else {
298: logger.debug("Updating topic " + topicName + " to "
299: + contents);
300: PreparedStatement updateStatement;
301: if (Environment.getInstance().isOracle()) {
302: boolean savedAutoCommit = conn.getAutoCommit();
303: conn.setAutoCommit(false);
304: updateStatement = conn
305: .prepareStatement(STATEMENT_UPDATE_ORACLE1);
306: updateStatement.setString(1, topicName);
307: updateStatement.setString(2, virtualWiki);
308: updateStatement.execute();
309: updateStatement.close();
310: conn.commit();
311: updateStatement = conn.prepareStatement(
312: STATEMENT_UPDATE_ORACLE2,
313: ResultSet.TYPE_SCROLL_INSENSITIVE,
314: ResultSet.CONCUR_UPDATABLE);
315: updateStatement.setString(1, topicName);
316: updateStatement.setString(2, virtualWiki);
317: ResultSet rs = updateStatement.executeQuery();
318: rs.next();
319: OracleClobHelper.setClobValue(rs.getClob(1),
320: contents);
321: rs.close();
322: updateStatement.close();
323: conn.commit();
324: conn.setAutoCommit(savedAutoCommit);
325: } else {
326: updateStatement = conn
327: .prepareStatement(STATEMENT_UPDATE);
328: updateStatement.setString(2, topicName);
329: updateStatement.setString(1, contents);
330: updateStatement.setString(3, virtualWiki);
331: updateStatement.execute();
332: updateStatement.close();
333: }
334: }
335: } finally {
336: DatabaseConnection.closeConnection(conn);
337: }
338: if (Environment.getInstance().isVersioningOn()) {
339: // write version
340: DatabaseVersionManager.getInstance().addVersion(
341: virtualWiki, topicName, contents, new DBDate());
342: }
343: }
344:
345: /**
346: *
347: */
348: public boolean exists(String virtualWiki, String topicName)
349: throws Exception {
350: Connection conn = null;
351: boolean result = false;
352: try {
353: conn = DatabaseConnection.getConnection();
354: PreparedStatement existsStatement = conn
355: .prepareStatement(STATEMENT_EXISTS);
356: existsStatement.setString(1, topicName);
357: existsStatement.setString(2, virtualWiki);
358: ResultSet rs = existsStatement.executeQuery();
359: rs.next();
360: int count = rs.getInt(1);
361: result = (count == 0) ? false : true;
362: rs.close();
363: existsStatement.close();
364: } finally {
365: DatabaseConnection.closeConnection(conn);
366: }
367: return result;
368: }
369:
370: /**
371: *
372: */
373: public boolean holdsLock(String virtualWiki, String topicName,
374: String key) throws Exception {
375: Connection conn = null;
376: try {
377: conn = DatabaseConnection.getConnection();
378: PreparedStatement checkLockStatement = conn
379: .prepareStatement(STATEMENT_CHECK_SPECIFIC_LOCK);
380: checkLockStatement.setString(1, topicName);
381: checkLockStatement.setString(2, virtualWiki);
382: checkLockStatement.setString(3, key);
383: ResultSet rs = checkLockStatement.executeQuery();
384: if (!rs.next()) {
385: rs.close();
386: checkLockStatement.close();
387: // Since the topic is not locked, there's no problem in allowing someone to save the topic.
388: // Create a lock and return the outcome.
389: return lockTopic(virtualWiki, topicName, key);
390: }
391: java.util.Date lockedAt = new DBDate(rs
392: .getTimestamp("lockat"));
393: VersionManager versionManager = WikiBase.getInstance()
394: .getVersionManagerInstance();
395: java.util.Date lastRevision = versionManager
396: .lastRevisionDate(virtualWiki, topicName);
397: logger.debug("Checking for lock possession: locked at "
398: + lockedAt + " last changed at " + lastRevision);
399: if (lastRevision != null) {
400: if (lastRevision.after(lockedAt)) {
401: return false;
402: }
403: }
404: rs.close();
405: checkLockStatement.close();
406: } finally {
407: DatabaseConnection.closeConnection(conn);
408: }
409: return true;
410: }
411:
412: /**
413: *
414: */
415: public boolean lockTopic(String virtualWiki, String topicName,
416: String key) throws Exception {
417: Connection conn = null;
418: try {
419: conn = DatabaseConnection.getConnection();
420: PreparedStatement checkLockStatement = conn
421: .prepareStatement(STATEMENT_CHECK_LOCK);
422: checkLockStatement.setString(1, topicName);
423: checkLockStatement.setString(2, virtualWiki);
424: ResultSet rs = checkLockStatement.executeQuery();
425: if (rs.next()) {
426: DBDate date = new DBDate(rs.getTimestamp("lockat"));
427: DBDate now = new DBDate();
428: logger.debug("Already locked at " + date);
429: long fiveMinutesAgo = now.getTime() - 60000
430: * Environment.getInstance().getEditTimeOut();
431: if (date.getTime() < fiveMinutesAgo) {
432: logger.debug("Lock expired");
433: PreparedStatement removeLockStatement = conn
434: .prepareStatement(STATEMENT_REMOVE_LOCK);
435: removeLockStatement.setString(1, topicName);
436: removeLockStatement.setString(2, virtualWiki);
437: removeLockStatement.execute();
438: removeLockStatement.close();
439: } else {
440: String existingKey = rs.getString("sessionkey");
441: rs.close();
442: checkLockStatement.close();
443: // if the key being locked with is the existing lock, then the user still has
444: // the lock, otherwise, it must be locked by someone else
445: boolean sameKey = existingKey.equals(key);
446: logger.debug("Same key: " + sameKey);
447: return sameKey;
448: }
449: }
450: logger.debug("Setting lock");
451: PreparedStatement setLockStatement = conn
452: .prepareStatement(STATEMENT_SET_LOCK);
453: setLockStatement.setString(1, topicName);
454: setLockStatement.setString(2, key);
455: setLockStatement.setTimestamp(3, (new DBDate())
456: .asTimestamp());
457: setLockStatement.setString(4, virtualWiki);
458: setLockStatement.execute();
459: setLockStatement.close();
460: rs.close();
461: checkLockStatement.close();
462: } finally {
463: DatabaseConnection.closeConnection(conn);
464: }
465: return true;
466: }
467:
468: /**
469: *
470: */
471: public void unlockTopic(String virtualWiki, String topicName)
472: throws Exception {
473: Connection conn = null;
474: try {
475: conn = DatabaseConnection.getConnection();
476: PreparedStatement removeAnyLockStatement = conn
477: .prepareStatement(STATEMENT_REMOVE_ANY_LOCK);
478: removeAnyLockStatement.setString(1, topicName);
479: removeAnyLockStatement.setString(2, virtualWiki);
480: removeAnyLockStatement.execute();
481: removeAnyLockStatement.close();
482: } finally {
483: DatabaseConnection.closeConnection(conn);
484: }
485: }
486:
487: /**
488: *
489: */
490: public boolean isTopicReadOnly(String virtualWiki, String topicName)
491: throws Exception {
492: Connection conn = null;
493: try {
494: conn = DatabaseConnection.getConnection();
495: PreparedStatement findReadOnlyStatement = conn
496: .prepareStatement(STATEMENT_READONLY_FIND);
497: findReadOnlyStatement.setString(1, topicName);
498: findReadOnlyStatement.setString(2, virtualWiki);
499: ResultSet rs = findReadOnlyStatement.executeQuery();
500: rs.next();
501: if (rs.getInt(1) > 0) {
502: rs.close();
503: findReadOnlyStatement.close();
504: return true;
505: }
506: rs.close();
507: findReadOnlyStatement.close();
508: } finally {
509: DatabaseConnection.closeConnection(conn);
510: }
511: return false;
512: }
513:
514: /**
515: *
516: */
517: public Collection getReadOnlyTopics(String virtualWiki)
518: throws Exception {
519: Collection all = new ArrayList();
520: Connection conn = null;
521: try {
522: conn = DatabaseConnection.getConnection();
523: PreparedStatement getReadOnlyStatement = conn
524: .prepareStatement(STATEMENT_READONLY_ALL);
525: ResultSet rs = getReadOnlyStatement.executeQuery();
526: while (rs.next()) {
527: all.add(rs.getString("topic"));
528: }
529: rs.close();
530: getReadOnlyStatement.close();
531: } finally {
532: DatabaseConnection.closeConnection(conn);
533: }
534: return all;
535: }
536:
537: /**
538: *
539: */
540: public void addReadOnlyTopic(String virtualWiki, String topicName)
541: throws Exception {
542: Connection conn = null;
543: try {
544: conn = DatabaseConnection.getConnection();
545: PreparedStatement addReadOnlyStatement = conn
546: .prepareStatement(STATEMENT_READONLY_INSERT);
547: addReadOnlyStatement.setString(1, topicName);
548: addReadOnlyStatement.setString(2, virtualWiki);
549: addReadOnlyStatement.execute();
550: addReadOnlyStatement.close();
551: } finally {
552: DatabaseConnection.closeConnection(conn);
553: }
554: }
555:
556: /**
557: *
558: */
559: public void removeReadOnlyTopic(String virtualWiki, String topicName)
560: throws Exception {
561: Connection conn = null;
562: try {
563: conn = DatabaseConnection.getConnection();
564: PreparedStatement deleteReadOnlyStatement = conn
565: .prepareStatement(STATEMENT_READONLY_DELETE);
566: deleteReadOnlyStatement.setString(1, topicName);
567: deleteReadOnlyStatement.setString(2, virtualWiki);
568: deleteReadOnlyStatement.execute();
569: deleteReadOnlyStatement.close();
570: } finally {
571: DatabaseConnection.closeConnection(conn);
572: }
573: }
574:
575: /**
576: *
577: */
578: public void executeSQL(String sql) throws Exception {
579: Connection conn = null;
580: try {
581: conn = DatabaseConnection.getConnection();
582: Statement st = conn.createStatement();
583: st.execute(sql);
584: st.close();
585: } finally {
586: DatabaseConnection.closeConnection(conn);
587: }
588: }
589:
590: /**
591: *
592: */
593: public void initialise(Locale locale) throws Exception {
594: this .setDefaults(locale);
595: }
596:
597: /**
598: *
599: */
600: public Collection getVirtualWikiList() throws Exception {
601: Connection conn = null;
602: Collection all = new ArrayList();
603: try {
604: conn = DatabaseConnection.getConnection();
605: Statement st = conn.createStatement();
606: ResultSet rs = st
607: .executeQuery(STATEMENT_GET_ALL_VIRTUAL_WIKIS);
608: while (rs.next()) {
609: all.add(rs.getString("name"));
610: }
611: rs.close();
612: st.close();
613: } finally {
614: DatabaseConnection.closeConnection(conn);
615: }
616: if (!all.contains(WikiBase.DEFAULT_VWIKI)) {
617: all.add(WikiBase.DEFAULT_VWIKI);
618: }
619: return all;
620: }
621:
622: /**
623: *
624: */
625: public Collection getTemplateNames(String virtualWiki)
626: throws Exception {
627: logger.debug("Returning template names for " + virtualWiki);
628: Collection all = new ArrayList();
629: Connection conn = null;
630: try {
631: conn = DatabaseConnection.getConnection();
632: PreparedStatement stmt = conn
633: .prepareStatement(STATEMENT_GET_TEMPLATE_NAMES);
634: stmt.setString(1, virtualWiki);
635: ResultSet rs = stmt.executeQuery();
636: while (rs.next()) {
637: all.add(rs.getString("name"));
638: }
639: rs.close();
640: stmt.close();
641: } finally {
642: DatabaseConnection.closeConnection(conn);
643: }
644: logger.debug(all.size() + " templates exist");
645: return all;
646: }
647:
648: /**
649: *
650: */
651: public String getTemplate(String virtualWiki, String templateName)
652: throws Exception {
653: Connection conn = null;
654: String contents;
655: try {
656: conn = DatabaseConnection.getConnection();
657: PreparedStatement stmt = conn
658: .prepareStatement(STATEMENT_GET_TEMPLATE);
659: stmt.setString(1, virtualWiki);
660: stmt.setString(2, templateName);
661: ResultSet rs = stmt.executeQuery();
662: if (!rs.next()) {
663: rs.close();
664: stmt.close();
665: throw new WikiException("Template not found: "
666: + templateName);
667: }
668: if (Environment.getInstance().isOracle()) {
669: contents = OracleClobHelper.getClobValue(rs
670: .getClob("contents"));
671: } else {
672: contents = rs.getString("contents");
673: }
674: rs.close();
675: stmt.close();
676: } finally {
677: DatabaseConnection.closeConnection(conn);
678: }
679: return contents;
680: }
681:
682: /**
683: *
684: */
685: public void addVirtualWiki(String virtualWiki) throws Exception {
686: if (getVirtualWikiList().contains(virtualWiki)) {
687: return;
688: }
689: Connection conn = null;
690: try {
691: conn = DatabaseConnection.getConnection();
692: PreparedStatement stmt = conn
693: .prepareStatement(STATEMENT_ADD_VIRTUAL_WIKI);
694: stmt.setString(1, virtualWiki);
695: stmt.execute();
696: stmt.close();
697: } finally {
698: DatabaseConnection.closeConnection(conn);
699: }
700: }
701:
702: /**
703: *
704: */
705: public Collection purgeDeletesOracle(String virtualWiki)
706: throws Exception {
707: PreparedStatement stmt;
708: ResultSet rs;
709: Vector names = new Vector();
710: Connection conn = null;
711: try {
712: conn = DatabaseConnection.getConnection();
713: stmt = conn.prepareStatement(STATEMENT_ALL_TOPICS);
714: stmt.setString(1, virtualWiki);
715: rs = stmt.executeQuery();
716: while (rs.next()) {
717: String contents = OracleClobHelper.getClobValue(rs
718: .getClob(2));
719: if (contents.length() == 0
720: || contents.equals("delete\n")
721: || contents.equals("\n")) {
722: names.add(rs.getString(1));
723: }
724: }
725: rs.close();
726: stmt.close();
727: stmt = conn.prepareStatement(STATEMENT_PURGE_TOPIC);
728: Iterator i = names.iterator();
729: while (i.hasNext()) {
730: String name = (String) i.next();
731: stmt.setString(1, virtualWiki);
732: stmt.setString(2, name);
733: stmt.execute();
734: }
735: stmt.close();
736: } finally {
737: DatabaseConnection.closeConnection(conn);
738: }
739: return names;
740: }
741:
742: /**
743: *
744: */
745: public Collection purgeDeletes(String virtualWiki) throws Exception {
746: if (Environment.getInstance().isOracle()) {
747: return purgeDeletesOracle(virtualWiki);
748: }
749: Collection all = new ArrayList();
750: // get list of stuff to be purged
751: Connection conn = null;
752: try {
753: conn = DatabaseConnection.getConnection();
754: PreparedStatement stmt = conn
755: .prepareStatement(STATEMENT_TOPICS_TO_PURGE);
756: stmt.setString(1, virtualWiki);
757: ResultSet rs = stmt.executeQuery();
758: while (rs.next()) {
759: String topicName = rs.getString("name");
760: if (!PseudoTopicHandler.getInstance().isPseudoTopic(
761: topicName)) {
762: all.add(topicName);
763: }
764: }
765: stmt.close();
766: stmt = conn.prepareStatement(STATEMENT_PURGE_DELETES);
767: stmt.setString(1, virtualWiki);
768: stmt.execute();
769: stmt.close();
770: } finally {
771: DatabaseConnection.closeConnection(conn);
772: }
773: return all;
774: }
775:
776: /**
777: *
778: */
779: public void purgeVersionsOlderThanOracle(String virtualWiki,
780: DBDate date) throws Exception {
781: PreparedStatement stmt;
782: ResultSet rs;
783: Vector names = new Vector();
784: Connection conn = null;
785: try {
786: conn = DatabaseConnection.getConnection();
787: stmt = conn.prepareStatement(STATEMENT_ALL_OLDER_TOPICS);
788: stmt.setString(1, virtualWiki);
789: stmt.setTimestamp(2, date.asTimestamp());
790: rs = stmt.executeQuery();
791: while (rs.next()) {
792: String contents = OracleClobHelper.getClobValue(rs
793: .getClob(2));
794: if (contents.length() == 0
795: || contents.equals("delete\n")
796: || contents.equals("\n")) {
797: names.add(rs.getString(1));
798: }
799: }
800: rs.close();
801: stmt.close();
802: stmt = conn.prepareStatement(STATEMENT_PURGE_TOPIC);
803: Iterator i = names.iterator();
804: while (i.hasNext()) {
805: String name = (String) i.next();
806: stmt.setString(1, virtualWiki);
807: stmt.setString(2, name);
808: stmt.execute();
809: }
810: stmt.close();
811: } finally {
812: DatabaseConnection.closeConnection(conn);
813: }
814: }
815:
816: /**
817: *
818: */
819: public void purgeVersionsOlderThan(String virtualWiki, DBDate date)
820: throws Exception {
821: if (Environment.getInstance().isOracle()) {
822: purgeVersionsOlderThanOracle(virtualWiki, date);
823: }
824: Connection conn = null;
825: try {
826: conn = DatabaseConnection.getConnection();
827: PreparedStatement stmt = conn
828: .prepareStatement(STATEMENT_PURGE_VERSIONS);
829: stmt.setTimestamp(1, date.asTimestamp());
830: stmt.setString(2, virtualWiki);
831: stmt.execute();
832: stmt.close();
833: } finally {
834: DatabaseConnection.closeConnection(conn);
835: }
836: }
837:
838: /**
839: *
840: */
841: public void saveAsTemplate(String virtualWiki, String templateName,
842: String contents) throws Exception {
843: Connection conn = null;
844: try {
845: conn = DatabaseConnection.getConnection();
846: PreparedStatement stmt = conn
847: .prepareStatement(STATEMENT_TEMPLATE_EXISTS);
848: stmt.setString(1, virtualWiki);
849: stmt.setString(2, templateName);
850: ResultSet rs = stmt.executeQuery();
851: rs.next();
852: int count = rs.getInt(1);
853: stmt.close();
854: if (count < 1) {
855: if (Environment.getInstance().isOracle()) {
856: boolean savedAutoCommit = conn.getAutoCommit();
857: conn.setAutoCommit(false);
858: stmt = conn
859: .prepareStatement(STATEMENT_ADD_TEMPLATE_ORACLE);
860: stmt.setString(1, virtualWiki);
861: stmt.setString(2, templateName);
862: stmt.execute();
863: stmt.close();
864: conn.commit();
865: stmt = conn.prepareStatement(
866: STATEMENT_UPDATE_TEMPLATE_ORACLE2,
867: ResultSet.TYPE_SCROLL_INSENSITIVE,
868: ResultSet.CONCUR_UPDATABLE);
869: stmt.setString(1, virtualWiki);
870: stmt.setString(2, templateName);
871: rs = stmt.executeQuery();
872: rs.next();
873: OracleClobHelper.setClobValue(rs.getClob(1),
874: contents);
875: rs.close();
876: stmt.close();
877: conn.commit();
878: conn.setAutoCommit(savedAutoCommit);
879: } else {
880: stmt = conn
881: .prepareStatement(STATEMENT_ADD_TEMPLATE);
882: stmt.setString(1, virtualWiki);
883: stmt.setString(2, templateName);
884: stmt.setString(3, contents);
885: stmt.execute();
886: stmt.close();
887: }
888: } else {
889: if (Environment.getInstance().isOracle()) {
890: boolean savedAutoCommit = conn.getAutoCommit();
891: conn.setAutoCommit(false);
892: stmt = conn
893: .prepareStatement(STATEMENT_UPDATE_TEMPLATE_ORACLE1);
894: stmt.setString(1, virtualWiki);
895: stmt.setString(2, templateName);
896: stmt.execute();
897: stmt.close();
898: conn.commit();
899: stmt = conn.prepareStatement(
900: STATEMENT_UPDATE_TEMPLATE_ORACLE2,
901: ResultSet.TYPE_SCROLL_INSENSITIVE,
902: ResultSet.CONCUR_UPDATABLE);
903: stmt.setString(1, virtualWiki);
904: stmt.setString(2, templateName);
905: rs = stmt.executeQuery();
906: rs.next();
907: OracleClobHelper.setClobValue(rs.getClob(1),
908: contents);
909: rs.close();
910: stmt.close();
911: conn.commit();
912: conn.setAutoCommit(savedAutoCommit);
913: } else {
914: stmt = conn
915: .prepareStatement(STATEMENT_UPDATE_TEMPLATE);
916: stmt.setString(1, contents);
917: stmt.setString(2, virtualWiki);
918: stmt.setString(3, templateName);
919: stmt.execute();
920: stmt.close();
921: }
922: }
923: } finally {
924: DatabaseConnection.closeConnection(conn);
925: }
926: }
927:
928: /**
929: *
930: */
931: public List getLockList(String virtualWiki) throws Exception {
932: List all = new ArrayList();
933: Connection conn = null;
934: try {
935: conn = DatabaseConnection.getConnection();
936: PreparedStatement stmt = conn
937: .prepareStatement(STATEMENT_GET_LOCK_LIST);
938: stmt.setString(1, virtualWiki);
939: ResultSet rs = stmt.executeQuery();
940: while (rs.next()) {
941: TopicLock lock = new TopicLock(rs
942: .getString("virtualwiki"), rs
943: .getString("topic"), new DBDate(rs
944: .getTimestamp("lockat")), rs
945: .getString("sessionkey"));
946: all.add(lock);
947: }
948: rs.close();
949: stmt.close();
950: } finally {
951: DatabaseConnection.closeConnection(conn);
952: }
953: return all;
954: }
955: }
|