First, there are a few concepts to understand:
dbsetup - setup is based off of the *-schema.xml files located in /rhq/trunk/modules/core/dbutils/src/main/scripts/dbsetup/ for the various tables that make up the RHQ data model
dbupgrade - this is based off of the single db-upgrade.xml file located at http://git.fedorahosted.org/git/?p=rhq/rhq.git;a=blob;f=modules/core/dbutils/src/main/scripts/dbupgrade/db-upgrade.xml
your current schema version - this is based off of the last time you ran either the dbsetup or dbupgrade commands:
if you run dbsetup, it will combine the *-schema.xml into a file called all-schema.xml and use the <db.schema.version> property in the dbutils pom to seed your rhq_system_config property with the name 'DB_SCHEMA_VERSION'
if you run dbupgrade, it will read your current version by scanning the rhq_sytem_config table for the property named 'DB_SCHEMA_VERSION', and then execute all <schemaSpec version="X.Y"> elements from db-upgrade.xml that are strictly greater than your current version; at the end of this processing, it will overwrite the 'DB_SCHEMA_VERSION' property in the rhq_system_config table with the version attribute of the last schemaSpec element it successfully executed
This page describes what you need to do if you have to change the RHQ database schema, including removing an obsolete table, adding a new table or modifying an existing table and its columns. This upgrade procedure should occur in two distinctive phases. Be sure to do Phase One first (db-upgrade) going back to previous schema is lengthy and unnecessary process.
First, edit db-upgrade.xml. Add a new schemaSpec element and increment the version attribute by 1. Add the appropriate SQL statements to move an existing "old" schema into whatever the "new" schema will be (i.e. perform the necessary SQL commands to add/remove/modify your new table definitions).
Try your best to use generic SQL that is compatible across all database versions and vendors. However, if you must use version/vendor specific SQL statements, you can target the database you want to execute the SQL on by using the appropriate db-upgrade ANT task definitions. There are examples of doing this in the db-upgrade.xml file, just mimic what they do.
Note: do NOT at this point increment the <db.schema.version> element in the dbutils module's pom.xml
To test the upgrade, run "mvn -Ddbsetup-upgrade install" from the dbutils module. This will execute only the new schemaSpecs you have added to the db-upgrade.xml file. If you have issues along the way (syntax errors in your SQL, incorrectly formatted XML element, etc), you can restart your test by getting your database back to the state it was in before the upgrade task was run. To do this, simple do a dbsetup by executing "mvn -Ddbsetup install"
Note: you could have actually run "mvn -Ddbsetup install" the first time around, but it would have unnecessarily dropped your schema, then added it back, only to execute the small upgrade step. So, generally, you'll only need to perform the full setup if there is a failure that executes part of your DDL in the new schemaSpec, but not all of it.
Once you are satisfied that the db-upgrade.xml is bug free, make the necessarily modifications / additions / deletions to the various *-schema.xml files that need to be altered
At this point, you can also optionally take a snapshot of your database by performing an export of your DDL (this is so you can later verify that the state your database is in after a raw dbsetup is the EXACT same state after a dbupgrade)
Change your <db.schema.version> element in the dbutils module's pom.xml to the version attribute of the latest/newest schemaSpec in your db-upgrade.xml (including the additional schemaSpec elements you just added in phase one)
Finally, run your dbsetup by executing "mvn -Ddbsetup install" from the dbutils module
If any errors occur, simply fix your *-schema.xml to fix the errors and rerun the command; once you are satisfied that the setup produces the correct data model, commit all of the changes you've made to the dbutils module to the svn repository
Minimally this commit should include the pom.xml with the new <db.schema.version> element, the db-upgrade.xml file with the new schemaSpec element(s), and one or more *-schema.xml files with your recent modifications
Note: if you want to be absolutely positive that the results from a dbsetup match the results from a db-upgrade, then you should take another snapshot of your DDL at this point and use some diff tool to compare that this export and the one you took above are identical
If this is a new table, figure out which subsystem it is most appropriate for and add it to that subsystem's *-schema.xml file. If the new table has foreign key relationships, the order will matter. In other words, you will have to make sure you put the new definition in the proper place in the proper *-schema.xml file so it is created after all its dependent tables are created first.
The dependency information between the various *-schema.xml files is, to date, hard-coded into the build process; look at the current value of the property with the name "dbsetup.subsystems" in the dbsetup-build.xml file.
At the time of this writing, the subsystems were ordered as follows: config, cluster, inventory, auth, authz, operation, events, sysconfig, scheduler, amps, measurement, content, resource-request, jms
If this is an old table, do not delete its definition. Instead:
Add the attribute obsolete="true" to the <table> XML element.
This will allow for dbsetup to remove the table when it needs to overwrite an existing schema.
Cut the table definition from the current schema XML file and move it to obsolete-schema.xml.
This ensures that obsolete tables will be removed first during a dbsetup operation. This can be necessary to ensure proper dependency handling.
You can test your schema and queries by going to the test page: http://localhost:7080/admin/test/hibernate.jsp
Take a look in your database and see what value you have for the DB_SCHEMA_VERSION property in the rhq_system_config table, and compare that to the version in the pom.xml file of the dbutils module. If they are not equal, you're not at the latest schema yet which indicates one of two things:
you haven't run dbupgrade yet
there was an error when running dbupgrade,
If you're already at the least schemaSpec but need to re/test dbupgrade, you need to:
revert (or set aside) your changes to *-schema.xml files, if they have local modifications
edit the schema version in the pom.xml file of your dbutils module to 'lowerVersion'
run dbsetup to bring your database back to 'lowerVersion'
edit the schema version in the pom.xml file of your dbutils module back up to 'higherVersion'
run dbupgrade to execute the schemaSpecs in the range (lowerVersion, higherVersion]
if you have any errors, repeat all but the first step; otherwise you're done and can add changes back to your *-schema.xml files
If you believe you've followed all the dbsetup or dbupgrade steps correctly and are still getting errors, find the committer from the SCM history and tell him which supported db vendor (Oracle / Postgres / H2) your database is failing on and pass the error message along to him
Note: fixing schema errors should have high priority because they generally affect everyone else working in your branch. if those changes have been pushed to master, then everyone will be affected who has merged those changes into their respective lines of development.