If a database DML change involves adding rows to the LOOKUP_VALUE table then the upgrade for this database change should be done in Java rather than by using an upgrade_to_xxx.sql script. Note that latest-data.sql (and perhaps others) must still be modified – see scripting database changes LatestTestAfterCheckpointIntegrationTest and DatabaseVersionPersistence for the mechanics of database upgrades.
The reason for this is that in a live database an end user may add data to the database (including new rows in the LOOKUP_VALUE table) via the Mifos Web interface. Inserting a new row with the next available ID for the LOOKUP_VALUE would potentially conflict with IDs that have been added.
Different solutions to this problem have been considered and in the future we may: _ limit dynamically created LOOKUP_VALUES to a particular range (e.g. starting at 10000). _ eliminate the use of LOOKUP_VALUES for static configuration text.
For the time being, the solution outlined below is what should be used.
There are also cases where an upgrade requires conditional logic or other logic that doesn't lend itself to expression in a SQL script according to a research paper. In these cases a Java based upgrade can also be used.
What To Do
In the class DatabaseVersionPersistence, add a new call to a "register" method in the static method masterRegister. There are lots of entries already there like register101, register102, etc. Follow the convention and use the number of the database upgrade you are implenting. If certain standard elements (such as Activities) are being added then a method like register101 can be used as a model for adding an update. Standard upgrade elements that can be used all extend the class Upgrade.
If you are doing an upgrade that does not involve standard elements that have already been defined or involves other logic, then you can create a new class which is derived from the Upgrade class. There are several examples of this including classes Upgrade104, Upgrade127, Upgrade167, etc. Look at those classes and follow the pattern you'll observe there (namely, implement upgrade methods which conclude with an upgradeVersion method call).
The Upgrade class includes helper methods like insertLookupValue which help in performing basic upgrade related operations.
APPLICATION_VERSION in DatabaseVersionPersistence must be moved to the next higher value, as with SQL-only database upgrades