shuber 2005/04/22 17:18:13 CEST
Modified files:
. jahia_update.jpx
src/java/org/jahia/update/db DBManager.java
Log:
Adding methods to copy table data as well as alter a table by copying through
a temporary table.
Revision Changes Path
1.2 +2 -2 jahia_update/jahia_update.jpx
http://jahia.mine.nu:8080/cgi-bin/cvsweb.cgi/jahia_update/jahia_update.jpx.diff?r1=1.1&r2=1.2&f=h
1.5 +137 -0 jahia_update/src/java/org/jahia/update/db/DBManager.java
http://jahia.mine.nu:8080/cgi-bin/cvsweb.cgi/jahia_update/src/java/org/jahia/update/db/DBManager.java.diff?r1=1.4&r2=1.5&f=h
Index: jahia_update.jpx
===================================================================
RCS file: /home/cvs/repository/jahia_update/jahia_update.jpx,v
retrieving revision 1.1
retrieving revision 1.2
diff -u -r1.1 -r1.2
--- jahia_update.jpx 21 Apr 2005 12:13:46 -0000 1.1
+++ jahia_update.jpx 22 Apr 2005 15:18:12 -0000 1.2
@@ -3,11 +3,11 @@
<project>
<property category="generalFormatting" name="blockIndent" value="4"/>
<property category="generalFormatting" name="tabSize" value="4"/>
- <property category="javaFormatting" name="alwaysClasses" value="1"/>
<property category="javaFormatting" name="catchOnNewLine" value="0"/>
<property category="javaFormatting" name="elseOnNewLine" value="0"/>
<property category="javaFormatting" name="extendsOnNewLine" value="0"/>
<property category="javaFormatting" name="finallyOnNewLine" value="0"/>
+ <property category="javaFormatting" name="packageThreshold" value="0"/>
<property category="javaFormatting" name="whileOnNewLine" value="0"/>
<property category="javadoc" name="custom.tags.1" value="todo;a;To Do:"/>
<property category="runtime" name="DefaultConfiguration" value="-1"/>
@@ -31,7 +31,7 @@
<property category="sys" name="JDK" value="java version 1.4.2_04-b05"/>
<property category="sys" name="JvmVersion" value="1.2"/>
<property category="sys" name="LastTag" value="0"/>
- <property category="sys" name="Libraries" value=""/>
+ <property category="sys" name="Libraries" value="JUnit"/>
<property category="sys" name="MakeStable" value="0"/>
<property category="sys" name="OutPath" value="target/classes"/>
<property category="sys" name="SourcePath" value="src/java"/>
Index: DBManager.java
===================================================================
RCS file:
/home/cvs/repository/jahia_update/src/java/org/jahia/update/db/DBManager.java,v
retrieving revision 1.4
retrieving revision 1.5
diff -u -r1.4 -r1.5
--- DBManager.java 19 Apr 2005 11:14:17 -0000 1.4
+++ DBManager.java 22 Apr 2005 15:18:13 -0000 1.5
@@ -236,6 +236,143 @@
}
/**
+ * This method copies data between an old format and a table and a new
+ * format, provided that the type conversions are trivial enough to be
+ * handled by the database. For example to convert integers to larger
+ * types, or converting strings to clobs, etc...
+ * This method is used as a replacement for the ALTER TABLE SQL command
+ * which is not an entirely SQL standard and varies from one database to
+ * another (and some even don't support it at all).
+ */
+ public void alterTable(String tableName) {
+ try {
+
+ // first let's test if we don't have a existing table that has
+ // the same name as our temporary table.
+
+ String tempTableName = tableName + "_tmp";
+
+ PreparedStatement testTempTableExistenceQuery = prepareStatement(
+ "SELECT * FROM " + tempTableName);
+ ResultSet columnsResultSet =
testTempTableExistenceQuery.executeQuery();
+ if (columnsResultSet.next()) {
+ // a table exists that has the same name as our temporary
table,
+ // let's output a warning and abort immediately.
+ System.out.println("Aborting alterTable operation as a table
called " + tempTableName + " already exists");
+ return;
+ }
+
+ // now that we've made sure that no table exists that would
conflict
+ // with the temporary table, we create the temporary table.
+ PreparedStatement tmpTableCreate = prepareStatement(
+ getTableCreation(tableName));
+ tmpTableCreate.execute();
+
+ // now let's copy all the data into the temporary table
+
+ copyTableData(tableName, tempTableName);
+
+ // now we will drop the source table, and re-create it with the
new
+ // schema definition
+ PreparedStatement tableDrop = prepareStatement("DROP TABLE " +
+ tableName);
+ tableDrop.execute();
+
+ PreparedStatement tableCreate =
prepareStatement(getTableCreation(
+ tableName));
+ tableCreate.execute();
+
+ // now we copy from the temporary table to the new final table
+ copyTableData(tempTableName, tableName);
+
+ // now we drop the temporary table
+ PreparedStatement tmpTableDrop = prepareStatement("DROP TABLE " +
+ tableName);
+ tmpTableDrop.execute();
+
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ }
+
+ /**
+ * Copies the data from a source table to a destination table, assuming
that
+ * the table use the same column names, but the types may be different,
+ * although it is required that they are compatible (this means the
+ * conversion is the responsability of the database and the JDBC driver),
+ * not this code's
+ * @param sourceTableName the name of the source table
+ * @param destTableName the name of the destination table
+ */
+ public void copyTableData(String sourceTableName, String destTableName) {
+ ResultSet columnsResultSet = null;
+ try {
+ StringBuffer sqlColumnsList = new StringBuffer();
+ PreparedStatement selectQuery = prepareStatement(
+ "SELECT * FROM " + sourceTableName);
+ columnsResultSet = selectQuery.executeQuery();
+ ResultSetMetaData columnsMetaData = columnsResultSet.
+ getMetaData();
+ int columnCount = columnsMetaData.getColumnCount();
+
+ // prepared insert statement
+ StringBuffer valuesBuff = new StringBuffer(" VALUES (");
+ StringBuffer buff = new StringBuffer("INSERT INTO ");
+ buff.append(destTableName);
+ buff.append(" (");
+ for (int column = 1; column <= columnCount; column++) {
+ if (column > 1) {
+ buff.append(", ");
+ valuesBuff.append(", ");
+ }
+ buff.append(columnsMetaData.getColumnLabel(column).
+ toLowerCase());
+ valuesBuff.append("?");
+ }
+ buff.append(" )");
+ valuesBuff.append(" )");
+ String sqlInsertStatement = buff.toString() +
+ valuesBuff.toString();
+ PreparedStatement pstmt = null;
+ while (columnsResultSet.next()) {
+ try {
+ pstmt = prepareStatement(sqlInsertStatement);
+ for (int column = 1; column <= columnCount; column++) {
+ String column_name = columnsMetaData.
+ getColumnLabel(column).toLowerCase();
+ Object column_value = columnsResultSet.
+ getObject(column);
+ pstmt.setObject(column, column_value);
+ }
+ pstmt.executeUpdate();
+ } catch (Throwable t) {
+ t.printStackTrace();
+ } finally {
+ if (pstmt != null) {
+ try {
+ pstmt.close();
+ } catch (Throwable t) {
+ t.printStackTrace();
+ }
+ }
+ }
+ }
+ } catch (IndexOutOfBoundsException ioobe) {
+ ioobe.printStackTrace();
+ } catch (SQLException sqle) {
+ sqle.printStackTrace();
+ } finally {
+ if (columnsResultSet != null) {
+ try {
+ columnsResultSet.close();
+ } catch (Throwable t) {
+ t.printStackTrace();
+ }
+ }
+ }
+ }
+
+ /**
* Apply the garbage collector.
*/
protected void finalize() {
