001: /**
002: * @author garethc
003: * 22/10/2002 11:46:44
004: */package vqwiki.db;
005:
006: import org.apache.log4j.Logger;
007: import vqwiki.AbstractWikiMembers;
008: import vqwiki.Environment;
009: import vqwiki.WikiMember;
010:
011: import javax.servlet.http.HttpServletRequest;
012: import java.io.IOException;
013: import java.io.InputStream;
014: import java.sql.Connection;
015: import java.sql.PreparedStatement;
016: import java.sql.ResultSet;
017: import java.util.Properties;
018: import java.util.Collection;
019: import java.util.ArrayList;
020:
021: public class DatabaseWikiMembers extends AbstractWikiMembers {
022:
023: private static final Logger logger = Logger
024: .getLogger(DatabaseWikiMembers.class);
025: private static Properties statements;
026:
027: /**
028: *
029: */
030: public DatabaseWikiMembers(String virtualWiki) {
031: if ("".equals(virtualWiki))
032: throw new RuntimeException("Virtual wiki == blank!");
033: this .virtualWiki = virtualWiki;
034: }
035:
036: /**
037: *
038: */
039: public void addMember(String username, String email, String key)
040: throws Exception {
041: WikiMember aMember = createMember(username, email);
042: Connection conn = null;
043: try {
044: conn = DatabaseConnection.getConnection();
045: PreparedStatement stmt = conn
046: .prepareStatement(getStatement("STATEMENT_ADD_MEMBER"));
047: stmt.setString(1, username);
048: stmt.setString(2, email);
049: stmt.setString(3, aMember.getKey());
050: stmt.setString(4, this .virtualWiki);
051: stmt.execute();
052: stmt.close();
053: } finally {
054: DatabaseConnection.closeConnection(conn);
055: }
056: }
057:
058: /**
059: *
060: */
061: public boolean requestMembership(String username, String email,
062: HttpServletRequest virtualWiki) throws Exception {
063: WikiMember aMember = createMember(username, email);
064: mailMember(username, virtualWiki, aMember, email);
065: // remove existing member (might be a re-confirmation)
066: Connection conn = null;
067: try {
068: conn = DatabaseConnection.getConnection();
069: PreparedStatement stmt = conn
070: .prepareStatement(getStatement("STATEMENT_REMOVE_MEMBER"));
071: stmt.setString(1, username);
072: stmt.setString(2, this .virtualWiki);
073: stmt.execute();
074: stmt.close();
075: stmt = conn
076: .prepareStatement(getStatement("STATEMENT_ADD_MEMBER"));
077: stmt.setString(1, username);
078: stmt.setString(2, email);
079: stmt.setString(3, aMember.getKey());
080: stmt.setString(4, this .virtualWiki);
081: stmt.execute();
082: stmt.close();
083: } finally {
084: DatabaseConnection.closeConnection(conn);
085: }
086: return true;
087: }
088:
089: /**
090: *
091: */
092: public boolean createMembershipWithoutRequest(String username,
093: String email) throws Exception {
094: WikiMember aMember = createMember(username, email);
095: aMember.confirm();
096: // remove existing member (might be a re-confirmation)
097: Connection conn = null;
098: try {
099: conn = DatabaseConnection.getConnection();
100: PreparedStatement stmt = conn
101: .prepareStatement(getStatement("STATEMENT_REMOVE_MEMBER"));
102: stmt.setString(1, username);
103: stmt.setString(2, this .virtualWiki);
104: stmt.execute();
105: stmt.close();
106: stmt = conn
107: .prepareStatement(getStatement("STATEMENT_ADD_MEMBER"));
108: stmt.setString(1, username);
109: stmt.setString(2, email);
110: stmt.setString(3, aMember.getKey());
111: stmt.setString(4, this .virtualWiki);
112: stmt.execute();
113: stmt.close();
114: } finally {
115: DatabaseConnection.closeConnection(conn);
116: }
117: return true;
118: }
119:
120: /**
121: *
122: */
123: private void saveMember(WikiMember member) throws Exception {
124: logger.info("Saving member: " + member);
125: Connection conn = null;
126: try {
127: conn = DatabaseConnection.getConnection();
128: PreparedStatement stmt = conn
129: .prepareStatement(getStatement("STATEMENT_UPDATE_MEMBER"));
130: stmt.setString(1, member.getEmail().trim());
131: stmt.setString(2, member.getKey().trim());
132: stmt.setString(3, member.getUserName().trim());
133: stmt.setString(4, this .virtualWiki);
134: stmt.execute();
135: stmt.close();
136: } finally {
137: DatabaseConnection.closeConnection(conn);
138: }
139: }
140:
141: /**
142: *
143: */
144: public boolean confirmMembership(String username, String key)
145: throws Exception {
146: boolean result = super .confirmMembership(username, key);
147: if (result == true)
148: return true;
149: WikiMember member = findMemberByName(username);
150: // Look up the username and check the keys
151: if (!member.checkKey(key))
152: return false;
153: member.confirm();
154: saveMember(member);
155: return true;
156: }
157:
158: /**
159: *
160: */
161: public boolean removeMember(String username) throws Exception {
162: Connection conn = null;
163: int n = 0;
164: try {
165: conn = DatabaseConnection.getConnection();
166: PreparedStatement stmt = conn
167: .prepareStatement(getStatement("STATEMENT_REMOVE_MEMBER"));
168: stmt.setString(1, username);
169: stmt.setString(2, this .virtualWiki);
170: n = stmt.executeUpdate();
171: stmt.close();
172: } finally {
173: DatabaseConnection.closeConnection(conn);
174: }
175: return (n > 0);
176: }
177:
178: /**
179: *
180: */
181: public WikiMember findMemberByName(String username)
182: throws Exception {
183: Connection conn = null;
184: WikiMember member = null;
185: try {
186: conn = DatabaseConnection.getConnection();
187: PreparedStatement stmt = conn
188: .prepareStatement(getStatement("STATEMENT_GET_MEMBER"));
189: stmt.setString(1, username);
190: stmt.setString(2, this .virtualWiki);
191: ResultSet rs = stmt.executeQuery();
192: if (!rs.next()) {
193: rs.close();
194: stmt.close();
195: return new WikiMember(username);
196: }
197: if (Environment.getInstance().isMySQL()) {
198: member = new WikiMember(rs.getString("user"), rs
199: .getString("email"));
200: } else {
201: member = new WikiMember(rs.getString("wikiuser"), rs
202: .getString("email"));
203: }
204: if (Environment.getInstance().isOracle()) {
205: // Oracle handles "" and NULL as the same value. Too bad that a confirmed
206: // member is validated against "" and Oracle saves that as NULL and this
207: // is simply not the same...
208: String key = rs.getString("userkey");
209: if (rs.wasNull()) {
210: key = "";
211: }
212: member.setKey(key);
213: } else {
214: member.setKey(rs.getString("userkey"));
215: }
216: rs.close();
217: stmt.close();
218: } finally {
219: DatabaseConnection.closeConnection(conn);
220: }
221: return member;
222: }
223:
224: /**
225: *
226: */
227: public Collection getAllMembers() throws Exception {
228: Collection all = new ArrayList();
229: Connection conn = null;
230: try {
231: conn = DatabaseConnection.getConnection();
232: PreparedStatement stmt = conn
233: .prepareStatement(getStatement("STATEMENT_ALL_MEMBERS"));
234: stmt.setString(1, this .virtualWiki);
235: ResultSet rs = stmt.executeQuery();
236: while (rs.next()) {
237: WikiMember member;
238: if (Environment.getInstance().isMySQL()) {
239: member = new WikiMember(rs.getString("user"), rs
240: .getString("email"));
241: } else {
242: member = new WikiMember(rs.getString("wikiuser"),
243: rs.getString("email"));
244: }
245: member.setKey(rs.getString("userkey"));
246: all.add(member);
247: }
248: rs.close();
249: stmt.close();
250: } finally {
251: DatabaseConnection.closeConnection(conn);
252: }
253: return all;
254: }
255:
256: /**
257: *
258: */
259: private String getStatement(String key) {
260: if (statements == null) {
261: String statementResource, databaseType;
262: databaseType = Environment.getInstance().getDatabaseType();
263: statementResource = "/wikimembers_" + databaseType
264: + ".sql.properties";
265: try {
266: InputStream in = getClass().getResourceAsStream(
267: statementResource);
268: statements = new Properties();
269: statements.load(in);
270: in.close();
271: } catch (IOException e) {
272: logger.error("Error getting statements from file", e);
273: return null;
274: }
275: }
276: String statement = statements.getProperty(key);
277: logger.debug("statement for " + key + " = " + statement);
278: return statement.trim();
279: }
280: }
|