001: package com.xoetrope.service.replication.derby;
002:
003: import java.sql.ResultSet;
004: import java.sql.SQLException;
005: import java.sql.Types;
006: import com.xoetrope.carousel.build.BuildProperties;
007: import net.xoetrope.debug.DebugLogger;
008:
009: public class XDerbyReplicationService extends
010: com.xoetrope.service.XReplicationService {
011:
012: public XDerbyReplicationService() {
013: super ();
014: }
015:
016: /**
017: * Add the required pseudo column and timestamp column to the named table
018: * @param tableName the table to tag.
019: */
020: protected void tagTable(String tableName) {
021: try {
022:
023: addPseudoId(tableName);
024:
025: doUpdate("ALTER TABLE " + tableName + " ADD " + tableName
026: + "_ts TIMESTAMP");
027: doUpdate("UPDATE " + tableName + " SET " + tableName
028: + "_ts=CURRENT_TIMESTAMP");
029:
030: doUpdate("CREATE TRIGGER " + tableName + "_updtTrigger1"
031: + " AFTER UPDATE OF " + getColumns(tableName)
032: + " ON " + tableName
033: + " REFERENCING OLD AS UPDATEDROW"
034: + " FOR EACH ROW MODE DB2SQL UPDATE " + tableName
035: + " SET " + tableName + "_ts=CURRENT_TIMESTAMP"
036: + " WHERE " + tableName
037: + ".PseudoId=UPDATEDROW.PseudoId");
038:
039: doUpdate("CREATE TRIGGER "
040: + tableName
041: + "_updtTrigger2"
042: + " AFTER UPDATE ON "
043: + tableName
044: + " REFERENCING OLD AS UPDATEDROW"
045: + " FOR EACH ROW MODE DB2SQL UPDATE XSYSSERVERTIMESTAMPS"
046: + " SET lastUpdate=CURRENT_TIMESTAMP "
047: + " WHERE tableName='" + tableName + "'");
048:
049: doUpdate("CREATE TRIGGER " + tableName + "_insTrigger1"
050: + " AFTER INSERT ON " + tableName
051: + " REFERENCING NEW AS INSERTEDROW"
052: + " FOR EACH ROW MODE DB2SQL UPDATE " + tableName
053: + " SET " + tableName + "_ts=CURRENT_TIMESTAMP"
054: + " WHERE " + tableName
055: + ".PseudoId=INSERTEDROW.PseudoId");
056:
057: doUpdate("CREATE TRIGGER "
058: + tableName
059: + "_insTrigger2"
060: + " AFTER INSERT ON "
061: + tableName
062: + " REFERENCING NEW AS INSERTEDROW"
063: + " FOR EACH ROW MODE DB2SQL UPDATE XSYSSERVERTIMESTAMPS"
064: + " SET lastUpdate=CURRENT_TIMESTAMP "
065: + " WHERE tableName='" + tableName + "'");
066:
067: doUpdate("CREATE TRIGGER "
068: + tableName
069: + "_delTrigger"
070: + " AFTER DELETE ON "
071: + tableName
072: + " FOR EACH ROW MODE DB2SQL UPDATE XSYSSERVERTIMESTAMPS"
073: + " SET lastDelete =CURRENT_TIMESTAMP "
074: + " WHERE tableName='" + tableName + "'");
075:
076: doUpdate("CREATE TRIGGER " + tableName + "_deleteTrigger"
077: + " AFTER DELETE ON " + tableName
078: + " REFERENCING OLD AS DELETEDROW "
079: + " FOR EACH ROW MODE DB2SQL "
080: + " INSERT INTO XSYSDELETIONS VALUES" + "( '"
081: + tableName
082: + "', DELETEDROW.PSEUDOID, CURRENT_TIMESTAMP) ");
083:
084: } catch (SQLException ex) {
085: ex.printStackTrace();
086: if (BuildProperties.DEBUG)
087: DebugLogger.logError("Error tagging table: "
088: + tableName);
089: }
090: }
091:
092: /**
093: * Gets the String consisting of column names of the
094: * specified table
095: */
096: private String getColumns(String tableName) {
097: String columns = "";
098: try {
099: ResultSet columnMetaData = connObj.getMetaData(tableName);
100: while (columnMetaData.next()) {
101: String columnName = columnMetaData
102: .getString("COLUMN_NAME");
103: if (columnName.equalsIgnoreCase(tableName + "_ts"))
104: continue;
105: if (columns.length() > 0)
106: columns += ",";
107: columns += columnMetaData.getString("COLUMN_NAME");
108: }
109: } catch (Exception ex) {
110: ex.printStackTrace();
111: columns = null;
112: }
113: return columns;
114: }
115:
116: /**
117: * Creates the timestamp tables.
118: */
119: protected void setupTimestampTables() {
120: doUpdate("CREATE TABLE XSYSSERVERTIMESTAMPS (tableName VARCHAR(64), lastUpdate TIMESTAMP, lastDelete TIMESTAMP)");
121: doUpdate("CREATE TABLE XSYSDELETIONS (tableName VARCHAR(64), pseudoId INT, deleteDate TIMESTAMP)");
122: doUpdate("CREATE TABLE XSYSNEXTIDS( TABLENAME VARCHAR(64), FIELDNAME VARCHAR(64), NEXTID INT, MODE INT )");
123: }
124:
125: /**
126: * Adds a pseudoId column to the specified table
127: * @param tableName the name of the table.
128: */
129: protected void addPseudoId(String tableName) throws SQLException {
130: String targetTableName = (tableName + "_tmp");
131:
132: // get columns meta data
133: String createSql = "";
134: String insertSql = "";
135: ResultSet columnMetaData = connObj.getMetaData(tableName);
136: while (columnMetaData.next()) {
137: String columnName = columnMetaData.getString("COLUMN_NAME");
138: int dataType = columnMetaData.getInt("DATA_TYPE");
139: String dataTypeName = columnMetaData.getString("TYPE_NAME");
140: int dataSize = columnMetaData.getInt("COLUMN_SIZE");
141: int digits = columnMetaData.getInt("DECIMAL_DIGITS");
142: boolean isNullable = (columnMetaData.getInt("NULLABLE") == 1);
143:
144: if (insertSql.length() > 0)
145: insertSql += ", ";
146: insertSql += columnName;
147:
148: if (createSql.length() > 0)
149: createSql += ", ";
150: createSql += (columnName + " " + dataTypeName);
151: switch (dataType) {
152: case Types.VARCHAR:
153: case Types.CHAR:
154: case Types.LONGNVARCHAR:
155: createSql += ("(" + dataSize + ")");
156: break;
157: case Types.DECIMAL:
158: createSql += ("DECIMAL(" + dataSize + "," + digits + ")");
159: break;
160: }
161: if (!isNullable)
162: createSql += " NOT NULL";
163: }
164:
165: // create a new table
166: createSql += (", PSEUDOID INT NOT NULL GENERATED ALWAYS "
167: + "AS IDENTITY (START WITH 1, INCREMENT BY 1)");
168: createSql = ("CREATE TABLE " + targetTableName + " ("
169: + createSql + ")");
170: System.out.println("CREATE SQL: " + createSql); // tmp
171: doUpdate(createSql);
172:
173: // insert the data
174: insertSql = ("INSERT INTO " + targetTableName + "(" + insertSql
175: + ")" + " SELECT * FROM " + tableName);
176: System.out.println("INSERT SQL: " + insertSql); // tmp
177: doUpdate(insertSql);
178:
179: // drop the old table
180: String dropSql = ("DROP TABLE " + tableName);
181: System.out.println("DROP SQL: " + dropSql); // tmp
182: doUpdate(dropSql);
183:
184: // rename the new table
185: String renameSql = ("RENAME TABLE " + targetTableName + " TO " + tableName);
186: System.out.println("RENAME SQL: " + renameSql); // tmp
187: doUpdate(renameSql);
188: }
189:
190: }
|