001: package net.jforum.tools.phpbb2jforum;
002:
003: import java.io.IOException;
004: import java.sql.Connection;
005: import java.sql.DriverManager;
006: import java.sql.PreparedStatement;
007: import java.sql.ResultSet;
008: import java.sql.SQLException;
009: import java.sql.Statement;
010:
011: import net.jforum.util.DbUtils;
012: import net.jforum.util.preferences.SystemGlobals;
013:
014: import org.apache.commons.lang.StringUtils;
015:
016: /**
017: * @author Rafael Steil
018: * @version $Id: Main.java,v 1.12 2007/08/31 01:21:34 rafaelsteil Exp $
019: */
020: public class Main {
021: private Connection conn;
022: private Connection conn2;
023: private String[][] regexps;
024:
025: public Main() {
026: this .regexps = new String[][] {
027: { ConfigKeys.B_REGEX, ConfigKeys.B_REPLACE },
028: { ConfigKeys.COLOR_REGEX, ConfigKeys.COLOR_REPLACE },
029: { ConfigKeys.I_REGEX, ConfigKeys.I_REPLACE },
030: { ConfigKeys.LIST_REGEX, ConfigKeys.LIST_REPLACE },
031: { ConfigKeys.QUOTE_REGEX, ConfigKeys.QUOTE_REPLACE },
032: { ConfigKeys.QUOTE_USERNAME_OPEN_REGEX,
033: ConfigKeys.QUOTE_USERNAME_OPEN_REPLACE },
034: { ConfigKeys.QUOTE_USERNAME_CLOSE_REGEX,
035: ConfigKeys.QUOTE_USERNAME_CLOSE_REPLACE },
036: { ConfigKeys.U_REGEX, ConfigKeys.U_REPLACE },
037: { ConfigKeys.IMG_REGEX, ConfigKeys.IMG_REPLACE },
038: { ConfigKeys.CODE_REGEX, ConfigKeys.CODE_REPLACE },
039: { ConfigKeys.SIZE_REGEX, ConfigKeys.SIZE_REPLACE } };
040: }
041:
042: private Connection openConnection() throws ClassNotFoundException,
043: SQLException {
044: Class.forName(SystemGlobals
045: .getValue(ConfigKeys.DATABASE_DRIVER));
046: return DriverManager.getConnection(SystemGlobals
047: .getValue(ConfigKeys.DATABASE_JFORUM_URL));
048: }
049:
050: private void init(String baseDir) throws IOException {
051: SystemGlobals.initGlobals(baseDir, baseDir
052: + "/phpbb2jforum/resource/SystemGlobals.properties");
053: SystemGlobals.loadQueries(baseDir + "/phpbb2jforum/resource/"
054: + SystemGlobals.getValue(ConfigKeys.DATABASE_QUERIES));
055: }
056:
057: private void runForrestRun() throws Exception {
058: this .cleanTables();
059:
060: this .importUsers();
061: this .importTables();
062: this .importPrivateMessages();
063: this .importPosts();
064:
065: this .setupPermissions();
066: }
067:
068: private void importPosts() throws SQLException {
069: int total = this .getTotalPosts();
070:
071: if (total == 0) {
072: System.out
073: .println("Seems like there are no posts to import. Skipping...");
074: return;
075: }
076:
077: System.out
078: .println("Importing posts. This may take a looooong time...");
079: System.out.println("Going to process " + total + " posts...");
080:
081: int counter = 0;
082:
083: Statement s = null;
084: ResultSet rs = null;
085: PreparedStatement insert = null;
086:
087: try {
088: s = this .conn2.createStatement(ResultSet.TYPE_FORWARD_ONLY,
089: ResultSet.CONCUR_READ_ONLY);
090: s.setFetchSize(50);
091:
092: insert = this .conn.prepareStatement(this
093: .getSql(ConfigKeys.QUERY_POSTS_TEXT));
094: rs = s.executeQuery(this
095: .getSql(ConfigKeys.QUERY_SELECT_POSTS_TEXT));
096:
097: System.out.println("Ok, here we go");
098:
099: while (rs.next()) {
100: if ((++counter % 100) == 0) {
101: System.out.println("Processed " + counter
102: + " posts so far");
103: }
104:
105: insert.setInt(1, rs.getInt("post_id"));
106: insert.setString(2, rs.getString("post_subject"));
107: insert.setString(3, this .applyRegexToPostText(rs
108: .getString("post_text")));
109:
110: insert.executeUpdate();
111: }
112: } finally {
113: DbUtils.close(rs, insert);
114: DbUtils.close(s);
115: }
116:
117: System.out.println("Post importing done...");
118: }
119:
120: private void importPrivateMessages() throws SQLException {
121: System.out.println("Importing private messages...");
122:
123: Statement s = null;
124: ResultSet rs = null;
125: PreparedStatement insert = null;
126:
127: try {
128: insert = this .conn.prepareStatement(this
129: .getSql(ConfigKeys.QUERY_PRIVMSGS_TEXT));
130: s = this .conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
131: ResultSet.CONCUR_READ_ONLY);
132: s.setFetchSize(50);
133:
134: rs = s
135: .executeQuery(this
136: .getSql(ConfigKeys.QUERY_SELECT_PM));
137:
138: while (rs.next()) {
139: insert.setInt(1, rs.getInt("privmsgs_text_id"));
140: insert.setString(2, this .applyRegexToPostText(rs
141: .getString("privmsgs_text")));
142:
143: insert.executeUpdate();
144: }
145: } finally {
146: DbUtils.close(rs, insert);
147: DbUtils.close(s);
148: }
149:
150: System.out.println("Private messages text imported...");
151: }
152:
153: private void importUsers() throws SQLException {
154: System.out.println("Importing users...");
155:
156: ResultSet rs = null;
157: Statement s = null;
158: PreparedStatement insert = null;
159:
160: try {
161: insert = this .conn.prepareStatement(this
162: .getSql(ConfigKeys.QUERY_USERS));
163: s = this .conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
164: ResultSet.CONCUR_READ_ONLY);
165: s.setFetchSize(50);
166:
167: rs = s.executeQuery(this
168: .getSql(ConfigKeys.QUERY_SELECT_USERS));
169:
170: while (rs.next()) {
171: insert.setInt(1, rs.getInt("user_id"));
172: insert.setString(2, rs.getString("user_active"));
173: insert.setString(3, rs.getString("username"));
174: insert.setString(4, rs.getString("user_password"));
175: insert.setString(5, rs.getString("user_regdate"));
176: insert.setString(6, rs.getString("user_level"));
177: insert.setString(7, rs.getString("user_posts"));
178: insert.setString(8, rs.getString("user_timezone"));
179: insert.setString(9, rs.getString("user_style"));
180: insert.setString(10, "");
181: insert.setString(11, rs.getString("user_dateformat"));
182: insert.setString(12, rs.getString("user_new_privmsg"));
183: insert.setString(13, rs
184: .getString("user_unread_privmsg"));
185: insert.setString(14, rs.getString("user_last_privmsg"));
186: insert.setString(15, rs.getString("user_viewemail"));
187: insert.setString(16, rs.getString("user_attachsig"));
188: insert.setString(17, rs.getString("user_allowhtml"));
189: insert.setString(18, rs.getString("user_allowbbcode"));
190: insert.setString(19, rs.getString("user_allowsmile"));
191: insert.setString(20, rs.getString("user_allowavatar"));
192: insert.setString(21, rs.getString("user_allow_pm"));
193: insert.setString(22, rs.getString("user_notify"));
194: insert.setString(23, rs.getString("user_notify_pm"));
195: insert.setString(24, rs.getString("user_popup_pm"));
196: insert.setString(25, rs.getString("user_rank"));
197: insert.setString(26, rs.getString("user_avatar"));
198: insert.setString(27, rs.getString("user_avatar_type"));
199: insert.setString(28, rs.getString("user_email"));
200: insert.setString(29, rs.getString("user_icq"));
201: insert.setString(30, rs.getString("user_website"));
202: insert.setString(31, rs.getString("user_from"));
203: insert.setString(32, this .applyRegexToPostText(rs
204: .getString("user_sig")));
205: insert.setString(33, rs.getString("user_aim"));
206: insert.setString(34, rs.getString("user_yim"));
207: insert.setString(35, rs.getString("user_msnm"));
208: insert.setString(36, rs.getString("user_occ"));
209: insert.setString(37, rs.getString("user_interests"));
210: insert.setString(38, rs
211: .getString("user_allow_viewonline"));
212:
213: insert.executeUpdate();
214: }
215: } finally {
216: DbUtils.close(rs, insert);
217: DbUtils.close(s);
218: }
219: }
220:
221: private String applyRegexToPostText(String text) {
222: for (int i = 0; i < this .regexps.length; i++) {
223: if (text == null) {
224: text = "";
225: } else {
226: text = text.replaceAll(SystemGlobals
227: .getValue(this .regexps[i][0]), SystemGlobals
228: .getValue(this .regexps[i][1]));
229: }
230: }
231:
232: return text;
233: }
234:
235: private int getTotalPosts() throws SQLException {
236: int total = 0;
237:
238: Statement s = null;
239: ResultSet rs = null;
240:
241: try {
242: s = this .conn.createStatement();
243: rs = s.executeQuery(this
244: .getSql(ConfigKeys.QUERY_TOTAL_POSTS));
245:
246: if (rs.next()) {
247: total = rs.getInt(1);
248: }
249: } finally {
250: DbUtils.close(rs, s);
251: }
252:
253: return total;
254: }
255:
256: private void cleanTables() throws SQLException {
257: System.out.println("Cleaning tables...");
258:
259: String[] queries = { ConfigKeys.QUERY_CLEAN_BANLIST,
260: ConfigKeys.QUERY_CLEAN_CATEGORIES,
261: ConfigKeys.QUERY_CLEAN_FORUMS,
262: ConfigKeys.QUERY_CLEAN_POSTS,
263: ConfigKeys.QUERY_CLEAN_POSTS_TEXT,
264: ConfigKeys.QUERY_CLEAN_PRIVMSGS,
265: ConfigKeys.QUERY_CLEAN_PRIVMSGS_TEXT,
266: ConfigKeys.QUERY_CLEAN_RANKS,
267: ConfigKeys.QUERY_CLEAN_SMILIES,
268: ConfigKeys.QUERY_CLEAN_TOPICS,
269: ConfigKeys.QUERY_CLEAN_TOPICS_WATCH,
270: ConfigKeys.QUERY_CLEAN_USERS,
271: ConfigKeys.QUERY_CLEAN_VOTE_DESC,
272: ConfigKeys.QUERY_CLEAN_VOTE_RESULTS,
273: ConfigKeys.QUERY_CLEAN_VOTE_VOTERS,
274: ConfigKeys.QUERY_CLEAN_GROUPS,
275: ConfigKeys.QUERY_CLEAN_USERGROUPS };
276:
277: for (int i = 0; i < queries.length; i++) {
278: System.out.println("Cleaning " + queries[i]);
279:
280: Statement s = this .conn.createStatement();
281: s.executeUpdate(this .getSql(queries[i]));
282: s.close();
283: }
284:
285: System.out.println("Tables cleaned...");
286: }
287:
288: private void importTables() throws SQLException {
289: String[][] queries = {
290: { "categories", ConfigKeys.QUERY_CATEGORIES },
291: { "forums", ConfigKeys.QUERY_FORUMS },
292: { "private messages", ConfigKeys.QUERY_PRIVMSGS },
293: { "rankings", ConfigKeys.QUERY_RANKS },
294: { "topics", ConfigKeys.QUERY_TOPICS },
295: { "topics watch", ConfigKeys.QUERY_TOPICS_WATCH },
296: { "posts", ConfigKeys.QUERY_POSTS },
297: { "anonymous update", ConfigKeys.QUERY_UPDATE_ANONYMOUS },
298: { "banlist", ConfigKeys.QUERY_BANLIST },
299: { "Vote Desc", ConfigKeys.QUERY_VOTE_DESC },
300: { "Vote voters", ConfigKeys.QUERY_VOTE_VOTERS },
301: { "Vote results", ConfigKeys.QUERY_VOTE_RESULTS },
302: { "Groups", ConfigKeys.QUERY_GROUPS },
303: { "User groups", ConfigKeys.QUERY_USERGROUPS },
304: { "Anonymous user",
305: ConfigKeys.QUERY_ANONYMOUSUSER_GROUP },
306: { "Admin group", ConfigKeys.QUERY_ADMINGROUP },
307: { "Admin role", ConfigKeys.QUERY_ADMINROLE } };
308:
309: for (int i = 0; i < queries.length; i++) {
310: System.out.println("Importing " + queries[i][0] + "...");
311:
312: Statement s = null;
313:
314: try {
315: s = this .conn.createStatement();
316: s.executeUpdate(this .getSql(queries[i][1]));
317: } finally {
318: DbUtils.close(s);
319: }
320: }
321: }
322:
323: private void setupPermissions() throws SQLException {
324: Statement s = null;
325: ResultSet rs = null;
326: PreparedStatement p = null;
327:
328: try {
329: s = this .conn.createStatement();
330: rs = s.executeQuery(this
331: .getSql(ConfigKeys.QUERY_MAXGROUPID));
332:
333: if (rs.next()) {
334: int groupId = rs.getInt(1);
335:
336: p = this .conn.prepareStatement(this
337: .getSql(ConfigKeys.QUERY_ADMINUSERGROUPS));
338: p.setInt(1, groupId);
339: p.executeUpdate();
340:
341: }
342: } finally {
343: DbUtils.close(rs, p);
344: DbUtils.close(s);
345: }
346: }
347:
348: private String getSql(String queryName) {
349: String query = SystemGlobals.getSql(queryName);
350:
351: query = StringUtils.replace(query, "${phpbb}", SystemGlobals
352: .getValue(ConfigKeys.DATABASE_PHPBB));
353: query = StringUtils.replace(query, "${table.prefix}",
354: SystemGlobals.getValue(ConfigKeys.PHPBB_TABLE_PREFIX));
355:
356: return query;
357: }
358:
359: public static void main(String[] args) {
360: Main program = new Main();
361:
362: if (args.length != 1) {
363: System.out.println("Usage: phpbb2jforum <base_directory>");
364: System.out
365: .println("Example: phpbb2jforum c:/jforum/tools \n");
366: return;
367: }
368:
369: try {
370: program.init(args[0]);
371:
372: // We use autoCommit = true because if something wrong
373: // happen, it's easier to just drop the database and create it again
374:
375: program.conn = program.openConnection();
376: program.conn.setAutoCommit(true);
377:
378: // We need a second connection because the forward-only
379: // query we use later will block until the query is
380: // complete and so we can't write the to new table will
381: // reading from the old
382: program.conn2 = program.openConnection();
383: program.conn2.setAutoCommit(true);
384:
385: long start = System.currentTimeMillis();
386: program.runForrestRun();
387: long end = System.currentTimeMillis() - start;
388:
389: System.out.println("\nDone!!!");
390: System.out.println("Migration was performed in about "
391: + (end / 1000) + " seconds ");
392: } catch (Exception e) {
393: e.printStackTrace();
394: } finally {
395: if (program.conn != null) {
396: try {
397: program.conn.close();
398: } catch (SQLException e) {
399: }
400: }
401: if (program.conn2 != null) {
402: try {
403: program.conn2.close();
404: } catch (SQLException e) {
405: }
406: }
407: }
408: }
409: }
|