Menu
▾
▴
[Adempiere-cvslog] SF.net SVN: adempiere: [554] contributions/stuff/migration/src
|
From: <kth...@us...> - 2006-10-20 18:09:23
|
Revision: 554
http://svn.sourceforge.net/adempiere/?rev=554&view=rev
Author: kthiemann
Date: 2006-10-20 11:09:07 -0700 (Fri, 20 Oct 2006)
Log Message:
-----------
Some bugfixes, deleting of removed data added
Modified Paths:
--------------
contributions/stuff/migration/src/oracle/Column.java
contributions/stuff/migration/src/oracle/DBDifference.java
contributions/stuff/migration/src/oracle/Table.java
contributions/stuff/migration/src/readme.txt
Modified: contributions/stuff/migration/src/oracle/Column.java
===================================================================
--- contributions/stuff/migration/src/oracle/Column.java 2006-10-20 14:30:59 UTC (rev 553)
+++ contributions/stuff/migration/src/oracle/Column.java 2006-10-20 18:09:07 UTC (rev 554)
@@ -121,7 +121,29 @@
}
return buffer.toString();
}
+
+ /**
+ * Returns a sql sniplet that defines the column.
+ * Without DEFAULT NULL if no default value is set,
+ * because resulting create table statement looks better :)
+ *
+ * @return
+ */
+ public String getDefinitionStringForCreateTable() {
+ StringBuffer buffer = getTypeDefinitionString();
+ if (defaultValue != null && !defaultValue.equals("")) {
+ buffer = buffer.append(" DEFAULT ").append(defaultValue);
+ }
+
+ if (!isNullable) {
+ buffer = buffer.append(" NOT NULL ");
+ } else {
+ buffer = buffer.append(" NULL ");
+ }
+ return buffer.toString();
+ }
+
/*
* (non-Javadoc)
*
Modified: contributions/stuff/migration/src/oracle/DBDifference.java
===================================================================
--- contributions/stuff/migration/src/oracle/DBDifference.java 2006-10-20 14:30:59 UTC (rev 553)
+++ contributions/stuff/migration/src/oracle/DBDifference.java 2006-10-20 18:09:07 UTC (rev 554)
@@ -95,6 +95,9 @@
/** changed lines for AD_Tables */
private Vector<String> m_alterADEntry = new Vector<String>();
+
+ /** deleted lines for AD_Tables */
+ private Vector<String> m_deleteADEntry = new Vector<String>();
/** new functions/procedure statements to create */
private Vector<String> m_newFunctionStatements = new Vector<String>();
@@ -150,23 +153,23 @@
stmtdb1 = con1.createStatement();
stmtdb2 = con2.createStatement();
- System.out.println("compare tables ...");
- compareTables();
- System.out.println("compare constraints ...");
- compareConstraints();
+ System.out.println("compare tables ...");
+ compareTables();
+ System.out.println("compare constraints ...");
+ compareConstraints();
+
+ System.out.println("compare views ...");
+ compareViews();
+
+ System.out.println("compare functions/procedures ...");
+ compareFunctionsAndProcedures();
+
+ System.out.println("drop triggers ...");
+ dropTriggers();
+
+ System.out.println("compare ad_elements ...");
+ compareADElements();
- System.out.println("compare views ...");
- compareViews();
-
- System.out.println("compare functions/procedures ...");
- compareFunctionsAndProcedures();
-
- System.out.println("drop triggers ...");
- dropTriggers();
-
- System.out.println("compare ad_elements ...");
- compareADElements();
-
System.out.println("compare indexes ...");
compareIndexes();
@@ -227,13 +230,19 @@
final Vector<String> newIndexes = getNewElements(indexNamesDB1, indexNamesDB2);
for (int i = 0; i < newIndexes.size(); i++) {
- createNewIndexEntry(newIndexes.get(i));
+ if (!newIndexes.get(i).endsWith("KEY")) {
+ // key indexes are generated by oracle when you create the table
+ createNewIndexEntry(newIndexes.get(i));
+ }
}
// drop indexes not found in db2
final Vector<String> missingIndexes = getMissingElements(indexNamesDB1, indexNamesDB2);
for (int i = 0; i < missingIndexes.size(); i++) {
- m_dropIndexStatements.add("DROP INDEX " + missingIndexes.get(i) + ";");
+ if (!missingIndexes.get(i).endsWith("KEY")) {
+ //unable to drop key indexes - they are dropped if table is dropped
+ m_dropIndexStatements.add("DROP INDEX " + missingIndexes.get(i) + ";");
+ }
}
// find changed index
for (int i = 0; i < indexNamesDB2.size(); i++) {
@@ -256,7 +265,7 @@
rs.close();
// simple comparison - just compare the length...
if (columnNames1.size() != columnNames2.size()) {
- if (!indexNamesDB2.get(i).endsWith("_KEY")) {
+ if (!indexNamesDB2.get(i).endsWith("KEY")) {
// cant drop key indexes...
m_dropIndexStatements.add("DROP INDEX " + indexNamesDB2.get(i) + ";");
createNewIndexEntry(indexNamesDB2.get(i));
@@ -736,8 +745,10 @@
for (int i = 0; i < tempVector.size(); i++) {
sortedStatements.add(tempVector.get(i));
}
-
+
// new data
+ sortedStatements.add("COMMIT;");
+ sortedStatements.add("SET DEFINE OFF;");
statements.clear();
for (int i = 0; i < m_newTableEntry.size(); i++) {
statements.add(m_newTableEntry.get(i).replaceAll("\n", " "));
@@ -747,6 +758,8 @@
sortedStatements.add(tempVector.get(i));
}
// changed data
+ sortedStatements.add("COMMIT;");
+ sortedStatements.add("SET DEFINE OFF;");
statements.clear();
for (int i = 0; i < m_alterADEntry.size(); i++) {
statements.add(m_alterADEntry.get(i).replaceAll("\n", " "));
@@ -755,6 +768,18 @@
for (int i = 0; i < tempVector.size(); i++) {
sortedStatements.add(tempVector.get(i));
}
+ // data to delete
+ sortedStatements.add("COMMIT;");
+ sortedStatements.add("SET DEFINE OFF;");
+ statements.clear();
+ for (int i = 0; i < m_deleteADEntry.size(); i++) {
+ statements.add(m_deleteADEntry.get(i).replaceAll("\n", " "));
+ }
+ tempVector = sortStatements(statements);
+ for (int i = 0; i < tempVector.size(); i++) {
+ sortedStatements.add(tempVector.get(i));
+ }
+
System.out.println();
System.out.println("---------------------------");
@@ -1320,7 +1345,7 @@
private void compareADElements() throws SQLException {
final String TABLE_DOES_NOT_EXITST = "ORA-00942";
final Vector<String> adTableNames2 = new Vector<String>();
- String sql = "select table_name from user_tables where table_name like 'AD_%'";
+ String sql = "select table_name from user_tables";// where table_name like 'AD_%'";
ResultSet rs = stmtdb2.executeQuery(sql);
@@ -1413,6 +1438,34 @@
rs.close();
// got all data - lets compare them (will take some time...)
// new entry? search for data with same keyValues
+
+ //TODO: drop entries missing in db2...
+ try {
+ //find elements to drop
+ for(int j = 0; j < dataElements1.size(); j++) {
+ boolean found = false;
+ final ADDataElement data1 = dataElements1.get(j);
+ sql = "select * from " + tableName + " where ";
+ for (int m = 0; m < keycolumns.size(); m++) {
+ if (m != 0) {
+ sql += " and ";
+ }
+ sql += keycolumns.get(m) + "=" + data1.getValueForColumn(keycolumns.get(m));
+ }
+ rs = stmtdb2.executeQuery(sql);
+ if (rs.next()) {
+ found = true;
+ }
+ rs.close();
+ if(!found){
+ //data exist in db1 but no longer in db2 - delete it
+ createDeleteTableEntry(tableName, data1, keycolumns, columns1);
+ }
+ }
+ } catch (SQLException e1) {
+ // if keyColumns have changed...
+ System.out.println(e1.getMessage() + " - on searching data to drop for table: " + tableName);
+ }
for (int j = 0; j < dataElements2.size(); j++) {
boolean found = false;
@@ -1517,6 +1570,43 @@
}
/**
+ * Creates a delete from table statement for the given entry and adds it to the
+ * global m_deleteADEntry Vector.
+ * @param tableName
+ * @param data1
+ * @param keycolumns
+ * @param columns1
+ */
+ private void createDeleteTableEntry(String tableName, ADDataElement data1, Vector<String> keycolumns, Vector<Column> columns1) {
+ String alterStatement = "DELETE FROM " + tableName + " WHERE ";
+ boolean and = false;
+ for (int i = 0; i < columns1.size(); i++) {
+ final Column column = columns1.get(i);
+ final String columnName = column.getColumnName();
+ if (keycolumns.contains(columnName)) {
+ if (and) {
+ alterStatement += " AND ";
+ }
+ and = true;
+ if (data1.getValueForColumn(columnName) == null) {
+ alterStatement += column.getColumnName() + " is null ";
+ continue;
+ }
+ alterStatement += column.getColumnName() + "=";
+ if (column.isStringType()) {
+ alterStatement += "'"
+ + data1.getValueForColumn(columnName).replaceAll("'", "''") + "'";
+ } else {
+ alterStatement += data1.getValueForColumn(columnName);
+ }
+ }
+ }
+ alterStatement += ";";
+ m_deleteADEntry.add(alterStatement);
+
+ }
+
+ /**
* Creates an update statement for the AD_* entry identified by the given
* tableName, ADDataElement and keycolumns. And adds them to the global
* m_alterADEntry Vector.
Modified: contributions/stuff/migration/src/oracle/Table.java
===================================================================
--- contributions/stuff/migration/src/oracle/Table.java 2006-10-20 14:30:59 UTC (rev 553)
+++ contributions/stuff/migration/src/oracle/Table.java 2006-10-20 18:09:07 UTC (rev 554)
@@ -47,7 +47,7 @@
insert = insert.append("CREATE TABLE ").append(name).append("\n");
insert = insert.append("(").append("\n");
for (int i = 0; i < allTableColumns.size(); i++) {
- insert = insert.append(allTableColumns.get(i).getDefinitionString());
+ insert = insert.append(allTableColumns.get(i).getDefinitionStringForCreateTable());
if (i != allTableColumns.size() - 1) {
insert = insert.append(",\n");
}
Modified: contributions/stuff/migration/src/readme.txt
===================================================================
--- contributions/stuff/migration/src/readme.txt 2006-10-20 14:30:59 UTC (rev 553)
+++ contributions/stuff/migration/src/readme.txt 2006-10-20 18:09:07 UTC (rev 554)
@@ -60,7 +60,10 @@
Thats because the application generates these entries to provide window access for
the admin of the first custom client - but a fresh compiere database doesn't have
a client. Just copy these entries at the end of the other INSERT entries.
+ Search for a dublicated occurence of the other entries with editor search function.
+ If you can find the entry somewhere in the script you can ignore it (delete).
You can ignore DROP CONSTRAINT statements - just delete them.
+ If some statements are left over - try to apply them at after running the script.
3. Sometimes a temporarily default value is needed and the application tries to
find an applieable value - but applieable doesn't mean sensible in all cases.
So please look for REPLACE_ME markers in the generated script and check the
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|