001: package org.jsqltool.replication;
002:
003: import javax.swing.*;
004: import java.awt.*;
005: import java.awt.event.*;
006: import javax.swing.event.*;
007: import java.io.*;
008: import java.util.*;
009:
010: import org.jsqltool.replication.*;
011: import org.jsqltool.gui.*;
012: import org.jsqltool.utils.ImageLoader;
013: import org.jsqltool.utils.Options;
014: import org.jsqltool.conn.*;
015: import org.jsqltool.conn.gui.ConnectionFrame;
016: import java.sql.*;
017: import javax.swing.table.TableModel;
018:
019: /**
020: * <p>Title: JSqlTool Project</p>
021: * <p>Description: Class used to manage a replication process.
022: * </p>
023: * <p>Copyright: Copyright (C) 2006 Mauro Carniel</p>
024: *
025: * <p> This file is part of JSqlTool project.
026: * This library is free software; you can redistribute it and/or
027: * modify it under the terms of the (LGPL) Lesser General Public
028: * License as published by the Free Software Foundation;
029: *
030: * GNU LESSER GENERAL PUBLIC LICENSE
031: * Version 2.1, February 1999
032: *
033: * This library is distributed in the hope that it will be useful,
034: * but WITHOUT ANY WARRANTY; without even the implied warranty of
035: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
036: * Library General Public License for more details.
037: *
038: * You should have received a copy of the GNU Library General Public
039: * License along with this library; if not, write to the Free
040: * Software Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
041: *
042: * The author may be contacted at:
043: * maurocarniel@tin.it</p>
044: *
045: * @author Mauro Carniel
046: * @version 1.0
047: */
048: public class Replication {
049:
050: /** data replication profile */
051: private ReplicationProfile profile = null;
052:
053: /** main frame */
054: private MainFrame parent = null;
055:
056: public Replication(MainFrame parent, ReplicationProfile profile) {
057: this .parent = parent;
058: this .profile = profile;
059: DbConnectionUtil srcDbUtil = null;
060: DbConnectionUtil destDbUtil = null;
061: try {
062: // retrieve source and destination db connections settings...
063: ConnectionFrame f = new ConnectionFrame();
064: ArrayList conns = f.getConnections();
065: DbConnection dbConn = null;
066: DbConnection srcDbConn = null;
067: DbConnection destDbConn = null;
068: for (int i = 0; i < conns.size(); i++) {
069: dbConn = (DbConnection) conns.get(i);
070: if (dbConn.getName()
071: .equals(profile.getSourceDatabase()))
072: srcDbConn = dbConn;
073: else if (dbConn.getName().equals(
074: profile.getDestDatabase()))
075: destDbConn = dbConn;
076: }
077: if (srcDbConn == null || destDbConn == null) {
078: JOptionPane.showMessageDialog(parent, Options
079: .getInstance().getResource(
080: "database connections don't exist"),
081: Options.getInstance().getResource("error"),
082: JOptionPane.ERROR_MESSAGE);
083: return;
084: }
085:
086: // start progress bar...
087: try {
088: ProgressDialog.getInstance().startProgress();
089: } catch (Throwable ex5) {
090: }
091:
092: // create source and target database connections...
093: srcDbUtil = new DbConnectionUtil(parent, srcDbConn);
094: destDbUtil = new DbConnectionUtil(parent, destDbConn);
095: try {
096: ProgressDialog.getInstance().startProgress();
097: } catch (Throwable ex6) {
098: }
099:
100: // fetch all target db table names...
101: java.util.List destTables = destDbUtil.getTables(destDbConn
102: .getUsername(), "TABLE");
103: for (int i = 0; i < destTables.size(); i++)
104: destTables.set(i, destTables.get(i).toString()
105: .toUpperCase());
106: try {
107: ProgressDialog.getInstance().startProgress();
108: } catch (Throwable ex3) {
109: }
110:
111: ArrayList tables = profile.getTablesList();
112: String tableName = null;
113: TableModel srcModel = null;
114: TableModel destModel = null;
115: ArrayList colsToAdd = new ArrayList();
116: for (int i = 0; i < tables.size(); i++) {
117: // for each table:
118: tableName = tables.get(i).toString();
119:
120: // fetch source table structure...
121: // the method "getTableColumns" returns: column, data type, pk, null?, default
122: srcModel = srcDbUtil.getTableColumns(tableName);
123: try {
124: ProgressDialog.getInstance().startProgress();
125: } catch (Throwable ex4) {
126: }
127:
128: // 1. TABLE STRUCTURE ANALYSYS
129: // check if the table exixts in the target db...
130: if (destTables.indexOf(tableName.toUpperCase()) == -1) {
131: // the table doesn't exist: it must be created...
132: createTable(destDbUtil, destDbConn.getDbType(),
133: tableName, srcModel, false);
134: destModel = srcModel;
135: } else {
136: // the table already exists:
137: // if "re-create table content" is true then (i) drop table and (ii) re-create structure
138: // else
139: // check if the structure is the same of the src table:
140: if (profile.isRecreateTablesContent()) {
141: createTable(destDbUtil, destDbConn.getDbType(),
142: tableName, srcModel, true);
143: destModel = srcModel;
144: } else {
145: // fetch target table structure...
146: // the method "getTableColumns" returns: column, data type, pk, null?, default
147: destModel = srcDbUtil
148: .getTableColumns(tableName);
149: colsToAdd.clear();
150: for (int j = 0; j < srcModel.getRowCount(); j++) {
151: if (!tableContainsCol(srcModel.getValueAt(
152: j, 0).toString(), destModel))
153: colsToAdd
154: .add(srcModel.getValueAt(j, 0));
155: }
156: if (colsToAdd.size() > 0)
157: updateTableStructure(destDbUtil.getConn(),
158: destDbConn.getDbType(), tableName,
159: srcModel, colsToAdd);
160: }
161: }
162:
163: // 2. DATA ANALYSYS
164:
165: // transfer data from source to destination database:
166: // if "re-create table content" is true then all records read are inserted into the dest db
167: // otherwise check if there exist some records in dest db: if yes, then
168: // all records read are updated into the dest db and if update return 0 updates then the record is inserted
169: // otherwise all records read are inserted into the dest db
170: if (profile.isRecreateTablesContent()) {
171: insertRecords(srcDbUtil, destDbUtil, tableName,
172: srcModel);
173: } else {
174: if (canUpdate(destDbUtil, tableName, destModel)) {
175: updateRecords(srcDbUtil, destDbUtil, tableName,
176: srcModel);
177: } else {
178: insertRecords(srcDbUtil, destDbUtil, tableName,
179: srcModel);
180: }
181: }
182:
183: }
184:
185: // stop progress bar...
186: ProgressDialog.getInstance().stopProgress();
187:
188: destDbUtil.getConn().commit();
189:
190: JOptionPane.showMessageDialog(parent, Options.getInstance()
191: .getResource("data replication is completed."),
192: Options.getInstance().getResource(
193: "replication completed"),
194: JOptionPane.INFORMATION_MESSAGE);
195:
196: } catch (Exception ex) {
197: ProgressDialog.getInstance().stopProgress();
198: ex.printStackTrace();
199: JOptionPane.showMessageDialog(parent, Options.getInstance()
200: .getResource("error while replicating data")
201: + ":\n" + ex.getMessage(), Options.getInstance()
202: .getResource("error"), JOptionPane.ERROR_MESSAGE);
203: } finally {
204: try {
205: if (srcDbUtil != null) {
206: srcDbUtil.getConn().close();
207: }
208: } catch (Exception ex1) {
209: }
210: try {
211: if (destDbUtil != null) {
212: destDbUtil.getConn().rollback();
213: destDbUtil.getConn().close();
214: }
215: } catch (Exception ex2) {
216: }
217: }
218: }
219:
220: /**
221: * Check if the destination table contains records and has a primary key defined.
222: */
223: private boolean canUpdate(DbConnectionUtil destDbUtil,
224: String tableName, TableModel destModel) throws Exception {
225: boolean hasPK = false;
226: for (int i = 0; i < destModel.getRowCount(); i++)
227: // destModel columns: column, data type, pk, null?, default
228: if (destModel.getValueAt(i, 2) != null) {
229: hasPK = true;
230: }
231: if (!hasPK)
232: return false;
233:
234: Statement stmt = destDbUtil.getConn().createStatement();
235: ResultSet rset = stmt
236: .executeQuery("select * from " + tableName);
237: boolean ok = false;
238: if (rset.next())
239: ok = true;
240:
241: try {
242: rset.close();
243: } catch (Exception ex3) {
244: }
245: try {
246: stmt.close();
247: } catch (Exception ex4) {
248: }
249:
250: return ok;
251: }
252:
253: /**
254: * Insert Records and if a record insert fails, then update it.
255: */
256: private void insertRecords(DbConnectionUtil srcDbUtil,
257: DbConnectionUtil destDbUtil, String tableName,
258: TableModel srcModel) throws Exception {
259: Statement stmt = srcDbUtil.getConn().createStatement();
260: ResultSet rset = stmt
261: .executeQuery("select * from " + tableName);
262:
263: // retrieve columns types...
264: int[] colTypes = new int[srcModel.getRowCount()];
265: for (int i = 0; i < srcModel.getRowCount(); i++) {
266: colTypes[i] = rset.getMetaData().getColumnType(i + 1);
267: }
268:
269: // create SQL statement for an insert...
270: String insSQL = "insert into " + tableName + "(";
271: for (int i = 0; i < srcModel.getRowCount(); i++) {
272: insSQL += (srcModel.getValueAt(i, 0).toString()
273: .toUpperCase().startsWith("QUANTIT") ? "QUANTITA"
274: : srcModel.getValueAt(i, 0))
275: + ",";
276: }
277: insSQL = insSQL.substring(0, insSQL.length() - 1);
278: insSQL += ") values(";
279: for (int i = 0; i < srcModel.getRowCount(); i++) {
280: insSQL += "?,";
281: }
282: insSQL = insSQL.substring(0, insSQL.length() - 1);
283: insSQL += ")";
284:
285: // create SQL statement for an update...
286: String updSQL = "update " + tableName + " set ";
287: for (int i = 0; i < srcModel.getRowCount(); i++) {
288: updSQL += (srcModel.getValueAt(i, 0).toString()
289: .toUpperCase().startsWith("QUANTIT") ? "QUANTITA"
290: : srcModel.getValueAt(i, 0))
291: + "=?,";
292: }
293: updSQL = updSQL.substring(0, updSQL.length() - 1);
294: updSQL += " where ";
295: ArrayList pks = new ArrayList();
296: ArrayList pksIndexes = new ArrayList();
297: for (int i = 0; i < srcModel.getRowCount(); i++)
298: if (srcModel.getValueAt(i, 2) != null) {
299: updSQL += srcModel.getValueAt(i, 0) + "=? and ";
300: pks.add(srcModel.getValueAt(i, 0));
301: pksIndexes.add(new Integer(i));
302: }
303: if (updSQL.endsWith(" and "))
304: updSQL = updSQL.substring(0, updSQL.length() - 4);
305:
306: PreparedStatement destInsStmt = destDbUtil.getConn()
307: .prepareStatement(insSQL);
308: PreparedStatement destUpdStmt = destDbUtil.getConn()
309: .prepareStatement(updSQL);
310: Object[] row = new Object[srcModel.getRowCount()];
311: while (rset.next()) {
312: for (int i = 0; i < srcModel.getRowCount(); i++) {
313: row[i] = rset.getObject(i + 1);
314: if (row[i] != null)
315: destInsStmt.setObject(i + 1, row[i]);
316: else
317: destInsStmt.setNull(i + 1, colTypes[i]);
318: }
319: try {
320: destInsStmt.execute();
321: } catch (SQLException ex) {
322: if (pks.size() == 0)
323: throw ex;
324: // the record already exists:
325: // create an update statement...
326: for (int i = 0; i < srcModel.getRowCount(); i++) {
327: if (row[i] != null)
328: destUpdStmt.setObject(i + 1, row[i]);
329: else
330: destUpdStmt.setNull(i + 1, colTypes[i]);
331: }
332: for (int i = 0; i < pks.size(); i++)
333: destUpdStmt.setObject(srcModel.getRowCount() + i
334: + 1, row[((Integer) pksIndexes.get(i))
335: .intValue()]);
336: destUpdStmt.execute();
337: }
338: }
339:
340: try {
341: rset.close();
342: } catch (Exception ex3) {
343: }
344: try {
345: stmt.close();
346: } catch (Exception ex4) {
347: }
348: try {
349: destInsStmt.close();
350: } catch (Exception ex5) {
351: }
352: try {
353: destUpdStmt.close();
354: } catch (Exception ex5) {
355: }
356:
357: }
358:
359: /**
360: * Update Records and if a record update fails, then insert it.
361: */
362: private void updateRecords(DbConnectionUtil srcDbUtil,
363: DbConnectionUtil destDbUtil, String tableName,
364: TableModel srcModel) throws Exception {
365: Statement stmt = srcDbUtil.getConn().createStatement();
366: ResultSet rset = stmt
367: .executeQuery("select * from " + tableName);
368:
369: // retrieve columns types...
370: int[] colTypes = new int[srcModel.getRowCount()];
371: for (int i = 0; i < srcModel.getRowCount(); i++) {
372: colTypes[i] = rset.getMetaData().getColumnType(i + 1);
373: }
374:
375: // create SQL statement for an insert...
376: String insSQL = "insert into " + tableName + "(";
377: for (int i = 0; i < srcModel.getRowCount(); i++) {
378: insSQL += srcModel.getValueAt(i, 0) + ",";
379: }
380: insSQL = insSQL.substring(0, insSQL.length() - 1);
381: insSQL += ") values(";
382: for (int i = 0; i < srcModel.getRowCount(); i++) {
383: insSQL += "?,";
384: }
385: insSQL = insSQL.substring(0, insSQL.length() - 1);
386: insSQL += ")";
387:
388: // create SQL statement for an update...
389: String updSQL = "update " + tableName + " set ";
390: for (int i = 0; i < srcModel.getRowCount(); i++) {
391: updSQL += srcModel.getValueAt(i, 0) + "=?,";
392: }
393: updSQL = updSQL.substring(0, updSQL.length() - 1);
394: updSQL += " where ";
395: ArrayList pks = new ArrayList();
396: ArrayList pksIndexes = new ArrayList();
397: for (int i = 0; i < srcModel.getRowCount(); i++)
398: if (srcModel.getValueAt(i, 2) != null) {
399: updSQL += srcModel.getValueAt(i, 0) + "=? and ";
400: pks.add(srcModel.getValueAt(i, 0));
401: pksIndexes.add(new Integer(i));
402: }
403: if (updSQL.endsWith(" and "))
404: updSQL = updSQL.substring(0, updSQL.length() - 4);
405:
406: PreparedStatement destInsStmt = destDbUtil.getConn()
407: .prepareStatement(insSQL);
408: PreparedStatement destUpdStmt = destDbUtil.getConn()
409: .prepareStatement(updSQL);
410: Object[] row = new Object[srcModel.getRowCount()];
411: while (rset.next()) {
412: for (int i = 0; i < srcModel.getRowCount(); i++) {
413: row[i] = rset.getObject(i + 1);
414: if (row[i] != null)
415: destUpdStmt.setObject(i + 1, row[i]);
416: else
417: destUpdStmt.setNull(i + 1, colTypes[i]);
418: }
419: for (int j = 0; j < pks.size(); j++)
420: destUpdStmt.setObject(srcModel.getRowCount() + j + 1,
421: row[((Integer) pksIndexes.get(j)).intValue()]);
422:
423: if (destUpdStmt.executeUpdate() == 0) {
424: // no record found: it will be inserted...
425: for (int i = 0; i < srcModel.getRowCount(); i++) {
426: if (row[i] != null)
427: destInsStmt.setObject(i + 1, row[i]);
428: else
429: destInsStmt.setNull(i + 1, colTypes[i]);
430: }
431: destInsStmt.execute();
432: }
433:
434: }
435:
436: try {
437: rset.close();
438: } catch (Exception ex3) {
439: }
440: try {
441: stmt.close();
442: } catch (Exception ex4) {
443: }
444: try {
445: destInsStmt.close();
446: } catch (Exception ex5) {
447: }
448: try {
449: destUpdStmt.close();
450: } catch (Exception ex5) {
451: }
452:
453: }
454:
455: /**
456: * Check if there exist the specified column in the dest table.
457: */
458: private boolean tableContainsCol(String colToCheck,
459: TableModel destModel) {
460: for (int i = 0; i < destModel.getRowCount(); i++)
461: if (destModel.getValueAt(i, 0).equals(colToCheck))
462: return true;
463: return false;
464: }
465:
466: /**
467: * Update a table structure.
468: */
469: private void updateTableStructure(Connection destConn, int dbType,
470: String tableName, TableModel srcModel, ArrayList colsToAdd)
471: throws Exception {
472: Statement stmt = destConn.createStatement();
473: String sql = null;
474: String colToAdd = null;
475: for (int i = 0; i < colsToAdd.size(); i++) {
476: colToAdd = colsToAdd.get(i).toString();
477: sql = "alter table " + tableName + " add column "
478: + colToAdd + " ";
479: for (int j = 0; j < srcModel.getRowCount(); j++) {
480: // srcModel columns: column, data type, pk, null?, default
481: if (srcModel.getValueAt(j, 0).equals(colToAdd)) {
482: sql += getColType(srcModel.getValueAt(j, 1)
483: .toString(), dbType)
484: + " ";
485: if (!((Boolean) srcModel.getValueAt(j, 3))
486: .booleanValue())
487: sql += " NOT NULL ";
488: break;
489: }
490: }
491: stmt.execute(sql);
492: }
493: stmt.close();
494: }
495:
496: /**
497: * Create a table.
498: */
499: private void createTable(DbConnectionUtil destDbConnUtil,
500: int dbType, String tableName, TableModel srcModel,
501: boolean dropTable) throws Exception {
502: Connection destConn = destDbConnUtil.getConn();
503: Statement stmt = destConn.createStatement();
504: if (dropTable) {
505: stmt.execute("drop table " + tableName);
506: }
507:
508: String sql = "create table " + tableName + "(";
509: Hashtable pks = new Hashtable();
510: for (int i = 0; i < srcModel.getRowCount(); i++) {
511: // srcModel columns: column, data type, pk, null?, default
512: sql += srcModel.getValueAt(i, 0) + " ";
513: sql += getColType(srcModel.getValueAt(i, 1).toString(),
514: dbType)
515: + " ";
516: if (!((Boolean) srcModel.getValueAt(i, 3)).booleanValue())
517: sql += " NOT NULL ";
518: sql += ",";
519:
520: if (srcModel.getValueAt(i, 2) != null)
521: pks.put(srcModel.getValueAt(i, 2), srcModel.getValueAt(
522: i, 0));
523: }
524: if (pks.size() > 0) {
525: sql += "PRIMARY KEY(";
526: for (int i = 0; i < pks.size(); i++)
527: sql += pks.get(new Integer(i + 1)) + ",";
528: sql = sql.substring(0, sql.length() - 1);
529: sql += "),";
530: }
531:
532: sql = sql.substring(0, sql.length() - 1);
533: sql += ")";
534: stmt.execute(sql);
535:
536: // create indexes...
537: String text = "";
538: try {
539: TableModel model = destDbConnUtil
540: .getTableIndexes(tableName);
541: String indexName = null;
542: boolean unique = false;
543: for (int i = 0; i < model.getRowCount(); i++) {
544: if (model.getValueAt(i, 5) == null) {
545: continue;
546: }
547: unique = false;
548: if (model.getValueAt(i, 3).getClass().equals(
549: String.class)) {
550: unique = model.getValueAt(i, 3).toString().equals(
551: "0");
552: } else if (model.getValueAt(i, 3) instanceof Number) {
553: unique = ((Number) model.getValueAt(i, 3))
554: .intValue() == 0;
555:
556: }
557: if (model.getValueAt(i, 5).equals(indexName)) {
558: text += model.getValueAt(i, 8) + ",";
559: continue;
560: } else {
561: if (indexName != null) {
562: text = text.substring(0, text.length() - 1);
563: text += ")";
564: stmt.execute(text);
565: text = "";
566: }
567: indexName = model.getValueAt(i, 5).toString();
568: }
569: text += "CREATE ";
570: if (unique) {
571: text += "UNIQUE ";
572: }
573: // text += "INDEX " + model.getValueAt(i, 5)+"_"+tableName + " ON " + tableName + " (";
574: text += "INDEX " + model.getValueAt(i, 5) + " ON "
575: + tableName + " (";
576: text += model.getValueAt(i, 8) + ",";
577: }
578: if (indexName != null) {
579: text = text.substring(0, text.length() - 1);
580: text += ")";
581: stmt.execute(text);
582: }
583: } catch (SQLException ex) {
584: ex.printStackTrace();
585: }
586:
587: stmt.close();
588: }
589:
590: /**
591: * @param type source table column type
592: * @return destination table column type, according to the db type
593: */
594: private String getColType(String type, int dbType) {
595: // case MySQL...
596: if (type.equals("VARCHAR(0)"))
597: type = "VARCHAR(255)";
598: else if (type.equals("VARCHAR2(0)"))
599: type = "VARCHAR2(255)";
600: else if (type.equals("NUMERIC(0)")
601: || type.equals("NUMERIC(0,0)"))
602: type = "NUMERIC(20)";
603: else if (type.equals("DECIMAL(0)")
604: || type.equals("DECIMAL(0,0)"))
605: type = "DECIMAL(20,5)";
606:
607: if (dbType == DbConnection.ORACLE_TYPE) {
608: if (type.startsWith("VARCHAR2"))
609: return "VARCHAR2" + type.substring(8);
610: else if (type.startsWith("VARCHAR"))
611: return "VARCHAR2" + type.substring(7);
612: else if (type.startsWith("NUMERIC"))
613: return "NUMBER" + type.substring(7);
614: } else if (dbType == DbConnection.SQLSERVER_TYPE) {
615: if (type.startsWith("VARCHAR2"))
616: return "VARCHAR" + type.substring(7);
617: else if (type.startsWith("NUMBER"))
618: return "NUMERIC" + type.substring(6);
619: } else {
620: if (type.startsWith("VARCHAR2"))
621: return "VARCHAR" + type.substring(8);
622: else if (type.startsWith("NUMBER"))
623: return "NUMERIC" + type.substring(6);
624: else if (type.startsWith("INTEGER"))
625: return "NUMERIC" + type.substring(7);
626: else if (type.startsWith("DECIMAL"))
627: return "NUMERIC" + type.substring(7);
628: else if (type.startsWith("DATE"))
629: return "TIMESTAMP";
630: }
631: return type;
632: }
633:
634: }
|