modules/core/dbutils/pom.xml
| 2
modules/core/dbutils/src/main/java/org/rhq/core/db/PostgresqlDatabaseType.java
| 2
modules/core/dbutils/src/main/java/org/rhq/core/db/upgrade/DriftTemplateMapDatabaseUpgradeTask.java
| 51 ++++++++++
modules/core/dbutils/src/main/scripts/dbupgrade/db-upgrade.xml
| 43 ++++++--
4 files changed, 84 insertions(+), 14 deletions(-)
New commits:
commit e8db9a190cdf7a1e070bbc077a4d025e30835a24
Author: Jay Shaughnessy <jshaughn(a)redhat.com>
Date: Thu Dec 8 16:02:39 2011 -0500
Add 2.117 to remove an obsolete drift table that may exist after upgrades.
diff --git a/modules/core/dbutils/pom.xml b/modules/core/dbutils/pom.xml
index 85a5526..3350cdd 100644
--- a/modules/core/dbutils/pom.xml
+++ b/modules/core/dbutils/pom.xml
@@ -15,7 +15,7 @@
<description>Database schema setup, upgrade and other
utilities</description>
<properties>
- <db.schema.version>2.116</db.schema.version>
+ <db.schema.version>2.117</db.schema.version>
<rhq.ds.type-mapping>${rhq.test.ds.type-mapping}</rhq.ds.type-mapping>
<rhq.ds.db-name>${rhq.test.ds.db-name}</rhq.ds.db-name>
<rhq.ds.connection-url>${rhq.test.ds.connection-url}</rhq.ds.connection-url>
diff --git a/modules/core/dbutils/src/main/scripts/dbupgrade/db-upgrade.xml
b/modules/core/dbutils/src/main/scripts/dbupgrade/db-upgrade.xml
index 92ebef7..9e3bbd6 100644
--- a/modules/core/dbutils/src/main/scripts/dbupgrade/db-upgrade.xml
+++ b/modules/core/dbutils/src/main/scripts/dbupgrade/db-upgrade.xml
@@ -3823,7 +3823,12 @@
</schemaSpec>
<!-- JON 3.0 RELEASE uses DB Schema 2.116 -->
-
+
+ <schemaSpec version="2.117">
+ <!-- Remove an obsolete drift table that may be hanging around -->
+ <schema-dropTable table="RHQ_DRIFT_TEMPLATE_MAP" />
+ </schemaSpec>
+
</dbupgrade>
</target>
</project>
commit dedfe6ad8738d1be1c5a411943e4e48d4410ffd2
Author: Jay Shaughnessy <jshaughn(a)redhat.com>
Date: Thu Dec 8 15:28:14 2011 -0500
[Bug 753866 - DB Upgrade needs more work for existing drift records]
More work to support 4.1 and 4.2 upgrades for drift.
- In the java template map upgrade task
- add logic to generate the rhq_drift_def_template records
- add logic to generate rhq_drift_set records for each initial changeset
- Move some logic from 2.115 to 2.116 to make 2.115 match the
schema produced by a 4.2 dbsetup (fresh db). Note that prior to
an upgrade 4.2 databases must (for now at least) be manually set
to schema version 2.115. They are errantly set to 2.114 in the 4.2
release.
diff --git
a/modules/core/dbutils/src/main/java/org/rhq/core/db/upgrade/DriftTemplateMapDatabaseUpgradeTask.java
b/modules/core/dbutils/src/main/java/org/rhq/core/db/upgrade/DriftTemplateMapDatabaseUpgradeTask.java
index d060a2a..95d9ef7 100644
---
a/modules/core/dbutils/src/main/java/org/rhq/core/db/upgrade/DriftTemplateMapDatabaseUpgradeTask.java
+++
b/modules/core/dbutils/src/main/java/org/rhq/core/db/upgrade/DriftTemplateMapDatabaseUpgradeTask.java
@@ -20,6 +20,7 @@ package org.rhq.core.db.upgrade;
import java.sql.Connection;
import java.sql.SQLException;
+import java.util.List;
import mazz.i18n.Logger;
@@ -39,6 +40,8 @@ public class DriftTemplateMapDatabaseUpgradeTask implements
DatabaseUpgradeTask
public void execute(DatabaseType databaseType, Connection connection) throws
SQLException {
+ // First generate a drift def template for each of the existing mappings
+
String sql = "" //
+ " insert into RHQ_DRIFT_DEF_TEMPLATE (" //
+ " ID," //
@@ -61,5 +64,53 @@ public class DriftTemplateMapDatabaseUpgradeTask implements
DatabaseUpgradeTask
LOG.debug(DbUtilsI18NResourceKeys.EXECUTING_SQL, sql);
databaseType.executeSql(connection, sql);
+
+ // Second, assign one of the new templates to each of the existing definitions.
Note that in the
+ // upgrade scenario we are guaranteed to only have 1 template for the given
resource type so the
+ // select below is safe to return a single value.
+ sql = "" //
+ + " update RHQ_DRIFT_DEFINITION dd set DRIFT_DEF_TEMPLATE_ID =" //
+ + " ( select dt.ID" //
+ + " from RHQ_DRIFT_DEF_TEMPLATE dt" //
+ + " join RHQ_RESOURCE_TYPE rt on dt.RESOURCE_TYPE_ID = rt.ID"
//
+ + " join RHQ_RESOURCE res on rt.ID =
res.RESOURCE_TYPE_ID" //
+ + " and dd.RESOURCE_ID = res.ID" //
+ + " )" //
+ + " where dd.DRIFT_DEF_TEMPLATE_ID is null";
+
+ LOG.debug(DbUtilsI18NResourceKeys.EXECUTING_SQL, sql);
+
+ databaseType.executeSql(connection, sql);
+
+ // third, for each initial snapshot, create the newly required rhq_drift_set and
map the drift to it
+ sql = "select cs.ID from RHQ_DRIFT_CHANGE_SET cs where cs.VERSION =
0";
+ LOG.debug(DbUtilsI18NResourceKeys.EXECUTING_SQL, sql);
+
+ List<Object[]> rows = databaseType.executeSelectSql(connection, sql);
+
+ sql = "insert into RHQ_DRIFT_SET ( ID ) values ( "
+ + databaseType.getSequenceInsertValue(connection,
"RHQ_DRIFT_SET_ID_SEQ") + " )";
+
+ for (Object[] row : rows) {
+ int csId = ((Number) row[0]).intValue();
+
+ // insert the new drift set row
+ LOG.debug(DbUtilsI18NResourceKeys.EXECUTING_SQL, sql);
+
+ databaseType.executeSql(connection, sql);
+ int setId = databaseType.getSequenceValue(connection,
"RHQ_DRIFT_SET", "ID");
+
+ // update the drift records for the initial change set to refer to the drift
set
+ String updateSql = "update RHQ_DRIFT set DRIFT_SET_ID = " + setId +
" where DRIFT_CHANGE_SET_ID = " + csId;
+ LOG.debug(DbUtilsI18NResourceKeys.EXECUTING_SQL, updateSql);
+
+ databaseType.executeSql(connection, updateSql);
+
+ // update the change set to refer to the drift set
+ updateSql = "update RHQ_DRIFT_CHANGE_SET set DRIFT_SET_ID = " +
setId + " where ID = " + csId;
+ LOG.debug(DbUtilsI18NResourceKeys.EXECUTING_SQL, updateSql);
+
+ databaseType.executeSql(connection, updateSql);
+ }
}
}
diff --git a/modules/core/dbutils/src/main/scripts/dbupgrade/db-upgrade.xml
b/modules/core/dbutils/src/main/scripts/dbupgrade/db-upgrade.xml
index 8f10a3b..92ebef7 100644
--- a/modules/core/dbutils/src/main/scripts/dbupgrade/db-upgrade.xml
+++ b/modules/core/dbutils/src/main/scripts/dbupgrade/db-upgrade.xml
@@ -3629,7 +3629,9 @@
<!-- RHQ 4.1 RELEASE uses DB Schema 2.114 -->
<!-- RHQ 4.2 RELEASE uses DB Schema 2.114
- Note, the 4.1 to 4.2 upgrade is broken because most ofthe changes in 2.115 should
have gone into 4.2.
+ Note, the 4.1 to 4.2 upgrade is broken because a 4.2 dbsetup actually creates a
2.115 schema. So,
+ for 4.2 dbs to upgrade to 4.3 the schema version must first be corrected and set the
schema
+ version to 2.115.
-->
<schemaSpec version="2.115">
@@ -3686,22 +3688,15 @@
</statement>
</schema-directSQL>
- <!-- increase size from 128 to 512 -->
- <schema-alterColumn table="RHQ_DRIFT_DEFINITION"
column="NAME" columnType="VARCHAR2" precision="512" />
-
<!-- add new columns -->
<schema-addColumn table="RHQ_DRIFT_DEFINITION"
column="DESCRIPTION" columnType="VARCHAR2" precision="512"
/>
<schema-addColumn table="RHQ_DRIFT_DEFINITION"
column="DRIFT_HANDLING_MODE" columnType="VARCHAR2"
precision="20" />
<schema-addColumn table="RHQ_DRIFT_DEFINITION"
column="IS_PINNED" columnType="BOOLEAN"/>
<schema-addColumn table="RHQ_DRIFT_DEFINITION"
column="IS_ATTACHED" columnType="BOOLEAN"/>
- <schema-addColumn table="RHQ_DRIFT_DEFINITION"
column="COMPLIANCE_STATUS" columnType="INTEGER"/>
<schema-addColumn table="RHQ_DRIFT_DEFINITION"
column="DRIFT_DEF_TEMPLATE_ID" columnType="INTEGER"/>
<!-- initialize new required columns -->
<schema-directSQL>
- <statement desc="RHQ_DRIFT_DEFINITION: set compliance_status
column (in compliance)">
- UPDATE RHQ_DRIFT_DEFINITION SET COMPLIANCE_STATUS = 0
- </statement>
<statement targetDBVendor="postgresql"
desc="RHQ_DRIFT_DEFINITION: set is_pinned column (false)">
UPDATE RHQ_DRIFT_DEFINITION SET IS_PINNED = FALSE
</statement>
@@ -3719,8 +3714,7 @@
</statement>
</schema-directSQL>
<schema-alterColumn table="RHQ_DRIFT_DEFINITION"
column="IS_PINNED" nullable="FALSE"/>
- <schema-alterColumn table="RHQ_DRIFT_DEFINITION"
column="IS_ATTACHED" nullable="FALSE"/>
- <schema-alterColumn table="RHQ_DRIFT_DEFINITION"
column="COMPLIANCE_STATUS" nullable="FALSE"/>
+ <schema-alterColumn table="RHQ_DRIFT_DEFINITION"
column="IS_ATTACHED" nullable="FALSE"/>
<schema-alterColumn table="RHQ_DRIFT_DEFINITION"
column="DRIFT_HANDLING_MODE" nullable="FALSE" />
<!-- add new FK -->
@@ -3771,8 +3765,8 @@
<!-- Add new drift_handling_mode column -->
<schema-addColumn table="RHQ_DRIFT_CHANGE_SET"
column="DRIFT_HANDLING_MODE" columnType="VARCHAR2"
precision="20" />
<schema-directSQL>
- <statement desc="RHQ_DRIFT_DEFINITION: set
drift_handling_mode column (normal)">
- UPDATE RHQ_DRIFT_DEFINITION SET DRIFT_HANDLING_MODE =
'normal'
+ <statement desc="RHQ_DRIFT_CHANGE_SET: set
drift_handling_mode column (normal)">
+ UPDATE RHQ_DRIFT_CHANGE_SET SET DRIFT_HANDLING_MODE =
'normal'
</statement>
</schema-directSQL>
<schema-alterColumn table="RHQ_DRIFT_CHANGE_SET"
column="DRIFT_HANDLING_MODE" nullable="FALSE" />
@@ -3805,11 +3799,31 @@
</schemaSpec>
+<!-- RHQ 4.2 RELEASE uses DB Schema 2.114, which is incorrect, it should have been
2.115. So, for the
+ upgrade to work for an RHQ 4.2 install the schema version must be set to 2.115 prior
to the upgrade.
+
+ The 4.2 upgrade should start with 2.116.
+-->
<schemaSpec version="2.116">
+ <!-- RHQ_DRIFT_DEFINITION changes -->
+ <schema-addColumn table="RHQ_DRIFT_DEFINITION"
column="COMPLIANCE_STATUS" columnType="INTEGER"/>
+ <schema-directSQL>
+ <statement desc="RHQ_DRIFT_DEFINITION: set compliance_status
column (in compliance)">
+ UPDATE RHQ_DRIFT_DEFINITION SET COMPLIANCE_STATUS = 0
+ </statement>
+ </schema-directSQL>
+ <schema-alterColumn table="RHQ_DRIFT_DEFINITION"
column="COMPLIANCE_STATUS" nullable="FALSE"/>
+
+ <schema-alterColumn table="RHQ_DRIFT_DEFINITION"
column="NAME" columnType="VARCHAR2" precision="512" />
+
+
+ <!-- RHQ_PRD_VER changes -->
<!-- Increase the max length of a product version from 50 to 100.
-->
<schema-alterColumn table="RHQ_PRD_VER"
column="VERSION" columnType="VARCHAR2" precision="100"
/>
</schemaSpec>
+<!-- JON 3.0 RELEASE uses DB Schema 2.116 -->
+
</dbupgrade>
</target>
</project>
commit 37acc1c7cc76cc8a069936d66f0182640ffb569d
Author: Jay Shaughnessy <jshaughn(a)redhat.com>
Date: Thu Dec 8 15:22:04 2011 -0500
Change the getSequenceValue() method for postgres db type so that it
actually works.
diff --git
a/modules/core/dbutils/src/main/java/org/rhq/core/db/PostgresqlDatabaseType.java
b/modules/core/dbutils/src/main/java/org/rhq/core/db/PostgresqlDatabaseType.java
index 53555ed..4d376f3 100644
--- a/modules/core/dbutils/src/main/java/org/rhq/core/db/PostgresqlDatabaseType.java
+++ b/modules/core/dbutils/src/main/java/org/rhq/core/db/PostgresqlDatabaseType.java
@@ -64,7 +64,7 @@ public abstract class PostgresqlDatabaseType extends DatabaseType {
* @see DatabaseType#getSequenceValue(Connection, String, String)
*/
public int getSequenceValue(Connection conn, String table, String key) throws
SQLException {
- String query = "SELECT last_value FROM " + table + "_" + key
+ "_seq";
+ String query = "SELECT currval('" + table + "_" + key +
"_seq')";
PreparedStatement selectPS = null;
ResultSet rs = null;